MySQL 命令行基础

CREATE TABLE person(
		id_card  VARCHAR(20),
		name VARCHAR(20),
		year_ INT,
		month_ INT,
		day_ INT,
		gender VARCHAR(6),
		email VARCHAR(69)
); 

INSERT INTO person(id_card, name, year_, month_, day_, gender, email) VALUES ('36232919800620', 'Douma', 1980, 6, 20, 'MALE',
'douma_twq@163.com');

类型

数据类型	占用字节	有符号范围	无符号范围
整型
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT	4	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT	8	-2^63 ~ 2^63-1	0 ~ 2^64-1
小数类型
FLOAT(M, D)	4
DOUBLE(M,D)	8
DECIMAL(M,D)	最多65个数字
字符串类型
CHAR(m)	定义一个长度为m字节的字符串
VARCHAR(m)	定义一个长度可变的字符串
时间类型
DATE	3	YYYY-MM-DD	日期 年月日
TIME	3	HH:mm:ss	时间 时分秒
YEAR	1	YYYY	4位数的年份
DATATIME	8	YYYY-MM-DD HH:mm:ss	日期时间结合体
TIMESTAMP	4	数字	1970-01-01 00:00:00 开始的秒数

完整性约束

1. 非空约束

要求一个字段的值不能为空

CREATE TABLE member (
		mid_  INT  UNSIGHED,
		name VARCHAR(50) NOT NULL,
);

1. 唯一约束

要求一个字段，的值不会被重复插入

DROP TABLE IF EXISTS member;
CREATE TABLE member (
			mid_  INT UNSIGNED,
			name VARCHAR(50) NOT NULL DEFAULT 'test',
			email VARCHAR(50) UNIQUE
);

INSERT INTO member(mid_, name, email) VALUES (1, 'codfish', 'codfish@gmail.com');
INSERT INTO member(mid_, name, email) VALUES (2, 'catfish', 'codfish@gmail.com');
-- 因为字段重复报错

1. 主键约束

必须非空，且必须唯一

DROP TABLE IF EXISTS member;
CREATE TABLE member (
			mid_ INT UNSIGNED PRIMARY KEY ,
			name VARCHAR(50) NOT NULL DEFAULT 'test',
			email VARCHAR(50) UNIQUE
);

INSERT INTO member(mid_, name, email) VALUES (1, 'codfish', 'codfish@gmail.com');
INSERT INTO member(mid_, name, email) VALUES (1, 'catfish', 'codfish@gmail.com');

1. 检查约束

对某个字段的值进行区间检查

DROP TABLE IF EXISTS member;
CREATE TABLE member (
			mid_ INT UNSIGNED PRIMARY KEY ,
			name VARCHAR(50) NOT NULL DEFAULT 'test',
			email VARCHAR(50) UNIQUE,
			age  SMALLINT UNSIGNED CHECK(age > 0 AND age < 200)
);

INSERT INTO member(mid_, name, email, age) VALUES (1, 'codfish', 'codfish@gmail.com', 250);
-- 违反检查约束

基于枚举的属性检查

DROP TABLE IF EXISTS member;
CREATE TABLE member (
			mid_ INT UNSIGNED PRIMARY KEY ,
			name VARCHAR(50) NOT NULL DEFAULT 'test',
			email VARCHAR(50) UNIQUE,
			age  SMALLINT UNSIGNED CHECK(age > 0 AND age < 200),
			gender ENUM('MALE','FEMALE')
);

INSERT INTO member(mid_, name, email, age) VALUES (1, 'codfish', 'codfish@gmail.com', 250,'codfish');
-- 违反检查约束

1. 外键约束

CREATE TABLE student(
			sid INT UNSIGNED,
			name VARCHAR(40) NOT NULL,
			PRIMARY KEY(sid)
);

CREATE TABLE book(
			bid INT UNSIGNED,
			title VARCHAR(50) NOT NULL,
			sid INT UNSIGNED, 
			CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES student(sid)  --设置外键关联父表
);

如果 父表中的某一行数据有对应的子数据，那么必须先删除子表中的数据才可以删除父表中的数据

假设删除父表，必须先删除子表。

SQL 分类

1. DDL 数据定义语音

CREATE DATABASE codfish;
DROP DATABASE codfish;
CREATE TABLE [table_name] (字段名 数据类型 [约束],);
ALTER TABLE [table_name] ADD (age TINYINT UNSIGNED);
ALTER TABLE [table_name] RENAME COLUMN [column_name] TO [new_column_name];
ALTER TABLE [table_name] MODIFY [column_name] [type]
ALTER TABLE [table_name] DROP [column_name]

CREATE TABLE [table_name] AS 子查询;
CREATE TABLE [table_name] AS SELECT * FROM emp WHERE 1 <> 1;

1. DML 数据操作语言

INSERT INTO [table_name](字段1,字段2, ....) VALUES (值1, 值2, 值3, ...);

DELETE FROM table_name WHERE [statement];

UPDATE table_name SET 字段1 = 新的值 WHERE [statement]; 

SET @avg_sal = 0 ; -- 设置变量
SELECT AVG(sal) INTO @avg_sal FROM  new_emp ;
UPDATE new_emp SET sal = sal * 1.2 WHERE sal < @avg_sal

1. DCL 数据控制语言

CREATE USER 'root'@'192.168.126.1'  IDENTIFIED BY '123456';

CREATE USER 'codfish'@'%' IDENTIFIED BY '123456';

SELECT * FROM mysql.user;  -- 查询用户权限信息

GRANT ALL PRIVILEGES ON *.* TO 'codifsh'@'%'  WITH GRANT OPTION

REVOKE ALL PRIVILEGES  ON *.* TO 'codfish'@'%' ;

GRANT ALL PRIVILEGES ON codfish.* TO 'codfish'@'%' WITH GRANT OPTION

SELECT * FROM mysql.db WHERE user = 'codfish'\G;

GRANT ALL PRIVILEGES ON codfish.t1 TO 'codfish'@'%' WITH GRANT OPTION

GRANT SELECT(c), INSERT(c,d) ON douma.t2 TO 'douma'@'%' WITH GRANT OPTION

1. DQL 数据查询语言

使用的表结构

mysql> desc dept
+--------+-------------------+------+-----+---------+-------+
| Field  | Type              | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| deptno | smallint unsigned | NO   | PRI | NULL    |       |
| dname  | varchar(14)       | NO   |     | NULL    |       |
| loc    | varchar(13)       | YES  |     | NULL    |       |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> desc emp
+----------+-------------------+------+-----+---------+-------+
| Field    | Type              | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| empno    | int unsigned      | NO   | PRI | NULL    |       |
| ename    | varchar(10)       | NO   |     | NULL    |       |
| job      | varchar(9)        | YES  |     | NULL    |       |
| mgr      | int unsigned      | YES  |     | NULL    |       |
| hiredate | date              | YES  |     | NULL    |       |
| sal      | decimal(7,2)      | YES  |     | NULL    |       |
| comm     | decimal(7,2)      | YES  |     | NULL    |       |
| deptno   | smallint unsigned | YES  |     | NULL    |       |
+----------+-------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> desc salgrade;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| grade | smallint unsigned | YES  |     | NULL    |       |
| losal | int unsigned      | YES  |     | NULL    |       |
| hisal | int unsigned      | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+

SELECT * FROM emp;
-- 先执行FROM 子句
-- 再执行 SELECT * 子句
SELECT empno, ename  FROM emp;
-- 指定字段查询
SELECT DISTINCT job FROM emp ;
-- 数据去重,按照条目去重
SELECT empno, ename, sal * 12, sal / 30 FROM emp;
SELECT [DISTINCT] * | 字段名 [[AS] 别名], 字段名[[AS] 别名] FROM 表名 [别名] [WHERE 条件]

SELECT * FROM emp WHERE job = 'CLERK';
SELECT * FROM emp WHERE job != 'CLERK';
SELECT * FROM emp WHERE job <> 'CLERK';
-- 条件查询
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000
-- 范围查询

SELECT * FROM emp WHERE job = 'SALESMAN' AND sal > 1200 ;
SELECT * FROM emp WHERE deptno = 10 OR deptno = 20 ;
SELECT * FROM emp WHERE deptno [NOT] IN (10,20);
-- 组合查询

SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK');

SELECT * FROM emp WHERE comm IS NULL ;
SELECT * FROM emp WHERE comm IS NOT NULL ;
-- 非空判断

SELECT * FROM emp WHERE ename LIKE '%F%'; 
SELECT * FROM emp WHERE ename NOT LIKE '%M%';
SELECT * FROM emp WHERE ename LIKE '______%';

SELECT * FROM emp WHERE ename (job != 'MANAGER' AND job != 'CLERK') AND (ename LIKE '%S%' OR ename LIKE '%K%')

SELECT * FROM emp ORDER BY sal DESC;
-- 降序排列
SELECT * FROM emp ORDER BY sal ASC
-- 升序排列
SELECT ename, LOWER(ename) FROM emp ;
-- 单行函数
-- 字符串函数： 操作字符串的函数，输入是字符串的函数, VARCHAR类型,CHAR类型
UPPER -- 字符转换为大写
RELACE(ename, 'A', '_') -- 字段替换
LENGTH(ename) -- 计算字符按长度
SUBSTR(ename, 1, 3) -- 截取字符串 字段名 | 字符串， 第几个字符开始，截取长度
INSTR('codfish, show you code', 'code') -- 查询目标字段的位置，查询失败返回0
RPAD('codfish', 10, '*') --如果不够目标长度，使用对应提示符填充
LPAD -- 同上，从左侧补充
TRIM -- 去除空格
LTRIM -- 去除左侧空格
RTRIM -- 去除右侧空格
-- 数值函数： 输入是数值的函数,INT, DECIMAL, FLOAT, DOUBLE类型
ROUND(789.655) -- 保留特定位数,可以向整数保留, 四舍五入
TRUNCATE() -- 向下取
-- 日期函数： 输入是日期的函数,DATE 类型
CURRENT_DATE -- 获取当前日期
CURRENT_TIME -- 获取当前时间
CURRENT_TIMESTAMP -- 获取时间戳
NOW() 
DATE_ADD(CURRENT_DATE , INTERVAL 3 DAY);
DATE_ADD(CURRENT_DATE , INTERVAL 1 YEAR);
DATE_SUB -- 减去
DATE_SUB(CURRENT_DATE , INTERVAL '-1 10' YEAR_DAY);
TO_DAYS  -- 查询从unix到现在的天数
LAST_DAY --获取当前日期月份的最后一天
TIMESTAMPDIFF(YEAR, hiredate , CURRENT_DATE) hire_years -- 计算当前日期与雇佣日期之间的年数
-- 转换函数： 字符串, 数值, 日期之间的转换的函数
DATE_FORMAT -- 日期转换成字符串
STR_TO_DATE -- 字符串转换为日期
UNIX_TIMESTAMP -- 时间转换为unix时间
FROM_UNIXTIME -- 从unix时间转换为时间
CAST(expr AS type) -- 将表达式转换为目标类型
-- 通用函数： 数据库特有的一些函数
IFNULL(字段,0) -- 判断目标值为空，则返回参数
IF(statement, true_return , false_return) -- 根据判断结果返回值

CASE .... WHEN .... THEN .... END ... 分支处理

复杂查询

消除笛卡尔积

通过条件匹配，消除多表查询过程中的非必要膨胀数据

内连接

左表中没有的数据在连接查询结果中不会出现

右表中没有的数据在连接查询结果中也不会出现

只有左表和右表中都存在的数据才会显示到连接查询的结果中

SELECT e.name, e.job, e.sal, d.dname, 

外连接

左连接/右连接

-- 需求： 查询出在1981念雇佣的全部雇员的编号，姓名，雇佣日期，工作，领导姓名，雇员月工资，
-- 雇员年工资，雇员工资等级，部门编号，部门名称，部门位置，并且要求这些雇员的月基本工资在
-- 1500~3500之间；将最后的结构按照年工资的降序排列，如果年工资相等，则按照工作时间进行排序

-- 1. 需要查询的表：
--     雇员的编号，姓名，雇佣日期（按照年/月/日显示），工作，雇员月工资，雇员年工资（基本工资+奖金）:emp
--     领导姓名： emp
--     部门编号，部门名称，部门位置：dept
--     雇员工资等级： salgrade
-- 2. 确定关联字段：
--     emp 和 emp表： emp.mgr = emp.empno
--     emp 和 dept表： emp.deptno = dept.deptno
--     emp 和 salgrade： emp.sal  BETWEEN salgrade.losal AND salgrade.hisal

-- 步骤一： 在1981年雇佣的全部雇员的编号，姓名，雇佣日期，工作，雇员月工资...
-- 要求这些雇员的月基本工资在1500~3500 元之间
-- 两个条件： YEAR(hiredate) = '1981' AND sal BETWEEN 1500 AND 3500
SELECT
		e.empno,
		e.ename,
		DATE_FORMAT(e.hiredate, '%Y/%m/%d') hiredate ,
		e.job,
		e.sal,
		(e.sal * 12 + IFNULL(e.comm,0)) year_sal
FROM emp e 
WHERE YEAR(e.hiredate) = '1981' AND e.sal BETWEEN 1500 AND 3500;

-- 步骤二： 关联emp表， 查询领导姓名
SELECT
		e.empno,
		e.ename,
		DATE_FORMAT(e.hiredate, '%Y/%m/%d') hiredate ,
		e.job,
		e.sal,
		(e.sal * 12 + IFNULL(e.comm,0)) year_sal 
		m.ename manager_name
FROM  emp e JOIN emp m ON e.mgr = m.empno
WHERE YEAR(e.hiredate) = '1981' AND e.sal BETWEEN 1500 AND 3500;

-- 步骤三； 关联dept表，查询部门编号，部门名称，部门位置
SELECT
		e.empno,
		e.ename,
		DATE_FORMAT(e.hiredate, '%Y/%m/%d') hiredate ,
		e.job,
		e.sal,
		(e.sal * 12 + IFNULL(e.comm,0)) year_sal 
		m.ename manager_name
FROM  emp e JOIN emp m ON e.mgr = m.empno
JOIN dept d ON e.deptno = d.deptno
WHERE YEAR(e.hiredate) = '1981' AND e.sal BETWEEN 1500 AND 3500 ; 

-- 步骤四： 关联 salgrade表，查询雇员等级
SELECT
		e.empno,
		e.ename,
		DATE_FORMAT(e.hiredate, '%Y/%m/%d') hiredate ,
		e.job,
		e.sal,
		(e.sal * 12 + IFNULL(e.comm,0)) year_sal 
		m.ename manager_name
FROM  emp e JOIN emp m ON e.mgr = m.empno
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE YEAR(e.hiredate) = '1981' AND e.sal BETWEEN 1500 AND 3500 ;

-- 步骤五： 排序 
SELECT
		e.empno,
		e.ename,
		DATE_FORMAT(e.hiredate, '%Y/%m/%d') hiredate,
		e.job,
		e.sal,
		(e.sal * 12 + IFNULL(e.comm,0)) year_sal,
		m.ename manager_name
FROM  emp e JOIN emp m ON e.mgr = m.empno
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE YEAR(e.hiredate) = '1981' AND e.sal BETWEEN 1500 AND 3500
ORDER BY year_sal DESC , hiredate ASC;

-- 语法
SELECT [DISTINCT] * | 字段名 [[AS] 别名], 字段名[[AS] 别名], ...
FROM 表名 [别名]
[[INNER] JOIN 表名 [别名]  USING(关联字段)]
[[INNER] JOIN 表名 [别名]  ON 关联条件]
[LEFT | RIGHT | FULL [OUTER] JOIN 表名 [别名] ON(关联条件)]
[WHERE 条件1 AND | OR 条件2.... ]
[ORDER BY 字段名 [DESC | ASC], 字段名 [DESC | ASC]]

-- 执行顺序
-- FROM ...
-- WHERE 过滤
-- SELECT 查询字段值
-- ORDER BY 排序

聚合函数

SELECT COUNT(*) FROM emp ;
AVG() -- 取平均值
SUM() -- 求和
MAX() -- 最大值
MIN() -- 最小值
COUNT() -- 查询总记录数
SELECT COUNT(DISTINCT job) FROM emp;  --去重

GROUP BY

分组聚合

如果没有分组的话。在SELECT子句后面不能同时出现字段和聚合函数

在有分组的情况下，在SELECT子句后面不能出现分组字段之外的字段

SELECT deptno , MAX(sal), MIN(sal) FROM emp GROUP BY deptno ;

HAVING

用于过滤分组后的数据

SELECT job, ROUND(AVG(sal) ,2) avg_salary, COUNT(*) cnt FROM emp GROUP BY job HAVING AVG(sal) > 2000;

处理顺序

-- 执行顺序
-- FROM ...
-- WHERE 过滤
-- GROUP BY 字段名
-- HAVING 过滤条件
-- SELECT 查询字段值
-- ORDER BY 排序

子查询

SELECT * FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);

子查询位置

1. WHERE 子句

SELECT * FROM emp WHERE (job, sal) = (SELECT job , mgr FROM emp WHERE ename='SCOTT') AND ename != 'SCOTT';

SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno ); -- 单行多列

SELECT * FROM emp WHERE sal = ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);

SELECT * FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE job = 'MANAGER');

1. HAVING 子句

select deptno, COUNT(*), AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > (SELECT AVG(sal) FROM emp);

1. FROM 子句

SELECT d.deptno, d.dname, d.loc , temp.cnt, temp.avg_salary 
FROM dept d JOIN (SELECT deptno, CUONT(empno) cnt, ROUND(AVG(sal), 2) avg_salary
									FROM  emp
									GROUP BY deptno) temp USING(deptno);

视图

视图可以理解为一个”窗口”，通过这个窗口可以看到底层表中的特定数据。当你查询视图时，MySQL会执行视图定义中的SELECT语句，并返回相应的结果。

主要特点

数据安全性：视图可以隐藏敏感字段，只向用户展示需要的数据列，提供了一层数据访问控制。

查询简化：对于复杂的多表连接查询，可以创建视图来简化操作，用户只需要查询视图即可。逻辑独立性：当底层表结构发生变化时，视图可以保持接口的稳定性，减少对应用程序的影响。创建和使用示例

CREATE VIEW employee_info AS
SELECT emp_id, name, department, salary 
FROM employees 
WHERE status = 'active';

-- 查询视图
SELECT * FROM employee_info WHERE department = 'IT';

-- 更新视图（会影响底层表）
UPDATE employee_info SET salary = 8000 WHERE emp_id = 101;

存储过程

存储过程是预编译的SQL语句集合，存储在数据库中可重复调用的程序单元，类似于数据库层面的函数。

性能优势：一次编译多次执行，减少网络开销和解析时间。

安全性：有效防止SQL注入，提供精细的权限控制。

可维护性：业务逻辑集中管理，代码重用性强。

功能丰富：支持参数传递、条件判断、循环控制、异常处理等复杂逻辑。

DELIMITER //
CREATE PROCEDURE procedure_name(
    IN input_param VARCHAR(50),
    OUT output_param INT,
    INOUT inout_param DECIMAL(10,2)
)
BEGIN
    -- 声明变量
    DECLARE var_name INT DEFAULT 0;
    -- 业务逻辑
    IF condition THEN
        -- 处理逻辑
    END IF;
    -- 设置输出参数
    SET output_param = var_name;
END //
DELIMITER ;

Limit

LIMIT是MySQL用于限制查询结果集行数的关键字，主要用于分页查询和结果集截取。

基本语法

*-- 限制返回行数*
SELECT * FROM table_name LIMIT n;
*-- 分页查询*
SELECT * FROM table_name LIMIT offset, count;
SELECT * FROM table_name LIMIT count OFFSET offset;

使用模式

结果限制：

*-- 获取前10条记录*
SELECT * FROM users LIMIT 10;

分页查询：

SELECT * FROM users LIMIT 10, 10;
*-- 或者使用OFFSET语法*
SELECT * FROM users LIMIT 10 OFFSET 10;

性能优化配合：

*-- 配合ORDER BY优化排序分页*
SELECT * FROM users ORDER BY create_time DESC LIMIT 20;
*-- 使用索引字段进行分页*
SELECT * FROM users WHERE id > 1000 ORDER BY id LIMIT 10;

SQL的处理过程

SQL 架构

SQL 通常由 连接层，服务层和存储层组成

连接层： 负责处理TCP连接，客户端通过TCP连接完成对SQL服务的数据查询

服务层： 服务层主要提供的是将用户发送的字符串指令解析为对应的SQL指令，并优化查询过程

存储层： 存储层用于定义数据的存储方式和存储逻辑，并定义相关的读写接口，供服务层调用进行数据查询。

处理过程