mysql常用sql

沐雨云楼 db/ mysql mysql
发布时间 :
字数:4.4k 阅读 : 评论:
  1. 1.准备工作
    1. 1-1.建库语句
    2. 1-2.建表语句
    3. 1-3.初始化数据
  2. 2.常用sql
    1. 2-1.建表
    2. 2-2.修改表
    3. 2-3.索引操作
    4. 2-4.其他
  3. 3.explain
    1. 3-1.介绍
    2. 3-2.示例
  4. 4.常用函数
    1. 4-1.日期
    2. 4-2.coalesce
  5. 5.链接

1.准备工作

模仿oracel的几张默认表，进行演示。所以下面先建表和导入数据。

1-1.建库语句

CREATE DATABASE IF NOT EXISTS iworkh_demo
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_general_ci;
-- 数据库编码格式
show variables like 'character_set_database';

-- 查看数据表的编码格式
show create table <表名>;

-- 修改数据库的编码格式
ALTER DATABASE <数据库名> character set utf8mb4;

1-2.建表语句

dept表

create table dept
(
    deptno int unsigned auto_increment primary key COMMENT '部门编号',
    dname  varchar(15) COMMENT '部门名称',
    loc    varchar(50) COMMENT '部门所在位置'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='部门表';

emp表

create table emp
(
    empno    int unsigned auto_increment primary key COMMENT '雇员编号',
    ename    varchar(15) COMMENT '雇员姓名',
    job      varchar(10) COMMENT '雇员职位',
    mgr      int unsigned COMMENT '雇员对应的领导的编号',
    hiredate date COMMENT '雇员的雇佣日期',
    sal      decimal(7, 2) COMMENT '雇员的基本工资',
    comm     decimal(7, 2) COMMENT '奖金',
    deptno   int unsigned COMMENT '所在部门',
    foreign key (deptno) references dept (deptno)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='雇员表';

salgrade表,工资等级表

create table salgrade
(
    grade int unsigned COMMENT '工资等级',
    losal int unsigned COMMENT '此等级的最低工资',
    hisal int unsigned COMMENT '此等级的最高工资'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='工资等级表';

bonus 表,工资表

create table bonus
(
    ename varchar(10) COMMENT '雇员姓名',
    job   varchar(9) COMMENT '雇员职位',
    sal   decimal(7, 2) COMMENT '雇员工资',
    comm  decimal(7, 2) COMMENT '雇员资金'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='工资表'

1-3.初始化数据

dept的初始化数据

INSERT INTO dept
VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept
VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept
VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept
VALUES (40, 'OPERATIONS', 'BOSTON');

emp的初始数据

INSERT INTO emp
VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-2-20', 1600, 300, 30);
INSERT INTO emp
VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
INSERT INTO emp
VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, NULL, 20);
INSERT INTO emp
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
INSERT INTO emp
VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
INSERT INTO emp
VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
INSERT INTO emp
VALUES (7788, 'SCOTT', 'ANALYST', 7566, '87-7-13', 3000, NULL, 20);
INSERT INTO emp
VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp
VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
INSERT INTO emp
VALUES (7876, 'ADAMS', 'CLERK', 7788, '87-7-13', 1100, NULL, 20);
INSERT INTO emp
VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
INSERT INTO emp
VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
INSERT INTO emp
VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);

salgrade 的初始数据

INSERT INTO salgrade
VALUES (1, 700, 1200);
INSERT INTO salgrade
VALUES (2, 1201, 1400);
INSERT INTO salgrade
VALUES (3, 1401, 2000);
INSERT INTO salgrade
VALUES (4, 2001, 3000);
INSERT INTO salgrade
VALUES (5, 3001, 9999);

2.常用sql

2-1.建表

建表并定义索引

CREATE TABLE projectfile
(
    id               INT AUTO_INCREMENT COMMENT '附件id',
    fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
    projectid        INT COMMENT '项目id;此列受project表中的id列约束',
    filename         VARCHAR(512) COMMENT '附件名',
    fileurl          VARCHAR(512) COMMENT '附件下载地址',
    filesize         BIGINT COMMENT '附件大小，单位Byte',
    -- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
    PRIMARY KEY (id),
    -- 主外键约束（注:project表中的id字段约束了此表中的projectid字段）
    FOREIGN KEY (projectid) REFERENCES project (id),
    -- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
    UNIQUE INDEX (projectid),
    -- 给fileuploadercode字段创建普通索引
    INDEX (fileuploadercode)
    -- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB
  DEFAULT CHARSET = utf8 COMMENT '项目附件表';

建表并定义组合索引

CREATE TABLE projectfile
(
    id               INT AUTO_INCREMENT COMMENT '附件id',
    fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
    projectid        INT COMMENT '项目id;此列受project表中的id列约束',
    filename         VARCHAR(512) COMMENT '附件名',
    fileurl          VARCHAR(512) COMMENT '附件下载地址',
    filesize         BIGINT COMMENT '附件大小，单位Byte',
    -- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
    PRIMARY KEY (id),
    -- 创建组合索引
    INDEX (fileuploadercode, projectid)
    -- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB
  DEFAULT CHARSET = utf8 COMMENT '项目附件表';

2-2.修改表

添加字段

ALTER TABLE order ADD code CHAR(6) DEFAULT NULL COMMENT '优惠码'

修改字段

-- 修改字段名
ALTER TABLE 表名 CHANGE 旧字段名  新字段名

-- 修改字段类型
ALTER TABLE 表名 MODIFY 字段名 字段类型(字段长度)

删除字段

ALTER TABLE 表名 DROP 字段名

2-3.索引操作

查询

SHOW INDEX FROM `table_name`;

删除

DROP INDEX `索引名` ON `表名`;

ALTER TABLE `表名` DROP INDEX `索引名`

添加索引

语法：

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法];

CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法];

示例：

-- 添加PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

-- 添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` );

-- 添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` );

CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`);

-- 添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`);

-- 添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );

CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`);

2-4.其他

-- 查询有哪些表
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'iworkh_demo';

3.explain

3-1.介绍

分析性能和对mysql优化时，常常会使用explain，来查看执行计划。

explain
SELECT ename, hiredate hiredate
FROM emp
WHERE empno = 7499;

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 SIMPLE emp NULL const PRIMARY PRIMARY 4 const 1 100 NULL
  • id：SELECT识别符。这是SELECT的查询序列号。
  • select_type：SELECT类型。
    • SIMPLE： 简单SELECT(不使用UNION或子查询)
    • PRIMARY： 最外面的SELECT
    • UNION：UNION中的第二个或后面的SELECT语句
    • DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
    • UNION RESULT：UNION的结果
    • SUBQUERY：子查询中的第一个SELECT
    • DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
    • DERIVED：导出表的SELECT(FROM子句的子查询)
  • table：表名
  • type：联接类型。是SQL性能的非常重要的一个指标，结果值从好到坏依次是：
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。一般来说，得保证查询至少达到range级别。
    • system：表仅有一行(=系统表)。这是const联接类型的一个特例。
    • const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
    • eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
    • ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
    • ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    • index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
    • unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
    • index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
    • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range
    • index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
    • all：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。
  • possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
  • key：key列显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
  • key_len：key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。
  • ref：ref列显示使用哪个列或常数与key一起从表中选择行。
  • rows：rows列显示MySQL认为它执行查询时必须检查的行数。
  • extra：该列包含MySQL解决查询的详细信息。
    • Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
    • Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
    • range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
    • Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
    • Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
    • Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
    • Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。
    • Using sort_union(…), Using union(…), Using intersect(…)：这些函数说明如何为index_merge联接类型合并索引扫描。
    • Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

3-2.示例

先查表上有哪些索引

show index from emp;

empno: primary key
deptno: index

示例1

explain SELECT * FROM emp WHERE empno = 7499;

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 SIMPLE emp NULL const PRIMARY PRIMARY 4 const 1 100 NULL

示例2

explain SELECT * FROM emp WHERE deptno = 20;

explain SELECT * FROM emp WHERE deptno in (20);

where条件字段是索引，使用=, type为ref，ref为const

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 SIMPLE emp NULL ref deptno deptno 5 const 5 100 NULL

示例3

explain SELECT * FROM emp WHERE deptno in (10, 20);

where条件字段是索引，使用in, type为range，extra为Using index condition

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 SIMPLE emp NULL range deptno deptno 5 NULL 8 100 Using index condition

示例4

explain SELECT * FROM emp WHERE deptno in (20, 30);

where条件字段是索引，使用in, type为ALL，extra为Using where.
这in的语句虽然跟上一个差不多，但是in (20, 30)比in (10, 20)的数据量多，优化器会任务全表扫描效率比通过索引快，所以type为ALL

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 SIMPLE emp NULL ALL deptno NULL NULL NULL 14 78.57 Using where

那如何让它命中索引呢？

force index

explain SELECT * FROM emp force index(deptno) WHERE deptno in (20, 30);

使用force index(deptno)强制性走索引

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 SIMPLE emp NULL ALL deptno NULL NULL NULL 14 78.57 Using where

union all

explain SELECT * FROM emp WHERE deptno=20 union all SELECT * FROM emp WHERE deptno=30;

两个union all时select_type为select_type和UNION
where条件字段是索引, type为ref，ref为const

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 PRIMARY emp NULL ref deptno deptno 5 const 5 100 NULL
2 UNION emp NULL ref deptno deptno 5 const 6 100 NULL

union

explain SELECT * FROM emp WHERE deptno=20 union SELECT * FROM emp WHERE deptno=30;

两个union all时select_type为select_type、UNION和UNION RESULT
where条件字段是索引, type为ref，ref为const

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 PRIMARY emp NULL ref deptno deptno 5 const 5 100 NULL
2 UNION emp NULL ref deptno deptno 5 const 6 100 NULL
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary

这注意下union和union all的区别。union all不会去重，而union会去重。

示例5

explain SELECT * FROM emp WHERE deptno > 20;

where条件字段是索引, type为range，extra为Using index condition

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 SIMPLE emp NULL range deptno deptno 5 NULL 6 100 Using index condition

示例6

explain SELECT * FROM emp WHERE sal > 2000;

where条件字段不是索引, type为ALL，extra为Using where

结果：

id select_type table partitions type possible_keys key key_len ref rows fitered extra
1 SIMPLE emp NULL ALL NULL NULL NULL NULL 14 33.33 Using where

4.常用函数

4-1.日期

日期相关常用函数示例

-- CURRENT_DATE 当前日期  2020-09-22
select CURRENT_DATE() from dual;

-- CURRENT_TIME 当前时间 10:16:00
select CURRENT_TIME() from dual;

-- CURRENT_TIMESTAMP 当前时间 2020-09-22 10:16:54
select CURRENT_TIMESTAMP() from dual;

-- NOW 当前时间 2020-09-22 10:16:54
select NOW() from dual;

-- DATE_ADD (两天前)
select DATE_ADD(NOW(), INTERVAL -2 DAY) from dual;

-- (两天后)
select DATE_ADD(NOW(), INTERVAL 2 DAY) from dual;

-- DATE_FORMAT 日期转化为`YYYY-MM-DD HH:MM:SS`格式
SELECT ename, DATE_FORMAT(hiredate, '%Y-%c-%d %h:%i:%s') hiredate FROM emp;
SELECT ename, DATE_FORMAT(hiredate, '%Y-%c-%d') hiredate FROM emp;

-- 复杂点的
select concat(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -2 DAY),'%Y-%m-%d') ,' 00:00:00'), concat(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -2 DAY),'%Y-%m-%d'),' 23:59:59' ) from dual;

4-2.coalesce

语法: COALESCE ( expression1, expression2, ... expression-n );

表示如果第一个不为空取第一个，否则判断下一个，以此类推，如果全部为空，则返回null值。

对于Oracle数据库，一般经常对空值处理的函数为NVL，而mysql中常用到的是ifnull，这两个函数相似，其实都是由一个函数衍生而来，那就是COALESCE()函数。

  • MYSQL: IFNULL(expression, value)
  • MSSQLServer: ISNULL(expression, value)
  • Oracle: NVL(expression,value)

在字段中使用

select ename , coalesce(comm,'没福利') as comm from emp;

等价于

SELECT ename
     , CASE
           WHEN comm IS NOT NULL THEN comm
           ELSE
               '没福利' END as comm
FROM emp;

结果:

comm
没福利
300.00
500.00

在条件中使用

在条件中最好不要使用COALESCE函数，使用AND,OR,IS NULL和括号方式来替换掉。

方式一

WHERE
    u.id = COALESCE(user_id, su.custom_id)

修改方案

WHERE
    (user_id IS NULL OR u.id = user_id) AND
    (su.custom_id = student_number)

方式二

WHERE
CASE 
    WHEN user_id IS NOT NULL AND LENGTH(user_id) > 0
    THEN
        u.id = user_id 
    ELSE
        su.custom_id = student_number
    END

修改方案

WHERE
    (user_id IS NOT NULL AND LENGTH(user_id) > 0 AND u.id = user_id)
    OR
    (su.custom_id = student_number)

5.链接

转载请注明来源，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 157162006@qq.com

赏

文章标题:mysql常用sql

字数:4.4k

本文作者:沐雨云楼

发布时间:2020-08-19, 21:30:00

最后更新:2020-10-06, 10:20:38

原始链接:https://iworkh.gitee.io/blog/2020/08/19/mysql-common-sql/

版权声明: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

©2020 iworkh

目录
×

喜欢就点赞,疼爱就打赏

pgmanor iworkh gitee