一、数据库
- 数据库(DB):以易于访问格式存储的数据集合
- 分类:关系数据库(相互关联的表,可以使用SQL管理)和非关系数据库
- 数据库管理系统(DBMS)
二、SQL语法
USE 数据库名字;
- 查询【DML】:
(1)基本语法:
SELECT 项名字
FROM 表名字
WHERE 筛选条件
ORDER BY 项名字 LIMIT 排序后显示数量
——注释
① SELECT
i. 所有列 *
ii. 自定义想要的项及顺序
iii. 简单运算 age*(10+1) mod 2
iv. 重新命名 AS
v. 去重(所有项都一致才算重复) DISTINCT
【eg1】
SELECT *
FROM list
——从表list中选择所有记录
【eg2】
SELECT name, price, (price*(10+1) mod 2) AS ’new price’
FROM list
——从表list中选择name、price、(price*(10+1) mod 2)作为new price
【eg3】
SELECT DISTINCT name
FROM list
——从表list中选择不重复的名字
② FROM:
i. 别名 直接添加在表的名字后
【eg1】
SELECT *
FROM list l
③ WHERE:
i. 大小(先后)> >= < <= = != <>
ii. 某项为空 =NULL、!=NULL、IS NULL、IS NOT NULL;含NULL的运算结果是NULL
iii. 连接筛选条件AND / OR,且AND优先级高于OR
iv. 符合备选IN(数字,数字) / NOT IN(‘字符串’)和between…and …
v. 模糊查询like ’%或_’ 和regexp ‘^ $ | [] [-]’
【eg】
SELECT *
FROM products
WHERE points>3000
WHERE birdate>‘1990-01-01‘ OR points>1000 AND state=’VA’ ——birdate在1990.1.1以后或 积分超过1000且在VA ,AND优先级高于OR
WHERE state IN (‘VA’,’FL’,’GA’) ——state项值为VA或FL或GA’
WHERE state NOT IN (‘VA’,’FL’,’GA’)
WHERE points BETWEEN 1000 AND 3000 ——积分在1000-3000之间,左开右开
WHERE name LIKE ’%b%’/’%b’/’b% ’——名字含有b/以b结尾/以b开头
WHERE name LIKE ‘_____y’/’’b_y’ ——_占位,名字符合格式
WHERE name REGEXP ‘b[a-h]’ ——名字为b后接abcdefgh中任一字符
WHERE name REGEXP ‘[RU]B’ ——名字含有RB或UB
WHERE name REGEXP ‘ELKA|AMBUR’ ——名字含有ELKA或AMBUR
WHERE name REGEXP ‘EY$|ON$’ ——名字以EY或ON结尾
WHERE name REGEXP ‘^MY|SE’ ——名字以MY开头或含有SE
WHERE phone IS NULL
([^…]、*、+、{n}、{n,m}待整理)
④ ORDER BY:
i. 列名
ii. 列的序号
iii. 升序为默认,降序 DESC
iv. 可以用多个
【eg】
ORDER BY state, name DESC
ORDER BY 1,2
⑤ LIMIT:
i. 前多少的个数
ii. 最多显示全部数据不显示空
iii. 偏移量,个数
【eg】
LIMIT 3
LIMIT 300 ——如果没有300个数据,就有多少显示多少
LIMIT 6,3 ——偏移量为6,显示三个,即显示位次为6,7,8的记录
(2)联接:
① 内部联接(INNER)JOIN:
i. FROM 表1 JOIN 表2 ON 表1联接使用的列=表2联接使用的列
ii. FROM 表1 JOIN 表2 USING(相同的列名)
iii. 可用逻辑连接词联接多个匹配的列
iv. 可自我联接但要设置别名以供区分
v. 可多表联接
vi. 隐式写法:FROM两个表,WHERE联接条件
【eg1】
SELECT *
FROM orders
JOIN customers
ON orders.id=customers.id
JOIN list
USING(number)
【eg2】
SELECT id
FROM orders
JOIN customers ON orders.id=customers.id
——错误,两个表都有id,不知道要select哪个
SELECT o.id
FROM orders o
JOIN customers ON o.id=customers.id
——正确
【eg3】
SELECT e. employee id ,e.name, m.name as manager
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id ——自我链接设两个别名就可以了
JOIN employees m ON e.reports_to = m.employee_id AND e.name=m.name ——联合主键
【eg4】
SELECT *
FROM orders o,customers c
WHERE o.customers_id=c.customers_id
——隐式联接
② 外部联接(OUTER)JOIN:
i. FROM 等式左侧项所在表名(一般是含空值的)
LEFT/RIGHT JOIN 等式右侧表名 ON 表1联接使用的列=表2联接使用的列
ii. 尽量避免使用右联接,
【eg】
SELECT*
FROM orders o
LEFT JOIN employees ON e.reports_to=m.employees_id
③ 自然联接:
i. NATURAL JOIN不常用
④ 交叉联接:
i. CROSS JOIN 连接结果为笛卡尔积
ii. 隐式写法:FROM两个表,WHERE处没有联接条件
【eg1】
SELECT *
FROM size
CROSS JOIN color
【eg2】
SELECT *
FROM size, color
(3)联合查询结果:
UNION
相同类型才可以联合
2. 增加数据
(1)插入数据:
INSERT INTO 表名/列名
VALUES ()
【eg1】
INSERT INTO list
VALUES (default,’john’,’smith’,NULL,default) ——default
【eg2】
INSERT INTO product (name,qulity,prize)
VALUES (‘a’,’a’,1),(‘b’,’a’,3)
【eg2】
INSERT INTO oders(customer_id,order_date,status)
VALUES (1,’2019-01-01’,1);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,1,2.95),(LAT_INSERT_ID(),2,1,3.95)
——LAST_INSERT_ID()是最近一个 insert动作(中第一条信息)的主键值,只适用与自增主键
(2)复制表:
CREATE TABLE 复制后表的名字 AS
SELECT
FROM
WHERE
【eg1】
CREATE TABLE list AS
SELECT *
FROM orders
WHERE date<’2020-01-01’
【eg2】
CREATE TABLE copy AS
SELECT i.invoice_id, i,number, c.name=client
FROM invoice i
JOIN clients c USING (client_id)
WHERE status!=NULL
- 更新表中数据
UPDATE 表名
SET 项=更新后的值
WHERE 更新条件
【eg】
UPDATE orders
SET comments=’gold’
WHERE customer_ID IN (SELECT customer_ID FROM orders WHERE poins>3000)
- 删除表中数据
DELETE
FROM 表名
WHERE 删除条件
【eg】
DELETE FROM invoices
WHERE client_id=(SELECT id FROM clients WHERE name=’works’)
——删除invoices表中client_id值与表clients中名字为works的记录对应的数据