MySQL初学笔记1008

兄弟们，数据库employee.sql可以从上一个博客中下载

条件查询

语法：

select 查询列表 from 表名; where 筛选条件

分类：

一、按条件表达式筛选 条件运算符：> < = != >= <= 二、按逻辑表达式查询 逻辑运算符： && || ! and or not 作用：用于连接条件表达式 &&和and:两个条件都为true，结果为true，反之为false ||或or:只要有一个条件为true,结果为true，反之为false ！或not：如果连接的条件本身为false，结果为true，反之为false 三、模糊查询 like between and in is null

一、按条件表达式筛选

案例1：查询工资>12000的员工信息

select * from employees where salary>12000

案例2：查询部门编号不等于90号的员工姓和部门编号

select last_name,department_id from employees where department_id !=90

二、按逻辑表达式筛选

案例1：查询工资在10000到20000之间的员工姓、工资以及奖金

select last_name,salary,commission_pct from employees where salary >=10000 and salary <=20000;

案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息

select * from employees where department_id<90 or department_id>110 or salary >15000;

模糊查询

like

特点： ①一般和通配符搭配使用 通配符： % 任意多个字符，包含0个字符 _任意单个字符

between and

特点： ①可以提高语句的简洁度 ②包含临界值 ③两个临界值不要调换顺序（从小到大）

in

特点： ①使用in提高语句简洁度 ②in列表的值类型必须统一或兼容

is null | is not null

特点： ①提高代码简洁度

1.like

案例1：查询员工姓中包含字符a的员工信息

select * from employees where last_name like '%a%';

案例2：查询员工姓中第三个字符为r,第五个字符为o的员工姓和工资

select last_name,salary from employees where last_name like'__r_o%';

案例3:查询员工姓中第二个字符为_的员工姓

select last_name from employees where last_name like '_\_%';

2.between and

案例1：查询员工编号在100到120之间的员工信息

select * from employees where employee_id BETWEEN 100 and 120;

3.in

案例1：查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工姓和工种编号

select last_name ,job_id from employees where job_id IN('IT_PROG','AD_VP','AD_PRES');

4.is null

案例1：查询没有奖金的员工姓和奖金率

select last_name,commission_pct from employees where commission_pct is null;

5.is not null

案例1：查询有奖金的员工姓和奖金率

select last_name,commission_pct from employees where commission_pct is not null;

排序查询

select 查询列表 from 表名; where 筛选条件 order by 排序列表 asc | desc 特点： 1.asc代表升序，desc代表降序 如果不写，默认是升序 2.order by子句可以支持单个字段、多个字段、表达式、函数、别名 3.order by子句一般是放在查询语句最后面，limit子句除外

案例1：查询员工信息，要求工资从高到低实现排序

select * from employees order by salary desc;

案例2：查询员工信息，要求工资从低到高实现排序

select * from employees order by salary asc;

案例3：查询部门编号>=90的员工信息，按入职时间先后进行排序

select * from employees where department_id >=90 order by hiredate asc

案例4：按年薪的高低显示员工的信息和年薪【按表达式排序】

select * ,salary*12*(1+IFNULL(commission_pct,0)) 年薪 from employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) desc

案例5：按年薪的高低显示员工的信息和年薪【按别名排序】

select * ,salary*12*(1+IFNULL(commission_pct,0)) 年薪 from employees ORDER BY '年薪' desc

案例6：按姓名的长度显示员工的姓名和工资【按函数排序】

select LENGTH(last_name) 字节长度,last_name,salary from employees order by LENGTH(last_name) desc;

案例7：查询员工信息，要求先按工资升序排序，再按员工编号降序排序【按多个字段排序】

select * from employees order by salary asc,employee_id desc;

练习题

1.查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序

select last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪 from employees order by 年薪 desc,last_name asc

2.选择工资不在8000到17000的员工的姓名和工资，按工资降序

select last_name,salary from employees where salary between 8000 and 17000 order by salary desc

3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序

select * from employees where email like '%e%' order by LENGTH(email) desc,department_id asc;

常见函数

/* 功能：类似于Java的方法，将一组逻辑语句封装在方法体重，对外暴露方法名 好处：1.隐藏了实现细节 2.提高了代码的重用性 调用：select 函数名(实参列表) from 表 特点：函数名 函数功能 分类：1.单行函数（） 如：concat、length、ifnull等 2.分组函数 功能：做统计使用，又称为统计函数、聚合函数、组函数

*/

一、字符函数

1.length 获取参数值的字节个数

select length('john') select length('奥斯卡1221')

2.concat拼接字符串

select concat(last_name,'_',first_name) 姓名 from employees;

3.upper、lower

select upper(last_name) from employees select lower(last_name) from employees

案例：将姓变大写，名变小写，然后拼接

select concat(upper(last_name),'_',lower(first_name)) 姓名 from employees

4.substr 截取字符（substring）

注意：索引从1开始.

截取从指定索引处后面所有字符

select substr('奥斯卡爱上了宁荣荣',7) out_put;

截取从指定所引出指定字符长度的字符

select substr('奥斯卡爱上了宁荣荣',1,3) out_put;

案例：姓名中首字符大写，其他字符小写，用下划线拼接显示出来

select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) out_put from employees;

5.instr 用于返回子串第一次出现的索引，找不到返回0

select instr('戴沐白爱上了朱竹清','朱竹清') as out_put

6.trim 去前后空格/字符

select trim(' 史莱克学院 ') as out_put select trim('a'from 'aaaa史莱克aa学院aaaaa') as out_put

7.lpad 用指定的字符实现左填充指定长度

select lpad('小舞',10,'*') as out_put

8.rpad 用指定的字符实现右填充指定长度

select rpad('小舞',10,'*') as out_put

9.replace 替换

select replace('张无忌爱上了周芷若','周芷若','赵敏') as out_put

二、数学函数

round 四舍五入

select round(1.65) select round(-1.45)

ceil 向上取整,返回大于等于该参数的最小整数

select ceil(1.02) select ceil(-1.02)

floor 向下取整

select floor(-9.99) select floor(1.02)

truncate 截断

select truncate(1.65,1)

mode取余

select mod(10,3) select 10%3

三、日期函数

now 返回当前系统的日期+时间

select now()

curdate 返回当前系统日期,不包含时间

select curdate()

curtime 返回当前系统时间，不包含日期

select curtime()

获取指定的部分、年、月、日、时、分、秒

select year(now()) select year(hiredate) 年 from employees

str_to_date 将字符通过指定的格式转换成日期

select STR_TO_DATE('1999-1-1','%Y-%c-%d') as out_put

案例：查询入职日期1992-4-3的员工信息

select * from employees where hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y')

date_format将日期转换成字符

select DATE_FORMAT(now(),'%Y年%m月%d日') as out_put

查询有奖金的员工名和入职日期（xx月/xx日 xx年）

select last_name,DATE_FORMAT(hiredate,'%m月%d日 %Y年') 入职日期 from employees where commission_pct is not null

其他函数

select version() select database()