目录
一.导入数据库
在数据库中
(root@localhost) [(none)]> source /root/test.sql
二.select
显示表格中一个或数个字段的所有资料
select 字段名 from 表名;
(root@localhost) [hellodb]> select StuID,Name from students where StuID=1;
三.distinct
不显示重复的资料(去重)
select distinct 字段 from 表名﹔
(root@localhost) [hellodb]> select distinct age from students;
四.where
有条件的查询
select '字段' from 表名 where 条件;
(root@localhost) [hellodb]> select * from students where StuID=1;
五.and or
and 且 or 或
select 字段名 from 表名 where 条件1 (and|or) 条件2 (and|or)条件3;
法一
(root@localhost) [hellodb]> select * from students where age>30 and age<40;
法二
(root@localhost) [hellodb]> select * from students where gender='m' or(age>30 and age<40);
六. in
显示已知值的资料
select 字段名 from 表名 where 字段 in ('值1','值2'....);
(root@localhost) [hellodb]> select * from students where StuID in (1,2,3,4);
七.between
显示两个值范围内的资料
select 字段名 from 表名 where 字段 between '值1' and '值2';
(root@localhost) [hellodb]> select * from students where StuID between 1 and 4;
八.like 通配符
通配符通常是和 like 一起使用
select 字段名 from 表名 where 字段 like 模式
通配符 | 含义 |
---|---|
% | 表示零个,一个或者多个字符 |
_ | 下划线表示单个字符 |
A_Z | 所有以A开头 Z 结尾的字符串 'ABZ' 'ACZ' 'ACCCCZ'不在范围内 下划线只表示一个字符 AZ 包含a空格z |
ABC% | 所有以ABC开头的字符串 ABCD ABCABC |
%CBA | 所有以CBA结尾的字符串 WCBA CBACBA |
%AN% | 所有包含AN的字符串 los angeles |
_AN% | 所有 第二个字母为 A 第三个字母 为N 的字符串 |
(root@localhost) [hellodb]> select * from students where name like 's%';
九.order by
按关键字排序
select 字段名 from 表名 where 条件 order by 字段 [asc,desc];
asc正向排序 desc逆向排序
(root@localhost) [hellodb]> select * from students where stuid<10 order by age;
十.函数
1.数学函数
函数 | 含义 |
---|---|
abs(x) | 返回x的绝对值 |
rand() | 返回0到1的随机数 |
mod(x,y) | 返回x除以y以后的余数 |
power(x,y) | 返回x的y次方 |
round(x) | 返回离x最近的整数 |
round(x,y) | 保留x的y位小数四舍五入后的值 |
sqrt(x) | 返回x的平方根 |
truncate(x,y) | 返回数字 x 截断为 y 位小数的值 |
ceil(x) | 返回大于或等于 x 的最小整数 |
floor(x) | 返回小于或等于 x 的最大整数 |
greatest(x1,x2.....) | 返回集合中最大的值 |
least(x1,x2..........) | 返回集合中最小的值 |
2. 聚合函数
函数 | 含义 |
---|---|
avg() | 返回指定列的平均值 |
count() | 返回指定列中非 NULL 值的个数 |
min() | 返回指定列的最小值 |
max() | 返回指定列的最大值 |
sum(x) | 返回指定列的所有值之和 |
如果表只有一个字段使用*不会忽略空记录
如果count后面加上明确字段会忽略空记录
3. 字符串函数
函数 | 描述 |
---|---|
trim() | 返回去除指定格式的值 |
concat(x,y) | 将提供的参数 x 和 y 拼接成一个字符串 |
substr(x,y) | 获取从字符串 x 中的第 y 个位置开始的字符串,跟substring()函数作用相同 |
substr(x,y,z) | 获取从字符串 x 中的第 y 个位置开始长度为z 的字符串 |
length(x) | 返回字符串 x 的长度 |
replace(x,y,z) | 将字符串 z 替代字符串 x 中的字符串 y |
upper(x) | 将字符串 x 的所有字母变成大写字母 |
lower(x) | 将字符串 x 的所有字母变成小写字母 |
left(x,y) | 返回字符串 x 的前 y 个字符 |
right(x,y) | 返回字符串 x 的后 y 个字符 |
repeat(x,y) | 将字符串 x 重复 y 次 |
space(x) | 返回 x 个空格 |
strcmp(x,y) | 比较 x 和 y,返回的值可以为-1,0,1 |
reverse(x) | 将字符串 x 反转 |
select trim (位置 要移除的字符串 from 字符串)
位置可以是 leading(开始) trailing(结尾) both(起头及结尾)
将两个字段拼接在一起有两种方法:
(root@localhost) [hellodb]> select concat(Stuid,name) from students;
(root@localhost) [hellodb]> select stuid || name from students;
十一.group by
后面的字段的查询结果进行汇总分组,通常是结合聚合函数一起使用的
有一个原则,就是select 后面的所有列中,没有使用聚合函数的列必须出现在 group by 的后面
select 字段1,sum(字段2) from 表名 group by 字段1;
(root@localhost) [hellodb]> select classid,sum(age) from students group by classid;
十二.having
用来过滤由group by语句返回的记录集,通常与group by语句联合使用
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏,那就不需要GROUP BY子句。
SELECT 字段1,SUM("字段")FROM 表格名 GROUP BY 字段1 having(函数条件);
十三.别名
- 在 mysql 查询时,当表的名字比较长或者表内某些字段比较长时,为了方便书写或者多次使用相同的表,可以给字段列或表设置别名
- 方便操作,增强可读性
字段的别名
(root@localhost) [hellodb]> select avg(age) as '平均年龄' from students;
as可以省略,给平均年龄设置别名
表的别名
select 表别名.原字段 as 修改字段[,表格别名.原字段 as 修改字段] from 原表名 as 表格列名;
(root@localhost) [hellodb]> select s.name as name_students, s.stuid as id from students as s;
十四. 子查询
子查询技术嵌套查询,在WHERE 子句或HAVING 子句中插入另一个SQL语句
子查询语句是先于主查询语句被执行的,其结果作为外层的条件返回给主查询进行下一步的查询过滤
子语句可以与主语句所查询的表相同,也可以不同
性能较差
(root@localhost) [hellodb]> select name,age from students where age > (select avg(age) from students);
十五.exists
这个关键字在子查询时,布尔值判断,where之后的语句成立,布尔值则为0,则进行前面的操作,否则返回值为0 当使用 sum求和结合exists ,如果查询结果不成立,返回值为null
(root@localhost) [hellodb]> select * from teachers where exists (select teacherid from students where teacherid<1);
十六.连接
1.内连接
(root@localhost) [hellodb]> select * from teachers inner join students on students.teacherid=teachers.tid;
2.左连接
(root@localhost) [hellodb]> select * from students left join teachers on teachers.tid=students.teacherid;
(root@localhost) [hellodb]> select * from teachers left join students on students.teacherid=teachers.tid;
(root@localhost) [hellodb]> select * from students s left join teachers t on s.teacherid=t.tid order by tid;
3.右连接
(root@localhost) [hellodb]> select * from teachers right join students on students.teacherid=teachers.tid;
十七.视图
- 可以被当作是数据库中的虚拟表,存储查询结果的表。退出数据库后视图还是存在的。
- 这张虚拟表是真实数据的一个动态映射
- 这张虚拟表能动态的保存结果集
- 因为视图和真实表之间是动态同步的关系,所以我们修改虚拟表的同时,真实数据也会收到影响
- 但是视图除了名字什么都没有,是一个投影,所以不占空间
- 我们只需要对其做权限的设置,就可保证其安全性
- 我们可以为视图定义展示的条件,为不同的人群展示不同的网内容
(root@localhost) [hellodb]> create view oyyy_view as select s.name as name1,t.name as name2 from students s,teachers t;
(root@localhost) [hellodb]> drop view oyyy_view;
十八.联集
字段,数据类型要一致
(root@localhost) [hellodb]> select * from teachers union select stuid,name,age,gender from students;
十九.case
条件可以是一个数值或者公式else子句
(root@localhost) [hellodb]> select
-> case gender
-> when 'm' then '男'
-> when 'f' then '女'
-> else '其他'
-> end '性别',name from students
-> ;
(root@localhost) [hellodb]> SELECT name '姓名',age '年龄',
-> CASE
-> WHEN age < 20 THEN '年轻人'
-> WHEN age >= 20 and age < 50 THEN '中年人'
-> ELSE '老年人'
-> END '年龄段' from students;