MySql

1.MySql安装：

https://ceshiren.com/t/topic/16070

2.常用命令行操作：

# 开启mysql服务
net start mysql

# 登录：mysql -h主机IP -u用户名 -p密码
mysql -uroot -p

# 修改密码：alter user 'root'@'localhost' identified by '密码';
alter user 'root'@'localhost' identified by '123';

# 退出
exit

# 关闭mysql服务
net stop mysql

3.DDL数据库操作

-- 创建最基本的数据库
CREATE DATABASE test_db;

-- 创建 test_db2 数据库，并设置字符集为utf8
CREATE DATABASE test_db2 CHARACTER SET utf8;

-- 创建 test_db3 数据库，先判断，如果不存在再创建（*推荐*）
CREATE DATABASE IF NOT EXISTS test_db3 CHARACTER SET utf8; 

-- 查看当前所有数据库alter
SHOW DATABASES;

-- 选择数据库 test_db
USE test_db;

-- 查看 test_db 数据库的定义信息
SHOW CREATE DATABASE test_db;

-- 修改数据库 test_db 的字符集为utf8
ALTER DATABASE test_db CHARACTER SET utf8;

-- 删除 test_db3 数据库
DROP DATABASE test_db3;

-- 如果 test_db2 数据库存在，再删除（*推荐*）
DROP DATABASE IF EXISTS test_db2;

4.DDL数据库表操作

-- 选择 test_db 为当前数据库
USE test_db;



-- 新增数据库表
-- 创建学员表
CREATE TABLE student(
	id INT,
    name VARCHAR(20)
);

-- 复制一个与 student 表相同结构的 s2 表
CREATE TABLE s2 LIKE student;



-- 查看数据库表
-- 查看当前数据库中的所有表
SHOW TABLES;

-- 查看 student 表的结构
DESC student;

-- 查看 student 表中 name 列的信息
DESC student name;



-- 修改数据库表
-- 向 student 表添加新的列 score，且不为空
ALTER TABLE student ADD score VARCHAR(20) NOT NULL;

-- 修改 student 表中的 score 列的数据类型，数据可为空
ALTER TABLE student MODIFY score INT;

-- 修改 student 表中 name 列名为stu_name，并且指定列的默认值为 student_name
ALTER TABLE student CHANGE COLUMN name stu_name VARCHAR(30) DEFAULT 'student_name';

-- 删除 student 表中的 email 列
ALTER TABLE student DROP email;

-- 查看表名
SHOW TABLES;

-- 修改 student 表改名为 stu 表
ALTER TABLE student RENAME AS stu;

-- 修改 stu 表改名为 stu_table
RENAME TABLE stu TO stu_table;



-- 删除数据库表
-- 直接删除 s2 表
DROP TABLE s2;

-- 先判断，如果存在在删除 student 表（*推荐*）
DROP TABLE IF EXISTS student;

5.DML表数据操作

-- 选择 test_db 数据库
USE test_db;

-- 创建 user 表
CREATE TABLE user(
	id INT,
    name VARCHAR(20),
    age INT,
    sex CHAR(1),
    address VARCHAR(50)
);



-- 插入表数据
-- 插入一条完整的数据，写出全部的列名
INSERT INTO user(id, name, age, sex, address) VALUES(1, '高生辰', 18, '男', '深圳');

-- 插入一条完整的数据，不写列名
INSERT INTO user VALUES(2, '小美美', 16, '女', '湖南');

-- 插入表中的一行中的某几列的值
INSERT INTO user(id, name, address) VALUES(3, '叶叶', '深圳');

-- 一次插入多条数据
INSERT INTO user(id, name, address) VALUES(4, '高高', '深圳'),(5, '文文' , '武汉'),(6, '包包' , '北京');



-- 修改表数据
-- 修改 id 为 2 的信息：地址改为长沙
UPDATE user SET address='长沙' WHERE id=2;

-- 一次修改多个列的值，修改 id 为 4 的信息：年龄改为30， 性别改为男
UPDATE user SET age=30, sex='男' WHERE id=4;



-- 删除表数据
-- 删除 user 表中 id 为 6的数据
DELETE FROM user WHERE id=6;

-- 删除 student 表中所有的数据（不推荐，删除表结构和所有数据）
DELETE FROM student;

-- 删除 stu_table 表中所有的数据（仅删除表中所有数据，表结构仍然存在）
TRUNCATE TABLE stu_table;

6.DQL表查询操作

/*基础查询语法
SELECT DISTINCT <列名>
FROM <表名>
WHERE <查询条件表达式>
GROUP BY <分组的列名>
HAVING <分组后的查询条件表达式>
ORDER BY <排序的列名> [ASC / DESC]
LIMIT [开始的行数], <查询记录的条数>
*/

/*SQL语句执行顺序
原始数据库-->FROM 子句-->WHERE 子句-->GROUP BY 子句-->HAVING 子句-->SELECT-->DISTINCT-->ORDER BY 子句-->LIMIT-->最终结果
*/

-- 选择数据库
USE employees;

-- 查询部门表中的所有信息【单表查询】
SELECT * FROM departments;

-- 查询部门表中的部门名称【字段查询】
SELECT dept_name FROM departments;

-- 查询员工信息，并将列名改为中文【取别名】
SELECT 
	emp_no AS '员工编号', 
    last_name AS '名', 
    first_name AS '姓', 
    gender AS '性别', 
    hire_date AS '入职日期' 
FROM 
	employees emp;
    
-- 去掉重复的职级信息【DISTINCT去重】
SELECT DISTINCT title FROM titles;

-- 所有员工的工资+1000元进行显示【运算查询】
SELECT emp_no, salary + 1000 FROM salaries;



-- 查询出生日期晚于 1965-01-01 的员工编号、姓名和生日
SELECT 
    emp_no, first_name, last_name, birth_date
FROM
    employees
WHERE
    birth_date > '1965-01-01';

-- 查询年薪介于 70000 到 70003 之间的员工编号和年薪【BETWEEN ... AND ...】
SELECT 
    emp_no, salary
FROM
    salaries
WHERE
    salary BETWEEN 70000 AND 70003;
    
-- 查询入职日期为 1995-01-27 和 1995-03-20 日的员工信息【IN】
SELECT 
    *
FROM
    employees
WHERE
    hire_date IN ('1995-01-27' , '1995-03-20');

-- 查询学生表中年龄为NULL的学生信息【NULL】
USE test_db;
SELECT * FROM user WHERE age IS NULL;

-- 查询名字为 Lillian 且姓氏为 Haddadi 的员工信息【AND &&】
SELECT * FROM employees WHERE first_name='Lillian' && last_name='Haddadi';

-- 查询名字为 Lillian 或姓氏为 Terkki 的员工信息【OR ||】
SELECT * FROM employees WHERE first_name='Lillian' OR last_name='Terkki';

-- 查询名字为 Lillian 且性别不是女的员工信息【NOT】
SELECT * FROM employees WHERE first_name='Lillian' AND NOT gender='F';

-- 查询名字中包含 fai 的员工信息【% 匹配任意多个字符】
SELECT * FROM employees WHERE first_name LIKE '%fai%';

-- 查询名字中 fa 开头的名字长度为 3 为的员工信息【_ 匹配一个字符】
SELECT * FROM employees WHERE first_name LIKE 'fa_';



-- 使用 salary 字段，对 salaries 表数据进行升序排序【ORDER BY...ASC默认】
SELECT * FROM salaries ORDER BY salary;

-- 使用 salary 字段，对 salaries 表数据进行降序排序【ORDER BY...DESC】
SELECT * FROM salaries ORDER BY salary DESC;

-- 查询员工的编号和入职日期，按照员工入职日期从晚到早排序
SELECT 
    emp_no, hire_date
FROM
    employees
ORDER BY hire_date DESC;

-- 在入职日期降序排序的基础上，再使用 emp_no 降序排序【组合排序】
SELECT 
    emp_no, hire_date
FROM
    employees
ORDER BY hire_date DESC, emp_no DESC;



-- 查询职级名称为 Senior Engineer 的员工数量【COUNT()】
SELECT COUNT(title) FROM titles WHERE title='Senior Engineer';

-- 查询员工编号为 10002 的员工的最高年薪【MAX()】
SELECT MAX(salary) FROM salaries WHERE emp_no=10002;

-- 查询员工编号为 10002 的员工的最低年薪【MIN()】
SELECT MIN(salary) FROM salaries WHERE emp_no=10002;

-- 查询员工编号为 10002 的员工的薪水总和【SUM()】
SELECT SUM(salary) FROM salaries WHERE emp_no=10002;

-- 查询员工编号为 10002 的员工的平均年薪【AVG()】
SELECT AVG(salary) FROM salaries WHERE emp_no=10002;



-- 查询每个员工的薪资和【GROUP BY】
SELECT emp_no, SUM(salary) FROM salaries GROUP BY emp_no;

-- 查询员工编号小于 10010 的，薪资和小于 400000 的员工的薪资和【GROUP BY...HAVING...】
SELECT 
    emp_no, SUM(salary)
FROM
    salaries
WHERE
    emp_no < 10010
GROUP BY emp_no
HAVING SUM(salary) < 400000;



-- 展示前 10 条员工的信息【LIMIT】
SELECT * FROM employees LIMIT 0, 10;
SELECT * FROM employees LIMIT 10;
SELECT * FROM employees LIMIT 10 OFFSET 0;

-- 显示年薪从高到低排序，第 15 位到第 20 位员工的编号和年薪
SELECT emp_no, salary FROM salaries ORDER BY salary DESC LIMIT 14, 6;
SELECT emp_no, salary FROM salaries ORDER BY salary DESC LIMIT 6 OFFSET 14;

7.SQL约束

-- 选择 test_db 数据库
USE test_db;

-- 创建一个带主键的表
CREATE TABLE emp1(
	eid INT PRIMARY KEY,
    ename VARCHAR(20),
    sex CHAR(1)
);
-- 查看表结构
DESC emp1;

-- 给存在的表添加主键
CREATE TABLE emp2(
	eid INT,
    ename VARCHAR(20),
    sex CHAR(1)
);
-- 通过 DDL 语句添加主键
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

-- 创建主键自增的表
CREATE TABLE emp3(
	eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)
);

-- 创建主键自增的表，指定自增起始值
CREATE TABLE emp4(
	eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)
)AUTO_INCREMENT=100;

-- 删除 emp4 中的数据
DELETE FROM emp4;       # 删除表中的数据，对自增没有影响，会按照原来的顺序继续增加
TRUNCATE TABLE emp4;    # 删除旧表，重新创建新表，自增是从1开始

-- 删除 emp2 中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;

-- 创建有非空约束的表
CREATE TABLE emp5(
	eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20) NOT NULL,
    sex CHAR(1)
);

-- 创建带有唯一约束的表
CREATE TABLE emp6(
	eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20) UNIQUE,
    sex CHAR(1)
);

-- 创建带有默认值的表
CREATE TABLE emp7(
	eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)  DEFAULT '女'
);


-- 创建部门信息表（主表）
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(20),
    dept_manager VARCHAR(20),
    dept_location VARCHAR(20)
);
INSERT INTO dept VALUES(1, '开发部', '高生辰', '深圳');
INSERT INTO dept VALUES(2, '测试部', '周莹莹', '上海');
INSERT INTO dept VALUES(3, '运营部', '小菜', '武汉');

-- 创建员工信息表（从表）
CREATE TABLE employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    gender CHAR(1),
    dept_id INT,
    salary INT,
    -- 添加外键约束
    CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
    -- 设置允许级联删除
    ON DELETE CASCADE
);

8.多表查询

-- 查询出公司所有员工信息及对应的部门信息（笛卡尔积）
SELECT * FROM dept, employee WHERE id=dept_id;

-- 筛选出运营部的员工id，姓名及所在城市(内连接)
-- 隐式内连接
SELECT eid, ename, dept_location FROM dept, employee WHERE id=dept_id AND dept_name='运营部';     
-- 显示内连接    
SELECT eid, ename, dept_location FROM dept INNER JOIN employee ON id=dept_id AND dept_name='运营部';  



-- 公司新成立人力资源部，还未招聘员工，请使用左连接查询方式查询出公司所有部门员工的员工号，姓名，性别，以及他们所在的部门名称和城市(LEFT JOIN:显示左表的数据，右表中没有的项，显示为空)
SELECT 
    eid, ename, gender, dept_name, dept_location
FROM
    dept
        LEFT JOIN
    employee ON id = dept_id;

-- 右连接的方式查询出所有员工信息以及他们所在的部门名称和城市(RIGHT JOIN:显示右表的数据，左表中没有的项，显示为空)
SELECT 
    eid, ename, age, gender, salary, dept_name, dept_location
FROM
    dept
        RIGHT JOIN
    employee ON id = dept_id;

9.子查询

-- 计算出各部门性别为男性的员工人数
SELECT dept_name, COUNT(eid)
FROM (SELECT * FROM employee WHERE gender = '男') man 
INNER JOIN dept ON id = dept_id
GROUP BY dept_name;

-- 查询出在/不在深圳地区的所有员工信息（IN/NOT IN）
SELECT * FROM employee WHERE dept_id IN (SELECT id FROM dept WHERE dept_location='深圳');
SELECT * FROM employee WHERE dept_id NOT IN (SELECT id FROM dept WHERE dept_location='深圳');

-- 查询出薪资大于公司平均薪资的员工 id ，姓名及薪资
SELECT AVG(salary) FROM employee;
SELECT eid, ename, salary FROM employee WHERE salary> (SELECT AVG(salary) FROM employee);

-- 查询出销售额超过全公司平均销售额的部门
WITH temp_dept AS (
	SELECT department.dept_id, city, manager, SUM(volume) total_volume
	FROM department INNER JOIN sales_list ON department.dept_id = sales_list.dept_id
	GROUP BY dept_id ORDER BY total_volume DESC)
SELECT dept_id, city, manager, total_volume 
FROM temp_dept 
WHERE total_volume>(SELECT AVG(total_volume) FROM temp_dept);

-- 使用试图来简化练习
CREATE VIEW temp_dept AS (
	SELECT department.dept_id, city, manager, SUM(volume) total_volume
	FROM department INNER JOIN sales_list ON department.dept_id = sales_list.dept_id
	GROUP BY dept_id ORDER BY total_volume DESC
);
SELECT * FROM temp_dept WHERE total_volume>(SELECT AVG(total_volume) FROM temp_dept);