sql学习笔记

最新推荐文章于 2023-09-07 16:22:01 发布

天上飞的云传奇

最新推荐文章于 2023-09-07 16:22:01 发布

阅读量100

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_45862170/article/details/119044147

版权

数据库专栏收录该内容

23 篇文章 0 订阅

订阅专栏

sql学习笔记

sql

select

distinct 去除state字段的重复项

SELECT DISTINCT state FROM customers;

between a and b >=a <=b 包含临界值
WHERE name LIKE ‘b%’ 获取name值为b开头的任意长度的字符串不区分大小写 ‘%b%’字符串中间有b ‘_b’ 下划线代表一个字符 '__y’代表三个字符最后一个是y _代表一个任意字符
WHERE name REGEXP ‘filed’ ；包含filed的所有。 ‘^filed’以filed开头的所有 ‘filed$’ 以结尾的所有

WHERE name REGEXP ‘dile|filed’ 包含dile或者filed的所有

‘[gim]e’ 搜索的ge 或者 ie 或者me ‘[a-h]e’ 搜索 e前面含有a-h 的所有
IS NULL WHERE name IS NULL 获取名字为null的数据 IS NOT NULL

连接

INNER JOIN

SELECT * FROM atable a JOIN btable b ON a.id = b.id;

自连接同一张表

SELECT * from employee e JOIN employee m ON e.reports_to = m.employee_id;

多表连接

SELECT pa.payment_id,cl.`name`,pam.`name` AS 'payment method',date,amount FROM payments pa
JOIN clients cl 
ON pa.client_id = cl.client_id
JOIN payment_methods pam
ON pa.payment_method = pam.payment_method_id ;

外连接 LEFT JOIN RIGHT JOIN

LEFT JOIN 返回所有左表的结果就是from a 不管 on这个连接条件

多表外连接

SELECT ord.order_date,ord.order_id,cus.first_name,shi.`name` AS shipper,os.`name` AS status
FROM orders ord
JOIN customers cus ON ord.customer_id = cus.customer_id
LEFT JOIN shippers shi ON ord.shipper_id = shi.shipper_id
JOIN order_statuses os ON ord.`status` = os.order_status_id;

USING 如果是 ON a.name = b.name 可以使用 USING (name) 代替

UNION联合可以将两个select语句返回结果联合到一起

SELECT a.name , 'Active' AS status FROM atable a 
UNION
SELECT b.name , 'Archived' AS status FROM btable b ;

列属性增加更改

多列增加

INSERT INTO shippers (name) VALUES('haha'),('shipper1'),('shipper2')

表复制(存档) 不会保留主键信息不会自增

CREATE TABLE atable_archived AS SELECT * FROM atable

子查询插入数据

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

聚合函数

sum() avg() max() min() count(*)
group by dept 根据dept进行分组

having 过滤组可放于group by后面

SELECT pa.date,pm.`name` AS payment_method,sum(pa.amount) AS total_payment 
FROM payments pa
JOIN payment_methods pm 
ON pa.payment_method = pm.payment_method_id
GROUP BY pa.date , pm.`name`

WITH ROLLOP 使用在分组之后对数据进行汇总

复杂查询

All 关键字可以让where sum > ALL (110,112,203) 如果sum大于所有的数则返回在结果集中
ANY关键字和in一样 where id in (1,2,3) where id = ANY(1,2,3)

相关查询查询员工表中高于部门平均工资的人

SELECT * FROM employees e
WHERE salary >(
	SELECT AVG(salary) 
  FROM employees 
	WHERE office_id = e.office_id
)

EXISTS 运算符 where id in (子查询) 子查询返回一个结果列表然后判断id where exists (子查询) 不会返回一个大的结果列表而是直接一条一条进行判断，性能更好

子查询 SELECT

SELECT 
	client_id,
	name,
	(SELECT SUM(invoice_total)
	FROM invoices 
	where	client_id = cli.client_id) AS total_sales,
(SELECT AVG(invoice_total)FROM invoices ) AS average ,
(SELECT total_sales -  average) AS difference 
FROM clients cli

子查询写到form后可以将5作为一张表再次进行筛选

触发器

mysql触发器中， NEW关键字，和 MS SQL Server 中的 INSERTED 和 DELETED 类似，MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据。

具体地：
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用。

 DELIMITER %%

CREATE TRIGGER payments_after_delete
	AFTER DELETE ON payments
	FOR EACH ROW
BEGIN
	UPDATE invoices SET payment_total = payment_total - OLD.amount
	WHERE invoice_id = OLD.invoice_id;
END %%

DELIMITER ;

SHOW TRIGGERS 查看所有触发器

还可以用来审计，就是将某人对数据库的操作记录下来

事件

每隔多久执行一次，

DELIMITER %%

CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE 
	EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN

END %%

DELIMITER ;

SHOW EVENTS; 查看事件

drop event if exists

事务

ACID Atomicity 原子性 Consistency 一致性 isolation 隔离性 Durability 持久性
事务隔离级别四个级别和常见并发问题
1. 读未提交，读已提交，可重复读(确保不同的读取下返回同一个值) ，序列化
2. 脏读：事务A读到了事务B未提交的数据。解决：读已提交
3. 不可重复读：读取了相同数据俩次得到了不同的值。解决：可重复读
4. 幻读：事务a读取符合条件的人员表，其它事务刚好执行某个操作导致有一个人不满足条件，但执行完又满足。或者刚好当事务a读取之后又插入一条。解决：系列化，在事务a读取时其它对该表的操作都会被事务a知道

数据类型

string type
- char() 存储固定长度的字符串 char(10) mysql会保留30个字节因为字母1个字节中文3个字节。char(4) 最大为四个字符，检测到char值尾部空格会被删除，实际大小以固定，不到四个字符会填充空格。
- varchar 可变长度字符串最大 0到65535 . 实际占用空间大小为字符串实际长度+1(字符串结束符) 保存和查找时尾部空格被保留。
integers 整形
- TINYINT 1字节 -128-127数值状态
- UNSIGNED TINYINT [0,255] 年龄
- int 4b
定点数和浮点数
- DECIMAL(9,2) -->1234567.89
布尔型
- BOOL TRUE OR FALSE
TIME
- TIMESTAMP 4b 2038年之前的时间
- DATETIME 8b
BLOB
- 存储二进制数据
- tinyBlob 255b
- blob 65kb
- mediumblob 16mb
- longblob 4gb

数据库模型设计

概念模型 --》逻辑模型–》实体模型
第一范式：数据表的每一列都应该是单一的
第二范式：数据表的每一列都应该描述这个实体
第三范式：表中列不能是表中其它列的派生列

索引

EXPLAIN SELECT customer_id FROM customers WHERE state = 'CA';

CREATE INDEX idx_state ON customers (state);


EXPLAIN SELECT customer_id FROM customers WHERE points >1000;

CREATE INDEX idx_point ON customers (points);

前缀索引：只对字符串的前几个字符进行索引
全文索引：create fulltext index where Match(title,body) against('name haha ')

where Match(title,body) against('name -haha +body ‘’this a name‘’ ’ in boolean mode)有name或者body但是没有haha 准确包含this a name
复合索引：CREATE INDEX idx_state ON customers (state，points); 定位state有points

IN 和 EXISTS 区别

SELECT * FROM atable WHERE id in (SELECT id FROM btable)
SELECT * FROM atable a WHERE EXISTS (SELECT id FROM btable b WHERE a.id = b.id)
SELECT * FROM customers c WHERE 
EXISTS(SELECT 1 from orders o WHERE customer_id in (1,2,3) );

IN 会用a表的每一行id与b表所有id逐个比较，
EXISTS 用a表id 去执行类似SELECT 1 FROM btable WHERE a.id = b.id;这样只是执行a表length次
感觉就是用a表的每一行去执行括号内的子查询

IN 适合于外表大而内表小的情况；
EXISTS 适合于外表小而内表大的情况。

https://leetcode-cn.com/problems/swap-salary/comments/
update salary set sex = (case sex when ‘m’ then ‘f’ else ‘m’ end);
update salary set sex = if(sex = ‘m’,‘f’,‘m’)
case用法： case a when cond1 then exp1 else cond2 then exp2 else exp3
当a满足条件cond1时，返回exp1 当a满足条件cond2时，返回exp2 否则返回exp3

牛客https://www.nowcoder.com/practice/b9068bfe5df74276bd015b9729eec4bf?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1
-- rank排名：查询表中大于自己薪水的员工的数量（考虑并列：去重）
SELECT 
  s1.emp_no,
  s1.salary,
  (SELECT 
    COUNT(DISTINCT s2.salary) 
  FROM
    salaries s2 
  WHERE s2.to_date = '9999-01-01' 
    AND s2.salary >= s1.salary) AS `rank`  -- 去重：计算并列排名
FROM
  salaries s1 
WHERE s1.to_date = '9999-01-01' 
ORDER BY s1.salary DESC,
  s1.emp_no ;

SELECT 
    COUNT(DISTINCT s2.salary) 
  FROM
    salaries s2 
  WHERE  s2.salary >= s1.salary

评论区的解释
仔细区分代码中的s2、s1。比如，一个序列为4，3，2，1。如果要判断这个序列中每个数排在第几名（可以参考成绩排名），那么就看自己的前面有几个人，比如4，>=4的有1人，>=3的有2人，>=2的有3人，>=1的有4人。前面有几个人，count()在代码中表示筛选出几个人，就表示第几名。 distinct应该好理解，比如序列3，2，2，1，>=2的有3，2，2，加上distinct后，就剩下3，2，那么2在序列中排第二名。

天上飞的云传奇

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql学习笔记

sql学习笔记sqlselectdistinct 去除state字段的重复项SELECT DISTINCT state FROM customers; between a and b >=a <=b 包含临界值WHERE name LIKE ‘b%’ 获取name值为b开头的任意长度的字符串不区分大小写 ‘%b%’字符串中间有b ‘_b’ 下划线代表一个字符 '__y’代表三个字符最后一个是y _代表一个任意字符WHERE name REGEXP ‘f
复制链接

扫一扫