MySql数据库操作之DDL

最新推荐文章于 2024-05-22 00:54:35 发布

未至_HB

最新推荐文章于 2024-05-22 00:54:35 发布

阅读量686

点赞数 1

分类专栏： mysql之DDL操作文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_55032390/article/details/127231119

版权

mysql之DDL操作专栏收录该内容

1 篇文章 0 订阅

订阅专栏

#********************DDL*********************
#######################数据库之操作###################
#查看当所有据库项目：

SHOW DATABASES;

#创建数据库

CREATE DATABASE mydb;
CREATE DATABASE IF NOT EXISTS mydb;

#使用某一数据库：

USE  mydb;

#修改数据库编码

ALTER DATABASE mydb CHARACTER SET utf8;

#删除数据库

DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

###########################表操作######################
#查看所有表

SHOW TABLE;

#查看表结构

DESC <表名>

#创建表
-- create table <表名>(字段一，字段二，字段三)；

CREATE TABLE student(
id INT,
sname VARCHAR(20),
gender VARCHAR(20),
age INT,
birth DATE,
address VARCHAR(20)
);

#查看指定表的创建语句

SHOW CREATE TABLE student;

-- CREATE TABLE `student` (
-- `id` INT DEFAULT NULL,
-- `sname` VARCHAR(20) DEFAULT NULL,
-- `gender` VARCHAR(20) DEFAULT NULL,
-- `age` INT DEFAULT NULL,
-- `birth` DATE DEFAULT NULL,
-- `address` VARCHAR(20) DEFAULT NULL
-- ) ENGINE=INNODB DEFAULT CHARSET=utf8mb3
#删除表
-- drop table <表名>

DROP TABLE student;

#修改表添加列
-- alter table <表名> add 列名类型（长度） [约束]

ALTER TABLE student ADD stunumber VARCHAR(20);

#修改列名和类型：
-- alter table <表名> change 旧列名新列名类型（长度）；

ALTER TABLE student CHANGE dept department VARCHAR(30);

#修改表删除列：
-- alter table <表名> drop <列名>；

ALTER TABLE student DROP department；

#修改表名：
-- rename <旧表名> to <新表名>；
-- 例如将student表的名字修改为stu

RENAME TABLE student TO stu;

#********************DML**************************
#DML:数据操作语言，用来对数据库中的表的数据记录进行更新
#关键字：插入insert 、删除delete 、更新update
#数据的插入
-- 格式一：insert into <表名>（列名一、列名二、列名三、列名四...）values(值一、值二、值三....);

INSERT INTO student(id,sname,gender,age,birth,address,stunumber)
            VALUES(1002,'张三','男',20,'2002-5-10','北京市','2028524130');

-- 格式二：insert into <表名> values(值一、值二、值三...)

INSERT INTO student 
             VALUES (1001,'王五','女',19,'2002-07-19','河南省','2028524132'),
                   (1003,'李四','男',18,'2002-05-21','河北省','2028524131');

# 数据的修改、更新
-- 格式一：
-- update <表名> set 字段名=值<通俗易懂的名字>,字段名=值...;
-- 例如：将所有的学生的地址修改为重庆

UPDATE student SET address='重庆';

-- 格式二：
-- update <表名> set 字段名=值,字段名=值...where 条件；
-- 例如将学号为1001的学生的地址修改为北京

UPDATE student SET address='重庆' WHERE id=1001;

-- 将名字为王五的学生的学号修改为1001，地址修改为北京；

UPDATE student SET id=1003,address='北京' WHERE sname='王五';

# 数据的删除
-- 格式一：
-- delete from <表名> [where 条件];
-- 格式二
-- truncate table <表名> 或者 truncate 表名
-- 例如删除学号为1003的学生数据

DELETE FROM student WHERE id=1003;

-- 删除表中的所有数据

DELETE FROM student;

-- 清空表数据

TRUNCATE TABLE student;
TRUNCATE student;

#注意delete 和truncate原理不同，delete只删除表的内容，而truncate 类似于drop table ，可以理解为删除整个表，在创建该表。

#练习
/*
1、创建员工表employee,字段如下：
id（），name（），gender（），salary（）
2、插入数据
-- 1，'张三','女',2000
-- 2, '李四','男',3000
-- 3, '王五','男',4000
3、修改表数据
-- 将所有员工薪水修改为5000元
-- 将姓名为张三的员工薪水修改为3800
-- 将姓名为李四的员工的薪水修改为5000，gender修改为女
-- 将王五的薪水在原有的基础上增加1000元

*/

USE mydb;
 
 DROP TABLE employee;
 
 CREATE TABLE employee(
 id INT,
 ename VARCHAR(20),
 gender VARCHAR(20),
 salary INT,
 CONSTRAINT pk1 PRIMARY KEY(id)
 );
 INSERT INTO employee VALUES(1,'张三','女',2000),
                            (2,'李四','男',3000),
                            (3,'王五','男',4000);
 UPDATE employee SET salary=5000;
 UPDATE employee SET salary=3800 WHERE ename='张三';
 UPDATE employee SET salary=5000,gender='女' WHERE ename='李四';
 UPDATE employee SET salary=salary+1000 WHERE ename='王五';

#******************MySql约束**********************
#约束：实际上就是表中数据的限制条件
#作用：保证表中的记录的完整新和有效性
#分类：主键约束（primary key）、自增长约束(auto_increment)、非空约束(not null)、唯一性约束(unique)、默认约束(default)、零填充约束(zerofill)、外键约束(foreign key)

############################### 主键约束（primary key）
-- 可以是一个列或者多个列的组合，其值能唯一地标识表中的每一行，方便快速查找
-- 每个表只允许有一个主键
-- 主键约束相当于唯一约束加非空约束，主键约束不允许重复，也不允许出现空值
-- 当建立主键约束的时候，系统会默认的在列或列的组合上建立对应的唯一索引
-- 操作：添加单列主键、添加多列联合主键,删除主键

# 添加单列主键
-- create table(
-- ...
-- <字段名><数据类型> primary key
-- ...
-- );
-- 方法一：

CREATE TABLE employ(
 id INT PRIMARY KEY,
 ename VARCHAR(20),
 gender VARCHAR(20),
 salary DOUBLE
 );

-- 方法二：

CREATE TABLE employ(
 id INT,
 ename VARCHAR(20),
 gender VARCHAR(20),
 salary DOUBLE,
 CONSTRAINT pk1 PRIMARY KEY(id)
 );

-- 主键的作用：主键约束的列非空且唯一

 INSERT INTO employ VALUES(1,'张三','女',2000),
                          (2,'李四','男',3000),
                          (3,'王五','男',4000);

-- 唯一性

 INSERT INTO employ VALUES(1,'韩博','女',5000);

-- 错误代码： 1062
-- Duplicate entry '1' for key 'employ.PRIMARY'
-- 非空性

INSERT INTO employ VALUES(NULL,'韩博','女',5000);

-- 错误代码： 1048
-- Column 'id' cannot be null

#添加多列主键
-- 又称联合主键，就是这个主键是由一张表中的多个字段组成的
-- 当主键是由多个字段组成的时候，不能直接在字段名后面声明主键约束
-- 一个表只能有一个主键，联合主键也是一个主键
-- create table <表名>(
-- ...
-- primary key (字段1，字段2，字段3，....字段n)
-- );

 CREATE TABLE emp(
 id INT,
 ename VARCHAR(20),
 gender VARCHAR(20),
 salary DOUBLE,
 PRIMARY KEY(id,ename)
 );

-- 联合主键的作用：联合主键的各列，每一列都不能为空，
-- 非空性

INSERT INTO emp VALUES(NULL,'韩博','女',5000);

-- 错误代码： 1048
-- Column 'id' cannot be null

 INSERT INTO emp VALUES(4,NULL,'女',5000);

-- 错误代码： 1048
-- Column 'ename' cannot be null

-- 唯一性

INSERT INTO emp VALUES(1,'张三','女',5000);

-- 错误代码： 1062
-- Duplicate entry '1-张三' for key 'emp.PRIMARY'# 通过修改表结构添加主键
-- 主键约束不仅在创建表的同时创建，也可以再修改表的时候添加
-- create table 表名(
-- ...
-- );
-- alter table <表名> add primary key(字段列表);

 CREATE TABLE emp4(
 id INT,
 ename VARCHAR(20),
 gender VARCHAR(20),
 salary DOUBLE
 );
 ALTER TABLE emp4 ADD PRIMARY KEY(id,ename);
 INSERT INTO emp4 VALUES(1,'张三','女',2000),
                        (2,'李四','男',3000),
                        (3,'王五','男',4000);
 INSERT INTO emp4 VALUES(1,'张三','女',5000);

# 删除主键约束

-- 格式

 ALTER TABLE <数据表名> DROP PRIMARY KEY;

-- 删除单列主键

 ALTER TABLE employ DROP PRIMARY KEY;

-- 删除联合主键

ALTER TABLE emp4 DROP PRIMARY KEY;
 ALTER TABLE emp DROP PRIMARY KEY;

###################自增长约束（auto_increment）
-- 当主键定义为自增长的时候，这个主键的值就不需要用户输入数据了，而由数据库系统根据定义自动赋值，每增加一条记录，主键会自动一相同的步伐进行增长
-- 字段名数据类型 auto_increment
-- 默认情况下，auto_increment的初始值是1，每增一条记录，字段值自动加1。若创建的某一个表的第一条记录设置了字段的初始值，，那么新增加记录就从这个初始值开始自增
-- 方法一：在创建表的时候指定自增字段的初始值

CREATE TABLE t_user2(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20)
 )AUTO_INCREMENT=100;

-- 方法2：

 CREATE TABLE t_user2(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20)
 );
 ALTER TABLE t_user2 AUTO_INCREMENT=100;

-- 一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或者是主键的一部分）
-- auto_increment约束的字段必须具备NOT NULL属性
-- auto_increment约束的字段只能是整数类型（TINYINT,SMALLINT,INT,BIGINT）
-- auto_increment约束字段的最大值受到该字段的数据类型约束，如果达到上限，euto_increment会自动失效。
-- delete 清空表之后，再添加数据自增长从断点
-- tuncate 清空表之后，自增长从一开始

CREATE TABLE t_user1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)

************************非空约束(NOT NULL)
-- 添加方式
-- 01 创建表的时候指定：create table <表名>(字段名字段类型 not null);

CREATE TABLE t_user3(
id INT,
tname VARCHAR(20) NOT NULL,
address VARCHAR(20) NOT NULL
);

INSERT INTO t_user3(id) VALUES(1001);

-- 这种情况，则表示，name、address字段为空值，不可以
-- 错误代码： 1364
-- Field 'tname' doesn't have a default value

INSERT INTO t_user3(id,tname,address) VALUES(1001,NULL,NULL);

-- 这种情况，则表示，name、address字段为空值,也不可以
-- 错误代码： 1048
-- Column 'tname' cannot be null

INSERT INTO t_user3(id,tNAME,address) VALUES(1001,'null','null');

-- 可以
-- -- 1 queries executed, 1 success, 0 errors, 0 warnings

INSERT INTO t_user3(id,tNAME,address) VALUES(1001,'','');

-- 可以
1 queries executed, 1 success, 0 ERRORS, 0 WARNINGS

-- 02 创建表之后修改表结构添加约束 alter table <表名> add not null()

CREATE TABLE t_user3(
id INT,
tNAME VARCHAR(20),
address VARCHAR(20)
);
ALTER TABLE t_user3 MODIFY tname VARCHAR(20) NOT NULL;
ALTER TABLE t_user3 MODIFY address VARCHAR(20) NOT NULL;

-- 03 删除非空约束

ALTER TABLE t_user3 MODIFY tname VARCHAR(20);
ALTER TABLE t_user3 MODIFY address VARCHAR(20);

#######################唯一约束(unique)
-- 唯一约束（unique key）是指所有记录中字段的值不能重复出现。
-- 唯一约束方式；
-- 方式一：<字段名> <数据类型> unique
-- 方式二：alter table <表名> add constraint 约束名 unique(列)；
-- 删除唯一约束：alter table <表名> drop index <唯一约束名>；
#######################默认约束（default）
-- 用来指定某列的默认值
-- 方式一：<字段一> <数据类型> default<默认值>
-- 方式二：alter table 表名 modify <列名> <类型> default 默认值；
-- 删除默认约束：alter table <表名> change column <字段名> <类型> default null;
#######################零填充约束(zerofill)
-- 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
-- zerofill默认为int（10）
-- 当使用zerofill时，默认会自动加unsigned(无符号)属性，使用unsigned属性后，数值范围是原数据的2倍
-- 添加约束：<字段名> <数据类型> zerofill
-- 删除约束： alter table <表名> modify <字段名> <数据类型>；

CREATE TABLE t_user4(
id INT ZEROFILL,
t_name VARCHAR(20) 
); 
INSERT INTO t_user4 VALUES(123,'张三');
INSERT INTO t_user4 VALUES(1,'李四');
ALTER TABLE  t_user4 MODIFY id INT;