李昕垚的博客

SQL基本语句

简单介绍SELECT,INSERT,UPDATE,DELETE四大基本语句

SELECT

SELECT和FROM

1
2
SELECT * FROM table;
SELECT <col> FROM dtable;

WHERE

WHERE是限定条件,查询更快

1
SELECT <col> FROM table WHERE <restrictive condition>;

WHERE子句运算符

  1. =, >, <, !=, >=等比较运算符
  2. NOT, AND, OR等逻辑运算符, 在WHERE后结合条件
  3. BETWEEN
  4. LIKE, %, _结合使用
  5. IN
  6. EXISTS

ORDER BY

1
2
3
4
SELECT <col>
FROM table
WHERE <condition>
ORDER BY <col1>, <col2> DESC;

GROUP BY和聚合函数

GROUP BY放在ORDER BY之前,常和SUM,MIN,MAX,COUNT一起使用,如查询salesID大于5的员工的销售量QTY,且按销售量排序,可写为

1
2
3
4
5
SELECT ID,COUNT(QTY)
FORM sales
WHERE ID > 5
GROUP BY ID
ORDER BY COUNT(QTY) DESC;

HAVING

HAVINGGROUP BY配合使用,在上例中加销量大于2000的条件

1
2
3
4
5
6
SELECT ID,COUNT(QTY)
FORM sales
WHERE ID > 5
GROUP BY ID
HAVING COUNT(QTY) > 2000
ORDER BY COUNT(QTY) DESC;

DISTINCT

表示去除重复项,假设销售表里ID不是主键,而且ID有重复,现在挑出不重复的ID

1
2
SELECT DISTINCT(ID)
FROM sales;

INSERT

1
2
3
4
INSERT INTO table
(col1,col2,...)
VALUES
(value1,value2,...)

UPDATE

UPDATE表示更新,常和WHERE一块使用

1
2
3
UPDATE table
SET Name = "lixinyao"
WHERE ID = 79110;

DELETE

删除ID为79110的记录

1
2
DELETE table
WHERE ID =79110;
请李昕垚吃个糖?