MySQL数据库之单表查询--示例

 注释、*、条件查询、as取别名、比较-逻辑-算术运算符、

 and、or、模糊匹配（%、_）--示例

1 /*
2 sql中有三种注释方式：
3 1、#：单行注释
4 2、--：代表单行注释，横杠后面要有空格
5 3、/星号...星号/：多行注释
6 */
7
8 #查询整张表，*号代表所有字段
9 select * from students;
10
11 -- 查询指定列的数据
12 SELECT name,birthday from students;
13
14 /*查询名字叫张三的人的姓名和生日*/
15 SELECT name,birthday from students where name='张三';
16
17 -- 查询同时满足多个条件的数据
18 SELECT * from students WHERE sex='男' and age=25;
19
20 -- 查询满足其中一个条件的数据
21 SELECT * from students WHERE sex='女' or age=21;
22
23 #查询指定列，并取列的别名，可以用as，也可以省略，取别名不会修改表中列的名字
24 select xuhao as 序号,name 姓名,sex 性别 from students;
25
26 #比较运算符：=、>、<、>=、<=、!=
27 select * from students where age>=25;
28 SELECT * from students WHERE age!=30;
29 #<>也可以表示不等于
30 SELECT * from students WHERE age<>30;
31
32 #逻辑运算符：and、or、not，and同时满足，or满足其中一个条件，not取反、不满足
33 SELECT * from students WHERE not sex='男';
34 SELECT * from students where not sex='男' and age=20;
35
36 #算术运算符：+、-、*、/
37 SELECT * from students where age+1>25;
38 #如果有空值，可以用ifnull将空值进行转换
39 SELECT * FROM students where IFNULL(age,100)>25;
40
41 #in、not in
42 SELECT * FROM students WHERE age in (20,25,30);
43 #相当于使用or查询
44 SELECT * from students where age=20 or age=25 or age=30;
45
46 select * from students WHERE age not in(20,25,30);
47 #相当于使用以下查询语句
48 SELECT * from students WHERE age!=20 and age!=25 and age!=30;
49 SELECT * from students WHERE not age=20 and not age=25 and not age=30;
50 SELECT * from students where not(age=20 or age=25 or age=30);
51
52
53 #模糊匹配：%若干个任意字符、_一个任意字符
54 #查询名字中以王开头的人
55 SELECT * from students where name like '王%';
56 #查询名字中以王开头且名字为两个字符的人
57 SELECT * from students WHERE name like '王_';
58 #查询名字以王结尾的人
59 select * from students WHERE name like '%王';
60 #查询名字包含王字的人
61 SELECT * from students WHERE name like '%王%';
62 #查询名字不包含王字且性别为男的人
63 SELECT * from students WHERE name not like'%王%' and sex='男';

 字符串相关函数、数字相关函数、日期时间函数、聚合函数、排序、分组--示例

1 select * from students;
2
3 #查询age为空的人的数据
4 SELECT name,age from students WHERE age is null;
5
6 #查询age不为空的人的数据
7 SELECT name,age from students WHERE age is not null;
8
9 #查询name为空字符串的人的数据，空字符不是null
10 SELECT * from students WHERE name='';
11
12 #BETWEEN 起始值 AND 结束值，查询的是数值在某个范围之间的数据，是一个闭区间
13 SELECT * from students WHERE age BETWEEN 26 and 30;
14 #等同于用大于等于、小于等于查询
15 SELECT * from students WHERE age>=26 and age<=30;
16 #以下为错误写法
17 SELECT * from students WHERE 26<=age<=30;
18
19
20 #==============字符串相关函数========================
21 #字符串拼接CONCAT(STR1,STR2...)
22 SELECT CONCAT('HELLO',' ','WORLD') AS STRING;
23 #+号不能用来拼接字符串，+只能用来作算术运算
24 SELECT 'HELLO'+' '+'WORLD' AS STR;
25 SELECT concat(name,birthday) from students;
26
27 #求字符串所占字节长度length(str)，单位是字节，utf8中一个英文占一个字节，一个中文占3个字节
28 SELECT LENGTH('hello');
29 SELECT LENGTH('王');
30 #求name中包含两个中文的人的数据
31 SELECT * from students WHERE length(name)=6;
32 #求字符串的字符长度char_length(str)，单位是字符
33 SELECT CHAR_LENGTH('王');
34 #求名字为两个字符的人的数据
35 SELECT * from students WHERE char_length(name)=2;
36
37 #求子串
38 #SUBSTR(STR,POS),pos表示开始截取的位置，位置从1开始
39 SELECT SUBSTR(name,2) from students WHERE name='欧阳娜娜';
40 #SUBSTR(str,pos,len),POS表示起始位置，len表示截取长度
41 SELECT SUBSTR(name,3,2) from students WHERE name='欧阳娜娜';
42 #也可以用SUBSTRING()来截取子串
43 SELECT SUBSTRING(name,3,2) from students WHERE `name`='欧阳娜娜';
44
45 #转换成大写UPPER(STR)
46 SELECT UPPER('Hello');
47 #转换成小写LOWER(STR)
48 SELECT LOWER('HELLO');
49 #忽略大小写，查询name为sea的人
50 SELECT * from students WHERE LOWER(name)='sea';
51
52
53 #==============数字相关函数===============
54 #四舍五入，ROUND()
55 #默认只取整数，大于等于5向前进1，小于5直接丢弃
56 SELECT ROUND('1133.878');
57 #也可以指定保留多少位小数
58 SELECT ROUND('1133.878',2);
59 #向上取整ceil(),只要小数部分不是0整数部分都加1
60 SELECT CEIL('1133.0001');
61 #向下取整floor()，只取整数部分，不管小数部分为多少都直接丢弃
62 SELECT FLOOR('1113.888');
63
64
65 #================日期时间函数===================
66 #当前日期三种方法
67 SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE;
68 #当前时间
69 SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME;
70 #当前日期时间
71 SELECT CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP;
72 #求取当前日期时间
73 SELECT CONCAT(CURDATE(),CURRENT_TIME());
74 #设置张三的生日为当前
75 update students set birthday=CURRENT_TIMESTAMP() WHERE name='张三';
76 #求取年份
77 SELECT YEAR(CURDATE());
78 #求取月份
79 SELECT MONTH(CURDATE());
80 #求取日数(月中的第几天)
81 SELECT DAY(CURDATE());
82 #求取相差的天数
83 SELECT DATEDIFF(CURDATE(),'2021-1-1');
84 #根据日期求取年龄
85 SELECT YEAR(CURDATE())-YEAR('1990-3-1') as age;
86 #查询年龄为26的人
87 SELECT * from students WHERE year(CURDATE())-YEAR(birthday)=26;
88
89
90
91 #==================聚合函数=========================
92 #最大值max()
93 #查询学生表中年龄最大的年龄
94 SELECT MAX(age) from students;
95 #查询学生表中年龄最大的学生的所有信息
96 SELECT * from students ;
97 #查询学生表中年龄最大的学生的信息
98 SELECT * from students WHERE age=(SELECT MAX(age) from students);
99 #聚合函数放在查询列当中的时候，不能再跟着其他字段，否则查询出来的数据会不准确
100 SELECT MAX(age),name,age from students;
101
102 #最小值min()
103 #查询学生表中最小的年龄
104 SELECT min(age) from students;
105 #查询年龄最小的学生的全部信息
106 SELECT * from students WHERE age=(SELECT min(age) from students);
107
108 #平均值avg()
109 #查询平均年龄，并四舍五入，年龄为空的人不计算在内
110 SELECT ROUND(avg(age)) from students;
111 #如果年龄为Null，将null转换为0，再计算平均年龄，年龄为空的人也计算在内
112 SELECT ROUND(avg(IFNULL(age,0))) from students;
113 #查询年龄大于平均年龄的人
114 SELECT * from students WHERE IFNULL(age,0)>(SELECT ROUND(avg(IFNULL(age,0))) from student
115
116 #求总数sum
117 SELECT sum(age) from students;
118
119 #求个数count()，一般计算个数的时候，都会去除重复数据，也就是说相同的数据只计算一次，去重使用dist
120 SELECT COUNT(IFNULL(age,0)) from students;
121 SELECT COUNT(DISTINCT IFNULL(age,0)) as count from students;
122 #distinct后面跟着多个字段时，表示查询多个字段都不相同时的数据，字段只都相同的数据，只显示一条数据
123 SELECT DISTINCT sex,age from students;
124
125
126
127 #=====================排序========================
128 #按年龄排序，默认按照从小到大排序
129 SELECT * from students ORDER BY IFNULL(age,0);
130 #年龄从小到大排序，asc表示升序排列，asc可以省略
131 SELECT * from students ORDER BY IFNULL(age,0) asc;
132 #年龄按照从大到小排序，desc表示降序（倒序）排列
133 SELECT * from students ORDER BY IFNULL(age,0) desc;
134
135 #先按照性别排序，再按照年龄进行排序，可以分别指定每个字段的排序规则
136 SELECT * from students ORDER BY sex asc,IFNULL(age,0) desc
137
138
139 #=================分组===================
140 #按sex分组，查询不同的性别
141 SELECT sex from students GROUP BY sex;
142 #按sex分组，查询所有字段，没有什么意义
143 SELECT * from students GROUP BY sex;
144 #按性别分组查询最大年龄，查询列当中一般只包含分组字段和聚合函数
145 SELECT sex,MAX(age)from students GROUP BY sex;
146 #分别查询男生女生当中年龄最大的人
147 SELECT * from students WHERE (sex,age) in (SELECT sex,MAX(age)from students GROUP BY sex)
148 #按性别分组查询平均年龄
149 SELECT sex,avg(age) from students GROUP BY sex;
150
151 SELECT DISTINCT name from students;
152 SELECT name from students GROUP BY name;

分页、where分组排序分页、子查询 -- 示例

1
2 -- create table score(id int PRIMARY KEY auto_increment,stu_id int,subject char(10),sc in
3 -- insert into score(stu_id,subject,sc) values(1,'英语',89),(2,'英语',99),(3,'英语',79),(4
4 -- (1,'数学',97),(2,'数学',99),(3,'数学',86),(4,'数学',75);
5
6 select * from score;
7 SELECT * from score ORDER BY subject,sc desc;
8 select subject,max(sc) from score GROUP BY subject;
9 #按科目查询平均分
10 SELECT subject,avg(sc) from score GROUP BY subject;
11 #求平均分大于80分的科目
12 #having要搭配group by使用，作用是对分组之后的数据进行筛选
13 SELECT subject,avg(sc) from score GROUP BY subject HAVING avg(sc)>80;
14 #求最高分大于95分的科目
15 SELECT SUBJECT,MAX(sc) from score GROUP BY SUBJECT HAVING max(sc)>95;
16 #求各个学生的总分
17 SELECT stu_id,SUM(sc) from score GROUP BY stu_id;
18 #求总分大于150分的学生的成绩
19 SELECT stu_id,SUM(sc) from score GROUP BY stu_id HAVING SUM(sc)>150;
20 #求总分大于150分的学生的成绩详情
21
22
23 #=================分页=================
24 SELECT * from score;
25 #查询前N行数据，limit表示分页
26 SELECT * from score LIMIT 4;
27
28 #查询从第M+1行开始的N行数据
29 SELECT * from score LIMIT 2,3;
30
31
32 #==========where分组排序分页================
33 #where条件是在group by之前执行的
34 #求成绩大于70分的各科目的学生成绩
35 SELECT * from score WHERE sc>70 order by subject;
36 #求分数大于0分的各科目的平均成绩
37 select subject,avg(sc) from score WHERE sc>0 GROUP BY subject;
38
39 #按学生总成绩倒序排列，一个查询语句里面如果有分组和排序的时候，先GROUP BY 再用ORDER BY
40 SELECT stu_id,SUM(sc) from score GROUP BY stu_id ORDER BY SUM(sc) desc;
41
42 #查询学生总成绩的前3名，有分组排序分页时，先分组后排序再分页
43 SELECT stu_id,SUM(sc) from score GROUP BY stu_id ORDER BY SUM(sc) desc LIMIT 3;
44
45 #先后顺序，先where，再group by ,然后having，再然后order by，最后limit
46 SELECT stu_id,avg(sc) from score where sc>0 GROUP BY stu_id ORDER BY avg(sc) desc LIMIT
47
48
49 #==============子查询==================
50 #子查询的用法：1、放在where条件中 2、将查询结果当作一个表放在from后面
51 #查询学生成绩表中的最高分
52 SELECT max(sc) from score;
53 #查询最高分的学生的所有字段
54 SELECT * from score WHERE sc=(SELECT max(sc) from score);
55 #查询最高分的学生的所有字段
56 SELECT score.* from score,(SELECT max(sc) sc from score) mc where score.sc=mc.sc;
57 #查询各个科目的最高分
58 SELECT subject,max(sc) from score GROUP BY SUBJECT;
59 #查询各科目的最高分的学生所有字段
60 select score.* from score,
61 (SELECT subject,max(sc) sc from score GROUP BY SUBJECT) as sm
62 where score.subject=sm.subject and score.sc=sm.sc;