MySQL之一：数据库、数据表

最新推荐文章于 2024-03-02 15:52:12 发布

难选昵称

最新推荐文章于 2024-03-02 15:52:12 发布

阅读量499

点赞数

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

本文链接：https://blog.csdn.net/qq_26949665/article/details/99962073

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.知识点提炼

MySQL默认的端口号是多少：3306
MySQL中的超级用户叫什么：root
创建数据库：CREATE DATABASE
修改数据库：ALTER DATABASE
删除数据库：DROP DATABASE

2.常用命令：

显示当前服务器版本：SELECT VERSION();
显示当前日期时间：SLELECT NOW();
显示当前用户：SELECT USER();

3.MySQL语句的规范（默认规范，实际不区分大小写）：

关键字与函数名称全部大写
数据库名称、表名称、字段名称全部小写
SQL语句必须以分号结尾

注：关键字：是指在SQL中有意义的字，某些关键字（例如SELECT, DELETE, 或 BIGINT）是保留的，需要特殊处理才能用作表和列名称等标识符。这一点对于内置函数的名称也适用。

4.数据库操作

创建数据库

CREATE {DATABASE | SCHAMA} [IF NOT EXISTS] db_name [DEFAULF] CHARACTER SET [=] charset_name;

其中：[DEFAULF] CHARACTER SET [=] charset_name;，代表设置数据库的编码方式，不写默认是数据库的编码方式。

查看当前服务器下的数据库列表

SHOW {DATABASES | SCHAMAS} [LIKE 'pattern' | WHERE expr]

修改数据库

ALTER {DATABASE | SCHAMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name

删除数据库

DROP {DATABASE | SCHAMA} [IF EXISTS] db_name

注：查看警告信息：SHOW WARNINGS;

5.数据类型

定义：数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

注：以TINYINT为例，讨论取值范围：

1）对于无符号数：1111 1111 ~ 0000 0000 即 255 ~ 0

2）对于有符号数：最大值是0111 1111 即 +127，最小值为什么是-128呢？因为在计算机中，表示负值是用补码，虽然 -0 也是 0，这样就出现两个补码代表一个数值的情况，为例将补码与数字一一对应，所以认为规定 0 一律用 +0 代表，同时为了充分利用资源，九江原来本应该表示 -0 的补码规定为代表 -128.

注：M >= D

注：CHAR(M)是定长类型，如果不足长度则通过空格来补全；VARCHAR(M)是变长类型；

ENUM枚举值，即给定几个选项，从选项中做选择，最多有65535个选择；

SET代表在集合中选择任意的排列组合都是可以的；

6.操作数据表

定义：数据表（或称表）是数据库最重要的组成部分之一，是其他对象的基础。

1）USE

打开数据库
USE 数据库名称；

注：显示当前打开的数据库：SELECT DATABASE();

创建数据表，注意每句结束用逗号分隔，最后一句除外

CREATE TABLE [IF NOT EXISTS] table_name (

column_name data_type ,

......

)

eg：创建数据表

mysql> CREATE TABLE tb1(
       username VARCHAR(20),
       age TINYINT UNSGINED,
       salary FLOAT(8,2) UNSIGNED    
);

2）SHOW TABLES

查看数据表列表
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

3)SHOW COLUMNS

查看数据表的结构
SHOW COLUMNS FROM tb_name

4)INSERT

插入记录
INSERT [INTO] tb1_name[(column_name , ...)] VALUES(val , ...)

注：如果省略列名（字段值），那么就需要对每一个字段逐一赋值，否则会报错。如果不省略就可以为某些字段赋值。

5）SELECT

记录查找
SELECT expr，... FROM tb1_name

eg：

6）空值与非空

NULL，字段值可以为空
NOT NULL，字段值禁止为空

7）AUTO_INCREMENT

自动编号，且必须与主键组合使用
默认情况下，起始值为1，每次的增量为1
一般为整型，如果为浮点型，小数位数一定为0

8）PRIMARY KEY

主键约束
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL
主键不一定与AUTO_INCREMENT一起使用

9）UNIQUE KEY

唯一约束
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值（NULL）
每张数据表可以存在多个唯一约束

10）DEFAULT

默认值
当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

11）约束

约束保证数据的完整性和一致性
约束分为表级约束和列级约束（根据字段数目的多少决定的，如果约束只针对某个字段使用，则称为列级约束；如果针对两个到多个字段使用，则称为标记约束）
约束类型包括（按照功能划分）：

NOT NULL（非空约束）
PRIMARY KEY（主键约束）
UNIQUE KEY（唯一约束）
DEFAULT（默认约束）
FOREIGN KEY（外键约束）

12）FOREIGN KEYp

保持数据一致性，完整性
实现数据表的一对一或一对多关系
外键约束的要求：

父表（子表所参照的表）和子表（具有外键列的表）必须使用相同的存储引擎，而且禁止使用临时表。
数据表的存储引擎只能为 InnoDB。
外键列（曾经加过FOREIGN 关键字的列）和参照列（外键列参照的那一列）必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

编辑数据表的默认存储引擎：

MySQL配置文件my.ini：default-storage-engine = INNODB，然后重启MySQL相关服务

对于3进行实现举例：

1.登录MySQL客户端
mysql -uroot -ppassword

2.打开一个数据库
USE test;

3.创建两张数据表
CREATE TABLE provices(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL
);
创建完成如果不确定可以通过SHOW CREATE TABLE Provices来查看创建命令

CREATE TABLE user(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid BIGINT,
    FOREIGN KEY (pid) REFERENCES provices (id)
);
此时会报错：无法创建user表，原因是违反了条件3，pid的数据类型和父表中的id的数据类型不同导致的。

于是对其进行修改，而且对于整型也一定要保证有无符号的统一，重新创建user表：
CREATE TABLE user(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY (pid) REFERENCES provices (id)
);

注：目前的父表是provices，子表是user，子表user中有外键，其参照的表叫做父表。

对于4进行分析：

外键列是pid，参照列是id。在参照列中并未创建索引，如果没有创建，会自动创建索引，这里由于id是主键，在创建时MySQL自动为其创建了索引，所以参照列已经存在索引。检验：显示索引：SHOW IINDEXS FROM provices[\G是以网格形式呈现]

再查看子表是否存在索引：

发现存在两个索引，分别是id主键和pid外键也被创建了索引。

外键约束的参照操作：在进行了外键约束的创建以后，在更新表的时候，子表是否也进行相应的操作

CASCADE 级联：从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL ：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL`
RESTRICT 限制：拒绝对父表的删除或更新操作
NO ACTION ：标准SQL的关键字，在MySQL中与RESTRICT相同

eg：而且需要注意，父表要在子表前插入记录，否则子表的记录无法参照

1.为了演示CASCADE，首先创建一张新的用户表users1:
CREATE TABLE users1(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY (pid) REFERENCES provices (id) ON DELETE CASCADE
);

2.查看users1表的创建命令：SHOW CREATE TABLE users1

注：其中ON DELETE CASCADE表示的是在父表中删除相应的行，同时也删除，更新子表中相应的行。

如果插入到子表中的记录中的外键列对应的约束列还不存在，那么就会插入（eg第二条记录插入失败）失败，并且下一条记录插入时，如果是AUTO_INCREMENT的话就会从第三条记录插入。

在外键约束中，在实际开发中，很少使用物理的外键约束，一般使用逻辑外键约束，因为物理的外键约束，只有InnoDB引擎才支持，所以一般不会去实际定义物理的外键。逻辑约束的意思是，两张表按照某种联系去创建，但是不使用FOREIGN KEY这个关键词来定义。

13）表级约束与列级约束

对一个数据列建立的约束，称为列级约束
对多个数据列建立的约束，称为表级约束
列级约束既可以在列定义时声明，也可以在列定义后声明。
表级约束只能在列定义后声明
NOT NULL和DEFAULT只存在列级约束，而对于其他三种PRIMARY KEY、UNIQUE KEY、FOREIGN KEY都是既存在表级约束又存在表级约束。

14）修改数据表

添加单列

ALTER TABLE tb1_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

发现默认是在之后添加

添加多列

ALTER TABLE tb1_name ADD [COLUMN] (col_name column_definition , ...)

注：添加多列时，不能指定位置关系，只能在规定的位置下方添加

删除列

ALTER TABLE tb1_name DROP [COLUMN] col1_name , DROP [COLUMN] col2_name , ADD [COLUMN] col3_name col_definition

添加主键约束

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name , ...)

添加唯一约束

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name，...)

添加外键约束

ALTER TABLE tb_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name，...) reference_definition

eg:

ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCES provices (id);

添加 / 删除默认约束

ALTER TABLE tb_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

删除主键约束

ALTER TABLE tb_name DROP PRIMARY KEY

删除唯一约束

ALTER TABLE tb_name DROP {INDEX | KEY} index_name

删除外键约束

ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol

注：查看外键约束的名称，通过：SHOW CREATE TABLE users; 找到[约束]CONSTRAINT 'fk_symbol'

修改列定义：有可能字段的数据类型、位置上存在问题

ALTER TABLE tb_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

注：由大类型转换到小类型时要注意数据的丢失

修改列名称

ALTER TABLE tb_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

修改数据表名称

方法1：ALTER TABLE tb_name RENAME [TO | AS] new_tb_name
方法2：RENAME TABLE tb_name TO new_tb_name [,tb_name2 TO new_tb_name2] ...

难选昵称

关注

0
点赞
踩
0

收藏

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

分享到 QQ

分享到新浪微博

扫一扫

专栏目录