MySQL数据库与表的操作详解-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_42874319/article/details/120040777

文章目录

1. 数据库的创建与删除

（1）数据库的创建：

语法：

create database [if not exists] 库名

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

create database employees;
create database if not 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. 数据类型（了解）

在这里插入图片描述

分类：

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

字符型：

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

日期型

6.1 整型

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

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

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）定点型的精确度较高，如果要求插入的数值的精度较高，如货币运输等则考虑使用
在这里插入图片描述

6.3 字符型

较短的文本：char、varchar

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

char和varchar类型

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

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