MySQL笔记

MySQL的常见命令

1.查看当前所有数据库

show databases;

2.打开指定的库

​ use 库名;

3.查看当前库的所有表

show tables;

4.查看其它库的所有表

show tables from 库名；

5.创建表

create table 表名(

​ 列名 列类型,

​ 列名 列类型,

​ …

)

6.查看表结构

desc 表名;

7.查看服务器的版本

方式一：登录mysql服务端

select version();

方式二：没有登录mysql服务端

mysql --version或mysql --v

MySQL的语法规范

1.不区分大小写,但建议关键字大写，表名、列名小写

2.每条命令用分号结尾

3.每条命令根据需要，可以进行缩进、换行

4.注释

​ 单行注释：#注释文字

​ 单行注释：-- 注释文字

​ 多行注释：/* 注释文字 */

DQL语言

一、基础查询：

select 
	查询列表 
from 
	表名；

特点：

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

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

1、查询表中的单个字段

select last_name from employees;

2、查询表中的多个字段

select last_name,salary,email from employees;

3、查询表中的所有字段

select * from employees;

4、查询常量值

select 100;#数字
select 'john';#字母

5、查询表达式

select 100*90;

6、查询函数

select version();#调用该函数 显示它的返回值

7、起别名

select 
	100*90 as "结果";
select 
	last_name as "姓"，first_name as "名" 
from 
	employees;#as 可去掉换成空格

8、去重

select 
	distinct department_id 
from
	employees;#去除重复的部门编号

9、+号的作用

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

select 100+90;两个操作数都为数值型，则做加法运算

select ‘123’+90;其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算，如果失败，则将字符型数值转换为0

select null+10;只要其中一方为null，则结果肯定为null

若要做拼接：

select 
	concat(last_name,first_name) as "姓名" 
from 
	employees;#查询员工名和姓连接成一个字段，并显示为姓名

显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT

select 
	ifnull(commission_pct,0) as 奖金率,# 若commission_pct为null返回0
select 
	concat(`first_name`,',',`last_name`,',',`job_id`,',',ifnull(commission_pct,0)) as OUT_PUT 
from 
	employees;

二、条件查询

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

分类：

1、按条件表达式筛选

条件运算符：大于> 小于< 等于= 不等<> 大于等于>= 小于等于<=

# 查询工资>12000的员工信息
select
	*
from 
	employees
where
	salary>12000;
# 查询部门编号不等于90号的员工名和部门编号
select
	last_name,
	department_id
from
	employees
where
	department_id<>90;
	

2、按逻辑表达式筛选

逻辑运算符：与and 或or 非not

# 查询工资在10000到20000之间的员工名、工资以及奖金
select
	last_name,
	salary,
	commission_pct
from
	eemployees
where
	salary>=10000 and salary<=20000;
# 查询部门编号不是在90到110之间，或者工资高于15000的员工信息
select
	*
from
	employees
where
	department_id<90 or department_id>110 or salary>=15000;

3、模糊查询

like :一般和通配符搭配使用：

​ %表示任意多个字符（包含0个字符）

​ _表示任意单个字符

# 查询员工姓中包含字符a的员工信息
select
	*
from
	employees
where
	last_name like '%a%';
#查询员工姓中第三个字符为n,第五个字符为l的员工名和工资
select
	first_name,
	salary
from
	employees
where
	first_name like '__n_l%';
# 查询员工名中第二个字符为_的员工名
select
	first_name
from
	employees
where
	first_name like '_\_%';# 使用转义字符\
或者可以：
where
	first_name like '_$_%' escape '$';# 用escape说明$为转义字符

between and:包含临界值

# 查询员工编号在100到120之间的员工信息
select
	*
from
	employees
where
	employee_id betweeen 100 and 120;

in:用于判断某字段的值是否属于in列表中的某一项

​ in列表的值类型必须统一

# 查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
select
	first_name,
	job_id
from
	employees
where
	job_id in ('IT_PROG','AD_VP','AD_PRES');

is null:=或<>不能用于判断null值

# 查询没有奖金率的员工名和奖金率
select
	first_name,
	commission_pct
from
	employees
where
	commission_pct is null;
# 查询有奖金的
where commission_pct is not null;

安全等于<=> 可读性差 不常用

# 查询没有奖金率的员工名和奖金率
select
	first_name,
	commission_pct
from
	employees
where
	commission_pct <=>null;
#查询工资为12000的员工信息
select
	*
from
	employees
where
	salary <=> 12000;

三、排序查询

select
	查询列表
from
	表
Where
	筛选条件
order  by 排序列表 [asc|desc]#asc代表的是升序，desc代表的是降序，如果不写，默认是升序
							#order by子句一般放在查询语句的最后面，limit子句除外
# 例一：查询员工信息，要求工资从高到低排序
select  
	*
from
	employees
order by salary desc;
#例二：查询部门编号>=90的员工信息，按入职时间的先后进行排序
select
	*
from 
	emloyees
where
	department_id>=90
order by hiredate asc;
#例三：按年薪的高低显示员工信息和年薪【按表达式排序】
selsect
	*,
	salary*12+(1+ifnull(commission_pct,0))
from
	employees
order by salary*12+(1+ifnull(commission_pct,0)) desc;
#例四：按年薪的高低显示员工信息和年薪【按别名排序】
selsect
	*,
	salary*12+(1+ifnull(commission_pct,0)) 年薪
from
	employees
order by 年薪 desc;
# 例五：按名字的长度显示员工的姓名和工资【按函数排序】
selsect 
	length(first_name) 字节长度,
	first_nmae,
	salary
from
	employees
order by length(first_name) desc;
#例六：查询员工信息，要求先按工资升序排序，再按员工编号降序排序【按多个字段排序】
select
	*
from
	employees
order by salary asc,employee_id desc;

		

四、常见函数

调用：select 函数名(实参列表)
分类：
	1、单行函数：如concat length ifnull等
	2、分组函数：做统计使用，又称为统计函数、聚合函数、组函数

1、字符函数

#length 获取参数值的字节个数
select length('john');#返回值为4

#concat 拼接字符串
select concat(first_nmae,'_',last_name) 姓名 from employees;

#upper lower
select upper('john');#返回JOHN
select lower('ABC');#返回abc
示例：将姓变大写，名变小写，然后拼接
select 
	concat(lower(first_name),upper(last_name)) 姓名
from
	employees;

#substr、substring （索引从1开始） 
截取从指定索引处后面所有字符
select substr('今天没有好好学习',5)；#输出为好好学习
截取从指定索引处指定字符长度的字符
select substr('今天没有好好学习',1,3)#输出今天没
示例:名字中首字符大写，其它字符小写然后用_拼接，显示出来
select
	concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2)))
from 
	employees;

#instr 返回子串第一次出现的索引，如果找不到则返回0
select instr('今天有好好学习吗','好好学习');#输出4

#trim
select length(trim('   好好学习   '));#输出12
select trim('a' from 'aaaa好好学aa习aaa');#输出好好学aa习

#lpad 用指定的字符实现左填充指定长度
select lpad('你好',4,'*');#输出**你好

#rpad 用指定的字符实现右填充指定长度
select rpad('你好',4,'ab');#输出你好ab

#replace
select replace('今天好好学习','好好学习','没有好好学习');#输出今天没有好好学习

2、数学函数

#round 四舍五入
select round(1.65);#输出2
select round(-1.45);#输出-1
select round(-1.65);#输出-2
select round(1.567,2);# 输出1.57

#ceil 向上取整,返回大于等于该参数的最小整数
select ceil(1.52);#输出2
select ceil(-1.02);#输出-1

#floor 向下取整，返回小于等于该参数的最大整数
select floor(9.99);#输出9
select floor(-9.99);#输出-10

#truncate 截断
select truncate(1.653423,1);#输出1.6

#mod 取余
select mod(10,3);#输出1

3、日期函数

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

#curdate 返回当前系统日期，不包含时间
select curdate();

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

#year 获取年
select year('1998-1-1') 年;#输出1998
select 
	year(hiredate) 年
from 
	employees;
#month 获取月
select month('1998-1-1') 月;#输出1
select monthname('1998-1-1') 月;#输出January
#获取日day 小时hour 分钟minute 秒second

#str_to_date 将日期格式的字符转换成指定格式的日期
查询入职日期为4-3 1992的员工信息
select
	*
from
	employees
where
	hiredate=str_to_data('4-3 1992','%c-%d %Y');

#date_format 将日期转换为字符
查询有奖金的员工名和入职日期（xx月/xx日 xx年）
select 
	first_name,
	date_format(hiredate,'%m月/%d日 %Y年') 入职日期
from
	employees
where
	commission_pct is nt null;

4、其它函数

select version();查询版本
select database();查询当前数据库
select user();查询当前用户

五、流程控制函数

1、if函数

select if(10>5,'大','小');#如果成立返回大，不成立返回小
#查询员工的名字和奖金，并标注有没有奖金
select
	first_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;
作用二：类似于多重if
case
when 条件1 then 要显示的值1
when 条件2 then 要显示的值2
...
else 要显示的值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;

六、分组函数

1、sum求和

select 
	sum(salary)
from 
	employees;#输出员工工资总和

2、avg求平均值

select 
	avg(salary)
from 
	employees;#输出员工工资平均值

3、max求最大值

select 
	max(salary)
from 
	employees;#输出员工工资最大值

4、min求最小值

select 
	min(salary)
from 
	employees;#输出员工工资最小值

5、count计算个数

select 
	count(salary)
from 
	employees;#输出员工数量