sql语句练习 - 增删改查
sql语句练习 - 增删改查
目录
一、基础功能
二、聚合函数 （单表查询
三、 多表查询，表联结
四、 查询练习＆新增行语句
五、创建表
六、删除表
七、对表结构的修改语句
八、虚拟表
九、存储过程
十、其他补充
一、基础功能
1.基本数值运算
select 6*6 ; -- 计算器;SELECT ABS(-231279); -- 绝对值;SELECT SQRT(81); -- 平方根;SELECT ROUND(533.9); -- 四舍五入;
SELECT ROUND(533.732781937,2); -- 四舍五入到后面的【2】位小数SELECT CHARACTER_LENGTH(str)LENGTH('dafsa'); -- 字符长度;SELECT CHAR_LENGTH('字符长度'); -- 一个中文有三字节,用char_length才是纯字符长度段;2.空格处理＆大小写转换
SELECT RTRIM(' FDSA F') ; -- 左右空格处理--右长空格;SELECT LTRIM(' FSAF ') -- 左右空格处理--左长空格SELECT UPPER ('gbih'); --字母大写SELECT LOWER('FDNSAIFNIDSJVN '); --字母小写
SELECT UPPER(RTRIM(' cnjxvnxcjovn')); --清除空格后大写3.获取日期
SELECT MINUTE(NOW()); --获取分钟SELECT DAYNAME(NOW()); --获取当前日SELECT CURDATE(); --获取当前日期SELECT CURTIME(); --获取当前时二、聚合函数 （单表查询
-- COUNT AVG MAX MIN SUM
1.计数语句
SELECT COUNT(COL) FROM milk_tea AS M;SELECT COUNT( DISTINCT m.sale_price) FROM milk_tea AS m ;2.求和语句
SELECT SUM(m.in_price )*0.9 FROM milk_tea AS m ;SELECT SUM(m.sale_price - m.in_price ) FROM milk_tea AS m ;SELECT SUM(IFNULL(sale_price,0) - m.in_price) FROM milk_tea AS m ;SELECT m.net_w,SUM(m.sale_price) FROM milk_tea AS m GROUP BY m.net_w;SELECT m.net_w,SUM(m.sale_price) FROM milk_tea AS m WHERE m.net_w IN ('100g','200g') GROUP BY m.net_w;SELECT SUM(m.sale_price ) / COUNT(m.sale_price) FROM milk_tea AS m;3.极值、均值语句
SELECT AVG(m.sale_price) FROM milk_tea AS m;SELECT MIN(m.sale_price) FROM milk_tea AS m ;SELECT MAX(m.sale_price) FROM milk_tea AS m ;SELECT MAX(m.sale_price),MIN(m.sale_price), SUM(m.sale_price ),COUNT(m.sale_price) FROM milk_tea AS m ;4.练习语句
SELECT m.net_w,count(m.sale_price) FROM milk_tea AS m WHERE m.net_w IN ('100g','200g') GROUP BY m.net_w; -- 先对结果过滤，只要100g和200g的，之后再对其分组SELECT p.class, COUNT(1) FROM prod_info AS p GROUP BY p.class HAVING COUNT(1) > 4 ;SELECT p.class, COUNT(1) FROM prod_info AS p WHERE p.class = '零食' GROUP BY p.class ;三、 多表查询，表联结
1. 同表的列联结语句
练习语句①
SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name = '奶茶' ; -- 查出奶茶的价格SELECT * FROM milk_tea AS m1WHERE m1.sale_price > (SELECT m.sale_price FROM milk_tea AS mWHERE m.prod_name = '奶茶' )SELECT m1.*, (SELECT m.sale_price FROM milk_tea AS mWHERE m.prod_name = '奶茶')
FROM milk_tea AS m1;练习语句②
SELECT p.prod_name,p.type,p.sale_price FROM prod_info AS p WHERE p.prod_name = '抽纸';SELECT * FROM (SELECT p.prod_name,p.type,p.sale_price FROM prod_info AS p WHERE p.prod_name = '抽纸') AS bWHERE b.sale_price > 26;
2.不同表的列联结语句
SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name = '奶茶' ; SELECT p.class,AVG(p.sale_price) FROM prod_info AS p GROUP BY p.class; --选出列与加个列的均值，按p.class列排序SELECT p.class,AVG(p.sale_price) FROM prod_info AS p GROUP BY p.class HAVING AVG(p.sale_price) >15; --HAVING能与GROUP BY联合使用过滤SELECT p.class,AVG(p.sale_price) FROM prod_info AS p GROUP BY p.class HAVING AVG(p.sale_price) > (SELECT m.sale_price FROM milk_tea AS mWHERE m.prod_name = '奶茶');
3.运用IN操作符联结
IN 用于在 WHERE 子句中规定多个值
SELECT prod_name FROM milk_tea AS mWHERE m.sale_price = 15 ;SELECT * FROM milk_tea as m WHERE m.prod_name IN( '奶茶', '薯片' ,'薯条'); --列中有这些字符的用INSELECT * FROM milk_tea as m1 WHERE m1.prod_name IN( SELECT prod_name FROM milk_tea AS mWHERE m.sale_price = 15 );
4.联结练习总语句
①联结
SELECT * FROM prod_info AS p;
SELECT * FROM supplier_info AS s;SELECT s.*,p.* FROM prod_info AS p,supplier_info AS s
WHERE p.supplier_id = s.supplier_id;SELECT * FROM prod_info AS p,supplier_info AS s
WHERE p.supplier_id = s.supplier_id;SELECT p.*,s.* FROM prod_info AS p,supplier_info AS s
WHERE p.supplier_id = s.supplier_id;SELECT p.prod_name,p.sale_price,s.supplier_name FROM prod_info AS p,supplier_info AS sWHERE p.supplier_id = s.supplier_id;SELECT * FROM order_list AS l;SELECT l.prod_id FROM order_list AS l WHERE l.order_id = '20190403001' ;SELECT * FROM prod_info AS p WHERE p.prod_id IN( '20003','20004') ;SELECT * FROM prod_info AS p WHERE p.prod_id IN( SELECT l.prod_id FROM order_list AS l WHERE l.order_id = '20190403001') ;②内部联结
SELECT p.*FROM prod_info AS p order_list AS lWHERE p.prod_id = l.prod_id AND l.order_id = '20190403001';SELECT p.*FROM prod_info AS p INNER JOIN order_list AS lON p.prod_id = l.prod_idAND l.order_id = '20190403001';SELECT c.*,l.*FROM cust_info AS c LEFT JOIN order_list AS lON c.cust_id = l.cust_idAND l.order_id LIKE '20190401%';SELECT * FROM order_list AS l WHERE l.order_id LIKE '20190407%';UNION ALLSELECT * FROM order_list AS l WHERE l.order_id LIKE '20190407%';四、 查询练习＆新增行语句
1.查询语句复习
SELECT * FROM prod_info;SELECT * FROM prod_info2; -- 每次插入的行，都可以查得到SELECT *FROM prod_info2 ORDER BY prod_id DESC ;2.插入行
INSERT INTO prod_info2 VALUES('T000001','测试商品','test','215','555','nj00001');INSERT INTO prod_info2 (prod_id,prod_name,brand,type)VALUES('T0000211','测试商品','test','test');SELECT * FROM prod_info AS p WHERE p.prod_id = '10001' INSERT INTO prod_info；3.其他练习语句
条件语句汇总①
SELECT CONCAT('T',p.prod_id),prod_name,p.brand,p.type,p.class,
FROM prod_info AS p
WHERE p.prod_id = '10002' ; SELECT *FROM prod_info2 ORDER BY prod_id DESC ;
UPDATE prod_info2
SET class = '日用品' ;SELECT * FROM prod_info2 ;
UPDATE prod_info2
SET class = '零食'
WHERE prod_id LIKE 'T%';SELECT * FROM prod_info2 WHERE prod_id LIKE 'T%';条件语句汇总②
-- 全场打九折UPDATE prod_info2
SET sale_price = sale_price *0.9;-- 部分商品筛选处理
UPDATE prod_info2
SET sale_price = sale_price *0.9
WHERE prod_name ='抽纸' OR class = '饮料';-- 检验过滤条件SELECT * FROM prod_info2 WHERE prod_name ='抽纸' OR class = '饮料';
SELECT * FROM prod_info2 WHERE prod_name ='抽纸' OR class = '饮料';UPDATE prod_info2
SET sale_price = sale_price *0.9,cost = cost *0.9 --同时，进货价也打九折,用逗号隔开WHERE prod_name ='抽纸' OR class = '饮料';
五、创建表
1.创建表语句
CREATE TABLE pet2
(name VARCHAR(225) NOT NULL, -- 加上属性的限制，用空格隔开
owner VARCHAR(225) NOT NULL,
specise VARCHAR(225),
sex CHAR(1), -- 定成的：不是m就是f，（字符长度）
birth date,
death date
);2.创建后的插入语句
SELECT * FROM pet2;
-- bowser diane dog m 1979-08-31 1995-07-29 --插入该行【插入语句】
INSERT INTO pet2 VALUES('bowser','diane','dog','m','1979-08-31','1995-07-29 ');-- 插入部分行INSERT INTO pet2(name,owner,specise,sex) VALUES('bowser2','daine','dog','m');六、删除表
1.删除表的语句练习
CREATE TABLE pet2
(name VARCHAR(225) NOT NULL,
owner VARCHAR(225) DEFAULT 'police', -- default是默认值的限定，一定要是一个【常数】
specise VARCHAR(225),
sex CHAR(1), birth date,
death date
);SELECT * FROM pet2;INSERT INTO pet2 VALUES('bowser','diane','dog','m','1979-08-31','1995-07-29');
INSERT INTO pet2(name,owner,specise,sex) VALUES('bowser2','daine','dog','m');INSERT INTO pet2(name,specise,sex) VALUES('bowser3','dog','m'); -- 运用了默认值INSERT INTO pet2(name,owner,specise,sex) VALUES('bowser2',NULL,'dog','m'); -- 赋予为空值 则有意义UPDATE pet2 -- 【改】修改的目标表
SET owner = NULL
WHERE name = 'bowser2';UPDATE pet2
SET owner = 'chika'
WHERE owner = 'diane';-- 删掉表 转到mysqlCREATE TABLE pet3
AS -- 复制
SELECT * FROM pet2;SELECT * FROM pet3;-- 【删】删除表
DROP TABLE pet3;CREATE TABLE pet3
AS
SELECT p2.name,p2.owner FROM pet2 AS p2;CREATE TABLE pet3
AS
SELECT p2.name,p2.owner FROM pet2 AS p2 WHERE 1 = 2; -- 复制一个有列的空行，用where 1 =2 (即不存在)2. 删除字段语句
ALTER TABLE pet3 DROP birth;
ALTER TABLE pet3 DROP birth,DROP sex;SELECT * FROM pet3;七、对表结构的修改语句
1. 增加一个字段
ALTER TABLE pet3 ADD sex CHAR(1);
ALTER TABLE pet3 ADD birth date NOT nULL;2.修改字符长度
ALTER TABLE ;八、虚拟表
-- 创建一个 视图表
SELECT * FROM pet;CREATE VIEW pet_show AS
SELECT * FROM pet;SELECT *FROM pet_show AS ps WHERE ps.sex = 'm'; -- where是对视图 做一个过滤的操作SELECT ps.name,ps.species FROM pet_show AS ps WHERE ps.sex = 'm'; DROP VIEW pet_show;