MySQL

一、MySQL服务的使用

1. 服务器开启和关闭

• 通过windows提供的工具
• 通过cmd进行命令的操作

开启：net start 服务名

关闭：net stop 服务名

2. 通过cmd连接Mysql服务器

• 命令

① mysql -h 127.0.0.1 -P 3306 -u root -p

如果连接的是本机的话，可以省略 -h 和 -P

② mysql -uroot -p

二、SQL

1. sql语句的分类

• DDL: 数据定义语句，对数据库、表的操作(新建库、表、删除库，表，修改表)
• DML:数据操作语句，对表格内的数据进行增删改
• DQL: 数据查询语句（从 DML 中间查询单独拿出来）
• DCL：数据控制语句，例如：grant(用户权限)，commit，rollback（事务）等

2. DDL语言

• 对数据库的操作

# 对数据库的操作
# 新建数据库
create database 数据库名;
#查看数据库
show databases;
#选择数据库
use 数据库名;
#查看创建数据库的sql语句
show create database 数据库名;
# 删除数据库
drop database 数据库名;

• 对表的操作

#新建表
#语法
create table 表名(字段名1 数据类型1 约束条件1,字段名2 数据类型2 约束条件2....);
	#条件：
		① 每列都必须指定数据类型
		② 约束条件可有可无(非空，默认值，唯一键，外键，插件约束条件、自增机制);
		
#删除表
drop table 表名;
#修改表
	# 语法：alter table 表名 具体操作;
	# 新增一列
		#在employees在表内新增salary这一列，数据类型为double
		alter table employees add salary double;
		#在employees在表内新增address这一列，数据类型为varchar(50),加在id的后面
		alter table employees add address varchar(50) after id;
		#在employees在表内新增gender这一列，数据类型为char(1),放在第一列（没有before）
		alter table employees add gender char first;
		
	#修改列的名字
		# 将employees表中将address改成addr(数据类型必须跟上)
		alter table employees change address addr varchar(50);
	#修改列的位置
		# 将employees表中的id移动到第一位
		alter table employees modify id int frist;
		# 将employees表中的gender移动到name后
		alter table employees modify gender char after name;
	#修改列的数据类型
		# 将employees表中的gender的数据类型改为int(0-男，1-女)
		alter table employees modify gender int;
	#删除一列
		# 将employees表中的gender删除
		alter table employees drop gender;
	#修改表名
		alter table employees rename emp;

#查看所有表
	show tables;
#查看表结构
	desc 表名;

3. 数据类型

① 数值型

• 整数

  • int

    int(一般不设置)，如果设置宽度为3时，显示的时候是:001

    最大默认长度：mysql8之前默认是11，mysql8直接去掉了。

  • bigint

• 小数

  • float

  • double

    double(x,y) x是整体长度，y是保留小数的长度

② 字符串

• char

  是字符串不是字符型(可以存储多个字符)

  定长字符串（一旦长度确定，数据库会生成置定长度的空间，来存储数据）

  不指定长度的话，默认为1

• varchar

  变长字符串

  必须指定长度，不指定长度是错误语法

• enum

  枚举、只能在给定范围内n选1

  enum('','','');

• set

  可以在给定范围内n选m

  set('java','打球','追星','rap','跳舞')

  选择值时：'java,rap,打球'

• blob：大字节（字节形式的数据：图片、音频、视频）

• text：大文本（大量普通文本）

  不会讲大容量的内容存储到数据库，而是讲文件存储在服务器的硬盘上，将文件的路径保存在数据库里。

③ 日期

• year 4位年份
• date 日期（年月日）
• time 时间（时分秒）
• datetime 日期+时间
• timestamp 时间戳（日期+时间）

4. 语法规则

不区分大小写，但建议关键字大写，库名、字段名、表名建议小写

命名：26个英文字母大小写，数字0-9，下划线，不要使用其它符号

建议不要使用mysql的关键字来作为表名、字段名、数据库名等，但如果用到关键字起名也可以用(飘号`)

数据库名、表名、字段名中间不要包含空格

同一个mysql中，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名

标点符号必须是英文的，字符串和日期需要加引号

• 注释

  单行注释：# 或 --

  对行注释：/* */

5. DML

5.1 新增

新增语法：insert into 表名(字段列表) values(值列表)；

# 条件：值的列表要和字段列表一一对应
insert into emp(id,addr,`name`,salary) values(1004,'北京',‘迪丽热巴’,3000);
# 简化版(如果新增全部列的数据可以简化,值列表要和表结构的顺序保持一致)
insert into emp values(1005,'北京','过的嘎嘎',3000);
# 多条新增语法
insert into emp(id,name) values(1007,'xx'),(1008,'xdd'),(1009,'ees');
insert into emp values(1010,'bj','xxxs',3000),(1011,'sh','dee',4000);

5.2 查询

1. 简单查询

语法：select 字段名列表/表达式/函数 from 表名

# 查询所有员工的信息
select * from emp;
# 查询所有员工的姓名、工资和奖金
select name,salary,bonus from emp;
# 查询工资大于5000的员工信息
select * from emp where salary>=5000;
# 查询工资大于等于5000并且电话是110
select * from emp where salary and phone='118';
# 查询工资大于等于5000或者电话是110
select * from emp where sarary or phone='110';

2. 算术运算符

+ - * / %

设置别名语法： 列名或表名或表达式或函数 as 别名 （as可以省略）

# 查询所有员工信息和其年薪
select *,salary*12+bonus as `sum` from emp;
# IFNULL 判断是否为null，如果为null则采用后面的默认值
select *,salary*12+ifnull(bonus,10) as `sum` from emp;

3. 关系运算符

> = < >= <= = != <=>

#查询工资大于5000的员工信息
select * from emp where salary >= 5000;

• 关于null的特殊使用

# 查看没有地址的员工信息
select * from emp where addr=null; #错误的， null不能用=
select * from emp where addr<=>null; 
select * from emp where addr is null;

# 查看你有地址的员工信息
select * from emp where addr!=null; #错误的
select * from emp where addr<!=>null; #语法错误
select * from emp where addr is not null;
select * from emp where not addr is null;

4. 逻辑运算符

and or not

# 查询工资在3500到5500
select * from emp where salary>=3500 and salary<=5500;
# 查询工资是4000/5000/3000的员工信息
select * from emp where salary=4000 or salary=5000 or salary salary=3000;
# 查询工资不是3000和4000
select * from emp where salary != 3000 and salary != 4000;

5. 区间判断

between and

in(xxx,xxx) not in(xxx,xxx)

select * from emp where salary between 4001 and 5500;
select * from emp where salary in(4000,5000);
select * from emp where salary no in(4000,5000);

6. 模糊查询

语法：字段名 like '模糊数据'

符号：%:0-n个字符

​ _:1个字符，占位符

# 查询姓张的员工信息
select * from emp where name like '张%';
# 查询以张结尾的名字的员工的信息
select * from emp where name like '%张';
# 查询名字中包含张的员工信信息
select * from emp where name like '%张%';
# 查询姓张且名字为三个字符的员工信息
select * from emp where name like '张__';

7. 排序

order by asc/desc:默认情况为asc升序

# 查询所有员工信息，根据工资升序排列/降序
select * from emp order by salary desc;
# 查询所有员工信息，根据工资降序排列，如果一样，则按照奖金降序排列
select * from emp order by salary desc,bonus desc;

5.3 修改

语法：update 表名 set 字段名1=新值1,字段名2=新值2....[where 条件];

修改语句的修改条件和简单查询语法是一致的。

# 将所有人的薪资增加1000块
update emp set salary=salary+1000;
# 将所有人的薪资增加500块，奖金设置为5000
update emp set salary=salary+500,bonus=5000;
# 将没有地址的员工地址统一设置为西安
update emp set addr='西安' where addr is null;
# 将西安的女员工的薪资增加1000
update emp set salary=salary+1000 where addr='西安' and gender='女'; 

5.4 删除

语法：delete from 表名 [where 删除条件]

删除条件和简单查询条件的语法是一致的

# 删除姓张的员工信息
delete from emp where name like '张%';
# 删除工资低于5000，性别为女的
delete from emp where salary<5000 and gender;

#清空表
# 删除姓张的员工信息
delete from emp where name like '张%';
# 删除工资低于5000，性别为女的
delete from emp where salary<5000 and gender='女';
#清空表
delete from emp; # 一行一行的删除
truncate table emp; #将表格删除，在按照创建一次

	DELETE	TRUNCATE
删除方式	一条一条	删表再建
回滚	提交之前可以回滚	不支持回滚
id自增机制	不会破坏自增机制	会破坏

6. 约束条件

• 功能：对列的数据进行约束

• 6大约束

  非空

  默认值

  主键约束(非空+唯一)

  唯一键约束

  检查约束(mysql8才开始支持)

  外键约束

一个机制：自增机制

语法：create table 表名(字段名 数据类型 约束条件)

6.1 非空约束

# 1. 新建表格位置可以设置
create table demo01(
	id int,
    `name` varchar(20) not null,
    email varchar(20) not null
);

# 2. 将demo01中name的非空删除(DDL修改操作)
alter table demo01 modify name varchar(20);
# 3. 键demo01中name的非空加上
alter table demo01 modify name varchar(20) no null;

6.2 默认值

# 1. 新建表格时添加默认值
create table demo02(
	id int,
    name varchar(20) not null default '某某',
    email varchar(20)
);
# 2. 后期删除默认值约束
alter table demo02 modify name varchar(20);
# 3. 后期再添加默认约束
alter table demo02 modify name varchar(20) default '某某';

6.3 唯一键

所有的键约束的添加都会自动对该列添加索引（提高查询效率，降低增删改的效率）

#1. 新建表个添加唯一键
create table demo03(
	id int,
    name varchar(20) unique key,
    email varchar(20) unique key
);

create table demo03(
	id int,
    name varchar(20),
    email varchar(20),
    unique key(name)
);

• 联合唯一键（参考是一对数据）

create table demo03(
	id int,
    name varchar(20),
    email varchar(20),
    unique key(name,email)
);

• 查看表格内的所有索引

show index from demo03;

# 2. 删除唯一键约束（删除索引即删除唯一键）
alter table demo03 drop index name;
# 3. 新增唯一性约束
alter table demo03 add unique key(name);

6.4 主键约束

# 1.新建表格
create table demo04(
	id int primary key,
    name varchar(20)
);
create table demo04(
	id int,
    name varchar(20)，
    primary key(id)
);
# 联合主键
create table demo04(
	id int,
    name varchar(20),
    primary key(id,name)
);

# 后期删除主键约束
alter table demo04 drop primary key
# 后添加主键约束
alter table demo04 drop primary key(id,name)

6.5 检查约束

# 1. 创建表格添加约束
create table demo05(
	id int,
    name varchar(50),
    age int check(age>0 and age<120)
);

create table demo05(
	id int,
    name varchar(50),
    age int,
    check(age>18 and age<60)
);

查看约束名

select * from information_schema.table_constraints where table_name='demo05';

# 2. 后期删除检查约束
alter table demo05 drop check demo05_chk_1
# 3. 后期添加检查约束
alter table demo05 add check(age>8 and age<60)

7.6 外键约束

涉及到两张表（主表，从表）

外键约束

主表（被参考的表） 从表（参考其他表的表

create table dept(
	id int primary key,
    name varchar(50),
    address varchar(50)
);

• 从表上添加外键约束
  • 主表必须是已存在的
  • 主表被参考的列必须是键列

create table emp01(
	id int primary key,
    name varchar(50),
    age int,
    dept_id int,
    foreign key(dept_id) reference dept(id)
);

select * from information_schema.table_constraints where table_name='emp-1';
# 2.删除外键约束
alter table emp01 drop foreign key emp01_ibfk_1;
#删除外键约束的时候，自动创建的索引不给删除（）
alter table emp01 drop drop index dept_id

# 3. 添加外键约束
alter table emp01 add foreign key(dept_id) references dept(id);
# 不删外键，删索引(和唯一键不同，删索引不会导致外键的删除)
alter table emp01 drop index dept_id;

# 4. 如果有外键约束的话，删除数据和修改数据就有一些限制
# 如果删除列对应的列还有信息，就不能删除，没有信息了，就可以删除了。

# 设置外键约束，并且设置约束等于为 CASCADE/SET NULL
alter table emp01 add foreign key(dept_id) references dept(id) on update cascade on delete cascade;

alter table emp01 add foreign key(dept_id) references dept(id) on update set null on delete set null

8. 自增长

条件：① 需要是主键 ② 整数列

# 1. 创建表格时添加
create table demo06(
	id int primary key auto_increament,
    name varchar(20),
    age int
);
# 2. 删除自增机制
alter table demo06 modify id int;
# 3. 添加自增机制
alter table demo06 modify id int auto_increment;

三、函数

1 分组函数

分组函数：统计范围所有的查询结果

count(字段名)
sum(字段名)
avg(字段名)
max(字段名)
min(字段名)

# 求出所有员工的个数
select count(*) from emp01;
# 求出年龄在18岁以上的员工个数
select count(*) from emp01 where age>18;
# 统计平均年龄
select avg(age) from emp01
# 查询最大年龄
select max(age) from emp01
# 查询最小年龄
select min(age) from emp01
# 统计age的总和
select sum(age) from emp01

2 分组函数遇到分组

• 统计范围在本组
• 语法：group by 去重操作
• 统计的每个部门的平均年龄、总年龄、最大年龄、总人数
• 分组(一样的数据自动分为一组)

select dept_id,avg(age) as agvage,sum(age),max(age),min(age),count(*) from emp01 group by dept_id;

# 统一每个部门的平均年龄，员工要求18岁以上
select dept_id,avg(age),count(*) from emp01 where age>18 group by dept_id;

# 员工要求18岁以上，统一每个部门的总人数，只要总人数大于1人的
# having 添加筛选条件(在分组函数后添加筛选条件)

select dept_id,avg(age),count(*) from emp01 where age>18 group by dept_id having count(*)>2;

3 分页

语法：sql语句 limit n,m

n: 开始的索引位置

m: 数据条数

# 第一页
select * from emp01 limit 2
# 0开始的索引位置
select * from emp01 limit 0,2
# 第二页
select * from emp01 limit 2,2
# 第三页
select * from emp01 limit 4,2
# 第四页
select * from emp01 limit 6,2

# 年龄大于18的员工信息，要第二页
select * from emp01 where age>18 limit 2,2;

4 单行函数

1. 数学相关

select ceil(1.1) 2 向上取整

select floor(1.6) 1 向下取整

select rand() 获取0~1之间的随机数

select round(3.1415926,2) 保留2位小数 四舍五入

select truncate(3.1415926,3) 直接保留2位小数

select pow(5,2) 5的2次方

2. 字符串相关

# 字符串的无缝拼接
select concat('java','mysql','jdbc');

# 查询所有员工信息，要求name和年龄拼接显示
select id,concat(name,age),dept_id from emp01;
select id,concat_ws('-',name,age),dept_id from emp01;

# 查询name和name的字符长度和字节字节长度
select name,char_length(name),length(name) from emp01;

# 从左边截取n位
select left('www.atguigu.com',5); 

#用 by 从 java 的左侧填充到长度位7
select lpad('java',7,'by');# bybjava

# 去除首部的 ‘a’ both:首尾 leading:首 trailing:尾
select trim(both/leading/trailing 'a' from 'aaajavaaaa');  #javaaaa

# 将‘www.atguigu.com’ 从第2位到第5位替换为java
select insert('www.atguigu.com',2,5,'java');#wjavaguigu.com

# 将‘www.atguigu.com’ 中 'atguigu' 替换成 ’guoji‘
select replace('www.atguigu.com','atguigu','guoji'); #www.guoji.com

3. 日期相关

# 日期函数
select curdate(); #当前日期
select curtime(); #当前时间
select now(); #当前日期时间

# 查询所有信息，包括主人的年龄
select *,year(now())-year(owner_birth) age from pet;

select data_format(now(),'%Y年%m月%d日 %H时%i分%s秒')

4. 系统函数

select database(); #当前的数据库
select version(); #当前的mysql的版本
select user(); #当前用户

5. 加密函数

select md('root');

6. 判断函数

• 条件判断函数

select if(5<3,5,6);

• case-when 判断范围

# 查询所有员工信息，年龄低于20的，显示实习，在20至25，显示正式，大于25，显示元老
select *,
	case 
		when age<20 then '实习'
		when age>=20 and age<=25 then '正式'
		else '元老'
		end as '说明'
from emp01;

• case 字段名 when 根据值判断

 # 查询所有员工信息，18->小,20->中,25->居中,30->ok
 select *,
 case age 
 when 18 then '小'
 when 20 then '中'
 when 25 then '居中'
 when 30 then 'ok'
 end '说明'
 from emp;

四、关联查询

1. 关联关系

① 一对多

dept表：id primary key

emp表：dept_id
constraint emp_ibfk_1 foreign key(dept_id) references dept(id);

② 多对一

③ 一对一

wife表：id primary key

husband表：wife_id
unique key wife_id(wife_id);

④ 多对多

course表：id primary key

student表：id primary key

student_course表：primary key(stu_id,cou_id);

2. 关联查询

① 内连接

特点：没有关联关系的数据是查不出来的

select * from emp,dept; # 存在笛卡尔积现象

• 通过表名.字段名区分不同的属性

select * from emp,dept where e.id=dept.id; #92
select * from emp inner join dept on e.id=dept.id; #95

# 查询人事部的信息(包括其部门的员工信息)
select * from emp,dept where emp.dept_id=dept.id and dept.name='人事部';
select * from emp inner join  dept on emp.dept_id=dept_id where dept.name='人事部';

# 查看康师傅的信息（包括其部门信息）
select * from emp,dept where emp.dept_id=dept.id and emp.name='康师傅';
select * from emp inner join dept on emp.dept_id=dept.id where emp.name='康师傅';

② 左外连接 右外连接

特点：左外是将左侧表的数据全部显示，右侧表没有关联关系数据用null补齐，右外同理

# 查询所有员工信息(包括其部门信息，如果没有部门显示为null)
#左外连接
select * from emp left join dept on emp.dept_id = dept.id
#左外连接
select * from dept right join emp on emp.dept_id = dept.id

# 查询所有部门的信息(包括其员工的信息，没有员工的显示)
select * from emp right join dept on emp.dept_id=dept.id;
select * from dept left join emp on emp.dept_id=dept.id;

③ 全外连接(MySQL 不支持，union 去替代)

特点：两表数据都显示，没有关联关系的数据用null补齐

要求：必须一左一右

# 查询所有员工和部门的信息(没有关联关系的都用null补齐)
select * from emp left join dept on emp.dept_id=dept.id
union
select * from emp right join dept on emp.dept_id=dept.id;

⑤ 多对多练习

# 查询张三的信息(包括其选课信息)
select * from student s join course c join student_course sc on s.id=sc.stu_id and c_id=sc.cou_id where s.name='张三';

# 查询java课程的信息(包括被哪些学生选了)
select * from student s join course c join student_course sc on s.id=sc.stu_id and c.id=sc.cou_id where c.name='java';

⑥ 自查询

# 查询陕西省的信息(包括其市的信息)
# 将一张表格看作两张表格(需要器不同的别名即可)
select * from city c1 join city c2 on c1.id=c2.pid where c1.name='陕西省';

五、select 的七大子句

select 内容(字段名、函数、表达式)
① from 表名
② on 设置关联条件（去除笛卡尔积）
③ where 设置筛选条件
④ group by 字段名
⑤ having 筛选条件(分组之后进行操作)
⑥ order by 排序
⑦ limit 分页

六、 子查询

sql 语句的嵌套

1. select 后嵌套sql语句

# 查询出所有员工信息，计算出每位员工的工资和平均的差值

#方法一：
# 1. 计算出所有员工的平均值
select avg(salary) from emp;
# 2. 作为第二次查询的运算数
select *,salary-41670 from emp;

# 3. 二合一
select *,salary-(select avg(salary) from emp) 差值 from emp;

2. 放在 where 或者 having

# 查询刘亦菲同部门的员工的信息

# 1.查询刘亦菲所在的部门
select dept_id from emp where name='刘亦菲';
# 2. 二次查询,根据部门id寻找员工信息
select * from emp where dept_id=1;
# 二合一
select * from ep where dept_id=(select dept_id from emp where name='刘亦菲');

# 查出比1部门工资都高的员工信息
select * from emp where salary > (select max(salary) from emp where dept_id=1);
select * from emp where salary>all(select salary from emp where dept_id=1)1;

# 查出比1部门工资高的员工信息
select * from emp where salary < (select min(salary) from emp where dept_id=1) and dept_id!=1;
select * from emp where salary<any(select salary from emp where dept_id=1) and dept_id!=1;

3. 放在from后

# 查询出所有部门的平均工资，平均工资高于30000的部门信息
select * from　(select dept_id,avg(salary) savg from emp group by dept_id) allavg where allavg.savg > 3000;

4. 修改和删除语句中

# 修改人事部的工资上调了1000元
update emp set salary=salary+1000 where dept_id=(select id from dept where name='人事部' );

# 删除测试部的员工
delete from emp where dept_id = (select id from dept where nam='测试部');

5. 表进行备份

# 只复制表结构，没有数据
create table t_emp like emp;

# 将数据添加进去
insert into t_emp values(select * from emp);

# 二合一
create table t_emp(select * from emp where id > 1);

七、事务

1. 事务的特点

1.事务的处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事物都被提交(commit), 那么这些修改就永久地保存下来了；要么数据库管理系统将放弃所作的所有修改，整个事务回滚（rollback）到最初状态

2. 事务的ACID的属性

**原子性(Automicity)：**不可分割

**一致性(Consistency)：**执行前和执行后的总数是不变的

**隔离性(Isolation)：**幻读、不可重复读、脏读

**持久性(Durability)：**持久化了

• 关于隔离性：

  脏读：一个事务读到了另一个事物未提交的数据

  不可重复读：一个事务两次读取的单条数据需要保持一次

  幻读：一次事务两次读取表格的数据需要一致

• 关于隔离级别

  读未提交：可以读到其它的事务为提交的数据

  读已提交：可以读到其它事务已提交的数据（解决脏读）

  可重复读：当一个事务对一条记录进行读取，其它事务是无权对该条记录进行修改操作（解决脏读、不可 重复读）

  串行化：当一个事务读取一张表的数据时，其它事务无权对这张表进行修改操作（都可以解决）

# 将提交方式设置为手动提交
set autocommit=FALSE;
# 进行n个新增、删除、修改的操作
update emp set salary=salary-1 where name='刘亦菲';
update emp set salary=salary+1 where name='张三';

# 提交
commit;

# 回滚
rollback;