数据库实践题

mysql安装
1、你需要先安装Mysql数据库，其实就是安装Mysql数据库服务器，然后设置环境变量path，在cmd.exe里查询查看环境变量参数的命令是：path
2、第二步就是连接Mysql服务器，命令如下：mysql -h localhost -u root -p
3、查看可用的databases，命令就是show databases; 必须有分号。
4、想用那个库都可以，只要输入命令use +要使用库就行，如use test，
查看有那些表show tables;
5、这个是我新安装的数据库，没有任何表，我现在就来新创建一个user_table表；
注意：命令行是以英文分号;为结束，只要没有分号，Mysql就认为命令没有输完，就不会执行命令。
create table ZBD (NAME VARCHAR(8),SEX VARCHAR(5) );
6、现在就表示进入到test库，并可以执行自己的sql脚本了，但是一般在开发中这样操作，效率很低的，我们可以借助客户端来操作数据库，高效有方便，如：

建表、插入、修改

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO runoob_tbl 
    (runoob_title, runoob_author, submission_date)
     VALUES
     ("学习 PHP", "菜鸟教程", NOW());

 select * from runoob_tbl;
 
SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';

UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

DELETE FROM runoob_tbl WHERE runoob_id=3;

SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM';

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

SELECT * from runoob_tbl ORDER BY submission_date ASC;

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;

多表查询

在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：记住区别
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

select a.firstName, a.lastName, b.city , b.state  from   Person a   left join Address b on  a.PersonId = b.PersonId;必须是on


SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

select 10 MOD 4;

SELECT DAY("2017-06-15");  

第二高

SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

第n高
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N-1;//只能设置
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
            salary
      FROM 
            employee
      GROUP BY //防止salary重复
            salary
      ORDER BY //从大到小
            salary DESC
      LIMIT N, 1 //从N开始获取一个
  );
END

原题中为需要求出获得s学生分数的排名，换个思路想，就是要求的有多少个不同的分数在当前s的前面。

子查询

子查询：

 
(SELECT count(DISTINCT Score) FROM Scores WHERE Score >= s.Score), 此时子查询会在FROM之后执行，子查询中先执行FROM，
后执行WHERE Score >= s.Score 获得由多少个分数在当前s学生的前面，最后通过count(DISTINCT Score)计算出有多少个不同的分数在s之前。
得到答案。最后ORDER BY Score DESC;进行排序。
对于a表中的每一个分数score，找出b表中有多少个大于或等于该分数的不同的分数，然后按降序排列
SELECT Score, 
    (SELECT count(DISTINCT Score) FROM Scores WHERE Score >= s.Score) AS 'Rank'
FROM Scores s
ORDER BY Score DESC;

成本最低min、BETWEEN

 SQL28 每个供应商成本最低的产品：min代表只有一个，id也就只有一个了。
select vend_id,min(prod_price) as cheapest_item from Products 
group by vend_id
order by cheapest_item 

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

总金额sum，INNER

SQL35 返回每个顾客不同订单的总金额
select b.cust_id ,sum(a.item_price*a.quantity) as total_ordered
from OrderItems a  INNER JOIN  Orders b //内连接，去掉注释
where a.order_num=b.order_num //必须给表起名字
group by b.cust_id
order by total_ordered desc
 
select os.cust_id,
(select sum(item_price*quantity) from OrderItems 
where a.Order_num=os.order_num) //where指定了sum(item_price*quantity)的条件，很关键，表示了计算的范围
as total_ordered 
from Orders os
order by total_ordered desc

on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。          根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。          在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。          如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。          在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里

join

SQL38 返回顾客名称和相关订单号以及每个订单的总价
 select cust_name,b.order_num,quantity*item_price OrderTotal 
 //sum(c.quantity*c.item_price) 不能用sum,SUM() 函数返回数值列的总数。本题是每个订单的总价
from Customers a
join Orders b on a.cust_id=b.cust_id  //条件分开写的
join OrderItems c on b.order_num=c.order_num
order by cust_name,order_num;



select a.cust_name,b.order_num,c.quantity*c.item_price as OrderTotal//字段别写错，要的数据，多行用sum，单行不用
from Customers a join  Orders b  join  OrderItems c
where a.cust_id=b.cust_id and b.order_num=c.order_num//条件这样合起来写也行
order by cust_name,OrderTotal
 


AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和


select cust_name, sum(item_price*quantity) as total_price 
from OrderItems  a join Orders b 
join Customers c
where b.cust_id=c.cust_id and a.order_num=b.order_num
group by cust_name//分组
having total_price>=1000//条件
order by total_price//排序




select cust_name, order_num
from  Orders b 
join Customers c
where b.cust_id=c.cust_id  
order by cust_name

UNION

将两个 SELECT 语句结合起来，以便从 OrderItems表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。
SELECT prod_id,quantity
FROM OrderItems
WHERE quantity=100
UNION //链接语句
SELECT prod_id,quantity
FROM OrderItems
WHERE prod_id LIKE"BNBG%"
 

LIMIT top

SELECT * FROM Websites LIMIT 2;
SELECT TOP 50 PERCENT * FROM Websites;50%

SQL 约束（Constraints）

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (Id_P)  //PRIMARY KEY约束
)

牛客1题

SELECT tag, SUM(if_retweet) as retweet_cnt,
    ROUND(SUM(if_retweet) / COUNT(if_retweet), 3) as retweet_rate
FROM tb_user_video_log a
JOIN tb_video_info b on a.video_id=b.video_id
WHERE DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(start_time)) <= 29
GROUP BY tag
ORDER BY retweet_rate DESC;

牛2

select t.author,t.month,
round(t.net_cnt/t.play_cnt,3) fans_growth_rate,
sum(net_cnt) over (PARTITION by t.author order by t.month) total_fans
FROM
(select i.author,DATE_FORMAT(u.start_time,'%Y-%m') month,
sum(case 
when if_follow=1 then 1
when if_follow=2 then -1
else 0 end) net_cnt,
count(i.author) play_cnt
from tb_video_info i
 join tb_user_video_log u
on i.video_id=u.video_id
where year(u.start_time)=2021
group by i.author,DATE_FORMAT(u.start_time,'%Y-%m'))t
order by t.author,total_fans;