数据类型分类
整数型，浮点型(float和double)，定点数，字符串(char,varchar,text)， 日期时间类型，修饰符
字符串(char,varchar,text)
char(n) 固定长度，最多255个字符,注意不是字节
varchar(n) 可变长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符
串末尾不能有空格，varchar不限于此
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1
个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3.char类型的字符串检索速度要比varchar类型的快
varchar 和 text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text 是实际字符数+2个字节。
2.text类型不能有默认值
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text
二进制数据BLOB
BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，
不分大小写
BLOB存储的数据只能整体读出
TEXT可以指定字符集，BLOB不用指定字符集
日期时间类型
date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp 此字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间
修饰符
适用所有类型的修饰符：
NULL 数据列可包含NULL值，默认值
NOT NULL 数据列不允许包含NULL值，相当于网站注册表中的 * 为必填选项
DEFAULT 默认值
PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULL
UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET name 指定一个字符集
适用数值型的修饰符：
AUTO_INCREMENT 自动递增，适用于整数类型, 必须作用于某个 key 的字段,比如primary key UNSIGNED 无符号
范例：关于AUTO_INCREMENT
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.02 sec)
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test
Database changed
mysql> create table t1 (id int unsigned auto_increment primary key) auto_increment = 4294967294;
Query OK, 0 rows affected (0.03 sec)
#创建db1
mysql> create database db1 character set utf8 collate utf8_bin;
Query OK, 1 row affected, 2 warnings (0.01 sec)
#修改db1
mysql> alter database db1 character set utf8 collate utf8_b
bin;
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> show table status from db1 like "t1" \G
Empty set (0.00 sec)
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1;
+------------+
| id |
+------------+
| 4294967294 |
+------------+
1 row in set (0.00 sec)
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------------+
| id |
+------------+
| 4294967294 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)
mysql> insert into t1 values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't1.PRIMARY'
MariaDB [testdb]> insert t1 value(null);
ERROR 167 (22003): Out of range value for column 'id' at row 1
#上面表的数据类型无法存放所有数据,修改过数据类型实现
mysql> alter table t1 modify id bigint auto_increment;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
+-------+--------+------+-----+---------+----------------+
1 row in set (0.00 sec)
mysql> insert t1 values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------------+
| id |
+------------+
| 4294967294 |
| 4294967295 |
| 4294967296 |
+------------+
3 rows in set (0.00 sec)
DDL 语句
创建表
创建表的方法
mysql> create table student(
id int unsigned auto_increment primary key,
name varchar(20) not null,
age tinyint unsigned,
#height DECIMAL(5,2),
gender enum ('M','F')default 'M'
)engine=innodb auto_increment=10 default charset=utf8;
#id字段以10初始值
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert student (name,age)values('xiaoming',20);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 10 | xiaoming | 20 | M |
+----+----------+------+--------+
1 row in set (0.00 sec)
mysql> insert student (name,age,gender)values('xiaolv',18,'F');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 10 | xiaoming | 20 | M |
| 11 | xiaolv | 18 | F |
+----+----------+------+--------+
2 rows in set (0.00 sec)
#创建表的格式
CREATE TABLE employee (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age
tinyint UNSIGNED,PRIMARY KEY(id,name));
范例：auto_increment 属性
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.01 sec)
mysql> set @@auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 3 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> create table autoinc1 (col int not null auto_increment primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into autoinc1 values (null),(null),(null),(null);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select col from autoinc1;
+-----+
| col |
+-----+
| 3 |
| 13 |
| 23 |
| 33 |
+-----+
4 rows in set (0.00 sec)
范例：时间类型
#创建test数据库
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
#使用test数据库
mysql> use test
Database changed
#创建testdate表
mysql> create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)
#插入数据
mysql> insert testdate values(),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
#查看表
mysql> select * from testdate
-> ;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2022-11-08 14:42:03 |
| 2 | 2022-11-08 14:42:03 |
| 3 | 2022-11-08 14:42:03 |
+----+---------------------+
3 rows in set (0.00 sec)
#修改其它字段,会自动更新timestamp字段
mysql> select * from testdate
-> ;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2022-11-08 14:42:03 |
| 2 | 2022-11-08 14:42:03 |
| 3 | 2022-11-08 14:42:03 |
+----+---------------------+
3 rows in set (0.00 sec)
mysql> update testdate set id=5 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2022-11-08 14:42:03 |
| 2 | 2022-11-08 14:42:03 |
| 5 | 2022-11-08 14:46:06 |
+----+---------------------+
3 rows in set (0.00 sec)
MariaDB [testdb1]> create table testdate (id int auto_increment primary key,date
timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);
MariaDB [testdb1]> insert testdate ()values()()();
MariaDB [testdb1]> select * from testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2020-09-23 08:41:43 |
| 2 | 2020-09-23 08:41:43 |
| 3 | 2020-09-23 08:41:43 |
+----+---------------------+
3 row in set (0.000 sec)
(2) 通过查询现存表创建；新表会被直接插入查询而来的数据
MariaDB [db1]> create table user select user,host,password from mysql.user;
Query OK, 4 rows affected (0.008 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student |
| user |
+---------------+
2 rows in set (0.000 sec)
MariaDB [db1]> desc user;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user | char(80) | NO | | | |
| host | char(60) | NO | | | |
| password | char(41) | NO | | | |
+----------+----------+------+-----+---------+-------+
(3) 通过复制现存的表的表结构创建，但不复制数据
MariaDB [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
#创建像学生表的老师表
MariaDB [db1]> create table teacher like student;
Query OK, 0 rows affected (0.006 sec)
MariaDB [db1]> desc teacher;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
范例: 创建外键表
#创建外键首先要排除同表有相同的值，如有则需要新建换个表
#换表
mysql> use db1
Database changed
#创建一个学校的表
mysql> create table school ( id int primary key auto_increment,name varchar(10));
Query OK, 0 rows affected (0.05 sec)
#创建一个学校一样的老师外键表
mysql> create table teacher ( id int primary key auto_incremenment,name varchar(10),school_id int,foreign key(school_id)references school(id));
Query OK, 0 rows affected (0.04 sec)
#对文件内容降序排序
mysql> desc school;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
#对文件内容降序排序
mysql> desc teacher;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| school_id | int | YES | MUL | NULL | |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#查看
mysql> show create table teacher;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb3_bin DEFAULT NULL,
`school_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `school_id` (`school_id`),
CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#对文件插入多条数据
mysql> insert school values(0,'wang'),(0,'edu');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
#筛选和学校有关的所有信息
mysql> select * from school;
+----+------+
| id | name |
+----+------+
| 1 | wang |
| 2 | edu |
+----+------+
2 rows in set (0.00 sec)
#对文件插入多条数据
mysql> insert teacher values(0,xiaoming,1);
ERROR 1054 (42S22): Unknown column 'xiaoming' in 'field list'
mysql> insert teacher values(0,'xiaoming',1);
Query OK, 1 row affected (0.01 sec)
#对文件插入多条数据
mysql> insert teacher values(0,'xiaohong',2);
Query OK, 1 row affected (0.01 sec)
#筛选和老师有关的所有信息
mysql> select * from teacher;
+----+----------+-----------+
| id | name | school_id |
+----+----------+-----------+
| 1 | xiaoming | 1 |
| 2 | xiaohong | 2 |
+----+----------+-----------+
2 rows in set (0.00 sec)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`))
mysql>
表查看
desc 查看表结构 show tables 查看表
查看表：
SHOW TABLES [FROM db_name]
查看表创建命令：
DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name
查看当前库里的所有表或指定表的状态：
SHOW TABLE STATUS [LIKE 'tbl_name']
查看支持的engine类型
SHOW ENGINES;
查看库中所有表状态
SHOW TABLE STATUS FROM db_name
#查看整个表的内容
mysql> show table status like 'student'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 12
Create_time: 2022-11-08 11:29:43
Update_time: 2022-11-08 11:43:47
Check_time: NULL
Collation: utf8mb3_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
#查看整个库的内容
mysql> SHOW TABLE STATUS FROM test\G
修改和删除表
修改表
ALTER TABLE 'tbl_name'
#字段：
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）
查看修改表帮助
Help ALTER TABLE
删除表
DROP TABLE [IF EXISTS] 'tbl_name';
修改表范例
#修改表名
ALTER TABLE students RENAME s1;
#添加字段
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
#修改字段类型
ALTER TABLE s1 MODIFY phone int;
#修改字段名称和类型
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
#删除字段
ALTER TABLE s1 DROP COLUMN mobile;
#修改字符集
ALTER TABLE s1 character set utf8;
#修改数据类型和字符集
ALTER TABLE s1 change name name varchar(20) character set utf8;
#添加字段
ALTER TABLE students ADD gender ENUM('m','f');
alter table student modify is_del bool default false;
#修改字段名和类型
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
#删除字段
ALTER TABLE students DROP age;
#查看表结构
DESC students;
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;
#添加外键
ALTER TABLE students add foreign key(TeacherID) references teachers(tid);
#删除外键
SHOW CREATE TABLE students #查看外键名
ALTER TABLE students drop foreign key <外键名>;
DML 语句
范例: 全值插入
mysql> insert student values(0,'wang',18,default);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+--------+
| id | name | age | gender |
+----+------+------+--------+
| 1 | wang | 18 | M |
+----+------+------+--------+
1 row in set (0.00 sec)
范例: 部分列插入
mysql> insert student(name,age)values('zhang',20);
Query OK, 1 row affected (0.01 sec)
mysql> insert student(id,name,age)values(default,'li',19);
Query OK, 1 row affected (0.00 sec)
mysql> insert student(id,name,gender)values(null,'zhao','F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | wang | 18 | M |
| 2 | zhang | 20 | M |
| 3 | li | 19 | M |
| 4 | zhao | NULL | F |
+----+-------+------+--------+
4 rows in set (0.00 sec)UPDATE 语句
注意：一定要有限制条件，否则将修改所有行的指定字
可利用mysql 选项避免此错误：
mysql -U | --safe-updates| --i-am-a-dummy
[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates
DELETE 语句
删除表中数据，但不会自动缩减数据文件的大小。
注意：一定要有限制条件，否则将清空表中的所有数据
如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。
TRUNCATE TABLE tbl_name;
缩减表大小
OPTIMIZE TABLE tb_name
范例: 删除数据可以使用逻辑删除，添加一个标识字段实现,删除数据即修改标识字段
mysql> alter table student add is_del bool default false;
#mysql> alter table student add is_del tinyint(1) default 0;
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
| is_del | tinyint(1) | YES | | 0 | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
DQL 语句
单表操作
范例：简单查询
DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE 't%'
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students
select * from students where classid in (1,3,5);
select * from students where classid not in (1,3,5);
范例： 记录去重
MariaDB [hellodb]> select distinct gender from students ;
+--------+
| gender |
+--------+
| M |
| F |
+--------+
2 rows in set (0.001 sec)
#将age和gender多个字段重复的记录去重
mysql> select distinct age,gender from students;
范例：SQL 注入入侵
create table user (id int auto_increment primary key,name varchar(20),password
varchar(30));
insert into user (name,password) values('admin','123456'),('wang','654321');
select * from user where name='admin' and password='' or '1'='1';
select * from user where name='admin' and password='' or '1=1';
select * from user where name='admin'; -- ' and password='wang123';
select * from user where name='admin'; # ' and password='wang123';
范例: 分页查询
#只取前3个
mysql> select * from students limit 0,3;
mysql> select * from students limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from students limit 1,3;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
# 查询第n页的数据,每页显示m条记录
mysql> select * from students limit (n-1) * m,m;
范例: 聚合函数
mysql> select sum(age)/count(*) from students where gender
= 'M';
+-------------------+
| sum(age)/count(*) |
+-------------------+
| 33.0000 |
+-------------------+
1 row in set (0.01 sec)
mysql> select sum(age)/count(*) from students where gender = 'F';
+-------------------+
| sum(age)/count(*) |
+-------------------+
| 19.0000 |
+-------------------+
1 row in set (0.00 sec)
范例：分组统计
mysql> select classid, count(*) 数量 from students group by classid;
+---------+--------+
| classid | 数量 |
+---------+--------+
| 2 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 5 | 1 |
| 7 | 3 |
| 6 | 4 |
| NULL | 2 |
+---------+--------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,gender, count(*) 数量 from students group by
classid,gender;
+---------+--------+--------+
| classid | gender | 数量 |
+---------+--------+--------+
| NULL | M | 2 |
| 1 | F | 2 |
| 1 | M | 2 |
| 2 | M | 3 |
| 3 | F | 3 |
| 3 | M | 1 |
| 4 | M | 4 |
| 5 | M | 1 |
| 6 | F | 3 |
| 6 | M | 1 |
| 7 | F | 2 |
| 7 | M | 1 |
+---------+--------+--------+
12 rows in set (0.001 sec)
#分组统计
select classid,avg(age) as 平均年龄 from students where classid > 3 group by
classid having 平均年龄 >30 ;
select gender,avg(age) 平均年龄 from students group by gender having gender='M';
#多个字段分组统计
select classid,gender,count(*) 数量 from students group by classid,gender;
select classid,gender,count(*) 数量 from students group by gender,classid;
范例: group_concat函数实现分组信息的集合
mysql> select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------------------
---------------------------------------------------------------------------------
+
| gender | group_concat(name)
|
+--------+-----------------------------------------------------------------------
---------------------------------------------------------------------------------
+
| F | Xiao Qiao,Huang Yueying,Xi Ren,Lin Daiyu,Ren Yingying,Yue
Lingshan,Diao Chan,Wen Qingqing,Xue Baochai,Lu Wushuang
|
| M | Tian Boguang,Sun Dasheng,Xu Xian,Ma Chao,Hua Rong,Lin Chong,Xu
Zhu,Duan Yu,Shi Zhongyu,Yuan Chengzhi,Shi Qing,Yu Yutong,Ding Dian,Xie Yanke,Shi
Potian |
+--------+-----------------------------------------------------------------------
---------------------------------------------------------------------------------
+
2 rows in set (0.00 sec)
# with rollup 分组后聚合函数统计后再做汇总
mysql> select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| F | 10 |
| M | 15 |
| NULL | 25 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select gender,group_concat(name) from students group by gender with
rollup;
范例: 分组统计
#注意:一旦使用分组group by,在select 后面的只能采用分组的列和聚合函数,其它的列不能放在select后面,否则根据系统变量SQL_MODE的值不同而不同的结果
#以下为MySQL8.0.17 的执行结果
mysql> use hellodb
mysql> select classid,count(*) 数量 from students group by classid;
+---------+--------+
| classid | 数量 |
+---------+--------+
| 2 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 5 | 1 |
| 7 | 3 |
| 6 | 4 |
| NULL | 2 |
+---------+--------+
8 rows in set (0.00 sec)
mysql> select @@sql_mode;
+--------------------------------------------------------------------------------
---------------------------------------+
| @@sql_mode
|
+--------------------------------------------------------------------------------
---------------------------------------+
|
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DI
VISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------------------------------------------
---------------------------------------+
1 row in set (0.00 sec)
#以下是Mariadb10.3.17的执行结果
MariaDB [hellodb]> select classid, count(*), stuid from students group by
classid;
+---------+----------+-------+
| classid | count(*) | stuid |
+---------+----------+-------+
| NULL | 2 | 24 |
| 1 | 4 | 2 |
| 2 | 3 | 1 |
| 3 | 4 | 5 |
| 4 | 4 | 4 |
| 5 | 1 | 6 |
| 6 | 4 | 9 |
| 7 | 3 | 8 |
+---------+----------+-------+
8 rows in set (0.001 sec)
MariaDB [hellodb]> select @@sql_mode;
+--------------------------------------------------------------------------------
-----------+
| @@sql_mode
|
+--------------------------------------------------------------------------------
-----------+
范例: 排序
#只取前3个
mysql> select * from students order by age desc limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
#跳过前3个只显示后续的2个
mysql> select * from students order by age desc limit 3,2;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+--------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
select classid,sum(age) from students where classid is not null group by
classid order by classid;
select classid,sum(age) from students group by classid having classid is not
null order by classid;
select classid,sum(age) from students where classid is not null group by
classid order by classid limit 2,3;
#必须先过滤，再排序
select * from students where classid is not null order by gender desc, age asc ;
#多列排序
select * from students order by gender desc, age asc;
范例：正序排序时将NULL记录排在最后
#对classid 正序排序，NULL记录排在最后
select * from students order by -classid desc ;
#分组后再排序
MariaDB [hellodb]> select gender,classid,avg(age) from students where classid is
not null group by gender,classid order by gender,classid;
+--------+---------+----------+
| gender | classid | avg(age) |
+--------+---------+----------+
| F | 1 | 19.0000 |
| F | 3 | 18.3333 |
| F | 6 | 20.0000 |
| F | 7 | 18.0000 |
| F | 77 | 18.0000 |
| F | 93 | 18.0000 |
| M | 1 | 21.5000 |
| M | 2 | 35.2000 |
| M | 3 | 23.0000 |
| M | 4 | 23.6000 |
| M | 5 | 46.0000 |
| M | 6 | 23.0000 |
| M | 7 | 23.0000 |
| M | 94 | 18.0000 |
+--------+---------+----------+
14 rows in set (0.001 sec)
#有年龄的正序排列前10个
MariaDB [hellodb]> select * from students order by age limit 10;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 33 | Miejue Shitai | 18 | F | 77 | NULL |
| 32 | Zhang Sanfeng | 18 | M | 94 | NULL |
| 27 | liudehua | 18 | F | 1 | NULL |
| 34 | Lin Chaoying | 18 | F | 93 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.001 sec)
#由不同的年龄排序取前三个
MariaDB [hellodb]> select distinct age from students order by age limit 3 ;
+-----+
| age |
+-----+
| 17 |
| 18 |
| 19 |
+-----+
3 rows in set (0.001 sec)
MariaDB [hellodb]> select distinct age from students order by age limit 3,5 ;
+-----+
| age |
+-----+
| 20 |
| 21 |
| 22 |
| 23 |
| 25 |
+-----+
5 rows in set (0.001 sec)
范例: 分组和排序的次序
#顺序: group by,having,order by
mysql> select classid,count(*) from students group by classid having classid is
not null order by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+----------+
7 rows in set (0.00 sec)
以下排序会造成错误排序
#以下顺序会出错,group by,order by,having
mysql> select classid,count(*) from students group by classid order by classid
having classid is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'having classid is not null' at line 1
#以下顺序会出错,order by,group by,having
mysql> select classid,count(*) from students order by classid group by classid
having classid is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'group
by classid having classid is not null' at line 1
范例：时间字段进行过滤查询,并且timestamp可以随其它字段的更新自动更新
MariaDB [testdb]> create table testdate (id int auto_increment primary key,date
timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
MariaDB [testdb]> insert testdate () values();
MariaDB [testdb]> insert testdate values(),(),();
MariaDB [testdb]> select * from testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2020-06-03 15:21:03 |
| 2 | 2020-06-03 15:21:12 |
| 3 | 2020-06-03 15:21:14 |
| 4 | 2020-06-03 15:21:17 |
| 5 | 2020-06-03 18:27:39 |
| 6 | 2020-06-03 18:27:44 |
+----+---------------------+
6 rows in set (0.001 sec)
MariaDB [testdb]> select * from testdate where date between '2020-06-03
15:21:12' and '2020-06-03 18:27:40';
+----+---------------------+
| id | date |
+----+---------------------+
| 2 | 2020-06-03 15:21:12 |
| 3 | 2020-06-03 15:21:14 |
| 4 | 2020-06-03 15:21:17 |
| 5 | 2020-06-03 18:27:39 |
+----+---------------------+
4 rows in set (0.000 sec)
MariaDB [testdb]> select * from testdate where date >= '2020-06-03 15:21:12'
and date <= '2020-06-03 18:27:40';
+----+---------------------+
| id | date |
+----+---------------------+
| 2 | 2020-06-03 15:21:12 |
| 3 | 2020-06-03 15:21:14 |
| 4 | 2020-06-03 15:21:17 |
| 5 | 2020-06-03 18:27:39 |
+----+---------------------+
4 rows in set (0.001 sec)
#修改其它字段,会自动更新timestamp字段
mysql> update testdate set id=10 where id=1;
mysql> select * from testdate3;
+----+---------------------+
| id | date |
+----+---------------------+
| 2 | 2020-06-03 15:21:12 |
| 3 | 2020-06-03 15:21:14 |
| 4 | 2020-06-03 15:21:17 |
| 5 | 2020-06-03 18:27:39 |
| 6 | 2020-06-03 18:27:44 |
| 10 | 2020-06-03 18:34:51 |
+----+---------------------+
6 rows in set (0.001 sec)