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 #效果类似于等值连接和内连接

茈亾奈淫尋味

顶起顶起顶起