MySQL数据库-库表操作

一、SQL语句基础

1.SQL简介

SQL：结构化查询语言（Structured Query Language），在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。使用SQL语句，程序员和数据库管理员可以完成如下任务：

（1）改变数据库的结构

（2）更改系统的安全设置

（3）增加用户对数据库或表的许可权限

（4）在数据库中检索需要的信息

（5）对数据库的信息进行更新

2.SQL语句分类

MySQL致力于支持全套ANSI/ISO SQL标准。在MySQL数据库中，SQL语句主要可以划分为以下几类

（1）DDL(Data Definition Language):数据定义语言，定义对数据库对象(库、表、列、索引)的操作， 如：CREATE、DROP、ALTER、RENAME、 TRUNCATE等。

（2）DML(Data Manipulation Language): 数据操作语言，定义对数据库记录的操作，如：INSERT、 DELETE、UPDATE、SELECT等。

（3）DCL(Data Control Language): 数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级 别，如：GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等，Transaction Control：事务控制。

3.SQL语句的书写规范

（1）在数据库系统中，SQL语句不区分大小写（建议用大写），但字符串常量区分大小写

（2）SQL语句可单行或多行书写，以 ; 结尾

（3）关键词不能跨多行或简写

（4）用空格和缩进来提高语句的可读性

（5）字句通常位于独立行，便于编辑，提高可读性。

（6）注释：-- 和 # 为单行注释， /* */ 和 // 为多行注释

二、数据库操作

1.数据库的登入及退出

登录格式：

 mysql  -u  用户名  -h  服务器的主机地址  -p密码  -A

• -u 后面跟登录数据库的用户名
• -h 后面的参数为服务器的主机地址
• -p 后面是用户登录密码，注意：-p和密码之间没有空格，若出现空格系统不会把其当成密码对待
• -A 不预读数据库信息，加上该参数则不预读数据库中的表等信息，不会有表名及列名的补全，但读取速度会加快。

登出格式：（1）exit        （2）quit        （3）\q

2.查看数据库

show  databases  [like 条件];

（1）普通查看（查看所有数据库）：show databases;

（2）使用条件查看（查看名字为sys的数据库）：show databases like 'sys';

（3）带有通配符的条件查看：

• %：匹配任意零个或多个字符。（如查询s开头的数据库：show databases like 's%' ）
• _：匹配任意单个字符。

3.创建数据库

create  database  数据库名

（1）创建数据库testdb：create databases testdb;

（2）查看创建数据库语句：show create database 数据库名;

4.切换数据库

（1）查看当前使用的数据库：select database();

（2）选择数据库：use 数据库名;

5.查看当前用户

select  user();

6.删除数据库

drop databases 数据库名;

（1）删除数据库testdb：drop database testdb;

三、数据库对象

1.数据库组成

2.数据库对象的命名规则

（1）命名使用具有意义的英文词汇，词汇中间以下划线分隔。

（2）名称由字母、数字、# 、_ 、$组成，必须以英文字母开头。

（3）不要使用MySQL的保留字。

（4）所有数据库对象使用小写字母，实际上MySQL中是可以设置大小写是否敏感的，为了保证统一性应使用小写表示。

（5）在同一个数据库对象集合内对象不能同名。

3.生产中对象命名规范

数据库：

（1）数据库命名尽量不超过30个字符。

（2）数据库命名一般为项目名称+代表库含义的简写，比如IM项目的工作数据库，可以是 im_flow。

（3）命名应使用小写。

表：

（1）常规表：以t_开头，t代表table的意思，命名规则即 t + 模块（包含模块含义的简写） + 表（包含表含义的简写），比如用户模块的教育信息表：t_user_eduinfo。

（2）临时表：temp前缀+模块+表+日期后缀：temp_user_eduinfo_20240520。

（3）备份表（用于保存和归档历史数据或者作为灾备恢复的数据）：bak前缀+模块+表+日期后缀：bak_user_eduinfo_20231219。

（4）同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义。

（5）多个单词以下划线_分割。

（6）常规表明尽量不超过30个字符，temp表和bak表视情况而定，也尽量简短为宜。

四、表的基本操作

1.数据类型

（1）整型

（2）浮点型

（3）文本型

（4）date类型

2.创建表

create  table  表名  (列名1  列类型1  约束, 列名2  列类型2  约束, ....);

（1）创建一个职工信息表（包含id、name、salary）：create table t_employee_info(id int, name varchar(25), salary float);

3.查看表

（1）show语句：显示当前数据库中已有的数据表。

show  tables  [from  数据库名]; 

注：show tables默认查看当前使用数据库的表

（2）describe语句：查看数据表中各列的信息。

{describe/desc}  表名  [列名];   #一般使用 desc 表名

show  columns  from  表名称;

字段意义分析：

• Field :字段名称
• type：字段类型
• null：是否允许为空
• key：索引类型
• default：默认值
• extra：填充

（3）使用\G可以查看更全面建表时的定义信息。

show  create  table  表名\G

4.删除表

drop  table  [if  exists]  表名;

注意：

• 用户必须拥有执行 drop table 命令的权限，否则数据表不会被删除
• 推荐使用if exists字句，即先判断是否存在，存在则删除

5.修改表

（1）更改表名

alter  table  表名  rename  新表名;

rename  table  表名  to  新表名;

（2）增加新列

alter  table  表名  add  新列名  列类型;    #增加到最后一列

alter  table  表名  add  新列名  列类型  after  指定列列名;    #增加到指定列前一列

alter  table  表名  add  新列名  列类型  first;    #增加到第一列

（3）删除列

alter  table  表名  drop  列名;

（4）修改列名

alter  table  表名  change  旧列名  新列名  列类型;

（5）修改列类型

alter  table  表名  modify  列名  列类型;

（6）修改列位置

alter  table  表名  modify  列名  列类型  after  某列;

6.复制表的结构

方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中。

create  table  新表名  like  原表;

方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。

create  table  新表名  select * from 原表;

方法三：若已经存在一张结构一致的表，复制数据。

insert  into  新表  select  *  from  原表;

7.删除表的注意事项

• 删除数据库表之前，一定要备份数据，以免数据丢失。
• 在删除数据库表之前，要确保该表没有被其他的表所引用，否则可能会破坏外键约束。
• 在删除数据库表之前，要确保该表的所有索引都已经被删除。如果还存在索引，可能会导致删除失败。
• 在删除数据库表之前，要确保该表的所有相关程序已经停止运行，以免影响其他程序的正常运行。

五、表的约束

1.概念

约束是在表上强制执行的数据校验规则，本质上是Mysql通过限制用户操作的方式,来达到维 护数据本身安全及数据完整性的一套方案。

数据的完整性要从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录。
• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”。
• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门。
• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理 的工资不得高于本部门职工的平均工资的5倍。

2.语法

（1）列级约束：在定义列的同时定义约束

create  table  表名  (列名  类型  约束);

（2）表级约束：在定义了所有列之后定义的约束

create  table  表名  constraint  约束名  约束类型  (列信息);

（3）创建表之后添加约束

alter  table  表名  add  constraint  约束名  约束类型(要约束的列名);

注意：

约束名推荐采用：表名_列名_约束类型简介

3.非空约束

作用：限定某个字段/某列的值不允许为空

值：null（默认值）和not null（不为空）

4.默认值约束

作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

值：default '默认值'

5.列描述

作用：列描述没有实际含义,专门用来描述字段,会根据表创建语句保存,供操作者来查看该列的含义，相当于 C/C++的注释。

值：comment ’注释值‘

6.主键约束

作用：数据库存储数据不是唯一的目的，还要考虑到提取数据，一般要求数据库表中的记录要有一个特定的唯一标识，来表示唯一性,这个唯一的特定标识就是主键，类似于序号学号这样的唯一标识,可以根据主键来唯一地筛选出一条记录。

值：primary key

注意：当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了，需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会 破坏数据的完整性。

7.主键自增长

作用：给主键添加自增长的数值，当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键

值：auto_increment

注意：

• 自增长字段必须是整数,自增长字段可以不设置初始值,默认从1开始递增
• 被自增长的字段必须作为主键或者其他具有唯一性的键使用,(必须保证这一列字段具有唯一性的字段)
• 自增长字段也可以插入数据,只要不与已有数据重复即可,之后新增数据会从最大值开始递增
• 任何一个字段要做自增长,前提是本身是一个索引（key一栏有值）
• 一张表当中最多只能有一个自增长的列
• 约束的字段必须具备 NOT NULL 属性

8.唯一性约束

作用：限制某个字段/某列的值不能重复。

值：unique

唯一键和主键的关系：

• 主键更多的是标识唯一性
• 唯一键更多的是保证在业务上,不要和别的信息出现重复
• 主键只能有一个,唯一键能设置多个
• 主键用来查找,唯一键用来保证数据的完整性
• 主键和唯一键不是对立关系而是补充关系,目的是让数据库创建出一个表具有非常强的约束,彻底杜绝 用户的误插入，在一张表当中,可以有一个主键,也可以同时具有唯一键, 它们不是用来对比的,只是负 责数据在不同层面的唯一性,既要保证选择成为主键的属性字段不能重复不能为空,同时也要保证本身 具有唯一性的列的字段不能出现冲突

注意：当唯一键冲突时，自增长字段值不连续。

9.外键约束

作用：限定某个表的某个字段的引用完整性，外键约束主要定义在从表上,主表则必须是有主键约束或唯一键约束.当定义外键后,要求外键列数据必须在主表的主键列存在或为null。

foreign  key  (从表的字段名称) references  主表名字(主表的字段名称)

注意：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一 个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

10.检查约束

作用：检查约束是用来检查数据表中字段值有效性的一种手段，一般用于设置值的范围。

（1）建表时创建检查约束

create  table  表名(列名1 类型1, 列名2 类型2, check(检查约束);

例如（约束薪水值处于0-10000）：create table t5(id int primary key, name varchar(20), salary float, check(salary>0 and salary<10000) );

（2）修改表时添加检查约束

alter  table  表名  add  constraint  检查约束名  check(检查约束);

11.删除表的约束

（1）删除not null约束

alter  table  表名  modify  列名  类型;

（2）删除unique约束

alter  table  表名  drop  index  唯一约束名;

（3）删除primary key约束

alter  table  表名 drop  primary  key;

（4）删除foreign key约束

alter  table  表名  drop  foreign  key  外键名;