MySql初步学习
创建用户: CREATE USER '用户名'[@'主机名'] IDENTIFIED BY '密码';
主机名可以为空,为空默认为%权限,表示所有主机可连接。
create USER 'jredu'@'loaclhost' IDENTIFIED by 'jredu';
给用户分配权限: GRANT 权限名 on 数据库名.表名 to 用户@主机
ALL表示所有权限,*.* 表示所有数据库中的表
GRANT ALL ON *.* to 'jredu'@'loaclhost';
REVOKE FROM 删除用户权限
REVOKE ALL ON *.* from 'jredu'@'loaclhost';
GRANT SELECT,INSERT,UPDATE,DELETE ON pingjiao.`admin` to 'jredu'@'loacalhost';
创建数据库:create DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET [=] 'utf8'];
如果省略if NOT EXISTS 再重复创建数据库时,会报错!
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET 'utf8';
删除数据库: DROP DATABASE [IF EXISTS] 数据库名;
DROP DATABASE IF EXISTS mydb;
查询本机中所有数据库
SHOW DATABASES;
使用mydb这个数据库,表示下面的查询都将针对mydb数据库。
USE mydb;
查询数据库中所有数据表
SHOW TABLES;
【数据类型】
一、字符型:
① 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^32 ~ 2^32-1
⑤ BIGINT:无符号 0~2^64-1 有符号 -2^63 ~ 2^63-1
三、浮点型:
① FLOAT:精确到小数点后7位
② DOUBLE:精确到小数点后15~16位有效数字。
四、日期时间数据类型
注意:由于时间存储使用字符串或时间戳存储,所以数据库中几乎不同日期类型。
① DATE:存储日期和时间数据
② TIMESTAMP:比DATE更精确。
【数据库设计的三大范式】
1.第一范式(1NF):数据表中的每一列(字段),必须是不可拆分的最小单元。也就是说
要确保每一列的原子性。
2.第二范式(2NF):满足1NF后,要求表中的所有列都必须依赖于主键,而不能有任何一列与主键
没有关系。也就是说,一个表只描述一件事情。
例如:订单表,只能描述订单相关的信息,所以所有的字段必须与订单ID相关;
产品表,只能描述产品相关的信息,所以所有的字段必须与产品ID相关;
因此:不能在同一张表中,同时出现订单信息和产品信息。
3.第三范式(3NF):满足2NF后,要求:表中的每一列都要与主键直接相关,而不是间接相关。
(表中的每一列,都只能依赖于主键)
例如:订单表中,需要有客户相关信息,在分离出客户表之后。
订单表中,只需要有一个用户ID即可。而不能有其他的客户信息。因为,其他的
用户信息是直接关联与用户ID,而不是关联与订单ID。
【第二范式与第三范式的本质区别】
在于有没有分出两张表,第二范式是说一张表中包含了多种不同的实体的属性,
那么必须要分成多张表。第三范式要求,已经分好了多张表的话,那么,一张表中只能有另一张
表中的ID(主键),而不能有其他的任何信息(其他任何信息,一律用主键在另一张表中查询)。
USE mydb;
-- 创建表:
-- IF NOT EXISTS 可以省略,省略后重复创建报错。
-- 定义列:列名 数据类型 列定义关键字
-- 常用的列定义关键字:
-- UNSIGNED:设置列为无符号列。 无符号只能限制类型为数字类型的列。
-- AUTO_INCREMENT:设置列为自动增长列。
-- 自动增长必须是主键!!主键不一定是自动增长,但一定是UNIQUE
/*【主键】
1.主键注意事项:主键默认非空!主键默认唯一性约束!
只有主键才能设置自动增长(主键不一定自增,自增必须是主键)
2.设置主键的方式?
① 在列定义的时候:id int PRIMARY KEY
② 在列定义完成后设置: PRIMARY KEY(id)
PRIMARY KEY:设置主键约束。
NOT_NULL:设置非空约束。
UNIQUE:设置唯一性约束,该字段不能出现重复值。UNIQUE的可以是任意字段。
DEFAULT:默认值约束。heighe DOUBLE(3,2) DEFAULT 1.2 heigh如果不输入默认为1.2
FOREIGN KEY: 设置外键约束
【外键】
1.设置外键的注意事项:
① 只有INNODB的数据库引擎支持外键
修改my.ini文件设置default-storge-engin=INNODB
② 外加与参照列的数据类型必须相同。
(数值型要求长度和无符号都相同,字符串要求类型相同,长度可以不同。)
③ 设置外键的字段必须要有索引。如果没有索引,设置外键时会自动生成索引。
2.设置外键的语法:
[CONSTRAINT 外键名] FOREIGN KEY (外键字段) REFERENCES 参照表(参照字段)
3.外键约束的参照操作:
参照操作:当对参照表的参照字段进行删除或更新时,外键表中的外键如何应对。
参照操作可选值: RESTRICT 拒绝参照表删除或更新参照字段。(默认)
no action 与 RESTRICT 相同,但这个指令只在MySql生效
CASCADE 删除或更新参照表的参照字段时,外键表的记录同步删除更新
SET NULL 删除或更新参照表的参照字段时,外键表的外键设为NULL
*/
DROP TABLE IF EXISTS tbl;
-- IF NOT EXISTS 创建表时会确认表是否存在,存在就不执行了
CREATE TABLE IF NOT EXISTS tbl(
id INT(1) UNSIGNED PRIMARY KEY auto_increment,
`name` VARCHAR(255) not NULL,-- name 是系统关键字,所以使用反引号包裹
age TINYINT(3) ,
height DOUBLE(3,2) DEFAULT 1.2,
clsId INT UNSIGNED,
CONSTRAINT tbl_fk_classes FOREIGN KEY (clsid) REFERENCES
)auto_increment=8 default-ENGINE;
DROP TABLE IF EXISTS `cls`;
CREATE TABLE IF NOT EXISTS cls(
id INT UNSIGNED PRIMARY KEY auto_increment,
classname VARCHAR(255) not NULL
);
-- SHOW COLUMNS FROM cls;
DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user`(
id INT UNSIGNED PRIMARY KEY auto_increment,
clsId INT UNSIGNED ,
`name` varchar(255) not null,
CONSTRAINT aaa FOREIGN KEY (clsId) REFERENCES cls(id) ON DELETE SET NULL ON UPDATE CASCADE
);
SHOW TABLES;
-- 显示表结构 COLUMNS
SHOW COLUMNS from tbl;
-- 显示表的建表语句
SHOW CREATE TABLE tbl;
-- 删除表(如果存在则删除)
DROP TABLE IF EXISTS tbl;
-- 修改表名
-- 1.alter table 旧表名 RENAME [to] 新表名;
ALTER TABLE tbl RENAME tbl2;
-- 2.同时修改多表:rename TABLE 旧表名 to 新表名[, 另一个旧表名 TO 另一个新表名];
RENAME TABLE tbl2 TO tbl1;
-- 修改字段 列
-- ALTER TABLE 表名 CHANGE 旧列名 新列名 列定义 [FIRST | AFTER 某一列列名]
-- FIRST将此字段调整为表格第一列,after 将一列放到某一列后面。
ALTER TABLE tbl CHANGE `name` `username` VARCHAR(200) not NULL after age
-- MODIFY只修改列定义,不能修改列名
ALTER TABLE tbl MODIFY `name` VARCHAR(200) not NULL AFTER age
-- 删除表中某一列
ALTER TABLE tbl DROP height
-- 为表新增某一列
ALTER TABLE tbl ADD height DOUBLE(3,2) DEFAULT 1.2 AFTER age
-- 为表新增多列,不能调整列的位置,只能插在最后。
ALTER TABLE tbl ADD(
weight DOUBLE(3,2) UNSIGNED,
school VARCHAR(255)
);
-- 增加主键约束
ALTER TABLE tbl ADD PRIMARY KEY(age)
-- 删除主键约束
ALTER TABLE tbl DROP PRIMARY KEY
-- 新增唯一性约束
ALTER TABLE tbl ADD UNIQUE KEY(username)
-- 删除唯一性约束:由于创建唯一性约束会默认创建索引,所以删除时,需删除索引。
ALTER TABLE tbl DROP INDEX username
-- 设置默认值约束
ALTER TABLE tbl ALTER age SET DEFAULT 20;
-- 删除默认值约束
ALTER TABLE tbl ALTER age DROP DEFAULT;
-- 设置外键约束(按照外键名删除外键)
ALTER TABLE tbl add constraint waijianming foreign key (clsId)
REFERENCES cls(id) on delete set null update cascade;
-- 删除外键约束。由于创建外键时会默认创建索引,所以删除外键后,需要再删除索引
ALTER TABLE tbl DROP FOREIGN KEY aaa;
ALTER TABLE tbl DROP INDEX aa