mysq基础学习笔记四 数据定义语言

目录

库和表的管理

常见数据类型介绍

常见约束

标识列

---

数据定义语言（data define language）

库和表的管理

一、库的管理
1.库的创建

语法:

create database 【if not exists】 库名 【character set 字符集名】;

案例：创建库Books

CREATE DATABSES [IF NOT EXISTS] books;

2.库的修改
更改库的字符集

ALTER DATABASES books CHARACTER SET 字符集名

3.库的删除

DROP DATABASES [IF EXISTS] books;

二、表的管理
1.表的创建

create table 【if not exists】表名(
    列名 列的类型【（长度） 约束】,
    列名 列的类型【（长度） 约束】,
    列名 列的类型【（长度） 约束】,
    ...
    列名 列的类型【（长度） 约束】
)

案例：创建表book

CREATE TABLE book(
    id INT,
    bName VARCHAR(20),
    price DOUBLE,
    authorId INT,
    publishDate DATEYIME
 );

案例：创建表author

CREATE TABLE author(
    id INT,
    au_name VARCHAR(20),
    nation VARCHAR(10)    
);

2.表的修改

ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN 列名 【类型 约束】

1.修改列名

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;

2.修改列的类型或约束

ALTER TABLE 表名 MODIFY COLUMN 列名 新类型【新约束】;

3.添加列

ALTER TABLE 表名 ADD COLUMN 列名 类型;

4.删除列

ALTER TABLE 表名 DROP COLUMN 列名 ;

5.修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;

3.表的删除

DROP TABLE [IF EXISTS] 表名;

通用的写法：

DROP DATABASES IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLES 表名();

4.表的复制
1.仅复制表的结构

CREATE TABLE 新表名 LIKE 旧表名;

2.复制表的结构+数据

CREATE TABLE 新表名
SELECT * FORM 旧表名

只复制部分数据

CREATE TABLE 新表名
SELECT 相关列 FORM 旧表名
where 筛选条件;

只复制部分结构

CREATE TABLE 新表名
SELECT 部分列 FORM 旧表名
where 0(1=2);

练习

-- 1. 创建表dept1
NAME  NULL?  TYPE
id            INT(7)
name          VARCHAR(25)

CREATE TABLE dept1 (id INT(7), NAME VARCHAR(25));

-- 2.将表departments中的数据插入到新表dept2中
CREATE TABLE dept2 SELECT * FROM departments;

-- 3.创建表emp5
CREATE TABLE emp5 (
	id INT (7),
	First_name VARCHAR (25),
	Last_name VARCHAR (25),
	dept_id INT (7)
)

-- 4.将列Last_name的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR (50);

-- 5.根据表employees创建表employees2
CREATE TABLE employees LIKE employees;

-- 6.删除表emp5;
DROP TABLES IF EXISTS emp5;

-- 7.将表employees2重命名为emp5;
ALTER TABLE employees2 RENAME TO emp5;

-- 8.在表emp5中添加新列test_column,
ALTER TABLE emp5 ADD COLUMN test_column INT;

-- 9.直接删除表emp5中的列dept_id
ALTER TABLE emp5 DROP COLUMN dept_id;

常见数据类型介绍

数值型：
    整型：
        分类：  tinyint smallint mediumint int\integer bigint
        字节数： 1        2        3        4            8
        特点: 
            1.如不设置有无符号，默认有符号。若要设置无符号，则添加unsigned关键字
            2.如果插入的数值超出整形范围，汇报out of range异常，并且插入临界值
            3.如不设置长度，会有默认的长度。长度代表显示的最大宽度，不过不够会用0填充，但必须搭配zerofill使用    
     
    小数：
        定点数 dec(M,D)    decimal(M,D)
        浮点型 float(M,D)  double(M,D)
        特点：M和D D表示小数部位的长度  M代表整数部分+小数部位的长度  
        M和D都可省略 如果是decimal，则M默认为10，D默认为0，如果是float和double根据插入数值的精度来决定精度
        定点型精确度较高，要求插入的数值的精度较高，如货币运算，则使用定点型

原则：所选用的类型越简单越好，能保存数值的类型越小越好
                
字符型：
      较短的文本： char varchar
      说明：用来保存mysql中较短的字符串。
      写法        M的意思                         特点      空间的耗费    效率
      char(M)：   M代表最多的字符数，可省，默认为1  M为0-255  固定长度的字符   比较耗费     高
      varchar(M)：M代表最多的字符数，不可省      M为0-255    可变长度的字符   比较节省     低

      其他:binary和varbinary用于保存较短的二进制
           enum用于保存枚举
           set用户保存集合
      较长的文本： text bolb(较大的二进制)
        
日期型：date只保存日期  time只保存时间 year 只保存年 timestamp日期+时间 datetime
特点：
                      字节     范围        时区等的影响
       datetime    8        1000-9999    不受
     timestamp   4        1970-2038    受
        
1.设置无符号和有符号

CREATE TABLE tab_date(
    t1 DATATIME,
    t2 TIMESTAMP
);

2.时间型

 create table tab_int(
    t1 INT,
    t2 INT UNSIGNED
);

常见约束

常见约束
含义：一种限制，用于限制表中的数据，为了保证表中数据的一致性
分类：六大约束
    NOT NULL:非空，保证该字段的值不为空
    DEFAULT：默认，保证该字段的值有默认值
    PRIMARY KEY:主键，保证该字段的值具有唯一性，并且非空
    UNIQUE:唯一，保证该字段的值具有唯一性，可以为空
    CHECK:检查约束（mysql不支持）
    FOREIGN KEY:外键，限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值（在从表添加外键约束，用于引用主表中某列的值）

添加约束的时机：
    1.创建表时；
    2.修改表时；
    
约束的添加分类：
     列级约束：六大约束语法上都支持，但外键约束没有效果
     表级约束：除了非空、默认，其他的都支持

主键和唯一的对比：
              保证唯一性  是否允许为空   一个表中的个数     是否允许组合
    主键         Y            N                            至多一个          Y,但不推荐
    唯一         Y            Y                            可以多个          Y,但不推荐
 
外键：
    1.要求在从表设置外键关系
    2.从表的外键列的类型和主表的关联类的类型要求一致或兼容，名称无要求
    3.主表的关联类必须是一个key(一般是主键或唯一键)
    4.输入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表

CREATE TABLE 表名(
    字段名 字段类型 列级约束,
    字段名 字段类型,
    表级约束
)

一、创建表时添加约束
1.添加列级约束
语法：直接在字段名和类型后面追加约束类型即可
只支持：默认、非空、主键、唯一

CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
    id INT PRIMARY KEY,
    stuName VARCHAR(20) NOT NULL,
    -- 不支持
    gender CHAR(1) CHECK(gender='男' OR gender = '女'), 
    seat INT UNIQUE,
    AGE INT DEFAULT 18,
    -- 不支持
    majorId INT FOREIGN KEY REFERENCES major(id); 
);

CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);

查看stuinfo表中所有的所有，包括主键外键唯一

SHOW INDEX FROM stuinfo;

添加表级约束
语法：
在各个字段的最下面 【constraint 约束名】 约束类型（字段名）

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuName VARCHAR(20) ,
    gender CHAR(1) ,
    seat INT ,
    AGE INT ,
    majorId INT ,
    CONSTRAINT pk(键名任取) PRIMARY KEY(id，stuname),#组合主键
    CONSTRAINT uq UNIQUE(seat),#唯一键
    CONSTRAINT  ck CHECK(gender='男' OR gender = '女'),#检查
    CONSTRAINT  fk_stuinfo_major FOREIGN KEY(maiorid) REFERENCES major(id) #外键
);

通用的写法：

CREATE TABLE IF EXISTS stuinfo(
    id  INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuindo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

二、修改表时添加约束
语法：
1 添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束

2 添加表级约束

alter table 表名 add 【constraint 约束名】 约束类型（字段名）【外键的引用】

1.添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

2.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3.添加主键
列级约束

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4.添加唯一键
列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

5.添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

3.修改表时删除元素
1.删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

2.删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

3.删除主键

ALTER TABLE stuinfo DROP PRIMARY KRY;

4删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

5删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

 练习

-- 1.向表emp2的id列中添加PRIMARY KEY 约束（my_emp_id_pk）
ALERT TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
ALERT TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

-- 2.向表dept2的id列中添加PRIMARY KEY 约束（my_dept_id_pk）
ALERT TABLE dept2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);

-- 3.向表emp2中添加列dept_id,并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 ADD COLUMN dept_id INT ;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

标识列

标识列又称为自增长列，可以不用手动的插入值，系统提供默认的序列值

特点：
1.标识列必须和主键搭配吗？
不一定，但要求是一个KEY

2.一个表中只能有一个标识列

3.表示列的类型只能是数值型

4.标识列可以通过SET AUTO_INCREMENT_INCREMENT=步长 设置步长，通过手动插入值设置起始值一、创建表时设置标识列

CREATE TABLE tab_identity(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

INSERT INTO tab_identity VALUES(NULL,'join');
INSERT INTO tab_identity(name) VALUES('join');
-- 改增长值
SET AUTO_INCREMENT_INCREMENT=3;

二、修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

三、修改表时删除标识列

ALTER TABLE tab_indentity MODIFY COLUMN id INT ;