mysql DDL/DML/DQL操作及约束完整性

1、SQL 操作分类

（1）DDL(data definitation language):数据定义语言
数据库，表（清空表数据，查看表结构），视图等的创建和销毁
create，drop，(truncate，desc）
（2）DML(data manipulation language):数据操纵语言
数据的增删改操作
insert:添加
delete:删除
update:修改
（3）DQL(data query language):数据查询语言
数据的查询
select
（4）DCL(data control language):数据控制语言
授权和取消授权
grant,revoke

2、DDL 数据定义语言

数据库中的数据存放在数据库表中，以二维数组的形式存在。
一行代表一条数据记录，称为记录。
一列代表同一域的数据，表示同一属性，称为字段。

2.1、数据库操作

2.1.1、创建数据库：create

注意：[] 里的内容表示可省略。

# 语法
create {database|schema} [if not exists] dbname;

# 创建 mydb 数据库
create database if not exists mydb;

2.1.2、查看所有的数据库：show

# 查看所有的数据库
show databases;

2.1.3、切换到某一个数据库：use

# 语法
use dbname;

# 切换到 mydb 数据库
use mydb;

2.1.4、修改数据库编码格式：alter

# 修改 dbname 数据库编码格式（本例改为utf-8,目前开发大多数都改成utf-8）
alter database dbname character set UTF8;

2.1.5、销毁数据库：drop

# 语法
drop {database|schema} [if exists] dbname;

# 销毁 mydb 数据库
drop database if exists mydb;

2.2、数据表操作

2.2.1、创建数据表：create

# 语法
create table [if not exists] tname;

# 创建一张 user 数据库表
create table if not exists `user`(
  userid int,
  username varchar(20),
  `password` varchar(20),
  birthday date
);

2.2.2、查看当前数据库下所有数据表：show

# 查看当前数据库下所有数据表
show tables;

2.2.3、销毁数据表：drop

# 语法
drop table [if exists] t_name;

# 销毁 user 数据表
drop table if exists `user`;

2.2.4、清空表数据：truncate

truncate 清空表所有数据，主键自增也重置。

# 语法1
truncate table tbl_name 
# 语法2
truncate tbl_name

# 清空 user 数据表
truncate table user;

2.2.5、查看表结构的详细信息：desc

# 语法
desc tname;

# 查看 user 表结构的详细信息 
desc user;

2.2.6、修改表结构：alter

2.2.6.1、添加字段

添加字段语法：

alter table tname add [column] col_name col_definition [after|first]

添加字段操作：

# 添加 sex 列(默认最后一列)
alter table `user` add sex varchar(10);
# 添加到指定列后
alter table `user` add sex varchar(10) after userid;
# 添加到首列
alter table `user` add sex varchar(10) first;

2.2.6.2、修改字段

修改字段语法：

alter table tname change col_name new_col_name col_definition;

修改字段操作：

# 修改数据类型
alter table user CHANGE sex sex VARCHAR(10);
# 修改字段名称
alter table user CHANGE sex usex VARCHAR(10);
# 修改字段位置
alter table user change birthday birthday date after userid;

2.2.6.3、删除字段

删除字段语法：

alter table `user` drop col_name;

删除字段操作：

# 删除sex列
alter table user drop sex;

2.2.7、表重命名：rename

# 表重命名语法
rename table table_name to new_table_name;

# 修改 user 表为 tuser
rename table user to tuser;

3、数据表的约束

3.1、完整性约束

完整性即数据的准确性。
实体完整性：保证记录是唯一的，不重复的。

3.1.1、主键约束:primary key

主键字段唯一且不能为空。

（1）创建表时声明主键(两种方法)：
第一种方法：

create table user(
	userid int primary key,
	username varchar(20)
);

第二种方法：

create table user(
	userid int,
	username varchar(20),
	primary key(userid)
);

（2）修改表结构添加约束：

alter table user add CONSTRAINT PK_USERID primary key(userid);

3.1.2、唯一约束:unique

唯一约束字段值不能重复。

（1）创建表时声明唯一约束（两种方法）：
第一种方法：

create table user(
	userid int primary key,
	username varchar(20),
	card varchar(18) unique
);

第二种方法：

create table user(
	userid int primary key,
	username varchar(20),
	card varchar(18),
	unique(card) 
);

（2）修改表结构添加唯一约束：

alter table user add CONSTRAINT UN_CAED unique(card);

3.1.3、主键自增:auto_increment

主键自增字段必须为整型数值，自增，每次自动加 1。

create table user(
	userid int primary key auto_increment,
	username varchar(20),
	card varchar(18)
);

3.2、域完整性

类型约束，保证列的数据正确性。

3.2.1、非空约束：not null

create table user(
	userid int primary key auto_increment,
	username varchar(20) not null,
	card varchar(18) not null
);

3.2.2、默认值约束

create table user(
	userid int primary key auto_increment,
	username varchar(20) not null,
	sex varchar(10) default '男',
	card varchar(18) not null
);

3.3、引用完整性

保证引用关系(外键)的正确性，参照完整性：

alter table emp add CONSTRAINT PK_DEPTNO foreign key(deptno) REFERENCES dept(deptno);

自定义完整性（check约束mysql不支持）：

# 添加约束保证部门编号只能在1到120之间
alter table dept add CONSTRAINT CK_AGE check(deptno BETWEEN 1 and 120);

3.4、删除约束

alter table user drop primary key;
alter table user drop foreign key;

4、DML 数据操纵语言

4.1、添加数据：insert

# values 中的数据值与表中的所有字段位置一致,字符串插入需要使用引号
# 语法1：插入一条数据
insert into tbname values(col1,col2,col3, ... ,coln);

# 语法2：批量插入数据
insert into tbname values (col1,col2,col3, ... ,coln),(col1,col2,col3, ... ,coln),(col1,col2,col3, ... ,coln);

# 语法3：向特定字段位置插入数据，前提是没有指定插入的字段允许空值操作，或者字段可以默认填值
insert into tbname(colx,coly,... ,coln) values (colx,coly,... ,coln),(colx,coly,... ,coln);

# 语法4：复制已有的表
insert into user(id,name) select userid,username from tuser;

# 向 user(id int,username varchar,sex varchar,age int)(1,张三,男,19) 表中插入一条数据：
insert into user values(2,'李四','男',20);

4.2、修改数据：update

# 语法
update tname set col_name = col_value;

# 注意：使用 update 没有使用条件，将修改所有记录的值
update user set sex = '女' where id = 2;

4.3、删除数据：delete

# 使用 delete 语句，没有 where 条件将删除所有的数据
# 语法
delete from tname;

// 删除所有数据是一行一行删除，因此比 truncate 速度慢，而且不会重置主键自增值。
delete from tuser where userid = 1;

5、DQL 数据查询语言：select

# 常用语法,查询表中所有数据
select * from tname；

常用关键字（执行顺序重上到下）：
where：条件语句，聚合前先筛选记录，也就是说作用在 group by 和 having 字句前。
group by：分组字段。
having：与 group by 组合，筛选成组后的各种数据，在聚合后对组记录进行筛选。
order by col desc：排序。
limit：限制结果数量，用于分页查询。

5.1、查询所有/指定字段数据

# 查询所有数据
select * from user;

# 查询指定列的所有数据
select username,password from user;

5.2、条件查询：where

# 条件查询(单一):查询年龄>18的用户信息
select * from user where age > 18;
# 条件查询(组合),多个条件使用 and 或 or 连接,查询年龄>18的所有女生
select * from user where age >18 and sex = '女';

5.3、取别名：as

# 取别名(字段),[as] 可不用
select userid [as] uid,username [as] uname from user;
# 取别名(表)
select u.userid,u.username from user u; 

5.4、去重：distinct

# 去重(distinct)
select distinct username from user;

5.5、模糊查询：like

# 模糊查询  like
# % 代表匹配 0 到多个字符
# _ 代表匹配一个字符

# 查询以"张"打头的所有用户
select * from user where username like '张%';
# 查询以“四”结尾的所有用户
select * from user where username like '%四';
# 查询包含“李”的所有用户
select * from user where username like '%李%';
# 查询第二个字为“三”的用户
select * from user where username like '_三%'

5.6、判断是否为 null：is [not] null

# 非空  is null
select * from user where sex is [not] null; 

5.7、范围查询：between … and …

# 范围查询  between … and …:查询年龄在12和20之间的用户
select * from user where age BETWEEN 12 and 20; 
等价于 
select * from user where age >=12 and age <=20;

5.8、集合查询：in(x,y)

# 集合查询：查询用户id为1、2、3的用户 
select * from user where id in (1,2,3);
等价于
select * from user where age = 1 or age = 2 or age = 3;

5.9、排序：order by … [asc]/desc

# 排序:按照年龄升序排序,[asc] 可省略
select * from user order by age [asc];

# 排序:按照年龄排序排序
select * from user order by age desc;

# 按照年龄升序排序,如果年龄一样，按照编号降序排序
select * from user order by age,id desc;

5.10、分页查询：limit m,n

# 限制结果数量:用于分页查询 limit m,n: m代表开始索引(从0开始),n代表长度
# 从查询结果数据中索引0开始，取两条数据
select * from user limit 0,2;