MySql查询和约束-课堂笔记
一、DQL
- 条件查询
1.1 单条件查询
-
运算符有:>, <, >=, <=, =, <>。is null 和 is not null
-- 1. 单条件查询 -- 1.1 查询所有的男同学 SELECT * FROM student3 WHERE sex = '男'; -- 1.2 查询年龄在25岁以上的同学 SELECT * FROM student3 WHERE age > 25; -- 1.3 查询地址不是香港的同学 SELECT * FROM student3 WHERE address <> '香港'; -- 1.4 查询英语成绩为空的同学 SELECT * FROM student3 WHERE english IS NULL; SELECT * FROM student3 WHERE english IS NOT NULL;
1.2 多条件
-
连接符有:and, or, not
-- 2. 多条件查询 -- 2.1 查询年龄大于25岁的男同学 SELECT * FROM student3 WHERE age > 25 AND sex = '男'; -- 2.2 查询年龄小于25岁的同学,或者英语成绩大于80的同学 SELECT * FROM student3 WHERE age < 25 OR english > 80; -- 2.3 查询年龄不大于25岁的同学 SELECT * FROM student3 WHERE age <= 25; SELECT * FROM student3 WHERE NOT(age > 25);
1.3 范围条件
-
between 开始值 and 结束值,包含开始值和结束值
-
in(值1, 值2, …)
– 3. 范围查询
– 3.1 查询年龄在20到50之间(包含20和50)SELECT * FROM student3 WHERE age >= 20 AND age <= 50; SELECT * FROM student3 WHERE age BETWEEN 20 AND 50; -- 3.2 查询地址是深圳和南京的同学 SELECT * FROM student3 WHERE address = '深圳' OR address = '南京'; SELECT * FROM student3 WHERE address IN ('深圳', '南京');
1.4 模糊查询
- 语法:like ‘表达式’。在表达式里可以写:
-
_:一个任意字符
-
%:任意个任意字符
– 4. 模糊查询
– 4.1 查询所有姓马的同学SELECT * FROM student3 WHERE NAME LIKE '马%'; -- 4.2 查询姓马的、名称为两个字的同学 SELECT * FROM student3 WHERE NAME LIKE '马_'; -- 4.3 查询姓名里含有“德”字的同学 SELECT * FROM student3 WHERE NAME LIKE '%德%'; -- 4.4 查询姓名为两个字的同学 SELECT * FROM student3 WHERE NAME LIKE '__'; SELECT * FROM student3 WHERE CHAR_LENGTH(NAME) = 3; -- 4.5 查询姓名里不包含“德”字的同学 SELECT * FROM student3 WHERE NAME NOT LIKE '%德%';
-
- 排序查询
- 语法:order by 排序字段 排序规则, 排序字段2 排序规则2, …。排序规则有:
-
ASC:升序,从小到大,默认的
-
DESC:降序,从大到小
– 5. 排序查询
-- 5.1 查询所有的同学,按照年龄从小到大排列显示 SELECT * FROM student3 ORDER BY age ASC -- 5.2 查询所有的同学,按照年龄从小到大排列显示;如果年龄相同,按照数学成绩从高到低排序 SELECT * FROM student3 ORDER BY age ASC, math DESC -- 5.3 查询所有的男同学,按照年龄从小到大排列显示;如果年龄相同,按照数学成绩从高到低排序 SELECT * FROM student3 WHERE sex = '男' ORDER BY age ASC, math DESC
-
- 聚合函数
- 聚合函数:用于统计的。常用的聚合函数有:
- count():统计数量
- sum():求和
- avg():求平均值
- max():求最大值
- min():求最小值
注意:所有聚合函数会忽略null值
-- 6. 聚合函数
-- 6.1 查询学生表里的数量
SELECT COUNT(*) FROM student3;
-- 6.2 查询所有学生的年龄总和
SELECT SUM(age) FROM student3;
-- 6.3 查询所有学生的平均年龄
SELECT SUM(age)/COUNT(*) FROM student3;
SELECT AVG(age) FROM student3;
-- 6.4 查询最大年龄
SELECT MAX(age) FROM student3;
-- 6.5 查询最小年龄
SELECT MIN(age) FROM student3;
-- 6.6 所有聚合函数都会忽略null值
SELECT COUNT(english) FROM student3;
SELECT AVG(english) FROM student3;
SELECT SUM(english)/COUNT(*) FROM student3;
- 分组查询
- 语法:group by 分组字段 having 分组后的过滤条件
- 注意:
- select后边,只能跟分组的字段,或者聚合函数
- where和having的区别:
-
where在分组前执行;having在分组后执行
-
where过滤原始表的数据;having过滤分组后的数据
-
where里不要写聚合函数;having里可以写聚合函数
-- 7. 分组查询 -- 7.1 分组统计男生和女生的数量 SELECT sex,COUNT(*) FROM student3 GROUP BY sex; -- 7.2 分组统计每个地方的学生数量 SELECT address, COUNT(*) FROM student3 GROUP BY address; -- 7.3 分组统计每个地方的学生数量,只要数量大于1的 SELECT address, COUNT(*) FROM student3 GROUP BY address HAVING COUNT(*) > 1; -- 7.4 分组统计每个地方的男生数量 SELECT address, COUNT(*) FROM student3 WHERE sex = '男' GROUP BY address; -- 7.5 分组统计每个地方的男生数量,只要数量大于1的 SELECT address, COUNT(*) FROM student3 WHERE sex = '男' GROUP BY address HAVING COUNT(*) > 1;
-
- 分页查询
-
语法:limit 起始索引,查询数量
– 8. 分页查询:每页3条
-- 8.1 查询第1页的数据 起始索引:0, 查询数量:3 SELECT * FROM student3 LIMIT 0, 3; -- 8.2 查询第2页的数据 起始索引:3, 查询数量:3 SELECT * FROM student3 LIMIT 3, 3;
-
综合查询
– 数据准备
create tablestudent3
(
id
int (11),
name
varchar (60),
age
int (11),
sex
varchar (15),
address
varchar (300),
math
int (11),
english
int (11)
);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘1’,‘马云’,‘55’,‘男’,‘杭州’,‘66’,‘78’);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘2’,‘马化腾’,‘45’,‘女’,‘深圳’,‘98’,‘87’);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘3’,‘马景涛’,‘55’,‘男’,‘香港’,‘56’,‘77’);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘4’,‘柳岩’,‘20’,‘女’,‘湖南’,‘76’,‘65’);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘5’,‘柳青’,‘20’,‘男’,‘湖南’,‘86’,NULL);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘6’,‘刘德华’,‘57’,‘男’,‘香港’,‘99’,‘99’);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘7’,‘马德华’,‘22’,‘女’,‘香港’,‘99’,‘99’);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘8’,‘德玛西亚’,‘18’,‘男’,‘南京’,‘56’,‘65’);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘9’,‘李彦宏’,‘48’,‘男’,‘湖南’,‘100’,‘100’);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘10’,‘丁磊’,‘45’,‘男’,‘湖南’,‘100’,‘99’);
insert into student3
(id
, name
, age
, sex
, address
, math
, english
) values(‘11’,‘东哥’,‘49’,‘男’,‘杭州’,‘95’,‘90’);
– 9. 综合查询
– 9.1 分组统计每个地方的男生数量,只要数量大于1的。查询结果按照数量升序显示。 每页2条,显示第1页
SELECT address, COUNT(*) FROM student3 WHERE sex = '男' GROUP BY address HAVING COUNT(*) > 1 ORDER BY COUNT(*) ASC LIMIT 0,2;
二、备份和恢复
-
SQLyog备份与恢复
-
dos窗口备份与恢复
- 备份(dos命令):mysqldump -u用户名 -p密码 数据库名称 > E:\heima63.sql
- 恢复(sql语句):
- 登录MySql
- 如果库不存在,就创建库;
- 使用库
- 执行SQL:source E:\heima63.sql
三、约束
- 主键约束
-
主键:一张表的唯一标识。原则上:每张表必须有主键,且只能有一个主键
-
主键约束:
-
特点:被主键约束的字段,值必须是非空、唯一
-
语法:primary key
-- 1. 创建一张商品表product(商品id 主键, 商品名称pname, 商品价格price) CREATE TABLE product( id INT PRIMARY KEY, pname VARCHAR(100), price DOUBLE );` - 2. 验证主键的效果:值必须唯一、非空 INSERT INTO product (id,pname,price) VALUES (1,'Thinkpad T490', 8999); -- 正常插入 INSERT INTO product (id,pname,price) VALUES (1,'macbook pro', 15999); -- 不能插入,主键值重复了 INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999); -- 不能插入,主键值为空了
-
-
主键自增策略:
-
要求:主键字段必须是整数类型,MySql会自动生成主键值:1,2,3, … +1
-
语法:primary key auto_increment
-- 1. 创建一张商品表product(商品id 主键 自增, 商品名称pname, 商品价格price) CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(100), price DOUBLE ); -- 2. 验证主键自增的效果:主键不设置值,或者设置为null,MySql会自动生成主键值 INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', 8999); -- 正常插入 INSERT INTO product (id,pname,price) VALUES (NULL,'macbook pro', 15999); -- 正常插入 INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999); -- 正常插入
-
- 唯一性约束
-
效果:唯一性约束的字段,值不能重复,但是可以为null
-
语法:字段名 类型 unique
-- 1. 创建一张商品表product(商品id 主键 自增, 商品名称pname 唯一, 商品价格price) CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(100) UNIQUE, price DOUBLE ); -- 2. 验证唯一性约束的效果:值不能重复 INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', 8999); -- 正常插入 INSERT INTO product (id,pname,price) VALUES (NULL,'macbook pro', 15999); -- 正常插入 INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999); -- 正常插入 INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999); -- 不能插入,因为pname值不唯一 INSERT INTO product (id,pname,price) VALUES (NULL,NULL, 5999); -- 正常插入
- 非空约束
-
效果:非空约束的字段,值不能为null
-
语法:字段名 类型 not null
-- 1. 创建一张商品表product(商品id 主键 自增, 商品名称pname 唯一、非空, 商品价格price) CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(100) UNIQUE NOT NULL, price DOUBLE ); -- 2. 验证非空约束的效果:值不能为空 INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', 8999); -- 正常插入 INSERT INTO product (id,pname,price) VALUES (NULL,'macbook pro', 15999); -- 正常插入 INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999); -- 正常插入 INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999); -- 不能插入,因为pname值不唯一 INSERT INTO product (id,pname,price) VALUES (NULL,NULL, 5999); -- 不能插入,因为pname值为空了
- 默认值约束
-
效果:默认值约束的字段,如果不指定值,取默认值(如果指定null值,值就是null,不取默认值)
-
语法:字段名 类型 default 默认值
-- 1. 创建一张商品表product(商品id 主键 自增, 商品名称pname 唯一、非空, 商品价格price 默认值0) CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(100) UNIQUE NOT NULL, price DOUBLE DEFAULT 0 ); -- 2. 验证默认值的效果:不设置值的时候,取默认值 INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', NULL); -- 插入null值,不取默认值 INSERT INTO product (id,pname) VALUES (NULL,'macbook pro'); -- 正常插入
- 外键约束
- 效果:外键约束的字段,必须从指定表的主键中取值。
- 目的:保证数据的一致性和完整性,避免脏数据
- 语法:
-
创建表时设置外键:[constraint 约束名称] foreign key(外键字段) references 主表(主键)
-
已有表里设置外键:alter table 表名称 add [constraint 约束名称] foreign key(外键字段) references 主表(主键)
-- 1. 创建一张商品分类表category(主键id, 分类名称cname) CREATE TABLE category( id INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(50) NOT NULL UNIQUE ); -- 2. 准备一些商品分类信息 INSERT INTO category (id,cname) VALUES (NULL, '家用电器'); -- id值是1 INSERT INTO category (id,cname) VALUES (NULL, '手机数码'); -- id值是2 INSERT INTO category (id,cname) VALUES (NULL, '电脑办公'); -- id值是3 -- 3. 创建一张商品表product(主键id,商品名称pname,商品价格price, 所属分类cid 外键 从category的id里取值) CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(100) NOT NULL UNIQUE, price DOUBLE DEFAULT 0, cid INT NOT NULL, CONSTRAINT product_category_fk FOREIGN KEY(cid) REFERENCES category(id) ); -- 4. 准备一些商品数据 INSERT INTO product (id,pname,price,cid) VALUES (NULL, '小米电视4A 65', 2999, 1); -- 正常插入,cid的值是从category表的id里取的 INSERT INTO product (id,pname,price,cid) VALUES (NULL, '吉普JEEP牛仔裤男士青年商务休闲长裤子', 133.2, 4);-- 不能插入,但是插入的是脏数据,没有对应的分类 INSERT INTO product (id,pname,price,cid) VALUES (NULL, '吉普JEEP牛仔裤男士青年商务休闲长裤子', 133.2, NULL); -- 不能插入(因为外键字段上有约束not null)
-
四、表关系
- 一对一(了解)
- 不常用,因为一对一的两张表,通常可以合并成一张表。但是以下情况,建议拆分表,例如:
- 出于效率的考虑:常用字段放在一张表,不常用字段放在另外一张。两张表一对一
- 出于业务划分的考虑:用户信息表,和帐户信息表。
- 一对多
- 例如:分类和商品,用户和订单
- 建表原则:在从表上增加一个字段,作为外键指向主表的主键。
- 从表:多的一方
- 主表:一的一方
- 多对多
-
例如:老师和学生,订单和商品,学生和课程
-
建表原则:建立一张中间关系表,表里要有两个字段,是外键分别指向两张表的主键
– 多对多 表设计: 学生表,课程表
– 1. 创建学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);
– 2. 创建课程表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20)
);
– 3. 创建中间关系表
CREATE TABLE student_course(
id INT PRIMARY KEY AUTO_INCREMENT,
sid INT,
cid INT,
FOREIGN KEY(sid) REFERENCES student(id),
FOREIGN KEY(cid) REFERENCES course(id)
);
– 4. 准备数据
INSERT INTO student(id, NAME) VALUES (NULL, ‘刘德华’);
INSERT INTO student(id, NAME) VALUES (NULL, ‘刘邦’);
INSERT INTO student(id, NAME) VALUES (NULL, ‘刘文健’);INSERT INTO course(id,sname) VALUES (NULL, ‘英语’);
INSERT INTO course(id,sname) VALUES (NULL, ‘Java’);
INSERT INTO course(id,sname) VALUES (NULL, ‘体育’);– 刘文健选择课程:英语和Java
INSERT INTO student_course(id,sid,cid) VALUES (NULL, 3, 1);
INSERT INTO student_course(id,sid,cid) VALUES (NULL, 3, 2);
– 刘德华选择课程:英语和体育
INSERT INTO student_course(id,sid,cid) VALUES (NULL, 1, 1);
INSERT INTO student_course(id,sid,cid) VALUES (NULL, 1, 3);
内容回顾
-
DQL查询
- 简单查询
- 条件查询
- 排序查询
- 聚合函数
- 分组查询
- 分页查询
- 综合查询:
select 字段|聚合函数 from 表名 where 条件 group by 分组字段 having 分组后过滤条件 order by 排序字段 排序规则 limit 起始索引, 查询数量
-
备份和恢复(了解)
-
约束
- 主键约束:主键字段值非空唯一;一张表要有只能有一个主键
- 唯一性约束:字段值不能重复,但是可以为null
- 非空约束:字段值不能为null
- 默认值约束:如果不设置字段值,就取默认值。
- 外键约束:外键字段的值,必须从主表的主键中取值。维护数据的一致性和完整性,避免脏数据
-
表关系:
- 一对一(了解):
- 建表原则:任意一张表增加外键,外键字段指向另外一张表的主键,并且外键字段要加唯一性约束
- 一对多:
- 建表原则:在从表上增加外键字段,指向主表的主键字段
- 主表:一的一方
- 从表:多的一方
- 多对多:
- 建表原则:建立一张中间关系表,表里有两个外键字段,分别指向两张表的主键
- 一对一(了解):