首页 > 代码库 > 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详解