MySQL-Day02 数据库以及数据表的基本操作

最新推荐文章于 2024-07-04 10:01:20 发布

Golang_HZ

最新推荐文章于 2024-07-04 10:01:20 发布

阅读量2k

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_51612062/article/details/127983634

版权

MySQL 专栏收录该内容

4 篇文章 2 订阅

订阅专栏

面试题：InnoDB和MyISAM的区别？

5. modify 修改类型，添加非空默认值等，同时还可以修改

一、数据库的基本操作

1.1 创建数据库

MySQL 安装完成之后，将会在其data目录下自动创建几个必须的数据库，可以使用

show databases;
-- 注意sql语法中结束符是;

语句来列出 MySQL 数据库管理系统的数据库列表。输入语句后运行结果如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

MySQL四个自带的数据库为mysql、information_schema、performance_schema、sys。

information_schema数据库：提供访问数据库元素的方式
performance_schema数据库：此数据库为数据库性能优化提供重要的参考信息
sys数据库：所有的数据源来自 performance_schema
mysql数据库：该数据库也是个核心数据库，存储用户的权限信息与帮助信息。不可以随意修改

如何自己创建库呢？可以使用sql语句创建数据库：

语法1：

create database 数据库名;

注：如果创建的数据库名已存在会报错。

语法2：

create database if not exists 数据库名;

注：如果数据库已存在则不创建，否则创建。不会报错。

可以使用命令查看某个库的定义信息：

show create database 数据库名;

将查询到如下信息

mysql> show create database mydb;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.2 删除数据库

删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将一同被删除。删除数据库语句和创建数据库的命令相似，MySQL中删除数据库的基本语法格式为：

语法1：

drop database 数据库名;
-- 注意：若指定的数据库不存在，则删除出错；

语法2：

drop database if exists 数据库名;
-- 注意：有这个库就删除，没有就不删，系统不会报错。

注意：使用DROP DATABASE命令时要非常谨慎，在执行该命令时，MySQL 不会给出任何提醒确认信息。用DROP DATABASE声明删除数据库后，数据库中存储的所有数据表和数据也将一同被删除，而且不能恢复。

1.3 使用创建的数据库

数据表属于数据库，在创建数据表之前，应该使用语句“USE ”指定操作是在哪个数据库中进行，如果没有选择数据库，就会抛出“No database selected”的错误。

所以在操作数据库之前我们需要先使用该数据库：

语法：use 数据库名;

注意：使用了系统中不存在的数据库会报错。

查看当前使用的数据库的名称：

语法：select database();

1.4 扩展知识：InnoDB表

从MySQL 8.0开始，系统表全部换成事务型的InnoDB表，默认的 MySQL实例将不包含任何MyISAM 表，除非手动创建MyISAM表。

SELECT DISTINCT ENGINE FROM information_schema.`TABLES`;

面试题：InnoDB和MyISAM的区别？

区别：

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

如何选择：

1. 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

二、数据表的基本操作

在操作某个数据表之前，应该使用语句“USE 数据库名;”指定操作是在哪个数据库中进行。

数据表由行和列构成，行被称为记录。是组织数据的单位。列被称为字段，每一列表示记录的一个属性，有相应的描述信息，如数据类型、数据宽度等。

2.1 创建数据表

所谓创建数据表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程。

创建数据表的语句为CREATE TABLE，语法规则如下：

create table 表名(
    字段1 数据类型 [约束] [默认值],
    字段2 数据类型 [约束] [默认值],
    ......
    字段n 数据类型 [约束] [默认值]
);

例：创建一个学生表（student）

-- 创建表
create table student(
	sid int,
	sname varchar(50),
	ssex char(3),
	sscore float,
	sborn date
);

注：在创建表的过程中字段的最后一个字段不能加逗号或是分号；不加任何符号。

MySQL数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型：

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

注意：decimal(6,2) 小数点两位 .占据一位 xxx.xx

日期和时间类型：

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

字符串类型：

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：

如果保存图片可以使用blob数据类型
如果保存文件可以用text数据类型。
char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
一个汉字在utf8下占据3个字节

char 与 varchar 区别是什么？

char是固定长度
varchar是可变长度

例如：char(8) varchar(8)，如果此时需要保存字母a，char(8) 此时总长度就是8，varchar(8) 总长度是1，最多能放8个长度的字符。

2.2 表约束

数据的完整性是指数据的可靠性和准确性，包括实体完整性、引用完整性、域完整性和自定义完整性等。

约束是在表上强制执行的一些数据校验规则，被插入、修改或删除的数据必须符合在相关字段上设置的这些约束条件。

四种完整性约束：

实体完整性：唯一约束（UNIQUE）、主键约束（PRIMARY KEY）、标识列
域完完整性：限制数据类型、默认值（DEFAULT）、非空约束（NOT NULL）、检查（CHECK，mysql不支持）
引用完整性：外键约束（FOREIGN KEY）
自定义完整性：过程，触发器等

2.2.1 主键约束

主键，又称主码，是表中一列或多列的组合。

主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型：单字段主键和多字段联合主键。添加主键会自动给该列添加索引。

单字段主键：

主键由一个字段组成，SQL语句格式分为以下两种情况。

1. 定义列的同时定义主键，语法如下：

格式1：字段名字段类型 primary key

格式2：primary key(字段名)

格式3：constraint 主键名 primary key(字段名)

示例：

-- auto_increment 自动增长，在mysql数据库中只有主键能自动增长
create table student(
    sid int PRIMARY KEY auto_increment,
    sname varchar(30),
    sphone varchar(11) unique
);

或

create table student(
	sid int,
	sname varchar(30),
	sphone varchar(11) unique,
	PRIMARY KEY(sid)
);

或

create table student(
	sid int,
	sname varchar(30),
	sphone varchar(11) unique,
	constraint pk_sid PRIMARY KEY(sid)
);

2. 定义表之后指定主键

语法1：alter table 表名 add constraint 主键名 primary key(字段);

语法2：alter table 表名 modify 字段名数据类型 primary key;

示例：建立好一个student表

create table student(
	sid int,
	sname varchar(30),
	sphone varchar(11)
);

为 sid 添加主键

-- 方法1
alter table student add constraint pk_sid primary key(sid);
-- 删除主键
alter table student drop primary key;
-- 方法2
alter table student modify sid int primary key;

多字段联合主键：

语法：primary key(字段1,字段2,...字段n)

主键选取原则：最少更改原则以及最少列原则。

主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。
主键应该是单列的，以便提高连接和筛选操作的效率。
永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。
主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
主键应当有计算机自动生成

我们在建立数据库的时候，需要为每张表指定一个主键，主键代表记录的唯一性，主键的值是非空且唯一的。一个表只能有一个主键，但是这个主键不一定只有一列，可以由多列组成

2.2.2 非空约束

添加非空约束和默认值的语法：

1. 创建表的同时添加：
字段名数据类型 not null,
字段名数据类型 not null defualt 默认值,
字段名数据类型 defualt 默认值,

2. 如果表已经创建，添加非空约束和默认值
alter table 表名 modify 字段名数据类型 not null;
alter table 表名 modify 字段名数据类型 not null default 默认值;
alter table 表名 modify 字段名数据类型 default 默认值;

create table student(
sid int primary key auto_increment,
sname varchar(50) not null default '无名氏',
ssex char(3) default '男'
);

或

create table student(
sid int primary key auto_increment,
sname varchar(50),
ssex char(3)
);

alter table student modify sname varchar(50) not null default '无名氏';

注意：数据表中若有一条记录，值为null，想添加该字段非空，此时添加不上

2.2.3 唯一约束

添加唯一约束的语法：

1. 创建表的同时添加：
字段名数据类型 unique,

2. 如果表已经创建，添加非空约束和默认值

alter table 表名 modify 字段名数据类型 unique;

alter table 表名 add constraint 唯一约束名 unique (字段);

示例：

create table student(
sid int primary key auto_increment,
sname varchar(50) not null default '无名氏',
ssex char(3) default '男',
sphone varchar(50) unique
);

alter table student add constraint uk_stu_phone unique(sphone);

2.2.4 外键约束

创建外键约束的语法：

1. 创建表的同时添加：
foreign key(当前表字段名) references 关联表(关联字段名)

2. 如果表已经创建，添加非空约束和默认值

alter table 子表 add constraint 约束名 foreign key(外键) references 主表(主键)

MySQL数据库外键的创建，需要满足以下四个条件，否则会被MySQL数据库拒绝：

1、创建外键的表和列存在
2、组成外键的列存在索引
3、必须指定数据表的引擎为InnoDB
4、外键字段和关联字段，数据类型必须一致

两表关联关系：一对一、一对多、多对多关系

一对一

如身份证和人的关系，一个人有一张身份证，一个身份证对应一个人

人preson表
*/
create table person(
	p_id int primary key,
	p_name varchar(50),
	p_sex char(3) default '男',
	p_tel varchar(50)
);

/*
身份证信息表idcard
*/
create table idcard(
	c_id int primary key,
	c_number varchar(50),
	c_address varchar(200),
	c_begin date,
	c_end date
);

alter table idcard add constraint fk_idcard_person foreign key(c_id) references person(p_id);

一对多

一个部门下有多个员工，一个员工从属一个部门

        部门        员工

1               N

1                1

部门和员工是一对多的关系，员工和部门的多对一的关系

部门表称为父表，员工表称为子表【父子关系】

部门表称为主表，员工表称为从表【主从关系】

create table employee(
	emp_id int primary key auto_increment,
	emp_name varchar(50) not null,
	emp_sex char(3) default '男',
	emp_salary float not null default 3000,
	emp_phone varchar(50),
	emp_birth date,
	dept_id int
);

/*
部门表
*/
create table dept(
	dept_id int primary key auto_increment,
	dept_name varchar(50),
	dept_num int,
	dept_desc varchar(200)
);

-- 设置外键
alter table employee add constraint fk_emp_dept foreign key(dept_id) references dept(dept_id);

多对多

游戏账号和角色的关系就是一种多对多的关系

一个游戏账号有多个角色，一个角色有多个玩家玩

-- 账号表
create table account(
	acc_id int primary key,
	acc_name varchar(50),
	acc_pwd varchar(50),
	acc_status varchar(30),
	acc_logintime date
);
-- 角色表
create table roles(
	role_id int primary key,
	role_name varchar(50),
	role_desc varchar(200)
);
-- 关系表
create table userrole(
	acc_id int,
	role_id int,
	foreign key (acc_id) references account(acc_id),
	foreign key (role_id) references roles(role_id)
);

alter table userrole add constraint pk_userrole primary key(acc_id, role_id);

2.3 查看数据表

1. 查看当前数据库下所有的表名：

语法：show tables;

2. 查看某个表的建表信息：

语法：show create table 表名;

mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `sid` int NOT NULL AUTO_INCREMENT,
  `sname` varchar(50) NOT NULL,
  `ssex` char(3) DEFAULT '男',
  `sscore` float DEFAULT NULL,
  `sphone` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  UNIQUE KEY `sphone` (`sphone`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3. 查看表的详细信息：

语法：desc 表名;

注：desc当前数据库中不存在的表名时，会报错。

mysql> desc student;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| sid    | int         | NO   | PRI | NULL    | auto_increment |
| sname  | varchar(50) | NO   |     | NULL    |                |
| ssex   | char(3)     | YES  |     | 男      |                |
| sscore | float       | YES  |     | NULL    |                |
| sphone | varchar(50) | YES  | UNI | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

2.4 删除数据表

语法1：

drop table 数据表名;
-- 注意：若指定的数据表不存在，则删除出错；

语法2：

drop table if exists 数据表名;
-- 注意：有这个数据表就删除，没有就不删，系统不会报错。

注意：若有外键关联，删除会报错，需先删除外键，或直接先删除有外键关联的表

删除外键约束语法：alter table 表名 drop foreign key 外键名;

2.5 修改数据表

1. 修改表名字rename

语法：alter table 表名 rename to 新表名；

示例：

-- 将student表改名为stus
alter table student rename to stus;

2. 修改字段名change

语法：alter table 表名 change 旧字段名新字段名新字段数据类型;

示例：

-- 修改stus表中phone字段的名字为sphone
alter table stus change phone sphone varchar(30);

3. 添加字段add

语法：alter table 表名 add 字段名字段数据类型【first / after 指定字段名】;

说明：【】中语句为可选语句，为该新添加字段设置位置

① first：表示添加在第一行；

② after 指定字段名：在指定列后面插入新的列

示例：

-- 增加字段名favoriate_activity，数据类型为VARCHAR(100)。
alter table employees add favoriate_activity varchar(100);

4. 删除字段drop

语法：alter table 表名 drop 字段名;

示例：

alter table student rename to stus;

5. modify 修改类型，添加非空默认值等，同时还可以修改

语法：alter table 表名 modify 字段名字段类型【约束(非空、默认值、UNIQUE等)】【after 其他字段名】;

示例：

-- 修改sex字段，数据类型为CHAR(1)，非空约束, 默认值为'男', 在name字段之后。
alter table employees_info modify sex char(1) not null default '男' after name;

6. 修改引擎

语法：alter table 表名 engine = InnDB|MyISAM

查看存储引擎：show create table 表名;

示例：

-- 修改数据表的存储引擎为MyISAM。
alter table customers_info engine=MyISAM;

使用场景：在mysql中，建立外键关联，关联表必须是使用innodb，将engine修改为innodb即可

7. 删除主键

语法：alter table 表名 drop primary key；

说明：因为主键一个表只有一个，直接drop

示例：

-- 删除stus表的主键
alter table stus drop primary key;

8. 删除外键

语法：alter table 表名 drop foreign key 外键名;

说明：因为外键可能不止一个，所以需要加上外键名

示例：

-- 删除orders表的外键约束，然后删除表customers。
alter table orders drop foreign key fk_order_id;

9. 删除自动增长

语法：alter table stus modify 自增长字段名数据类型;

示例：

-- 删除sid字段的自增
alter table student modify sid int;

10. 删除唯一约束

语法：alter table tabname drop index 唯一约束的字段名;

示例：

-- 删除sphone字段的唯一约束
alter table student drop index sphone;

Golang_HZ

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录