sql操作练习题
数据库基本操作
- 进入数据库
打开cmd 输入mysql -uroot -p123 enter进入 - 查看数据库仓库
show databases; - 选择某个数据库进入
use test; - 查看该数据库的表
show tables; - 查看该表在哪个数据库下面
select database(); - 从某个数据库中查看表,注意此时还是处于test数据库中
show tables from mysql; - 查看某个表的信息
desc 表名; - 创建自己的数据库
create database bjpowernode; - 导入数据库脚本
Source D:\course\03-MySQL\资料\课程数据库脚本\bjpowernode.sql - 退出
Exit
查询
- 查看某个表的某个字段,多个字段,全部字段(不推荐)
select ename from emp;
select ename,sal from emp;
select * from emp;
- 查看某个表的某个字段,列乘以数字,列起别名
select ename,sal*12 from emp;
select ename,sal*12 as yearsal from emp;(起个名字)
数据排序
- 按照某个字段排序,默认是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降序
条件筛选
- 单筛选条件
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;
- NOT操作符用在where后面
select ename,sal from emp where not sal = 5000;
- 多条件筛选
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不同哦
- 空值查询,(不能用=,只能用is)
select ename,comm from emp where comm is null;
select ename,comm from emp where comm is not null;
- 模糊查询,选择员工名字中含有s的,第二个字母为s的(%通配符,_占位符)
select ename from emp where ename like ‘%s%’;
select ename from emp where ename like ‘_s%’;
- 注意 not和in,between,exist一起使用会很高效。
拼接计算字段
- 拼接两个字段并起别名
SELECT Concat(vend_name, ' (', vend_country, ')')
AS vend_title
FROM Vendors
ORDER BY vend_name;
- 计算两个字段的乘积并起别名
SELECT prod_id, quantity, item_price, quantity*item_price
AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
数据处理函数
- 大写函数
SELECT vend_name, UPPER(vend_name)
AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;
单个聚合函数
- AVG()函数,列的平均值
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
- COUNT()函数,计算行的数目
SELECT COUNT(*) AS num_cust //表的行数,不忽略null
FROM Customers;
SELECT COUNT(cust_email) AS num_cust //表中某列有值的行数,会忽略null
FROM Customers;
- MAX()函数,列中最大值;MIN()函数,列中最小值
SELECT MAX(prod_price) AS max_price //忽略null
FROM Products;
SELECT MIN(prod_price) AS min_price //忽略null
FROM Products;
- 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;
数据分组
- 分组
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
- 过滤
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
- 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一起使用
- 与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'));
联结
- 内联结 联结两个表
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;
- 内联结 联结多个表
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;
- 子查询和联结查询对比
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';
- 给表起别名
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';
- 自联结,自联结比子查询要快
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');
- 外联结,包括没有关联行的行(分成左外联接和右外联结)
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
- 使用带聚集函数的联结
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;
组合查询
- 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';
插入数据
- 插入完整的行,推荐第二种方法
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);
- 从一个表复制到另外一个表
SELECT *
INTO CustCopy
FROM Customers;
更新数据
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland'
WHERE cust_id = '1000000006';
删除数据
DELETE FROM Customers
WHERE cust_id = '1000000006';
创建表
更多推荐
11 Sql语句汇总
发布评论