数据库--MYSQL基础（单表），数据库，表增删改查，掌握 SQL 语句的 CRUD

数据库简介

简介

数据库（ DataBase ， DB ）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种 用户或应用共享的数据集合。

数据库管理系统（ DataBase Management System ， DBMS ）：指一种操作和管理数据库的大型软

件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

数据库软件应该为 数据库管理系统 ，数据库是通过数据库管理系统创建和操作的。

数据库：存储、维护和管理数据的集合。

常见数据库管理系统

Oracle ： Oracle 数据库被认为是业界目前比较成功的关系型数据库管理系统。 Oracle 数据库可以运行 在UNIX 、 Windows 等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的 ISO 标准安全性 认证。

MySQL ： MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 旗下 产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面， MySQL 是最好的 RDBMS (Relational Database Management System，关系数据库管理系统 ) 应用软件。

三大范式（规范）

什么是三大范式：

第一范式： 无重复的列。 当关系模式 R 的所有属性都不能在分解为更基本的数据单位时，称 R 是满足第一范式的，简记为1NF 。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的 关系模式中实现不了。

第二范式： 属性完全依赖于主键 [ 消除部分子函数依赖 ] 。 如果关系模式 R 满足第一范式，并且 R 得所有 非主属性都完全依赖于R 的每一个候选关键属性，称 R 满足第二范式，简记为 2NF 。第二范式（ 2NF ）是 在第一范式（1NF ）的基础上建立起来的，即满足第二范式（ 2NF ）必须先满足第一范式（ 1NF ）。 第 二范式（ 2NF ）要求数据库表中的每个实例或行必须可以被唯一地区分 。为实现区分通常需要为表加上 一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。

第二范式（ 2NF ）和第三范式（ 3NF ）的概念很容易混淆，区分它们的关键点在于， 2NF ：非主键列是 否完全依赖于主键，还是依赖于主键的一部分； 3NF ：非主键列是直接依赖于主键，还是直接依赖于非 主键列。

SQL语言

概述

SQL ： Structure Query Language （结构化查询语言）， SQL 被美国国家标准局（ ANSI ）确定为关

系型数据库语言的美国标准，后来被国际化标准组织（ ISO ）采纳为关系数据库语言的国际标准。

各数据库厂商都支持 ISO 的 SQL 标准， 普通话

各数据库厂商在标准的基础上做了自己的扩展， 方言

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并删除条目等操作。

Create, Read, Update, and Delete 通常称为 CRUD 操作。

SQL语句分类

DDL （ Data Defifinition Language ）：数据定义语言，用来定义数据库对象：库、表、列等。

DML （ Data Manipulation Language ）：数据操作语言，用来定义数据库记录（数据）增删改。

DQL （ Data Query Language ）：数据查询语言，用来查询记录（数据）查询。

DCL （ Data Control Language ）：数据控制语言，用来定义访问权限和安全级别。

mysql 中的关键字不区分大小写

DDL(Data Definition Language)数据定义语言
-- 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

DDL(Data Definition Language)数据定义语言
-- 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
-- ----------------------------------------------------------------------------------
-- 数据库的增删改查 CRUD
-- Create 库的创建
CREATE DATABASE kkbdb2;
-- 判断一个库是否存在，不存在时才创建
CREATE DATABASE IF NOT EXISTS kkbdb2;
-- 创建数据库时，设置编码，UTF-8 在MYSQL中使用UTF8MB4替代
CREATE DATABASE kkbdb3 CHARACTER SET UTF8MB4;
-- Retrieve 库的查询
-- 查看所有库的名称
SHOW DATABASES;
-- 查看指定库的创建语句
SHOW CREATE DATABASE kkbdb;
-- UPDATE 库的修改 
-- 修改数据库的字符集
ALTER DATABASE kkbdb3 CHARACTER SET GBK;
-- DELETE 库的删除
-- 删除数据库
DROP DATABASE kkbdb3;
-- 判断数据的存在再删除
DROP DATABASE IF EXISTS kkbdb2;
-- 数据库的选择
-- 选择库
USE kkbdb;
-- 查看当前选择的库
SELECT DATABASE();

-- 表格的增删改查 CRUD
-- 表格的CREATE
/*
	CREATE TABLE 表名(
		列名1 数据类型,
		列名2 数据类型,
		...
	);
	常用数据类型
	int 整型数字
	bigint 大型整型数字
	double 浮点型数字 score double(5,2);小数点前5位，小数点后2位
	date 日期 只有年月日 yyyy-MM-dd
	datetime 日期加时间 年月日时分秒 yyyy-MM-dd HH:mm:ss
	timestamp 时间戳 13位的数字 从1970年1-1 到现在的毫秒数
	varchar 可变长度字符串 4000是数据库存储最大的值 name varchar(20); name 最大使用20个字符
	如果有很大的数据不会存储数据库里，把它作为文件存在一个位置，把文件的地址存在数据库里
*/
-- ----------------------------------------------------------------------------------
/*
创建一个学生表student
学号id，整形数字，最长不超过5位
姓名name，字符串，最长不超过20个字符
年龄age，整形数字，最长不超过3位
生日birthday，日期类型
生成数据时间insert_time，时间戳
*/
CREATE TABLE STUDENT(
	ID INT(5),
	NAME VARCHAR(20),
	AGE INT(3),
	BIRTHDAY DATE,
	INSERT_TIME TIMESTAMP	
);
-- 查看student表中所有内容
SELECT * FROM student;
-- ----------------------------------------------------------------------------------
/*
创建一个图书表book
编号id，整形数字
书名name，字符串
简介infor，字符串
*/
CREATE TABLE BOOK(
	ID INT,
	NAME VARCHAR(100),
	INFOR VARCHAR(4000)
);
-- 查看book表中所有内容
SELECT * FROM book;
-- --------------------------------------------------------------------------------
-- 通过复制表结构完成新表的创建
-- create table 表名 like 被复制的表名;
CREATE TABLE S_EMP LIKE EMP;
SELECT * FROM S_EMP;
-- --------------------------------------------------------------------------------
-- RETRIEVE 查询
-- 查询数据库中的所有表格名称
SHOW TABLES;
-- 查询某个表格的结构
DESC BOOK;
-- --------------------------------------------------------------------------------
-- updat 修改
-- 修改表名 ALTER TABLE 表名 RENAME TO 新表名;
ALTER TABLE TEACHER RENAME TO NURSE;
-- 修改表的字符集 ALTER TABLE 表名 CHARACTER SET 字符集名称;
ALTER TABLE NURSE CHARACTER SET GBK;
-- 修改表的结构 增加列 ALTER TABLE 表名 ADD 列名 数据类型;
ALTER TABLE STUDENT ADD HEIHEI VARCHAR(20);
-- 修改表的结构 修改列 ALTER TABLE 表名 CHANGE/MODIFY 列名 新列名 数据类型;
ALTER TABLE STUDENT CHANGE HEIHEI HEHE VARCHAR(10);
SELECT * FROM STUDENT;
-- 修改表的结构 删除列 ALTER TABLE 表名 DROP 列名;
ALTER TABLE STUDENT DROP HEHE;
SELECT * FROM STUDENT;
-- --------------------------------------------------------------------------------
-- 表格删除 DELETE 
-- DROP TABLE 表名
-- DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS BOOK;
-- DDL 数据定义语言 数据库的增删改查，表格的增删改查 create，delete，update，retrieve。

DQL(Data Query Language)数据查询语言
-- 用来查询数据库中表的记录(数据)。关键字：select, where等

DQL(Data Query Language)数据查询语言
-- 用来查询数据库中表的记录(数据)。关键字：select, where等
-- ----------------------------------------------------------------------------------
-- 查询一个表中所有数据
-- 字段列表由1-n个字段名组成，多个之间用,隔开
-- *代表所有字段名
SELECT * FROM DEPT;
-- 查询EMP 表中ENAME字段和SAL字段
SELECT ENAME,SAL FROM EMP;
SELECT SAL FROM EMP;
-- 去除查询的重复数据  去重关键字distinct
SELECT DISTINCT SAL FROM EMP;
-- 运算 四则运算 薪资*14
SELECT ENAME,SAL*14 FROM EMP;
-- 对查询的列数据，指定更好语义化理解的别名
-- SELECT 字段名1 别名, 字段名2... from 表名;
-- SELECT 字段名1 as 别名, 字段名2... from 表名;
SELECT ENAME,SAL*14 Y_SAL FROM EMP;
-- 判断空的函数
-- ifnull(表达式1,表达式2)
-- 当表达式1为null的时候返回表达式2，表达式1的结果非空，返回表达式1
SELECT ENAME,JOB,(SAL*14)+IFNULL(COMM,0) Y_SAL FROM EMP;
-- ----------------------------------------------------------------------------------
-- 条件查询
-- 对结果进行条件筛选，符合条件的才会被查询到
-- select 字段列表 from 表名 where 条件.
SELECT * FROM EMP WHERE SAL>2000;
-- 查询部门为10的所有员工
SELECT * FROM EMP WHERE DEPTNO=10;
-- 查询部门不为10的所有员工
SELECT * FROM EMP WHERE DEPTNO!=10;
-- 判断null 字段名is null / 字段名 is not null
-- 查询部门提成为空的所有员工
SELECT * FROM EMP WHERE COMM IS NULL;
-- 查询部门提成不为空的所有员工
SELECT * FROM EMP WHERE COMM IS NOT NULL;
-- 连接多个条件，使用与或非
-- 与 and &&
-- 或 or ||
-- 非 not !
SELECT * FROM EMP WHERE SAL<4000 && SAL>2000;
-- 取区间数据 字段名 between 起始值 and 结束值;
SELECT * FROM EMP WHERE SAL BETWEEN 2450 AND 3000;
SELECT * FROM EMP WHERE DEPTNO=10 OR DEPTNO=20;
-- in(数据1,数据2...) 数据匹配范围
SELECT * FROM EMP WHERE DEPTNO IN(10,20);
-- 模糊查询
-- 占位符：_任意一个字符 %0-n个任何字符
-- 查询姓名第三个字时斯的员工
SELECT * FROM EMP WHERE ENAME LIKE "__斯";
-- 查询姓名中包含K的员工
SELECT * FROM EMP WHERE ENAME LIKE "%K%";
-- 查询姓名中以马字开头的员工
SELECT * FROM EMP WHERE ENAME LIKE "马%";
-- ----------------------------------------------------------------------------------
-- 排序查询
-- order by 排序字段1 排序方式2,排序字段2 排序方式2;
-- 排序方式 升序asc 降序 desc 
-- 查询员工信息，排序方式：按照部门编号倒序排列
SELECT * FROM emp ORDER BY DEPTNO DESC;
-- 查询员工信息，排序方式：按照工资升序排列,当薪资相同时按照提成降序排列
SELECT * FROM emp ORDER BY SAL ASC, COMM DESC;
-- ----------------------------------------------------------------------------------
-- 聚合函数，将一列的所有数据作为一个整体进行纵向运算，并得到一个结果。
-- count(表达式或列名)：计算个数
-- max(表达式或列名)：计算最大值
-- min(表达式或列名)：计算最小值
-- sum(表达式或列名)：计算和
-- avg(表达式或列名)：计算平均值
-- 计算emp表中，所有员工平均薪资
SELECT	AVG(sal) FROM EMP; 
-- 计算emp表中，所有员工薪资最大值
SELECT	MAX(sal) FROM EMP; 
-- 聚合函数在运算时，参与运算的有空值，则忽略
-- 计算当月开支工资加提成
SELECT	SUM(SAL) + SUM(IFNULL(COMM,0)) FROM EMP;
SELECT	SUM(SAL+IFNULL(COMM,0)) FROM EMP;
SELECT	SUM(sal) FROM EMP; 
SELECT	SUM(IFNULL(COMM,0)) FROM EMP; 
-- ----------------------------------------------------------------------------------
-- 分组查询，把查询的结果，分组显示，查询的结果就是分组的数量
-- 假设分组查询得到的结果是5组，则只会显示5行的数据，每行数据显示的是每组的聚合函数统计结果或分组字段的值
-- group by 分组字段
-- group by 分组字段 having 分组条件
-- 按照薪资分组，显示各种薪资分组后的数量和薪资
SELECT SAL,COUNT(*) FROM EMP GROUP BY SAL;
-- where 与 having区别
-- where条件在分组之前进行判断，不满足where条件的不会被查询到
-- having条件在分组之后进行限定，不满足having的数据不会被显示
-- 以部门分组，判断工资大于1000，显示部门和部门人数
SELECT DEPTNO,COUNT(*) FROM EMP WHERE SAL>1000 GROUP BY DEPTNO;
-- 以部门分组，判断工资大于1000，显示部门和部门人数，分组后人数小于3的不显示
SELECT DEPTNO,COUNT(*) FROM EMP WHERE SAL>1000 GROUP BY DEPTNO HAVING COUNT(*)>3;
-- 计算出部门的平均薪资，显示部门编号和平均薪资
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;
-- 计算平均薪资
SELECT AVG(SAL) FROM EMP;
-- ----------------------------------------------------------------------------------
-- 分页查询limit MYSQL方言
-- 查询所有员工信息，要求分页显示，每页显示3个员工，按照薪资排序
SELECT * FROM EMP ORDER BY SAL LIMIT 0,3;
SELECT * FROM EMP ORDER BY SAL LIMIT 3,3;
-- ----------------------------------------------------------------------------------
-- 完整格式
-- select 字段列表 from 表名 where 条件列表 group by 分组字段 having 分组条件 order by 排序字段 排序方式 limit 分页;

DML(Data Manipulation Language)数据操作语言
-- 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

DML(Data Manipulation Language)数据操作语言
-- 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
-- 对表格中的数据进行修改操作(新增数据，删除数据，修改某一行数据)
-- 增加数据
-- 向指定列新增一行数据 insert into 表名(列名1,列名2...) values(列名1的值,列名2的值...);
-- 向全部列新增一行数据 insert into 表名 values(按照表格定义的字段顺序，依次填入);
INSERT INTO STUDENT(ID,NAME,AGE,BIRTHDAY,INSERT_TIME) VALUES(201,"小春",18,"2000-03-01","20000301123000");
INSERT INTO STUDENT VALUES(2,"大春",12,"2020-01-01","2022-02-25 16:56:00");
-- 一次插入多行
INSERT INTO STUDENT VALUES(1,"大春",12,"2020-01-01","2022-02-25 16:56:00"),(2,"小春",13,"2020-03-01","2022-02-24 12:56:00");
SELECT * FROM STUDENT;
-- 删除数据
-- DELETE FROM 表名 [where 删除的限定条件]
-- 清空表格的删除方式
-- DELETE FROM 表名;
-- TRUNCATE TABLE 表名;先删除表格文件再创建
DELETE FROM STUDENT;
TRUNCATE TABLE STUDENT;
-- 修改数据
-- UPDATE 表名 SET 列名1=值,列名2=值 [WHERE条件];
-- 不添加限定条件的修改，指的是修改全表的数据
UPDATE STUDENT SET NAME="小小春" WHERE ID=1;
DELETE FROM STUDENT WHERE ID=1;

 DCL(Data Control Language)数据控制语言
-- 用来定义数据库的访问权限和安全级别，及创建

-- DCL(Data Control Language)数据控制语言
-- 用来定义数据库的访问权限和安全级别，及创建
-- 管理用户
-- 添加一个用户
-- create user '用户名'@'主机名' identified by '密码';
-- 删除用户
-- drop user '用户名'@'主机名';
CREATE USER 'qw'@'localhost' IDENTIFIED BY '123456';
DROP USER 'qw'@'localhost';

练习题：

数据表:

年级表(Grade):年级 id(主键)-gradeid,年级名称(gname)

成绩表(score):id(主键)-scoreid,学员编号-stuno，科目 id-subjectid,

分数-score，考试时间-examtime

学生表(student):学生编号(主键)-stuid，学生姓名-stuname，登录密

码-password，性别-sex，年级 id-gid,电话-telphone，地址-address，出生日

期-birthday，邮箱-email

科目表(subject):科目 id(主键)-subjectid,科目名称-subjectname，课

时-studycount，年级 id-gradeid

需求:

1. grade 表增加一个阶段，“就业期”

2.将第三阶段的学生的 gradeid 改为就业期的 id

3.查询所有得了 100 分的学号

4.查询所有 1989 年出生的学生（1989-1-1~1990-1-1）

5.查询学生姓名为“金蝶”的全部信息

6.查询 subjectid 为 8 的科目考试未及格（60 分）的学号和成绩

7.查询第 3 阶段课时大于 50 的课程全部信息

8.查询 S1101001 学生的考试信息

9.查询所有第二阶段的女生信息

10.“基于.NET 平台的软件系统分层开发”需要多少课时

11.查询“设计 MySchool 数据库”和“面向对象程序设计”的课时(使用 in)

12 查询所有地址在山东的学生信息

13 查询所有姓凌的单名同学

14.查询 gradeid 为 1 的学生信息，按出生日期升序排序

15.查询 subjectid 为 3 的考试的成绩信息，用降序排序

16.查询 gradeid 为 2 的课程中课时最多的课程信息

17.查询北京的学生有多少个

18.查询有多少个科目学时小于 50

19.查询 gradeid 为 2 的阶段总课时是多少

20.查询 subjectid 为 8 的课程学生平均分

21.查询 gradeid 为 3 的课程中最多的学时和最少的学时

22.查询每个科目有多少人次考试

23.每个阶段课程的平均课时

24.查询每个阶段的男生和女生个数（group by 两列）

CREATE TABLE grade(
	gradeid INT PRIMARY KEY AUTO_INCREMENT,
	gname VARCHAR(20)
);
SELECT * FROM grade;
INSERT INTO grade(gname) 
VALUES('第一阶段'),
('第二阶段'),
('第三阶段');

CREATE TABLE score(
	scoreid INT PRIMARY KEY AUTO_INCREMENT,
	stuno VARCHAR(50),
	subjectid INT,
	score INT,
	exantime DATE
);
INSERT INTO score(stuno,subjectid,score,exantime)
VALUES('s11011001',1,100,'2010-10-01'),
('s11011002',2,50,'2010-10-01'),
('s11011003',5,30,'2010-10-01'),
('s11011004',8,70,'2010-10-01');
INSERT INTO score(stuno,subjectid,score,exantime) VALUES
('s11011005',3,70,'2010-10-01'),
('s11011006',3,30,'2010-10-01');
SELECT * FROM score;

CREATE TABLE student(
	stuid INT PRIMARY KEY AUTO_INCREMENT,
	stuname VARCHAR(20),
	PASSWORD VARCHAR(10),
	sex VARCHAR(10),
	gid INT,
	telphone VARCHAR(20),
	address VARCHAR(20),
	birthday DATE,
	email VARCHAR(10)
);
INSERT INTO student(stuname,PASSWORD,sex,gid,telphone,address,birthday,email)
VALUES('金蝶','1111','女',1,'110','山东济南','1989-1-12','123@qq.com'),
('凌云','1111','女',2,'120','山东青岛','1989-12-12','123@qq.com'),
('张三','1111','男',5,'130','北京','1999-12-12','123@qq.com'),
('李四','1111','男',8,'140','上海','1995-12-12','123@qq.com');
SELECT * FROM student;

CREATE TABLE SUBJECT(
	subjectid INT PRIMARY KEY AUTO_INCREMENT,
	subjectname VARCHAR(100),
	studycount INT,
	gradeid INT
)
INSERT INTO SUBJECT(subjectname,studycount,gradeid)
VALUES('基于.NET 平台的软件系统分层开发',60,1),
('设计 MySchool 数据库',61,1),
('面向对象程序设计',30,2),
('java基础',40,3),
('javaweb',50,1),
('计算机网络',60,2),
('STM32',70,3),
('php',10,4);
SELECT * FROM SUBJECT;
SHOW TABLES;
-- 1. grade 表增加一个阶段，“就业期”
INSERT INTO grade(gname) VALUES('就业期'); 
SELECT * FROM grade;
-- 2.将第三阶段的学生的 gradeid 改为就业期的 id
UPDATE student SET gid=(SELECT gradeid FROM grade WHERE gname='就业期')
WHERE gid=(SELECT gradeid FROM grade WHERE gname='第三阶段' );
SELECT * FROM student;
-- 3.查询所有得了 100 分的学号
SELECT stuno FROM score WHERE score=100;
-- 4.查询所有 1989 年出生的学生（1989-1-1~1990-1-1）
SELECT * FROM student WHERE birthday BETWEEN '1989-1-1' AND '1990-1-1';
-- 5.查询学生姓名为“金蝶”的全部信息
SELECT * FROM student WHERE stuname='金蝶';
-- 6.查询 subjectid 为 8 的科目考试未及格（60 分）的学号和成绩
SELECT stuno,score FROM score WHERE subjectid=8 AND score<60;
-- 7.查询第 3 阶段课时大于 50 的课程全部信息
SELECT * FROM SUBJECT WHERE studycount>50 AND gradeid=3;
-- 8.查询 S1101001 学生的考试信息
SELECT * FROM score WHERE stuno='S11011001';
-- 9.查询所有第二阶段的女生信息
SELECT * FROM student WHERE sex='女' AND gid=
(SELECT gradeid FROM grade WHERE gname='第二阶段');
-- 10.“基于.NET 平台的软件系统分层开发”需要多少课时
SELECT subjectname,studycount FROM SUBJECT WHERE subjectname='基于.NET 平台的软件系统分层开发';
-- 11.查询“设计 MySchool 数据库”和“面向对象程序设计”的课时(使用 in)
SELECT subjectname,studycount FROM SUBJECT WHERE subjectname
IN('设计 MySchool 数据库','面向对象程序设计');
-- 12 查询所有地址在山东的学生信息
SELECT * FROM student WHERE address LIKE'山东%';
-- 13 查询所有姓凌的单名同学
SELECT * FROM student WHERE stuname LIKE'凌%';
-- 14.查询 gradeid 为 1 的学生信息，按出生日期升序排序
SELECT * FROM student WHERE gid=1 ORDER BY birthday;
-- 15.查询 subjectid 为 3 的考试的成绩信息，用降序排序
SELECT * FROM score WHERE subjectid=3 ORDER BY score DESC;
-- 16.查询 gradeid 为 2 的课程中课时最多的课程信息
SELECT * FROM SUBJECT WHERE gradeid=2 ORDER BY studycount DESC LIMIT 0,1;
-- 17.查询北京的学生有多少个
SELECT COUNT(*) FROM student WHERE address='北京';
-- 18.查询有多少个科目学时小于 50
SELECT COUNT(*) FROM SUBJECT WHERE studycount<50;
-- 19.查询 gradeid 为 2 的阶段总课时是多少
SELECT SUM(studycount) FROM SUBJECT WHERE gradeid=2;
-- 20.查询 subjectid 为 8 的课程学生平均分
SELECT AVG(score) FROM score WHERE subjectid=8;
-- 21.查询 gradeid 为 3 的课程中最多的学时和最少的学时
SELECT MAX(studycount),MIN(studycount) FROM SUBJECT WHERE gradeid=3;
-- 22.查询每个科目有多少人次考试
SELECT subjectid, COUNT(*) FROM score GROUP BY subjectid;
-- 23.每个阶段课程的平均课时
SELECT gradeid, AVG(studycount) FROM SUBJECT GROUP BY gradeid;
-- 24.查询每个阶段的男生和女生个数（group by 两列）
SELECT gid,sex ,COUNT(*) FROM student GROUP BY sex , gid;