MySQL数据库

零、启动服务和登录数据库

在登录的前提一定要先切换到MySQL目录:

当前MySQL安装目录为：/opt/soft/MySQL-5.7.30

service mysql start                //启动MySQL服务
service mysql stop                //暂停MySQL服务
service mysql restart            //重启MySQL服务
./mysql -uroot -p                    //登录MySQL

一、创建、修改和删除数据库

创建数据库：

create database 需要创建的数据库名;                        //创建数据库
show databases 需要查看的数据库名;                            //查看数据库
default character set 字符集名字;                        //设置字符集，mysql8.0不可用
set character set 字符集名字；                                //设置字符集

打开数据库：

use 需要打开的数据名;                                //打开数据库

修改数据库名字：

alter database 现数据库名 rename to 修改为数据库名;

删除数据库：

DROP DATABASE [IF EXISTS] 数据库名;

修改字符集：

alter database 数据库名字 character set 字符集;

不懂：

[[DEFAULT] CHARACTER SET 字符集名
|[DEFAULT] COLLATE 校对规则名]

二、创建、修改和删除表

创建表：

IF NOT EXISTS是一个可选项，如果加上了这个选项，MySQL会在创建表之前检查该表是否已经存在，如果已经存在则不会重新创建，而是直接返回一个提示信息。如果不加上这个选项，当创建的表已经存在时会报错。

[TEMPORARY]是一个可选项，用于创建临时表，临时表是在当前连接会话结束时自动销毁的。临时表通常用于需要临时存储数据的场景，如在进行大量数据处理时，需要暂存一些中间结果。使用临时表可以避免对实际表的过度占用，也可以提高查询效率。

ENGINE是用来指定表的存储引擎的选项。存储引擎是负责管理数据的底层软件，不同的存储引擎具有不同的特点和优缺点，可以根据实际需求来选择合适的存储引擎。

在创建一个表时需要，先使用use进入数据再进行创建否则报错！

打开数据库：

use 数据库名
CREATE  [TEMPORARY]  TABLE  [IF NOT EXISTS] 表名(
    列名 数据类型 约束,
  列名 数据类型 约束
)ENGINE=存储引擎;;

简约版：

CREATE TABLE 表名(
    列名 数据类型 约束,
  列名 数据类型 约束
);

用于显示当前数据库中的所有表：

SHOW TABLES;

查看指定表：

{DESCRIBE|DESC} 表名 [列名|通配符]

describe 指定表名;
desc 指定表名；

修改表_删除列：

[COLUMN] 列名 用于删除表中的某一列。其中，IGNORE 关键字表示忽略删除不存在的列。

ALTER  [IGNORE] TABLE 表  DROP [COLUMN] 列名

image-20230423023620419

修改表_修改列数据类型：

ALTER [IGNORE] TABLE 表名 MODIFY [COLUMN] 列定义 [FIRST|AFTER 列名]
ALTER TABLE 表名 MODIFY 列定义 [FIRST 列名]                //移动到表中的第一个位置
ALTER TABLE 表名 MODIFY 列定义 [AFTER 指定列名]        //修改后移动到指定列名之后

image-20230423024042544

修改表_重命名列：

ALTER  [IGNORE] TABLE 表名 CHANGE [COLUMN] 旧列名 列定义 [FIRST|AFTER 列名]   /*对列重命名
ALTER TABLE 表名 CHANGE  旧列名 列定义 [FIRST|AFTER 数据类型]
ALTER TABLE 表名 CHANGE  旧列名 列定义 数据类型                            //
ALTER TABLE 表名 CHANGE  旧列名 列定义 FIRST 数据类型                //
ALTER TABLE 表名 CHANGE  旧列名 列定义 AFTER 数据类型                //

修改表_设置列 非空约束：

alter table 表名 change 旧列名 列定义 [FIRST|AFTER 数据类型] not null;
alter table student change name pro_name char(5) not null;

修改表_设置列 唯一约束UNIQUE：

ALTER TABLE 表名 ADD UNIQUE KEY 约束名(列名);

修改表_设置列 主键约束PRIMARY：

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

修改表_设置列 外键约束FOREIGN：

ALTER TABLE 表名 ADD FOREIGN KEY (外键列) REFERENCES 参考表名 (参考列);

修改表_设置列 默认值：

ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

其中，表名为要修改的表名，列名为要修改的列名，默认值为要设置的默认值。

例如，要为表student中的列age设置默认值为18，可以使用以下语句：

ALTER TABLE student ALTER COLUMN age SET DEFAULT 18;

修改表_设置列 检查约束：

ALTER TABLE 表名 ADD [CONSTRAINT] 约束名 CHECK(条件表达式);

在表 student 添加一个名字为check_age的检查约束，要求age必须大于等于0
alter table student add check_age check;

查看表，显示表语句：

show create table 表名；

三、数据类型

  1. 整型数据类型：
  • TINYINT：1字节，范围-128 ~ 127（有符号），0 ~ 255（无符号），通常用于存储布尔值或小的整数。
  • SMALLINT：2字节，范围-32768 ~ 32767（有符号），0 ~ 65535（无符号），适用于小的整数。
  • MEDIUMINT：3字节，范围-8388608 ~ 8388607（有符号），0 ~ 16777215（无符号），通常用于整数。
  • INT：4字节，范围-2147483648 ~ 2147483647（有符号），0 ~ 4294967295（无符号），通常用于存储整数。
  • BIGINT：8字节，范围-9223372036854775808 ~ 9223372036854775807（有符号），0 ~ 18446744073709551615（无符号），适用于非常大的整数或需要非常高精度计算的场景。
  1. 浮点型数据类型：
  • FLOAT(M,D)：4字节，范围-3.40282347E+38 ~ -1.17549435E-38、0和1.17549435E-38 ~ 3.40282347E+38，M表示总位数，D表示小数点后位数，通常用于小数计算。
  • DOUBLE(M,D)：8字节，范围-1.7976931348623157E+308 ~ -2.2250738585072014E-308、0和2.2250738585072014E-308 ~ 1.7976931348623157E+308，M表示总位数，D表示小数点后位数，适用于非常大或高精度计算的场景。
  1. 字符型数据类型：
  • CHAR(N)：定长字符类型，N表示字符长度，范围0 ~ 255，适用于长度固定的字符串。
  • VARCHAR(N)：变长字符类型，N表示字符长度，范围0 ~ 65535，适用于长度不固定的字符串。
  • TEXT：可变长度字符类型，可以存储最大长度为65535字节的字符串。
  • ENUM('value1','value2',...)：枚举类型，只能存储枚举列出的值，最多可以列举65535个值。
  • SET('value1','value2',...)：集合类型，可以存储一组枚举值，最多可以列举64个值。
  1. 日期和时间类型：
  • DATE：日期类型，范围从'1000-01-01'到'9999-12-31'，存储格式为'YYYY-MM-DD'。
  • TIME：时间类型，范围从'-838:59:59'到'838:59:59'，存储格式为'HH:MM:SS'。
  • DATETIME：日期时间类型，范围从'1000-01-01 00:00:

四、六大约束

4.1、非空约束

保证一列或一组列不允许为空值

创建一个有非空约束的表，其中name列不能为空：

CREATE TABLE employee (
  id INT NOT NULL,
  name VARCHAR(20) NOT NULL,
  age INT,
  salary DECIMAL(10, 2),
  PRIMARY KEY (id)
);

4.2、唯一约束

保证一列或一组列的值时唯一的，但允许空值

创建一个有唯一性约束的表，其中email列必须是唯一的：

CREATE TABLE user (
  id INT NOT NULL,
  username VARCHAR(20) NOT NULL,
  email VARCHAR(50) UNIQUE,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

4.3、主键约束

唯一标识一条记录一列或一组列，不允许重复制或空值

创建一个有主键的表，其中id列是主键：

CREATE TABLE student (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  age INT
);

4.4、外键约束

用于在一个表中创建指向另一个表中主键的外键，确保数据的完整性和一致性。

创建一个有外键约束的表，其中user_id列是对user表的id列的引用：

CREATE TABLE comment (
  id INT NOT NULL,
  user_id INT,
  comment_text VARCHAR(200),
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id)
);

4.5、检查约束

用于限制列的取值范围，例如限制某一列的取值只能在 1 到 100 之间。

创建一个有检查约束的表，其中age列必须大于18：

CREATE TABLE student (
  id INT NOT NULL,
  name VARCHAR(20) NOT NULL,
  age INT,
  PRIMARY KEY (id),
  CHECK (age > 18)
);

4.6、默认约束

创建一个有默认值约束的表，其中sex列默认为'unknown'：

CREATE TABLE user (
  id INT NOT NULL,
  name VARCHAR(20) NOT NULL,
  sex VARCHAR(10) DEFAULT 'unknown',
  PRIMARY KEY (id)
);

五、管理约束

ALTER TABLE ADD 约束：                //添加约束
ALTER TABLE DROP 约束：            //删除约束
ALTER TABLE MODIFY 约束：        //修改约束
ALTER TABLE ENABLE 约束：        //启用约束
ALTER TABLE DISABLE 约束：        //禁用约束
  • ADD：添加约束
  • DROP：删除约束
  • MODIFY：修改约束
  • ENABLE：启用约束
  • DISABLE：禁用约束

六、标识列

标识列目的是为了确保每条记录都有唯一的标识符

6.1、设置自增长列：

auto increment

6.2、设置自增长步长：

set auto_increment_increment=3;

6.3、查看自增长值：

show create table 表名

6.4、删除子增长的列：

alter table 表名 modify id int;

将学号id设置为主键，id从1开始自增长，步长是2

alter table student modify id int auto_increment;
set auto_increment_increment=2;
show variables like "%auto_increment%";

alter table student modify scores double auto_increment;

作业建立lib数据库

  • 创建一个信息表profession

    • 自增长id
    • 专业编号
    • 专业名称
    • 专业描述信息
    • 成立日期要求四个字段

    四个字段均不允许为空，专业编号为主键。同事在学生student表中添加一个专业表号的字段，该字段是引用专业信息表的专业表号。

CREATE TABLE profession (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pno VARCHAR(20) NOT NULL,
  pname VARCHAR(100) NOT NULL,
  description TEXT NOT NULL,
  establish_date DATE NOT NULL,
  UNIQUE KEY (pno)
);


ALTER TABLE student ADD COLUMN profession_no VARCHAR(20) REFERENCES profession(pno);
最后修改：2024 年 01 月 24 日
© 允许规范转载
咱们谁跟谁，用不着～