mysql对应语句关键字_mysql常用语句及关键字

一、常用sql语句

注意，关键字和函数最好大写，字段和表名小写，这样很容易区分。而且，表名用t_开头，字段最好用f开头。

1.创建数据库t_user

CREATE  DATABASE t_user;

2.删除数据库t_user

DROP DATABASE t_user;

3.使用数据库t_user

USE t_user;

显示数据库中的表

SHOW TABLES;

4.创建数据表 t_order

表名的命名规范为表名必须用 t_ 的前缀。比如t_order

CREATE TABLE IF NOT EXISTS t_order(

id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键,自增id',

order_id VARCHAR(25) NOT NULL UNIQUE COMMENT '订单号,唯一',

pay_status TINYINT(2) UNSIGNED DEFAULT '0' COMMENT '0：未支付,1：支付成功,2：支付失败,3：已下单,4：申请退款,5：退款成功,6：退款失败 ',

user_id INT(11) NOT NULL COMMENT '用户id' ,

total_price DECIMAL(25, 2) NOT NULL COMMENT '交易金额',

result text NULL COMMENT '订单的回调结果',

create_date DATE COMMENT '购买日期',

create_time DATETIME  COMMENT '购买时间',

INDEX(order_id,create_date)

)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

AUTO_INCREMENT 表示自增，UNSIGNED 表示无符号，UNIQUE 表示唯一约束，COMMENT为字段描述。DEFAULT表示默认值。NOT NULL表示不能为空。

查看建表语句DDL：

SHOW CREATE TABLE 表名

5.修改字段：

ALTER TABLE t_input_invoice_order CHANGE agent_id agent_number VARCHAR(25)

添加字段：

ALTER TABLE t_input_invoice_agent

ADD merchant_id VARCHAR(25);

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

ALTER TABLE t_input_invoice_deduction

ADD fdeduction_date DATE  COMMENT '抵扣税期'

AFTER fdeduction_month ;

删除字段：

ALTER TABLE t_input_invoice_agent   DROP COLUMN     merchant_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 TABLEt_orders

(

Id_Oint NOT NULL,

OrderNoint NOT NULL,

Id_Pint,PRIMARY KEY(Id_O),CONSTRAINT fk_PerOrders FOREIGN KEY(Id_P)REFERENCESPersons(Id_P)

)

1.PRIMARY KEY 主键

主键表示该列值在表中是唯一的，不可以有重复。

主键必须包含唯一的值。主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

主键的命名规范为pk_开头

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 TABLEOrders

(

Id_Oint NOT NULL,

OrderNoint NOT NULL,

Id_Pint,PRIMARY KEY(Id_O),CONSTRAINT fk_PerOrders FOREIGN KEY(Id_P)REFERENCESPersons(Id_P)

)

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

7. CREATE TABLE   SELECT

根据查询结果创建表格

CREATE  TABLE  user2  (SELECT a.* FROM  user1  a  WHERE a.ID='SYSTEM')

三、查询关键字

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相同的数据。

SELECTPersons.LastName, Persons.FirstName, Orders.OrderNoFROMPersonsINNER JOINOrdersON Persons.Id_P =Orders.Id_PORDER BY Persons.LastName

各种JOIN的区别：

JOIN: 如果表中有至少一个匹配，则返回行

INNER JOIN 与 JOIN 是相同的。

LEFT JOIN: 即便关联条件不符合，左边表的数据一定会存在合并后的大表中。

RIGHT JOIN: 即便关联条件不符合，右边表的数据一定会存在合并后的大表中。

FULL JOIN: 只要其中一个表中存在匹配，就返回行。

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 语句常用于创建表的备份复件或者用于对记录进行存档。

SELECTLastName,FirstNameINTOPersons_backupFROM Persons

另外的，还有INSERT INTO SELECT 语句

从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

SQL INSERT INTO SELECT 。

我们可以从一个表中复制所有的列插入到另一个已存在的表中：

INSERT INTO TABLE2   SELECT * FROM TABLE1;

INSERT INTOtable2

(column_name(s))SELECTcolumn_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

示例如下：

selectc_insrnc_cde, c_nme_cnfromt_prd_ins awhere a.c_prod_no = '01'

and not exists(select 1

from (selectd.c_insrnc_cde, a.c_nme_cnfromt_prd_ins a,

tb_bas_ply_policy_rdr c,

tb_bas_ply_policy_rdr_list dwhere a.c_insrnc_cde =d.c_insrnc_cdeand d.c_policy_id =c.c_policy_idand d.c_seq_no =c.c_seq_noand c.c_reach_area = '00701') twhere 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)FROMcourseGROUP BY Cno;

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

--统计分组后各种情况的数量

SELECT COUNT(*),age,sex FROM staff WHERE 1=1

and create_date>='2018-05-07 00:00:00'

GROUP BYage,sexORDER BY age ASC

2.HAVING     分组筛选

对由SUM()或其它集合函数运算结果的输出进行限制。可以对分组添加限制条件。如下示：

SELECT store_name, SUM(sales)FROMStore_InformationGROUP BYstore_nameHAVING SUM(sales) > 1500

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

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

4.CASE...  WHEN

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

有两种表现形式：

CASE

WHEN SEX = '1' THEN '男'

WHEN SEX= '2' THEN '女'

ELSE '未知' END

或者是：

CASEsexWHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '未知'

END;

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

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

SELECTid,name,create_time,

(CASE WHEN status = 0 THEN '失败'

WHEN status = 1 THEN '成功'

WHEN status = 2 THEN '等待中'

ELSE '其它' END) ASresultFROM 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)表示将数字类型转化为字符类型。

UPDATEt_input_invoice_deductionSET fdeduction_date=CONCAT(fdeduction_year, '-',CASE WHEN fdeduction_month<10

THENCONCAT('0',CAST(fdeduction_month AS CHAR))ELSE CAST(fdeduction_month AS CHAR)END )

Case...When还可以作为COUNT的条件。

比如，count( CASE WHEN err_str is  not null THEN 1 ELSE NULL END )表示如果err_str不是空那么就计数1，用于计算err_str不为空的数量

类似于编程语言中的if else。是判断语句

比如：

SELECT MOND_ID,INTERFACE_CODE,COUNT(*) as REQUEST_COUNT, count( CASE WHEN errstr is not null THEN 1 ELSE NULL END ) asFAILURE_COUNTFROM vbase.ESB_TRACE_20180428 WHERE 1=1

and create_date>='2018-04-28 09:00:00'

and create_date

and INTERFACE_CODE='Svc_ProvisionRet'

GROUP BYMOND_ID,INTERFACE_CODEORDER BY MOND_ID 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) asemployeesFROMdept_empGROUP 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，表示从第一个下标到第十的下标。

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

结果为2019-08-07

5.LEFT()、RIGHT().

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

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

7.TRIM(字符)会同时删除字符串前后出现的空白字符

LTRIM(字串): 将所有字串开头的空白字符移除。

RTRIM(字串): 将所有字串结尾的空白字符移除。

四、日期处理

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

CURDATE()：表示当前日期

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

四、INDEX索引

索引对于操作者，仅仅提供创建、删除和修改的功能。操作者在增删改查过程中，系统会自动调用索引。

可以在表中创建索引，以便更加快速高效地查询数据。

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

1.添加索引。

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

五、EXPLAIN  执行计划

查看SELECT语句的执行计划

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

六、练习题目：