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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
| SELECT *
FROM employees;
SELECT *
FROM employees
LIMIT 100;
SELECT *
FROM employees
WHERE last_name='Facello';
SELECT *
FROM employees
WHERE last_name<>'Facello' and emp_no<10020;
SELECT last_name 姓,first_name 名
FROM employees
WHERE hire_date>'2000-01-01';
SELECT *
FROM employees t
WHERE t.gender='M' AND t.last_name='Pettey';
SELECT *
FROM employees t
WHERE t.gender='M' AND t.last_name='Pettey' or t.gender='M' AND t.last_name='Hutton';
SELECT DISTINCT (last_name)
FROM employees t
ORDER BY t.last_name DESC;
SELECT last_name,hire_date
FROM employees
WHERE hire_date>='2000'
ORDER BY hire_date DESC ,last_name ASC;
SELECT last_name,hire_date
FROM employees
WHERE last_name NOT IN ('Facello','Hutton');
SELECT last_name,hire_date
FROM employees
WHERE last_name IN ('Facello','Hutton');
SELECT last_name,hire_date
FROM employees
WHERE hire_date is NULL;
SELECT EXTRACT(YEAR FROM CURDATE());
SELECT last_name,EXTRACT(YEAR FROM CURDATE()),EXTRACT(YEAR FROM hire_date)
FROM employees
WHERE EXTRACT(YEAR FROM CURDATE())-EXTRACT(YEAR FROM hire_date)>20;
SELECT last_name,hire_date
FROM employees
WHERE hire_date<DATE_SUB(CURDATE(),INTERVAL 20 YEAR);
SELECT DISTINCT(emp_no)
FROM salaries
WHERE salary BETWEEN 20000 AND 50000;
SELECT last_name 姓,first_name 名
FROM employees
WHERE first_name LIKE 'b%';
SELECT last_name 姓,first_name 名
FROM employees
WHERE first_name REGEXP '^b';
SELECT last_name 姓,first_name 名
FROM employees
WHERE first_name LIKE '%nd';
SELECT last_name 姓,first_name 名
FROM employees
WHERE first_name REGEXP 'nd$';
SELECT last_name 姓,first_name 名
FROM employees
WHERE first_name LIKE '%we%';
SELECT last_name 姓,first_name 名
FROM employees
WHERE first_name REGEXP 'we';
SELECT last_name 姓,first_name 名
FROM employees
WHERE first_name LIKE '_____';
SELECT last_name 姓,first_name 名
FROM employees
WHERE LENGTH(first_name)=5;
SELECT last_name 姓,first_name 名
FROM employees
WHERE first_name REGEXP '^.....$';
SELECT COUNT(salary)
FROM salaries
WHERE emp_no=10001;
SELECT SUM(salary)
FROM salaries
WHERE emp_no=10001;
SELECT MAX(salary)
FROM salaries
WHERE emp_no=10001;
SELECT MIN(salary)
FROM salaries
WHERE emp_no=10001;
SELECT ROUND(AVG(salary),2)
FROM salaries
WHERE emp_no=10001;
SELECT MAX(hire_date),gender
FROM employees
GROUP BY gender;
SELECT emp_no,AVG(salary) 平均薪资,MAX(salary) 最高薪资,MIN(salary) 最低薪资
FROM salaries
GROUP BY emp_no;
SELECT emp_no
FROM dept_emp
GROUP BY emp_no
HAVING COUNT(emp_no)>1;
SELECT * FROM employees
WHERE emp_no in(
SELECT emp_no FROM dept_emp
GROUP BY emp_no
HAVING COUNT(emp_no)>1);
SELECT * FROM employees
WHERE emp_no=(
SELECT emp_no FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC LIMIT 1);
SELECT * FROM salaries
WHERE emp_no in(
SELECT emp_no FROM salaries
GROUP BY emp_no HAVING COUNT(emp_no)>=18);
SELECT a.emp_no,a.first_name,b.dept_no
FROM employees a,dept_emp b
WHERE a.emp_no=b.emp_no;
SELECT a.emp_no,a.first_name,b.dept_no,c.dept_name
FROM employees a,dept_emp b,departments c
WHERE a.emp_no=b.emp_no AND b.dept_no=c.dept_no;
SELECT a.emp_no,a.first_name
FROM employees a
INNER JOIN dept_emp b ON a.emp_no=b.emp_no
INNER JOIN departments c ON b.dept_no=c.dept_no
WHERE c.dept_name='Customer Service'
SELECT COUNT(a.emp_no)
FROM employees a
LEFT JOIN dept_emp b ON a.emp_no=b.emp_no
LEFT JOIN departments c ON b.dept_no=c.dept_no
WHERE c.dept_name='Customer Service'
SELECT COUNT(a.emp_no)
FROM employees a
RIGHT JOIN dept_emp b ON a.emp_no=b.emp_no
RIGHT JOIN departments c ON b.dept_no=c.dept_no
WHERE c.dept_name='Customer Service'
SELECT *
FROM departments UNION ALL
SELECT * FROM departments ORDER BY dept_no;
SELECT emp_no,MD5(salary) 薪水保密
FROM salaries WHERE to_date='9999-01-01'
ORDER BY salary ASC LIMIT 10;
SELECT emp_no,MD5(salary) 薪水保密
FROM salaries WHERE to_date='9999-01-01'
ORDER BY salary ASC LIMIT 10;
SELECT emp_no,MD5(salary) 薪水保密
FROM salaries WHERE to_date>CURDATE()
ORDER BY salary LIMIT 10;
UPDATE salaries SET salary=salary+3000
WHERE emp_no IN(
SELECT DISTINCT(emp_no) FROM dept_emp
WHERE EXTRACT(YEAR FROM from_date)=2002
AND to_date>CURDATE()
)
CREATE TABLE `testdata`(
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`payload` json DEFAULT NULL,
`response` json DEFAULT NULL,
PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
DESC testdata;
INSERT INTO testdata (payload,response)
VALUES('{"id":1,"name":"test"}','{"code":40000,"msg":"ok"}');
INSERT INTO testdata (payload,response)
VALUES(JSON_OBJECT("id",3,"name","dev1"),
JSON_ARRAY(1,3,5));
SELECT id,payload->'$.id',payload->'$.name',response->'$[0]',response->'$[2]' FROM testdata;
SELECT * FROM testdata WHERE payload->'$.name'='test';
存储过程
DROP TABLE IF EXISTS big_data;
CREATE TABLE big_data(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name1 VARCHAR(16) DEFAULT NULL,
age INT(11),
email VARCHAR(64) DEFAULT NULL
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
DROP PROCEDURE if exists insert_data_pro;
DELIMITER $$
CREATE
PROCEDURE insert_data_pro()
BEGIN
DECLARE i INT;
SET i=0;
WHILE i>=0 && i<= 10000 DO
INSERT INTO
big_data(name1,age,email)values(concat('test',i),rand()*50,concat('test',i,'@qq.com'));
SET i=i + 1;
END WHILE;
END$$
DELIMITER ;
call insert_data_pro();
ALTER TABLE big_data ENGINE=INNODB;
|
