6天掌握MySQL基础--day5

最新推荐文章于 2022-01-16 21:44:59 发布

birdneverstop

最新推荐文章于 2022-01-16 21:44:59 发布

阅读量1.2k

点赞数 3

分类专栏： # 6天掌握MySQL基础文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_42224119/article/details/105265517

版权

6天掌握MySQL基础专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

DDL语言（接day4）
- 常见约束
- 标识列
TCL语言
- 事务
视图

复习
day4的学习内容

DDL语言（接day4）

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机：
1.创建表时
2.修改表时

约束的添加分类：
列级约束：

  六大约束语法上都支持，但外键约束没有效果

表级约束：

  除了非空、默认，其他的都支持

主键和唯一的对比：

	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	√	×	至多有1个	√，但不推荐
唯一	√	√	可以有多个	√，但不推荐

列级约束与表级约束区别

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其他支持	可以（主键没有效果）

外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表删除数据时，先删除从表，再删除主表

CREATE TABLE 表名 (
    字段名 字段类型 列级约束,
    字段名 字段类型,
    表级约束
) CREATE DATABASE students ;

#一、创建表时添加约束
#1.添加列级约束
/*
语法：

直接在字段名和类型后面追加约束类型即可

只支持：默认、非空、主键、唯一
*/
USE students ;

DROP TABLE stuinfo ;

CREATE TABLE stuinfo (
    id INT PRIMARY KEY, #主键
    stuName VARCHAR (20) NOT NULL UNIQUE, #非空
    gender CHAR(1) CHECK (gender = '男' 
        OR gender = '女'), #检查
    seat INT UNIQUE, #唯一
    age INT DEFAULT 18, #默认约束
    majorId INT REFERENCES major (id) #外键
) ;

CREATE TABLE major (
    id INT PRIMARY KEY,
    majorName VARCHAR (20)
) ;

#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo ;

#2.添加表级约束
/*
语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/
DROP TABLE IF EXISTS stuinfo ;

CREATE TABLE stuinfo (
    id INT,
    stuname VARCHAR (20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,
    CONSTRAINT pk PRIMARY KEY (id), #主键
    CONSTRAINT uq UNIQUE (seat), #唯一键
    CONSTRAINT ck CHECK (gender = '男' 
        OR gender = '女'), #检查
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id) #外键
) ;

SHOW INDEX FROM stuinfo ;

#通用的写法：★
CREATE TABLE IF NOT EXISTS stuinfo (
    id INT PRIMARY KEY,
    stuname VARCHAR (20),
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id)
) ;

#二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
DROP TABLE IF EXISTS stuinfo ;

CREATE TABLE stuinfo (
    id INT,
    stuname VARCHAR (20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT
) DESC stuinfo ;

#1.添加非空约束
ALTER TABLE stuinfo 
    MODIFY COLUMN stuname VARCHAR (20) NOT NULL ;

#2.添加默认约束
ALTER TABLE stuinfo 
    MODIFY COLUMN age INT DEFAULT 18 ;

#3.添加主键
#①列级约束
ALTER TABLE stuinfo 
    MODIFY COLUMN id INT PRIMARY KEY ;

#②表级约束
ALTER TABLE stuinfo 
    ADD PRIMARY KEY (id) ;

#4.添加唯一
#①列级约束
ALTER TABLE stuinfo 
    MODIFY COLUMN seat INT UNIQUE ;

#②表级约束
ALTER TABLE stuinfo 
    ADD UNIQUE (seat) ;

#5.添加外键
ALTER TABLE stuinfo 
    ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id) ;

#三、修改表时删除约束
#1.删除非空约束
ALTER TABLE stuinfo 
    MODIFY COLUMN stuname VARCHAR (20) NULL ;

#2.删除默认约束
ALTER TABLE stuinfo 
    MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo 
    DROP PRIMARY KEY ;

#4.删除唯一
ALTER TABLE stuinfo 
    DROP INDEX seat ;

#5.删除外键
ALTER TABLE stuinfo 
    DROP FOREIGN KEY fk_stuinfo_major ;

SHOW INDEX FROM stuinfo ;

标识列

又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表至多有一个标识列
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3设置步长；可以通过手动插入值，设置起始值

#一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity ;

CREATE TABLE tab_identity (
    id INT,
    NAME FLOAT UNIQUE AUTO_INCREMENT,
    seat INT
) ;

TRUNCATE TABLE tab_identity ;

INSERT INTO tab_identity (id, NAME) 
VALUES
    (NULL, 'john') ;

INSERT INTO tab_identity (NAME) 
VALUES
    ('lucy') ;

SELECT 
    * 
FROM
    tab_identity ;

#查看变量
SHOW VARIABLES LIKE '%auto_increment%' ;

#设置步长，一般不改
SET auto_increment_increment = 3 ;

#二、修改表时设置标识列
ALTER TABLE tab_identify 
    MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT ;

#三、修改表时删除标识列
ALTER TABLE tab_identify 
    MODIFY COLUMN id INT ;

TCL语言

Transaction Control Language 事务控制语言

事务

事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行
事务的特性(ACID)：
原子性(Atomicity)：一个事务不可再分割，要么都执行要么都不执行
一致性(Consistency)：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性(Isolation)：一个事务的执行不受其他事务的干扰
持久性(Durability)：一个事务一旦提交，则会永久的改变数据库的数据
事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用set autocommit=0;

事务的使用步骤

步骤1：开启事务
set autocommit=0;
start transaction;可选的

步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...

步骤3：结束事务
commit;提交事务
rollback;回滚事务

实例

#开启事务
SET autocommit = 0 ;

START TRANSACTION ;

#编写一组事务的语句
UPDATE 
    account 
SET
    balance = 1000 
WHERE username = '张无忌' ;

UPDATE 
    account 
SET
    balance = 1000 
WHERE username = '赵敏' ;

#结束事务
ROLLBACK ;
#commit;

事务的隔离级别：

	脏读	不可重复读	幻读
read uncommitted：	√	√	√
read committed：	×	√	√
repeatable read：	×	×	√
serializable：	×	×	×

mysql中默认第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

保存点，设置回滚到的位置

SET autocommit = 0 ;

START TRANSACTION ;

DELETE 
FROM
    account 
WHERE id = 25 ;

SAVEPOINT a ;#设置保存点

DELETE 
FROM
    account 
WHERE id = 28 ;

ROLLBACK TO a ;#回滚到保存点

视图

含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据，只保存SQL逻辑，不保存查询结果

	创建语法的关键字	是否实际占用物理空间	使用
视图	create view	只是保存了sql逻辑	增删改查，只是一般不能增删改
表	create table	保存了数据	增删改查

#一、创建视图
/*
语法：
create view 视图名
as
查询语句;
*/

#案例：查询姓张的学生名和专业名
SELECT 
    stuname,
    majorname 
FROM
    stuinfo s 
    INNER JOIN major m 
        ON s.`majorid` = m.`id` 
WHERE s.`stuname` LIKE '张%' ;

CREATE VIEW v1 AS 
SELECT 
    stuname,
    majorname 
FROM
    stuinfo s 
    INNER JOIN major m 
        ON s.`majorid` = m.`id` ;

SELECT 
    * 
FROM
    v1 
WHERE stuname LIKE '张%' ;

#查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1 AS 
SELECT 
    last_name,
    department_name,
    job_title 
FROM
    employees e 
    JOIN departments d 
        ON e.department_id = d.department_id 
    JOIN jobs j 
        ON j.job_id = e.job_id ;

#②使用
SELECT 
    * 
FROM
    myv1 
WHERE last_name LIKE '%a%' ;

#二、视图的修改
#方式一：
/*
create or replace view  视图名
as
查询语句;

*/
SELECT 
    * 
FROM
    myv3 CREATE OR REPLACE VIEW myv3 AS 
    SELECT 
        AVG(salary),
        job_id 
    FROM
        employees 
    GROUP BY job_id ;

#方式二：
/*
语法：
alter view 视图名
as 
查询语句;

*/
ALTER VIEW myv3 
    AS 
    SELECT 
        * 
    FROM
        employees ;

#三、删除视图
/*
语法：drop view 视图名,视图名,...;
*/
DROP VIEW emp_v1,
emp_v2,
myv3 ;

#四、查看视图
DESC myv3 ;

SHOW CREATE VIEW myv3 ;

#五、视图的更新
CREATE OR REPLACE VIEW myv1 AS 
SELECT 
    last_name,
    email,
    salary * 12 * (1+ IFNULL(commission_pct, 0)) "annual salary" 
FROM
    employees ;

CREATE OR REPLACE VIEW myv1 AS 
SELECT 
    last_name,
    email 
FROM
    employees ;

SELECT 
    * 
FROM
    myv1 ;

SELECT 
    * 
FROM
    employees ;

#六、使用
#1.插入
INSERT INTO myv1 
VALUES
    ('张飞', 'zf@qq.com') ;

#2.修改
UPDATE 
    myv1 
SET
    last_name = '张无忌' 
WHERE last_name = '张飞' ;

#3.删除
DELETE 
FROM
    myv1 
WHERE last_name = '张无忌' ;

#4.查看
select
	*
from
	myv1;

#具备以下特点的视图不允许更新,一般也不更新，视图主要用来查看
#①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1 AS 
SELECT 
    MAX(salary) m,
    department_id 
FROM
    employees 
GROUP BY department_id ;

SELECT 
    * 
FROM
    myv1 ;

#更新
UPDATE 
    myv1 
SET
    m = 9000 
WHERE department_id = 10 ;

#②常量视图
CREATE OR REPLACE VIEW myv2 AS 
SELECT 
    'john' NAME ;

SELECT 
    * 
FROM
    myv2 ;

#更新
UPDATE 
    myv2 
SET
    NAME = 'lucy' ;

#③Select中包含子查询
CREATE OR REPLACE VIEW myv3 AS 
SELECT 
    department_id,
    (SELECT 
        MAX(salary) 
    FROM
        employees) 最高工资 
FROM
    departments ;

#更新
SELECT 
    * 
FROM
    myv3 ;

UPDATE 
    myv3 
SET
    最高工资 = 100000 ;

#④join
CREATE OR REPLACE VIEW myv4 AS 
SELECT 
    last_name,
    department_name 
FROM
    employees e 
    JOIN departments d 
        ON e.department_id = d.department_id ;

#更新
SELECT 
    * 
FROM
    myv4 ;

UPDATE 
    myv4 
SET
    last_name = '张飞' 
WHERE last_name = 'Whalen' ;

INSERT INTO myv4 
VALUES
    ('陈真', 'xxxx') ;

#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5 AS 
SELECT 
    * 
FROM
    myv3 ;

#更新
SELECT 
    * 
FROM
    myv5 ;

UPDATE 
    myv5 
SET
    最高工资 = 10000 
WHERE department_id = 60 ;

#⑥where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6 AS 
SELECT 
    last_name,
    email,
    salary 
FROM
    employees 
WHERE employee_id IN 
    (SELECT 
        manager_id 
    FROM
        employees 
    WHERE manager_id IS NOT NULL) ;

#更新
SELECT 
    * 
FROM
    myv6 ;

UPDATE 
    myv6 
SET
    salary = 10000 
WHERE last_name = 'k_ing' ;