Mysql 笔记
查看其他的库的所有表:
show tables from db_test;
命令行下使用 \g 结尾也可以;
注释:
单行注释:# 注释文字
单行注释:-- 注释文字
多行注释: /* 注释文字 */
select ‘123’+90 : 如果其中一方为字符型,会试图转换为 数值
运算时 一方为 null 全部为null
IFNULL(commission_pct,0) 指定为null 的默认值
SELECT
IFNULL(commission_pct,0) as 奖金率,
commission_pct
FROM
employee;
模糊查询:
like
特点:一般和通配符搭配适用
通配符:
% 任意多个字符,包含0个字符
_ 任意单个字符
eg: ,,, where last_name like '_$_' escape '$';
between and
in
eg: ,,,, where join_id in ('it_prot',"ad_vp",'ad_pres'); #不支持准确的值
is null | not null
安全等于 <=>:判断是否等于
不等于:<>
远程连接: mysql -h localhost -P 3306 -uroot -proot
查询函数:select 函数名(实参列表)
select * from employee order by salary ; # 默认升序
#查询员工的姓名和部门号和年薪 按年薪降序,按姓名升序
select last_name ,department_id,salary*12(1+IFNULL(commission_pct,0)) 年薪
from employees
order by 年薪 desc ,last_name asc;
#选择工资不在8000 到17000的员工的姓名和工资,按工资降序
select last_name,salary
form employees
where salary not between 8000 and 17000
order by salary desc;
#查询邮箱中包含e的员工的信息,并先按邮箱的字节数降序,再按部门号升序
select * ,Length(email)
from eemployees
where email like '%e%'
order by length(email) desc ,department_id asc;
常见函数
概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1,隐藏了实现细节 2,提高代码的重用性
调用:select 函数名(实参列表) 【from表】;
特点:
1.叫什么
2.干什么(函数功能)
分类:
单行函数
字符函数 数学函数 日期函数 (concat length ifnull)
分组函数
功能:做统计使用,又称为统计函数,聚合函数,组合函数
1- 字符函数
1).length(‘join’)
length(‘张三丰’); # utf8 一个函数占三个字节
2).concat 拼接字符串
select conccat(last_name,’_’,first_name) 姓名 form employees;
3).upper.lower
select upper(‘join’);
select lower(‘join’);
select concat(uppder(last_name),lower(first_name)) 姓名 from employees;
4)substr substring
#截取从指定索引处开始的所有字符 从4开始截取后面的 索引从1开始
select substr("aaadfdfdf",4) 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
#返回子串在 原始字符串中的起始索引 #4
select instr('杨不悔爱上了' ,'爱上') as out_put;
#6.trim #去除空格 去除前后的 LTRIM RTRIM
select length(trim(' 张翠山 ')) as out_put;
select TRIM('a' from 'aaaaaaa扎根aaaa啊') as out_put;
#7.lpad 用指定的字符实现左填充 rpad
select lpad('殷素素',2,'**') as out_put;
#8.replace 替换
SELECT REPLACE('周至柔爱上了宜速速速','速','无') AS out_put;
2.数学函数
#1.round四舍五入
select round(1.45);
select round(-1.567,2);
#ceil 向上取整 ,返回该参数的最小整数 大
select ceil(1.00)
#floor 向下取整数,返回该参数的最大整数 取小的
#trucate 截断指定索引的的后面
select truncate (1.6999,1)
#mod 取余 a-a/b*b
select mod(-10,-3);
日期函数:
select now(); 当前日期
select currenttime(); 返回当前时间
可以获取指定的部分 年 月 日
select year(now()) 年;
select MONTHNEAM(NOW()) 月; 取出英文月份
#str_to_date : 将字符通过指定的格式转换为日期
select * from employees where hiredata =str_to_date('4-3 1992' ,'%c-%d %Y' );
#date_format 将日期转换为字符串
流程控制函数
#case 要判断的 表达式
when 常量1 then 要显示的值1或者语句 ;
when 常量2 then 要显示的值2或语句 ;
else 要显示的值能或者语句
end
select salry 原始工资 ,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;
存储过程 :
定义:一组预先编译好的sql语句的集合,理解成批处理语句
create procedure 存储过程名(参数列表)
begin
存储过程体
end
注意:
1.参数列表包含三部分
参数模式 参数名 参数类型
举例:
in stuname varchar(20)
参数模式:
in :该参数可以作为输入
out :该参数可以作为输出
inout: 既可以传入值又可以传出
2.如果存储过程仅仅只有一句,begin end 可以省略
存储过程中的每条sql语句的结尾要求必须加分号
存储过程的结尾可以使用delimiter 重新设置
语法:
delimiter 结束的标记
案例:
delimiter $;
调用语法
call 存储过程名(实例列表);
1.空参列表
#案例1:插入到admin表中五条记录
#delimiter $
create procedure myp1()
begin
insert into admin(username,`password`)
values(`join1`,`231`),
values(`jore1`,`231`),
values(`rose`,`231`);
end $
调用:call lmyp1()$
案例2 创建带in模式参数的存储过程
1.创建存储过程实现 根据女神名,查询对应的男神信息
create procedure myp2(in beautyName varchar(20))
begin
select bo.*
from boys bo
right join beaury b on bo.id=b.biyfriend_id
WHERE B.NAME=beautyName;
End $
调用: call myp2(‘周冬雨’)
2.创建存储过程实现,用户是否登录成功
create procedure myp3(in username varchar(20),in PASSWORD VARCHAR(20))
BEGIN
declear result varchar(20) default ''; #声明并初始化
select count(*) into result
from admin
where admin.username=username
and password = password;
select result; 使用
END $
call myp3(‘张飞’,‘8888’);
创建带out模式的存储过程
案例1, 根据女神名,返回对应的男神名
create procedure myp5(IN beautyName varchar(20),out boyname varchar(20))
begin
selecy bo.boyname
from boys bo
inner join beauty b on bo.id =b.boyfriend_id
where b.name=beautyName;
end $
调用
call myp5(‘兄按照’ @bName)
select @bName$
根据女神名,返回对应的男神名和男神魅力值
create procedure myp5(in beautyName varchar(20), out boyName varchar(20),out userCp Int)
BEGIN
select bo.boyname,bo.userCp into boyName,userCp
from boys bo
inner join beauty b on bo.id =b.boyfriend_id
where b.name=beautyName;
END $
#调用 myp6(‘小号’,@bName ,@userCp)$
带inout 模式参数的存储过程
案例1: 传入a和b两个值,最终a和b都翻倍并返回
create procedure myp8(inout a int ,intout b int)
begin
set a=a*2; #局部变量不用@
set b=b*2;
end$
调用
set @a=10;
set @b=20;
call myp8(@a,@b)
select @a,@b;
#1.创建存储过程实现传入用户名和密码,插入到admin表中
create procedure test_pro1(IN username varchar(20),In loginPwd varchar(20))
begin
insert into admin(username ,password)
values(username,loginpwd);
end $
调用 call test_pro1(‘admin’,‘0000’)$
#2创建存储过程实现传入女神编号,返回女神名称和女神电话
create procedure test_pro2(in id int ,out name varchar(20),out phone varchar(20))
begin
select b.name,b.phone into name,phone
from beauty b
where b.id =id;
end $
call test_pro2(1,@n,@p)
select @n,@p;
#3创建存储过程或函数实现传入两个女神生日,返回大小
create procedure test_pro3 (in birth1 datatime,in birth2 datetime ,out result int)
begin
#datediff (1,2) 1>2输出一个大于0的数
select datediff(birth1,birth2) into result;
end $
调用: call test_pro3(‘1998-1-1’,now(),@result) $
select @result $
#4,创建存储过程或函数传入一个日期,格式化为xx年xx月xx日并返回
create procedure test_pro4(in mydata datetime ,out str data varchar(50))
begin
select date_formate(mydate,’%y年%m月%d日’) into strdata;
end $
调用: call test_pro4(now(),@str)$
select @str $
#5.创建存储过程或函数实现传入女神名称,返回: 女神 and 男神 格式的字符串
如传入: 小昭
返回:小昭 and 张无忌
create procedure test_pro5(in beaytyname varchar(20) ,out str varchar(50))
begin
select concat(beauty ,'and' ,ifnull(boyname,'null')) into str
from boys bo
right join beauty b on b.boyfriend_id = bo.id
where b.name = beautyName;
end $
调用:call test_pro5(‘小昭’ ,@str) $
select @str $
#5.创建存储过程或者函数,根据传入的条目数和索引数和起始索引,查询beauty表的记录
create procedure test_pro6( in startIndex int, in size int)
begin
select * from beauty limit stratIndex,size;
end $
call test_pro6(3,5) $
删除存储过程
drop procedure 存储过程名;
drop procedure test_pro1;
查看存储过程
show create procedure myp2;