拜师之SQL（三）

1. 排序

SELECT * FROM wdz
ORDER BY id;

--或者使用字段的位置
SELECT * FROM wdz
ORDER BY 1;

SELECT * FROM wdz
ORDER BY age ASC, id DESC;

2. 较为复杂的更新和删除

UPDATE wdz SET age=age-3 WHERE id<=3;
UPDATE wdz SET age=age+3 ORDER BY age DESC LIMIT 3;
DELETE FROM wdz WHERE gender='male' ORDER BY age LIMIT 3;

3. 内连接查询

使用where语句

SELECT wdz.id, username, province_name FROM wdz, proname
WHERE wdz.proid=proname.id;

内连接语句

SELECT wdz.id, wdz.username, proname.province_name
FROM wdz
INNER JOIN proname
ON wdz.proid=proname.id;

4. 外链接

缺失值用NULL替代

SELECT wdz.id, wdz.username, proname.province_name
FROM wdz
LEFT JOIN proname
ON wdz.proid=proname.id;

5. 联合查询

SELECT name FROM wdz UNION SELECT name FROM wdz1;
SELECT name FROM wdz UNION ALL SELECT name FROM wdz1;

SELECT id, username FROM wdz WHERE proid IN (SELECT id FROM pro_name);

SELECT id, username FROM wdz WHERE score >= (SELECT level FROM scholarship WHERE id=1);

SELECT id, username FROM wdz WHERE EXISTS (SELECT * FROM pro_name);

SELECT id, username FROM wdz WHERE score >= SOME (SELECT level FROM scholarship WHERE id=1);

6. 正则表达式

SELECT * FROM wdz WHERE username REGEXP '^t';
SELECT * FROM wdz WHERE username REGEXP 'g$';