数据库的简单运用

史密斯太太和她的驴

于 2022-12-14 11:15:47 发布

阅读量1k

点赞数 16

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_43766651/article/details/128313598

版权

一、数据库分类

其实，数据库通常分为网络式数据、层次式数据库库和关系式数据库三种，而不同的数据库是按不同的数据结构来联系和组织的。在现在的应用当中，最常见的数据库模型是关系型数据库和非关系型数据库这两种数据库类型。

1、关系型数据库的简要介绍

关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。其实，目前关系型数据库的具体产品有：Oracle和MySQL。其中Oracle在数据库领域是占领导地位的。SQLServer + C#, Java + Oracle, Java + MySql, DB2(IBM)。关系型数据库虽然有多种，当基本的sql语句（增，删，查，该）基本一致。

嵌入式数据库：在移动设备上使用。Android(SQLite), H2

2、非关系型数据库的简要介绍

1）、键值存储数据库

键值数据库就类似传统语言中使用的哈希表。可以通过key来添加、查询或者删除数据库，因为使用key主键访问，所以会获得很高的性能及扩展性。键值数据库主要使用一个哈希表，这个表中有一个特定的键和一个指针指向特定的数据。Key/value模型对于IT系统来说的优势在于简单、易部署、高并发；

典型产品：Memcached、Redis、MemcacheDB

2）、列存储数据库

列存储数据库将数据存储在列族中，一个列族存储经常被一起查询的相关数据，比如人类，我们经常会查询某个人的姓名和年龄，而不是薪资。这种情况下姓名和年龄会被放到一个列族中，薪资会被放到另一个列族中。这种数据库通常用来应对分布式存储海量数据；这种数据库的典型产品有：Cassandra、HBase

3）、面向文档数据库

文档型数据库的灵感是来自于Lotus Notes办公软件，而且它同第一种键值数据库类似。该类型的数据模型是版本化的文档，半结构化的文档以特定的格式存储，比如JSON。文档型数据库可以看作是键值数据库的升级版，允许之间嵌套键值。而且文档型数据库比键值数据库的查询效率更高；

面向文档数据库会将数据以文档形式存储。每个文档都是自包含的数据单元，是一系列数据项的集合。每个数据项都有一个名词与对应值，值既可以是简单的数据类型，如字符串、数字和日期等；也可以是复杂的类型，如有序列表和关联对象。数据存储的最小单位是文档，同一个表中存储的文档属性可以是不同的，数据可以使用XML、JSON或JSONB等多种形式存储。这种数据库的典型产品有：MongoDB、CouchDB；

4）、图形数据库

图形数据库允许我们将数据以图的方式存储。实体会被作为顶点，而实体之间的关系则会被作为边。比如我们有三个实体，Steve Jobs、Apple和Next，则会有两个“Founded by”的边将Apple和Next连接到Steve Jobs。这种数据库的典型产品有：Neo4J、InforGrid。

二、MySQL数据的安装

这是使用mysql8.0.29解压版为例

第一步：解压mysql8.0.29到任意目录(推荐C盘下非中文路径)

第二步：在解压后的mysql根目录系，创建my.ini文件，并添加mysql的基本配置信息

在my.ini文件下添加如下配置：

[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

# 设置3306端口

port = 3306

# 设置mysql的安装目录

basedir= C:\\Program Files\\mysql-8.0.29-winx64

# 设置mysql数据库的数据的存放目录

datadir= C:\\Program Files\\mysql-8.0.29-winx64\\data 

# 允许最大连接数

max_connections=20

# 服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nytB78Ut-1670987690123)(raw/mysql1.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5VURMthg-1670987690123)(raw/mysql2.png)]

第三步：安装mysql服务

使用管理员身份打开cmd，进入到mysql路径下的bin目录, 执行mysqld --install命令，安装mysql服务。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KgA1rJ7s-1670987690124)(raw/msyql4.png)]

第四步：获取mysql的登录密码
输入mysqld --initialize命令初始化mysql的data数据目录，初始化完毕后，会在解压目录下生成一个data文件夹，在这个文件夹下有一个.err结尾的文件，打开后会有随机生成的密码(需要记忆该随机密码)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pzFZvONg-1670987690124)(raw/msyql5.png)]

第五步：开启mysql服务，并设置mysql服务的启动方式（随开机启动、手动两种方式）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2ewFB8Ma-1670987690124)(raw/msyql7.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jn3wx2hU-1670987690125)(raw/mysql8.png)]

第六步：修改随机密码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nusS47Tq-1670987690125)(raw/mysql6.png)]

mysql解压版卸载：
第一步：停止mysql服务。
第二步：管理员身份打开cmd， 进入到msyql的bin目录下
	输入：mysqld -remove

三、MySQL体系结构简介

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IvLgpL5D-1670987690125)(raw/mysql.png)]

1. 企业管理服务和工具

系统管理和控制工具，例如 MySQL 备份恢复、MySQL 复制、MySQL 集群等工具。

2. 连接池

负责监听对客户端向 MySQL Server 端的各种请求，建立连接、权限校验、维持和管理连接，通信方式是半双工模式，数据可以双向传输，但不能同时传输。

单工：数据单向发送。
半双工：数据双向传输，但不能同时传输。
全双工：数据双向传输，可以同时传输。

那么 MySQL 是怎么保存连接得嘞？

每个成功连接 MySQL Server 的客户端请求都会创建或分配一个线程，在内存中分配一块空间存储对应的会话信息，其中包含权限等信息，该线程负责客户端与 MySQL Server 端的通信，接收客户端发送的命令，传递服务端的结果信息等。

用户的权限表在系统表空间的 mysql 库中的 user 表中，这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

MySQL 允许最大的连接数

show variables like '%max_connections%';

3. SQL接口

负责接收用户 SQL 命令，如 DML，DDL 和存储过程等，并将命令发送到其他部分，并接收其他部分返回的结果数据，将结果数据返回给客户端。

4. 查询缓存

首先需要说明：在 MySQL8.0 中已经删除了查询缓存，MySQL5.7 中仍然存在查询缓存。

如果开启了 MySQL 缓存的话，成功获取一个 MySQL 连接后，会先到查询缓存看看，之前是不是执行过这条语句。

如果之前执行过，那么这条语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段，通过存储引擎去查询。执行完成后，执行结果会被存入查询缓存中。

5. 解析器

负责将接收到的 SQL 命令解析和验证。解析器主要功能：

将 SQL 语句分解成数据结构，并将这个结构传递到后续步骤，以后 SQL 语句的传递和处理就是基于这个结构的。说人话就是将我们写的 SQL 语句分解成 MySQL 认识的语法往下传递。
如果在分解构成中遇到错误，那么就说明这个 sql 语句是不合理的。说人话就是看看我们写的 SQL 语句有没有语法错误。

6. 查询优化器

负责 SQL 语句在查询之前对查询进行优化，这个过程会使用 optimizer trace 优化查询 SQL，然后计算各种可以使用的索引和全表扫描的查询成本相比较，选择最优的查询方式。

optimizer trace 工具会在后面的文章中说到，MySQL 到底通过什么规则计算的查询成本的，为什么有时候明明有可以使用的索引最后还是走的全表扫描，在后面章节小二会提到哦，欢迎各位客官关注。

7. 可拔插存储引擎

存储引擎就是如何管理操作数据(存储数据、更新数据、查询数据等)的一种方法，当然在 MySQL 中。而可拔插就可以理解为 MySQL 提供了一个接口，只要遵循规则即可以自定义实现存储引擎，Java中接口与实现类的关系。

8. 文件系统

文件系统主要是将数据库的数据存储在操作系统的文件系统之上，并完成与存储引擎的交互。例如数据库文件，表文件和各种日志文件(bin log、redo log、undo log等)。

mysql语句执行流程：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dngM9jB0-1670987690126)(raw/mysql9.png)]

四、mysql的文件系统

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z0hkVVbM-1670987690126)(raw/mysql10.png)]

五、mysql的基本命令

在cmd命令行下，操作mysql数据库
1、	登录数据库：
	mysql -u root -p

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DBuYsPlC-1670987690126)(raw/mysq11.png)]

2、显示系统中所有的数据库
	show databases;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3DouDLgz-1670987690126)(raw/mysql12png.png)]

3、创建数据库：
	a、CREATE DATABASE 数据名称；
	b、指定数据库的字符集是utf8格式：
		CREATE DATABASE 数据名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JgRELkNn-1670987690127)(raw/mysql13.png)]

4、删除数据库（危险操作，程序员没有权限）
	drop database 数据库名;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QrUDTuZp-1670987690127)(raw/mysql14.png)]

5、选择、切换数据库：use 数据库名称
	use 数据库名;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R6U2GV3o-1670987690127)(raw/mysql15.png)]

6、查看数据库中的数据表
	show tables;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IrcQLYj2-1670987690127)(raw/mysql16.png)]

7、查看数据表的表结构
	desc 数据表名;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OTEe5hfd-1670987690127)(raw/mysql17.png)]

8、数据表中的概念：
	字段， 记录， 数据元素

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-85LEzudE-1670987690127)(raw/mysqla-1.png)]

9、创建数据表
CREATE TABLE attendance(
	-- 字段名称；字段类型；PRIMARY KEY：主键字段；AUTO_INCREMEN：主键自增；
	-- COMMENT '考勤ID'：字段说明
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT '考勤ID',  
	-- 字段名称；字段类型；NOT NULL：条件约束；COMMENT '上班时间'：字段说明
	upTime TIMESTAMP NOT NULL COMMENT '上班时间',
	endTime TIMESTAMP NOT NULL COMMENT '下班时间',
	attname CHAR(30) NOT NULL COMMENT '考勤名称'
) COMMENT '考勤表'; -- COMMENT '考勤表'：数据表的作用说明

PRIMARY KEY主键：
	1、唯一标识一条数据记录，主键字段内容不能重复。
	2、一个表中，只能有一个主键
	3、主键字段不能为空
	
AUTO_INCREMENT：
	1、字段自增。
	2、字段类型只能为数字类型。
字段的数据类型：
	int:整型数据;
	char(100):长度100以内的字符型；如果数据不够100长度，实际存储长度还是100个字符的长度。
		运行效率高于varchar类型。
	varchar(100):长度100以内的字符型;如果数据不够100长度，以实际存储的数据长度保存数据。
		运行效率低于char类型。
	text:用于存储长文本。
	TIMESTAMP：时间类型,用于存放时间，格式：年-月-日 时：分：秒
	time:时间类型，格式 时：分：秒
	double:
	float:
	boolean
NOT NULL:非空约束，约束字段不能为空	
COMMENT：字段或数据表的说明，为开发过程中，方便查看数据字段的作用

10、添加数据
	格式：
	INSERT INTO 数据表名(字段名称1， 字段名2.....) 
		VALUES	(值1, 值2.......),
				(值1, 值2.......)
    实例：
	INSERT INTO attendance(upTime, endTime, attname) 
		VALUES	('2022-12-13 09:00:00', '2022-12-13 17:00:00', '正常出勤'),
				('2022-12-14 09:00:00', '2022-12-14 17:00:00', '正常出勤')

11、修改数据表名
	格式：a、rename table 原有表名 to 新表名;
		 b、alter table 原有表名 rename 新表名;
			实例：ALTER TABLE users RENAME emp;

12、alter关键字：数据表结构结修改,不修改数据表的实际内容
	a、数据表字段的添加，删除，修改
		1)、修改数据字段名及属性:alter table 表名 change 原有字段 新字段 [新字段类型];
			实例：ALTER TABLE emp CHANGE userAddre userAddress CHAR(100);
		
		2)、删除字段：alter table 表名 drop column 需要删除的列;
			实例：ALTER TABLE emp DROP COLUMN userSex;
		
		3)、添加字段：alter table 表名 add column 需要添加的列 字段类型;
			实例：ALTER TABLE emp ADD COLUMN userSex CHAR(10);
			
			
	b、在原有表基础上，对主外键的操作：
		1)、添加主键(一个表只能有一个主键)：alter table 表名 add primary key (字段1,字段2, 字段3....);
			(1).设置主键时，可以在几个相关字段上完成，即设置符合主键。
			(2).一张表只能设置一个主键。
			实例：ALTER TABLE achievements ADD PRIMARY KEY (acId, empId)
		
		2)、删除主键：alter table 表名 drop primary key;
			注意：删除主键时，该主键字段本能是自增字段。
			实例：ALTER TABLE achievements DROP PRIMARY KEY;

		3)、添加外键：alter table 表名 add foreign key 外键名 (外键字段) references 主键表(主健字段);
			(1).建立外键关联时，必须指向另外一个表的主键。
			(2).外键关联的作用，用于约束外键表的数据完整性及正确性。
			(3).已经建立外键关联的数据表，在删除主键表的数据时，需要停用(删除外键关联)或优先删除外键表对应的数据
			(4).先添加主键表数据，再添加外键表数据。
			(5).注意：在实际开发中，主外键只有逻辑关联，而无实际的主外键关联。
			
			实例：ALTER TABLE users ADD FOREIGN KEY fk_users_dept_deptId (deptId) REFERENCES dept(deptId);
		
		4)、删除外键：alter table 表名 drop foreign key 外键名;
			实例：ALTER TABLE users DROP FOREIGN KEY users_ibfk_1;

13、外键关联

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NwWNK5S7-1670987690128)(raw/mysqla-2.png)]

14、简单查询
	(1)、查询数据中所有的数据
		SELECT * FROM 表名
	(2)、查询数据中指定字段
		select 字段名1, 字段名2.... from 表名

15、where条件语句，对查询结果进行条件筛选
	a、条件表达式
		=， >, < , !=, <>
		实例:select * from attendance where id != 2;
			
		OR，AND连接多个Where条件
		SELECT * FROM attendance WHERE id = 2 OR id = 4;
		SELECT * FROM attendance WHERE id >= 3 AND attname = '早退'
	b、使用计算列
		1)、拼接字符串类型的字段：concat()函数完成
			SELECT userId, CONCAT(userName, '-', userAddress), deptId FROM users;
		2)、多个数字类型的字段求和，比如在员工收入表中，统计单个员工当月总收入=工资收入+奖金+项目提成
			SELECT userId, userName, salary+salary1 FROM users;
			
	c、as关键字：为查询的字段及表名取别名
		as是可以省略。
		SELECT userId 用户编号, CONCAT(userName, '-', userAddress) 名称与地址, deptId AS 部门编号 FROM users AS u;
	
	d、模糊查询: like, not like, 字符串拼接
		1)、%：任意长度的字符串
			SELECT * FROM users WHERE userAddress LIKE CONCAT('%', '成都', '%');
		2)、_:任意一位字符串
			SELECT * FROM users WHERE userName LIKE CONCAT('_林', '%')
	
	e、范围之内查询:between.... and....
		范围之内查询可以使用 >,<条件完成，一般不适用between.... and做范围内查询
		SELECT * FROM users WHERE inTime >= '2022-07-01' AND inTime <='2022-07-07' 

		SELECT * FROM users WHERE inTime BETWEEN '2022-07-01' AND '2022-07-07';
		
	f、in:在指定范围之内:格式in('value1', 'value2'.....)
		SELECT * FROM users WHERE userName IN ('肖林', '吴俊翰')
		SELECT * FROM users WHERE userName ='肖林' OR userName = '吴俊翰'
		in范围内查询会转化为Or条件表达式。一般情况下使用or条件，而不使用in条件。
			
		not in：不在范围内，执行方向查询。
		SELECT * FROM users WHERE userName NOT IN ('肖林');
	
	g、is null与 is not null
	   is null:空判定， is not null:非空判定
	   		SELECT * FROM users WHERE userAddress IS NOT NULL
	   		
	h、去掉重复的查询记录: distinct
		distinct:数据字段内容去重，一般单独使用
		SELECT DISTINCT(userAddress) FROM users;
		
	i、对查询结果排序：order by关键字； asc:按字段的升序排列(可以省略)。 desc：按字段降序排列
		SELECT * FROM users WHERE inTime IS NOT NULL ORDER BY inTIme ASC;
		按多个字段排序
		SELECT * FROM users WHERE inTime IS NOT NULL ORDER BY inTIme ASC, userName ASC;
			
	j、limit关键字,limit后面只能是>=0的整数。
		1)、limit n: -->limit只有一个参数,查询前面n条数据
		2)、limit n, m: -->n:数据表的起始条数(从0条开始), m：查询的数据记录数
		3)、可以使用limit完成分页查询功能。
			SELECT * FROM users LIMIT 0, 2;
			SELECT * FROM users LIMIT 2, 2;
			SELECT * FROM users LIMIT 4, 2;
			
    k、left join左连接
    
    l、right...join右连接

六、课后练习

实验表结构如下：
学生表： Student ( Sno , Sname , Sex , Sage , Sdept )
	Student 由学号（ Sno )、姓名（ Sname )、性别（ Sex )、年龄（ Sage )、所在系（ Sdept ）五个属性组成，其中 Sno 为主键。
	
课程表： Course ( Cno , Cname , Ceredit )
	Course 由课程号( Cnp )、课程名（ Cname )、学分（ Ccredit )三个属性组成，其中 Cno 为主键。

学生选课表： Score ( Sno , Cno , Grade ).
	Score 由学号( Sno )、课程号( Cno 、成绩（ Grade )三个属性组成，其中 Sno 、 Cno 的组合为主键。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KsswuUxL-1670987690128)(raw/mysqla-10.png)]

创建数据库，数据表，添加数据：
CREATE TABLE `course` (
  `c_id` int NOT NULL COMMENT '课程号',
  `c_name` varchar(10) NOT NULL COMMENT '课程名',
  `c_credit` int NOT NULL COMMENT '学分',
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `course`(`c_id`,`c_name`,`c_credit`) values (1,'财政学',100),(2,'国际金融',100),(3,'国际结算',100),(4,'数据结构算法',100),(5,'操作系统',100),(6,'计算机组成原理',100),(7,'数据通信',100),(8,'信息网络安全',100),(9,'计算机辅助教学',100),(10,'数学分析',100),(11,'高等代数',100),(12,'解析几何',100);

CREATE TABLE `score` (
  `s_id` int NOT NULL COMMENT '学号',
  `c_id` int NOT NULL COMMENT '课程号',
  `grade` int NOT NULL COMMENT '成绩',
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into `score`(`s_id`,`c_id`,`grade`) values (20221001,1,100),(20221001,2,100),(20221001,3,100),(20221002,4,95),(20221002,5,80),(20221002,6,70),(20221003,7,80),(20221003,8,90),(20221003,9,70),(20221004,10,89),(20221004,11,68),(20221004,12,96),(20221005,1,76),(20221005,2,85),(20221005,3,83),(20221006,4,75),(20221006,5,96),(20221006,6,74),(20221007,7,68),(20221007,8,77),(20221007,9,97),(20221008,10,88),(20221008,11,67),(20221008,12,94),(20221009,1,64),(20221009,2,78),(20221009,3,84),(20221010,4,87),(20221010,5,79),(20221010,6,86),(20221011,7,90),(20221011,8,86),(20221011,9,98),(20221012,10,85),(20221012,11,88),(20221012,12,77),(20221013,1,77),(20221013,2,86),(20221013,3,88),(20221014,4,60),(20221014,5,58),(20221014,6,67),(20221015,7,58),(20221015,8,70),(20221015,9,77);

CREATE TABLE `student` (
  `s_id` int NOT NULL COMMENT '学号',
  `s_name` varchar(10) NOT NULL COMMENT '学生姓名',
  `s_sex` varchar(3) NOT NULL COMMENT '学生性别',
  `s_age` int DEFAULT NULL COMMENT '学生年龄',
  `s_dept` varchar(10) DEFAULT NULL COMMENT '学生所在系',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `student`(`s_id`,`s_name`,`s_sex`,`s_age`,`s_dept`) values (20221001,'苗子','女',18,'金融系'),(20221002,'悟空','男',19,'软件工程系'),(20221003,'芯姐','女',18,'信息系'),(20221004,'怡姐','女',18,'数学系'),(20221005,'熊二','男',19,'金融系'),(20221006,'宝器','男',21,'软件工程系'),(20221007,'西兰','女',18,'信息系'),(20221008,'汪子','男',21,'数学系'),(20221009,'亮子','男',23,'金融系'),(20221010,'陈龙','男',24,'软件工程系'),(20221011,'children','女',18,'信息系'),(20221012,'陈子涵','男',25,'数学系'),(20221013,'芳姐','女',18,'金融系'),(20221014,'李云龙','男',26,'软件工程系'),(20221015,'张阳','女',22,'信息系');


1)査询全体学生的学号、姓名、所在系。
	SELECT s_id, s_name, s_dept FROM student;

2)査询全体学生的详细信息。
	SELECT * FROM student;
查询每个学生的基本信息，选课名称及每科成绩
	-- 使用笛卡尔乘积方式完成连表查询
	SELECT stu.s_id, stu.s_name, stu.s_dept, co.c_name, sc.grade  FROM student AS stu, score AS sc, course AS co 
		WHERE stu.s_id = sc.s_id AND sc.c_id = co.c_id;
	
查询每个学生的基本信息，选课名称及每科成绩
	SELECT stu.s_id, stu.s_name, stu.s_dept, co.c_name, sc.grade FROM student AS stu 
		LEFT JOIN score sc ON stu.s_id = sc.s_id
		LEFT JOIN course co ON co.c_id = sc.c_id;


3)査询全体学生的姓名及其出生年份。
	SELECT NOW(); //获取系统年-月-日 时：分：秒
	SELECT YEAR(NOW());  //获取当前年份
	SELECT MONTH(NOW()); //获取当前月份
	SELECT DAY(NOW());  //获取当前日期
	SELECT s.s_id, s.s_name, s.s_sex, YEAR(NOW()) - s.s_age AS 出生年份, s_dept FROM student AS s;

4)査询软件工程系及金融系全体学生的名单。
	SELECT * FROM student WHERE s_dept IN ('软件工程系', '金融系');

	SELECT * FROM student WHERE s_dept  = '软件工程系' OR s_dept = '金融系';

5)査询所有年龄在20岁以下的学生姓名以及年龄。
	SELECT s_name, s_age FROM student WHERE s_age <=20

6)査询考试成绩不及格的学生的学号,姓名，科目名称及成绩
	-- where条件语句在left...join, right...join, inner.join之后。
	SELECT stu.s_id, stu.s_name, stu.s_dept, co.c_name, sc.grade FROM student AS stu 
		LEFT JOIN score sc ON stu.s_id = sc.s_id
		LEFT JOIN course co ON co.c_id = sc.c_id
		WHERE sc.grade < 60;


7）查询年龄在20~25岁（包括20、25）之间的学生的姓名、系别和年龄。
	SELECT s_name, s_sex, s_age FROM student WHERE s_age >= 20 AND s_age <= 25;
	-- BETWEEN AND：范围内查询，包含边界值
	SELECT s_name, s_sex, s_age FROM student WHERE s_age BETWEEN 20 AND 25;

8)査询不在信息系、数学系、也不在软件工程系学生的姓名和性别。
	SELECT s_name, s_sex FROM student WHERE s_dept NOT IN('信息系', '数学系', '软件工程系')
	SELECT s_name, s_sex FROM student WHERE s_dept != '信息系' AND s_dept != '数学系' AND s_dept != '软件工程系';

9)査询所有姓李且全名为三个汉字的学生的姓名、学号和性别。
	SELECT s_name, s_id, s_sex FROM student WHERE s_name LIKE '李__';
	-- like语句中，一般使用concat()拼接字符
	SELECT s_name, s_id, s_sex FROM student WHERE s_name LIKE CONCAT('李', '_', '_');

10)査询姓名中第2个字为“阳”字的学生的姓名和学号。
	SELECT s_name, s_id FROM student WHERE s_name LIKE '_阳%';
	SELECT s_name, s_id FROM student WHERE s_name LIKE CONCAT('_','阳', '%');

11)査询软件工程系年龄在20岁以下的学生的姓名。
	ELECT s_name FROM student WHERE s_dept = '软件工程系' AND s_age < 20;

12)査询选修了3号课程的学生的学号, 姓名及其成绩，査询结果按分数的降序排列
	SELECT student.s_id, s_name, grade FROM student LEFT JOIN score
		ON student.s_id = score.s_id
		WHERE score.c_id = 3

13)査询全体学生的情况，结果按所在系的升序排列，同一系的按年龄降序排列。
	-- asc:升序， desc:降序
	SELECT stu.s_id, stu.s_name, stu.s_age, stu.s_dept, co.c_name, sc.grade FROM student AS stu 
		LEFT JOIN score sc ON stu.s_id = sc.s_id
		LEFT JOIN course co ON co.c_id = sc.c_id
		ORDER BY stu.s_dept ASC, stu.s_age DESC;

14）统计学生总人数。
	-- count(字段) 为空(NULL)的字段不在统计范围之内
	SELECT COUNT(1) FROM student;
	-- count(*):如果表中有主键，默认使用主键完成统计，如果没有主键，使用表中不为空的字段约束完成统计。
	SELECT COUNT(*) FROM student;
	-- 表中有主键，使用count(主键字段)执行效率最高
	SELECT COUNT(s_id) FROM student;

	SELECT COUNT(1) FROM test;
	SELECT COUNT(*) FROM test;
	SELECT COUNT(id) FROM test;
	SELECT COUNT(NAME) FROM test;

	SELECT COUNT(id) FROM test;

15)査询选修了课程的学生人数(每个课程的选修人数)。count, GROUP BY
SELECT COUNT(s_id) AS 人数, s_dept FROM student GROUP BY s_dept ORDER BY 人数;


16）计算1号课程的学生平均成绩。avg
SELECT AVG(grade) AS 平均成绩, c_id FROM score WHERE c_id = 1

-- 课后补充：where与gourp by...having的区别。

-- 查询课程1, 课程2的平局成绩
-- having:对group by分组后的数进行条件筛选，having与group by配合使用。
SELECT AVG(grade) AS 平均成绩, c_id FROM score GROUP BY c_id HAVING c_id = 1 OR c_id = 2;
-- where条件在group by之前。
SELECT AVG(grade) AS 平均成绩, c_id FROM score WHERE c_id <= 2 GROUP BY c_id;

17）查询选修了1号课程的学生最高分数．M3名 MAX
SELECT MIN(grade) FROM score WHERE c_id = 1;

-- 查询每个课程的最高学分
-- union:拼接两个查询结果集
SELECT * FROM 
(
	SELECT MAX(grade) AS 学分, c_id FROM score GROUP BY c_id
	UNION
	SELECT MIN(grade), c_id FROM score GROUP BY c_id
	UNION
	SELECT AVG(grade), c_id FROM score GROUP BY c_id
) sc ORDER BY c_id;


SELECT MAX(grade) AS 最高学分, MIN(grade) AS 最低学分, AVG(grade) 平均成绩, c_id FROM score GROUP BY c_id

18）求各课程号及相应的选课人数。
SELECT COUNT(s_id), co.c_id FROM score AS sc RIGHT JOIN course AS co
	ON sc.c_id = co.c_id
	GROUP BY co.c_id
	ORDER BY co.c_id;

19)査询选修了4门以上课程的学生学号。
SELECT COUNT(c_id) AS 选课数, s_id FROM score GROUP BY s_id HAVING 选课数 >= 4

20)査询毎个学生及其选修课程的情况及总成绩。 GROUP_CONCAT():行合并, 与group by分组配合使用	
SELECT stu.s_id, stu.s_name, stu.s_dept, GROUP_CONCAT(co.c_name), SUM(sc.grade) AS 总成绩 FROM student AS stu 
		LEFT JOIN score sc ON stu.s_id = sc.s_id
		LEFT JOIN course co ON co.c_id = sc.c_id
		GROUP BY stu.s_id

21)査询选修2号课程且成绩在90分以上的所有学生。
	方式1：通过笛卡尔乘积方式，进行连表查询（不推荐）
	SELECT stu.s_id, stu.s_name, sc.grade FROM score AS sc, student AS stu
	WHERE sc.s_id = stu.s_id AND sc.c_id = 2 AND sc.grade >= 90

	方式2：通过左连接方式，进行连表查询（推荐）
	SELECT stu.s_id, stu.s_name, sc.grade FROM score AS sc LEFT JOIN student AS stu
	ON sc.s_id = stu.s_id
	WHERE sc.c_id = 2 AND sc.grade >= 90
	
22)査询毎个学生的学号、姓名、选修的课程名和成绩。
	方式1：通过笛卡尔乘积方式，进行连表查询（不推荐）
	SELECT stu.s_id, stu.s_name, co.c_name, sc.grade FROM student AS stu, score AS sc, 
	course AS co WHERE stu.s_id = sc.s_id AND sc.c_id = co.c_id
	
	方式2：通过左连接方式，进行连表查询（推荐）
	SELECT stu.s_id, stu.s_name, IFNULL(co.c_name, '没有选课')AS c_name, 
		IFNULL(sc.grade, 1) AS grade 
	FROM student AS stu LEFT JOIN score AS sc
		ON stu.s_id = sc.s_id
		LEFT JOIN course AS co
		ON co.c_id = sc.c_id

23)査询所有选修了1号课程的学生姓名。
	方式1：
	SELECT stu.s_name FROM student AS stu RIGHT JOIN score AS sc
	ON stu.s_id = sc.s_id
	WHERE sc.c_id = 1;
	
	方式2：把名字进行行合并
	SELECT GROUP_CONCAT(stu.s_name) FROM student AS stu RIGHT JOIN score AS sc
	ON stu.s_id = sc.s_id
	WHERE sc.c_id = 1;

	方式3：查询选修了课程1，课程2的学生姓名，并进行行合并
	SELECT GROUP_CONCAT(stu.s_name), sc.c_id FROM student AS stu RIGHT JOIN score AS sc
	ON stu.s_id = sc.s_id
	GROUP BY sc.c_id
	HAVING sc.c_id = 1 OR sc.c_id = 2;
	
	方式4：查询选修了课程1，课程2的学生姓名
	SELECT stu.s_name, sc.c_id FROM student AS stu RIGHT JOIN score AS sc
	ON stu.s_id = sc.s_id
	WHERE sc.c_id = 1 OR sc.c_id = 2;

24)査询选修了课程名为“数据库”的学生的学号和姓名。
    -- 采用子查询的方式实现
    -- 第一步 ：查询“财政学”课程号
    SELECT c_id FROM course WHERE c_name = '财政学'

    -- 第二步：查询选修了“财政学”学生的学号
    SELECT s_id FROM score WHERE c_id IN (SELECT c_id FROM course WHERE c_name = '财政学')
    
    -- 第三步：根据第二步中的学号，在学生表查询姓名和学号
    SELECT s_id, s_name FROM student WHERE s_id IN(SELECT s_id FROM score WHERE c_id IN
    	(SELECT c_id FROM course WHERE c_name = '财政学'));

-- 使用left join连接查询
    SELECT stu.s_id, stu.s_name FROM student AS stu LEFT JOIN score AS sc
        ON stu.s_id = sc.s_id
        LEFT JOIN course AS co
        ON co.c_id = sc.c_id
        WHERE co.c_name = '财政学'

七、索引

数据库的索引：索引即目录。索引主要是提高查询效率。
1、主键上默认有个主索引

2、一般在where条件字段上添加索引。

3、索引的名称规范：index_开头

3、索引的类型：
	a、主索引。也是一个唯一索引。
	b、唯一索引。也能唯一标识一条记录。索引字段可以是多个。
		#语法：alter table 表名 add unique [索引名] (字段1, 字段2....，字段n);
		ALTER TABLE course ADD UNIQUE index_course_name (c_name);
		
	c、普通索引，如果索引包含多个字段，则为符合索引
		#语法：alter table 表名 add index 索引名(字段名)
		ALTER TABLE score ADD INDEX index_sid_cid(s_id, c_id);
		
	d、全文索引:fulltext关键字, 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
		#语法：alter table 表名 add fulltext (user_id);
		ALTER TABLE student ADD FULLTEXT (s_dept);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zSYpsBev-1670987690128)(raw/mysql-index.png)]

八、视图

创建视图的格式：create view 视图名as (查询语句)
	
	CREATE VIEW view_student AS (
		SELECT s_id, s_name FROM student
	)

1、可以把视图理解为数据表，从视图中查询数据，与从表中查询数据语法格式一致。
2、数据表与视图之间有数据同步功能，修改原始数据时，视图中的数据会同步，反之修改视图中的数据，原始数据也会同步。
3、视图也是一种提高查询效率的方式。

注意：多表联合查询的视图，修改视图中的数据（该功能待定）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WFgFYQyV-1670987690128)(raw/mysql-view.png)]
rse ADD UNIQUE index_course_name (c_name);

c、普通索引，如果索引包含多个字段，则为符合索引
	#语法：alter table 表名 add index 索引名(字段名)
	ALTER TABLE score ADD INDEX index_sid_cid(s_id, c_id);
	
d、全文索引:fulltext关键字, 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
	#语法：alter table 表名 add fulltext (user_id);
	ALTER TABLE student ADD FULLTEXT (s_dept);


[外链图片转存中...(img-zSYpsBev-1670987690128)]

#### 八、视图

创建视图的格式：create view 视图名as (查询语句)

CREATE VIEW view_student AS (
	SELECT s_id, s_name FROM student
)

1、可以把视图理解为数据表，从视图中查询数据，与从表中查询数据语法格式一致。
2、数据表与视图之间有数据同步功能，修改原始数据时，视图中的数据会同步，反之修改视图中的数据，原始数据也会同步。
3、视图也是一种提高查询效率的方式。

注意：多表联合查询的视图，修改视图中的数据（该功能待定）


[外链图片转存中...(img-WFgFYQyV-1670987690128)]

史密斯太太和她的驴

关注

16
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
数据库的简单运用

关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。其实，目前关系型数据库的具体产品有：Oracle和MySQL。其中Oracle在数据库领域是占领导地位的。关系型数据库虽然有多种，当基本的sql语句（增，删，查，该）基本一致。嵌入式数据库：在移动设备上使用。键值数据库就类似传统语言中使用的哈希表。
复制链接

扫一扫