mysql常用语句及关键字

一、常用sql语句

注意，关键字和函数最好大写，字段和表名小写，这样很容易区分。

1.创建数据库t_user
CREATE  DATABASE t_user;
2.删除数据库t_user
DROP DATABASE t_user;
3.使用数据库t_user：   USE t_user;
显示数据库中的表：  SHOW TABLES;

数据表命名

表名的命名规范为表名可以用 t_ 、tb_的前缀，或者是业务模块前缀。比如t_order。

有些项目也会使用 tt_、tm_、 ts_ 等前缀，根据项目的习惯命名就好了。

参考自：https://www.codetd.com/fr/article/13668245

 创建数据表

* mysql建表：

详情见： https://www.cnblogs.com/expiator/p/16542232.html

* 查看建表语句DDL：

SHOW CREATE TABLE 表名

 * 复制表结构： 新建表A，复制表B的结构：

create  table 表A like 表B

 * 找出Mysql表在哪个库： 

-- 找出Mysql表在哪个库
SELECT table_schema FROM information_schema.TABLES WHERE table_name = '表名字';

新增数据/更新数据

5.修改字段，使用 ALTER TABLE CHANGE ：

ALTER TABLE t_user_agent CHANGE agent_id  agent_number VARCHAR(25) COMMENT '备注说明'

注意：修改字段，记得加上COMMENT 备注。

5.1添加字段，使用 ALTER TABLE  ADD：

ALTER  TABLE  t_user_agent  ADD  merchant_id  VARCHAR(25); 

在指定位置(比如某一列的字段后面)添加字段，可以使用AFTER，如下：

ALTER  TABLE  t_user_agent  ADD  duction_date  DATE  COMMENT '税期'  AFTER  duction_month ;

注意：新增字段，如果字段是NOT NULL的，最好加上DEFAULT默认值，或者是UPDATE更新历史数据中新增字段的值。

因为历史数据是不存在这个新增字段的，查询做字段映射时会报错。

5.2 删除字段，使用  ALTER TABLE  DROP  COLUMN ：

ALTER TABLE t_user_agent   DROP COLUMN  user_id

6.删除数据表 t_sys_role
DROP TABLE  IF EXISTS   t_sys_role;
6.插入数据
INSERT  INTO  t_sys_role   VALUES(1,'0','管理员','admin');

7.查询数据
SELECT * FROM t_sys_role;
8.修改数据
UPDATE  t_sys_role  SET role='user' WHERE id=1;注意：使用UPDATE进行更新时，SET多个字段，使用逗号,隔开，而不是用AND。

正确的语句如下：

UPDATE  t_sys_user SET fname='lin' , fscore='88' WHERE fid='3'

如果使用以下sql，可能会出现问题。

UPDATE  t_sys_user SET fname='lin' AND fscore='88' WHERE fid='3'

9.删除数据
DELETE FROM t_sys_role  WHERE id=1;

注意：在使用DELETE和UPDATE之前，一定一定要先SELECT查询一下，将要删除的是哪些数据！！！

最好在查询后，还"导出数据"进行备份，然后再进行数据变更。

 二、建表关键字

0. constraints ：SQL 约束
约束用于限制加入表的数据的类型。
可以在创建表时规定约束(通过 CREATE TABLE 语句)，或者在表创建之后也可以(通过 ALTER TABLE 语句)。
比如，not null,unique,PRIMARY KEY,foreign key,default都属于约束
可以用CONSTRAINT 命名约束。如下，命名外键约束为fk_PerOrders。

CREATE TABLE t_orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)

1.PRIMARY KEY 主键
主键表示该列值在表中是唯一的，不可以有重复。
主键必须包含唯一的值。主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

主键的命名规范为pk_开头。

一般是在建表的时候指定了主键,就会创建主键索引。

主键索引:它是一种特殊的唯一索引，不允许有NULL值。

复合主键：就是指表的主键含有一个以上的字段组成。比如 primary key (name,age)

复合主键，只有在查询复合主键的第一个字段时，才会走主键索引。其他的字段，都是全表扫描。

联合主键：多个表的主键联合，对应另一张表的一个主键。

2.AUTO_INCREMENT自增
auto_increment 就是对主键自动增加编号的。
3.UNSIGNED无符号
unsigned是无符号的意思，代表该字段没有正负。
4.字段数据类型中，varchar和char的区别是什么？
varchar长度是可变的，能够节省空间。比如说存储"abc"，CHAR(10)需要10个字符的空间来储存，而VARCHAR(10)只需要3个字符的空间。
5.FOREIGN KEY  外键约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
如下示，Id_P是Orders表的外键，也是Persons表的主键。可以用CONSTRAINT 命名约束。命名外键约束为fk_PerOrders。

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)

6.UNIQUE 约束, 使数据库表中的某一列或某几列的里面的内容不重复

7. CREATE TABLE   SELECT  
根据查询结果创建表格
CREATE  TABLE  t_user2  (SELECT a.* FROM  user1  a  WHERE a.ID='SYSTEM')

8.复制表数据到另一个张

INSERT INTO t_user2 SELECT * FROM t_user;

9. 修改表名

ALTER TABLE t_user2 RENAME TO t_user;

三、查询关键字

0. IS NULL和IS NOT NULL。

不能使用 字段= NULL 或 字段!= NULL 在列中查找数据。可以使用以下格式：

WHERE 字段 IS NOT NULL

NULL值查询使用IS NULL和IS NOT NULL查询，而空值('')可以使用=或者!=、<、>、等算术运算符。

在实际开发中，建议使用空值。使用WHERE IS NULL不走索引。

Mysql里面NULL和空值的区别，详情见： https://www.cnblogs.com/lonelyxmas/p/10371120.html

0.  % 通配符，表示一个或多个字符
如下示，查询170开头的电话号码。
SELECT  order,age,sex FROM phone_info  WHERE  phoneNumber LIKE  "170%"
最好不要用左模糊，会因为扫全表而变慢。

1. AS  别名，其中 AS 可以省略
普通的表可以取简单的别名a,b,c。。而任意查询出来的结果，也可以通过取别名作为一个表来使用。
比如，以下将(SELECT * FROM user WHERE id=1  )的查询结果，通过取别名来使用
SELECT a.username FROM (SELECT * FROM user WHERE id=1  )  a;

字段，同样也可以通过AS取别名。
2.  JOIN   连接：用于根据两个或多个表中的列之间的关系，从这些表中查询数据。通过Inner JOIN  ...  On   实现。

JOIN其实就是把两(多)张表合起来，从大表里面查询的一个操作。
如下示，查找Persons中Id_P和Orders表中Id_P相同的数据。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

各种JOIN的区别：
JOIN: 如果表中有至少一个匹配，则返回行
INNER JOIN 与 JOIN 是相同的。
LEFT JOIN: 即便关联条件不符合，左边表的数据一定会存在合并后的大表中。
RIGHT JOIN: 即便关联条件不符合，右边表的数据一定会存在合并后的大表中。
FULL JOIN: 只要其中一个表中存在匹配，就返回行。

JOIN使用要点：小表驱动大表。

LEFT JOIN 左连接,左边为驱动表,右边为被驱动表.
RIGHT JOIN 右连接,右边为驱动表,左边为被驱动表.
INNER JOIN 内连接, mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表.

3. UNION  组合查询/联合查询
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 联合的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。
同时，每条 SELECT 语句中的列的顺序必须相同。
如下示：
SELECT Employee_Name FROM Employees_China
UNION   SELECT Employee_Name FROM Employees_USA
另外，UNION和UNION ALL区别：
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
4.SELECT INTO   查询并插入
SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

SELECT LastName,FirstName
INTO Persons_backup
FROM Persons

另外的，还有INSERT INTO SELECT 语句
从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
SQL INSERT INTO SELECT 。
我们可以从一个表中复制所有的列插入到另一个已存在的表中：
INSERT INTO TABLE2   SELECT * FROM TABLE1;

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

5.DISTINCT 只返回不同的值。
以下返回不同的userId，不会重复：
SELECT DISTINCT userId FROM user
DISTINCT支持单列、多列的去重方式。
单列去重的方式简明易懂，即相同值只保留1个。
多列的去重则是根据指定的去重的列信息来进行，即只有所有指定的列信息都相同，才会被认为是重复的信息。
多列去重如下 ：

SELECT    count( DISTINCT user_code, user_no )    FROM    t_user

6.LIMIT  分页查询。

LIMIT X,Y  序号从0开始数，其中的X表示从第几行开始，Y表示返回几条记录。
示例：每页5行，返回第2页的数据，即返回第6~10条记录。
SELECT user_name FROM  user LIMIT 5，5

示例：查找入职员工时间排名倒数第三的员工所有信息 。

思路：倒序，然后分页查找，找到从第序号为2的开始的一条记录，就是倒数第三。

SELECT * FROM  employees  ORDER BY hire_date DESC  LIMIT 2,1

注意：Mysql并没有TOP关键字。

7.子查询

子查询的结果，可以作为另一张表使用。

8.EXISTS  指定一个子查询，检测行是否存在。

用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

常用语法：not exists (子查询)

或者 not exists (select 1 from 表名)

select 1 from 中的1是一常量(可以为任意数值)，查到的所有行的值都是它，select 1效率比select * 高。

参考资料：https://blog.csdn.net/ahesihua/article/details/84275723

示例如下：

select c_insrnc_cde, c_nme_cn
   from t_prd_ins a
where a.c_prod_no = '01'
    and not exists
(select 1
           from (select d.c_insrnc_cde, a.c_nme_cn
                   from t_prd_ins                   a,
                        tb_bas_ply_policy_rdr       c,
                        tb_bas_ply_policy_rdr_list d
                  where a.c_insrnc_cde = d.c_insrnc_cde
                    and d.c_policy_id = c.c_policy_id
                    and d.c_seq_no = c.c_seq_no
                    and c.c_reach_area = '00701'
                    ) t
          where t.c_insrnc_cde = a.c_insrnc_cde);

9.查看表结构、查看字段的命令，如下:
desc 表名;
show columns from 表名;
describe 表名;
show create table 表名;

10.其他：IN()、OR()、LIKE

四、分组关键字

1.GROUP BY   分组
将查询结果按某一列或多列的值分组
如下示：

SELECT Cno，count(sno)
FROM course
GROUP BY Cno;

一张表，一旦分组完成后，查询后只能得到组相关的信息。可以通过count(),sum(),max(),min(),avg()统计。
--统计分组后各种情况的数量

SELECT  COUNT(1),age,sex  
FROM staff WHERE 1=1
and create_date>='2018-05-07 00:00:00'  
GROUP BY age,sex
ORDER BY age ASC

2.HAVING     分组筛选
可以对由SUM()或其它集合函数运算结果的输出进行限制。对分组添加限制条件。

当我们用到聚合函数，如sum，count后，又需要筛选条件时，可以用having，

因为WHERE是在聚合前筛选记录的，HAVING是过滤聚合函数返回的结果。

having后的判断字段必须是聚合函数返回的结果。having和group by是组合着用的。

如下示：

SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500

HAVING有多个条件，可以用 AND 连起来

SELECT store_name, MAX(sales)
FROM t_store_information
GROUP BY store_name
HAVING MAX(sales) > 100 AND MAX(sales) <200 

3.COUNT(column_name) 函数返回指定列的值的数目
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

COUNT(1) 比 COUNT(*)的速度更快。

4.CASE...  WHEN

类似于java中的switch()语句。

有两种表现形式：

CASE
  WHEN SEX = '1' THEN '男'
  WHEN SEX= '2' THEN '女'
ELSE '未知' END

或者是：

CASE sex
      WHEN '1' THEN '男'
      WHEN '2' THEN '女'
      ELSE '未知'
  END;

可用于查询中，也可以用于变更中。

示例如下，查询示例如下：

SELECT id,name,create_time,
    (CASE WHEN status = 0 THEN '失败'
          WHEN status = 1 THEN '成功'
          WHEN status = 2 THEN '等待中'
          ELSE '其它'  END) AS result
FROM t_order

变更示例如下，变更salary表中的性别：

UPDATE salary 
SET 
  sex=CASE sex
      WHEN 'm' THEN 'f'
      ELSE 'm'
  END;

示例2，将数字型的年月拼接起来，比如，将数字字段2019和2，拼接成一个日期2019-02，

CAST(fdeduction_month AS CHAR)表示将数字类型转化为字符类型。

UPDATE t_user_duction
SET duction_date=CONCAT(duction_year, '-',
         CASE  WHEN duction_month<10 
             THEN CONCAT('0',CAST(duction_month AS CHAR))
                ELSE CAST(duction_month AS CHAR)
                END    )

Case...When还可以作为COUNT的条件。
比如，count( CASE WHEN err_reason is  not null THEN 1 ELSE NULL END )表示如果err_reason不是空那么就计数1，用于计算err_reason不为空的数量。
示例：

SELECT  COUNT(*) as REQUEST_COUNT,
        COUNT( CASE WHEN err_reason is  not null THEN 1 ELSE NULL END ) as FAILURE_COUNT,
        COUNT( CASE WHEN success_reason is not null THEN 1 ELSE NULL END ) as SUCCESS_COUNT 
FROM  trace_201804 WHERE 1=1 
AND create_date>='2018-04-28 09:00:00' 
AND  create_date<'2018-04-28 09:30:00' 
GROUP BY order_day, order_code  
ORDER BY order_day ASC

四、字符串处理

1.CONCAT (Str1,Str2)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

示例如下：

SELECT name FROM user 
WHERE name LIKE CONCAT('%','ming','%')

2.GROUP_CONCAT(字段)：将一个或多个字段的多行数据，把所有值合并成一行。默认按逗号,分隔

比如emp_no字段有三行数据，分别为10007、10008、10010，那么GROUP_CONCAT(emp_no)的值为合并后的 10007,10008,10010

示例：按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

SELECT dept_no,GROUP_CONCAT(emp_no)  as employees
FROM dept_emp
GROUP BY dept_no

如果想使用其他的分隔符 ，可以使用GROUP_CONCAT(字段  separator '分隔符') 。

示例如下：

select dept_no,group_concat(emp_no separator ';') from dept_emp group by dept_no;

 3.REPLACE() 替换

4.SUBSTRING(str,from,to) 截取字符串

str为字符串，from表示起始下标，to表示末尾下标。from和to这两个数字都包含边界。比如以下sql，表示从第一个下标到第十的下标。

获取时间字符串的年月日：

-- 结果为2019-08-07
SUBSTRING('2019-08-07 17:34:57' ,1 , 10);

获取时间字符串的年月：

SUBSTRING('2019-08-07 17:34:57' ,1 , 7)

统计每个月的数据量：

SELECT SUBSTRING(create_time ,1 , 7) , COUNT(1) 
FROM tt_order
WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-06-30 23:59:59'
GROUP BY  SUBSTRING(create_time ,1 , 7);

5.LEFT()、RIGHT().

6.CAST(字段名 AS CHAR)表示将字段转化为字符类型。

CAST(字段名 AS DECIMAL(23,2)) 表示将字段转化为小数类型，共23位，小数为2位。如果字段是以科学计数法表示的，也可以通过CAST将科学计数法转化为小数。

7.TRIM(字符)会同时删除字符串前后出现的空白字符
LTRIM(字串): 将所有字串开头的空白字符移除。
RTRIM(字串): 将所有字串结尾的空白字符移除。

四、日期

1. NOW()：表示当前日期及时间

2. CURDATE()：表示当前日期，也就是今天

CURTIME() ：表示当时的时间(不包含日期)

3. DATE_SUB(CURDATE(), INTERVAL 1 DAY)   昨天

DATE_SUB(CURDATE(), INTERVAL 7 DAY)   七天前

DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY)   获取本月第一天

4. mysql 字符串转日期：   str_to_date('2008-4-2 15:3:28','%Y-%m-%d %H:%i:%s')

%H 两位数字形式的小时，24 小时制。

%h 两位数字形式的小时，12 小时制。

mysql的日期与字符串互相转换： https://www.cnblogs.com/gavinYang/p/11197875.html

5.TO_DAYS(Date date)：返回从0000年(公元1年)至日期参数date的总天数

SELECT  TO_DAYS(NOW());

SELECT  TO_DAYS(str_to_date('2023-05-01','%Y-%m-%d %H:%i:%s'));

四、INDEX索引

索引对于操作者，仅仅提供创建、删除和修改的功能。操作者在增删改查过程中，系统会自动调用索引。
可以在表中创建索引，以便更加快速高效地查询数据。

索引的命名规范为idx_开头。比如idx_name

1.CREATE  INDEX 添加索引。

CREATE  INDEX 索引名  ON  表名(字段名)
创建一个简单的索引，名为 idx_name，在 t_person 表的 last_name 列，如下示：
CREATE INDEX idx_name ON   t_person(last_name)

2.ADD  INDEX  添加索引：

ALTER TABLE 表名 ADD  INDEX 索引名 (字段名1[，字段名2 …]);
例子：  ALTER TABLE   t_employee  ADD  INDEX   idx_emp_name (name);

3.SHOW  INDEX  显示索引

例如：SHOW INDEX FROM t_person

4.DROP  INDEX   删除索引

ALTER TABLE  t_test  DROP  INDEX   idx_order ;

5.RENAME INDEX  重命名索引

将t_test表的order_id_2索引，重命名为 idx_order

ALTER TABLE t_test RENAME INDEX order_id_2 TO  idx_order

联合索引：

联合索引，是指将表上的多个列作为一个索引。

CREATE INDEX idx_user ON   t_person(last_name,first_name)
或者是：

ALTER TABLE   t_test  ADD  INDEX   idx_user  (last_name,first_name);

唯一索引：

唯一索引，是指多个列的组合只能唯一。

ALTER TABLE tb_person ADD UNIQUE KEY uk_user_id_status (user_id,status);

或者是：

CREATE UNIQUE KEY uk_user_id_status ON tb_person(user_id,status)
或者是：

ALTER TABLE t_person ADD CONSTRAINT uk_user_id_status UNIQUE (user_id,status)

五、EXPLAIN  执行计划

查看SELECT语句的执行计划

在Sql语句前面加一个EXPLAIN即可。

EXPLAIN结果，重点看key这个字段，key表示此次查询中确切使用到的索引.
其次看 type，有没有走索引。
还可以看下 rows，扫描了多少行，扫描的行越少越好。
如果extra 中，出现了 using filesort，也要注意，尽量避免。

详情见： Mysql性能优化EXPLAIN

六、其他

* mysql 生成 UUID;
SELECT UUID();
* mysql 生成 UUID，并去掉横杠 -
SELECT replace(UUID(),'-','');

* mysql 解决主从延迟。

核心的sql，可以强制走主库。比如订单的支付状态查询、退款状态查询。

/*FORCE_MASTER*/ SELECT * FROM table_name

附录、练习题目：

基础题:   牛客网Sql实战

进阶题：  sql语句练习50题