MySQL数据定义语言

2024年6月9日 00:00

1.库操作

1.1 创建数据库

CREATE DATABASE book

1.2 创建数据库，如果存在

CREATE DATABASE IF NOT EXISTS book

1.3 切换到某个数据库

use book

1.4 更改库字符集

ALTER DATABASE book CHARACTER SET utf8ALTER DATABASE book CHARACTER SET gbk

1.5 删除数据库

DROP DATABASE bookDROP DATABASE IF EXISTS book

2.数据类型

2.1 整形

整形 TINYINT SMALLINT MEDIUMINT INT(INTEGER) BIGINT，对于整形，长度由范围决定，设置的长度用来补零，但必须在建立字段时搭配zerofill，一旦zerofill，默认为无符号，如果不设置无符号，默认是有符号，如果插入数值超出范围，系统会警告，默认插入临界值。

# 设置有无符号create table user( age int UNSIGNED , id int )INSERT INTO `user` VALUES (-9,9)

2.2 浮点数

浮点型小数 float(m,d) double(m,d)
定点型小数 DECIMAL(m,d) ，精度高，最大取值范围与double相同，m 整数部分和小数部分合起来的位数，d 小数点后保留位数，超出范围默认是临界值

2.3 字符型

短文本char VARCHAR，长文本text

CHAR(m)，m指最大字符数, 固定长度字符，开的空间与长度有关，耗费空间，效率高，可以省略m， m默认是1。
VARCHAR(m) m指最大字符数, 可变长度的字符，开的空间与实际占用有关，节省空间，效率低，不能省略m。

2.4 枚举型

enum('c','b') 掺入的值不在枚举内，默认是空，不区分大小写

2.5 SET

一次可以插入多个值，不区分大小写

2.6 日期值

必须用单引号引起，每个类型作用范围不同

date 1000-01-01 - 9999-12-31
time 838:59:59
datetime 1000-01-01 - 9999-12-31
year 1901 - 2155
TIMESTAMP 1970 - 2038年某一时刻，受时区影响，更能反应实际日期，与MySQL版本和sqlmode关系很大

3.表操作

3.1 创建表

CREATE TABLE book(id INT(10) ,`name` VARCHAR (30),price DOUBLE,authorId INT(10),publishDate DATETIME);CREATE TABLE author(id INT,`name` VARCHAR(20));

3.2 查看表描述

DESC book

3.3 修改列名类型

ALTER TABLE book CHANGE COLUMN publishDate pubDate datetime

3.4 修改类约束类型

ALTER TABLE book MODIFY COLUMN pubDate datetime

3.5 添加新列

ALTER TABLE author ADD COLUMN last_name VARCHAR(20)ALTER TABLE author ADD COLUMN first_name VARCHAR(20)

3.6 删除列

ALTER TABLE author DROP COLUMN first_name

3.7 修改表名

ALTER TABLE author RENAME TO `authors`

3.8 删除表

DROP TABLE IF EXISTS `authors`

3.9 复制

仅仅复制结构

CREATE TABLE 1_author LIKE `author`

复制结构和数据

CREATE TABLE 12_author SELECT * FROM `author`

条件复制

CREATE TABLE 12_author SELECT * FROM `author` WHERE id = 1

复制部分列

CREATE TABLE 12_author SELECT `name` FROM `author` WHERE id = 1

复制部分列，不要数据，使用恒不成立

CREATE TABLE 12_author SELECT `name` FROM `author` WHERE 2 = 1CREATE TABLE 12_author SELECT `name` FROM `author` WHERE 0 /*0==false*/

4.约束

6种约束

NOT NULL (非空约束): 确保列不能有 NULL 值。常用于必须填写的字段。
DEFAULT (默认值约束): 如果插入数据时未指定该列的值，则使用默认值填充。
PRIMARY KEY (主键约束): 保证字段值的唯一性和非空，常用于唯一标识表中每一行的字段。
UNIQUE (唯一约束): 确保列中的值唯一，但可以有 NULL 值。
CHECK (检查约束): 验证字段的值是否符合指定条件，但 MySQL 并不支持 CHECK 约束。
FOREIGN KEY (外键约束): 设定两个表之间的关系，确保该字段的值必须来自于主表的关联列。

表级与列级约束:

表级约束: 可以在创建表时定义，除 NOT NULL 和 DEFAULT 之外的所有约束都支持。
列级约束: 语法上支持所有约束，但 MySQL 中外键在列级定义时可能无法有效执行（需要在表级定义才能生效）。

4.1 主键

添加主键

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY-- 支持表级约束的也可以这样ALTER TABLE stuinfo ADD PRIMARY KEY (id)

CREATE TABLE stuinfo(id int ,stuname VARCHAR(20) ,gender CHAR (1) ,seat INT ,age  INT UNSIGNED ,majarId INT,CONSTRAINT pk/*mysql中，主键改名无效*/ PRIMARY KEY (id))

主键和唯一的对比

都可以保证唯一，主键不能为空，每个表只能有1个，可以两个列自合一起，唯一允许为空，一个表可以有多个，不能多个为空，可以两个列自合一起。

外键，从表设置外键关系，数据类型一致或兼容，主表被关联列必须是一个key,一般是主键或唯一，插入数据时，先插入主表在插入从表，删除数据，先删除从表，再删除主表。

4.2 非空

CREATE TABLE stuinfo(id int ,stuname VARCHAR(20) ,gender CHAR (1) ,seat INT ,age  INT UNSIGNED ,majarId INT)-- 修改表时添加约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULLALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL

4.3 唯一

添加唯一

ALTER TABLE stuinfo ADD UNIQUE (seat)

4.4 外键

添加外键

ALTER TABLE stuinfo ADD FOREIGN KEY (majarId) REFERENCES majar(id)

添加外键名字

ALTER TABLE stuinfo ADD CONSTRAINT fk_majar FOREIGN KEY (majarId) REFERENCES majar(id)

4.5删除约束

删除非空

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL

不添加条件，就会删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age

删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY

先查到，在删除唯一约束

SHOW INDEX FROM stuinfo ALTER TABLE stuinfo DROP INDEX seat

删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_majar

4.6标识列

限制某个字段，又叫自增长列，默认从1开始，必须是一个key，同一张表标识列只能有一个，只能是数值类型

CREATE TABLE stuinfo(id int PRIMARY KEY auto_increment,stuname VARCHAR(20) ,gender CHAR (1) ,seat INT ,age  INT UNSIGNED ,majarId INT)

步长auto_increment_increment 起始值auto_increment_offset,mysql 可以设置步长，但是不能设置起始值

show VARIABLES like '%auto_increment%'

设置删除标识列

ALTER TABLE stuinfo MODIFY COLUMN id int PRIMARY KEY auto_incrementALTER TABLE stuinfo MODIFY COLUMN id int

Liu Zijian's Blog
MySQL存储过程 2023年11月13日 00:00

MySQL存储过程

Liu Zijian's Blog

2023年11月13日 00:00

使用存储过程，减少编译次数和连接数据库次数，提高效率

1. 创建

参数模式

IN :可以作为输入，需要调用者传入值
OUT ：可以作为输出，可以作为返回值
INOUT：既可以传入值，又可以返回值

语法

CREATE PROCEDURE 存储过程名(参数模式 参数名 参数类型,参数模式 参数名 参数类型,...) BEGIN （语句只有一条 begin end 可省略）合法有效的SQL语句;（分号）合法有效的SQL语句;END

示例

delimiter $CREATE PROCEDURE pr1()BEGININSERT INTO book(`name`, price, author) VALUES('012',23.56,'lzj');INSERT INTO book(`name`, price, author) VALUES('45',23.56,'lzj');INSERT INTO book(`name`, price, author) VALUES('12',23.56,'lzj');INSERT INTO book(`name`, price, author) VALUES('87',23.56,'lzj');INSERT INTO book(`name`, price, author) VALUES('32',23.56,'lzj');END $

调用示例

CALL name(var, var);CALL pr1();

2. IN

创建

delimiter $CREATE procedure pr2(IN bid INT)BEGINSELECT * FROM beauty WHERE boyfriend_id = bid;END $delimiter $CREATE PROCEDURE pr3(IN username VARCHAR(20), IN `password` VARCHAR(20))BEGINDECLARE res INT;SELECT COUNT(*) INTO res FROM admin WHERE admin.username = username AND admin.`password` = `password`;SELECT res;END $

调用

CALL pr2(12);CALL pr3('john','8888');

3. OUT

创建

delimiter $CREATE PROCEDURE pr4(IN girl INT, OUT boy INT)BEGINSELECT beauty.boyfriend_id INTO boy FROM beauty WHERE beauty.id = girl;END $

调用

CALL pr4(1, @boy);SELECT @boy;

4. 两个OUT的存储过程

创建

delimiter $CREATE PROCEDURE pr5(IN girlid INT, OUT boyid INT, OUT girlname VARCHAR(20))BEGINSELECT beauty.boyfriend_id,  beauty.`name` INTO boyid, girlnameFROM beauty WHERE beauty.id = girlid;END $

调用

CALL pr5(2, @boyid, @girlname);SELECT  @boyid;SELECT @girlname;

5. INOUT

创建

delimiter $CREATE PROCEDURE pr6(INOUT a INT, INOUT b INT) BEGINSET a = a*2;SET b = b*2;END $

调用

SET @i = 2;SET @j = 4;call pr6(@i, @j);SELECT @i;SELECT @j;

6. 删除

只能同时删除一个

DROP PROCEDURE pr2;

7. 查看

show CREATE PROCEDURE pr3;

Liu Zijian's Blog
MySQL变量 2023年10月27日 00:00

MySQL变量

Liu Zijian's Blog

2023年10月27日 00:00

系统变量：
- 全局变量：整个服务器有效
- 会话变量：一次连接
自定义变量：
- 用户变量
- 局部变量

查看所有会话变量

SHOW VARIABLES ;

所有全局变量

SHOW GLOBAL VARIABLES ;

查看部分全局变量的值

SHOW GLOBAL VARIABLES LIKE '%char%';SHOW SESSION VARIABLES ;SHOW SESSION VARIABLES LIKE '%auto%'SHOW GLOBAL VARIABLES LIKE '%auto%'

查看系统变量名

-- 默认查会话变量SELECT  @@varname ;SELECT  @@session.varname ;-- 查询系统变量SELECT @@globle.varname ;SELECT @@auto_increment_increment ;

为变量赋值

SET GLOBAL (如果是SESSION可以不写) varname = valueSET @@global varname = valueSET @@session varname = value

自定义变量

用户变量针对当前连接有效，声明时必须初始化，可以不指定类型

三种语法

SET @mycat = 'helloworld';SET @car := 'car';SELECT @cat := 'cat';

更新

适用于声明的语法，还可以查出一个值赋给变量

SELECT employees.email INTO @cat FROM employees WHERE employee_id = 100

查询变量的值

SELECT @cat

使用用户变量也需要@

SET @a = 1;SET @b = 2;SET @c = @a + @b;SELECT @c

Liu Zijian's Blog
MySQL视图 2023年10月27日 00:00

MySQL视图

Liu Zijian's Blog

2023年10月27日 00:00

视图，即基于查询的虚拟表，始于mysql 5.1 ，通过普通表动态生成的数据，适用于复杂查询，在使用时动态生成，只保存了SQL逻辑，适用于多个地方用到同一个SQL，且SQL复杂，可以隐藏列定义，保护数据，不会暴漏原始表。

1.创建

CREATE VIEW empjobdept AS SELECT employees.last_name AS lastname,employees.employee_id AS eid,departments.department_name AS deptname,jobs.job_title AS jobtitleFROM employees LEFT JOIN departments ON departments.department_id = employees.department_idLEFT JOIN jobs ON jobs.job_id = employees.job_id

2.使用视图

SELECT * from empjobdept

3.修改视图

存在就修改，不存在就创建

CREATE OR REPLACE VIEW AS 查询语句ALTER VIEW 视图名 AS 查询语句

4.删除视图

DROP VIEW 视图名，视图名....

5.查看视图

DESC empjobdept;show create view empjobdept;

6.修改数据

视图的数据支持更新修改和删除，而且会影响到原始表。

不能编辑视图的情况

1.包含分组函数， GROUP BY ，HAVING ，去重，联合，子查询等语句。
2.from不能更改的视图。
3.常量视图。
4.WHERE字句子查询引用了from字句中的表时。

7.视图和表的对比

创建语法不同，视图数据不占用物理空间，只保存逻辑。

Liu Zijian's Blog
MySQL事务 2023年10月27日 00:00

MySQL事务

Liu Zijian's Blog

2023年10月27日 00:00

1.事务

MySQL的事务是一组被视为一个单一逻辑工作单元的SQL操作。事务确保这些操作要么全部成功，要么在出现错误时全部失败并回滚，以保持数据库的一致性和完整性。事务通常用于需要多个步骤的操作，这些步骤必须全部成功才能确保数据的完整性，比如在银行转账中，转出和转入账户的操作必须同时完成。

在MySQL中，不同的存储引擎具有不同的特性，myisam memory存储引擎均不支持事务，如果你需要事务支持，InnoDB 是最推荐的选择，因为它在性能和事务处理方面表现优异，并且是MySQL的默认存储引擎。

查看当前存储引擎

SHOW ENGINES;

查看事务是否自动提交

SHOW VARIABLES LIKE '%autocommit%';

MySQL隐式事务INSERT， UPDATE， DELETE默认开启自动提交

2.事务的特性

Atomicity, 原子性：每个事务都是不可分割的单位，要么全部执行，要么全部失败
Consistency, 一致性：使数据库从一个一致的状态切换到另一个一致性的状态
Isolation, 隔离性：一个事务执行不受其他事务干扰（需要隔离级别控制）
Durability, 持久性：一个事务，一旦提交，就是永久性改变

3.事务的语法

提交一个事务

-- 必须,使用多个语句的事务，需要禁用隐式事务SET autocommit = 0;  -- 可选START TRANSACTION; -- 只有增删改查可以有事务，ddl语句没有事务UPDATE account SET money = money-20 WHERE id = 1; UPDATE account SET money = money+20 WHERE id = 2; -- 结束事务,应用程序外无法决定使用提交还是回滚,COMMIT,ROLLBACK只能手动选择一个COMMIT;-- ROLLBACK;

回滚到回滚点

-- 必须,使用多个语句的事务，需要禁用隐式事务SET autocommit = 0;   -- 可选START TRANSACTION; -- 只有增删改查可以有事务，ddl语句没有事务UPDATE account SET money = money-20 WHERE id = 1; -- 回滚点SAVEPOINT aUPDATE account SET money = money+20 WHERE id = 2; -- 结束事务,应用程序外无法决定使用提交还是回滚,COMMIT,ROLLBACK只能手动选择一个-- COMMIT;ROLLBACK TO a;

4.事务的隔离级别

运行多个事务，访问相同数据，如果不采取隔离机制，就会引发并发问题，设置隔离级别避免并发问题，Oracle支持:读已提交，串行化，默认读已提交，MySQL支持:读未提交，读已提交，可重复读，串行化，默认可重复读。

4.1 事务中的读现象

脏读：
- 定义：一个事务读取到另一个未提交事务修改的数据，如果该事务回滚，那么读取的数据将是无效的。
- 影响：导致数据不一致，通常与更新操作相关。
不可重复读：
- 定义：一个事务内多次读取同一数据，结果却不一致，这是因为在两次读取之间，其他事务修改了数据。
- 影响：导致一个事务在不同时间点读取相同数据的结果不同，通常与更新操作相关。
幻读：
- 定义：一个事务读取到的结果和之后的读取不一致，是因为其他事务插入或删除了数据行。
- 影响：事务在读取同一范围的数据时，发现新插入或删除的“幻影”行，通常与插入或删除操作相关。

4.2 事务的隔离级别及其特性

读未提交（Read Uncommitted）：
- 允许现象：脏读。
- 特点：事务可以读取到其他事务未提交的修改。
- 问题：数据一致性最低。
读已提交（Read Committed）：
- 避免：脏读。
- 允许现象：不可重复读和幻读。
- 特点：一个事务只能读取其他已提交事务的数据。
- 问题：数据在同一事务内多次读取时，结果可能不一致。
可重复读（Repeatable Read）：
- 避免：脏读和不可重复读。
- 允许现象：幻读。
- 特点：保证一个事务内多次读取同一数据时结果一致。
- 问题：数据一致性更高，但仍可能出现幻读。
串行化（Serializable）：
- 避免：脏读、不可重复读、幻读。
- 特点：事务按顺序执行，完全隔离，达到最高级别的数据一致性。
- 问题：性能最低，因事务需要排队，常引起锁竞争。

总结：

脏读可以通过将隔离级别提高到 读已提交 或更高来避免。
不可重复读可通过使用 可重复读 或 串行化 隔离级别来避免。
幻读只有在 串行化 隔离级别下才完全解决，但这会牺牲性能。

4.3设置隔离级别

查看当前隔离级别

SELECT @@tx_isolation

设置隔离级别为读未提交

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Liu Zijian's Blog
MySQL插入修改和删除 2023年10月27日 00:00

MySQL插入修改和删除

Liu Zijian's Blog

2023年10月27日 00:00

插入

支持一次性插入多行，支持子查询

INSERT INTO beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id) VALUES (13, 'fbb', '女', '1997-01-01', '', NULL, 2);

多行

INSERT INTO beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id) VALUES (17, '杨紫', '女', '1997-01-01', '', NULL, 2),(18, '蒋欣', '女', '1997-01-01', '', NULL, 2);

不支持一次插入多行

INSERT INTO beauty SET id = 14,`name` = '刘涛',phone = '135'

子查询

INSERT INTO beauty(id, `name`, phone)SELECT 26, "赵丽颖", "454" ;

修改

修改多表记录

UPDATE boys boyINNER JOIN beauty bea ON bea.boyfriend_id = boy.idSET bea.phone = "12344555"WHERE boy.id = 1UPDATE beauty LEFT JOIN boys ON boys.id = beauty.boyfriend_idSET beauty.phone = "cn666"WHERE beauty.boyfriend_id is NULL

删除

删除，有返回值，自增长从上个记录开始，支持事务回滚

DELETE FROM beauty WHERE id = 27

删谁delete后面就写谁

DELETE beautyFROM beauty INNER JOIN boys ON beauty.boyfriend_id = boys.idWHERE boys.id = 1DELETE beauty, boysFROM beauty INNER JOIN boys ON beauty.boyfriend_id = boys.idWHERE boys.id = 3

清空整个表,没有返回值，自增长从1开始，不支持事务回滚

TRUNCATE TABLE beautyy

Liu Zijian's Blog
MySQL查询 2023年10月27日 00:00

MySQL查询

Liu Zijian's Blog

2023年10月27日 00:00

1.常量

SELECT 6;

2.字符常量

SELECT 'a';SELECT "aaa";

3.表达式

SELECT 100 * 98;

4.函数

SELECT VERSION();SELECT DATABASE();SELECT USER();

5.别名

SELECT 'A' AS B;SELECT 'A' B;SELECT last_name AS 姓, last_name AS 名 FROM employees;

别名有特殊字符，加双引号

SELECT last_name AS "SELECT #" FROM employees;

6.去重

查员工表涉及的部门编号，字段前加上 DISTINCT

SELECT DISTINCT department_id FROM employees;

7.拼接

加号的作用是数学运算不能连接字符串，两个操作数都为数值，则进行运算，其中一方为字符型，则试图将字符转换为数值，如果转换成功，继续运算，如果转换失败，则字符型的值转换为 0，只要其中一方为 NULL，结果为 NULL

SELECT NULL + 10;SELECT last_name + first_name AS 姓名FROM employees;SELECT CONCAT(last_name, first_name) AS 姓名 FROM employees AS 姓名;

拼接

SELECT CONCAT('a', 'b', 'c');

NULL 和任何值拼接，结果都是 NULL

SELECT CONCAT('a', 'b', NULL);

ifnull() 如果为空值

SELECT IFNULL(NULL, 'default') AS ifn;

8.条件查询

SELECT * FROM employees WHERE salary > 12000;SELECT * FROM employees WHERE department_id <> 90;SELECT * FROM employees WHERE salary >= 10000 AND salary <= 20000;SELECT * FROM employees WHERE department_id < 90 OR department_id > 110 OR salary > 15000;SELECT * FROM employees WHERE NOT(department_id >= 90 AND department_id <= 110) OR salary > 15000;

9.模糊查询

百分号代表通配符，任意字符，也包含 0 个字符，下划线 _ 代表任意单个字符，

SELECT * FROM employees WHERE last_name LIKE '%o%';

查询第三个字母是 n，第五个字母是 n

SELECT * FROM employees WHERE last_name LIKE '__n_l%';

查询第二个字符就是下划线的特殊情况，要进行转义或切换通配符，否则直接__%查询会把所有查出

SELECT * FROM employees WHERE last_name LIKE '_\_%';SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

10.范围查询

BETWEEN AND 可以提高简捷度，结果包含区间值，临界值的位置不能颠倒，否则零行

SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;SELECT * FROM employees WHERE salary NOT BETWEEN 8000 AND 17000;

11.IN 查询

IN 值必须相同或兼容，不能使用通配符

SELECT * FROM employees WHERE job_id IN ('IT_PROG', 'AD_VP');

12.NULL 查询

查询 NULL 用 IS / IS NOT，等于和不等于号不能判断空值

SELECT * FROM employees WHERE commission_pct IS NULL;SELECT * FROM employees WHERE commission_pct IS NOT NULL;

安全等于可用于普通值也可以判断空值

SELECT * FROM employees WHERE commission_pct <=> NULL;

13.排序查询

升序， ASC 可以省略

SELECT * FROM employees ORDER BY salary ASC;

降序

SELECT * FROM employees ORDER BY salary DESC;

条件排序

SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;

表达式排序

SELECT   last_name,   first_name,   salary * 12 * (1 + IFNULL(commission_pct, 0)) AS annual_salary FROM   employeesORDER BY annual_salary ASC;

按照姓名长度

SELECT   LENGTH(CONCAT(last_name, first_name)) AS length,   last_name,   first_name,   salaryFROM   employeesORDER BY length;

多字段排序

SELECT * FROM employees ORDER BY salary ASC, employee_id DESC;

14.子查询

出现在其他语句中的查询语句就是子查询，也叫内查询。

子查询分为几种

标量子查询：结果集一行一列
列子查询：结果集一列多行
行子查询：结果集一行多列
表子查询:只要是查询结果就构成

子查询的使用规则

子查询必须放在小括号内。
子查询通常放在条件的右侧，例如 WHERE、HAVING 子句中的条件判断。
标量子查询：通常配合单行操作符（如 =、<> 等）使用。
列子查询：通常配合多行操作符（如 IN、ANY、SOME、ALL）使用。

子查询位置与支持情况

SELECT 后面：仅支持标量子查询。
FROM 后面：支持表子查询。
WHERE 和 HAVING ：支持标量子查询、列子查询，并且能支持行子查询。
EXISTS ：后面支持表子查询，通常用于判断某个条件是否存在。

14.1标量子查询

工资大于107号员工的人

SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE employee_id = 107)

与141号相同工种，工资比143号大的人

SELECT employee_id, 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)

哪个部门最低工资大于50号部门的最低工资

SELECT job_id, MIN(salary) AS min FROM employees GROUP BY job_idHAVING min > (SELECT MIN(salary) FROM employees WHERE department_id = 50)

工资最少

select * from employeesWHERE salary = (select MIN(salary) FROM employees )

14.2 列子查询

location_id 是1400,1700的部门的员工

SELECT last_name, department_id FROM employees WHERE department_id IN (SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400,1700))

比job_id 是 ‘IT_PROG’的任意一个员工工资少的其他工种的员工

SELECT * FROM employees WHERE salary < ANY(SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG'

比job_id 是 ‘IT_PROG’的所有的员工工资都少的其他工种的员工

SELECT * FROM employees WHERE salary < ALL(SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG'

查询编号最小，工资最高的人行子查询

SELECT * FROM employees WHERE (employee_id, salary) = (SELECT MIN(employee_id), MAX(salary) FROM employees)

14.3 SELECT 后的子查询

每个部门员工个数

SELECT dept.department_id AS deptId, (SELECT COUNT(*) FROM employees WHERE department_id = dept.department_id) AS countFROM departments dept

102号员工的部门名

SELECT employees.employee_id, employees.last_name,(SELECT department_name FROM departments WHERE department_id = employees.department_id) AS deptNameFROM employees WHERE employee_id = 102

from后的子查询

每个部门平均工资等级

SELECT jg.grade_level AS lavel ,avg_dep.salary AS salary,avg_dep.deptId AS deptIdFROM job_grades jgRIGHT JOIN (SELECT AVG(salary) AS salary, department_id AS deptId FROM employees GROUP BY department_id) avg_depON avg_dep.salary BETWEEN jg.lowest_sal AND highest_sal

14.4 EXISTS后子查询

相关子查询，布尔类型，看查询是否有值，括号内写查询语句

SELECT EXISTS(SELECT * FROM employees)SELECT EXISTS(SELECT * FROM employees WHERE salary = 251526)

查询有员工的部门

SELECT department_name FROM departments WHERE EXISTS (SELECT last_name, department_id FROM employees WHERE employees.department_id = departments.department_id)

没有配偶的男生

SELECT * FROM boys WHERE NOT EXISTS (SELECT * from beauty WHERE boyfriend_id = boys.id)

15.连接查询

连接查询的分类:

按年代

sql92标准
sql99标准（推荐）

按功能

内连接：等值连接非等值连接自连接
外连接：左外连接右外连接全外连接
交叉连接

15.1 笛卡尔积

所有行全部连接，因缺少条件造成

select `name`,boyName from beauty, boys

15.2 等值连接sql92

员工名和部门名

SELECT last_name, department_name FROM departments, employeesWHERE employees.department_id = departments.department_id

有奖金的员工名，工种号和工种名

SELECT last_name, jobs.job_id, job_title FROM jobs, employeesWHERE employees.job_id = jobs.job_idAND employees.commission_pct is not null

每个城市的部门数量

SELECT COUNT(departments.department_id) , locations.city FROM locations, departments WHERE departments.location_id = locations.location_idGROUP BY locations.city

15.3 非等值连接sql92

SELECT salary, employee_id ,grade_level from employees, job_grades AS gradesWHERE salary BETWEEN grades.lowest_sal AND grades.highest_sal

15.4 自连接sql92

员工名和上级的名字

SELECT emp.last_name , emp.employee_id , emp.manager_id FROM employees emp , employees mangWHERE emp.manager_id = mang.employee_id

15.5 等值连接sql99

SELECT job_title, last_name FROM employees empINNER JOIN jobs job ON job.job_id = emp.job_id

INNER省略

SELECT job_title, last_name FROM employees emp JOIN jobs job ON job.job_id = emp.job_id

15.6 非等值连接sql99

SELECT salary, employee_id ,grade_level from employeesINNER JOIN job_grades AS grades ON salary BETWEEN grades.lowest_sal AND grades.highest_sal

15.7 外连接

外连接
查一个表有一个表没有，主表都显示，副表和主表匹配的显示出来，没有匹配的用空填充。

15.7.1左外连接

left join 左边是主表

select beauty.`name`, boys.boyName FROM beautyLEFT OUTER JOIN boys ON beauty.boyfriend_id = boys.id

15.7.2 右外连接

right join 右边的是主表

select beauty.`name`, boys.boyName FROM boysRIGHT OUTER JOIN beauty ON beauty.boyfriend_id = boys.id

没有员工的部门

select emp.last_name , dept.department_id FROM departments dept LEFT OUTER JOIN employees empON dept.department_id = emp.department_id WHERE emp.employee_id is NULL

15.7.3 交叉连接

笛卡尔积

SELECT beauty.*, boys.* FROM beautyCROSS JOIN boys

Liu Zijian's Blog
MySQL系统命令 2023年10月27日 00:00

MySQL系统命令

Liu Zijian's Blog

2023年10月27日 00:00

登录命令

mysql -h 主机 -P 端口 -u root -p

密码与p之间不得有空格，其他项之间可以有

mysql -h 主机 -P 端口 -u root -p密码

数据库备份

mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql

导入数据库

mysql -u用户名 -p密码 feaf_accounting < /tmp/accounting.sql

Liu Zijian's Blog
MySQL函数 2022年6月11日 00:00

MySQL函数

Liu Zijian's Blog

2022年6月11日 00:00

MySQL函数分为两种：单行函数：做处理，分组函数：做统计

1.单行函数

字节数

SELECT LENGTH('1234哈哈') AS len -- 字节数

查看客户端字符集

SHOW VARIABLES LIKE '%char%'

字符串连接大写小写

SELECT CONCAT('a','b','c') SELECT UPPER('ASddfDSU')SELECT LOWER('ASddfDSU')

索引从1开始，从某一位开始的截取字符长度

SELECT SUBSTR('helloworld', 6)

从某一索引开始，截取某段长度字符长度

SELECT SUBSTR('helloworld',1,3)SELECT CONCAT(  UPPER( SUBSTR(last_name, 1, 1) )    ) FROM employees

返回起始索引如果找不到返回0

SELECT INSTR('helloworld','or')

去前后空格

SELECT TRIM('  vdfsv  scs  ')

去掉首尾的o

SELECT TRIM('o' FROM 'ooooooooooooooooooheoooolloooooooooooooooooooooooooooooooo')

指定字符左填充到指定长度，如果超过，右边的被截断

SELECT LPAD('hello',10,'*')SELECT LPAD('hellohellohtllohello',10,'*')

指定字符右填充到指定长度，如果超过，右边的被截断

SELECT RPAD('hello',10,'*')SELECT RPAD('hellohellohtllohello',10,'*')

替换

SELECT REPLACE('王老八夜里打酱油和酱油','酱油','酒')

数学函数

ROUND(x) 四舍五入

SELECT ROUND(1.65)

ROUND(x, y) 小数保留

SELECT ROUND(1.6545, 2)SELECT ROUND(1.4578, 2)

上取整，返回大于等于参数的最小整数

SELECT CEIL(1.025)SELECT CEIL(1.00)SELECT CEIL(-1.025)SELECT CEIL(-1.00)

下取整，返回小于等于参数的最大整数

SELECT FLOOR(1.021)SELECT FLOOR(9.6)SELECT FLOOR(-9.6)

截断

SELECT TRUNCATE(1.65,1)

%:余数

SELECT MOD(-11,3)SELECT MOD(11,3)

日期时间函数

SELECT NOW()SELECT CURRENT_TIME()SELECT YEAR(NOW())SELECT YEAR('1996-08-14')SELECT MONTH(NOW())SELECT MONTHNAME(NOW())

字符通过指定格式转换成日期

SELECT STR_TO_DATE('14-8-1996','%d-%m-%Y')SELECT STR_TO_DATE('14-8-96','%d-%m-%y')SELECT * FROM employees WHERE hiredate = '1992-4-3'SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y')SELECT DATE_FORMAT(NOW(),'%y-%m-%d')SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')SELECT DATE_FORMAT(NOW(),'%c-%d %Y')SELECT last_name, DATE_FORMAT(hiredate,'%y年/%m月 %d日') AS hiredate FROM employees

流程控制函数

SELECT IF(1=1,1,0)SELECT IF(1=41,1,0)SELECT last_name, commission_pct, IF(commission_pct IS NOT NULL, '有奖金', '没奖金') FROM employees ORDER BY commission_pct DESC/*case结构1 类似switch 适合判断等值运算*/SELECTlast_name,department_id,salary,CASEdepartment_id WHEN 30 THENsalary * 1.2 WHEN 40 THENsalary * 1.5 ELSE salary END AS new_salary FROMemployees/*case结构1 类似多重if 适合判断范围运算*/SELECTlast_name,department_id,salary,CASE WHEN salary > 20000 THEN 'A'WHEN salary > 15000 THEN 'B'WHEN salary > 10000 THEN 'C'ELSE'D'END AS 工资级别 FROM employees;

2.分组函数

分组函数用作统计，又叫组函数，聚合函数，统计函数

求和，忽略空值

SELECT SUM(salary) FROM employees;

平均，忽略空值

SELECT AVG(salary) FROM employees;

最大最小值，忽略空值

SELECT MIN(salary) FROM employeesSELECT MAX(salary) FROM employeesSELECT MAX(last_name), MIN(last_name) FROM employeesSELECT MAX(hiredate), MIN(hiredate) FROM employees

count函数
计算非空的值的个数，myisam下 COUNT()效率最高，INNODB下 COUNT() COUNT(1) 效率差不多，COUNT(字段)效率最低。

SELECT COUNT(employees.commission_pct) FROM employeesSELECT COUNT(employees.employee_id) FROM employees

可以用来查询总行数，某一列一个字段有值就统计上

SELECT COUNT(*) FROM employees

加上常量值，相当于表中添加一列，可以用来查询总行数

SELECT COUNT(1) FROM employees

DISTINCT 去重后统计

SELECT count(salary) FROM employeesSELECT count(DISTINCT salary) FROM employees

分组查询

和分组函数一同查询的字段要求是group by 后的字段

每个部门的平均工资

SELECT  AVG(salary) , department_id FROM employees GROUP BY department_id

工种最高工资

SELECT MAX(salary) salary, job_id FROM employees GROUP BY job_id

每个领导下有奖金的员工的最高工资（分组前的筛选）

SELECT MAX(salary), manager_id FROM employeesWHERE commission_pct IS NOT NULLGROUP BY manager_id

哪个部门的员工数量大于2（分组后的筛选：HAVING）

SELECT COUNT(*) as count , department_id FROM employees GROUP BY department_idHAVING count > 2

每个工种有奖金的员工最高工资大于12000的工种编号和最高工资

SELECT MAX(salary) as max, job_id FROM employees WHERE  commission_pct is not null GROUP BY job_idHAVING max > 12000

领导编号大于102 的员工最低工资大于5000的领导

SELECT min(salary) as min, manager_id FROM employees WHERE manager_id > 102GROUP BY manager_idHAVING min > 5000

按表达式筛选

按员工姓名长度分组，查员工个数大于5的姓名长度有几个

SELECT count(*) as count, LENGTH(last_name) len from employees GROUP BY lenHAVING count > 5

多个字段分组

每个部门每个工种的平均工资

SELECT AVG(salary) as salary, job_id, department_id FROM employeesGROUP BY job_id, department_idORDER BY salary DESC

每个部门每个工种的平均工资中大于10000的

SELECT AVG(salary) as salary, job_id, department_id FROM employeesGROUP BY job_id, department_idHAVING salary > 10000ORDER BY salary DESC

查各工种平均最大最小和总和

select sum(salary) as sum,min(salary) as min,max(salary) as max,avg(salary) as avg,job_id FROM employeesGROUP BY job_idORDER BY job_id DESC

3.自定义函数

函数和存储过程的区别
函数只能有一个返回，而且必须有返回，存储过程适合批量插入，更新，函数适合处理数据后，得到一个结果，适合查询。
创建

CREATE FUNCTION 名(参数列表) RETURNS 返回类型BEGIN  函数体(必须有return 语句)(函数体只有一句话，可以省略BEGIN END)END

delimiter $CREATE FUNCTION fun1() RETURNS INTBEGINDECLARE count INT DEFAULT 0;SELECT COUNT(*) INTO count FROM myemployees.employees;RETURN count;END $

delimiter $CREATE FUNCTION getPriceFromId(empId INT) RETURNS DOUBLEBEGINDECLARE price DOUBLE DEFAULT 0.0;SELECT employees.price INTO price FROM employees WHERE employee_id = empId;RETURN price;END $

调用

SELECT fun1();

查看函数

SHOW CREATE FUNCTION getPriceFromId

删除函数

DROP FUNCTION getPriceFromId

循环结构

WHILE :

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

LOOP:

标签: LOOP循环体;IF 退出条件 THENLEAVE 标签; END IF; END LOOP 标签;

REPEAT:

标签:REPEAT循环体UNTIL 结束条件 END REPEAT 标签;

循环控制语句：

ITERATE(继续，结束本次继续下次)
LEAVE (break,跳出所在循环)

普通视图

1.库操作

1.1 创建数据库

1.2 创建数据库，如果存在

1.3 切换到某个数据库

1.4 更改库字符集

1.5 删除数据库

2.数据类型

2.1 整形

2.2 浮点数

2.3 字符型

2.4 枚举型

2.5 SET

2.6 日期值

3.表操作

3.1 创建表

3.2 查看表描述

3.3 修改列名 类型

3.4 修改类约束 类型

3.5 添加新列

3.6 删除列

3.7 修改表名

3.8 删除表

3.9 复制

4.约束

4.1 主键

4.2 非空

4.3 唯一

4.4 外键

4.5删除约束

4.6标识列

1. 创建

2. IN

3. OUT

4. 两个OUT的存储过程

5. INOUT

6. 删除

7. 查看

1.创建

2.使用视图

3.修改视图

4.删除视图

5.查看视图

6.修改数据

7.视图和表的对比

1.事务

2.事务的特性

3.事务的语法

4.事务的隔离级别

4.1 事务中的读现象

4.2 事务的隔离级别及其特性

4.3设置隔离级别

插入

修改

删除

1.常量

2.字符常量

3.表达式

4.函数

5.别名

6.去重

7.拼接

8.条件查询

9.模糊查询

10.范围查询

11.IN 查询

12.NULL 查询

13.排序查询

14.子查询

14.1标量子查询

14.2 列子查询

14.3 SELECT 后的子查询

14.4 EXISTS后子查询

15.连接查询

15.1 笛卡尔积

15.2 等值连接sql92

15.3 非等值连接sql92

15.4 自连接sql92

15.5 等值连接sql99

15.6 非等值连接sql99

3.3 修改列名类型

3.4 修改类约束类型