数据库学习笔记 - MySQL基础知识

一、数据库基础知识

1.1 Whats's 数据库

数据库（Database，DB）：将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。如：大型-银行存储的信息，小型-电话簿。
数据库管理系统（Batabase Management System，DBMS）：用来管理数据库的计算机系统。
关系型数据库（Relational Database，RDB）：目前应用最广泛的数据库。
结构化查询语言（Structured Query Language，SQL）：专门用于操作 RDB。
常见的 5 种关系型数据库管理系统（Relational Database Management System，RDBMS）：

①Oracle Database：甲骨文公司

②SQL Server：微软公司

　 ③DB2：IBM 公司

　 ④PostgreSQL：开源

　 ⑤MySQL：开源

1.2 数据库结构

服务器：用于接收并处理其它程序发出的请求的程序（软件），或者是安装此类程序的设备（计算机）。
客户端：向服务器发出请求的程序（软件），或者是安装此类程序的设备（计算机）。

数据库学习笔记 - MySQL基础知识

表（table）：类似 Excel，由行和列组成的二维表。
字段：表的列（垂直方向）。
记录：表的行（水平方向）。【注意】关系数据库必须以行为单位进行数据读写。
单元格：行列交汇处。【注意】与 Excel 不同，一个单元格只能输入一个数据。

二、SQL基础知识

SQL(Structured Query Language)是结构化查询语言的简称，它是一种数据库查询和程序涉及语言，同时也是目前使用最广泛的关系型数据库操作语言。

SQL 语句：用关键字、表名和列名等组合而成的一条语句。
3 种 SQL 语句种类：

（1）DDL（Data Definition Language，数据定义语言）：创建、删除或修改数据库以及数据库中的表等对象。

　　　　　　①CREATE：创建数据库和表等对象

　　　　　　②DROP：删除数据库和表等对象

　　　　　　③ALTER：修改数据库和表等对象

（2）DML（Data Manipulation Language，数据操作语言）：查询或修改表中的记录。

　　　　　　①SELECT：查询表中的数据

　　　　　　②INSERT：向表中插入数据

　　　　　　③UPDATE：修改表中的数据

　　　　　　④DELETE：删除表中的数据

（3）DCL（Data Control Language，数据控制语言）：确认或取消对数据库中的数据变更的执行操作，以及对用户的操作数据库中的对象权限进行设定。

　　　　　　①COMMIT：确认对数据库中的数据进行的变更

　　　　　　②ROLLBACK：取消对数据库中的数据进行的变更

　　　　　　③GRANT：赋予用户的操作权限

　　　　　　④REVOKE：取消用户的操作权限

三、创建数据库与表

1. 创建数据库

语法：CREATE DATABASE <数据库名称>

-- 创建数据库

CREATE DATABASE student;

-- 查看建库语句

show create database student;

-- 删除数据库

drop database student;

2. 创建数据表

语法：

CREATE TABLE <表名>

(

<列名1> <数据类型> <约束>,

<列名1> <数据类型> <约束>,

...

<表约束1>,

<表约束2>,

...

)
# 建立一张用来存储学生信息的表

# 字段包含学号、姓名、性别、年龄、入学日期、班级等信息

create table student（

	num int ，

	name varchar(5),

	sex char(1),

	age int,

	entersate date,

	class varchar(10),

)

3.命名规则

①只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称；

②规范要求命名以半角英文字符开头；

③名称不能重复。

4.数据类型的指定：声明该列的数据类型，是一种约束。

5.数据类型介绍

①INTEGER：整型，意味不能存储小数；

②CHAR：字符串型，如 CHAR（10）和 CHAR（100）中的括号表名该列可以存储的字符串的最大长度。它是“定长字符串”，如 CHAR(8) 表示在列中插入 'abc' 时会自动保存成 'abc '（后面5个半角空格）的形式；

③VARCHAR：字符串型，类似 CHAR，不同的是它是 “可变长字符串”，如 VARCHAR(8) 在插入 'abc'，保存的就是字符串 'abc'；

④DATE：日期类型；

　... ...

6.约束

①非空约束：NULL 是空白（无记录）的意思的关键字，NOT NULL 表示必须输入的约束。

②主键约束：主键是可以确定一行数据的列，一般通过它取特定行的数据，它是唯一的，不允许重复。

7.新增表记录

INSERT INTO `student`(`num`,`name`,`sex`,`age`,`enterdate`,`class`) VALUES (1,'Emily','女',20,'2022-9-7','Python班级1');

8.修改和删除记录

# 更新表记录

update student set enterdate='2021-9-7' where num=1;

# 删除记录

delete from student where num=1;

# 删除全部记录

delete from student;

9.更新和删除表

删除表(被删除的表无法恢复)

语法：DROP TABLE <表名>
DROP TABLE student;
更新表
添加列

语法：ALTER TABLE <表名> ADD <列名> <类型>;
ALTER TABLE student ADD E-mail VARCHAR(100); -- 在 student 中添加名为 E-mail 类型为 VARCHAR(100) 的列
删除列

语法：ALTER TABLE <表名> DROP COLUMN <列名>;
ALTER TABLE student DROP COLUMN E-mail; -- 删除 student 表中名为 E-mail 的列
修改字段名

语法：ALTER TABLE <表名>change <老字段><新字段>数据类型（修改后的长度）

四、查询数据库表

1.查询指定列：select 关键字

语法：

select <列名>, ..... -- 希望查询的列的名称

from <表名> -- 指定选取数据的表
-- 从 student中取 3 列

SELECT num, name, sex　　-- 列的顺序可以任意指定，逗号（“,”）分隔，查询结果的顺序和 SELECT 子句中的顺序相同

FROM student;

2.查询表的所有列：星号（*）

语法：

select * -- 星号（*）代表所有列

from <表名>

3.为列设定别名：AS 关键字

SELECT num AS "学号", name AS "姓名"FROM student;    -- 不用 AS 关键字也可以

4.从结果中删除重复行：DISTINCT

（1）SELECT DISTINCT class FROM student;　　

（2）DISTINCT 对 NULL 类型的处理：存在多条 NULL 值的行时，会结合为一条 NULL 数据。

5.筛选记录：WHERE

WHERE 子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件，找出只符合该条件的记录。

语法：

select <列名>,

from <表名>

where <条件表达式>
-- where子句 + 关系运算符

select * from emp where deptno = 10;

-- where子句 + 逻辑运算符：and

select * from emp where sal >1500 and sal < 3000;

-- where子句 + 逻辑运算符：or

select * from emp where deptno = 10 or deptno = 20;

-- 模糊查询-- 查询名字中带A的员工

select * from emp where ename like '%A%';

-- 任意一个字符

select * from emp where ename like '_A%';

-- 关于null的判断

select * from emp where comm is null;

select * from emp where comm is not null;

五、数据更新

1.插入数据

INSERT 语句的基本语法

--语法：--INSERT INTO <表名>(列1, 列2, ...) VALUES (值1, 值2, ...)

2.删除数据

2 种删除数据的方法：

（1）DROP TABLE 语句：删除表

（2）DELETE 语句：删除表的数据

--语法--DELETE FROM <表名>;

指定删除对象的 DELETE 语句

--语法--DELETE FROM <表名>--WHERE <条件>;

3.更新数据

UPDATE 子句的基本语法

--语法--UPDATE <表名>--    SET <列名> = <表达式>

指定条件的 UPDATE 语句

--语法--UPDATE <表名>--    SET <列名> = <表达式>--WHERE <条件>;

使用 NULL 进行更新

--示例UPDATE dbo.Shohin    SET torokubi = NULLWHERE shohin_id = '008';

多列更新

--示例UPDATE dbo.Shohin    SET hanbai_tanka = hanbai_tanka * 10,　　　　--逗号分割        shiire_tanka = shiire_tanka / 2WHERE shohin_bunrui = '厨房用具'

六、多表查询

1、笛卡尔积

概念：表示两个表中的每一行任意组合的结果

例如：

有两个表，左表有m条数据记录，x个字段，右表有n条数据记录，y个字段，则执行交叉连接后将返回m*n条数据记录，x+y个字段

2、多表查询

先确定数据要用到哪些表。

将多个表先通过笛卡尔积变成一个表。

然后去除不符合逻辑的数据（根据两个表的关系去掉）。

最后当做是一个虚拟表一样来加上条件即可。

3、自然连接（natural join）

自然连接是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性

SQL语句
Select …… from 表1 natural join 表2;

4、内连接（innner join）

内连接基本与自然连接相同，不同之处在于自然连接要求是同名属性列的比较，而内连接则不要求两属性列名相同，可以使用using或on来指定某两列字段相同的连接条件

SQL语句
Select …… from 表1 inner join 表 2 on 表1.A=表2.E;Select …… from 表1 inner join 表 2 using(A);    --using缺点：关联的字段，必须是同名的

5、外连接

外连接有三种方式：左连接、右连接、全连接

外连接除了显示匹配的数据之外，还可以显示不匹配的数据

自然连接时某一些属性不同则会导致这些元组会被舍弃，那么如何去保存这些被丢失的数据呢？外连接就解决了相应的问题。

5.1 左连接（left outer join）

左连接是在两表进行自然连接，只把左边那个表要舍弃的保留在结果集中，右表对应的列上显示NULL

SQL语句
Select …… from 表1 left outer join 表2 on 表1.C=表2.C;Select …… from 表1 left join 表2 on 表1.C=表2.C;   --outer可以省略不写

5.2 右连接（right outer join）

右连接是在两表进行自然连接，只把右边那个表要舍弃的保留在结果集中，左表对应的列上显示NULL

SQL语句
Select …… from 表1 right outer join 表2 on 表1.C=表2.C;Select …… from 表1 right join 表2 on 表1.C=表2.C;   --outer可以省略不写

5.3、全连接（full join）

全连接是在两表进行自然连接，把左表和右表要舍弃的都保留在结果集中，相对应的列上显示NULL

SQL语句
Select …… from 表1 full outer join 表2 on 表1.C=表2.C;Select …… from 表1 full join 表2 on 表1.C=表2.C;   --outer可以省略不写
注意：这个语法在mysql中不支持，在oracle中支持

要解决上述问题可以使用左连接与右连接的并集
Select …… from 表1 left  join 表2 on 表1.C=表2.Cunion -- 并集Select …… from 表1 right join 表2 on 表1.C=表2.C;
注意：mysql中对集合的操作支持比较弱，只支持并集操作，交集、差集不支持(oracle中支持)

6、练习

准备表和数据

CREATE TABLE dept (deptno int NOT NULL,dname varchar(14),loc varchar(13),PRIMARY KEY (deptno))ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;CREATE TABLE emp (empno int NOT NULL,ename varchar(10),job varchar(9),mgr int,hiredate date,sal double(7,2),comm double(7,2),deptno int,PRIMARY KEY (empno),CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno))ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;CREATE TABLE salgrade (grade int NOT NULL, losal double(7,2),  hisal double(7,2),PRIMARY KEY (grade))ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO DEPT VALUES ('10', '教研部', '北京');INSERT INTO DEPT VALUES ('20', '学工部', '上海');INSERT INTO DEPT VALUES ('30', '销售部', '深圳');INSERT INTO DEPT VALUES ('40', '财务部', '广州');INSERT INTO DEPT VALUES ('50', '董事会', '成都');INSERT INTO EMP VALUES (1001,'SMITH','文员',1013,'2021-12-17',800,NULL,20); INSERT INTO EMP VALUES (1002,'ALLEN','销售员',1006,'2021-02-20',1600,300,30);INSERT INTO EMP VALUES (1003,'WARD','销售员',1006,'2021-02-22',1250,500,30); INSERT INTO EMP VALUES (1004,'JONES','经理',1009,'2021-04-02',2975,NULL,20); INSERT INTO EMP VALUES (1005,'MARTIN','销售员',1006,'2021-09-28',1250,1400,30); INSERT INTO EMP VALUES (1006,'BLAKE','经理',1009,'2021-05-01',2850,NULL,30); INSERT INTO EMP VALUES (1007,'CLARK','经理',1009,'2021-06-09',2450,NULL,10); INSERT INTO EMP VALUES (1008,'SCOTT','分析师',1004,'2021-07-13',3000,NULL,20); INSERT INTO EMP VALUES (1009,'KING','董事长',NULL,'2021-11-07',5000,NULL,10); INSERT INTO EMP VALUES (1010,'TURNER','销售员',1006,'2021-09-08',1500,0,30); INSERT INTO EMP VALUES (1011,'ADAMS','文员',1008,'2021-07-13',1100,NULL,20); INSERT INTO EMP VALUES (1012,'JAMES','文员',1008,'2021-12-03',950,NULL,30); INSERT INTO EMP VALUES (1013,'FORD','分析师',1004,'2021-12-03',3000,NULL,20); INSERT INTO EMP VALUES (1014,'MILLER','文员',1008,'2021-01-23',1300,NULL,10);INSERT INTO EMP VALUES (1015,'Emily','保洁员',1001,'2021-01-23',1300,NULL,10);INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO SALGRADE VALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999);

查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数

SELECT d.deptno,d.dname,d.loc,e1.`count(*)`FROM dept d INNER JOIN (SELECT deptno,COUNT(*) FROM emp GROUP BY deptno) e1ON d.deptno=e1.deptno;

列出所有员工的姓名以及直接上级

SELECT e1.ename,e2.ename 上级 FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empno;

列出工资比 ALLEN 高的所有员工

SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename = 'ALLEN');

列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称

SELECT e1.empno, e1.ename, d.dname from emp e1 left join emp e2 on e1.mgr = e2.empnoleft join dept d on e1.empno = d.empno where e1.hiredate < e2.hiredata;SELECT e1.empno, e1.ename, d.dname FROM emp e1, emp e2, dept d WHERE e1.mgr=e2.empno AND e1.hiredate<e2.hiredate AND e1.deptno=d.deptno;

列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SELECT * from emp e RIGHT JOIN dept d on e.deptno = d.deptno;

秒客网

数据库学习笔记 - MySQL基础知识

一、数据库基础知识

1.1 Whats's 数据库

1.2 数据库结构

二、SQL基础知识

三、创建数据库与表

1. 创建数据库

2. 创建数据表

3.命名规则

4.数据类型的指定：声明该列的数据类型，是一种约束。

5.数据类型介绍

6.约束

7.新增表记录

8.修改和删除记录

9.更新和删除表

四、查询数据库表

1.查询指定列：select 关键字

2.查询表的所有列：星号（*）

3.为列设定别名：AS 关键字

4.从结果中删除重复行：DISTINCT

5.筛选记录：WHERE

五、数据更新

1.插入数据

2.删除数据

3.更新数据

六、多表查询

1、笛卡尔积

2、多表查询

3、自然连接（natural join）

4、内连接（innner join）

5、外连接

5.1 左连接（left outer join）

5.2 右连接（right outer join）

5.3、全连接（full join）

6、练习

相关文章