SQL 基础知识

最新推荐文章于 2024-07-30 09:39:28 发布
最新推荐文章于 2024-07-30 09:39:28 发布
阅读量1.1w 收藏 3
点赞数 1
文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43914658/article/details/108699459
版权

通过示例学习

以下表为需要操作的数据对象

在这里插入图片描述

创建操作

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关键字，存在条件都不是索引时
参考：数据库系统概论(第五版) 王珊 萨师煊 编著
一夜星尘
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL Server基础知识
m0_61184185的博客
03-28 3572
SQL Server的数据库对象包括表（table），视图（view），索引（index），存储过程（stored procedure），触发器（trigger）等 SQL Server的数据库有两类：一类为系统数据库。一类为用户数据库 系统数据库即为master、model、msdb、tempdbmaster数据库：它是系统最重要的数据库，记录了SQL Server的系统信息，例如登录账号、系统配置、数据库位置及数据库错误信息等，用于控制用户数据库和SQL Server的运行。model数据库：为创建数据
Sql Server基础知识总结
it_超的专栏
04-26 707
1,sql server的历史: 第一代数据库:网状与层次模型 第二代数据库：关系模型 (sql server属于关系模型数据库) 第三代数据库：对象模型 2，实体:客观存在的可以被描述的事物叫实体 3，冗余:存在重复的数据 消除冗余的办法：分类存储 4，完整性:指的是数据的准确性 5，系统的内置数据库: (1)master:存储着系统信息。 (2)model:所有数据库的模板 (3)msdb:sql server代理服务存储系统任务以及自动化作业使用
SQL基础知识
最新发布
weixin_44002014的博客
07-30 840
该篇文章为作者学习SQL时的一些基础知识沉淀，所以内容均从网上搜集整合而成，希望能给同样学习SQL的朋友们提供一些方便查阅的资料。如果涉及抄袭问题烦请联系作者修改，如果内容有误欢迎指正！谢谢大家！祝愿大家知识和薪资都节节高升！
SQL SERVER 基础篇（一）：知识点、SQL语句学习及详细总结
热门推荐
lemonGuo的大脑皮层、小脑、海马体、杏仁核.....
12-09 5万+
SQL SERVER 基础篇，详细总结学习sql语句和知识点，适合入门
SQL Server基础知识汇总
wuhandecsdn的博客
12-26 1062
第六章：用表组织数据 参照SQL Server 2008 R2图形化操作界面总结.docx 小结： 1.数据完整性：正确反映实际情况；存在一定的数据冗余（数据重复） 2.系统数据库： A.Master数据库：系统级别的信息： （1）登录账户和系统配置信息 （2）所有其他数据库及数据库文件的位置 （3）初始化信息 B.Temp数据库：临时数据库，存放临时表、临时存储过程、临时工作表 （1）每次启...
SQL基础知识第2版.pdf
10-08
本文将详细介绍SQL的基础知识，包括SELECT语句的用法、DISTINCT关键词以及TOP子句的概念与应用。 首先，关于SELECT语句，它是SQL中用来从数据库表中选择数据的最重要的命令之一。SELECT语句的基本用法是从表中选取...
数据库SQL基础知识
07-26
数据库SQL基础知识是IT领域中至关重要的一环，尤其对于初学者来说，掌握好SQL语言是进入数据管理和分析世界的敲门砖。SQL（Structured Query Language），结构化查询语言，是用于管理和处理关系型数据库的标准语言。...
SQL基础知识 精品文档.docx
10-25
SQL 基础知识精要 SQL 是一种结构化查询语言，用于操作数据库。SQL 语言是数据库的标准语言，无论何时访问一个数据库，都需要使用 SQL 语言。 一、 SQL 介绍 SQL 是一种标准语言，用于操作数据库。SQL 语言可以...
SQL基本知识
小喵的博客
08-14 988
SQL基础知识 一、SQL简介 二、MySQL的安装与配置 三、DDL：数据定义语言 四、DML：数据操作语言 五、DQL：数据查询语言，针对单表的查询 六、完整性约束 6.1实体（记录）完整性 6.2域（字段）完整性 6.3参照完整性（多表的设计）：定义外键 第一：（非常多）一对多 第二：多对多（比较常见） 第三种：一对一（很少用到。实际开发根本不用） 七、多表的查询（...
mysql面经
qq_27095007的博客
09-27 2477
优化sql（sql语句优化、索引优化、表结构或设计优化、数据库访问性能优化） 1） SQL语句优化。（1）查询语句中尽量不要使用*，（2）用关联查询代替子查询，（3）用exists代替in，（4）调整where子句顺序，将过滤掉最多数据记录的条件写在where子句最末尾。 2） 索引优化。对查询字段建立索引，并且主要查询语句不要让索引失效。比如（1）使用like时，%不应该放在最前面，（2）联合...
sql基础知识
既然选择了-何必在意的专栏
10-27 1156
早上无事，想着整理一下sql中常用的一些操作数据库，操作表的语句，以备日后需要用到的时候可以及时查到。 一、操作数据库方面 创建一个名为 "my_db" 的数据库： CREATE DATABASE my_db
SQL基础知识全解：DML与DDL操作
"该资源是全面介绍SQL基础知识的文档，适合初学者逐步深入学习。文档内容涵盖SQL的主要组成部分，包括DML（数据操作语言）和DDL（数据定义语言）。DML涉及SELECT、UPDATE、DELETE和INSERT INTO等操作，用于查询、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值