概念
语法格式
简化版语法
数据准备
创建数据库
create database if not exists mydb2;
use mydb2;
创建商品表
create table mydb2.product(
pid int primary key auto_increment,
pname varchar(20),
price double,
category_id varchar(20) -- 商品所属分类
);
添加数据
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,'海澜之家卫衣',1800,'c002');
insert into product values(null,'杰克琼斯运动裤',430,'c002');
insert into product values(null,'兰蔻面霜',300,'c003');
insert into product values(null,'雅思兰黛精华水',200,'c003');
insert into product values(null,'香奈儿香水',350,'c003');
insert into product values(null,'SK-II神仙水',350,'c003');
insert into product values(null,'资生堂粉底液',180,'c003');
insert into product values(null,'老北京方便面',56,'c004');
insert into product values(null,'良品铺子海带丝',17,'c004');
insert into product values(null,'三只松鼠坚果',88,null);
简单查询
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 user.id,product.id from product,user;
select u.id,p.id from product p,user u;-- 别名可以起到简化的作用
3.2列别名
select pname as '商品名',price '商品价格' from product;
4.去掉重复值
select distinct price from product;
select distinct * from product;
5.查询结果是表达式（运算查询）：将所有商品的价格加价10元进行显示。
select pname,price + 10 new_price from product;
运算符
MySQL支持的四种运算符
1.算术运算符
select 6 + 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 4;-- 将所有商品的价格加10元。
select pname,price + 10 as new_price from product; -- 将所有的价格上涨10%
select pname,price * 1.1 as new_price from product;
2.比较运算符
3.逻辑运算符
-- 比较运算符
-- 逻辑运算符
use mydb2;
-- 查询商品名称为'海尔洗衣机'的商品所有信息
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 = 800 or price = 200;
select * from product where price = 800 || price = 200;
select * from product where price in (200,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,20) as small_number;
select least(10,20,null) as small_number; -- 如果求最小值时，拥有一个值为null,则不会进行比较，结果直接为null
-- 使用greatest求最大值
select greatest(10,20,30) as big_number;
select greatest(10,null,30) as big_number; -- 如果求最大值时，拥有一个值为null,则不会进行比较，结果直接为null
4.为运算符（了解）
select 3 & 5; -- 位与
0011
0101
0001
select 3|5; -- 位或
0011
0101
0111
select 3^5; -- 位异或
0011
0101
0110
select 3>>1; -- 位右移
0011 --->> 1 0001
select 3<<1; -- 位左移
0011 <---- 1 0110
select ~3; -- 位取反
00000000000000000000000000000011 --> 111111111111111111111111111111111100
排序查询
介绍
特点
-- 使用价格排序（降序）
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;
聚合查询
简介
-- 查询商品的总条数
use mydb2;
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 count(pid) from product where category_id = 'c001';
-- 查询商品的最大价格
select max(price) from product;
-- 查询商品的最小价格
select min(price) from product;
-- 查询商品的最大价格和最小价格
select max(price) as max_price,min(price) as min_price from product;
-- 查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';
聚合函数-NULL值的处理
介绍
-- 创建表
use mydb2;
create table mydb2.test_null( c1 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);
-- 测试
count() = count(1) select count(),count(1),count(c2) from test_null;
select sum(c2),max(c2),min(c2),avg(c2) from test_null; -- 平均值除以3
分组查询
简介
操作
select category_id,count(*) from product group by category_id;
分组之后的条件筛选
格式
-- 统计各个分类商品的个数，且只显示个数大于4的信息
-- 执行顺序：from -> group by -> count(pid) -> select -> having -> order by
-- select category_id,count(*) as cnt
from
product
group by
category_id
having
cnt > 4
order by
cnt ;
分页查询
简介
格式
操作
-- 查询product表的前5条记录
select * from product limit 5;
-- 从第四条开始显示，显示五条。
select * from product limit 3,5;
-- 分页显示
select * from product limit 0,60; -- (1-1)*60
select * from product limit 60,60; -- (2-1)*60
select * from product limit 120,60; -- (3-1)*60
select * from product limit n,60; -- (n-1)*60
INSERT INTO SELECT语句
简介
格式
use mydb2;
select * from product;
-- insert into table select ...
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;
SQL的书写顺序
select
category_id,count(*) as cnt
from
product
where
price > 100
group by
category_id
having
cnt > 4
order by
cnt ;
SQL的执行顺序
from -> where ->group by -> count(pid) - > having -> select -> order by -> limit
SQL的基本查询练习
学生表
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,'李一','女',89,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 new_socre FROM student;
-- 在所有总分上加10分特长分
SELECT name,chinese+english+math+10 as new_socre FROM student;
-- 使用别名表示学生分数
SELECT name,chinese '语文成绩',english '英语成绩',math '数学成绩' FROM student;
-- 查询英语成绩大于90的同学
SELECT name,english '英语成绩' FROM student where english > 90;
-- 查询总分大于200的同学
SELECT name,chinese + english + math FROM student where chinese + english + math > 200;
-- 查询英语分数在80-90分的同学
SELECT * FROM student WHERE english between 80 and 90; SELECT name,english from student WHERE 80 =< english and 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 name,english from student WHERE not (80 <= english and english <= 90);
SELECT name,english from student WHERE 80 >= english or english >= 90;
-- 查询数学分数为89，91，90的同学
SELECT * from student where math in(89,90,91);
-- 查询数学分数不为89，91，90的同学
SELECT * from student where math NOT in(89,90,91);
SELECT * from student where NOT math in(89,90,91);
-- 查询所有姓李的英语成绩。
SELECT name,english from student WHERE name like '李%';
-- 查询数学分80并且语文成绩80的同学
SELECT * from student where math = 80 and chinese = 80;
-- 查询英语80或者总分200的同学
SELECT name,english,chinese+math+english new_score FROM student where english = 80 || english+chinese+math=200;
-- 对于数学成绩降序排序后输出
SELECT * FROM student ORDER BY math DESC;
-- 对总分排序后输出，然后再从高到低的顺序输出
SELECT chinese+english+math score from student ORDER BY chinese+english+math desc;
-- 对所有李姓同学总分成绩排序输出 S
ELECT name,chinese+english+math score from student WHERE name like '李%' ORDER BY chinese+english+math desc;
-- 查询男生和女生分别有多少人，并且将人数降序排序输出,挑出人数大于4的一组。
SELECT
gender,count()
FROM
student
GROUP BY
gender
HAVING
COUNT() > 4
ORDER BY
count(*) DESC;
员工表
USE mydb2;
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,'1981-06-09',2450,NULL,10); INSERT INTO emp VALUES(7788,'SCOTT','MANAGER',7566,'1987-04-19',3000,NULL,20); INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,NULL,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,'RORD','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 NOT in(10) ORDER BY empno;
-- 查询姓名第二个字母不是“A”且薪水大于1000元的员工信息，按年薪降序排序。
-- ifnull(sal,0)如果sal值为null，则当作0，不为原来的值。
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 ORDER BY avg(sal) DESC;
-- 求各个部门的最高薪水
SELECT deptno,max(sal) FROM emp GROUP BY deptno;
-- 求每个部门每个岗位的最高薪水
SELECT deptno,job,max(sal) FROM emp GROUP BY deptno,job ORDER BY deptno;
-- 求平均薪水大于2000的部门编号
SELECT deptno,avg(sal) FROM emp GROUP BY deptno HAVING avg(sal)>2000;
-- 将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
SELECT deptno,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;
DQL正则表达式
介绍
格式
-- ^在字符串开始出进行匹配
SELECT 'abc' REGEXP '^a';
SELECT * from product WHERE pname REGEXP '^海';
-- $在字符串末尾开始匹配
SELECT 'abc' REGEXP 'a$'; SELECT 'abc' REGEXP 'c$';
SELECT * from product WHERE pname REGEXP '水$';
-- .匹配任意单个字符，可以匹配除了换行符以外事务任意字符
SELECT 'abc' REGEXP '.b'; SELECT 'abc' REGEXP '.c';
SELECT 'abc' REGEXP 'a.';
-- [...]匹配括号内的任意单个字符
SELECT 'abc' REGEXP '[xyz]'; -- 0 正则表达式的任意字符是否在前边的字符串中出现 SELECT 'abc' REGEXP '[xaz]';
-- ...注意^符合只有在[]内才是取翻的意思，在别的地方都是表示开始处匹配
SELECT 'a' REGEXP 'abc';
SELECT 'x' REGEXP 'abc';
SELECT 'abc' REGEXP 'a';
-- a匹配0个或多个a，包括空字符串。可以作为占位符使用，有没有指定字符都可以匹配到数据
SELECT 'stab' REGEXP '.tab';-- 1
SELECT 'stb' REGEXP '.tab';-- 1
SELECT '' REGEXP 'a'; -- 1
-- a+ 匹配1个或者多个a,但是不包括空字符
SELECT 'stab' REGEXP '.ta+b';-- 1
SELECT 'stb' REGEXP '.ta+b';-- 0
-- 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
SELECT 'b' REGEXP '^(a|b)'; -- 1
SELECT 'a' REGEXP '^(a|b)'; -- 1
SELECT 'c' REGEXP '^(a|b)'; -- 0
-- a{m} 匹配m个a
SELECT 'auuuuc' REGEXP 'au{4}c';-- 1
SELECT 'auuuuc' REGEXP 'au{3}c';-- 0
SELECT 'auuuuc' REGEXP 'au{5}c';-- 0
-- a{m,} 匹配m个或者更多个a
SELECT 'auuuuc' REGEXP 'au{3,}c';-- 1
SELECT 'auuuuc' REGEXP 'au{4,}c';-- 1
SELECT 'auuuuc' REGEXP 'au{5,}c';-- 0
-- a{m,n} 匹配m到n个a,包含m和n
SELECT 'auuuuc' REGEXP 'au{3,5}c';-- 1
SELECT 'auuuuc' REGEXP 'au{4,5}c';-- 1
SELECT 'auuuuc' REGEXP 'au{5,10}c';-- 0
-- (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
SELECT 'xababy' REGEXP 'x(ab){3}y'; -- 0
扫一扫
1782
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
