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关键字来完成连表。
现关系表如下(仅仅参考表数据结构及关系,不考虑表中内容):
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>=B.cc ORDER BY course_id ASC;
该题目的思路是,通过先查询出各科第三名的分数,之后连表后,通过成绩与第三名成绩的比较来进行筛选。
临时表
通过()包住筛选结果并给临时表通过as新命名。
select sid from (select * from score where num&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里的操作是一抹一样的!