MySQL进阶操作知识

编程学习

MySQL的基本操作主要是数据库的创建及部分简单的数据表行操作等，通过对MySQL的进阶学习，可以按照要求搜索出条件更复杂的结果，可以在讲几个表格进行连接后再次搜索。

mysql

0x00 数据表结构

一个数据表是可以没有主键的，但是不推荐这样，因为主键primary key的存在可以加速表格的搜索、让表格有完整约束性（主键必须是唯一的，不可重复不可为空的），最后还有可以被其他表引用成外键，对其他表做外键约束。

一个表格只能有一个主键，但主键可以是多列。如下：

create table Test(
    id int Not NULL auto_increment,
    id1 int Not Null,
    id2 int Not Null,
    primary key(id,id1)
)engine=innodb default charset=utf8;

可以show table table_name \G;命令来查看创建表格时候的语法：

mysql> show create table user \G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(10) NOT NULL,
`passwd` char(18) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

通过desc table_name;来查看表结构。

mysql> desc user;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| passwd | char(18) | NO | | NULL | |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

0x01 外键变种

唯一索引

比如在一个学生信息的用户表中，那么学号都是学生的唯一信息，那么可以设置学号为唯一索引。可以使用unique关键字来定义。

create table student(
    id int auto_increment primary key,
    name char(5),gender char(2),
    student_num int,
    unique unique_student_num (student_num)
)engine=innodb default charset=utf8;

通过unique 唯一索引名称 (列名)来定义唯一索引。

唯一索引的优点为：

  • 加速查找
  • 约束不能重复

外键的表现形式

外键可以分为几种情况：

  • 一对多
  • 一对一
  • 多对多

a.一对多

一对多的情况是属于比较常见的，比如表user中包含用户的username和department(int)，而表department中包含了具体部门。那么一个部门可以包含多个员工，这种情况就是典型的一对多。

create table department(
    id int auto_increment primary key,
    department char(10)
)engine=innodb default charset=utf8;
create table user(
    id int auto_increment primary key,
    name char(10),
    department int,
    constraint fk_department_user foreign key (id) references department(id)
)engine=innodb default charset=utf8;

这样的外键就是典型的一对多，也是比较常见的一种形式。

b.一对一

感觉一对一的情况比较多，比如表picture中包含图片的名字和路径(int)，而表pic_path中包含路跑的真实路径。那么图片和路径是一对一的关系，那么可以使用unique来完成外键。

create table pic_path(
	id int auto_increment primary key,
	path char(50),
	unique uni_path (path)
)engine=innodb default charset=utf8;

create table picture(
	id int auto_increment primary key,
	filename char(10),
	rela_path int,
	unique uni_filename_path (rela_path),
	constraint fk_rela_path foreign key (rela_path) references pic_path(id)
)engine=innodb default charset=utf8;

c.多对多

对于多对多的外键来说，来一般通过建3个表（或多个）来完成，如表user，表computer和表relationship。由于一台电脑可以给几个人使用，一个用户也可以使用多台电脑，那么用户与电脑之间的关系对应就存在relationship表中。

create table user(
	id int auto_increment primary key,
	name char(10)
)engine=innodb default charset=utf8;

create table computer(
	id int auto_increment primary key,
	pc_name char(10)
)engine=innodb default charset=utf8;

create table relationship(id int auto_increment primary key,user_id int,
	pc_id int,
	unique uni_user_pc (user_id,pc_id),
	constraint fk_user_id foreign key (user_id) references user(id),
	constraint fk_pc_id foreign key (pc_id) references computer(id)
)engine=innodb default charset=utf8;

0x02 增删改查进阶操作

a.增

#一次插入多条数据
insert into user(name,age) values('xzy',12),('moe',18);
#将user1中的数据插入user中
insert into user(name,age) select name,age from user1;

b.删、改

相对于增加的操作，删、改主要是定位条件，而条件的东西都写到下面的查询中吧！！！

c.查

现有表teacher：

+-----+------------+
| tid | tname |
+-----+------------+
| 1 | xzy |
| 2 | moe |
+-----+------------+

重命名列名（非永久修改）

可以通过as来实现。

select tid,tname as teachername from teacher;

多加一列表

select tid,tname,1 from teacher;

+—–+————+—+
| tid | tname | 1 |
+—–+————+—+
| 1 | xzy | 1 |
| 2 | moe | 1 |
+—–+————+—+
5 rows in set (0.00 sec)

在查询列时候，写一个任意内容，那么会添加一个内容列，且所有内容均为添加内容，入上面的1，后续操作中可以用来统计。

where操作符

对于查询时候where后面可以通过一些操作符来限制搜索条件：

and，or，>，<，!=，<>，in，not in，between……and……

通配符

sql中有2个通配符，分别是%和_。使用通配符需与like关键字搭配使用。

  • %    通配多个字符
  • _     通配一个字符
select * from teacher where tname like 'x%'；
select * from teacher where tname like 'x_'；

限制

对于较大数据库在搜索出较大数量的数据时候，会对资源进行占用，那么可以只显示其中的前几条，那么就可以使用到limit关键字来进行限制了。

#显示前10条数据
select * from teacher limit 10;
#显示第5条开始的10行数据
select * from teacher limit 5,10;
select * from teacher limit 10 offset 5;

注意offset的写法即可！

排序

排序主要有升序（asc）和降序（desc），比如按分数查询学生。与关键字oder by搭配使用。

select * from student oder by score asc;
select * from student oder by score desc;

分组

分组使用关键字group by来完成，其主要作用是用来去重及统计，常常与一些函数搭配使用。

#统计班里男生/女生的个数
select gender,count(gender) from student group by gender;

这里使用了count()函数来统计个数，与group by搭配使用的函数还有：

  • count()
  • max()
  • min()
  • avg()
  • sum()

对于上面的例子来说，如果只想统计男生的个数，那么不能在分组后的数据使用where条件来筛选，必须使用关键字having。

select gender,count(gender) from student group by gender having gender='男';

特别的：group by 必须在where之后，order by之前。

去重

除了group by可以去重外，还可以通过关键词distinct来去重，不过使用distinct的方法是不推荐的，因为查询效率较低。

select distinct province from student;

通过上面distinct对province列进行去重，就可以看看学生是来自哪些省份了。

连表

连表是很重要的操作，主要有3个方法，left join/right join/inner join搭配on关键字来完成连表。

现关系表如下（仅仅参考表数据结构及关系，不考虑表中内容）：

student-table查看每个课程的授课老师。

select course.cname,teacher.tname from course left join teacher on course.tearch_id=teacher.tid;

连表时候通过在left join左右分别写上表明，用on来搭配两表的关系，从而连接成一张表，在对表进行搜索操作。

A （left join、right join及inner join）B的用法是一致的，只是在结果的表现形式上有区别。

left join：A表显示所有，B表中没有对应关系则显示Null。

right join：B表显示所有，A表中没有对应关系则显示Null。

inner join:无论AB表，只显示不是Null的数据。

例题：

1.查询不同课程但成绩相同的学生的学号、课程号；（笛卡尔卡片机思路）

SELECT s1.student_id,s2.course_id FROM score as s1,score as s2
 WHERE s1.sid!=s2.sid and s1.course_id!=s2.course_id and s1.num=s2.num;

这样就可以选出学生的学号和课程了。主要思路是通过笛卡尔卡片机把数据放到了一行上。

2.查询各科成绩前三名的记录:(考虑成绩并列情况)

SELECT * FROM ( SELECT s1.student_id,s1.course_id,s1.num,(
    SELECT s2.num FROM score as s2 WHERE s2.course_id=s1.course_id GROUP BY s2.num ORDER BY s2.num DESC LIMIT 2,1
) as cc FROM score as s1) as B WHERE B.num&gt;=B.cc ORDER BY course_id ASC;

该题目的思路是，通过先查询出各科第三名的分数，之后连表后，通过成绩与第三名成绩的比较来进行筛选。

临时表

通过()包住筛选结果并给临时表通过as新命名。

select sid from (select * from score where num&amp;gt;60) as B;

条件过滤

1.统计学生的及格率，可以将大于60分的设置为1，小于60分的设置为0，之后通过统计后即可算出及格率。这里可以使用case when 条件 then 真值 else 假值 end来完成。

select course_id,avg(num),sum(case when num <60 THEN 0 ELSE 1 END),sum(1),
sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl 
from score GROUP BY course_id order by AVG(num) asc,jgl desc;

2.统计学生的成绩，如果成绩为Null的话，那么设置为0分。

select id,(if isnull(score.num),0,score.num) from score;

也是通过if来判断，if的使用和Excel里的操作是一抹一样的！

发表评论