技术成神

Appearance

Return to top
本页目录

3小时入门sql(基于mysql)

改变每一天/mysql练习 - Gitee.com

本教程是以该仓库与视频结合的笔记

01.select用法(输出语句)

sql
USE sql_store;

-- 返回客户的所有信息并且按第一个名字字母排序

SELECT * 
FROM customers
-- WHERE customer_id = 1
ORDER BY first_name;

-- 返回1,2两行数据

SELECT 1,2

image-20230710222820434

02.as 和distinct 用法 (功能语句)

sql
-- 返回客户的 最后一个名字 第一个名字 积分  折扣
SELECT 
	last_name,
	first_name,
	points,
	points+10 AS discount_factor
FROM customers;

-- 返回所有客户的地址

SELECT state
FROM customers;

-- 返回所有客户的地址,并消除重复行


SELECT DISTINCT state
FROM customers

AS 用作别名

DISTINCT用作消除重复行

03.select练习

sql
USE sql_store;
-- 用别名 返回所有产品的名称 单价 折扣价
SELECT 
	`name` , 
	unit_price , 
	unit_price*1.1 AS 'new price' 
FROM products

04.where用法 (判断语句)

sql
-- 积分大于3000的所有客户信息
SELECT *
FROM customers
WHERE points > 3000;	

-- 地区在va的所有客户信息
SELECT *
FROM customers
WHERE state = 'va';

-- 地区不在va的客户的所有信息
SELECT *
FROM customers
WHERE state <> 'va';

-- 在1990-01-01年后的客户信息
SELECT *
FROM customers
WHERE birth_date > '1990-01-01';


-- test:获取今年所有的订单
SELECT *
FROM orders
WHERE order_date > '2019';

05.and or not 用法(逻辑语句 : 并 且 非)

sql
-- 查询 客户是九零后或者 积分大于1000并且家在VA
-- AND 的优先级 比 OR高
SELECT *
FROM customers
WHERE birth_date > '1990' OR 
		(points > 1000 AND state = 'VA');

-- 取反的操作符
SELECT *
FROM customers
WHERE NOT (birth_date > '1999-01-01' OR points > 1000 );

-- 取反等价的
SELECT *
FROM customers
WHERE birth_date <= '1999-01-01' AND points <= 1000;

-- 练习 查询order_items表中 订单id为6的产品总价大于30的记录
SELECT * 
FROM order_items
WHERE order_id = 6 AND quantity*unit_price > 30;

06.in用法(逻辑语句 : 包含 离散值)

sql
-- 查询客户在VA或FL或GA
SELECT *
FROM customers
WHERE state = 'VA' OR state = 'FL' OR state = 'GA';

-- 查询客户在VA或FL或GA 用IN的写法
SELECT *
FROM customers
WHERE state IN ('VA','FL','GA');

-- 查询客户不在VA或FL或GA 用IN的写法
SELECT *
FROM customers
WHERE state NOT IN ('VA','FL','GA');

-- 练习:查询products表中库存数是49,38,72
SELECT * 
FROM products
WHERE quantity_in_stock IN (49,38,72);

07.between(逻辑语句 : 包含 连续值)

sql
-- 查询积分大于等于1000或者积分小于等于3000之间的客户
SELECT *
FROM customers 
WHERE points >= 1000 AND points <= 3000;

-- 用between等价上面的写法
SELECT *
FROM customers 
WHERE points BETWEEN 1000 AND 3000;

-- 练习 找出1999到2000出生的用户
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01';

08.like 和 % _ 用法(字符串 模糊匹配 占位匹配)

sql
-- 查询以姓名以b(不区分大小写)开头的客户
SELECT *
FROM customers
WHERE last_name LIKE 'b%';

-- 查询以姓名以y(不区分大小写)结尾的客户
SELECT *
FROM customers
WHERE last_name LIKE '%y';

-- 查询以姓名有6个字母,且最后是y的客户
SELECT *
FROM customers
WHERE last_name LIKE '_____y';

-- 练习 获取用户customers地址包含trail 或者 avenue 并电话号码是以9结尾的
SELECT *
FROM customers
WHERE (address LIKE '%trail%' OR address OR '%avenue%' )
      AND phone LIKE '%9';

-- 电话不是以9结尾的客户
SELECT *
FROM customers
WHERE phone NOT LIKE '%9';

09.REGEXP用法(字符串 正则匹配)

sql
-- 搜索姓名中带有field的客户
SELECT *
FROM customers
WHERE last_name LIKE '%field%';


-- 用正则来实现上述功能
SELECT *
FROM customers
WHERE last_name REGEXP 'field';

-- 姓名以field开头的客户
SELECT *
FROM customers
WHERE last_name REGEXP '^field';

-- 姓名以field结尾的客户
SELECT *
FROM customers
WHERE last_name REGEXP 'field$';


-- 姓名带有field或mac或rose的客户
SELECT *
FROM customers
WHERE last_name REGEXP 'field|mac|rose';

-- 姓名带以field开头或带有mac或rose的客户
SELECT *
FROM customers
WHERE last_name REGEXP '^field|mac|rose';

-- 姓名带有 ge ie me的客户
SELECT *
FROM customers
WHERE last_name REGEXP '[gim]e';

-- 姓名带有 eg ei em的客户
SELECT *
FROM customers
WHERE last_name REGEXP 'e[gim]';

-- 姓名带有 e且e前面包含a到h的任一字母的客户
SELECT *
FROM customers
WHERE last_name REGEXP '[abcdefgh]e';
-- 等价上述写法
SELECT *
FROM customers
WHERE last_name REGEXP '[a-h]e';

10.regexp练习

sql
-- 练习1:找出first_name名字是elka或者ambur的
SELECT *
FROM customers
WHERE first_name REGEXP 'elka|ambur';

-- 练习2:检索last_name名字是以ey或者on结尾的
SELECT *
FROM customers
WHERE last_name REGEXP 'ey$|on$';

-- 练习3:检索last_name名字是以my开头或者包含se的客户
SELECT *
FROM customers
WHERE last_name REGEXP '^my|se';

-- 练习4:获取last_name名字包含b紧跟r或者u
SELECT *
FROM customers
WHERE last_name REGEXP 'b[ru]';

11.is null用法(判空)

sql
-- 查询没有填写的电话的用户
SELECT *
FROM customers
WHERE phone IS NULL;

-- 查询填写的电话的用户
SELECT *
FROM customers
WHERE phone IS NOT NULL;

-- 练习:查询没有发货的订单
SELECT *
FROM orders
WHERE shipped_date IS NULL;

12.order by用法(排序)

sql
-- 默认排序是以id来排的,因为id是主键
SELECT *
FROM customers;

-- 以名称正序排序
SELECT *
FROM customers
ORDER BY first_name;

-- 以名称倒序排序
SELECT *
FROM customers
ORDER BY first_name DESC;

-- 以多列查询
-- 以名称和州名正序排序
SELECT *
FROM customers
ORDER BY first_name,state;

-- 以多列查询
-- 以名称和州名倒序排序
SELECT *
FROM customers
ORDER BY first_name DESC,state DESC;

-- 可以是不查询的列作为排序
SELECT first_name,last_name
FROM customers
ORDER BY birth_date;

-- 用别名进行排序
SELECT first_name,last_name,10 AS points
FROM customers
ORDER BY points,first_name;

-- 用选取列的位置进行排序
-- 下列的含义就是first_name和last_name
-- 尽量不要使用,如果选取的列就会改变排序顺序
SELECT first_name,last_name,10 AS points
FROM customers
ORDER BY 1,2;

-- 练习
SELECT order_id,product_id,quantity,unit_price,quantity*unit_price AS 'all price'
FROM order_items
WHERE order_id = 2
ORDER BY quantity*unit_price DESC;

-- 写法2
SELECT *,quantity*unit_price AS 'all price'
FROM order_items
WHERE order_id = 2
ORDER BY 'all price' DESC;

13.limit用法( 限制输出 与select配合)

sql
-- 选择头3个用户
SELECT *
FROM customers
LIMIT 3;

-- 偏移量
-- 分页,一页有3个用户
-- page1 1-3 page2 4-6 page3 7-9
-- 返回page3就需要偏移6位,每页3个
SELECT *
FROM customers
LIMIT 6,3;

-- 找出前3个积分最高的用户
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3;

-- 注意子句的顺序,select ,from ,where, order by,limit

14.join(inner join)用法( 多表查询)

sql
USE sql_store;
-- 多表查询
-- 一张表需要连接另一张表,之所以信息要分开写是因为如果要写在一张表中
-- 那如果更改了一个客户信息,那么所有的订单都要修改对应所有的信息,所以要分开写,连接靠id
-- 连接join + 连接的表名 on +连接的条件
-- INNER JOIN 和 JOIN 是一样的 
SELECT *
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id;

-- 多表输出指定的列
-- 不同的表的相同列需要带上表名
SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id;

-- 使用别名
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers
	ON o.customer_id = customers.customer_id;

-- 使用别名
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id;

-- 练习:订单order_items和产品表products
-- 注意用别名后只能表只能用别名了,其他的不行
SELECT o.order_id, o.quantity, o.unit_price, o.quantity*o.unit_price AS 'total_price',p.product_id,p.`name`,p.unit_price
FROM order_items o
JOIN products p
	ON o.product_id = p.product_id;

-- join可以跨库查询
SELECT *
FROM order_items o
JOIN sql_inventory.products p
	ON o.product_id = p.product_id;

-- join可以连接同一个表
-- 此处右边为null的自动删除
USE sql_hr;

SELECT 
	e.employee_id,
	e.first_name,
	m.first_name AS 'manager'
FROM employees e
JOIN employees m 
	ON e.reports_to = m.employee_id;

-- join 连接多个表

USE sql_store;

SELECT 
	o.order_id,
	o.order_date,
	c.first_name,
	c.last_name,
	os.`name` AS `status`
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
JOIN order_statuses os
	ON o.`status` = os.order_status_id;

-- 练习: join 连接多个表
USE sql_invoicing;

SELECT 
	p.`date`,
	c.`name`,
	p.invoice_id,
	p.amount,
	pm.`name` AS 'paymethod'
FROM payments p
JOIN clients c
	ON p.client_id = c.client_id
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id;

-- 连接带有复合主键的表
USE sql_store;

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_id
	AND oi.product_id = oin.product_id;

15.inner join on隐式写法(inner join on的简写)

sql
-- join连接表的隐式写法
-- 直接from多个表,在where中写连接条件

SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;

-- 如果没有写where的连接条件就是交叉表(笛卡尔积)
SELECT *
FROM orders o, customers c

16.外连接 right join和left join

mysql有两种连接方式 外连接和内连接

  • 内连接只返回复合连接条件的数据
  • 外连接无论符不符合都会返回

外连接有两种方式

  • right join 返回所有右边的表
  • left join 返回所有左边的表
sql
-- mysql有两种连接方式 外连接和内连接
-- 内连接只返回复合连接条件的数据
-- 外连接无论符不符合都会返回
-- 外连接有两种方式
-- 当是right join 返回所有右边的表
-- 当是left join 返回所有左边的表
USE sql_store;
-- 内连接
SELECT 
	c.customer_id,
	c.first_name,
	o.order_id
FROM customers c
JOIN orders o
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- 外连接
SELECT 
	c.customer_id,
	c.first_name,
	o.order_id
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- 练习:合并产品表和订单产品表
SELECT p.product_id,p.`name`,oi.quantity
FROM products p
LEFT JOIN order_items oi
	ON p.product_id = oi.product_id;

-- 多表外连接 外连接客户表 订单表 物流表
-- 少用右连接,保持连接一致性
SELECT 
	c.customer_id,
	c.first_name,
	o.order_id,
	sh.`name` AS shipper
FROM customers c
LEFT JOIN orders o
	ON c.customer_id=o.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id;


-- 练习: 订单表 order customers shippers order_statuses
SELECT 
	o.order_date,
	o.order_id,
	c.first_name,
	sh.`name` AS 'shipper',
	os.`name` AS 'status'
FROM orders o
JOIN customers c
	ON c.customer_id=o.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id
LEFT JOIN order_statuses os
	ON o.`status` = os.order_status_id
ORDER BY o.order_id;

-- 外连接同一张表
USE sql_hr;

SELECT 
	e.employee_id,
	e.first_name,
	m.first_name AS manager
FROM employees e
LEFT JOIN employees m
	ON e.reports_to = m.employee_id;

17.using子句(on的连接条件另一种写法)

sql
-- 使用on的连接条件会让sql看起来难懂
USE sql_store;

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_id AND
		oi.product_id = oin.product_id;

-- 使用using子句代替上述操作
SELECT *
FROM order_items oi
JOIN order_item_notes oin
	USING (order_id,product_id);

-- 简化
SELECT 
	o.order_id,
	c.first_name,
	sh.`name` AS shipper
FROM orders o
JOIN customers c
	USING (customer_id)
LEFT JOIN shippers sh
	USING (shipper_id);

-- 练习:
USE sql_invoicing;

SELECT 
	p.date,
	c.`name`,
	p.amount,
	pm.`name`
FROM payments p
JOIN clients c
	USING (client_id)
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id;

18. natural join(其他的连接类型:自然连接 自动识别连接条件,不用去写on连接条件)

sql
-- 数据库会自动识别相同的字段并连接
-- 不推荐这样写
SELECT 
	o.order_id,
	c.first_name
FROM orders o
NATURAL JOIN customers c

19.cross join(其他的连接类型:交叉连接 笛卡尔积)

sql
-- 交叉连接(笛卡尔积)
SELECT 
	c.first_name AS customer,
	p.`name` AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name;

-- 隐式交叉连接
SELECT 
	c.first_name AS customer,
	p.`name` AS product
FROM customers c,products p
ORDER BY c.first_name;

-- 练习 将shippers和products表进行显示连接和隐式连接
-- 写法1:隐式写法
SELECT *
FROM products p,shippers s
ORDER BY p.product_id;
-- 写法2:显示写法
SELECT *
FROM products p
CROSS JOIN shippers s
ORDER BY p.product_id

20.union 用法(聚合多个查询结果到一张表)

sql
-- UNION 增加表的行
-- 现在有两个查询
-- 查询1 有效订单
SELECT 
	order_id,
	order_date,
	'Active' AS 'status'
FROM orders
WHERE order_date >= '2019-01-01';

-- 查询2 归档订单
SELECT 
	order_id,
	order_date,
	'Archived' AS 'status'
FROM orders
WHERE order_date < '2019-01-01';

-- union合并两个查询到一张表上
SELECT 
	order_id,
	order_date,
	'Active' AS 'status'
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT 
	order_id,
	order_date,
	'Archived' AS 'status'
FROM orders
WHERE order_date < '2019-01-01';

-- 练习
SELECT 
	customer_id,
	first_name,
	points,
	'Gold' AS 'type'
FROM customers
WHERE points >=3000
UNION
SELECT 
	customer_id,
	first_name,
	points,
	'Sliver' AS 'type'
FROM customers
WHERE points >=2000 AND points < 3000
UNION
SELECT 
	customer_id,
	first_name,
	points,
	'Bronze' AS 'type'
FROM customers
WHERE points < 2000
ORDER BY first_name;

21.insert 用法(插入数据到表)

sql
-- 插入数据表
-- DEFAULT是让数据库填入默认值
USE sql_store;
INSERT INTO customers
VALUES (
	DEFAULT,
	'Lion',
	'Lee',
	'1999-01-01',
	NULL,
	'address',
	'city',
	'CA',
	DEFAULT
);

-- 插入方式2

INSERT INTO customers(
	first_name,
	last_name,
	birth_date,
	address,
	city,
	state
)
VALUES (
	'Lion2',
	'Lee',
	'1999-01-01',
	'address',
	'city',
	'CA'
);

-- 插入多条数据
INSERT INTO shippers (name)
VALUES ('Shipper1'),
				('Shipper2'),
				('Shipper3');

-- 练习:向产品表中添加3条产品记录
INSERT INTO products (
	`name`,
	quantity_in_stock,
	unit_price
)
VALUES ('drink',11,5.0),
('noodles',5,10.0),
('fruit',100,3.0);



-- 向关联表插入数据,
-- 添加订单记录以及订单中的产品
INSERT INTO orders(customer_id,order_date,`status`)
VALUES (1,'2019-01-02',1);
-- 最后插入成功数据的id
-- SELECT LAST_INSERT_ID(); 
INSERT INTO order_items
VALUES
	(LAST_INSERT_ID(),1,1,2.1),
	(LAST_INSERT_ID(),2,1,2.95)

22.create table as 用法(复制表)

sql
-- 复制一个表
CREATE TABLE orders_archived AS
SELECT * FROM orders;
-- 用这种方法复制的表,没有主键和自动增加属性

-- 复制一个表的部分数据
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01';

-- 练习:
USE sql_invoicing;
CREATE TABLE invoices_archived AS
SELECT 
	i.invoice_id,
	i.number,
	c.`name`,
	i.invoice_total,
	i.payment_total,
	i.invoice_date,
	i.due_date,
	i.payment_date
FROM invoices i
JOIN clients c
	USING(client_id)
WHERE i.payment_date IS NOT NULL;

23.update 用法(修改表)

sql
-- 更新一条数据
USE sql_invoicing;
SELECT * FROM sql_invoicing.invoices;

UPDATE invoices
SET payment_total = 10,
payment_date = '2019-03-01'
WHERE invoice_id = 1

-- 更新一条数据,使用默认值
UPDATE invoices
SET 
	payment_total = DEFAULT,
	payment_date = '2019-03-01'
WHERE invoice_id = 1;

-- 更新一条数据,插入对应的字段
USE sql_invoicing;
UPDATE invoices
SET 
	payment_total = invoice_total*0.5,
	payment_date = due_date
WHERE invoice_id = 3;

-- 更新多条数据
-- 只要条件满足多个就会自动更新
-- 更新全部把where去掉即可
UPDATE invoices
SET 
	payment_total = invoice_total*0.5,
	payment_date = due_date
WHERE client_id IN (3,4);

-- 练习
-- 给所有1990年以前的用户多加50积分
USE sql_store;
UPDATE customers
SET points = points+50
WHERE birth_date <'1990-01-01';

-- 通过子查询来更新数据
-- 在执行更新操作前,看一看子操作是什么
USE sql_invoicing;

UPDATE invoices
SET 
	payment_total = invoice_total*0.5,
	payment_date = due_date
WHERE client_id = (
	SELECT client_id
	FROM clients
	WHERE `name` = 'Myworks'
)

-- 有条件更新当前的表,直接加where
USE sql_invoicing;
UPDATE invoices
SET 
	payment_total = invoice_total*0.5,
	payment_date = due_date
WHERE payment_date IS NULL;

-- 练习:更新orders的积分大于3000的客户comments为金牌客户gold customers
USE sql_store;
UPDATE orders
SET 
	comments = 'Gold Customer'
WHERE customer_id IN 
(
	SELECT customer_id 
	FROM customers
	WHERE points >= 3000
)

24.delete 用法(删除表)

sql
-- delete 删除1行
-- 如果不写where则会删除表中所有的数据
USE sql_invoicing;
DELETE FROM invoices
WHERE invoice_id = 1;