一、==DQL==

1. 条件查询

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 '%德%';

2. 排序查询

语法：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

3. 聚合函数

聚合函数：用于统计的。常用的聚合函数有：

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;

4. 分组查询

语法：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; ​ 5. 分页查询

语法：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;

6. 综合查询

-- 数据准备

create table student3 ( 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;

二、备份和恢复

1. SQLyog备份与恢复

2. dos窗口备份与恢复

备份（dos命令）：mysqldump -u用户名 -p密码 数据库名称 > E:\heima63.sql

恢复（sql语句）：

登录MySql

如果库不存在，就创建库；

使用库

执行SQL：source E:\heima63.sql

三、约束

1. 主键约束

主键：一张表的唯一标识。原则上：每张表必须有主键，且只能有一个主键

主键约束：

特点：被主键约束的字段，值必须是非空、唯一

语法：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); -- 正常插入

2. 唯一性约束 效果：唯一性约束的字段，值不能重复，但是可以为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); -- 正常插入

3. 非空约束

效果：非空约束的字段，值不能为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值为空了

4. 默认值约束

效果：默认值约束的字段，如果不指定值，取默认值（如果指定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'); -- 正常插入

5. 外键约束

效果：外键约束的字段，必须从指定表的主键中取值。

目的：保证数据的一致性和完整性，避免脏数据

语法：

创建表时设置外键：[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. 一对一（了解）

不常用，因为一对一的两张表，通常可以合并成一张表。但是以下情况，建议拆分表，例如：

出于效率的考虑：常用字段放在一张表，不常用字段放在另外一张。两张表一对一

出于业务划分的考虑：用户信息表，和帐户信息表。

2. 一对多

例如：分类和商品，用户和订单

建表原则：在从表上增加一个字段，作为外键指向主表的主键。

从表：多的一方

主表：一的一方

3. 多对多

例如：老师和学生，订单和商品，学生和课程

建表原则：建立一张中间关系表，表里要有两个字段，是外键分别指向两张表的主键

-- 多对多 表设计: 学生表，课程表

-- 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

默认值约束：如果不设置字段值，就取默认值。

外键约束：外键字段的值，必须从主表的主键中取值。维护数据的一致性和完整性，避免脏数据

表关系：

一对一（了解）：

建表原则：任意一张表增加外键，外键字段指向另外一张表的主键，并且外键字段要加唯一性约束

一对多:

建表原则：在从表上增加外键字段，指向主表的主键字段

主表：一的一方

从表：多的一方

多对多：

建表原则：建立一张中间关系表，表里有两个外键字段，分别指向两张表的主键