MySQL高级语言（数据库达人必备技巧）

目录

一，导入数据库

二，select

三，distinct

四，where

五，and和or

六，in

七，between

八，like模糊匹配通配符查询

九，order by

十,函数

1.数字函数

2，聚合函数

3.字符串函数

十一，group by

十二，having

十三，别名

---

一，导入数据库

将数据库导入进数据库中

先将数据库拉到linux中
(root@localhost) [mysql]> source /opt/test.sql

二，select

显示表格中的一个或者多个字段中所有的信息
语法：
select 字段名  from 表名;
例子
select * from students;
select name from students;
select name,id,age from students;

三，distinct

distinct 查询不重复记录
语法:
select distinct 字段 from 表名﹔

例子：
(root@localhost) [hellodb]> select distinct age from students;

#去除年龄字段中重复的

(root@localhost) [hellodb]> select distinct gender from students;
#查找性别

四，where

where 有条件的查询
语法：select '字段' from 表名  where 条件

(root@localhost) [hellodb]> select name,age from students where age < 30;
#显示name和age 并且要找到age 小于30的

五，and和or

and 且         or  或
语法：
select 字段名  from 表名 where 条件1 (and|or) 条件2 (and|or)条件3;

例子：
(root@localhost) [hellodb]> select name,age from students where  age < 20   and age > 10;
查看年龄大于10岁小于20岁的人和名字
(root@localhost) [hellodb]> select name,age,classid from students where 30 < age and age > 20and classid=4;
select name,age from students where gender='m' or(30 > age and age > 20);
#男的  30到20岁之间  

六，in

in：
显示已知值的数值
语法：
select 字段名  from 表名 where 字段 in ('值1'，'值2'....);
例子：
select * from students where StuID in (1,2,3,4);
select * from students where ClassID in (1,4);

七，between

between：
显示两个值范围内的资料
语法：
select 字段名  from 表名 where 字段 between  '值1' and '值2';
包括 and两边的值
例子：
select * from students where name between 'ding dian' and 'ling chong';
# 一般不使用在字符串上
select * from students where stuid between 2 and 5;
#id 2到5 的信息 包括2和5
select * from students where age between '22' and '30';
#不需要表中一定有该字段，只会将22 到30 已有的都显示出来

八，like模糊匹配通配符查询

通配符通常是和  like 一起使用
语法：
select 字段名  from 表名 where 字段 like 模式

select * from students where name like 's%';
select * from students where name like '%on%';

通配符	含义
%	表示零个，一个或者多个字符
_	下划线表示单个字符
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 的字符串

九，order by

order by 按关键字排序
语法：
select 字段名  from 表名 where 条件 order by 字段 [asc,desc];
默认 asc 正向排序
desc 反向排序

select age,name from students order by age;
#正向排序

select age,name from students order by age desc;
#反向排序

可以加上where
select name,age from students where classid=3 order by age;
#显示 name和age字段的数据  并且只显示classid字段为3 的 并且以age字段排序

十,函数

1.数字函数

函数	含义
abs(x)	返回x 的 绝对值 |+ -1| = 1
rand()	返回0到1的随机数
mod(x,y)	返回x除以y以后的余数 x 除以 y 的值 取余
power(x,y)	返回x的y次方 x是底数 y是次方
round(x)	返回离x最近的整数 round（1.4） 取1 round（1.5）2
round(x,y）	保留x的y位小数四舍五入后的值round（3.1415926，5） 3.14159
sqrt(x)	返回x的平方根 4 2
truncate(x,y)	返回数字 x 截断为 y 位小数的值 truncate(3.1415926345,3); 3.141直接截断
ceil(x)	返回大于或等于 x 的最小整数 正整数就返回本身 小数就整数加一
floor(x)	返回小于或等于 x 的最大整数 就是整数本身
greatest(x1,x2.....)	返回返回集合中最大的值
least(x1,x2..........)	返回返回集合中最小的值

(root@localhost) [hellodb]> select abs (-11)
    -> ;
+-----------+
| abs (-11) |
+-----------+
|        11 |
+-----------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select power (16,4);
+--------------+
| power (16,4) |
+--------------+
|        65536 |
+--------------+
1 row in set (0.00 sec)

上述表格中有使用格式，在此不全部演示。

2，聚合函数

函数	含义
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数 空值返回
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

语法：
select 函数（字段） from  表名;

select avg(age) from students;
#求表中年龄的平均值
select sum(age) from students;
#求表中年龄的总和
select max(age) from students;
#求表中年龄的最大值
select min(age) from students;
#求表中年龄的最小值
select count(classid) from students;
#求表中有多少非空记录
select count(distinct gender) from students;

insert into test values ('');
#加入null值，不可使用

insert into test values(null);
select count(name) from test;

如果某表只有一个字段使用*不会忽略 null
如果count后面加上明确字段会忽略 null


select sum(age) from students where classid is Null;
#查询空值

insert into students values(26,' ',22,'m',3,1);

select count(name) from students;
空格字符是会被匹配的。

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 反转

#trim：
语法：
select trim (位置 要移除的字符串 from 原有的字符串) #区分大小写
其中位置的值可以是 
leading（开始） 
trailing(结尾)
both(起头及结尾)
要移除的字符串:从字符串的起头、结尾或起头及结尾移除的字符串，缺省时为空格。#区分大小写
select trim(leading 'Sun' from 'Sun Dasheng');
select trim(both  from ' Sun Dasheng      ');
#去除空格

#length:
语法：select length(字段) from 表名；
#可以在函数前再加字段
select name,length(name) from students;
#计算出字段中记录的字符长度

#replace（替换）
语法：select replace(字段，'原字符''替换字符') from 表名；
(root@localhost) [hellodb]> select replace(age,100,99) from students where name='Sun Dasheng';
+---------------------+
| replace(age,100,99) |
+---------------------+
| 99                  |
+---------------------+
1 row in set (0.00 sec)


#concat：
语法：select concat(字段1，字段2）from 表名
#如有任何一个参数为NULL ，则返回值为 NULL          
select concat(name,classid) from students;
elect concat(name,classid) from students where classid=3;
select name || classid from students where classid=3;
select concat(name,'\t',classid) from students where classid=3;
select concat(classid,name) from students order by classid;

#substr：               
语法：select substr(字段，开始截取字符，截取的长度)  where 字段='截取的字符串'               
select substr(name,6) from students where name='Sun Dasheng';
#从第六个开始保留 （包括第六个）
select substr(name,6,2) from students where name='Sun Dasheng';

十一，group by

group by:
对group by  后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的
group by    有一个原则，就是select 后面的所有列中，没有使用聚合函数的列必须出现在 group by 的后面。

语法：
select 字段1，sum(字段2) from 表名 group by 字段1；

例子：
select classid,sum(age) from students group by classid;
#求各个班的年龄总和
select classid,avg(age) from students group by classid;
#求平均年龄
select classid,count(age) from students group by classid;

十二，having

having：用来过滤由group by语句返回的记录集，通常与group by语句联合使用
having 语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。
语法:SELECT 字段1，SUM("字段")FROM 表格名 GROUP BY 字段1 having（函数条件);

select classid,avg(age) from students group by classid where age > 30;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where age > 30' at line 1

select classid,avg(age) from students group by classid having age > 30;
ERROR 1054 (42S22): Unknown column 'age' in 'having clause'

select  avg(age) from students group by classid having avg(age) >23;



要根据新表中的字段 来制定条件

十三，别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者 
多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性
语法
对于字段的别名：
select 原字段 as 修改字段,原字段 as 修改字段 from 表名 ;
#as 可以省略。
例子：
select s.name as n, s.stuid as id from students s;
#对于列的别名

#如果表的长度比较长，可以使用 AS 给表设置别名，在查询的过程中直接使用别名临时设置info的别名为i
对于表的别名：
select 表格别名.原字段 as 修改字段[,表格别名.原字段 as 修改字段]from 原表名 as 表格别名 ;
#as可以省略

select avg(age) '平均值' from students;
#将聚合函数字段 设置成平均值


使用场景：
1、对复杂的表进行查询的时候，别名可以缩短查询语句的长度
2、多表相连查询的时候（通俗易懂、减短sql语句长度）

此外，AS 还可以作为连接语句的操作符。
创建t1表，将info表的查询记录全部插入t1表

create table test as select * from students;
#可以使用as直接创建，不继承 特殊键

create table test2 (select * from students);
#或者不继承 特殊键
create table test2 like students;
#继承特殊键