尚硅谷 mysql基础 学习笔记

Mysql

相关概念

DB 数据库：保存一组有组织数据的容器

DBMS 数据库管理系统：用于管理DB中的数据

SQL 结构化查询语言：用于和DBMS通信的语言

# 进入 退出
mysql -h localhost -P 3306 -u root -proot
exit

常见命令

# 查看数据库版本
mysql --version
mysql -V
select version()(mysql服务器中)

# 查看数据库
show databases;

# 进入数据库
Use 数据库名;

# 查看数据库表
show tables;
show tables from 数据库名;

# 查看当前所在的数据库
select database();

#建表
create table info(
	id int,
	name varchar(20)
);

#查看表结构
desc 表名;

# ``单引号区分关键字和字段

语法规范

• Mysql中不区分大小写，建议关键字大写，表名、列名小写
• 命令可以换行和缩进，用分号结尾
• 注释
  • 单行注释：#或 --+空格
  • 多行注释：/**/

DQL(data query language)

基础查询

# 单表查询字段
select 字段1、字段n from 表;

# 起别名 别名中出现关键字等时可以加单引号
select 字段 as 别名 from 表;
select 字段 别名 from 表;

# 去重
select distinct 字段 from 表名;

# +号
# +号连接字符串和数字，先将字符串转换为数字，如果失败则赋值为零；

# 拼接 null和其他拼接
select concat(lastname,firstname)as 姓名 from 表

# ifnull
ifnull(字段a,value) 代表字段a的值为空则显示value

# isnull
判断某字段或表达式是否为null，是返回1，否则返回0

条件查询

# 基本结构
select 字段 from 表 where 筛选条件

筛选条件分类

• 条件表达式：>、<、=、不等于(!=、<>) >=、<=
  • 安全等于<=>：可以判断是否满足任意值
• 逻辑表达式：&&、||、！、and、or、not
• 模糊查询：like、between and、in、is null、is not null
  • like：和通配符搭配使用；%任意多个字符，包含0个字符；_任意一个字符
  • between and：包含两个边界
  • in：判断值是否是列表中的某一项
  • is null：=和!=不能判断null

# 查询部门编号不等于90的员工编号和部门编号
select employ_id,deparment_id from employee where deparment_id <>90

# 查询工资在1w到2w之间的员工名、工资
select last_name,salary, from employee where salary>=10000 and salary<=20000;

# 查询员工名中包含王的信息
select * from employee where last_name lile '王%' or first_name like '王%'

# 查询员工编号在100到200之间的信息
select * from employee from where employ_id between 100 and 200;

# 查询员工部门编号为IT_PROG,AD_VP,IT_MVG中某一个的员工信息
select * from employee from where deparment_id in('IT_PROG','AD_VP','IT_MVG');

# 查询没有奖金的员工名和奖金率
select last_name,comission_pct from employee where comission_pct is null;

# 安全等于
# 查询没有奖金的员工信息
select * from employee where commission_pcy <=> null;
# 查询工资为12000的员工信息
select * from employee where salary <=> 12000;

排序查询

order by 一般放在最后面，除开limit以外

select * from 表 [where 条件] order by 排序列表 [desc 降序||asc 升序,默认]

# 查询部门编号大于等于90的员工信息，按入职时间先后顺序进行排序
select * from employee where department >=90 order by hiredate asc;

# 按年薪的高低显示员工信息和年薪[按表达式排序]、[按别名排序]
select *,(12*salary*(1+ifnull(commission,0)) as '年薪' from employee order by '年薪' desc;

# 按姓名的长度降序显示员工的信息[按函数排序]
select * from employee order by length(last_name) desc;

# 查询员工信息，先按员工工资升序排序，再按员工编号降序排序[多字段排序]
select * from employee order by salary asc,employ_id desc;

常见函数

select 函数表(实参) [from 表]

单行函数

• 字符函数
• length(字符串)：获取字符串字节个数
• concat(str1,str2)：拼接字符串
  • upper(str)：将字符串变大写
  • lower(str)：将字符串变小写
  • substr[ing](str,index)：截取索引为index到结尾处的字符串；索引从一开始
  • substr[ing](str,index,len)：获取从索引为index开始的长度为len的字符串
  • instr(str1,str2)：返回str2在str1第一次出现的索引，没有则返回0
  • trim(str)：去掉str的前后空格
  • trim(str1 from str2)：去掉str2前后的str1
  • lpad(str1,len,str2)：用str2填充str1到指定的长度 ；如果str1长度小于len则从左往右截断
  • rpad(str1,len,str2)：用str2填充str1到指定的长度 ；如果str1长度小于len则从右往左截断
  • replace(str1,str2,str3)：用str3替换在str1中出现的所有的str2
• 数学函数
• round(val)：将val四舍五入；round(1.567,2)结果为1.57
• ceil(val)：返回>=val的最小整数
• floor(val)：返回<=val的最大整数
  • truncate(val,len)：保留len位val
    • mod(a,b)：返回a%b ; 取余的实质 a-a/b*b
      • rand()：随机获取0-1之间的小数
• 日期函数
  • now()：返回当前系统日期和时间
  • curdate()：返回当前系统日期
    • curtime()：返回当前时间，不包含日期
    • year()、month()、day()：截取日期
    • monthname()：获取月份的英文
    • str_to_date(str,’%m-%d-%y’)：将日期格式字符串转换指定格式日期
    • date_format(str,’%m-%d-%y’)：将日期转换为指定格式的字符串
    • datediff(date1,date2)：返回date1-date2的日期差的天数
• 其他函数
  • version()：查看数据库版本
  • database()：当前数据库
  • user()：当前用户
  • password(str)：返回对str加密之后的值
  • md5(str)：返回对str md5加密之后的值
• 流程控制函数
  • if(表达式,val1,val2)：如果表达式成立则显示val1，否则显示val2
  • case 表达式 when 常量1 then 显示的值1 when 常量2 then 显示的值2 esle 常量n end;
  • case when 条件1 then 显示的值1 when 条件2 then 显示的值2…else 显示的值n end

# 显示员工的姓名，首字母大写，其他字母小写然后用_拼接
select concat(upper(subtring(last_name,1,1)),'_',lower(substring(last_naem,2))) from employee;

# 查询员工的工资，如果部门号为30，则显示1.1倍工资，部门号为40则显示1.2倍工资，其他部门为原工资
select salary as 原始工资 
case department_id when 30 then 1.1*salary 
when 40 then 1.2*salary 
else salary
end as 新工资
from employee;

# 查询员工的工资，工资>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 employee;

分组函数

常用于统计使用，又称为统计函数，聚合函数，组函数；分组查询返回的结果为1；分组函数均忽略null

• sum()：求和；忽略null；可与distinct搭配使用
• avg()：平均；忽略null；可与distinct搭配使用
• max()：最大值；忽略null；可与distinct搭配使用
• min()：最小值；忽略null；可与distinct搭配使用
• count()：统计非空数值个数；可与distinct搭配使用

# 基本结构
select max(salary),min(salary) from employee;

# 统计行数
select count(*)/count(1)/count(字段) from employee;
myisam引擎下，conut(*)效率最高
innodb引擎下，count(*)和count(1)效率差不多，比count(字段)kuai

# 查询员工表中最大入职时间和最小入职时间的相差天数
select datediff(max(hiberate)-min(hiberate)) from employee;

分组查询

having对分组之后的结果进行筛选，where是对原表employ进行筛选；分组函数放在having中

# 基本结构
select column,column(分组列) from 表 [where 筛选条件] group by 分组列 [order by column];

# 查询每个工种的最高工资
select max(salary),job_id from employee group by job_id;

# 查询邮箱中包含字母a的，每个部门的平均工资
select avg(salary),department_id from employee where email like'