MySQL

MYSQL

停止服务：net stop 服务名称(MySQL80)
开始服务：net start 服务名称(MySQL80)

登录MySQL：
1.cd C:\Program Files\MySQL\MySQL Server 8.0\bin
2.mysql -uroot -p123456
只输入mysql -uroot -p (不用输密码，进入隐藏密码输入)

mysql常用命令

退出：exit
终止语句：\c
查看有哪些数据库：show databases;
创建数据库：create database 数据库名字;
使用数据库：use 数据库名字;
查看数据库下的表：show tables;
导入数据：source 路径
查看表数据：select * from 表名; //从表查询所有数据
不看表数据，只看表结构：desc 表名;
查看数据库版本号：select version();
查看当前使用的数据库：select database;

数据库当中最基本的单元是表 table

sql语句的分类

DQL：数据查询语言（凡是带有select关键字的都是查询语句）
DML：数据操作语言（凡是对表中的数据进行增删改查的都是DML）
insert增 delete删 update改
DDL：数据定义语言（凡是带有create、drop、alter的都是DDL）
DDL操作的是表的结构，不是表中数据
inset新建 alter修改 drop删除
TCL：事物控制语言
commit事务提交 rollback数据回滚
DCL：数据控制语言
grant授权 revoke撤销权限

简单查询

查询一个字段：select 字段名 from 表名;
（用’,‘隔开可以查询多个字段,*可以查询所有(不常用)）
给查询的列取别名：使用as关键字起别名(有’ '就用单引号括起来)
select 字段名 as 别名 from 表名;

字段可以使用数学表达式

select ename,sal*12 from emp;
select ename,sal*12 as 年薪 from emp;//起别名

条件查询

语法格式：
select:
字段1，字段2，字段3…
from:
表名
where:
条件;

select ename,empno from emp where sal = 800; //查询薪资等于800编号和名字

//查询名叫SMITH的薪资和编号
select empno,sal from emp where ename = 'SMITH';

and
//查询工作岗位是'MANAGER'和工资上2500的
select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500;
//and和or	and的优先级高 如果想让or先执行用()

or
//找出部门为10 or 20并且工资>2500
select * from emp where sal > 2500 and (deptno = 10 or deptno =20);

between
//查询薪资是800到2500的员工
select * from emp where sal between 800 and 5000;

in
in相当于多个or	not in表示不在这几个值当中的数据
//查询薪资是800和5000的员工 
select * from emp where sal in (800,5000);

like 模糊查询
//%匹配任意多个字符	下划线:任意一个字符
//找出名字里含有o的
select ename from emp where ename like '%o%';
//找出名字以t结尾的
select ename from emp where ename like '%t';
//找库名字以k开始的
select ename from emp where ename like 'k%';
//找出第二个字母是a的
select ename from emp where ename like '_a%';
//找出第三个字母是a的
select ename from emp where ename like '__a%';

排序 order by

语法格式：
select
字段1，字段2…
from
表名
order by
字段

//查询所有员工薪资并排序	(默认是升序)
select ename,sal from emp order by sal;
//在字段后面加desc是降序(asc是升序)
select ename,sal from emp order by sal desc;

数据处理函数

单行处理函数 （一个输入一个输出）

upper() 转换大写
lower() 转换小写

select lower(ename) as ename from emp;

substr(字段，起始下标，截图的长度)

//查找名字开头为A的
select ename from emp where substr(ename,1,1) = 'A';

lenth() 取长度
trim() 去空格
round(字符，保留位数) 四舍五入

select ename,round(sal,0) as sal from emp order by sal;

rand() 取随机数

//取100以内随机数
select round(rand()*100) from emp;

ifnull() 空处理函数(NULL只要参与运算，结果就是NULL)

//补助comm为NULL时，当作0
select ename, (sal + ifnull(comm,0)) * 12 as yearsal from emp;

case…when…then…when…then…else…end

//当员工的工作岗位是MANAGER，工资上调10%，当工作岗位是SALESMAN，工资上调50%
select ename,job,sal as oldsal,(case job when 'MANAGER' then sal *1.1 when 'SALESMAN' then sal *1.5 else sal end) as newsal from emp;

多行处理函数 （多个输入一个输出）

count() 计数
avg() 求平均值
sum() 求和
max() 求最大
min() 求最小

1.自动忽略NULL
2.count(*)和count(具体字段)区别：
count(具体字段):表示统计该字段下不为NULL的元素的总数
count( *):统计表中的总行数
3.不能直接用在where中,因为没有分组(group by)所有不能用分组函数

分组查询

语法格式：
	select
		...
	from
		...
	group by
		...

有group select只可以跟参与分组的字段和多行处理函数

//求每个工作岗位的平均工资
select job,round(avg(sal)) from emp group by job;
//找出每个部门，不同工作岗位的不同薪资
技巧：两个字段联合成一个字段看
select deptno,job,max(sal) from emp group by deptno,job order by deptno;

过滤语句having

having不能代替where，having必须和group by联合使用
优化：where和having，优先选择where，where实在完成不了的再选择having

//找出每个部门最高薪资，要求显示薪资大于3000
select deptno,max(sal) from emp group by deptno having max(sal) > 3000;//效率太低，先分组过滤再筛选
//先筛选大于3000的再分组
select deptno,max(sal) from emp where sal > 3000 group by deptno;

去除重复记录distinct

只能出现在所有字段的前方

select distinct job from emp;
//出现在两个字段之前，表示两个字段联合起来去重
使用技巧 select count(distinct job) from emp;查询岗位的多少

连接查询

多张表联合起来查询数据

内连接

特点：完全能匹配上这个条件的数据查询出来

等值连接 (条件是等量关系)

//查询每个员工所在部门名称，显示员工和部门名称
//sql92写法 缺点:结构不清晰，表的连接条件和筛选的条件都放在了where后面
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
//sql99写法 优点:表连接条件是独立的，连接之后若还要继续筛选，则往后添加where
select
	...
from
	a
inner(可省) join
	b
on
	a和b的连接条件
where
	筛选条件
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

非等值连接 (条件不是一个等量关系)

//找出每个员工的薪资等级，要求显示员工名、薪资、员工等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

自连接 (我自己连自己)

//查询员工的上级领导，要求显示员工名和领导名
//技巧:一张表看成不同的两张表
select a.ename,b.ename from emp a join emp b on a.mgr = b.empno;

外连接

特点：两张表产生了主次关系

左外连接(左连接)
右外连接(右连接)

select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
//right:代表了将join关键字右边的表看成了主表，主要是为了将这张表的数据全部查出来，顺带着关联查询左边的表

三张表以上的连接

select
	...
from
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a和c的连接条件
...
//找出每个员工的部门名以及工资等级，要求显示员工名，部门名，薪资，薪资等级
select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

子查询

select语句中嵌套select语句，被嵌套的select语句被称为子查询
子查询可以在where中使用多行处理函数

select
	..(select).
from
	..(select).
where
	..(select).
//找出比最低工资高的员工和工资
select ename,sal from emp where sal > (select min(sal) from emp);
//from后面的子查询，可以将子查询查询结果做一张临时表
//找出每个岗位的平均工资的薪资等级
1.找出每个岗位的平均工资
select job,avg(sal) from emp group by job;
2.把以上的看成临时表,将两个表连接
select t.*,s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal; 

Union 合并表

合并查询结果集

//查询工作工位是MANAGER和SALESMAN的员工
1.union效率高
2.union在进行结果合并时，要求两个结果集的列数相同
select ename,job from emp where job = 'MANAGER' union select ename from emp where job = 'SALESMAN';

取部分数据limit

将查询结果集的一部分取出来，通常使用在分页查询中(百度默认一页显示十个搜索结果)

limit startIndex,length
starIndex是启示下标，从0开始	length是长度
//按照薪资降序，取出排名在前5的员工
select ename,sal from emp order by sal desc limit 5;

通用分页

每页显示pageSize条记录
	第pageNo页：limit (pageNo - 1) * pageSize, pageSize;

表

创建表

建表属于DDL语句
create table 表名(
	字段名1 数据类型，
	字段名2 数据类型
	);
删除表

删除表

drop table 表名;//表不存在就会报错
drop table if exists 表名;//如果表存在就删除

快速创建表

create table 表名 as select 字段 from emp where 条件;
原理：将一个查询结果当作一个表新建(表的快速复制)

数据类型

varchar (最长25)可变长度的字符串，根据实际的数据长度动态分配空间，速度慢
char (最长25)定长字符串，分配固定长度空间，速度快
int (最长11)数字中的整数型
bigint 数字中的长整型
float 单精度浮点型
douvle 双精度浮点型
date 短日期 默认格式：%Y-%m-%d
datetime 长日期 默认格式：%Y-%m-%d %h:%i:%s
clob 字符大对象，最大可用存储4G字符，存储文章，说明…
blob 二进制大对象，使用IO流，存储声音，图片…

now()获取系统事件 年月日时分秒

插入insert

insert into 表名(字段名1，字段名2...) values(值1，值2...);
字段名和值要一一对应

插入多条数据

insert into 表名(字段名1，字段名2...) values
		(值1，值2...),
		(值1，值2...);

插入日期

str_to_date：将字符串varchar类型转换成date类型
通常用于插入日期

str_to_date('字符串日期','日期格式')
日期格式 %Y年 %m月 %d日 %h时 %i分 %s秒
如果格式是%Y-%m-%d会自动转换,不用str_to_date()

date_format：将date类型转换成具有一定格式的varchar字符串类型
通常用于查询日期，设置展示的日期格式

data_format(日期格式数据,'日期格式')

修改update

update 表名 set 字段名1=值1,字段名2=值2... where 条件;
!!!没有限制条件会导致所有数据全部更新

删除数据

delete from 表名 where 条件;
!!!没有条件，整张表数据都会删除
原理:删除但是空间没有释放
可用rollback恢复数据

truncate table 表名;
表被一次截断，不能回滚恢复数据

约束

保证表中数据有效
列级约束，在列后面，给单个字段添加约束
表级约束，没有在列的后面，给多个字段添加一个约束

create table nidi(
	id int not null,	//列级约束
	name varchar,
	email varchar,
    unique(name,email)	//表级约束
);

非空约束：not null
唯一性约束：unique
主键约束：primary key (PK)
外键约束：foreign key (FK)

非空约束not nul

列级约束，约束的字段不能为NULL，否则报错

唯一性约束unique

表级约束，约束的字段不能重复，但是可用为NULL

联合唯一，两个字段联合起来具有唯一性,两个数据有一个不同就可以插入

create table nidi(
	name varchar,
	email varchar,
    unique(name,email)
);

主键约束primary key

表级约束，唯一不为空(not null+unique)，相当于身份证号

每一张表都要有主键，否则表无效，且只能有一个
唯一不为空(not null和unique一起用)，该字段变为主键字段
建议使用 int bigint char，不建议使用varchar，一般都为定长

自动维护主键值auto_increment

//auto_increment表示自增，从1开始，以1递增
create table nidi(
	id int primary key auto_increment,
	name varchar,
);
insert into nidi(name) values('luxiaotao');
insert into nidi(name) values('luxiaotao');
insert into nidi(name) values('luxiaotao');
//id自动生成1,2,3

外键约束foreign key

b表受到a表的约束，b表只能添加a表中有的值
实际场景：防止写错导致数据无效，只有100，101班级编号，却添加了102

create table t_class(   //a表
	classno int primary key,
    classname varchar(255)
);
create table t_student(   //b表
	no int primary key auto_increment,
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno)
);
//b表中的cno不能添加a表中classno中没有的

事务start transaction

一个事务就是一个完整的业务逻辑，由多条DML语句同时完成
假设转账，从A向B转10000
将A的钱减去10000(update)
将B的钱加上10000(update)

只有insert delete update和事物有关，其他没关系

事务的执行过程中，每一条DML的操作都会记录到“日志文件”
mysql默认每执行一句DML语句则提交一次
关闭自动提交 start transaction === 开启事务;

提交事务commit
清空日志文件，将数据全部彻底持久化到数据库表中
提交事务标志着，事物的结束，是一种全部成功的结束
回滚事物transaction
将之前所有DML操作撤销，并且清空日志文件
回滚事务标志着，事务的结束，并且是失败的结束

事务的4个特性：
A：原子性
说明事务是最小的工作单位，不可再分
C：一致性
所有事务要求，在同一事务中，所有操作必须同时成功或者同时失败，确保数据一致
D：持久性
事务最终结束的一个保障，事务提交相当于将没有保存到硬盘上的数据保存到硬盘上
I：隔离性
事务A和事务B之间具有一定的距离

隔离性的四个级别
读未提交：read uncommitted
事务A可以读取到事务B未读取到的数据
缺点：读到脏数据

​ 读已提交：read committed（提交之后才读得到）
​ 事务A只能读到事务B提交之后的数据
缺点:不可重复读取数据
​ 事务开启后，第一次读到的事务是3条，事务没有结束，可能第二次读就是4条

​ 可重复读：repeatable read（提交之后也读不到）
​ 事务A开启后，不管多久，每次在事务A读到的数据都是一致的，即使事务B数据已经改变并且提交了，事务A读到的数据还是没有发生改变
缺点：幻影读

​ 序列化/串行化：serializable
​ 事务A操作时事务B就不能操作，不能并发

查看当前隔离级别 select @@transaction_isolation;
改变当前隔离级别 set global transaction isolation level read uncommitted;

索引index

在表的字段上添加，相当于一本书的目录，缩小扫描范围的机制
一个表的一个字段或多个字段联合起来可以添加一个索引
索引要排序，只有才需了才能区间查找
一个字段上有主键PK或唯一约束unique的话，字段会自动创建索引对象

创建索引

create index 索引名字 on 表名(添加索引的字段);
create index emp_ename_index on emp(ename);

删除索引

drop index 索引名字 on 表名;
drop index emp_ename_index on emp;

查看sql语句是否使用索引

explain select * from emp where ename = 'king';

索引失效的几种情况

第一种情况
select * from emp where ename like '%t';
ename即使添加索引，也不会走索引，因为模糊匹配当中以%开头
所以尽量避免模糊查询的时候以%开始

第二种情况
使用or的情况会失效，如果使用or要求or的两边字段都要有索引
少用or 可以用union

第三种情况
使用复合索引的时候，没有使用左侧的字段查找，索引失效
复合索引：两个字段或更多的字段联合起来添加一个索引
creat index emp_job_sal_index on emp(job,sal);
select * from emp where job = '';索引生效
select * from emp where sal = '';索引失效

第四种情况
在where中索引列参加了运算，索引失效
select sal from emp where sal+1 = 800;

第五种情况
在where中，索引列使用了函数
select * from emp where lower(ename) = 'smith';

视图view

只有DQL语句(select)才能以view形式创建

通过对视图的操作，会影响到原表的数据
可以面向视图对象进行增删改查，对视图对象的增删改查会导致原表会操作

实际使用：简化sql语句
假设一条复杂的sql语句，而这条sql语句需要在不同的位置上反复使用，每一次使用这个sql语句都要重新 编写，很长很麻烦
在需要编写这条sql语句的位置直接使用视图对象，简化开发，因为修改的时候只需要修改一个位置，大大简化sql语句

创建视图对象

creat view 视图名字 as select语句
creat view emp_view as select * from emp;把查询结果当作视图

删除视图对象

drop view 视图名字
drop view emp_view;

设计三范式

第一范式：
要求任何一张表上必须有主键，每一个字段原子性不可再分
第二范式：
建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖
第三范式：
建立在第二范式的基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖

执行顺序

select
	...
from
	...
where
	...
group by
	...
having by
	...
order by
	...
limit

执行顺序:1.from 2.where 3.group by 4.having 5.select 6.order by 7.limit
为什么分组函数不能在where中使用？
因为where执行的时候还没分组
那为什么select sum(sal) from emp;可以使用？
因为select在group by之后执行