08-创建和管理表

素颜马尾好姑娘i 2022-09-09 13:42 169阅读 0赞

### 文章目录 ###

*   *  1. 数据库的创建与删除
     *  2. 表的创建
     *   *  2.1 CREATE TABLE语法：
         *  2.2 使用子查询创建表
     *  3. 表的修改
     *  4. 表的删除
     *  5. 表的复制
     *  6. 数据类型（了解）
     *   *  6.1 整型
         *  6.2 小数
         *  6.3 字符型
         *  6.4 日期型

--------------------

## 1. 数据库的创建与删除 ##

**（1）数据库的创建：**

语法：

create database [if not exists] 库名

创建一个保存员工信息的数据库

create database employees;
    create database if not exists employees;  # 先判断是否存在，以防止创建重名的数据库

相关其他命令

show databases;   # 查看当前所有数据库
    use employees;    # “使用”一个数据库，使其作为当前数据库

命名规则

*  数据库名不得超过30个字符，变量名限制为29个
 *  必须只能包含A–Z, a–z, 0–9, \_共63个字符
 *  不能在对象名的字符间留空格
 *  必须不能和用户定义的其他对象重名
 *  必须保证你的字段没有和保留字、数据库系统或常用方法冲突
 *  保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了

**（2）数据库的修改：**

库一般不能修改，也没有指定的语句对库进行重命名

更改库的字符集

ALTER DATABASE employees CHARACTER SET 新名字;

**（3）数据库的删除：**

DROP DATABASE employees;
    DROP DATABASE if exists employees;

## 2. 表的创建 ##

### 2.1 CREATE TABLE语法： ###

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

必须具备：CREATE TABLE权限、存储空间

必须指定：表名、列名, 数据类型, 尺寸

CREATE TABLE IF NOT EXISTS stuinfo(
    	stuId INT,           --编号
    	stuName VARCHAR(20), --姓名
    	gender CHAR,         --性别
    	bornDate DATETIME    --出生日期
    );
    DESC stuinfo;
    
    CREATE TABLE emp (    
        emp_id INT AUTO_INCREMENT,   --int类型，自增   
        emp_name CHAR (20),          --最多保存20个中英文字符   
        salary DOUBLE,               --总位数不超过15位
        birthday DATE,               --日期类型
        PRIMARY KEY (emp_id)         --主键
    ) ;

### 2.2 使用子查询创建表 ###

使用AS subquery 选项，将创建表和插入数据结合起来

CREATE TABLE table [(column, column...)]
    AS subquery;

*  指定的列和子查询中的列要一一对应
 *  通过列名和默认值定义列

复制现有的表：

create table emp1 as select * from employees;
    create table emp2 as select * from employees where 1=2;
    --创建的emp2是空表，只是复制了对应的字段

CREATE TABLE dept80
    AS
        SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
        FROM employees
        WHERE department_id = 80;

## 3. 表的修改 ##

语法：

alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

使用ALTER TABLE 语句可以实现：

**（1）向已有的表中添加列**

ALTER TABLE auther ADD COLUMN annual DOUBLE;

**（2）修改现有表中的列的类型或者约束**

可以修改列的数据类型, 尺寸和默认值

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

CREATE TABLE emp5(
        id INT(7),
        first_name VARCHAR(25),
        last_name VARCHAR(25),
        dept_id INT(7)
    );
    --将列last_name的长度增加到50
    ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);

**（3）删除现有表中的列**

ALTER TABLE author DROP COLUMN annual;

**（4）修改现有表中的列名**

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

**（5）修改表名**

ALTER TABLE author RENAME TO book_auther;

## 4. 表的删除 ##

语法：

DROP TABLE IF EXISTS book_author;
    SHOW TABLES;

*  数据和结构都被删除
 *  所有正在运行的相关事务被提交
 *  所有相关索引被删除
 *  DROP TABLE 语句不能回滚

通用的写法：

DROP DATABASE IF EXISTS 旧库名;
    CHANGE DATABASE 新库名;
    
    DROP TABLE IF EXISTS 旧表名;
    CHANGE TABLE 新表名;

**清空表：TRUNCATE TABLE 语句:**

*  删除表中所有的数据
 *  释放表的存储空间

TRUNCATE TABLE detail_dept;

TRUNCATE语句不能回滚

可以使用DELETE 语句删除数据,可以回滚

## 5. 表的复制 ##

INSERT INTO author VALUES
    (1,'村上春树','日本')，
    (2,'莫言','中国')，
    (3,'金庸','中国')，

仅仅复制表的结构

CREATE TABLE copy LIKE author;

复制表的结构加数据

CREATE TABLE copy2
    SELECT * FROM author;

只复制部分数据

CREATE TABLE copy3
    SELECT id,au_name
    FROM author
    WHERE nation='中国'；

仅复制某些字段

CREATE TABLE copy4
    SELECT id,au_name
    FROM author
    WHERE 0； --写一个肯定不满足的条件，这样就不会有数据复制过去

## 6. 数据类型（了解） ##

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_20_color_FFFFFF_t_70_g_se_x_16]

**分类：**

数值型：整型、小数（定点数、浮点数）

字符型：

*  较短的文本：char，varchar
 *  较长的文本：text，blob

日期型

### 6.1 整型 ###

<table> 
 <thead> 
  <tr> 
   <th align="center">整数类型</th> 
   <th align="center">字节</th> 
   <th align="center">范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">Tinyint</td> 
   <td align="center">1</td> 
   <td align="center">有符号：-128~127<br>无符号：0~255</td> 
  </tr> 
  <tr> 
   <td align="center">Smallint</td> 
   <td align="center">2</td> 
   <td align="center">有符号：-32768~32767<br>无符号：0~65535</td> 
  </tr> 
  <tr> 
   <td align="center">Mediumint</td> 
   <td align="center">3</td> 
   <td align="center">有符号：-8388608~8388607<br>无符号：0~1677215<br>(好吧，反正很大，不用记住)</td> 
  </tr> 
  <tr> 
   <td align="center">Int、integer</td> 
   <td align="center">4</td> 
   <td align="center">有符号：- 2147483648~2147483647<br>无符号：0~4294967295<br>(好吧，反正很大，不用记住)</td> 
  </tr> 
  <tr> 
   <td align="center">Bigint</td> 
   <td align="center">8</td> 
   <td align="center">有符号：-9223372036854775808~9223372036854775807<br>无符号：0~9223372036854775807*2+1<br>(好吧，反正很大，不用记住)</td> 
  </tr> 
 </tbody> 
</table>

如何设置无符号和有符号？

DROP TABLE IF EXISTS tab_int
    CREATE TABLE tab_int(
        t1 INT,
        t2 INT UNSIGNED
    );
    DESC tab_int;

特点：

（1）如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字

（2）如果插入的数值超出了整型的范围。会报out of range异常，并且插入临界值

（3）如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

### 6.2 小数 ###

定点数：DEC(M,D)，DECIMAL(M,D)

浮点数：float(M,D)（4字节）,double(M,D)（8字节）

特点：

（1）M：整数部分+小数部分，D：小数部分，如果超出范围则插入临界值

（2）M和D都可以省略，如果是decimal，则M默认是10，D默认是0，如果是float和double，则会根据插入的数值的精度来决定精度；

（3）定点型的精确度较高，如果要求插入的数值的精度较高，如货币运输等则考虑使用  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_20_color_FFFFFF_t_70_g_se_x_16 1]

### 6.3 字符型 ###

较短的文本：char、varchar

较长的文本：text、blob（较大的二进制）

**char和varchar类型**

说明：用来保存MySQL中较短的字符串。

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>写法</th> 
   <th>M的意思</th> 
   <th>特点</th> 
   <th>空间耗费</th> 
   <th>效率</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char</td> 
   <td>char(M)</td> 
   <td>最大的字符数，可以省略，默认为1</td> 
   <td>固定长度的字符</td> 
   <td>比较耗费</td> 
   <td>高</td> 
  </tr> 
  <tr> 
   <td>varchar</td> 
   <td>varchar(M)</td> 
   <td>最大的字符数，不可以忽略</td> 
   <td>可变长度的字符</td> 
   <td>比较节省</td> 
   <td>低</td> 
  </tr> 
 </tbody> 
</table>

**binary和varbinary类型**

说明：类似于char和varchar，不同的是它们包含二进制字符串而不包含非二进制字符串。

**Enum类型**

说明: 又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。

如果列表成员为1~255，则需要1个字节存储如果列表成员为255~65535，则需要2个字节存储,最多需要65535个成员！

**Set类型**

说明：和Enum类型类似，里面可以保存0~64个成员。和Enum类型最大的区别是：SET类型一次可以选取多个成员，而Enum只能选一个根据成员个数不同，存储所占的字节也不同

### 6.4 日期型 ###

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_20_color_FFFFFF_t_70_g_se_x_16 2]  
datetime和timestamp的区别

1、Timestamp支持的时间范围较小，取值范围：19700101080001——2038年的某个时间，Datetime的取值范围：1000-1-1 ——9999—12-31

2、timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时的当地时区

3、timestamp的属性受Mysql版本和SQLMode的影响很大

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/bcfe443b6d38460784a88f5ff1a800ef.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220829/24c0699e3b2842febbf54cc4655f3c66.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220829/714e6dd9a3e349f885febe4a358bbd36.png