MySQL数据库

MySQL Gitee笔记

小皮面板(PHP Study)

安装路径不能包含“中文”或者“空格”，否则会报错。

点击设置-文件位置-MySQL，打开文件路径，复制路径到Windows设置环境变量

设置环境变量：右键此电脑-属性-高级系统设置-高级-环境变量-上边是用户环境变量，下面是系统环境变量。

在系统变量中找到Path-双击或者点编辑

点击新建，把X:\phpstudy_pro\Extensions\MySQLX.X.X路径复制上去。然后一路的确定即可。

把X:\phpstudy_pro\Extensions\MySQLX.X.X\bin文件配置到用户变量中

点击新建，把X:\phpstudy_pro\Extensions\MySQLX.X.X\bin路径复制上去，然后一路确定即可。

打开CMD终端，输入：mysql -uroot -proot

出现Welcome to the MySQL monitor表示配置成功。

数据库：

1.初识MySQL语句：

每条命令以；或 \g 或 \G 结束

注释:

单行：# 或 -- ... --

多行：/* ... */

增：create database 数据库名称 charset utf-8;

推荐：create database 数据库名称 default character set utf8 collate utf8_general_ci;

查看数据库的创建细节：show create database 数据库名称;

查看所有数据库：show databases;

切换数据库：use 数据库名称;

查看当前所在数据库：select database();

改：alter database 数据库名称 charset gbk;

删：drop database 数据库名称;

数据表：

增：create table 数据表名称(字段数据类型(宽度),约束,功能);

查：show create table 数据表名称;

查看当前数据库所有表：show tables;

数据表的结构形式查看：desc 数据表名称;

改：alter table 数据表名称 modify 字段数据类型(宽度),约束,功能;

alter table 数据表名称 change 原字段改后字段数据类型(宽度);

删：drop table 数据表名称;

数据表数据：

增：insert 数据表名称(字段1,字段2) values(数据1,'数据2'),(数据3,'数据4');

或insert into 数据表名称(字段1,字段2) values(数据1,'数据2'),(数据3,'数据4');

查：select *from 数据表名称;

select *from 数据库名称.数据表名称;

select 字段1,字段2…… from 数据库名称.数据表;

改：update 数据库名称.数据表名称 set 字段=数据;

更改一条数据：UPDATA 数据库名称.数据表名称 SET 字段=数据 WHERE 字段=数据;

更改一条数据：update 数据库名称.数据表名称 set 字段=数据 where 字段=数据;

删除一条数据：delete 数据表 where 字段=数据;

复制：表结构+记录：create table 新数据表名称 select 字段 from 原数据表名称;

只复制表结构不复制记录：create table 新数据表名称 select 字段 from 原数据表名称 where条件不成立;

like:只复制表结构不复制记录 create table 新数据表名称 like 原数据表名称;

删：delete from 数据表名称;

2.库操作：

①.系统数据库

information_schema：MySQL 等数据库管理系统里的一个特殊系统数据库。虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数。它的用途是存储数据库元数据，也就是有关数据库本身的数据，像数据库、表、列、索引等的定义和信息。如：用户表信息，列信息，权限信息，字符信息等。

mysql：授权库，主要存储系统用户的权限信息。

performance_schema：MySQL5.5开始新增的一个数据库;主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件， SQL 语句的执行、锁的等待、线程的活动等。

sys:MySQL 5.7 版本引入的一个系统数据库，它包含了一系列的视图和存储过程，用于提供关于数据库服务器性能、配置、查询执行等方面的详细信息。这些视图和存储过程的数据是基于底层的系统表和性能架构表进行整合和处理的。通过查询sys数据库中的视图，管理员和开发人员可以方便地获取到数据库运行状态的各种指标，例如查询执行时间、数据库锁信息、系统资源使用情况等，从而更好地进行性能优化、故障排查和系统管理。

test:MySQL数据库系统自动创建的测试数据库。

②.help:查看命令的使用方法。

③.表的操作：

存储引擎：就是表的数据类型。

查看MySQL支持的存储引擎：show engines;

MySQL自带的库：show create table mysql.user\G

显示方式：show create table 数据表名称\G

数据类型：

1. 整数类型

类型	字节大小	有符号范围(Signed)	无符号-范围(Unsigned)
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	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

2. 字符串

类型	说明	使用场景
CHAR	固定长度，小型数据	身份证号、手机号、电话、密码
VARCHAR	可变长度，小型数据	姓名、地址、品牌、型号
TEXT	可变长度，字符个数大于 4000	存储小型文章或者新闻
LONGTEXT	可变长度，极大型文本数据	存储极大型文本数据

3. 时间类型

类型	字节大小	示例
DATE	4	'2022-01-01'
TIME	3	'12:29:59'
DATETIME	8	'2020-01-01 12:29:59'
YEAR	1	'2017'
TIMESTAMP	4	'1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC

查看字符串的长度：select char_length(字段) from 数据表;

设置几个字节长度就显示几个字节长度：SET sql_mode ='PAD_CHAR_TO_FULL_LENGTH';

枚举类型enum:单选。只能在给定的范围内选择一个值。

集合类型set:多选。在给定的范围内一个或一个以上的值。

UNIQUE 是一个重要的约束条件，主要用于保证表中某一列或者多列组合的值具有唯一性，即表中不会存在重复的记录。

单列唯一：

-- 方式一
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50)
);    
-- 方式二
CREATE TABLE department (
    id INT,
    name CHAR(10),
    UNIQUE(id),
    UNIQUE(name)
);

多列&联合唯一：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    UNIQUE (customer_id, product_id, order_date)
);

primary key主键：是一个极为关键的约束条件，不为空且唯一，它的作用是唯一标识表中的每一行记录。

单列主键：当表中某一列的值能够保证唯一性时，可将该列设为主键。

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

多列&联合主键：若单靠一列无法保证记录的唯一性，可将多列组合起来设为主键。

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

修改数据表时添加主键：

-- 单列主键：
ALTER TABLE 数据表
ADD PRIMARY KEY (字段); 
-- 多列主键：
ALTER TABLE 数据表
ADD PRIMARY KEY (字段1, 字段2);

删除主键：

ALTER TABLE 数据表
DROP PRIMARY KEY;

AUTO_INCREMENT 是一个非常实用的约束条件，主要用于为表中的某一列自动生成唯一的整数值，通常与 PRIMARY KEY 约束一起使用，为表中的每一行记录生成一个唯一的标识符。

CREATE TABLE 数据表 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

除了在创建表时使用 AUTO_INCREMENT，它在修改表结构、数据插入、重置自增序列等场景中也有重要应用。

可以使用 ALTER TABLE 语句来实现。

-- 创建一个不含 AUTO_INCREMENT 的表
CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(50)
);

-- 为 product_id 列添加 AUTO_INCREMENT 属性
ALTER TABLE products
MODIFY COLUMN product_id INT AUTO_INCREMENT PRIMARY KEY;
-- 移除 product_id 列的 AUTO_INCREMENT 属性
ALTER TABLE products
MODIFY COLUMN product_id INT;

-- 清空表数据
DELETE FROM products;

-- 重置自增序列从 1 开始
ALTER TABLE products AUTO_INCREMENT = 1;

-- 复制 products 表的结构到 new_products 表
CREATE TABLE new_products LIKE products;

查询变量名称：show variables like 'auto_inc%';

设置auto_increment步长：set global auto_increment_increment=3;

设置auto_increment起始偏移量：set global auto_increment_offset=2;

强调：auto_increment_offset要<=auto_increment_increment.否则无效！

更改后必须先退出MySQL再登录才能看到效果。

清空数据恢复默认：truncate 数据表;

foreign key外键:是一个表中的一列或多列，其值与另一个表的主键（Primary Key）相匹配。它创建了两个表之间的关系，保证了引用完整性。

数据完整性：外键能够防止在一个表中插入无效的数据，保证了数据的一致性。
建立关系：在数据库中定义表与表之间的关系，让数据之间的关联更清晰。

先建被关联的数据表，并且保证其不为空且唯一性：

-- 创建被关联的department数据表

create table department(
id int,
name char(16),
commnet char(50)
);

-- 先在被关联的department数据表里插入数据

insert into department values
(1,'IT','技术能力有限部门'),
(2,'销售','销售能力部门'),
(3,'财务','花钱特别多部门');

再建关联的表：

-- 创建 staff 关联表，并使用外键关联到 department 表

create table staff(
id int primary key,
name char(10),
sex enum('male','female'),
department_id int,
foreign key(department_id) references department(id)
);

-- 再在关联的staff数据表里插入数据

insert into staff values
(1,'勇哥','male',1),
(2,'白娘子','female',2),
(3,'小青','female',3),
(4,'许仙','male',2);

在这个例子中，staff表中的department列是外键，它引用了department表中的id列（主键）。这意味着在staff表中插入数据时，department_id的值必须在department表的id列中存在。

外键约束的操作：

级联删除（CASCADE DELETE）：当删除父表中的记录时，相关的子表记录也会被自动删除。
级联更新（CASCADE UPDATE）：当更新父表中的主键值时，相关的子表记录的外键值也会被自动更新。

-- 创建 departments 表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 创建 employees 表，并设置外键约束，同时启用级联删除和级联更新
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 插入示例数据
INSERT INTO departments (id, name) VALUES (1, '技术部');
INSERT INTO employees (id, name, department_id) VALUES (1, '张三', 1);

-- 查看插入的数据
SELECT * FROM departments;
SELECT * FROM employees;

-- 尝试删除 departments 表中的记录，观察 employees 表的变化
DELETE FROM departments WHERE id = 1;

-- 再次查看 employees 表，确认相关记录已被删除
SELECT * FROM employees;

-- 重新插入数据
INSERT INTO departments (id, name) VALUES (1, '技术部');
INSERT INTO employees (id, name, department_id) VALUES (1, '张三', 1);

-- 尝试更新 departments 表中的主键值，观察 employees 表的变化
UPDATE departments SET id = 2 WHERE id = 1;

-- 再次查看 employees 表，确认外键值已被更新
SELECT * FROM employees;

一对多关系：是指在两个表中，一个被关联表（通常称为主表或父表）中的一条记录可以与另一个关联表（通常称为从表或子表）中的多条记录相关联。

多对一关系：指的是在一个关联表（子表）里的多条记录能够和另一个被关联表（父表）中的同一条记录相对应。

一对多数据表或称为多对一数据表：

/* 一对多或（多对一）：一个出版社可以出版多本书
关联方式：foreign key
一对多或是多对一表 */

create table press(
id int primary key auto_increment,
name varchar(20));

create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade);

insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社');
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独狐九剑',3),
('降龙十八掌',2),
('葵花宝典',3);

解释：在 book 表中设置的外键约束 foreign key(press_id) references press(id) on delete cascade on update cascade，其作用是维护数据的一致性。

foreign key(press_id)：这部分表明在 book 表中，press_id 列被指定为外键。外键是一种特殊的字段，其作用是建立不同表之间的关联。

references press(id)：此部分说明 press_id 列引用了 press 表的 id 列。也就是说，book 表中的 press_id 列的值必须是 press 表中 id 列已经存在的值。

on delete cascade 表示当删除 press 表中的某条出版社记录时，book 表中所有关联该出版社的书籍记录会被自动删除；

on update cascade 表示当更新 press 表中某条记录的 id 时，book 表中关联该出版社的书籍记录的 press_id 会自动更新。

多对多关系：多对多关系指的是两个表之间，一个表中的一条记录能够和另一个表中的多条记录相关联，反之亦然。

在上述数据库设计中，press 表和 book 表之间是一对多关系，如果要实现多对多关系，通常需要引入一个中间表。

-- 创建出版社表
CREATE TABLE press (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 创建图书表
CREATE TABLE book (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 创建中间表，用于建立多对多关系
CREATE TABLE book_press_relation (
    book_id INT,
    press_id INT,
    PRIMARY KEY (book_id, press_id),
    FOREIGN KEY (book_id) REFERENCES book(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (press_id) REFERENCES press(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- 向出版社表插入数据
INSERT INTO press (name) VALUES
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社');

-- 向图书表插入数据
INSERT INTO book (name) VALUES
('九阳神功'),
('九阴真经'),
('九阴白骨爪'),
('独狐九剑'),
('降龙十八掌'),
('葵花宝典');

-- 向中间表插入数据，建立图书与出版社的多对多关系
INSERT INTO book_press_relation (book_id, press_id) VALUES
(1, 1),
(2, 2),
(3, 2),
(4, 3),
(5, 2),
(6, 3),
-- 假设《九阳神功》同时由人民音乐不好听出版社出版
(1, 2);

一对一关系：指的是被关联表（主表）中的一条记录仅能和关联表（从表）中的一条记录关联，反之亦然。

-- 创建客户表
create table customer(
    id int primary key auto_increment,
    name varchar(20) not null,
    qq varchar(11) not null,
    phone char(16) not null
);

-- 创建学生表
create table student(
    id int primary key auto_increment,
    class_name varchar(20) not null,
    customer_id int unique, -- 该字段一定要是唯一的
    foreign key(customer_id) references customer(id) -- 外键的字段一定要保证 unique
    on delete cascade
    on update cascade
);

-- 增加客户
insert into customer(name,qq,phone) values
('李飞机','1234567','13011223344'),
('王大炮','7654321','13155667788'),
('守榴弹','11223344','13011223344'),
('吴坦克','55667788','13300112233'),
('嬴火箭','6688668','15533445567'),
('战地雷','56445787','15665487987');

-- 增加学生
insert into student(class_name,customer_id) values
('起飞3班',3),
('陆地18班',4),
('陆地18班',5);

posted @ 2025-03-15 16:26 自学小天才阅读(135) 评论(0) 收藏举报

刷新页面返回顶部

xxy002

MySQL数据库

1. 整数类型

2. 字符串

3. 时间类型

公告