MySQL学习笔记

MySQL数据库基础

一.数据库基础知识

1.数据：

​ 所谓数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。现在计算机存储和处理的数据范围十分广泛，而描述这些数据的符号也变得越来越复杂了。

2.数据库：

​ 所谓数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。现在计算机存储和处理的数据范围十分广泛，而描述这些数据的符号也变得越来越复杂了。

3.数据库管理系统

​ 所谓数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。现在计算机存储和处理的数据范围十分广泛，而描述这些数据的符号也变得越来越复杂了。

4.数据库应用程序

​ 数据库应用程序（Database Application System，DBAS）是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序，如学生管理系统、人事管理系统、图书管理系统等。

5. 数据库管理员

​ 数据库管理员（Database Administrator，DBA）是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护。

6.最终用户

​ 最终用户（User）指的是数据库应用程序的使用者。用户面向的是数据库应用程序（通过应用程序操作数据），并不会直接与数据库打交道。

7.数据库系统

​ 数据库系统（Database System，DBS）一般是由数据库、数据库管理系统、数据库应用程序、数据库管理员和最终用户构成。其中DBMS是数据库系统的基础和核心。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6UeTjnKT-1639190493020)(MySQL%E6%95%B0%E6%8D%AE%E5%BA%93%E5%9F%BA%E7%A1%80(2)].assets/image-20211207110732936.png)

---

二：数据库简介

1.数据库的连接

• 连接mysql

  • 启动MySQL服务
    • net start mysql
  • 连接mysql
    • Mysql -h127.0.0.1 -uroot -p
  • 退出MySQL
    • exit
    • quit
  • 关闭MySQL
    • net stop mysql

• mac电脑连接mysql

  • alias mysql=/usr/local/mysql/bin/mysql
  • alias mysqladmin=/usr/local/mysql/bin/mysqladmin
  • sudo /usr/local/mysql/support-files/mysql.server start

---

2.数据库的相关操作

• 创建数据库
  • create database mydb character set utf8mb4;
• 显示所有数据库
  • show databases;
• 切换数据库
  • use mydb;
• 显示数据库下的所有数据表
  • show tables;
• 删除数据库
  • Drop database mydb;

---

3.SQL语言入门

SQL语言分为五个部分:

• 数据查询语言（Data Query Language,DQL）:DQL主要用于数据的查询，其基本结构是使用select子句，from子句和where子句的组合来查询一条或多条数据

• 数据操作语言（Data Manipulation Language，DML）：DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：

  • INSERT：增加数据

  • UPDATE：修改数据

  • DELETE：删除数据

• 数据定义语言（Data Definition Language，DDL）：DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：

  • CREATE：创建数据库对象

  • ALTER：修改数据库对象

  • DROP：删除数据库对象

• 数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问数据库的权限，其主要包括：

  • GRANT：授予用户某种权限
  • REVOKE：回收授予的某种权限

• 事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。其主要包括：

  • START TRANSACTION：开启事务
  • COMMIT：提交事务
  • ROLLBACK：回滚事务
  • SET TRANSACTION：设置事务的属性

注意：

DML和DDL的不同 

数据操纵语言（insert、update、delete）针对表中的数据                

而数据定义语言（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure、触发器trigger 

---

三、DDL和DML

1.创建数据库表

• 认识数据表:

  • 表（Table）是数据库中数据存储最常见和最简单的一种形式，数据库可以将复杂的数据结构用较为简单的二维表来表示。二维表是由行和列组成的，分别都包含着数据
  • 每个表都是由若干行和列组成的，在数据库中表中的行被称为记录，表中的列被称为是这些记录的字段。
  • 记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据是指一条完整的记录。
  • 字段是表里的一列。

---

示例1：创建数据表student

要求：

​ 1.建立一张用来存储学生信息的表

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

​ 3、学号是主键 = 不能为空、唯一

​ 4、姓名不能为空

​ 5、性别默认是男

​ 6、Email唯一

-- 条件2
create table student(
	sno int(6),
	sname varchar(12),
	sex char(2),
	age int,
	enterdate date,
	clazzname varchar(12),
	email varchar(20)
)

• 查看表的结构
  • desc student
• 查看完整的建表语句
  • show create table student
• 删除数据库表
  • drop table 表名；

---

2.数据库表列类型

• 整数类型

整数类型	大小	表数范围（有符号）	表数范围（无符号）	作用
TINYINT	1字节	(-128，127)	(0，255)	小整数值
SMALLINT	2字节	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3字节	(-8388608，8388607)	(0，16777215)	大整数值
INT/INTEGER	4字节	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8字节	(-9233372036854775808，9223372036854775807)	(0，18446744073709551615)	极大整数值

主键自增：不使用序列，通过auto_increment，要求是整数类型

• 浮点数类型

浮点数类型	大小	作用
FLOAT	4字节	单精度浮点数
DOUBLE	8字节	双精度浮点数

需要注意的是与整数类型不一样的是，浮点数类型的宽度不会自动扩充

• 字符串类型

字符串类型	大小	描述
CHAR(M)	0~255字符	允许长度0~M个字符的定长字符串
VARCHAR(M)	0~65535字符	允许长度0~M个字符的变长字符串
BINARY(M)	0~255字节	允许长度0~M个字节的定长二进制字符串
VARBINARY(M)	0~65535字节	允许长度0~M个字节的变长二进制字符串
TINYBLOB	0~255字节	二进制形式的短文本数据（长度为不超过255个字符）
TINYTEXT	0~255字节	短文本数据
BLOB	binary large object	二进制形式的长文本数据
TEXT		长文本数据
MEDIUMBLOB	0~16777215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0~16777215字节	中等长度文本数据
LOGNGBLOB	0~4294967295字节	二进制形式的极大文本数据
LONGTEXT	0~4294967295字节	极大文本数据

​ CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。

• 日期和时间类型

类型	格式	取值范围	0值
TIME	‘HH:MM:SS’	(‘-838:59:59’, ‘838:59:59’)	‘00:00:00’
DATE	‘YYYY-MM-DD’	(‘1000-01-01’, ‘9999-12-31’)	‘0000-00-00’
YEAR	YYYY	(1901, 2155), 0000	0000
DATETIME	‘YYYY-MM-DD HH:MM:SS’	(‘1000-01-01 00:00:00’, ‘9999-12-31 23:59:59’)	‘0000-00-00 00:00:00’
TIMESTAMP 时间戳	‘YYYY-MM-DD HH:MM:SS’	(‘1970-01-01 00:00:01’ UTC, ‘2038-01-19 03:14:07’ UTC)	‘0000-00-00 00:00:00’

​ TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：

​ (1) 数据的取值范围不同，TIMESTEMP类型的取值范围更小。

​ (2) 如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值，MySQL会自动将该字段赋值为系统当前的日期与时间。

​ (3) TIMESTEMP类型还可以使用CURRENT_TIMESTAMP来获取系统当前时间。

​ (4) TIMESTEMP类型有一个很大的特点，那就是时间是根据时区来显示的。例如，在东八区插入的TIMESTEMP数据为2017-07-11 16:43:25，在东七区显示时，时间部分就变成了15:43:25，在东九区显示时，时间部分就变成了17:43:25。

---

3.添加、更新、删除数据

• 添加数据
  • insert into 表名（列名） values(值)
  • 插入多行数据：insert into 表名（列名） values(值 )，（值）

insert into student values(1,'张三','男',34,'1999-10-10','武当一班','120@qq.com');
insert into student values(2,'张三','男',34,'1999-10-10','武当一班','120@qq.com'),(1,'张三','男',34,'1999-10-10','武当一班','120@qq.com'),(3,'张三','男',34,'1999-10-10','武当一班','120@qq.com');

• 更新数据

  • update 表名 set 字段名=新值 where 条件

  update student set sno=4 where age=134;
  

• 删除数据

  • delete from 表名 where 条件

delete from student where sno = 1;

• 注意事项

  • char varchar 是字符的个数，不是字节的个数，可以使用binary，varbinary表示定长和不定长的字节个数。

  • int 宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节

  • 如何写入当前的时间 now() sysdate() CURRENT_DATE()

  • 时间的方式多样 ‘1256-12-23’ “1256/12/23” “1256.12.23”

  • 字符串不区分单引号和双引号

  • delete语句中from不可少

---

4.修改删除数据库表

• 增加列数（不是数据）
  • 增加一列：alter table student add 列名 类型 约束（默认添加到最后一列）
  • 增加一列：alter table student add 列名 类型 约束 first（添加到第一列）
  • 增加一列到指定位置:alter table student add 列名 类型 约束 after 列名（添加到该列名之前）

alter table student add score double(4,1);
alter table student add score double(4,1) first;
alter table student add score double(4,1) after age;

• 删除列数

  • 删除一列：alter table 表名 drop 字段；

  alter table student drop score;
  

• 修改列信息

  • 修改一列 列名不变：alter table 表名 modify 字段名 新类型 约束
  • 修改一列 类名改变: alter table 表名 change 字段名 新名 新类型 约束

  alter table student modify score double(5,2)
  alter table student change score score1 double(4,1);
  

• 修改表名

  • Alter table 表名 rename to 新表名
  • alter table 表名 rename 新表名

  alter table student rename to stu;
  alter table stu rename student;
  

---

5.表的完整性约束

​ 为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。

• MySQL中主要支持六种完整性约束，如表所示。 其中Check约束是MySQL8中提供的支持。

约束条件	约束描述
PRIMARY KEY	主键约束，约束字段的值可唯一地标识对应的记录
NOT NULL	非空约束，约束字段的值不能为空
UNIQUE	唯一约束，约束字段的值是唯一的
CHECK	检查约束，限制某个字段的取值范围
DEFAULT	默认值约束，约束字段的默认值
AUTO_INCREMENT	自动增加约束，约束字段的值自动递增
FOREIGN KEY	外键约束，约束表与表之间的关系

• 约束从作用上可以分为两类：

  • 表级约束：可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称；

  • 列级约束：包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名；

• 列级约束

  create table student(
  	sno int(4) primary key auto_increment,
  	name varchar(12) not null,
  	sex char(1) default '男' check(sex='男' or sex='女'),
  	age int(3) check(age>=0 and age<=100),
  	enterdate date,
  	classname varchar(10),
  	email varchar(20) unique
  );
  

• 表级约束

  create table student(
  	sno int(10),
  	name varchar(12) not null,
  	sex char(2) default '男',
  	age int(2),
  	enterdate date,
  	classname varchar(20),
  	email varchar(20),
  	constraint pk_stu primary key(sno),
  	constraint uk_stu_email unique(email),
  	constraint ck_stu_sex check(sex in ('男','女')),
  	constraint ck_stu_age check(age>=0 and age<=100)
  );
  -- 为主键添加自增约束
  alter table student modify sno int(10) auto_increment;
  

---

6.外键约束

​ 外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表

• 创建一个班级表

  create table class(
  	cno int(4) auto_increment,
  	cname varchar(12) not null,
  	room varchar(4),
  	primary key(cno)
  );
  

• 创建一个学生表外键关联

  create table student(
  	sno int(6) primary key auto_increment,
  	name varchar(12),
  	sex char(2),
  	age int(2),
  	classno int(4),
  	constraint fk_stu_classsno foreign key (classno) references class(cno)
  );
  -- 查看表结构
  desc student;
  -- 外键策略
  alter table student add constraint fk_stu_classno foreign key (classno) references class(cno) on delete set null on update cascade;
  

• 外键策略

• 如果想删除1班，请手动的先对1班的学生进行处理（删除或者清空外键）

  • update student set classno = null where classno=1

• 希望在更新班级编号的时候，可以直接更新学生的班级编号；希望在删除某个班级的时候，清空学生的班级编号

  • 首先删除外键：alter table student drop foreign key fk_stu_classsno
  • 添加外键是采用外键策略 cascade（进行相同操作）
    • alter table student add constraint fk_stu_classno foreign key (classno) references class(cno) on delete set null on update cascade;

---

7.DML的更多操作

• 快速创建数据表,结构和数据完全相同

  create table class2
  as
  select * from class
  

• 快速创建数据库表，只要结构相同，不要数据

  create table class3
  as
  select * from class where 1=2;
  

• 快速创建数据库表，只要部分结构

  create table class4
  as
  select cno,cname from class; 
  

• 清除所有数据

  delete from class4;
  

• 删除所有数据:更推荐使用的方式，效率高

  truncate