1、创建表(DDL)
-
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]CREATE TABLE mytest(id INT UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR(30) NOT NULL,age TINYINT UNSIGNED NOT NULL,gender ENUM('F','M'));
-
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statementCREATE TABLE mytest1 SELECT * FROM mytest;
-
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }CREATE TABLE mytest2 LIKE mytest;
2、插入数据(DML)
-
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]INSERT students(Name,Age,Gender,ClassID,TeacherID) VALUE("Jrc",22,'M',NULL,NULL);
-
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]INSERT students SET Name="Zl",Age=23,Gender="F",ClassID=NULL,TeacherID=NULL;
-
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
3、删除数据(DML)
-
Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]DELETE FROM students WHERE Name='Jrc';
-
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.] [, tbl_name[.]] ...
FROM table_references
[WHERE where_condition]DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.] [, tbl_name[.]] ...
FROM table_references
[WHERE where_condition]
4、更新数据(DML)
-
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]UPDATE students set Name='Jrc',Age=22,Gender='M' WHERE Name='Zl';
-
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
5、查询数据(DML)
- 简单查询
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
SELECT语句的执行流程:
FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> SELECT --LIMIT
DISTINCT:数据去重;
SQL_CACHE:指定存储查询结果与缓存中;
SQL_NO_CACHE:查询结果不予缓存;
query_cache_type值为ON时,查询缓存功能打开,SELECT的结果符合缓存即会缓存;
query_cache_tyoe值为DEMAND时,指定SQL_CACHE的SELECT语句才会缓存;
字段显示可以显示别名:
col1 AS alias1,col2 AS alias2,......
-
WHERE子句:指明过滤条件以实现”选择“的功能;
过滤条件:布尔型表达式;
算术操作符:+,-,*,、,%
比较操作符:=,!=,< , >
BETWEEN min_num AND max_num
IN (element1,element2,...)
IS NULL
IS NOT NULL
LIKE:
%:任意长度的任意字符;
_:任意单个字符;
RLIKE
REGEXP
逻辑操作符:
NOT
AND
OR
XOR -
GROUP BY:根据指定的条件把查询结果进行“分组”以用于做“聚合”运算;
avg(),max(),min(),count(),sum()
HAVING:对分组聚合运算后的结果做指定条件过滤;SELECT ClassID,count(StuID) AS NOS FROM students GROUP BY ClassID HAVING NOS>=3;
-
ORDER BY:根据指定的字段对查询结果进行排序;
升序:ASC
降序:DESC -
LIMIT [[offset,]row_count]]:对查询的结果进行输出行数限制;
SELECT * FROM students LIMIT 10,3;
对查询结果中的数据请求施加锁:
FOR UPDATE:写锁,排他锁;
LOCK IN SHARE MODE:读锁,共享锁;
-
多表查询
交叉连接:笛卡尔乘积
内连接:
等值连接:让表之间的字段以“等值”建立连接关系;
不等值连接
自然连接
自连接
外连接:
左外连接:
FROM tb1 LEFT JOIN tb2 ON tb1.col1=tb2.col
FROM tb1 RIGHT JOIN tb2 ON tb1.col1=tb2.colSELECT students.Name,classes.Class FROM classes,students WHERE classes.ClassID=students.ClassID;
-
子查询:在查询语句嵌套着查询语句
基于某语句的查询结果再次进行查询
用在WHERE子句中的子查询:
(1)用于比较表达式中的子查询,子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
(2)用于IN中的子查询,子查询应该单键查询并返回一个或多个值从而构成列表;
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
(3)用于EXISTS
用于FROM子句中的子查询
-
联合查询:UNION
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
网友评论