数据库详细入门教程

创建用户：CREATE USER '用户名'【@'主机名'】 IDENTIFIED BY '密码'

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

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

GRANT TO给用户分配权限；GRANT 权限名 ON 数据库名 表名 TO 用户@主机

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

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

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

REVOKE FROM 删除用户权限

REVOKE ALL ON *.* FROM 'jredu'@'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 DATABASE;

SHOW DATABASE;

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

USE mydb;

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

SHOW TABLES FROM mysql;

mySQL中常用的数据类型

一、字符型：

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

②VARCHAR(): 存储可变长度的字符串，最常用的。0~65535 * 10；

③ TEXT: 可以存储可变长度的字符串，常用于发布文章等 0~65535*10^2；

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

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

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

二、整形：

① 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：可以精确到小数点后7位有效数字

② DOUBLE：可以精确到小数点后15到16位有效数字

四、日期时间数据类型

由于时间存储使用字符串或者时间戳存储，所以数据库中几乎不用数据类型

① DATE 存储日期和时间数据

② TIMESTAMP: 比 DATE 更精确

数据库设计的三大范式；

1、第一范式

1NF：数据表中的每一列必须是不可拆分的最小单元，也就是确保每一列的原子性

例如 userInfo：'山东省烟台市 13181602111'；

userAds：'山东省烟台市' userTel:'13181602111'

2、第二范式

2NF：满足1NF后，要求表中的所有列都必须依赖于主键，而不能有任何一列与主键没有关系。也就 是说一个表只描述一件事情

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

产品表：只能描述产品相关的信息，所以所有的字段必须与产品id相关

因此， 不能同时存在产品和订单的信息

3、第三范式

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

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

[第二范式与第三范式的本质区别]

在于有没有分出两张表，第二范式是说一张表中如果包含了多种不同实体的属性，那么必须分成多张表

第三范式要求已经分好了多张表的话，那么，一张表中只能有另一张表的ID(主键)而不能有其他的任何信息，（其他的任何信息，一律用主键在另一张表中查询）

创建表

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

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

常用的列定义关键字

UNSIGNED 设置列为无符号列，只能设置类型为数字类型的列

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

[主键]

1、主键的注意事项

主键默认非空，主键默认唯一性约束，只有主键才能设置自动增长（主键不一定自增，自增一定是主键）

2、设置主键的方式

① 在列定义时设置， id INT UNSIGNED PRIMARY KEY,

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

PRIMARY KEY(id) : 设置主键约束

NOT NULL：设置列为非空 该字段不能为空

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

DEFAULT 1.2 设置默认值约束， 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 参照表(参照字段)]

[ON DELETE SET NULL ON UPDATE CASCADE] --> 设置参照完整性

3、外键约束的参照完整性操作

参照操作 当对参照表的参照字段进行删除或更新是，外键表中的外键应如何应对

参照操作可选值 RESTRICT 拒绝参照表删除或更新参照字段（默认）

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

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

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

显示表结构

SHOW COLUMNS FROM table1;

显示表的建表语句

SHOW CREATE TABLE table1;

删除表

DROP TABLE IF EXISTS table1;

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

ALTER TABLE table1 RENAME TO table2;

同时修改多表 RENAME TABLE table2 TO table1【,`user` TO user1…………】;

RENAME TABLE table2 TO table1,`user` TO user1;

修改字段 列

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

FIRST 将这个字段调整为表格度一列， AFTER 某一列 将这个字段放在某一列后面

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

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

ALTER TABLE table1 MODIFY `name` `username` VARCHAR(200) NOT NULL AFTER age;

删除表中某一列

ALTER TABLE table1 DROP height;

新增一列 必选部分 ALTER TABLE table1 ADD height DOUBLE(8,2)

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

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

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

增加主键约束

ALTER TABLE table1 ADD PRIMARY KEY(id);

删除主键约束

ALTER TABLE table1 DROP PRIMARY KEY;

新增唯一性约束

ALTER TABLE table1 UNIQUE KEY(username);

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

ALTER TABLE table1 DROP INDEX username;

设置默认值约束

ALTER TABLE table1 ALTER age SET DEFAULT 20;

删除默认值约束

ALTER TABLE table1 ALTER age DROP DEFAULT;

设置外键约束 必选部分 ALTER TABLE table1 ADD CONSTRAINT waijianming FOREIGN KEY (clsid) REFERENCES classes(id)

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

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

ALTER TABLE table1 DROP FOREIGN KEY table1_fk_classes;

ALTER TABLE table1 DROP INDEX table1_fk_classes;

ALTER TABLE table1 DROP INDEX table1_fk_classes;