数据定义语言DDL

1、 操作数据库

• 创建数据库
  create database if not exists hello；
• 删除数据库
  drop database if exists hello
• 使用数据库
  use hello
  如果表明或者字段名是一个特殊字符，就需要带
• 查看数据库
  show databases --查看所有的数据库
  

• 查看数据库定义语句
  show create database school
• 查看表格定义语句
  show create table teacher
• 查看表结构
  DESC teacher

2. 常用表的列类型

• 数值型——可以不加引号

    TINYINT:   微整型，占一个字节，  范围  -128~127       
    SMALLINT:   小整型，占2个字节，范围  -32768~32767      
    INT:   整型，占4个字节        
    BIGINT:   大整型，占8个字节
    FLOAT:   单精度浮点型，占4个字节，最大3.4e38，可能产生误差     
    DOUBLE:   双精度浮点型，占8个字节，可能产生误差       
    DECIMAL(M,D):  定点小数，小数点不会变化，几乎也不会产生误差，M代表总的有效数，D代表小数点后的有效位数（工资、价格.....）    
    BOOL/BOOLEAN: 通常用于存储两个值得数据，  TRUE/FALSE。真正存储时 true转为1，FALSE转为0。因为Mysql中没有真正的布尔型，最终会自动转为微整型TINYINT。
  

• 日期时间型——必须加引号

  DATE:   日期型  2020-4-12     
  TIME:   时间型  16:48:25  
  DATETIME:   日期时间型    2020-4-2 16:48:25   
  TIMESTAMP：时间戳，1970.1.1到现在的毫秒数
  YEAR：    年份
  

• 字符串型——必须加引号

  VARCHAR(M): 变长字符串，不会产生空间浪费，操作速度相对慢，M的最大值为65535
  CHAR(M): 定长字符，可能会产生空间浪费，操作速度相对快，往往存储一些固定长度的数据（如手机号码、身份证号等），M的最大值为255
  TINYTEXT：微型文本，大小为2-1
  TEXT(M): 大型变长字符串，M的最大值是2G.
  

MySQL的数据类型和字段属性

3. 数据库的字段属性

1.auto_increment
auto_increment能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插入的行赋值为上一次插入的ID+1。
MySQL要求将auto_increment属性用于作为主键的列。此外，每个表只允许有一个auto_increment列。例如：
id smallint not null auto_increment primary key

2.binary
binary属性只用于char和varchar值。当为列指定了该属性时，将以区分大小写的方式排序。与之相反，忽略binary属性时，将使用不区分大小写的方式排序。例如：
hostname char(25) binary not null

3.default
default属性确保在没有任何值可用的情况下，赋予某个常量值，这个值必须是常量，因为MySQL不允许插入函数或表达式值。此外，此属性无法用于BLOB或TEXT列。如果已经为此列指定了NULL属性，没有指定默认值时默认值将为NULL，否则默认值将依赖于字段的数据类型。例如：
subscribed enum('0', '1') not null default '0'

4.index
如果所有其他因素都相同，要加速数据库查询，使用索引通常是最重要的一个步骤。索引一个列会为该列创建一个有序的键数组，每个键指向其相应的表行。以后针对输入条件可以搜索这个有序的键数组，与搜索整个未索引的表相比，这将在性能方面得到极大的提升。

create table employees
(
id varchar(9) not null,
firstname varchar(15) not null,
lastname varchar(25) not null,
email varchar(45) not null,
phone varchar(10) not null,
index lastname(lastname),
primary key(id)
);

我们也可以利用MySQL的create index命令在创建表之后增加索引：
create index lastname on employees (lastname(7));
这一次只索引了名字的前7个字符，因为可能不需要其它字母来区分不同的名字。因为使用较小的索引时性能更好，所以应当在实践中尽量使用小的索引。

5.not null
如果将一个列定义为not null，将不允许向该列插入null值。建议在重要情况下始终使用not null属性，因为它提供了一个基本验证，确保已经向查询传递了所有必要的值。

6.null
为列指定null属性时，该列可以保持为空，而不论行中其它列是否已经被填充。记住，null精确的说法是“无”，而不是空字符串或0。

7.primary key
primary key属性用于确保指定行的唯一性。指定为主键的列中，值不能重复，也不能为空。为指定为主键的列赋予auto_increment属性是很常见的，因为此列不必与行数据有任何关系，而只是作为一个唯一标识符。主键又分为以下两种：
(1)单字段主键
如果输入到数据库中的每行都已经有不可修改的唯一标识符，一般会使用单字段主键。注意，此主键一旦设置就不能再修改。
(2)多字段主键
如果记录中任何一个字段都不可能保证唯一性，就可以使用多字段主键。这时，多个字段联合起来确保唯一性。如果出现这种情况，指定一个auto_increment整数作为主键是更好的办法。

8.unique
被赋予unique属性的列将确保所有值都有不同的值，只是null值可以重复。一般会指定一个列为unique，以确保该列的所有值都不同。例如：
email varchar(45) unique

9.zerofill
zerofill属性可用于任何数值类型，用0填充所有剩余字段空间。例如，无符号int的默认宽度是10；因此，当“零填充”的int值为4时，将表示它为0000000004。例如：
orderid int unsigned zerofill not null

10.unsigned
无符号的整数，声明了该列不能声明为负数

每一个表都必须存在以下五个字段
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间

4. 创建数据库表

通用语法：

CREATE TABLE [IF NOT EXISTS] `表名`（
	`字段名` 列类型 [属性] [索引] [注释],
  `字段名` 列类型 [属性] [索引] [注释],
  ......
  `字段名` 列类型 [属性] [索引] [注释]
）[表类型][字符集设置][注释];

实例：

CREATE TABLE
IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL DEFAULT 'HELLO' COMMENT 'HELLO',
	`password` VARCHAR ( 20 ) NOT NULL,
	`sex` VARCHAR ( 2 ) NOT NULL ,
	`birthday` DATETIME DEFAULT NULL,
	`address` VARCHAR ( 50 ) DEFAULT NULL,
	`email` VARCHAR ( 30 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ) 
	) ENGINE = INNODB DEFAULT CHARSET = UTF8;

5、数据表的类型

5.1 数据库引擎

• INNODB MySQL默认引擎
• MYISAM 较早时间使用的引擎

常规使用操作：

• MYISAM 节约空间、速度较快、支持全文索引
• INNODB 安全性高、事务处理速度快、多表多用户操作

MySQL引擎在物理文件上的区别

• INNODB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• MYISAM对应文件
  • *.frm -表结构的定义文件
  • *.MYD -数据文件（data)
  • *.MYI -数据索引（index）

设置数据库表的字符集编码

charset=utf8

不设置的情况下，会是MySQL默认的字符集编码，MySQL的默认编码是Latin1，不支持中文

MySQL的默认字符编码的设置

6、修改删除表

修改

-- 所有的创建和删除操作尽量加上判断，避免报错
-- 修改表名
ALTER TABLE 旧表名 RENAME AS 新表名
-- 增加表的字段
ALTER TABLE 表名 ADD 字段名 列属性
-- 修改表的字段（重命名、修改约束）
-- change用来字段重命名，不能修改字段类型和约束
-- ALTER TABLE 表名 MODIFY 字段名 列属性
-- modify不能用来字段重命名，只能修改字段类型和约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性
ALTER TABLE teacher MODIFY age VARCHAR(11)  -- 修改约束
ALTER TABLE teacher CHANGE age age1 int(1)  -- 重命名
-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher DROP age1

删除

-- 删除表，如果表存在再删除
DROP TABLE IF EXISTS teacher