MySQL学习笔记【基础篇】

net start 服务名（启动服务）
net stop 服务名（停止服务）

create table 表名(

	列名 列类型,
	列名 列类型，
	。。。
);

DQL（Data Query Language）：数据查询语言
	select 
DML(Data Manipulate Language):数据操作语言
	insert 、update、delete
DDL（Data Define Languge）：数据定义语言
	create、drop、alter
TCL（Transaction Control Language）：事务控制语言
	commit、rollback

select 100+90;如果两个操作数均为数值型，则做加法运算。
select '123' + 90;	若其中一方为字符型，则试图将字符型数值转化成数值型
						如果转换成功，则继续做加法运算；
						否则，则将字符型转换成0
//select '123'+90的结果为213
select 'john'+90;		//结果为90

select null+10;			只要其中一方为null，则结果肯定为null

查询员工号为176的员工的姓名和部门号和年薪：
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct, 0)) AS 年薪 FROM employees;

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1			//注意：如果要显示语句，则需要在语句后面加分号
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

SELECT salary 原始工资,department_id, 
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

case
when 条件1 then 显示的值1或语句1					//注意：如果要显示语句，则需要在语句后面加分号
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

SELECT salary,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

SELECT NAME,boyName 
FROM boys, beauty
WHERE beauty.`boyfriend_id`=boys.id;

SELECT last_name, department_name
FROM employees, departments
WHERE employees.`department_id`=departments.`department_id`;

-- 当emplyees,jobs表中都有job_id字段时，需要指明它来自哪个表，否则会执行错误。
SELECT last_name, employees.job_id, job_title
FROM employees,jobs
WHERE employees.`job_id`=jobs.`job_id`;

-- as可省略
SELECT last_name, e.job_id, job_title
FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;

SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

SELECT city,COUNT(*) 个数
FROM locations l, departments d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;

SELECT department_name,e.manager_id,MIN(salary)
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.`department_name`, d.`manager_id`;

SELECT job_title,COUNT(*)
FROM employees e, jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY j.`job_title`
ORDER BY COUNT(*) DESC;

SELECT last_name,department_name,city
FROM employees e, departments d, locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

SELECT salary,grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;

-- 可以把employees看成两个表e, m，e是员工表，m是上级表。
SELECT e.employee_id ,e.last_name,m.employee_id,m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

select 字段，...
from 表1 别名
【inner|left outer|right outer|cross】join 表2 别名 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE '%e%';

-- 根据要求，查询部门个数>3的城市名和部门个数，也就是说先按照城市名进行分组，然后再用部门个数>3这个条件进行筛选（这就用到了having）

SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

-- ①查询每个部门的员工个数
select count(*),department_name
from employees
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
group by department_name
-- ②在①的结果上选员工数>3的记录，并排序
SELECT department_name 部门名,COUNT(*)
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

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`
ORDER BY department_name DESC;

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

SELECT COUNT(*) 个数,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

SELECT e.last_name 员工名,m.last_name 上级名
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

SELECT e.last_name 员工名,m.last_name 上级名
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

SELECT b.name 女神名
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;		

SELECT b.name 女神名
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

-- 根据要求，查询女神的男朋友信息，如果没有则用NULL填充，从这里可以看出女神表beauty为主表，男神表boys为从表。
--下面使用左外连接 left左面的为主表
SELECT b.id,b.`name`,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3;

-- 从要求中可以推断出locations表为主表，departments表为从表，因为有些部门没有和城市匹配。
-- 下面使用右外连接 right右面的为主表
SELECT city
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;

-- 题目要求查询部门名为指定内容的员工信息，考虑到可能会出现有些部门没有员工，为了查到全，所以这里采用外连接，不采用内连接。部门表departments为主表，员工表employees为从表
-- 下面使用左外连接
SELECT e.*,d.department_name
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE d.`department_name` IN('SAL','IT');

-- ①：查询Abel的工资
SELECT salary
FROM employees 
WHERE last_name='Abel';
-- ②：查询员工的信息，满足salary>①的结果
SELECT *
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees 
	WHERE last_name='Abel'
);

-- ①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
-- ②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
-- ③查询员工的信息，要求job_id=①并且salary>②
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

-- ①查询公司的最低工资
SELECT MIN(salary)
FROM employees;
-- ②查询员工的last_name、job_id和salary,要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

-- ①:查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;
-- ②:查询每个部门的最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id;
-- ③:在②的基础上筛选，满足min(salary)>①
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT salary
	FROM employees
	WHERE department_id = 50
);


-- 上面这个是非法使用，原因是子查询的结果不是单行单列

-- 1.先查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1400 
OR location_id = 1700;
-- 2.查询员工姓名，要求部门号是1列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1400 
	OR location_id = 1700
);

-- 1.查询job_id=IT_PROG部门的任一工资
SELECT DISTINCT salary
FROM employees 
WHERE job_id = 'IT_PROG';
-- 2.查询员工号、姓名、job_id以及salary，并且salary<any(1中的任意一个)
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees 
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
-- 或者这么写
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < (
	SELECT DISTINCT MAX(salary)
	FROM employees 
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees 
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
-- 或者这么写
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < (
	SELECT DISTINCT MIN(salary)
	FROM employees 
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

-- 1.查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
-- 2.查询最高工资
SELECT MAX(salary)
FROM employees;
-- 3.查询员工信息
SELECT * 
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);


-- 用行子查询解决
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id  = d.`department_id`
) 个数
FROM departments d;

SELECT(
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id = e.department_id
	WHERE e.employee_id = 102
) 部门名;

-- 1.先查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
-- 2.连接1的结果集何job_grades表，筛选条件是平均工资在lowest_sal和highest_sal之间。
SELECT ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;


-- 发现from将子查询结果充当一张表，要求必须起别名

SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id = e.`department_id`
);


 -- 或使用in解决
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
);

-- 使用in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
);

-- 使用exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE b.`boyfriend_id`=bo.`id`
);

-- 1.查询Zlotkey的部门
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';

-- 2.查询部门号等于1.的结果的姓名和工资
SELECT last_name, salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'
);

-- 1.查询公司的平均工资
SELECT AVG(salary)
FROM employees;
-- 2.查询工资比1.的结果高的员工的员工号，姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);

-- 首先需要读懂题意，问的是各部门中工资大于本部门平均工资的员工的信息，意思就是筛选处每个部门大于自身部门的平均工资的员工

-- 1.查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees 
GROUP BY department_id;
-- 2.连接1.的结果集和employees表，进行筛选
SELECT employee_id,last_name,salary, e.department_id
FROM employees e
INNER JOIN(
	SELECT AVG(salary) ag,department_id
	FROM employees 
	GROUP BY department_id
)ag_dep
ON e.`department_id`=ag_dep.department_id
WHERE salary > ag_dep.ag;

-- 1.查询姓名中包含字母u的员工的部门号
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';
-- 2.查询部门号=1.的结果集的任意一个员工的员工号和姓名
SELECT last_name,employee_id
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
);

-- 1.查询location_id为1700的部门
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700;
-- 2.查询部门号=1.的结果集中的任意一个的员工号。
SELECT employee_id
FROM employees
WHERE department_id = ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
);
-- 按照这里的题意，any换成in也行

-- 1.查询姓名为K_ing的员工编号
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing';
-- 2.查询哪个员工的manager_id = 1.的结果集的任意一个
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
	SELECT employee_id
	FROM employees
	WHERE last_name = 'K_ing'
);

-- 1.查询最高工资
SELECT MAX(salary)
FROM employees;
-- 2.查询工资=1的结果集的姓、名
SELECT CONCAT(first_name,last_name) "姓名"
FROM employees
WHERE salary=(
	SELECT MAX(salary)
	FROM employees
);

-- 一、查询最低工资
SELECT MIN(salary)
FROM employees;
-- 二、查询last_name,salary，要求salary=一、的结果
SELECT last_name,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

-- 一、查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
-- 二、查询一、结果中的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep;
-- 三、查询哪个部门的平均工资=二、的结果
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) ag_dep
);
-- 四、查询部门信息
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep
	)
);

-- 一、求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
order by avg(salary)
limit 1;
-- 二、查询部门信息
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
);

-- 一、查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
-- 二、求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
order by avg(salary)
limit 1;
-- 三、查询部门信息(这里用到了表子查询)
SELECT d.*
FROM departments d
JOIN (
	SELECT AVG(salary),department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
)ag_dep
ON d.`department_id`=ag_dep.department_id;

-- 一、查询每个job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1;
-- 二、查询job信息
SELECT * 
FROM jobs
WHERE jobs.`job_id` = (
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

-- 一、查询公司的平均工资
SELECT AVG(salary)
FROM employees;
-- 二、查询每个部门的平均工资
SELECT AVG(salary)
FROM employees
GROUP BY department_id;
-- 三、筛选二、的结果集，满足平均工资>一的结果

-- 一、查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees;
-- 二、查询详细信息，满足employee_id=1的结果
SELECT *
FROM employees
WHERE employee_id = ANY(
	SELECT DISTINCT manager_id
	FROM employees
);

-- 一、查询各部门的最高工资
SELECT MAX(salary)
FROM employees
GROUP BY department_id;
-- 二、找一、结果集中最低的那个
SELECT MAX(salary)
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1;
-- 三、查询哪个部门的最高工资等于二、结果，
SELECT MIN(salary),department_id
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

-- 一、找到平均工资最高的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;
-- 二、将employees和departments连接查询，筛选条件是一、的结果
SELECT last_name,d.department_id,email,salary
FROM employees e
INNER JOIN departments d 
ON d.`manager_id` = e.`employee_id`
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit offset,size;

-- offset表示要显示条目的起始索引（起始索引从0开始）
-- size表示要显示的条目个数



--执行顺序：from表先走，再inner join on,再去筛选，再去group by，再去having，再走select，然后order by，最后执行limit

SELECT * FROM employees LIMIT 0,5;

SELECT * FROM employees LIMIT 10,15;

SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;

查询语句1
union
查询语句2
union
...

SELECT * FROM employees WHERE email LIKE '%a%'
UNION 
SELECT * FROM employees WHERE department_id>90;

-- 方式一、
insert into 表名(字段名，...)
values(值1，...);

-- 方式二、
insert into 表名
set 列名=值,列名=值,...

-- 方式一支持插入多行数据，方式二不支持
-- 方式一支持子查询，方式二不支持

-- 修改单表语法：
update 表名 set 字段=新值,字段=新值
【where 条件】

-- 修改多表语法【补充】：
-- sql92语法
update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 连接条件
and 筛选条件
--sql99语法
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件

UPDATE boys bo
INNER JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='114'
WHERE bo.`boyName`='张无忌';

UPDATE boys bo
RIGHT JOIN beauty b 
ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

--方式一：delete语句
-- 单表删除
delete from 表名 【where 筛选条件】
-- 不加where的时候会清空该表

-- 多表删除【补充】
--sql92语法
delete 别名1，别名2
from 表1 别名1，表2 别名2
where 连接条件
and 筛选条件;
--sql99语法
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名
where 筛选条件;

--方式二：truncate语句（不允许加where）
truncate table 表名
--执行它后会清空该表

create database [if not exists]库名;

-- 加IF NOT EXISTS是为了避免当存在那个库时再创建会报错
CREATE DATABASE IF NOT EXISTS books;

ALTER DATABASE books CHARACTER SET utf8;

-- 加if exists是为了避免删除不存在的库报错
drop database [if exists] 库名

DROP DATABASE IF EXISTS books;

create table 表名(
	列名 列的类型【(长度)约束】,
    列名 列的类型【(长度)约束】,
	列名 列的类型【(长度)约束】,
    ...
    列名 列的类型【(长度)约束】
);

-- 加IF NOT EXISTS是为了增强容错性
CREATE TABLE IF NOT EXISTS book(
	id INT,#编号
	bName VARCHAR(20),#图书名
	price DOUBLE,#价格
	authorId INT,#作者
	publishDate DATETIME #出版日期
);

CREATE TABLE author(
	id INT,#编号
	au_name VARCHAR(20),
	nation VARCHAR(10) #国籍
);

alter table 表名 add|drop|modify|change column 字段名【字段类型 约束】;

-- 加IF EXISTS是为了容错性
DROP TABLE IF EXISTS book_author;

-- 创建一个copy表，复制author表的结构
CREATE TABLE copy LIKE author;

CREATE TABLE copy2
SELECT * FROM author;

CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';

CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 1=2;
-- 或
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;

CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED
);

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

--尽管这里写了外键约束，但由于在列级约束中外键约束没有效果，故这里没有效果。
--这个check在mysql不支持
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL,#非空约束
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查约束
	seat INT UNIQUE,#唯一约束
	age INT DEFAULT 18,#默认约束
	majorId INT REFERENCES major(id)#外键约束
);

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

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(genger = '男' OR gender = '女'),#检查约束
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键

);

-- 或者这样写

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	PRIMARY KEY(id),#主键
	UNIQUE(seat),#唯一键
	CHECK(genger = '男' OR gender = '女'),#检查约束
	FOREIGN KEY(majorid) REFERENCES major(id)#外键
);

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	studname VARCHAR(20) NOT NULL,
	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
);

-- 将stuname字段添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

-- 将age字段添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

-- 将id字段添加主键约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
--或者这么写
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

-- 将seat字段添加唯一
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
-- 或者这么写
ALTER TABLE stuinfo ADD UNIQUE(seat);

-- 为majorid字段添加外键约束，与major表中的id字段关联
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

ALTER TABLE stuinfo MODIFY COLUMN age INT;

ALTER TABLE stuinfo DROP PRIMARY KEY;

ALTER TABLE stuinfo DROP INDEX seat;
-- index后面要写什么需要先执行show index from stuinfo 查看对应的Key_name是什么,填到index后面

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
-- foreign key 后面要写什么需要先执行show index from stuinfo 查看对应的Key_name是什么,填到key后面

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,		-- 看AUTO_INCREMENT
	NAME VARCHAR(20)
);

INSERT INTO tab_identity VALUES(NULL,'join');
INSERT INTO tab_identity(NAME) VALUES('tom');

SELECT * FROM tab_identity;

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT,
	NAME VARCHAR(20)
);

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE tab_identity MODIFY COLUMN id INT;

初始时
张三丰 1000
郭襄   1000
update 表 set 张三丰的余额=500 where name='张三丰'
update 表 set 郭襄的余额=1500 where name='郭襄'

-- 用cmd命令窗口(管理员模式)操作

-- 查看当前的隔离级别
select @@tx_isolation;

-- 设置隔离级别
set session transaction isolation level 隔离级别名称;

-- 操作数据库
use 数据库名;

-- 开启事务
SET autocommit = 0;

-- 编写sql语句

-- 结束事务
-- rollback; 写rollback或commit，根据具体情况决定
commit;

SET autocommit = 0;
START TRANSACTION;		#在客户端中一般使用这条，在控制台里不需要

DELETE FROM account WHERE id = 1;
SAVEPOINT a; #设置保存点
DELETE FROM account WHERE id = 3;
ROLLBACK TO a;	#回滚到保存点,这样就导致id=1的数据删除，id=3的数据没有删除

SELECT * FROM account;

-- 以前是这样写
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
查询语句;

-- 创建视图
CREATE VIEW myv1
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 myv1 WHERE last_name LIKE '%a%';

# 创建视图，查看每个部门的平均工资
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`;

#前面已经创建过视图myv2，这里就不需要再创建

# 使用
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

# 创建视图myv3,将包含视图myv2的sql操作再次封装
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`;

-- 方式一
creat or replace view 视图名
as
查询语句;

-- 方式二
alter view 视图名
as 
查询语句;

drop view 视图名,视图名,...;

-- 这里说的查看是指查看它的结构
desc 视图名;

CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees e
WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1;	

CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx, department_id
FROM employees
GROUP BY department_id
HAVING mx > 12000;

SELECT d.*, e.mx
FROM emp_v2 e
INNER JOIN departments d
ON d.`department_id` = e.department_id;

CREATE TABLE bookType(
	id INT PRIMARY KEY,
	NAME VARCHAR(20)
);

CREATE TABLE Book(
	bid INT PRIMARY KEY,
	bname VARCHAR(32) UNIQUE NOT NULL,
	price DOUBLE DEFAULT 10,
	bypeId INT,
	CONSTRAINT fk_book_booktype FOREIGN KEY(bypeId) REFERENCES bookType(id)
);

SET autocommit = 0;
START TRANSACTION;
#先插入主表中数据
INSERT INTO bookType VALUES
(2333, '水浒');
#再插入从表中数据
INSERT INTO Book VALUES
(1, '张飞', 100000, 2333);
COMMIT;

CREATE VIEW myv1
AS
SELECT bname, NAME
FROM book bo
INNER JOIN booktype b 
ON bo.bypeId=b.id
WHERE price > 100;

SELECT * FROM myv1;

ALTER VIEW myv1
AS
SELECT bname, price
FROM book
WHERE price BETWEEN 90 AND 120;

SELECT * FROM myv1;

DROP VIEW myv1;

SHOW GLOBAL VARIABLES;

-- 查看包含char的全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';

-- 查看autocommit的值
SELECT @@global.autocommit;
-- 查看隔离级别
SELECT @@global.tx_isolation;

SET @@global.autocommit=0;

SHOW SESSION VARIABLES;
-- 或者
SHOW VARIABLES;

SHOW SESSION VARIABLES LIKE '%char%';
-- 或者
SHOW VARIABLES LIKE '%char%';

SELECT @@tx_isolation;
-- 或者
SELECT @@session.tx_isolation;

SET @@session.tx_isolation = 'read-uncommitted';
-- 或者
SET SESSION tx_isolation = 'read-committed';

声明
赋值
使用(查看、比较、运算等)

-- 下面三种方式都可以
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;

-- 方式一：通过set或select
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
-- 方式二：通过select into
SELECT 字段 INTO @变量名
FROM 表;

-- 例如：查看employees表中的数量
SELECT COUNT(*) INTO @count
FROM employees;

SELECT @用户变量名;

-- 例如：
SELECT @count;

-- 只声明
DECLARE 变量名 类型;
-- 声明并初始化
DECLARE 变量名 类型 DEFAULT 值;

-- 方式一：通过set或select
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
-- 方式二：通过select into
SELECT 字段 INTO 局部变量名
FROM 表;

SELECT 局部变量名;

SET @m = 1;
SET @n = 2;
SET @sum = @m + @n;
SELECT @sum;

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN 
	存储过程体(一组合法的SQL语句)
END

DELIMITER 结束标记
-- 如 DELIMITER $

CALL 存储过程名(实参列表);

DROP PROCEDURE 存储过程名

DROP PROCEDURE test_pro3;

SHOW CREATE PROCEDURE test_pro3;

-- 注意在cmd命令行窗口管理员模式下进行
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username, `password`) 
	VALUES('join1', '0000'),
	('lily', '0000'),
	('tom', '0000'),
	('zz', '0000'),
	('dd', '0000');
END $

# 调用
CALL myp1() $

DELIMITER $			#确定$符号为结束标记
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN 
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
 
END $

# 调用
CALL myp2('刘岩') $

DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN 
	DECLARE result INT DEFAULT 0; #变量声明并初始化
	
	SELECT COUNT(*) INTO result	#变量赋值
	FROM admin a
	WHERE a.username = username
	AND a.password = PASSWORD;
	
	SELECT IF(result>0, '成功', '失败');	#变量使用
END $
#调用
CALL myp3('john', '1000') $

DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
	
	
END $

SET @bName$		#声明用户变量
#调用
CALL myp4('张飞',@bName)$

SELECT @bName$

DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN 
	SELECT bo.boyName, bo.userCP INTO boyName, userCP
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#调用
CALL myp5('张飞',@bName, @usercp) $
SELECT @bName,@usercp$

DELIMITER $
CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)
BEGIN
	SET a = a * 2;	#这里a和b都是局部变量，设置值时不用加@
	SET b = b * 2;
END $

-- 调用的时候需要先创建两个变量
SET @a = 10$
SET @b = 20$
CALL myp6(@a, @b)$
SELECT @a, @b$

CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginPwd VARCHAR(20))
BEGIN 
	INSERT INTO admin(admin.username, PASSWORD)
	VALUES(username,loginPwd);
END $
#调用
CALL test_pro1('张飞','123123')$

DELIMITER $
CREATE PROCEDURE test_pro2(IN id INT, OUT gname VARCHAR(20), OUT phone VARCHAR(20))
BEGIN 
	SELECT b.`name`, b.`phone` INTO gname, phone
	FROM beauty b
	WHERE b.`id` = id;
END $
#调用
CALL test_pro2(1, @a, @b)$
SELECT @a, @b$

DELIMITER $
CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
BEGIN 
	SELECT DATEDIFF(birth1,birth2) INTO result;
END $

CALL test_pro3('1998-1-1',NOW(),@result)$
SELECT @result $

DELIMITER $
CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
	SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strDate;
END $

CALL test_pro4(NOW(), @str)$
SELECT @str $

DELIMITER $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(50), OUT str VARCHAR(100))
BEGIN 
	SELECT CONCAT(beautyName, ' and ', IFNULL(boyName, 'null')) INTO str
	FROM boys bo
	RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
	WHERE b.name = beautyName;
END $

CALL test_pro5('xx', @str) $
SELECT @str $

DELIMITER $
CREATE PROCEDURE test_pro6(IN size INT, IN startIndex INT)
BEGIN
	SELECT * FROM beauty LIMIT startIndex, size;
END$
CALL test_pro6(3, 5) $

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

SELECT 函数名(实参列表)

DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;	#定义局部变量
	SELECT COUNT(*) INTO c 		#为变量赋值
	FROM employees;
	
	RETURN c;			#返回值
END$

SELECT myf1()$

DELIMITER $
CREATE FUNCTION myf3(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal = 0;		#定义用户变量
	SELECT salary INTO @sal		#赋值
	FROM employees
	WHERE last_name = empName;	
	
	RETURN @sal;				#返回值
END $

SELECT myf3('Olson')$

DELIMITER $
CREATE FUNCTION myf4(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name = deptName;
	RETURN sal;
END $

SELECT myf4('IT')$

SHOW CREATE FUNCTION myf4;

DROP FUNCTION myf4;

SELECT IF(表达式1,表达式2,表达式3);
--执行顺序
如果表达式1成立，则if函数会返回表达式2的值，否则会返回表达式3的值。

-- 情况1
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1
WHEN 要判断的值 THEN 返回的值2
WHEN 要判断的值 THEN 返回的值3
...
ELSE 要返回的值n
END
-- 情况2
CASE
WHEN 要判断的条件1 THEN 返回的值1
WHEN 要判断的条件2 THEN 返回的值2
WHEN 要判断的条件3 THEN 返回的值3
...
ELSE 要返回的值n
END

DELIMITER $
CREATE PROCEDURE test_case(IN score INT)
BEGIN
	CASE
	WHEN score>=90 AND score<=100 THEN SELECT 'A';
	WHEN score>=80 THEN SELECT 'B';
	WHEN score>=60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END $

CALL test_case(88)$

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
【ELSE 语句n;】		-- 这一句可以省略
END IF;

DELIMITER $
CREATE FUNCTION test_if(score INT)RETURNS CHAR(1)
BEGIN
	
	IF score >= 90 AND score <= 100 THEN RETURN 'A';
	ELSEIF score >= 80 THEN RETURN 'B';
	ELSEIF score >= 60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END $

SELECT test_if(88)$

【标签:】WHILE 循环条件  DO
	循环体
END WHILE 【标签】;

-- 如果想要加入循环控制，则需要写标签。

【标签:】loop
	循环体
end loop 【标签】;
-- 如果想要加入循环控制，则需要写标签。
-- 可以用来模拟简单的死循环

【标签:】repeat
	循环体;
until 结束循环的条件
end repeat【标签】;
-- 如果想要加入循环控制，则需要写标签。

DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i <= insertCount DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('hh',i),'adb');
		SET i = i + 1;
	END WHILE;
END $

CALL pro_while1(3)$

DELIMITER $
CREATE PROCEDURE pro_while2(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i <= insertCount DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('hh',i),'adb');
		IF i >= 20 THEN LEAVE a;
		END IF;	
		SET i = i + 1;
	END WHILE a;
END $

CALL pro_while2(32)$

DELIMITER $
CREATE PROCEDURE pro_while3(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i <= insertCount DO
		SET i = i + 1;
		IF MOD(i,2) != 0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('hh',i),'adb');
		
	END WHILE a;
END $

CALL pro_while3(32)$

-- 创建表
CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
);
-- 插入数据事务
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;		#定义一个循环变量i,表示插入次数
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT DEFAULT 1;	#代表起始索引长度
	DECLARE len INT DEFAULT 1;		#代表截取的字符的长度
	WHILE i <= insertCount DO
		SET len = FLOOR(RAND()*(20-startIndex+1)+1);		#产生一个随机的整数，代表截取长度，1- (26-startIndex+1)
		SET startIndex = FLOOR(RAND()*26+1);			#产生一个随机的整数，代表起始索引1-26
		INSERT INTO stringcontent(content)VALUES(SUBSTR(str,startIndex,len));
		SET i = i + 1;			#循环变量更新
	END WHILE;

END $

CALL test_randstr_insert(14)$