一、MySql基础查询
- 查询表中的某个字段
select 字段名 from 表名;
- 查询表中的多个字段
select 字段名1, 字段名2,... from 表名;
- 查询表中所有的字段
select *from 表名;
- 查询常量值
select 值; //select 100; select "zhansan";
- 查询表达式
select 数字 运算符 数字; //select 100%98;
- 查询函数
select 函数名(); //select version(); 查询版本号 select database(); 查询当前数据库
- 起别名(提高可读写,有重名可以区分开来)
select 表达式 as 别名; //SELECT DATABASE() as 版本;
//select last_name as 姓, first_name as 名 from employees;
select 字段名1 别名,字段名2 别名 from 表名;
//select last_name 姓, first_name 名 from employees;
- 去重(distinct)
select distinct 字段名 from 表名;
- 字段拼接查询
select concat(字段名1,字段名2,...) as 别名 from 表名;
//select concat(last_name,first_name) as 姓名 from employees;
二、条件查询(where)
分类:
- 按条件表达式查询(条件运算符 :> < = != >= <=);
- 按逻辑表达式筛选(逻辑运算符 && || ! and or not);
- 模糊查询(like between and in is null);
- 查询表中满足某个条件的所有信息
select * from 表名 where 判断条件;
select * from employees where salary > 12000; //查询工资大于12000的员工信息
select last_name,department_id from employees department_id != 90; //查询部门编号不等于90号的员工名和部门编号
- 按逻辑表达式查询
//查询工资在10000到20000之间的员工名,工资及奖金(where ... and ...)
select last_name,salary,commission_pct from employees salary>=10000 and salary<=20000;
//查询部门编号不是在90到110之间,或者工资高于15000的员工信息(or)
select * from employees where department_id < 90 or department_id >110 or salary>15000;
- 模糊查询(like)
//查询员工名中包含字符a的员工信息(%通配符)
select *from employees where last_name like "%a%";
//查询员工名中第三个字符为e,第五个字符为a的员工名和工资(_下划线)
select last_name,salary from employees where last_name like "__e_a";
//查询员工名中第二个字符为_的员工名(\专一字符)
select last_name from employees where last_name like "_\_%";
- 模糊查询(between … and …)
//查询员工编号在100到120之间的员工信息
select * from employees where employee_id between 100 and 120;(包含临界值,但是不能颠倒顺序)
- 模糊查询(in)(判断某字段的值是否属于in列表中的某一项,in列表中的值的数据类型必须一致)
//查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
select last_name,job_id from employees where job_id = "IT_PROG" or job_id = "AD_VP" or job_id = "AD_PRES";
select last_name,job_id from employees where job_id in("IT_PROG","AD_VP","AD_PRES");
- 模糊查询( is null 仅仅判断 NULL值 is not null)
//查询没有奖金的员工和奖金率
select last_name,commission_pct form employees where commission_pct is null;
select last_name,commission_pct form employees where commission_pct is not null;
//安全等于(判断是否等于) <=>
select last_name,commission_pct form employees where commission_pct <=> null;
ifnull(id,0) //判断是否为null ,如果为null 就返回指定的值,否则返回本来的值
三、排序查询
语法:
select 查询列表 from 表名 【where 筛选条件】 order by 排序列表 【asc | desc】;
特点:
- asc 代表的是升序,desc代表的是降序,如果不写就默认是升序排序
- order by子句一般是放在查询语句的最后面,limit 子句除外
- 查询员工信息,要求工资从高到底排序
select * from employees order by desc;
- 查询部门编号 >= 90的员工信息,按入职时间的先后排序(添加筛选条件)
select * from employees where department_id >= 90 order by asc;
- 按年薪的高低显示员工的信息和年薪 (按表达式排序)
select *,salary *12 * (1 + ifnull(commission_pct,0)) as 年薪 from employees order by salary *12 * (1 + ifnull(commission_pct,0)) desc;
- 按年薪的高低显示员工的信息和年薪 (按别名排序)
select *,salary *12 * (1 + ifnull(commission_pct,0)) as 年薪 from employees order by 年薪 desc;
- 按姓名的长度显示员工的姓名和工资(按函数排序)
select length(last_name) as 字节长度,last_name,salary from employees order by length(last_name) desc;
- 查询员工信息,要求先按工资排序(升序),再按员工编号排序(降序)(多个字段排序)
select * from employees order by salary asc,employ_id desc;(可以继续多个次要排序)
四、常见函数
调用方法:select 函数名(实参列表)【from 表】
分类:
- 单行函数:例如 concat 、length()、ifnull()等
- 分组函数:做统计使用,又称统计函数、聚合函数、组函数
1. 字符函数
- length() 获取参数值得字节个数
select length("join"); //4个字节
select length("张三丰"); //9个字节。(一个中文3个字节)
- concat() 拼接字符串
select concat(last_name,"_",first_name) as 姓名 from employees;
- upper()、lower() 转换大小写
select upper("join"); //转换成大写
select lower("JOIN"); //转换成小写
- substr()、substring() 截取字符串 有函数重载,且注意索引是从1开始
select substr("abcd",2); //bcd 从指定索引处截取到末尾字符长度
select substr("abcdef",2,3); //从指定索引处截取指定字符长度
- instr() 返回子串第一次出现的索引,如果找不到就返回0
select instr("杨不悔爱上了小强","小强");
- trim() 去空格
select length(trim(" 张三丰 "));
- lpad() 用指定的字符实现左填充指定长度
select lpad("张三丰",10,"*"); //*******张三丰
select lpad("张三丰",2,"*"); 超过了就不填充,并且会截断字符串 //张三
- rpad() 用指定的字符实现右填充指定长度
select rpad("张三丰",10,"*"); //张三丰*******
select rpad("张三丰",2,"*"); 超过了就不填充,并且会截断字符串 //张三
- replace() 替换
select replace("我爱我的家","我的家","我的祖国"); //我爱我的祖国
2. 数学函数
- round() 四舍五入
select round(1.5); //负数也可以(先取绝对值,再四舍五入,最后加负号)
select round(1.567,2) //小数点后面保留两位 1.57
- ceil() 向上取整 ,返回>=该参数的最小整数
select ceil(1.52); //2
- floor() 向上取整 ,返回<=该参数的最大整数
select floor(-9.99); //-10
- truncate() 截断
select truncate(1.699,1); //1
- mod() 取余(a-a/b*b)
select mod(10,-3); //1
3. 日期函数
- now() 返回当前系统日期 + 时间
select now(); //2020-06-19 20:55:38
- curdate() 返回当前系统日期,不包含时间
select curdate(); //2020-06-19
- curtime() 返回当前系统时间
select curtime(); //20:58:12
- 可以获取指定的部分,年,月,日,小时,分钟,秒
select year(now()); //函数可以嵌套调用
select month(now());
- str_to_date() 将字符通过指定的格式转换成日期
select str_to_date("2020-6-19","%Y-%c-%d");
- date_format() 将日期转换成字符
select date_format(now(),"%y年%m月%d日"); //20年06月19日
- datediff() 返回两个日期相差的天数
select datediff("2020-05-01","202006-19");
4. 其他函数
- select version(); //查看当前数据库版本
- select database(); //查看当前使用的数据库
- select user(); //查看当前用户
五、流程控制函数
1. if()函数:if else 的效果
select if(10 < 5, "大",小);
select last_name,commission_pct,if(commission_pct is null,"没奖金,呵呵","有奖金,嘻嘻") from employees;
2. case函数使用一:switch case 的效果
语法:case 要判断的字段或表达式
when 常量1 then 要显示的值1或 语句1
when 常量2 then 要显示的值2或 语句2
…
else 要显示的值n或语句n;
end
- 查询员工的工资
//部门编号 = 30 ,显示的工资为1.1倍
//部门编号 = 40 ,显示的工资为1.2倍
//部门编号 = 50 ,显示的工资为1.3倍
//其他部门,显示的工资为原工资
select salary 原始工资,department_id,
case department_id
when 30 then salary * 1.1
when 40 then salary * 1.2
when 50 then salary * 1.3
else salary
end as 新工资
from employees;
3. case函数使用二:类似于 多重if
语法:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end
- 查询员工的工资情况
//如果工资>20000,显示A级别
//如果工资>15000,显示B级别
//如果工资>10000,显示C级别
//否则显示D级别
select salary
case
when salary>20000 then "A"
when salary>15000 then "B"
when salary>10000 then "C"
else "D"
end as "工资级别"
from employees;