数据库学习-数据库基本操作DQL-基本查询

数据库管理系统一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。

MySQL提供了功能强大、灵活的语句来实现这些操作。

MySQL数据库使用select语句来查询数据。

语法格式

select 
    [all|distinct]
    目标列的表达式1 别名,
    目标列的表达式2 别名...
from 表名或视图名 别名,表名或视图名 别名...
[where 条件表达式]
[group by 列名]
[having 条件表达式]
[order by 列名 [asc|deac]]
[limit 数字或列表];

简化版语法

select *| 列名 from 表 where 条件;

---

数据准备

-- 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,'花花公子夹克',400,'c002');
insert into product values(null,'劲霸休闲裤',530,'c002');
insert into product values(null,'海澜之家卫衣',580,'c002');
insert into product values(null,'杰克琼斯运动裤',480,'c002');

insert into product values(null,'兰蔻面霜',600,'c003');
insert into product values(null,'雅诗兰黛精华水',680,'c003');
insert into product values(null,'香奈儿香水',360,'c003');
insert into product values(null,'SK-Ⅱ神仙水',580,'c003');
insert into product values(null,'资生堂粉底液',180,'c003');

insert into product values(null,'老北京方便面',80,'c004');
insert into product values(null,'良品铺子海带丝',18,'c004');
insert into product values(null,'三只松鼠坚果',80,null);

---

简单查询

查询所有的商品

select * from product;

查询商品名和商品价格

select pname,price from product;

别名查询 使用的关键字是as（as可以省略）

-- 表别名
select * from product as p;
select * from product p;

-- 列别名
select pname,price from product;
select pname as '商品名',price '商品价格' from product;

去掉重复值

select distinct price from product;
select distinct * from product;-- 去除所有列的重复值

查询结果是表达式（运算查询）

select pname,price+10 new_price from product;

---

运算符

数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。

MySQL支持4种运算符：

算数运算符

比较运算符

逻辑运算符

位运算符

 位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制，进行位运算。然后再将计算结果从二进制数变回十进制数。

运算符操作-算数运算符

select 6+2;
select 6-2;
select 6*2;
select 6/2;
select 6%2;
select pname,price+10 as new_price from product;
select pname,price*1.1 as new_price 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);
-- 查询商品价格大于等于80元的所有商品信息
select * from product where price>=80;
-- 查询商品价格在200到1000之间的所有商品
select * from product where price>=200 and price<=1000;
select * from product where price>=200 && price<=1000;
select * from product where price between 200 and 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 '_蔻%';-- 下划线匹配单个字符
-- 查询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,5,30) as small_number;
select least(10,null,30);-- 如果求最小值时，有值为null，则不会进行比较，结果直接为null
-- 使用greatest求最大值
select greatest(10,20,30) as big_number;
-- 如果求最大值时，有值为null，则不会进行比较，结果直接为null

运算符操作-位运算符（了解）

select 3&5;-- 位与
select 3|5;-- 位或
select 3^5;-- 位异或
select 3>>1;-- 位右移
select 3<<1;-- 位左移
select ~3;-- 位取反

---

排序查询

如果我们需要对读取的数据进行排序，我们就可以使用MySQL的order by子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

select
字段1,字段2,...
from 表名
order by 字段1 asc|desc,字段2 asc|desc...

特点

1.asc代表升序，desc代表降序，如果不写默认升序

2.order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名

3.order by子句，放在查询语句的最后面。limit子句除外

使用价格降序排序

select * from product order by price desc;

在价格降序的基础上，以分类降序排序

select * from product order by price desc,category_id desc;

显示商品的价格（去重）并降序排序 

select distinct price from product order by price desc;

---

聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值

 查询商品的总条数

select count(pid) from product;
select count(*) from product;

查询价格大于200商品的总条数

select count(pid) from product where price>200;

查询分类为'c001'的所有商品总和

select sum(price) from product where category_id='c001';

查询商品的最大价格

select max(price) from product;

查询商品的最小价格

select min(price) from product;

查询分类为'c002'所有商品的平均价格

select avg(price) from product where category_id='c002';

---

聚合查询-NULL值的处理

1、count函数对null值的处理

如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。

2、sum和avg函数对null值的处理

这两个函数忽略null值的存在，就好像该条记录不存在一样。

3、max和min函数对null值的处理

max和民两个函数同样忽略null值的存在。

---

分组查询-group by

分组查询是指使用group by字句对查询信息进行分组。

格式：

select 字段1,字段2... from 表名 group by 分组字段 having 分组条件;

统计各个分类商品的个数

select category_id,count(pid) from product group by category_id;

分组之后的条件筛选-having：

        分组之后对统计结果进行筛选的话必须使用having，不能使用where

        where子句用来筛选from子句中指定的操作所产生的行

        group by子句用来分组where子句的输出

        having子句用来从分组的结果中筛选行

统计各个分类商品的个数，且只显示个数大于4的信息

select category_id,count(pid) from product group by category_id having count(pid)>4;

---

分页查询-limit

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1~5条，第二页显示6~10条。

方式1 显示前n条

select 字段1,字段2,... from 表明 limit n;

方式2 分页显示

select 字段1,字段2...from 表明 limit m,n;

m：整数，表示从第几条索引开始，计算方式：（当前页-1）*每页显示条数

n：整数，表示查询多少条数据

查询product表的前5条数据

select * from product limit 5;

从第4条开始显示，显示5条

select * from product limit 3,5;

---

INSERT INTO SELECT语句

将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句。

格式

insert into Table2(field1,field2,...) select value1,value2,... from Table1;

create table product2(
pname varchar(20),
price double
);
insert into product2(pname,price) select pname,price from product;
select * from product2;

insert into Table2 select * from Table1;

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;

要求：目标表Table2必须存在

---

正则表达式

正则表达式描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。

MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。

-- ^在字符串开始处进行匹配
select 'abc' regexp '^a';-- 1
select * from product where pname regexp '^海';
-- $在字符串末尾
select 'abc' regexp 'a$';-- 0
select * from product where pname regexp '水$';
-- .匹配任意单个字符，可以匹配除了换行符以外的任意字符
select 'abc' regexp '.b';-- 1
select 'abc' regexp 'a.';
-- [...]匹配括号内的任意单个字符
select 'abc' regexp '[xyz]'-- 0
select 'abc' regexp '[xaz]'-- 1
-- [^...]注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
select 'a' regexp '[^abc]'-- 0
select 'x' regexp '[^abc]'-- 1
select 'abc' regexp '[^a]'-- 1
-- a*匹配0个或多个a，包括空字符串。可以作为占位符使用，有没有指定字符都可以匹配到数据
select 'stab' regexp '.ta*b';-- 1
select 'stb' regexp '.ta*b';-- 1
select '' regexp 'a*';-- 1
-- a?匹配0个或者1个a
select 'stb' regexp '.ta?b';-- 1
select 'stab' regexp '.ta?b';-- 1
select 'staab' regexp '.ta?b';-- 0
-- a1|a2匹配a1或者a2
select 'a' regexp 'a|b'-- 1
select 'b' regexp '^(a|b)'-- 1
-- a{m}匹配m个a
select 'auuuuc' regexp 'au{4}c'-- 1
-- a{m,}匹配m个a
select 'auuuuc' regexp 'au{3,}c'-- 1
-- a{m,n}匹配m到n个a，包含m和n
select 'auuuuc' regexp 'au{3,5}c'-- 1
-- (abc)abc作为一个匹配序列，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号
select 'xababy' regexp 'x(abab)y'-- 1
select 'xababy' regexp 'x(ab)*y'-- 1
select 'xababy' regexp 'x(ab){1,2}y'-- 1

---

 

 

练习1

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,'张明','男',89,78,90);
insert into student(id,name,gender,chinese,english,math) values(2,'李进','男',67,53,95);
insert into student(id,name,gender,chinese,english,math) values(3,'王五','女',87,78,77);
insert into student(id,name,gender,chinese,english,math) values(4,'李一','女',88,98,92);
insert into student(id,name,gender,chinese,english,math) values(5,'李财','男',82,84,67);
insert into student(id,name,gender,chinese,english,math) values(6,'张宝','男',55,85,45);
insert into student(id,name,gender,chinese,english,math) values(7,'黄蓉','女',75,65,30);
insert into student(id,name,gender,chinese,english,math) values(7,'黄蓉','女',75,65,30);

-- 查询表中所有学生的信息
select * from student;
-- 查询表中所有学生的姓名和对应的英语成绩
select name,english from student;
-- 过滤表中重复数据
select distinct * from student;
-- 统计每个学生的总分
select name,(chinese+english+math) as total_score from student;
-- 在所有学生总分数上加10分特长分
select name,(chinese+english+math+10) as total_score from student;
-- 使用别名表示学生分数
select name,chinese '语文成绩',english '英语成绩',math '数学成绩' from student;
-- 查询英语成绩大于90分的同学
select * from student where english>90;
-- 查询总分大于200分的所有同学
select * from student where chinese+english+math>200;
-- 查询英语分数在80~90的同学
select * from student where english between 80 and 90;
select * from student where english>=80 && english<=90;
-- 查询英语分数不在80~90的同学
select * from student where not (english between 80 and 90);
select * from student where english not between 80 and 90;
select * from student where not (english>=80 && english<=90);
select * from student where english<80 || english>90;
-- 查询数学分数为89，90，91的同学
select * from student where math in(89,90,91);
-- 查询数学分数不为89，90，91的同学
select * from student where math not in(89,90,91);
-- 查询所有姓李的学生英语成绩
select name,english from student where name like '李%';
-- 查询数学分80且语文分80的同学
select * from student where math=80 and chinese=80;
-- 对数学成绩降序排序后输出
select * from student order by math desc;
-- 对姓李的学生总分成绩降序输出
select * from student where name like '李%' order by (chinese+english+math) desc;
-- 查询男生和女生分别有多少人，并将人数降序排序输出
select gender,count(*) as total_cnt from student group by gender order by total_cnt desc;

练习2

create table emp(
empno int,-- 员工编号
ename varchar(50),-- 员工名字
job varchar(50),-- 工作名字
mgr int,-- 上级领导编号
hiredate date,-- 入职日期
sal int,-- 薪资
comm int,-- 奖金
deptno int-- 部门编号
);
insert into emp values(7369,'Smith','CLERK',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'Allen','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'Ward','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'Jones','MANAGER',7839,'1981-04-02',2975,NULL,20);
insert into emp values(7654,'Martin','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'Blake','MANAGER',7839,'1981-05-01',2850,NULL,30);
insert into emp values(7782,'Clark','MANAGER',7839,'1971-06-09',2450,NULL,10);
insert into emp values(7788,'Scott','ANALYST',7566,'1987-04-19',3000,NULL,20);
insert into emp values(7839,'King','PRESIDENT',NULL,'1971-11-17',5000,NULL,10);
insert into emp values(7844,'Turner','SALERMAN',7698,'1987-09-08',1500,0,30);
insert into emp values(7876,'Adams','CLERK',7788,'1987-05-23',1100,NULL,20);
insert into emp values(7900,'James','CLERK',7698,'1981-12-03',950,NULL,30);
insert into emp values(7902,'Ford','ANALYST',7566,'1981-12-03',3000,NULL,20);
insert into emp values(7934,'Miller','CLERK',7782,'1982-01-23',1300,NULL,10);

-- 按照员工编号升序排序不在10号部门工作的员工信息
select * from emp where deptno!=10 order by empno;
-- 查询姓名第二个字母不是A且薪水大于1000的员工信息，按年薪降序排序
-- ifnull(comm,0) 如果comm的值为null，则当作0，不为null，则还是原来的值
select * from emp where ename not like '_A%' and sal>1000 order by (12*sal+ifnull(comm,0)) desc;
-- 求每个部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
-- 求每个部门每个岗位的最高薪水
select deptno,job,max(sal) from emp group by deptno,job order by deptno;
-- 将部门平均薪水大于1500的部门列出来，按部门平均薪水降序排序
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal>1500 order by avg_sal desc;
-- 选择公司中有奖金的员工姓名，工资
select * from emp where comm is not null;
-- 查询员工最高工资和最低工资的差距
select max(sal)-min(sal) from emp;