本文汇总了本学期MySQL的学习内容
本文为学习黑马程序员的2022版MySQL课程后的学习笔记。
本人是初学者，接触MySQL几个月。
本文不涉及下载安装配置等相关操作。不使用docs，编辑器使用的是SQLyog，若需要安装包可以联系作者【仅限学习使用】。
创建表
- 数据库的查看选择及创建
-- 查看所有数据库
SHOW DATABASES;
-- 创建数据库
CREATE DATABASE mydb1;
CREATE DATABASE IF NOT EXISTS mydb1;
-- 选择使用哪一个数据库
USE mydb1;
结果如下
- 数据库删除及修改编码操作
-- 删除数据库
DROP DATABASE mydb1;
DROP DATABASE IF NOT EXISTS mydb1;
-- 修改数据库编码
ALTER DATABASE mydb1 CHARACTER SET utf8;
- 创建第一个表
首先是创建表，这里IF NOT EXISTS关键字防止重复创建而导致错误。
上面是正常创建表的过程，下面是在创建后对原表进行修改的操作。
-- 创建表
CREATE TABLE IF NOT EXISTS student(
sid INT UNSIGNED,
NAME VARCHAR(20),
gender VARCHAR(10),
age INT,
birth DATE,
address VARCHAR(20),
score DOUBLE
);
-- 1.添加列：alter table 表名 add 列名 类型(长度) [约束]
ALTER TABLE student ADD dept VARCHAR(20);
-- 2.修改列名和类型: alter table 表名 change 旧列名 新列名 类型(长度) [约束]
ALTER TABLE student CHANGE dept department VARCHAR(30);
-- 3.修改表删除列： alter table 表名 drop 列名;
ALTER TABLE student DROP department;
-- 4.修改表名: rename table 表名 to 新表名;
RENAME TABLE student TO stu;
- 向表中插入数据
一共有两种方式，第一种是指定需要插入的列；第二种是全部列均插入。插入多行可以中间用逗号隔开。
-- DML操作
-- 1.数据的插入
-- 格式1: insert into 表(列名1，列名2，列名3...) values(值1，值2，值3...);
INSERT INTO student(sid,NAME,gender,age,birth,address,score)
VALUES(1001,'张三','男',18,'2001-12-23','北京',85.5);
INSERT INTO student(sid,NAME,gender,age,birth,address,score)
VALUES(1001,'李四','男',19,'2002-09-13','上海',78.5);
VALUES(1001,'王五','女',17,'2003-11-29','深圳',66.5);
INSERT INTO student(sid) VALUES(1004);
INSERT INTO student(sid,NAME) VALUES(1005,'赵六');
-- 格式2：insert into 表 values(值1，值2，值3...)； //向表中插入所有列
INSERT INTO student VALUES(1006,'张华','女',21,'1999-01-08','广州',79);
INSERT INTO student VALUES(1007,'钱博','男',21,'2005-05-07','北京',98),
(1008,'牛子','男',21,'1998-05-04','武汉',89);
- 数据修改操作
这里可以与WHERE关键字一起使用
-- 数据修改
-- 格式1：update 表名 set 字段名=值，字段名=值...;
-- 格式2：update 表名 set 字段名=值，字段名=值... where 条件;
-- 1.将所有学生的地址修改为重庆
UPDATE student SET address = '重庆';
-- 2.将id为1004的学生的地址修改为北京
UPDATE student SET address = '北京' WHERE sid = 1004;
UPDATE student SET address = '北京' WHERE sid > 1004;
-- 3.将id为1005的学生的地址修改为北京，成绩修改为100
UPDATE student SET address = '北京', score = 100 WHERE sid = 1005;
- 删除数据
注意：删除操作中DELETE 和 TRUNCATE表面效果相同，其实是不相同的。利用TRUNCATE关键字删除的更加彻底，先删除整个表，然后进行创建。
-- 删除数据
-- 格式：delete from 表名 [where 条件];
-- truncate table 表名 或者 truncate 表名
-- 删除sid为1004的学生数据
DELETE FROM student WHERE sid = 1004;
-- 删除表所有数据
DELETE FROM student;
-- 清空表数据
TRUNCATE TABLE student;-- 更加彻底，先删除整个表，然后创建新表
TRUNCATE student;-- 这样的写法也是可以的
查询操作
- 基本查询操作，首先我们创建一个表并插入数据
-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb2;
USE mydb2;
-- 创建商品表
CREATE TABLE product(
pid INT PRIMARY KEY AUTO_INCREMENT, -- 商品编号
pname VARCHAR(20) NOT NULL, -- 商品名字
price DOUBLE, -- 商品价格
category_id VARCHAR(20) -- 商品所属分类
);
INSERT INTO product VALUES(NULL,'海尔洗衣机',5000,'c001');
INSERT INTO product VALUES(NULL,'美的音箱',3000,'c001');
INSERT INTO product VALUES(NULL,'格力空调',5000,'c001');
INSERT INTO product VALUES(NULL,'九阳电饭煲',5000,'c001');
INSERT INTO product VALUES(NULL,'啄木鸟衬衣',300,'c002');
INSERT INTO product VALUES(NULL,'恒源祥西裤',800,'c002');
INSERT INTO product VALUES(NULL,'花花公子夹克',440,'c002');
INSERT INTO product VALUES(NULL,'劲霸休闲裤',266,'c002');
INSERT INTO product VALUES(NULL,'海澜之家卫衣',180,'c002');
INSERT INTO product VALUES(NULL,'杰克琼斯运动裤',430,'c002');
INSERT INTO product VALUES(NULL,'兰蔻面霜',300,'c003');
INSERT INTO product VALUES(NULL,'雅诗兰黛精华水',200,'c003');
INSERT INTO product VALUES(NULL,'香奈儿香水',350,'c003');
INSERT INTO product VALUES(NULL,'SK-II神仙水',350,'c003');
INSERT INTO product VALUES(NULL,'资生堂粉底液',180,'c003');
INSERT INTO product VALUES(NULL,'老北京方便面',56,'c004');
INSERT INTO product VALUES(NULL,'良品铺子海带丝',17,'c004');
INSERT INTO product VALUES(NULL,'三只松鼠坚果',88,NULL);
- 对所有数据进行查询
-- 1.查询所有商品
SELECT pid,pNAME,price,category_id FROM product;
SELECT * FROM product;
- 有筛选条件（投影）查询
-- 2.查询商品名和商品价格
SELECT pname,price FROM product;
- 给表起别名操作
-- 3.1表别名:
SELECT * FROM product AS p;
SELECT * FROM product p;
-- 举个例子：SELECT p.id,u.id FROM product p, USER u;
-- 3.2列别名:
SELECT pname AS '商品名', price '商品价格' FROM product;
- 去重复查询
-- 4.去掉重复值
SELECT DISTINCT price FROM product;
SELECT DISTINCT * FROM product;
- 运算查询（即显示但并不改变数据库中元组的信息）
-- 5.查询结果是表达式(运算查询)：将所有商品的加价10元进行显示，
SELECT pname,price+10 new_price FROM product;
运算符
- 算术运算符
-- 1.算术运算符
SELECT 6+2;
SELECT 6-2;
SELECT 6*2;
SELECT 6/2;
SELECT 6%2;
-- 将所有商品的价格加10元
SELECT pname,price+10 AS new_price FROM product;
-- 将所有的商品价格上调10%
SELECT pname,price*1.1 AS new_price FROM product;
- 比较运算符和逻辑运算符
主义不能用 = NULL 只能用 IS NULL
-- 查询商品名称为“海尔洗衣机”的商品的所有信息
SELECT * FROM product WHERE pname = "海尔洗衣机";
-- 查询价格为800的商品
SELECT * FROM product WHERE price = 800;
-- 查询价格不是800的商品
SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE price <> 800;
SELECT * FROM product WHERE NOT (price = 800);
-- 查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price >= 60;
-- 查询价格在200到1000之间所有商品
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
SELECT * FROM product WHERE price >= 200 AND price <= 1000;
SELECT * FROM product WHERE price >=200 && price <= 1000;
-- 查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price IN(200,800);
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price = 200 || price = 800;
-- 查询含有'裤'字的所有商品
SELECT * FROM product WHERE pname LIKE '%裤%'; -- %用来匹配任意字符
-- 查询以'海'字开头的所有商品
SELECT * FROM product WHERE pname LIKE '海%';
-- 查询第二个字为'蔻'的所有商品
SELECT * FROM product WHERE pname LIKE '_蔻%'; -- 下划线匹配单个字符
-- 查询category_id为null的商品
SELECT * FROM product WHERE category_id IS NULL;
-- 查询category_id不为null的商品
SELECT * FROM product WHERE category_id IS NOT NULL;
-- 使用least求最小值
SELECT LEAST(10,5,20) AS small_number;
SELECT LEAST(10,NULL,20); -- 如果求最小值时，有个值为null，则不会进行比较，结果直接为null；
-- 使用greatest求最大值
SELECT GREATEST(10,20,30) AS big_number;
SELECT GREATEST(10,NULL,30) AS big_number; -- 如果求最大值时，有个值为null，则不会进行比较，结果直接为null；
- 位运算符
SELECT 3 & 5; -- 位与
SELECT 3 | 5; -- 位或
SELECT 3 ^ 5; -- 位异或
SELECT 3 >> 5; -- 位右移
SELECT 3 << 5; -- 位左移
SELECT ~3; -- 位取反
- 排序查询
-- 1.使用价格排序（降序）
SELECT * FROM product ORDER BY price DESC;
-- 2.在价格排序（降序）的基础上，以分类排序（降序）
SELECT * FROM product ORDER BY price DESC, category_id DESC;
-- 3.显示商品的价格（去重复），并排序（降序）
SELECT DISTINCT price FROM product ORDER BY price DESC;
聚合函数
-
count() 统计指定列不为NULL的记录行数;
-
sum() 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
-
max() 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
-
min() 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
-
avg() 计算指定列的平均值，如果指定类型不是数值类型，那么计算结果为0.
-
对于上述创建表的相关操作
-- 1.查询商品的总条数
SELECT COUNT(pid) FROM product;
SELECT COUNT(*) FROM product;
-- 2.查询价格大于200商品的总条数
SELECT COUNT(pid) FROM product WHERE price > 200;
-- 3.查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
-- 4.查询商品的最大价格
SELECT MAX(price) FROM product;
-- 5.查询商品的最小价格
SELECT MIN(price) FROM product;
SELECT MAX(price) max_price,MIN(price) min_price FROM product;
-- 6.查询分为为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';
- 对于NULL值的处理，可以自行尝试
USE mydb2;
-- 创建表
CREATE TABLE test_null(
c1 VARCHAR(20),
c2 INT
);
-- 插入数据
INSERT INTO test_null VALUES('aaa',3);
INSERT INTO test_null VALUES('bbb',3);
INSERT INTO test_null VALUES('ccc',NULL);
INSERT INTO test_null VALUES('ddd',6);
-- 测试
SELECT COUNT(*),COUNT(1),COUNT(c2) FROM test_null;
SELECT SUM(c2),MAX(c2),MIN(c2),AVG(c2) FROM test_null;
- 分组查询（这里需要使用HAVING关键字）
- select 字段1，字段2.... from 表名 group by 分组字段 having 分组条件;
-- 1.统计各个分类商品的个数
-- 注意 分组之后 select的后边只能写分组字段和聚合函数
SELECT category_id,COUNT(pid) FROM product GROUP BY category_id;
-- 分组之后的条件筛选-having
-- select 字段1,字段2... from 表明 group by 分组字段 having 分组条件;
-- 2.统计各个分类商品的个数，且只显示个数大于4的信息
-- SQL执行顺序：from -> group by -> count(pid) > select -> having -> order by
SELECT
category_id,COUNT(pid) cnt
FROM
product
GROUP BY
category_id
HAVING
cnt > 4
ORDER BY
cnt;
- 分页查询（使用limit关键字）
-- 分页查询-limit
-- 1.查询product表的前5条记录
SELECT * FROM product LIMIT 5;
-- 2.从第4条开始显示，显示5条
SELECT * FROM product LIMIT 3,5;
-- 3.分页显示
SELECT * FROM product LIMIT 0,60;
SELECT * FROM product LIMIT 60,60;
SELECT * FROM product LIMIT 120,60;
SELECT * FROM product LIMIT (n-1)*60,60;
正则表达式
-- 正则表达式
-- ^在字符串开始处进行匹配
SELECT 'abc' REGEXP '^a';
SELECT * FROM product WHERE pname REGEXP '^海';
-- $ 在字符串末尾开始匹配
SELECT 'abc' REGEXP 'a$';
SELECT 'abc' REGEXP 'c$';
SELECT * FROM product WHERE pname REGEXP '水$';
-- .匹配任意单个字符，可以匹配除了换行符之外的任意字符
SELECT 'abc' REGEXP '.b';
SELECT 'abc' REGEXP '.c';
SELECT 'abc' REGEXP 'a.';
-- [...]匹配括号内的任意单个字符
SELECT 'abc' REGEXP '[xyz]';
SELECT 'abc' REGEXP '[xaz]';
-- [^...]注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
SELECT 'a' REGEXP '[^abc]';
SELECT 'x' REGEXP '[^abc]';
SELECT 'abc' REGEXP '[^a]'; -- 1
-- a* 匹配0个或多个a，包括空字符串。可以作占位符使用，有没有指定字符都可以匹配到数据
SELECT 'stab' REGEXP '.ta*b'; -- 1
SELECT 'stb' REGEXP '.ta*b'; -- 1
SELECT ''REGEXP 'a*'; -- 1
-- a+ 匹配1个或者多个a，但是不包括空字符
SELECT 'stab' REGEXP '.ta+b'; -- 1
SELECT 'stb' REGEXP '.ta+b'; -- 0
-- a? 匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b'; -- 1
SELECT 'stab' REGEXP '.ta?b'; -- 1
SELECT 'staab' REGEXP '.ta?b'; -- 0
-- a1|a2 匹配a1或者a2
SELECT 'a' REGEXP 'a|b'; -- 1
SELECT 'b' REGEXP 'a|b'; -- 1
-- 下面的表示以什么开头
SELECT 'b' REGEXP '^(a|b)'; -- 1
SELECT 'a' REGEXP '^(a|b)'; -- 1
SELECT 'c' REGEXP '^(a|b)'; -- 0
-- a{m} 匹配m个a
SELECT 'auuuuc' REGEXP 'au{4}c' -- 1
SELECT 'auuuuc' REGEXP 'au{3}c' -- 0
-- a{m,} 匹配m个或者更多个a
SELECT 'auuuuc' REGEXP 'au{3,}c' -- 1
SELECT 'auuuuc' REGEXP 'au{4,}c' -- 1
SELECT 'auuuuc' REGEXP 'au{5,}c' -- 0
-- a{m,n} 匹配m到n个a，包含m和n
SELECT 'auuuuc' REGEXP 'au{3,5}c' -- 1
SELECT 'auuuuc' REGEXP 'au{4,5}c' -- 1
SELECT 'auuuuc' REGEXP 'au{5,10}c' -- 0
-- (abc)
-- abc作为一个匹配序列，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用括号，所以括号适合上面的所有情况
SELECT 'xababy' REGEXP 'x(abab)y'; -- 1
SELECT 'xababy' REGEXP 'x(ab)*y'; -- 1
SELECT 'xababy' REGEXP 'x(ab){1,2}y'; -- 1
SELECT 'xababy' REGEXP 'x(ab){3}y'; -- 0
键
- 创建主键约束
共有两种方式，一种是直接在属性后添加主键约束，另一种是在全部声明完属性后利用CONSTRAINT关键字进行主键添加。
不可以有两个主键相同的元组，并且主键不能为空。
/*
在create table语句中，通过 PRIMARY KEY关键字来指定主键
在定义字段的同时指定主键，语法格式如下:
create table 表名(
...
<字段名><数据类型> primary key
)
*/
USE mydb1;
CREATE TABLE emp1(
eid INT PRIMARY KEY,
NAME VARCHAR(20),
deptId INT,
salary DOUBLE
);
-- 下面是方法2
CREATE TABLE emp2(
eid INT,
NAME VARCHAR(20),
deptId INT,
salary DOUBLE,
CONSTRAINT pk1 PRIMARY KEY(eid) -- constraint pk1 可以省略
)
- 联合主键
即主键由一张表中多个字段组成
-- 联合主键
-- 所谓的联合主键，就是这个主键是由一张表中多个字段组成的
-- primary key(字段1，字段2，...，字段n)
CREATE TABLE emp3(
NAME VARCHAR(20),
deptId INT,
salary DOUBLE,
CONSTRAINT pk2 PRIMARY KEY(NAME,deptId)
);
-- 联合主键的各列，每一列都不能为空
INSERT INTO emp3 VALUES('张三',30,5000);
- 在声明表之后添加单/多列主键的方法
使用ALTER TABLE
-- 添加单列主键
CREATE TABLE emp4(
eid INT,
NAME VARCHAR(20),
deptId INT,
salary DOUBLE
);
ALTER TABLE emp4 ADD PRIMARY KEY(eid);
-- 添加多列主键
CREATE TABLE emp5(
eid INT,
NAME VARCHAR(20),
deptId INT,
salary DOUBLE
);
- 删除单/多列主键的方法——(简单粗暴)
- 删除主键
-- 1.删除单列主键
ALTER TABLE emp1 DROP PRIMARY KEY;
-- 2.删除多列主键
ALTER TABLE emp5 DROP PRIMARY KEY;
- 自增长约束
从本处可以看出DELETE 和 TRUNCATE的区别
-- 自增长约束
CREATE TABLE t_user1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO t_user1 VALUES(NULL,'张三');
INSERT INTO t_user1(NAME) VALUES('李四');
DELETE FROM t_user1; -- delete删除数据之后，自增长还是在最后一个值基础上加1
INSERT INTO t_user1 VALUES(NULL,'张三');
INSERT INTO t_user1(NAME) VALUES('李四');
TRUNCATE t_user1; -- truncate删除之后，自增长从1开始
INSERT INTO t_user1 VALUES(NULL,'张三');
INSERT INTO t_user1(NAME) VALUES('李四');
- 指定自增长约束的初始值
-- 指定自增长的初始值
-- 方式一：创建表时指定
CREATE TABLE t_user2(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)AUTO_INCREMENT = 100;
INSERT INTO t_user2 VALUES(NULL,'张三');
-- 方式二：创建表之后指定
CREATE TABLE t_user3(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
ALTER TABLE t_user3 AUTO_INCREMENT = 200;
INSERT INTO t_user3 VALUES(NULL,'张三');
- 创建非空约束
-- 1.创建非空约束-方式1，创建表时指定
CREATE TABLE t_user6(
id INT,
NAME VARCHAR(20) NOT NULL, -- 指定非空约束
address VARCHAR(20) NOT NULL -- 指定非空约束
);
INSERT INTO t_user6(id) VALUES(1001);-- 不可以
INSERT INTO t_user6(id,NAME,address) VALUES(1001,NULL,NULL);-- 不可以
INSERT INTO t_user6(id,NAME,address) VALUES(1001,'NULL','NULL');-- 可以
INSERT INTO t_user6(id,NAME,address) VALUES(1001,'','');-- 可以
-- 2.创建非空约束-方式2，创建表之后指定
CREATE TABLE t_user7(
id INT,
NAME VARCHAR(20),
address VARCHAR(20)
);
ALTER TABLE t_user7 MODIFY NAME VARCHAR(20) NOT NULL;
ALTER TABLE t_user7 MODIFY address VARCHAR(20) NOT NULL;
- 删除非空约束
-- 3.删除非空约束
-- alter table 表名 modify 字段 类型
ALTER TABLE t_user7 MODIFY NAME VARCHAR(20);
ALTER TABLE t_user7 MODIFY address VARCHAR(20);
- 创建唯一约束
-- 唯一约束
-- 1.添加唯一约束-方式1-创建表时指定
CREATE TABLE t_user8(
id INT,
NAME VARCHAR(20),
phone_number VARCHAR(20) UNIQUE -- 指定唯一约束
);
INSERT INTO t_user8 VALUES(1001,'张三',138);
INSERT INTO t_user8 VALUES(1001,'张三2',139);
INSERT INTO t_user8 VALUES(1001,'张三3',NULL);
INSERT INTO t_user8 VALUES(1001,'张三4',NULL);-- 在MYSQL中 NULL和任何值都不相同 甚至和自己都不相同
-- 2.添加唯一约束-方式2-创建表之后指定
CREATE TABLE t_user9(
id INT,
NAME VARCHAR(20),
phone_number VARCHAR(20)
);
ALTER TABLE t_user9 ADD CONSTRAINT unique_pn UNIQUE(phone_number);
- 删除唯一约束（与上面的操作相同）
-- 3.删除唯一约束
-- 格式： alter table <表名> drop index<唯一约束名>
ALTER TABLE t_user9 DROP INDEX unique_pn;
- 创建默认约束及删除默认约束
-- 默认约束
-- 方式1-创建表时指定
CREATE TABLE t_user10(
id INT,
NAME VARCHAR(20),
address VARCHAR(20) DEFAULT '北京' -- 指定默认约束
);
INSERT INTO t_user10(id,NAME) VALUE(1001,'张三');
INSERT INTO t_user10(id,NAME,address) VALUE(1001,'张三','上海');
INSERT INTO t_user10 VALUE(1002,'李四',NULL);
-- 方式2-创建表之后指定
-- alter table 表名 modify 列名 类型 default 默认值;
CREATE TABLE t_user11(
id INT,
NAME VARCHAR(20),
address VARCHAR(20)
);
ALTER TABLE t_user11 MODIFY address VARCHAR(20) DEFAULT '深圳';
-- 2.删除默认约束
-- alter table <表名> change column <字段名> <类型> default null;
ALTER TABLE t_user11 MODIFY address VARCHAR(20) DEFAULT NULL;
- 零填充约束
-- 零填充约束
-- 1.添加约束
CREATE TABLE t_user12(
id INT ZEROFILL, -- 零填充约束
NAME VARCHAR(20)
);
INSERT INTO t_user12 VALUES(123,'张三');
-- 2.删除约束
ALTER TABLE t_user12 MODIFY id INT;
外键
- 创建外键的基本方式
-- 创建部门表
CREATE TABLE IF NOT EXISTS dept(
deptno VARCHAR(20) PRIMARY KEY, -- 部门号
NAME VARCHAR(20) -- 部门名字
);
-- 创建员工表，并创建外键约束 -方式1
-- [constraint <外键名>] foreign key 字段名 [, 字段名2,...] references <主表名> 主键列1 [,主键列2,...]
CREATE TABLE IF NOT EXISTS emp(
eid VARCHAR(20) PRIMARY KEY, -- 员工编号
ename VARCHAR(20), -- 员工名字
age INT, -- 员工年龄
dept_id VARCHAR(20), -- 员工所属部门
CONSTRAINT emp_fk FOREIGN KEY(dept_id) REFERENCES dept(deptno)
);
- 如果多个表之间有关系才会使用外键，故在此处我们需要创建多个表
-- 创建员工表，并创建外键约束 -方式2
-- [constraint <外键名>] foreign key 字段名 [, 字段名2,...] references <主表名> 主键列1 [,主键列2,...]
CREATE TABLE
IF NOT EXISTS dept2 (
deptno VARCHAR (20) PRIMARY KEY,
-- 部门号
NAME VARCHAR (20) -- 部门名字
);
CREATE TABLE
IF NOT EXISTS emp2 (
eid VARCHAR (20) PRIMARY KEY,
-- 员工编号
ename VARCHAR (20),
-- 员工名字
age INT,
-- 员工年龄
dept_id VARCHAR (20) -- 员工所属部门
);
ALTER TABLE emp2 ADD CONSTRAINT emp2_fk FOREIGN KEY (dept_id) REFERENCES dept2 (deptno);
- 给表添加数据
-- 1、添加主表数据
-- 注意必须先给主表添加数据
INSERT INTO dept VALUES ('1001', '研发部');
INSERT INTO dept VALUES ('1002', '销售部');
INSERT INTO dept VALUES ('1003', '财务部');
INSERT INTO dept VALUES ('1004', '人事部');
-- 2、添加从表数据
-- 注意给从表添加数据时，外键列的值必须依赖主表的主键列
INSERT INTO emp VALUES('1','乔峰',20,'1001');
INSERT INTO emp VALUES('2','段誉',21,'1001');
INSERT INTO emp VALUES('3','虚竹',23,'1001');
INSERT INTO emp VALUES('4','阿紫',18,'1002');
INSERT INTO emp VALUES('5','扫地僧',35,'1002');
INSERT INTO emp VALUES('6','李秋水',33,'1003');
INSERT INTO emp VALUES('7','鸠摩智',50,'1003');
INSERT INTO emp VALUES('8','天山童姥',60,'1005'); -- 不可以
- 删除数据
注意，从表可以随便删除，主表中不能随便删除
-- 3、删除数据
/*
注意：
1：主表的数据被从表依赖时，不能删除，否则可以删除
2：从表的数据可以随便删除
*/
DELETE FROM dept WHERE deptno = '1001'; -- 不可以删除
DELETE FROM dept WHERE deptno = '1004'; -- 可以删除
DELETE FROM emp WHERE eid = '7'; -- 可以删除
DELETE FROM emp;
- 删除外键约束
-- 4、删除外键约束
-- alter table <表名> drop foreign key <外键约束名>
ALTER TABLE emp2 DROP FOREIGN KEY emp2_fk;
内连接和外连接
- 创建表及添加数据
-- 创建部门表
CREATE TABLE IF NOT EXISTS dept3(
deptno VARCHAR(20) PRIMARY KEY, -- 部门号
NAME VARCHAR(20) -- 部门名字
);
-- 创建员工表
CREATE TABLE IF NOT EXISTS emp3(
eid VARCHAR(20) PRIMARY KEY, -- 员工编号
ename VARCHAR(20), -- 员工名字
age INT, -- 员工年龄
dept_id VARCHAR(20) -- 员工所属部门
);
-- 给dept3表添加数据
INSERT INTO dept3 VALUES('1001','研发部');
INSERT INTO dept3 VALUES('1002','销售部');
INSERT INTO dept3 VALUES('1003','财务部');
INSERT INTO dept3 VALUES('1004','人事部');
-- 给emp3表添加数据
INSERT INTO emp3 VALUES('1','乔峰',20,'1001');
INSERT INTO emp3 VALUES('2','段誉',27,'1001');
INSERT INTO emp3 VALUES('3','虚竹',21,'1001');
INSERT INTO emp3 VALUES('4','阿紫',23,'1001');
INSERT INTO emp3 VALUES('5','扫地僧',18,'1002');
INSERT INTO emp3 VALUES('6','李秋水',85,'1002');
INSERT INTO emp3 VALUES('7','鸠摩智',33,'1002');
INSERT INTO emp3 VALUES('8','天山童姥',50,'1003');
INSERT INTO emp3 VALUES('9','慕容博',60,'1003');
INSERT INTO emp3 VALUES('10','丁春秋',58,'1005');
- 内连接操作
-- 查询每个部门的所属员工
-- 隐式内连接
SELECT * FROM dept3,emp3 WHERE dept3.deptno = emp3.dept_id;
SELECT * FROM dept3 a,emp3 b WHERE a.deptno = b.dept_id;
-- 显示内连接
SELECT * FROM dept3 INNER JOIN emp3 ON dept3.deptno = emp3.dept_id;
SELECT * FROM dept3 a INNER JOIN emp3 b ON a.deptno = b.dept_id;
-- 查询研发部门的所属员工
-- 隐式内连接
SELECT * FROM dept3 a, emp3 b WHERE a.deptno = b.dept_id AND NAME = '研发部';
-- 显示内连接
SELECT * FROM dept3 a INNER JOIN emp3 b ON a.deptno = b.dept_id AND NAME = '研发部';
-- 查询研发部和销售部的所属员工
SELECT * FROM dept3 a JOIN emp3 b ON a.deptno = b.dept_id AND (NAME = '研发部' OR NAME = '销售部');
SELECT * FROM dept3 a JOIN emp3 b ON a.deptno = b.dept_id AND NAME IN('研发部','销售部');
-- 查询每个部门的员工数，并升序排序
SELECT a.name,a.deptno,COUNT(1) FROM dept3 a JOIN emp3 b ON a.deptno = b.dept_id GROUP BY a.deptno,NAME;
-- 查询人数大于等于3的部门，并按照人数降序排序
SELECT
a.deptno,
a.name,
COUNT(1) AS total_cnt
FROM dept3 a
JOIN emp3 b ON a.deptno = b.dept_id
GROUP BY
a.deptno,NAME
HAVING
total_cnt >= 3
ORDER BY
total_cnt DESC;
- 外连接操作
-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
SELECT * FROM dept3 a LEFT OUTER JOIN emp3 b ON a.deptno = b.dept_id;
SELECT * FROM dept3 a LEFT JOIN emp3 b ON a.deptno = b.dept_id;
/*
select
*
from A
LEFT JOIN B ON 条件1
LEFT JOIN C ON 条件2
LEFT JOIN D ON 条件3
*/
-- 查询哪些员工有对应的部门，哪些没有
SELECT * FROM dept3 a RIGHT OUTER JOIN emp3 b ON a.deptno = b.dept_id;
SELECT * FROM dept3 a RIGHT JOIN emp3 b ON a.deptno = b.dept_id;
/*
select
*
from A
RIGHT JOIN B ON 条件1
RIGHT JOIN C ON 条件2
RIGHT JOIN D ON 条件3
*/
- 满外连接操作
-- 实现满外连接：full join
-- 使用union关键字实现做外连接和右外连接的并集
-- union是将两个查询结果上下拼接，并去重
SELECT * FROM dept3 a LEFT JOIN emp3 b ON a.deptno = b.dept_id
UNION
SELECT * FROM dept3 a RIGHT JOIN emp3 b ON a.deptno = b.dept_id;
SELECT * FROM dept3 a LEFT JOIN emp3 b ON a.deptno = b.dept_id
UNION ALL
SELECT * FROM dept3 a RIGHT JOIN emp3 b ON a.deptno = b.dept_id;
- 子查询操作
-- 查询年龄最大的员工信息，显示信息包括员工号，员工名字，员工年龄
-- 1:查询最大年龄
SELECT MAX(age) FROM emp3;
-- 2:让每一个员工的年龄和最大年龄进行比较，相等则满足条件
SELECT * FROM emp3 WHERE age = (SELECT MAX(age) FROM emp3);
-- 查询研发部和销售部的员工信息，包含员工号、员工名字
-- 方式1-关联查询
SELECT * FROM dept3 a JOIN emp3 b ON a.deptno = b.dept_id AND NAME = '研发部' OR NAME= '销售部';
-- 查询方式2-子查询
-- 2.1先查询研发部和销售部的部门号：deptno 1001 和 1002
SELECT deptno FROM dept3 WHERE NAME = '研发部' OR NAME = '销售部';
-- 2.2查询哪个员工的部门号是1001 或者 1002
SELECT * FROM emp3 WHERE dept_id IN (SELECT deptno FROM dept3 WHERE NAME = '研发部' OR NAME = '销售部');
-- 查询研发部30岁以下的员工信息，包括员工号、员工名字、部门名字
-- 方式1-关联查询
SELECT * FROM dept3 a JOIN emp3 b ON a.deptno = b.dept_id AND (NAME = '研发部' AND age < 30);
-- 方式2-子查询
-- 2.1 在部门表中查询研发部信息
SELECT * FROM dept3 WHERE NAME = '研发部';
-- 2.2 在员工表中查询年龄小于30岁的员工信息
SELECT * FROM emp3 WHERE age < 30;
-- 2.3 将以上两个查询的结果进行关联查询
SELECT * FROM (SELECT * FROM dept3 WHERE NAME = '研发部') t1 JOIN (SELECT * FROM emp3 WHERE age < 30) t2 ON t1.deptno = t2.dept_id;
-- 子查询-关键字
-- 1.查询年龄大于'1003'部门所有年龄的员工信息
SELECT * FROM emp3 WHERE age > ALL(SELECT age FROM emp3 WHERE dept_id = '1003');
-- 2.查询不属于任何一个部门的员工信息
SELECT * FROM emp3 WHERE dept_id != ALL(SELECT deptno FROM dept3);
-- 子查询关键字 ANY和SOME
-- 查询年龄大于'1003'部门任意一个员工年龄的员工信息
SELECT * FROM emp3 WHERE age > SOME(SELECT age FROM emp3 WHERE dept_id = '1003') AND dept_id != 1003;
-- 子查询关键字-IN
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
SELECT eid, ename FROM emp3 WHERE dept_id IN(SELECT deptno FROM dept3 WHERE NAME = '研发部' OR NAME = '销售部');
-- 子查询关键字-exists
-- select ... from ... where exists(查询语句)
SELECT * FROM emp3 WHERE EXISTS(SELECT * FROM emp3);
-- 查询公式是否有大于60岁的员工，有则输出
SELECT * FROM emp3 a WHERE EXISTS(SELECT * FROM emp3 WHERE a.age > 60);
SELECT * FROM emp3 a WHERE eid IN(SELECT eid FROM emp3 WHERE a.age > 60);
-- 查询有所属部门的员工信息
SELECT * FROM emp3 a WHERE EXISTS(SELECT * FROM dept3 b WHERE a.dept_id = b.deptno);
SELECT * FROM emp3 a WHERE dept_id IN(SELECT deptno FROM dept3 b WHERE a.dept_id = b.deptno);
一些常用的函数
不用死记硬背，需要的时候进行查询即可
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
emp_name CHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
salary DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '工资',
department CHAR(20) NOT NULL DEFAULT '' COMMENT '部门'
);
INSERT INTO emp(emp_name,salary,department)
VALUES('张晶晶',5000,'财务部'),('王飞飞',5000,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部')
-- 将所有员工的名字合并成一行
SELECT GROUP_CONCAT(emp_name)FROM emp;
-- 指定分隔符合并
SELECT GROUP_CONCAT(emp_name SEPARATOR ';') FROM emp;
-- 指定排序方式和分隔符
SELECT department,GROUP_CONCAT(emp_name SEPARATOR ';') FROM emp GROUP BY department;
SELECT department,GROUP_CONCAT(emp_name ORDER BY salary SEPARATOR ';') FROM emp GROUP BY department;
-- 数学函数
SELECT ABS(-10);
SELECT ABS(10);
SELECT ABS(表示式或者字段) FROM 表
-- 向上取整
SELECT CEIL(1.1);
SELECT CEIL(1.0);
-- 向下取整
SELECT FLOOR(1.1);
SELECT FLOOR(1.9);
-- 取列表最大值
SELECT GREATEST(1,2,3);
-- 取列表最小值
SELECT LEAST(1,2,3);
-- 取模
SELECT MOD(5,2);
-- 取x的y次方
SELECT POWER(2,3);
-- 取随机数
SELECT RAND() * 100;
SELECT FLOOR(RAND() * 100);
-- 取四舍五入的小数
SELECT ROUND(3.1415);
-- 将小数的四舍五入取指定位数小数
SELECT ROUND(3.1415,3);
USE mydb2;
SELECT category_id,ROUND(AVG(price),2) FROM product GROUP BY category_id;
-- 将小数直接截取到指定位数
SELECT TRUNCATE(3.1415,3);-- 3.141
-- 字符串函数
-- 1:获取字符串字符个数
SELECT CHAR_LENGTH('hello');
SELECT CHAR_LENGTH('你好吗');
-- length取长度，返回的单位是字节
SELECT LENGTH('hello');
SELECT LENGTH('你好吗');
-- 2:字符串合并
SELECT CONCAT('hello','world');
SELECT CONCAT(c1,c2) FROM TABLE_NAME;
-- 2：指定分隔符进行字符串合并
SELECT CONCAT_WS('-','hello','world');
-- 3:返回字符串在列表中的位置
SELECT FIELD('aaa','aaa','bbb','ccc');
SELECT FIELD('bbb','aaa','bbb','ccc');
-- 4:去除字符串左边空格
SELECT LTRIM(' aaaa');-- 去除左侧空格
SELECT RTRIM(' a ');-- 去除右侧空格
SELECT TRIM(' a ');-- 去除两端空格
-- 5：字符串截取
SELECT MID("helloworld",2,3); -- 从第二个字符开始截取，截取长度为3
-- 6：获取字符串A在字符串B中的位置
SELECT POSITION('abc' IN 'helloabcworld');
-- 7:字符串替换
SELECT REPLACE('helloaaaworld','aaa',' ');
-- 8:字符串翻转
SELECT REVERSE('hello');
-- 9:返回字符串的后几个字符
SELECT RIGHT('hello',3); -- 返回最后三个字符
-- 10:字符串比较
SELECT STRCMP('hello','world');
-- 11:字符串截取
SELECT SUBSTR('hello',2,3); -- 从第二个字符开始，截取三个字符
SELECT SUBSTRING('hello',2,3); -- 从第二个字符开始，截取三个字符
-- 这两个函数的功能完全一样，只是名字不一样而已
-- 12:将小写转大写
SELECT UCASE('helloWorld');
SELECT UPPER('helloWorld');
-- 13:将大写转为小写
SELECT LCASE('HELLOWORLD');
-- 日期函数
-- 1：获取时间戳（毫秒值）
SELECT UNIX_TIMESTAMP();
-- 2:将一个日期字符串转为毫秒值
SELECT UNIX_TIMESTAMP('2021-12-21 08:08:08');
-- 3:将时间戳毫秒值转为指定格式的日期
SELECT FROM_UNIXTIME(1640045288,'%Y-%m-%d %H:%i:%s');
-- 4:获取当前的年月日
SELECT CURDATE();
SELECT CURRENT_DATE();
-- 5:获取当前的时分秒
SELECT CURRENT_TIME();
SELECT CURTIME();
-- 6:获取你年月日 和 时分秒
SELECT CURRENT_TIMESTAMP();
-- 7:从日期字符串中获取年月日
SELECT DATE('2022-12-12 12:34:56');
-- 8:获取时间之间的差值
SELECT DATEDIFF('2021-12-23','2008-08-08');
SELECT DATEDIFF(CURRENT_DATE(),'2008-08-08');
-- 9:获取时间的差值（秒级）
SELECT TIMEDIFF('12:12:34','10:18:56'); -- 01:53:38
-- 10：日期格式化
SELECT DATE_FORMAT('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s');
SELECT DATE_FORMAT('2021-12-13 11:11:11','%Y-%m-%d %H:%i:%s');
-- 11:将字符串转为日期
SELECT STR_TO_DATE('2021-12-13 11:11:11','%Y-%m-%d %H:%i:%s');
-- 12：将日期进行减法 -- 日期向前跳转
SELECT DATE_SUB('2021-10-01',INTERVAL 2 DAY);
SELECT DATE_SUB('2021-10-01',INTERVAL 2 MONTH);
-- 12：将日期进行加法 -- 日期向后跳转
SELECT DATE_ADD('2021-10-01',INTERVAL 2 DAY);
SELECT DATE_ADD('2021-10-01',INTERVAL 2 MONTH);
-- 14：从日期中获取小时
SELECT EXTRACT(HOUR FROM '2022-12-12 12:34:56');
SELECT EXTRACT(YEAR FROM '2022-12-12 12:34:56');
SELECT EXTRACT(MONTH FROM '2022-12-12 12:34:56');
-- 15:获取给定日期所在月的最后一天
SELECT LAST_DAY('2021-08-13');
-- 16:获取指定年份和天数的日期
SELECT MAKEDATE('2021',53);
-- 17:根据日期获取年月日，时分秒
SELECT YEAR('2021-12-13 11:12:13');
SELECT MONTH('2021-12-13 11:12:13');
SELECT MINUTE('2021-12-13 11:12:13');
SELECT QUARTER('2021-12-13 11:12:13'); -- 获取季度
-- 18：根据日期获取信息
SELECT MONTHNAME('2021-12-13 11:12:13'); -- 获取月份的英文
SELECT DAYNAME('2021-12-13 11:12:13'); -- 获取周几：Monday
SELECT DAYOFMONTH('2021-12-13 11:12:13'); -- 当月的第几天
SELECT DAYOFWEEK('2021-12-13 11:12:13'); -- 1：周日 2 周一
SELECT DAYOFYEAR('2021-12-13 11:12:13'); -- 获取一年的第几天
SELECT WEEK ('2021-12-13 11:12:13');
SELECT YEARWEEK('2021-12-13');
SELECT NOW();
视图
- 创建视图
-- 创建视图
USE mydb6_view;
CREATE OR REPLACE VIEW view1_emp
AS
SELECT ename,job FROM emp;
SELECT * FROM view1_emp;
UPDATE view1_emp SET ename = '周瑜' WHERE ename = '鲁肃';
INSERT INTO view1_emp VALUES('周瑜','文员');
- 查看表和视图
SHOW TABLES;
SHOW FULL TABLES;
SELECT * FROM view1_emp;
- 修改视图
-- 修改视图
ALTER VIEW view1_emp
AS
SELECT a.deptno,a.dname,a.loc,b.ename,b.sal FROM dept a,emp b WHERE a.deptno = b.deptno;
SELECT * FROM view1_emp;
- 不可以进行更新的视图
1.若视图是由两个以上基本表导出的，则此视图不允许更新
2.若视图的字段来⾃字段表达式或常数，则不允许对此视图执⾏INSERT和UPDATE操作，但允许执⾏DELETE
3.若视图字段来⾃聚集函数AVG、SUM等，则此视图不允许更新
4.若视图定义中含有GROUP BY HAVING⼦句，则此视图不允许更新
5.若视图中含有DISTINCT语句，则此视图不允许更新
6.若视图定义中含有嵌套查询，并且内层查询的FROM⼦句涉及的表也是导出该视图的基本表，则此视图不允许更新
7.⼀个不允许更新的视图上定义的视图也不允许更新
-- 视图包含聚合函数不可更新
CREATE OR REPLACE VIEW view2_emp
AS
SELECT COUNT(*) cnt FROM emp;
SELECT * FROM view2_emp;
INSERT INTO view2_emp VALUES(100);
UPDATE view2_emp SET cnt = 100;
-- 视图不可包含distinct关键字
CREATE OR REPLACE VIEW view3_emp
AS
SELECT DISTINCT job FROM emp;
INSERT INTO view3_emp VALUES('财务');
-- 其他，就不都写了
CREATE OR REPLACE view4_emp
AS
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING cnt>2;
INSERT INTO view2_emp VALUES(30,100);
- 其他视图操作
-- 重命名视图
RENAME TABLE view1_emp TO myview1;
-- 删除视图
DROP VIEW IF EXISTS myview1;
对比不用视图和用视图的区别
-- 1.查询部门平均薪水最高的部门名称
SELECT
a.deptno,
a.`dname`,
a.`loc`,
avg_sal
FROM
dept a,
(SELECT
*
FROM
(SELECT
*,
RANK () OVER (
ORDER BY avg_sal DESC) rn
FROM
(SELECT
deptno,
AVG(sal) avg_sal
FROM
emp
GROUP BY deptno) t) tt
WHERE rn = 1) ttt
WHERE a.deptno = ttt.deptno ;
-- ---------------------------------
CREATE VIEW test_view1
AS
SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno;
CREATE VIEW test_view2
AS
SELECT *,RANK() OVER(ORDER BY avg_sal DESC)rn FROM test_view1;
CREATE VIEW test_view3
AS
SELECT * FROM test_view2 tt WHERE rn = 1;
SELECT
a.deptno,
a.`dname`,
a.`loc`,
avg_sal
FROM
dept a,
test_view3 ttt
WHERE a.deptno = ttt.deptno;
-- 2.查询员工比所属领导薪资高的部门名、员工名、员工领导编号
-- 2.1查询员工比领导工资高的部门号
CREATE VIEW test_view4
AS
SELECT
a.ename ename,
a.sal sal,
b.ename mgrname,
b.sal msal,
a.`deptno`
FROM
emp a,
emp b
WHERE a.`mgr` = b.`empno`
AND a.`sal` > b.`sal` ;
-- 2.2将第一步查询出来的部门号和部门表进行连表查询
SELECT * FROM dept a JOIN test_view4 b ON a.`deptno` = b.`deptno`;
-- 3:查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，并查询出薪资在前三名的员工信息
-- 3.1 需求1：查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资
CREATE VIEW test_view5
AS
SELECT
a.deptno,
a.dname,
a.loc,
b.empno,
b.ename,
b.sal
FROM
dept a
JOIN emp b
ON a.`deptno` = b.`deptno`
AND YEAR(hiredate) > '2000'
AND a.`loc` = '上海'
JOIN salgrade c
ON grade = 4
AND b.`sal` BETWEEN c.`losal`
AND c.`hisal` ;
SELECT
*
FROM
(SELECT
*,
RANK () OVER (
ORDER BY sal DESC) rn
FROM
test_view5) t
WHERE rn <= 3 ;
存储过程
-- 存储过程 ------
CREATE DATABASE mysql7_procedure;
USE mysql7_procedure;
DELIMITER $$
CREATE PROCEDURE proc01()
BEGIN
SELECT empno,ename FROM emp;
END $$
DELIMITER ;
-- 调用存储过程
CALL proc01();
-- 定义变量
DELIMITER $$
CREATE PROCEDURE proc02()
BEGIN
DECLARE var_name01 VARCHAR(20) DEFAULT 'aaa'; -- 声明/定义变量
SET var_name01 = 'zhangsan'; -- 给变量赋值
SELECT var_name01; -- 输出变量的值
END $$
DELIMITER ;
CALL proc02();
DELIMITER $$
CREATE PROCEDURE proc03()
BEGIN
DECLARE my_ename VARCHAR(20);
SELECT ename INTO my_ename FROM emp WHERE empno = 1001;
SELECT my_ename;
END $$
DELIMITER ;
CALL proc03();
-- 用户变量
DELIMITER $$
CREATE PROCEDURE proc04()
BEGIN
SET @var_nam01 = 'beijing';
SELECT @var_nam01;
END $$
DELIMITER ;
CALL proc04();
SELECT @var_nam01; -- 也可以使用用户变量
-- 系统变量
-- 全局变量
USE mysql7_procedure;
-- 查看全局变量
SHOW GLOBAL VARIABLES;
-- 查看某全局变量
SELECT @@global.auto_increment_increment;
-- 修改全局变量的值
SET GLOBAL sort_buffer_size = 40000;
SET @@global.sort_buffer_size = 33000;
SELECT @@global.sort_buffer_size;
-- 会话变量
-- 查看会话变量
SHOW SESSION VARIABLES;
-- 查看某会话变量
SELECT @session.auto_increment_increment;
-- 修改会话变量的值
SET SESSION sort_buffer_size = 50000;
SET @@session.sort_buffer_size = 60000;
SELECT @@session.sort_buffer_size;
-- ---------传入参数: in -------------------------
-- 封装有参数的存储过程，传入员工编号，查找员工信息
DELIMITER $$
CREATE PROCEDURE proc06(IN param_empno INT)
BEGIN
SELECT * FROM emp WHERE empno = param_empno;
END $$
DELIMITER ;
CALL proc06(1001);
CALL proc06(1002);
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
DELIMITER $$
CREATE PROCEDURE proc07(IN param_dname VARCHAR(50), IN param_sal DECIMAL(7,2))
BEGIN
SELECT * FROM dept a, emp b WHERE a.deptno = b.deptno AND a.dname = param_dname AND b.sal > param_sal;
END $$
CALL proc07('学工部',20000);
-- --------------传出参数:out---------
USE mysql7_procedure;
-- 封装有参数的存储过程，传入员工编号，返回员工名字
DELIMITER $$
CREATE PROCEDURE proc08 (
IN in_empno INT,
OUT out_ename VARCHAR (50),
OUT out_sal DECIMAL (7, 2)
)
BEGIN
SELECT
ename,
sal INTO out_ename,
out_sal
FROM
emp
WHERE empno = in_empno ;
END $$
DELIMITER ;
CALL proc08(1001,@o_ename,@o_sal);
SELECT @o_ename,@o_sal;
SELECT @o_sal;
-- 传入一个数字，传出这个数字的10倍值
DELIMITER $$
CREATE PROCEDURE proc10(INOUT num INT)
BEGIN
SET num = num * 10;
END $$
DELIMITER ;
SET @inout_num = 2;
CALL proc10(@inout_num);吧
SELECT @inout_num;
-- 传入员工名，拼接部门号，传入薪资，求出年薪
DELIMITER $$
CREATE PROCEDURE proc11(INOUT inout_ename VARCHAR(50), INOUT inout_sal INT)
BEGIN
SELECT CONCAT_WS('_',deptno,ename) INTO inout_ename FROM emp WHERE emp.ename = inout_ename;
SET inout_sal = inout_sal * 12;
END $$
DELIMITER ;
SET @inout_ename = '关羽';
SET @inout_sal0 = 3000;
CALL proc11(@inout_ename,@inout_sal);
SELECT @inout_ename, @inout_sal;
-- 存储过程-if
-- 案例1
-- 输入学生的成绩，来判断成绩的级别：
DELIMITER $$
CREATE PROCEDURE proc_12_if(IN score INT)
BEGIN
IF score < 60
THEN
SELECT '不及格';
ELSEIF score >= 60 AND score < 80
THEN
SELECT '及格';
ELSEIF score >= 80 AND score < 90
THEN
SELECT '良好';
ELSEIF score >= 90 AND score <= 100
THEN
SELECT '优秀';
ELSE
SELECT '成绩错误';
END IF;
END $$
DELIMITER ;
DROP PROCEDURE proc_12_if;
SET @score = 100;
CALL proc_12_if(@score);
-- 输入员工的名字，判断工资的情况
DELIMITER $$
CREATE PROCEDURE proc_13_if(IN in_ename VARCHAR(20))
BEGIN
DECLARE var_sal DECIMAL(7,2);
DECLARE result VARCHAR(20);
SELECT sal INTO var_sal FROM emp WHERE ename = in_ename;
IF var_sal < 10000
THEN
SET result = '适用薪资';
ELSEIF var_sal < 20000
THEN
SET result = '转正薪资';
ELSE
SET result = '元老薪资';
END IF;
SELECT result;
END $$
DELIMITER ;
CALL proc_13_if('关羽');
-- ------------------------------
DELIMITER $$
CREATE PROCEDURE proc14_case(IN pay_type INT)
BEGIN
CASE pay_type
WHEN 1 THEN SELECT '微信支付';
WHEN 2 THEN SELECT '支付宝支付';
WHEN 3 THEN SELECT '银行卡支付';
ELSE SELECT '其他方式支付';
END CASE;
END $$
DELIMITER ;
CALL proc14_case(1);
-- while 循环
-- 创建测试表
CREATE TABLE USER(
uid INT PRIMARY KEY,
username VARCHAR(50),
PASSWORD VARCHAR(50)
);
-- 普通循环过程
DELIMITER $$
CREATE PROCEDURE proc16_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
label : WHILE i <= insertCount DO
INSERT INTO USER(uid,username,PASSWORD) VALUES(i,CONCAT('user-',i),'123456');
SET i = i + 1;
END WHILE label;
END $$
DELIMITER ;
CALL proc16_while(10);
SELECT * FROM USER;
-- 循环过程 + leave
DELIMITER $$
CREATE PROCEDURE proc17_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
label : WHILE i <= insertCount DO
INSERT INTO USER(uid,username,PASSWORD) VALUES(i,CONCAT('user-',i),'123456');
IF i = 5 THEN
LEAVE label;
END IF;
SET i = i + 1;
END WHILE label;
END $$
DELIMITER ;
CALL proc17_while(10);
SELECT * FROM USER;
DELETE FROM USER;
-- iterate 相当于C++中的continue 这里代码就不写了 和上面一样需要添加标签
DELIMITER $$
CREATE PROCEDURE proc17_while_iterate(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
label : WHILE i < insertCount DO
SET i = i + 1;
IF i = 5 THEN
ITERATE label;
END IF;
INSERT INTO USER(uid,username,PASSWORD) VALUES(i,CONCAT('user-',i),'123456');
END WHILE label;
END $$
DELIMITER ;
DROP PROCEDURE proc17_while_iterate;
CALL proc17_while_iterate(7);
SELECT * FROM USER;
DELETE FROM USER;
-- 声明游标
-- 打开游标
-- 通过游标获取值
-- 关闭游标
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资，将查询的结果集添加游标
DELIMITER $$
CREATE PROCEDURE proc19_cursor(IN in_dname VARCHAR(50))
BEGIN
-- 定义局部变量
DECLARE var_empno INT;
DECLARE var_ename VARCHAR(50);
DECLARE var_sal DECIMAL(7,2);
-- 声明游标
DECLARE my_cursor CURSOR FOR
SELECT empno,ename,sal
FROM dept a, emp b
WHERE a.deptno = b.deptno AND a.dname = in_dname;
-- 打开游标
OPEN my_cursor;
-- 通过游标获取值
label:LOOP
FETCH my_cursor INTO var_empno, var_ename, var_sal;
SELECT var_empno, var_ename, var_sal;
END LOOP label;
-- 关闭游标
CLOSE my_cursor;
END $$
DELIMITER ;
CALL proc19_cursor('销售部');
-- 在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错
DELIMITER $$
CREATE PROCEDURE proc19_cursor(IN in_dname VARCHAR(50))
BEGIN
-- 定义局部变量
DECLARE var_empno INT;
DECLARE var_ename VARCHAR(50);
DECLARE var_sal DECIMAL(7,2);
-- 定义标记值
DECLARE flag INT DEFAULT 1;
-- 声明游标
DECLARE my_cursor CURSOR FOR
SELECT empno,ename,sal
FROM dept a, emp b
WHERE a.deptno = b.deptno AND a.dname = in_dname;
-- 定义句柄：定义异常的处理方式
/*
1、异常处理完之后程序该怎么证明
continue:继续执行剩余代码
exit:直接终止程序
undo:不知处
2、触发条件
条件码：
1329
条件名：
SQLWARING
NOT FOUND
SQLEXCEPTION
3、异常触发之后执行什么代码
设置flag的值 --->0
*/
DECLARE CONTINUE HANDLER FOR 1329 SET flag = 0;
-- 打开游标
OPEN my_cursor;
-- 通过游标获取值
label:LOOP
FETCH my_cursor INTO var_empno, var_ename, var_sal;
-- 判断flag，如果flag的值为1，则执行，否则不执行
IF flag = 1 THEN
SELECT var_empno, var_ename, var_sal;
ELSE
LEAVE label;
END IF;
END LOOP label;
-- 关闭游标
CLOSE my_cursor;
END $$
DELIMITER ;
CALL proc19_cursor('销售部');
-- 练习-------------------
DELIMITER $$
CREATE PROCEDURE proc22_demo()
BEGIN
DECLARE next_year INT; -- 下一个月的年份
DECLARE next_month INT; -- 下一个月的月份
DECLARE next_month_day INT; -- 下一个月的s日期
DECLARE next_month_str CHAR(2); -- 下一个月的月份字符串
DECLARE next_month_day_str CHAR(2); -- 下一个月的日字符串
-- 处理每天的表名
DECLARE table_name_str CHAR(10);
DECLARE t_index INT DEFAULT 1;
-- 获取下个月的年份
SET next_year = YEAR(DATE_ADD(NOW(),INTERVAL 1 MONTH));
-- 获取下个月是几月
SET next_month = MONTH(DATE_ADD(NOW,INTERVAL 1 MONTH));
-- 下个月最后一天是几号
SET next_month_day = DAYOFMONTH(LAST_DAY(DATE_ADD(NOW,INTERVAL 1 MONTH)));
IF next_month < 10
THEN SET next_
END $$
DELIMITER ;
学习笔记仅供参考-------
1007
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
