表修改、表的引擎、约束条件

表修改、表的引擎

一、数据库修改字符编码

• 修改字符编码

  alter database 数据库名 charset=编码格式

mysql> show create database db1;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database db1 charset='utf8';
Query OK, 1 row affected (0.00 sec)

mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

二、用户操作（重要）

1. 为特定的数据库分配该数据库的操作权限的用户

   grant 权限 on 数据库.表 to 用户名@"主机" identified by "密码";
   

   • all: 所有权限，insert, delete, update, select,drop等
   • 数据库.表： 相应数据库下的所有表（*.*）
   • 用户名@“localhost”: 本机通过用户名（自己创建）：用户登录操作数据库
   • identified by ‘密码’： 为登录的用户名设置密码

   eg: mysql> grant all on db1.* to randy@"localhost" identified by '123';
      
   mysql -urandy -p123
   mysql> show dates;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | db1                |
   +--------------------+
   

2. 为特定用户指定特定的权限

grant select, delete, update, insert, alter, drop on randy.* to randysun@'localhost' identified by '123456789';

• select, delete, update, insert, alter,drop：用户指定的权限
• randy.*: randy数据库下面的所有表
• randysun@‘localhost’: 任何机器可以通过randysun用户登录
• identified by ‘123456789’: randysun用户密码为：123456789

3. 撤销权限

# 禁用本地用户对数据库中表的权限操作
revoke 权限1，权限2, 权限.... on 数据库名.表 from 用户名@"主机名"；

# 撤销用户对数据库中所有表的drop权限
eg: revoke drop on randy.* from randysun@"localhost"；

3. 删除用户

   drop user 用户名@"主机名"；
   
   eg: drop user randysun@"localhost"
   

三、表的修改

3.1 修改表名

​ 格式： alter table 旧表 rename 新表;

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

mysql> alter table test rename new_t;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| new_t         |
+---------------+
1 row in set (0.00 sec)

3.2 修改字段名（修改字段名和类型）

​ 格式： alter table 表名 change 旧字段 新字段 类型(长度);

mysql> desc new_t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age   | varchar(34) | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(14) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table new_t change age new_age int;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc new_t;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| new_age | int(11)     | YES  |     | NULL    |       |
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(14) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

3.3 修改字段属性(修改字段类型)

​ 格式： alter table 表名 modify 字段 新类型(长度);

mysql> desc new_t;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| new_age | int(11)     | YES  |     | NULL    |       |
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(14) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table new_t modify name char(16);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc new_t;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| new_age | int(11)  | YES  |     | NULL    |       |
| id      | int(11)  | YES  |     | NULL    |       |
| name    | char(16) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.3 创建表的完整语法

# 长度和约束在某些情况下是可以省略的
mysql>:create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)engine=innodb charset=utf8;

四、数据库表的搜索引擎--数据库优化方式

4.1 各种搜索引擎介绍

InnoDB：MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。

MyISAM：MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。

Memory：在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失

BLACKHOLE：黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于 DML 语句需要发送到从服务器，但主服务器并不会保留这种数据的备份的主从配置中。

五、存储引擎在mysql中的使用

引擎是建表是规定, 提供给表使用的, 不是数据库

innodb(默认): 支持事务, 行级锁, 外键

myisam: 查询效率要优于innodb, 当不需要支持事务, 行级锁, 外键, 可以通过设置myisam来优化数据库

5.1 查看存储引擎相关sql语句

# 查询当前数据库支持的存储引擎
mysql> show engines G;

# 查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";

5.2 在创建表时指定

# innodb(默认): 支持事务, 行级锁, 外键
mysql> create table t1(id int)engine=innodb; 

# myisam: 查询效率要优于innodb, 当不需要支持事务, 行级锁, 外键, 可以通过设置myisam来优化数据库
mysql> create table t2(id int)engine=myisam; 

# blackhole：黑洞，存进去的数据都会消失（可以理解不存数据）
mysql> create table t3(id int)engine=blackhole;

# memory：表结构是存储在硬盘上的，但是表数据全部存储在内存中
mysql> create table t4(id int)engine=memory; # 保存在内存中，重启则消失

六、数据库的模式

数据库安全模式主要是用于在创建表时候，里面的字段限制时候，插入大于规定的字符串长度，安全模式情况下就会报错。

# mysql 5.7 以后默认都是安全模式

# mysql 5.6 版本
sql_model=no_engine_substitution  # 非安全性，默认
sql_model=strict_trans_tables  # 安全性

# 查看当前数据库模式:
show variables like "%sql_mode%"; # %匹配0~n个任意字符 => 模糊查询

# 临时设置为安全模式，服务重启后会被重置
mysql>: set global sql_mode="strict_trans_tables";  # 在root用户登录状态下
# 在设置后，quit断开数据库连接后(服务器不重启)就会进入安全模式

# 安全模式下，非安全模式下sql执行的警告语句，都会抛异常
eg>: create table t1(name char(2));
eg>: insert into t1 values ("ab") # 正常
eg>:  insert into t1 values("abc"); # 错误 ERROR 1406 (22001): Data too long for column 'name' at row 1

七、约束

"""
primary key：主键，唯一标识，表都会拥有，不设置为默认找第一个 不空，唯一 字段，未标识则创建隐藏字段
foreign key：外键
unique：唯一性数据, 该条字段的值需要保证唯一,不能重复

auto_increment：自增，只能加给key的int类型字段，作为辅助修饰，一个表中只能设置一个自增字段

not null：不为空 - 针对一些字段，如注册时的用户名，出生人的性别等，这些需求下的字段，只不能设置为Null，必须要对其赋值
default：默认值 - 对有默认值意外的字段进行赋值时，有默认值的字段会被赋默认值

unsigned：无符号 - 存储的数字从0开始
zerofill：0填充 - 存整数时数据长度小于取值范围长度，会在数字左方用0填充
"""

# not null 与 default 限制
# 不能为空，没有默认值的x，必须赋值
# y、z在没有赋值情况下，才有默认值，设置值后，采用默认值
mysql>: create table td1 (x int not null, y int default 0, z int default 100);



# 报错，auto_increment必须设置给 键字段
mysql>: create table td2 (x int auto_increment);
# 报错，auto_increment必须设置给 int字段
mysql>: create table td2 (x char(4) auto_increment);
# 报错，auto_increment字段最多出现 1次
mysql>: create table td2 (x int unique auto_increment, y int unique auto_increment);

# 正确，主键和唯一键分析
# x为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
mysql>: create table td21 (x int unique auto_increment, y int unique);
# y为主键：没有设置primary key时，第一个 唯一自增键，会自动提升为主键
mysql>: create table td22 (x int unique, y int unique auto_increment);
# x为主键：设置了主键就是设置的，主键没设置自增，那自增是可以设置在唯一键上的
mysql>: create table td23 (x int primary key, y int unique auto_increment);
# x为主键：设置了主键就是设置的，主键设置了自增，自增字段只能有一个，所以唯一键不能再设置自增了
mysql>: create table td24 (x int primary key auto_increment, y int unique);
# 默认主键：没有设置主键，也没有 唯一自增键，那系统会默认添加一个 隐式主键（不可见）
mysql>: create table td25 (x int unique, y int unique);

# 唯一键：确保一个字段，数据不能重复
# 主键：是一条记录的唯一标识（可以理解为数据的编号）



# 联合唯一
# ip在port不同时，可以相同，ip不同时port也可以相同，均合法
# ip和port都相同时，就是重复数据，不合法
mysql>: create table tu1 (ip char(16), port int, unique(ip, port));

# 也可以设置成 联合主键，道理同 联合唯一
mysql>: create table tu2 (ip char(16), port int, primary key(ip, port));
# sql可以多行书写
mysql>: 
create table t22(
	ip char(16),
    port int,
    primary key(ip,port)
);


# 通常自增字段的 自增索引 会被永久记录，想清空表并清空自增索引：
mysql>: truncate 数据库名.表名