目录
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 的字符串
例:
(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..........) 返回返回集合中最小的值
例:
(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) 返回指定列的所有值之和
例:
语法:
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 反转
例:
#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)