数据库SQL高级语言

最新推荐文章于 2024-03-27 21:50:27 发布

LiuHuan303

最新推荐文章于 2024-03-27 21:50:27 发布

阅读量590

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/LiuHuan303/article/details/121745601

版权

1，导入数据库

将数据库文件导入系统中

[root@localhost ~]# cd /opt
[root@localhost opt]# rz -E
rz waiting to receive.
[root@localhost opt]# ls
dir_SC_UTF8                nginx-1.12.0            Python-3.7.7
Discuz_X3.4_SC_UTF8.zip    nginx-1.12.0.tar(1).gz  Python-3.7.7_.tgz
mysql-5.7.20               php-7.1.10              test.sql    #数据库文件
mysql-boost-5.7.20.tar.gz  php-7.1.10.tar.bz2

进入数据库刷新文件

[root@localhost opt]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.


(root@localhost) [(none)]> source /opt/test.sql    #source文件位置绝对路径
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

查看

(root@localhost) [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| hellodb  #数据文件  |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

2，select

显示表格中的一个或者多个字段中所有的信息
语法：
select 字段名 from 表名;

例：

(root@localhost) [hellodb]> select *from teachers;   #查看表全部内容
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
(root@localhost) [hellodb]> select name from teachers;    #查看表一个字段内容
+---------------+
| name          |
+---------------+
| Song Jiang    |
| Zhang Sanfeng |
| Miejue Shitai |
| Lin Chaoying  |
+---------------+
4 rows in set (0.00 sec)
(root@localhost) [hellodb]> select name,age from teachers;   #查看多个字段用逗号间隔
+---------------+-----+
| name          | age |
+---------------+-----+
| Song Jiang    |  45 |
| Zhang Sanfeng |  94 |
| Miejue Shitai |  77 |
| Lin Chaoying  |  93 |
+---------------+-----+
4 rows in set (0.00 sec)

3，distinct

distinct 查询不重复记录
中文含义：/dɪˈstɪŋkt/ 不同的明显的
语法:
select distinct 字段 from 表名﹔

例：

(root@localhost) [hellodb]> select *from teachers;    #查看全部表数据
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select distinct gender from teachers;
                                                #去除性别中重复字段
+--------+
| gender |
+--------+
| M      |
| F      |
+--------+
2 rows in set (0.00 sec)

4，where

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

例：（工作中禁止使用select *from 表名;查找时需要带上条件）

(root@localhost) [hellodb]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where age>80;  #查找年龄大于80
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   2 | Zhang Sanfeng |  94 | M      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
2 rows in set (0.01 sec)

(root@localhost) [hellodb]> select *from teachers where gender='m'; #查找性别为M
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
+-----+---------------+-----+--------+
2 rows in set (0.00 sec)

5，and，or

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

例：

(root@localhost) [hellodb]> select *from teachers;
+-----+---------------+-----+--------+  
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where age>50 and gender='m';
+-----+---------------+-----+--------+    ##查找年龄大于50并且性别为M的数据
| TID | Name          | Age | Gender |    ##两个条件需要都满足
+-----+---------------+-----+--------+
|   2 | Zhang Sanfeng |  94 | M      |
+-----+---------------+-----+--------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where age>50 or gendder='m';
+-----+---------------+-----+--------+  ##查找年龄大于50或者性别为M的数据
| TID | Name          | Age | Gender |  ##两个条件满足一个即可
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

6，in

in：显示已知值的数值
语法：
select 字段名 from 表名 where 字段 in ('值1'，'值2'....);

例：

(root@localhost) [hellodb]> select *from teachers;                   
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where tid in(2,3);
+-----+---------------+-----+--------+  ##查找tid是2,3的数据
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
+-----+---------------+-----+--------+
2 rows in set (0.00 sec)
(root@localhost) [hellodb]> select *from teachers where age in(93,94);
+-----+---------------+-----+--------+   ##查找age是93,94的数据
| TID | Name          | Age | Gender |   ##根据已知数据查找
+-----+---------------+-----+--------+
|   2 | Zhang Sanfeng |  94 | M      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
2 rows in set (0.00 sec)

7，between

between：
显示两个值范围内的资料
语法：
select 字段名 from 表名 where 字段 between '值1' and '值2';
包括 and两边的值

例：

(root@localhost) [hellodb]> select *from teachers;                   
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
(root@localhost) [hellodb]> select *from teachers where name between 'song jiang'
 and 'zhang sanfeng';                     ##查找两个名字中间的数据
+-----+---------------+-----+--------+    ##显示数据包括两个名字
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
+-----+---------------+-----+--------+
2 rows in set (0.00 sec)
(root@localhost) [hellodb]> select *from teachers where name between 'song jiang'
 and 'miejue shitai';                       ##输入查找不到数据
Empty set (0.00 sec)
(root@localhost) [hellodb]> select *from teachers where name between 'miejue shitai'
 and 'song jiang';            ##名字反过来就可以查找了（前面的不可以比后面的大）
+-----+---------------+-----+--------+      ##一般不使用字符串
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   3 | Miejue Shitai |  77 | F      |
+-----+---------------+-----+--------+
2 rows in set (0.00 sec)
(root@localhost) [hellodb]> select *from teachers where age between 45 and 93;
+-----+---------------+-----+--------+      ##查找age在45到93之间的数据
| TID | Name          | Age | Gender |      ##包括45和93
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where age between 30 and 90;
+-----+---------------+-----+--------+      ##查找age在30到90的数据 
| TID | Name          | Age | Gender |      ##只是一个范围数值可以不再数据库中
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   3 | Miejue Shitai |  77 | F      |
+-----+---------------+-----+--------+
2 rows in set (0.00 sec)

8，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 的字符串

通配符	含义
%	表示零个，一个或者多个字符
_	下划线表示单个字符
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 teachers;                   
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where name like 's%'';
+-----+------------+-----+--------+   ##查找name以s开头的数据
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Song Jiang |  45 | M      |
+-----+------------+-----+--------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where name like '%ng%';
+-----+---------------+-----+--------+ ##查找name中含有ng的数据
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where name like 'mie%';
+-----+---------------+-----+--------+    ##查找name以mie开头的数据
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   3 | Miejue Shitai |  77 | F      |
+-----+---------------+-----+--------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where name like '%ing';
+-----+--------------+-----+--------+    ##查找name以ing结尾的数据
| TID | Name         | Age | Gender |
+-----+--------------+-----+--------+
|   4 | Lin Chaoying |  93 | F      |
+-----+--------------+-----+--------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where name like '_h%';
+-----+---------------+-----+--------+    ##查找name第二个字母是h的数据
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   2 | Zhang Sanfeng |  94 | M      |
+-----+---------------+-----+--------+
1 row in set (0.00 sec)

9，order by

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

例：

(root@localhost) [hellodb]> select *from teachers;                   
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers order by age;
+-----+---------------+-----+--------+   #查找数据以age排序
| TID | Name          | Age | Gender |   #默认的是正向排序
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   2 | Zhang Sanfeng |  94 | M      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select *from teachers where gender='f'ordeer by age;
+-----+---------------+-----+--------+   #也可以加上where判断
| TID | Name          | Age | Gender |   #查找gender为f的以age排序
+-----+---------------+-----+--------+
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
2 rows in set (0.00 sec)
(root@localhost) [hellodb]> select name,age from teachers where genderr='f'
order by  age desc;  ##在表teachers表中查找gender为f的name，age字段按age倒序排列 
+---------------+-----+
| name          | age |
+---------------+-----+
| Lin Chaoying  |  93 |
| Miejue Shitai |  77 |
+---------------+-----+
2 rows in set (0.00 sec)

10，函数

数学函数

函数含义
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..........) 返回返回集合中最小的值

函数	含义
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(-12); #返回-12的绝对值
+----------+
| abs(-12) |
+----------+
|       12 |
+----------+
1 row in set (0.01 sec)

(root@localhost) [hellodb]> select rand(); #返回一个0-1的随机数
+---------------------+
| rand()              |
+---------------------+
| 0.47737625006341955 |
+---------------------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select mod(6,4); #返回6除以4的余数
+----------+
| mod(6,4) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select power(2,3); #返回2的3次方
+------------+
| power(2,3) |
+------------+
|          8 |
+------------+
1 row in set (0.01 sec)

(root@localhost) [hellodb]> select round(1.3);  #四舍五入原则返回数值
+------------+
| round(1.3) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select round(1.5);  #四舍五入返回数值
+------------+
| round(1.5) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select sqrt(9);  #返回9的开平方
+---------+
| sqrt(9) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select truncate(2.3456,3);  
+--------------------+         #切断小数点后3位，直接切断不遵循四舍五入
| truncate(2.3456,3) |
+--------------------+
|              2.345 |
+--------------------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select ceil(1.1); #小数点后任何数值都返回整数加一
+-----------+
| ceil(1.1) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select ceil(1);  #整数就返回整数
+---------+
| ceil(1) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select floor(1.9); #小数点后的都舍去返回整数
+------------+
| floor(1.9) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select floor(1);  #整数就返回整数
+----------+
| floor(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select greatest(12,5,8,24);  #取一组数的最大值
+---------------------+
| greatest(12,5,8,24) |
+---------------------+
|                  24 |
+---------------------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select least(12,5,8,24); #取一组数的最小值
+------------------+
| least(12,5,8,24) |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

聚合函数

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

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

例：

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

(root@localhost) [hellodb]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select avg(age)from teachers; 求表中age平均值
+----------+
| avg(age) |
+----------+
|  77.2500 |
+----------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select sum(age)from teachers; #求表中age的和
+----------+
| sum(age) |
+----------+
|      309 |
+----------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select max(age)from teachers; #求表中age最大值
+----------+
| max(age) |
+----------+
|       94 |
+----------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select min(age)from teachers; #求表中age最小值
+----------+
| min(age) |
+----------+
|       45 |
+----------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select count(age)from teachers; #求表中非空记录
+------------+
| count(age) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select count(distinct gender)from teacherss;
+------------------------+         #求表中gender去重后的记录数
| count(distinct gender) |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)

(root@localhost) [test]> select *from info;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | aa   | 40   |
|    2 |      | 40   |
|    3 | NULL | 40   |
|    4 | NULL | 30   |
+------+------+------+
4 rows in set (0.00 sec)

(root@localhost) [test]> select count(name)from info; 
+-------------+     #一个字段中的空格会被匹配，null空字符不会被匹配
| count(name) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)
(root@localhost) [test]> select count(*)from info; #匹配全表示不会忽略单个字符的空
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

字符串函数

函数含义
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;

select count(*) 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;
+-------------+
| count(name) |
+-------------+
|          27 |
+-------------+
空格字符是会被匹配的。
10.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 反转

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

函数	描述
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(起头及结尾)
要移除的字符串:从字符串的起头、结尾或起头及结尾移除的字符串，缺省时为空格。

(root@localhost) [hellodb]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
(root@localhost) [hellodb]> select trim(leading 'Lin'from'Lin chaoying');
+---------------------------------------+  #移除lin
| trim(leading 'Lin'from'Lin chaoying') | 
+---------------------------------------+
|  chaoying                             |
+---------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [hellodb]> select trim(both from'Lin chaoying    '); 
+-----------------------------------+      #去除开头及结尾的空格
| trim(both from'Lin chaoying    ') |
+-----------------------------------+
| Lin chaoying                      |
+-----------------------------------+
1 row in set (0.00 sec)

#length:
语法：select length(字段) from 表名；

(root@localhost) [hellodb]> select name,length(name) from teachers;
+---------------+--------------+    #统计每个name的字段长度
| name          | length(name) | 
+---------------+--------------+
| Song Jiang    |           10 |
| Zhang Sanfeng |           13 |
| Miejue Shitai |           13 |
| Lin Chaoying  |           12 |
+---------------+--------------+
4 rows in set (0.00 sec)

#replace（替换）
语法：select replace(字段，'原字符''替换字符') from 表名；

(root@localhost) [hellodb]> select replace(name,'ang','xxx')from teachers;
+---------------------------+   #将表中ang换成xxx（并没有真正的更改）
| replace(name,'ang','xxx') |
+---------------------------+
| Song Jixxx                |
| Zhxxx Sanfeng             |
| Miejue Shitai             |
| Lin Chaoying              |
+---------------------------+
4 rows in set (0.00 sec)

#concat：
语法：select concat(字段1，字段2）from 表名

(root@localhost) [hellodb]> select concat(tid,name,gender)from teachers;
+-------------------------+   #将多个字段拼成一个字符串
| concat(tid,name,gender) |
+-------------------------+
| 1Song JiangM            |
| 2Zhang SanfengM         |
| 3Miejue ShitaiF         |
| 4Lin ChaoyingF          |
+-------------------------+
4 rows in set (0.00 sec)

#substr：
语法：select substr(字段，开始截取字符，截取的长度) where 字段='截取的字符串'

(root@localhost) [hellodb]> select substr(name,3)from teachers 
where name='song jiang';    #不写截取长度默认截取全部
+----------------+          #截取从第三个开始包括第三个
| substr(name,3) |
+----------------+
| ng Jiang       |
+----------------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select substr(name,3,5)from teachers
 wheree name='song jiang';         #截取第三个字符开始截取5个字符，
+------------------+
| substr(name,3,5) |
+------------------+
| ng Ji            |
+------------------+
1 row in set (0.00 sec)

11，group by

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

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

(root@localhost) [hellodb]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
(root@localhost) [hellodb]> select gender,sum(age)from teachers
 group by gender;
+--------+----------+    ##以gender分组求age总和
| gender | sum(age) |
+--------+----------+
| F      |      170 |
| M      |      139 |
+--------+----------+
2 rows in set (0.00 sec)

12，having

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

(root@localhost) [hellodb]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select gender,avg(age)from teachers group  by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| F      |  85.0000 |
| M      |  69.5000 |
+--------+----------+
2 rows in set (0.00 sec)
(root@localhost) [hellodb]> select gender,avg(age)from teachers group by
 gender having age>80;   #having后的字段是新表中的字段
ERROR 1054 (42S22): Unknown column 'age' in 'having clause'
(root@localhost) [hellodb]> select gender,avg(age)from teachers group by
 gender having avg(age)>80;    #新表的字段avg（age）才可以成为条件
+--------+----------+
| gender | avg(age) |
+--------+----------+
| F      |  85.0000 |
+--------+----------+
1 row in set (0.00 sec)

13，别名

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

#as 可以省略

(root@localhost) [hellodb]> select name as n from teachers;
+---------------+      #显示时显示别名
| n             |
+---------------+
| Song Jiang    |
| Zhang Sanfeng |
| Miejue Shitai |
| Lin Chaoying  |
+---------------+
4 rows in set (0.00 sec)

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

(root@localhost) [hellodb]> select gender '性别',sum(age) '年龄和' 
froom teachers group by gender;     ##显示性别年龄和以性别为分组
+--------+-----------+ ##这里将gender别名定义为性别，sum（age）定义为年龄和
| 性别   | 年龄和    |
+--------+-----------+
| F      |       170 |
| M      |       139 |
+--------+-----------+
2 rows in set (0.00 sec)

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

使用as可以直接创建表

(root@localhost) [hellodb]> create table test1 as select *from teachers;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

(root@localhost) [hellodb]> select *from test1;  ##数据完全一样
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> desc test1;   ##没有继承特殊键
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| TID    | smallint(5) unsigned | NO   |     | 0       |       |
| Name   | varchar(100)         | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned  | NO   |     | NULL    |       |
| Gender | enum('F','M')        | YES  |     | NULL    |       |
+--------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> desc teachers;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| TID    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(100)         | NO   |     | NULL    |                |
| Age    | tinyint(3) unsigned  | NO   |     | NULL    |                |
| Gender | enum('F','M')        | YES  |     | NULL    |                |
+--------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

14，子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。
子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。

#子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
语法：
select 字段 from 表1 where 字段2 [比较运算符] (select 字段1 from 表格2 where 条件)
#比较运算符可以是 = > < >= <= 也可以是文字运算符 like in between

(root@localhost) [hellodb]> select name,age from teachers where age 
in (select age from teachers where age>80); #同一个表中
+---------------+-----+
| name          | age |
+---------------+-----+
| Zhang Sanfeng |  94 |
| Lin Chaoying  |  93 |
+---------------+-----+
2 rows in set (0.00 sec)

(root@localhost) [hellodb]> select teacherid from students where teacherid<3
    -> ;
+-----------+
| teacherid |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
(root@localhost) [hellodb]> select * from teachers where tid in 
(select teacherid from students where teacherid<3);
+-----+------------+-----+--------+  #先找出students表中teachersid<3的只有1
| TID | Name       | Age | Gender |  #再找到teachers表中tid=1的
+-----+------------+-----+--------+两个表中查找
|   1 | Song Jiang |  45 | M      |
+-----+------------+-----+--------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select avg(age) from teachers;  
+----------+   
| avg(age) |
+----------+
|  77.2500 |
+----------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select *from students where age>
(select avg(age) from teachers);  ##就是嵌套查询
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng | 100 | M      |    NULL |      NULL |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)

15，exists

这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空，则返回 TRUE；反之，则返回 FALSE

(root@localhost) [hellodb]> select * from teachers where tid in (select 
teacherid from students where teacherid<3); #不为空时
+-----+------------+-----+--------+
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Song Jiang |  45 | M      |
+-----+------------+-----+--------+
1 row in set (0.00 sec)

(root@localhost) [hellodb]> select * from teachers where exists (selecct 
teacherid from students where teacherid<3);  ##返回表中所有数据
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

(root@localhost) [hellodb]> select * from teachers where tid in (select 
teacherid from students where teacherid<1);  ##为空时
Empty set (0.00 sec)

(root@localhost) [hellodb]> select * from teachers where exists (selecct 
teacherid from students where teacherid<1);  ##返回空
Empty set (0.00 sec)

LiuHuan303

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库SQL高级语言

目录1，导入数据库2，select3，distinct4，where5，and，or6，in7，between8，like （通配符模糊查询）9，order by10，函数10.3 字符串函数11，group by12，having13，别名14，子查询15，exists1，导入数据库将数据库文件导入系统中[root@localhost ~]# cd /opt[root@localhost opt]# rz -Erz wait
复制链接

扫一扫