「MySQL」- 学习 SQL 语法

  CREATED BY JENKINSBOT

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 TABLESPACE Syntax
ALTER VIEW Syntax

CREATE DATABASE Syntax
CREATE EVENT Syntax
CREATE FUNCTION Syntax
CREATE INDEX Syntax
CREATE LOGFILE GROUP Syntax
CREATE PROCEDURE and CREATE FUNCTION Syntax
CREATE SERVER Syntax
CREATE TABLE Syntax
CREATE TABLESPACE Syntax
CREATE TRIGGER Syntax
CREATE VIEW Syntax

DROP DATABASE Syntax
DROP EVENT Syntax
DROP FUNCTION Syntax
DROP INDEX Syntax
DROP LOGFILE GROUP Syntax
DROP PROCEDURE and DROP FUNCTION Syntax
DROP SERVER Syntax
DROP TABLE Syntax
DROP TABLESPACE Syntax
DROP TRIGGER Syntax
DROP VIEW Syntax

RENAME TABLE Syntax

TRUNCATE TABLE Syntax(清空表)

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

TRUNCATE [TABLE] tbl_name

Data Manipulation Statements

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

SELECT Syntax

Subquery Syntax
UPDATE 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

SET Syntax

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
SHOW CREATE DATABASE Syntax
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
SHOW DATABASES Syntax
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;

10、数据修改
update table_name set col_name = value where col_name = 11;

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

参考文献

SQL Statement Syntax

更新日志

06/23/2017 调整页面索引。