MYSQL基本操作DQL
-- 1.数据准备
-- 1.创建数据库
create database if not exists mydb2;
use mydb2;
-- 2.创建商品表：
create table product(
pid int primary key auto_increment,-- 商品编号
pname varchar(20) not null, -- 商品名字
price double,#商品价格
category_id varchar(20)#商品所属分类
);
-- 3.添加数据
insert into product values(null,'海尔洗衣机',5000,'c001');
insert into product values(null,'美的冰箱',3000,'c001');
insert into product values(null,'格力空调',5000,'c001');
insert into product values(null,'九阳电煲饭',5000,'c001');
insert into product values(null,'衬衣',300,'c002');
insert into product values(null,'西装',800,'c002');
insert into product values(null,'夹克',440,'c002');
insert into product values(null,'闲裤',266,'c002');
insert into product values(null,'卫衣',180,'c002');
-- 简单查询
-- 1.查询所有的商品．
select pid,pname,price,category_id from product;
select * from product;
-- 2.查询商品名和商品价格。
select pname,price from product;
-- 3.别名查询.使用的关键字是as（as可以省略的）
-- 3.1表别名:
select * from product as p;
select * from product p;
select p.id,u.id from product p,user u;
-- 3.2列别名：
select pname as name,price '价格' from product;
-- 4.去掉重复值。
select distinct price from product;
select distinct * from product;#如果两行数据每列都一样，删掉一行
-- 5.查询结果是表达式（运算查询）：将所有商品的加价10元进行显示。
select pname,price +10 from product;
select distinct price +10 new_price from product;
-- 运算符
use mydt2;
-- 1.算数运算符
select 6 + 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 2;
-- 将所有商品加10元
select pname,price +10 from product;
-- 将所有商品加10%
select pname,price *1.1 from product;
-- 比较运算符
-- 逻辑运算符
-- 查询商品名称为“海尔洗衣机”的商品所有信息
select * from product where pname = '海尔洗衣机';
-- 查询价格为800商品
select * from product where price = 800;
-- 查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where not(price = 800);
-- 查询商品价格大于60元的所有商品信息
select * from product where price >= 60;
-- 查询商品价格在200到1000之间所有商品
select * from product where price between 200 and 1000;
select * from product where price >=200 and price <= 1000;
select * from product where price >=200 && price <= 1000;
-- 查询商品价格是200或800的所有商品
select * from product where price in(200,800);
select * from product where price = 200 or price = 800;
select * from product where price = 200 || price = 800;
-- 查询含有'鞋'字的所有商品
select * from product where pname like '%裤'#最后一个字是裤
select * from product where pname like '%裤%'#中间字是裤，%用来匹配任意字符
-- 查询以'海'开头的所有商品
select * from product where pname like '海%'
-- 查看第二个字为‘蔻’的商品
select * from product where pname like '_蔻'#‘_’下划线匹配单个字符
-- 查询category_id为null的商品
select * from product where category_id is null;
-- 查询category_id不为null分类的商品
select * from product where category_id is not null;
-- 使用least求最小值
select least(10,20,30) as zxz;#10
select least(10,null,30);#求最小值时，一个值为null，不进行比较，结果直接为null
-- 使用greatest求最大值
select greatest(10,20,30) as big ;
select greatest(10,,null,30);#求最大值时，一个值为null，不进行比较，结果直接为null
-- 4．位运算符
#I位与
select 3 & 5;
0011
0101
-------都为1为1
0001
#位或
select 3 | 5;
0011
0101
-------只要有一个为1为1
0111
#位异或
select 3 ^ 5;
0011
0101
-------相同为0，不同为1
0110
#位右移
select 3>>1;
0011
-------去掉最后一位，左边补0
0001
#位左移
select 3 << 1;
0011
-------去掉最前一位，右边补0
0110
#位取反
select ~3;
0011
1100
-- 排序查询
-- 1.使用价格排序(降序)
use mydb2;
select * from product order by price asc;#升序，默认升序
select * from product order by price desc;
-- 2.在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price desc,category_id desc;
-- 3.显示商品的价格(去重复)，并排序(降序)
select distinct price from product order by price desc;#不能增加别的字段，因为price去重后数量可能无法与其它字段一一对应
-- 聚合函数
-- count(）统计指定列不为NULL的记录行数；
-- sum(）计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
-- max(）计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
-- min()计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
-- avg(）计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0
-- 查询商品的总条数
SELECT count(pid) from product;#一共9条数据
SELECT count(*) from product;#不全为空的行
-- 查询价格大于200商品的总条数
select count(pid) from product where price>200;
-- 查询分类为'co01'的所有商品的总和
select sum(price) from product where category_id = 'c001';
-- 查询商品的最大价格
select max(price) from product;
-- 查询商品的最小价格
select max(price) max_price,min(price) min_price from product;
-- 查询分类为'c002'所有商品的平均价格
select * from product where category_id ='c002';
select avg(price) from product where category_id ='c002';
use mydb2;
-- 创建表
create table test_null(
cl varchar(20),
c2 int
);
-- 插入数据
insert into test_null values('aaa',3);
insert into test_null values('bbb',3);
insert into test_null values('ccc',NULL);
insert into test_null values('ddd',6);
-- 测试
select count(*), count(1), count(c2) from test_null;#443
select sum(c2),max(c2),min(c2),avg(c2) from test_null;#不对null值处理，可以default 0添加进表
-- 分组查询
-- select 字段1,字段2.. from 表名 group by 分组字段 having 分组条件;
-- 1 统计各个分类商品的个数
select category_id,count(pid) from product group by category_id;
select category_id,count(pid) from product group by category_id,price;#只有id和price都相同才可以分为一组
-- 注意，分组后select的后边只能写分组字段和聚合函数
-- 分组之后的条件筛选-having
-- select 字段1,字段2. from 表名 group by 分组字段 having 分组条件;
-- 2.统计各个分类商品的个数,且只显示个数大于4的信息
select category_id,count(pid)
from product
group by category_id
having count(pid) > 4
order by count(pid);
#执行顺序from-group by-count-select-having
-- 分页查询-limit
-- 1.查询product表的前5条记录
select * from product limit 5;
-- 2.从第4条开始显示，显示5条
select * from product limit 3,5;
use mydb2;
select * from product;
create table product2(
pname varchar(20),
price double);
insert into product2(pname,price) select pname,price from product;
select * from product2;
create table product3(
category_id varchar(20),
product_count int
);
insert into product3 select category_id ,count(*) from product group by category_id
select * from product3;
练习
use mydb2;
create table student(
id int,
name varchar(20),
gender varchar(20),
chinese int,
english int,
math int
);
insert into student(id,name,gender,chinese,english,math) values(1,'zm','man',89,77,76);
insert into student(id,name,gender,chinese,english,math) values(2,'lj','man',88,77,89);
insert into student(id,name,gender,chinese,english,math) values(3,'zww','woman',89,77,76);
insert into student(id,name,gender,chinese,english,math) values(1,'zm','man',89,77,76);
-- 查询表中所有学生的信息。
select * from student;
-- 查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
-- 过滤表中重复数据。
select distinct * from student;
-- 统计每个学生的总分。
SELECT name, chinese+english+math as totalscore from student;
-- 在所有学生总分数上加10分特长分。
SELECT name, chinese+english+math+10 as totalscore from student;
-- 使用别名表示学生分数。
SELECT name, chinese'语文',english'英语',math'数学' from student;
-- 查询英语成绩大于90分的同学
select * from student where english>90;
-- 查询总分大于200分的所有同学
SELECT *, chinese+english+math from student where chinese+english+math >200;
-- 查询英语分数在 80—90之间的同学。
select * from student where english<90 and english>80;
select * from student where english between 80 and 90;
-- 查询英语分数不在 80—90之间的同学。
select * from student where not (english<90 and english>80);
select * from student where english not between 80 and 90;
-- 查询数学分数为89,90,91的同学。
select * from student where math in (89,90,91);
select * from student where math not in (89,90,91);
-- 查询所有姓李的学生英语成绩。
select * from student where name like 'l%';
-- 查询数学分80并且语文分80的同学。
select * from student where math=80 and chinese=80;
-- 查询英语80或者总分200的同学
select * from student where english=80 and (chinese+english+math)=200;
-- 对数学成绩降序排序后输出。对总分排序后输出，然后再按从高到低的顺序输出
select * from student order by math desc;
-- 对姓李的学生成绩排序输出
select * from student where name like 'l%' order by math desc;
-- 查询男生和女生分别有多少人，并将人数降序排序输出,查出人数大于3的性别
select gender,count(*) from student group by gender having count(*)>3 order by count(*) desc;
use mydb2;
create table emp(
empno int,
ename varchar(50),
job varchar(50),
hiredate int,
sal int,
deptno int
);
alter table emp add mgr int;
alter table emp add comm int;
alter table emp change hiredate hiredate date;
insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'alien','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values(7666,'ward','manager',7992,'1980-12-18',890,null,20);
insert into emp values(7239,'smith','clerk',7902,'1980-12-17',800,null,20);
-- 按员工编号升序排列不在10号部门工作的员工信息
select * from emp where deptno != 10 order by empno;
-- 2、查询姓名第二个字母不是"A"且薪水大于1000元的员工信息，
-- 年薪=12*月薪+奖金
-- ifnull(sal,0)如果杀了为null，当做0，不为null，按原值
select * from emp where ename not like '_A%' and sal > 1000 order by (12*sal+ifnull(comm,0)) desc;
-- 3、求每个部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
-- 4、求各个部门的最高薪水
select deptno,max(sal) maxsal from emp group by deptno;
-- 求每个部门每个岗位的最高薪水
select deptno,job,max(sal) from emp group by deptno,job;
-- 求平均薪水大于2000的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
-- 将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序;
select deptno,avg(sal) avg_sal from emp group by deptno having
avg_sal > 1500 order by avg_sal desc;
-- 8、选择公司中有奖金的员工姓名，工资
select * from emp where comm is not null and not 0;
-- 9、查询员工最高工资和最低工资的差距
select max(sal)-min(sal) from emp
浙公网安备 33010602011771号