Mysql数据库的常用操作＞＞二

目录

一、字段约束

1、主键约束PRIMARY KEY

2、非空约束NOT NULL

3、唯一约束UNIQUE

4、默认约束DEFAULT

5、检查约束CHECK(条件)

6、外键约束

二、事务Transaction

1、概述

1.1事物的四大特性：持、原、隔、一

1.2隔离级别：从上至下安全性越来越好，性能越来越差

2、测试

三、索引

1、查看索引

2、单值索引：一个索引只包含一个字段

2.1、使用单值索引

3、唯一索引：一个索引只包含一个字段，索引列值不能重复

3.1、使用唯一索引

4、复合索引：一个索引包含多个字段，遵循最左特性

4.1使用复合索引(

5、查询SQL执行是否用到索引

6、删除索引

 四、视图

概述：

特点：

好处：

坏处：

---

一、字段约束

1、主键约束PRIMARY KEY

主键是唯一的且不为空，只能为一个字段添加主键。

主键自增策略：主键的值交给数据库去管理，数据库会查到当前记录中的最大值+1

#创建一个表名为a的表，并将其中的id字段设置为主键自增,记得先进入数据库，lab数据库的名字
USE lab;
CREATE TABLE a(id INT PRIMARY KEY AUTO_INCREMENT);
#给添加了主键约束的字段增加记录
INSERT INTO a VALUES(10);
INSERT INTO a VALUES(20);
INSERT INTO a VALUES(NULL);#不会报错因为已经设置了主键自增结果是21

2、非空约束NOT NULL

添加了非空约束的字段值不能为空，主键默认不能为空，设置了主键自增的可以

CREATE TABLE b(id INT PRIMARY KEY,bname VARCHAR(20) NOT NULL); #创建b表将bname字段设置成非空
#插入数据

INSERT INTO b VALUES (NULL,'小明');        #会报错，原因是主键没有设置自增所以不能为空
INSERT INTO b VALUES (1,NULL);            #同样会报错，创建表时已经将字段bname设置不为空
INSERT INTO b VALUES (1,'小明');            #成功

3、唯一约束UNIQUE

字段添加了唯一约束，那么它的值必须为唯一，不能有重复的

#创建c表，并将age字段设置为唯一约束
CREATE TABLE c(id INT PRIMARY KEY AUTO_INCREMENT,age INT UNIQUE);
#添加记录
INSERT INTO c VALUES(NULL,10);        #成功，主键自增
INSERT INTO c VALUES(NULL,20);        #成功，主键自增
INSERT INTO c VALUES(33,20);          #失败

4、默认约束DEFAULT

给字段添加默认值，之后在增加数据时，当你字段值不添加时就会默认为你设置的默认值

CREATE TABLE d(id INT PRIMARY KEY,sex VARCHAR(10) DEFAULT '男');

注意：在新增时就要在表后边规定字段例：

INSERT INTO f(id) VALUES(1);    #这样就可以得到id=1，性别为男的记录

5、检查约束CHECK(条件)

给字段添加检查约束，这样在增加新的数据时会检查是否满足条件，防止错误数据的添加

CREATE TABLE f(id INT PRIMARY KEY,sex VARCHAR(10),age INT CHECK(age>0 AND age<100));

6、外键约束

防止冗余的数据，通过外键来描述两张表的关系，当子表中添加数据时，子表的主键值必须取自于主表！#当主表删除数据时，子表没有相关的记录

CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
PASSWORD VARCHAR(20));

CREATE TABLE tb_user_addr(
user_id INT PRIMARY KEY,
address VARCHAR(100),
#描述和tb_user表的关系，外键
#语法：foreign key(本表的主键名)references 对方表名（对方表的主键）
FOREIGN KEY(user_id) REFERENCES tb_user(id)#创建外键
);

二、事务Transaction

1、概述

保证多条操作要么全成功，要么全失败。

1.1事物的四大特性：持、原、隔、一

1）持久性：对数据库的操作是持久生效的

2）原子性：把多条SQL，看作是一个原子，密不可分，要不全成功，要不全失败

3）隔离性：数据库为了保证性能也支持高并发，但有安全隐患，保证多个操作之间是隔离的

4）一致性：保证在多个系统中是一致的

1.2隔离级别：从上至下安全性越来越好，性能越来越差

1）读未提交：性能最好，但安全性最差，一般不推荐使用此隔离级别

2）读以提交：性能较差，安全性较高，Oracle数据库的默认隔离级别

3）可重复读：性能较差，安全性较高，MySQL数据库的默认隔离级别

4）串行化：性能差

2、测试

MySQL数据库会为每条SQL语句提供事务管理。每条SQL语句执行前MySQL会自动开启事务，执行结束后会自动关闭事务，如果想自己关系事务使用:开启事务(start)/关闭事务（commit/rollback）

START TRANSACTION #开启事务
INSERT INTO c VALUES(8,'85');
INSERT INTO c VALUES(9,'93');
COMMIT;#结束事务

三、索引

概述：为了提高查询效率，索引会单独生成一张表，要合理使用

分类：单值索引、唯一索引、复合索引.

1、查看索引

SHOW INDEX FROM student;        #主键自带索引

2、单值索引：一个索引只包含一个字段

#向学员表的name字段添加单值索引
CREATE INDEX name_index ON student(NAME);            

2.1、使用单值索引

SELECT * FROM student WHERE name='Jack' #使用单值索引

3、唯一索引：一个索引只包含一个字段，索引列值不能重复

CREATE UNIQUE INDEX age_index ON student(age);        #创建唯一索引
SHOW INDEX FROM student;        #查看索引

3.1、使用唯一索引

SEKECT * FROM student WHERE age=30;

4、复合索引：一个索引包含多个字段，遵循最左特性

CREATE INDEX fuhe_index ON student(age,name); #最左特性就是查询语句中条件必须包含关于age的条件

4.1使用复合索引(

EXPLAIN SELECT * FROM student WHERE age=10; #复合索引生效
EXPLAIN SELECT * FROM student WHERE age=10 AND NAME='Lilei'; #复合索引生效
EXPLAIN SELECT * FROM student WHERE NAME='Lilei'; #复合索引失效
EXPLAIN SELECT * FROM student WHERE NAME='Lilei' AND sex='男'; #复合索引失效
EXPLAIN SELECT * FROM student WHERE NAME='Lilei' AND age=10; #复合索引生效效

5、查询SQL执行是否用到索引

EXPLAIN     #添加这个单词和下面的查询语句一起运行就可以查看是否用索引了
SELECT * FROM student WHERE age=10;

6、删除索引

ALTER TABLE student DROP INDEX fuhe_index;

 四、视图

概述：

和索引一样都是对数据库优化的有效方案

特点：

1.可以把试图当作表来使用2.视图里存的数据是SQL查询到的结果3.SQL无法优化，要合理使用

好处：

1.简化了查询SQL（相同的SQL直接查视图即可）2.视图可以被共享，视图屏蔽了真是业务的复杂性

坏处：

一旦创建无法更改，只能删除重新创建

#2.1 创建视图
#语法:create view 视图名 as 查询的SQL语句
CREATE VIEW name_view AS SELECT * FROM emp WHERE name LIKE '%L%'
#2.2 使用视图
SELECT * FROM name_view
#2.3删除视图
DROP VIEW name_view;