MySQL数据库2

一、DQL(数据查询语言)--select关键字

基本结构是select子句，from子句和where子句

1.基础查询

select 查询列表 from 表名;

特点：查询列表可以是表中的字段、常量值、表达式、函数

           查询的结果是一个虚拟的表格

(1)查询表中的单个字段

 (2)查询表中的多个字段

 (3)查询表中的所有字段

一一列举

拖拽数据库中的表

(4)查询常量值 

(5)查询表达式

(6)查询函数

(7)取别名

 便于理解且若要查询的字段有重名，别名可以区分

①as

②空格

 (8)去重--distinct

(9) +号的作用

MySQL中只有一个功能：运算符

案例：将员工名和姓连接成一个字段，显示为姓名

函数concat(字符串,字符串,...)返回字符串拼接的结果，但若某一字符串的内容为null，结果也为null

解决方法：根据有null的字段分情况讨论，为null时设为0，不为null时正常显示，函数ifnull() 

SELECT
	IFNULL( commission_pct, 0 ) AS 奖金率,
	commission_pct 
FROM
	employees;

因此，在有null值的字符串处改为ifnull(commission_pct,0) 

注：快速格式化：“美化SQL”

2.条件查询

SELECT
	查询列表 
FROM
	表名 
WHERE
	筛选条件;

条件表达式：>,<,>=,<=,=,!=(<>) 

安全等于<=>既可以判断null也可以判断其他值

逻辑表达式（连接条件表达式）：&&,||,!       and or not

模糊查询：

like一般和通配符%、_使用，%表示任意0个或多个字符,_表示任意单个字符

between and提高语句简洁度，包含临界值，两个临界值不能调换顺序（相当于>=  and  <=

in判断某字段的值是否属于in列表中的一项，提高语句简洁度，in列表的值类型必须一致或兼容，不能使用通配符（因为相当于等于号）

is null,is not null 等号和不等号不能判断null，is后不能改别的数充当等号的作用例如is 12000

案例：查询工资大于12000的员工

案例：查询工资在12000-15000的员工名、工资和奖金。

SELECT
	last_name,
	salary,
	commission_pct 
FROM
	employees 
WHERE
	salary >= 12000 
	AND salary <= 15000;

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

SELECT
	*
FROM
	employees 
WHERE
	last_name LIKE '%a%';

案例：查询员工名中第三个字符为e，第五个字符为a的员工名和工资

SELECT
	last_name,
	salary
FROM
	employees 
WHERE
	last_name LIKE '__a_e';

案例：查询员工名中第二个字符为——的员工名 

 in

SELECT
	last_name,job_id 
FROM
	employees 
WHERE
	job_id = 'IT_PROT' 
	OR job_id = 'AD_VP' 
	OR job_id = 'AD_PRES';
等价于
SELECT
	last_name,job_id 
FROM
	employees 
WHERE
	job_id IN('IT_PROT','AD_VP','AD_PRES');

3.排序查询

SELECT
	查询列表               ③
FROM
	表                    ①
[ WHERE 筛选条件 ]         ②
ORDER BY
	排序列表 [ ASC | DESC ];④

 asc升序，desc降序，默认升序，order by 子句中可以支持单个字段、多个字段、表达式、函数、别名，order by子句一般是放在查询语句的最后面，limit子句除外

SELECT
	*
FROM
	employees
ORDER BY
	salary DESC;#查询员工信息，工资从高到低排序

求字符串长度函数length() 

4.常见函数

函数：将一组逻辑语句封装在方法体中，对外暴露方法名

好处：隐藏了细节，提高代码的重用性

调用：select 函数名（实参列表）[from 表]；

分类：单行函数 concat、length、ifnull

分组函数：做统计使用（统计函数、聚合函数、组函数）

（1）字符函数

length()获取参数值的字节数。汉字3个字节，字母1个字节

concat()拼接字符串

upper(),lower()大小写转换

substr()、subtring()截取字符串

注：索引从1开始

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

截取从指定索引处指定字符长度的字符 

instr(字符串1，字符串2)；返回字符串2在字符串1中第一次出现的索引值，找不到返回0

trim()去掉前后空格

去掉前后指定字符

lpad() 用指定字符实现左填充指定长度

 要求字符数小于原本字符数就截断

rpad()用指定的字符实现右填充指定长度

replace()替换

（2）数学函数

round(数字)四舍五入，对负数来说先取绝对值四舍五入再添负号

round(1.56876,2)保留两位小数1.57

ceil()向上取整ceil(-1.02)=>-1

floor()向下取整 

truncate()截断truncate(小数，整数)保留整数位小数，后面舍弃

mod(）取余，结果的符号与被除数一致

（3）日期函数

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

curdata()返回当前系统日期，不加时间

curtime()返回当前时间，不包含日期

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

SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

 

序号	格式符	功能
1	%Y	四位的年份
2	%y	2位的年份
3	%m	月份（01，02，...，11，12）
4	%c	月份（1，2，..，11，12）
5	%d	日（01，02，..）
6	%H	小时（24小时制）
7	%h	小时（12小时制）
8	%i	分钟（00，01，..59）
9	%s	秒（00，02，...59）

（4）其他函数

（5）流程控制函数

if

case

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2；
...
else 要显示的值n或语句n;
end

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

5.分组函数

sum求和 avg平均值 max最大值 min最小值 count计算个数

sum avg一般用于处理数值型

max min count可以处理任何类型

以上分组函数都忽略null值

和distinct搭配实现去重

count详细介绍

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;#查看总行数
SELECT COUNT(1) FROM employees;#查看总行数，原理是相当于在原表的基础上加了1列1，数1的个数就是数行数
/*效率上，在myisam存储引擎下，count(*)效率高，在innodb存储引擎下，count(*)和count(1)效率差不多，比count(字段)要高 

和分组函数一同查询的字段要求是group by后的字段