文章目录
MySQL核心语法
2.1 数据类型
| 数据类型 | 描述 |
|---|---|
| CHARACTER(n) | 字符/字符串。固定长度 n。 |
| VARCHAR(n) 或 CHARACTER VARYING(n) | 字符/字符串。可变长度。最大长度 n。 |
| BINARY(n) | 二进制串。固定长度 n。 |
| BOOLEAN | 存储 TRUE 或 FALSE 值 |
| VARBINARY(n) 或 BINARY VARYING(n) | 二进制串。可变长度。最大长度 n。 |
| INTEGER§ | 整数值（没有小数点）。精度 p。 |
| SMALLINT | 整数值（没有小数点）。精度 5。 |
| INTEGER | 整数值（没有小数点）。精度 10。 |
| BIGINT | 整数值（没有小数点）。精度 19。 |
| DECIMAL(p,s) | 精确数值，精度 p，小数点后位数 s。例如：decimal(5,2) 是一个小数点前有 3 位数，小数点后有 2 位数的数字。 |
| NUMERIC(p,s) | 精确数值，精度 p，小数点后位数 s。（与 DECIMAL 相同） |
| FLOAT§ | 近似数值，尾数精度 p。一个采用以 10 为基数的指数计数法的浮点数。该类型的 size 参数由一个指定最小精度的单一数字组成。 |
| REAL | 近似数值，尾数精度 7。 |
| FLOAT | 近似数值，尾数精度 16。 |
| DOUBLE PRECISION | 近似数值，尾数精度 16。 |
| DATE | 存储年、月、日的值。 |
| TIME | 存储小时、分、秒的值。 |
| TIMESTAMP | 存储年、月、日、小时、分、秒的值。 |
| INTERVAL | 由一些整数字段组成，代表一段时间，取决于区间的类型。 |
| ARRAY | 元素的固定长度的有序集合 |
| MULTISET | 元素的可变长度的无序集合 |
| XML | 存储 XML 数据 |
2.2 DDL语法
2.2.1.创建数据库
CREATE DATABASE 名字;
CREATE DATABASE test;
2.2.2.选择要操作的库
USE 数据库名;
USE test;
2.2.3.列出所有数据库
SHOW DATABASES;
2.2.4.删除数据库
删除数据库结构加内容：
DROP DATABASE 数据库名；
删除时只能针对一个数据库进行操作，不可同时操作多个数据库
2.2.5.创建表语句
2.2.5.1 创建表
CREATE TABLE 名字(字段名1 字段类型 各种约束[是否为主键；是否为null；是否自增])[指定字符集；指定排序方式];
CREATE TABLE tb_user(
id int primary key auto_increment not null,
name varchar(40) not null unique,
password varchar(40) not null default “000”
)charset = utf8;
2.2.5.2 列出库中的表：
展示当前数据库内的所有表：
SHOW TABLES;
展示指定表的数据结构：
DESC 表名；
2.2.5.3 删除表：
删除表结构及内容：
DROP TABLE 表名；
2.2.6 约束
为了规定表结构，让使用者按照一定规范进行数据存储及读取操作的限定。
常用约束：
2.2.6.1 : 是否为空
NOT NULL
2.2.6.2：唯一约束
2.2.6.3：主键约束
2.2.6.4：自动增长
2.3 DML语句
2.3.1.插入语句
插入完整字段语句：
INSERT INTO 表名 values (值1，值2...)
插入指定字段语句：
INSERT INTO 表名（字段名1，字段名2...) values (值1，值2...)
插入多条语句：
INSERT INTO 表名（字段名1，字段名2...）select 值1，值2... union all select 值3，值4...
如果对应字段的数据类型是字符或字符串类型的，需要用单引号或双引号将值括起来
2.3.2. 删除数据内容
删除表内容：
DELETE FROM 表名；
删除表内指定行：
DELETE FROM 表名 WHERE 字段名 = 值；
delete**只删除表内容，不删除表结构，可回滚 drop**删除表或者数据库的所有信息，包括数据和表结构，删除后释放空间，不可回滚 删除时只能针对一个表或者数据库进行操作，不可同时操作多个表和数据库
2.3.3. 更改语句
全部更改：
UPDATE 表名称 SET 字段名 = 新值；
部分更改：
UPDATE 表名称 SET 字段名 = 新值 WHERE 过滤条件；
2.3.4. 查询语句
2.3.4.1 逐行查询
查询例句：
SELECT EXPR1，EXPR2... FROM 目标数据源;
查询表达式互相没有干扰，独立完成查询操作
例子：
新建如下user表
create table tb_user(
id int primary key auto_increment not null,
name varchar(40) not null,
age int
)charset=utf8;
| id | name | age |
|---|---|---|
| 1 | 张三 | 20 |
| 2 | 张三1 | 20 |
| 3 | 张三2 | 20 |
| 4 | 张三4 | 20 |
| 5 | 张三5 | 20 |
| 6 | 张三 | 20 |
| 7 | 张三5 | 20 |
| 8 | 张三4 | 20 |
用以下语句进行查询：
SELECT id,1,"OK" from user;
查询结果为：
| id | 1 | OK |
|---|---|---|
| 1 | 1 | OK |
| 2 | 1 | OK |
| 3 | 1 | OK |
| 4 | 1 | OK |
| 5 | 1 | OK |
| 6 | 1 | OK |
| 7 | 1 | OK |
| 8 | 1 | OK |
可得出结论：
如果查询表达式为变量，则输出对应数据，如果查询表达式为常量，则输出常量本身
用以下语句进行查询：
SELECT id+10,1,"OK" from user;
查询结果为：
| id | 1 | OK |
|---|---|---|
| 11 | 1 | OK |
| 12 | 1 | OK |
| 13 | 1 | OK |
| 14 | 1 | OK |
| 15 | 1 | OK |
| 16 | 1 | OK |
| 17 | 1 | OK |
| 18 | 1 | OK |
可得出结论：
查询表达式可满足运算需求，最终输出结果为运算后的值
示例语句分解：
from ： 指定要查询的表
select ： 查询表达式
where ： 后面跟过滤条件 —— 一切可以产生布尔值结果的表达式
id、name、age+1、COUNT(*) ： 查询的具体表达式
表达式类型：可以是字段变量名、常量、算术表达式、布尔表达式等运算表达式、调用函数等。
2.3.4.2 常用表达式
- IFNULL函数
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
IFNULL意寓着如果查找到数据，则用查找出的数据，如果没找到，则用后一个默认数据。
- CASE WHEN语句
CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END
condition是一个返回布尔类型的表达式，如果表达式返回true，则整个函数返回相应result的值，如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。
例1：
CASE `SCORE` WHEN 'A' THEN '优' ELSE '不及格' END
CASE `SCORE` WHEN 'B' THEN '良' ELSE '不及格' END
CASE `SCORE` WHEN 'C' THEN '中' ELSE '不及格' END
等同于例2：
CASE WHEN `SCORE` = 'A' THEN '优'
WHEN SCORE = 'B' THEN '良'
WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
- 字符串拼接
CONCAT(string1,string2,…)
其中只要一个为Null，那全部为Null
2.3.4.3 过滤查询
查询例句：
SELECT EXPR1,EXPR2... FROM 目标数据源 WHERE 过滤条件；
用以下语句进行查询：
SELECT id+10,age,name from user where id >3;
查询结果为：
| id | name | age |
|---|---|---|
| 13 | 张三2 | 20 |
| 14 | 张三4 | 20 |
| 15 | 张三5 | 20 |
| 16 | 张三 | 20 |
| 17 | 张三5 | 20 |
| 18 | 张三4 | 20 |
可得出结论：
过滤查询语句的优先级高于查询表达式语句
- LIKE操作符：
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
- RLIKE操作符：
SELECT column_name(s) FROM table_name WHERE column_name RLIKE "^\\w{0,3}&";
- 通配符：
| 通配符 | 描述 |
|---|---|
| % | 替代 0 个或多个字符 |
| _ | 替代一个字符 |
- IN操作符
IN 操作符允许在 WHERE 子句中规定多个值。
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
- BETWEEN操作符
BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
等效于 value1 < column_name AND value2 > column
- 别名
通过使用 SQL，可以为表名称或列名称指定别名。创建别名是为了让列名称的可读性更强。
column 别名
column AS 别名
两种方式都可以
- UNION
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
- NULL值子句
IS NULL：
SELECT EXPR1,EXPR2... FROM 数据源1 WHERE 字段1 IS NULL;
SELECT EXPR1,EXPR2... FROM 数据源1 WHERE 字段1 IS NOT NULL;
因为NULL不可以用=、<、>这些比较符进行比较，所以需要用IS NULL和IS NOT NULL比较
2.3.4.4 多条件查询
SELECT EXPR1,EXPR2... FROM 目标数据源 WHERE 条件语句1 AND 条件语句2 AND 条件语句3 ...
2.3.4.5 字段排序
SELECT EXPR1,EXPR2... FROM 目标数据源 ORDER BY 排序字段[默认升序，降序用desc+字段]
2.3.4.6 分组查询
查询例句：
SELECT 聚合函数1，聚合函数2... FROM 目标数据源 GROUP BY 分组字段；
用以下语句进行查询：
SELECT name,MAX(age) FROM user GROUP BY name;
查询结果为：
| name | age |
|---|---|
| 张三 | 20 |
| 张三1 | 20 |
| 张三2 | 20 |
| 张三4 | 20 |
可得出结论：
分组字段名可作为聚合函数使用，非分组字段名需要使用聚合函数进行计算
2.3.4.7 聚合函数
· AVG() - 返回平均值
SELECT AVG(column_name) FROM table_name
· COUNT() - 返回行数
SQL COUNT(column_name) 语法
COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：
SELECT COUNT(column_name) FROM table_name;
SQL COUNT(*) 语法
COUNT(*) 函数返回表中的记录数：
SELECT COUNT(*) FROM table_name;
SQL COUNT(DISTINCT column_name) 语法
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：
SELECT COUNT(DISTINCT column_name) FROM table_name;
· MAX() - 返回最大值
SELECT MAX(column_name) FROM table_name
· MIN() - 返回最小值
SELECT MIN(column_name) FROM table_name
· SUM() - 返回总和
SELECT SUM(column_name) FROM table_name
2.3.4.8 分组过滤
SELECT 聚合函数1，聚合函数2... FROM 目标数据源 GROUP BY 分组字段 HAVING 聚合函数3·条件语句；
SELECT 聚合函数1，聚合函数2... FROM 目标数据源 WHERE 条件语句 GROUP BY 分组字段 HAVING 聚合函数3·条件语句；
select category,avg(price),count(price),max(pnum),min(id),sum(pnum) from products where id > 3 group by category having avg(price) > 50 ;
分组过滤执行时条件变量需要使用聚合函数进行计算
SQL执行顺序为： FROM 数据源 ——>WHERE 过滤 ——>GROUP BY 分组 ——>HAVING 过滤 ——>SELECT 查询
2.3.4.9 JOIN查询
INNER JOIN（内连接 INNER JOIN与JOIN是相同）：
SELECT EXPR1,EXPR2... FROM 目标数据源1 JOIN 目标数据源2 ON 两个数据源共同字段判断语句
LEFT JOIN（左外连接，以左表为主，关联不上的字段用null填充）：
SELECT EXPR1,EXPR2... FROM 目标数据源1 LEFT JOIN 目标数据源2 ON 两个数据源共同字段判断语句
RIGHT JOIN（右外连接，以右表为主，关联不上的字段用null填充）：
SELECT EXPR1,EXPR2... FROM 目标数据源1 RIGHT JOIN 目标数据源2 ON 两个数据源共同字段判断语句
FULL JOIN（全连接，相当于左右外连接）：
SELECT EXPR1,EXPR2... FROM 目标数据源1 FULL JOIN 目标数据源2 ON 两个数据源共同字段判断语句
CROSS JOIN（交叉连接，连接之祖）：
SELECT EXPR1,EXPR2... FROM 目标数据源1 CROSS JOIN 目标数据源2 ON 两个数据源共同字段判断语句
，（多数据源联合查询）：
SELECT EXPR1,EXPR2... FROM 目标数据源1,目标数据源2 ON 两个数据源共同字段判断语句
注：交叉连接、内连接、多个数据源联合查询三种方式操作后得出的是两个数据源的笛卡尔积
2.3.4.10 嵌套子查询
关键：内部的一个查询语句作为外部的查询语句的数据源之一
SELECT EXPR1,EXPR2... FROM (SELECT EXPR1,EXPR2... FROM 目标数据源1)
其中如果要使用内部查询后的字段作为外部查询的条件时，需要将整个子查询语句起别名，用别名进行调用，如下：
SELECT EXPR1,EXPR2... FROM(SELECT EXPR3,EXPR4... FROM 目标数据源1) AS T1 WHERE T1.EXPR3 > 判断条件
2.3.4.11 外键
建立外键的前提： 本表的列必须与外键类型相同(外键必须是外表主键)。
指定外键关键字： foreign key(列名)
引用外键关键字： references <主表名>(主表主键)
事件触发限制: on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action
例如：
tb_order表主键oid 类型 int
tb_user 表主键id类型 int
创建含有外键的表：
CREATE TABLE temp (
uid INT,
oid INT,
PRIMARY KEY ( uid, oid ),
FOREIGN KEY ( uid ) REFERENCES tb_user(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ( oid ) REFERENCES tb_order(oid) ON DELETE CASCADE ON UPDATE CASCADE
)
说明：把oid列 设为外键 参照外表tb_order的oid列 当外键的值删除 本表中对应的列删除 当外键的值改变 本表中对应的列值改变;把uid列 设为外键 参照外表tb_user的id列 当外键的值删除 本表中对应的列删除 当外键的值改变 本表中对应的列值改变。
优化手段
① SQL优化
避免 SELECT *，只查询需要的字段。 小表驱动大表，即小的数据集驱动大的数据集： 当B表的数据集比A表小时，用in优化
exist两表执行顺序是先查B表再查A表查询语句：SELECT * FROM tb_dept WHERE id in (SELECT id FROM tb_dept) ; 当A表的数据集比B表小时，用exist优化in ，两表执行顺序是先查A表，再查B表，查询语句：SELECT * FROM A WHERE EXISTS (SELECT id FROM B WHERE A.id = B.ID) ; 尽量使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表。
② 优化索引的使用
尽量使用主键查询，而非其他索引，因为主键查询不会触发回表查询。 不做列运算，把计算都放入各个业务系统实现
查询语句尽可能简单，大语句拆小语句，减少锁时间 or 查询改写成 union 查询 不用函数和触发器 避免 %xx
查询，可以使用：select * from t where reverse(f) like reverse(’%abc’); 少用 join
查询 使用同类型比较，比如 ‘123’ 和 ‘123’、123 和 123 尽量避免在 where 子句中使用 != 或者 <>
操作符，查询引用会放弃索引而进行全表扫描 列表数据使用分页查询，每页数据量不要太大 避免在索引列上使用 is null 和 is not null
③ 表结构设计优化
使用可以存下数据最小的数据类型。 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int。 尽可能使用
not null 定义字段，因为 null 占用 4 字节空间。数字可以默认 0 ，字符串默认 “” 尽量少用 text 类型，非用不可时最好独立出一张表。 尽量使用 timestamp，而非 datetime。 单表不要有太多字段，建议在 20 个字段以内。
Mysql常用数据类型存储大小及范围：https://blog.youkuaiyun.com/HXNLYW/article/details/100104768
3.如果以上优化还是有问题，可以使用show profiles 分析sql 性能
show profiles
show profile for query [queryId]
索引
作用：加快查询速度
分类：主键索引（primary），唯一索引（unique），普通索引，全文索引（fulltext），组合索引
实现原理：B+树（只有叶子结点存储真实数据，非叶子节点存储指针）。尽可能让查询在内存中进行，减少IO次数。
b+树的查找过程
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
建索引的几大原则
1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
