「MySQL」- 语法 | Statement

参考 | SQL Statement Syntax

Originally based upon relational algebra and tuple relational calculus, SQL consists of many types of statements, which may be informally classed as sublanguages, commonly: Data query Language (DQL), Data Definition Language (DDL), Data Control Language (DCL), and Data Manipulation Language (DML).

DQL | Data Query Language

Data query language – Wikipedia

DCL | Data Control Language

DDL | Data Definition Statements

ALTER DATABASE Syntax
ALTER EVENT Syntax
ALTER FUNCTION Syntax
ALTER INSTANCE Syntax
ALTER LOGFILE GROUP Syntax
ALTER PROCEDURE Syntax
ALTER SERVER Syntax

ALTER TABLE Syntax

ALTER TABLE Persons ADD PRIMARY KEY (ID);


SQL PRIMARY KEY Constraint

ALTER TABLESPACE Syntax
ALTER VIEW Syntax

Database (Schema)

TRUNCATE TABLE Syntax（清空表）

MySQL :: MySQL 8.0 Reference Manual :: 13.1.37 TRUNCATE TABLE Statement 

TRUNCATE [TABLE] tbl_name

DML | Data Manipulation Statements

CALL Syntax
DELETE Syntax
DO Syntax
HANDLER Syntax
LOAD DATA INFILE Syntax
LOAD XML Syntax

INSERT and REPLACE

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

REPLACE INTO `tablename` (`id`, `name`, `age`) VALUES (1, “A”, 19)

SELECT

https://dev.mysql.com/doc/refman/5.7/en/select.html

Although often considered part of DML, the SQL SELECT statement is strictly speaking an example of DQL. When adding FROM or WHERE data manipulators to the SELECT statement the statement is then considered part of the DML. ——
Data query language – Wikipedia

SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ‘, ‘) FROM peoples_hobbies GROUP BY person_id;


sql – Can I concatenate multiple MySQL rows into one field? – Stack Overflow

Subquery Syntax

Transactional and Locking Statements

START TRANSACTION, COMMIT, and ROLLBACK Syntax
Statements That Cannot Be Rolled Back
Statements That Cause an Implicit Commit
SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax
LOCK TABLES and UNLOCK TABLES Syntax
SET TRANSACTION Syntax
XA Transactions

Replication Statements

SQL Statements for Controlling Master Servers
SQL Statements for Controlling Slave Servers
SQL Statements for Controlling Group Replication

Prepared SQL Statement Syntax

PREPARE Syntax
EXECUTE Syntax
DEALLOCATE PREPARE Syntax

Compound-Statement Syntax

BEGIN … END Compound-Statement Syntax
Statement Label Syntax
DECLARE Syntax
Variables in Stored Programs
Flow Control Statements
Cursors
Condition Handling

数据库管理语句 | Database Administration Statements

Account Management Statements

Table Maintenance Statements

Plugin and User-Defined Function Statements

Other Administrative Statements

SHOW Syntax

SHOW 语句有许多形式用于提供有关服务器的数据库、表、列、状态信息的信息；
同时支持 LIKE 进行匹配查找，有几个语句还支持 WHERE 查找。相关内容可查看文档：Section 24.34, “Extensions to SHOW Statements”
许多 MySQL API（如 PHP）可以使 SHOW 语句返回的结果与 SELECT 中的结果集一样。有关内容可查看 MySQL 的文档：Chapter 27, Connectors and APIs
此外，可以使用 INFORMATION_SCHEMA 数据库中执行查询；

SHOW BINARY LOGS Syntax
SHOW BINLOG EVENTS Syntax
SHOW CHARACTER SET Syntax
SHOW COLLATION Syntax
SHOW COLUMNS Syntax
mysql
SHOW CREATE EVENT Syntax
SHOW CREATE FUNCTION Syntax
SHOW CREATE PROCEDURE Syntax
SHOW CREATE TABLE Syntax
SHOW CREATE TRIGGER Syntax
SHOW CREATE USER Syntax
SHOW CREATE VIEW Syntax
2 Functions and Operators
SHOW ENGINE Syntax
SHOW ENGINES Syntax
SHOW ERRORS Syntax
SHOW EVENTS Syntax
SHOW FUNCTION CODE Syntax
SHOW FUNCTION STATUS Syntax
SHOW GRANTS Syntax
SHOW INDEX Syntax
SHOW MASTER STATUS Syntax
SHOW PLUGINS Syntax
SHOW PRIVILEGES Syntax
SHOW PROCEDURE CODE Syntax
SHOW PROCEDURE STATUS Syntax
SHOW PROCESSLIST Syntax
SHOW PROFILE Syntax
SHOW PROFILES Syntax
SHOW RELAYLOG EVENTS Syntax
SHOW SLAVE HOSTS Syntax
SHOW SLAVE STATUS Syntax
SHOW STATUS Syntax
SHOW TABLE STATUS Syntax
SHOW TABLES Syntax
SHOW TRIGGERS Syntax
SHOW VARIABLES Syntax
SHOW WARNINGS Syntax

Utility Statements

DESCRIBE Syntax

EXPLAIN Syntax

HELP Syntax

USE Statement

MySQL :: MySQL 5.7 Reference Manual :: 13.8.4 USE Statement

USE db_name 语句告诉 MySQL 将名为 db_name 的数据库用作执行后续语句的默认数据库。MySQL 将一直保持默认数据库，直到会话结束或执行另一个 USE 语句： 


USE db1;
SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable;   # selects from db2.mytable

当使用了 USE 语句选择数据库之后，依旧可以访问其他数据库。如下示例（当选择 db1 时，访问 db2）： 


USE db1;
SELECT author_name,editor_name FROM author,db2.editor
  WHERE author.editor_id = db2.editor.editor_id;

其他

1、连接数据库
mysql -hlocalhost -u root -p

2、显示全部的数据库：
show databases;

3、选择数据库
use db_name;

4、创建数据库
create database db_name;

5、删除数据库
drop database db_name;

6、创建表
create table demo(
id int(6) unsigned zerofill not null default 0 comment ‘ID’,
name char(20) not null,
sex int(4) not null default ‘0’,
degree double(16,2)
);
以 id 列为例子：id int(6) unsigned zerofill not null default 0 comment ‘ID’
id:列名
int(6) : INT 类型；6 表示显示长度，只有使用了 zerofill 才能有作用，当值的长度不足 6 的时候，追添加前导 0 来显示；
unsigned : 表示为正整数，即不能小于 0；默认为 signed，
not null : 该列不能插入空值
default：默认值
comment：对列的注释
show create table demo;// 显示建表语句

7、删除表
drop table table_name;

8、清空表：
trancate table table_name;

8、数据插入
insert into table_name (`列名`) values（’1231’）;
9、数据删除
delete from table_name;

11、数据查询
select * from table_name
where col_name = 12;

12、联合查询
left join table_name on …
right join table_name on …
inner join table_name on …

13、
分组：group by
聚合：

COUNT()函数：用于统计记录的条数；

SUM()函数：用于计算字段的值的总和；

AVG()函数：用于计算字段的值的平均值；

MAX()函数：用于查询字段的最大值；

MIN()函数：用于查询字段的最小值；

14、排序
order by col_name
desc
asc

15、查询指定条数
limit m,n

16、数据筛选
where

关键字执行顺序（‘（）’中的数字代表执行顺序）
（8）SELECT（9）DISTINCT <select_list>
（1）FROM <left_table>
（3）<join_type> JOIN <right_table>
（2）ON <join_condition>
（4）WHERE <where_condition>
（5）GROUP BY <grout_by_list>
（6）WITH {CUTE|ROLLUP}
（7）HAVING <having_condition>
（10）ORDER BY <order_by_list>
（11）LIMIT <limit_number>

关于 WITH 和 HAVING
HAVING 用于对分组或聚合以后的数据进行筛选，区别与 where，where 是在分组或聚合之前执行的
WITH ROLLUP 跟在 group by 后面，对分组的数据进行求和；目前 MySQL 不支持 CUBE 操作
DEMO:
SELECT name,COUNT(age) FROM employee GROUP BY sex WITH ROLLUP;
name count(age) sex
Tom 14 2
Jack 12 2
NULL 26 4
Mary 15 1
Eric 19 1
NULL 34 2

CREATE INDEX

https://dev.mysql.com/doc/refman/5.7/en/create-index.html

Filed under: K4NZDROID - @ 9:10 PM