Mysql学习笔记（一）

一、基础概念

1.术语

• 数据库(DB)
• 数据库管理系统(DBMS)
• SQL(Structured Query Language)

2.关系型数据库（二维表）

二、SQL

1.分类

• DDL(Data Definition Language)数据定义语言（操作数据库、表、字段）
• DML(Data Manipulation Language)数据操作语言（增删改）
• DQL(Data Query Language)数据查询语言（查）
• DCL(Data Control Language)数据控制语言（控制权限、用户）

2.DLL

*[...]为可选参数

操作数据库

　　1)show databases;

　　2)select database();

　　3)create database [ if not exists] 数据库名[ default charset 字符集 ][ collate排序规则 ]

　　e.g create database itNM default charset utf8mb4;

　　4)delete database [ if exists ]数据库名;

　　5)use database;

操作表（查询创建）

　　1)show tables

　　2)desc 表名;（查看表信息）

　　3)show create talbe 表名;（查询制定表的建表语句）

　　4)create table 表名( 字段1 字段1类型 [ COMMENT 字段1注释],....)[ COMMENT 表注释 ];

?

1 2 3 4 5 6

create table tb_item(     id int comment '编号',     tb_name varchar(18) comment '商品名称',     tb_price double comment '商品价格',     tb_time datetime comment'商品日期' )comment '商品表';

操作表（修改）

　　1)alter table 表名 add 字段名 类型（长度） [ COMMENT 注释 ][ 约束 ];

　　　e.g alter table tb_item add tb_user varchar(20) comment '生产厂商';

　　2)alter table 表名 modify 字段名 新数据类型（长度）;

　　3)alter table 表名 change 旧字段名 新字段名 类型（长度）[ COMMENT 注释 ][ 约束 ];

　　4)alter table 表名 drop 字段名;

　　5)alter table 表名 rename to 新表名;

　　6)drop table [ if exists ] 表名;

　　7) truncate table 表名;

3.DML

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

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

　　3)update 表名 set 字段名= 值1，字段2=值2，... where[ 条件 ];

　　4)delete from 表名 [ where 条件 ];

4.DQL

　　1)select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数

　　2）as 设置别名

　　3）distinct

　　4）<> 或!=

*where和having的区别

• 执行时机：where在分组之前过滤，不满足where不参与分组；having在分组后过滤
• 判断条件：where不能对聚合函数进行判断；having可以

　　**分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

　　**支持多字段分组

*排序查询

　　**asc（升序）

　　**desc(降序）

*分页查询

　　**select 字段列表 from 表名 limit 起始索引，查询记录数;

　　（起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。）

*执行顺序

　　from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 select 字段列表 order by排序字段列表 limit 分页参数

5.DCL(Host如果是Localhost,则表示只能在本机访问)

　　1)select * from mysql.user;

　　2)create user '用户名@'主机名'' identified by '密码';

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

　　4)drop user '用户名'@'主机名';

　　5)show grants for '用户名'@'主机名';

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

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

*主机名可以使用 % 通配。

e.g create user 'lijx'@'%' idenfied by '1234';(用户lijx可以在任意主机访问该数据库)

三、约束

　　1)not nul

　　2)unique

　　3)primary key

　　4)default

　　5)check

　　6)foreign key

*操作外键

　　**create table 表名(...,[constraint] [外键名称] foreign key (外键字段名) references 主表（主表字段名）);

　　**alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表（主表字段名）;

　　**alter table 表名 drop foreign key 外键名称;

*外键删除\更新 后行为

　　1)no action\restrict(当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。)

　　2)cascade（当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录）

　　3)set null(当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null）

四、多表查询

　　默认：笛卡尔积

　　*内连接：查询交集（隐式内连接、显式内连接）

　　*左外连接：查询左表所有数据，以及两张表交集部分数据

　　*右外连接：查询右表所有数据，以及两张表交集部分数据

　　*自连接：当前表与自身的连接查询，自连接必须使用表别名(在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。)

　　*联合查询：Union all

　　*子查询

　　　　SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

　　　　e.g select from t1 where column1 = (select column1 from t2）；

　　　　1)标量子查询

　　　　子查询的结果是单个值

　　　　2)列子查询

　　　　查询结果是一列（常用in、not in、any、some、all）

　　　　3)行子查询

　　　　查询结果是一行

　　　　4)表子查询

　　　　查询结果是多行多列

六、事务

1.简介

事务是一组操作的集合，是不可分割的工作单位，操作要么同时成功，要么同时失败（Mysql执行一条DML，默认提交事务）

2.控制事务

1)select @@autocommit;（设置事务）

set @@autocommit=0;

2)COMMIT;（提交事务）

3)ROLLBACK;（回滚事务）

4)START TRANSACTION 或BEGIN（开启事务）

3.四大特性(ACID)

• 原子性(Atomicity):事务是不可分割的最小操作单元，要么同时成功，要么同时失败
• 一致性(Consistency):事务完成后，数据要保持一致
• 隔离性(Isolation):数据库系统提供的隔离机制
• 持久性(Durability):事务一旦提交和回滚，数据改变是永久的

4.并发事务问题

• 脏读：一个事务读取到另一个事务没有提交的数据
• 不可重复读：一个事务先后读取同一条数据，但两次读取数据不同
• 幻读：一条事务按照条件查询数据时，没有对应的数据行，但是插入时，又发现数据已经存在

5.事务隔离级别

　　1)select @@TRANSACTION_ISOLATION;

　　2)set [ session|global ] transaction isolation level { read uncommitted | read committed | repeatable read | serializable }