MySQL基本语法及其操作（二）

奕宸Long

于 2022-08-01 19:24:17 发布

阅读量254

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_54435916/article/details/126077017

版权

一、MySQL中枚举的用法

1、枚举（enum）：

实现将所有的可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个

2、枚举使用方法：

enum（可能出现的列表）; //存储数据，只能存储上面定义好的数据

3、使用枚举的好处：

（1）限定值的可能性，规范数据格式

（2）速度快，枚举型是利用整数进行管理的，能够2个字节进行管理

（3）节省存储空间（枚举通常有一个别名：单选框），枚举实际存储的是数值，而不是字符串本身

4、创建枚举表

create table my_enum(
    -> sex enum('男','女','保密')
    -> );

insert into my_enum(sex) values('男');

insert into my_enum(sex) values('女'),('保密');   //批量插入

insert into my_enum(sex) values(1),(3);      //枚举实际存储的是数值，所以可以直接插入数值,枚举        
                                             //元素的实际规律：按照元素出现的顺序，从1开始编号

mysql> select sex + 0,sex from my_enum;
+---------+------+
| sex + 0 | sex  |
+---------+------+
|       1 | 男     |
|       2 | 女    |
|       3 | 保密     |
|       1 | 男     |
|       3 | 保密     |

二、修改表约束条件

1、增加主键约束：

alter table 表名 add constraint 约束名 primary key(列名);

mysql> create table Users(
    -> id int,
    -> username varchar(30) not null,
    -> password varchar(10) not null
    -> );

mysql> alter table Users add constraint u_id primary key(id);   //增加主键约束

2、增加唯一性约束：

alter table 表名 add constraint 约束名 unique(列名);

 alter table Users add constraint u_name unique(username);

3、增加外键约束：

 create table LoginHistorys(
    -> id int primary key auto_increment,
    -> time datetime not null
    -> );

alter table LoginHistorys add column uid int;

//增加外键约束
alter table LoginHistorys add constraint L_id foreign key(uid) references Users(id);

3、删除表字段唯一性约束：

只需删除该字段的唯一性索引即可，语法格式如下（注意需指定唯一性索引的索引名）。

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

 alter table Users drop index u_name;

4、删除外键约束：

需指定外键约束名称，语法格式如下：

alter table 表名drop foreign key 约束名

alter table LoginHistorys drop foreign key L_id;

5、删除主键约束：

注意：有关联的时候，先删除外键约束，再删除关联表的主键约束

alter table 表名drop primary key;

 alter table Users drop primary key;

三、数据命令

1、增加数据命令：

insert into 表名（列名1，列名2，列名3.。。。）values (值1，值2，值3.。。。)

批量插入：

insert into 表名[(列名……)] values (第一组值), (第二组值), (第三。。。。);

create table dept(
    -> deptno int primary key auto_increment,
    -> dname varchar(20) not null,
    -> loc varchar(30)
    -> );

insert into dept values(1,'开发部','A区'),(2,'研发部','B区');

insert into dept(dname,loc) values('人力资源部','C区'),('办公部','D 区');

有关时间的问题：

sysdate() #得到系统当前时间

日期date： ‘yyyy-MM-dd’

datetime ‘yyyy-MM-dd HH:mm:ss’

timestamp ‘yyyy-MM-dd HH:mm:ss’

time ‘HH:mm:ss’

注：如果timestamp 和datetime中插入null, timestamp中插入的还是系统的当前时间。

Datetime中插入的是空。

mysql> insert into emp values(1,'小白','开发',0,'2019-9-16',3000,1000,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(2,'小名','开发',0,sysdate(),3000,1000,2);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from emp;
+-------+-------+------+------+------------+---------+---------+--------+
| empno | ename | job  | mgr  | hiredate   | sal     | comm    | deptno |
+-------+-------+------+------+------------+---------+---------+--------+
|     1 | 小白     | 开发     | 0    | 2019-09-16 | 3000.00 | 1000.00 |      1 |
|     2 | 小名     | 开发     | 0    | 2022-07-31 | 3000.00 | 1000.00 |      2 |
+-------+-------+------+------+------------+---------+---------+--------+
2 rows in set (0.00 sec)

2、删除数据命令

（1） delete from 表名 [where 条件];

（2）清除数据（truncate 表名）

清除和删除的区别：

1、删除（delete）：

可以添加where条件删除指定数据，数据可以恢复，通过回滚rollback; 为保证数据恢复，能够记日志，速度慢

2、回滚

delete删除所有记录（被删除的记录是保存在日志文件中的，所以可以被恢复。

mysql> set autocommit=0;   ##数据库默认是自动提交，值1.设置为手动提交
Query OK, 0 rows affected (0.00 sec)

mysql> delete from emp4;
Query OK, 14 rows affected (0.00 sec)

mysql> ROLLBACK;   #数据没有删除rollback回滚可恢复。
Query OK, 0 rows affected (0.01 sec)

mysql> select * from emp4;

2、清除(truncate)：

不能添加where条件，只能全表删除，数据不可以恢复。没有记日志功能，速度快

truncate清除数据（不能恢复）

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> TRUNCATE table emp5;
Query OK, 0 rows affected (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp5;    #数据全部删除，无法回滚
Empty set (0.00 sec)

（3）删除基础表（主表）中的记录时，如果这条记录已经被别的表引用了，那么这条记录不能删除。如果没有被引用，那么是可以删除的。

（4）删除主表的数据，看级联关系

mysql> create table dept1 select * from dept;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> create table emp1 select * from emp;
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

#添加主键约束
mysql> alter table dept1 add constraint primary key(deptno);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

#添加外键约束
mysql> alter table emp1 add constraint foreign key(deptno) references dept1(deptno);
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

#默认级联约束、不能删除或更新父行:外键约束失败
mysql> delete from dept1 where deptno=10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`l1`.`emp1`, CONSTRAINT `emp1_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept1` (`deptno`))

#更改级联约束
mysql> alter table emp1 add constraint foreign key(deptno) references dept1(deptno) on delete set null;
Query OK, 16 rows affected (0.02 sec)
Records: 16  Duplicates: 0  Warnings: 0

#删除数据
mysql> delete from dept1 where deptno=10;
Query OK, 1 row affected (0.00 sec)

#更改级联约束
mysql> alter table emp1 add constraint foreign key(deptno) references dept1(deptno) on delete cascade;
Query OK, 16 rows affected (0.02 sec)
Records: 16  Duplicates: 0  Warnings: 0

#删除数据，数据被删除，emp表中外键是20数据也被删除
mysql> delete from dept1 where deptno=20;
Query OK, 1 row affected (0.00 sec)

on delete no action---默认

如果主表数据被外键引用，不允许删除。如果没被引用可以删除。

on delete set null---外键置空

如果主表数据被外键引用，允许删除，外键值为null；

on delete cascade---级联删除

如果主表数据被外键引用，允许删除，关联数据也被删除。

3、修改数据命令

update 表名

Set 列名1=值1[, 列名2=值2,。。。。]

[Where 条件语句]

例子：将emp1表中修改7782员工的新工资为原工资的150%，工作为“保洁”

update emp1
    -> set sal = sal *1.5,job='保洁'
    -> where empno = 7900;

4、查询命令

select * from 表名;

四、数据库相关概念

1、三大范式

（1）第一范式：字段不能再分

例如：将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储

（2）第二范式：每个表必须有主键，必须让表中每列都和主键相关

在数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

（3）第三范式：确保每列都和主键列直接相关,而不是间接相关

一个表只能有自己的列（直接依赖于主键），以及外键。不能有其他表的非主键字段。

2、表与表的关联关系

（1）一对一

个体和身份证的关系

（2）一对多、多对一

班级和学生

（3）多对多

学生和选课

3、字段约束

主键字约束（primary key）值不能为空，并且值唯一

外键字约束（foreign key）值必须为指向的表的主键值

惟一性约束（unique）值唯一

非空约束 (not null) 值不能为空

检查约束（check）值为符合指定条件的值，Mysql不支持

缺省约束（default）相当于值没有约束

4、字段类型

奕宸Long

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL基本语法及其操作（二）

MySQL枚举用法、修改表的约束条件、时间、回滚、清除数据、字段类型
复制链接

扫一扫