MySQL之单表、多表查询、连接、视图

759876uyt

select 查询类型:单表查询、多表查询，子查询
select * from tb_name; 查询表中所有字段
select field1,field2 from tb_name; 投影，只显示每个实体中的field1、field2
select * from tb_name where 条件； where进行条件查询
例：
>select name ,age from students;
>select name,age from students where age >10;
>select name,age from students where gender='F';

还可以让相同的值只显示一次进行查询：
>select distinct gender from students; 这样最多显示两个结果M和F

from字句：要查询的关系 表、多个表及其他select语句
where字句：布尔关系式 > < = >= <= + - * and or not between...and...等

例：
>select name,age from students where age+1>20;
>select name from students where age>20 and gender='F';
>select name,age,gender from students where not age>20 and not gender='M';
查询年龄不大于20，且不为‘M’的name，age,gender从students表中
或者
>select name,age,gender from students where not(age>20 or gender='M');

>select name,age,gender from students where age>=20 and age<=25;
或者
>select name,age,gender from students where age between 20 and 25;

like '通配符'
%:表示任意长的任意字符
_:表示任意单个字符

例：
>select name,age,gender from students where name like 'y%'
查询所有以y开头的name，显示name,age,gender从students表中

>select name,age，gender from students where name like 'y____';

查询所有以y开头后面跟着四个字符的

>select name,age,gender from students where name like '%zhao%';
查询所有名称中包含zhao的

rlike '正则biaodashi'

>select name,age,gender from students where name rlike '^[znx].*$';

查询所有以z或n或x开头的mc

做离散取值：
>select name,age,gender from students where age in (18,20,23,25.27,29,30);
查询出年龄为18,28,23,25,27,29,30的

>select name from students where cid is not null;
>select name from students where cid is null;

order by排序,asc或desc,默认为asc(升序）

>select name from students where cid is not null order by desc;
查询出cid不为空的，在按照降序进行排序
>select name from students where cid is not null order by asc;
查询出cid不为空的，在按照升序进行排序

as为字段取别名
>select name as students_name from students;
查询出name，但其显示出的字段为students_name


select还可以进行算数运算
mysql> select 2+1;
+-----+
| 2+1 |
+-----+
|   3 |
+-----+
1 row in set (0.04 sec)

mysql> select 2*6;
+-----+
| 2*6 |
+-----+
|  12 |
+-----+
1 row in set (0.00 sec)

mysql> select 2+6 as sum;
+-----+
| sum |
+-----+
|   8 |
+-----+
1 row in set (0.00 sec)

limit offset,count (偏移量，显示个数）
例
>select name as students_name from students limit 2;
只显示前两个，并给别名
>select name as students_name from students limit 2,3;
每隔两个显示一显示，共显示3个

聚合 avg、sum、count、max、min 平均 和 总数 最大 最小
>select avg(age) from students;  显示平均年龄
>select max(age) from students;  显示最大年龄

>select avg(age) from students where gender='F'; 显示所有女性的平均年龄

group by 将某个关键字进行分组，在从分组中进行选择或者统计(先分组在选择）
>select age,gender from students group by gender;
先按照性别分为F或者M组，再显示age，gender

>select avg(age) from students group by gender;
先按照性别进行分组，再分别显示两组的平均年龄（即求男和女的各自平均年龄）

>select count(course) as person，course from students group by course;
先按照course（课程）分组，在统计每个课程的选修人数,显示结果为person

having 对group by的结果再次进行过滤，只能与group by连用
>select count(course) as person,course from students group by course having person>=2；
按照course进行分组，然后统计每个course的总数并取别名为person，最后将统计数大于2的显示出来

多表查询


表连接：交叉连接，笛卡尔连接，自然连接，外连接，自连接

自然连接如：
>select * from students,courses where student.cid1=courses.cid;
>select students.name,courses.cname from students,courses where students.cid1=courses.cid;
>select s.name,c.cname from students as s,courses as c where s.cid1=c.cid;
students表别名为s,courses表别名为c,并在s表的cid1与c表的cid相等时，显示出s表的name和c表的cname

外连接，分为左外连接...left join...on...和右外连接...right join...on...
>select s.name,c.name from students as s left join courses as c on s.cid1=c.cid;
已左边的表（s）为标准，左边表有的则显示且当s.cid1=c.cid

>select s.name,c.cname from students as s right join course as c on s.cid1=c.cid;
以右边的表（c）为标准，右边表有的则显示且当s.cid1=c.cid


自连接：要查的也属于自己的一部分，如sid为1的tid为3，而tid为3的表示sid=3
>select s.name as stu,c.name as teacher from students as s,students as c where c.tid=s.cid;
为students起了两个别名，前面的为s,后面的为c,并查询s表的name、别名为stu,和c表的name、别名为teacher(s和c都是students表）

子查询
在比较操作中使用子查询，子查询只能返回单值
>select name from students where age in (select avg(age) from students);
查询出大于平均年龄的name
注：因为要做比较,所以括号中的查询结果只能是一个单值

in()和not in()使用子查询，其子查询不必为单值
>select name from students where age in (select age from tutors)
找出tutors表和students表中年龄一样的name

在from中使用子查询
>select name,age from (select name,age from students) as t where t.age>=20;
将查找到的结果起别名为t，在从t中找出年龄>=20的name,age

联合查询：union
>(select name,age from students) union (select name,age from tutors);
将students表和tutors表的查询结果一起显示

>select cname from courses where cid not in （select distinct cid2 from students where cid2 is not null）;
先找出students表中不为空的cid2且结果不能重复，再找出courses表中cid和students表中cid2不一样的，最后将cname显示出来

>select tname from tutors where tid not in (select distinct tid from courses);
>select cid1 from students group by cid1 having count(cid1) >=2;
先按照cid1进行分组，并选出cid1的总数大于等于2的cid1,最后将符合条件的cid1显示出来

>select cname from courses where cid in (select cid1 from students group by cid1 having count(cid1)>=2）；
先对students表按照cid1进行分组，再选出cid1>=2的cid1，当courses表中的cid与students表中选出的cid1向同时，显示出course表的cname
>select t.tname,c.cname from tutors as t left join courses as c on t.tid=c.tid;
tutors表别名我t，course表别名我c，以左边的表（t）为标准显示且要t.tid=c.cid

三张表查询
>select name,cname,tname from students,courses,tutors where students.cid1=courses.cid and
courses.tid=tutors.tid;

视图;存储下来的select语句，基于基表的查询结果；视图也成为虚表，它依赖的表成为基表
物化视图：可以将查询结果保存下来（但基表更新后，物化视图也要跟新，且MySQL不支持物化视图及在上面创建索引）

一般来说不允许向视图中插入数据，视图也是一张表（虚表）
例
>create view myview as select name,cname,tname from students,courses,tutors where students.cd1=courses.cid and courses.tid=tutors.tid;
创建一个叫做myview的视图,其保存的查询结果为下面的select语句
>select name,cname,tname from students,courses,tutors where students.cd1=courses.cid and courses.tid=tutors.tid;
>select * from myview; 则可以直接查看保存的结果(myview就是一个表（虚））
>show create view sct; 可以查看创建此视图时使用的语句

mysql -e 此选项允许在shell命令行不用登陆mysqld就可以执行并将结果显示出来

例:
#mysql -e 'create database mydb;' 直接在shell命令中创建一个数据库mydb
#mysql -e 'show databases;'     直接在shell中显示数据库
#mysql -e "insert into students(name,age) values ('zxl','23');" 直接在shell中向数据库插入值
注：小心最外面的引号与（）中单引号冲突，所以最外面换成双引号。
因此mysql -e选项可以方便的将sql语句写长脚本，从而自动执行

>select * from tutors order by tid desc limit 1;

对tid进行降序排列，并且显示第一个实体；即显示tid最大的那一行。

以下是三种插入方法

1>insert into tutors set tname='zxl',gender='M',age='23';

2>insert into tutors(tname,gender,age) select name,gender,age from students where age>20;
找出students表中年龄大于20的，并将name,gender,age差入到tutors表的name,gender,age字段

3>insert into student(name,cid) values ('zxl',1),('Fade',2);

delete删除
>delete from tb_name where 条件；（支持order by,limit等修饰）
例：
>delete from students where age>=23;
将表students中年龄大于23的实体全部删了

truncate清空表，并且重置auto_increment计数器
>truncate tb_name;
例：
>truncate students; 清空students表

update更新表
>update tb_name set col1=...,col2=... where 条件；（支持order by ,limit）
例：
>update students set name='zxl',age='23' where id=1 and gender='M';
将students表中，id=1且性别为男的name改为'zxl',age改为23