MYSQL

SQL命令
—— 连接本机服务器，登录服务器
mysql -uroot -p
密码
—— 查看数据库
show databases;
—— 进入数据库
use 库名；
—— 查看数据表
show 表名;
—— 查看表结构
desc 表名；
—— 退出登录、断开连接
exit;
quit;
\q

—— 删除db1库
drop database if exists db1;
—— 重新创建ab1库
create database db1 charset utf8;
—— 查看、进入db1库
show databases;
use db1;
—— 删除stu学生表
drop table if exists stu;
—— 创建stu学生表
create table stu(
id int,
name varchar(20),
gender char(1),
birthday date
);

—— 查看表结构
desc stu；                                                       

数据类型：
数字：
整数： 
tinyint
smallint
int
bigint
浮点数：
float
double
decimal
两个标记：
unsigned 无符号数 只有正数 
zerofill 配合（3）显示位数，如果不足位数，则补0
字符串：
char 定长字符串
varchar 变长字符串
text 长文本类型
日期时间：
date 年月日
time 时分秒
datetime 年月日时分秒
timestamp 时间戳
sql结构化查询语句（Structured Query Language）
DDL 数据定义语言（建库、建表）
DML 数据操作(增删改)
DQL 数据查询（select）

DML（增删改）：
-- 中文，先告诉服务器，客户端是什么编码，连接断开之前，服务器可以记住这个编码
set names gbk;
-- 查询stu表的数据
select * from stu;
-- 插入id，name
insert into stu(id,name)
values(7,'李四');
intsert into stu(id,name)
values(8,'王五'),(9,'赵六'),(10,'钱七');
select * from stu;

-- 把7号，李四的性别和生日，修改成‘女’，‘1998-8-4’
update stu set gender = '女',birthday = '1998-8-4'
where id = 7;
select * from stu;

-- 删除id>8的数据
delete from stu
where id>8;
select * from stu;

-- 准备测试数据
-- hr_mysql.sql 放在d盘根目录，可以方便写路径
-- sql脚本文件，建库、建表、插入数据的sql代码
-- 执行这个文件中的sql代码
source d：\hr_mysql.sql

-- 查看表
show tables;
-- 查看员工表结构
desc emps;
-- 员工表数据
select * from emps;
select id ,fname,sal,dept_id 
from emps;

where字句
设置过滤条件
=                                             等值过滤
<>                                           不等过滤
> >= < <=                                大于 大于等于 小于 小于等于
between 小 and 大                  >= 小 并且 <= 大
in(7,2,9,4)                                在指定的一组值中取值
is null                                       是null
is not null                                 不是null
like                                           字符串模糊匹配 %通配多个字符 _通配单个字符 匹配_需转义：\_ \\ \%
not not between and、not in(...)、not like
and                                           并且 and优先级比or高，建议使用小括号
or                                              或者

-- 员工id是122
select id,fname,sal,dept_id
from emps
where id=122;
-- 部门编号dept_id是30
select id,fname,sal,dept_id
from emps
where dept_id=30;
-- 工作岗位代码是'IT_PROG'
select id,fname,sal,dept_id
from emps
where dept_id='IT_PROG';
-- 部门编号dept_id不是50
select id,fname,sal,dept_id
from emps
where dept_id<>50;
-- 工资sal>5000
select id,fname,sal,dept_id
from emps
where sal>5000;
-- 工资范围[5000,8000]
select id,fname,sal,dept_id
from emps
where sal between 5000 and 8000;
-- 工资范围[5000,8000]
select id,fname,sal,dept_id
from emps
where sal>=5000 and sal<=8000;
-- id是120，122，100，150
select id,fname,sal,dept_id
from emps
where id in(120,122,100,150)
-- id是120，122，100，150
select id,fname,sal,dept_id
from emps
where id=120 or id=122 or id=100 or id=150;
-- 没有部门的员工 dept_id是null
select id,fname,sal,dept_id
from emps
where dept_id is null;
-- 有提成的员工，com_pct不是null
select id,fname,sal,dept_id
from emps
where com_pct is not null;
-- fname中包含en 
select id,fname,sal,dept_id
from emps
where fname like '%en%';
-- fname 第3、4个字符是en
select id,fname,sal,dept_id
from emps
where fname like '__en%';

distinct 去除重复值
select distinct a from ... 去除a字段的重复值
select distinct a,b from ... 去除a，b字段组合的重复值
-- 查询所有部门id
select distinct dept_id from emps; 
where dept_id is not null;

order by 字句
排序
order by a 按a字段升序排列
order by a,b 按a字段升序排列，a字段相同，再按b字段升序排列
asc 升序（默认）
desc 降序
order by a desc a降序
order by a desc,b a降序，b升序
order by a desc,b desc a降序，b降序
-- 查询50部门员工，按工资降序 应先过滤再排序
select id,fname,sal,dept_id
from emps
where dept_id=50
order by sal desc;
-- 所有员工 按部门升序，相同部门按工资降序
select id,fname,sal,dept_id
from emps
order by dept_id,sal desc;
查询语句的执行顺序
select 字段
from 表名
where 过滤条件
order by 字段
1.where 过滤
2.选取字段
3.order by 排序
单引号：
字符串内容中的单引号，用两个单引号转义
'I'm Abc'
'I''m Abc'
sql注入攻击

函数
字符串函数
数字函数
日期函数
null值函数

字符串函数
char_length(字符串) 字符个数
length(字符串) 字节数
substring(字符串,起始位置，截取长度) 截取字符串
instr(主串，子串) 获取子串在主串中的位置
left(字符串，获得几位) 获得左侧字符
right(字符串，获得几位) 获得右侧字符
concat(s1,s2,s3) 连接成一个字符串 sql server 用+号连接 oracle用||连接
lpad(字符串，8，'*) 左侧填充为8个长度，不足8位截断
rpad(字符串，8，'*) 右侧填充为8个长度，不足8位截断
replace(tel,'44','88') 将tel中的所有44替换为88

-- fname和lname首字母相同
select id,fname,lname,sal,dept_id
from emps
where left(fname,1)=left(lname,1);
-- fname和lname首字母相同(常用)
select id,fname,lname,sal,dept_id
from emps
where substring(fname,1,1)=substring(lname,1,1);
-- fname和lname连接起来再对齐中间的空格
select concat(lpad(fname,20,' '),' ',lname)
from emps;

数字函数
ceil(数字) 向上取整
floor(数字) 向下取整
round(数字，2) 四舍五入到小数点2位
truncate(数字，2) 舍弃到小数点2位
rand() 随机数 左闭[0，1)右开

-- 工资上涨11.31%，向上取整到十位
select id,fname,sal,ceil(sal*1.113/10)*10
from emps;
-- 所有员工随机排序
select id,fname,sal,dept_id
from emps
order by rand();
日期函数
now() 当前日期时间
currdate() 当前日期
currtime() 当前时间
extract(字段 from 日期) 抽取日期中指定的字段的值
data_add(日期，interval 字段 值) 在指定字段上加一个值
datediff(日期1，日期2) 获得两个日期相差的天数

-- 查询系统当前时间
select now();
-- 查询1997年入职的所有员工
select id,fname,hdate
from emps
where hdate between '1197-1-1' and '1997-13-31'; 
-- 查询1997年入职的所有员工
select id,fname,hdate
from emps
where extract(yeat from hdate)=1997;
-- 员工已入职多少年
select id,fname,hdate,datediff(now(),hdate)/365 y
from emps
order by y;

null空值函数
ifnull(a,b) a不是null 则返回a a是null 则返回b
-- 年薪*提成
select id,fname,sal,sal*12*(1+ifnull(com_pct,0)) t
from emps
order by t desc;

多行函数（聚合函数）
sum() 求和函数
avg() 求平均值函数
max() 求最大值函数
min() 求最小值函数
count() 求行数
1.多行函数不能和其他字段一起查询
2.多个多行函数可以一起查询
3.多行函数会忽略null值
4.count(8)计算行数，不忽略null值
5.count(distinct a)去除重复再计数 

-- 最低工资值
select min(sal)
from emps;

group by 字句(分组求多行函数)
1.按指定字段中相同的值进行分组
2.分组后分别求多行函数
3.分组字段可以查询
4.group by a 按a字段相同值分组
5.group by a，b 按a，b组合的相同值分组

-- 每个部门的平均工资
select avg(sal),dept_id
from emps
where dept_id is not null
group by dept_id;
-- 每个工作岗位job_id的人数
select count(*),job_id
from emps
group by job_id;

-- 电话号码tel包含‘44’，并把44替换成88
select id,fname,tel,replace(tel,'44','88')
from emps
where tel like '%44%';

-- mgr_id，主管id是100，120，122的人数
select count(*)
from emps
where mgr_id in(100,120,122);

-- 按主管id分组，求每个主管的手下人数
select count(*),mgr_id 
from emps 
where mgr_id is not null
group by mgr_id;

having 字句(与group by一起用)
用来对多行函数结果进行过滤
having和where作用相同，都是条件过滤
where过滤普通条件，最早执行
having过滤多行函数结果，分组求完多行函数后，才执行

-- 只有一个手下的主管id
select mgr_id,count(*) c
from emps
where mgr_id is not null
group by mgr_id
having c=1;
-- 平均工资小于等于5000的岗位代码
select job_id,avg(sal) a
from emps
group by job_id
having a<=5000;

子查询
查询嵌套
条件子查询
from子查询（行内视图）
select子查询

条件子查询
一个查询的查询结果，作为另一个查询的过滤条件
select ... where a = (select ...)
单值子查询，结果是单个 = >=  < <=
多值子查询 in > all (select...)
比最大值大
in > any (select...)
比最小值大
多列子查询
where（a,b） in (select...)
where (a,b) = (select...)
from子查询（行内视图）
从查询的查询结果，再查询
select ... from (select ...) t

-- 工资小于平均公司 多行函数不能写在where后面
select id,fname,sal
from emps
where sal<(select avg(sal) from emps);

-- 每个部门，拿最高工资的员工
-- 1.每个部门的最高工资值
dept_id,sal) in(
select dept_id,max(sal) m
from emps
where dept_id is not null
group by dept_id;
-- 2.每个部门，拿最高工资的员工
selct id,fname,sal,dept_id
from emps
where (dept_id,sal) in(
select dept_id,max(sal) m
from emps
where dept_id is not null
group by dept_id
);

-- 只有一个人的部门，查询这些员工
-- 1.按部门分组求人数，过滤只有一个人的部门
select dept_id
from emps
where dept_id is not null
group by dept_id
having count(*)=1;
-- 2.用部门id过滤查询员工
select id,fname,sal,dept_id
from emps
where dept_id in (10,40,70);
-- 3.只有一个人的部门，查询这些员工
select id,fname,sal,dept_id
from emps
where dept_id in(
select dept_id
from emps
where dept_id is not null
group by dept_id
having count(*)=1;
);
-- 平均工资最低的工作岗位代码
-- 1.按job_id分组求平均工资
-- 2.平均工资最小值
-- 3.用平均工资最小值过滤岗位代码
select job_id,avg(sal) a
from emps
group by job_id
having a = (
select min(a)
from (
select job_id,avg(sal) a
from emps
group by job_id
) t
);
-- 手下人数最多的主管id
-- 1.按mgr_id分组求人数
-- 2.求人数的最大值
-- 3.用人数过滤查询主管id
select mgr_id,count(*) c
from emps
where mgr_id is not null
group by mgr_id
having c = (
select max(c) from (
select mgr_id,count(*) c
from emps
where mgr_id is not null
group by mgr_id
order by c
) t 
);

5种约束
约束就是限制字段中的取值
1.主键约束
2.外键约束
3.非空约束
4.唯一约束
5.检查约束

主键
数据表中,一行数据的唯一标识
不重复,不能是null值,自动生成索引
一般使用"非业务数据"来作为主键(自动增加,随机生成)
-- 如果没有db1库,新建
create database db1 charset utf8;
use db1;
create table xuesheng(
id int primary key,
stu_num int,
name varchar(20)
);
-- 告诉服务器,客户端使用的什么字符编码
set names gbk;
insert into xuesheng(id,name)
values(1,'张三'),(2,'李四');

自增主键
整数类型主键,可以设置自动生成自增的值
create table xuesheng(
id int primary key auto_increment,
...
);

-- 修改表,把主键id修改成自增
alter table xuesheng
modify id int auto_increment;

insert into xuesheng(name)
values('李四'),('王五'),('赵六');
select * from xuesheng;

获得刚刚产生的自增值
select last_insert_id(); 只获得当前会话产生的自增值

外键约束
限制一个字段,只能取指定的主键字段中,存在的值,会自动生存索引
create table banji(
id int primary key auto_increment,
name varchar(20)
); 
-- 修改学生表,添加外键字段 ban_id
-- 引用banji表的主键
alter table xuesheng
add(
ban_id int,
foreign key(ban_id) references banji(id)
);

insert into banji(name)
values('A'),('B');
select * from banji;
-- 让学生1,2,3在一班
update xuesheng set ban_id =1
where id in(1,2,3);
-- 让学生4,5再2班
update xuesheng set ban_id =2
where id in(4,5);
select * from xuesheng;

非空约束
create table xuesheng(
...
name varchar(20) not null,
... 
); 
唯一约束(也会自动创建索引)
create table xuesheng(
...
email varchar(100) unique -- not null
... 
);
检查约束
设置检查条件,约束字段的取值
mysql不支持
mariadb支持检查约束
create table xuesheng(
...
age int,
gender char(1),
...
check(age>=7&&age<=60) -- check(gender in('M','F')) 
);
多表连接查询
两章表,按指定条件,连接成一张表,从连接的结果表中查询

use ht;
-- 部门表
select * from depts;
-- 查询员工,显示员工的部门名
select e.id,e.fname,e.sal,d.dept_id,d.dept_name
from emps e,depts d
where e.dept_id = d.dept_id;
-- 地区表
select * from locations;
-- 查询部门,显示部门的城市
select d.dept_id,d.dept_name,l.city
from depts d,locations l
where d.loc_id = l.loc_id;
-- 查询部门,显示部门经理名
select d.dept_id,d.dept_name,e.fname
from depts d,emps e
where d.mgr_id = e.id;
-- 查询员工,显示员工上司名(主管名)
-- 自连接,一张表,看作是两张表来连接
select e1.id,e2.fname,e1.sal,e2.fname mgr
from emps e1,emps e2
where e1.mgr_id = e2.id;

内连接:只查询满足连接条件的数据
外连接:不满足连接条件的数据也要查询
左外连接:查询左侧表条件外数据
右外连接:查询右侧表条件外数据
全外连接:双侧表条件外数据(mysql不支持)
外连接这种非sql连接语句,每种数据库外连接语法都不同
例如:Sql server
where a.id = b.xid(+) 右外连接
Oracle
where a.id =* b.xid 左外连接
Mysql
没有外连接的非标准语法,需使用标准的表连接语法来实现
标准的表连接语法
内连接:
select ...
from a join b
on(a.id = b.xid)
join c
on(...)
左外连接:
select ...
from a left join b
on(a.id = b.xid)
右外连接:
select ...
from a right join b
on(a.id = b.xid)

-- 查询所有部门，显示部门经理，没有经理显示null
select d.dept_id,d.dept_name,e.fname
from depts d left join emps e
on(d.mgr_id = e.id);
-- 查询107个员工，显示部门名和城市
select e.id,e.fname,e.sal,d.dept_name,l.city
from emps e left join depts d
on(e.dept_id = d.dept_id)
left join locations l
on(d.loc_id = l.loc_id); 

事务
事务由一组sql语句组成
事务的sql语句整体成功或整体失败
A转帐100给B
update 账户 set money = money-100
where id = 'A';
update 账户 set money = money+100
where id = 'B';
数据操作的最小单元是事务，而不是sql语句
启动事务
begin
start transaction
事务启动后，执行的数据增删改操作，会暂时记录再一个日志文件中
提交事务
commit 事务日志中记录的数据操作，在数据表中生效并清空日志文件
回滚事务
rollback 清空日志

事务特性ACID
A Atomic
原子性，数据操作的最小单元时事务，而不是sql语句
C Consistency
一致性，事务完成前后，数据要保持逻辑的一致性
A+B=100 转帐前
A+B=100 转帐后
I Isolation
隔离性，一个事务操作数据时，其他事务要等待 隔离级别
D Durancy
持久性，事务成功，数据被永久的保存

数据访问冲突的问题
3种问题
脏数据：
一个事务未提交的数据，被另一个事务查询到
不可重复读：
针对数据修改，update操作，查询过的数据，再次查询时，与之前的查询结果不一致
幻觉读：
针对数据的插入和删除，新插入的数据，查询不到，删除的数据，能查询到

隔离界别
为了避免数据访问冲突的问题，避免数据的混乱，数据库会让事务进行隔离
为了兼顾数据安全和性能，数据库提供了四种隔离级别，级别越高越安全，性能也越差，级别越低，越不安全，性能越好
mysql设置隔离级别：
set tx_isolation = 'read_uncommitted' 读取未提交 脏读、不可重复读、幻读 
set tx_isolation = 'read_committed' 提交可读取 存在不可重复读，幻读
set tx_isolation = 'repeatable_read' 可重复读（默认级别）存在幻读
set tx_isolation = 'serialiable' 串行化，排队

default character set utf8 设置默认字符编码为 utf8