表操作
创建数据库
CREATE DATABASE name;
删除数据库
DROP DATABASE name;
创建表
CREATE TABLE table_name (column_name, type);
插入数据
INSERT INTO table_name (column1, column2) VALUE (...),(...);
删除表
DROP TABLE table_name;
删除数据
DELETE FROM table_name WHERE ...;
删除所有数据，但保留表格
TRUNCATE TABLE table_name;
修改数据
UPDATE table_name SET column1=value1, column2 = value2 WHERE ...;
修改表
删除某列
ALTER TABLE table_name DROP column;
增加一列
ALTER TABLE table_name ADD column_name type;
修改字段类型
ALTER TABLE table_name MODIFY column1 new_type;
修改字段名字
ALTER TABLE table_name CHANGE column1 new_column1 new_type;
修改字段默认值
ALTER TABLE table_name ALTER column1 SET DEFAILT 1000;
表查询
查询所有数据
SELECT * FROM table_name;
条件查询
SELECT * FROM table_name WHERE conditions;
DISTINCT
SELECT DISTINCT * FROM table_name;
AS
SELECT s.class_id AS id FROM students s;
AS可以省略
AND
OR
NOT
LIMIT N
查询前N条数据
SELECT * FROM table_name LIMIT N:
投影查询
SELECT column1, column2 FROM table_name;
ORDER BY
SELECT column1, column2 FROM table_name WHERE conditios ORDER BY column3 DESC;
分页查询
LIMIT N OFFSET M
从M号记录开始最多打印N条
SELECT *FROM table_name ORDER BY column1 LIMIT 3 OFFSET 0;
聚合查询
COUNT
计算行数
SELECT COUNT(*) FROM table_name WHERE conditions;
SUM
AVG
MAX
MIN
分组
GROUP BY
SELECT COUNT(*) num FROM students GROUP BY class_id;
多列进行分组时
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id, gender;
HAVING
分组后的条件
SELECT class_id, gender COUNT(*) num FROM students GROUP BY class_id, gender HAVING gender = 'F';
多表查询
SELECT * FROM table_name1, table_name2;
两个表的乘积，这种查询称为笛卡尔查询
连接查询
RIGHT OUTER JOIN
SELECT c.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s RIGHT OUTER JOIN classes c ON s.class_id = c.id;
右表所有的行
FULL OUTER JOIN
两张表的所有记录全部选择出来
