操作表，DML，DQL，DCL，表约束，索引，视图，范式

1. SQL语法

SQL语句可以单行或多行书写，以分号结尾
使用空格和缩进来增强语句的可读性
Mysql数据库的mysql语句不区分大小写
注释：
- 单行注释：– 注释内容或 # 注释内容
- 多行注释：/* 注释内容 */

2. 语句分类

DDL(Data Definition Language )定义数据库 create/alter/drop/truncate

DML(Data Manipulation Language)操作数据库 insert/update/delete

DQL(Data Query Language)查询数据库 select

DCL(Data Control Language)数据库数据访问权限，安全级别 grant，revoke等

3. 数据库对象

对象名称	关键字
表	table
视图	view
索引	index
函数	function
存储过程	procedure
触发器	trigger
约束	constraint

4. 列类型

列类型	说明
tinyint/int/mediunint/int/bigint	整数
float/double	浮点数，存在精度丢失
decimal	不存在精度丢失
date	YYYY-MM-DD 1970-01-01
datetime	YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00
time	HH:MM:SS 00:00:00
timestamp	YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 UTC
char	定长字符
varchar	可变字符
blob	二进制大对象，存储图片等(InputStream)
text	文本
enum(‘’,’’,….)	枚举中的一个
set(‘’,’’,…..)	集合中的几个
clob	文本大对象（流对象Reader）

5. 操作数据库

5.1. Create

/*字符集*/
create database 数据库名称 default character set utf8;
/*创建数据库*/
create database if not exists 数据库名称;
/*创建并指定字符集*/
create database if not exists 数据库名称 character set utf8;

5.2. Retrieve

/*查询所有数据库*/
show databases;

/*查询数据库的创建语句*/
show create database 数据库名称;

5.3. Update

1 2	/更新数据库字符集/ alter database 数据库名称 character set 字符集名称;

5.4. Delete

1 2	/删除数据库/ drop database if exists 数据库名称;

5.5. 使用数据库

/*当前数据库*/
select database();

/*使用数据库*/
use 数据库名称;

6. 操作表

6.1. Create

create table test(
 id int primary key auto_increment,
 name varchar(20) not null
)

/*复制表*/
create table 表名 like 另一个表名

6.2. Retrieve

/*查询所有表*/
show tables;

/*查询表结构*/
desc 表名;

6.3. Update

/*修改表名*/
alter table 表名 to 新表名;

/*修改表字符集*/
alter table 表名 character set 字符集;

/*添加列*/
alter table add 列名 列类型;

/*修改列类型*/
alter table 表名 change 列名 列类型;

/*删除列*/
alter table 表名 drop 列名;

6.4. Delete

1
2
3

/*删除数据库*/
drop table if exists 表名;
drop table 表名;

7. DML操作数据库

7.1. insert

1	insert into 表名 (列名,列名,...) values(列值,列值,...);

7.2. update

1	update 表名 set 列名=列值 where 列名=列值;

7.3. delete

delete from test where id=1; 

/*先删除表，后创建一张一样的表*/
truncate 表名;

8. DQL查询数据库

8.1. select

1	select * from 表名 where 列名=列值;

8.2. 别名

1
2
3

select 列名 as '别名' from 表名 where 列名=列值;

select 列名 '别名' from 表名 where 列名=列值;

8.3. 个数

1 2	/select默认去除null值行/ select count(ifnull(列名,0)) from 表名;

8.4. 去重

1	select distinct 列名 from 表名 where 列名=列值;

8.5. 排序

select 列名 from 表名 order by 列名 desc; /*降序*/
select 列名 from 表名 order by 列名; /*升序*/

/*按列1降序，一样时按列2升序*/
select 列名 from 表名 order by 列1 desc, 列2 asc;

8.6. 模糊查询

1 2	/%多个字符，_单个字符/ select 列名 from 表名 where 列名 like '王%';

8.7. 区间/分组查询

select 列名 from 表名 where 列名 between 1 and 3  /*[1,3]*/
select 列名 from 表名 where 列名 in(1,3)  /*1 or 3*/

/*null值*/
select * from 表名 where 列名 is null;
select * from 表名 where 列名 is not null;

/*
	使用分组字段，聚合函数进行查询
  where 在分组之前限定，不满足，不参与
  having 在分组之后限定，不满足，不被查询
*/
select count(*) from 表名 group by 列名 having count(*)>2

8.8. 分页查询

开始索引 = (当前页码-1) * 每页查询列数

1 2	/从0开始/ select * from 表名 limit 开始索引,每页查询列数;

8.9. 多表查询

/*隐式内连接*/
select * from 表A 别名a,表B 别名b where a.id = b.id

/*显式内连接*/
select * from 表A 别名a inner join 表B 别名b on a.id = b.id

/*外连接*/
/*左外连接，表A属性全显示*/
select * from 表A 别名a left join 表B 别名b on a.id = b.id
/*右外连接，表B属性全显示*/
select * from 表A 别名a right join 表B 别名b on a.id = b.id

/*子查询-嵌套*/
select * from 表名 where 列名 = (select max(列名) from 表名);

9. DCL数据库访问权限

9.1. 添加用户

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

/*%可以在任意主机使用用户登录数据库*/
create user 'backup'@'%' identified by '123456';

9.2. 删除用户

1	drop '用户名'@'主机名';

9.3. 修改密码

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

9.4. 查询用户

1	use * from user;

9.5. 用户权限

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

/*grant 权限列表[all,select,update,alter,delete,...] on 数据库.表 to '用户名'@'主机名'*/
grant all on *.* to '用户名'@'%';

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

10. 表约束

constraint 约束名属性(列名);

/*unique 唯一约束可以有多个null*/
constraint id_con unique(id);
/*删除unique*/
alter table 表名 drop index id;
/*添加unique*/
alter table 表名 modify 列名 列类型 unique;

/*添加主键*/
id int primary key
/*删除unique*/
alter table 表名 drop primary key;

constraint 约束名 foreign key 列名 references 另一个表名 另一个表列名;

drop foreign key 约束名;

11. 索引

create index 索引名 on 表名(列名);

1
2
3

create index idIndex on test(id);

drop inIndx on test;

12. 视图

create view 视图名 as select语句;

1 2	create view testview as select * from test; drop view testview；

13. 范式

13.1. 第一范式

每一列都是不可分割的原子数据项

13.2. 第二范式

在1NF基础上，非码属性必须完全依赖于码(消除非主属性对主码的部分函数依赖)

函数依赖：A->B，通过属性A(属性组)的值，可以确定唯一属性B的值，B依赖于A

学号->姓名，(学号，课程)->分数
完全函数依赖：A是一个属性组，B属性的确定需要A所在的整个属性组

(学号，课程名)->分数
部分函数依赖：A是一个属性组，B属性的确定需要A所在的属性组中的每一些属性

(学号，课程名)->姓名，学号->姓名
传递函数依赖：A->B，B->C，通过属性A(属性组)的值，可以确定唯一属性B的值，通过属性B(属性组)的值，可以确定唯一属性C的值

学号->系名，系名->系主任，学号->姓名
码：一个属性(属性组)被其他属性完全依赖

(学号，课程名)

主属性：码属性组中的所有属性

非主属性：除码属性组以外的属性

13.3. 第三范式

消除传递函数依赖

学号->系名，系名->系主任