通过示例学习
以下表为需要操作的数据对象
创建操作
CREATE TABLE Student(
Sno CHAR(20) PRIMARY KEY,
Sname CHAR(10) UNIQUE,
Ssex CHAR(5) NOT NULL,
Sage SMALLINT ,
Sdept CHAR(4));
CREATE TABLE Course(
Cno SMALLINT PRIMARY KEY,
Cname CHAR(20) NOT NULL,
Cpno INT ,
Ccredit INT ,);
CREATE TABLE SC (
Sno CHAR(20),
Cno SMALLINT ,
Grade INT ,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno));
基本语句讲解
创建表格语句
CREATE TABLE [表名] (
[列名1] <数据类型> [列级完整性约束条件],
[列名2] <数据类型> [列级完整性约束条件],
[列名3] <数据类型> [列级完整性约束条件],
...
);
外键语句
-- 外键是另一表的主键, 外键可以有重复的, 可以是空值
FOREIGN KEY [列名] REFERENCES [表名][列名]
插入数据操作
INSERT INTO Student VALUES ('201215121','李勇','男',20,'CS');
INSERT INTO Student VALUES ('201215122','刘晨','女',19,'CS');
INSERT INTO Student VALUES ('201215123','王敏','女',18,'MA');
INSERT INTO Student VALUES ('201215125','张立','男',19,'IS');
INSERT INTO Course VALUES (1,'数据库',5,4);
INSERT INTO Course VALUES (2,'数学',NULL,2);
INSERT INTO Course VALUES (3,'信息系统',1,4);
INSERT INTO Course VALUES (4,'操作系统',6,3);
INSERT INTO Course VALUES (5,'数据结构',7,4);
INSERT INTO Course VALUES (6,'数据处理',NULL,2);
INSERT INTO Course VALUES (7,'PASCAL语言',6,4);
INSERT INTO SC VALUES ('201215121',1,92);
INSERT INTO SC VALUES ('201215121',2,85);
INSERT INTO SC VALUES ('201215121',3,88);
INSERT INTO SC VALUES ('201215122',2,90);
INSERT INTO SC VALUES ('201215122',3,80);
基本语句讲解
插入语句块
INSERT INTO [表名] VALUES ([列名1数据],[列名2数据],[列名3数据],[列名4数据],[列名5数据]...);
INSERT INTO [表名] VALUES ([列名1数据],[列名2数据],[列名3数据],[列名4数据],[列名5数据]...);
...
表格操作
全部输出
-- *代表选择全部列
SELECT *
FROM Student;
SELECT *
FROM Course;
SELECT *
FROM SC;
结果显示:
查询输出
-- 挑选每位学生出生年,并重命名
SELECT Sname , 2014-Sage birthday
FROM Student;
SELECT Sname ,'Year of birth:' BRITH,2014-Sage brithday,LOWER(Sdept) SDEPT
FROM Student;
-- 具有重复元组
SELECT Sno 'Sno具有重复元组'
FROM SC;
-- 没有重复元组
SELECT DISTINCT Sno 'Sno无重复元组'
FROM SC;
SELECT Sname ,Sage,'选择所有年龄小于20的学生姓名和年龄'
FROM Student
WHERE Sage < 20;
SELECT Sname , Sdept , Sage ,'查询所有年龄在20和23之间的学生信息'
FROM Student
WHERE Sage BETWEEN 20 AND 23;
SELECT Sname , Sdept , Sage ,'查询所有系为CS或者IS的学生信息'
FROM Student
WHERE Sdept IN('CS','IS');
SELECT *,'查询所有姓刘的学生信息'
FROM Student
WHERE Sname LIKE '刘_';
-- 这里用到了等值连接
SELECT Sname , SC.Sno , Grade ,'查询选择了课程3的学生姓名及成绩,并按降序排列'
FROM SC,Student
WHERE Cno = 3 AND SC.Sno = Student.Sno
ORDER BY Grade DESC;
SELECT Sno , '查询选修了两门及以上课程的学生学号'
FROM SC
GROUP BY Sno
HAVING COUNT(*) >= 2;
-- 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
FROM Student
WHERE Sdept = 'CS')
AND Sdept<>'CS';
-- 查询所有选修了1号课程的学生姓名
SELECT sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
-- 查询计算机科学系的学生及年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION
SELECT *
FROM Student
WHERE Sage <= 19;
结果显示:
修改数据:
-- 修改数据:将学生201215121的年龄改为22岁
UPDATE Student
SET Sage=22
WHERE Sno = '201215121';
-- 一次修改多个属性
UPDATE student
SET sno = '202000001',
sname = '张三'
WHERE sname = '张立';
SELECT *
FROM student;
-- 还原
UPDATE student
SET sno = '201215125',
sname = '张立'
WHERE sname = '张三';
删除数据:
-- 删除数据:删除学号为201215128的学生记录
DELETE
FROM Student
WHERE Sno = '201215125';
创建并查询视图:
CREATE VIEW IS_STUDENT
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept = 'IS';
SELECT *
FROM IS_STUDENT;
删除视图:
DROP VIEW IS_STUDENT;
更新视图:
UPDATE IS_STUDENT
SET Sname='刘辰'
WHERE Sno = '201215125';
SELECT *
FROM IS_STUDENT;
空值处理:
空值与另一个值的运算结果还是空值,其结果为UNKNOWN.
x----y----xANDy----xORy----NOTx
T T T T F
T U U T F
T F F T F
U T U T U
U F F U U
U U U U U
F T F T T
F F F F T
F U F F T
判断一个属性是否为空值,用IS NULL 和IS NOT NULL来表示
基本语句讲解
SELECT [ALL|DISTINCT] <目标表达式1> [.<目标表达式2>]...
FROM <表名或视图1>[,<表名或视图2>]...
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]]
通配符:
用于与 LIKE 关键词配合的语句
% 代表任意长度的字符串
例如 a%b 代表以a开头,b结尾的任意长度的字符串
_ 代表任意单个字符
例如 a_b 代表以a开头b结尾的长度为三的字符串
聚集函数:
COUNT(*) 统计元组个数
COUNT([DINSTINCT|ALL]<列名>) 统计一列中值的个数
SUM([DINSTINCT|ALL]<列名>) 计算一列值的总和(数值型)
AVG([DINSTINCT|ALL]<列名>) 计算一列的平均值(数值型)
MAX([DINSTINCT|ALL]<列名>) 求一列值的最大值
MIN([DINSTINCT|ALL]<列名>) 求一列值中的最小值
带有ANY或ALL谓词的子查询:
> ANY 大于子查询结果中某个值
> ALL 大于子查询结果中所有值
< ANY 小于子查询结果某个值
< ALL 小于子查询结果所有值
>=ANY 大于等于子查询结果中的某个值
>=ALL 大于等于子查询结果中的所有值
<=ANY 小于等于子查询结果中的某个值
<=ALL 小于等于子查询结果中的所有值
=ALL 等于子查询结果的所有值
<>ANY 不等于子查询结果中的某个值
<>ALL 不等于子查询结果中的任何一个值
带有EXISTS谓词的子查询:
带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值"true"或逻辑假值"false"
集合查询:
集合操作主要包括UNION、交操作INTERSECT和差操作EXCEPT
修改数据:
UPDATA [表名]
SET <列名><表达式>[,<列名><表达式>]
[WHREE <条件>]
删除数据:
DELETE
FROM [表名]
[WHERE <条件>]
创建视图:
CREATE VIEW [视图名] [,<列名> ...]
AS <子查询>
[WITH CHECK OPTION];
删除视图:
DROP VIEW [视图名] [CASCADE]
更新视图:
UPDATA[视图名]
SET <列名><表达式>[,<列名><表达式>]
[WHREE <条件>]
自主存取控制
创建和删除用户
创建用户:
CREATE USER `user3` @`localhost` IDENTIFIED WITH caching_sha2_password BY 'user3';
删除用户:
DROP USER `user3` @`localhost`;
创建用户:
create CREATE USER `用户名` @`主机名` IDENTIFIED WITH 身份验证插件 BY '密码';
删除用户:
drop user `用户名`@`主机名`;
授权:授予与收回
授予某用户对某一个数据表的某一项权限:
首先可以看到user1并没有select权限
当然也能通过如下语句查看某用户的所有权限:
SHOW GRANTS FOR `user1` @`localhost`;
执行以下语句后:
授予某用户对某一个数据表的某一项权限
-- 将查询student表的权限赋予用户user1
GRANT SELECT
ON TABLE test.student
TO user1@localhost;
将对某一个表的所有权限授予给某一些用户:
-- 将对student表的所有权限授予给user2和user3
GRANT ALL PRIVILEGES ON TABLE test.student
TO user2 @localhost,
user3 @localhost;
SHOW GRANTS FOR user2 @localhost;
SHOW GRANTS FOR user3 @localhost;
将对表中的特定属性进行授予权限:
-- 把查询student表和修改学生学号的权限授予用户user4
GRANT SELECT UPDATE ( sno )
ON TABLE test.student
TO user4 @localhost;
SHOW GRANTS FOR user4 @localhost;
权限再授予:
-- 把对表sc的insert权限授予用户user5,并允许将此权限再授予其它用户
GRANT INSERT
ON TABLE sc
TO user4 @localhost
WITH GRANT OPTION;
SHOW GRANTS FOR user4 @localhost;
进入被授予的用户再授予权限:
权限回收:
-- 权限回收:
SHOW GRANTS FOR user5 @localhost;
REVOKE INSERT
ON test.sc
FROM user5 @localhost;
SHOW GRANTS FOR user5 @localhost;
权限授予:
GRANT <权限>[,<权限>]...
ON <对象类型><对象名>
TO <用户>[,<用户>]...
[WITH GRANT OPTION]
权限回收:
REVOKE <权限>[,<权限>]...
ON <对象类型><对象名>
FROM <用户>[,<用户>]...
MySQL过程体
SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
存储过程是可编程的函数,在数据库中创建并保存,可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟,它允许控制数据的访问方式。
具有的优势:
1.增强了SQL语言的灵活性
2.较快的执行速度
3.标准组件式编程
4.增强安全性
无参过程体
-- 定义无参过程体
create procedure print1()
begin
select * from student;
select * from course;
select * from sc;
end;
-- 调用无参过程体
call print1();
IN参数的过程体
-- 定义IN参数的过程体
create procedure print2(in input varchar(20))
begin
select *
from student
where sno = input;
select *
from sc
where sno = input;
-- 内部改变IN参数
set input = 1;
end;
-- 定义用户变量
set @input = '201215121';
-- 调用IN参数的过程体
call print2(@input);
-- 查询IN参数外部是否改变
select @input; # 未发生改变
OUT参数的过程体
-- 定义带有OUT参数的过程体
create procedure print3(out output1 varchar(20),out output2 varchar(20))
begin
-- 查看过程体内部的参数
select output1,output2;
set output1 = 1000;
set output2 = 2000;
end;
set @output1 = '0001';
set @output2 = '0002';
call print3(@output1,@output2);
select @output1,@output2;
INOUT参数的过程体
-- 定义带有INOUT参数的过程体
create procedure print4(inout inoutput varchar(20))
begin
select inoutput;
set inoutput = '2000';
end;
set @inoutput = '0001';
call print4(@inoutput);
select @inoutput;
IF-THEN-ELSEIF-ELSE
create procedure fun1(in number int)
begin
-- 声明int变量
declare var int;
-- 表达式
set var = number + 1;
if var < 10 then
select var;
end if;
if var > 10 then
select var;
elseif var = 10 then
select var;
end if;
end;
set @number = 9;
call fun1(@number);
CASE-WHEN-THEN-ELSE
create procedure fun2(in number int)
begin
declare var int;
set var = number - 1;
case var
when 0 then
call fun1(var);
when 1 then
call fun1(var);
else
call fun1(var);
end case;
end;
set @number = 1;
call fun2(@number);
WHILE-END WHILE
create procedure fun3(in number int)
begin
declare var int;
set var = number;
while var < 20 and var > 0 do
set var = var + number;
end while;
select var;
end;
set @number = 5;
call fun3(@number);
REPEAT-UNTIL END REPEAT
create procedure fun4(in number int)
begin
declare var int;
set var = 0;
repeat
set var = var + 1;
until var >= number
end repeat;
select var;
end;
set @number = 10;
call fun4(@number);
LOOP-END LOOP
create procedure fun5(in number int)
begin
declare var int;
set var = 0;
loop_tag:loop
set var = var + 1;
if var = 10 then
-- 这个和break类似
leave loop_tag;
end if;
end loop;
select var;
end;
set @number = 20;
call fun5(@number);
MySQL补充
分页语句
select [查询列名] from [表名] limit 检索起始行末,检索个数
举例:
select * from student limit 0,3; -- 从第一行开始,检索3行数据
select * from student limit 3,3; -- 从第四行开始,检索3行数据
结果:
索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
索引的优缺点
索引大大提高了查询速度,却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件,同时,建立索引会占用磁盘空间的索引文件。
索引类型
- 主键索引
设置主键后数据库会自动建立索引- 单值索引
一个索引包含多个列,一个表可以有多个单列索引- 唯一索引
索引的值必须唯一,可以为空- 复合索引
一个索引包含多个列
查看索引信息
show index from [表名];
普通索引创建的两种方式
- 建表时创建
普通索引
create table [表名](列名,列名…,key(列名))
唯一索引
create table [表名](列名,列名…,unique(列名))
复合索引
create table [表名](列名,列名…,key(列名,列名…))
- 建表之后创建
普通索引
create index 索引名称 on [表名](列名)
唯一索引
create unique index 索引名称 on [表名](列名)
复合索引
create unique index 索引名称 on [表名](列名,列名…)
复合索引规则?
- 最左前缀原则
- 动态调整查询字段顺序
MySQL数据存储为B+树结构,默认一页为16KB大小,其页目录存放的数据是主键和指针,树的根节点常驻内存…
聚簇索引和非聚簇索引
- 聚簇索引(一般主键为聚簇索引):将数据存储与索引放到一起,索引结构的叶子节点保存了数据
- 非聚簇索引(辅助索引):将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置
MySQL存储引擎MyISAM与InnoDB的底层数据结构的主要区别为磁盘上存储的文件以及存储索引以及组织存储索引的方式不同。
MyISAM索引文件和数据文件是分离的(非聚集索引),索引的叶节点存放的是对应索引在文件系统中的数据地址,比如说查找id=1的元素时,是先索引树查询到1对应的数据文件地址,然后再拿着地址在数据文件中取出对应的数据,而Innodb的主键索引文件上直接存放该行数据,其为聚簇索引,非主索引指向对主键的引用
什么情况下不会使用到索引?
- like查询中,使用%在条件之前
- 查询语句中使用复合索引,不遵循复合索引规则
- 查询语句使用OR关键字,存在条件都不是索引时