SQL语句基础

• 数据类型
• DQL语言
• DML语言
• DDL语言
• DCL语言
• TCL语言
• 非外键约束
• 外键约束
• 外键策略
• DDL和DML一点补充
• 多表查询语法
• 子查询
• 事务
• 事务并发问题
• 事务隔离级别
• 视图+存储过程
• JDBC+sql注入+其他

安装：安装前卸载所有mysql和mariadb版本

wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

使用上面的命令就直接下载了安装用的Yum Repository，大概25KB的样子，然后就可以直接yum安装了。
[root@localhost ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm
之后就开始安装MySQL服务器。
[root@localhost ~]# yum -y install mysql-community-server
这步可能会花些时间，安装完成后就会覆盖掉之前的mariadb。
至此MySQL就安装完成了，然后是对MySQL的一些设置。
首先启动MySQL
[root@localhost ~]# systemctl start  mysqld.service
查看MySQL运行状态，运行状态如图
[root@localhost ~]# systemctl status mysqld.service
此时MySQL已经开始正常运行，不过要想进入MySQL还得先找出此时root用户的密码，通过如下命令可以在日志文件中找出密码：
[root@localhost ~]# grep "password" /var/log/mysqld.log
如下命令进入数据库：
[root@localhost ~]# mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'QAZwsx123.';

use mysql
select host from user where user='root';
update user set host = '%' where user ='root';
flush privileges; #立刻生效

无法用navicat连接数据库可能是防火墙没关闭：
systemctl stop firewalld.service            #停止firewall
systemctl disable firewalld.service        #禁止firewall开机启动

横向：记录。纵向：字段

基本语句汇总：(全文基于sql99语法)

sql语句关键字，表名（ 只在windows系统中有效，在linux系统中是区分大小写的 ），字段名等不区分大小写。默认内容也不区分大小写，在使用where子句时注意可能会同时影响同一个值的多个大小写不同的记录，例如 where sname = “bob” 和 where sname = “BOB”

插入：INSERT INTO [表名] VALUES('value1','value2')  
     INSERT INTO [表名]([字段1],[字段2]) VALUES('value1','value2')  
     INSERT INTO [表名]([字段1],[字段2]) SELECT [字段1],[字段2] FROM [表名]
复制：SELECT * INTO 目标表名 FROM 源表名 WHERE 1=2 (【复制表结构】即:让WHERE条件不成立)
     SELECT * INTO 目标表名 FROM 源表名（【复制表结构及数据】要求目标表不存在，因为在插入时会自动创建）
删除表：DROP TABLE [表名]
删除数据：DELETE FROM [表名] WHERE 范围  
清空：TRUNCATE TABLE [表名]
更新：UPDATE [表名] SET [字段]='value1' WHERE 范围  
替换：UPDATE [表名] SET [字段] = REPLACE([字段], '替换前内容', '替换后内容');
选择：SELECT * FROM [表名] WHERE 范围  
查找：SELECT * FROM [表名] WHERE [字段] LIKE '%value1%'
排序：SELECT * FROM [表名] ORDER BY [字段1] ASC,[字段2] DESC 
总数：SELECT COUNT(*) AS TotalCount FROM [表名]  
求和：SELECT SUM([字段]) AS SumValue FROM [表名]  
平均：SELECT AVG([字段]) AS AvgValue FROM [表名]  
最大：SELECT MAX([字段]) AS MaxValue FROM [表名]  
最小：SELECT MIN([字段]) AS MinValue FROM [表名]

--添加主键
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY(字段);

--修改字段名 (注意: 更改对象名的任一部分都可能会破坏脚本和存储过程)
EXEC SP_RENAME '表名.字段名','新字段名'

--修改字段类型：
ALTER TABLE 表名 ALTER COLUMN 字段名 INT NOT NULL

--增加字段
ALTER TABLE 表名 ADD 字段名 INT NOT NULL

--删除字段
ALTER TABLE 表名 DROP COLUMN 字段名;

--外连接   
A、left join：   
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。   
B：right join:   
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。   
C：join：   
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

数据类型

整形一般：
INT：4字节
BIGINT：8字节
mysql中支持选择在该类型关键字后面括号内指定整数值的显示宽度，显示宽度不限制列内保存值的实际范围，也不限制超过列的指定宽度的值的显示。例如int（4）的意思是最好是4个字节。

小数一般：
DOUBLE：8字节
注意和整形不同的是，double类型不会自动扩充字段宽度，例如score double（4,1）的意思是总长度为4，小数部分为1，并且不会自动扩充

字符串类型：
char（M）：允许长度最长为m的定长字符串，占用空间为m
varchar（M）：长度为m的变长字符串，占用空间根据实际情况分配
blob：二进制长文本，可以存音频视频等
TEXT：长文本数据

日期和时间类型：
DATE：YYYY-MM-DD
DATETIME：YYYY-MM-DD, HH:MM:SS
TIMESTAMP:时间戳，YYYY-MM-DD, HH:MM:SS，和DATETIME不同的是时间戳范围是1970-2038，如果没有被赋值或者被赋予了null的值，mysql会自动将该字段值设为系统当前日期和时间。

DQL语言

SELECT子句，from子句，where子句组合来查询一条或多条数据

创建员工表，部门表，工资等级表，奖金表

create table DEPT(
DEPTNO int(2) not null,
DNAME VARCHAR(14) ,
LOC VARCHAR(13)
)
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);

create table EMP(
EMPNO int(4) primary key,
ENAME VARCHAR(10) ,
JOB VARCHAR(9),
MGR int(4),
HIREDATE DATE,
SAL double(7,2),
COMM double(7,2),
DEPTNO int(2)
);
alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO)
-- alter TABLE EMP drop foreign key FK_DEPTNO

create table SALGRADE(
GRADE int primary key,
LOSAL double(7,2),
HISAL double(7,2)
);

create table BONUS(
ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL DOUBLE(7,2),
COMM DOUBLE(7,2)
)

insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK') ;
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20); 
insert into EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 ',1250,1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01',2850, null, 30);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839,'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0,30);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7875, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20) ;
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into EMP ( EMPNO,ENAME, JOB, MGR,HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into SALGRADE (GRADE, LOSAL, HISAL)
values (1, 700, 1200) ;
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);

基础

-- 部分行
select empno, ename,sal from EMP;
-- 显示部分行
select * from EMP  where sal > 2000
select empno,ename,job,mgr from EMP where sal > 2000

-- 起别名
select empno 员工编号, ename 姓名, sal 工资 from EMP
-- as = alias
select empno as '员工 编号', ename as "姓名", sal 工资 from EMP

-- 算数运算符
select empno, ename, sal, sal+1000, deptno from EMP where sal < 2500;
select empno, ename, sal, comm, sal+comm from EMP; --如果sal和comm中一个为空则结果为空，如何解决？

-- 去重
select job from EMP
select distinct job from EMP
select job, deptno from EMP
select distinct job, deptno from EMP

-- 排序
select * from EMP order by sal -- 默认升序
select * from EMP order by sal asc
select * from EMP order by sal desc
select * from EMP order by sal asc ,deptno desc -- sal相同时 deptno按降序排列

where

select * from EMP where deptno = 10 -- 还可以< > <= >=, 不等于可以用 <> 或者！=
select * from EMP where job = 'clerk' -- 不区分大小写
select * from EMP where binary job = 'clerk' -- 加上binary可以区分大小写
select * from EMP where hiredate < '1981.12.25'

-- where子句+逻辑运算符 and
select * from EMP where sal > 1500 and sal < 3000 -- (1500, 3000)
select * from EMP where sal > 1500 && sal < 3000
select * from EMP where sal > 1500 and sal < 3000 order by sal
select * from EMP where sal between 1500 and 3000 -- [1500, 3000]
-- or
select * from EMP where deptno = 10 or deptno = 20
select * from EMP where deptno = 10 || deptno = 20
select * from EMP where deptno in (10,20)
select * from EMP where job in ('clerk','manager','analyst')

-- 模糊查询
select * from EMP where ename like '%A%' -- 百分号代表任意多个字符
select * from EMP where ename like '_A%' -- 任意一个字符

-- 判空
select * from EMP where comm is null
select * from EMP where comm is not null

-- 小括号 先算and 再算or
select * from EMP where job = 'SALESMAN' or job = 'CLERK' and sal >=1500
select * from EMP where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500)
select * from EMP where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500

函数分类

单行函数

select empno, ename, lower(ename), upper(ename), sal from EMP -- 单行函数，一组数据返回一组结果
select max(sal), min(sal), count(sal), sum(sal), avg(sal) from EMP -- 多行函数，一组数据只返回一个结果，也称分组函数
-- 注意除了这五个多行函数，别的都是单行函数
-- 数值函数
select ename, length(ename), substring(ename,2,3) from EMP -- 从2开始向后数三个
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual -- dual实际上是一个伪表
select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入
select ceil(sal) from EMP
select 10/3, 10%3, mod(10,3)
-- 日期和时间函数
select * from EMP
select curdate(), curtime()
select now(), sysdate(), sleep(3), now(), sysdate() -- now()当前时间 sysdate()执行到当前位置的时间，年月日时分秒

insert into EMP values (9999, 'lili', 'SALESMAN', 7698, now(), 1000, null, 30) -- now()可以表示年月日时分秒，但是插入数据的时候还是要参照表的结构来进行
desc EMP;

-- 流程函数 
select empno,ename, sal, if(sal > 2500, "高薪",'底薪') as '薪资等级' from EMP -- 双分支
select empno,ename, sal, comm, sal+ifnull(comm, 0) from EMP -- 解决之前comm为空时，sal+comm为空的问题，单分支
select nullif(1,1),nullif(1,2) from dual; -- 如果value1等于value2， 则返回null， 否则返日value1
-- case
select empno, ename, job, 
case job
when 'CLERK' then '店员'
when 'SALESMAN' then '销售'
when 'MANAGER' then '经理'
else '其他'
end '岗位',
sal from EMP
select empno, ename, job, 
case 
when sal <=1000 then 'A'
when sal<=2000 then 'B'
when sal <=3000 then 'C'
else 'D'
end '工资等级',
deptno from EMP
-- 其他函数
select database(), user(), version() -- 当前数据库，当前用户，当前版本

多行函数

select max(sal), min(sal), count(sal), sum(sal), avg(sal) from EMP -- 多行函数，一组数据只返回一个结果，也称分组函数
-- 多行函数自动忽略null值
select max( comm) , min( comm) , count( comm) , sum(comm) , sum( comm)/ count(comm) , avg(comm) from EMP;
-- max() ,min(), count()针对所有类型 sum(),avg()只针对数值型类型有效
select max(ename) ,min(ename) , count(ename) , sum(ename) ,avg(ename) from EMP;
-- 计数
select count(*) from EMP
select 1 from EMP
select count(1) from EMP

group by分组

-- 各部门平均工资
select deptno, avg(sal) from EMP;
select deptno, avg(sal) from EMP group by deptno -- 字段和多行函数不能同时使用，除非字段属于分组
select deptno, avg(sal) from EMP group by deptno order by deptno desc
-- 各岗位平均工资
select job, lower(job), avg(sal) from EMP group by job

having，分组以后进行二次筛选

-- 统计各个部门平均工资，并只显示平均工资2000以上的 分组以后二次筛选使用having
select deptno, avg(sal) from EMP group by deptno
select deptno, avg(sal) 平均工资 from EMP group by deptno having avg(sal)>2000
select deptno, avg(sal) 平均工资 from EMP group by deptno having avg(sal)>2000 order by deptno desc
-- 统计各个岗位的平均工资，除了MANAGER岗位
-- 方法一：用where
select job from EMP where job != 'MANAGER'
select job from EMP where job != 'MANAGER' group by job
select job, avg(sal) from EMP where job != 'MANAGER' group by job
-- 方法二：用having
select job, avg(sal) from EMP group by job having job != 'MANAGER'
-- 两个方法的区别，where在分组前进行过滤，having在分组后进行过滤
-- 列出最小工资小于2000的职位
select job, min(sal) from EMP group by job having min(sal) < 2000
-- 平均工资大于1200的部门和工作搭配的组合
select deptno, job, avg(sal) from EMP group by deptno, job having avg(sal) > 1200 order by deptno
-- 统计人数小于4的部门的平均工资
select deptno, count(1), avg(sal) from EMP group by deptno having count(1) < 4
-- 统计各个部门最高工资，排除最高工资小于3000的部门
select deptno, max(sal) from EMP group by deptno having max(sal) < 3000

select语句的执行顺序：

from -> where -> group by -> select -> having -> order by

DML语言

insert

insert into t_student VALUES (1,'bob','d',17,'2022-5-6','软件班','aa@qq.com');
insert into t_student VALUES (11110000,'bob','d',17,'2022-5-6','软件班','aa@qq.com');

注意事项：
1. int 宽度是显示宽度,如果超过，可以自动增大宽度，int底层都是4个字节
2.时间的方式多样'1256-12-23' "1256/12/23" "1256.12.23"
3.字符串不区分单引号和双引号
4.如何写入当前的时间now(), sysdate() , CURRENT_DATE()
5. char varchar是字符的个数，不是字节的个数，可以使用binary, varbinary表示定长和不定长的字节个数。
测试上述事项：
insert into t_student VALUES (11110000,'bob','d',171,'2022.5.6','软件班','aa@qq.com');
insert into t_student VALUES (11110000,'bob','d',172,'2022/5/6','软件班','aa@qq.com');
insert into t_student VALUES (11110000,"bob",'d',173,'2022-5-6','软件班','aa@qq.com');
insert into t_student VALUES (11110000,'bob','d',174,now(),'软件班','aa@qq.com');
6.如果不是全字段插入数据的话，需要加入字段名字，否则mysql不知道怎么对应
insert into t_student (sno,sname,enterdate) VALUES (11110000,'bob',now());

update

--where子句的字符串匹配不区分大小写
update t_student set sex = '男'; --影响全表
update t_student set sex = '男' where sno = 1;
UPDATE t_student set classname = "ok" WHERE sno = 11110000;
UPDATE t_student set age = 1 WHERE classname = "OK"; 

delete

DELETE FROM t_student; --清空全表数据
DELETE FROM t_student WHERE sno = 1;

DDL语言

create，alter，drop。创建，修改，删除库

创建表：新建数据库的时候一定要选字符集为 utf-8mb4，对应utf-8字符集。否则插入中文会有乱码。

create table t_student(
sno int(6),  -- 显示长度为6，但超过6了也能存进去
sname VARCHAR(5),--字符个数为5
sex char(1),--字符个数为1
age int(3),
enterdate date,
classname VARCHAR(10),
email VARCHAR(15)
);

-- 查看表结构,展示字段的详细信息
desc t_student;

-- 查看表中数据
select * from t_student

-- 查看建表时使用的语句
show create table t_student
CREATE TABLE `t_student` (
  `sno` int(6) DEFAULT NULL,
  `sname` varchar(5) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

alter drop

-- 增加一列
ALTER table t_student add score DOUBLE(5,2);
UPDATE t_student set score = 123.55555 WHERE sno = 1; -- 注意，会自动把小数点后面四舍五入到两位

-- 删除一列
ALTER table t_student drop score;

-- 增加一列到最前面
ALTER TABLE t_student add score DOUBLE(5,2) FIRST;

-- 增加一列到某个字段后面
ALTER TABLE t_student add score DOUBLE(5,2) AFTER sex;

-- 修改一列的数据类型
ALTER TABLE t_student MODIFY score FLOAT(4,1);
-- 同时修改一列的数据类型和列名
ALTER TABLE t_student CHANGE score scores DOUBLE(5,1);

-- 删除表
DROP TABLE t_student;

DCL语言

grant，revoke。授予，收回权限

TCL语言

start transaction开启事务

commit提交事务

rollback回滚事务

set transaction设置事务属性

非外键约束

-- 列级约束
create table t_student(
sno int(6) PRIMARY KEY auto_increment,  -- 主键非空，自增
sname VARCHAR(5) not NULL, -- 姓名非空
sex char(1) DEFAULT '男' CHECK(sex='男' || sex='女'), -- 默认值，检查值
age int(3) CHECK(age>=18 and age<=50), -- 检查范围
enterdate date, 
classname VARCHAR(10),
email VARCHAR(15) UNIQUE); -- 检查唯一
测试：
INSERT INTO t_student VALUES(null, '小明',DEFAULT,10,'2020-1-1', 'jj','xmm@11.com');
-- 如果sql报错，主键可能就是不连号的，我们也不要求主键连号


-- 表级约束
create table t_student(
sno int(6) auto_increment,
sname VARCHAR(5) NOT NULL,
sex char(1) ,
age int(3),
enterdate date,
classname VARCHAR(10),
email VARCHAR(15),
CONSTRAINT pk_stu PRIMARY KEY (sno);
CONSTRAINT ck_sex CHECK(sex='男' || sex='女'),
CONSTRAINT ck_age CHECK(age>=18 and age<=50),
CONSTRAINT ck_em UNIQUE(email);
);

-- 建表之后添加约束
ALTER TABLE t_student add CONSTRAINT pk_stu PRIMARY KEY (sno);
ALTER TABLE t_student MODIFY sno int(6) auto_increment;
ALTER TABLE t_student add CONSTRAINT ck_sex CHECK(sex='男' || sex='女'),
ALTER TABLE t_student add CONSTRAINT ck_age CHECK(age>=18 and age<=50),
ALTER TABLE t_student add CONSTRAINT ck_em UNIQUE(email);

外键约束

外键：表中的某个字段依赖于另一个表中的某个字段。被依赖的字段必须具有主键约束或者唯一约束。
被依赖的表：称为主表或者父表，设置外键约束的表称为子表或从表。

-- 查看表结构,展示字段的详细信息
desc t_student;
desc t_class;
-- 查看表中数据
select * from t_student
select * from t_class

create table t_class(
cno int(4) primary key auto_increment,
cname varchar(10) not null,
room char(4)
)

-- 添加班级数据
insert into t_class values(null, 'java' , 'a');
insert into t_class values(null, 'pyt' , 'b');
insert into t_class values(null, 'qt' , 'c');

create table t_student(
sno int(6) primary key auto_increment,
sname varchar(5) not null,
classno int(4),
constraint fk foreign key (classno) references t_class(cno)
)

CREATE TABLE t_student(
sno int(6) PRIMARY KEY auto_increment,
sname VARCHAR(5) not NULL,
classno int(4)
)
-- 建表后加外键约束
alter table t_student add constraint fk foreign key (classno) references t_class(cno);

insert into t_student values(null, '张', 1),(null, '李', 2),(null, '王', 3);

-- 测试外键约束
delete from t_class where cno = 1;

外键策略

select * from t_student
select * from t_class
-- 策略1 no action不允许操作
-- 通过操作sql来完成
-- 先把从表的学生对应班级改为null
update t_student set classno = null where classno = 2
-- 然后再删除班级2
delete from t_class where cno = 2

-- 策略2： cascade级联操作 ，操作主表的时候影响从表的外键信息，
-- 先删除原来的外键约束
alter table t_student drop foreign key fk
-- 重新添加外键约束
alter table t_student add constraint fk foreign key (classno) references t_class(cno) on update cascade on delete cascade
-- 试试更新
update t_class set cno = 5 where cno =3
-- 试试删除
delete from t_class where cno = 5;

-- 策略3，set null 置空操作
-- 先删除之前的外键约束
alter table t_student drop foreign key fk
-- 重新添加外键约束
alter table t_student add CONSTRAINT fk  foreign key(classno) references t_class(cno) on update cascade on delete set null
-- 试试更新
update t_class set cno = 9 where cno = 1
-- 试试删除
delete from t_class where cno = 9

-- 策略2和3可以混合使用

-- 应用场合：
-- 删除朋友圈，同时删除点赞，留言 级联操作
-- 解散班级，对应的学生  set null 

DDL和DML一点补充

-- 一个表复制到另一个表里
create table t_student2 as 
select * from t_student
-- 一个表复制到另一个表，只复制结构不复制数据
create table t_student3 as 
select * from t_student where 1=2
-- 一个表复制到另一个表，只要部分列，部分数据
create table t_student3 as 
select sno,sname from t_student where sno = 4
-- 清空表中数据
delete from t_student
truncate table t_student

delete 和 truncate的区别：
1.delete是dml，truncate是ddl
2.delete把表中数据一条一条删除完，truncate是保留表结构，然后重新创建表，所以状态都相当于新表，效率更高
3.delete可以回滚，truncate操作导致隐式提交，不能回滚
4.delete删除记录后，再次向表中添加数据，对于设置有自增约束字段的值，会从删除前该字段的最大值加一开始自增，truncate会从1重新开始自增

多表查询语法

交叉连接，自然连接，内连接

-- 查询员工编号，姓名，部门编号
select empno, ename, deptno from EMP

-- 查询员工编号，姓名，部门编号，部门名称
select * from EMP
select * from DEPT

-- 多表查询
-- 交叉连接 cross join
select * from EMP cross join DEPT -- 笛卡尔积，56条，无实际意义，有理论意义
select * from EMP join DEPT -- cross可以省略不写，mysql中可以，oracle中不可以

-- 自然连接：natural join，自动匹配所有同名列
select * from EMP natural join DEPT
select empno, ename, sal, dname, loc from EMP natural join DEPT -- 缺点：查询字段的时候没有指定字段所属的数据库表，效率低
select EMP.empno, EMP.ename, EMP.sal, DEPT.dname, DEPT.loc from EMP natural join DEPT -- 解决：指定表名
select E.empno, E.ename, E.sal, D.dname, D.loc from EMP E natural join DEPT D -- 表明如果太复杂怎么办？解决：起别名
-- 自然连接自动匹配表中所有同名列，如何只匹配部分同名列？
select * from EMP E join DEPT D using(deptno)
-- using的缺点，两个表中的关联的字段名必须一样，解决：内连接 on 子句
select * from EMP E join DEPT D on (E.deptno = D.deptno)
-- 综合看on子句用的多

select * from EMP E join DEPT D on (E.deptno = D.deptno) where sal > 3500
-- 条件：
-- 筛选条件 where having
-- 连接条件 on using natural

外连接

-- inner join和on子句显示的是所有匹配的信息
select * from EMP E inner join DEPT D on E.deptno = D.deptno
-- 存在的问题：1，40号部门没有员工，没有显示在查询结果中  2.员工如果没有部门，就不会显示在查询结果中
-- 外连接：除了显示匹配的数据之外，还可以显示不匹配的数据 
-- 左外连接：left outer join ，左面那个表的信息即使不匹配也可以看出效果
select * from EMP E left outer join DEPT D on E.deptno = D.deptno
-- 右外连接，右面那个表的信息即使不匹配也可以看出效果
select * from EMP E right outer join DEPT D on E.deptno = D.deptno
-- 全外连接，左右表中不匹配的数据都可以显示，但mysql中不支持，解决方法：union: 取并集（去重）效率低
select * from EMP E left outer join DEPT D on E.deptno = D.deptno union select * from EMP E right outer join DEPT D on E.deptno = D.deptno
-- union: 取并集（不去重）效率高
select * from EMP E left outer join DEPT D on E.deptno = D.deptno union all select * from EMP E right outer join DEPT D on E.deptno = D.deptno
-- mysql 中对集合操作支持比较弱，仅支持并集，交集，不支持差集
-- outer可以省略不写

以三表查询为例

-- 查询员工编号，姓名，薪水，部门编号，部门名称，薪水等级
select * from EMP
select * from DEPT
select * from SALGRADE
-- 关联三张表
select E.empno, E.ename, E.sal, D.deptno, D.dname from EMP E 
right join DEPT D on E.deptno = D.deptno 
join SALGRADE S on E.sal between S.losal and S.hisal
-- 后面join下去就可以了

自连接查询：
自关联：自己和自己关联

-- 查询员工的编号，姓名，上级编号，上级姓名
select e1.empno, e1.ename 员工姓名, e2.empno, e2.ename 员工领导 from EMP e1 join EMP e2 on e1.MGR = e2.empno
-- 有一个员工king，他没有上级领导但也需要展示出来，可以通过左外连接实现
select e1.empno, e1.ename 员工姓名, e2.empno, e2.ename 员工领导 from EMP e1 left join EMP e2 on e1.MGR = e2.empno

子查询

一条sql包含多个select

执行顺序：先执行子查询，再执行外查询
不相关子查询：子查询可以独立运行，称为不相关子查询
不相关子查询分类：根据子查询结果的行数，可以分为单行子查询（子查询结果只有一行）和多行子查询（子查询结果有多行）

不相关子查询：

-- 查询所有比clerk工资高的员工的信息
-- 步骤一：查询clerk工资
select sal from EMP where ename = 'clark'
-- 步骤二：查询所有比clerk工资高的员工信息
select * from EMP where sal > 2450
-- 两次命令解决问题，第二个命令依托于第一个命令，讲两个命令合并
select * from EMP where sal > (select sal from EMP where ename = 'clark') -- 括号里的子查询可以独立运行，称为不相关子查询

-- 单行子查询
-- 查询工资高于平均工资的雇员名字和工资
select ename, sal from EMP where sal > (select avg(sal) from EMP)
-- 查询和clark同一部门并且工资比他低的雇员名字和工资
select ename, sal from EMP where deptno = (select deptno from EMP where ename = 'Clark') and sal < (select sal from EMP where ename = 'Clark')
-- 查询职务和scott相同，比scott雇佣时间早的雇员信息
select * from EMP where job = (select job from EMP where ename = 'scott') and hiredate < (select hiredate from EMP where ename = 'scott')

-- 多行子查询
-- 查询部门20中，职务同部门10的雇员一样的雇员信息
-- 先查部门20里雇员信息
	select * from EMP where deptno = 20
-- 在查部门10的雇员职务
select job from EMP where deptno = 10
select * from EMP where deptno = 20 and job in (select job from EMP where deptno = 10)
select * from EMP where deptno = 20 and job = any (select job from EMP where deptno = 10) -- 等价
-- 查询工资比所有的salesman都高的雇员编号，名字和工资
select empno, ename, sal from EMP
select sal from EMP where job = 'Salesman'
-- 组合
select empno, ename, sal from EMP where sal > all(select sal from EMP where job = 'Salesman')
select empno, ename, sal from EMP where sal > (select max(sal) from EMP where job = 'Salesman')
-- 查询工资低于任意一个clerk的工资的雇员信息(比最大的小)
select * from EMP
select sal from EMP where job = 'clerk'
select * from EMP where sal < any(select sal from EMP where job = 'clerk')
select * from EMP where sal < (select max(sal) from EMP where job = 'clerk') -- 等价
select * from EMP where sal < (select max(sal) from EMP where job = 'clerk') and job != 'clerk'

相关子查询：子查询不可以独立运行，先运行外查询，再运行内查询

-- 查询最高工资的员工（不相关子查询）
select * from EMP where sal = (select max(sal) from EMP)
-- 查询本部门工资最高的员工
-- 方法一：不相关子查询
select * from EMP where deptno = 10 and sal = (select max(sal) from EMP where deptno = 10) -- 先查10部门
union
select * from EMP where deptno = 20 and sal = (select max(sal) from EMP where deptno = 20) -- 再查20部门
union
select * from EMP where deptno = 30 and sal = (select max(sal) from EMP where deptno = 30)
-- 缺点：语句太多
-- 方法二:相关子查询
select * from EMP e where sal = (select max(sal) from EMP where deptno = e.deptno) order by deptno
-- 查询工资高于其所在岗位的平均工资的员工
-- 不相关子查询
select * from EMP where job = 'clerk' and sal > (select avg(sal) from EMP where job = 'clerk') -- 然后分别查多个部门再union
select * from EMP e where sal > (select avg(sal) from EMP where job = e.job) -- 加上等好，把一个部门只有一个人，工资正好等于平均工资的情况也包含进来

事务

原子性：一组事物要么都成功，要么都失败
一致性：使数据库从一个一致性状态变到另一个一致性状态。必须达到我们预期效果。一致性通过原子性保证。
隔离性：各个事物之间互不影响，互不干扰。
持久性：数据做的任何改变都要记录到永久存储器中。

create table account(
id int primary key auto_increment,
uname varchar(10) not null,
balance double
)

select * from account
insert into account values(null, '丽丽', 2000),(null, '小刚', 2000)

-- 丽丽转给小刚200元
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
-- 默认一个DML语句是一个事物，所以上面的操作执行了两个事物
-- 必须让上面两个操作控制在一个事物之中
-- 手动开启事物，开启后执行的操作全部控制在一个事物中
start transaction;
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
rollback -- 手动回滚，刚才执行的操作全部取消
commit -- 手动提交
-- 注意，在执行回滚和提交之前，无论进行何种修改，修改的都是缓存中的数据，必须要提交之后才写入数据库。回滚和提交之后，事物就结束了

事务并发问题

脏读：读到了错的数据，使用了尚未提交的数据。
一个事物正在访问数据并对数据进行修改，但尚未提交到数据库中。另一个事物也访问到了这个数据并使用了修改后的数据，但因为这个数据尚未被提交，有可能被回滚，所以是脏数据。

事物A	事物B
开启事务A
	开启事物B
查询余额为100
	余额增至150
查询余额为150
	事物回滚

不可重复读：
一个事物执行时，由于另一个事物的影响，导致多次读取同一数据的结果不一样。

事物A	事物B
开启事物A
	开启事物B
查询余额为100
	余额增至150
查询余额为100
	提交事物
查询余额为150

幻读：
与不可重复读类似，一个事务执行时，另一个事务插入了几条数据，在随后的查询中第一个事务发现多了一些原本不存在的记录，就像幻觉一样。

事物A	事物B
开启事务A
	开启事务B
查询id<3的记录，共三条
	插入一条记录，id=2
	提交事务
查询id<3的事务，共四条

不可重复读和幻读的区别：
1.前者侧重于对数据的修改，后者侧重于新增或者删除。
2.解决不可重复读需要锁定行，解决幻读需要锁定表

事务隔离级别

通过设置事务的隔离级别来解决并发问题。

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITED	√	√	√
READ COMMITED		√	√
REPEATABLE READ			√
SERIALIZABLE

实际中一般使用可重复读

-- 查看默认的事务隔离级别，默认可重复读
select @@transaction_isolation
-- 设置事务的隔离级别 设置当前会话的隔离级别
set session transaction isolation level read UNCOMMITTED
set session transaction isolation level read COMMITTED
set session transaction isolation level repeatable read
set session transaction isolation level serializable
-- 测试事务隔离
-- 新建一个事务
start transaction
select * from account where id = 1
commit

-- 再建一个事务模拟并发
start transaction;
select * from account where id = 1;
update account set balance = balance + 100 where id = 1;
rollback;
commit;

视图

从一个单张或者多张基础数据表，或其他视图中构建出来的虚拟表。

只存放定义，不存放真实的数据。本质是一个查询语句。
好处：简化用户操作，使用户将精力集中在数据上，同时对机密数据提供保护。

-- 创建单表视图
create or replace view v1
as 
select empno, ename, job, deptno
from EMP
where deptno = 20
with check option
-- 查看视图
select * from v1
-- 在视图中插入数据
insert into v1 (empno, ename, job, deptno) values (9999, 'lili', 'clerk', 20)
insert into v1 (empno, ename, job, deptno) values (8829, 'nana', 'clerk', 30)

-- 创建，替换多表视图
create or replace view v2
as 
select e.empno, e.ename, e.sal, d.deptno, d.dname 
from EMP e
join DEPT d
on e.deptno = d.deptno
where sal > 2000
-- 测试
select * from v2

-- 创建统计视图
create or replace view v3
as 
select e.deptno, d.dname, avg(sal),min(sal),count(1)
from EMP e
join DEPT d
using(deptno)
group by e.deptno
-- 测试
select * from v3

-- 创建基于视图的视图
create or replace view v4
as
select * from v3 where deptno = 20
-- 测试
select * from v4

存储过程：
1.提高执行性能，普通sql语句会对词法分析，编译，执行等，存储过程只在第一次执行语法分析，编译，执行。
2.减轻网络负担，只需要从客户端传递sql参数即可，不需要传递sql本身
3.将数据库处理黑匣子化，不用考虑存储过程的详细处理，只需要知道调用哪个存储过程即可

create procedure mp1(name varchar(10))
begin
	if name is null or name = "" then 
		select * from EMP
	else
		select * from EMP where ename like concat('%',name,'%');
	end if;
end

-- 定义一个没有返回值 存储过程
-- 实现模糊查询操作
select * from EMP where ename like '%A%'

create procedure mp1(aaa varchar(10))
begin
	if aaa is null or aaa = "" then 
		select * from EMP;
	else
		select * from EMP where ename like concat('%',aaa,'%');
	end if;
end;
-- 删除存储过程
drop procedure mp1
-- 调用存储过程
call mp1(null);
call mp1('R');

-- 带返回值的存储过程
-- 实现模糊查询操作
-- in前面的参数in可以省略不写，found_rows()是mysql中定义的一个函数，作用是返回查询结果的条数
create procedure mp2(in name varchar(10), out num int(3))
begin
if name is null or name = "" then
select * from EMP
else
select * from EMP where ename like concat('%',name,'%')
end if
select found_rows() into num
end
-- 调用存储过程
call mp2(null, @num);
select @num;
call mp2('R', @aa);
select @aa;

JDBC+sql注入

JDBC:
制定了一系列连接数据库的操作规范接口，这些接口存放在JDK的核心库中，例如java.sql包，javax.sql包等。
调用这些接口时，这些接口会调用对应的数据库的驱动，mysql有mysql的驱动，oracle有oracle的驱动，所以还需要单独导入所使用的数据库的连接驱动。

jar文件里面就是一些打包好的字节码文件

JDBC主要模块：drivermanager，其管理以下几个部分：
1.connection：相当于java和mysql之间的一条路
2.driver：相当于路上运货的司机
3.statement：相当于司机开的卡车，负责运货，包含sql语句和resultset结果集

java实体类：用于存储从数据库中查询出来的数据，属性必须是私有的，实体类的属性推荐写成包装类（用Integer不用int，日期类型推荐写成java.util.date）,属性需要有get和set方法，必须具备空参构造方法，实体类应当实现序列化接口（和mybatis及分布式有关），需要实现一个空参数的构造方法

sql注入：构建特殊输入作为参数传入web应用程序，这些输入大多是sql语法组合，通过执行sql语句执行攻击者所需要的操作。原因是没有对用户输入进行过滤
例：输入的密码为 ‘sdfg’ or ‘a’ = ‘a’ ，导致查询用户密码的结果恒为真

select * from account where username = 'sdfg' and password = 'sdfg' or 'a' = 'a'

JDBC中可以使用preparedstatement防止sql注入攻击。（原理是把语法中的引号进行转义），实际中用preparedstatement而不用statement

preparestatement优点：语句只编译一次，减少编译次数，提高安全性能

如果开启预编译，需要在开启预编译的同时开启预编译缓存才能带来些许的性能提升。 需要在url后面加上如下参数：

&useServerPrepStms = true & cachePrepStms =true;

DAO模式应具备的元素：1.实体类，和数据库中的表一一对应 2.DAO层，定义数据要执行那些操作的接口和实现类 3.mybatis对DAO层代码进行了封装，代码编写方式会有其他变化

连接池：提前创建好connection存入容器中，使用的时候直接取出来即可。如果在获取connection时集合空了， 就创建一个新的connection并返回，归还时如果数量超过了连接池设置的规定上限，将多余的连接释放掉即可。常用连接池包括：
C3p0，Druid，DBCP

日志框架log4j：和使用IO流打印e.printStackTrace相比，1.可以长久保存，2.有等级，3.格式可以很好的定制，4.代码编写简单。
log4j日志级别：
FATAL：出现非常严重的错误事件，这些错误可能导致程序中止
ERROR：虽有错误，但允许程序继续运行
WARN：指运行环境潜藏危害
INFO：报告信息，这些信息在粗粒度级别上突出显示应用程序的进程
DEBUG：细粒度事件，对应用程序的调试是最有用的
log4j框架的使用：加入jar包，log4j-1.2.8.jar，加入属性文件src下log4j.properties（只能起这个名字）

log4j.properties配置文件：

1og4j.rootLogger=debug, stdout 采用stdout方式输出日志
1og4j.rootLogger=debug, logfile 采用logfile的方式输出日志
1og4j.rootLogger=debug, stdout , logfile 采用两种方式输出日志

用stdout方式输出日志
1og4j.appender.stdout=org. apache .log4j.ConsoleAppender
1og4j.appender.stdout.Target=System. err
1og4j.appender.stdout.layout=org. apache .1og4j .SimpleLayout

用logfile方式输出日志
1og4j.appender.1ogfile=org. apache.log4j.FileAppender
1og4j.appender.1ogfile.File=d:/msb.1og
1og4j.appender.logfile.layout=org.apache.1og4j.PatternLayout
1og4j.appender.1ogfile.layout.ConversionPattern=%d{yyy-MM-dd HH:mm:ss} %l %F %p %m%n

三大范式：保证数据库设计合理的一些经验性规范

目标：结构合理，冗余较小，尽量避免插入删除修改异常

一范式：列原子性（列不可分） 详细地址-> 省，市，区
二范式：数据和联合主键的完全相关性，每一列都和主键相关，而不能只和主键的一部分相关（主要针对联合主键）
三范式：数据和主键直接相关性（列中每一列数据都和主键直接相关，而不能间接相关）

一对一关系：A表中一条数据对应B表另一条关系（学号 - 身份证）
一对多：A表中多条对应B表中一条（学生 - 班级）多条学生记录对应班级表中一条记录
多对多：学生表 课程表 选课表