数据库管理操作，简单易懂，初学者一看就会

一.SQL常用操作语句

1.查询语句

select 列1,列2,...,列n

from 表名

where 条件

group by 列

having 条件

order by 列 asc/desc

limit 位置,记录数

2. 插入语句

#按照字段插入

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

#插入一条

insert  into  表名  values(id,name,age,address);

#同时插入多条语句

insert  into  表名 

(列1,列2,列3,列4)

values

(值1,值2,值3,值4),

(值1,值2,值3,值4),

(值1,值2,值3,值4);

#同时插入多条语句

insert into 表名

(列1,列2,列3,列4)

select 值1,值2,值3,值4 union

select 值1,值2,值3,值4

3.修改语句

update 表名

set 列名1 = '列值1',

列名2 = '列值2',

列名3 = '列值3'

where 条件

4.删除语句 

delete from 表名 where 条件

 二.数据完整性

        数据完整性（Data Integrity）是指数据的精确性（Accuracy） 和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性分为四类：实体完整性（Entity Integrity）、域完整性（Domain Integrity）、参照完整性（Referential Integrity）、用户自定义完整性（User-definedIntegrity）。

1.数据完整性分类

• 实体完整性(Entity Integrity)：保证数据记录的唯一性，主键,自增,唯一

• 域完整性(Domain Integrity) : 非空,默认值,外键约束

• 引用完整性(Referential Integrity) : 外键约束

• 用户自定义完整性(User-defined Intergrity)   

2.实体完整性(Entity Integrity)约束 

实体完整性(Entity Integrity)约束 

3.主键约束（primary key）   

特点: 唯一且非空。并且主键列会自动生成索引。

  注: 行数据的唯一标识，不能为null，不能重复，一个表中应该有主键，只能有一个主键，有主键（索引）的表，查询效率高。

# 主键约束: primary key

# 特点: 该列数据必须唯一，而且不能为空

create table emp

(

empId int primary key,

empName varchar(20),

sex char(1),

hireDate date,

 phone varchar(20)

)

SELECT * from emp

insert into emp

(empId,empName,sex,hireDate,phone)

VALUES

(1001,'张三','男','2000-01-01','13112345678')

#问题1：插入重复数据会报错

insert into emp

(empId,empName,sex,hireDate,phone)

VALUES

(1001,'张三风','男','2000-01-01','13112345678')

#问题2:主键列不插入数据或者插入null值，也要报错

insert into emp

(empName,sex)

VALUES

('李四','女')

#添加主键

alter table 表名

add constraint 约束名 primary key (列)

#删除主键

alter table 表名

drop primary key 

主键的特点: 

（1）唯一性：一个表中只能有一个主键。如果在其他字段上建立主键，则原来的主键就会取消。联合主键！

（2）非空性：主键的值不可重复，也不可为空；

（3）一张表要有一个无意义的主键。 主键主要是用于其他表的外键关联，以及本记录的修改与删除；

自增约束（auto_increment）

特点: 系统自动生成，自动加1。要和主键约束一起使用。一个表只能由一个自增列

#添加自增

alter table 表名

modify column id int auto_increment

#删除自增

alter table 表名

modify column id int 

注意: 自增值在某些场景下会出现断层 

1.在mysql存储引擎是innodb的情况下，如果使用select union语法做批量插入，则会导致两次插入的自增值断层。而使用 insert values语法不会导致这个问题。将表的存储引擎改为MyISAM后这个问题可以解决

#查看表使用存储引擎

show create table emp

#创建表指定引擎

create table emp

(

empId int primary key auto_increment,

empName varchar(20),

sex char(1),

phone varchar(11) unique

)ENGINE = 'myisam'

2. 在innodb存储引擎下，在插入唯一约束数据重复，外键无效数据时，此时报错，会导致自增断层。而数据长度过长导致的插入不成功不会导致自增断层。

3. 在有自增列的表中删除记录之后，再插入数据时，自增不会连续增长。

 唯一约束（unique）

特点: 确保数据的唯一性。唯一约束允许null值。

#添加唯一约束

alter table 表名

add constraint 唯一键约束名 UNIQUE(列)

#删除唯一索引约束

alter table 表名

drop index 唯一键约束名

实体完整性总结:

#实体完整性:

#1. 主键约束(primary key) : 

#   唯一且不能为空,可以用整数列也可以用于字符串列

#   举例:学号,员工编号

#2. 自增约束(auto_increment): 

#   自动增长。用于整形列和主键一起用。一个表中只能有一个自增列

#   举例:整形学号,整形员工编号

#3. 唯一约束(unique)

#   唯一可以允许null。一个表中可以有多个。

#   举例: 手机号,身份证号

三.引用完整性  

引用完整性用于限定多表之间的关联关系。

外键约束(foreign key)

特点: 确保表与表之间的数据的一致性。

#添加外键约束

alter table 表名

add constraint 约束名

foreign key(当前表列名)

references 关联表(关联表列名)

#删除外键约束

alter table 表名

drop foreign key 外键约束名

在具有引用完整性的表之间，禁止用户进行下列操作

（1）当主表中没有关联的记录时，将记录添加到从表中。

#分类中的分类编号只有1,2,3但是商品要插入4的分类，此时报错

insert into goods

(goodsName,price,produceDate,address,categoryId)

values

('洗衣粉',2.5,'2020-01-01','广州',4)

报错:

[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`qy149_0219`.`goods`, CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`categoryId`) REFERENCES `category` (`categoryid`))

（2）更改主表表中的被关联值并导致从表中的记录孤立。 

#商品表关联了分类表中，编号为1的记录，此时将分类表中的1改为10

#此时会报错外键错误

update category

set categoryId = 10

where categoryId = 1

报错:

[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`qy149_0219`.`goods`, CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`categoryId`) REFERENCES `category` (`categoryid`))

（3）从主表中删除记录，但仍存在与该记录匹配的相关记录 

#删除主表中的记录，但是从表对改记录有关联，此时会报错

delete from category where categoryId = 2

错误:

[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`qy149_0219`.`goods`, CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`categoryId`) REFERENCES `category` (`categoryid`))

(4) 主表被从表关联，此时删除主表结构，会报错，主表无法删除。

解决方法: 先删除从表，再删除主表

#分类表被商品表关联，此时删除分类表，则会报错

drop table category

错误：

[Err] 3730 - Cannot drop table 'category' referenced by a foreign key constraint 'goods_ibfk_1' on table 'goods'.

删除主表的解决方法： 

#先删除从表(商品表),在删除主表

drop table goods;

drop table category;

注意： 在实际项目中考虑到数据库的处理性能和表的维护，一般并不会实际加外键约束。而只是设计表时让从表和主表有对应数据列即可。这被称为逻辑约束。 

四.域完整性(Domain Integrity) 

域完整性，主要用于限定数据范围

默认值约束：default

当没有为列提供值得时候，用默认值填充列

create table emp

(

empId int primary key auto_increment,

empName varchar(20) not null,#非空约束

sex char(1) not null, #非空约束

hireDate date,

  phone varchar(20) unique,  # 唯一约束

province varchar(20) default '河南'  #省份

)

/*

默认值约束: DEFAULT

#           加了默认值约束，则该列值没有提供的时候，用默认值填充

#           如果提供了列值，则用提供的值填充

*/

 非空约束：not null

create table emp

(

empId int primary key auto_increment,

empName varchar(20) not null,#非空约束

sex char(1) not null, #非空约束

hireDate date,

  phone varchar(20) unique,  # 唯一约束

province varchar(20) #省份

)

用户自定义完整性

用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。例如某个属性必须取唯一值，某个非主属性也不能取空值，某个属性的取值范围在0-100之间等

用户定义完整性可以涵盖实体完整性、域完整性、参照完整性等完整性类型。

建表时加约束

实例:

#创建班级表

create table class (

id int(5) primary key auto_increment,

classname varchar(30) unique not null

);

#创建学生表：

create table student(

id int(5) primary key auto_increment,

name varchar(50) not null,

age it(5) not null,

classid int(5),#外键列

    constraint 外键名classid_fk_id foreign key(引用id) references 被引用表名 (被引用id)

);

数据定义语言(DDL)

主要用来修改数据表结构用的，增加/减少/修改 字段 （属性） 添加各种约束

//添加新的字段 

alter table 表名 add 列名 <类型> ; 

//添加新的字段 位置在第一列

alter table 表名 add 列名 <类型> first; 

//将列名1添加到列名2的后边

alter table 表名 add 列名1 <类型> after 列名2;  

//修改字段名和类型

alter table 表名 change COLUMN <旧列名> <新列名> <新列类型>;

//修改列名的类型

alter table 表名 modify <列名> <类型>;

//删除列

alter table 表名 drop <列名>;

//给表重命名

alter table 表名 rename <新表名>;

//设置字符集

alter table 表名 CHARACTER SET <字符集名>;

//移动列的位置  将列名a移动到列名b的后边

alter table 表名 modify <列名a>(列类型) after <列名b>;

//给id添加自增约束

alter table 表名 modify id int(5) auto_increment;

//修改表中的字段不为空/或者为空

alter table 表名 modify 字段名  类型 not null/null;

//添加表的字段自增主键    如果要修改只需将add 改成 modify

alter table 表名 add column 字段名 类型 auto_increment not null, add primary key(cid);

//添加唯一约束：

alter table 表名 add constraint uk_(字段名) unique(字段名);

//删除表的字段

alter table 表名 drop column 字段;

//删除自增主键约束

alter table 表名 modify id int;

//删除表的主键约束，要先删除自增约束才可以删除主键约束

alter table 表名 drop primary key;

//添加外键约束

//外键名  fk_classid_id   student classid    class id

alter table 表名 add constraint 外键名  

foreign key(引用id) references 被引用表名 (被引用id)

//添加主键约束

alter table 表名 add constraint pk_id primary key(id(修改的主键列名));

//删除外键约束

-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称

show create table 表名;

-- 获取名称之后就可以根据名称来删除外键约束

alter table 表名 drop foreign key 外键名;