MySQL基础教学02

1.DQL:查询语句

1.1 排序查询

语法

SELECT 字段名 FROM 表名 [WHERE条件] ORDER BY 字段名 [ASC|DESC];

ASC: 升序，默认值
DESC: 降序

注意
- 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
例子
- 查询所有数据,使用年龄降序排序
```
select * from student order by age desc;
```
- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
```
select * from student order by age desc, math asc;
```

补充案例

计算math 和 english总成绩并按照总成绩降序排序

SELECT NAME , math + IFNULL(english,0)  score FROM stu ORDER BY score DESC;

order by 总结
- order by 子句后可以支持那些内容？
```
表中的字段  函数  
```
- order by 执行顺序？
```
最后再执行  除了limit语句
```

1.2 聚合函数

什么是sql函数

当我们学习编程语言的时候，经常会遇到函数。函数的好处是，它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了编写代码的效率，又提高了可维护性。在sql中函数主要要对数据进行处理！
常用的sql函数
- 算术函数
- 字符串函数
- 日期函数
- 转换函数
- 聚合函数/聚集函数

语法

SELECT 聚合函数(列名) FROM 表名;

五个聚合函数

聚合函数	说明
count(*) \| count(主键)	计算表中的总记录数
max	计算最大值
min	计算最小值
sum	计算和
avg	计算平均值

注意

：聚合函数的计算，排除null值。
解决方案

：
1. 选择不包含非空的列进行计算
2. IFNULL函数

其他函数

函数名	说明	作用
length(str)	字符函数	获取字符的字节个数
upper(str)	字符函数	将字符转换为大写字符
lower(str)	字符函数	将字符转换为小写字符
substring(str,pos)	字符函数	截取从指定索引处后面所有的字符
substring(str,pos,len)	字符函数	截取从pos索引开始截取len个字符
replace(str,from_str,to_str)	字符函数	将str中的字符 from_str字符替换成to_str字符
round(x)	数学函数	四舍五入
round(x,d)	数学函数	四舍五入 d:代表的是保留小数点后几位
cell(x)	数学函数	向上取整
floor(x)	数学函数	向下取整
mod(n,m)	数学函数	取余数 mod(10,3) 相当于: select 10 % 3
str_to_date(str,format)	日期函数	将日期字符转换成指定格式的日期 str_to_date(‘1990-11-11’,’%Y-%m-%d’);
date_format(date,format)	日期函数	将日期转换成字符 date_format(now(),’%Y/%m/%d’);

例子

:
- 查询 id 字段，如果为 null，则使用 0 代替
```
select ifnull(id,0) from student;
```
- 查询总记录数-利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏
```
select count(ifnull(id,0)) from student;
```
- 查询年龄大于 20 的总数
```
select count(*) from student where age>20;
```
- 查询数学成绩总分
```
select sum(math) 总分 from student;
```
- 查询数学成绩平均分
```
select avg(math) 平均分 from student;
```
- 查询数学成绩最高分
```
select max(math) 最高分 from student;
```
- 查询数学成绩最低分
```
select min(math) 最低分 from student;
```

1.3 分组查询

语法

SELECT 字段 1,字段 2... FROM 表名 [where条件] GROUP BY 分组字段 [HAVING 条件] [order by];

注意
- 分组之后查询的字段：分组字段、聚合函数
- where 和 having 的区别？
  - where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来 where 对基本的条件筛选
  - where 后不可以跟聚合函数，having可以进行聚合函数的判断。
where 操作的数据源: 原始表

having 操作的数据源: 结果集

例子

按照性别分组。分别查询男、女同学的平均分
```
SELECT sex , AVG(math) FROM student GROUP BY sex;
```

按照性别分组。分别查询男、女同学的平均分,人数

SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;

按照性别分组。分别查询男、女同学的平均分,人数要求：分数低于70分的人，不参与分组
```
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
```

按照性别分组。分别查询男、女同学的平均分,人数要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人

SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

-- 最终写法
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

案例

按照address进行分组,并且math>60分的人员 , 分组完成之后,我只要人数>=2,并按照人数降序排序

SELECT 
	address,
	COUNT(id) AS c
FROM 
	student
WHERE
	math > 60
GROUP BY 
	address
HAVING 
	c>=2
ORDER BY
	c DESC;
	
#######################
SELECT address,COUNT(id) AS c
FROM student
WHERE math > 60
GROUP BY address
HAVING c>=1
ORDER BY c DESC	;

1.4 分页查询

语法

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句];

LIMIT语法格式
- 格式
```
LIMIT offset,length;
```
  - offset：起始行数，从 0 开始计数，如果省略，默认就是 0
  - length：返回的行数

计算公式

开始的索引 = （当前的页码 - 1） * 每页显示的条数

例子

-- 每页查询显示3条数据
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页

2. 约束

2.1 约束简介

2.1.1 什么是约束

概念

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

2.2.2 约束的分类

约束	说明
primary key	主键约束:非空且唯一
not null	非空约束 : 某一列的值不能为空
unique	唯一约束 : 某一列的值不能重复
foreign key	外键约束

2.2 约束详解

2.2.1 非空约束

create table 表名(

列名1 数据类型约束,

);

创建表时添加约束

CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL -- name为非空
);

创建表完后，添加非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

删除name的非空约束

 ALTER TABLE stu MODIFY NAME VARCHAR(20);

2.2.2 唯一约束

创建表时，添加唯一约束
```
CREATE TABLE stu(
 	id INT,
 	phone_number VARCHAR(20) UNIQUE -- 手机号
);
```
- 注意
  - 唯一约束可以有NULL值,也可以有多个null值

表创建完后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

2.2.3 主键约束

在创建表时，添加主键约束：primary key

create table stu(
	id int primary key ,-- 给id添加主键约束
	name varchar(20)
);

注意
- 含义：非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识

创建完表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

创建完表后，删除主键

-- 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;

2.2.4 自动增长

概念

如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

在创建表时，添加主键约束，并且完成主键自增长

create table stu(
    id int primary key auto_increment,-- 给id添加主键约束
    name varchar(20)
);

添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

删除自动增长
```
ALTER TABLE stu MODIFY id INT;
```

综合案例

/*
  创建一个person表
	pid 主键 自动增长
	pname 不为空
	phone_number 不为空,且唯一
	address 不为空 
	id_card 不为空,且唯一
*/

-- 创建Person表
CREATE TABLE person(
	pid INT PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(30) NOT NULL,
	phone_number VARCHAR(11) UNIQUE,
	address VARCHAR(50) NOT NULL ,
	id_card VARCHAR(20) NOT NULL UNIQUE
);

-- 添加一条数据
INSERT INTO person(`name`,`phone_number`,`card_number`) VALUES 
('张三','13814381438','411411198311114123');

2.2.4 外键约束

概念

foreign key,让表与表产生关系，从而保证数据的正确性。

数据准备

-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);


-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT, -- 外键对应主表的主键
	-- CONSTRAINT dept_emp_fk_id  可以省略不写
	CONSTRAINT dept_emp_fk_id FOREIGN  KEY (dep_id) REFERENCES department (id)
	 
);


-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
SELECT * FROM employee;

在创建表时，添加外键

-- 语法
create table 表名(
    ....
    外键列
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

级联操作

CREATE TABLE pp(
	pid INT PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(30) NOT NULL,
	phone VARCHAR(11) NOT NULL UNIQUE,
	p_fk_id INT
	-- 创建表的时候,添加外键和级联操作,两个级联操作可以同时加上,也可以单独加
	CONSTRAINT pp_p_fk FOREIGN KEY (p_fk_id) REFERENCES p1(id) ON UPDATE CASCADE  ON DELETE CASCADE
);

分类
- 级联更新：ON UPDATE CASCADE
- 级联删除：ON DELETE CASCADE

添加级联

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE

总结:
- 添加约束的时候,建议创建表的时候添加约束 , 不建议大家删除约束
- 级联尽量避免设置级联效率低
- 设置外键一般不设置外键(建立逻辑外键,不建立实际外键)

3.数据库的设计

3.1 多表之间的关系(了解)

3.1.1 分类

一对一(了解)

* 如：人和身份证
* 分析：一个人只有一个身份证，一个身份证只能对应一个人

一对多(多对一)

* 如：部门和员工
* 分析：一个部门有多个员工，一个员工只能对应一个部门

多对多

* 如：学生和课程
* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

3.1.2 实现关系