MySQL基础知识

一、SQL四种分类：DDL、DML、DQL、 DCL

DDL:用来定义数据库对象（数据库，表，字段）

DML：用来对数据库中数据进行增删改

DQL：查询数据

DCL：用来创建数据库用户、控制访问权限

1.DDL

数据库操作

• 查询数据库：show databases              查询所有数据库

                             show databases()            查询当前数据库

• 创建数据库  create database (if not exists)  数据库名 (default charset 字符集（utf8mb4）)  (collate 排序规则)
• 删除数据库   drop database 数据库名
• 使用数据库 use 数据库名

 表操作

• 创建表

CREATE table temp(
id int COMMENT '编号',//comment 是注释
name VARCHAR(50) COMMENT '姓名',
age int comment '年龄',
gender varchar(10) comment '性别',
birthday date comment '生日'
);

• 添加字段

alter table 表名 add 字段名 字段类型 comment 注释

• 修改数据类型/字段名+数据类型

//修改数据类型
alter table 表名 modify 字段名 新数据类型(长度)

//修改字段名和数据类型
alter table 表名 change 旧字段名 新字段名 数据类型(长度)

•  删除一列

alter table 表名 drop 要删除的字段名

•  修改表名

alter table 表名 rename to 新表名

•  删除表名

drop table 表名

 2.DML

• 添加数据

//向指定字段添加数据
insert into 表名(字段名) values(数据)

//向所有字段添加数据
insert into 表名 values(数据)

•  修改

update 表名 set 字段名=值 (where条件)

•  删除

delete from 表名(where 条件)

 3.DQL

• 基本查询

//查询所有数据 *
select * from 表名

//别名 as
select 字段名 as 别名 from 表名

//去重查询 distinct
select distinct 字段名 from 表名

•  条件查询 where

select 字段名 from 表名 where 条件

 

•  聚合函数（count  min max  avg sum）

select 聚合函数(字段名) from 表名

•  分组查询 group by

//where 是分组前过滤
//having 是分组后过滤
select 字段名 from 表名 （where条件) group by 分组字段名 （having 分组后过滤条件） 

•  排序查询 order by  多重排序使用 ，

// asc 升序
// desc 降序
select 字段名 from 表名 order by 排序字段名 升序/降序

• 分页查询 limit

select 字段名 from 表名 limit 起始索引 查询记录数
//起始索引 = （查询页码 - 1） * 查询记录数
//查询记录数 = 每页显示几条记录

• 执行顺序
• from > where > group by > having > select > order by > limit

4.DCL 

•  查询用户

use mysql
select * from user

•  新增用户

// localhost  =   本主机
// %  =  所有主机
create user '用户名'@'主机名' identified by '密码'

•  修改用户密码 

alter user '用户名'@'主机' identified with mysql_native_password by '新密码'

•  删除用户

drop user 用户名

•  用户权限

•  查询用户权限

select grant for '用户名'@'主机'

•  修改用户权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'

•  撤销用户权限

revoke 权限列表 on 数据库名.表名 to '用户名'@'主机名'

二、函数

• 字符串函数 

•  数值函数

• 日期函数 

• 流程函数 

 三、约束

  

•  添加外键

alter table 表名 add constraint 外键名 foreign key(要设外键的列名) references 表名(列名)

•  删除外键

alter table 表名 drop foreign key 外键别名

•  外键删除和更新行为

四、多表查询

• 内连接 ：两张表交集的部分
• 隐式内连接  

select 字段列表 from 表名1，表名2 where 条件

•  显示内连接

select 字段名 from 表名1 inner join 表名2 on 连接条件

• 左外链接-包含表1数据和表1和表2集合的数据

select 字段名 from 表1 left outer join 表2 on 条件

•  右外链接 -包含表2数据和表1表2集合的数据

select 字段名 from 表1 right outer join 表2 on 条件

• 自连接  

select 字段名 from 表1名 表1别名 join 表2名 表2别名 on 条件

• 联合查询 - union      union all

//union all  将 两个select查询后的结果合并
//列数和字段类型要保持一直
//union会对合并后的数据去重
select * from 表名 where 条件
union all
select * from 表名 where 条件

• 子查询
• 标量子查询-子查询返回的值是单个的值

select name from student where tem_id = (select id from tem where name = '研发部')

• 列子查询-子查询返回的是一列   in、not in、all、any、some  

select name from student where tem_id in (select id from tem where name in('售前部','研发部'))

•  行子查询-子查询返回的是一行 =、!=、in、not in、

select * from student where (gender,tem_id) =(select gender,tem_id from student where name ='王五')

• 表子查询-子查询返回一张表  in

select * from student where (gender,tem_id) in (select gender,tem_id from student where name ='王五' or name = '张三')

五、事务

事务是一组操作，他是一个不可分割的工作单位，事务会把所有操作数作为一个整体向系统提交或撤销请求，要么同时成功，要么同时失败

//方法一
//修改事务提交的提交方式，将提交改成手动提交
select @@autocommit;     //1 = 自动提交    0 = 手动提交

set @@autocommit = 0;


//提交事务
commit


//事务回滚
rollback


//方法二
//开启事务
start transaction

//提交事务
commit


//事务回滚
rollback

• 事务四大特性：原子性，一致性，隔离性，持久性
• 原子性：事务是不可分割的最小单元，要么全部成功，要么全部失败
• 一致性：事务完成时，必须使所有的数据都保持一致状态
• 隔离性：数据库系统提供的隔离机制，必须使事务在不受外部并发操作影响的环境下进行
• 持久性：事务一旦提交或回滚，数据库中的数据的变化是永久的

• 并发事务问题：脏读，不可重复读，幻读
• 脏读：一个事务读到另一个事务还没提交的数据。（一个事务还没提交数据，另一个事务就执行了读操作）
•  不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。
• 幻读：一个事务按条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了。

•  事务隔离级别

•  查看系统隔离级别

select @@transaction_isolation

• 修改系统隔离级别

set session/global transaction isolation level 隔离级别 ;