Myspl数据库：
数据库：
-
数据库【按照数据结构来组织、存储和管理数据的仓库】。是一个长期储存在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。
-
数据对于公司来说是最宝贵的财富，程序员工作就是对数据进行管理，包括运算、流转、存储、展示等，数据库最重要的功能就是【存储数据】，长期保存数据。
Mysql：
- Mysql是一个【关系型数据库管理系统】，瑞典公司开发，被【Oracle】收购
- Mysql使用了一种语言【SQL语言】。
- Mysql分为社区版和商业版，体积小、速度快、成本低、开源。
基本操作：
MySQL保存数据的模式：
- 创建一个数据库。
- 在数据库下保存多张表。
- 在每张表中保存多条数据。
登录mysql
mysql-h 127.0.0.1-p3306-uroot-p
Mysql是一个数据库管理系统，管理多个数据库。
创建数据库：
create database 数据库名
create schema 数据库名
查看所有的数据库：
show databases 查看数据库们
使用数据库：
use 数据库名
表：
用来存储数据的对象，是有结构的数据的集合。
- 行：一行即为一条数据，数据库一共有多少条数据，实际就是有几行数据。
- 列：一列即为一个字段，数据库一共有多少个字段，实际上就是有几列数据。
SQL语言：
SQL是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存储数据以及查询、更新和管理关系型数据库系统。
SQL分类：
- DCL：数据控制语言，用来定义访问权限和安全级别。
- DDL：数据定义语言，用来定义数据库对象：库，表，字段等。功能：创建、删除、修改库和表结构。
- DML：数据操作语言，用来定义数据的增删改记录。
- DQL：数据库查询语言，用来记录查询。
- TCL：事务控制语言，用来管理事务。
DCL（数据库控制语言）
创建用户：
创建一个用户，该用户只能在指定的ip地址登录mysql：
create user 用户名@IP地址 identified by ‘密码’;
创建一个用户，该用户可以在任意的ip地址登录mysql：
create user 'moon'@'%' identified by ‘密码’;
修改密码：
--5.7版本需要使用password对密码进行加密--
set passwword for 'moon'@'%'=password('新密码');
--8.0版本直接赋值
set passwword for 'moon'@'%'='新密码';
给用户授权：
给指定用户在指定数据库赋予指定权限，权限有很多，列举几个常用的：
- create：可以创建数据库
- select：查询数据
- delete：删除数据
- update：修改数据
- insert：插入数据
--语法：grant '权限一，权限二...权限n' on 数据库名.* to 用户名@IP地址
用的是` `着重符，不是单引号。
撤销授权：
--语法：revoke 权限一，权限二...权限n' on 数据库名.* from 用户名@IP地址
查看权限：
查看指定用户权限：
--语法：show grants for 用户名@Ip地址
删除用户：
--语法：drop user 用户名@IP地址
DDL（数据定义语言）：
DDL主要是用在定义或改变表的结构，
创建表：
create table 表名(
字段名1(列名) 类型(长度) 约束条件，
字段名2(列名) 类型(长度) 约束条件，·
字段名3(列名) 类型(长度) 约束条件，
......
);
在关系型数据库中，我们需要设定表名和列名，同时设定。
数据类型：
整型：
| MySQL数据类型 | 含义 |
|---|---|
| tinyint | 1字节，范围(-128~127) |
| smallint | 2字节，范围(-32768~32767) |
| mediumint | 3字节，范围(-8388608~8388607) |
| int | 4字节，范围(-2147483648~2147483647) |
| bigint | 8字节，范围(-9223372036854775808~9223372036854775807) |
在整型中，我们默认使用的是【有符号的】，我们可以使用unsigned关键字，定义成无符号类型，tinyint unsigned的取值范围0~255.
如果长度需要配合zerofill：
int(4) unsigned zerofill;
说明：上述int长度为4，如果设置了zerofill，如果数据是1，最终存到表格中的数据格式为0001，0010.
浮点型
| MySQL数据类型 | 含义 |
|---|---|
| float(m,d) | 4字节，单精度浮点型，m总长度，d小数位。 |
| double(m,d) | 8字节，双精度浮点型，m总长度，d小数位。 |
| decimal(m,d) | decimal是存储为字符串的浮点数，对应我们Java中的BigDecimal。 |
比如定义一个float(5,3):
- 插入123.45678，最后查询得到99.999；
- 插入12.3456789，最后查询得到12.346；
- 所以在使用浮点型时，要以插入到数据库的实际结果为准。
字符串类型：
| MySQL数据类型 | 含义 |
|---|---|
| char(n) | 固定长度，最多255个字节。 |
| varchar(n) | 可变长度，最多65535个字节。 |
| tinytext | 可变长度，最大255个字符。 |
| text | 可变长度，最大65535个字符。 |
| mediumtext | 可变长度，最大16MB。 |
| longtext | 可变长度，最大4GB。 |
（1）char和varchar区别：
- char类型是【定长】的类型，当定义char(10),输入的是“123”，他们占用的空间依然是10个字符。当输入的字符如果超出指定的范围，char会截取超出的字符。而且，当存储char，MySQL会自动删除输入字符串末尾的空格。
- char适合存储很短的，一般固定长度的字符串。例如：char非常适合存储密码MD5值，因为他是一个定长的值。对于短的列，char比varchar在存储空间上效率更高。
- varchar(n)类型用来存储可变长度，长度最大为n个字符的可变长度的字符串数据。比如varchar(10)，然后存储“abc”，就是存储了3个字符。
- char类型每次修改的数据长度相同，效率更高。varchar，每次修改的数据长度如果不同，效率更高。
（2）varchar和text区别：
- text不能设置默认值，varchar可以设置默认值。
- text类型，由于单表的最大行宽的限制，支持溢出存储，只会存放768字节在数据页中，剩余的数据存储在溢出段中。
- 一般我们都是用varchar。
日期类型
| MySQL数据类型 | 含义 |
|---|---|
| date | 3字节，日期，格式：2022-08-15 |
| time | 3字节，时间，格式：10：54：30 |
| datatime | 8字节，日期时间，格式2022-08-25 10：55：30 |
| timestamp | 4字节，时间戳，毫秒数 |
| year | 1字节，年份 |
建表约束
因为一张表要有多个列，数据库中的表不止有一张，建表约束说的是应该如何规范数据以及表之间的关系。
MySQL约束类型
| 约束名称 | 描述 |
|---|---|
| NOT NULL | 非空约束 |
| UNIQUE | 唯一约束，取值不允许重复 |
| PRIMARY KEY | 主键约束(主关键字)，自带非空，唯一，索引 |
| DEFAULT | 默认值 |
| FOREIGH KEY | 外键约束，表和表之间的约束。 |
（1）NOT NULL约束
CREATE TABLE `student` (
`stu_id` int,
`stu_name` VARCHAR(50) NOT NULL,
`gender` char(1) DEFAULT '男',
`brithday` datetime,
PRIMARY KEY(stu_id)
);
（2）UNIQUE约束
create table `book` (
`id` int PRIMARY KEY auto_increment,
`name` varchar(50) not null,
`bar_code` VARCHAR(30) not null,
`aut_id` int not null,
UNIQUE(bar_code)
);
（3）主键约束
用多个列来共同当主键：
create table `author`(
`aut_id` int,
`aut_name` varchar(50) not null,
`gender` char(1) default '男',
`country` varchar(50),
`birthday` datetime,
PRIMARY KEY(aut_id,aut_name)
);
（4）外键约束
推荐配合主键去使用。有了这个约束，我们在向表中插入数据时，来源于另外一张表的主键
外键会产生的效果：
- 删除表的时候，如果不删除引用外键的表，被引用的表是不能直接删除。
- 外键的值必须来源于引用的表的主键字符。
create table `author`(
`aut_id` int,
`aut_name` varchar(50) not null,
`gender` char(1) default '男',
`country` varchar(50),
`birthday` datetime,
PRIMARY KEY(aut_id)
);
create table `book` (
`id` int PRIMARY KEY auto_increment,
`name` varchar(50) not null,
`bar_code` VARCHAR(30) not null UNIQUE,
`aut_id` int not null,
FOREIGN KEY(aut_id) REFERENCES author(aut_id)
);
在创建表的时候，建议字段名使用着重符。
对表的修改操作：
查看当前库中的所有表：
show tables;
查看表结构：
desc 表名;
修改表有5个操作，但是前缀都是一样的
alter table 表名...
- 添加列：
ALTER table author add (hobby varchar(20),address varchar(50));
- 修改列的数据类型：
ALTER table author MODIFY address varchar(100);
- 修改列名称和数据类型：
alter table author change address addr VARCHAR(60);
- 删除列：
alter table author drop addr;
- 修改表名：
ALTER TABLE author RENAME `authors`;
- 删除表：
drop table if EXISTS `user`;
- 添加主键
alter table 表名 ADD CONSTRAINT 主键名(pk_表名) primary key 表名(字段名);
ALTER TABLE `authors` ADD CONSTRAINT pk_authors PRIMARY KEY `authors` ( aut_id );
DML(数据操作语言)：
该语言来对表记录进行操作（增删改），不包含查询。
插入数据：
INSERT INTO `authors` ( aut_id, aut_name, gender, country, birthday, hobby ) VALUES (4,'罗曼罗兰','女','漂亮国','1945-8-15','写字');
如果插入的是全字段，字段名可以省略。
INSERT INTO `authors` VALUES (5,'韩寒','男','中国','1984-8-15','赛车');
说明：
- 在数据库中所有的字符串类型，必须使用引导。
- 如果部分字段插入，必须列名和值要匹配。如果全字段插入，则列名可以省略。
批量插入（MySQL特有）:
INSERT INTO `authors` VALUES
(7,"李诞",'男','中国','1985-8-15','脱口秀'),
(8,"史铁生",'男','中国','1967-8-15','绘画');
修改数据：
修改某列的全部的值。
update `authors` set aut_name = '郭小四',country='中国';
修改特定的行的数据：
update `authors` set aut_name = '金庸',country='中国' where aut_id = 1;
where是一个关键字，我们可以使用where关键字实现丰富的筛选，很像if语句，可以使用各种复杂的条件运算：
-
=
-
！=
-
>
-
<
-
>=
-
<=
-
<>不等于
-
between...and
- where aut_id between 1 and 4
- where aut_id>1 and aut_name='xxx'
删除数据：
全部删除：
delete from `student`;
根据条件删除：
delete from `authors` where aut_id = 8;
说明：通过delete这种方式删除的数据，主键如果是自动递增，会断档。
截断（清空表）：
TRUNCATE student;
说明：
truncate实际上应该属于DDL语言，操作立即生效，不能撤回。
- truncate和delete都是删除数据，drop删除整个表。
- truncate速度快，效率高，可以理解为直接删除整个表，再重新建立。
- truncate和delete都不会使表结构及其列，约束，索引等发生改变。
DQL(数据库查询语言)：
重点：DQL是我们每天都要接触编写最多也是最难的SQL，该语言用来查询记录,不会修改数据库和表结构。
构建数据库：
创建一张student表：
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT(10) PRIMARY KEY,
`name` VARCHAR(10),
age INT(10) NOT NULL,
gender VARCHAR(2)
);
构建一张course表：
DROP TABLE IF EXISTS course;
CREATE TABLE course(
id INT(10) PRIMARY KEY,
`name` VARCHAR(10),
t_id INT(10)
);
构建一张teacher表：
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher(
id INT(10) PRIMARY KEY,
`name` VARCHAR(10)
);
构建一个score表：
DROP TABLE IF EXISTS score;
CREATE TABLE scores(
s_id INT(10),
score INT(10),
c_id INT(10),
PRIMARY KEY(s_id,c_id)
);
表格填充数据：
insert into student (id,name,age,gender)VALUES(1,'小明',19,'男'),(2,'小红',19,'男'),(3,'小刚',24,'男'),(4,'小龙',11,'男'),(5,'小丽',18,'男'),(6,'小军',18,'女'),(7,'小航',16,'男'),(8,'小亮',23,'男'),(9,'小杰',22,'女'),(10,'小虎',21,'男');
insert into course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);
insert into teacher (id,name)VALUES(1,'Tom'),(2,'Jerry'),(3,'Tony'),(4,'Jack'),(5,'Rose');
insert into scores (s_id,score,c_id)VALUES(1,80,1);
insert into scores (s_id,score,c_id)VALUES(1,56,2);
insert into scores (s_id,score,c_id)VALUES(1,95,3);
insert into scores (s_id,score,c_id)VALUES(1,30,4);
insert into scores (s_id,score,c_id)VALUES(1,76,5);
insert into scores (s_id,score,c_id)VALUES(2,35,1);
insert into scores (s_id,score,c_id)VALUES(2,86,2);
insert into scores (s_id,score,c_id)VALUES(2,45,3);
insert into scores (s_id,score,c_id)VALUES(2,94,4);
insert into scores (s_id,score,c_id)VALUES(2,79,5);
insert into scores (s_id,score,c_id)VALUES(3,65,2);
insert into scores (s_id,score,c_id)VALUES(3,85,3);
insert into scores (s_id,score,c_id)VALUES(3,37,4);
insert into scores (s_id,score,c_id)VALUES(3,79,5);
insert into scores (s_id,score,c_id)VALUES(4,66,1);
insert into scores (s_id,score,c_id)VALUES(4,39,2);
insert into scores (s_id,score,c_id)VALUES(4,85,3);
insert into scores (s_id,score,c_id)VALUES(5,66,2);
insert into scores (s_id,score,c_id)VALUES(5,89,3);
insert into scores (s_id,score,c_id)VALUES(5,74,4);
insert into scores (s_id,score,c_id)VALUES(6,80,1);
insert into scores (s_id,score,c_id)VALUES(6,56,2);
insert into scores (s_id,score,c_id)VALUES(6,95,3);
insert into scores (s_id,score,c_id)VALUES(6,30,4);
insert into scores (s_id,score,c_id)VALUES(6,76,5);
insert into scores (s_id,score,c_id)VALUES(7,35,1);
insert into scores (s_id,score,c_id)VALUES(7,86,2);
insert into scores (s_id,score,c_id)VALUES(7,45,3);
insert into scores (s_id,score,c_id)VALUES(7,94,4);
insert into scores (s_id,score,c_id)VALUES(7,79,5);
insert into scores (s_id,score,c_id)VALUES(8,65,2);
insert into scores (s_id,score,c_id)VALUES(8,85,3);
insert into scores (s_id,score,c_id)VALUES(8,37,4);
insert into scores (s_id,score,c_id)VALUES(8,79,5);
insert into scores (s_id,score,c_id)VALUES(9,66,1);
insert into scores (s_id,score,c_id)VALUES(9,39,2);
insert into scores (s_id,score,c_id)VALUES(9,85,3);
insert into scores (s_id,score,c_id)VALUES(9,79,5);
insert into scores (s_id,score,c_id)VALUES(10,66,2);
insert into scores (s_id,score,c_id)VALUES(10,89,3);
insert into scores (s_id,score,c_id)VALUES(10,74,4);
insert into scores (s_id,score,c_id)VALUES(10,79,5);
单表查询：
基本查询：
基本语法：
查询所有列：
select * from 表名;
查询指定的列：
select id,`name`,age from 表名;
开发中严禁使用select * 。
如果表中有完全重复的记录，在查询之前加上distinct，重复的记录只显示一次。
select distinct id from 表名;
列运算：
select id,`name`,age/10 from 表名;
我们写的所有的查询语句，最终执行的结果，都是生成一张虚拟表。
select id,`name`,age+1000 from 表名;
注意：
1、null与任何值做运算结果都为null，需要用到函数IFNULL()，如果薪资是空，则为0.
2、将字符串做加减乘除运算，会把字符串当0处理。
select ifnull(sal,0)+1000 from employee;
别名：
我们可以给列起【别名】，因为在查询过程中，列名很可能重复，可能名字不够简洁，或者列的名字不能满足我们的要求。
select id `编号`,`name` `姓名`,age `年龄`,gender `性别` from student;
select id as `编号`,`name` as `姓名`,age as `年龄`,gender as `性别` from student;
条件控制：
条件查询：在后面添加where指定条件。
select * from student where id = 3;
select * from student where id in (1,3,5);
select * from student where id > 2;
select * from student where id BETWEEN 3 and 5;
select * from student where id BETWEEN 6 and 7 or age > 20;
模糊查询：查询所有姓张的
select * from student where `name` like '张%';
select * from student where `name` like '张_';
select * from student where `name` like '%明%';
select * from student where `name` like '_明_';
通配符：_下划线代表一个字符，%百分号代表任意个字符。
排序：
- 升序
select * from student order by age ASC;
--ASC可以省略
- 降序
select * from student order by age DESC;
--DESC不可以省略
- 使用多个列作为排序条件：当第一个排序条件相同时，根据第二列排序条件进行排序（第二列如果还相同，......）
select * from student order by age ASC,id desc;
举例：
创建一张用户表，id，username，password。
几乎所有的表都会有两个字段，create_time,update_time.
几乎所有的查询都会按照update_time降序排列。
聚合函数：
count：
查询满足条件的记录行数，后边可以跟where条件。
如果满足条件的行数为空，不会进行统计。
如果我们要统计真实有效的记录数，最好不要用可以为空列。
- count(*)
- count(主键)(推荐)
- count(1)(不推荐)
select count(列名) from 表名;
max：
查询满足条件的最大记录值，后面可以跟where条件。
select max(列名) from 表名 where 条件;
min：
查询满足条件的记录中的最小值，后面可以跟where条件。
select min(列名) from 表名 where 条件;
sum：
查询满足条件的记录的和，后面可以跟where条件。
select sum(列名) from 表名 where 条件;
avg：
查询满足条件的记录的平均数，后面可以跟where条件。
select avg(列名) from 表名 where 条件;
分组查询：
顾名思义：分组查询就是将原有的数据进行分组统计。
举例：
将班级的同学按照性别分组，统计男生和女生的平均年龄.
select 分组列名,聚合函数1,聚合函数2... from 表名 group by 该分组列名;
分组要使用关键词group by，后面可以是一列，也可以是多个列，分组后查询的列只能是分组的列，或者是使用了聚合函数的其他的列，剩余的列不能单独使用。
-- 根据性别分组，查看每一组的平均年龄和最大年龄
select gender,avg(age),max(age) from student group by gender;
-- 根据专业号分组，查看每一个专业的平均分
select c_id,avg(score) from scores group by c_id;
一旦发生分组，我们查询的结果只能是所有男生的年龄平均值、最大值，而不能是某一个男生的数据。
分组查询前，可以通过关键字【where】先把满足条件的人分出来，再分组。
select 分组列,聚合函数1... from 表名 where 条件 group by 分组列;
select c_id,avg(score) from scores where c_id in (1,2,3) group by c_id;
分组查询后，也可以通过关键字【having】把组信息中满足条件的组再细分出来。
select 分组列,聚合函数1... from 表名 where 条件 group by 分组列 having 聚合函数或列名（条件）;
select gender,avg(age),sum(age) `sum_age` from student GROUP BY gender HAVING `sum_age` > 50;
面试题：
where和having的区别？
1、where是写在group by之前的筛选，在分组前筛选；having是写在group by之后，分组后再筛选。
2、where只能使用分组的列作为筛选条件；having既可以使用分组的列，也可以使用聚合函数列作为筛选条件。
分页查询：
limit字句，用来限定查询结果的起始行以及总行数。MYSQL独有的。
select * from student limit 4,3;
select * from student limit 4;
- 如果只有一个参数，说明从起始位置查找4条记录。
- 如果有两个参数，说明从第4行下一行，向后查找3条记录。
面试题：
- MySQL：limit
- Oracle：rownum
- SqlServer：top
分析：
student表中有10条数据，如果每页4条，分几页？怎么分？
3页，(int)(Math.ceil(10/4));
显示第一页数据：select * from student limit 0,4;
第二页：select * from student limit 4,4;
第三页：select * from student limit 8,4;
一个问题：判断在student表中有没有叫“小红”的这个人？
1.0版本
select * from student where name = '小红';
select id from student where name = '小红';
2.0版本
select count(id) from student where name = '小红';
3.0版本
select id from student where name = '小红' limit 1;
注意：Limit字句永远是在整个的sql语句的最后。
多表查询：
笛卡尔积：
select * from student,teacher;
如果两个表没有任何关联关系，我们也不会连接这两张表。
出现笛卡尔乘积，会生成一张虚拟表，这张虚拟表的数据就是表一和表二数据的乘积。
注意：开发中，一定要避免出现笛卡尔积。
多表连接的方式有四种：
- 内连接
- 外连接
- 全连接
- 子查询
SQL92语法：
1992年的语法：
select stu.id,stu.name,stu.age,sc.score from student stu,scores sc where stu.id = sc.s_id;
select stu.id,stu.name,stu.age,sc.score,c.name from student stu,scores sc,course c where stu.id = sc.s_id and c.id=sc.c_id;
SELECT
stu.id 学号,
stu.name 姓名,
stu.age 年龄,
sc.score 分数,
c.name 科目
FROM
student stu,
scores sc,
course c
WHERE
stu.id = sc.s_id
AND c.id = sc.c_id
AND
stu.name in('小红','小军');
SELECT
sc.c_id,
MAX( score ),
MIN( score ),
c.NAME
FROM
student stu,
scores sc,
course c
WHERE
sc.c_id = c.id
GROUP BY
sc.c_id;
SQL92语法，多表查询，如果数据为空，会自动过滤掉。
SQL99语法：
1999年的语法：
内连接：
在我们刚才的sql当中，使用逗号分隔两张表进行查询，mysql进行优化默认就等效于内连接，使用join关键字，使用on来确定连接条件。where只做筛选条件。
select
t.*,c.*,sc.*
from
teacher t,
join course c on c.t_id = t.id
join scores sc on sc.c_id = c.id;
外连接（常用）：
内连接与外连接的区别：
-
对于【内连接】的两个表，如果【驱动表】在【被驱动表】找不到与之匹配的记录，则最终的记录不会出现在结果集中。
-
对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中。针对不同的【驱动表】的位置，分为【左外连接】和【右外链接】。
-
对于左连接，左边的表为主，左边的表的记录会完整的出现在结果集里。
-
对于右连接，右边的表为主，左边的表的记录会完整的出现在结果集里。
左连接：
SELECT
t.*,
c.*
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id;
右连接：
SELECT
t.*,
c.*
FROM
course c
RIGHT JOIN teacher t ON t.id = c.t_id;
全连接：
mysql不支持全连接。oracle支持全连接。
SELECT
*
FROM
teacher t
FULL JOIN course c ON c.t_id = t.id;
可以通过一些手段实现全连接的效果
SELECT
t.*,
c.*
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
UNION
SELECT
t.*,
c.*
FROM
teacher t
RIGHT JOIN course c ON t.id = c.t_id
需求：
-- 1.查询'01'号学生的姓名和各科成绩 **
SELECT
s.id sid,
s.`name` sname,
c.`name` cname,
sc.score
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
s.id = 1;
-- 2.查询各个学科的平均成绩和最高成绩**
SELECT
c.id,
c.`name`,
AVG( sc.score ),
max( sc.score )
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
c.id,
c.`name`;
-- 3.查询每个同学的最高成绩和科目名称****（明天说，子查询）
select t.id,t.name,c.id,c.name,r.score from
(select s.id,s.name,
(select max(score) from scores sc where r.s_id = s.id)
score from student s) t
left join scores r on r.s_id =t.id and r.score = t.score
left join course c on r.c_id = c.id;
-- 4.查询所有姓张的同学的各科成绩**
SELECT
s.id,
s.`name`,
c.`name` cname,
sc.score
FROM
SELECT
s.id,
s.`name`,
c.`name` cname,
sc.score
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
s.`name` LIKE '张%';
-- 5.查询每个课程的最高分的学生信息*****（明天说，子查询）
-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
SELECT
s.*,
c.NAME 科目,
sc.score 分数
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
WHERE
s.NAME LIKE '%张%'
OR s.NAME LIKE '%李%';
-- 7.查询平均成绩及格的同学的信息。（子查询）
SELECT
s.*,
avg( sc.score )
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
GROUP BY
s_id
HAVING
avg( sc.score )>= 60
ORDER BY
avg( sc.score ) DESC;
-- 8.将学生按照总分数进行排名。（从高到低）
SELECT
s.*,
sum( score )
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
GROUP BY
s_id
ORDER BY
sum( score ) DESC;
-- 9.查询数学成绩的最高分、最低分、平均分。
SELECT
c.NAME,
max( sc.score ),
min( sc.score ),
avg( sc.score )
FROM
course c
LEFT JOIN scores sc ON sc.c_id = c.id
GROUP BY
NAME
HAVING
c.NAME = '数学';
-- 10.将各科目按照平均分排序。
SELECT
c.NAME,
avg( sc.score )
FROM
course c
LEFT JOIN scores sc ON sc.c_id = c.id
GROUP BY
c.name
ORDER BY
avg( sc.score ) DESC;
子查询：
按照结果集的行列数不同，子查询可以分为以下几类：
- 标量子查询：结果集只有一行一列。
- 列子查询：结果集有一列多行。
- 行子查询：结果集有一行多列。
- 表子查询：结果集多行多列。
-- 查询比小虎年龄大的所有学生
-- 标量子查询
select * from student where age >
(select age from student where name = '小虎');
-- 查询一门分数大于90分的学生
-- 列子查询
select * from student where id in
(select s_id from scores where score > 90);
-- 查询男生且年龄最大的学生
-- 行子查询
select * from student where age =
(select max(age) from student group by gender having gender = '男');
总结：
- where型子查询，如果是where列 = (内层sql)，则内层的sql返回的必须是单行单列，单个值。
- where型子查询，如果是where(列1，列2) = （内层sql），内层返回的sql必须是单行，可以是多列。
-- 取数学成绩前五的学生信息
select s.*,sc.score,c.name
from student s
left join scores sc on s.id = sc.c_id
where c.name = '数学'
order by score desc
limit 5;
-- 取数学成绩前五的男生信息
select * from
(select s.*,sc.score,c.name
from student s
left join scores sc on s.id = sc.c_id
where c.name = '数学'
order by score desc
limit 5) t
where t.gender ='男';
经验分享：
1.分析需求
2.拆步骤
3.分步写sql
4.整合拼装sql
-- 查询每个学科参加考试的人数
select count(*),c_id from scores GROUP BY c_id
select t.id,t.name,count(*) '代课的数量'
from teacher t LEFT JOIN course c
ON c.t_id = t.id GROUP BY t.id,t.name;
select t.id t.name,(
select count(*) from course c where c.id = t.id
) as 代课的数量
from teacher t;
总结：如果一个需求可以不用子查询，尽量不使用，sql可读性太低。
-- 11.查询老师的信息和他所带的科目的平均分
SELECT t.*,AVG(s.score),c.name
FROM teacher t
LEFT JOIN course c ON c.t_id = t.id
LEFT JOIN scores s ON s.c_id = c.id
GROUP BY t.id;
-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
SELECT t.*,MAX(s.score),MIN(s.score),c.name
FROM teacher t
LEFT JOIN course c ON c.t_id = t.id
LEFT JOIN scores s ON s.c_id = c.id
GROUP BY t.id
HAVING t.name = 'Tom' OR T.name = 'Jerry';
-- 13.查询每个学生的最好成绩的科目名称（子查询）
SELECT n.id,n.name,n.score,c.name
FROM (
SELECT stu.id,stu.name,max(s.score) score FROM student stu
LEFT JOIN scores s ON s.s_id = stu.id
GROUP BY stu.id) n
LEFT JOIN scores s ON s.s_id = n.id and s.score = n.score
LEFT JOIN course c ON s.c_id = c.id;6
-- 14.查询所有学生的课程及分数
SELECT stu.name,c.name,s.score
FROM scores s
LEFT JOIN course c ON s.c_id = c.id
LEFT JOIN student stu ON s.s_id = stu.id
GROUP BY stu.id,c.id;
-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
SELECT stu.id,stu.name,c.id,s.score
FROM scores s
LEFT JOIN course c ON s.c_id = c.id
LEFT JOIN student stu ON s.s_id = stu.id
where c.id = 1 AND s.score > 60
ORDER BY s.score DESC;
-- 16.查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
select s.id,s.name,AVG(sc.score)
FROM student s
LEFT JOIN scores sc ON sc.s_id = s.id
GROUP BY s.id
HAVING AVG(sc.score) >= 70;
-- 17.查询有不及格课程的学生信息
SELECT s.*,sc.score,c.`name`
FROM scores sc
LEFT JOIN student s ON s.id = sc.s_id
LEFT JOIN course c ON c.id = sc.c_id
WHERE sc.score < 60;
-- 18.查询每门课程有成绩的学生人数
SELECT
COUNT( s.id )
FROM
scores sc
LEFT JOIN student s ON s.id = sc.s_id
LEFT JOIN course c ON c.id = sc.c_id
GROUP BY
c.name;
-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，在按照课程编号升序
SELECT AVG(sc.score),c.name,c.id
FROM scores sc
LEFT JOIN course c ON sc.c_id = c.id
GROUP BY c.name
ORDER BY AVG(sc.score) DESC,c.id;
-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
select s.id,s.name,AVG(sc.score)
FROM student s
LEFT JOIN scores sc ON sc.s_id = s.id
GROUP BY s.id
HAVING AVG(sc.score) > 60;
-- 21.查询有且仅有一门成绩在80分以上的学生信息
SELECT s.*
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
WHERE sc.score >= 80
GROUP BY s.id
HAVING COUNT(sc.score) = 1 ;
-- 22.查询只有三门课程的学生的学号和姓名
SELECT s.id,s.`name`
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
GROUP BY s.id
HAVING COUNT(sc.score) = 3 ;
-- 23.查询有不及格课程的课程信息
SELECT c.*
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
WHERE sc.score < 60
GROUP BY c.id
ORDER BY c.id ;
-- 24.查询至少选择四门课程的学生信息
SELECT s.id,s.`name`
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
GROUP BY s.id
HAVING COUNT(sc.score) >= 4 ;
-- 25.查询没有学全所有课程的同学的信息
SELECT s.id,s.`name`
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
GROUP BY s.id
HAVING COUNT(sc.score) < 5;
-- 26.查询选全所有课程的同学的信息
SELECT s.id,s.`name`
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
GROUP BY s.id
HAVING COUNT(sc.score) = 5;
-- 27.查询各学生都选了多少门课
SELECT s.id,s.`name`,COUNT(sc.score)
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
GROUP BY s.id;
-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT s.id,s.`name`,c.name,sc.score
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
GROUP BY s.id,c.`name`
HAVING c.`name` = 'java' AND sc.score < 60;
-- 29.查询学过"Tony"老师授课的同学的信息
SELECT s.id,s.`name`,t.name
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
LEFT JOIN teacher t ON t.id = c.t_id
GROUP BY s.id,t.id
HAVING t.name = 'tom';
-- 30.查询没学过"Tony"老师授课的学生信息
SELECT s.id,s.`name`,t.name
FROM scores sc
LEFT JOIN student s ON sc.s_id = s.id
LEFT JOIN course c ON sc.c_id = c.id
LEFT JOIN teacher t ON t.id = c.t_id
GROUP BY s.id
HAVING t.name != 'tom';
日期格式：
| 格式 | 描述 |
|---|---|
| %a | 缩写的星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值（00-31） |
| %e | 月的天，数值（0-31） |
| %f | 微秒 |
| %H | 小时（00-23） |
| %h | 小时（01-12） |
| %I | 小时（01-12） |
| %i | 分钟，数值（00-59） |
| %j | 年的天（001-366） |
| %k | 小时（0-23） |
| %l | 小时（1-12） |
| %M | 月名 |
| %m | 月，数值（00-12） |
| %p | AM或PM |
| %r | 时间，12-小时 （hh:mm:ss AM或PM） |
| %S | 秒（00-59） |
| %s | 秒（0-59） |
| %T | 时间，24-小时（hh:mm:ss） |
| %U | 周（00-53）星期日是一周的第一天 |
| %u | 周（00-53）星期一是一周的第一天 |
| %W | 星期名 |
| %Y | 年，2022 |
| %y | 年，22 |
MySQL常用函数
聚合函数
- count：计数。count(*)≈count(1)>count(主键)
- count(*)：MySQL对count(*)底层优化，count(0)。
- count(1)
- count(主键)
- count(字段)
- min：最小值
- max：最大值
- sum：求和
- avg：平均值
数值型函数
主要是对数值型进行处理。
- ceiling(x)：向上取整
- floor(x)：向下取整
- round(x)：四舍五入
- truncate(x,y)：返回数字x截断为y位小数的结果
- PI：圆周率，π
- rand：返回0到1的随机数
- abs：绝对值
-- 绝对值
select ABS(-4) 4的绝对值,ABS(-1.1);
-- 向下取整，向上取整，四舍五入
select CEILING(4.1),FLOOR(1.1),ROUND(-4.4)
-- 取余
select MOD(60,11);
-- 随机数
select RAND(),RAND(),RAND()
-- 截断
select TRUNCATE(2.33999999,2);
字符串型函数
对字符串进行处理。
- length(s)：字符串的长度
- concat(s1,s2,.....sn)：合并字符串
- lower(str)：将字母转成小写
- upper(str)：将字母转成大写
- left(str,x)：返回字符串str的左边的x个字符
- right(str,x)：返回字符串str右边的x个字符
- trim：去掉左右两边的空格
- replace：替换
- substring：截取
- reverse：反转
select LEFT('abcdefg',2);
select RIGHT('abcdefg',2);
select REVERSE('hijklmn');
select REPLACE('abcdefg','abc','x');
日期和时间函数
date,time,datetime,timestamp,year。
获取时间和日期
- 【curdate】和【current_date】，返回当前的系统日期。
- 【curtime】和【current_time】，返回当前的系统时间。
- 【now】和【sysdate】，返回当前的系统时间和日期。
select CURRENT_DATE();
select CURTIME();
select now();
时间戳和日期转换函数
- 【UNIX_TIMESTAMP】获取unix时间戳函数
- 【FROM_UNIXTIME】将时间戳转换为时间格式
select UNIX_TIMESTAMP();
select FROM_UNIXTIME(1660785720);
根据日期获取年月日的数值
select MONTH(SYSDATE());
select MONTHNAME(SYSDATE());
select DAYNAME(SYSDATE());
select DAYOFWEEK(SYSDATE());
select WEEK(SYSDATE());
select DAYOFMONTH(SYSDATE());
select YEAR(SYSDATE());
时间日期的计算
-- 日期加法
select DATE_ADD(SYSDATE(),INTERVAL 70 DAY);
-- 日期减法
select DATE_SUB(SYSDATE(),INTERVAL 10 DAY);
-- 时间间隔
select DATEDIFF('2023-01-01',SYSDATE());
-- 日期格式化
select DATE_FORMAT(SYSDATE(),'%W %M %D %Y');
加密函数
-- 把传入的参数的字符串按照md5算法进行加密，得到一个32位的16进制的字符串
select MD5('123456');
md5算法是不可逆的。
流程控制函数
可以进行条件判断，用来实现SQL语句的逻辑。
- if(test,t,f)：如果test是真，则返回t，否则返回f
- ifnull(arg1,arg2)：如果arg1不是空，返回arg1，否则返回arg2
- nullif(arg1,arg2)：如果arg1=arg2返回null，否则返回arg1
select IF(2 > 1,'a','b');
select IFNULL(sal,0);
select NULLIF(age,0);
对一系列的值进行判断：
-- 输出学生的各科的成绩，以及评级，60以下D，60-70是C，71-80是B，80以上是A
SELECT
*,
CASE
WHEN score < 60 THEN 'D' WHEN score >= 60
AND score < 70 THEN 'C' WHEN score >= 70
AND score < 80 THEN 'B' WHEN score >= 80 THEN
'A'
END AS '评级'
FROM
mystudent;
-- 行转列
SELECT
user_name,
max( CASE course WHEN '数学' THEN score ELSE 0 END ) '数学',
max( CASE course WHEN '语文' THEN score ELSE 0 END ) '语文',
max( CASE course WHEN '英语' THEN score ELSE 0 END ) '英语'
FROM
mystudent
GROUP BY
user_name
数据库设计
三范式
- 第一范式：要求有主键，并且要求每一个字段的原子性不能再分。
- 第二范式：要求所有的非主键字段完全依赖主键，不能产生部分依赖
- 第三范式：所有非主键字段和主键字段之间不能产生传递依赖。
第一范式
不符合第一范式表结构：
| id | name | 联系方式 |
|---|---|---|
| 1001 | aaa | [[email protected] , 13314569878](mailto:[email protected] , 13314569878) |
| 1002 | bbb | [[email protected] , 13245678945](mailto:[email protected] , 13245678945) |
| 1003 | ccc | [[email protected] , 15000456987](mailto:[email protected] , 15000456987) |
符合第一范式的表结构：
| id | name | 邮箱 | 手机号 |
|---|---|---|---|
| 1001 | aaa | [email protected] | 12321321321 |
| 1002 | bbb | [email protected] | 32132654654 |
| 1003 | ccc | [email protected] | 45654654654 |
必须有主键，这是数据库设计的基本要求，一般情况下我们采用数值型或定长字符串，列不能再分，比如：联系方式。
关于第一范式，保证每一行的数据是唯一，每个表必须有主键。
第二范式
建立在第一范式的基础上，要求所有非主键字段完全依赖于主键，不能产生部分依赖。
| 学号 | 性别 | 姓名 | 课程编号 | 课程名称 | 教室 | 成绩 |
|---|---|---|---|---|---|---|
| 1001 | 男 | a | 2001 | java | 301 | 89 |
| 1002 | 女 | b | 2002 | mysql | 302 | 90 |
| 1003 | 男 | c | 2003 | html | 303 | 91 |
| 1004 | 男 | d | 2004 | python | 304 | 52 |
| 1005 | 女 | e | 2005 | c++ | 305 | 67 |
| 1006 | 男 | f | 2006 | c# | 306 | 84 |
解决方案：
学生表：学号是主键
| 学号 | 性别 | 姓名 |
|---|---|---|
| 1001 | 男 | a |
| 1002 | 女 | b |
| 1003 | 男 | c |
| 1004 | 男 | d |
| 1005 | 女 | e |
| 1006 | 男 | f |
课程表：课程编号是主键
| 课程编号 | 课程名称 | 教室 |
|---|---|---|
| 2001 | java | 301 |
| 2002 | mysql | 302 |
| 2003 | html | 303 |
| 2004 | python | 304 |
| 2005 | c++ | 305 |
| 2006 | c# | 306 |
成绩表：学号和课程编号为联合主键
| 学号 | 课程编号 | 成绩 |
|---|---|---|
| 1001 | 2001 | 89 |
| 1002 | 2002 | 90 |
| 1003 | 2003 | 91 |
| 1004 | 2004 | 52 |
| 1005 | 2005 | 67 |
| 1006 | 2006 | 84 |
第三范式
建立在第二范式基础上，非主键字段不能传递依赖于主键字段。
不满足第三范式：
| 学号 | 姓名 | 课程编号 | 课程名称 |
|---|---|---|---|
| 1001 | a | 2001 | java |
| 1002 | b | 2002 | mysql |
| 1003 | c | 2003 | html |
| 1004 | d | 2004 | python |
| 1005 | e | 2005 | c++ |
| 1006 | f | 2006 | c# |
解决方案：
学生表：学号是主键
| 学号 | 姓名 | 课程编号 |
|---|---|---|
| 1001 | a | 2001 |
| 1002 | b | 2002 |
| 1003 | c | 2003 |
| 1004 | d | 2004 |
| 1005 | e | 2005 |
| 1006 | f | 2006 |
课程表：课程编号是主键
| 课程编号 | 课程名称 |
|---|---|
| 2001 | java |
| 2002 | mysql |
| 2003 | html |
| 2004 | python |
| 2005 | c++ |
| 2006 | c# |
常见的表关系
一对一
学生信息表分为基本信息表和信息信息表。
- 分为两张表，共享主键。
- 分两张表，用外键连接。
一对多
两张表，外键在多的一方。
- 分两张表存储，在多的一方加外键
- 这个外键字段引用是一的一方的主键
多对多
-
分三张表存储，在学生表存储学生信息，在课程表存储课程信息。
-
在成绩表中存储学生和课程的对应关系。