时间:2021-07-01 10:21:17 帮助过:8人阅读
目录
- <code>'''
- 增:insert [into] [数据库名.]表名(字段1[,...,字段n]) values (数据1[,...,数据n])[,...,(数据1[,...,数据n])];
- 删:delete from [数据库名.]表名 [条件]
- 改:update [数据库名.]表名 set 字段1=值1[,...,字段n=值n] [条件]
- 查:select [distinct] 字段1 [as 别名1],...,字段n [as 别名n] from [数据库名.]表名 [条件];
- '''
- # 条件:from, where, group by, having,distinct, order by, limit ==>层层筛选后的结果
- # 注:一条查询语句,可以拥有多种筛选条件,条件的顺序必须按照上方顺序进行逐步筛选,distinct稍有特殊(书写位置),条件的种类可以不全
- # 可以缺失,但不能乱序</code>
- <code>mysql>: create table t1(
- id int,
- x int,
- y int
- );
- mysql>: insert into t1 values(1,1,1),(2,1,2),(3,2,2),(4,2,2);
- mysql>: select distinct * from t1; # 全部数据
- mysql>: select distinct x,y from t1; # 结果 1,1 1,2 2,2
- mysql>: select distinct y from t1; # 结果1 2
- # 总结:distinct对参与查询的所有字段,整体去重(所查的所有字段的值都相同,才认为是重复数据)</code>
- <code>CREATE TABLE `emp` (
- `id` int(0) NOT NULL AUTO_INCREMENT,
- `name` varchar(10) NOT NULL,
- `gender` enum('男','女','未知') NULL DEFAULT '未知',
- `age` int(0) NULL DEFAULT 0,
- `salary` float NULL DEFAULT 0,
- `area` varchar(20) NULL DEFAULT '中国',
- `port` varchar(20) DEFAULT '未知',
- `dep` varchar(20),
- PRIMARY KEY (`id`)
- );
- INSERT INTO `emp` VALUES
- (1, 'yangsir', '男', 42, 10.5, '上海', '浦东', '教职部'),
- (2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
- (3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
- (4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
- (5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
- (6, 'zero', '男', 18, 8.8, '中国', '黄浦', '咨询部'),
- (7, 'owen', '男', 18, 8.8, '安徽', '宣城', '教学部'),
- (8, 'jason', '男', 28, 9.8, '安徽', '巢湖', '教学部'),
- (9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
- (10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
- (11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
- (12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
- (13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
- (14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部');</code>
- <code>'''
- 拼接:concat() | concat_ws()
- 大小写:upper() | lower()
- 浮点型操作:ceil() | floor() | round()
- 整型:可以直接运算
- '''
- mysql>: select name,area,port from emp;
- mysql>: select name,concat(area,'-',port) from emp;
- mysql>: select name as 姓名,concat(area,'-',port) 地址 from emp; # 上海-浦东
- mysql>: select name as 姓名,concat_ws('-',area,port,dep) 信息 from emp; # 上海-浦东-教职部
- mysql>: select upper(name) 姓名大写,lower(name) 姓名小写 from emp;
- mysql>: select id,salary,ceil(salary)上薪资,floor(salary)下薪资,round(salary)入薪资 from emp;
- mysql>: select name 姓名, age 旧年龄, age+1 新年龄 from emp;</code>
- <code># 多条件协调操作导入:where 奇数 [group by 部门 having 平均薪资] order by [平均]薪资 limit 1
- mysql>: select * from emp where id<5 limit 1; # 正常 id小于5的第一条
- mysql>: select * from emp limit 1 where id<5; # 异常,条件乱序
- # 判断规则
- '''
- 比较符合:> | < | >= | <= | = | !=
- 区间符合:between 开始 and 结束 | in(自定义容器)
- 逻辑符合:and | or | not
- 相似符合:like _|%
- 正则符合:regexp 正则语法
- '''
- mysql>: select * from emp where salary>5;
- mysql>: select * from emp where id%2=0;
- mysql>: select * from emp where salary between 6 and 9;
- mysql>: select * from emp where id in(1,3,7,20);
- # _o 某o | __o 某某o | _o% 某o* (*是0~n个任意字符) | %o% *o* _表示一个字符,%表select示多个字符
- mysql>: select * from emp where name like '%o%';
- mysql>: select * from emp where name like '_o%';
- mysql>: select * from emp where name like '___o%';
- # sql只支持部分正则语法
- mysql>: select * from emp where name regexp '.*\d'; # 不支持\d代表数字,认为\d就是普通字符串
- mysql>: select * from emp where name regexp '.*[0-9]'; # 支持[]语法</code>
- <code># 表象:在没有分组的情况下,where与having结果相同
- # 重点:having可以对 聚合结果 进行筛选
- mysql>: select * from emp where salary>5;
- mysql>: select * from emp having salary>5;
- mysql>: select * from emp where id in(5,10,15,20);
- mysql>: select * from emp having id in(5,10,15,20);</code>
- <code>"""
- max():最大值
- min():最小值
- avg():平均值
- sum():和
- count():记数
- group_concat():组内字段拼接,用来查看组内其他字段
- """</code>
- <code># 修改my.ini配置重启mysql服务
- sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- # 在sql_mode没有 ONLY_FULL_GROUP_BY 限制下,可以执行,但结果没有意义
- # 有 ONLY_FULL_GROUP_BY 限制,报错
- mysql>: select * from emp group by dep;
- # 分组后,表中数据考虑范围就不是 单条记录,因为每个分组都包含了多条记录,参照分组字段,对每个分组中的 多条记录 统一处理
- # eg: 按部门分组,每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人
- # 将多条数据统一处理,这种方式就叫 聚合
- # 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
- # 注:参与分组的字段,也归于 聚合结果
- mysql>:
- select
- dep 部门,
- group_concat(name) 成员,
- max(salary) 最高薪资,
- min(salary) 最低薪资,
- avg(salary) 平均薪资,
- sum(salary) 总薪资,
- count(salary) 人数
- from emp group by dep;
- mysql>: select dep 部门,max(age) 最高年龄 from emp group by dep;
- # 总结:分组后,查询条件只能为 分组字段 和 聚合函数操作的聚合结果</code>
- <code>mysql>:
- select
- dep 部门,
- group_concat(name) 成员,
- max(salary) 最高薪资,
- min(salary) 最低薪资,
- avg(salary) 平均薪资,
- sum(salary) 总薪资,
- count(salary) 人数
- from emp group by dep;
- # 最低薪资小于2
- mysql>:
- select
- dep 部门,
- group_concat(name) 成员,
- max(salary) 最高薪资,
- min(salary) 最低薪资,
- avg(salary) 平均薪资,
- sum(salary) 总薪资,
- count(salary) 人数
- from emp group by dep having min(salary)<2;
- # having可以对 聚合结果 再进行筛选,where不可以</code>
- <code># order by 主排序字段 [asc|desc], 次排序字段1 [asc|desc], ...次排序字段n [asc|desc]</code>
- <code>mysql>: select * from emp;
- # 按年龄升序
- mysql>: select * from emp order by age asc;
- # 按薪资降序
- mysql>: select * from emp order by salary desc;
- # 按薪资降序,如果相同,再按年龄降序
- mysql>: select * from emp order by salary desc,age desc;
- # 按年龄降序,如果相同,再按薪资降序
- mysql>: select * from emp order by age desc,salary desc;</code>
- <code>mysql>:
- select
- dep 部门,
- group_concat(name) 成员,
- max(salary) 最高薪资,
- min(salary) 最低薪资,
- avg(salary) 平均薪资,
- sum(salary) 总薪资,
- count(gender) 人数
- from emp group by dep;
- # 最高薪资降序
- mysql>:
- select
- dep 部门,
- group_concat(name) 成员,
- max(salary) 最高薪资,
- min(salary) 最低薪资,
- avg(salary) 平均薪资,
- sum(salary) 总薪资,
- count(gender) 人数
- from emp group by dep
- order by max(salary) desc;</code>
- <code># 语法:limit 条数 | limit 偏移量,条数
- mysql>: select name,salary from emp where salary<8 order by salary desc limit 1;
- mysql>: select * from emp limit 5,3; # 先偏移5条满足条件的记录,再查询3条 打印id为 6,7,8 的三条数据</code>
- <code># 连接:将有联系的多张表通过关联(有联系就行,不一定是外键)字段,进行连接,形成一张大表
- # 连表查询:在大表的基础上进行查询,就称之为连表查询
- # 将表与表建立连接的方式有四种:内连接,左连接,右连接,全连接</code>
- <code>create table dep(
- id int primary key auto_increment,
- name varchar(16),
- work varchar(16)
- );
- create table emp(
- id int primary key auto_increment,
- name varchar(16),
- salary float,
- dep_id int
- );
- insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车');
- insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);</code>
- <code>集合 X{a, b} * Y{o, p, q} => Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}}
- mysql>: select * from emp,dep;
- # 总结:是两张表 记录的所有排列组合,数据没有利用价值</code>
- <code># 关键字:inner join on
- # 语法:from A表 inner join B表 on A表.关联字段=B表.关联字段
- mysql>:
- select
- emp.id,emp.name,salary,dep.name,work
- from emp inner join dep on emp.dep_id = dep.id
- order by emp.id;
- # 总结:只保留两个表有关联的数据</code>
- <code># 关键字:left join on
- # 语法:from 左表 left join 右表 on 左表.关联字段=右表.关联字段
- mysql>:
- select
- emp.id,emp.name,salary,dep.name,work
- from emp left join dep on emp.dep_id = dep.id
- order by emp.id;
- # 总结:保留左表的全部数据,右表有对应数据直接连表显示,没有对应关系空填充</code>
- <code># 关键字:right join on
- # 语法:from 左表 right join 右表 on 左表.关联字段=右表.关联字段
- mysql>:
- select
- emp.id,emp.name,salary,dep.name,work
- from emp right join dep on emp.dep_id = dep.id
- order by emp.id;
- # 总结:保留右表的全部数据,左表有对应数据直接连表显示,没有对应关系空填充</code>
- <code>mysql>:
- select
- emp.id,emp.name,salary,dep.name,work
- from emp right join dep on emp.dep_id = dep.id
- order by emp.id;
- mysql>:
- select
- emp.id,emp.name,salary,dep.name,work
- from dep left join emp on emp.dep_id = dep.id
- order by emp.id;
- # 总结:更换一下左右表的位置,相对应更换左右连接关键字,结果相同</code>
- <code>mysql>:
- select
- emp.id,emp.name,salary,dep.name,work
- from emp left join dep on emp.dep_id = dep.id
- union
- select
- emp.id,emp.name,salary,dep.name,work
- from emp right join dep on emp.dep_id = dep.id
- order by id;
- # 总结:左表右表数据都被保留,彼此有对应关系正常显示,彼此没有对应关系均空填充对方</code>
- <code>create table author(
- id int,
- name varchar(64),
- detail_id int
- );
- create table author_detail(
- id int,
- phone varchar(11)
- );
- insert into author values(1,'Bob',1),(2,'Tom',2),(3,'ruakei',0);
- insert into author_detail values(1, '13344556677'), (2, '14466779988'), (3, '12344332255');
- select author.id,name,phone from author join author_detail on author.detail_id=author_detail.id
- order by author.id;
- select author.id,name,phone from author left join author_detail on author.detail_id=author_detail.id
- union
- select author.id,name,phone from author right join author_detail on author.detail_id=author_detail.id
- order by id;</code>
- <code># 在一对一基础上,建立 作者与书 的
- create table author(
- id int,
- name varchar(64),
- detail_id int
- );
- insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);
- create table book(
- id int,
- name varchar(64),
- price decimal(5,2)
- );
- insert into book values(1, 'python', 3.66), (2, 'Linux', 2.66), (3, 'Go', 4.66);
- create table author_book(
- id int,
- author_id int,
- book_id int
- );
- # 数据:author-book:1-1,2 2-2,3 3-1,3
- insert into author_book values(1,1,1),(2,1,2),(3,2,2),(4,2,3),(5,3,1),(6,3,3);
- # 将有关联的表一一建立连接,查询所以自己所需字段
- select book.name,price,author.name,author_detail.phone from book
- join author_book on book.id = author_book.book_id
- join author on author_book.author_id = author.id
- left join author_detail on author.detail_id = author_detail.id;</code>
数据库查询
标签:left join 查询 tom 成员 结束 平均值 ble end lin