MySQL高级1.mysql高级3.内置函数4.时间与字符串的相互转换

1.mysql高级

1.关系

创建学生成绩关系表

drop table if exists scores;
drop table if exists students;
drop table if exists subjects;

create table students(
    id int auto_increment primary key,
    sname varchar(20)
);

create table subjects(
    id int auto_increment primary key,
    stitle varchar(20)
);

/*
create table scores(
    id int auto_increment primary key,
    stuid int,
    subid int,
    score decimal(5,2),
    foreign key(stuid) references students(id),   #引用的第一种写法
    foreign key(subid) references subjects(id)
);
*/
create table scores(
    id int auto_increment primary key,
    stuid int,
    subid int,
    score decimal(5,2)
);

#引用的第二种写法
alter table scores
add constraint fk_scores_stuid foreign key(stuid)
references students(id) on delete cascade;  #外键的级联操作

alter table scores
add constraint fk_scores_subid foreign key(subid)
references subjects(id) on delete cascade;


insert into students(sname) values('小茗');
insert into students(sname) values('可乐');
insert into students(sname) values('小伊');
insert into subjects(stitle) values('语文');
insert into subjects(stitle) values('数学');
insert into subjects(stitle) values('英语');

select * from students;
select * from subjects;
select * from scores;

insert into scores(stuid,subid,score) values(1,1,88);
insert into scores(stuid,subid,score) values(1,2,90);
insert into scores(stuid,subid,score) values(2,3,99);
insert into scores(stuid,subid,score) values(2,1,70);

-- 查询一个学生各科的成绩
select t1.id,t1.sname,t2.score
from students t1,scores t2
where t1.id = t2.stuid;

三张表如图

查询一个学生各科成绩的结果

级联操作的类型包括:

  • restrict(限制):默认值,抛异常
  • cascade(级联):如果主表的记录删掉,则从表中相关联的记录都将被删除
  • set null:将外键设置为空
  • no action:什么都不做

2.连接查询

  • 当需要对有关系的多张表进行查询时,需要使用连接 join

连接查询分类如下:

  • 表A inner join 表B:表A与表B匹配的行会出现在结果中
  • 表A left join 表B:表A与表B匹配的行会出现在结果中,外加表A中独有的数据,未对应的数据使用null填充
  • 表A right join 表B:表A与表B匹配的行会出现在结果中,外加表B中独有的数据,未对应的数据使用null填充
  • 在查询或条件中推荐使用“表名.列名”的语法
  • 如果多个表中列名不重复可以省略“表名.”部分
  • 如果表的名称太长,可以在表名后面使用' as 简写名'或' 简写名',为表起个临时的简写名称

实例一

-- 先建立关系表
drop table if exists x_emp;
drop table if exists x_dept;
create table x_dept(
     id int auto_increment primary key,
     dname varchar(20)
);
create table x_emp(
     id int auto_increment primary key,
     ename varchar(20),
     did int not null,
     mgr int, 
     foreign key(did) references x_dept(id),
     foreign key(mgr) references x_emp(id)
);
insert into x_dept(dname) values('研发部');
insert into x_dept(dname) values('人事部');
insert into x_dept(dname) values('财务部');

insert into x_emp(ename,did,mgr) values('老王',1,null);
insert into x_emp(ename,did,mgr) values('老张',1,1);
insert into x_emp(ename,did,mgr) values('老赵',1,1);
insert into x_emp(ename,did,mgr) values('小茗',2,3);
insert into x_emp(ename,did,mgr) values('小伊',2,3);

select * from x_dept;
select * from x_emp;

创建的两个表

/*查询员工的编号,姓名,上级的姓名*/
select * from x_emp 员工表,x_emp 上级表
where 员工表.mgr = 上级表.id;

-- 第一种写法
select t1.id,t1.ename,t2.ename
from x_emp t1,x_emp t2
where t1.mgr = t2.id;

-- 第二种写法
select t1.id,t1.ename,t2.ename
from x_emp t1
inner join x_emp t2
on t1.mgr = t2.id;

查询员工额编号,姓名,上级的姓名

-- 'left',表x_emp与表x_dept匹配的行会出现在结果中,外加表x_emp中独有的数据,未对应的数据使用null填充
select * 
from x_emp left outer join x_dept
on x_emp.did = x_dept.id;

left

-- 'right',表x_emp与表x_dept匹配的行会出现在结果中,外加表x_dept中独有的数据,未对应的数据使用null填充
select * 
from x_emp right outer join x_dept
on x_emp.did = x_dept.id;

right

/*x_emp中,查询是经理的人的名字
    1、哪些是经理啊?
    2、再找名字
*/
select ename 
from x_emp 
where id in(
    select mgr from x_emp
);

查询哪些是经理

实例二

drop table if exists scores;
drop table if exists students;
drop table if exists subjects;

create table students(
    id int auto_increment primary key,
    sname varchar(20)
);

create table subjects(
    id int auto_increment primary key,
    stitle varchar(20)
);


create table scores(
    id int auto_increment primary key,
    stuid int,
    subid int,
    score decimal(5,2),
    foreign key(stuid) references students(id),
    foreign key(subid) references subjects(id)
);

insert into students(sname) values('小茗');
insert into students(sname) values('可乐');
insert into students(sname) values('小伊');
insert into subjects(stitle) values('语文');
insert into subjects(stitle) values('数学');
insert into subjects(stitle) values('英语');

insert into scores(stuid,subid,score) values(1,1,88);
insert into scores(stuid,subid,score) values(1,2,90);
insert into scores(stuid,subid,score) values(2,3,99);
insert into scores(stuid,subid,score) values(3,1,70);

select * from students;
select * from subjects;
select * from scores;

创建的三个表

/*查询学生的编号,姓名,科目的名称,成绩*/
-- 三种方法都可以

select t1.id,t1.sname,t2.stitle,t3.score
from students t1,subjects t2,scores t3
where t3.stuid = t1.id and t3.subid = t2.id;

select t1.id,t1.sname,t2.stitle,t3.score
from scores t3
inner join students t1
on t3.stuid = t1.id 
inner join subjects t2
on t3.subid = t2.id;


select t1.id,t1.sname,t2.stitle,t3.score
from  students t1
inner join scores t3
on t3.stuid = t1.id 
inner join subjects t2
on t3.subid = t2.id;

查询学生的编号,姓名,科目的名称,成绩

实例三

drop table if exists emp1;
drop table if exists salgrade;
create table emp1(
     id int auto_increment primary key,
   name VARCHAR(20),
     sal int
);
create table salgrade(
     id int auto_increment primary key,
     low int,
     high int
);
insert into emp1(name,sal) values('小茗',4000);
insert into emp1(name,sal) values('可乐',5000);
insert into emp1(name,sal) values('小伊',8800);

insert into salgrade(low,high) values(1000,3999);
insert into salgrade(low,high) values(4000,7999);
insert into salgrade(low,high) values(8000,10000);

select * from emp1;
select * from salgrade;

创建的两个表

-- 查询员工的编号,工资和等级
select t1.id,t1.name,t1.sal,t2.id
from emp1 t1 inner join salgrade t2
on t1.sal between t2.low and t2.high;

查询员工的编号,工资和等级

create view myView2
as
select * from emp1;

delete from emp1;
select * from myView2;

delete from emp1后,表emp1数据为空

实例四

/*查询各学生的语文、数学、英语的成绩*/
select sname,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='语文' and stuid=stu.id) as 语文,
(select sco.score from  scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='数学' and stuid=stu.id) as 数学,
(select sco.score from  scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='英语' and stuid=stu.id) as 英语
from students stu;

-- 这里我们可以用视图来查询,视图的用途就是查询

/*视图*/
create view myView 
as
select sname,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='语文' and stuid=stu.id) as 语文,
(select sco.score from  scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='数学' and stuid=stu.id) as 数学,
(select sco.score from  scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='英语' and stuid=stu.id) as 英语
from students stu;


select * from myView;
select sname from myView;

查询各学生的语文、数学、英语的成绩

用视图以后查询的结果如图

事务

  • 当一个业务逻辑需要多个sql完成时,如果其中某条sql语句出错,则希望整个操作都退回,保证数据的正确性。
  • 使用事务可以完成退回的功能,保证业务逻辑的正确性
  • 两个功能:有一个出问题,回滚。都没有问题,提交。(同生共死)
事务四大特性(简称ACID)
  • 原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行
  • 一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致
  • 隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的
  • 持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障

要求:表的类型必须是innodb或bdb类型(表的默认类型就是innodb),才可以对此表使用事务

查看表的创建语句 show create table students;

修改表的类型 alter table '表名' engine=innodb;

事务语句

  • 开启begin;
  • 提交commit;
  • 回滚rollback;

示例

drop table if exists bank;
create table bank(
    id int primary key,
    name varchar(20),
    money int 
);
insert into bank values(1,'王明哲',100);
insert into bank values(2,'张妙涵',1);
select * from bank;
update bank set money = money-10 where id=1;
update bank set money = money+10 where id=2;
select * from bank;
-- 查看建表语句
show create table students;

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
-- 从建表语句中我们可以看出默认的是InnoDB类型
/*事务,一般用来做测试*/
begin;
delete from bank; 
rollback;
commit;

用事务来进行测试

3.内置函数

1.字符串函数

/*字符串函数,sql里函数都是有返回值的*/
-- 查看字符的ascii码值ascii(str),str是空串时返回0
select ascii('a');
97

-- 查看ascii码值对应的字符char(数字)
select char(97);
a

-- 拼接字符串concat(str1,str2...)
select concat(12,34,'ab','小茗');
1234ab小茗

-- 包含字符个数length(str)
select length('中a')
4
select length(ename),ename from x_emp;


/*截取字符串
left(str,len)返回字符串str的左端len个字符
right(str,len)返回字符串str的右端len个字符
substring(str,pos,len)返回字符串str的位置pos起len个字符*/

select substring('abc123',2,3);
bc1


/*去除空格
ltrim(str)返回删除了左空格的字符串str
rtrim(str)返回删除了右空格的字符串str
trim([方向 remstr from str)返回从某侧删除remstr后的字符串str,方向词包括both、leading、trailing,表示两侧、左、右*/
select trim('  bar   ');
bar

select trim(leading 'x' FROM 'xxxbarxxx');
barxxx

select trim(both 'x' FROM 'xxxbarxxx');
bar

select trim(trailing 'x' FROM 'xxxbarxxx');
xxxbar

--返回由n个空格字符组成的一个字符串space(n)
select space(10);

-- 替换字符串replace(str,from_str,to_str)
select replace('abc123','123','def');

-- 大小写转换,函数如下
lower(str)
upper(str)
select lower('aBcD');

2.数学函数

-- 求绝对值abs(n)
select abs(-32);
32

-- 求m除以n的余数mod(m,n),同运算符%
select mod(10,3);
1
select 10%3;
1

-- 地板floor(n),表示不大于n的最大整数
select floor(2.3);
2

-- 天花板ceiling(n),表示不小于n的最大整数
select ceiling(2.3);
3

-- 求四舍五入值round(n,d),n表示原数,d表示小数位置,默认为0
select round(1.6);
2

-- 求x的y次幂pow(x,y)
select pow(2,3);
8

-- 获取圆周率PI()
select PI();
3.141593

-- 随机数rand(),值为0-1.0的浮点数
select rand();
0.7000933091157053

select floor(2.3),ceil(2.3),round(2.3456,2);
2          3          2.35

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)
  • 日期计算,使用+-运算符,数字后面的关键字为year、month、day、 hour、minute、second
  • 日期格式化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日')
2017年10月20日

/*字符串-->日期*/
select str_to_date('2017年10月20日','%Y年%m月%d日')
2017-10-20

-- 当前日期current_date()
select current_date();
2017-06-23

-- 当前时间current_time()
select current_time();
21:21:55

-- 当前日期时间now()
select now();
2017-06-23 21:21:41
create table t1(
    id int,
    birthday date
);
insert into t1 values(1,'2017-10-20');
insert into t1 values(2,'2017/10/20');
insert into t1 values(3,str_to_date('2017年10月21日','%Y年%m月%d日'));
insert into t1 values('4','2017/10/20');
select * from t1;

/*日期-->字符串*/
select date_format('2017-10-20','%Y年%m月%d日')
/*字符串-->日期*/
select str_to_date('2017年10月20日','%Y年%m月%d日')

select current_date(),now();

4.时间与字符串的相互转换

import time
import  datetime

stime1 = '2015年10月20日'
#字符串--->时间time.struct_time
time1 = time.strptime(stime1,'%Y年%m月%d日')
print(time1)
结果:
time.struct_time(tm_year=2015, tm_mon=10, tm_mday=20, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=1, tm_yday=293, tm_isdst=-1)


#时间time.struct_time--->字符串
stime1 = time.strftime('%Y-%m-%d',time1)
print(stime1)
结果:
2015-10-20
dt = datetime.datetime.now()
print(dt)
结果:
2017-06-24 14:59:09.948008
dt = datetime.date(2017,6,24)
print(str(dt))
print(type(dt))
结果:
2017-06-24
<class 'datetime.date'>


dt = datetime.datetime(2017,6,24,14,23,45)
print(dt)
结果:
2017-06-24 14:23:45

dt = datetime.date(2017,6,24)
print(dt.timetuple())
结果:
time.struct_time(tm_year=2017, tm_mon=6, tm_mday=24, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=5, tm_yday=175, tm_isdst=-1)

dt = datetime.date(2017,6,24)
s = time.strftime('%Y/%m/%d',dt.timetuple())
print(s)
结果:
2017/06/24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏idba

order by 原理以及优化

一 简介 偏向于业务的(MySQL)DBA或者业务的开发者来说,order by 排序是一个常见的业务功能,将结果根据指定的字段排序,满足前端展示的需求。然而...

14130
来自专栏技术专栏

hive 中 统计某字段json数组中每个value出现的次数

需要将json数组里的qd_title都提取出来转换成hive中的array数组。下面介绍两种方法

1.1K30
来自专栏Python爬虫实战

MySQL 从零开始:07 数据搜索与搜索

数据库表中包含了很多数据,一般我们不会检索表中的所有行。通常会根据特定的条件来提取出表的子集,此时我们需要指定搜索条件(search criteria),搜索条...

12220
来自专栏机器学习入门

LWC 56:718. Maximum Length of Repeated Subarray

LWC 56:718. Maximum Length of Repeated Subarray 传送门:718. Maximum Length of Repea...

22260
来自专栏GreenLeaves

SQL练习之求解填字游戏

SELECT * FROM dbo.spt_values ? 假设我们需要找具有以下特点的单词: (1)第二个字母:a (2)第四个字母:l (3)一共只有5个...

200100
来自专栏pangguoming

sharding-jdbc之——分库分表实例

转载请注明出处:http://blog.csdn.net/l1028386804/article/details/79368021

39110
来自专栏java一日一条

MySql数据库索引原理

第二部分结合MySQL数据库中InnoDB数据存储引擎中索引的架构实现讨论聚集索引、非聚集索引及覆盖索引等话题。

28830
来自专栏数据和云

DB2 Vs MySQL系列 | MySQL与DB2的数据类型对比

随着MySQL数据库的应用越来越广泛,DB2向MySQL数据库的迁移需求也越来越多。进行数据库之间迁移的时候,首先遇到的并且也是最基本最重要的就是两种数据库数据...

39160
来自专栏Java呓语

第9章、语言结构

字符串是包含在单引号(')或双引号(")字符中的字节或字符序列。 以下几行例子是等同的:

9830
来自专栏程序猿DD

10分钟让你明白MySQL是如何利用索引的

一、前言 在MySQL中进行SQL优化的时候,经常会在一些情况下,对MySQL能否利用索引有一些迷惑。 譬如: MySQL 在遇到范围查询条件的时候就停止匹配了...

36690

扫码关注云+社区

领取腾讯云代金券