✍、目录
目录
- ✍、目录
- 1、DDL
- 1.1、对数据库的常用操作
- 1.2、对表结构的常用操作
- 1.2.1、创建表
- 1.2.2、数据类型
- 1、数值类型
- 2、字符串类型
- 3、日期类型
- 1.2.3、其他操作
- 1.3、修改表结构
- 1.3.1、添加列
- 1.3.2、修改列名和类型
- 1.3.3、删除列
- 1.3.4、修改表名
- 2、DML
- 2.1、数据插入
- 2.2、数据修改
- 2.3、数据删除
- 3、约束
- 3.1、主键约束
- 3.1.1、添加单列主键
- 3.1.2、添加多列主键(联合主键)
- 3.1.3、通过修改表结构添加主键
- 3.1.4、删除主键约束
- 3.2、自增长约束
- 3.2.1、指定自增字段初始值
- 3.3、非空约束
- 3.3.1、删除非空约束
- 3.4、唯一约束
- 3.4.1、删除唯一约束
- 3.5、默认约束
- 3.5.1、删除默认约束
- 3.6、零填充约束
- 3.6.1、删除零填充约束
- 4、DQL
- 4.1、基本查询
- 4.2、运算符
- 4.2.1、算术运算符
- 4.2.2、比较运算符
- 4.2.3、逻辑运算符
- 4.2.4、位运算符
- 4.3、排序查询
- 4.4、聚合查询
- 4.4.1、null值处理
- 4.5、分组查询
- 4.5.1、having
- 4.6、分页查询
- 4.7、insert into select
- 4.8、select into from
- 5、DQL语句练习一
- 6、DQL语句练习二
- 7、DQL正则表达式
名称 | 解释 | 命令 |
DDL(数据定义语言) | 定义和管理数据对象，如数据库，数据表等 | create、drop、alter |
DML(数据操作语言) | 用于操作数据库对象中所包含的数据 | insert、update、delete |
DQL(数据查询语言) | 用于查询数据库数据 | select |
DCL(数据控制语言) | 用于管理数据库的语言，包括管理权限及数据更改 | grant、commit、rollback |
1、DDL
DDL：Data Definition Language，数据定义语言，定义和管理数据对象，如数据库，数据表等
1.1、对数据库的常用操作
功能 | SQL |
查看所有的数据库 | show databases; |
创建数据库 | create database [if not exists] 数据库名; |
使用数据库 | use 数据库 |
删除数据库 | drop database [if exists] 数据库名; |
修改数据库编码 | alter database 数据库名 character set utf8 |
使用SQLyog创建数据库时，需要选择
- 基字符集：
utf8 - 数据库排序规则：
utf8_unicode_ci
1.2、对表结构的常用操作
1.2.1、创建表
创建表是构建一张空表，指定这个表的名字，这个表有几列，每一列叫什么名字，以及每一列存储的数据类型。
中括号[]内的内容可写可不写，尖括号<>内的必须写
create table [if not exists] 表名(
字段名1 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];例如：
- 创建一个school数据库
- 创建学生表
- 学号int
- 姓名varchar
- 密码varchar
- 性别varchar
- 出生日期datetime
- 家庭住址varchar
- 邮箱varchar
-- 创建数据库 school
CREATE DATABASE IF NOT EXISTS `school`;
-- 删除数据库 student
DROP DATABASE IF EXISTS `student`;
-- 注意点:表的名称和字段尽量使用 `` 括起来
-- 创建学生表
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '主键', -- id 不为空，自增
`name` VARCHAR(30) NOT NULL DEFAULT '林晓' COMMENT '姓名', -- name 不为空，默认为林晓
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', -- pwd 不为空，默认为123456
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别', -- sex 不为空，默认为男
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期', -- birthdat 默认为空
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址', -- address 默认为空
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', -- email默认为空
PRIMARY KEY (`id`) -- 主键，一般一个表只有一个唯一的主键
)ENGINE=INNODB DEFAULT CHARSET=utf8;1.2.2、数据类型
数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来，使用数据类型的原则是:够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。
1、数值类型
类型 | 用途 | 存储需求 |
tinyint | 小整数值 | 1 byte |
smallint | 大整数值 | 2 bytes |
mediumint | 大整数值 | 3 bytes |
int / integer | 大整数值 | 4 bytes |
bigint | 极大整数值 | 8 bytes |
float | 单精度浮点数值 | 4 bytes |
double | 双精度浮点数值 | 8 bytes |
decimal | 小数值 | 金融计算的时候，一般是使用decimal |
其中的decimal有以下用法
decimal（5，2）- 其中 5 为最多可以存储的十进制位数为10位，是小数点前后的位数总和
- 2表示小数点后面的位数
2、字符串类型
类型 | 用途 | 大小 |
char | 定长字符串 | 0-255 bytes |
varchar | 变长字符串 | 0-65535 bytes |
tinyblob | 不超过255个字符的二进制字符串 | 0-255 bytes |
tinytext | 短文本字符串 | 0-255 bytes |
blob | 二进制形式的长文本数据 | 0-65535 bytes |
text | 长文本数据 | 0-65535 bytes |
mediumblob | 二进制形式的中等长度文本数据 | |
mediumtext | 中等长度文本数据 | |
longblob | 二进制形式的极大文本数据 |
3、日期类型
类型 | 格式 | 用途 |
date | YYYY-MM-DD | 日期值 |
time | HH:MM:SS | 时间值或持续时间 |
year | YYYY | 年份值 |
datetime | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
timestamp | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
1.2.3、其他操作
功能 | SQL |
查看当前数据库的所有表名称 | show tables; |
查看指定某个表的创建语句 | show create table 表名; |
查看表结构 | desc 表名 |
删除表 | drop table 表名 |
1.3、修改表结构
1.3.1、添加列
语法：
alter table 表名 add 列名 类型(长度)[约束]例如：为 student 表添加一个新的字段为：系别 dept 类型为 varchar(20)
alter table student add `dept` varchar(20)1.3.2、修改列名和类型
语法：
alter table 表名 change 旧列名 新列名 类型(长度) 约束;例如：为 student 表的 dept 字段更换为 department varchar(30)
alter table student change `dept` department varchar(30)1.3.3、删除列
语法：
alter table 表名 drop 列名;例子： 删除 student 表中的 department 列
alter table student drop department;1.3.4、修改表名
语法：
rename table 表名 to 新表名例子： 将表 student 改名为 stu
rename table student to stu2、DML
DML: Data Manipulation Language,数据操作语言，用来对数据库中表的数据记录进行更新。
2.1、数据插入
语法：
-- 向表中插入某些列
insert into 表(列名1,列名2,列名3...) values ('值1','值2','值3'...)
-- 向表中插入所有列
insert into 表 values(值1,值2,值3....)- 一般写插入语句，我们一定要数据和字段一一对应！
- 字段是可以省略的，但是后面的值必须要一一对应
例子：
insert into student(sid,name,gender,age,birth,address,score) values(1001,'男',18,'2001-08-09','杭州',90)
insert into student values(1001,'男',18,'2001-08-09','杭州',90)2.2、数据修改
语法：
update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值....where 条件;- 条件，筛选的条件，如果没有指定，则会修改所有的列
- value ，是一个具体的值，也可以是一个变量
- 多个设置的属性之间，使用英文逗号隔开
例子：
-- 将所有学生的地址改为成都
update student set address = '成都'
-- 将 id 为 1004 的学生的地址改为北京
update student set address = '北京' where id = 1004
-- 将 id 为 1005 的学生的地址修改为北京,成绩修改为100
update student set address = '北京',score = 100 where id = 1005
update `student` set `name`='狂神' where id = 1;
-- 不指定条件的情况下，会改动所有表！
update `student` set `name`='长江7号';
-- 修改多个属性
update `student` set `name`='狂神',`email`='12123@qq.com' where id between 2 and 5;
-- 通过多个条件定位数据
update `student` set `name`='长江7号' where `name`='狂神' and `sex`='男';操作符 | 含义 | 范围 | 结果 |
= | 等于 | 5=6 | false |
<>或 != | 不等于 | 5<>6 | true |
> | 大于 | ||
< | 小于 | ||
<= | 小于等于 | ||
>= | 大于等于 | ||
between…and… | 在某个范围内 | [2,5] | |
and | && | 5>1 and 1>2 | false |
or | || | 5>1or 1>2 | true |
2.3、数据删除
语法：
delete from 表名 [where 条件]
truncate table 表名
-- 或者
truncate 表名例如：
-- 删除sid为1004的学生数据
delete from student where sid = 1004;
-- 删除表中所有数据
delete from student;
-- 清空表数据
truncate table student;
truncate student;注意：delete 和 truncate 原理不同,delete只是删除内容,而 truncate 类似于 drop table,可以理解为事将整个表删除,然后再创建该表
delete 和 truncate 区别
- 相同点: 都能删除数据，都不会删除表结构，但truncate速度更快
- 不同点：
- truncate 重新设置 自增列，计数器会归零
- truncate 不会影响事务
3、约束
约束constraint实际上就是表中数据的限制条件。
作用：表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。
约束的分类
- 主键约束 primary key
- 自增长约束 auto_increment
- 非空约束 not null
- 唯一性约束 unique
- 默认约束 default
- 零填充约束 zerofill
- 外键约束 foreign key
3.1、主键约束
- MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。
- 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
- 每个表最多只允许一个主键
- 主键的关键字是：
primary key - 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
3.1.1、添加单列主键
创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键
方式一语法：
-- 在 create table 语句中,通过 PRIMARY KEY 关键字来指定逐渐
create table 表名(
<字段名> <数据类型> primary key
)方式二实现：
create table empl(
`id` int primary key,
`name` varchar(20),
)方式二语法：
-- 在定义字段之后再指定主键,语法格式如下
create table 表名(
[constraint<约束名>] primary key [字段名]
)方式二实现：
create table emp2(
id int,
name varchar(20),
constraint pk1 primary key(id)
)3.1.2、添加多列主键(联合主键)
所谓的联合主键，就是这个主键是由一张表中多个字段组成的。
注意：
- 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束
- 一张表只能有一个主键，联合主键也是一个主键
语法：
create table 表名(
primary key(字段1,字段2....)
)实现：
create table emp3(
name varchar(20),
id int,
primary key(name,id)
)3.1.3、通过修改表结构添加主键
主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。
语法：
create table 表名(
);
alter table <表名> add primary key(字段列表);实现：
-- 添加单列主键
create table emp4(
id int,
name varchar(20)
);
alter table emp4 add primary key(id);3.1.4、删除主键约束
一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。
语法：
alter table <数据表名> drop primary key;实现：
-- 删除单列主键
alter table emp1 drop primary key;
-- 删除联合主键
alter table emp5 drop primary key;3.2、自增长约束
在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。
通过给字段添加 auto_increment 属性来实现主键自增长。
语法：
字段名 数据类型 auto_increment实现：
create table student(
id int primary key auto_increment,
name varchar(20)
);特点：
- 默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。
- 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
- auto_increment约束的字段必须具备 NOT NULL 属性。
- auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等)。
- auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。
3.2.1、指定自增字段初始值
如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加
方式一：创建表时指定
create table student(
id int primary key auto_increment,
name varchar(20)
)auto_increment=100;方式二：指定自增字段初始值
create table student(
id int primary key auto_increment,
name varchar(20)
);
alter table student auto_increment=100;delete和truncate在删除后自增列的变化
- delete 数据之后自动增长从断点开始
- truncate 数据之后自动增长从默认起始值开始
3.3、非空约束
MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。
语法：
-- 方式一🔥
<字段名><数据类型> not null;
-- 方式二
alter table 表名 modify 字段 类型 not null;实现：
-- 方式一🔥
create table student(
id int,
name varchar(20) not null,
address varchar(20) not null
);
-- 方式二
create table student(
id int,
name varchar(20),
address varchar(20)
);
alter table student modify name varchar(20) not null;
alter table student modify address varchar(20) not null;3.3.1、删除非空约束
语法：
alter table 表名 modify 字段 类型实现：
alter table student modify name varchar(20);
alter table student modify address varchar(20);3.4、唯一约束
唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。
语法：
-- 方式一
<字段名><数据类型> unique
-- 方式二
alter table 表名 add constraint 约束名 unique(列);实现：
-- 方式一 创建表时指定
create table student(
id int,
name varchar(20),
address varchar(20) unique -- 指定唯一约束
);
-- 方式二
create table student(
id int,
name varchar(20),
address varchar(20)
);
alter table student add constraint unique_ad unique(address);3.4.1、删除唯一约束
语法：
-- alter table <表名> drop index <唯一约束名>
alter table student drop index unique_ad;3.5、默认约束
MySQL 默认值约束用来指定某列的默认值。
语法：
-- 方式一
<字段名><数据类型> default <默认值>
-- 方式二
alter table 表名 modify 列名 类型 default 默认值;实现：
-- 方式一
create table student(
id int,
name varchar(20) default '小林' -- 指定默认约束
)；
-- 方式二
create table student(
id int,
name varchar(20)
)；
alter table student modify name varchar(20) default '小林'3.5.1、删除默认约束
语法：
alter table <表名> modify column <字段名><类型> default null;实现：
alter table student modify column name varchar(20) default null;3.6、零填充约束
- 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
- zerofill默认为int(10)
- 当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128 ~ +127，无符号为0~256。
实现：
create table student(
id int zerofill, -- 零填充约束
name varchar(20)
);3.6.1、删除零填充约束
语法：
alter table <表名> modify <字段><数据类型>实现：
alter table <表名> modify <id><int>4、DQL
4.1、基本查询
语法格式：
select [all|distinct]
<目标列的表达式1> [别名],
<目标列的表达式2> [别名]
from <表名或视图名> [别名],<表名或视图名> [别名]
[where<条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>]- 创建数据库和表
-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb2;
USE mydb2;
-- 创建商品表：
CREATE TABLE product(
pid INT PRIMARY KEY AUTO_INCREMENT, -- 商品编号
pname VARCHAR(20) NOT NULL , -- 商品名字
price DOUBLE, -- 商品价格
category_id VARCHAR(20) -- 商品所属分类
);
INSERT INTO product VALUES(NULL,'海尔洗衣机',5000,'c001');
INSERT INTO product VALUES(NULL,'美的冰箱',3000,'c001');
INSERT INTO product VALUES(NULL,'格力空调',5000,'c001');
INSERT INTO product VALUES(NULL,'九阳电饭煲',200,'c001');
INSERT INTO product VALUES(NULL,'啄木鸟衬衣',300,'c002');
INSERT INTO product VALUES(NULL,'恒源祥西裤',800,'c002');
INSERT INTO product VALUES(NULL,'花花公子夹克',440,'c002');
INSERT INTO product VALUES(NULL,'劲霸休闲裤',266,'c002');
INSERT INTO product VALUES(NULL,'海澜之家卫衣',180,'c002');
INSERT INTO product VALUES(NULL,'杰克琼斯运动裤',430,'c002');
INSERT INTO product VALUES(NULL,'兰蔻面霜',300,'c003');
INSERT INTO product VALUES(NULL,'雅诗兰黛精华水',200,'c003');
INSERT INTO product VALUES(NULL,'香奈儿香水',350,'c003');
INSERT INTO product VALUES(NULL,'SK-II神仙水',350,'c003');
INSERT INTO product VALUES(NULL,'资生堂粉底液',180,'c003');
INSERT INTO product VALUES(NULL,'老北京方便面',56,'c004');
INSERT INTO product VALUES(NULL,'良品铺子海带丝',17,'c004');
INSERT INTO product VALUES(NULL,'三只松鼠坚果',88,NULL);
- 查询所有的商品
select * from product;- 查询商品名和商品价格
select pname,price from product;- 别名查询
- 表别名
select * from product as p;- 列别名
select pname as pn from product;- 去掉重复值
select distinct price from product;
- 查询结果是表达式(运算查询)：将所有商品的价格+10元进行显示
select pname,price+10 from product;
4.2、运算符
数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。
例如，学生表中存在一个birth字段，这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。
MySQL支持4种运算符：
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
4.2.1、算术运算符
算术运算符 | 说明 |
+ | 加法运算 |
- | 减法运算 |
* | 乘法运算 |
/ 或者 DIV | 除法运算，返回商 |
% 或者 MOD | 求余运算，返回余数 |
select 6 + 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 2;
-- 将每件商品的价格加10
select name,price+10 as new_price from product;4.2.2、比较运算符
比较运算符 | 说明 |
= | 等于 |
< 和 <= | 小于和小于等于 |
> 和 >= | 大于和大于等于 |
<=> | 安全的等于，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 |
<> 或 != | 不等于 |
isnull 或者 is null | 判断一个值是否为null |
is not null | 判断一个值是否不为null |
least | 当有两个或多个参数时，返回最小值 |
greatest | 当有两个或多个参数时，返回最大值 |
between and | 判断一个值是否落在两个值之间 |
in | 判断一个值是in列表中的任意一个值 |
not in | 判断一个值不是in列表中的任意一个值 |
like | 通配符匹配 |
regexp | 正则表达式匹配 |
-- 查询商品名称为“海尔洗衣机”的商品所有信息：
select * from product where pname = '海尔洗衣机';
-- 查询价格为800商品
select * from product where price = 800;
-- 查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where not(price = 800);
-- 查询商品价格大于60元的所有商品信息
select * from product where price > 60;
-- 查询商品价格在200到1000之间所有商品
select * from product where price >= 200 and price <=1000;
select * from product where price between 200 and 1000;4.2.3、逻辑运算符
逻辑运算符 | 说明 |
not 或者 ！ | 逻辑非 |
and 或者 && | 逻辑与 |
or 或者 || | 逻辑或 |
like | a like b，SQL匹配，如果a匹配b，则结果为真 |
in | a in(a1,a2,a3…)，假设a在a1或者a2…，则结果为真 |
-- 查询商品价格是200或800的所有商品
select * from product where price = 200 or price = 800;
select * from product where price in (200,800);
-- 查询含有'裤'字的所有产品
select * from product where pname like '%裤%';
-- 查询以'海'开头的所有商品
select * from product where pname like '海%';
-- 查询第二个字为'寇'的所有商品
select * from product where pname like '_寇%';
-- 查询category_id为null的商品
select * from product where category_id is null;
-- 查询category_id不为null分类的商品
select * from product where category_id is not null;
-- 使用least求最小值
select least(10,20,30); -- 10
select least(10,null,30); -- null
-- 使用greatest求最大值
select greatest(10,20,30); -- 30
select greatest(10,null,30); -- null- like结合使用的通配符 :
%(代表0到任意个字符)_(一个字符) - 注意：
% _只能在like里面使用 -
in里面是确切的集合
4.2.4、位运算符
位运算符 | 说明 |
| | 按位或 |
& | 按位与 |
^ | 按位异或 |
<< | 按位左移 |
>> | 按位右移 |
~ | 按位取反，反转所有比特 |
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。
select 3&5; -- 位与
select 3|5; -- 位或
select 3^5; -- 位异或
select 3>>1; -- 位左移
select 3<<1; -- 位右移
select ~3; -- 位取反4.3、排序查询
如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
语法：
select
字段名1,字段名2
from 表名
order by 字段名1 [asc|desc],字段名2[asc|desc]特点：
- asc 代表升序，desc 代表降序，如果不写默认升序
- order by 用于子句中可以支持单个字段，多个字段，表达式，函数，别名
- order by 子句，放在查询语句的最后面。LIMIT子句除外
-- 使用价格排序(降序)
select * from product order by price desc;
-- 在价格排序(降序)的基础上,以分类排序(降序)
select * from product order by price desc,category_id asc;
-- 显示商品的价格(去重复),并排序(降序)
select distinct price from product order by price desc;4.4、聚合查询
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
聚合函数 | 作用 |
count() | 统计指定列不为null的 |
sum() | 计算指定列的 |
max() | 计算指定列的 |
min() | 计算指定列的 |
avg() | 计算指定列的 |
-- 查询商品的总条数：统计product表有多少行
select count(*) from product;
-- 查询价格大于200商品的总条数
select count(*) from product where price > 200;
-- 查询分类为'c001'的所有商品的综合
select sum(price) from product where category_id = 'c001';
-- 查询商品的最小价格
select min(price) from product;
-- 查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';4.4.1、null值处理
- count 函数对null值的处理
如果 count 函数的参数为星号*,则统计所有记录的个数。而如果参数为某字段，则不统计含 null 值的记录个数 - sum 和 avg 函数对 null 值的处理
这两个函数忽略 null 值的存在，就好像该条记录不存在一样 - max 和 min 函数对null值的处理
max 和 min 两个函数同样忽略null值的存在。
我们可以进行测试：
-- 创建表
CREATE TABLE test_null(
c1 VARCHAR(20),
c2 INT
);
-- 插入数据
INSERT INTO test_null VALUES('aaa',3);
INSERT INTO test_null VALUES('bbb',3);
INSERT INTO test_null VALUES('ccc',NULL);
INSERT INTO test_null VALUES('ddd',6);-- 测试
SELECT COUNT(*), COUNT(1), COUNT(c2) FROM test_null;
- count(*) 统计所有记录的个数为4
- count(1) 统计所有记录的个数为4
- count(c2) 统计不统计含 null 值的记录个数为3
-- 测试
SELECT SUM(c2),MAX(c2),MIN(c2),AVG(c2) FROM test_null;
- sum(c2) 忽略 null值
- max(c2) 忽略 null值
- min(c2) 忽略 null值
- avg(c2) 忽略 null值
4.5、分组查询
分组查询是指使用group by字句对查询信息进行分组。
语法：
select
字段1,字段2
from 表名
group by 分组字段
having 分组条件；实现：
-- 统计各个分类商品的个数
select category_id,count(*) from product group by category_id;
如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现：
4.5.1、having
分组之后的条件筛选-having
- 分组之后对统计结果进行筛选的话必须使用having，不能使用where
- where子句用来筛选 FROM 子句中指定的操作所产生的行
- group by 子句用来分组 WHERE 子句的输出。
- having 子句用来从分组的结果中筛选行
-- 统计各个分类商品的个数,且只显示个数大于4的信息
SELECT category_id,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;
4.6、分页查询
分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。
语法：
-- 方式一：显示前n条
select 字段1,字段2 from 表名 limit n
-- 方式二：分页显示
-- m:整数，表示从第几条索引开始,计算方式 (当前页-1)*每页显示条数
-- n:整数,表示查询多少条数据
select 字段1,字段2 from 表名 limit m,n实现：
-- 查询product表的前5条记录
select * from product limit 5;
-- 从第4条开始显示,显示5条
select * from product limit 3,5;
4.7、insert into select
将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句 。
语法：
insert into Table2(field1,field2) select value1,value2 from Table1
-- 或者
insert into Table2 select * from Table1要求目标表 Table2 必须存在
4.8、select into from
将一张表的数据导入到另一张表中，有两种选择 SELECT INTO 和 INSERT INTO SELECT 。
格式：
select value1,value2 into Table2 from Table1要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
5、DQL语句练习一
-- 创建数据库mydb1
create database if not exists mydb1;
-- 使用数据库mydb1
use mydb1;
-- 创建学生表
create table student(
id int,
name varchar(20),
gender varchar(20),
chinese int,
english int,
math int
);
-- 插入数据
insert into student values(1,'张明','男',89,78,90);
insert into student values(2,'李进','男',67,68,90);
insert into student values(3,'王五','女',81,72,91);
insert into student values(4,'李一','女',89,88,88);
insert into student values(5,'李财','女',81,78,90);
insert into student values(6,'张宝','男',80,70,90);
insert into student values(7,'黄蓉','男',70,80,90);
insert into student values(8,'大宝','女',83,73,93);
insert into student values(9,'二宝','男',84,75,96);
insert into student values(10,'三宝','男',84,74,94);
- 查询表中所有学生的信息
select * from student;- 查询表中所有学生的姓名和对应的英语成绩
select name,english from student;- 过滤表中重复数据
-- 若 distinct 后面跟着的列,表示这一列重复的会被去掉
-- 若 distinct 后面跟着的*,表示这一行所有字段都重复的会被去掉
select distinct * from student;- 统计每个学生的总分
-- 正确写法
select name,chinese + english + math as total_score from student;
-- 错误写法(注意,sum() 表示对某一列进行求和)
select name,sum() from student;- 在所有学生总分数上加10分特长分
select name,(chinese + english + math + 10) as total_score from student;- 使用别名表示学生分数
-- 起别名 as 可以省略
select name,chinese '语文成绩',english '英语成绩', math '数学成绩' from student;
-- 写法二
select name,chinese as '语文成绩',english as '英语成绩', math as '数学成绩' from student;- 查询英语成绩大于90分的同学
select * from student where english > 90;- 查询总分大于200分的所有同学
select * from student where (chinese + english + math) > 200;
-- 错误写法(先执行from,再执行 where,所以在执行 where 时别名不存在,这样写会报错)
select *, (chinese + english + math) as total_score from student where total_score > 200;- 查询英语分数在 80 - 90 之间的同学
select * from student where english between 80 and 90;
select * from student where english ≥ 80 and english ≤ 90;- 查询英语分数不在 80 - 90 之间
select * from student where not (english between 80 and 90);
select * from student where english not between 80 and 90;
select * from student where not (english ≥ 80 and english ≤ 90);
select * from student where english ＜ 80 or english ＞ 90;- 查询数学分数为 89，90，91的同学
select * from student where math in(80,90,91);- 查询数学分数不为 89，90，91的同学
select * from student where math not in(80,90,91);
select * from student where not math in(80,90,91);- 查询所有姓李的学生英语成绩
select name,english from student where name like '李%';- 查询数学分80并且语文分80的同学
select * from student where math = 80 and chinese = 80;- 查询英语80或者总分两百的同学
select * from student where english = 80 and (chinese + math + english) = 200;- 对数学成绩降序排序后输出
select * from student order by math desc;- 对总分排序后输出，并且按从高到低的顺序输出
select * from student order by (chinese + math + english) desc;- 对姓李的学生总分成绩排序输出
select * from student where name like '李%' order by (chinese + math + english) desc;- 查询男生和女生分别有多少人，并将人数降序排序输出
select
gender,count(*) as total_cnt
from
student
group by
gender
order by
total_cnt
desc;6、DQL语句练习二
-- 创建员工表
create table emp(
empno int, -- 员工编号
ename varchar(50), -- 员工名字
job varchar(50), -- 工作名字
mgr int, -- 上级领导编号
hiredate date, -- 入职日期
sal int, -- 薪资
comm int, -- 奖金
deptno int -- 部门编号
);
-- 插入数据
insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',1800,NULL,20);
insert into emp values(7469,'RED','AUGENSE',7692,'1981-12-17',2800,200,30);
insert into emp values(7569,'PINK','OPKUO',7839,'1982-12-17',3800,300,40);
insert into emp values(7669,'YELLOW','WEWQS',8921,'1983-12-17',4800,NULL,50);
insert into emp values(7769,'KING','QWERQ',7902,'1984-12-17',5800,500,60);
insert into emp values(7869,'HACK','ZXCMK',7902,'1985-12-17',6800,NULL,70);
insert into emp values(7969,'JACK','SDAWD',7902,'1986-12-17',7800,600,80);
insert into emp values(7169,'ADAMS','SAAWQ',7902,'1987-12-17',8800,NULL,90);
insert into emp values(7269,'FORD','CLERK',7902,'1988-12-17',9800,NULL,10);
insert into emp values(7844,'SCOTT','CLERK',7902,'1989-12-17',1800,NULL,20);
insert into emp values(7969,'SMITH','CLERK',7902,'1980-12-17',2800,900,20);
insert into emp values(7069,'SMITH','CLERK',7902,'1981-12-17',3800,NULL,20);
- 按员工编号升序排列不在10号部门工作的员工信息
select * from emp where deptno != 10 order by empno asc;- 查询姓名的第二个字母不是A，且薪资大于1000的员工信息，并按年薪降序排列
-- 年薪 = 薪资 × 12 + 奖金 ,奖金里面有 null 值存在,所以不能直接加null
-- ifnull(comm,0) 此函数的作用:如果奖金comm为null,将其看作0来处理
select
*
from
emp
where
ename
not like
'_A%'
and
sal > 1000
order by
(sal*12+ ifnull(comm,0)) desc;- 求每个部门的平均薪水(每个部门，分组查询)
select deptno,avg(sal) from emp group by deptno;- 求各个部门的最高薪水
select deptno,max(sal) from emp group by deptno;- 求每个部门每个岗位的最高薪水
-- 两个分组,同一个岗位且属于同一个部门分组
select deptno,job,max(sal) from emp group by deptno,job;- 求平均薪水大于2000的部门编号
-- 对分组之后的结果进行筛选用 having
-- select deptno,avg(sal) avg_sal from emp group by deptno 这查出来就是分组之后的结果
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;- 将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 1500 order by avg_sal desc;- 选择公司中有奖金的员工姓名、薪资
select * from emp where comm is not null;- 查询员工最高工资和最低工资的差距
select max(sal) - min(sal) as '薪资差距' from emp;7、DQL正则表达式
正则表达式(regular expression)描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。
MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。
模式 | 描述 |
^ | 匹配输入字符串的开始位置 |
$ | 匹配输入字符串的结束位置 |
. | 匹配除 “\n” 之外的任何单个字符。 |
[…] | 字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。 |
[^…] | 负值字符集合。匹配未包含的任意字符。例如， |
p1|p2|p3 | 匹配p1或p2或p3。例如：‘z|food’ 能匹配 “z” 或 “food”。 ‘(z|f)ood’ 则匹配 “zood” 或者 “food” |
* | 匹配前面的子表达式零次或多次。例如： |
+ | 匹配前面的子表达式一次或多次。例如： |
{n} | n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。 |
{n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次 |
-- ^ 在字符串开始处进行匹配
-- 含义: abc 是否以 a 开头
select 'abc' regexp '^a'; -- 结果为1,表示结果为真
-- 判断哪些商品的名字是以海开头
select * from product where pname regexp '^海';
-- $ 在字符串末尾开始匹配
-- 含义: abc 是否以 a 结尾
select 'abc' regexp 'a$'; -- 结果为0,表示否
-- 判断哪些商品的名字是以水结尾
select * from product where pname regexp '水$';-- .匹配任意单个字符(除了换行符之外)
-- 含义: abc 中是否 b 前面有一个字符
select 'abc' regexp '.b'; -- 1
select 'abc' regexp 'a.'; -- 1-- [...]匹配括号内的任意单个字符
-- 含义: xyz 中任意单个字符是否有在 abc 中出现呢
select 'abc' regexp '[xyz]'; -- 0
select 'abc' regexp '[xaz]'; -- 1-- [^...] 注意 ^ 符合只有在 [] 内才是取反的意思,在别的地方都是表示开始处匹配
-- 含义: abc 里面任意单个字符都没有在前面出现
select 'a' regexp '[^abc]'; -- 0
select 'x' regexp '[^abc]'; -- 1
select 'abc' regexp '[^a]'; -- 1-- a* 匹配0个或多个a,包括空字符串。可以作为占位符使用,有没有指定字符都可以匹配到数据
select 'stab' regexp '.ta*b'; -- 1
select 'stb' regexp '.ta*b'; -- 1
select '' regexp 'a*'; -- 1-- a+ 匹配1个或多个a,但是不包括空字符
select 'stab' regexp '.ta+b'; -- 1
select 'stb' regexp '.ta+b'; -- 0-- a? 匹配0个或一个a(只能出现一个或者0个)
select 'stab' regexp '.ta?b'; -- 1
select 'stb' regexp '.ta?b'; -- 1
select 'staab' regexp '.ta?b'; -- 0-- a1|a2 匹配a1或者a2
select 'a' regexp 'a|b'; -- 1
