目录
一、==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
-
默认值约束：如果不设置字段值，就取默认值。
-
外键约束：外键字段的值，必须从主表的主键中取值。维护数据的一致性和完整性，避免脏数据
-
-
表关系：
-
一对一（了解）：
-
建表原则：任意一张表增加外键，外键字段指向另外一张表的主键，并且外键字段要加唯一性约束
-
-
一对多:
-
建表原则：在从表上增加外键字段，指向主表的主键字段
-
主表：一的一方
-
从表：多的一方
-
-
多对多：
-
建表原则：建立一张中间关系表，表里有两个外键字段，分别指向两张表的主键
-
-
扫一扫
429
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
