SQL基础之增、删、改、查

SQL: Structured Query Language

SELECT

查

• SELECT FROM

整张表查询

SELECT * FROM table_name;

查询某列/多列

SELECT column_1, column_2
FROM table_name

• SELECT DISTINCT FROM

SELECT DISTINCT column_1 
FROM table_name
;

SELECT DISTINCT column_1, column_2 
FROM table_name
;

• SELECT FORM WHERE

SELECT column_1, column_2
FROM table_name
WHERE column_1 = ''
;

WHERE 子句中的运算符

= 等于
<> 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=

大于
< 小于
= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式
IN 指定针对某个列的多个可能值

• SELECT FORM WHERE AND

SELECT column_1, column_2, column_3
FROM table_name
WHERE column_2 = 3
AND column_3 IS NOT NULL
;

• SELECT FORM WHERE OR

SELECT column_1, column_2, column_3
FROM table_name
WHERE column_2 = 3
OR column_2 = 1
;

• SELECT FORM WHERE AND OR

SELECT column_1, column_2, column_3
FROM table_name
WHERE column_2 = 3
AND (column_3 IS NOT NULL OR column_2 = 1)
;

• SELECT FROM WHERE LIKE

// 查询 neme 以 ‘X’ 开头的数据

SELECT FORM table_name 
WHERE name LIKE 'X%'

// 查询，name以‘X’结尾的数据

SELECT FORM table_name 
WHERE name NOT LIKE '%X'

// 查询，name中包含 ‘X’ 的所有数据

SELECT FORM table_name 
WHERE name NOT LIKE '%X%'

% 是 SQL 通配符。

• SELECT FROM ORDER BY

SELECT column_1, column_2, column_3, column_4
FROM table_name
WHERE (column_2 = 3 OR column_2 = 1)
AND column_3 IS NOT NULL
ORDER BY column_4 ASC|DESC
;

• SELECT FROM LIMIT

MySQL 的语法使用LIMIT来限制搜索数据条数

// 搜索前5条数据

SELECT * FROM table_name
LIMIT 5
;

INSERT

增

INSERT INTO table_name
VALUES (value1,value2,value3,...)
;

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...)
;

UPDATE

改

UPDATE table_name
SET column_x = 'xx', column_y = 'xx'
WHERE column_1 = '13'

@warning: UPDATE 语句使用需谨慎，初学者能不用切勿随意使用，毕竟会修改表里的数据。
建议使用UPDATE语句一定加上WHERE语句，否则可能会导致表里所有数据被修改。

DELETE

删

• DELETE FROM WHERE

// 条件删除

DELETE FROM table_name
WHERE (column_x IS NULL)
;

// 删除所有数据

DELETE FROM table_name

DELETE * FROM table_name

// 删除某行

DELETE FROM table_name
WHERE (column_x = xxx)
;

参考

[1] SQL 通配符: https://www.runoob.com/sql/sql-wildcards.html