MySQL语句的使用

目录

一，基本的增删改查

二，案例演示

三，排序拓展

1.拓展一

2. 拓展二

---

一，基本的增删改查

（SQLServer与PLSQL的语句基本一致）

提示：但是"+"  在MySQL里面加号不做拼接，只做运算

select * from t_student
desc t_student
insert into t_student values('01' , '赵雷' , '1990-01-01' , '男');

--模糊查询
select * from t_student where sname='赵雷'

update t_student set sname='xi' where sid='01'

DELETE * FROM t_student where sid = '01';

二，案例演示

案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
WHERE
commission_pct IS NULL;
​
​
案例1：查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
WHERE
commission_pct IS NOT NULL;



案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
t_mysql_employees
​
WHERE
salary <=> 12000;



添加筛选条件再排序
​
案例：查询部门编号>=90的员工信息，并按员工编号降序
​
SELECT *
FROM t_mysql_employees
WHERE department_id>=90
ORDER BY employee_id DESC;
​
​
3、按表达式排序
#案例：查询员工信息 按年薪降序
​
​
SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM t_mysql_employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
​
​


4、按别名排序
案例：查询员工信息 按年薪升序
​
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM t_mysql_employees
ORDER BY 年薪 ASC;
​




5、按函数排序
案例：查询员工名，并且按名字的长度降序
​
SELECT LENGTH(last_name),last_name
FROM t_mysql_employees
ORDER BY LENGTH(last_name) DESC;
​


6、按多个字段排序

案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM t_mysql_employees
ORDER BY salary DESC,employee_id ASC;
​

 

《参考效果图》

 

 

 

 

 

三，排序拓展

1.拓展一

《代码演示》

1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
​
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM t_mysql_employees
ORDER BY 年薪 DESC,last_name ASC;
​

 《效果图如下》 

2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary
FROM t_mysql_employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
​

 

3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
​
SELECT *,LENGTH(email)
FROM t_mysql_employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
​

 

 

 

2. 拓展二

《相关表》

--用户表
CREATE TABLE tb_user(
	user_id  VARCHAR(15) NOT NULL,
	order_time datetime not null,
	order_category VARCHAR(20) not null,
	order_amt float not null,
	shop_id VARCHAR(50) not null
)


insert into tb_user VALUES('D890','2018-01-06 12:34:00','烤串',18.90,'ZL123');
insert into tb_user VALUES('D890','2018-02-07 12:34:00','汉堡',19.50,'SM456');
insert into tb_user VALUES('B456','2018-03-06 12:34:00','鸡翅',10.30,'ZL123');
insert into tb_user VALUES('B456','2018-04-01 12:34:00','麻辣烫',25.30,'HBW123');
insert into tb_user VALUES('C789','2018-05-09 12:34:00','麻辣烫',15.78,'XM456');

--商户表
CREATE TABLE tb_db(
	shop_id  VARCHAR(15) NOT NULL,
	db_name VARCHAR(20),
	db_team VARCHAR(20),
	START_time datetime,
	END_time datetime
)



insert into tb_db VALUES('ZL123','小明','销售A组','2018-01-01','2015-01-14');
insert into tb_db VALUES('ZL123','小张','销售A组','2016-01-15','2018-01-17');
insert into tb_db VALUES('SM456','小张','销售B组','2016-01-17','2016-01-16');
insert into tb_db VALUES('HBW123','小邓','销售B组','2015-01-01','2017-01-14');
insert into tb_db VALUES('HBW123','小李','销售C组','2015-01-01','2018-01-15');
insert into tb_db VALUES('XM456','小陈','销售C组','2017-01-01','2019-01-14')

--一月每笔消费大于20元的用户的总消费金额

select user_id,SUM(order_amt) from(
select * from tb_user where order_time like '%-01-%' and order_amt>=20
) a GROUP BY user_id

效果图

 

 

《代码演示》

--一月只吃了麻辣烫和汉堡的人数
select count(*) from(
select count(*) from(
select * from (
select * from tb_user where order_time like '%-01-%'
) c where order_category='麻辣烫' or order_category='汉堡'
) d GROUP BY user_id HAVING COUNT(*)=2
) e

 

 

 

 

--计算每个BD_team的bd对应门店的销售额

select SUM(order_amt) from tb_db a, tb_user b where a.shop_id=b.shop_id GROUP BY a.db_team