库与表基本操作以及数据增删改

青旅半醒 2022-05-15 13:44 160阅读 0赞

# 库与表基本操作以及数据增删改 #

# 前言 #

数据库，后端开发者必学，而且现在以MySQL居多。家下来我们将系统化MySQL一些高级用法。打算先讲很多人关注的使用方式（增删改查以及其优化），然后就讲数据库和表的操作（很多我们学习忽略的地方），接着就是引擎还有更高级的查询等等

# 操作数据语句优化认识： #

通常情况下，当访问某张表的时候，读取者首先必须获取该表的锁，如果有写入操作到达，那么写入者一直等待读取者完成操作（查询开始之后就不能中断，因此不允许读取者完成之前进行写操作）。当读取者完成对表的操作的时候，锁就会被解除。如果写入者正在等待的时候，另一个读取操作到达了，该读取操作也会被阻塞（block），因为默认的调度策略是写入者优先于读取者。当第一个读取者完成操作并解放锁后，写入者开始操作，并且直到该写入者完成操作，第二个读取者才开始操作。

通过**LOCK TABLES**和**UNLOCK TABLES**语句可以显式地获取或释放锁，但是在通常情况下，服务器的锁管理器会自动地在需要的时候获取锁，在不再需要的时候释放锁。获取的锁的类型依赖于客户端是写入还是读取操作。

对某张表进行写入操作的客户端必须拥有独占的（排他的）访问权的锁。操作在进行的过程中，该数据表处于不一致的（inconsistent）状态，因为数据记录在删除、添加或修改的时候，数据表上的索引也可能需要更新以相互匹配。允许两个客户端同时写入一张数据表是不利的，因为这样的操作会很快使数据表中的信息成为一堆无用的垃圾。同时允许客户端读取变化之中的数据表也不正确，因为正在读取的位置中的数据可能正在变化（修改），读取的结果可能并不是真实的。因此对某张表执行读取操作的客户端也必须获取一个锁，防止在读取的过程中，其它的客户端写入或改变表。但是这个锁不需要独占的访问权。因为读取操作不会改变数据，因此没有理由让某个读取者阻止其它的读取者访问这张表。所以读取锁可允许其它的客户端在同一时刻读取这张表。  
虽然通过锁机制，可以实现多线程同时对某个表进行操作，但当某个线程作更新操作时，首先要获得独占的访问权。在更新的过程中，所有其它想要访问这个表的线程必须要等到其更新完成为止。此时就会导致锁竞争的问题，从而导致用户等待时间的延长。

因此：要提高MySQL的更新/插入效率，应首先考虑降低锁的竞争，减少写操作的等待时间。 （在后面才讨论表设计的优化）

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

CREATE TABLE `score` (
        `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
        `change_type` VARCHAR(255) NULL DEFAULT NULL,
        `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `score` INT(11) NULL DEFAULT NULL,
        `user_id` BIGINT(20) NULL DEFAULT NULL,
        PRIMARY KEY (`id`)
    )
    COLLATE='utf8_general_ci';
    
    CREATE TABLE `gag` (
        `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
        `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `gag_time` DATE NULL DEFAULT NULL,
        `user_id` BIGINT(20) NULL DEFAULT NULL,
        PRIMARY KEY (`id`)
    )
    COLLATE='utf8_general_ci';

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

# INSERT语句 #

**INSERT \[INTO\]** 表名 \[(字段列表)\] **VALUES** (值列表)\[, (值列表), …\]

**注意：**  
如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。  
可同时插入多条数据记录！

**例子：**

INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1);

**优化：**  
（1）当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真的插入工作。上述所说的同时插入多条就是一种优化。（经测试，大概10条同时插入是最高效的）

**例子：**

INSERT INTO score (change_type,score,user_id) VALUES ('吃饭',10,1),('喝茶',10,1),('喝茶',10,1);

（2）将进程/线程数控制在2倍于CPU数目相对合适  
（3）采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）

**DELETE语句：**  
DELETE FROM 表名\[ 删除条件子句\]  
没有条件子句，则会删除全部（***慎重***）

**例子：**

DELETE FROM gag;      删除全部
    DELETE FROM gag WHERE id=1;

**补充：**  
Mysql中truncate table和delete语句都可以删除表里面所有数据，但是在一些情况下有些不同！

**例子：**  
`truncate table gag;`

（1）truncate table速度要更快一些，但truncate删除后不记录mysql日志，不可以恢复数据。

（2）如果没有外键关联，innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。

（3）表有外键关联，truncate table删除表数据为逐行删除，  
如果外键指定级联删除(delete cascade)，关联的子表也会会被删除所有表数据。  
如果外键未指定级联(cascde),truncate table逐行删除数据，  
如果是父行关联子表行数据，将会报错。  
（4）auto\_increment计数器在truncate table后会重置为0.与是否有外键关联没有关系。

**注意：**  
一个大的 DELETE 或 INSERT 操作，要非常小心，因为这两个操作是会锁表的，表一锁住，其他操作就进不来了。因此，我们要交给DBA去拆分，重整数据库策略，比如限制处理1000条。  
MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以在超大型数据库中，删除时处理好索引关系非常重要。

**推荐的折中方法：**  
在删除数据之前删除这几个索引，然后删除其中无用数据，删除完成后重新创建索引。

**UPDATE语句**  
`UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]`  
例子：

UPDATE score SET change_type='洗澡' WHERE id=2;

**优化：**  
更新多条记录

Update score  
      SET change_type = CASE id  
        WHEN 1 THEN 'value1'  
        WHEN 2 THEN 'value2'  
        WHEN 3 THEN 'value3'  
      END  
    WHERE id IN (1,2,3)

更新多条记录的多个值

Update score  
      SET change_type = CASE id  
        WHEN 1 THEN 'value1'  
        WHEN 2 THEN 'value2'  
        WHEN 3 THEN 'value3'  
      END , 
        score = CASE id
         WHEN 1 THEN 1
        WHEN 2 THEN 2
        WHEN 3 THEN 3
      END
    WHERE id IN (1,2,3)

(1). 尽量不要修改主键字段。  
(2). 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。  
(3). 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。  
(4). 避免UPDATE将要复制到其他数据库的列。  
(5). 避免UPDATE建有很多索引的列。  
(6). 避免UPDATE在WHERE子句条件中的列。

# 库与表基本操作 #

## 库的基本操作 ##

（1）查看所有数据库以及使用数据库：

show databases;
    use table;

（2）查看当前数据库

select database();

（3）显示当前时间、用户名、数据库版本

select now(), user(), version();

（4）创建库

create database[ if not exists] 数据库名 数据库选项
        数据库选项：
            CHARACTER SET charset_name
            COLLATE collation_name

（5）查看当前库信息

show create database 数据库名

（6）修改库的选项信息

alter database 库名 选项信息

（7）删除库

drop database[ if exists] 数据库名
            同时删除该数据库相关的目录及其目录内容

## 表的基本操作： ##

（1）创建表：

create  table[ if not exists]  表名 ( 表的结构定义 )[ 表选项]
            每个字段必须有数据类型
            最后一个字段后不能有逗号
            对于字段的定义：
                字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']

（2）表的选项：

1.  删除列

ALTER TABLE 【表名字】 DROP 【列名称】

1.  增加列

ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL COMMENT ‘注释说明’

1.  修改列的类型信息

ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称（这里可以用和原来列同名即可）】 BIGINT NOT NULL COMMENT ‘注释说明’

1.  重命名列

ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL COMMENT ‘注释说明’

1.  重命名表

ALTER TABLE 【表名字】 RENAME 【表新名字】

1.  删除表中主键

Alter TABLE 【表名字】 drop primary key

1.  添加主键

ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)

1.  添加索引

ALTER TABLE sj_resource_charges add index INDEX_NAME (name);

1.  添加唯一限制条件索引

ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);

1.  删除索引

Alter table tablename drop index emp_name;

（3）查看所有表

SHOW TABLES；

（4）查看表结构：

SHOW CREATE TABLE 表名    （信息更详细）
        DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
        SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

（5）修改表：对表进行重命名

RENAME TABLE 原表名 TO 新表名
    RENAME TABLE 原表名 TO 库名.表名    （可将表移动到另一个数据库）

（6）删除表

DROP TABLE[ IF EXISTS] 表名

（7）清空表数据

TRUNCATE [TABLE] 表名

（8）复制表结构

CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名