#常见约束
/*
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类:六大约束
NOT NULL:非空,用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认,用于保证该字段有默认值
比如性别
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
比如学号、员工编号等
UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束,用于引用主表中某列的值
比如学生表的专业编号,员工表的部门编号,员工表的工种编号
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:
六大约束语法上都支持,但外键约束没有效果
表级约束:
除了非空、默认,其他的都支持
主键和唯一的大对比:
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √,但不推荐
唯一 √ √ 可以有多个 √,但不推荐
外键:
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3、主表的关联列必须是一个key(一般是主键或唯一)
4、插入数据时,先插入主表,再插入从表
删除数据时,先删除从表,再删除主表
*/
CREATE TABLE 表名 (
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
);
#一、创建表时添加约束
#1.添加列级约束
/*
语法:
直接在字段名和类型后面追加 约束类型即可。
只支持:默认、非空、主键、唯一
*/
CREATE DATABASE students;
CREATE TABLE stuinfo (
id INT PRIMARY KEY,
stuName VARCHAR (7) NOT NULL,
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,
major_name VARCHAR (10) UNIQUE
);
#查看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 un UNIQUE (seat),
CONSTRAINT fk_stu_maj FOREIGN KEY (majorid) REFERENCES major (id)
);
SHOW INDEX FROM stuinfo;
#通用的写法:★
CREATE TABLE IF NOT EXISTS stuinfo (
id INT PRIMARY KEY,
stuname VARCHAR (20),
gender 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
);
#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 VARCHAR (10) 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 (10) 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;
#案例讲解:
#1.向表emp2的id列中添加PRIMARY KEY约束(my_emp_id_pk)
CREATE TABLE emp2 (
id INT
);
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
#2.向表dept2的id列中添加PRIMARY KEY约束(my_dept_id_pk)
ALTER TABLE emp2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);
#3.向表emp2中添加列dept_id,
# 并在其中定义FOREIGN KEY约束,与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 ADD dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT for_key FOREIGN KEY(dept_id) REFERENCES dept2(id);
/*
位置 支持的约束类型 是否可以起约束名
列级约束: 列的后面 语法都支持,但外键没有效果 不可以
表级约束: 所有列的下面 默认和非空不支持,其他支持 可以(主键没有效果)
*/
标识列
#标识列
/*
又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1、标识列必须和主键搭配吗?不一定,但要求是一个key
2、一个表可以有几个标识列?至多一个!
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值,设置起始值
*/
#一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity (
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR (10)
);
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;
事务
#TCL
/*
Transaction Control Language 事务控制语言
事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
案例:转账
张三丰 1000
郭襄 1000
update 表 set 张三丰的余额=500 where name='张三丰'
意外
update 表 set 郭襄的余额=1500 where name='郭襄'
事务的特性:
ACID
原子性:一个事务不可再分割,要么都执行要么都不执行
一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性:一个事务的执行不受其他事务的干扰
持久性:一个事务一旦提交,则会永久的改变数据库的数据.
事务的创建
隐式事务:事务没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id =1;
显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用
set autocommit=0;
步骤1:开启事务
set autocommit=0;
start transaction;可选的
步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
步骤3:结束事务
commit;提交事务
rollback;回滚事务
savepoint 节点名;设置保存点
事务的隔离级别:
脏读 不可重复读 幻读
read uncommitted:√ √ √
read committed: × √ √
repeatable read: × × √
serializable × × ×
mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
开启事务的语句;
update 表 set 张三丰的余额=500 where name='张三丰'
update 表 set 郭襄的余额=1500 where name='郭襄'
结束事务的语句;
*/
SHOW VARIABLES LIKE 'autocommit';
SHOW ENGINES;
#1.演示事务的使用步骤
#开启事务
SET autocommit = 0;
START TRANSACTION;
#开启事务
SET autocommit = 0;
START TRANSACTION;
#编写一组事务的语句
UPDATE
account
SET
balance = 1000
WHERE username = '张无忌';
UPDATE
account
SET
balance = 1000
WHERE username = '赵敏';
#结束事务
ROLLBACK;
#commit;
SELECT
*
FROM
account;
#2.演示事务对于delete和truncate的处理的区别
SET autocommit = 0;
START TRANSACTION;
DELETE
FROM
account;
ROLLBACK;
#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版本出现的新特性,是通过表动态生成的数据
比如:舞蹈班和普通班级的对比
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查,只是一般不能增删改
表 create table 保存了数据 增删改查
*/
#案例:查询姓张的学生名和专业名
SELECT
*
FROM
`stuinfo`;
CREATE VIEW v1 AS
SELECT
stuname,
major_name
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 view1 AS
SELECT
last_name,
department_name,
job_title
FROM
employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
ON e.job_id = j.job_id;
SELECT
*
FROM
view1
WHERE last_name LIKE '%a%';
#2.查询各部门的平均工资级别
CREATE VIEW view2 AS
SELECT
AVG (salary) ag_sal,
department_id
FROM
employees
GROUP BY department_id;
SELECT
grade_level,
department_id
FROM
view2
JOIN job_grades
ON ag_sal BETWEEN lowest_sal
AND highest_sal;
#3.查询平均工资最低的部门信息
SELECT
*
FROM
view2
ORDER BY ag_sal
LIMIT 1;
#4.查询平均工资最低的部门名和工资
SELECT
department_name,
ag_sal
FROM
view2 v
JOIN departments d
ON v.`department_id` = d.`department_id`
ORDER BY ag_sal
LIMIT 1;
#二、视图的修改
#方式一:
/*
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 myv1,
myv3;
#四、查看视图
DESC view1;
SHOW CREATE VIEW view1;
#五、视图的更新
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 view1 AS
SELECT
last_name,
email
FROM
employees;
#1.插入
SELECT
*
FROM
view1;
INSERT INTO view1
VALUES
('张三丰', 'zsf@gmail.com');
#2.修改
UPDATE
view1
SET
last_name = '张无忌'
WHERE last_name = '张三丰';
#3.删除
DELETE
FROM
view1
WHERE last_name = '张无忌';
#具备以下特点的视图不允许更新
#①包含以下关键字的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';
#案例讲解:
#一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE VIEW emp_v1
AS
SELECT last_name,salary,email,phone_number
FROM employees
WHERE phone_number LIKE '011%';
SELECT * FROM emp_v1;
#二、创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
CREATE VIEW emp_v2
AS
SELECT MAX(salary)max_sal,department_id
FROM employees
GROUP BY department_id
HAVING max_sal>12000;
SELECT d.*
FROM departments d
JOIN emp_v2 e
ON e.department_id=d.department_id;
SELECT d.* FROM departments d
INNER JOIN (
SELECT MAX(salary) max_sal,department_id
FROM employees
GROUP BY department_id
HAVING max_sal>12000
) max_dep
ON max_dep.department_id = d.department_id;
变量
#变量
/*
系统变量:
全局变量
会话变量
自定义变量:
用户变量
局部变量
*/
#一、系统变量
/*
说明:变量由系统定义,不是用户定义,属于服务器层面
注意:全局变量需要添加global关键字,会话变量需要添加session关键字,如果不写,默认会话级别
使用步骤:
1、查看所有系统变量
show global|【session】variables;
2、查看满足条件的部分系统变量
show global|【session】 variables like '%char%';
3、查看指定的系统变量的值
select @@global|【session】系统变量名;
4、为某个系统变量赋值
方式一:
set global|【session】系统变量名=值;
方式二:
set @@global|【session】系统变量名=值;
*/
#1》全局变量
/*
作用域:针对于所有会话(连接)有效,但不能跨重启
*/
#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT
@@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit = 0;
SET GLOBAL autocommit = 0;
#2》会话变量
/*
作用域:针对于当前会话(连接)有效
*/
#①查看所有会话变量
SHOW SESSION VARIABLES;
#②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的会话变量的值
SELECT
@@autocommit;
SELECT
@@session.tx_isolation;
#④为某个会话变量赋值
SET @@session.tx_isolation = 'read-uncommitted';
SET SESSION tx_isolation = 'read-committed';
#二、自定义变量
/*
说明:变量由用户自定义,而不是系统提供的
使用步骤:
1、声明
2、赋值
3、使用(查看、比较、运算等)
*/
#1》用户变量
/*
作用域:针对于当前会话(连接)有效,作用域同于会话变量
*/
#赋值操作符:=或:=
#①声明并初始化
SET @变量名 = 值;
SET @变量名 := 值;
SELECT
@变量名 := 值;
#②赋值(更新变量的值)
#方式一:
SET @变量名 = 值;
SET @变量名 := 值;
SELECT
@变量名 := 值;
#方式二:
SELECT
字段 INTO @变量名
FROM
表;
#③使用(查看变量的值)
SELECT
@变量名;
#2》局部变量
/*
作用域:仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/
#①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;
#②赋值(更新变量的值)
#方式一:
SET 局部变量名 = 值;
SET 局部变量名 := 值;
SELECT
局部变量名 := 值;
#方式二:
SELECT
字段 INTO 具备变量名
FROM
表;
#③使用(查看变量的值)
SELECT
局部变量名;
#案例:声明两个变量,求和并打印
#用户变量
SET @a = 3;
SET @b = 6;
SET @sum = @a + @b;
SELECT
@sum;
#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE `sum` INT;
`sum` = m + n
SELECT
`sum`;
#用户变量和局部变量的对比
作用域 定义位置 语法 用户变量 当前会话 会话的任何地方 加 @符号,不用指定类型 局部变量 定义它的BEGIN END中
BEGIN
END的第一句话 一般不用加 @,
需要指定类型