MySQL数据库

最新推荐文章于 2023-03-15 22:17:34 发布

サUBLL� 尔~

最新推荐文章于 2023-03-15 22:17:34 发布

阅读量122

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_59378215/article/details/120131966

版权

九、DDL

9.1、和数据库相关的

（1）创建数据库 create database 数据库名;

（2）查看所有的数据库 show databases；

（3）查看某个数据库的详细定义语句 show create database 数据库名;

mysql> show create database atguigudb;
+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| atguigudb | CREATE DATABASE `atguigudb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database atguigudb\G
*************************** 1. row ***************************
       Database: atguigudb
Create Database: CREATE DATABASE `atguigudb` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)xxxxxxxxxx mysql> show create database atguigudb;+-----------+--------------------------------------------------------------------+| Database  | Create Database                                                    |+-----------+--------------------------------------------------------------------+| atguigudb | CREATE DATABASE `atguigudb` /*!40100 DEFAULT CHARACTER SET utf8 */ |+-----------+--------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create database atguigudb\G*************************** 1. row ***************************       Database: atguigudbCreate Database: CREATE DATABASE `atguigudb` /*!40100 DEFAULT CHARACTER SET utf8 */1 row in set (0.00 sec)show creat

（4）删除数据库 drop database 数据库名;

（5）使用数据库 use 数据库名;

9.2、和数据表相关的

（1）查看某个数据库的所有表格 use 数据库名; show tables;

（2）创建表格

create table 数据表名称(   
 字段名  数据类型,   
 字段名  数据类型
);

create table teacher(   
  tid int, 
  tname varchar(5),   
  salary double,  
  weight double(5,2),   
  birthday date,   
  gender enum('男','女'),   
 blood enum('A','B','AB','O'),   
 tel char(11)
);

（3）查看表的详细定义信息 show create table 表名称;/show create table 表名称\G

mysql> show create table teacher\G
*************************** 1. row ***************************
       Table: teacher
Create Table: CREATE TABLE `teacher` (
  `tid` int(11) DEFAULT NULL,
  `tname` varchar(5) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `weight` double(5,2) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `gender` enum('男','女') DEFAULT NULL,
  `blood` enum('A','B','AB','O') DEFAULT NULL,
  `tel` char(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

（4）查看表结构 desc 表名称;

mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid      | int(11)                | YES  |     | NULL    |       |
| tname    | varchar(5)             | YES  |     | NULL    |       |
| salary   | double                 | YES  |     | NULL    |       |
| weight   | double(5,2)            | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| gender   | enum('男','女')        | YES  |     | NULL    |       |
| blood    | enum('A','B','AB','O') | YES  |     | NULL    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

（5）删除表格，包括表结构和里面的数据 drop table 表名称;

（6）修改表结构：删除字段 alter table 表名称 drop 【column】字段名称;

(7）修改表结构：增加字段【column】栏；列

alter table 表名称 add 【column】 字段名称 数据类型;
alter table 表名称 add 【column】 字段名称 数据类型 first;
alter table 表名称 add 【column】 字段名称 数据类型 after 另一个字段;

(8)修改表结构：修改字段的数据类型

alter table 表名称 modify 【column】字段名称新的数据类型;

mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid      | int(11)                | YES  |     | NULL    |       |
| tname    | varchar(5)             | YES  |     | NULL    |       |
| salary   | double                 | YES  |     | NULL    |       |
| weight   | double(5,2)            | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| gender   | enum('男','女')        | YES  |     | NULL    |       |
| blood    | enum('A','B','AB','O') | YES  |     | NULL    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> alter table teacher modify column weight double;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid      | int(11)                | YES  |     | NULL    |       |
| tname    | varchar(5)             | YES  |     | NULL    |       |
| salary   | double                 | YES  |     | NULL    |       |
| weight   | double                 | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| gender   | enum('男','女')        | YES  |     | NULL    |       |
| blood    | enum('A','B','AB','O') | YES  |     | NULL    |       |
|tel      | char(11)               | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

(9)修改表结构：修改字段的名称

alter table 表名称 change 【column】旧字段名称新字段名称新的数据类型;

(10)修改表结构：修改字段位置

alter table 表名称 modify 【column】字段名称数据类型 first;

alter table 表名称 modify 【column】字段名称数据类型 after 另一个字段;

(11)修改表名称（重命名表）

alter table 旧表名称 rename to 新表名称;

rename table 旧表名称 to 新表名称;

9.3 修改数据库和表的字符编码

修改编码：

（1)先停止服务，（2）修改my.ini文件（3）重新启动服务

说明：

如果是在修改my.ini之前建的库和表，那么库和表的编码还是原来的Latin1，要么删了重建，要么使用alter语句修改编码。

ENGINE=InnoDB DEFAULT CHARSET=latin1
alter table 表名称 charset utf8; #修改表字符编码为UTF8   
CHARRACTER SET latin1 DEFAULT NULL, #字段仍然是latin1编码

alter table 表名称 modify 【molumn】charset utf8; #修改表字段编码为UTF8   
ENGINE=InnoDB DEFAULT CHARSET=UTF8

alter database 数据库名 charset utf8;#修改表字库编码为UTF8

十、DML

10.1 添加语句

（1）添加一条记录到某个表中

insert into 表名称 values(值列表); #值列表中的值的顺序、类型、个数必须与表结构一一对应

（2）添加一条记录到某个表中

insert into 表名称 values(字段列表); #值列表中的值的顺序、类型、个数必须与表结构一一对应

（3）添加多条记录到某个表中

insert into 表名称 values(值列表),(值列表),(值列表); #值列表中的值的顺序、类型、个数必须与表结构一一对应

insert into 表名称(字段列表) values(值列表),(值列表),(值列表); #值列表中的值的顺序、类型、个数必须与(字段列表)一一对应

insert into teacher (tid,tname,salary,phone)
 values(4,'赵六',16000,'15789546586'),
(5,'汪飞',18000,'15789548886'),
(6,'天琪',19000,'15909546586');

10.2 修改语句

update 表名称 set 字段名 = 值,字段名 = 值;#给所有行修改

update 表名称 set 字段名 = 值,字段名 = 值 where 条件; #给满足条件的行修改

#修改所有人的薪资，都涨了1000
update teacher set salary = salary + 1000 ;

 #修改天琪的薪资降低5000
update teacher set salary = salary-5000 where tname = '天琪';

10.3 查询（最简单的查询）

（1）查询表中的所有数据 select * from 表名称;

（2）查询表中的部分列的数据 select 字段名,字段名 from 表名称;

（3）查询表中的部分行的数据 select * from 表名称 where 条件;

10.4 删除

（1）删除部分行的数据 delect from 表名称 where 条件;

（2）删除整张表的数据,但表结构留下 delect from 表名称;

（3）截断表，清空表中的结构，只有表结构 truncate 表名称;

truncate表和delete表的区别：

delete是一条一条删除记录的。如果在事务中，支持回滚。

truncate是把整个表drop，新建一张，效率更高。就算在事务中，也无法回滚。

十一、约束

11.1 约束的作用

约束是为了保证数据的完整性，数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

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

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

11.2 约束的类型

键约束：主键约束、外键约束、唯一键约束
Not NULL约束：非空约束
Check约束：检查约束
Default约束：默认值约束
自增约束

约束和索引不同：

约束是一个逻辑概念，它不会单独占用物理空间，

索引是一个物理概念，它是会占用物理空间。

例如：字典

字典里面有要求，不能有重复的字（字一样，读音也一样），这是约束。

字典里面有“目录”，它可以快速的查找某个字，目录需要占用单独的页。

11.3 主键约束（重要）

1、主键约束的作用

用来唯一的确定一条记录

2、关键字：primary key

3、特点：

（1）唯一并且非空

（2）一个表最多只能有一个主键约束

（3）如果主键是由多列组成，可以使用复合主键

（4）主键列会自动创建索引（能够根据主键查询的，就根据主键查询，效率更高）

主键列的唯一并且非空是约束的概念，但是mysql会给每个表的主键列创建索引，会开辟单独的物理空间来存储每一个主键的目录表（Btree结构）。这样设计的意义，可以根据主键快速查询到某一行的记录。

（5）如果删除主键约束了，主键约束对应的索引就自动删除了。

4、如何指定主键约束

（1）建表时指定主键约束

create table 表名称(
	字段名  数据类型  primary key,
    字段名  数据类型,  
    字段名  数据类型  
);
create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    primary key(字段名)
);

（2）建表后增加主键约束

alter table 表名称 add primary key (字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

5、复合主键

create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    primary key(字段名1,字段名2)  #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

6、如何删除主键约束

alter table 表名称 drop primary key;

7、如何查看一个表的主键约束

desc 表名称;

show create table 表名称;

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
#WHERE条件
#table_name = '表名称'条件是指定表名称

11.4 唯一键约束（重要）

1、唯一约束的作用

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

2、唯一键约束和主键约束的不同

（1）主键约束，除了唯一，还要非空，

唯一键约束只是限定唯一，但可以为空（NULL）

（2）主键约束，一个表只能有一个，

唯一键约束，一个表可以有多个

3、关键字：unique key

4、如何指定唯一键约束

（1）建表时

reate table 表名称(
	字段名  数据类型  primary key,
    字段名  数据类型  unique key,  
    字段名  数据类型  unique key,
    字段名  数据类型
);
create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    primary key(字段名),
    unique key(字段名)
);

（2）建表后指定唯一键约束

alter table 表名称 add unique key(字段列表); #字段列表中如果是一个字段，表示该列的值唯一，如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的

5、如何查看唯一键约束

desc 表名称;

show create table 表名称;

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
#WHERE条件
#table_name = '表名称'条件是指定表名称

6、复合唯一

create table 表名称(
	字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);

7、删除唯一键约束

alter table 表名称 drop index 约束名; #查看好约束之后再删除；删除唯一键约束需要手动删除对应的索引；另外，因为一个表中可能有多个唯一键约束，所以需要指定约束的名

8、唯一约束键的特点

（1）唯一键约束限定唯一

（2）唯一键约束允许为空

（3）一个表可以有多个唯一键约束

（4）唯一键约束也可以是复合唯一

（5）唯一键约束也会自动创建索引

（6）删除唯一键约束，需要手动删除对应的索引

9、如何查看表的索引 show index from 表名称;

11.5 非空约束（重要）

1、作用

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

2、关键字：not null

3、特点

（1）只能某个列单独限定非空，不能组合非空

（2）一个表可以有很多列都分别限定了非空

4、如何指定非空约束

（1）建表时

create table 表名称(    
  字段名  数据类型  primary key,   
  字段名  数据类型  unique key not null,   
  字段名  数据类型  unique key,  
  字段名  数据类型  not null, 
);

create table 表名称(   
  字段名  数据类型,   
  字段名  数据类型 not null,   
  字段名  数据类型 not null,    
  primary key(字段名),   
  unique key(字段名)
);

（2）建表后

alter table 表名称 modify 字段名数据类型 not null；

create table student(
	sid int primary key,
    sname varchar(20) ,
    tel char(11) unique key,
    cardid char(18) unique key 
);

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | YES  |     | NULL    |       |
| tel    | char(11)    | YES  | UNI | NULL    |       |
| cardid | char(18)    | YES  | UNI | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

alter table student modify sname varchar(20) not null;

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | NO   |     | NULL    |       |
| tel    | char(11)    | YES  | UNI | NULL    |       |
| cardid | char(18)    | YES  | UNI | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

alter table student modify cardid char(18) not null;

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | NO   |     | NULL    |       |
| tel    | char(11)    | YES  | UNI | NULL    |       |
| cardid | char(18)    | NO   | UNI | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5、如何删除非空约束

alter table 表名称 modify 字段名数据类型 not null; #给某个字段加非空约束
alter table 表名称 modify 字段名数据类型 ; #去掉not null，相当于修改某个非注解字段，该字段允许为空

11.6 默认值约束（重要）

1、作用

给某个字段/某列指定默认值，当添加时或修改时，可以使用默认值。

2、关键字：default

3、如何给字段加默认值

（1）建表时

create table 表名称(
	字段名  数据类型  primary key,
    字段名  数据类型  unique key not null,  
    字段名  数据类型  unique key,
    字段名  数据类型  not null default 默认值, 
);
create table 表名称(
	字段名  数据类型 default 默认值 ,
    字段名  数据类型 not null default 默认值,  
    字段名  数据类型 not null default 默认值,,
    primary key(字段名),
    unique key(字段名)
);

说明：默认值约束一般不在唯一键和主键列上加

（2）建表后

alter table 表名称 modify 字段名数据类型 default 默认值；
alter table 表名称 modify 字段名数据类型 default 默认值 not null;

#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了

4、如何删除默认值约束

alter table 表名称 modify 字段名数据类型 ; #删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名数据类型 not null; #删除默认值约束，保留非空约束

11.7 自增约束（重要）

1、作用

某个字段的值自增

2、关键字：auto_increment

3、特点和要求

（1）每一个表只能有一个自增约束

（2）自增约束的列必须是键列（主键列，唯一键列）

（3）自增约束的列的数据类型必须是整数类型

（4）如果自增列指定了 0 和 null，会在当前最大值的基础上自增，

如果自增列手动指定了具体值，直接赋值为具体值。

4、如何指定自增约束

（1）建表时

create table 表名称(
	字段名  数据类型  primary key auto_increment,
    字段名  数据类型  unique key not null,  
    字段名  数据类型  unique key,
    字段名  数据类型  not null default 默认值, 
);
create table 表名称(
	字段名  数据类型 default 默认值 ,
    字段名  数据类型 unique key auto_increment,  
    字段名  数据类型 not null default 默认值,,
    primary key(字段名)
);

（2）建表后

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

5、如何删除自增约束

alter table 表名称 modify 字段名数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名数据类型; #去掉auto_increment相当于删除

6、自增变量

本质上每一个表有一个自增变量，它会记录当前自增字段最大的值，每次添加添加都会自增1，

（1）自增自增指定了特定值，不会自增1

（2）有些失败，例如：违反非空约束等，不会自增1

（3）有些失败会自增，例如：违反唯一性约束等，会自增1

（4）添加成功，会自增1

11.8 检查约束（了解）

1、作用

检查某个字段的值是否符号xx要求，一般指的是值的范围

2、关键字：check

3、说明：mysql不支持

11.9 外键约束（了解）

外键约束会影响性能，效率，所以很多人不愿意加外键约束。

1、作用

限定某个表的某个字段的引用完整性，

比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

2、关键字：foreign key

3、主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表，

部门表是主表，员工表是从表。

例如：学生表、课程表、选课表

选课表的学生和课程要分别参考学生表和课程表，

学生表和课程表是主表，选课表是从表。

4、特点

（1）在“从表”中指定外键约束，并且一个表可以建立多个外键约束

（2）创建(create)表时就指定外键约束的话，先创建主表，再创建从表

（3）删表时，先删从表（或先删除外键约束），再删除主表

（4）从表的外键列，必须引用/参考主表的键列（主键或唯一键）

为什么？因为被依赖/被参考的值必须是唯一的

（5）从表的外键列的数据类型，要与主表被参考/被引用的列的数据类型一致，并且逻辑意义一致。

例如：都是表示部门编号，都是int类型。

（6）外键列也会自动建立索引（根据外键查询效率很高，很多）

（7）外键约束的删除，所以不会自动删除，如果要删除对应的索引，必须手动删除

5、如何指定外键约束

（1）建表时

create table 主表名称(
	字段1  数据类型  primary key,
    字段2  数据类型
);

create table 从表名称(
	字段1  数据类型  primary key,
    字段2  数据类型,
    foreign key （从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

（2）建表后

alter table 从表名称 add foreign key (从表的字段) references 主表（被引用字段) 【on update xx】【on delete xx】;

总结：约束关系是针对双方的

添加了外键约束后，主表的修改和删除数据受约束
添加了外键约束后，从表的添加和修改数据受约束
在从表上建立外键，要求主表必须存在
删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

6、约束等级

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
Restrict方式：同no action, 都是立即检查外键约束
Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式。

7、如何查看外键约束名

desc 从表名称; #可以看到外键约束，但看不到外键约束名

show create table 从表名称; #可以看到外键约束名

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
#WHERE条件
#table_name = '表名称'条件是指定表名称

8、如何删除外键约束

删除外键约束，不会自动删除外键约束列的索引，需要单独删除。

(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名

alter table 从表名 drop foreign key 外键约束名;

（2）第二步查看索引名和删除索引
show index from 表名称; #查看某个表的索引名

alter table 从表名 drop index 索引名;

サUBLL� 尔~

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库

九、DDL9.1、和数据库相关的（1）创建数据库 create database 数据库名;（2）查看所有的数据库 show databases；（3）查看某个数据库的详细定义语句 show create database 数据库名;mysql> show create database atguigudb;+-----------+--------------------------------...
复制链接

扫一扫