MYSQL数据库基础和常用语法汇总03篇-数据查询

【数据查询语言】

(1) 查询系统内部函数返回内容和算式计算

显示MYSQL的版本：select version();

显示当前时间：select now();

显示年月日：SELECT DAYOFMONTH(CURRENT_DATE);

显示字符串：SELECT "welecome to my blog!";

当计算器用：select ((4 * 4) / 10 ) + 25;

（2）基本select查询，注意格式： select ... from ...where ...

select * from stu; #查询表中所有存在数据

select * from stu where name="lishi"; #查询表中指定一条件后对应记录

select * from stu where name="lishi" and age=21; #查询表中指定两条件筛选后记录

select DISTINCT age from stu; #去掉重复项

select DISTINCT name,age from stu; #去掉重复项

SELECT * from stu c where c.age=21; #给表取别名

SELECT * from stu c,stu11 c1 where c.id=c1.id; #给2表取别名，并关联查询

SELECT * from stu as c,stu11 as c1 where c.id=c1.id; #同上，但带了as

SELECT sex as "性别",age as "abc" from stu WHERE age=21; #给表中列取别名

(3)where子句

逻辑关系：= > >= <= <> or(或) and(且)

包含关系：between...and... in not in

模糊匹配：like

是否为空：is null

select * from stu where age>21; #查找年龄大于21

select * from stu where age<=25; #查找年龄小于25

select * from stu where age<>25; #查找年龄不为25的其它记录

select * from stu where id=1 or id=5; #查找id为1或5的记录，只要存在1或5就输出

select * from stu where id=1 or sex='female'; #查找id为1或性别为female的记录，满足2条件的都输出

select * from stu where age between 20 and 30; #查找年龄在20-30之间

select * from stu where age>=20 and age<=30; #同上

select * from stu where age in('21','25'); #查找年龄在21，25，满足这2条件的

SELECT * from stu where id in(1,8) and price in(10,15)

SELECT * from stu where id in(1,8) or price in(8,15)

select * from stu where age not in('23','25'); #查找年龄不在23，25的，排除这2条件的其它值输出

SELECT * from fruit where name="菠萝"

select * from stu where name like '%chen%'; # 模糊查询包含有chen的

select * from stu where name like 'chen%'; # 模糊查询以chen开头的

select * from stu where name like '%chen'; # 模糊查询以chen结尾的

select * from stu where name like '%\%%'; #模糊查询包含有%,具有相同字符%的，加\进行转义处理

select * from stu c where c.tel is NULL; # 查找记录为空的

------------------------------------

(4)算术表达式 + — * /

select *，age,age-5,age+5 from stu c where c.sex='female'; #对字段进行增加或减少5，输出时会有2新列出现

SELECt age,(age+5)*2 from stu c WHERE c.sex='female'; #对字段进行组合运算

（5）字符串的连接、排序、分组、、

　Ａ，字符串连接

SELECT CONCAT(name,'-',c.age) FROM stu; #表示把前后两个字符拼接起来，中间用字符'-'隔开

　Ｂ，排序

排序：select * from table1 order by field1,field2 [desc] #默认为升序ASC，可以省略

SELECT * from stu order by age DESC;　　＃按年龄降序输出

SELECT *,age as a from stu order by a DESC;　　＃按年龄（加别名）降序输出

SELECT *,age as a from stu order by a ASC;　　＃按年龄（加别名）升序输出，ASC可省略　

SELECT * from stu ORDER BY 3 ASC; #按【列序号】排序，其中3代表age字段位置

SELECT * from meat where id in(1,3) ORDER BY price DESC; 指定行进行排序

SELECT * from stu c ORDER BY c.age,c.sex; #多列排序，先按第1个字段排序，再按第2个字段排序(基于在第1列中的重复项范围)

如果第1个字段中有重复值,则重复值中再按照第2个字段进行排序

C,统计函数

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1

总数：select count(*) as totalcount from table1 #totalcount为别名,统计总数据数，count(id)

练习题

1，求出年龄超过平均值的人数

SELECT COUNT(sage) from stu WHERE sage>AVG(sage) #错误，无法运行

SELECT COUNT(sage) from stu WHERE sage>(select AVG(sage) from stu) #Ok

D，分组(结合统计函数) group by 分组查询,HAVING为条件

select sex from stu group by sex; # 对单个列进行分组

select sex,count(sex) from stu group by sex; # 对单个列进行分组,且统计组中个数

select sex,max(age) from stu group by sex; # 对单个列进行分组,且统计组中年龄最大

#min()求最小值, avg()求平均值

select sex,max(age) from stu group by sex HAVING count(sex)>1; #先按性别分组，分组后再统计各组人数，超过1人的进行输出，并在输出的组中求出最大年龄

select id,name,sex, from stu group by sex order by age desc;

#先按性别分组，分组后再按年龄降序

【读取行数据】

读取所有行 select * from stu;

读取前三行 select * from stu limit 1,3;

#从首行开始往下读取3行

从第2行开始读取三行 select * from stu limit 1,3;

#从第2行开始往下读取3行

从第2行开始读取三行 select * from stu order by ID asc limit 0,3;

#按ID列先升序，再从首行开始(0位置)往下读取3行

读取最后一行 select * from stu order by ID desc limit 0,3;

#倒序读取3行

读取前100行 select * from stu order by ID limit 0,100;

#若没有100行，则输出最大行

读取第i行 select * from stu order by ID limit i-1,1;

#读取第i行

SELECT * from stu LIMIT 4 OFFSET 2 # 从第3个位置往后输出4行，Offset后面接的数字代表位置

【子查询】

概念：在标准的查询语句中的条件处嵌套了另外一个查询语句

<标量子查询>

是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)

SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)

SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

如：

SELECT * from animal where id = (SELECT id from fruit where name="荔枝");

SELECT * from animal where id = (SELECT MIN(price) from fruit)

SELECT * from stu where sage>(SELECT tage from teac where tage BETWEEN 30 and 40)

#当子查询语句返回的结果为多个数值时，无法传递给主查询，执行查询语句会报错

<列子查询>

指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。

可以使用 IN、ANY和 ALL 操作符

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s2 FROM t2) # ANY是指取出 t2表中s2最小值

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s2 FROM t2) # ALL 大于t2表中s2的最大值

举例如下：

SELECT * from stu where sage>(SELECT tage from teac where tage BETWEEN 30 and 40)

#当子查询语句返回的结果为多个数值时，无法传递给主查询，执行查询语句会报错

SELECT * from stu where sage in(SELECT tage from teac where tage BETWEEN 30 and 40)

#当子查询语句返回的结果为多个数值时，可以通过列子查询传递

SELECT * from stu where sage> ANY(SELECT tage from teac where tage BETWEEN 30 and 40)

#ANY是指在子查询的结果中取最小值进行输出

SELECT * from stu where sage> ALL(SELECT tage from teac where tage BETWEEN 30 and 40)

#ALL是指在子查询的结果中取最大值进行输出

<行子查询>

指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。

SELECT * FROM article WHERE (title,content,uid) = (SELECT title, content,uid FROM blog WHERE bid=2)

SELECT * from meat where (id,price)=(SELECT id,price from friut where name="banana") ;#同时可以指定2个或多个条件去匹配另外的表

<表子查询>

指子查询返回的结果集是 N 行 N 列的一个表数据。

SELECT * FROM article WHERE (title, content, uid) IN (SELECT title, content, uid FROM blog)

（6）多表查询（UNION,JOIN）

1)连接查询

同时涉及多个表的查询

[等值连接]

例：查询每个学生及其选修课程的情况

SELECT st.*,sc.* FROM st,sc WHERE st.sno = sc.sno;

2）union 联合，，拼接

要拼接成一个表，2个表必须是列数相同（上下连接）；

可以联合2个表进行查询，合并输出，表1：stu 表2：stu11

select name from stu union [ALL | DISTINCT] #ALL: 可选，返回所有结果集，包含重复数据。

select name from stu11; #DISTINCT: 可选，删除结果集中重复的数据。默认情况下UNION 已经删除了重复数据

或

select name

from stu

where id=1

union

select name

from stu11

where id=2;

SELECT id,NAME,price from animal UNION SELECT id,NAME,price from fruit; #指定相同列，2表进行连接，纵向显示；

也可以实现三表连接查询，如下

SELECT * from meat UNION DISTINCT SELECT * from friut union SELECT * from veg;

3）连接（JOIN）

A, INNER JOIN（内连接）

接下来我们就使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取

举栗子：

SELECT c.id,c.tel,c1.name,c1.score from stu c INNER JOIN stu11 c1 on c.id=c1.id;

B, LEFT JOIN(左连接)

MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

举栗子：

SELECT c.id,c.tel,c1.name,c1.score from stu c left JOIN stu11 c1 on c.id=c1.id;

#查询后显示结果条数以左表数量为主，即使右边数据多，也不显示

C，RIGHT JOIN（右连接）

MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。

SELECT c.id,c.tel,c1.name,c1.score from stu c RIGHT JOIN stu11 c1 on c.id=c1.id;

#查询后显示结果条数以右表数量为主，即使左边数据多，也不显示

D，其它连接方式

[使用外连接]

A、left （outer） join：

左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

1、左外连接：是A和B的交集再并上A的所有数据。

SELECT * FROM a LEFT OUTER JOIN b ON a.`ageId` = b.`id`

2、左外连接：其运算方式为：A左连接B的记录=图3公共部分记录集C＋表A记录集A1

B：right （outer） join:

1、右外连接：是A和B的交集再并上B的所有数据。

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

SELECT * FROM a right OUTER JOIN b ON a.`ageId` = b.`id`

2、右外连接：其运算方式为：A右连接B的记录=图3公共部分记录集C＋表B记录集B1 。

C：cross （outer） join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

SELECT * from student_new s cross JOIN teacher t on s.sno=t.tno #效果类似于等值连接和内连接