11 Sql语句汇总

曾经终败给现在 2023-03-06 08:03 2阅读 0赞

[sql操作练习题][sql]

### 数据库基本操作 ###

1.  进入数据库  
    打开cmd 输入mysql -uroot -p123 enter进入
2.  查看数据库仓库  
    show databases;
3.  选择某个数据库进入  
    use test;
4.  查看该数据库的表  
    show tables;
5.  查看该表在哪个数据库下面  
    select database();
6.  从某个数据库中查看表,注意此时还是处于test数据库中  
    show tables from mysql;
7.  查看某个表的信息  
    desc 表名;
8.  创建自己的数据库  
    create database bjpowernode;
9.  导入数据库脚本  
    Source D:\\course\\03-MySQL\\资料\\课程数据库脚本\\bjpowernode.sql
10. 退出  
    Exit

### 查询 ###

1.  查看某个表的某个字段，多个字段，全部字段(不推荐)

select ename from emp;
    select ename,sal from emp;
    select * from emp;

1.  查看某个表的某个字段，列乘以数字，列起别名

select ename,sal*12 from emp;
    select ename,sal*12 as yearsal from emp;(起个名字)

### 数据排序 ###

1.  按照某个字段排序，默认是asc升序，desc是降序，order by要放在最后

select ename,sal from emp order by sal;//升序
    select ename,sal from emp order by sal desc;//降序
    select ename,sal from emp order by ename,sal;//ename升序，sal升序
    select ename,sal from emp order by ename,sal desc;//ename升序，sal降序

### 条件筛选 ###

1.  单筛选条件

select ename,sal from emp where sal = 5000;
    select ename,job from emp where job=’MANAGER’;//标准
    select ename,job from emp where job=”MANAGER”;
    select ename,sal from emp where sal <> 5000;
    select ename,sal from emp where sal != 5000;

1.  NOT操作符用在where后面

select ename,sal from emp where not sal = 5000;

1.  多条件筛选

select ename,sal from emp where sal > 2000 and sal < 5000;
    select ename,sal from emp where sal >= 2000 and sal < =5000;
    select ename,sal from emp where sal between 2000 and 5000;
    select depno,ename,sal from emp where sal > 1800 and (depno = 20 or depno = 30);
    //注意:and比or优先级高,所以要加括号
    select ename,sal from emp where sal in (2000,5000);
    //in和or作用类似，查询sal是2000或者5000的，注意和between不同哦

1.  空值查询，(不能用=,只能用is)

select ename,comm from emp where comm is null;
    select ename,comm from emp where comm is not null;

1.  模糊查询，选择员工名字中含有s的,第二个字母为s的(%通配符,\_占位符)

select ename from emp where ename like ‘%s%’;
    select ename from emp where ename like ‘_s%’;

1.  注意 not和in，between，exist一起使用会很高效。

### 拼接计算字段 ###

1.  拼接两个字段并起别名

SELECT Concat(vend_name, ' (', vend_country, ')') 
    AS vend_title 
    FROM Vendors 
    ORDER BY vend_name;

1.  计算两个字段的乘积并起别名

SELECT prod_id, quantity, item_price, quantity*item_price 
    AS expanded_price 
    FROM OrderItems 
    WHERE order_num = 20008;

### 数据处理函数 ###

1.  大写函数

SELECT vend_name, UPPER(vend_name) 
    AS vend_name_upcase 
    FROM Vendors 
    ORDER BY vend_name;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzMzODUxOQ_size_16_color_FFFFFF_t_70]

### 单个聚合函数 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzMzODUxOQ_size_16_color_FFFFFF_t_70 1]

1.  AVG()函数，列的平均值

SELECT AVG(prod_price) AS avg_price 
    FROM Products 
    WHERE vend_id = 'DLL01';

1.  COUNT()函数，计算行的数目

SELECT COUNT(*) AS num_cust //表的行数，不忽略null
    FROM Customers;

SELECT COUNT(cust_email) AS num_cust //表中某列有值的行数，会忽略null
    FROM Customers;

1.  MAX()函数，列中最大值；MIN()函数，列中最小值

SELECT MAX(prod_price) AS max_price //忽略null
    FROM Products;
    SELECT MIN(prod_price) AS min_price //忽略null
    FROM Products;

1.  SUM()函数，列值的和

SELECT SUM(quantity) AS items_ordered //忽略null
    FROM OrderItems 
    WHERE order_num = 20005;
    
    SELECT SUM(item_price*quantity) AS total_price //忽略null
    FROM OrderItems 
    WHERE order_num = 20005;

注意：如果只需要聚集不同值，就使用`distinct`关键字，DISTINCT 不能用于 COUNT(\*)

### 组合聚合函数 ###

SELECT COUNT(*) AS num_items, 
    MIN(prod_price) AS price_min, 
    MAX(prod_price) AS price_max, 
    AVG(prod_price) AS price_avg 
    FROM Products;

### 数据分组 ###

1.  分组

SELECT vend_id, COUNT(*) AS num_prods 
    FROM Products 
    GROUP BY vend_id;

1.  过滤

SELECT cust_id, COUNT(*) AS orders 
    FROM Orders 
    GROUP BY cust_id 
    HAVING COUNT(*) >= 2;

1.  having和where一起用

SELECT vend_id, COUNT(*) AS num_prods 
    FROM Products 
    WHERE prod_price >= 4 
    GROUP BY vend_id 
    HAVING COUNT(*) >= 2;

注意：having用于组过滤，where用于行过滤，所以having通常与group by一起使用

1.  与order by一起用

SELECT order_num, COUNT(*) AS items 
    FROM OrderItems 
    GROUP BY order_num 
    HAVING COUNT(*) >= 3 
    ORDER BY items, order_num;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzMzODUxOQ_size_16_color_FFFFFF_t_70 2]

### 子查询 ###

1.利用嵌套子查询进行过滤

SELECT cust_id 
    FROM Orders 
    WHERE order_num IN (SELECT order_num
    					FROM OrderItems 
    					WHERE prod_id = 'RGAN01');

SELECT cust_name, cust_contact
    FROM Customers 
    WHERE cust_id IN (SELECT cust_id
    					FROM Orders 
    					WHERE order_num IN (SELECT order_num 
    										FROM OrderItems 
    										WHERE prod_id = 'RGAN01'));

### 联结 ###

1.  内联结 联结两个表

SELECT vend_name, prod_name, prod_price 
    FROM Vendors, Products 
    WHERE Vendors.vend_id = Products.vend_id;
    
    SELECT vend_name, prod_name, prod_price 
    FROM Vendors INNER JOIN Products 
    ON Vendors.vend_id = Products.vend_id;

1.  内联结 联结多个表

SELECT prod_name, vend_name, prod_price, quantity 
    FROM OrderItems, Products, Vendors 
    WHERE Products.vend_id = Vendors.vend_id
    AND OrderItems.prod_id = Products.prod_id 
    AND order_num = 20007;

1.  子查询和联结查询对比

SELECT cust_name, cust_contact 
    FROM Customers 
    WHERE cust_id IN (SELECT cust_id
    					FROM Orders 
    					WHERE order_num IN (SELECT order_num 
    										FROM OrderItems 
    										WHERE prod_id = 'RGAN01'));

SELECT cust_name, cust_contact 
    FROM Customers, Orders, OrderItems 
    WHERE Customers.cust_id = Orders.cust_id
    AND OrderItems.order_num = Orders.order_num 
    AND prod_id = 'RGAN01';

1.  给表起别名

SELECT cust_name, cust_contact 
    FROM Customers AS C, Orders AS O, OrderItems AS OI 
    WHERE C.cust_id = O.cust_id
    AND OI.order_num = O.order_num 
    AND prod_id = 'RGAN01';

1.  自联结，自联结比子查询要快

SELECT c1.cust_id, c1.cust_name, c1.cust_contact 
    FROM Customers AS c1, Customers AS c2 
    WHERE c1.cust_name = c2.cust_name 
    AND c2.cust_contact = 'Jim Jones';
    
    SELECT cust_id, cust_name, cust_contact 
    FROM Customers 
    WHERE cust_name = (SELECT cust_name
    					FROM Customers 
    					WHERE cust_contact = 'Jim Jones');

1.  外联结，包括没有关联行的行（分成左外联接和右外联结）

SELECT Customers.cust_id, Orders.order_num 
    FROM Customers LEFT OUTER JOIN Orders 
    ON Customers.cust_id = Orders.cust_id;
    //使用 LEFT OUTER JOIN 从 FROM 子句左边的表 （Customers 表）中选择所有行。
    //为了从右边的表中选择所有行，需要使 用 RIGHT OUTER JOIN

1.  使用带聚集函数的联结

SELECT Customers.cust_id, 
    		COUNT(Orders.order_num) AS num_ord 
    FROM Customers INNER JOIN Orders 
    ON Customers.cust_id = Orders.cust_id 
    GROUP BY Customers.cust_id;

### 组合查询 ###

1.  union组合两个查询，直接用union连接，用法很简单，会自动去重，用了union就不允许使用多条 ORDER BY 子句了。

SELECT cust_name, cust_contact, cust_email 
    FROM Customers 
    WHERE cust_state IN ('IL','IN','MI') 
    UNION 
    SELECT cust_name, cust_contact, cust_email 
    FROM Customers 
    WHERE cust_name = 'Fun4All';

### 插入数据 ###

1.  插入完整的行，推荐第二种方法

INSERT INTO Customers 
    VALUES('1000000006',
    		'Toy Land', 
    		'123 Any Street', 
    		'New York', 'NY', 
    		'11111', 
    		'USA', 
    		NULL, 
    		NULL);
    
    INSERT INTO Customers(cust_id,
    					cust_name, 
    					cust_address, 
    					cust_city, 
    					cust_state, 
    					cust_zip, 
    					cust_country, 
    					cust_contact, 
    					cust_email)
    VALUES('1000000006',
    		'Toy Land', 
    		'123 Any Street', 
    		'New York', 
    		'NY', 
    		'11111', 
    		'USA', 
    		NULL, 
    		NULL);

1.  从一个表复制到另外一个表

SELECT * 
    INTO CustCopy 
    FROM Customers;

### 更新数据 ###

UPDATE Customers 
    SET cust_contact = 'Sam Roberts', 
    	cust_email = 'sam@toyland.com' 
    WHERE cust_id = '1000000006';

### 删除数据 ###

DELETE FROM Customers 
    WHERE cust_id = '1000000006';

### 创建表 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzMzODUxOQ_size_16_color_FFFFFF_t_70 3]

[sql]: https://www.2cto.com/database/201807/757985.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzMzODUxOQ_size_16_color_FFFFFF_t_70]: /images/20230208/ade1819cd420430d835f5d77d6f69a1b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzMzODUxOQ_size_16_color_FFFFFF_t_70 1]: /images/20230208/b60a08634e154155a67b6e4a0caba97e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzMzODUxOQ_size_16_color_FFFFFF_t_70 2]: /images/20230208/c4ceaa8e752b47cf81a8e1db0b58df83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzMzODUxOQ_size_16_color_FFFFFF_t_70 3]: /images/20230208/3c098415a30a4ecd8a2214efe56425e3.png