数据类型
数值型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1字节 | (-128,127) | (0,255) | 小整数值 |
| SMALLINT | 2字节 | (-32 768,32 767) | (0,65 535) | 大整数值 |
| MEDIUMINT | 3字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
| BIGINT | 8字节 | (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4字节 | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度浮点数值 |
| DOUBLE | 8字节 | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
字符型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过255个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式长文本数据 |
| TEXT | 0-65 535字节 | 长文本字符串 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0- 4 294 967 295字节 | 二进制形式极大文本数据 |
| LONGTEXT | 0- 4 294 967 295字节 | 极大文本数据 |
说明
- 一个汉字占多少长度与编码有关
- UTF－8：一个汉字＝3个字节
- GBK：一个汉字＝2个字节
- VARCHAR(n) 表示最多可放入n个字符，字母汉子均可以，实际占用字节长度不同
select LENGTH(fieldname) from tablename检查长度
日期和时间类型
| 类型 | 大小 | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATA | 3字节 | 1000-01-01~9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3字节 | '-838:59:59'~'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1字节 | 1901~2155 | YYY | 年份值 |
| DATATIME | 8字节 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4字节 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
数据库和数据表管理
关键字
- CREATE 创建 库/表
- DROP 删除 库/表
- USE 切换数据库
DROP DATABASE test;CREATE DATABASE IF NOT EXISTS test;USE test;CREATE TABLE user( uid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20) NOT NULL)CHARACTER SET 'utf8';复制代码
数据表操作
添加删除字段
关键字
- ALTER 选中表
- DROP 删除字段
- ADD 添加字段
- MODIFY 修改字段属性
- CHANGE 修改字段名称 属性
- DESC 描述表结构
- NOT NULL 非空属性
- AUTO_INCREMENT 自增属性
- PRIMARY KEY 主键约束
- UNSIGNED 无符号位
- UNIQUE 唯一
- DEFAULT 默认值
- AFTER 位置在某字段之后
- FIRST 位置在第一位
- RENAME 重命名表
ALTER TABLE user ADD password VARCHAR(20) NOT NULL , ADD name VARCHAR(20) NOT NULL;DESC user;+----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+----------------+| uid | int(11) | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || name | varchar(20) | NO | | NULL | |+----------+-------------+------+-----+---------+----------------+ALTER TABLE user DROP name;DESC user;+----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+----------------+| uid | int(11) | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | |+----------+-------------+------+-----+---------+----------------+ALTER TABLE user ADD name VARCHAR(40) NOT NULL;DESC user;+----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+----------------+| uid | int(11) | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || name | varchar(40) | NO | | NULL | |+----------+-------------+------+-----+---------+----------------+ALTER TABLE user MODIFY uid MEDIUMINT UNSIGNED AUTO_INCREMENT;DESC user;+----------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+---------+----------------+| uid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || name | varchar(40) | NO | | NULL | |+----------+-----------------------+------+-----+---------+----------------+ALTER TABLE user CHANGE name email VARCHAR(50) NOT NULL UNIQUE DEFAULT '213@qq.com';DESC user;+----------+-----------------------+------+-----+------------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+------------+----------------+| uid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || email | varchar(50) | NO | UNI | 213@qq.com | |+----------+-----------------------+------+-----+------------+----------------+ALTER TABLE user ADD name VARCHAR(20) NOT NULL AFTER uid;DESC user;+----------+-----------------------+------+-----+------------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+------------+----------------+| uid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment || name | varchar(20) | NO | | NULL | || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || email | varchar(50) | NO | UNI | 213@qq.com | |+----------+-----------------------+------+-----+------------+----------------+复制代码
MODIFY/CHANGE 修改字段属性会清空除主键和唯一索引之外原来所有属性，而非单独修改某一个
ALTER TABLE user MODIFY email INT;DESC user;+----------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+---------+----------------+| uid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment || name | varchar(20) | NO | | NULL | || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || email | int(11) | YES | UNI | NULL | |+----------+-----------------------+------+-----+---------+----------------+复制代码
主键和唯一索引操作
如果主键有自增属性需要先使用MODIFY/CHANGE语句移除AUTO_INCREMENT
删除主键
ALTER TABLE user MODIFY uid MEDIUMINT UNSIGNED;ALTER TABLE user DROP PRIMARY KEY; +----------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+---------+-------+| uid | mediumint(8) unsigned | NO | | NULL | || name | varchar(20) | NO | | NULL | || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || email | int(11) | YES | UNI | NULL | |+----------+-----------------------+------+-----+---------+-------+复制代码
添加主键
ALTER TABLE user ADD PRIMARY KEY (uid);+----------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+---------+-------+| uid | mediumint(8) unsigned | NO | PRI | NULL | || name | varchar(20) | NO | | NULL | || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || email | int(11) | YES | UNI | NULL | |+----------+-----------------------+------+-----+---------+-------+复制代码
删除唯一索引
ALTER TABLE user DROP INDEX email;+----------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+---------+-------+| uid | mediumint(8) unsigned | NO | PRI | NULL | || name | varchar(20) | NO | | NULL | || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || email | int(11) | YES | | NULL | |+----------+-----------------------+------+-----+---------+-------+复制代码
添加唯一索引
ALTER TABLE user ADD UNIQUE KEY | INDEX 索引名称（字段名称） 索引名称可选，不输入索引名称时默认取字段名称
ALTER TABLE user ADD UNIQUE KEY (email);+----------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+---------+-------+| uid | mediumint(8) unsigned | NO | PRI | NULL | || name | varchar(20) | NO | | NULL | || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || email | int(11) | YES | UNI | NULL | |+----------+-----------------------+------+-----+---------+-------+复制代码
重命名表
- ALTER TABLE 原表名称 RENAME [TO/AS] 新表名称；
- RENAME 原表名称 TO 新表名称；
ALTER TABLE user RENAME user1;DESC user1;+----------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+---------+-------+| uid | mediumint(8) unsigned | NO | PRI | NULL | || name | varchar(20) | NO | | NULL | || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || email | int(11) | YES | UNI | NULL | |+----------+-----------------------+------+-----+---------+-------+RENAME TABLE user1 TO user;DESC user;+----------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-----------------------+------+-----+---------+-------+| uid | mediumint(8) unsigned | NO | PRI | NULL | || name | varchar(20) | NO | | NULL | || username | varchar(20) | NO | | NULL | || password | varchar(20) | NO | | NULL | || email | int(11) | YES | UNI | NULL | |+----------+-----------------------+------+-----+---------+-------+复制代码
数据操作
CREATE DATABASE IF NOT EXISTS test_crud DEFAULT CHARACTER SET "utf8";USE test_crud;CREATE TABLE IF NOT EXISTS user( id INT UNSIGNED AUTO_INCREMENT COMMENT '编号', username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名', age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄', email VARCHAR(20) UNIQUE NOT NULL DEFAULT 'xx@163.com' COMMENT '邮箱')ENGINE = INNODB CHARSET = UTF8;DESC user;+----------+---------------------+------+-----+------------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+---------------------+------+-----+------------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || username | varchar(20) | NO | UNI | NULL | || age | tinyint(3) unsigned | YES | | 18 | || email | varchar(20) | NO | UNI | xx@163.com | |+----------+---------------------+------+-----+------------+----------------+复制代码
添加记录
INSERT [INTO ] tbl_name[(col_name,...)]{VALUE|VALUSE}(VALUES...)
- 不指定字段名
需要按照建表时的字段顺序给每一个字段赋值
INSERT user VALUE(1,'ming',20,'123@qq.com');INSERT INTO user VALUE(NULL ,'ning',18,'ning@qq.com');SELECT * FROM user;+----+----------+------+-------------+| id | username | age | email |+----+----------+------+-------------+| 1 | ming | 20 | 123@qq.com || 2 | ning | 18 | ning@qq.com |+----+----------+------+-------------+复制代码
- 列出指定字段
INSERT user(username) VALUES ('hong');INSERT user(username,email) VALUES ('lan','lan@qq.com');SELECT * FROM user;+----+----------+------+-------------+| id | username | age | email |+----+----------+------+-------------+| 1 | ming | 20 | 123@qq.com || 2 | ning | 18 | ning@qq.com || 3 | hong | 18 | xx@163.com || 4 | lan | 18 | lan@qq.com |+----+----------+------+-------------+复制代码 - 一次插入多条记录
VALUS后跟多个括号
INSERT user (username,email) VALUES ('nick','nick@163.com' ),('pony','100@qq.com');SELECT * FROM user;+----+----------+------+--------------+| id | username | age | email |+----+----------+------+--------------+| 1 | ming | 20 | 123@qq.com || 2 | ning | 18 | ning@qq.com || 3 | hong | 18 | xx@163.com || 4 | lan | 18 | lan@qq.com || 5 | nick | 18 | nick@163.com || 6 | pony | 18 | 100@qq.com |+----+----------+------+--------------+复制代码 - INSERT SET 语句插入
INSERT user SET username='mike',email='mike@qq.com';SELECT * FROM user;+----+----------+------+--------------+| id | username | age | email |+----+----------+------+--------------+| 1 | ming | 20 | 123@qq.com || 2 | ning | 18 | ning@qq.com || 3 | hong | 18 | xx@163.com || 4 | lan | 18 | lan@qq.com || 5 | nick | 18 | nick@163.com || 6 | pony | 18 | 100@qq.com || 8 | mike | 18 | mike@qq.com |+----+----------+------+--------------+复制代码
- INSERT SELECT 把查询结果插入到指定数据表 可加LIMIT限制
-- 把user表中的id插入到addr表中的user_idINSERT addr(user_id) SELECT id FROM user;复制代码
修改记录
不添加条件会将表中所有数据都更新
UPDATE user SET age=16 WHERE email='xx@163.com';SELECT * FROM user;+----+----------+------+--------------+| id | username | age | email |+----+----------+------+--------------+| 1 | ming | 20 | 123@qq.com || 2 | ning | 18 | ning@qq.com || 3 | hong | 16 | xx@163.com || 4 | lan | 18 | lan@qq.com || 5 | nick | 18 | nick@163.com || 6 | pony | 18 | 100@qq.com || 8 | mike | 18 | mike@qq.com |+----+----------+------+--------------+复制代码
删除记录
不添加条件表中所有记录都会被删除，删除记录后 auto_increment 不会重置！！！
DELETE FROM user WHERE email='xx@163.com';SELECT * FROM user;+----+----------+------+--------------+| id | username | age | email |+----+----------+------+--------------+| 1 | ming | 20 | 123@qq.com || 2 | ning | 18 | ning@qq.com || 4 | lan | 18 | lan@qq.com || 5 | nick | 18 | nick@163.com || 6 | pony | 18 | 100@qq.com || 8 | mike | 18 | mike@qq.com |+----+----------+------+--------------+-- 删除全部记录，再插入新纪录 id不会重置DELETE FROM user ;Query OK, 6 rows affected (0.00 sec)SELECT * FROM user;Empty set (0.00 sec)INSERT user (username,email) VALUES ('nick','nick@163.com' ),('pony','100@qq.com');SELECT * FROM user;+----+----------+------+--------------+| id | username | age | email |+----+----------+------+--------------+| 9 | nick | 18 | nick@163.com || 10 | pony | 18 | 100@qq.com |+----+----------+------+--------------+-- SHOW CREATE TABLE tbl_name 可以得到当前AUTO_INCREMENT值SHOW CREATE TABLE user; user | CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号', `username` varchar(20) NOT NULL COMMENT '用户名', `age` tinyint(3) unsigned DEFAULT '18' COMMENT '年龄', `email` varchar(20) DEFAULT 'xx@163.com', PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), UNIQUE KEY `email` (`email`), UNIQUE KEY `email_2` (`email`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8-- 删除记录后可以通过ALTER TABLE tbl_name AUTO_INCREMENT=n指定当前的AUTO_INCREMENT值;-- 重置表（删除所有记录和重置AUTO_INCREMENT）TRUNCATE TABLE tbl_name;复制代码 查询记录
基本语法
-- 查询所有字段所有记录SELECT * FROM tbl_name;-- 当没有打开要查询的数据库时 可以用db_name.tbl_name(数据库名.表名)SELECT * FROM db_name.tbl_name;复制代码
条件删选
- = 等于
-
- <=> 等于(检测NULL)
- > 大于
- < 小于
- >= 大于等于
- <= 小于等于
- != 不等于
- <> 不等于
-- 查询name='zhangsan'的所有记录-- 对于等于NULL的值 -- 1. name<=>NULL-- 2. IS NULL || IS NOT NULLSELECT * FROM tbl_name WHERE name='zhangsan';SELECT * FROM tbl_name WHERE name<=>null;SELECT * FROM tbl_name WHERE name IS NULL;SELECT * FROM tbl_name WHERE name IS NOT NULL;复制代码
指定范围条件复制代码
- BETWEEN x AND y 在x-y之间
- NOT BETWEEN x AND y 不在x-y之间
SELECT * FROM tbl_name WHERE age BETWEEN 18 AND 26;SELECT * FROM tbl_name WHERE age NOT BETWEEN 18 AND 26;复制代码
指定集合复制代码
- IN(set) 在set集合内
- NOT IN(set) 不在set集合内
SELECT * FROM tbl_name WHERE age IN(18,29,30);SELECT * FROM tbl_name WHERE age NOT IN(18,29,30);复制代码
逻辑运算符
- AND 与
- OR 或
SELECT * FROM tbl_name WHERE age=18 AND sex='f';SELECT * FROM tbl_name WHERE age=18 OR sex='f';复制代码
模糊查询
- LIKE
- NOT LIT
- % 任意长度字符串
- _ 任意一个字符
-- 查询用户名中包含‘三’的记录。eg:'X三X','三X','X三'SELECT * FROM user WHERE username LIKE '%三%';-- 查询用户名长度为3的记录SELECT * FROM user WHERE username LIKE '___';复制代码
字段过滤
-- 查询name='zhangsan'的所有记录的id,emailSELECT id,email FROM tbl_name WHERE name='zhangsan';复制代码
字段起别名
-- 查询表中的id 编号,email 邮箱SELECT id AS '编号',email AS '邮箱' FROM tbl_name;复制代码
表起别名(用于多表查询)
-- 查询表中的id 编号,email 邮箱SELECT id ,email FROM tbl_name AS t1;复制代码
分组
- GROUP BY 把值相同的记录分在一个组中
-- 按照性别分组 -- 只会显示各组中一条记录SELECT id,username,sex FROM tbl_name GROUP BY sex;+----+-----------+-----+--------+| id | username | sex | addr |+----+-----------+-----+--------+| 2 | 刘嘉玲 | 女 | 山东 || 1 | 张三丰 | 男 | 北京 |+----+-----------+-----+--------+-- 高版本mysql(5.7+)报错 this is incompatible with sql_mode=only_full_group_by-- 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。-- 解决办法 my.cnf中，[mysqld]下增加如下命令sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'复制代码
- GROUP_CONCAT() 查看组中某个字段的详细信息
-- 按照性别分组 查询用户名有哪些SELECT GROUP_CONCAT(username),sex,id FROM tbl_name GROUP BY sex;+------------------------------------------------+-----+----+| GROUP_CONCAT(username) | sex | id |+------------------------------------------------+-----+----+| 刘嘉玲,杨幂,梁静茹 | 女 | 2 || 张三丰,黎明,林俊杰,苏见信,吴青峰 | 男 | 1 |+------------------------------------------------+-----+----+复制代码
- 聚合函数配合使用
- COUNT() 统计记录总数
- SUM() 求和
- MAX() 求最大值
- MIN() 求最小值
- AVG() 求平均值
-- 查询user表中总人数SELECT COUNT(*) AS total FROM `user`;-- COUNT(字段名) 如果该记录的该字段为NULL 则不统计在内 所以此处统计人数用COUNT(*)+-------+| total |+-------+| 8 |+-------+-- 查询user表中男女人数SELECT COUNT(*) AS total FROM `user` GROUP BY sex;+-------+| total |+-------+| 3 || 5 |+-------+-- 按照性别分组，得到用户详情，统计各组人数SELECT GROUP_CONCAT(username),COUNT(*) AS total_users FROM user GROUP BY sex;+------------------------------------------------+-------------+| GROUP_CONCAT(username) | total_users |+------------------------------------------------+-------------+| 刘嘉玲,杨幂,梁静茹 | 3 || 张三丰,黎明,林俊杰,苏见信,吴青峰 | 5 |+------------------------------------------------+-------------+-- 按照地址分组统计用户数，用户名，年龄总和，最大年龄，最小年龄，平均年龄SELECT addr,COUNT(*) AS total_users,GROUP_CONCAT(username),SUM(age) AS sum_age,MAX(age) AS max_age,MIN(age) AS min_age,AVG(age) AS avg_age FROM user GROUP BY addr;+--------------+-------------+------------------------+---------+---------+---------+---------+| addr | total_users | GROUP_CONCAT(username) | sum_age | max_age | min_age | avg_age |+--------------+-------------+------------------------+---------+---------+---------+---------+| 北京 | 2 | 张三丰,杨幂 | 134 | 100 | 34 | 67.0000 || 台湾 | 2 | 苏见信,吴青峰 | 65 | 35 | 30 | 32.5000 || 山东 | 1 | 刘嘉玲 | 56 | 56 | 56 | 56.0000 || 新加坡 | 1 | 林俊杰 | 32 | 32 | 32 | 32.0000 || 香港 | 1 | 黎明 | 55 | 55 | 55 | 55.0000 || 马来西亚 | 1 | 梁静茹 | 33 | 33 | 33 | 33.0000 |+--------------+-------------+------------------------+---------+---------+---------+---------+复制代码
- 配合 WITH ROLLUP 在记录末尾添加一条记录，是上面所有记录总和
SELECT addr,COUNT(*) AS total_users,GROUP_CONCAT(username),SUM(age) AS sum_age,MAX(age) AS max_age,MIN(age) AS min_age,AVG(age) AS avg_age FROM user GROUP BY addr WITH ROLLUP;+--------------+-------------+---------------------------------------------------------------------------+---------+---------+---------+---------+| addr | total_users | GROUP_CONCAT(username) | sum_age | max_age | min_age | avg_age |+--------------+-------------+---------------------------------------------------------------------------+---------+---------+---------+---------+| 北京 | 2 | 张三丰,杨幂 | 134 | 100 | 34 | 67.0000 || 台湾 | 2 | 苏见信,吴青峰 | 65 | 35 | 30 | 32.5000 || 山东 | 1 | 刘嘉玲 | 56 | 56 | 56 | 56.0000 || 新加坡 | 1 | 林俊杰 | 32 | 32 | 32 | 32.0000 || 香港 | 1 | 黎明 | 55 | 55 | 55 | 55.0000 || 马来西亚 | 1 | 梁静茹 | 33 | 33 | 33 | 33.0000 || NULL | 8 | 张三丰,杨幂,苏见信,吴青峰,刘嘉玲,林俊杰,黎明,梁静茹 | 375 | 100 | 30 | 46.8750 |+--------------+-------------+---------------------------------------------------------------------------+---------+---------+---------+---------+复制代码
- HAVING 子句对分组结果二次筛选
-- 按照addr分组，统计总人数SELECT addr,COUNT(*) AS total_user, GROUP_CONCAT(username) AS usernames FROM user GROUP BY addr;+--------------+------------+---------------------+| addr | total_user | usernames |+--------------+------------+---------------------+| 北京 | 2 | 张三丰,杨幂 || 台湾 | 2 | 苏见信,吴青峰 || 山东 | 1 | 刘嘉玲 || 新加坡 | 1 | 林俊杰 || 香港 | 1 | 黎明 || 马来西亚 | 1 | 梁静茹 |+--------------+------------+---------------------+-- 统计组中人数大于1SELECT addr,COUNT(*) AS total_user, GROUP_CONCAT(username) AS usernames FROM user GROUP BY addr HAVING COUNT(*)>1;+--------+------------+---------------------+| addr | total_user | usernames |+--------+------------+---------------------+| 北京 | 2 | 张三丰,杨幂 || 台湾 | 2 | 苏见信,吴青峰 |+--------+------------+---------------------+-- HAVING COUNT(*)>1 可以替换成HAVING total_user>1;复制代码
排序
- ORDER BY ASC|DESC
-- 按照id降序排列 SELECT * FROM user ORDER BY id DESC;+----+-----------+-----+--------------+-----+| id | username | sex | addr | age |+----+-----------+-----+--------------+-----+| 8 | 吴青峰 | 男 | 台湾 | 30 || 7 | 苏见信 | 男 | 台湾 | 35 || 6 | 梁静茹 | 女 | 马来西亚 | 33 || 5 | 林俊杰 | 男 | 新加坡 | 32 || 4 | 黎明 | 男 | 香港 | 55 || 3 | 杨幂 | 女 | 北京 | 34 || 2 | 刘嘉玲 | 女 | 山东 | 56 || 1 | 张三丰 | 男 | 北京 | 100 |+----+-----------+-----+--------------+-----+-- 按照id升序排列 默认(ASC)SELECT * FROM user ORDER BY id;+----+-----------+-----+--------------+-----+| id | username | sex | addr | age |+----+-----------+-----+--------------+-----+| 1 | 张三丰 | 男 | 北京 | 100 || 2 | 刘嘉玲 | 女 | 山东 | 56 || 3 | 杨幂 | 女 | 北京 | 34 || 4 | 黎明 | 男 | 香港 | 55 || 5 | 林俊杰 | 男 | 新加坡 | 32 || 6 | 梁静茹 | 女 | 马来西亚 | 33 || 7 | 苏见信 | 男 | 台湾 | 35 || 8 | 吴青峰 | 男 | 台湾 | 30 |+----+-----------+-----+--------------+-----+-- 按照age升序，id升序排列 当优先按age排序，当age出现相同值时按id排序SELECT * FROM user ORDER BY age,id;+----+-----------+-----+--------------+-----+| id | username | sex | addr | age |+----+-----------+-----+--------------+-----+| 8 | 吴青峰 | 男 | 台湾 | 30 || 5 | 林俊杰 | 男 | 新加坡 | 32 || 6 | 梁静茹 | 女 | 马来西亚 | 33 || 3 | 杨幂 | 女 | 北京 | 34 || 7 | 苏见信 | 男 | 台湾 | 35 || 4 | 黎明 | 男 | 香港 | 55 || 2 | 刘嘉玲 | 女 | 山东 | 56 || 1 | 张三丰 | 男 | 北京 | 100 |+----+-----------+-----+--------------+-----+复制代码
- 打乱记录顺序
SELECT * FROM user ORDER BY RAND(); +----+-----------+-----+--------------+-----+| id | username | sex | addr | age |+----+-----------+-----+--------------+-----+| 1 | 张三丰 | 男 | 北京 | 100 || 7 | 苏见信 | 男 | 台湾 | 35 || 6 | 梁静茹 | 女 | 马来西亚 | 33 || 4 | 黎明 | 男 | 香港 | 55 || 8 | 吴青峰 | 男 | 台湾 | 30 || 2 | 刘嘉玲 | 女 | 山东 | 56 || 3 | 杨幂 | 女 | 北京 | 34 || 5 | 林俊杰 | 男 | 新加坡 | 32 |+----+-----------+-----+--------------+-----+复制代码
LIMIT 限制结果集显示条数
- LIMIT 显示结果集的前几条
- LIMIT offset,row_count 从offset开始，显示row_count条数据
SELECT * FROM user LIMIT 5;+----+-----------+-----+-----------+-----+| id | username | sex | addr | age |+----+-----------+-----+-----------+-----+| 1 | 张三丰 | 男 | 北京 | 100 || 2 | 刘嘉玲 | 女 | 山东 | 56 || 3 | 杨幂 | 女 | 北京 | 34 || 4 | 黎明 | 男 | 香港 | 55 || 5 | 林俊杰 | 男 | 新加坡 | 32 |+----+-----------+-----+-----------+-----+-- 可以用于做分页SELECT * FROM user LIMIT 3,5;+----+-----------+-----+--------------+-----+| id | username | sex | addr | age |+----+-----------+-----+--------------+-----+| 4 | 黎明 | 男 | 香港 | 55 || 5 | 林俊杰 | 男 | 新加坡 | 32 || 6 | 梁静茹 | 女 | 马来西亚 | 33 || 7 | 苏见信 | 男 | 台湾 | 35 || 8 | 吴青峰 | 男 | 台湾 | 30 |+----+-----------+-----+--------------+-----+复制代码