MySQL

创建数据库

CREATE DATABASE [IF NOT EXISTS] db name #如果该数据库已经存在，不会报错，否则报错
[create_specification[create_specification]….
[DEFAULT] CHARACTER SET charset name[DEFAULT COLLATE collation name

1.CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf8
2.COLLATE: 指定数据库字符集的校对规则(常用的utf8_bin)[区分大小写]
意默认是(utf8 general_ci)[不区分大小写]

2. 查看和删除数据库

1. 显示服务器所有的数据库的语句

show databases;

2. 显示指定数据库的创建信息

show database db_name;

dos命令行指定数据库

use database_name;

3. 删除数据库

drop database db_name;

4. 备份和恢复数据库

1. 备份数据库（必须在DOS命令行下执行）

mysqldump -u root - p -B 数据库1 数据库2 数据库3..... > 文件名

2. 恢复数据库（必须在MySQL终端行下执行）

source 文件名

文件名可带路径

3. 备份表

mysqldump -u root - p 数据库表1 表2..... > 文件名

二、MySQL的数据类型

枚举类型语法字段 ENUM(其中值)，

三、创建表

CREATE TABLE table_name (
        field1 datatype,

        field2 datatype,

        field3 datatype

......
)

character set 字符集

collate 校对规则

engine引擎 field:指定列名

datatype:指定列类型(字段类型)

character set :如不指定,则为所在数据库字符集

collate: 如不指定则为所在数据库校对规则
engine:指定引擎

四、操作表（未指定数据库）

1. 增加列/字段

alter table 表名 add 列名指定类型 not null default " " after 表名; -- 是指定位置

实例：emp表上增加一个 image 列/字段 varchar 类型要求在 resume 列之后，要求非空，默认 ' '

alter table emp add image varchar(50) not null default ' ' after resume;

2. 修改列/字段

alter table 表名 modify 被修改的列名指定类型 not null default ' ' ;

案例：修改 emp表的job列，更改为varcahr(80) 类型，不为空，默认 ' '

alter table emp modify job varchar(80) not null default " ";

2.1 修改列名/字段

alter table 表名 change 表名新列名指定类型 not null default ' ' ;

案例:把emp表中id列更改为ID；

alter table emp change id  ID not null default ' ' ;

3. 删除列/字段

alter table 表名 drop 列名;

案例 :删除emp表中的sex列

alter table emp drop sex;

4. 更改表信息

rename table 表名 to 新名字

案例：把表名为emp的更新名字为employee

rename table emp to employee

5. 修改表名编码

alter table 表名 character aet 编码 --或者charset 编码

alter table emp charset utf8;

六、MySQL CRUD语句

1. insert 向表中添加数据

insert into 表名（参数列表）values (对应的数据); --values(),(),(),(),.. 可以增加多条信息

1. 如果对表中的所有字段进行复制参数列表可以不写

案例:向emp表中增加一条记录

create table emp (id int , `name` varchar(20), sex varchar(6) default '女'，salary double); 

insert into emp (id , `name` , sex) values (1 , '健康生活', '男',1200);--增加一条数据

insert into emp (id , `name` , sex) values (1 , '健康生活', '男',1300), (2 , '年后' ,'女',2300);
--增加两条数据

default 可给一个默认值

2. update 修改表中一列数据

update table_name set col_name = newInformation where 修改哪一行信息一般为表头信息

不写where相当于修改col_name字段下的所有信息

set col_name1 = new1,col_name2 = new2 可修改多个信息

案例:修改emp表中年后的 salary基础上加1000，sex 设置为男

update emp set salary = salary + 1000 ,sex = '男' where name = '年后';

#修改了 年后 信息的 salary sex 字段 ， 不加where 则 把 salary sex 字段下的信息，全部修改为 +1000 男

3. delete 删除表中数据

delete from table _name where [where_def]

不加where 会删除表中所有记录 , 但是不会摧毁表，

彻底删除表 drop table table_name;

案例：删除emp表中的'年后'的信息

delete from emp where 'name' = '年后';

4. select 查询语句

1. 基本语法

select (distinct) * | (column1, column2,column3) from table_name

1 distinct 是可选的，表示是否过滤掉相同信息，只有查询的内容全部相同

2. * 代表查询所有表中字段信息

3. column 是字段的名字，代表仅查找这些字段的信息

4. from 代表查询那张表

案例:查询表emp中的所有字段的信息

select * from emp;

--仅查看name字段

select name from emp;

2. 使用查询到的列进行运算

select (chinese ,english, math) form table_name; -- 统计表中语数外所有成绩之和

select (chinese ,english, math) as total_name from table_form; -- as 做一个别名，显示表的字段上

3. where 过滤器

1. 运算符

日期类型可以直接比较

R between M and N 表示 R 在【M，N】区间

like 关键字的使用 % 表示0个多个字符 _ 表示打单个字符

案例 : 取出员工名以H开头的员工
select * from emp where enam like'H%';
案例：取出第三个字符为Q的员工
select * from emp where ename like '__Q%';

4. order by 排序（最后）

order by 列名 Asc | Desc , 列名 Asc | Desc

Asc （升序默认）

Desc （降序）

注意：后面可以加 , 表示多个排序

案例：针对 student 表中math 成绩降序排序

select name,math form student order by(math) desc;

七、select语句函数

1. count 统计函数

语法 select count （*）| count （列数）where 过滤条件

count（*）和count（列数的区别）

count（*）是对满足所有条件的列数总和

count（列名）可以排除为null值的情况

案例：总计一个班级所有数学成绩大于80的人数

select count (name) from student where math > 80;

2. sum 求和函数

语法 select sum（cop1）{，sum（cop2）...} from table_name where 过滤条件

1 . 对多个列求和，逗号不能少

2 . 针对数值型，非数值型不可运算

案列：对一个班级数学总成绩和英语的平均成绩

select sum (math) as math_total_sorce , sum (english) / count(english) from student;
--可以用as去一个别名

3. avg 求平均分

语法 select avg (列名){ ,avg(列名2) } from table_name;

案列：对一个班级的数学，语文，英语，各科成绩求平均分

select avg(chinese) , avg (math) , avg (englist) from student;

4. max(最大值) min(最小值)

语法 select max/min （列名）{，mix/min（列名2）} from table_name;

对一个字段取最大值和最小值

案列：取语文成绩最高分，和数学成绩最低分

select mix(chinese),min(math) from student;

5、分组函数

语法：select com1，com2.... from table_name group by com having ....

groub by 通过分组，针对其相同的统计。

having 筛选，后面写要筛选的条件

案列:对一个员工表，通过部门分组，统计最大值最小值和总的工资

select sum(salary) , mix(salary), min (salary) from emp groub by dep;

案列：对上一个案例，筛选出，总工资大于3000；

select sum(salary) as sum_salary , mix(salary), min (salary)
 from emp groub by dep having sum_salary;

--通过别名

6、字符串函数

charset (string)	返回字符串的字符集
concat (string,[.......string])	拼接多个字符串
instr(string , substring)	返回substring 在string的位置，没有则返回0
ucase（string）	返回string的大写
lcase（string）	返回string的小写
left（string ，length）	从字符串string左边去length的字符串
substring(string , strart, end)	截取字符串，如果没有end，则从 start 开始到结束。从1开始
Ltrim （string）rtrim（string） trim（string）	去掉左边空格去掉右边空格去掉左右空格
length（string）	string的长度【按照字节】
replace（str ，search_str, replace_str ）	在总字符串的str 把子串的search替换replace_str
strcmp(string1 , string2)	比较两个字符串的大小

案例: 把员工名字的首字母大写

select concat (ucase(substring(ename,1,1)),substring(ename,2)) from emp;

7、数学函数

abs（num）	返回num的绝对值
bin（num）	十进制转二进制
hex（num）	转换成十六进制
ceiling（num）	向上取整
floor（num）	向下取整
conv（num from_base ,to_base）	把from_base 禁止的 num 转换成 to_base进制
format （num ， decimal）	把数 num 四舍五入到 decimal 位数
least （num1，num2，num3.....）	得到最小值
mod（num ，m）	取余，数num 对 m取余
rand（seed）	随机数， 0 <= rand() <= 1.0 传入种子，随机数不会变化

8、日期函数

案例：计算1998-7-08 到 2022 -8-16 的有多少天

select datediff ('2022-08-16', '1998-07-08') from DUAL

--DUAL 是系统自带的表，可以用来测试

案例：计算你到现在时间活了多长时间

select timediff (now(), '2004-07-16') from dual;

2.

9. 加密和系统函数

--查看当前用户和IP
select user();

--查看当前数据库名
 select database;

--创建一个用户表 
create table user (id int , `name' varchar(18), pas varchar(32));


insert into user values (1,'小鲨鱼',MD5(123456) );

--添加信息，密码加密用于保护;

10. 流程控制函数

11. 分页查询

总结：如果出现 1. 分组（group by），2. 排序（order by），3. 分页（limit）

八、多表查询

select * from emp ， dep；

默认查询多表的结果是笛卡尔积，也就是第一个表的第一行与第二个表的每一行进行组合

返回的表的记录 = 表1 的行数 * 表2的行数

需要用 where 过滤

遇见两张表的字段一样时，需要用表 . 字段来区分

多表查询的 过滤条件 不能少于 表数-1

select emp1.* from emp1,emp2;
--表示查询emp1的所有字段

2. 自连接 -- 将同一张表看成两张表

select * from emp worker , emp boss;

查询二此同一张表必须取别名 , 就相当于 worker boss 两张表，不过内容一样，在通过筛where 选，获得你想要的信息

select 取别名需要 as new_name

from 之后取别名 name new_name

对于这种表查询员工和对应的上司

select emp.ename, boss.ename from emp woker ,emp boss where worker.mgr = boss.empno;

2. 子查询--嵌套select 语句

（1）单行子查询--只返回一行数据的子查询语句.

select * from emp where depton = (select depton from emp where ename = "SWITCH");

--查询 与Switch 相同部门的 成员信息

(2）多行子查询--返回多行语句的子查询语句需要用 in 连接.

select distinct job from emp where depton = 10

--distinct 表示不重复的结果

select ename,salary from emp where job in (select distinct job from emp where depton = 10);

--查询多个工作相同的成员信息

（3）把子查询当做一个临时表，进行查询；

(4) 多列子查询

语法 select * from emp where（字段1, 字段2, ....） = 子查询

表示查询字段1和字段2。。都相等的信息

3. 表复制-蠕虫复制

表去重

4. all 和 any

all 代表所有

select ename salary from emp where salary > all(select salary from emp where depton = 10 )

any 代表其中一个

4. 合并查询

1. union all --简单合并，不会去重

2. union -- 合并但去重记录

将两个查询记录合并

5. 外连接

1. 左外连接

语法 : select....from 表1 left join 表2 on 条件

即使表1的内容不满足条件，也把表1 完全显示

2. 右外连接

语法 : select....from 表1 right join 表2 on 条件

即使表1的内容不满足条件，也把表2 完全显示

表1 是左表表2 是右表

九、约束

1. PRIMARY KEY--主键

#1. 主键的值唯一且不能为空
#2. 一张表只能有一个主键，但可以是复合主键

3. 主键指定的方式有两种

#(1)字段 primary key

2）在表的后面，声明 primary key (字段)

#4. 使用 desc 可以查看主键情况

2. not null （非空）

3. unique 唯一(null 可以为多个)

4. 外键

foreign key (从表字段) references （主表/主键/unique字段）

1. 主表必须有主键（primary key）约束或者 unique 约束

1 外键指向的表的字段，要求是primary key 或者是 unique
2.表的引擎类型是innodb,这样的表才支持外键
3.外键字段的类型要和主键字段的类型一致(长度可以不同)
4. 外键字段的值，必须在主键字段中出现过，或者为null[前
提是外键字段允许为null]
5. 一旦建立主外键的关系，数据不能随意删除了。

5. check --约束字段在某一个范围合法

mysql5.7 只是校验语法，但不生效

十、自增长

auto_increment

1. 一般来说，自增长配合主键使用：字段类型 primary key auto_increment

2. 也可以单独使用，但必须配合 unique

3. 自增长修饰的字段类型一般是整型的（小数也可以，较少使用）

4. 每次加入自增长的值就是当前表中的最大值 + 1

1. 指定字段自增长

2. 对自增长字段插入值

3. 更改自增长的初始值

十一、索引（提高查询速度）

1. 索引特点 --大幅度提高查询效率

默认查询进行全表扫描，效率较慢.

加了索引，会把表改变成二叉树形式.

索引针对一个字段对于其他的字段查询不会提升

索引缺点

1. 占用磁盘空间

2. 对（update delete insert）语句有效率影响.

2. 索引类别

1. 主键索引，一个字段为主键（primary key），则该主键就是一个索引

2. 唯一索引，（unique）.

3. 普通索引（index）

4. 全文索引，建议使用 solr 和 ElasticSearch （ES）

3. 创建索引

查询该表的索引

show indexs from table_name;

1. 主键索引

(1) create table table_01 (id int primary key,`name` varchar(32)) -- 这个id 为该表主键，也是索引

(2) alter table_name add primary key (列名)；

2. 唯一索引

（1） create table table_01 (id int unique,`name` varchar(32)) -- 这个id 为该表唯一，也是索引

（2） create unique index（索引名称）on table_name (列名)

某个字段不允许相等，就创建唯一索引

3. 普通索引

（1）create index （索引名称）on table_name (列名)

4. 删除索引

drop index (索引名称) on table_name

alter table table_name drop primay key #删除主键索引

5. 创建规则

1. 查询频率高 的字段创建字段

2. 更新频繁的字段不适合创建索引

十二、事务操作(针对 dml 【delete,update,insert】】)

概念：事务操作是针对一组的dml语句，要么全部成功，要么全部失败如：转账，保证数据的一致性

单方向的回滚

（1）事务的基本操作

start transaction	开启一个事务
savepoint	保存点
rollback to 保存点	回滚到保存点
rollback（没有保存点）	回滚到事务开始
commit （提交）	修改提交，不可回滚

事务操作细节

表的引擎需要innodb myisam 不支持

2. 事务隔离级别

（1）三种读数据的错误

1. 脏读：当一个事务读取到另一个事务 尚未提交的数据就产生脏读。

2. 不可重复读：同一查询在同一事务中多次执行，由于其他事务提交所做的删除或修改，结果查询的结果集不同，产生不可重复读.

3. 幻读：同一查询在同一事务中多次执行，由于其他事务提交的插入操作，结果查询的结果不同，产生幻读.

（2）四种隔离级别会发生对应的错误

隔离类别	脏读	不可重复读	幻读	锁
Read uncommitted (读未提交)	V	V	V	X
Read committed （读已提交）	X	V	V	X
Repeatable Read (可重复读)	X	X	X	X
Serializable （可串行化）	X	X	X	V

（3）设置/查询隔离级别

(1) 查询

1 查询当前会话的隔离级别 : select @@tx_isolation

2. 查询当前系统的隔离级别 : select @@global.tx_isolation

（2）设置

1. 设置当前会话的隔离级别 : set session transaction isolation level 【4 隔离级别】

2. 设置系统的隔离级别： set global transaction isolation 【4 隔离级别】

系统默认是 repeatable Read ，一般不会

（4）事务的 ACID 特点

1. 原子性（Atomicity）

事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另一个一致性状态

3. 隔离性（Isolation）

多个用户连接到数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发的事务之间要相互隔离

4. 持久性（Durability）

一个事务一旦被提交，他对数据库中的数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

十三、引擎

（1）三种常用引擎选择

1. 不需要事务，只完成简单的crud 使用 MyIsam

2. 需要事务---使用innodb

3. 数据频繁更新，存储无意义的，使用 Memory （基于内存，重启MySQL数据消失）

（2）修改表的引擎

alter teable table_name engine = 引擎类型

十四、视图

对于一张表，根据查询用户的权限，从而查看对应的数据，并不是表中全部数据用户可以查看.

1. 视图原理

视图是一个虚拟的表，内容有查询确定，其中数据来自对应真实表（基表）

1. 视图根据基表创建，一个虚拟的表.

2. 同样有字段，数据来自基表.

3. 通过视图可修改基表数据.

2. 视图基本使用

1. create view view_name as select 语句 -- 创建视图

2. alter view view_name as selectyu语句 -- 对视图重新定义

3. show create view view_name -- 显示创建视图的指令

4. drop view view_name1,view_name2 -- 删除视图

3. 视图应用

1. 安全，对于用户不可查看的保密字段，进行视图

2. 性能

3. 灵活，更新表，如果新表的结果差不多，可以通过视图实例化一个表，完成对表的更新

十五、用户管理

根据不同的开发人员，赋予其不同的权限.

用户保存在 mysql 数据库中的 user 表

1. 用户操作

1. 创建用户，同时指定密码

create user '用户名' @'允许登陆的位置' indenified '密码'

2. 删除用户

drop user '用户名' @'允许登陆的位置' ；

3. 修改自己的密码

set password = password( '新密码' )

4. 修改别的用户修改密码（需要有其权限）

set password for '用户名' @'允许登陆的位置' = password( '新密码' )

2. root 权限管理

参考韩顺平老师

2. 基本语法

grant 权限列表 on 库.对象 to '用户名' @'允许登陆的位置' 【 indenified by '密码'】

【简要说明】

1. 权限列表为权限之间逗号隔如： select ，update，delete on ...

2. *.* ：代表本系统的所有数据库中的所有对象

3. 库.* ：代表某一个数据库中的所有数据对象

4. indenified by '密码' 可以省略，也可以写出

如果写出则，如果该用户存在则修改密码，如果不存在则创建用户