首页 > 代码库 > mysql详解
mysql详解
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 ‘正则表达式‘
>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 |
+-----+
mysql> select 2*6;
+-----+
| 2*6 |
+-----+
| 12 |
+-----+
mysql> select 2+6 as sum;
+-----+
| sum |
+-----+
| 8 |
+-----+
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
mysql详解