欢迎您的光临,本博所发布之文章皆为作者亲测通过,如有错误,欢迎通过各种方式指正。

文摘  MySQL数据库高级用法(二):连接查询、子查询、函数、用户管理、读写分离

MySQL 本站 1129 0评论

六、连接查询


这个时候就出现了笛卡尔积懵了吧,我自己都懵了。上张图来理解一下吧。 

11.jpg

就是多表的数据查询,就会产生笛卡尔积,产生出很多我们不需要的数据,所以我们就要避免笛卡儿积的产生,需要加限定条件,条件最少是表的数量-1。一般是主外键。


1.内链接和外连接和自连接


先把图给大家上了,然后我再解释一下,然后再敲个简单的代码给大家看看。 

22.jpg

下面给大家写了一个简单的内连接查询(两个表inner join ….on…)但是我好像犯了一个错误,inner join 后面的条件应不是where应该用on,虽然也运行出来了结果,这只能说明mysql数据库的灵活,所以还是按照规范来写吧。

33.jpg


2.外连接


44.jpg

最复杂的就是外连接了,需要区分主表和次表,这个关系到你的运行结果

55.jpg


看运行结果最后又一行为null,这就是主表的作用,保留主表的数据,次表没有对应的就为null


3.自连接


66.jpg

这个图和内链接的那个代码几乎一样,只不过没有使用内链接

77.jpg

这个链接有点不会整了,简单的就是这样,复杂的不好说,就这样吧。。。。


七、子查询


什么是子查询,就是将第一查询的加过再作为一个表或者一个结果再进行查询,支持嵌套使用 

88.jpg

这样就完成了一个子查询,将第一次查询的结果,作为一个结果下一次使用。 

这只是一个简单了例子,大家要掌握这种查询方式。


八、内置函数


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

赞 (4) or 分享 ()
游客 发表我的评论   换个身份
取消评论

表情
(0)个小伙伴在吐槽