当前位置:文档之家› msyql 多表查询

msyql 多表查询

msyql 多表查询
msyql 多表查询

多表查询

1.简单查询

内连接查询

1】简单的连接查询

给两个表找中间桥梁: select 字段列表from 表1,表2 where 表1.s_di=表2.s_id;

eg: select s_name,marks from student_info,marks_info where

student_info.s_id=marks_info.s_id;

eg: select s_name,marks,c_name from

student_info,marks_info,class_info where marks_info.s_id=student_info.s_id and

marks_info.c_id=class_info.c_id;

问题:如果表名很长,书写繁琐,代码不清晰。

解决:使用表别名

定义格式:1】表名as 别名

2】表名别名

别名作用域只在本查询语句中

eg : select s_name,c_name,marks from student_info s,marks_info

m,class_info c where s.s_id=m.s_id and m.c_id=c.c_id

查询学生的编号,姓名,科目,成绩

问题:如果查询的字段在多个表中存在,那么就必须在字段前添加表名或表的别名

select s.s_id,s_name,c_name,marks from student_info s,marks_info

m,class_info c where s.s_id=m.s_id and m.c_id=c.c_id

2】SQL内外连接(是SQL语言中的多表分类,查询效率比简单查询和子查询在某种情况下要高)

内连接格式:select 字段列表from 表1 inner join 表2 on 表1.字段=表2.字段

eg :查询学生的姓名和成绩

select s_name,c_name,marks from student_info inner join marks_info on

student_info.s_id=marks_info.s_id inner join class_info on

marks_info.c_id=class_info.c_id;

(我试了下,这个作用其实和那个把两个表中相同的如ID号再把其对应的字段放在一起。和select * from table1,table2 where table1.id=table2.id差不多,效果一样)3】自然查询: 是简单的查询中去掉重复的记录在字段中添加distinct

外连接查询outer join

1】左连接(left join)select 字段列表from 表1 left join 表2 on 表1.字段=表2.字段

(查询语句以表1为参照表) 如果表2中有与表1相匹配的记录,那么就显示记录,如果表2中没有与表1相匹配的记录则在相对应的字段上填充null)

问题:如果查询的记录,必须以某个表作为参照表,打印出记录相同的所有的记录SELECT s_name, marks FROM student_info s LEFT JOIN marks_info m ON

s.s_id = m.s_id

2】右连接(right join) (查询语句以表2为参照表)

如果表1中有与表2相匹配的记录,那么就显示记录,如果表1中没有与表2相匹配的记录则在相对应的字段上填充null)

SELECT s_name, marks FROM marks_info m RIGHT JOIN student_info s ON

s.s_id = m.s_id

3】全连接full join (将两个记录集进行合并,并且去掉重复的记录)

mysql 不支持全连接

合并记录集:union (all |distinct)

union all :将两个记录集进行合并,不去掉重复的记录

union (distinct): ...去掉重复的记录

注意:

----------------------------记录集在合并时,查询的结构(字段类型要完全一样)----------------------------------

eg :

SELECT s_name, marks FROM student_info s LEFT JOIN marks_info m ON s.s_id = m.s_id

union

SELECT s_name, marks FROM marks_info m RIGHT JOIN student_info s ON s.s_id

= m.s_id

交叉查询

笛卡尔积: cross join

格式: select 字段列表from 表1 cross join 表2

自查询:

自己需要的筛选条件来自于自己表的某个字段

feiker (2010-1-15 17:12:27)

MYSQL:

1.问题:检索出本校学生所在的地区有哪些?(记录中有重复)

解决1.distinct

2.group by

2.问题:检索出id为散列数据(1,3,4)的学生基本信息

1. or -------where id=1 or id=3 or id=4

2. in -------检索散列记录的where id in (1,3,4); 查询效率比or 要高

3. 问题: 检索出id 在1和3之间的连续记录

1. between 1 and 3 ;查询效率比and 高

2. and 和比较运算符where id>1 and id<3

4. 问题:检索出id 不是4的记录

1. where id <>|!= 4; <>:sql数据库中的不等于!=:mysql数据库管理系统中的不等于

2. where id not 4;

5. 问题: 检索出id 非散列数据(1,3,4)中的学生记录?

1. not in(1,3,4)

6. 问题: 检索出id 非空记录

记录为空: 中间没有记录,但记录存在=''

自己打的null: 打入的字符串为null 查询用='null'

系统null : 就是记录没有定义is null

7. 问题: 根据字段中的某些查询数据:

检索出学生名中含有d字符的所有记录

‘_’:代表任意一个字符

'%': 代表任意的多个字符

_d_

like 尽量不要使用like %d%

注意: 在Mysql 查询语句中尽量不要使用like 模糊查询,使用时尽量不要使用%开头

聚合函数尽量要和group by 一起使用(先分组后计算)

只计算各个组内的数据

格式: 函数名(distinct|all 字段名|*)

有distinct 先筛选在->分组->计算

8. 字段别名:字段名as 新名字

*9. SQL语言的常量

问题: 如何检索出下面格式的数据

编号 1 tom

编号 2 jjj

编号 3 hhh

解决方案

定义格式: select '编号',s_id,s_namefeiker (2010-1-15 17:13:06)

1.多表查询

超过一个表的综合查询

注意找到表与表之间的纽带

分类

1.内连接

等值连接---查询结果带有重复记录

select 字段列表from 表列表where 表1.字段1=表2.字段1 and

表1.字段2=表2.字段2 and......

如果查询的字段在多个表中存在要使用"表明.字段名"的方式来表明字段是哪个表中的

e.g:select

student_info.s_id,s_name,age,area,c_name,mark from student_info,marks,class

where student_info.s_id=marks.s_id and marks.c_id=class.c_id

如果重复字段较多可以给使用的表起别名

表名as 别名|表名别名

e.g:select s.s_id,s_name,age,area,c_name,mark

from student_info as s,marks as m,class as c where s.s_id=m.s_id and

ms.c_id=c.c_id

先分组后计算

e.g:select c_name,avg(mark) as "平均成绩" from class,marks where class.c_id=marks.c_id group by c_name

不等值连接

自然连接---查询结果没有重复记录(distinct|group by)

2.外连接

左连接

右连接

全连接

3.交叉连接

cross---select 字段列表from 表1 cross join 表2

e.g:select s_name,mark from student_info cross join

marks

4.自连接

inner join

sql语句中

内连接

select 字段列表from 表1 inner join 表2 on

表1.字段1=表2.字段2

e.g:select s_name,mark from student_info

inner join marks on student_info.s_id=marks.s_id

外连接

左连接

select 字段列表from 表1 left outer join 表2 on

表1.字段1=表2.字段1

e.g:select s_name,mark from student_info left

outer join marks on student_info.s_id=marks.s_id

注意以左边的表为标准用右边的表进行匹配如果右边表中有匹配的记录那么显示

没有就填充null

右连接

select 字段列表from 表1 right outer join 表2 on 表1.字段1=表2.字段1

e.g:select s_name,mark from student_info right

outer join marks on student_info.s_id=marks.s_id

注意以右边的表为标准用左边的表进行匹配如果左边表中有匹配的记录那么显示

没有就填充null

全连接---在mysql中不支持全连接或使用union all

union all 将两个记录集合并不去除重复记录默认情况下没有all

相当于加了distinct去掉重复记录相当于全连接

2.嵌套查询(子查询)

内查询记录比较庞大而最后需要的查询结果却寥寥无几时

select 字段列表from 表1 where 字段in、>、<、any、all、some、exists(筛选后的字段)

e.g:select s_name from student_info where s_id in(select s_id

from marks where mark>60)

any 任何一个符合条件

all 子查询所有结果都成立此条件才成立

exists 子查询中有结果返回那么主查询就执行如果没有结果返回

运算符

> 、< 、>= 、<= 、!= 、<>、in、all、any、exists

not (in|all|any|exists)

3.视图

源于表而又高于表的虚拟表

视图可以像正常表进行查询但是不能进行添加删除可以修改

格式---create view 视图名as 查询语句

范围频繁使用到多个表中的数据时将使用到得数据生成视图

删除视图---drop view 视图名;完全一样

php中使用视图---与普通表查询

4.索引:在记录上建立的虚拟目录

索引文件目录->索引文件件->记录

1.避免查询数据时进行全表扫描,插入和删除更快

2.在使用max、min等聚合函数时更加有效

3.多表查询时更加有效

缺点1.浪费磁盘空间 2.索引过多使索引文件目录过大降低查询速度

索引分类

按纪录性质分

普通索引---可以在任何的字段上建立索引

唯一索引---不能在重复的记录上建立索引

主键---索引名唯一记录唯一

按索引占有的字段分

单列索引

多列索引

按索引的内容来分

短索引(最左前缀)---将字段上的部分数据作为索引内容

创建普通索引方法-----标示符:key->mul (desc 表名查看)----不唯一可以有重复记录

Non_unique 是否索引唯一

已存在表

create index 索引名on 表名(字段名);

e.g.create index id_index on student_info(s_id)

查看方法

show index fron 表名;e.g.SHOW INDEX FROM student_info

已存在表修改表的命令

alter table 表名add index 索引名(索引字段);

e.g.alter table student_info add index name_index (s_name);

不存在表创建索引

create table person (pid int(4),pname

char(30),index 索引名(字段名));

唯一索引索引唯一记录可重复-----标示符:key->unl

创建方法

create unique index 索引名on 表名(字段名);

e.g.create unique index id_index on student_info(s_id)

已存在表修改表的命令

alter table 表名add unique 索引名(索引字段);

e.g.alter table student_info add unique name_index (s_name);

不存在表创建索引

create table person (pid int(4),pname

char(30),unique 索引名(字段名));

主键索引唯一建索引的字段记录唯一-----标示符->pri

不存在表创建单列主键

create table person (pid int(4),pname

char(30),primary key 索引名(字段名));

不存在表创建多列主键

create table person (pid int(4),pname

char(30),primary key 索引名(字段名字段名));

表存在增加主键

alter table表名add primary key(字段列表)

如果一个字段不能唯一的表示一条记录的话那么我们可以使用多个字段唯一的表示一条记录这个就叫做多列主键

三种索引区别

普通索引:可以在任何字段上建立

唯一索引:一个表中可以建立多个记录不能有重复记录可以为空

主键:一个表只能有一个记录不能重复也不能为null

单列索引单列普通索引|唯一索引|单列主键

多列索引多列普通索引|唯一索引|多列主键

已存在表

create index 索引名on 表名(字段列表);

e.g.create index id_index on student_info(s_id,s_name)

查看方法

show index fron 表名;e.g.SHOW INDEX FROM student_info

已存在表修改表的命令

alter table 表名add index 索引名(索引字段);

e.g.alter table student_info add index name_index (s_name);

不存在表创建索引

create table person (pid int(4),pname

char(30),index 索引名(字段列表));

优点

建立一个多列索引相当于建立多个单列索引

节省磁盘空间

短索引:必须建立在字符型或者文本型的字段上

普通短索引唯一短索引

Sub_part :标明是短索引里面是截取的字符数

表已存在

create index 索引名on 表名(字段列表)

e.g:create index index1 on news(title(10))

什么地方建立索引

一般只在设计数据库已经设计在内

where语句后频繁出现的字段在此字段上建立

删除索引

1.drop index 索引名on 表名

2.alter table 表名drop index 索引名

选择索引列:

1)搜索的索引列,不一定是所要选择的列。换句话说,最适合索引的列是出现在WHERE

子句中的列,或连接子句中指定的列,而不是出现在SELECT 关键字后的选择列表中的列:2)对于惟一值的列,索引的效果最好(性别不行)

3)多列索引相当于,多个索引

6.程序逻辑

1.存储过程

存储子程序,mysql的自定义函数有自己的控制结构

和检测语句

变量的定义:@;

set @var='100';

输出语句:

select 变量名

同时定义多个变量

set 变量[=值],变量2[=值]

储存过程的定义格式

create procedure 存储过程名(in|out|inout 参数名类型)

储存主题

步骤

定义界定符

delimiter //

1.定义存储过程

定义变量(out,inout)

2.调用存储过程(call 存储过程名)

查询变量结果

e.g:select * from student_info;

create procedure stu_pro()

select * from student_info;

call stu_pro();

语句块begin 过程体end

存储过程中的三种参数

in ---相当于php中的形参往存储过程中传值

out---相当于php中的return 从存储过程带回值存储过程中能有多个out型参数但是php中只能有一个return

inout---传入传出参数

e.g:检索出id=3|4|5的信息

create procedure stu_id_pro(in sid

int(4))

begin

select * from student_info where

s_id=sid;

end

//

call stu_id_pro(3);

//

传出参数

create procedure stu(out sid int (4))

begin

set sid=100;

end

//

set @a=0;

//

call stu(@a);

//

select @a;

//

变量赋值

set 变量名=值

select 字段名into 变量名from 表名where sid=值赋值只能一个

返回id=3的年龄返回

create procedure age(in sid int(4),out age1

int(4))

begin

select age into age1 from

student_info where s_id=sid;

end//

call age(3,@a)//

select @a//

inout参数 e.g使用存储过程使变量值+1返回

create procedure sum1(inout a int (4))

begin

set a=a+1;

end//

set @p=100//

call sum1(@p)//

select @p//

程序控制结构

分支语句

单分支语句if

多分支语句case

循环

while

repeat

loop

1.if语句

if 条件then

操作

elseif 条件

else

end if

create procedure if_pro(in tag int(4))

begin

if tag=1 then

select * from student_info;

else

elect * from class;

end if ;

end//

2.case

case 变量名

when 值then 操作

else

操作;

end case;

create procedure if_pro1(in tag int(4))

begin

case tag

when tag=1 then

select * from student_info;

when tag=2 then

select * from class;

when tag=3 then

select * from people;

else

select * from person;

end case;

end//

[2]

case

when 条件1 then 操作1

when 条件2 then 操作2

...

else

操作

end case;

create procedure if_pro2(in tag int(4))

begin

case tag

when tag>6 then

select * from student_info;

when tag>3 then

select * from class;

else

select * from person;

end case;

end//

while循环-----条件成立时执行循环

while 条件do

操作

end while

e.g create procedure sums1()

begin

declare tag int(4) default 0;

declare num int(4) default 0;

while num<=10 do

set tag=num+tag;

set num=num+1;

end while;

select tag;

end//

repeat-----条件成立退出循环

repeat

操作

until

end repeat

e.g create procedure sums2()

begin

declare tag int(4) default 0;

declare num int(4) default 0;

repeat

set num=num+1;

set tag=num+tag;

until num>=10

end repeat;

select tag;

end//

loop循环

leave:跳出循环

标签名:loop

操作

if 条件成立then

leave 标签名;

else

iterate 标签名;

end loop 标签名;

e.g:

create procedure sums3()

begin

declare tag int(4) default 0;

declare num int(4) default 0;

loop1:loop

set num=num+1;

set tag=num+tag;

if num>=10 then

leave loop1;

else

iterate loop1;

end if;

end loop loop1;

select tag;

end//

存储过程---游标用来存储记录集(数据结构)

步骤

1.定义游标

declare cursor 游标名for select 语句

2.打开游标

open 游标名;

3.取得游标中的每条记录

fetch 游标名into 变量列表;

fetch 语句触发handler 根据handler有两条路可走1.继续往下执行2.捕获错误设置错误标示

循环依靠handler执行的结果判断是否循环取值

4.关闭游标

close 游标名;

mysql错误句柄

declare 执行什么操作(continue|exit) handler for sqlstate 错误类型set 操作

e.g:declare continue handle for sqlstate "0200" set tag=1;

错误类型

01---sql错误

02---数据未发现错误

/**********************************************e.g.***************************** ***************************

create procedure money_pro3()

begin

declare a int(4) default 0;

declare b int(4) default 0;

declare tag int(4) default 0;

declare cur1 cursor for select sid,marks from stu;

declare continue handler for sqlstate "02000" set tag=1;

open cur1;

repeat

fetch cur1 into a,b;

if b<60 then

update stu set money=500 where sid=a;

elseif b<70 then

update stu set money=1000 where sid=a;

elseif b<80 then

update stu set money=2000 where sid=a;

elseif b<90 then

update stu set money=3000 where sid=a;

else

update stu set money=4000 where sid=a;

end if;

until tag

end repeat;

close cur1;

end

create procedure cha3()

begin

declare a int(4) default 0;

declare b int(4) default 0;

declare c int(4) default 0;

declare tag int(4) default 0;

declare cur1 cursor for select a.id,a.num,b.num from a,b where

a.id=

b.id;

declare continue handler for sqlstate "02000" set tag=1;

open cur1;

repeat

fetch cur1 into a,b,c;

if b

update a set a.num=c where id=a;

update b set b.num=b where id=a;

end if;

until tag

end repeat;

close cur1;

end

******************************************************************************* *****/

2.触发器

存储过程是用户手动调用的mysql程序逻辑

触发器是用户执行某种操作之后mysq自动执行的程序逻辑

创建触发器的语句

create trigger 触发器名

before|after

insert|update|delete //触发操作

on 表名称

for each row

begin

触发器sql语句

end

触发时间

before e.g:before delete on stu;-----在删除stu表的记录前触发

after e.g:after delete on stu;-----在删除stu表的记录后触发

关键字

new old

在插入操作时只有new

更新时new为即将更新的数据old为原数据

在删除操作时只有old

create trigger sum

before

insert

on a

for each row

begin

set new.num=new.num+1;

end

create trigger change3

after insert on student

for each row

begin

declare a int(4) default 0;

declare b char(30);

declare tag int(4) default 0;

declare t int(4) default 0;

declare cur1 cursor for select name from school;

declare continue handler for sqlstate "02000" set tag=1;

open cur1;

repeat

fetch cur1 into b;

set a=new.school;

if b=a then

set t=1;

end if;

until tag

end repeat;

if t=0 then

select "hello";

insert into school(name) values('a');

end if;

close cur1;

end

存储过程在php中的使用-----在mysq中用于存储过程的命令在php中使用mysql_query()来执行

php中mysql函数不支持结果集的返回使用pdo mysql里数据库抽象层或mysql扩展类

create procedure pro2(in a int(4),out b int(4))

begin

set b=a;

end

7.数据库优化(sql语句的优化和数据库设计的优化)

1.编码问题---设计数据库的编码和浏览器上的编码统一

客户端编码和服务器编码浏览器会自动转换

2.设计表时,不能出现表中有表的情况

3.在同一表中字段尽量不要很多

4.在同一数据库中不能出现很多表--------1000表

5.字段中设计字段类型时尽可能使用较小的类型char要比varchar小得多int比double、float小

6.使用存储过程替代php频繁的访问数据库的简单逻辑

7.索引要有用时才能建立不能建立很多的索引浪费空间,降低索引效率

8.设计数据库时尽量让设计的数据库符合第三范式

9.在设计字段时,字段能不为null的就不为null-------not null

具体操作优化

1.查询时不要查询不必要的字段

2.尽量不使用模糊查询,如果必须使用尽量不要在规则内有百分号

3.在合理的字段上建立索引可以加快连接查询的速度

4.使用join链接代替普通的多表连接(=)

如果连接的字段上有索引,那么使用join连接查询代替子查询如果没有索引,子查询中的记录很大而有效记录很小,那么推荐使用子查询,但是子查询的深度不能过深

5.如果表中多个字段经常同时出现在where后,那么建立多列索引

6.在记录集合并时---union,尽量的不要删除重复的记录而使用union all

7.使用聚合函数时,尽量不要使用where语句count(sid)

8.group by 分组和order by排序时,如果后面跟的字段一样,那么顺序要一样

9.建立索引时,尽量的建立短索引

10.建立索引时,尽量的建立唯一索引

11.在插入数据时,尽量的写出被插入的字段列表,而不是*

12.频繁使用到多表中的数据时,建立视图

13.where语句后的多个条件是,尽量的将能多记录过多筛选的条件放到前面

14.where语句多个条件时,去掉不必要的()和不必要的条件

15.插入数据时:插入数据

1.insert values;

2.insert values,values,values;

3.load

插入速度高------>低

批量插入------>多条记录插入------>单条注意插入

数据库安装

1.创建标准的配置文件

2.检测锁定文件是否存在--------------------防止重复安装毁坏原有数据

3.如果存在,跳转到index页面,如果不存在,读取标准配置文件中内容

4.将用户填写的数据库配置替换到上面读取的字符串内容中

5.将配置好的字符串写道用户使用的配置文件

6.生成锁定文件,锁定项目我也来说两句查看全部回复用户登录

用户登录用户名: 密码: 验证码: 换一张记住我注册新用户| 找回密码最新发表高晟倩2010旅行用婴儿车,有车族父母的选择

拥有幸福生活贪心女友把我当提款机用完就甩拥有幸福生活口述:纯朴老公竟有个“网妻”高晟倩2010外贸童装库存批发,网店实体店进货可

resteps生活中的疾病预防的方法高晟倩2010谁知道福步外贸库存批发网的网址?淘高晟倩2010谁知道福步外贸库存批发网的网址?淘

feikerJson 解决中文问题zgfm12什么是角座阀?角座阀的工作原理- ╄中国飞客网╄| 站点地图| 友情链接| 上网助手|

标题内容作者

中国飞客网宗旨Copyright Supesite https://www.doczj.com/doc/f7525932.html, © 2009-2010

Make my minds to serve for the People

苏ICP备09088643号

相关主题
相关文档 最新文档