秋招数据库学习3

20240415
周一摸鱼
20240416

1407.排名靠前的旅行者

select name ,ifnull(sum(distance),0) as travelled_distance
from users left join rides on users.id=rides.user_id
group by users.id
order by travelled_distance desc,name

1484.按日期分组销售

select  sell_date,count(distinct product) as num_sold,
    group_concat(distinct product order by product separator ',') as products
from Activities
group by sell_date 
order by sell_date asc

1517.查找拥有有效邮箱的用户

select user_id,name,mail
from users
where mail regexp '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';

^：这个符号表示字符串的开始。
[a-zA-Z]：这个部分匹配任何单个字母，无论大小写。
[a-zA-Z0-9_.-]：这个部分匹配任何数量（包括零个）的字母，数字，下划线，点或破折号。
						  星号表示前面的字符可以出现任意次数。
\@leetcode\.com：这个部分匹配"@leetcode.com"字符串。在这里，\用于转义特殊字符@和.，
 					使它们被视为普通字符而不是正则表达式的特殊符号。
$：这个符号表示字符串的结束。

1527.患某种疾病的患者

select patient_id,patient_name,conditions
from patients
where conditions regexp '\\bDIAB1.*'

select patient_id,patient_name,conditions
from patients
where concat(" ",conditions) like '% DIAB1%';

（1）数据定义（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
（2）数据操纵（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
（3）数据控制（DCL）包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
（4）嵌入式SQL的使用规定（TCL）涉及到SQL语句嵌入在宿主语言程序中使用的规则。 

1. 索引滥用会降低更新表的速度，更新表时，MySQL不仅要更新数据，保存数据，还要更新索引，保存索引，索引会占用磁盘空间。

All()：对所有数据都满足条件，整个条件才成立，>=all()等价于max，<=all()等价于min；
Any：只要有一条数据满足条件，整个条件成立，>any()等价于>min，<any()等价于<max；
some的作用和Any一样
3.count()函数有两种使用方式：1、使用count(*)对表中的行数进行计数，此种用法不忽略null值；2、使用count(column)对特定列中具有值的行进行计数，此种用法忽略null值。
4.视图是虚拟表，视图所引用的表称为视图的基表。

20240417

1683.无效的推文

对于SQL表，用于计算字符串中字符数的最佳函数是 CHAR_LENGTH(str)，它返回字符串 str 的长度。

另一个常用的函数 LENGTH(str) 在这个问题中也适用，因为列 content 只包含英文字符，没有特殊字符。否则，LENGTH() 可能会返回不同的结果，因为该函数返回字符串 str 的字节数，某些字符包含多于 1 个字节。

select tweet_id
from tweets
where char_length(content)>15

1693.每天的领导和合伙人

select date_id,make_name,count(distinct lead_id) as unique_leads,
        count(distinct partner_id) as unique_partners
from dailysales
group by date_id,make_name

1291.求关注者的数量

select  user_id,count( user_id) as followers_count
from followers
group by  user_id
order by  user_id

1050.合作过至少三次的演员和导演

select actor_id,director_id 
from ActorDirector
group by actor_id,director_id
having count(*) >=3

1. MySQL中ALTER TABLE命令可以修改数据表的表名或数据表的字段。但是接不同后缀意义不同，比如：
   要修改表名或索引名时，可以用RENAME函数；当然RENAME也可以更改列名，但是后面要加TO，且它只会更改列的名字，并不更改定义。
   要修改字段定义和名称，可以用MODIFY或CHANGE函数。但是MODIFY只改字段定义，不改名字；CHANGE是两个都可以修改。
   要修改字段默认值，可以用ALTER 字段名 SET DEFULT 更改值。

2.在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。
● 未授权读取（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。
● 授权读取（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
● 可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。
● 序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、虚读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

20240418

577.员工奖金

https://leetcode.cn/problems/employee-bonus/

select name,bonus
from Employee left join Bonus 
on Employee.EmpId=Bonus.EmpId
where bonus is null or bonus <1000

584.寻找用户推荐人

https://leetcode.cn/problems/find-customer-referee/

select name
from Customer
where referee_id <>2 or referee_id is null

586.订单最多的客户

https://leetcode.cn/problems/customer-placing-the-largest-number-of-orders/

select customer_number
from Orders
group by customer_number
Order by count(*) desc
limit 1; 

595.大的国家

select name,population,area
from world
where area>=3000000 or population>=25000000

1.为了保证视图是可更新的，其定义中不能包含以下语法结构：

集合操作符
DISTINCT 操作符
聚合函数或分析型函数
GROUP BY，ORDER BY，CONNECT BY，或 START WITH 字句
在 SELECT 之后的列表中使用集合表达式
在 SELECT 之后的列表中使用子查询
连接（join）（但是有例外情况）
对于不可更新的视图，可以利用 INSTEAD OF 触发器对其数据进行修改