数据库基础以及表的创建

-- 创建用户：

CREATE USER ‘用户名’ [@'主机名'] IDENTIFIED BY '密码'

-- 主机名可以为空，为空默认为%权限，表示所有主机可连接。

CREATE USER 'xxxx' @ 'localhost' IDENTIFIED BY xxxx' ;

-- 给用户分配权限：GRANT 权限名  ON  数据库名. 表明  TO 用户@主机

-- ALL 表示所有权限  *.*表示所有数据中的所有表

GRANT ALL ON *.* TO xxxx'@'localhost';

GRANT SELECT,INSERT,DELETE,UPDATE ON mysql.`user` TO 'xxxx'@'localhost';

-- REVOKE FROM 删除用户权限。

REVOKE SELECT ON *.* FROM xxxx'@'localhost';

-- 创建数据库：

CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET [=] 'UTF8']

-- 如果省略 IF NOT EXISTS 在重复创建数据库时，会报错！

CREATE DATABASE IF NOT EXISTS myDB3 CHARACTER SET ='UTF8';

-- 删除数据库：

DROP DATABASE [IF EXISTS] 数据库名 DROP DATABASE  IF EXISTS mydb2;

-- 查询本机中所有的数据库

SHOW DATABASES;

-- 使用mydb这个数据库，表示下面的查询，都将默认针对mydb数据库

USE mydb;

-- 查询数据库中所有数据表 SHOW TABLES [FROM 数据库]

SHOW TABLES;

 MySQL 中常见的数据类型：

一、字符型：

①CHAR(N):固定N个字符长度的字符串，如果长度不够会自动空格补齐。 N的返回0~255；

②VARCHAR()：存储可变长度的字符串，最常用的。0~2^16-1 *10；

③TEXT:存储可变长度的字符串。(常用于发布文章等大段内容)  0~2^16-1*10^2;

④TINYTEXT: 0~2^8-1*10;

⑤MEDIUMTEXT：0~2^24-1*10^3;

⑥LONGTEXT：0~2^32-1*10^4;

⑦enum（"男"，"女"）：枚举类型，字段只能容纳枚举出的数据

二、整形：

①TINYINT：  无符号 0~2^8-1  有符号 -2^7~2^7-1

②SMALLINT： 无符号 0~2^16-1  有符号 -2^15~2^15-1

③MEDIUMINT：无符号 0~2^24-1  有符号 -2^23~2^23-1

④INT: 无符号 0~2^32-1  有符号 -2^31~2^31-1

⑤BIGINT：无符号 0~2^64-1  有符号 -2^63~2^63-1

三、浮点型：

①FLOAT:可以精确到小数点后七位有效数字；

②DOUBLE：可以精确到小数点后十五到十六位有效数字

四、日期时间数据类型 注意：由于时间存储使用字符串或者时间戳存储，所以数据库中几乎不用日期类型。

①DATE:存储日期和时间数据

②TIMESTAMP:比DATE更精确

[数据库设计的三大范式]

1、第一范式(1NF)：

数据表中的每一列(字段)，必须是不可拆分的最小单元。也就是确保每一列的原子性。

例如：userInfo:  'xx省xx市 1234567'      userAds:  'xx省xx市'     userTel: '1234567'

2、第二范式(2NF):

满足1NF后，要求：表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系。

例如：订单表，只能描述订单相关的信息，所以所有的字段都必须与订单ID相关；    产品表，只能描述产品相关的信息，所以所有的字段都必须与产品ID相关；

因此，不能在一张表中同时出现订单信息与产品信息。

3、第三范式(3NF)：

满足2NF后，要求：表中的每一列都要与主键直接相关，而不是间接相关。(表中的每一列，只能依赖于主键)

例如：订单表中，需要有客户相关信息，在分离出客户表之后。订单表中，只需要有一个用户ID即可。而不能有其他的客户信息。 因为其他的用户信息是直接关联于用户ID，而不是关联于订单ID。

【第二范式与第三范式的本质区别】

在于有没有分出两张表，第二范式是说一张表中包含了多种不同实体的属性，那么必须要分成多张表。第三范式是要求， 已经分好了多张表的话，那么，一张表中只能有另一张表中的ID（主键），而不能有其他的任何信息（其他的任何信息， 一律用主键在另一表查询）。

USE mydb;

-- 创建表：

-- IF NOT EXISTS 可以省略，省略后重复创建报错，如果不省略，则创建时会检测表是否已存在，如果表存在则不再执行创建语句

-- 定义列： 列名 数据类型  列定义关键字

-- 常用的列定义关键字：

--  UNSIGNED:设置列为无符号列。只能设置类型为数字类型的列

-- PRIMARY KEY:设置主键约束。

-- AUTO_INCREMENT:设置列为自动增长列，自动增长列必须是主键

-- -- 【主键】

-- 1、主键的注意事项？ 主键默认非空！  主键默认唯一性约束！

--    只有主键才能设置自动增长（主键不一定自增，自增必须是主键）

-- 2、设置主键的方式？

①在列定义时设置：  id INT PRIMARY KEY

--  ②在列定义完成后设置： PRIMARY KEY (id)

-- UNIQUE:设置唯一性约束。该字段不能出现重复值。

-- NOT NULL:设置非空约束。该字段不能为空。

-- DEFAULT:设置默认值约束。 height DOUBLE(3,2) DEFAULT 1.2  height如果不输入，默认1.2

-- FOREIGN KEY:设置外键约束。

-- 【外键】

-- 1、设置外键有哪些注意事项？

-- ①只有INNODB的数据库引擎支持外键。

-- 修改my.ini文件设置default-storage-engine=INNODB

-- ②外键与参照列的数据类型必须相同。（数值型要求长度和无符号都相同，字符串要求类型相同，长度可以不同。）

-- ③设置外键的字段必须要有索引。如果没有索引，设置外键时会自动生成一个索引。

-- 2、设置外键的语法？

-- [CONSTRAINT 外键名]  FOREIGN KEY(外键字段) REFERENCES classes  参照表(参照字段)

--  [ON DELETE SET NULL ON UPDATE CASCADE]

--设置参照完整性

-- 3、外键约束的参照操作？

--  参照完整性操作：当对参照表的参照字段进行删除或更新时，外键表中的外键如何应对。

-- 参照完整性可选值： RESTRICT 拒绝参照表删除或更新参照字段。

--                  NO ACTION 与RESTRICT 相同，但这个指令只在MySQL生效

--                  CASCADE 删除或更新参照表的参照字段时，外键表的记录同步删除更新

--                  SET NULL 删除或更新参照表的参照字段时，外键表的外键设为NULL

 

SHOW TABLES;

-- 显示表结构

SHOW COLUMNS FROM TB1;

-- 显示表的建表语句

SHOW CREATE TABLE TB1;

-- 删除表

DROP TABLE  IF  EXISTS tb1;

-- 修改表名

ALTER TABLE 旧表名 RENAME  [TO] 新表名;

ALTER TABLE tb1 RENAME tb2;

-- 同时修改多表名 

RENAME TABLE tb2 TO tb1[,`user` TO user1……];

RENAME TABLE tb2 TO tb1,`user` TO user1;

-- 修改字段 列

-- ALTER TABLE 表名 CHANGE 旧列名 新列名 列定义 [FIRST |  AFTER 某一列]

-- FIRST： 将这个字段调整为表格第一列  

AFTER 某一列： 将这个字段放到某一列后面

ALTER TABLE tb1 CHANGE `name` `username` VARCHAR(200) NOT NULL AFTER age

-- MODIFY 只修改列定义，不能改名

ALTER TABLE tb1 MODIFY `name`  VARCHAR(200) NOT NULL  AFTER age

-- 删除表中某一列

ALTER TABLE tb1 DROP height;

-- 新增一列  必选部分 

ALTER TABLE tb1 ADD height DOUBLE(8,2) ALTER TABLE tb1 ADD height DOUBLE(8,2) DEFAULT 1.2 AFTER age;

-- 新增多列 不能调整列的位置，只能插在最后。

ALTER TABLE tb1 ADD( weight DOUBLE(3,2) UNSIGNED, school VARCHAR(255) );

-- 增加主键约束

ALTER TABLE tb1 ADD PRIMARY KEY (id)

-- 删除主键约束

ALTER TABLE tb1 DROP PRIMARY KEY;

-- 新增唯一性约束

ALTER TABLE tb1 ADD UNIQUE KEY(username);

-- 删除唯一性约束：由于创建唯一性约束会默认创建索引，所以删除时，需删除索引

ALTER TABLE tb1 DROP INDEX username;

-- 设置默认值约束

ALTER TABLE tb1 ALTER age SET DEFAULT 20;

-- 删除默认值约束

ALTER TABLE tb1 ALTER age DROP DEFAULT ;

-- 设置外键约束

ALTER TABLE tb1 ADD FOREIGN KEY (clsid) REFERENCES classes(id)

ALTER TABLE tb1 ADD CONSTRAINT waijianming FOREIGN KEY (clsid) REFERENCES classes(id) ON DELETE SET NULL ON UPDATE CASCADE;

-- 删除外键约束。由于创建外键时会默认创建索引，所以删除外键后，需要在删除索引

ALTER TABLE tb1 DROP FOREIGN KEY id;