目录
查询基础
查询全部
select * from worker；
查询部分内容
select 部门号，职工号，姓名 from worker；
查询年薪：工资*14 年薪为别名,一般别名在双引号内
select 部门号，职工号，姓名,工资*14 年薪 from worker；
别名 as可以省略
select name as 姓名，job 工作 from t1；
加法说明
mysql> select 100+80; 180 mysql> select '123'+80; 203 mysql> select 'abc'+80; 80 mysql> select 'abc'+'bc'; 0 mysql> select null+80; null
空值的判断 不能直接= 可以使用安全等于<=>
select name from emp where comn is NULL;
select name from emp where comn is not NULL;不为空
去除重复结果distinct
select distinct 部门号 from worker；
查询在 之间
select * from worker where 工资 between 4000 and 5000；
查询工资为4000或者5000
select * from worker where 工资=4000 or 工资=5000；
select * from worker where 工资 in （4000，5000）；
使用like模糊查询
select * from worker where name like '张%'；
select * from worker where name like '张_'；张后面只有一个字
逻辑运算符and与，or或，not非，匹配正则：regexp
查询结果取前几个limit
select * from worker
order by 工资 desc
limit 3；
查询第三个开始的三条数据
select * from student limit 2,3;
查询性别非男的学生记录
select * from worker where gender ！='男'；
查询结果排序
select * from worker order by 工资； 默认升序
select * from worker order by 工资 esc；升序
select * from worker order by 工资 desc；降序
查询后将结果创建为新表
create table worktime（职工号 int primary key，姓名 varchar（20），参加工作 date） select 职工号，姓名，工作时间 from worker
联合查询UNION
select name from student union 加上all可以去除重复结果 select name from worker;
函数
select max（工资），min（工资），avg（工资），sum（工资）from worker；查询最大值，最小值，和值，平均值
select count（1）from worker；统计功能，括号里随便写点啥，不能不写
mysql> select sqrt(9);求二次方根 +---------+ | sqrt(9) | +---------+ | 3 | +---------+ mysql> select pow(2,3);求参数次方 +----------+ | pow(2,3) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) mysql> select mod(5,3);求余数 +----------+ | mod(5,3) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> select ceil(-4.7),ceiling(5.8);向下取整 +------------+--------------+ | ceil(-4.7) | ceiling(5.8) | +------------+--------------+ | -4 | 6 | +------------+--------------+ 1 row in set (0.00 sec) mysql> select floor(-4.7);向上取整 +-------------+ | floor(-4.7) | +-------------+ | -5 | +-------------+ 1 row in set (0.00 sec) mysql> select rand();生成0-1随机数 +---------------------+ | rand() | +---------------------+ | 0.18036383593488842 | +---------------------+ 1 row in set (0.00 sec) mysql> select round(-4.7);四舍五入 +-------------+ | round(-4.7) | +-------------+ | -5 | +-------------+ 1 row in set (0.00 sec)
字符串函数
统计字符个数
mysql> select length('this is text');
+------------------------+
| length('this is text') |
+------------------------+
| 12 |
+------------------------+
1 row in set (0.00 sec)
合并字符串
mysql> select concat('this','is');
+---------------------+
| concat('this','is') |
+---------------------+
| thisis |
+---------------------+
1 row in set (0.00 sec)
从左侧截取一定长度
mysql> select left('abcdefg',3);
+-------------------+
| left('abcdefg',3) |
+-------------------+
| abc |
+-------------------+
1 row in set (0.00 sec)
从右侧截取一定长度
mysql> select right('abcdefg',3);
+--------------------+
| right('abcdefg',3) |
+--------------------+
| efg |
+--------------------+
1 row in set (0.00 sec)
删除左右俩测空格
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
截取字符串，返回指定位置开始指定长度的字符串
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
字符串逆序
mysql> SELECT REVERSE('abc');
-> 'cba'
比较俩个字符串大小
mysql> SELECT STRCMP('text', 'text2');
-> -1
mysql> SELECT STRCMP('text2', 'text');
-> 1
mysql> SELECT STRCMP('text', 'text');
-> 0
时间日期类函数
-
curdate返回当前日期
-
curtime返回当前时间
-
now返回当前日期和时间
-
sysdate返回当前日期和时间
-
date获取指定日期时间的日期部分
mysql> select date(now()); +-------------+ | date(now()) | +-------------+ | 2022-07-28 | +-------------+ 1 row in set (0.00 sec)
-
time获取指定日期时间的时间部分
-
month获取指定日期的月份
-
mounthname英文显示获取到的月份
-
day获取指定日期的天数
-
dayname获取指定日期的星期几的英文
-
year获取年份
-
dayofweek获取对应一周的位置值，周日值为1
-
week获取指定日期时一年中的第几周
-
dayofyear获取指定日期是一年的第几天
流程控制函数
if判断，流程控制
ifnull判断是否为空
case搜索语句
mysql> select if(1<2,'yes','no'); +--------------------+ | if(1<2,'yes','no') | +--------------------+ | yes | +--------------------+ 1 row in set (0.00 sec) mysql> select name,gender,if(gender='M','帅气','漂亮') 备注 from t1; +---------+--------+--------+ | name | gender | 备注 | +---------+--------+--------+ | bob | M | 帅气 | | beb | M | 帅气 | | jim | F | 漂亮 | | cxk | M | 帅气 | | zhang | M | 帅气 | | wujing | M | 帅气 | | wangjie | M | 帅气 | +---------+--------+--------+ 7 rows in set (0.00 sec)
mysql> select ifnull(1,2),ifnull(null,3); +-------------+----------------+ | ifnull(1,2) | ifnull(null,3) | +-------------+----------------+ | 1 | 3 | +-------------+----------------+
mysql> select case when 1<0 then 'true' else 'false' end; +--------------------------------------------+ | case when 1<0 then 'true' else 'false' end | +--------------------------------------------+ | false | +--------------------------------------------+ mysql> select case 2 when 1 then 'one' when 2 then 'two' else 'more' end; +------------------------------------------------------------+ | case 2 when 1 then 'one' when 2 then 'two' else 'more' end | +------------------------------------------------------------+ | two | +------------------------------------------------------------+ 1 row in set (0.04 sec) mysql> select ename,sai, case when sai>20000 then 'A' when sai>15000 then 'B' else 'c' end as '工资级别' from t1;
数据分组group by
mysql> select c_id,max(s_score),min(s_score),avg(s_score) -> from score -> group by c_id; +------+--------------+--------------+--------------+ | c_id | max(s_score) | min(s_score) | avg(s_score) | +------+--------------+--------------+--------------+ | 01 | 80 | 31 | 64.5000 | | 02 | 90 | 30 | 72.6667 | | 03 | 99 | 20 | 68.5000 | +------+--------------+--------------+--------------+
having子句用于分组后的结果再进行条件过滤
mysql> select c_id,max(s_score),min(s_score),avg(s_score) -> from score -> group by c_id -> having avg(s_score)>65; +------+--------------+--------------+--------------+ | c_id | max(s_score) | min(s_score) | avg(s_score) | +------+--------------+--------------+--------------+ | 02 | 90 | 30 | 72.6667 | | 03 | 99 | 20 | 68.5000 | +------+--------------+--------------+--------------+ 2 rows in set (0.05 sec)
where子句必须在分组前使用，where子句不能使用聚合函数，having子句可以使用聚合函数
group_concat多行数据合并
select id，group_concat(name) from score group by id;
199
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
