mysql基础
1.mysql安装注意事项
安装mysql注意事项:
1.安装mysql5.6需要 系统的.net framework版本4.0及其以上
2.安装mysql5.7需要 系统的.net framework版本4.5及其以上
卸载mysql注意事项:
1.需要先关闭服务(打开运行界面输入:services.msc)
2.然后卸载mysql应用程序
3.检测安装目录下有没有mysql临时文件。
配置mysql：
1.找到mysql的安装bin目录
2.将bin目录复制到 系统变量中path目录下(环境变量与环境之间用英文的分号隔开)
3.配置好了 打开dos界面（运行界面输入 cmd 回车）
4.输入 mysql -uroot -p123456回车
忘记密码了，想要跳过密码登录(免密码登录)
1.找到my.ini配置文件 （C:\ProgramData\MySQL\MySQL Server 5.7/my.ini）
2.在指定位置添加 skip-grant-tables;(跳过权限登录) [mysqld]77行左右
[mysqld]
skip-grant-tables
保存
3.重启mysql服务
运行界面输入 services.msc 进入之后 重新启动mysql服务
4.即可无需密码登录了...(在dos界面中 输入mysql回车即可登录)
2. 当下比较流行的数据库
mysql:免费并且开源(使用免费，并且开放源代码)
oracle:使用免费，服务收费
-----------------------------------------------------------------------------------------
sql server
sqlite
db2
........
数据库熟练掌握mysql,oracle，sql server中的任意一种
3. 数据库概述
数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。
-----------------------------------------------------------------------------------
专门用于保存以后项目中的一个数据仓库（学习数据库主要是学习对数据库的数据进行crud的操作）
SQL:结构化查询语言
SQL语句分类：
1.查询语句DQL:主要由select关键字完成
2.数据操作语言DML：分别用于添加、修改和删除表中的行。主要由insert、update和delete三个关键字完成。
3.数据定义语言DDL:主要由create、alter、drop、truncate四个关键字完成
4.数据控制语言DCL：主要由grant和revoke两个关键字完成。
5.事务控制语句：主要由commit、rollback和savepoint三个关键字完成。
SQL语言注意点：
SQL语句不分大小写
SQL语句可以写在一行，也可以写在多行。
关键字不能被缩写也不能被分行
因为有些SQL语句可以不加分号，有些必须加，加上也不报错。
4. 如何创建数据库
## mysql注释1
-- mysql注释2
## 1.创建数据库，如果数据库存在则创建数据库并且设置相应的编码
create database [库名] ;
create database if not exists schooldb charset utf8 collate utf8_unicode_ci;
## 2. 如果数据库存在则删除数据库
drop database if exists schooldb;
## 3. 使用数据库（创建表格默认创建到当前正在使用的数据库中）
use schooldb;
## 注意:执行创建表格代码 ，将表格添加到当前正在使用的数据库中
## 4. 查看当前用户下所有的数据库
show databases;
## 5. 查看当前数据库中所有的表格
show tables;
## 6. 查看当前数据库中的表格结构 desc 表名 查看表结构
desc app;
5. 数据库常用数据类型
mysql java
整数:
tinyint byte
smallInt short
int int
bigInt long
数据库字符串(''引起来)
varchar(255) String
0-255长度的字符串
小数:
double double
float float
decimal(n,m) n位 m位
------------------------------------
时间
date 年月日
time 时分秒 Date（java.sql.Date）
datetime 年月日时分秒
timestamp 时间戳 (插入null值，系统会默认设置当前系统时间)
---------------------------------------------------------
文本信息
text 4k
longtext 4G
视频音频文件
blob 4K
longblob 4G
-------------------------------------------------------------
枚举类型
enum
6. 表的约束
<1>.不允许存在2条完全相同的记录
id stu_name stu_pass
1 'jack' '1234' 第一条记录
1 'jack' '1234' 第二条记录
完全相同的记录
为了保证数据库中不出现完全相同的2条记录，提供主键 primary key 去约束字段
主键约束:唯一，非空 (字段一旦被设置了主键约束，每一个值都是独一无二)
注意:一个表格只允许存在一个主键。
<2>.一般主键会配合自增使用(自增的字段不需要人为插入数据)，每插入一行数据 字段自加1
auto_increment 主键或者唯一约束(对应字段会设置索引) 并且对应的字段必须是数值类型。
<3>.一般在注册时候，经常遇到一些必填选项，说明此字段不能为空(非空约束)
not null
7.创建表格
进入大学，学校会为每一个学生开一个账号，用来选课，查询课程，成绩的学生账号，一般会给学生安排一个默认密码，当然登录进去之后可以修改...
create table 表名(
字段名称1 数据类型1 [default 默认值] [primary key] [auto_increment][not null],
字段名称2 数据类型2 [default 默认值] [primary key] [auto_increment][not null],
字段名称3 数据类型3 [default 默认值] [primary key] [auto_increment][not null]
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意:
<1>.加[]说明可以省略
<2>.一个表格只能有一个主键
具体实例
id(编号),stu_name(姓名),stu_pass(密码),stu_sex(性别),stu_score(分数),stu_major(专业)
## 7.创建表格
create table student(
id int primary key auto_increment,
stu_name varchar(50) not null,
stu_pass varchar(50) default '1234' not null,
stu_sex varchar(10) not null,
stu_score int ,
stu_major varchar(50) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意:
<1>.往表格中插入数据，不能违背字段的约束
<2>.一行记录中只要有一个字段的值违背了约束，那么整条记录都无法插入到表格中
7.1 插入数据
##8.1 插入数据方式1:（可以跳过拥有默认值，以及自增的字段,允许为空）
insert into student(stu_name,stu_sex,stu_score,stu_major) values
('赔钱虎','男',89,'打野'),
('赵云','男',84,'打野'),
('公孙离','女',90,'射手'),
('妲己','女',90,'法师'),
('小乔','女',90,'法师');
##8.2 插入数据方式2: (按照表格创建字段的顺序插入数据)
insert into student values
(null,'李白','123456','男',90,'打野'),
(null,'猪八戒','123456','男',90,'上单'),
(null,'牛魔','123456','男',70,'辅助'),
(null,'瑶瑶公主','123456','女',90,'辅助');
7.2 修改数据
补充条件
where
and（并且） 等价于java中&&
or （或者） 等价于 java 中 ||
not (取反) 等价于 java中 !
注意:默认情况下修改整个表格中的所有对应字段的信息信息，
修改语句
update 表名 set 字段名1=新值，字段名2=新值;
代码实现:
## 9.1 将所有的学生密码修改成'abcd';
update student set stu_pass='abcd';
## 9.2 将id为1,3,7的学生性别修改成女,密码修改成'1234'
update student set stu_sex='女' ,stu_pass='1234' where id=1 or id=3 or id=7;
## 9.3 将id为1,3,7的学生性别修改成女,密码修改成'1234'（另外之中写法） 推荐使用
update student set stu_sex='男',stu_pass='asdf' where id in(1,3,7);
## 9.4 将分数为空的学生信息 密码改为 'qwer';
update student set stu_pass='qwer' where stu_score is null;
## 9.5 将分数不为null 的学生密码改为 '1234';
update student set stu_pass='1234' where stu_score is not null;
## 9.6 将id为 奇数的并且 成绩不为null 学生密码全部修改成 '1357';
update student set stu_pass='1357' where id%2!=0 and stu_score is not null;
7.3 删除数据
delete from 表名 where ...
注意:
<1>.不加where条件全部删除
<2>.数据删除完毕 id不会重置
## 10.1 删除全部数据，id不会重置
delete from student;
## 10.2 根据条件删除 将缺考的学生信息全部删除
delete from student where stu_score is null;
7.4 查询
## 1 查询全部的学生信息
select * from student ;
## 2 查询全部学生的姓名和密码
select stu_name,stu_pass from student;
## 3 查询id为奇数的全部学生信息
select * from student where id%2!=0;
## 4 查询缺考的学生信息
select * from student where stu_score is null;
## 5 查询优秀的学生信息 (分数在 85-100之间)
select * from student where stu_score>=85 and stu_score<=100;
## 6 查询优秀的学生信息 (分数在 85-100之间) 等价写法 （建议使用）
select * from student where stu_score between 85 and 100;
## 7 查询id为 11,15,17 的全部学生信息
select * from student where id=11 or id=15 or id=17;
## 8 查询id为 11,15,17 的全部学生信息 等价写法 in
select * from student where id in(11,15,17);
## 9 查询参加考试的学生信息，并且按照成绩进行顺序排列
## order by 字段 （asc 顺序 desc 倒序） 默认值顺序
select * from student where stu_score is not null order by stu_score asc;
## 9 查询参加考试的学生信息，并且按照成绩进行倒序排列
select * from student where stu_score is not null order by stu_score desc;
## 多条件排序
## 10 查询参加考试的学生信息，并且按照成绩进行倒序排列，如果成绩一样按照id进行倒序排列
select * from student where stu_score is not null order by stu_score desc,id desc;
## 11 查询的去重
select distinct stu_score from student;
## 12 模糊搜索 like 关键字
## %:任意长度的任意字符对应的通配符 _:单个任意字符对应的通配符
## 搜索包含乔的所有学生信息
select * from student where stu_name like '%乔%';
## 13 查询乔开头的所有学生信息
select * from student where stu_name like '乔%';
## 14 查询乔开头的并且名字由2个字组成 的学生信息
select * from student where stu_name like '乔_';
## 15 查询以乔结尾的全部学生信息
select * from student where stu_name like '%乔';
## 16 分页查询 分页 当前是第几页 ,一页最多显示多少条记录
## limit 参数1,参数2 （参数1：指定位置开始截取(0:代表从第一行开始截取，一次类推) 参数2:截取的长度）
## 一页显示3 条数据 pageCount
## 第一页 limit 0,3;
## 第二页 limit 3,3;
## 第三页 limit 6,3;
## 第四页 limit 9,3;
-- 第nowPage limit (nowPage-1)*pageCount,pageCount;
select * from student limit 0,3;
select * from student limit 3,3;
select * from student limit 6,3;
## 17.聚合函数
统计 count(), 求和sum()，最高分max(), 最低分min()，求平均avg(),加密MD5()
聚合函数的计算会排除null值
解决方案：
1.使用不包含空的列进行计算
2.IFNULL函数
COUNT：计数函数
count(要计数的字段)函数进行计数使用，忽略NULL值
---要得到表里某个字段拥有的个数 使用count(字段名)
---要得到表里拥有记录的行数
---使用count(*)或者在行数比较多的情况下count(1)效率比较高
——count(*)并不多见，通常是写count(primary key)
select count(age) as xx字段记录个数 from studenttable;
select count(*) as 总行数 from studenttable;
select count(1) as 总行数 from studenttable;
如有null值的情况：
select count(ifnull(english,0)) from stable;
CONCAT：MySQL没有连接符，由CONCAT函数代替
SELECT student_name,age FROM studenttable;
SELECT CONCAT(student_name,age) FROM studenttable;
对比两句运行结果，concat把两字字段结果连在一起
SELECT CONCAT(student_name,age) AS 名字年龄 FROM studenttable;
将两个字段取别名，AS 关键字可以省略
SUM:求和函数
求和函数用于对数据的求和，sum()只能作用于数值型数据
使用varchar类型也不报错，并且能把里面的数字相加
SELECT SUM(age) AS 年龄总和 FROM studenttable;
通常情况下SUM函数，如果求学生成绩总分，使用SUM函数应该怎么办？
select
stu_name,
SUM(english+math+chinese) as '总分',
from student
group by stu_id;
MAX、MIN、AVG：求最大值，最小值，平均数
SELECT MAX(age) FROM studenttable;
SELECT MIN(age) FROM studenttable;
验证：
select * from studenttable where age >= 7452;
SELECT AVG(age) FROM studenttable;
MD5：加密函数
要加密的字段
INSERT INTO studenttable VALUES (1716466091,'你好',20);
INSERT INTO studenttable VALUES (1716466081,MD5('你好'),20);
插入 1716466081，并且加密‘你好’，年龄为20，MD5加密是不可逆过程
可以通过：SELECT * FROM studenttable where student_name = MD5('你好');
进行对照
## 给字段去别名 as '别名'
select
COUNT(*)as '总人数',
SUM(stu_score) as '总分',
MAX(stu_score) as '最高分',
MIN(stu_score) as '最低分',
AVG(stu_score) as '平均分'
from student;
## 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于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 人数 COUNT(id) > 2;
## 18.统计各个专业的总人数，总分，最高分，最低分，平均分
-- group by 字段 根据指定的字段进行分组聚合
select
stu_major,
COUNT(*)as '总人数',
SUM(stu_score) as '总分',
MAX(stu_score) as '最高分',
MIN(stu_score) as '最低分',
AVG(stu_score) as '平均分'
from student
group by stu_major;
注意:
<1>.where根据什么条件进行查询(先看条件，然后根据条件查询出结果)
<2>.聚合函数是对查询出来的结果进行聚合
<3>.where 条件中绝对不能出现聚合函数
<4>.使用了聚合函数，select 之后如果想要查询出普通字段，只能查询group by 分组的字段,如果查询了非分组字段，只会显示该分组的第一个字段值。
## 19.统计各个专业，男生，女生 各自的总分，总分，最高分，最低分，平均分
select
stu_major,
stu_sex,
COUNT(*)as '总人数',
SUM(stu_score) as '总分',
MAX(stu_score) as '最高分',
MIN(stu_score) as '最低分',
AVG(stu_score) as '平均分'
from student
group by stu_major,stu_sex;
## 20 统计各个专业，男生，女生 各自的总分，总分，最高分，最低分，平均分，去掉平均分低于80分的信息
-- having 对聚合统计出来的结果进行筛选
select
stu_major,
stu_sex,
COUNT(*)as '总人数',
SUM(stu_score) as '总分',
MAX(stu_score) as '最高分',
MIN(stu_score) as '最低分',
AVG(stu_score) as '平均分'
from student
group by stu_major,stu_sex
having AVG(stu_score)>=80;
## 21 统计各个专业参加考试的，男生，女生 各自的总分，总分，最高分，最低分，平均分，去掉平均分低于80分的信息
select
stu_major,
stu_sex,
COUNT(*)as '总人数',
SUM(stu_score) as '总分',
MAX(stu_score) as '最高分',
MIN(stu_score) as '最低分',
AVG(stu_score) as '平均分'
from student
where stu_score is not null ## 先进行条件查询
group by stu_major,stu_sex ## 对查询出来的结果进行聚合统计
having AVG(stu_score)>=80; ## 对聚合之后的结果 进行筛选
## 22. 统计各个专业参加考试的，男生，女生 各自的总分，总分，最高分，最低分，平均分，去掉平均分低于80分的信息,
-- 按照平均分进行降序排列，如果平均分一样，看班级的最高分进行降序排列
select
stu_major,
stu_sex,
COUNT(*)as '总人数',
SUM(stu_score) as '总分',
MAX(stu_score) as '最高分',
MIN(stu_score) as '最低分',
AVG(stu_score) as '平均分'
from student
where stu_score is not null ## 先进行条件查询
group by stu_major,stu_sex ## 对查询出来的结果进行聚合统计
having AVG(stu_score)>=80 ## 对聚合之后的结果 进行筛选
order by AVG(stu_score) desc,MIN(stu_score) desc; ## 对最终的结果进行排序
## 22. 统计各个专业参加考试的，男生，女生 各自的总分，总分，最高分，最低分，平均分，去掉平均分低于80分的信息,
-- 按照平均分进行降序排列，如果平均分一样，看班级的最高分进行降序排列 ,截取前3个获奖
select
stu_major,
stu_sex,
COUNT(*)as '总人数',
SUM(stu_score) as '总分',
MAX(stu_score) as '最高分',
MIN(stu_score) as '最低分',
AVG(stu_score) as '平均分'
from student
where stu_score is not null ## 先进行条件查询
group by stu_major,stu_sex ## 对查询出来的结果进行聚合统计
having AVG(stu_score)>=80 ## 对聚合之后的结果 进行筛选
order by AVG(stu_score) desc,MIN(stu_score) desc ## 对最终的结果进行排序
limit 0,3; ##对最终的结果进行截取
8.内容补充
内容补充:
## 1.约束总结
primary key: 主键约束 (唯一且非空)
not null :非空约束
unique :唯一约束 (允许有一个null值)
check :检查约束 (了解一下,mysql中设置了不生效)
foreign key :外检约束 (企业中不推荐使用外键约束) 表与表之间的约束 (会写看的懂即可，不推荐使用);
注意:
<1>.一个表只能设置一个主键 或者 复合主键
<2>.一个字段可以同时设置多个约束
## 2.表格操作
-- 创建表格:create table if not exists 表名(字段1 数据类型1 [default 默认值] [约束],字段2 [default 默认值] [约束]);
-- 删除表格:drop table if exists 表名;
drop table if exists employee;
## 3.插入数据
-- <1>.第一种插入方式:(可以跳过 主键自增,拥有默认值,允许为空的值)
insert into 表名(字段1,字段2,字段3...) values(字段1的值,字段2的值,字段3的值...);
-- <2>.第二种插入方式:按照创建表格的时候 字段的顺序进行插入数据。。。
insert into 表名 values(字段1,字段2,字段3,...)
## 4. 修改数据
-- update 表名 set 字段1=新值 ,字段2=新值,... where 条件修改 (默认情况下修改全部)
-- where
## 常见的连接条件 and or not (判断是否为空 is null 等于null is not null 不为null)
## 字段1 in(值1,值2):字段1的值 是值1或者值2
## 字段1 between startValue and endValue 介于2者之间
## 5. 删除数据
-- <1>.清空表格数据的方式1:
-- delete from 表名 (删除表格的全部数据，id不能重置)
delete from student;
-- <2>.清空表格数据的方式2:
truncate 表名 (删除表格中的全部数据,并且id重置)
truncate student;
9.复杂查询
case when 条件 then 值1 else 值2 end
条件成立返回 值1 条件不成立返回值2
或者
if(条件,值1,值2)
条件成立返回值1,条件不成立 返回值2
<1>.查询成绩的时候 根据 成绩 输出及格不及格
## 23.查询学生信息，如果分数超过60分输出合格，否则输出不合格.
select
id,stu_name,stu_sex,
case when stu_score>=60 then '合格' else '不合格' end as '是否合格'
from student;
## 或者
select
id,stu_name,stu_sex,
IF(stu_score>=60,'合格','不合格') as '是否合格'
from student;
group_concat()分组进行字符串拼接
1.已知表格数据如下:
执行查询语句得到如下结果:
请写出对应查询结果的sql语句:
create table scales(
years int,
quarters int ,
amount double
);
insert into scales values
(1993,1,1.3),
(1993,2,1.5),
(1993,3,1.2),
(1993,4,1.8),
(1994,1,2.3),
(1994,2,2.5),
(1994,3,2.2),
(1994,4,2.8);
select * from scales;
## 使用case when 完成行转列。。。
select
years as '年份',
SUM(case when quarters=1 then amount else 0 end) as '一季度',
SUM(case when quarters=2 then amount else 0 end) as '二季度',
SUM(case when quarters=3 then amount else 0 end) as '三季度',
SUM(case when quarters=4 then amount else 0 end) as '四季度'
from scales
group by years;
## 使用if函数完成行转列
select
years as '年份',
SUM(IF(quarters=1,amount,0)) as '一季度',
SUM(IF(quarters=2,amount,0)) as '二季度',
SUM(IF(quarters=3,amount,0)) as '三季度',
SUM(IF(quarters=4,amount,0)) as '四季度'
from scales
group by years;
1.已知表格数据如下:
执行查询语句得到如下结果:
请写出对应查询结果的sql语句:
## 数据为字符串类型的行转列
create table loyal(
contname varchar(20),
riskname varchar(20)
);
insert into loyal values
('保单1','险种11'),
('保单1','险种22'),
('保单1','险种33'),
('保单1','险种44'),
('保单2','险种aa'),
('保单2','险种bb'),
('保单2','险种cc'),
('保单2','险种dd'),
('保单3','险种ss'),
('保单3','险种qq'),
('保单3','险种rr'),
('保单3','险种tt');
select * from loyal;
select
contname,
GROUP_CONCAT(riskname) as '险种'
from loyal
group by contname;
10.补充复合主键(了解)
将多个字段组合成一个主键，像这样的主键称之为复合主键
T 1
T 2
T 3
字段想要实现自增必须满足 该字段上面拥有索引(primary key , unique )，并且该字段属于数值类型
create table teacher(
tea_no varchar(4) default 'T',
tea_id int unique auto_increment,
tea_name varchar(40) not null,
tea_pass varchar(40) default '1234',
primary key(tea_no,tea_id)
);
复合主键:
tea_no ,tea_id 不能2个字段都相同
## 复合主键
create table if not exists teacher(
tea_no varchar(4) default 'T',
tea_id int unique auto_increment,
tea_name varchar(40) not null,
tea_pass varchar(40) default '1234',
primary key(tea_no,tea_id)
);
insert into teacher values
(default,null,'jack',default),
(default,null,'rouse',default),
(default,null,'charles',default),
(default,null,'tina',default);
### concat(str1,str2,str3...) 字符串的拼接
select
CONCAT(tea_no,tea_id) as '教师编号',
tea_name,
tea_pass
from teacher;
11 .对表格进行动态操作DDL
## DDL操作
## 增加字段
-- 1.1往表格中新增一个字段 tea_phone
alter table teacher add column tea_phone varchar(50) default '110' not null;
alter table teacher add column tea_sex varchar(10) default '男' not null;
-- 1.2增加多个字段
ALTER TABLE studenttable ADD COLUMN(
teacher_age INT(3) NOT NULL,
...
);
## 2. 删除表格中的一个字段
alter table teacher drop tea_phone;
## 3. 修改表格中字段的数据类型或者长度,约束也可以修改，但是不能修改字段的名字
注意：
如果列里有数据；
a.修改的列范围不能小于列里目前字段的最大长度
b.不能跨类型修改
alter table teacher modify tea_name varchar(60) not null;
## 4. 可以修改字段的全部信息，包括名字
alter table teacher change tea_name teaName varchar(60) not null;
## 5. 修改表名
alter table teacher rename to teacharOk;
alter table teacharOk rename to teacher;
12.三大范式
应用三大范式去设计数据库（三大范式就是定义了数据库设计的规范）
第一范式:列的原子性(每一个列都是不可分割的最小单元)
第二范式:在满足第一范式的基础之上，每一张表一般只用来描述一件事情。
第三范式:在满足第二范式的基础之上，每一个非主键字段与主键之间相关而非间接相关。
字段与表 （1对1） 直接相关
字段与表 (1对多) 间接相关
在有些应用场景下为了提高数据库的效率，减少表连接，会出现一些反范式的行为，比如:
一个订单与总金额直接相关，但是一般可以不把这个总金额设置到表中，因为实际上可以通过单价以及数量计算得到，不需要设置。
至于什么时候需要违反范式，需要根据实际情况来判断，在规范和效率之间权衡考虑。
13.应用范式设计数据库
设计实现选课系统...
create database if not exists selectCourseDB charset utf8 collate utf8_unicode_ci;
use selectCourseDB;
## 1.创建学生表
create table if not exists student(
id int primary key auto_increment,
stu_name varchar(50) not null,
stu_pass varchar(50) default '1234' not null,
stu_phone varchar(50) not null,
stu_sex varchar(10) not null
);
insert into student
values
(null,'jack',default,'12581','男'),
(null,'rouse',default,'12582','女'),
(null,'mary',default,'12583','男'),
(null,'tina',default,'12584','女'),
(null,'jam',default,'12585','男'),
(null,'iis',default,'12586','女');
## 2.创建课程表
create table if not exists course(
id int primary key auto_increment,
course_name varchar(50) not null,
score double not null
);
insert into course
values(null, 'java',5),
(null, 'mysql',6.5),
(null, 'HTML',4),
(null, 'css',2);
## 3.创建成绩表
create table if not exists score(
id int primary key auto_increment,
stu_id int,## 外键 引入学生表的主键
course_id int ## 外键 引入课程表的主键
);
## 添加分数字段
alter table score add column mark int;
insert into score values
(null,1,1,78),
(null,1,2,57),
(null,1,3,89),
(null,1,4,47),
(null,2,1,78),
(null,2,2,87),
(null,2,3,90),
(null,3,1,78),
(null,3,2,58),
(null,4,1,67),
(null,4,2,89),
(null,4,3,59),
(null,5,1,67),
(null,5,2,89),
(null,5,3,59),
(null,6,1,67),
(null,6,2,89),
(null,6,3,90),
(null,6,4,87);
## 给score表中的stu_id添加外键约束，引入学生表的主键
alter table score
add constraint score_FK01 ## 自定义的外键约束名
foreign key(stu_id) ## 将score表中stu_id作为外键
references student(id); ## 设置外键表引入的主键表中对应的主键字段
## 根据外键约束的名称删除外键约束
alter table score drop foreign key score_FK01;
##给score表中course_id 设置外键约束，引入课程表中的主键
alter table score
add constraint score_FK02
foreign key(course_id)
references course(id);
select * from student;
select * from course;
select * from score;
注意:
<1>.外键表中的数据是引入主键表中的数据
<2>.主键表中有的数据，外键表才可以使用，主键表之外的数据，无法插入外键表中
<3>.删除表的时候 ，先删除外键表，然后删除主键表(如果在外检表引入了主键表数据的情况下)
<4>.插入数据的时候，先插入主键表的数据，然后再插入外键表数据。
14 .多表查询
需要查询的数据来自于多个表格:(多表查询)
比如:
<1>.查询出所有学生的编号，名字，课程名字，课程学分，对应课程的成绩？（学生表，课程表，成绩表）
使用下列方式可以实现多表查询:
-- 1.使用等价连接:
select
student.id,student.stu_name,
course.course_name,course.score,
score.mark ## 查询各个表中对应的数据
from student,course,score ## (从哪些表里面查询数据)
where student.id=score.stu_id and score.`course_id`=course.id; ##表与表之间连接条件
-- 2.内连接:
select
student.id,student.stu_name,
course.course_name,course.score,
score.mark ## 查询各个表中对应的数据
from student
inner join score
on student.id=score.stu_id ## 连接条件
inner join course
on course.id=score.`course_id`;##(推荐使用)
-- 3.左内连接:是指以左边为基准，右边表的数据主动与左边相匹配，右边的表数据如若没有与左表相匹配的则显示为null。
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
查询的是左表所有数据以及其交集部分。
select
student.`id`,student.`stu_name`,
score.`mark`
from student
left join score
on student.`id`=score.`stu_id`
where score.`mark` is null;
-- 4.右内连接：将右边的表作为主表
## 一般会将主键表作为主表进行匹配
右外连接结果集包括right outer子句中指定的右表的所有行，而不是仅仅连接列所匹配的行。
语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
查询的是右表所有数据以及其交集部分。
select
student.`id`,student.`stu_name`,
score.`mark`
from score
right join student
on student.`id`=score.`stu_id`
where score.`mark` is null;
15.子查询
多表查询能够查询的数据，都可以使用子查询代替，多表查询的特点，虽然查询效率高于子查询，但是不灵活，使用子查询更加的灵活。
一般子查询的应用场景：已知一个表的条件，查询另一个表中的数据
概念：查询中嵌套查询，称嵌套查询为子查询。
-- 查询工资最高的员工信息
-- 1 查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;
-- 2 查询员工信息，并且工资等于9000的
SELECT * FROM emp WHERE emp.`salary` = 9000;
-- 一条sql就完成这个操作。子查询
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
子查询不同情况
1. 子查询的结果是单行单列的：
子查询可以作为条件，使用运算符去判断。 运算符： > 、>= 、< 、<= 、=
-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2. 子查询的结果是多行单列的：
子查询可以作为条件，使用运算符in来判断
-- 查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
3. 子查询的结果是多行多列的：
子查询可以作为一张虚拟表参与查询
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
WHERE t1.id = t2.dept_id;
-- 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11'
注意:
1.如果子查询查询出来的数据只有一个 那么可以使用等号连接，但是不建议使用等号 不管是返回1个还是多个
都建议使用in 连接子查询
2.能够用子查询解决的问题不一定能用多表查询解决。
子查询应用:
第一题解决方案:
-- 1.先查出有重复名字 学生名字
select `name` from student
group by `name`
having COUNT(*)>1;
-- 2.根据查询出来的名字 查询学生全部信息
select * from student
where `name` in
(select `name` from student
group by `name`
having COUNT(*)>1);
注意:
<1>.执行的时候先执行子查询，然后执行主查询
<2>.一般子查询查询的都是单个字段
<3>.主查询在where条件中引入子查询一般使用in()
第二题解决方案:
-- <1>.最高分
select max(score) from student_course where cid=1;
-- <2>.求第2高的成绩 (除了最高分其余学生中的最高分)
select max(score) from student_course where cid=1 and
score<(select max(score) from student_course where cid=1);
-- <3>.求出第2高的学生编号
select sid from student_course where cid=1 and score in(
select max(score) from student_course where cid=1 and
score<(select max(score) from student_course where cid=1)
);
-- <4>.根据id查询学生信息
select * from student where sid in(
select sid from student_course where cid=1 and score in(
select max(score) from student_course where cid=1 and
score<(select max(score) from student_course where cid=1))
);
16.视图
1.什么是视图？
视图是一张虚拟的表
2.为什么使用视图？
1.重复SQL语句
2.简化复杂的SQL操作
3.使用部分表而不是整个表
4.保护数据
-- 重复使用这张虚拟表:
SELECT * FROM studenttable INNER JOIN class
WHERE
studenttable.class_id = class.class_id;
-- 可以创建视图：
CREATE VIEW view_student_class AS
SELECT studenttable.*,class.class_name,class.class_teacher FROM
studenttable INNER JOIN class
WHERE studenttable.class_id = class.class_id;
-- 利用创建好的视图进行数据查询：
SELECT * FROM view_student_class
WHERE class_teacher = '右京先生';
-- 查询班级人数大于3的班级老师名字：
SELECT class_teacher,COUNT(class_teacher) FROM view_student_class
GROUP BY class_teacher HAVING COUNT(class_teacher) > 3;
查询数据可以直接从视图中查询
select * from stu_view;
17. 索引
索引:索引的设计思想类似于新华字典内容的编写顺序 (使用索引目的是为了提高检索效率) B-Tree
索引分类:
主键索引: 默认创建，不需要手动创建 (主键约束的字段)
唯一索引:唯一索引，拥有唯一约束的字段才可以设置唯一约束 unique index
普通索引:index
组合索引:将多个字段组合到一个索引中
全文索引:full index
代码结构:
create [unique] [full] index 自定义索引名字 on 表名(字段1)
组合索引
create index 组合索引名 on 表名(字段1,字段2,....);
应用场景:
登录
select * from user where user_name=... and user_pass=...
可以考虑将user_name,user_pass 设置成一个组合索引 提高多条件检索效率。
经常要根据你的手机号码进行查询信息
select * from user where user_phone=...
可以考虑给手机号码设置一个普通索引
## 创建组合索引
create index name_pass_index
on student(stu_name,stu_pass);
## 创建普通索引
create index name_index
on student(stu_name);
18.事务
事务
InnoDB储存引擎支持事务
不是所有的存储引擎支持事务，MylSAM不支持事务；InnoDB支持事务。
查看已安装mysql现在已提供什么存储引擎；
show engines;
查看MySQL当前默认储存引擎：
show variables like '%storage_engine%';
查看某个表用了什么引擎(在显示结果参数engine的后面的就表示当前用的储存引擎)
show create table 表名;
事务的基本介绍
事务四个属性： 原子性、一致性、隔离性、持久性 (A C I D)
原子性：事务由一个或者一组相互关联的SQL语句组成，这些语句被认为是一个不可分割的单元
一致性：对于数据库的修改是一致的，即多个用户查的数据是一样的。
一致性主要由mysql的日志机制处理，他记录数据的变化，为事务提供跟踪记录
隔离性：（孤立性）每个事务都有自己的空间，和其他发生在系统中的事务隔离开来
而且事务的结果只在它被完全执行时才能看到。
持久性：提交事务后，数据的更改是永久的，通过日志系统可以恢复在重启前进行的最后一次成功的更新
1. 概念：
如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
2. 操作：
1. 开启事务： start transaction;
2. 回滚：rollback;
3. 提交：commit;
3. 例子：
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 张三给李四转账 500 元
-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
-- 出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
-- 发现执行没有问题，提交事务
COMMIT;
-- 发现出问题了，回滚事务
ROLLBACK;
4.mysql数据库中事务默认自动提交
1.一次DML(增删改)语句会自动提交一次事务。
2.事务提交的两种方式：
自动提交：
mysql就是自动提交的
一条DML(增删改)语句会自动提交一次事务。
手动提交：
Oracle 数据库默认是手动提交事务
需要先开启事务，再提交
3.修改事务的默认提交方式：
查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
修改默认提交方式： set @@autocommit = 0;
3. 事务的隔离级别（了解）
概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在问题：
1.丢失数据
2. 脏读：一个事务，读取到另一个事务中没有提交的数据
允许读取到未提交的脏数据。
3. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
如果你在时间点T1读取了一些记录，在T2时再想重新读取一次同样的这些记录时，这些记录可能已经被改变、或者消失不见。
4. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。
但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，
貌似之前读到的数据是“鬼影”一样的幻觉。
隔离级别：由ANSI/ISO定义的SQL-92标准定义的四种隔离级别
1. read uncommitted：读未提交
产生的问题：脏读、不可重复读、幻读
2. read committed：读已提交 （Oracle默认）
产生的问题：不可重复读、幻读
3. repeatable read：可重复读 （MySQL默认）
产生的问题：幻读
4. serializable：串行化
可以解决所有的问题
设置和查看事务级别
1.查看当前回话隔离级别
select @@transaction_isolation; (mysql8.0之前的@@tx_isolation)
2.查看系统当前隔离级别
SELECT @@global.transaction_isolation;
3.设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4.设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
如何开启事务
1.设置提交状态： SET AUTOCOMMIT = FALSE;
SET AUTOCOMMIT = TRUE;
2.begin;或者start transaction;开启当前一个事务，显示执行
关闭事务：
开启事务后，执行语句，还未提交，查询的结果不变。
commit;自动提交 或者 ROLLBACK 回滚(回滚即没有发生过，并且关闭事务)
DDL语句 自动提交 相当于回滚
用户正常结束 相当于回滚
异常终止
只用commit 或者 指行 DDL语句数据库才会更新
悲观锁：
因为事务级别越大，开销越大，效率越慢。
所以所以级别二和悲观锁相结合。
它是数据库的锁
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
