MySQL笔记
1、查询条件分类
查询总结
SELECT 查询列表 7
FROM 表1 别名 1
连接类型 JOIN 表2 别名 2
ON 连接条件 3
WHERE 分组前筛选条件 4
GROUP BY 分组列表 5
HAVING 分组后筛选条件 6
ORDER BY 排序列表 8
LIMIT 起始条目，条目数 9
SELECT 查询列表 FROM 表名 WHERE 筛选条件
条件分类 1、<,>, =, !=, <>, <=, >= 2、逻辑运算符 && || ! AND OR NOT WHERE Id > 5 AND Id < 7 等价于 WHERE NOT (Id >=5 AND Id <=7) 3、模糊查询 LIKE 通配符 %任意个字符包含0个字符 _ 任意单个字符 /* LIKE '_$_%' ESCAPE '$' 意思是用$符号当做转义字符使用 '_\_%' ESCAPE '\'; 以一个字符开头，第二个字符是下划线的*/ BETWEEN 小值 AND 大值 IN (opt1,opt2) IS NULL 字段 IS NOT NULL 安全等于<=>NULL 和数值 LENGTH('john') =4 ORDER BY ID ASC, salary DESC; 先按ID升序，再按salary降序
2、sql函数
函数
1、单行函数
CONCAT(),LENGTH(),IFNULL(exp1,exp2);
字符函数
LENGTH(str)
SHOW VARIABLES LIKE '%char%' 查看字符集
UPPER(str)
LOWER(str)
SUBSTR()
SELECT SUBSTR('abcd',3) out_put; 表示从3开始后的所有字符。索引从1开始
SELECT SUBSTR('你好顶顶顶顶',2,3) out_put;表示从2开始后的3个字符
INSTR(str,_substr)返回子串中在str中第一次出现的索引
TRIM() 默认去掉首尾的空格
TRIM(‘a’ FROM 'ahhaha') 去掉首尾的a
LPAD('fff',4,'a') 用指定的a左边填充fff直到4个字符
RPAD('fff',4,'a') 用指定的a右边填充fff直到4个字符
REPLACE('ddffg','g','d')用g代替d
数学函数
ROUND(1.33) 四舍五入取整
ROUND(1.233,2) 1.23 保留两位小数
CEIL(1.03) 向上取整 2
FLOOR()
TRUNCATE(1.49,1) 截断 1.4
MOD(12,3)
日期函数
DATEDIFF('','') 相差的天数
NOW()日期+时间
CURDATE() 日期
CURTIME()时间
YEAR('1995-04-03') 取年 1995
MONTH('1995-04-03') 04
MONTHNAME('1995-04-03') April
STR_TO_DATE('03-11-2000','%m-%d-%y') 把给定的日期转换成指定格式的
DATE_FORMAT(NOW(),'%y年%m月%d日')
其他函数
SELECT VERSION()
SELECT DATABASE()
SELECT USER()
流程控制函数
IF()
SELECT IF(10<5,'less','more');
用法1 等值
SELECT (CASE 表达式
WHEN 常量1 THEN 表达式1
WHEN 常量2 THEN 表达式2
... ...
ELSE 表达式 （默认）
END ) AS COLUMN form TABLE
SELECT salary original_salary, dept_no,
CASE dept_no
WHEN 1 THEN salary*1.5
WHEN 2 THEN salary*2
ELSE salary*3
END AS new_salary FROM salary;
用法2 条件
CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
... ...
ELSE 表达式
END
SELECT salary CASE
WHEN salary > 2000 THEN 'A'
WHEN salary > 1000 THEN 'B'
ELSE 'D'
END AS 工资级别 FROM salary;
2、分组函数 用于分组后的技计算，也是多个记录
用于统计多个数据，又叫聚合函数,多个记录，生成一个值，null 未参与运算,搭配distinct 使用
和分组函数一同查询的字段一定是group by后的字段
SUM() 数值 SUM(DISTINCT salary)
AVG()数值 AVG(DISTINCT salary)
MAX() 数值，日期，字符串 MAX(DISTINCT salary)
MIN() 数值，字符串 MIN(DISTINCT salary)
COUNT() 非空字段 COUNT(DISTINCT salary)
COUNT(salary) <= COUNT(*) salary或其他字段中可能会有null，不参与运算
SELECT COUNT(*) FROM employees; 统计记录个数
SELECT COUNT(1) FROM employees; 统计记录个数
MYISAM 存储引擎下, COUNT(*)效率高
INNODB 存储引擎下,COUNT(*)和 COUNT(1)效率差不多
3、分组查询
分组查询
SELECT COLUMN, group_funtion(COLUMN)
FROM TABLE
[ WHERE CONDITION ] （分组之前的筛选，原始表）
GROUP BY EXP,EXP
HAVING 条件 （分组之后的筛选,分组后的表）分组函数做条件
[ ORDER BY COLUMN ]
1、查询每个工种的最高工种
SELECT *MAX(salary), job_id
FROM employees
GROUP BY job_id;
job_id要一样，是先分组，再在组内求最大值
2、求每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE comission IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;
4、连接查询
连接查询 多表查询 SELECT * FROM boys, girls; 多表查询会出现笛卡尔积现象，会出现m*n条记录 发生的原因是没有有效的连接筛选条件 连接分类： 年代： sql92标准 sql99标准（推荐） 功能： 内连接： 等值连接 非等值连接 自连接 外连接： 左外连接 右外连接 全外连接 交叉连接
4.1 sql92标准
1、等值连接 多表等值连接为满足条件的多表交集部分 n个表就有n-1个连接条件 一般需要为表其别名 执行顺序 5 SELECT lastName, departName 1 FROM employees, department 2 WHERE employees.departNo = department.id; 3 [GROUP BY COLUMN] 4 HAVING 6 ORDER BY 2、非等值连接 查询员工工资和工资级别 SELECT salary,grade_level FROM employees e, grades g WHERE salary BETWEEN g.lowest_salary AND g.highest_salary GROUP BY HAVING ORDER BY 3、自连接 把自己的表分成两张以上的表进行连接 查询员工号，员工姓名和其领导的员工号和领导姓名 SELECT e.id,e.name,m.id,m.name FROM employees e,employees m WHERE e.id=m.id; 分组前筛选 WHERE 原始表 GROUP BY 之前 分组后筛选 WHERE 分组后的表 GROUP BY 之后
4.2 sql99标准 推荐使用
sql99标准 SELECT COLUMNS FROM table1 alias1 连接类型 JOIN table2 alias2 ON 连接条件 [WHERE 筛选条件] [GROUP BY 分组] [HAVING 筛选条件] [ORDER BY 排序列表] 连接类型 内连接:INNER 外连接: 左外: LEFT [OUTER] 右外: RIGHT [OUTER] 全外: FULL [OUTER] 交叉连接： CROSS 内连接： 1、等值连接 sql99标准：连接条件和筛选条件分开 SELECT lastName, departName FROM employees e [ INNER ] JOIN department d ON e.departNo = d.id; #连接条件 [ INNER JOIN jobs ON 连接条件 ] [WHERE 筛选条件] #筛选条件 [GROUP BY COLUMN] [HAVING] [ORDER BY] sql92标准：连接条件和筛选条件放在一起，在where下 SELECT lastName, departName FROM employees e, department d WHERE e.departNo = d.id; # 连接条件 AND #筛选条件 [GROUP BY COLUMN] [HAVING] [ORDER BY] 2、非等值连接 查询员工工资和工资级别 SELECT salary,grade_level FROM employees e INNER JOIN grades g ON salary BETWEEN g.lowest_salary AND g.highest_salary WHERE GROUP BY HAVING ORDER BY 3、自连接 把自己的表分成两张以上的表进行连接 查询员工号，员工姓名和其领导的员工号和领导姓名 SELECT e.id,e.name,m.id,m.name FROM employees e INNER JOIN employees m ON e.id=m.id; 外连接：用于一个表中有的字段，而另一个表中没有的字段 1、外连接的查询结果为主表中的所有记录 如果从表中有与连接条件匹配的项就显示其记录 如果从表中有与连接条件没有匹配的项，就显示为 NULL 结论：外连接结果=内连接结果+主表中有而从表中没有的记录 2、左右外连接 左外连接 主表 LEFT JOIN 从表 右外连接 从表 RIGHT JOIN 主表 左外和右外交换顺序可以实现同样的效果 查询哪个部门没有员工 SELECT d.*, e.employee_id FROM departments d LEFT OUTER JOIN employees e ON d.depart_id =e.depart_id WHERE employee_id IS NULL 3、全连接=内连接结果+表1中有但表2中没有的字段+表2中有但表1中没有的字段 交叉连接：也就是笛卡尔积 SELECT g.*, b.* FROM girl g CROSS JOIN boys b;
5、子查询
子查询
出现在其他语句中的select语句
主查询或外查询
内部嵌套其他语句的select语句
SELECT COLUMNS
FROM table1
WHERE COLUMN 条件运算符(
SELECT COLUMN
FROM table2
WHERE CONDITION
);
子查询分类
位置不同：
1、select 后面
标量子查询
2、from 后面
表子查询 必须起别名
3、where或having后面 ※
标量子查询 √ 常用
列子查询 √ 常用
行子查询
4、exists后面（相关子查询）0，1 NOT exsits
表子查询
结果集的行列数不同：
1、标量子查询（结果集只有一行一列）
搭配单行操作符使用 <, > ,<=,>=, <>
2、列子查询（结果集有一列多行）
搭配多行操作符使用 IN(等价于 =ANY(子查询)), ANY/SOME,ALL, NOT IN(等价于 <>ALL(子查询))
3、行子查询（结果集有一行多列）
4、表子查询
6、 联合查询
合并多个查询结果，且多个表自己没有直接的连接方式 查询语句1 UNION 查询语句2 UNION ... ... 查询中国男性用户的信息和外国男性用户的信息 SELECT id,cname,csex FROM china WHERE csex='男' UNION SELECT fId,fName,fGender FROM foreigners WHERE fGender='male'; 注意事项： 1、要求多条查询语句的列数是一致的，顺序也是一致 2、 UNION 默认去重，使用 UNION ALL 可以包含重复项
7、DML Data Manipulation language
DML语言 INSERT 增 UPDATE 改 DELETE 删 1、 INSERT 方式1 INSERT INTO TABLES(column1,column2,...) VALUES(value1,value2,...),(value1,value2,...),(value1,value2,...)....; 支持子查询 INSERT INTO TABLES(column1,column2,...) SELECT (column1,column2,...) FROM tables； 方式2 INSERT INTO TABLES SET column1=value1,column2=value2...; 2、 UPDATE 单表 UPDATE TABLES SET column1=newValue1,column2=newValue2... WHERE conditions; 多表 sql92语法 UPDATE table1 alias1, table2 alias2 SET column1=newValue1,column2=newValue2... WHERE 连接条件 AND 筛选条件; sql99语法 UPDATE table1 alias1 INNER | LEFT | RIGHT JOIN table2 alias2 ON 连接条件 SET column1=newValue1,column2=newValue2... WHERE 筛选条件; 3、 DELETE 单表删除 DELETE FROM TABLES WHERE conditions; 多表删除 sql92语法 DELETE alias1 | alias2 FROM table1 alias1,tables2 alias2 WHERE 连接条件 AND 筛选条件 sql99语法 DELETE alias1 | alias2 FROM table1 alias1 INNER | LEFT | RIGHT JOIN tables2 alias2 ON 连接条件 WHERE 筛选条件 补充 TRUNCATE table1; 效率比delete高 全表删除，没有连接条件，where条件 DELETE 之后，自增长列从delete处开始，有返回值，可以回滚 TRUNCATE 之后，自增长列从1开始，无返回值，不可以回滚
8、DDL Data Definition Language
DDL 创建 CREATE 修改 ALTER 删除 DROP 1、库的管理 创建数据库 CREATE DATABASE IF NOT EXISTS books; 修改数据库 更改字符集 ALTER DATABASE books CHARACTER SET gbk; 删除数据库 DROP DATABASE IF EXISTS books; 2、表的管理 创建表 CREATE TABLE tb_name( col_name col_type(长度) 约束, col_name col_type(长度) 约束 ); 修改表 1、修改列名，类型，约束 ALTER TABLE book CHANGE COLUMN old_col new_col datatype 约束; ALTER TABLE book MODIFY COLUMN col datatype 约束; 2、添加新列 ALTER TABLE book ADD COLUMN col datatype 约束; 3、删除列 ALTER TABLE book DROP COLUMN col; 4、修改表名 ALTER TABLE book RENAME TO new_book; 删除表 DROP TABLE IF EXISTS book; 通用写法 DROP DATABASE IF EXISTS old_database; CREATE DATABASE new_database; DROP TABLE IF EXISTS old_table; CREATE TABLE new_table(col datatype ); 复制表 1、仅复制表结构 CREATE TABLE copied_book LIKE book; 2、复制表结构+[部分]数据 CREATE TABLE copied_book SELECT * FROM book [WHERE 条件]; 3、仅复制某些字段 CREATE TABLE copied_book SELECT col1,col2 FROM book WHERE 条件 1=2;
9、数据类型
数据类型
1、整形型 （默认 SIGNED ）
TINYINT SMALLINT MEDIUMINT INT/INTEGER BIGINT
1 2 3 4 8
CREATE TABLE tab_int(
t1 INT(7), #7代表占据的字符长度，这里表示t1占据7个位数,默认空格填充 [ 123]
t2 INT(7) ZEROFILL #7代表占据的字符长度，这里表示t1占据7个位数，位数不够用0 填充 [0000123]
)
小数
1、浮点型
FLOAT(M,D)
DOUBLE(M,D)
(M,D)可以省略，默认没有限制。
D是保留D位小数，M是总共的位数=整数位数+小数位数
2、定点型（精确到较高，货币运算）
DEC(M,D)
DECIMAL(M,D)
(M,D)可以省略，默认是(10,0)
字符型
1、短文本
CHAR(M) 固定长度M 效率高
VARCHAR(M) 可变长度M 效率低
M是字符数，可以是’A‘和’中‘是一样的，1个字符
2、长文本
TEXT
BLOB 较长的二进制
位类型 较短的二进制
BIT(M)
BINARY
VARBINARY
枚举型 选一个插入
CREATE TABLE tab_enum(
sex ENUM('女','男')
)
INSERT INTO tab_enum VALUES('男');
集合类型 选一个或多个插入
CREATE TABLE tab_enum(
set1 SET('a','b','c');
)
INSERT INTO tab_enum VALUES('a,b');
日期型
DATE 日期
TIME 时间
YEAR 年
DATETIME 8字节 时间+日期 1000-9999 不受时区影响
TIMESTAMP 4字节 时间+日期 1970-2038 受时区影响
10、常见约束
常见的约束
1、 NOT NULL 非空
2、 DEFAULT 默认
3、 PRIMARY KEY 唯一，非空，只能有一个列为主键
4、 UNIQUE 唯一，可以为空，可以有多个列为unique
5、 CHECK 检查约束 mysql不支持
6、 FOREIGN KEY 用于限制两表的关系，在从表添加外键（主表的主键和唯一键）
从表students，主表majors
插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
分类：
列级约束：六大约束语法上都支持，但外键约束没有效果
表级约束：除了非空、默认都支持
1、创建表时
CREATE DATABASE student;
CREATE TABLE majors(
id INT PRIMARY KEY,
majorName VARCHAR(20)
)
列级约束
CREATE TABLE students(
id INT PRIMARY KEY, #主键
stuName VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gener IN ('男','女')), #检查约束
seat INT UNIQUE, #唯一，可以为空
age INT DEFAULT 18,
marjorID INT FOREIGN KEY REFERENCES majors(id) #外键
)
表级约束
CREATE TABLE students(
id INT,
stuName VARCHAR(20) NOT NULL, #非空
gender CHAR(1),
seat INT ,
age INT DEFAULT 18,
marjorID INT,
CONSTRAINT pk PRIMARY KEY(id,stuName), #组合主键
CONSTRAINT uq UNIQUE(seat), #唯一，可以为空
CONSTRAINT ck CHECK(gener IN ('男','女')), #检查约束
CONSTRAINT fk_students_marjor FOREIGN KEY(marjorID) REFERENCES majors(id) #外键
)
添加列级约束
ALTER TABLE book MODIFY | CHANGE | ADD COLUMN col datatype 约束;
添加表级约束
ALTER TABLE book ADD 约束;
删除约束
ALTER TABLE book MODIFY COLUMN col datatype [约束]; #不加约束就删除了
ALTER TABLE book DROP PRIMARY KEY;
ALTER TABLE book DROP INDEX seat;
ALTER TABLE book DROP FOREIGN KEY fk_students_marjor ;
SHOW INDEX FROM students; #查看约束
标识列又称自增长列
一个表中只能有一个自增长列
自增长列只能是数值型
自增长列不一定与 PRIMARY KEY 搭配，但要与唯一列搭配，比如unique
id PRIMARY KEY AUTO_INCREMENT
SHOW VARIABLES LIKE "%AUTO_INCREMENT%";
SET AUTO_INCREMENT_INCREMENT =3; 设置步长为3
11、TCL
TCL TRANSACTION control LANGUAGE 事务 由一个或多个sql语句组成，每一个sql语句都相互依赖，整个单位的sql是一个不可分割的整体 SHOW ENGINES; 存储引擎 mysql常见的存储引擎 INNODB 支持事务 MYISAM 不支持事务 MEMORY 不支持事务 事务的ACID属性 1、原子性 Atomicity 事务是一个不可分割的整体，事务中的操作要么全部执行，要么同时不发生 2、一致性 consistency 事务必须从一个一致性状态变为另一个一致性状态 3、隔离性 ISOLATION 不同事务之间相互隔离，不会相互干扰 4、持久性 Durability 事务一旦提交，改变发生是持久的。 事务的创建 隐式事务 事务没有明显的开启或结束的标记，比如 INSERT UPDATE DELETE SHOW VARIABLES LIKE "autocommit"; ON 表示默认是隐式事务 显示事务 事务有明显的开启和结束的标记 前提是必须禁用自动提交功能，即 SET autocommit=0; step 1: 开启事务 SET autocommit=0; START TRANSACTION; (可选) step 2：编写事务中的sql DML 即 SELECT INSERT UPDATE DELETE 语句1; 语句2; ... step 3:结束事务 COMMIT 提交事务，立即生效 ROLLBACK 回滚事务，暂时不生效,存储在内存中 DELETE 支持回滚 TRUNCATE 不支持回滚 张三 有1000元 李四有 1000元 张三借给李四500元 #开启事务 SET autocommit=0; START TRANSACTION; (可选) #一组sql语句 UPDATE account SET balance =500 WHERE userName ='张三'; #savepoint savepoint1; #设置保存点 UPDATE account SET balance =1500 WHERE userName ='李四'; #结束事务 COMMIT; #rollback; #rollback to savepoint1; 事务的隔离级别 查看隔离级别 SELECT @@tx_isolation; 脏读(没有提交) 不可重复读（更新） 幻读（插入） READ UNCOMMITTED YES YES YES （当前事务中，别的事务更新，没有commit，会读取新数据，别的事务rollback前后，会读取脏数据（本来不存在的数据），前后数据不一致） READ COMMITTED NO YES YES （当前事务中，别的事务更新，commit以后，才会读取新数据，当前事务中会读到有效数据，但前后数据会不一样） REPEATABLE READ NO NO YES （当前事务中，别的事务更新，commit以后，前后不会读取新数据，新开事务才会读取新数据，当前事务中一直读到的是别的事务之前的数据。 别的事务插入新行前后，当前事务更新的列数会多一行） SERIALIZABLE NO NO NO （当前事务没有commit前，别的事务无法插入，无法更新，此级别较高，但性能较低） mysql默认的隔离级别是 REPEATABLE READ 设置当前连接的隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED 设置数据库系统的全局隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED
12、视图
视图 虚拟表，通过普通表动态生成的，临时性的 只保存sql逻辑，不保存查询结果 应用场景： 1、多个地方用到同样的查询结果 2、该查询结果使用的sql语句比较复杂、 3、保护了数据的安全性 创建视图 CREATE VIEW view_name AS SQL 语句 视图修改 方式一 CREATE OR REPLACE VIEW AS SQL 语句 方式二 ALTER VIEW vie_name AS SQL 语句 视图删除 DROP VIEW view1, view2,....;
