目录
select
显示表格中的一个或者多个字段中所有的信息
语法：select 字段名 from 表名;
例子：
(root@localhost) [jianghu]> select * from Dragon; +-----+--------------+------+--------+--------------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+--------------+------+--------+--------------+---------+ | 1 | 风清扬 | 56 | 男 | 独孤求败 | 1 | | 2 | 萧远山 | 34 | 男 | 2 | 3 | | 3 | 东方不败 | 25 | 女 | 7 | 2 | | 4 | 萧峰 | 21 | 男 | 6 | 3 | | 5 | 张三丰 | 100 | 男 | 2 | 1 | | 6 | 令狐冲 | 25 | 男 | 3 | 2 | | 7 | 杨过 | 18 | 男 | 3 | 3 | | 8 | 小龙女 | 20 | 女 | 3 | 3 | | 9 | 张无忌 | 27 | 男 | 1 | 8 | | 10 | 独孤求败 | 66 | 男 | 1 | 1 | | 11 | 郭靖 | 33 | 男 | 2 | 5 | | 12 | 黄蓉 | 25 | 女 | 2 | 5 | | 13 | 胡一刀 | 21 | 男 | 3 | 8 | | 14 | 袁承志 | 19 | 男 | 5 | 3 | | 15 | 虚竹 | 19 | 男 | 3 | 1 | | 16 | 石破天 | 24 | 男 | 1 | 1 | | 17 | 段誉 | 28 | 男 | 1 | 1 | | 18 | 阿青 | 28 | 女 | 3 | 5 | +-----+--------------+------+--------+--------------+---------+ 18 rows in set (0.01 sec)(root@localhost) [jianghu]> select * from one_db; +----+--------------+------+--------+-------+ | id | name | age | Gender | oneid | +----+--------------+------+--------+-------+ | 1 | 陆小凤 | 26 | 男 | 2 | | 2 | 西门吹雪 | 28 | 男 | 3 | | 3 | 楚留香 | 28 | 男 | 18 | | 4 | 李寻欢 | 28 | 男 | 15 | | 5 | 木道人 | 56 | 男 | 6 | | 6 | 沈浪 | 30 | 男 | 8 | +----+--------------+------+--------+-------+ 6 rows in set (0.00 sec)(root@localhost) [jianghu]> select name,age,Gender from one_db; +--------------+------+--------+ | name | age | Gender | +--------------+------+--------+ | 陆小凤 | 26 | 男 | | 西门吹雪 | 28 | 男 | | 楚留香 | 28 | 男 | | 李寻欢 | 28 | 男 | | 木道人 | 56 | 男 | | 沈浪 | 30 | 男 | +--------------+------+--------+ 6 rows in set (0.00 sec)
distinct
查询不重复记录
语法：select distinct 字段 from 表名﹔
例子：去除年龄段重复的
(root@localhost) [jianghu]> select distinct age from one_db; +------+ | age | +------+ | 26 | | 28 | | 56 | | 30 | +------+ 4 rows in set (0.00 sec)
where
有条件的查询
语法：select '字段' from 表名 where 条件
例子：
显示name和age 并且要找到age 小于30的
(root@localhost) [jianghu]> select name,age from Dragon where age <30; +--------------+------+ | name | age | +--------------+------+ | 东方不败 | 25 | | 萧峰 | 21 | | 令狐冲 | 25 | | 杨过 | 18 | | 小龙女 | 20 | | 张无忌 | 27 | | 黄蓉 | 25 | | 胡一刀 | 21 | | 袁承志 | 19 | | 虚竹 | 19 | | 石破天 | 24 | | 段誉 | 28 | | 阿青 | 28 | +--------------+------+ 13 rows in set (0.00 sec)
and；or
and 且 or 或
语法：select 字段名 from 表名 where 条件1 (and|or) 条件2 (and|or)条件3;
例子：
查找年龄小于30且大于25的
(root@localhost) [jianghu]> select name,age from Dragon where age<30 and age>25; +-----------+------+ | name | age | +-----------+------+ | 张无忌 | 27 | | 段誉 | 28 | | 阿青 | 28 | +-----------+------+ 3 rows in set (0.00 sec)查找是女性或大于25岁且小于30岁
(root@localhost) [jianghu]> select name,age, gender from Dragon where gender='女' or(age<30 and age>25); +--------------+------+--------+ | name | age | gender | +--------------+------+--------+ | 东方不败 | 25 | 女 | | 小龙女 | 20 | 女 | | 张无忌 | 27 | 男 | | 黄蓉 | 25 | 女 | | 段誉 | 28 | 男 | | 阿青 | 28 | 女 | +--------------+------+--------+ 6 rows in set (0.00 sec)
in
显示已知值的资料
语法：select 字段名 from 表名 where 字段 in ('值1'，'值2'....);
例子：
(root@localhost) [jianghu]> select * from Dragon where Classid in (2,6,8); +-----+--------------+------+--------+----------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+--------------+------+--------+----------+---------+ | 3 | 东方不败 | 25 | 女 | 7 | 2 | | 6 | 令狐冲 | 25 | 男 | 3 | 2 | | 9 | 张无忌 | 27 | 男 | 1 | 8 | | 13 | 胡一刀 | 21 | 男 | 3 | 8 | +-----+--------------+------+--------+----------+---------+ 4 rows in set (0.00 sec) (root@localhost) [jianghu]> select * from Dragon where masterid in (1,3,5); +-----+--------------+------+--------+----------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+--------------+------+--------+----------+---------+ | 6 | 令狐冲 | 25 | 男 | 3 | 2 | | 7 | 杨过 | 18 | 男 | 3 | 3 | | 8 | 小龙女 | 20 | 女 | 3 | 3 | | 9 | 张无忌 | 27 | 男 | 1 | 8 | | 10 | 独孤求败 | 66 | 男 | 1 | 1 | | 13 | 胡一刀 | 21 | 男 | 3 | 8 | | 14 | 袁承志 | 19 | 男 | 5 | 3 | | 15 | 虚竹 | 19 | 男 | 3 | 1 | | 16 | 石破天 | 24 | 男 | 1 | 1 | | 17 | 段誉 | 28 | 男 | 1 | 1 | | 18 | 阿青 | 28 | 女 | 3 | 5 | +-----+--------------+------+--------+----------+---------+ 11 rows in set, 1 warning (0.00 sec)
between
显示两个值范围内的资料
语法：select 字段名 from 表名 where 字段 between '值1' and '值2';
包括 and两边的值
一般不使用在字符串上 ，不是按照顺序寻找
(root@localhost) [jianghu]> select * from Dragon where name between '杨过' and '阿青'; +-----+--------------+------+--------+----------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+--------------+------+--------+----------+---------+ | 2 | 萧远山 | 34 | 男 | 2 | 3 | | 4 | 萧峰 | 21 | 男 | 6 | 3 | | 7 | 杨过 | 18 | 男 | 3 | 3 | | 10 | 独孤求败 | 66 | 男 | 1 | 1 | | 11 | 郭靖 | 33 | 男 | 2 | 5 | | 13 | 胡一刀 | 21 | 男 | 3 | 8 | | 14 | 袁承志 | 19 | 男 | 5 | 3 | | 15 | 虚竹 | 19 | 男 | 3 | 1 | | 16 | 石破天 | 24 | 男 | 1 | 1 | | 17 | 段誉 | 28 | 男 | 1 | 1 | | 18 | 阿青 | 28 | 女 | 3 | 5 | +-----+--------------+------+--------+----------+---------+ 11 rows in set (0.00 sec)查找id3到5的
(root@localhost) [jianghu]> select * from Dragon where did between '3' and '5'; +-----+--------------+------+--------+----------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+--------------+------+--------+----------+---------+ | 3 | 东方不败 | 25 | 女 | 7 | 2 | | 4 | 萧峰 | 21 | 男 | 6 | 3 | | 5 | 张三丰 | 100 | 男 | 2 | 1 | +-----+--------------+------+--------+----------+---------+ 3 rows in set (0.00 sec)不需要表中一定有该字段，只会将一个数到另一个数 已有的都显示出来
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) [jianghu]> select * from Dragon where name like '萧%'; +-----+-----------+------+--------+----------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+-----------+------+--------+----------+---------+ | 2 | 萧远山 | 34 | 男 | 2 | 3 | | 4 | 萧峰 | 21 | 男 | 6 | 3 | +-----+-----------+------+--------+----------+---------+ 2 rows in set (0.00 sec) (root@localhost) [jianghu]> select * from Dragon where name like '萧_山'; +-----+-----------+------+--------+----------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+-----------+------+--------+----------+---------+ | 2 | 萧远山 | 34 | 男 | 2 | 3 | +-----+-----------+------+--------+----------+---------+ 1 row in set (0.00 sec) (root@localhost) [jianghu]> select * from Dragon where name like '%一%'; +-----+-----------+------+--------+----------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+-----------+------+--------+----------+---------+ | 13 | 胡一刀 | 21 | 男 | 3 | 8 | +-----+-----------+------+--------+----------+---------+ 1 row in set (0.00 sec)like还可以 复制表结构，内容不会复制
(root@localhost) [jianghu]> create table test like Dragon; Query OK, 0 rows affected (0.02 sec) (root@localhost) [jianghu]> show tables; +-------------------+ | Tables_in_jianghu | +-------------------+ | Dragon | | one_db | | test | +-------------------+ 3 rows in set (0.00 sec) (root@localhost) [jianghu]> select * from test; Empty set (0.00 sec) (root@localhost) [jianghu]> desc test; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | Did | int(11) | NO | PRI | NULL | | | Name | varchar(11) | YES | | NULL | | | Age | int(10) | YES | | NULL | | | Gender | varchar(2) | YES | | NULL | | | MasterId | varchar(5) | YES | | NULL | | | ClassId | int(8) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
内容复制就利用insert into 结构
语法：insert into 新表名 select * from 旧表名
(root@localhost) [jianghu]> insert into test select * from Dragon;
Query OK, 18 rows affected (0.01 sec)
Records: 18 Duplicates: 0 Warnings: 0
order by
按关键字排序
语法：select 字段名 from 表名 where 条件 order by 字段 [asc,desc];
正向排序根据年龄
(root@localhost) [jianghu]> select * from Dragon order by age; +-----+--------------+------+--------+--------------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+--------------+------+--------+--------------+---------+ | 7 | 杨过 | 18 | 男 | 3 | 3 | | 14 | 袁承志 | 19 | 男 | 5 | 3 | | 15 | 虚竹 | 19 | 男 | 3 | 1 | | 8 | 小龙女 | 20 | 女 | 3 | 3 | | 4 | 萧峰 | 21 | 男 | 6 | 3 | | 13 | 胡一刀 | 21 | 男 | 3 | 8 | | 16 | 石破天 | 24 | 男 | 1 | 1 | | 3 | 东方不败 | 25 | 女 | 7 | 2 | | 6 | 令狐冲 | 25 | 男 | 3 | 2 | | 12 | 黄蓉 | 25 | 女 | 2 | 5 | | 9 | 张无忌 | 27 | 男 | 1 | 8 | | 18 | 阿青 | 28 | 女 | 3 | 5 | | 17 | 段誉 | 28 | 男 | 1 | 1 | | 11 | 郭靖 | 33 | 男 | 2 | 5 | | 2 | 萧远山 | 34 | 男 | 2 | 3 | | 1 | 风清扬 | 56 | 男 | 独孤求败 | 1 | | 10 | 独孤求败 | 66 | 男 | 1 | 1 | | 5 | 张三丰 | 100 | 男 | 2 | 1 | +-----+--------------+------+--------+--------------+---------+ 18 rows in set (0.00 sec)反向排序
(root@localhost) [jianghu]> select * from Dragon order by age desc; +-----+--------------+------+--------+--------------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+--------------+------+--------+--------------+---------+ | 5 | 张三丰 | 100 | 男 | 2 | 1 | | 10 | 独孤求败 | 66 | 男 | 1 | 1 | | 1 | 风清扬 | 56 | 男 | 独孤求败 | 1 | | 2 | 萧远山 | 34 | 男 | 2 | 3 | | 11 | 郭靖 | 33 | 男 | 2 | 5 | | 17 | 段誉 | 28 | 男 | 1 | 1 | | 18 | 阿青 | 28 | 女 | 3 | 5 | | 9 | 张无忌 | 27 | 男 | 1 | 8 | | 6 | 令狐冲 | 25 | 男 | 3 | 2 | | 3 | 东方不败 | 25 | 女 | 7 | 2 | | 12 | 黄蓉 | 25 | 女 | 2 | 5 | | 16 | 石破天 | 24 | 男 | 1 | 1 | | 4 | 萧峰 | 21 | 男 | 6 | 3 | | 13 | 胡一刀 | 21 | 男 | 3 | 8 | | 8 | 小龙女 | 20 | 女 | 3 | 3 | | 14 | 袁承志 | 19 | 男 | 5 | 3 | | 15 | 虚竹 | 19 | 男 | 3 | 1 | | 7 | 杨过 | 18 | 男 | 3 | 3 | +-----+--------------+------+--------+--------------+---------+ 18 rows in set (0.00 sec)可以配合where使用，只显示classid为三，且年龄排序
(root@localhost) [jianghu]> select * from Dragon where classid=3 order by age ; +-----+-----------+------+--------+----------+---------+ | Did | Name | Age | Gender | MasterId | ClassId | +-----+-----------+------+--------+----------+---------+ | 7 | 杨过 | 18 | 男 | 3 | 3 | | 14 | 袁承志 | 19 | 男 | 5 | 3 | | 8 | 小龙女 | 20 | 女 | 3 | 3 | | 4 | 萧峰 | 21 | 男 | 6 | 3 | | 2 | 萧远山 | 34 | 男 | 2 | 3 | +-----+-----------+------+--------+----------+---------+ 5 rows in set (0.00 sec)
函数
数学函数
| 函数 | 含义 |
|---|---|
| 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..........) | 返回返回集合中最小的值 |
abs返回绝对值
(root@localhost) [jianghu]> select abs(-1) -> ; +---------+ | abs(-1) | +---------+ | 1 | +---------+ 1 row in set (0.00 sec)0-1的随机数
(root@localhost) [jianghu]> select rand(); +--------------------+ | rand() | +--------------------+ | 0.8401190907136715 | +--------------------+ 1 row in set (0.00 sec)返回集合中最大的值
(root@localhost) [jianghu]> select greatest(55,66,88); +--------------------+ | greatest(55,66,88) | +--------------------+ | 88 | +--------------------+ 1 row in set (0.00 sec)返回大于或等于 x 的最小整数
返回小于或等于 x 的最大整数
(root@localhost) [jianghu]> select ceil(2.5); +-----------+ | ceil(2.5) | +-----------+ | 3 | +-----------+ 1 row in set (0.00 sec) (root@localhost) [jianghu]> select floor(2.5); +------------+ | floor(2.5) | +------------+ | 2 | +------------+ 1 row in set (0.00 sec)
聚合函数
| 函数 | 含义 |
|---|---|
| avg() | 返回指定列的平均值 |
| count() | 返回指定列中非 NULL 值的个数 |
| min() | 返回指定列的最小值 |
| max() | 返回指定列的最大值 |
| sum(x) | 返回指定列的所有值之和 |
查找Dragon年龄平均数
(root@localhost) [jianghu]> select avg(age) from Dragon; +----------+ | avg(age) | +----------+ | 32.7222 | +----------+ 1 row in set (0.00 sec)求表中年龄的总和
(root@localhost) [jianghu]> select sum(age) from Dragon; +----------+ | sum(age) | +----------+ | 589 | +----------+ 1 row in set (0.00 sec)求表中年龄的最大值
(root@localhost) [jianghu]> select max(age) from Dragon; +----------+ | max(age) | +----------+ | 100 | +----------+ 1 row in set (0.00 sec)求表中年龄最小值
(root@localhost) [jianghu]> select min(age) from Dragon; +----------+ | min(age) | +----------+ | 18 | +----------+ 1 row in set (0.00 sec)求表中有多少非空记录
(root@localhost) [jianghu]> select count(*) from Dragon; +----------+ | count(*) | +----------+ | 21 | +----------+ 1 row in set (0.00 sec)
字符串函数
| 函数 | 描述 |
|---|---|
| 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) [jianghu]> select trim(leading '胡一' from '胡一刀'); +-----------------------------------------+ | trim(leading '胡一' from '胡一刀') | +-----------------------------------------+ | 刀 | +-----------------------------------------+ 1 row in set (0.00 sec) (root@localhost) [jianghu]> select trim(both '刀' from '胡一刀'); +-----------------------------------+ | trim(both '刀' from '胡一刀') | +-----------------------------------+ | 胡一 | +-----------------------------------+ 1 row in set (0.00 sec)(root@localhost) [jianghu]> select trim(both from '一刀 '); +--------------------------------+ | trim(both from '一刀 ') | +--------------------------------+ | 一刀 | +--------------------------------+ 1 row in set (0.00 sec)
#去除空格
length:
select name,length(name) from students;
#计算出字段中记录的字符长度
(root@localhost) [jianghu]> select name ,length(name) from Dragon; +--------------+--------------+ | name | length(name) | +--------------+--------------+ | 风清扬 | 9 | | 萧远山 | 9 | | 东方不败 | 12 | | 萧峰 | 6 | | 张三丰 | 9 | | 令狐冲 | 9 | | 杨过 | 6 | | 小龙女 | 9 | | 张无忌 | 9 | | 独孤求败 | 12 | | 郭靖 | 6 | | 黄蓉 | 6 | | 胡一刀 | 9 | | 袁承志 | 9 | | 虚竹 | 6 | | 石破天 | 9 | | 段誉 | 6 | | 阿青 | 6 | | xxx | 3 | | xxx | 3 | | xxx | 3 | +--------------+--------------+ 21 rows in set (0.00 sec)
replace
语法：select replace(字段，'原字符''替换字符') from 表名；(root@localhost) [jianghu]> select replace(name,'xxx' ,'尹志平') from Dragon;
group by
对group by 后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的
group by有一个原则，就是select 后面的所有列中，没有使用聚合函数的列必须出现在 group by的后面。
语法：
select 字段1，sum(字段2) from 表名 group by 字段1；
求各个classid中年龄最大的
(root@localhost) [jianghu]> select classid,max(age) from Dragon group by classid; +---------+----------+ | classid | max(age) | +---------+----------+ | NULL | 50 | | 1 | 100 | | 2 | 25 | | 3 | 34 | | 5 | 33 | | 8 | 27 | +---------+----------+ 6 rows in set (0.00 sec)求平均年龄
(root@localhost) [jianghu]> select classid,avg(age) from Dragon group by classid; +---------+----------+ | classid | avg(age) | +---------+----------+ | NULL | 50.0000 | | 1 | 49.1250 | | 2 | 25.0000 | | 3 | 22.4000 | | 5 | 28.6667 | | 8 | 24.0000 | +---------+----------+ 6 rows in set (0.00 sec)
having
用来过滤由group by语句返回的记录集，通常与group by语句联合使用
having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。
语法:SELECT 字段1，SUM("字段")FROM 表格名 GROUP BY 字段1 having（函数条件);
假如用where过滤会报错
(root@localhost) [jianghu]> select classid,avg(age) from Dragon 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此时我们要用having过滤出各个classid中年龄大于23的平均年龄
(root@localhost) [jianghu]> select classid,avg(age) from Dragon group by classid having avg(age) > 23; +---------+----------+ | classid | avg(age) | +---------+----------+ | NULL | 50.0000 | | 1 | 49.1250 | | 2 | 25.0000 | | 5 | 28.6667 | | 8 | 24.0000 | +---------+----------+ 5 rows in set (0.00 sec)
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
