Mysql

| | | |

Mysql入门

image-20210108160053822

一：建库建表

首先先建立一个数据库，在该数据库种建立三个表，一个学生表，一个课程表，还有一个学习表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE DATABASES 练习库;
USE 练习库
CREATE TABLE student(
	sid VARCHAR(8) PRIMARY KEY,
	sname VARCHAR(8),
	ssex VARCHAR(2),
	sbirthday INT,
	sorigin VARCHAR(20),
	scollege VARCHAR(15)
);
CREATE TABLE course(
	cid VARCHAR(10) NOT NULL UNIQUE PRIMARY KEY,
	cname VARCHAR(15),
	ctime INT,
	cstartday VARCHAR(15),
	cproperties VARCHAR(15)
);
CREATE TABLE learn(
	sid VARCHAR(8),
	cid VARCHAR(10),
	score INT,
	PRIMARY KEY(sid,cid),
	FOREIGN KEY(sid) REFERENCES student(sid),
	FOREIGN KEY(cid) REFERENCES course(cid)
);

二：基本表的修改和删除

1.修改基本表

例一：将student表中的sbirthday的类型修改为date类型

1
2
3
4
-- 这里使用的是alter table 表名 modify 属性 数据类型 约束条件(比如:主键、唯一、非空、默认等等)
ALTER TABLE student MODIFY sbirthday DATE;
-- desc 表名        查看表结构
DESC student;

例二：将student表中增加年龄”sage”属性,类型为int类型，没有约束条件

1
2
3
-- 语法：alter table 表名 add 属性 数据类型 约束条件
ALTER TABLE student ADD sage INT;
DESC student;

例三：将student表中的sage删除

1
2
3
-- 语法：alter table 表名 drop 属性/约束条件
ALTER TABLE student DROP sage;
DESC student;

2.基本表的删除

例四：将某某表删除（这里就不拿这几个表演示了哈）

1
DROP TABLE 表名;

如果你在这里想要删除student表是不会成功的，你知道为什么吗？

因为student表中的sid作为了learn表中的外键，你要想删除成功，就必须先把learn表给删除掉后再删student表就可以啦。

三：CRUD

1.插入数据

例五：将一个新学生记录插入到学生表中

1
2
3
4
-- 语法：insert into 表名(属性1,属性2....) values (要插入的数据)
INSERT 
INTO student
VALUES ('099999','夏雨','女','2000-10-10','海南','计算机');

insert语法要注意，表名后面跟的属性列可写可不写，但是建议大家写上。若是插入所有的属性可以省略不写，但是values后插入必须要写全部的属性常量，若有的根本就没值，那就写null，但千万不能漏掉任何一个属性。

例六：插入多条数据

1
2
3
4
5
6
INSERT 
INTO student(sid,sname,ssex,sbirthday,sorigin,scollege)
VALUES 
('100000','夏禾','女','2001-10-10','湖南','计算机'),
('100001','楚河','男','1999-8-10','北京','物联网'),
('100002','春幕',NULL,'2001-10-10','湖南','计算机');

2.删除数据

语法：delete from 表名 条件

例七：删除学号为100000的学生

1
2
3
DELETE
FROM student
WHERE sid=100000;

例八：删除姓名为春幕的学生

1
2
3
DELETE 
FROM student
WHERE sname='春幕';

例九：删除专业为物联网而且学号是100001的学生

1
2
3
4
DELETE 
FROM student
WHERE scollege='物联网'
AND sid='100001';

3.修改数据

语法：update 表名 set 列名=？ where 条件

例10：把春幕的性别修改为女

1
2
3
UPDATE student
SET ssex='女'
WHERE sname='春幕';

例11：把春幕的籍贯改成江苏

1
2
3
UPDATE student
SET sorigin='江苏'
WHERE sname='春幕';

4.查询数据

语法：

1
2
3
4
5
6
select <属性列表>
from <表名/视图名>,<表名>
[where <条件>]
[group by <列名>]
[having <条件>]
[order by <列名> asc/desc]

在进行查询前，我们先插入几条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- student表数据
INSERT 
INTO student
VALUE 
('091501','王英','女','1997-11-03','河北','计算机'),
('091502','王小梅','女','2000-11-03','江苏','信电'),
('091503','张小飞','男','1996-11-13','江西','计算机'),
('091504','孙志鹏','男','1998-1-03','海南','计算机'),
('091505','徐颖','女','1997-11-03','江苏','外文'),
('091506','钱易蒙','男','2000-1-13','河北','信电');

-- course表数据
INSERT
INTO course
VALUE
('180101','C++程序设计',56,NULL,'必修'),
('180102','数据结构',48,180101,'必修'),
('180103','操作系统',48,180102,'必修'),
('180104','数据库原理',48,180103,'必修'),
('180105','DB_Design',32,180104,'选修');

-- learn表数据
INSERT
INTO learn
VALUE
('091501','180101',78),
('091501','180102',80),
('091501','180103',77),
('091503','180101',89),
('091503','180102',78),
('091503','180103',70),
('091503','180104',90),
('091504','180101',59),
('091504','180102',50),
('091504','180103',NULL);

<1>单表查询

(1)选择表中的若干列

例12：查询学生表所有学生的姓名和籍贯

1
2
SELECT sname,sorigin
FROM student;

例13：查询学生表中所有学生的籍贯（要求去重）

1
2
3
-- distinct去重关键字
SELECT DISTINCT sorigin
FROM student;

例14：查出学生表的所有信息

1
SELECT *FROM student;
(2)给列定义别名

使用AS来定义别名，其中AS可以省略

例15：查询学生的姓名和性别

1
2
SELECT sname 姓名,ssex 性别
FROM student;
(3)选择查询

使用where子句实现

例16：查询有不及格课程的学生的学号、课程号以及成绩

1
2
3
SELECT sid,cid,score
FROM learn
WHERE score<60;

例17：查询有不及格课程的学生的学号

1
2
3
SELECT DISTINCT sid
FROM learn
WHERE score<60;

例18：在student表中找出信电学院2000年后出生的学生记录

1
2
3
SELECT *
FROM student
WHERE scollege='信电' AND sbirthday>2000;
(4)确定范围

确定范围使用了一个关键字：属性 between xx and xx（包含xx,相当于 大于等于xx and 小于等于xx）

例19：查询学号在091503~091505的学生的姓名、性别、学院和出生日期。

1
2
3
SELECT sname,ssex,scollege,sbirthday
FROM student
WHERE sid BETWEEN '091503' AND '091505';
(5)确定集合

确定集合使用IN关键字

例20：查询信电学院、物联网学院和计算机学院的学生的学号、姓名和学院

1
2
3
SELECT sid,sname,scollege
FROM student
WHERE scollege IN('信电','计算机','物联网');
(6)字符匹配

字符匹配关键词like

例21：查询所有姓王的学生的姓名、学号和性别

1
2
3
SELECT sname,sid,ssex
FROM student
WHERE sname LIKE '王%';

例22：查找名字中第二个字为“小”字的学生的姓名和学号

1
2
3
SELECT sname,sid
FROM student
WHERE sname LIKE '%小%';

例23：查找课程名是DB_Design课程的课程号、课程性质

1
2
3
SELECT cid,cproperties
FROM course
WHERE cname LIKE 'DB_Design';
(7)空值

查询空值的关键字is null

例24：查询缺少成绩的学生的学号和相应的课程号

1
2
3
SELECT sid,score
FROM learn
WHERE score IS NULL;
(8)对查询结果进行分组

分组的关键词：group by

例25：查询各个课程号相应的选课人数

1
2
3
SELECT cid,COUNT(sid)
FROM learn
GROUP BY cid;

例26：查询学号在091501~091506至少选修了三门课程的学生的学号和选修课程的课程数

having短语用来指定筛选条件

1
2
3
4
5
6
7
8
SELECT sid,COUNT(cid)
FROM learn
WHERE sid BETWSELECT sid,score 
FROM learn
WHERE cid='180102'
ORDER BY score DESC;EEN '091501' AND '091506'
GROUP BY sid
HAVING COUNT(cid)>=3;
(9)使用集函数

例27：查询学生总人数

1
2
SELECT COUNT(sid) AS 总人数
FROM student;

例28：查询学习180101课程学生的平均成绩

1
2
3
SELECT AVG(score)
FROM learn
WHERE cid='180101';

例29：查询学习180101号课程的学生的最高分数

1
2
3
SELECT MAX(score)
FROM learn
WHERE cid='180101';

例30：查询选修了180102的课程的学生学号和成绩，查询结果按成绩从高到低排列

1
2
3
4
SELECT sid,score 
FROM learn
WHERE cid='180102'
ORDER BY score DESC;

例31：查询全体学生情况，查询结果按所在学院的名称升序排列，对同一个学院的学生按学号降序排列

1
2
3
SELECT *
FROM student
ORDER BY scollege ASC,sid DESC;

<2>连接查询

连接查询就慢慢的变难了，连接查询必须要掌握透彻！连接查询就是指，一个数据库中的多个表之间一般都存在某种内在联系，他们共同提供有用的信息，若查询同时涉及两个或多个表就是连接查询。前面的查询都是针对一个表进行的，较为简单。这后面的都是针对多表进行的，加油呀！

（1）ANSI方式

该方式可通过“表1 [inner] join 表2 on 连接条件”来实现两个表的连接。

例32：查询每个学生及其选修课程的情况

1
2
3
SELECT learn.sid,course.cname
FROM learn JOIN course ON learn.cid=course.cid
ORDER BY sid;
（2）theta方式

该方式通过where子句指定条件进行连接

例33：查询每个学生及其选修课程的情况

1
2
3
4
SELECT	l.sid 学号,c.cname 课程
FROM learn l,course c
WHERE l.cid=c.cid
ORDER BY l.sid;
（3）等值与非等值连接查询
1）什么是等值查询？

用来连接两个表的条件称为连接条件或连接谓词

一般形式:表名1.列名 比较运算符 表名2.列名

其中比较运算符主要有：=,>,<,>=,<=,!=

当比较运算符为=时，为等值连接，否则为非等值连接

注意：连接条件中的各连接字段类型必须是可比的，类型需要相同。

例：查询学生的姓名以及他们选的课程号，和课程名称

1
2
3
SELECT s.sname,l.cid,c.cname
FROM student s,learn l,course c
WHERE s.sid=l.sid AND l.cid=c.cid;
2）什么是自然连接？

自然连接是等值连接运算的一种特殊情况，即按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列。

例34：自然连接student和learn表

1
2
3
SELECT s.sid,sname,ssex,sbirthday,sorigin,scollege,cid,score
FROM student s,learn l
WHERE s.sid=l.sid;
（4）自身连接查询

例35：求一门课程的间接先选修

1
难题：遗留问题
（5）外连接查询
1）左外连接

左外连接什么时候用呢？

当规定所有记录都应该从连接语句左侧的表中返回。当右侧表中并没有匹配的记录时，左表中该记录依然会返回，而对应的右侧表中的列值将自动填充null值

例36：查询所有学生的姓名以及他们选修课程的课程号和成绩

1
2
SELECT s.sname,l.cid,l.score
FROM student s LEFT OUTER JOIN learn l ON s.sid=l.sid;
2）右外连接

右外连接什么时候使用呢？

当规定所有的记录都应该从连接语句右侧的表中返回。当左侧表中没有匹配的记录时，右表中的值依然返回，而对应的左表中的列值将自动填充null值

例37：查询所有的课程信息及选修该课程的学生的学号及成绩

1
2
3
SELECT course.cid,learn.sid,score
FROM course 
RIGHT OUTER JOIN learn ON learn.cid=course.cid;
（6）复合条件连接查询

例38：查询选修180101号课程且成绩在80分以上的学生的学号、姓名和成绩

1
2
3
4
5
SELECT s.sid,sname,l.score
FROM student s,learn l
WHERE s.sid=l.sid 
AND cid='180101'
AND score>80;

例39：查询每个学生及其选修的课程名及其成绩

1
2
3
4
SELECT s.sname,c.cname,l.score
FROM student s,learn l,course c
WHERE s.sid=l.sid
AND l.cid=c.cid;
（7）集合运算连接查询

SQL查询可以利用关系代数中的集合运算来组合关系，union代表了并集，intersect代表了交集，except代表了差集。但，注意：mysql中只支持union（∪）

例40：查询选修了180101号或180102号课程或二者都选修了的学生的学号、课程号和成绩

1
2
3
4
5
6
7
8
9
(SELECT s.sid,l.cid,l.score
FROM student s,learn l
WHERE s.sid=l.sid
AND l.cid='180101')
UNION
(SELECT s.sid,l.cid,l.score
FROM student s,learn l
WHERE s.sid=l.sid
AND l.cid='180102')

例41：查询同时选修了180101和180102号课程的学生的学号、课程号和成绩

mysql不支持,你可以试试

1

例42：查询选修了180101号课程的学生中没有选修180102号课程的学生学号、课程号和成绩

mysql不支持

1

<3>嵌套查询

在SQL中，一个select…from…where语句称为一个查询块，将一个查询块嵌套在另一个查询块的where子句或having条件中的查询成为嵌套查询。

使用方法：由里向外一层层的处理，根据子查询是否独立于父查询，可分为两类：

  • 不相关子查询，该类中的子查询独立于上层的父查询，每个子查询在夫查询处理之前就可以完成求解，子查询的结果作为父查询的查找条件。
  • 相关子查询，该类中的子查询依赖于父查询，最内层的子查询的执行需要用到父查询中的某些属性值。

例43：查询选修了180101号课程的学生姓名

1
2
3
4
5
6
7
8
9
10
11
12
-- 1.采用连接查询
SELECT s.sname
FROM student s,learn l
WHERE s.sid=l.sid
AND l.cid='180101';
-- 2.使用嵌套查询
SELECT sname
FROM student
WHERE sid IN
(SELECT sid
FROM learn
WHERE cid='180101');
（1）带有IN谓词的子查询

带有in谓词的子查询是指父查询与子查询之间用in进行连接，判断某个属性列值是否在子查询的结果中。

例44：查询与王英在同一个学院学习的学生学号和姓名

1
2
3
4
5
6
SELECT sid,sname
FROM student
WHERE scollege IN
(SELECT scollege
FROM student
WHERE sname='王英');

例45：查询选修了“数据库原理”课程的学生学号和姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 嵌套查询
SELECT sid,sname
FROM student
WHERE sid IN
(SELECT sid
FROM learn
WHERE cid IN
(SELECT cid
FROM course
WHERE cname='数据库原理'));
-- 连接查询
select s.sid,s.sname 
from student s,learn l,course c
where s.sid=l.sid
and l.cid=c.cid
and c.cname='数据库原理';
（2）带有比较运算符的子查询

子查询返回单值的时候可以使用比较运算符，可以使用ALL或ANY谓词来实现比较操作。

例46：查询其他学院中比计算机学院某个学生学号小的学生名单

1
难题：遗留问题
（3）带有exists谓词的子查询

exists代表存在量词，带有exists的子查询不返回任何数据，只产生逻辑真值“true”或者逻辑值“false”

例47：查询选修了180102号课程的学生学号和姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 第一种嵌套查询
select sname,sid
from student
where sid in
(select sid
from learn
where cid='180102');
-- 第二种
select sname,sid
from student
where exists(
select *
from learn
where student.sid=learn.sid
and learn.cid='180101');
-- 连接查询
select s.sname,s.sid
from student s,learn l
where s.sid=l.sid
and l.cid='180102';

例48：查询没有选修180102号课程的学生学号和姓名

1
2
3
4
5
6
7
select sname,sid
from student s
where not exists
(select *
from learn l
where s.sid=l.sid
and l.cid='180102');

四：索引

<1>什么是索引？索引是干什么的？

​ 索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
　　MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。
　　创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)，而不是在select的字段中，实际上，索引也是一张“表”，该表保存了主键与索引字段，并指向实体表的记录，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。说白了索引就是用来提高速度的，但是就需要维护索引造成资源的浪费，所以合理的创建索引是必要的。

<2>索引的分类

​ MYSQL的索引主要分为主键索引(PRIMARY KEY)，唯一索引(UNIQUE) ，普通索引(INDEX)和全文索引(FULLTEXT) 。

​ 主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。

​ 唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一。

​ 普通索引：这是最基本的索引，它没有任何限制。

​ 全文索引：FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列。

<3>索引的创建

1）普通索引的创建

1
2
3
4
5
6
7
8
9
10
11
12
#创建一个普通索引
use mytest;
-- 方式一：直接创建索引：create index 索引名 on 表名(列名);
create index productIndex ON product(price);
-- 方式二：更改表结构创建索引：alter table 表名 add index 索引名(列名);
alter table product add index product_index(pname);
-- 方式三：创建表的时候直接指定索引
create table workaholic(
    id int primary key,
    userName varchar(20) not null ,
    INDEX userNameIndex(userName)
);

如下图：

image-20210108101459948

2)唯一索引的创建

1
2
3
4
5
6
7
8
9
10
11
-- 唯一索引的创建
-- 方式一：直接创建
create unique index index_product on product(pname);
-- 方式二：修改表的结构
alter table product add unique index_price (price);
-- 方式三：创建表的时候直接指定 
create table workaholic(
       id int primary key,
       userName varchar(20) not null ,
       UNIQUE userNameIndex(userName)
);

image-20210108154503908

3)主键索引

主键索引就是添加主键约束即可

<4>查询索引

1）查询表中所有的索引

1
show index from 表名;

image-20210108101858371

2）查询数据库中的所有索引

1
select *from mysql.innodb_index_stats as a where a.database_name='数据库名';

image-20210108102433826

<5>删除索引

1
2
3
4
-- 删除索引
drop index productIndex on product;
-- 或者
alter table product drop index productIndex;

image-20210108102939161

相关文章
点击查看
-------------------本文结束 感谢您的阅读-------------------