WINDOWS运行快捷键:ctrl+shift+enter
-- 表示 注释
ENTER 快速输入提示的文字
1.基本语句:按下列顺序
①select +* (选所有列
select +具体的列 (+AS 更改别名
②from +表名
③where(条件语句) +筛选信息 (文本+'', 数字不用加
④order by +排序依据
2.where 用法
and 同时满足条件
or 任意满足一个
where not 两者都不
where state = 'A' OR state = 'B' 可用IN表示 where state IN (A,B)
where points >= 1000 AND points <=3000 可用BETWEEN表示
where points BETWEEN 1000 AND 3000
WHERE ....LIKE (占位符 %代表任意字符 _一个字符
WHERE A IS NULL / IS NOT NULL
3.REGEXP用法
REGEXP 包含
^文本首端 $文本末端
' A|B|C' 代表OR
'[abc]e'代表ae或be 或ce
'[a-h]e'代表ae到he
4.order by用法
ORDER BY A 升序排列(DESC 降序, A可以是算式
ORDER BY A,B 多条件排序
SELECT A,B FROM tables
ORDER BY 1,2 (按选出的第一列,第二列排序
5.LIMIT用法
LIMIT 3 显示数据前3列
LIMIT 6,3 跳过前6个,显示7-9三个数据
6.INTER JOIN
SELECT A,B,C(两个表中都有的字段需要加前缀 table.A
FROM table1 O (表名可以缩写 AS省略
JOIN table2 C
ON O.id = C.id
跨数据库连接 (给当前没选中的数据库加前缀
自连接(table 要用别名区分
FROM table1 T
JOIN table1 E
多数据表连接
FROM table1 M
JOIN table2 N
ON M.1=N.1
JOIN table3 O
ON M.2=O.2
复合主键 (复合条件连接 AND连接多个条件
FROM table1 M
JOIN table2 N
ON M.1=N.1
AND M.2=N.2
7.外连接
LEFT JOIN (以FROM后的表格为主
RIGHT JOIN (以JOIN后的表格为主
多数据表外连接 (都用LEFT JOIN 比较清晰
外自连接
SELECT *
FROM table1 T
LEFT/RIGHT JOIN table1 E
8.USING用法
(适用于表内列的名称一致
FROM table1 M
JOIN table2 N
ON M.1=N.1
AND M.2=N.2
等于 USING (1,2)
9.交叉连接
笛卡尔积
FROM table1
CROSS JOIN table2
等于
FROM table1 , table2
10.联合表
同一/不同数据源表连结 (连结时 保证列数相同
SELECT A
FROM table1
UNION
SELECT A
FROM table1/table2
11.列属性
Datatype INT(11)整数11个
VARCHAR(50) 最多50个字符
CHAR(50) 50个字符
PK 主键列
NN not null
12.增加行
增加1行数据 (括号内逗号,分隔
INSTER INTO table1 (A,B,C)
VALUE ('A1','B2','C3')
增加多行数据 (括号外逗号,分隔
INSTER INTO table1 (A)
VALUE ('A1'),
('B2'),
('C3')
- 插入分层行 (子母表层级
INSERT INTO table子表
VALUES (LAST_INSERT_ID(),A,B)
13.创建新表
复制表
CREATE TABLE newtable AS
SELECT * FROM table1
复制表中部份
INSERT INTO newtable
SELECT *
FROM table1
WHERE 条件
14.更新行
更新单行
UPDATE table
SET payment_total = 10,payment_date = '2021-11-04'
WHERE invoice_id = 1
清除更新内容
UPDATE table
SET payment_total = DEFAULT,payment_date = NULL
WHERE invoice_id = 1
子查询
UPDATE table
SET payment_total = 10,payment_date = '2021-11-04'
WHERE invoice_id = (
SELECT invoice_id
FROM table1
WHERE name = 'A' )
如果 invoice_id 查询结果多个,用IN
15.删除行
DELETE FROM table
WHERE +条件 (可用子查询语句
16.计算函数
--计算时()前不能有空格
SELECT
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average ,
SUM(invoice_total) AS total, /SUM(invoice_total*2) AS total, (可加算式
COUNT(invoice_total) AS number /
COUNT(DISTINCT invoice_total) AS number (计算不同值
FROM invoices
WHERE +条件(限制条件
(只能计算非空值
COUNT (*) 计算所有值
17.分组数据
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM table
--JOIN table1 USING(...)
WHERE +条件
GROUP BY client_id / 多个分组依据时 GROUP BY A,B
ORDER BY total_sales DESC
- 分组后筛选数据
--WHERE 用于分组前,且条件可以是SELECT中没有的;
--HAVING 用于分组后,且条件只能从SELECT中选择
SELECT SUM(invoice_total) AS total_sales,
COUNT(invoice) AS number_of_invoices
FROM table
WHERE +条件
GROUP BY client_id
HAVING total_sales >500 AND number_of_invoices >5 /可用AND并列多个条件
- ROLLUP运算符求和
SELECT
pm.name AS payment_method,
...
GROUP BY pm.name WITH POLLUP
(用WITH POLLUP运算符时,不能在GROUP BY 中使用别名