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;

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

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降序

条件筛选

2. 单筛选条件

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;

3. NOT操作符用在where后面

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

4. 多条件筛选

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不同哦

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

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

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

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

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

拼接计算字段

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

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

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

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;

单个聚合函数

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

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

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

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

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

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

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

4. 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;

2. 过滤

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

3. 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一起使用

4. 与order by一起用

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

子查询

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;

2. 内联结 联结多个表

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;

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

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';

4. 给表起别名

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';

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

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');

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

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

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

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);

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

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';

创建表