六、连接查询
这个时候就出现了笛卡尔积懵了吧,我自己都懵了。上张图来理解一下吧。
就是多表的数据查询,就会产生笛卡尔积,产生出很多我们不需要的数据,所以我们就要避免笛卡儿积的产生,需要加限定条件,条件最少是表的数量-1。一般是主外键。
1.内链接和外连接和自连接
先把图给大家上了,然后我再解释一下,然后再敲个简单的代码给大家看看。
下面给大家写了一个简单的内连接查询(两个表inner join ….on…)但是我好像犯了一个错误,inner join 后面的条件应不是where应该用on,虽然也运行出来了结果,这只能说明mysql数据库的灵活,所以还是按照规范来写吧。
2.外连接
最复杂的就是外连接了,需要区分主表和次表,这个关系到你的运行结果
看运行结果最后又一行为null,这就是主表的作用,保留主表的数据,次表没有对应的就为null
3.自连接
这个图和内链接的那个代码几乎一样,只不过没有使用内链接
这个链接有点不会整了,简单的就是这样,复杂的不好说,就这样吧。。。。
七、子查询
什么是子查询,就是将第一查询的加过再作为一个表或者一个结果再进行查询,支持嵌套使用
这样就完成了一个子查询,将第一次查询的结果,作为一个结果下一次使用。
这只是一个简单了例子,大家要掌握这种查询方式。
八、内置函数
mysql里面也有很多内置函数,我们应该了解,因为以后我们使用python代码也可以满足这些需求。
1)数字类
语句 | 含义 |
---|---|
select rand() from dual; | 随机数 |
select * from stuinfo order by rand(); | 随机排序 |
select round(5.6); | 四舍五入 |
select ceil(5.3); | 向上取整 |
select floor(5.6); | 向下取整 |
2)大小写转换
语句 | 含义 |
---|---|
select ucase(‘i am lyb’); | 大写 |
select lcase(‘I AM LYB’); | 小写 |
3)截取字符串
语句 | 含义 |
---|---|
select left(‘abcdefg’,3); | 截取左边的3位 |
select right(‘abcdefg’,3); | 截取右边3位 |
select substring(‘abcdefg’,2,3); | 从第2位开始取3个字符,起始位置从1开始 |
4)字符串拼接
select concat(sid,sname,age,sex,city) from stuinfo;
mysql> select concat(sid,sname,age,sex,city) from stuinfo;
+--------------------------------+
| concat(sid,sname,age,sex,city) |
+--------------------------------+
| 7小明18male上海 |
| 8小刚20male北京 |
| 9小强22male重庆 |
| 10小力23male天津 |
| 11小丽21female北京 |
| 12小月20female天津 |
| 13小yb18male重庆 |
| 17百强18male黑龙江 |
| 18百强118male黑龙江 |
| 19百强218male黑龙江 |
+--------------------------------+
5)coalesce(str1,str2):如果str1不为null则显示str1,否则显示str2
select sid,sname,coalesce(ch,'缺考'),coalesce(math,'缺考') from stuinfo left join stumarks using(sid);
mysql> select sid,sname,coalesce(ch,'缺考'),coalesce(math,'缺考') from stuinfo left join stumarks using(sid);
+-----+-------+---------------------+-----------------------+
| sid | sname | coalesce(ch,'缺考') | coalesce(math,'缺考') |
+-----+-------+---------------------+-----------------------+
| 11 | 小丽 | 100 | 80 |
| 8 | 小刚 | 60 | 98 |
| 10 | 小力 | 50 | 51 |
| 9 | 小强 | 67 | 88 |
| 7 | 小明 | 88 | 10 |
| 12 | 小月 | 96 | 97 |
| 17 | 百强 | 缺考 | 缺考 |
| 18 | 百强1 | 缺考 | 缺考 |
| 19 | 百强2 | 缺考 | 缺考 |
+-----+-------+---------------------+-----------------------+
6)length(字节长度)、char_length(字符长度)、trim(去两边空格)、repace(替换)
select length('千锋');
select char_length('千锋');
select length(trim(' 千锋 '));
select replace('pgone','one','two');
7)时间戳
select unix_timestamp();
8)将时间戳转成当前时间
select from_unixtime(unix_timestamp());
9)获取当前时间
select now(),year(now()),month(now()),day(now()),hour(now()), minute(now()),second(now())\G
#现在时间,年,月,日,时,分,秒
10)dayname(),monthname(),dayofyear()
select dayname(now()) as `星期`,monthname(now()) as `月份`,dayofyear(now()) as `本年第几天`;
11)datediff(结束日期,开始日期)
例题计算自己活了多少天
select datediff(now(),'1970-1-1');
12)md5():md5加密
select md5('@123456.');
1.字符串函数
sql里函数都是有返回值的
查看字符的ascii码值ascii(str),str是空串时返回0
select ascii(‘a’);
查看ascii码值对应的字符char(数字)
select char(97);
拼接字符串concat(str1,str2…)
select concat(12,34,’ab’);
包含字符个数length(str)
select length(‘abc’);
截取字符串
left(str,len)返回字符串str的左端len个字符
right(str,len)返回字符串str的右端len个字符
substring(str,pos,len)返回字符串str的位置pos起len个字符
select substring(‘abc123’,2,3);
去除空格
ltrim(str)返回删除了左空格的字符串str
rtrim(str)返回删除了右空格的字符串str
trim([方向 remstr from str)返回从某侧删除remstr后的字符串str,方向词包括both、leading、trailing,表示两侧、左、右
select trim(’ bar ‘);
select trim(leading ‘x’ FROM ‘xxxbarxxx’);
select trim(both ‘x’ FROM ‘xxxbarxxx’);
select trim(trailing ‘x’ FROM ‘xxxbarxxx’);
返回由n个空格字符组成的一个字符串space(n)
select space(10);
替换字符串replace(str,from_str,to_str)
select replace(‘abc123’,’123’,’def’);
大小写转换,函数如下
lower(str)
upper(str)
2.数学函数
求绝对值abs(n)
select abs(-32);
求m除以n的余数mod(m,n),同运算符%
select mod(10,3);
select 10%3;
地板floor(n),表示不大于n的最大整数
select floor(2.3);
天花板ceiling(n),表示不小于n的最大整数
select ceiling(2.3);
求四舍五入值round(n,d),n表示原数,d表示小数位置,默认为0
select round(1.6);
求x的y次幂pow(x,y)
select pow(2,3);
获取圆周率PI()
select PI();
随机数rand(),值为0-1.0的浮点数
select rand();
还有其它很多三角函数,使用时可以查询文档
3.时间函数
获取子值,语法如下
year(date)返回date的年份(范围在1000到9999)
month(date)返回date中的月份数值
day(date)返回date中的日期数值
hour(time)返回time的小时数(范围是0到23)
minute(time)返回time的分钟数(范围是0到59)
second(time)返回time的秒数(范围是0到59)
select year(‘2016-12-21’);
日期计算,使用+-运算符,数字后面的关键字为year、month、day、hour、minute、second
select ‘2016-12-21’+interval 1 day;
日期格式化date_format(date,format),format参数可用的值如下
* 获取年%Y,返回4位的整数
* 获取年%y,返回2位的整数
* 获取月%m,值为1-12的整数
* 获取日%d,返回整数
* 获取时%H,值为0-23的整数
* 获取时%h,值为1-12的整数
* 获取分%i,值为0-59的整数
* 获取秒%s,值为0-59的整数
/日期–>字符串/
select date_format(‘2017-10-20’,’%Y年%m月%d日’)
/字符串–>日期/
select str_to_date(‘2017年10月20日’,’%Y年%m月%d日’)
当前日期current_date()
select current_date();
当前时间current_time()
select current_time();
当前日期时间now()
select now();
4.自定义函数
#语法:
Create function 函数名(形参) returns 返回的数据类型
begin
//函数体
end
#第一步
delimiter //
#不带参数的函数
create function myfun() returns varchar(32)
begin
return '千锋python';
end //
#调用函数
select myfun()//
#带参数
create function myfun_1(num1 int,num2 int) returns int
begin
declare num int default 0;
set num=num1+num2;
return num;
end //
select myfun_1(100,200)//
#删除函数
drop function myfun_1//
九、SQL编程(了解)
1) if-elseif-else语句
#语法:
if 条件 then
//代码1
elseif 条件 then
//代码2
else
//代码3
end if;
create procedure pro_8(in grade int)
begin
if grade=1 then
select '金牌会员' as '等级';
elseif grade=2 then
select '普通会员' as '等级';
else
select '游客' as '等级';
end if;
end //
#调用
call pro_8(3)//
2) case-when语句
create procedure pro_9(in num int)
begin
case num
when 1 then select '杀马特' as '气质';
when 2 then select '屌丝' as '气质';
when 3 then select '正常人' as '气质';
when 4 then select '贵族' as '气质';
else select '输入不正确' as '气质';
end case;
end //
call pro_9(0)//
#显示学员的学号、姓名、性别、语文成绩、等级
select sid,sname,sex,ch,case
when ch>=90 then '等级A'
when ch>=80 then '等级B'
when ch>=70 then '等级C'
when ch>=60 then '等级D'
else '等级E'
end as '等级' from stuinfo left join stumarks using(sid)//
select sid,sname,sex,ch from stuinfo left join stumarks using(sid)//
3)loop循环
loop遇到leave退出
create procedure proc(in num int)
begin
declare total int default 0;
declare i int default 0;
sign:loop
set total=total+i;
set i=i+1;
if i>=num then
leave sign;# leave=break
end if;
end loop;
select total from dual;
end //
call proc(100)//
#如果没有设置标签名,leave loop
#sign是循环名,用于结束循环,可以自己随意取名字
4)while循环
#语法:
while 条件 do
//代码
end while
create procedure pro_11(in num int)
begin
declare total int default 0;
declare i int default 0;
while num>=i do
set total=total+i;
set i=i+1;
end while;
select total from dual;
end //
call pro_11(100)//
5)repeat循环
#语法
repeat
代码
until 条件 -- 直重复到条件为true才结束
end repeat
create procedure pro_12(in num int)
begin
declare total int default 0;
declare i int default 0;
repeat
set total=total+i;
set i=i+1;
until i>num
end repeat;
select total from dual;
end //
call pro_12(100)//
6)leave和iterate
leave类似于break,iterate类似于continue
create procedure pro_13()
begin
declare i int default 0;
sign:while i<5 do
set i=i+1;
if(i=3) then
leave sign; -- 类似于break
#iterate sign; -- 类似于continue
end if;
select i from dual;
end while;
end //
call pro_13()//
十、用户管理
mysqld --skip--grant--tables
#(5.5最好用)
#--skip--grant--tables 跳过登陆验证(MYSQL服务器开起中)
1)创建用户
语法:create user ‘用户名’@’允许登录的主机地址’ identified by 密码
#%代表数据库的库名
create user 'ruidong'@'%' identified by '123456';
2)删除用户
语法:drop user 用户
drop user ruidong;
3)增加用户权限
#将python的所有表的select权限付给ruidong用户
grant select on python.* to 'ruidong'@'%';
#将所有数据库中所有表的所有权限付给ruidong用户
grant all privileges on *.* to 'ruidong'@'%';
#创建用户并授权
grant all privileges on *.* to 'hal'@'%' identified by '123456' with grant option;
#创建好用户以后,刷新mysql用户权限表
flush privileges ;(linux ,mac)
revoke select on python.* from 'ruidong'@'%'; #删除select权限
revoke all privileges on *.* from 'ruidong'@'%'; #删除所有权限
4)mysql57忘记密码
1、首先停止mysql服务进程:
service mysqld stop
2.#然后编辑mysql的配置文件my.cnf(如果是windows的话找到my.ini)
vim /etc/my.cnf
3.#找到 [mysqld]这个模块:
#在最后面添加一段代码
skip-grant-tables ##忽略mysql权限问题,直接登录
#然后保存 :wq!退出
#启动mysql服务:
service mysqld start
#直接进入mysql数据库:
mysql
#选择mysql数据库
use mysql;
#对user表的root用户进行密码修改
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
#特别提醒注意的一点是,新版的mysql数据库下的user表中已经没有Password字段了
#而是将加密后的用户密码存储于authentication_string字段
#执行刷新
flush privileges;
#exit退出mysql
exit;
#启动服务
service mysqld start
参考网址:
https://blog.csdn.net/beichen0518/article/details/80820335
http://www.cnblogs.com/Aiapple/p/5683030.html
https://www.cnblogs.com/luckcs/articles/2543607.html
https://www.jb51.net/article/119161.htm
转载请注明: ITTXX.CN--分享互联网 » MySQL数据库高级用法(二):连接查询、子查询、函数、用户管理、读写分离
最后更新:2019-04-22 09:54:44