# MySQL 常用语句 ## 管理库/表(数据定义语言-DDL) ### 链接数据库 ```sql # 一般省略-h参数,mysql会自动默认为本地连接 # -p 参数是可选的,使用了-p 参数,但没有给值,客户端会提示输入密码,如果给值 -p 和密码之间不能有空格 mysql -h localhost -u root -p密码 ``` > 所有 SQL 命令结尾都必须以 `;` 或 `\g` 结尾 ### 选择数据库 ```sql USE user; # 返回值:Database changed ``` ### 显示所有数据库 ```sql SHOW DATABASES; # 返回值: # +--------------------+ # | Database | # +--------------------+ # | information_schema | # | mysql | # | performance_schema | # | test | # | user | # +--------------------+ ``` ### 显示数据表 ```sql SHOW TABLES; # 返回值: # +----------------+ # | Tables_in_user | # +----------------+ # | userinfo | # | xxx | # +----------------+ ``` ### 显示表列 ```sql SHOW COLUMNS FROM userinfo; DESC userinfo; # DESC 是 SHOW COLUMNS 的快捷方式 # 返回值包含:表结构和字段名、数据类型、是否允许NULL、键信息、默认值等其它信息 # +----------+-------------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +----------+-------------+------+-----+---------+-------+ # | username | varchar(10) | YES | | NULL | | # | age | varchar(10) | YES | | NULL | | # +----------+-------------+------+-----+---------+-------+ ``` ### 其它 SHOW 方法 ```sql SHOW STATUS # 显示广泛的服务器状态信息 SHOW CREATE DATABASE # 显示创建特定数据库 SHOW CREATE TABLE # 显示创建表的 MySQL 语句 SHOW GRANTS # 显示授予用户(所有用户或特定用户)的安全权限 SHOW ERRORS # 显示服务器错误消息 SHOW WARNINGS # 显示服务器警告消息 # 当忘了某个命令的时候可以使用 HELP 来查询 HELP SHOW # 如:HELP SELECT; HELP UPDATE; ``` ### 创建数据库 ```sql # 如果数据库名带 “-”,如:api-server, 则需要使用 `` 包裹,即:`api-server` CREATE DATABASE `数据库名`; # IF NOT EXISTS 表示如果需要创建的数据库已存在则不再创建;同时适用于创建数据表时使用 CREATE DATABASE IF NOT EXISTS `数据库名`; # DEFAULT CHARACTER SET utf8:数据库字符集,设置数据库的默认编码为 utf8,utf8 中间不要 "-"; # COLLATE utf8_general_ci:数据库校对规则, # utf8_bin:将字符串中的每一个字符用二进制数据存储,区分大小写。 # utf8_genera_ci:不区分大小写,ci 为 case insensitive 的缩写,即大小写不敏感。 # utf8_general_cs:区分大小写,cs 为 case sensitive 的缩写,即大小写敏感。 CREATE DATABASE IF NOT EXISTS `数据库名` DEFAULT CHARSET utf8 COLLATE utf8_general_ci; ``` ### 删除数据库 ```sql DROP DATABASE 数据库名; ``` ### 创建数据表 MySQL 常见数据类型: 1. **数值类型** 整数:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 浮点数:FLOAT、DOUBLE、DECIMAL 2. **日期时间类型** DATE(YYYY-MM-DD)、TIME(HH:MM:SS)、DATETIME、IMESTAMP(时间戳)、CURRENT_TIMESTAMP(系统当前时间)、YEAR 3. **字符串类型** CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM ```sql CREATE TABLE 表名( id INT UNSIGNED AUTO_INCREMENT, # id 为 INT 类型,无符号,自增;默认整型数据类型都是有符号的,char 是无符号 goodname VARCHAR(100) NOT NULL, # name 不能为空,可变长度的字符串类型 price VARCHAR(20) NOT NULL, # price 不能为空,可变长度的字符串类型 create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) # 设置 id 为主键 )ENGINE=InnoDB DEFAULT CHARSET=utf8; # 设置数据引擎为 innodb,编码为 utf8 ``` **<font color=red>PS</font>** 一个表中出现多个时间戳字段的定义,并设置了其中一个默认为 CURRENT_TIMESTAMP 会报错: > ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 解决办法: **方法一:**将 MySQL 升级到 5.6.5 版本以上,字段设置如下: ```sql create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ``` `ON UPDATE CURRENT_TIMESTAMP`:表示每次更新这条数据的时候,该字段都会更新成当前时间 **方法二:**不升级 MySQL 版本,将设定为 CURRENT_TIMESTAMP 的时间戳字段放在所有没有设定默认值的时间戳字段前面,则可以建表成功(**实际上 CURRENT_TIMESTAMP 时间戳默认值只能设置一个或者不设置,不能同时设置两个以上**)。 > 如果需求中有一个 update_time 字段和一个 create_time 字段,那么可以在建表时,设置其中一个字段为 CURRENT_TIMESTAMP 默认值,另一个字段在 insert 和 update 操作时写入具体的时间戳。 如下表所示:将 create_Time 字段设置 CURRENT_TIMESTAMP 时间戳,并在其他时间戳(start_time 和 end_time)前面,则创建成功 ```sql CREATE TABLE seckill( seckill_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '商品库存id', name VARCHAR(100) NOT NULL COMMENT '商品名称', number int NOT NULL COMMENT '库存数量', create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', start_time TIMESTAMP NOT NULL COMMENT '秒杀开启时间', end_time TIMESTAMP NOT NULL COMMENT '秒杀结束时间', PRIMARY KEY (seckill_id), key idx_start_time(start_time), key idx_end_time(end_time), key idx_create_time(create_time) )ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='秒杀库存表'; ``` ### 创建临时表 > 创建临时表关键字:`TEMPORARY` > MySQL 临时表只在当前连接可见,如果用脚本来创建 MySQL 临时表,那每当脚本执行完成后,该临时表也会自动销毁。 > 如果使用其他 MySQL 客户端程序连接 MySQL 数据库来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,也可以手动 DROP。 > 使用 `SHOW TABLES` 命令时,是看不到所创建的临时表的 ```sql CREATE TEMPORARY TABLE temp ( id INT NOT NULL AUTO_INCREMENT, field VARCHAR(100) NOT NULL DEFAULT 0, PRIMARY KEY (id) )ENGINE=InnoDB CHARSET=utf8; # 此时 SHOW TABLES 是看不到 temp 临时表的 # 插入一条数据 INSERT INTO temp (field) values (100); SELECT * FROM temp; # 返回结果: # +----+-------+ # | id | field | # +----+-------+ # | 1 | 100 | # +----+-------+ # 返回结果: # Query OK, 1 row affected (0.02 sec) # 手动删除临时表 DROP TABLE temp; # 返回结果: # Query OK, 0 rows affected (0.03 sec) SELECT * FROM temp; # 返回结果: # ERROR 1146 (42S02): Table 'class.temp' doesn't exist ``` ### 修改表名 ```sql ALTER TABLE 原表名 RENAME 新表名; ``` ### 删除数据表 ```sql DROP TABLE 表名; ``` ### 添加字段 ```sql # AFTER 表示指定添加到某个字段后面 ALTER TABLE 表名 ADD 新字段名 VARCHAR(100) AFTER 已存在字段名; ``` ### 修改字段 ```sql # 修改字段类型 ALTER TABLE 表名 MODIFY COLUMN 字段名 数据类型; # 修改字段名 ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型; # 删除默认值 ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT; # 设置默认值,如果已经存在默认值需要先删除 ALTER TABLE 表名 ALTER COLUMN 字段名 DEFAULT 默认值; ``` ### 删除字段 ``` ALTER TABLE 表名 DROP COLUMN 字段名; ``` ## 增删改查(数据操作语言-DML) ### 插入数据 如果表的定义允许,则可以在 INSERT 操作中省略某些列。 省略的列必须满足以下某个条件: 1. 该列定义为允许 NULL 值(无值或空值) 2. 在表定义中给出默认值。这表示如果不给出值,将使用默认值 如果对表中不允许 NULL 值且没有默认值的列不给出值,则 MySQL 将产生一条错误消息,并且相应的行插入不成功。 如果不提供列名,则必须给每个表列提供一个值。如果提供列名,则必须对每个列出的列给出一个值。 当语句中列名被明确列出时,插入顺序可以和表中真实顺序不一样 _1._ 插入一行数据 ```sql INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (value1,vaule2, ...); ``` _2._ 插入多行数据 当需要插入多条数据时,推荐使用一次 INSERT 多行数据,因为 MySQL 用单条 INSERT 语句处理多个插入比使用多条 INSERT 语句快。 ```sql INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (value1,vaule2, ...), (value1,vaule2, ...) ... ; ``` _3._ 插入检索出的数据 把从 A 表 SELECT 出来的数据,插入到 B 表中,不一定要求列名匹配,因为 MySQL 甚至不关心 SELECT 返回的列名,它使用的是列的位置。这对于从使用不同列名的表中导入数据是非常有用的。 ```sql # 这条语句将插入多少行依赖于 A 表中有多少行 INSERT INTO B表 (B表字段名1, B表字段名2, ...) SELECT A表字段名1, A表字段名2, ... FROM A表; ``` ### 更新数据 > 在 UPDATE 语句中可以使用子查询 修改一列数据 ```sql UPDATE 表名 SET 字段名 = 新值 WHERE 字段名 = 值 # 查询条件,如果没有查询条件则修改整个表 ; ``` 修改多列数据 ```sql UPDATE 表名 SET 字段名1 = 新值, 字段名2 = 新值 WHERE 字段名 = 值 # 查询条件 ; ``` ### 删除数据 删除一行数据 > DELETE 删除表的所有数据时,不会释放表所占用的空间,并且操作是可以撤销的 ```sql DELETE FROM 表名 WHERE 字段名 = 值; ``` 删除所有数据 > TRUNCATE TABLE 删除表的所有数据时,执行速度更快,而且还会释放表、段所占用的空间,并且操作是不能撤销的 ```sql TRUNCATE TABLE 表名; ``` ### 查询数据 _1._ 查询所有列 ```sql SELECT * FROM 表名; # 返回结果: # +----+----------+-----+-----------------+-----+ # | id | name | age | email | sex | # +----+----------+-----+-----------------+-----+ # | 1 | zhangsan | 50 | zhangsan@qq.com | 1 | # | 3 | lisi | 20 | lisi@qq.com | 0 | # | 4 | wangwu | 30 | wangwu@qq.com | 1 | # | 5 | zhaoliu | 40 | zhaoliu@qq.com | 0 | # | 6 | chenqi | 10 | chenqi@qq.com | 1 | # +----+----------+-----+-----------------+-----+ ``` _2._ 查询单列数据 ```sql SELECT 字段名 FROM 表名; # 返回结果的排序可能是数据被添加到表中的顺序,也可能不是 # 返回结果: # +----+ # | id | # +----+ # | 1 | # | 3 | # | 4 | # | 5 | # | 6 | # +----+ ``` _3._ 查询多列数据 ```sql SELECT 字段名1, 字段名2 FROM 表名; # 返回结果: # +----+----------+ # | id | name | # +----+----------+ # | 1 | zhangsan | # | 3 | lisi | # | 4 | wangwu | # | 5 | zhaoliu | # | 6 | chenqi | # +----+----------+ ``` _4._ 限定表名 同样也可以限定数据库名 ```sql SELECT 表名.字段名, 表名.字段名 FROM 表名; # SELECT 表名.字段名, 表名.字段名 FROM 数据库.表名; # 返回结果: # +----------+-----+ # | name | age | # +----------+-----+ # | zhangsan | 50 | # | lisi | 20 | # | wangwu | 30 | # | zhaoliu | 40 | # | chenqi | 10 | # +----------+-----+ ``` _5._ 查询不同的行 只返回不同(唯一)的字段值的行。 如:表中的 x 字段,每一条数据的值分别是 `0, 1, 2, 2, 1, 2, 0`,那么使用 `DISTINCT` 关键词查询 x 字段出来的结果就是 `0, 1, 2` ```sql SELECT DISTINCT 字段名1 FROM 表名; # 返回结果: # +-----+ # | sex | # +-----+ # | 1 | # | 0 | # +-----+ ``` ### 查询结果限制 - LIMIT `LIMIT 1,3` 表示从表中的第 2 条开始取 3 条数据 ```sql SELECT * FROM 表名 LIMIT 1,3; # MySQL 5 开始支持 OFFSET 写法 SELECT * FROM 表名 LIMIT 3 OFFSET 1; # 表示取 3 条数据,从第二条开始取 # 返回结果: # +----+---------+-----+----------------+-----+ # | id | name | age | email | sex | # +----+---------+-----+----------------+-----+ # | 3 | lisi | 20 | lisi@qq.com | 0 | # | 4 | wangwu | 30 | wangwu@qq.com | 1 | # | 5 | zhaoliu | 40 | zhaoliu@qq.com | 0 | # +----+---------+-----+----------------+-----+ ``` ### 查询结果排序 - ORDER BY _1._ 单列排序 ```sql SELECT * FROM 表名 ORDER BY 字段名; # 输出结果:按 age 排序 # +----+----------+-----+-----------------+-----+ # | id | name | age | email | sex | # +----+----------+-----+-----------------+-----+ # | 6 | chenqi | 10 | chenqi@qq.com | 1 | # | 3 | lisi | 20 | lisi@qq.com | 0 | # | 4 | wangwu | 30 | wangwu@qq.com | 1 | # | 5 | zhaoliu | 40 | zhaoliu@qq.com | 0 | # | 1 | zhangsan | 50 | zhangsan@qq.com | 1 | # +----+----------+-----+-----------------+-----+ ``` _2._ 多列排序 ```sql # 语法 # SELECT 字段名1, 字段名2 FROM 表名 ORDER BY 字段名1, 字段名2; # 示例: # 先按 sex 排序,在相同 sex 中再按 age 排序 SELECT sex, age FROM stu ORDER BY sex, age; # 输出结果 # +-----+-----+ # | sex | age | # +-----+-----+ # | 0 | 20 | # | 0 | 40 | # | 1 | 10 | # | 1 | 30 | # | 1 | 50 | # +-----+-----+ ``` _3._ 正序倒序 mysql 默认就是正序(ASC) ```sql # 单个字段倒序(降序) SELECT age FROM stu ORDER BY age DESC; # 输出结果: # +-----+ # | age | # +-----+ # | 50 | # | 40 | # | 30 | # | 20 | # | 10 | # +-----+ # 多个字段倒序(降序) SELECT sex, age FROM stu ORDER BY sex DESC, age; # 这里 sex 按倒序,age 按正序 # 输出结果: # +-----+-----+ # | sex | age | # +-----+-----+ # | 1 | 10 | # | 1 | 30 | # | 1 | 50 | # | 0 | 20 | # | 0 | 40 | # +-----+-----+ ``` _4._ 配合 LIMIT 的排序 ```sql # 找出 sex 字段中 age 最大的行 SELECT sex, age FROM stu ORDER BY sex DESC LIMIT 0, 1; # 输出结果: #+-----+-----+ #| sex | age | #+-----+-----+ #| 1 | 50 | #+-----+-----+ ``` ### 查询条件 - WHERE WHERE 操作符 | 操作符 | 说明 | | ------------ | ------------ | | = | 等于 | | <> | 不等于 | | != | 不等于 | | < | 小于 | | <= | 小于等于 | | > | 大于 | | >= | 大于等于 | | [AND &&](#查询多列) | 匹配多个条件 | | [OR &#124;&#124; ](#匹配任一条件) | 匹配任一条件 | | [IN](#in-操作符) | 指定条件范围,范围中的每个条件都可以进行匹配 | | [NOT !](#not-操作符) | 否定它之后所跟的任何条件 | | [BETWEEN](#范围查询) | 在指定的两个值之间 | #### 查询单列 ```sql SELECT * FROM stu WHERE name = 'zhangsan'; # 值为字符串时需要用单引号包裹 # 返回结果: # +----+----------+-----+-----------------+-----+ # | id | name | age | email | sex | # +----+----------+-----+-----------------+-----+ # | 1 | zhangsan | 50 | zhangsan@qq.com | 1 | # +----+----------+-----+-----------------+-----+ ``` #### 查询多列 AND 操作符 ```sql SELECT age, sex FROM stu WHERE age > 10 AND sex = 0; # 返回结果: # +-----+-----+ # | age | sex | # +-----+-----+ # | 20 | 0 | # | 40 | 0 | # | 70 | 0 | # | 90 | 0 | # | 99 | 0 | # +-----+-----+ ``` #### 匹配任一条件 OR 操作符 ```sql SELECT * FROM stu WHERE age < 30 OR sex = 0; # 返回结果: # +----+---------+-----+----------------+-----+ # | id | name | age | email | sex | # +----+---------+-----+----------------+-----+ # | 3 | lisi | 20 | lisi@qq.com | 0 | # | 5 | zhaoliu | 40 | zhaoliu@qq.com | 0 | # | 6 | chenqi | 10 | chenqi@qq.com | 1 | # | 8 | dingjiu | 70 | dingjiu@qq.com | 0 | # | 9 | liaoji | 90 | liaoji@qq.com | 0 | # | 11 | mazi | 99 | NULL | 0 | # +----+---------+-----+----------------+-----+ ``` #### OR & AND 一起使用 > 在处理 OR 操作符前,优先处理 AND 操作符。 > 任何时候使用具有 AND 和 OR 操作符的 WHERE 子句,都应该使用圆括号明确地分组操作符。 ```sql # 查询 id > 5 或 sex = 0,并且 age > 70 SELECT id, sex, age FROM stu WHERE (sex = 0 OR id > 5) AND age > 70; # 返回结果: # +----+-----+-----+ # | id | sex | age | # +----+-----+-----+ # | 9 | 0 | 90 | # | 10 | 1 | 80 | # | 11 | 0 | 99 | # +----+-----+-----+ ``` #### IN 操作符 > IN 操作符完成与 OR 相同的功能 ```sql SELECT id, age FROM stu WHERE age IN (90, 30) ORDER BY id; # 返回结果: # +----+-----+ # | id | age | # +----+-----+ # | 4 | 30 | # | 9 | 90 | # +----+-----+ ``` #### NOT 操作符 > MySQL 支持使用 NOT 对 IN 、 BETWEEN 和 EXISTS 子句取反 ```sql SELECT id, age FROM stu WHERE age NOT IN (90, 30) ORDER BY id; # 返回结果: # +----+-----+ # | id | age | # +----+-----+ # | 1 | 50 | # | 3 | 20 | # | 5 | 40 | # | 6 | 10 | # | 7 | 60 | # | 8 | 70 | # | 10 | 80 | # | 11 | 99 | # +----+-----+ ``` #### 范围查询 ```sql SELECT * FROM stu WHERE age BETWEEN 30 AND 60; # 返回结果: # +----+----------+-----+-----------------+-----+ # | id | name | age | email | sex | # +----+----------+-----+-----------------+-----+ # | 1 | zhangsan | 50 | zhangsan@qq.com | 1 | # | 4 | wangwu | 30 | wangwu@qq.com | 1 | # | 5 | zhaoliu | 40 | zhaoliu@qq.com | 0 | # | 7 | qianba | 60 | qianba@qq.com | 1 | # +----+----------+-----+-----------------+-----+ ``` #### 查询空值 ```sql SELECT * FROM stu WHERE email IS NULL; # 返回结果: # +----+------+-----+-------+-----+ # | id | name | age | email | sex | # +----+------+-----+-------+-----+ # | 11 | mazi | 99 | NULL | 0 | # +----+------+-----+-------+-----+ ``` #### 配合 ORDER BY > ORDER BY 必须放在 WHERE 后面 ```sql SELECT * FROM stu WHERE age > 60 ORDER BY age DESC; # 返回结果: # +----+---------+-----+----------------+-----+ # | id | name | age | email | sex | # +----+---------+-----+----------------+-----+ # | 11 | mazi | 99 | NULL | 0 | # | 9 | liaoji | 90 | liaoji@qq.com | 0 | # | 10 | yeshi | 80 | yeshi@qq.com | 1 | # | 8 | dingjiu | 70 | dingjiu@qq.com | 0 | # +----+---------+-----+----------------+-----+ ``` ### 通配符 LIKE 通配符 LIKE 也有叫 模糊查询 > 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。 > 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。 > 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据 _1._ 百分号( % )通配符 > % 代表搜索模式中给定位置的 0 个、1 个或多个字符 > 尾空格可能会干扰通配符匹配,例如在保存字段值 `liaoji` 时,在它后面有一个或多个空格,则子句 `WHERE name LIKE '%ji'` 将不会匹配它们,解决这个问题的一个简单的办法是在最后附加一个 % 。 ```sql SELECT name FROM stu WHERE name LIKE '%ao%'; # 返回结果: # +---------+ # | name | # +---------+ # | zhaoliu | # | liaoji | # +---------+ ``` _2._ 下划线(\_)通配符 > 只匹配单个字符 ```sql SELECT * FROM stu WHERE id LIKE '1_'; # 返回结果: # +----+-------+-----+--------------+-----+ # | id | name | age | email | sex | # +----+-------+-----+--------------+-----+ # | 10 | yeshi | 80 | yeshi@qq.com | 1 | # | 11 | mazi | 99 | NULL | 0 | # +----+-------+-----+--------------+-----+ ``` ### 正则表达式 > MySQL 中的正则表达式匹配不区分大小写,区分大小写使用 `BINARY` 关键字,如 `WHERE name REGEXP BINARY 'Chen'` > 匹配特殊字符,如 `.` `[]`,使用反斜杠转义 `\` **预定义字符集** | 类 | 说 明 | | ---------- | ------------------------------------------------- | | [:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) | | [:alpha:] | 任意字符(同[a-zA-Z]) | | [:blank:] | 空格和制表(同[\\t]) | | [:cntrl:] | ASCII 控制字符(ASCII 0 到 31 和 127) | | [:digit:] | 任意数字(同[0-9]) | | [:graph:] | 与[:print:]相同,但不包括空格 | | [:lower:] | 任意小写字母(同[a-z]) | | [:print:] | 任意可打印字符 | | [:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 | | [:space:] | 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v]) | | [:upper:] | 任意大写字母(同[A-Z]) | | [:xdigit:] | 任意十六进制数字(同[a-fA-F0-9]) | **重复元字符** | 元 字 符 | 说 明 | | -------- | ------------------------------ | | \* | 0 个或多个匹配 | | + | 1 个或多个匹配(等于{1,}) | | ? | 0 个或 1 个匹配(等于{0,1}) | | {n} | 指定数目的匹配 | | {n,} | 不少于指定数目的匹配 | | {n,m} | 匹配数目的范围(m 不超过 255) | **定位元字符** | 元 字 符 | 说 明 | | -------- | ---------- | | ^ | 文本的开始 | | \$ | 文本的结尾 | | [[:<:]] | 词的开始 | | [[:>:]] | 词的结尾 | _1._ 基本字符匹配 ```sql SELECT name FROM stu WHERE name REGEXP 'li'; # 返回结果: # +---------+ # | name | # +---------+ # | lisi | # | zhaoliu | # | liaoji | # +---------+ ``` 匹配任意一个字符 > `.` 在正则表达式语言中表示匹配任意一个字符 ```sql SELECT name FROM stu WHERE name REGEXP '.iu'; # 返回结果: # +---------+ # | name | # +---------+ # | zhaoliu | # | dingjiu | # +---------+ ``` _2._ OR 匹配 ```sql SELECT age FROM stu WHERE age REGEXP '30|90'; # 返回结果: # +-----+ # | age | # +-----+ # | 30 | # | 90 | # +-----+ ``` _3._ 匹配特定字符 `[]` > `[]` 是另一种形式的 OR 语句,`[xyz]` 中定义一组字符,它的意思是匹配 x 或 y 或 z > `[]` 和 OR 匹配的区别:OR 匹配应用于整个值,而 `[]` 限定于某个位置 > `[]` 中可以添加其它正则,如 [^xzy] 匹配除这些字符外的任何东西 ```sql SELECT email FROM stu WHERE email REGEXP '[nq]@qq'; # 返回结果: 只查询 @qq 前一个字符为 n 或 q 的数据 # +-----------------+ # | email | # +-----------------+ # | zhangsan@qq.com | # | zhubaqq@qq.com | # +-----------------+ # [] 和 OR 匹配的区别 SELECT email FROM stu WHERE email REGEXP 'n|q@qq'; # 返回结果:查询出 @qq 前所有字符包含了 n 或 q 的数据 # +-----------------+ # | email | # +-----------------+ # | zhangsan@qq.com | # | wangwu@qq.com | # | chenqi@qq.com | # | qianba@qq.com | # | dingjiu@qq.com | # | zhubaqq@qq.com | # +-----------------+ ``` _4._ 匹配范围 `-` ```sql SELECT age FROM stu WHERE age REGEXP '[3-5]'; # 返回结果: # +-----+ # | age | # +-----+ # | 50 | # | 30 | # | 40 | # +-----+ ``` ### 别名 - AS > 别名除了用于列名和计算字段外,还允许给表名起别名。表别名只在查询执行中使用,表别名不在查询结果中体现 ```sql SELECT name AS '姓名', age AS '年龄' FROM stu; # 返回结果: # +----------+------+ # | 姓名 | 年龄 | # +----------+------+ # | zhangsan | 50 | # | lisi | 20 | # | wangwu | 30 | # | zhaoliu | 40 | # | chenqi | 10 | # | qianba | 60 | # | dingjiu | 70 | # | liaoji | 90 | # | yeshi | 80 | # | mazi | 99 | # | zhuba | 88 | # +----------+------+ ``` ### 算术操作符 MySQL 支持基本算术操作符:`+`、`-`、`*`、`/` ```sql SELECT age * 10 AS '年龄乘以10' FROM stu; # 返回结果: # +------------+ # | 年龄乘以10 | # +------------+ # | 500 | # | 200 | # | 300 | # | 400 | # | 100 | # | 600 | # | 700 | # | 900 | # | 800 | # | 990 | # | 880 | # | 770 | # +------------+ # 两个字段计算 SELECT age, sex, age * sex AS 'age乘以sex' FROM stu; # 返回结果: # +-----+-----+------------+ # | age | sex | age乘以sex | # +-----+-----+------------+ # | 50 | 1 | 50 | # | 20 | 0 | 0 | # | 30 | 1 | 30 | # | 40 | 0 | 0 | # | 10 | 1 | 10 | # | 60 | 1 | 60 | # | 70 | 0 | 0 | # | 90 | 0 | 0 | # | 80 | 1 | 80 | # | 99 | 0 | 0 | # | 88 | 0 | 0 | # | 77 | 0 | 0 | # +-----+-----+------------+ ``` ### 函数 - 拼接字段 CONCAT() ```sql SELECT CONCAT(name, ' 已经 ',age, ' 岁了') AS '某某人几岁了' FROM stu ORDER BY age; # 返回结果: # +------------------------+ # | 某某人几岁了 | # +------------------------+ # | chenqi 已经 10 岁了 | # | lisi 已经 20 岁了 | # | wangwu 已经 30 岁了 | # | zhaoliu 已经 40 岁了 | # | zhangsan 已经 50 岁了 | # | qianba 已经 60 岁了 | # | dingjiu 已经 70 岁了 | # | zhaoqian 已经 77 岁了 | # | yeshi 已经 80 岁了 | # | zhuba 已经 88 岁了 | # | liaoji 已经 90 岁了 | # | mazi 已经 99 岁了 | # +------------------------+ ``` ### 文本处理函数 常用的文本处理函数 | 函 数 | 说 明 | 语法 | | ------------------------- | --------------------------------------------------------------------------------- | ------------------------------------------------------------------ | | LENGTH() | 返回字段的长度 | LENGTH(field) | | [LEFT()](#left) | 截取从左边开始的 n 个字符 | LEFT(field, number) | | RIGHT() | 截取从右边开始的 n 个字符 | RIGHT(field, number) | | [SUBSTRING()](#substring) | 截取指定位置开始的 n 个字符 | SUBSTRING(field, 开始位置, 截取字符串个数[可选,省略表示直到结束]) | | [LOCATE()](#locate) | 找出某个字符所在的位置;<br>在 WHERE 条件中使用:找出从指定位置开始某个字符的数据 | LOCATE(需要查找的字符, field, 开始位置[可选,省略表示从左边开始]) | | [REVERSE](#reverse) | 反转字符串 | REVERSE(field) | | LOWER() | 转换为小写 | LOWER(field) | | [UPPER()](#upper) | 转换为大写 | UPPER(field) | | [TRIM()](#trim) | 删除左右空格空格 | TRIM(field) | | LTRIM() | 删除左边的空格 | LTRIM(field) | | RTRIM() | 删除右边的空格 | RTRIM(field) | | SOUNDEX() | 返回的 SOUNDEX 值 | | #### LEFT() ```sql SELECT LEFT(name, 3) FROM stu LIMIT 5; # 返回结果: # +--------------+ # | left(name, 3) | # +--------------+ # | zha | # | lis | # | wan | # | zha | # | che | # +--------------+ ``` #### SUBSTRING() 截取 `email` 字段中第 3 个字符开始,到第 8 个字符结束 ```sql SELECT SUBSTRING(email, 3, 8) FROM stu LIMIT 5; # 返回结果: # +------------------------+ # | SUBSTRING(email, 3, 8) | # +------------------------+ # | angsan@q | # | si@qq.co | # | ngwu@qq. | # | aoliu@qq | # | enqi@qq. | # +------------------------+ ``` #### LOCATE() ```sql # 找出 name 字段中包含 an 的数据 SELECT name FROM stu WHERE LOCATE('an', name); # 返回结果: # +-----------+ # | name | # +-----------+ # | zhangsan | # | wangwu | # | qianba | # | zhaoqian | # +-----------+ # 从第 4 个字符开始查找出 name 字段中包含 an 的数据 SELECT name FROM stu WHERE LOCATE('an', name, 4); # 返回结果: # +-----------+ # | name | # +-----------+ # | zhangsan | # | zhaoqian | # +-----------+ # 找出指定字符所在的位置 SELECT LOCATE('qq', email) FROM stu LIMIT 5; # 返回结果: # +---------------------+ # | LOCATE('qq', email) | # +---------------------+ # | 10 | # | 6 | # | 8 | # | 9 | # | 8 | # +---------------------+ # 找出 email 字段中 @ 符之前的内容 SELECT LEFT(email, (LOCATE('qq', email) - 1)) FROM stu LIMIT 5; # 返回结果: # +----------------------------------------+ # | LEFT(email, (LOCATE('qq', email) - 1)) | # +----------------------------------------+ # | zhangsan@ | # | lisi@ | # | wangwu@ | # | zhaoliu@ | # | chenqi@ | # +----------------------------------------+ ``` #### REVERSE() ```sql SELECT REVERSE(name) FROM stu LIMIT 5; # 返回结果: # +---------------+ # | REVERSE(name) | # +---------------+ # | nasgnahz | # | isil | # | uwgnaw | # | uiloahz | # | iqnehc | # +---------------+ ``` #### TRIM() 上面[函数 - 拼接字段 CONCAT()](#函数-拼接字段-concat)的查询结果中, `zhaoqian` 这一行数据,在姓名这个字段右边是多出了一个空格的 ```sql SELECT CONCAT(TRIM(NAME), '去掉空格') FROM stu WHERE name REGEXP 'zhaoqian'; # 返回结果: # +--------------------------------+ # | CONCAT(TRIM(NAME), '去掉空格') | # +--------------------------------+ # | zhaoqian去掉空格 | # +--------------------------------+ ``` #### UPPER() ```sql SELECT UPPER(name) FROM stu LIMIT 5; # 返回结果: # +-------------+ # | UPPER(name) | # +-------------+ # | ZHANGSAN | # | LISI | # | WANGWU | # | ZHAOLIU | # | CHENQI | # +-------------+ ``` ### 日期时间函数 常用日期和时间处理函数 | 函 数 | 说 明 | | ------------- | ------------------------------ | | ADDDATE() | 增加一个日期(天、周等) | | ADDTIME() | 增加一个时间(时、分等) | | CURDATE() | 返回当前日期 | | CURTIME() | 返回当前时间 | | DATE() | 返回日期时间的日期部分 | | DATEDIFF() | 计算两个日期之差 | | DATE_ADD() | 高度灵活的日期运算函数 | | DATE_FORMAT() | 返回一个格式化的日期或时间串 | | DAY() | 返回一个日期的天数部分 | | DATOFWWWK() | 对于一个日期,返回对应的星期几 | | HOUR() | 返回一个时间的小时部分 | | MINUTE() | 返回一个时间的分钟部分 | | MONTH() | 返回一个日期的月份部分 | | NOW() | 返回当前日期和时间 | | SECOND() | 返回一个时间的秒部分 | | TIME() | 返回一个日期时间的时间部分 | | YEAR() | 返回一个日期的年份部分 | ```sql SELECT create_time FROM myshop WHERE DATE(create_time) = '2019-07-02'; # 返回结果: # +---------------------+ # | create_time | # +---------------------+ # | 2019-07-02 10:06:35 | # | 2019-07-02 11:24:06 | # | 2019-07-02 11:25:18 | # | 2019-07-02 11:26:28 | # +---------------------+ # 查询所有日期为 2 号的数据 SELECT start_time FROM myshop WHERE DAY(start_time) = 2; # 返回结果: # +---------------------+ # | start_time | # +---------------------+ # | 2019-06-02 01:03:10 | # | 2019-07-02 01:03:10 | # | 2019-07-02 01:03:10 | # | 2019-07-02 11:24:06 | # | 2019-07-02 11:25:18 | # | 2019-07-02 11:26:28 | # +---------------------+ # 查询 2019-07-03 到 2019-07-05 的所有数据 SELECT start_time FROM myshop WHERE DATE(start_time) BETWEEN '2019-07-03' AND '2019-07-05'; # 返回结果: # +---------------------+ # | start_time | # +---------------------+ # | 2019-07-03 01:03:10 | # | 2019-07-04 01:03:10 | # | 2019-07-05 01:03:10 | # +---------------------+ # 查询 2019-06 的所有数据 SELECT start_time FROM myshop WHERE YEAR(start_time) = 2019 AND MONTH(start_time) = 6; # 返回结果: # +---------------------+ # | start_time | # +---------------------+ # | 2019-06-30 01:03:10 | # +---------------------+ ``` ### 聚集函数 _1._ 求平均值 - AVG() ```sql SELECT AVG(age) FROM stu; # 返回结果: # +----------+ # | AVG(age) | # +----------+ # | 59.5 | # +----------+ ``` _2._ 统计有多少条数据 - COUNT() ```sql SELECT COUNT(*) FROM stu; # 返回结果: # +----------+ # | COUNT(*) | # +----------+ # | 12 | # +----------+ ``` _3._ 求最大值 - MAX() ```sql SELECT MAX(age) FROM stu; # 返回结果: # +----------+ # | MAX(age) | # +----------+ # | 99 | # +----------+ ``` _4._ 求最小值 - MIN() ```sql SELECT MIN(age) FROM stu; # 返回结果: # +----------+ # | MIN(age) | # +----------+ # | 10 | # +----------+ ``` _5._ 求和 - SUM() ```sql SELECT SUM(age) FROM stu; # 返回结果: # +----------+ # | SUM(age) | # +----------+ # | 714 | # +----------+ ``` _6._ 只包含不同的值(去重) - DISTINCT ```sql SELECT AVG(number) FROM myshop; # 返回结果: +-------------+ | AVG(number) | +-------------+ | 2313.3333 | +-------------+ SELECT AVG(DISTINCT number) FROM myshop; # 返回结果: +----------------------+ | AVG(DISTINCT number) | +----------------------+ | 2712.5000 | +----------------------+ ``` ### 分组 - GROUP BY & HAVING _1._ 分组 - GROUP BY > GROUP BY 子句必须出现在 WHERE 子句之后, ORDER BY 子句之前 ```sql SELECT sex, COUNT(*) FROM stu GROUP BY sex; # 返回结果: # +-----+----------+ # | sex | COUNT(*) | # +-----+----------+ # | 0 | 7 | # | 1 | 5 | # +-----+----------+ # WITH ROLLUP 关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组)的值 SELECT sex, COUNT(*) FROM stu GROUP BY sex WITH ROLLUP; # 返回结果: # +-----+----------+ # | sex | COUNT(*) | # +-----+----------+ # | 0 | 7 | # | 1 | 5 | # | NULL | 12 | # +-----+----------+ ``` _2._ 过滤 - HAVING > WHERE 在数据分组前进行过滤, HAVING 在数据分组后进行过滤 ```sql SELECT sex, COUNT(*) FROM stu GROUP BY sex HAVING COUNT(*) > 5; # 返回结果: # +-----+----------+ # | sex | COUNT(*) | # +-----+----------+ # | 0 | 7 | # +-----+----------+ ``` _3._ 组合使用 WHERE + GROUP BY + HAVING ```sql # 人数 > 5、age > 30 的分组 SELECT sex, COUNT(*) FROM stu WHERE age > 30 GROUP BY sex HAVING COUNT(*) > 5; # 返回结果: # +-----+----------+ # | sex | COUNT(*) | # +-----+----------+ # | 0 | 6 | # +-----+----------+ ``` GROUP BY + HAVING + ORDER BY ```sql SELECT sex, COUNT(*) FROM stu GROUP BY sex HAVING COUNT(*) >= 2 ORDER BY COUNT(*); # 返回结果: # +-----+----------+ # | sex | COUNT(*) | # +-----+----------+ # | 1 | 5 | # | 0 | 7 | # +-----+----------+ ``` ### 子查询 > 执行顺序:先执行子查询语句,再执行上层查询语句,即:从内向外执行 > 不限嵌套层数 > 子查询一般与 IN 操作符结合使用,但也可以用于测试等于( = )、不等于( <> )等 新建学生姓名表: ```sql # 建表 CREATE TABLE students( sid INT NOT NULL AUTO_INCREMENT COMMENT '学生id', name VARCHAR(100) NOT NULL COMMENT '学生姓名', PRIMARY KEY (sid) )ENGINE=InnoDB CHARSET=utf8 COMMENT='学生姓名表'; # 插入数据 insert into students(name) values ('zhangsan'), ('lisi'), ('wangwu'), ('zhaoliu'), ('chenqi'); ``` 新建课程表: ```sql # 建表 CREATE TABLE course( cid INT NOT NULL AUTO_INCREMENT COMMENT '课程id', course_name VARCHAR(100) NOT NULL COMMENT '课程', PRIMARY KEY (cid) )ENGINE=InnoDB CHARSET=utf8 COMMENT='课程表'; # 插入数据 insert into course(course_name) values ('语文'), ('数学'), ('英语'); ``` 新建学生成绩表: ```sql # 建表 CREATE TABLE result( rid INT NOT NULL AUTO_INCREMENT COMMENT '成绩id', sid VARCHAR(100) NOT NULL COMMENT '学生id', cid VARCHAR(100) NOT NULL COMMENT '课程id', grade VARCHAR(100) NOT NULL COMMENT '课程分数', PRIMARY KEY (rid) )ENGINE=InnoDB CHARSET=utf8 COMMENT='学生成绩表'; # 插入数据 insert into result(sid, cid, grade) values (1, 1, 101), (1, 2, 102), (1, 3, 103), (2, 1, 91), (2, 2, 92), (2, 3, 93), (3, 1, 81), (3, 2, 82), (3, 3, 83), (4, 1, 71), (4, 2, 72), (4, 3, 73), (5, 1, 61), (5, 2, 62), (5, 3, 63); ``` _1._ 查询 `lisi` 的 `grade` ```sql SELECT grade FROM result WHERE sid IN ( SELECT sid FROM students WHERE name = 'lisi' ); # 返回结果: # +-------+ # | grade | # +-------+ # | 91 | # | 92 | # | 93 | # +-------+ ``` _2._ 查询 `lisi` 的 `语文` 的成绩 ```sql SELECT grade FROM result WHERE cid = (SELECT sid FROM course WHERE course_name = '语文') && sid = (SELECT sid FROM students WHERE name = 'lisi') ; # 返回结果: # +-------+ # | grade | # +-------+ # | 92 | # +-------+ ``` _3._ 查询所有人的成绩,同时显示姓名和课程 ```sql SELECT rid, (SELECT students.name FROM students WHERE students.sid = result.sid) AS student_name, (SELECT course.course_name FROM course WHERE course.cid = result.cid) AS course_name, grade FROM result ORDER BY student_name; # 返回结果: # +-----+----------+-------------+-------+ # | rid | name | course_name | grade | # +-----+----------+-------------+-------+ # | 13 | chenqi | 语文 | 61 | # | 15 | chenqi | 英语 | 63 | # | 14 | chenqi | 数学 | 62 | # | 4 | lisi | 语文 | 91 | # | 6 | lisi | 英语 | 93 | # | 5 | lisi | 数学 | 92 | # | 8 | wangwu | 数学 | 82 | # | 7 | wangwu | 语文 | 81 | # | 9 | wangwu | 英语 | 83 | # | 1 | zhangsan | 语文 | 101 | # | 3 | zhangsan | 英语 | 103 | # | 2 | zhangsan | 数学 | 102 | # | 10 | zhaoliu | 语文 | 71 | # | 12 | zhaoliu | 英语 | 73 | # | 11 | zhaoliu | 数学 | 72 | # +-----+----------+-------------+-------+ ``` ### 表连接 - JOIN > SELECT / WHERE 时,如果字段名在多个表中是相同名称的需要完全限定列名,如:`students.name` > FROM 时指定需要链接的表名 > 表连接查询性能消耗很大,不要联结不必要的表。联结的表越多,性能下降越厉害 #### 内连接 - INNER JOIN 查询所有人的成绩,同时显示姓名和课程 ```sql SELECT rid, name, course_name, grade FROM students INNER JOIN result INNER JOIN course ON students.sid = result.sid AND course.cid = result.cid ORDER BY name; # 或 SELECT rid, name, course_name, grade FROM students, result, course WHERE students.sid = result.sid AND course.cid = result.cid ORDER BY name; # 返回结果: # +-----+----------+-------------+-------+ # | rid | name | course_name | grade | # +-----+----------+-------------+-------+ # | 13 | chenqi | 语文 | 61 | # | 15 | chenqi | 英语 | 63 | # | 14 | chenqi | 数学 | 62 | # | 4 | lisi | 语文 | 91 | # | 6 | lisi | 英语 | 93 | # | 5 | lisi | 数学 | 92 | # | 8 | wangwu | 数学 | 82 | # | 7 | wangwu | 语文 | 81 | # | 9 | wangwu | 英语 | 83 | # | 1 | zhangsan | 语文 | 101 | # | 3 | zhangsan | 英语 | 103 | # | 2 | zhangsan | 数学 | 102 | # | 10 | zhaoliu | 语文 | 71 | # | 12 | zhaoliu | 英语 | 73 | # | 11 | zhaoliu | 数学 | 72 | # +-----+----------+-------------+-------+ ``` #### 左连接 - LEFT JOIN > `LEFT JOIN` 会读取左表的全部数据,不管右表有无对应数据,当右表没有数据时,以 `NULL` 代替 为测试效果,先删除 `result` 表中 `sid` 为 `5` 的数据,即:`chenqi` 的数据 以下实例,以 `students` 为左表,`result` 为右表, ```sql SELECT result.sid, name, grade FROM students LEFT JOIN result ON result.sid = students.sid ORDER BY name; # 返回结果: # +------+----------+-------+ # | sid | name | grade | # +------+----------+-------+ # | NULL | chenqi | NULL | # | 2 | lisi | 91 | # | 2 | lisi | 93 | # | 2 | lisi | 92 | # | 3 | wangwu | 82 | # | 3 | wangwu | 81 | # | 3 | wangwu | 83 | # | 1 | zhangsan | 101 | # | 1 | zhangsan | 103 | # | 1 | zhangsan | 102 | # | 4 | zhaoliu | 71 | # | 4 | zhaoliu | 73 | # | 4 | zhaoliu | 72 | # +------+----------+-------+ ``` #### 右连接 - RIGHT JOIN > 与 `LEFT JOIN` 相反,`RIGHT JOIN` 会读取右表的全部数据,不管左表有无对应数据,当左表没有数据时,以 `NULL` 代替 ```sql SELECT result.sid, name, grade FROM result RIGHT JOIN students ON result.sid = students.sid ORDER BY name; # 返回结果: # +------+----------+-------+ # | sid | name | grade | # +------+----------+-------+ # | NULL | chenqi | NULL | # | 2 | lisi | 91 | # | 2 | lisi | 93 | # | 2 | lisi | 92 | # | 3 | wangwu | 82 | # | 3 | wangwu | 81 | # | 3 | wangwu | 83 | # | 1 | zhangsan | 101 | # | 1 | zhangsan | 103 | # | 1 | zhangsan | 102 | # | 4 | zhaoliu | 71 | # | 4 | zhaoliu | 73 | # | 4 | zhaoliu | 72 | # +------+----------+-------+ ``` #### 表连接 + 聚集函数 + GROUP BY 计算每位学生的平均成绩 ```sql SELECT result.sid, name, AVG(grade) AS '总分平均成绩' FROM students LEFT JOIN result ON result.sid = students.sid GROUP BY name ORDER BY AVG(grade); # 返回结果: # +------+----------+--------------+ # | sid | name | 总分平均成绩 | # +------+----------+--------------+ # | NULL | chenqi | NULL | # | 4 | zhaoliu | 72 | # | 3 | wangwu | 82 | # | 2 | lisi | 92 | # | 1 | zhangsan | 102 | # +------+----------+--------------+ ``` ### 组合查询 - UNION > `UNION` 会合并执行多条 `SELECT` 语句,并把输出组合成一个查询结果集 > `UNION` 默认会从查询结果集中自动去除重复的数据 > 如果想在返回的数据中包括重复的行,可使用 `UNION ALL` > `UNION` 的组合查询可以应用不同的表 UNION 遵循规则: > 1. `UNION` 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 `UNION` 分隔 > 2. `UNION` 中的每个查询必须包含相同的字段、表达式或聚集函数(不过各个字段不需要以相同的顺序列出) > 3. 字段数据类型必须兼容:类型不必完全相同,但必须是`DBMS`(数据库管理系统)可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型) ```sql SELECT sid, grade FROM result WHERE grade < 90 UNION SELECT sid, grade FROM result WHERE sid = 2 ORDER BY grade; # 返回结果: # +-----+-------+ # | sid | grade | # +-----+-------+ # | 4 | 71 | # | 4 | 72 | # | 4 | 73 | # | 3 | 81 | # | 3 | 82 | # | 3 | 83 | # | 2 | 91 | # | 2 | 92 | # | 2 | 93 | # +-----+-------+ ```