MySql
1.union联合查询
将多个select语句的结果纵向组合
select * from stuinfo union select * from stuinfoo;
union:
1.all #显示全部记录
2.distinct #(去除重复的值 他是默认)
select * from stuinfo union all select * from stuinfoo;
查找北京的女生和上海的男生 [晚上自己写一篇或多编]
select * from stuinfo where (city='上海' and sex='male') or (city='北京' and sex='female');
select * from stuinfo having city='上海' and sex='male' union all select * from stuinfo having city='北京' and sex='female';
Union的要求:
1.两边的select语句的字段数要一致
2,字段名可以不一样,最终按照第一个select语句的字段名返回
3.两边可以具有相同或不同的数据类型
男生的年龄降序,女生的年龄升序 [ 晚上自己写一篇或多编]
(select * from stuinfo having sex=1 order by age desc limit 1000) union all (select * from stuinfo having sex=2 order by age asc limit 1000);
2.多表查询
(1)分类
1.内连接
2.外连接
a.左外连接
b.右外连接
3.交叉连接
4.自然连接
(2)内连接(inner join)
#查询学生信息
select * from stuinfo inner join stumarks on stuinfo.sid = stumarks.stuno;
#inner 可以不用写
#给表取别名的 as 也可以省略不写
select * from stuinfo a join stumarks b on a.sid = b.stuno;
#inner join数据不完整就不显示
大坑别踩:
表连接肯定经常要用,如果你查询单条数据,而这条数据的从表信息不完整,使用内链接,查询出的结果为空,
但是,python代码的写法是一致的,为了避免python报错,基本不使用内链接.
(3)外连接
a.左外连接(left join)
select * from stuinfo a left join stumarks b on a.sid=b.sid;
#以左边的表为准,右边表中没有的记录用null表示
b.右外连接(right join)
select * from stuinfo a right join stumarks b on a.sid=b.sid;
##以右边的表为准,左边表中没有的记录用null表示
思考:
select * from 表一 inner join 表二 on 表一.公共字段=表二.公共字段;
select * from 表二 inner join 表一 on 表二.公共字段=表一.公共字段;
select * from 表一 left join 表二 on 表一.公共字段=表二.公共字段;
select * from 表一 right join 表二 on 表一.公共字段=表二.公共字段;
select * from 表一 left join 表二 on 表一.公共字段=表二.公共字段;
select * from 表二 right join 表一 on 表二.公共字段=表一.公共字段;
(4)交叉连接(cross join)
#交叉连接返回的结果和内链接一样的
select * from stuinfo a cross join stumarks b on a.sid=b.stuno;
select * from stuinfo,stumarks;
(5)自然连接(natural)
1.natural join #自然内连接
2.natural left join #自然左外连接
3.natural right join #自然右外连接
select * from stuinfo a natural join stumarks b;
select * from stuinfo a natural left join stumarks b;
select * from stuinfo a natural right join stumarks b;
结论:
1.自动判断连接条件,依据的是同名字段名
2.如果没有同名的字段名返回的是笛卡尔积
3.自动返回结果并进行整理;
a.连接字段最好保留一个
b.连接字段最好放在最前面
(6)using
#指定连接字段,using也会查询出的结果进行整理,整理的方式和自然连接相同.
select * from stuinfo left join stumarks using(sid);
练习
1.显示地区和每个地区参加数学考试的人数,并且按人数降序排列
select a.city,count(b.math) c from stuinfo a left join stumarks b using(sid) group by city order by c desc;
2..显示男生人数和女生人数
select a.sex,count(a.sex) from stuinfo a group by sex;
select sex,count(sex) from stuinfo where sex=1 union select sex,count(sex) from stuinfo where sex=2;
select sum(sex=1) 男,sum(sex=2) 女 from stuinfo;
3.显示每个地区的男生人数,女生人数,总人数
select city 城市,count(sex) 总人数,sum(sex='male') 男, sum(sex='female') 女 from stuinfo group by city;
3.子查询
什么叫子查询?
查询语句中还有查询语句,外面的查询称为父查询,里面的叫子查询.
子查询为父查询提供查询条件.
例题:查询数学成绩是80分的学生
#普通的查询
select * from stumarks left join stuinfo using(sid) where math=80;
#子查询
select * from stuinfo where sid=(select sid from stumarks where math = 80);
#如果使用等于,那就必须确保子查询查到的结果只有一个
#子查询的结果只能是单一的字段
例题:查找数学最高分的学生
#普通的查询
select a.sid,a.sname,a.sex,a.age,a.city,max(b.math) from stuinfo a left join stumarks b using(sid);#问题
#子查询
select * from stuinfo where sid = (select sid from stumarks where math = (select max(math) from stumarks));
(1)in|not in
如果子查询中返回了多条记录,使用 = 会发生错误,那么就必须要用in
例题:查询数学成绩不及格的学生
select * from stuinfo where sid in (select sid from stumarks where math in (select math from stumarks having math<60));
#1.数学成绩不及格 <60 60种可能
select math from stumarks having math<60; #查询到的有可能是一个也有可能是多个(58,59)
#2.这些成绩是那些学生的(sid)
select sid from stumarks where math in (58,59); #(3,4)
#3.根据学号查找学生的信息
select * from stuinfo where sid in (3,4);
例题:查询没有参加考试的学生
select * from stuinfo where sid in (select sid from stumarks where math is null);
(2)some | any|all
some和any是一样的,表示一些,类似与 in
all 表示全部的元素
#some
select * from stuinfo where sid =some (select sid from stumarks where math<60);
select *from stuinfo where sid =any (select sid from stumarks where math<60);
#条件都满足
select * from stuinfo where sid =all (select sid from stumarks where math<60);
查询数学90分以上的学生
select * from stuinfo where sid not in (select sid from stumarks where math<90);
select * from stuinfo where sid != some (select sid from stumarks where math<90);
#some = in !=some不等与 not in
#!=all 等同 not in
查询数学90以下的学生
select * from stuinfo where sid =some (select sid from stumarks where math in(select math from stumarks having math<90));
(3)exists | not exists
如果有人math超过100分,就显示所有学生的信息
select * from stuinfo where exists(select * from stumarks where math>=100);
#成绩未达到100分就显示
select * from stuinfo where not exists(select * from stumarks where math>=100);
(4)子查询分类
1.标量子查询:子查询返回的值只有一个 sid =
2.列子查询:子查询返回一个列表 sid in (3,4)
3.行子查询:查询多个条件由多个字段组成的行形成:
select * from stumarks where (sid,ch,math) in (select * from stumarks where math=100);
#例如python中
sid,ch,math = ((3,4),(88,99),(100,88))
找出语文成绩最高的男生和女生:
select * from stuinfo where sid in (select sid from stumarks where ch in ( select max(ch) from stuinfo left join stumarks using(sid) group by sex));
4.视图
1.视图是一张虚拟的表,他表示一张表的部分数据或多张表的综合数据,
视图的结构是建立在表的基础上
2.视图中没有数据,只有表结构,视图中的数据在基表中获取
3.一张表可以创建多个视图,一个视图可以引用多张表
(1)创建视图
create [or replace] view `视图名`
as
sql语句
create view stu_view_1
as
select sid,sname,age,sex,city,ch,math from stuinfo left join stumarks using(sid);
#视图创建完毕后,会在对应的文件夹中保存一个.frm的文件,子文件是视图的结构
(2)查询
select * from `stu_view_1`;
(3)修改视图
alter view stu_view_1
as
select * from stuinfo;
(4)查看视图的信息
show create view stu_view_1\G
mysql> show create view stu_view_1\G
*************************** 1. row ***************************
View: stu_view_1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `stu_view_1` AS select `stuinfo`.`sid` AS `sid`,`stuinfo`.
`sname` AS `sname`,`stuinfo`.`sex` AS `sex`,`stuinfo`.`age` AS `age`,`stuinfo`.`
city` AS `city` from `stuinfo`
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.00 sec)
(5)查看视图的结构
desc stu_view_1;
mysql> desc stu_view_1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| sid | int(11) | NO | | 0 | |
| sname | varchar(255) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| city | varchar(64) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
(6)查找视图;
show tables;#它也可以查看视图
select table_name from information_schema.views;
show table status where comment='view'\G;
(7)删除视图
drop view stu_view_1;
找出语文成绩最高的男生和女生:
select * from stu_view_1 where(sex,ch) in (select sex,max(ch)from stu_view_1 group by sex);
#把查询当作是数据源,命名为t
select * from (select * from stu_view_1 order by ch desc) t group by sex;
create view vw_1
as
select * from stu_view_1 order by ch desc;
(8)视图算法理论
1.merge:合并算法(将视图语句与外层语句合并执行)
2.temptable:临时表算法(将视图执行的结果作为一个临时表,再执行外层语句)
3.undefined:未定义算法,由mysql自己决定,一般使用的都是merge
#给视图指定算法
create or replace algorithm=merge view vm_2
as
select * from stuinfo order by sid desc;
5.事务
什么是事务?
1.事务是一个不可拆分的工作单元;
2.事务是作为一个整体向系统提交的,要么一起执行,要么一起不执行;
3.事务是不支持嵌套的
(1)事务的特性
1.原子性:不可拆分
2.一致性:要么一起执行,要么一起不执行
3.隔离性:事务彼此没关系
4.永久性:一旦执行成功,不可修改
#事务先要开启
start transaction;
#语句
insert into stuinfo values(null,'百强',1,18,'黑龙江');
insert into stuinfo values(null,'百强1',2,18,'黑龙江');
insert into stuinfo values(null,'百强2',3,18,'黑龙江');
#不成功回滚
rollback;
#成功->提交
commit;
#注意
事务只能在innodb引擎下使用
(2)自动提交事务
#查看自动提交事务是否开启
show variables like 'autocommit';
#如果是关的
set autocommit=0|1
6.索引
优点:加快了mysql的查找速度
缺点:
1.更多的储存空间来储存索引字段名
2.myisam使得insert,update,delete的速度变慢了(查询操作占用90%,cud操作加起来占用10%的操作还未必能达到)
(1)创建索引的原则
#适合
1.用于频繁查找的列(字段)
2.用于条件判断的列(字段)
3.用于排序的列(字段)
#不适合
1.列中的数据并不多
2.表中的数据量很小
(2)索引的类型
1.普通索引
create index isex on stuinfo(sex);
alter table stuinfo add index isname(sname);
2.唯一索引
unique
#创建方法如上
3.主键索引
primary key
#创建方法如上
4.全文索引
#xunsearch是一种工具
fulltext key
只能在myisam表引擎下使用(innodb,myisam创建的话都能创建,但是innodb中无效),使用like的时候提升效率.# where nama like '%你%';
create FULLTEXT KEY zu on stuinfo(age,city);
5.创建多列索引(组合索引)
create index zu on stuinfo(age,city);
alter table stuinfo add index isname(sname,city);
(3)删除索引
drop index zu on stuinfo;
#mysql优化
innodb=>执行 insert(多条) update delete 非常快
myisam=>执行 insert(单次) select 非常快
1.mysql需要搭建在远程的服务器中
2.1mysql台服务器中的所有表类型全部是innodb,其它4台服务器中的表全市myisam的
主从复制(一主多从)(双主多从)
master(主的mysql服务器 innodb) IO
#从服务器监听主服务器
slave(从服务器是myisam) 10.11.58.1 () IO
slave(从服务器是myisam) 10.11.58.2
slave(从服务器是myisam)
slave(从服务器是myisam)
从服务器(myisam)sub监听主服务器
监听什么?
ip地址
干吗了?
主服务器插入,修改,删除一条,从服务器插入,修改,删除一条.
从服务器主要是用来查询的.
innodb 和myisam有什么区别吗?
表文件,数据存储方式不同.
innodb可以使用外键,事务,唯一键
myisam可以使用全文索引
mysql怎么优化? IO
添加索引,常用被查询的,数据量大的
分表: 垂直分表和水平分表
垂直分表是分字段
水平分表是分数据的数量
数据库分库,主从
硬件方面考虑,固态硬盘比机械强
扫一扫
1920
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
