1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
| CREATE/DROP DATABASE/TABLE IF NOT EXISTS db/tab;
USE db;
SET SQL_SAFE_UPDATES = 0;
SHOW DATABASES/TABLES;
desc 表名;
CREATE TABLE student(
id INT primary key auto_increment,
NAME VARCHAR(20) unique,
age INT not NULL,
adress VARCHAR(10) constraint 外键名 foreign key (外键列名) reference 主表名(主表列名),
);
INSERT INTO student VALUES('1','马云','55','男', '杭州', '66', '78'),
('2', '柳青', '20', '女', '湖南', '86', NULL);
ALTER TABLE 表名 rename 新表名；
ALTER TABLE 表名 ADD/MODIFY 列名 数据类型；
ALTER TABLE 表名 CHANGE 列名 新列别 新数据类型；
ALTER TABLE 表名 DROP 列名;
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键列名) REFERENCES department 主表名(主表列名);
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
DELETE FROM student WHERE id = 9;
UPDATE student SET age = 99 WHERE id = 9;
DROP TABLE 表名；
SELECT
(DISTINCT) 字段列表 (as 别名)
FROM
表名列表
WHERE
条件列表
group by
分组字段
having
分组之后的条件
order by
排序字段 ASC/DESC
limit
分页限制
SELECT math, english, math + IFNULL(english, 0) [AS] 总分 FROM student;
SELECT * FROM student WHERE NAME LIKE '_化%';
SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT * FROM dept, emp;
SELECT emp.`name`,emp.`gender`,dept.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
SELECT * FROM emp [INNER] JOIN dept ON emp.`dept_id` = dept.`id`;
SELECT t1.*,t2.name FROM emp t1 LEFT [outer] JOIN dept t2 ON t1.`dept_id` = t2.`id`;
SELECT t1.*,t2.name FROM dept t2 RIGHT [outer] JOIN emp t1 ON t1.`dept_id` = t2.`id`;
SELECT * FROM emp WHERE emp.`salary` < (SELECT AVG(salary) FROM emp);
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '市场部' OR NAME = '财务部');
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.`id` = t2.dept_id;
select if(boolean testCondition, T valueTrue, T valueFalseOrNull) from user;
case when 条件 then 值
...
when 条件 then 值 else 0 end 字段名;
CONCAT(str1,str2,…)
CONCAT_WS(separator,str1,str2,...)
GROUP_CONCAT( [distinct] 要连接的字段 [group by 字段] [separator '分隔符'])
with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;
select id,score from union_test1 union [distinct] select id, score from union_test2;
jsonData = '{
"message":{
"location":[{"county":"浦东","city":"上海"},
{"county":"西直门","city":"北京"}]
} }'
select get_json_object(jsonData,'$.message.location[0].city') from test
|
