二、数据库管理

一、sql结束符

• sql命令行，输入sql后则等待结束符，因此可以将同一个sql分多行输入
• 如果不输入结束符，则按enter就会跳转到下一行

；           结束符
# 只在mysql自带的cli中生效
\G           垂直展示结束符    
\c           取消执行

# 1. 查询当前mysql的时间
select now();

# 2. 查询当前账户的用户名，返回当前用户@服务端的内网ip   shuzhan@117.35.135.138
select user();    

# 3. 查询当前版本号
select version();

# 4. \G 作为sql语句结束符，也可以使用 \G来结束，查询结果垂直显示
#    如果输入的语句比较短，则 \G没有什么意义，如果较长，则方便查看；
#    只在mysql自带的cli中生效
select user(),now(),version()\G

# 4.  \c : 输入前面的较长的sql，如果不想执行了，则 \c 来取消当前sql的执行；
select now(),version(),user()\c

# 5. 同一行输入多条指令，用  ； 隔开即可，多条sql可以一起执行
select now();select version();select user();

# 6. 关键字，函数等使用大写，    库名，表名，列名采用小写；

二、库

# 3. 选择切换数据库，选择后再可以查看当前数据库
USE lucycat; 
-- 3.1 不一定非要切换数据库，可以在当前库采用 库名.表名来调用其他库的表；
-- 3.2 每次从mysql断开重新打开，当前使用的库就是null;

# 4. 创建库
-- 4.1. 直接创建库，库名重复则报错
CREATE DATABASE shuzhan_db;
-- 4.2. 如果当前库存在，则不创建
CREATE DATABASE IF NOT EXISTS shuzhan_db;

# 5. 删除库
DROP DATABASE shuzhan_db;

三、表

1 普通表

• 一般表操作时候，要么就是库名.表名，要么就是在sql脚本前面指定用那个库

# 1. 列出当前数据库所有的表
SHOW TABLES;

# 2. 创建最普通的表
CREATE TABLE cnip (name char(20),weight INT,sex ENUM('F','M'));

-- 2.1 查看表的相关信息
SHOW CREATE TABLE cnip;
--2.2. 用上述指令查看后的信息：mysql会自动默认补全如下
CREATE TABLE `cnip` (
  `name` char(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `sex` enum('F','M') COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. 判断再创建：
-- 3.1. 只比较表名，并不会去比较表结构;
-- 3.2. 适用于批处理文件，如果重复表名，则会给出warnings信息，脚本可以继续执行；
CREATE TABLE IF NOT EXISTS  cnip ( name char(20),weight INT,sex ENUM('F','M'));


-- 4. 采用存储引擎创建
-- 存储引擎名字不区分大小写，如果该存储引擎在mysql中未启用，则会使用默认存储引擎；
CREATE TABLE `cnip` (
  `name` char(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `sex` enum('F','M') COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4.1. 修改存储引擎
ALTER TABLE cnip ENGINE = MyISAM;

2 分区表

• 数据量大的表，读写操作效率低，将一个大表的行数据，分配到不同的表；

-- 1.按照时间分区
-- 在var/lib/mysql/库名 目录下，会创建 phone#P#p1.ibd类似的数据保存数据文件
CREATE TABLE phone (id INT,name CHAR(20),created_time DATETIME NOT NULL)

PARTITION BY RANGE(YEAR(created_time))

(PARTITION p0 VALUES LESS THAN (2010),
PARTITION p1 VALUES LESS THAN (2011),
PARTITION p2 VALUES LESS THAN (2012),
PARTITION p3 VALUES LESS THAN (2013),
PARTITION pmax VALUES LESS THAN MAXVALUE);

--2. 如果后续数据都超过了2013，则可以对pmax分区进行再分区
ALTER TABLE phone REORGANIZE PARTITION pmax
INTO(
PARTITION p4 VALUES LESS THAN(2014),
PARTITION p5 VALUES LESS THAN(2015),
PARTITION pmax VALUES LESS THAN MAXVALUE
)

3 表描述

# 1. 描述一个表
DESC/DESCRIBE/EXPLAIN president;
SHOW COLUMNS/FIELDS FROM president;

DESC/DESCRIBE/EXPLAIN president '%name';       // 只描述带有name的字段
SHOW COLUMNS/FIELDS FROM president LIKE '%name';  

# 2.  查看行数据的详细信息
SHOW FULL FIELDS/COLUMNS FROM president;

4 删除表

# 1. 删除一个表
DROP TABLE cnip;
# 2. 删除多个表
DROP TABLE cnip,phone;
# 3. 批处理执行时候，
DROP TABLE IF EXISTS cnip;

5 复制表

# 1. 仅仅复制表的结构
CREATE TABLE cn_phone LIKE phone;

# 2. 复制表结构和数据
CREATE TABLE us_phone
SELECT * FROM phone;

# 3. 原表的部分复制
CREATE TABLE us_apple_phone
SELECT * FROM phone WHERE brand = 'Apple';   

四、行

1 插入

# 1.1. 插入一条，全列插入：必须包含所有的列数据
INSERT INTO president VALUES (1,"shu","zhan");

# 1.2. 插入多条，全列插入
#   批量插入执行效率更高
INSERT INTO president VALUES (2,"wu","wang"),(3,"si","li");

# 2.1 插入一条，指定列名插入
# 对于自增的键，如果赋值就取赋值的，不赋值则取默认的
INSERT INTO president (last_name,first_name,id) VALUES ("八","王",10);
# 2.2 插入多条，指定列名插入
INSERT INTO president (last_name,first_name,id) VALUES ("六","赵",5),("七","田",6);

# 3 set赋值方法：主键就没有给值，采取默认的即可
INSERT INTO president SET first_name = "朱", last_name = "院长";

2 删除更新

# 1. 删除: 删除所有数据
DELETE FROM cnip;
-- 指定删除： 一般删除时候，必须加where子句，避免误删
DELETE FROM cnip WHERE name = 'zhangsan';

# 2. 更新操作: 更新所有数据的name
UPDATE cnip SET name = 'sz';
-- 指定更新：更新时候，一般必须加where子句，避免误更新
UPDATE cnip SET name = 'sz' WHERE id = 4;
-- 指定更新多列数据
UPDATE cnip SET name = 'sz', color = 'red' WHERE id = 4;
-- 更新某列的值为NULL
UPDATE cnip SET name = NULL where id = 4;