欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

荐 MySQL6天笔记——day05

程序员文章站 2022-05-07 17:40:09
文章目录MySQLDDL语言常见的约束TCL语言其他视图欢迎斧正!!!MySQL全部SQL源文件链接:https://pan.baidu.com/s/1wc51qkVetSRybFzcIYGBIg提取码:3wptDDL语言常见的约束含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性。分类六大约束NOT NULL:非空,用于保证该字段的值不能为空比如姓名、学号等DEFAULT:默认,用于保证该字段有默认值比如性别PRIMARY KEY:主键,用...

MySQL

全部SQL源文件链接:https://pan.baidu.com/s/1wc51qkVetSRybFzcIYGBIg
提取码:3wpt

DDL语言

常见的约束

  • 含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性。

  • 分类

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

  • 约束的添加分类:

    • 列级约束:六大约束语法上都支持,但外键约束没有效果
    • 表级约束:除了非空、默认,其他的都支持
  • 语法

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 约束名】 约束类型(字段名)
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)
);

create table 表名(
  	字段名 字段类型 not null,#非空
  	字段名 字段类型 primary key,#主键
  	字段名 字段类型 unique,#唯一
  	字段名 字段类型 default,#默认
  	constraint 约束名 foreign key(字段名) references 主表(被引用列)
);

注意:
  			   支持类型		      可以起约束名			
列级约束		除了外键		     不可以
表级约束		除了非空和默认	  可以,但对主键无效
  
列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求
  • 主键和唯一的区别
		保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
主键		√				×			至多有1个           √,但不推荐
唯一		√				√			可以有多个          √,但不推荐

二、修改表时添加约束

  • 语法
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 addconstraint 约束名】 约束类型(字段名) 【外键的引用】;
  • 案例
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、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;

2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default;
删除默认
alter table 表名 modify column 字段名 字段类型 ;

3、主键
添加主键
alter table 表名 addconstraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;

4、唯一
添加唯一
alter table 表名 addconstraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;

5、外键
添加外键
alter table 表名 addconstraint 约束名】 foreign key(字段名) references 主表(被引用列);
删除外键
alter table 表名 drop foreign key 约束名;

四、自增长列(标识列)

  • 含义:可以不用手动的插入值,系统提供默认的序列值

  • 特点

    1.不用手动插入值,可以自动提供序列值,默认从1开始,步长为1
      auto_increment_increment
      如果要更改起始值:手动插入值
      如果要更改步长:更改系统变量
      set auto_increment_increment=;
    2.一个表至多有一个自增长列
    3.自增长列只能支持数值型
    4.自增长列必须为一个key
    
  • 案例

#一、创建表时设置标识列
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;
  • 语法总结
一、创建表时设置自增长列
create table(
	字段名 字段类型 约束 auto_increment
);

二、修改表时设置自增长列
alter tablemodify column 字段名 字段类型 约束 auto_increment;

三、删除自增长列
alter tablemodify column 字段名 字段类型 约束;

TCL语言

Transaction Control Language 事务控制语言

  • 事务:一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。

    案例:转账
    
    张三丰  1000
    郭襄	1000
    
    updateset 张三丰的余额=500 where name='张三丰'
    意外
    updateset 郭襄的余额=1500 where name='郭襄'
    
  • 事务的特性(ACID)

    • 原子性:一个事务不可再分割,要么都执行要么都不执行
    • 一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
    • 隔离性:一个事务的执行不受其他事务的干扰
    • 持久性:一个事务一旦提交,则会永久的改变数据库的数据.

事务的使用步骤 ★

  • 了解

    • 隐式(自动)事务:没有明显的开启和结束,本身就是一条事务可以自动提交,比如insert、update、delete

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

      开启事务的语句;
      updateset 张三丰的余额=500 where name='张三丰'
      
      updateset 郭襄的余额=1500 where name='郭襄' 
      结束事务的语句;
      
  • 具体步骤:

    步骤1:开启事务
    set autocommit=0;
    start transaction;可选的
    
    步骤2:编写事务中的sql语句(select insert update delete)
    语句1;
    语句2;
    ...
    
    步骤3:结束事务
    commit;提交事务
    rollback;回滚事务
    
  • 案例

SHOW VARIABLES LIKE 'autocommit';
SHOW ENGINES;

#1.演示事务的使用步骤
DROP TABLE IF EXISTS account;
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('张无忌',1000),('赵敏',1000);

#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';

#结束事务
ROLLBACK;
#commit;

SELECT * FROM account;

并发事务

  • 1、事务的并发问题是如何发生的?

    • 多个事务同时操作 同一个数据库的相同数据时
  • 2、并发问题都有哪些?

    • 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
    • 脏读:一个事务读取了其他事务还没有提交的数据,读到的是其他事务“更新”的数据;
    • 不可重复读:一个事务多次读取,结果不一样;
    • 幻读:一个事务读取了其他事务还没有提交的数据,只是读到的是 其他事务“插入”的数据。
  • 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.

  • 一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱

  • 3、如何解决并发问题?

    • 通过设置隔离级别来解决并发问题
  • 4、隔离级别

							脏读			不可重复读		  幻读
read uncommitted:读未提交     ×                ×              ×        
read committed:读已提交      √                ×              ×
repeatable read:可重复读     √                √              ×
serializable:串行化          √                √              √
  • Oracle 支持的2 种事务隔离级别:READ COMMITED, SERIALIZABLE。Oracle 默认的事务隔离级别为: READ COMMITED
  • Mysql 支持4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

#2.演示事务对于delete和truncate的处理的区别
SET autocommit=0;
START TRANSACTION;

DELETE FROM account;
ROLLBACK;

回滚点的演示

  • savepoint 节点名;设置保存点
#3.演示savepoint 的使用
SET autocommit=0;

START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

SELECT * FROM account;

其他

视图

什么是视图?

  • 视图:虚拟表,和普通表一样使用。mysql5.1版本出现的新特性,是通过表动态生成的数据。

  • 应用场景

    • 多个地方用到同样的查询结果
    • 该查询结果使用的sql语句较复杂
  • 案例

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 '张%';

视图的创建

  • 语法:create view 视图名 as 查询语句;
  • 案例
USE myemployees;

#1.查询姓名中包含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%';

#2.查询各部门的平均工资级别
    #①创建视图查看每个部门的平均工资
    CREATE VIEW myv2 AS
    SELECT AVG(salary) ag,department_id
    FROM employees GROUP BY department_id;

    #②使用
    SELECT myv2.`ag`,g.grade_level FROM myv2
    JOIN job_grades g
    ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

#4.查询平均工资最低的部门名和工资
    CREATE VIEW myv3 AS
    SELECT * FROM myv2 ORDER BY ag LIMIT 1;

    SELECT d.*,m.ag FROM myv3 m
    JOIN departments d
    ON m.`department_id`=d.`department_id`;
  • 视图的好处
    • 重用sql语句
    • 简化复杂的sql操作,不必知道它的查询细节
    • 保护数据,提高安全性

视图的修改

  • 语法

    方式一:
    create or replace view 视图名 as 查询语句;
    方式二:
    alter view 视图名 as 查询语句;
    
  • 案例

#方式一:
/*
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权限。

  • 语法:drop view 视图1,视图2,…;

  • 案例

/*
语法:drop view 视图名,视图名,...;
*/
DROP VIEW emp_v1,emp_v2,myv3;

视图的查看

  • 语法

    desc 视图名;
    show create view 视图名;
    
  • 案例

DESC myv3;
SHOW CREATE VIEW myv3;

视图的更新

  • 1.插入:insert
  • 2.修改:update
  • 3.删除:delete
  • 4.查看:select
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 = '张无忌';
  • 注意:视图一般用于查询的,而不是更新的,所以具备以下特点的视图都不允许更新
    • 包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
    • 常量视图
    • Select中包含子查询
    • join
    • from一个不能更新的视图
    • where子句的子查询引用了from子句中的表
#具备以下特点的视图不允许更新
#①包含以下关键字的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';

视图和表的对比

		关键字		  是否占用物理空间			使用
视图	view		占用较小,只保存sql逻辑	一般用于查询
表	    table		保存实际的数据			  增删改查

欢迎斧正!!!

本文地址:https://blog.csdn.net/m0_46153949/article/details/107134707