MYSQL 二、SQL语句总结 2 (创建和管理表 以及 数据增删改)

一、创建和管理数据库

标识符命名规则：

• 数据库名、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起来
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据 类型在一个表里是整数，那在另一个表里可就别变成字符型了

1）创建数据库

# 方式1：创建数据库
CREATE DATABASE 数据库名;

# 方式2：创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

# 方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ），
# 如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;

* 注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删
旧库完成的。

2）使用数据库
 

# 查看当前所有的数据库
SHOW DATABASES; #有一个S，代表多个数据库

# 查看当前正在使用的数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数

# 查看指定库下所有的表
SHOW TABLES FROM 数据库名;

# 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

#　使用/切换数据库
USE 数据库名;

＊　注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数
据库名.”。

3）修改数据库

# 更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等

# 方式1：删除指定的数据库
DROP DATABASE 数据库名;

# 方式2：删除指定的数据库（ 推荐 ）
DROP DATABASE IF EXISTS 数据库名;

二、创建表

1）创建表

# 创建方式1
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
* 加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；
* 如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

# 举例1 ：
CREATE TABLE emp ( -- 创建表
emp_id INT, -- int类型
emp_name VARCHAR(20), -- 最多保存20个中英文字符
salary DOUBLE, -- 总位数不超过15位
birthday DATE -- 日期类型
);

* MySQL在执行建表语句时，将id字段的类型设置为int(11)，这里的11实际上是int类型指定的显示宽度，
* 默认的显示宽度为11。也可以在创建数据表的时候指定数据的显示宽度。

# 举例2：
CREATE TABLE dept(
deptno INT(2) AUTO_INCREMENT, -- int类型，自增
dname VARCHAR(14),
loc VARCHAR(13),
PRIMARY KEY (deptno) -- 主键
);
* 在MySQL 8.x版本中，不再推荐为INT类型指定显示长度，并在未来的版本中可能去掉这样的语法。





# 创建方式2
* 使用 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;

2）查看数据表结构

# 在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用 DESCRIBE/DESC 语句查看数据表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构。
语法格式如下：
SHOW CREATE TABLE; 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

三、修改表

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现：

• 向已有的表中添加列
• 修改现有表中的列
• 删除现有表中的列
• 重命名现有表中的列

  # 1.追加一个列
  * 语法格式如下：
  ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
  
  * 举例：
  ALTER TABLE dept80 ADD job_id varchar(15)
  
  
  # 2.修改一个列
  * 可以修改列的数据类型，长度、默认值和位置
  * 修改字段数据类型、长度、默认值、位置的语法格式如下：
  ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名
  2】;
  
  * 举例：
  ALTER TABLE dept80 MODIFY last_name VARCHAR(30);
  ALTER TABLE dept80 MODIFY salary double(9,2) default 1000;
  
  * 对默认值的修改只影响今后对表的修改
  * 还可以通过此种方式修改列的约束。
  
  
  # 3.重命名一个列
  * 使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：
  ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
  * 举例：
  ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);
  
  
  # 4.删除一个列
  * 删除表中某个字段的语法格式如下：
  ALTER TABLE 表名 DROP 【COLUMN】字段名
  
  * 举例：
  ALTER TABLE dept80 DROP COLUMN job_id;
  
  
  # 5. 重命名表
  * 方式一：使用RENAME
  RENAME TABLE emp TO myemp;
  
  * 方式二：
  ALTER table dept RENAME [TO] detail_dept; -- [TO]可以省略
  
  
  # 6.删除表
  * 在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
  * 数据和结构都被删除
  * 所有正在运行的相关事务被提交
  * 所有相关索引被删除
  * 语法格式：
  DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
  
  * IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存
  * 在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。
  
  * 举例：
  DROP TABLE dept80;
  * DROP TABLE 语句不能回滚
  
  # 7. 清空表
  * TRUNCATE TABLE语句：
      * 删除表中所有的数据
      * 释放表的存储空间
  
  * 举例：
  TRUNCATE TABLE detail_dept;
  * TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
  * 对比：
  SET autocommit = FALSE;
  
  DELETE FROM emp2;
  #TRUNCATE TABLE emp2;
  
  SELECT * FROM emp2;
  
  ROLLBACK; //事务回滚
  
  
  
  

四、数据处理之增删改

        1）插入数据

# 方式1：VALUES的方式添加
* 使用这种语法一次只能向表中插入一条数据。
* 情况1：为表的所有字段按默认顺序插入数据
INSERT INTO 表名 VALUES (value1,value2,....);

* 值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
* 举例：
INSERT INTO departments VALUES (70, 'Pub', 100, 1700);
INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);


* 情况2：为表的指定字段插入数据
INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);

*为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。
* 在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要与column1,...columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。
* 举例：
INSERT INTO departments(department_id, department_name) VALUES (80, 'IT');


* 情况3：同时插入多条记录
* INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，* 基本语法格式如下：

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

或

INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

* 举例：
INSERT INTO emp(emp_id,emp_name)
VALUES (1001,'shkstart'),
(1002,'atguigu'),
(1003,'Tom');

* 小结：
* VALUES 也可以写成 VALUE ，但是VALUES是标准写法。
* 字符和日期型数据应包含在单引号中。




#  方式2：将查询结果插入到表中
* INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要* 使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多 
* 行。

* 基本语法格式如下：
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]
* 在 INSERT 语句中加入子查询。
* 不必书写 VALUES 子句。
* 子查询中的值列表应与 INSERT 子句中的列名对应。

* 举例：
INSERT INTO emp2 SELECT * FROM employees WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
        SELECT employee_id, last_name, salary, commission_pct 
        FROM employees
        WHERE job_id LIKE '%REP%';

        2）更新数据

#　使用 UPDATE 语句更新数据。语法如下：
UPDATE table_name　SET column1=value1, column2=value2, … , column=valuen　[WHERE condition]

* 使用 WHERE 子句指定需要更新的数据。
UPDATE employees SET department_id = 70 WHERE employee_id = 113;

* 如果省略 WHERE 子句，则表中的所有数据都将被更新。
UPDATE copy_emp SET department_id = 110;

        3）删除数据

# 使用 DELETE 语句从表中删除数据
DELETE FROM table_name [WHERE <condition>];

* table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，
* DELETE语句将删除表中的所有记录。

* 使用 WHERE 子句删除指定的记录。
DELETE FROM departments WHERE department_name = 'Finance';

* 如果省略 WHERE 子句，则表中的全部数据将被删除
DELETE FROM copy_emp;

4) MySQL8新特性：计算列

# 什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列
不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

# 在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲
解。

* 举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的
值。 首先创建测试表tb1，语句如下：
CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

* 插入演示数据，语句如下：
INSERT INTO tb1(a,b) VALUES (100,200);

* 查询数据表tb1中的数据，结果如下：
SELECT * FROM tb1;

* 更新数据中的数据，语句如下：
 UPDATE tb1 SET a = 500;

5）综合案例：

#1、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';
#指定使用哪个数据库
USE test01_library;


#2、创建表 books
CREATE TABLE books(
id INT,
name VARCHAR(50),
`authors` VARCHAR(100) ,
price FLOAT,
pubdate YEAR ,
note VARCHAR(100),
num INT
);


#3、向books表中插入记录
# 1）不指定字段名称，插入第一条记录
INSERT INTO books
VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);
# 2）指定所有字段名称，插入第二记录
INSERT INTO books (id,name,`authors`,price,pubdate,note,num)
VALUES(2,'EmmaT','Jane lura',35,1993,'Joke',22);
# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books (id,name,`authors`,price,pubdate,note,num) VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);


# 4、将小说类型(novel)的书的价格都增加5。
UPDATE books SET price=price+5 WHERE note = 'novel';


# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE books SET price=40,note='drama' WHERE name='EmmaT';


# 6、删除库存为0的记录。
DELETE FROM books WHERE num=0;

# 7、统计书名中包含a字母的书
SELECT * FROM books WHERE name LIKE '%a%';

# 8、统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num) FROM books WHERE name LIKE '%a%';

# 9、找出“novel”类型的书，按照价格降序排列
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC;

# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT * FROM books ORDER BY num DESC,note ASC;

# 11、按照note分类统计书的数量
SELECT note,COUNT(*) FROM books GROUP BY note;

# 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30;

# 13、查询所有图书，每页显示5本，显示第二页
SELECT * FROM books LIMIT 5,5;

# 14、按照note分类统计书的库存量，显示库存量最多的
SELECT note,SUM(num) sum_num FROM books GROUP BY note ORDER BY sum_num DESC LIMIT 0,1;

# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(name,' ',''))>=10;

/*
16、查询书名和类型，
其中note值为 novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
*/
SELECT name AS "书名" ,note, CASE note
WHEN 'novel' THEN '小说'
WHEN 'law' THEN '法律'
WHEN 'medicine' THEN '医药'
WHEN 'cartoon' THEN '卡通'
WHEN 'joke' THEN '笑话'
END AS "类型"
FROM books;

# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
SELECT name,num,CASE
WHEN num>30 THEN '滞销'
WHEN num>0 AND num<10 THEN '畅销'
WHEN num=0 THEN '无货'
ELSE '正常'
END AS "库存状态"
FROM books;

# 18、统计每一种note的库存量，并合计总量
SELECT IFNULL(note,'合计总库存量') AS note,SUM(num) FROM books GROUP BY note WITH
ROLLUP;

# 19、统计每一种note的数量，并合计总量
SELECT IFNULL(note,'合计总数') AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP;

# 20、统计库存量前三名的图书
SELECT * FROM books ORDER BY num DESC LIMIT 0,3;

# 21、找出最早出版的一本书
SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1;

# 22、找出novel中价格最高的一本书
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 0,1;

# 23、找出书名中字数最多的一本书，不含空格
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC LIMIT 0,1;