当前位置: 澳门新濠3559 > 数据库 > 正文

澳门新濠3559Use数据库名(切入数据库),union 合并

时间:2019-11-29 07:15来源:数据库
union查询就是把2条或者多条sql语句的查询结果,合并成一个结果集。 Mysql-h localhost –uroot –p (Cmd窗口连接数据库) 一、多表查询连接的选择: 相信这内连接,左连接什么的大家都比较熟

union查询就是把2条或者多条sql语句的查询结果,合并成一个结果集。

Mysql-h localhost –uroot –p (Cmd窗口连接数据库)

一、多表查询连接的选择:

澳门新濠3559 1

相信这内连接,左连接什么的大家都比较熟悉了,当然还有左外连接什么的,基本用不上我就不贴出来了。这图只是让大家回忆一下,各种连接查询。 然后要告诉大家的是,需要根据查询的情况,想好使用哪种连接方式效率更高。(这是技术文)

如:sql1: N行,sql2: M行,sql1 union sql2 ---> N+M行

Truncate表名(清空表)

二、MySQL的JOIN实现原理

在MySQL 中,只有一种Join 算法,就是大名鼎鼎的Nested Loop Join,他没有其他很多数据库所提供的Hash Join,也没有Sort Merge Join。顾名思义,Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与Join,则再通过前两个表的Join 结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复。 ——摘自《MySQL 性能调优与架构设计》

                                                 –

1、能否从2张表查询再union呢?

Show databases(查看数据库列表)

三、补充:mysql对sql语句的容错问题

即在sql语句不完全符合书写建议的情况,mysql会允许这种情况,尽可能解释它:

1)一般cross join后面加上where条件,但是用cross join+on也是被解释为cross join+where;

2)一般内连接都需要加上on限定条件,如上面场景一;如果不加会被解释为交叉连接;

3)如果连接表格使用的是逗号,会被解释为交叉连接;

注:sql标准中还有union join和natural inner join,mysql不支持,而且本身也没有多大意义,其实就是为了“健壮”。但是其实结果可以用上面的几种连接方式得到。

可以,union 合并的是"结果集",不区分在自于哪一张表.

Use数据库名(切入数据库)

三、超大型数据尽可能尽力不要写子查询,使用连接(JOIN)去替换它:

当然,关于这句话,也不一定就全是这样。

1)因为在大型的数据处理中,子查询是非常常见的,特别是在查询出来的数据需要进一步处理的情况,无论是可读性还是效率上,这时候的子查都是更优。

2)然而在一些特定的场景,可以直接从数据库读取就可以的,比如一个表(A表 a,b,c字段,需要内部数据交集)join自己的效率必然比放一个子查在where中快得多。(这真是技术文)

2、取自于2张表,通过"别名"让2个结果集的列一致。那么,如果取出的结果集,列名字不一样,还能否union.

Show tables (查看数据库表)

四、使用联合(UNION)来代替手动创建的临时表

UNION是会把结果排序的!!!

union查询:它可以把需要使用临时表的两条或更多的select查询合并的一个查询中(即把两次或多次查询结果合并起来。)。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用union来创建查询的时候,我们只需要用UNION作为关键字把多个select语句连接起来就可以了,要注意的是所有select语句中的字段数目要想同。

#

要求:两次查询的列数必须一致(列的类型可以不一样,但推荐查询的每一列,相对应的类型要一样)

可以来自多张表的数据:多次sql语句取出的列名可以不一致,此时以第一个sql语句的列名为准。

如果不同的语句中取出的行,有完全相同(这里表示的是每个列的值都相同),那么union会将相同的行合并,最终只保留一行。也可以这样理解,union会去掉重复的行。

如果不想去掉重复的行,可以使用union all。

如果子句中有order by,limit,需用括号()包起来。推荐放到所有子句之后,即对最终合并的结果来排序或筛选。

澳门新濠3559 2

注意:

1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名

2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同

UNION ALL的作用和语法:

默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。

澳门新濠3559 3

可以,而且取出的最终列名,以第1条sql为准

CREATE DATABASE abccs (创建数据库)

五、总结

(1)对于要求全面的结果时,我们需要使用连接操作(LEFT JOIN / RIGHT JOIN / FULL JOIN);

(2)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

澳门新濠3559 4

备注、描述、评论之类的可以设置为 NULL,其他最好不要使用NULL。

不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num = 0

(3)in 和 not in 也要慎用,否则会导致全表扫描,如:

澳门新濠3559 5

对于连续的数值,能用 between 就不要用 in 了:

澳门新濠3559 6

很多时候用 exists 代替 in 是一个好的选择:

澳门新濠3559 7

(4)尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

(5)尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

(6)不要以为使用MySQL的一些连接操作对查询有多么大的改善,其实核心是索引(别打我,下一篇讲)(这绝对是技术文)

3、union满足什么条件就可以用了?

Desc user (查看user的表结构)

 

 

只要结果集中的列数一致就可以.(如都是2列或者N列)

insert into user (uid,name,age) values

4、union后结果集,可否再排序呢?

(1,'lisi',23); (插入语句)

可以的。Sql1 union sql2 order by 字段

insert into user values

注意: order by 是针对合并后的结果集排的序.

(1,'lisi',89),(2,'lucy',17),(3,'lilci',19);(插入多行)

5、如果Union后的结果有重复(即某2行,或N行,所有的列,值都一样),怎么办?

select * from user; (查询数据表)

这种情况是比较常见的,默认会去重.

update user set age=8 where name

6、如果不想去重怎么办?

='lisi'; (修改数据值)

union all

update user set name='nobody',age=9 where

 

uid=2; (修改多列数据值)

下面通过实例来验证union的常用特性以及应用:

update user set age =89;(修改所有列表数据值,使用时应注意)

CREATE TABLE num_a (
    id VARCHAR( 3 ) NOT NULL,
    num INT(3 ) UNSIGNED NOT NULL
)CHARSET utf8 ENGINE MYISAM;
CREATE TABLE num_b (
    id VARCHAR( 3 ) NOT NULL,
    num INT(3 ) UNSIGNED NOT NULL
)CHARSET utf8 ENGINE MYISAM;

INSERT INTO num_a VALUES ( 'a', 5 );
INSERT INTO num_a VALUES ( 'b', 10 );
INSERT INTO num_a VALUES ( 'c', 15 );
INSERT INTO num_a VALUES ( 'd', 10 );

INSERT INTO num_b VALUES ( 'b', 5 );
INSERT INTO num_b VALUES ( 'c', 15 );
INSERT INTO num_b VALUES ( 'd', 20 );
INSERT INTO num_b VALUES ( 'e', 99 );

学习至第六课(六分15秒)

澳门新濠3559 8     澳门新濠3559 9

delete from user where uid=4;(删除uid为4的行)

 

delete from user;(删除该表下的所有数据,注意使用)

 1,union会去掉重复的行

select * from user where uid=2;(单独查询一行)

SELECT id,num FROM num_a UNION SELECT id, num FROM num_b

select uid,name from user where uid>=2;(单独查询某几列)

澳门新濠3559 10

查询模型(重要)

 

1.列是变量,变量可以计算,where是表达式,值为真假。

2、order by对union后的结果集排序

2.select * from user where 1; (1为真取出所有行)

SELECT id,num FROM num_a UNION SELECT id, num FROM num_b ORDER BY num DESC

3.select * from user where 0; (0为假取出0行)

澳门新濠3559 11

4.select uid,name,age+1 from user

 

where 1;(计算变量)

3、UNION ALL不会过滤重复的行

学习至第九课(8分44秒)

SELECT id,num FROM num_a UNION ALL SELECT id, num FROM num_b

澳门新濠3559 12

澳门新濠3559 13

 

select * from goods wherecat_id=4 || cat_id=11;(逻辑或查询)

4、把num_a和num_b不同的索引结果保留, 相同的索引结果相加  然后输出:

select *from goods where cat_idin (4,11);(查询id为4和11的两条数据)

SELECT a.id, ( a.num + b.num ) AS num FROM num_a AS a INNER JOIN num_b AS b ON a.id = b.id
UNION ALL
SELECT * FROM num_a AS a WHERE NOT EXISTS( SELECT * FROM num_b AS b WHERE a.id = b.id )
UNION ALL
SELECT * FROM num_b AS b WHERE NOT EXISTS( SELECT * FROM num_a AS a WHERE a.id = b.id )
ORDER BY id ASC

select * from goods where shop_price between 100 and 500; (查询50-100之间)

澳门新濠3559Use数据库名(切入数据库),union 合并的是"结果集"。 澳门新濠3559 14

select *from goods where cat_idnot in (3,11);(查询除了3和11的所有数据)

  

select * from goods wherecat_id=3 and (shop_price<1000 or shop_price>3000) and click_count>5;(注:and优先级大于or,为了避免可以加括号)

5、第二种方法用子查询分组统计,也可以达到同样的效果

select*from goods wheregoods_name like '诺基亚%';(模糊查询任意字符)

SELECT id, SUM( num ) AS num FROM ( SELECT * FROM num_a a UNION ALL SELECT * FROM num_b b ) tmp
GROUP BY id;

select * from goods wheregoods_name like '诺基亚___';(模糊查询单一字符)

 

update mian set num=floor(num/10)*10 where num between 20 and 39;

select goods_id,concat('htc',substring(goods_name,4))from goods where goods_name like '诺基亚%';(大胆的将列看成变量,使用了substring函数与substring函数)

奇怪的null

先创建一张表create table tmp (

id int,

name varchar(20)

) charset utf8 engine myisam;

在插入两行数据

insert into tmp

VALUES

(1,'liubei'),

(2,null);

SELECT * FROM tmp where name is null;(查询为null的行)

SELECT * FROM tmp where name is not null;(查询不为null的数据行)

group分组与统计函数

SELECT avg(shop_price) from goods;(avg求平均值)

Max()求最大

Count()行数/统计

Avg()求平均数

Min()求最小

Sum()求总和

SELECT cat_id,avg(shop_price) from goods group by cat_id; (分组统计)

Having筛选

SELECT goods_id,goods_name,(market_price-shop_price) as s from goods

where 1 having s > 200;(根据列计算的结果集在进行表达式)

查询两门及两门挂科以上的学生平均成绩

select name,sum(score<60) as gk ,avg (score) as

pj FROM result GROUP BY name

HAVING gk>=2;

学习至第十六课(0分0秒)

排序

排序有可能是直接利用索引在磁盘里直接排序好,也可能没排序好,到内存里再次排序

SELECT goods_id,goods_name,shop_price from goods order by shop_price;(默认升序)

Desc(降序)asc (默认是asc升序)

SELECT goods_id,cat_id,goods_name,shop_price from goods ORDER BY

cat_id asc,shop_price desc; (按栏目升序排序在根据不同栏目价格降序排序)

Limit

SELECT * from goods order by shop_price desc LIMIT 0,3;(从0开始取三条)

子句的查询陷阱

之前where,group by, having,order by,limit。

5种子句是有严格的顺序,where,group by ,having,order by ,limit,

引出子查询

查询最新商品

第一种写法:Select goods_id from goods order by

goods_id desc limit 0,1;

第二种写法: Select max(goods_id) from goods;

第三种写法:select goods_id,goods_name from goods wheregoods_id=(select max(goods_id)fromgoods); (where型的子查询)

取每个栏目下最新的商品。

selectgoods_id,cat_id,goods_name from goods where goods_id in (SELECT max(goods_id)from goods group by cat_id);

where型子查询是内层查询的结果,作为外层的sql的比较条件。

From子查询

Select goods_id,cat_id,goods_name from (select * from goods where 1

order by cat_id asc, goods_id desc) as tmp group by cat_id;

Exists子查询

Select * from category where exists (select * from goods where

goods.cat_id=category.cat_id);(检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,)

新手1+N模式查询

以下为新手查询方式

澳门新濠3559 15

链接查询

内连接查询

select boy.hid,boy.bname,girl.hid,girl.gname FROMboy inner join girl on boy.hid=girl.hid;(内连接查询)

表一inner join表二用于连接on一个条件

只查询出条件符合的数据

左连接查询

Select boy.hid,bname,girl.hid,gnamefrom boy left join girl on boy.hid=girl.hid;

左连接以左边数据为准,查询右边相关数据,查不到的补null

Select boy.hid,bname,girl.hid,gnamefrom boy rightjoin girl onboy.hid=girl.hid;

Union查询

Union查询就是把2条或多条sql的查询结果,合并成1个结果集

Sql1 N行

Sql 2 M行

Sql1 union sql2,N+M行

select uid,name from user union select id,name from tmp;(查询两张表的结果)

场景:2条语句,各自的where条件非常复杂,可以简化成简单条件,再union

Union的语句必须满足1个条件:各语句取出的列数相同

列名称未必要一致,列名称会使用第1条sql的列名称为准

注意:使用union时,完全相等的行,将会被合并。

合并是比较耗时的操作。一般不让union进行合并

使用”union all”可以避免合并。

讨论: union的子句中,不用写order by,sql合并后得到的总的结果,可以order by,

子句order by失去意义。

(SELECT * FROM a)

union ALL

(SELECT * FROM b)

ORDER BY num desc;

可形成总的结果集后在排序。

select id,sum(num) from (SELECT * FROM a

union ALL

SELECT * FROM b) as tmp

GROUP BY id; (加子查询)

DML复习完毕

DDL

Drop table表名(删除表)

create table t1 (sn int,name varchar(10));(建表语句)

学习至第29课0分0秒

整型列

数值型---整型,浮点型,定点型

字符串-char,varchar,text

日期时间类型

2012-12-13

14:26:23

Bigint 8字节

int 4字节

mediumint 3字节

smallint 2字节

tinyint 1字节

澳门新濠3559 16

列的可选参数

Unsigned,zerofill,M

Unsigned无符号,列的值从0开始不为负(默认为负数列,对应上表,添加unsigned属性后可存储更大的值,却不可存储负数。)

alter table t2 add unum tinyint unsigned;(新增一个unsigned属性列)

zerofill适合用于学号,编码等,固定宽度的数字,可以用0填充至固定宽度

学号–->1->0001

学号—>123->0123

思路zerofill填充至多宽?M代表宽度

alter table t2 add sntinyint(5) zerofill;(添加0填充)

zerofill属性默认决定列为unsigned

宽度并不是限制范围。

浮点列与定点列

Float(M,D),M是精度,总位数,D标度,小数点后面的位数

Float/double,有精度损失

Decimal定点型,更精确

Char(10)定长十个字符十个字条的宽度!定长类型

Varchar(10)最长10个,变长类型

Char型,如果不够m个字符,内部用空格补齐,取出时再把右侧空格删掉

注:这意味着,如果右侧本身有空格,将会丢失。

速度上:定长速度快些

用户名:char

Blob;是二进制类型,用来存储图像,音频等二进制信息。

意义:2进制,0-255都有可能出现。

Blob在于防止因为字符集的问题,导致信息丢失。

比如:一张图片中有0xFF字节,这个在ascii字符集认为非法。在入库的时候。被过滤了。

Enum枚举型,是定义好。值。值就在某种范围内。

Gender(“男”,“女”)Insert时。只能在选男或者女

Set :和枚举差不多,但是可以有多个值。

日期时间类型

create table t9(

id int,

ts timestamp

);

Timestamp(操作该表自动获取更新时间戳但是并不建议)

时间戳采用int unsigned存储即可。

列的默认值

澳门新濠3559 17

create table t10(

id int not null default 0,

name char(10) not nulldefault ''

);

Default为列值为空时的默认值

主键与自增

主键primary key,此列不重复,能够区分每一行!

一张表只能1列为auto_increment,且此列必须加索引(index/key)

一张表如果所有列都定长,可以极大的提高查询速度。

注:表设计优化

定长与变长分离

常用与不常用列分离

澳门新濠3559 18

列的删除增加与修改

Rename table Regist3 to reg3(修改表名)

Alter table reg3 add height tinyint unsigned not null default 0;(新增列)

Alter table reg3 drop column height(删掉列字段)

After weight(设置到指定字段后面)

Alter table表名change height shenggao smallint;(修改列)

Modify(不能修改列名)

视图(view)

Select goods_id,goods_name,(market_price-shop_price) as sheng from

goods;

Create view as

mysql> create view vgoods as select

goods_id,goods_name,(market_price-shop_price

) as sheng from goods;(创建一个视图)

View又被称为虚拟表,view是sql的查询结果。

有什么用?

1:权限控制时可以用

比如,某几个列,允许用户查询,其他列不允许。

可以通过视图,开放其中一列或几列,直到权限控制的作用。

2:简化复杂的查询。

查询每个栏目下商品的平均价格,并按平均价格排序,查出平均价前3高的栏目。

3:视图能不能更新,删除?

答:如果视图的每一行,是与物理表,一一对应的,则可以。

View的行是由物理表多行经过计算得到的结果,view不可以更新的。

视图的algorithm

视图放哪儿了(存储的都是语句)

对于简单查询形成的view,再对view查询时,如where,order等等,

可以把建视图物理语句+查视图的语句=====合并成==>查物理的语句

这种视图的算法叫merge(合并)

学习至第39课9分25秒

也有可能,视图的语句本身比较复杂,很难再和查询视图的语句合并,mysql可以先执行视图的创建语句,把结果集形成内存中的临时表,然后再去查临时表.temptable

Create algorithm=merge view (用于视图比较简单的直接用merge算法)

Create algorithm=temptable view(用于比较复杂的视图)

或者直接view让数据库自己去判断。

表/视图管理语句

澳门新濠3559 19

存储引擎的概念

澳门新濠3559 20

字符集与乱码问题

字符集

统计字符集即不会乱码,

声明表:charset utf8

客户端声明utf8

如mysql黑窗口声明:set names utf8.

校对级

就是服务器连接排序规则

索引的概念

MYI为索引文件


索引是数据的目录,能快速定位数据的位置

索引提高了查询速度,降低了增删改的速度,并非加的越多越好。

一般在查询频率高列上加,而且在重复度低列上加效果更好。

Key,普通索引,

Unique key,唯一索引

Primary key主键索引

Fulltext全文索引(中文环境下,全文索引无效,要分词+索引,一般用第三方解决方案,如sphinx)

创建索引

澳门新濠3559 21

索引长度:建索引时,可以索引时,key只索引列的前一部分的内容,比如,前10个字符。如key email(email(10))

多列索引,就是把2列或多列的值,看成一个整体,然后建索引。

澳门新濠3559 22

Explain查看索引信息。

冗余索引就是在某个列上,可能存在多个索引

澳门新濠3559 23

索引操作

查看: show index from表名也可以show create table表名

删除:alter table表名drop index索引名

或者drop index索引名on表名

添加: alter table表名add [index/unique]索引名(列名);

添加主键索引

澳门新濠3559 24

以上为DML DDL知识结束。

事务

创建转账表

澳门新濠3559 25

start transaction;开启事务

commit;结束提交事务


rollback;(回滚)

原子性(Atomicity)。事务中的所有操作要么全部执行成功,要么全部取消。

一致性(Consistency)澳门新濠3559,。事务开始之前和结束之后,数据库完整性约束没有破坏。

隔离性(Isolation)。事务提交之前对其它事务不可见。

持久性(Durability)。事务一旦提交,其结果是永久的。

编辑:数据库 本文来源:澳门新濠3559Use数据库名(切入数据库),union 合并

关键词: