MySQL高级语言

目录

一.导入数据库

二.select

三.distinct

四.where

五.and or

法一 

法二

六. in

七.between

八.like  通配符

 九.order by

十.函数

1.数学函数

2. 聚合函数

3. 字符串函数

十一.group by

十二.having

十三.别名

十四. 子查询

十五.exists

十六.连接

​1.内连接

​2.左连接

​3.右连接

​十七.视图

十八.联集

十九.case

---

一.导入数据库

 在数据库中

(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;