mysql笔记

最新推荐文章于 2023-05-14 13:06:54 发布

蓝桥杯加油！

最新推荐文章于 2023-05-14 13:06:54 发布

阅读量215

点赞数

分类专栏：学习笔记

本文链接：https://blog.csdn.net/weixin_45653889/article/details/105097524

版权

学习笔记专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

SQL笔记[课程地址](https://www.bilibili.com/video/BV1QJ411Q7pY?from=search&seid=5949822793188992560)

SQL笔记课程地址

1.The select statement

1.select clause

USE sql_store;
SELECT first_name,last_name,points,
	points + 10 AS 'discount factor'  必须有个tab空格
FROM customers
WHERE customer_id = 1
ORDER BY first_name

返回独一无二的

SELECT DISTINCT state
FROM customers

2.where clause

SELECT *
FROM customers
-- where  points > 3000
-- WHERE state != 'VA' -- <>
WHERE birth_date > '1990-01-01'

3.operator

and

SELECT *
FROM customers
WHERE  points > 3000 OR 
	(state != 'VA' AND birth_date > '1990-01-01')
NOT （）

and 的优先级高于or
in

SELECT *
FROM customers
WHERE  state IN ('VA','FL','GA') -- 也可以是数值

between and

SELECT *
FROM customers
WHERE  points BETWEEN 1000 AND 3000 
					BETWEEN '1990-01-01' AND '2000-01-01'

like

WHERE  last_name LIKE 'b%' -- 获得b开头的
                       %b%  -- 有b就行
                       %b   --b结尾
                       _y   -- 第二个必须是y
                       b___y
-- % any number of characters
-- _ one character

正则表达式

SELECT *
FROM customers
WHERE  last_name REGEXP 'field'
						^field -- 开头
                         field$ -- 结尾
                         'field|mac' -- logical or
                         '[gim]e'-- ge ie me
                         '[a-h]e'

null

SELECT *
FROM customers
WHERE  phone IS NULL -- IS NOT NULL

4.order by clause

SELECT *
FROM customers
ORDER BY state DESC,first_name DESC -- 也可以是是算术式子

SELECT first_name,last_name,10 AS points
FROM customers
ORDER BY 1 DESC,2 DESC

5. limit clause

SELECT *
FROM customers
LIMIT 6,3 -- 跳过编号为6 读取三个

2.inner join

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

不同数据库的连接

SELECT *
FROM order_items oi
JOIN sql_inventory.products p 
	ON oi.product_id = p.product_id

note : 查询根据当前依据的库不同儿显示哦
self join

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 mutiple tables

USE sql_store;

SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
JOIN order_statuses os
	ON o.status = os.order_status_id

compound join condidtion

SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
	AND o.status = os.order_status_id

implicit join

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

3.outer join

SELECT *
FROM orders o
RIGHT JOIN customers c
	ON o.customer_id = c.customer_id
ORDER BY c.customer_id

右连接显示右边的所有
join mutiple tables

SELECT *
FROM orders o
LEFT JOIN customers c
	ON o.customer_id = c.customer_id
LEFT JOIN shippers shippers
	ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

self join

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

USING

SELECT
	o.order_id,
	c.first_name,
	sh.name AS shipper
FROM orders o 
JOIN customers c
	--ON o.customer_id = c. customer_id
	USING (customer_id)
LEFT JOIN shippers
	USING (shipper_id)

	USING(customer_id,product_id)

natural join 一般不用

SELECT *
FROM orders o
NATURAL JOIN customer c

cross join

SELECT *
FROM customers c 
CROSS JOIN products p
ORDER BY c.first_name

隐式

SELECT *
FROM customers c ,products p
ORDER BY c.first_name

4.union

SELECT 
	order_id,
	order_date,
	'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION 
SELECT 
	order_id,
	order_date,
	'Achived' AS status
FROM orders
WHERE order_date < '2019-01-01'

5. insert,update,delete

insert

一条语句

INSERT INTO customers (first_name,last_name,birth_date,address,city,state) -- 加了这行后，后面的default可以省略
VALUES (DEFAULT, 'John','Smith',NULL,DEFAULT,'address','city','CA',DEFAULT)

多条语句

INSERT INTO customers (first_name,last_name,birth_date,address,city,state) -- 加了这行后，后面的default可以省略
VALUES (DEFAULT, 'John','Smith',NULL,DEFAULT,'address','city','CA',DEFAULT)
	   (                                                                  )
	   (                                                                  )

**插入不同表 hierarchical rows **

INSERT INTO oders (customer_id,order_date,status) -- 加了这行后，后面的default可以省略
VALUES (1,'2019-01-02',1);

SELECT last_insert_id()                                                               )

INSERT INTO orders (customer_id,order_date,status) -- 加了这行后，后面的default可以省略
VALUES (1,'2019-01-02',1);

INSERT INTO order_items
VALUES 
		(last_insert_id(),1,1,2.95),
		(last_insert_id(),2,1,2.15)

copy

CREATE TABLE orders_archived AS 
SELECT * FROM orders -- 会有缺失主键

INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

CREATE TABLE ... AS 
SELECT 
		...

update

update a row

UPDATE invoices
SET payment_total = 10,payment_date = null
WHERE invoice_id = 1

update rows

将它的 safe upgrade 取消勾选

UPDATE invoices
SET payment_total = 10,payment_date = null
WHERE client_id IN (3,4)

using subquerys

UPDATE invoices
SET payment_total = 10,payment_date = null
WHERE client_id =
			(SELECT client_id
			FROM clients
			WHERE name = 'Myworks')

delete

DELETE FROM invoices
WHERE invoice_id = 
		(SELECT *
		FROM clients
		WHERE name = "Myworks")

restore

点击file

6.aggregate

aggregate functions

note: 不想用大写了！！还是小写看的舒服！！！

select
	max(invoice_total) as highest,
    min(invoice_total) as lowest,
    avg(invoice_total) as average
    sum(invoice_total * 1.2) as total
    count(invoice_total) as count_of_invoices
    count(*) as total_records
    count(distinct client_id) as total_records
FROM invoices
WHERE invoice_date > '2019-07-01'

group by clause

select
	client_id,
	sum(invoice_total * 1.2) as total
FROM invoices
group by client_id  -- 先分组再排序哦
order by total desc

having clause

select
	client_id,
	sum(invoice_total * 1.2) as total
	count(*) as number
FROM invoices
group by client_id
having total > 500 and number > 5  -- 分组后的筛选哦

where 后可接不在select范围内的句子，having必须在其中

rollup operator(only for mysql

select
	client_id,
	sum(invoice_total * 1.2) as total
FROM invoices i 
group by client_id with rollup

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VYEc54pB-1585123506593)(C:\Users\pp\Pictures\3.png)]

select
	state,
    city,
	sum(invoice_total * 1.2) as total
FROM invoices i
join clients c using (client_id) 
group by state,city with rollup

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gBP2Oy8L-1585123506596)(C:\Users\pp\Pictures\4.png)]

7.complex queries

subqueries

select *
from products
where unit_price > (
	select unit_price
    from products
    where product_id =3
    )

in operator

select *
from products
where product_id not in (
	select distinct product_id
    from order_items
    )

subqueries vs join
all keyword

select *
from invoices
where invoice_total >(
	select max(invoice_total
	from invoices
	where client_id = 3
)

select *
from invoice
where invoice_total > (120, 130, 100...)
-- all 让它和后面的一列一列比较
select *
from invoices
where invoice_total > all (
	select invoice_total
	from invoices
	where client_id = 3
)

any keyword
correlated subqueries

select *
from employees e
where salary > (
	select avg(salary)
	from employees
	where office_id = e.office_id  -- 来自同一部门的比较！！like两个for循环
)

exists operator

select *
from products p
where not exists (
	select product_id 
	from order_items
	where product_id = p.product_id
)

subqueries in select clause

select
	invoice_id,
	invoice_total,
	(select avg(invoice_total)
		from invoices) as invoice_average,
	invoice_total - (select invoice_average) as difference
from invoices

subqueries in from clause

8.buit-in function

numeric

round() truncate() ceiling() floor() abs() rand()[0到1哦]

string

length() lower() upper() ltrim() rtrim() trim() left(‘hello’,2) right() substring(‘hello’,2,3) ——> ell

locate(‘n’,‘ren’) ——>3 [python:“ren”.index(“n”)]

replace() concat(first_name,’ ',last_name)

date

now() curdate() curttime() year(now()) dayname(now) extract(year from now())

format date and time

select date_format(now(),'%m %d %y')    '03 25 20'
select date_format(now(),'%M %D %Y')    March 25th 2020
select time_format(now(),'%H : %i %p')  '14 : 58 PM'

calculate date and time

select date_add(now(),interval 1 year)  
date_sub()
select datediff(now(),'2020-01-01')  -- 84
time_to_sec()

if null and coalesce

select
	order_id
	ifnull(shipper_id,'not assigned') as shipper
	coalesce(shipper_id,comments,'not assigned')
from orders

if
if(expression,first,second)

select 
	order_id
	if(
		year(order_date) = year(now()),
		'Active',
		'archived'
		) as category
from orders

case

select
	order_id,
	case 
		when ...    then 
		when ...    then 
		when ...    then 
		else 
	end as "hhhh"

9.view

create view 名字 as
select ...
...

drop view 名字

create or replace view 名字

(save your views in sql file)

with check option

蓝桥杯加油！

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql笔记

文章目录SQL笔记[课程地址](https://www.bilibili.com/video/BV1QJ411Q7pY?from=search&seid=5949822793188992560)1.The select statement1.select clause2.where clause3.operator4.order by clause5. limit clause2.inne...
复制链接

扫一扫