数据库相关语法
DCL:
DCL:给子账号开权限。
开子账号:
# 开子账号
create user 'OYO'@'localhost' identified by '123123';
子账号为:OYO
密码为:123123
登录子账号:
mysql -uOYO -p
添加权限:
# 添加权限
grant select,insert on 库名.* to 'OYO'@'localhost';
库名.*表示该库名下的所有表。
删除权限:
# 删除权限
revoke insert on 库名.* from 'OYO'@'localhost';
删除子账号:
# 删除子账号
drop user 'OYO'@'localhost';
设置编码格式:
# 设置临时编码格式(关闭命令行后就无效了)
SET CHARSET gbk;
数据类型:
数值类型(整型、浮点)
字符串类型
日期时间类型
复合类型
数值类型:
整数类型:
- tinyint - 1字节 - -128~127
- int - 4字节
tips:
存储状态码一般使用tinyint,其他的需求一般使用int就足够了。
整数类型 | 字节 | 取值范围 |
---|---|---|
tinyint | 1字节 | -128~127 |
smallint | 2字节 | -32768~32767 |
mediumint | 3字节 | -8388608~8388607 |
int | 4字节 | -2147483648~2147483647 |
bigint | 8字节 | ±9.22*10的18次方 |
int(3) 其中3表示数据不满3位使用0填充,实际上该字段上的数据的取值范围为:-2147483648~2147483647。
unsigned – 无符号zerofill – 0填充
CREATE TABLE a (
id int(3) unsigned zerofill NOT NULL PRIMARY KEY
)
insert into a(id) values(1);#001
insert into a(id) values(1234);#1234
浮点类型 :
float(8,2) – 单精度浮点型
double(8,2) – 双精度浮点型
decimal(8,2) – 以字符串形式存储的小数
(8,2)表示8位数,其中2位为小数位
tips:
因为float和double都有可能损失精度,有精度要求的字段一般使用decimal。
浮点类型 | 字节 | 取值范围 |
---|---|---|
float(m, d) | 4字节 | 单精度浮点型,m总个数,d小数位 |
double(m, d) | 8字节 | 双精度浮点型,m总个数,d小数位 |
decimal(m, d) | decimal是存储为字符串的浮点数 |
字符串类型:
char(32) – 定长字符串,如果存储"abc",底层会开辟32个字符长度的空间。
varchar(32) – 变长字符串,如果存储"abc",底层会根据具体数据开辟空间。
(32) – MySQL5.5之前为32字节,之后32个字符。
tips:char和varchar取值范围为0~255字符。
BLOB – 可以存储二进制数据的字符串类型,意味着该类型可以存储文件。
TEXT – 可以存储长文本数据的字符串类型,意味着该类型可以存储纯文本数据。
LONGBLOB – 可以存储极长的二进制数据。
LONGTEXT – 可以存储极长的纯文本数据。
字符串类型 | 字节 | 取 值范围 |
---|---|---|
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-255字节 | 变长字符串 |
TINYBLOB | 0-255字节 | 不超过255个字符的二进制字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
BLOB | 0-65535字节 | 二进制形式的长文本数据 |
TEXT | 0-65535字节 | 长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
VARBINARY(M) | 允许长度0-M个字节的定长字节符串 | 值的长度+1个字节 |
BINARY(M) | M | 允许长度0-M个字节的定长字节符串 |
tips:
存储二进制文件和纯文本文件的技术叫做CBLOB -> CLOB指的是存储纯文本文件,BLOB指的是存储二进制文件。
经验:MySQL虽然允许存储文件,但是实际开发中不会存储文件,如果要存储文件,在数据库中存储的是该文件的路径,是为了减少数据库的压力。
日期时间类型 :
date – 日期类型 – 2024-06-01
time – 时间类型 – 14:32:30
datetime – 日期时间类型 – 2024-06-01 14:32:30
timestamp – 时间戳 – 2024-06-01 14:32:30
year – 年份 – 2024
日期类型 | 字节 | 取值范围 |
---|---|---|
date | 3字节 | 日期,格式:2014-09-18 |
time | 3字节 | 时间,格式:08:42:30 |
datetime | 8字节 | 日期时间,格式:2014-09-18 08:42:30 |
timestamp | 4字节 | 自动存储记录修改的时间 |
year | 1字节 | 年份 |
# 时间戳可以设置更新数据就更新时间
CREATE TABLE a (
id int(3) unsigned zerofill NOT NULL PRIMARY KEY,
str varchar(3) COLLATE utf8mb4_general_ci DEFAULT NULL,
xxx timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
)
约束:
约束:对于该字段上数据的限制。
主键约束:
主键约束:该字段上的数据不允许重复且不能为null。
注意:一张表必须有主键,且只能有一个!
添加:
# 添加主键约束 -- 方式一
CREATE TABLE user(
username VARCHAR(32) PRIMARY KEY,
password VARCHAR(32),
name VARCHAR(32),
nickName VARCHAR(32),
phone VARCHAR(32),
sex VARCHAR(32),
age INT(3)
)
# 添加主键约束 -- 方式二
CREATE TABLE user(
username VARCHAR(32),
password VARCHAR(32),
name VARCHAR(32),
nickName VARCHAR(32),
phone VARCHAR(32),
sex VARCHAR(32),
age INT(3),
PRIMARY KEY(username,password)
)
PRIMARY KEY(username,password)->联合主键,意味着主键是username+password加在一起的数据。
# 添加主键约束 -- 方式三
CREATE TABLE user(
username VARCHAR(32),
password VARCHAR(32),
name VARCHAR(32),
nickName VARCHAR(32),
phone VARCHAR(32),
sex VARCHAR(32),
age INT(3)
)
ALTER TABLE user ADD PRIMARY KEY(username);
tips:创建表时就添加主键,方式三很少使用。
删除:
# 删除主键约束
ALTER TABLE user DROP PRIMARY KEY;
唯一约束:
唯一约束:该字段上的数据不允许重复,但可以为null。
# 添加唯一约束
ALTER TABLE user ADD UNIQUE(phone);
# 删除唯一约束
ALTER TABLE user DROP index phone;
非空约束:
非空约束:该字段上的数据不允许为null,但可以重复。
# 添加非空约束
ALTER TABLE user MODIFY password VARCHAR(32) NOT NULL;
# 删除非空约束
ALTER TABLE user MODIFY password VARCHAR(32) NULL;
# 添加默认值
ALTER TABLE user MODIFY password VARCHAR(32) DEFAULT '000000';
外键约束:
外键约束:保证两个字段之间参照完整性。
场景:
# 创建学科表,并插入数据
CREATE TABLE course(
id INT(3) PRIMARY KEY auto_increment,
name VARCHAR(32)
)
INSERT into course(name) VALUES('Java');
INSERT into course(name) VALUES('Python');
INSERT into course(name) VALUES('HTML');
# 创建学生表
CREATE TABLE student(
id INT(3) PRIMARY KEY auto_increment,
name VARCHAR(32),
c_id INT(3)
)
# 添加外键约束
alter table student add foreign key(c_id) references course(id);
# 删除外键约束
alter table student drop foreign key student_ibfk_1;
show create table student;
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
`c_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c_id` (`c_id`),
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `course` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
经验:外键已被淘汰,效率太低了,因为添加、修改数据时,MySQL都会去外键关联的表中查询是否有脏数据。
外键的优点:不会出现脏数据。外键的缺点:效率低。
如果不适用外键,如何避免脏数据呢?
在页面选择数据,不会乱填的!
索引:
索引:相当于一本书的目录,让查询更快。
注意:索引上的数据作为查询条件,速度会更快。
主键索引:
主键作为约束,该字段上的数据不能重复也不能为null,作为索引,查询会更快。
添加:
# 添加主键索引 -- 方式一
CREATE TABLE user(
username VARCHAR(32) PRIMARY KEY,
password VARCHAR(32),
name VARCHAR(32),
nickName VARCHAR(32),
phone VARCHAR(32),
sex VARCHAR(32),
age INT(3)
)
# 添加主键索引 -- 方式二
CREATE TABLE user(
username VARCHAR(32),
password VARCHAR(32),
name VARCHAR(32),
nickName VARCHAR(32),
phone VARCHAR(32),
sex VARCHAR(32),
age INT(3),
PRIMARY KEY(username,password)
)
PRIMARY KEY(username,password)->联合主键,意味着主键是username+password加在一起的数据。
# 添加主键索引 -- 方式三
CREATE TABLE user(
username VARCHAR(32),
password VARCHAR(32),
name VARCHAR(32),
nickName VARCHAR(32),
phone VARCHAR(32),
sex VARCHAR(32),
age INT(3)
)
ALTER TABLE user ADD PRIMARY KEY(username);
注意:创建表时就添加主键,方式三很少使用。
删除:
# 删除主键索引
ALTER TABLE user DROP PRIMARY KEY;
唯一索引:
注意:唯一作为约束,该字段上的数据不能重复,作为索引,查询会更快。
# 添加唯一索引
ALTER TABLE user ADD UNIQUE(phone);
# 删除唯一索引
ALTER TABLE user DROP index phone;
普通索引 :
普通索引:没有任何数据上的约束,作为索引,查询会更快。
# 添加普通索引
ALTER TABLE user ADD index(nickName);
# 删除普通索引
drop index nickName on user;
全文索引:
项目中不同,如果要做全文索引会使用ES(ElasticSearch)框架。
# 添加全文索引
ALTER TABLE news ADD FULLTEXT(info) WITH PARSER ngram;
ngram 中文、日文、韩文的全文解析器,如果不加则只能解析英文。
# 全文搜索
SELECT * FROM news WHERE MATCH(info) against('胡歌很帅');
# 删除全文索引
drop index info on news;
面试题:
面试题:索引为什么作为条件会更快?
因为索引底层使用B+Tree的数据结构,会让查询效率更高。
面试题:索引会让该字段作为条件查询更快,为什么创建表时不会每个字段都加上索引呢?(考点:索引的优缺点)
优点:该字段作为条件查询更快。缺点:添加、删除数据时,索引的数据结构发生改变。
tips:
- 会把经常作为查询条件的字段设置为索引。
- 偶尔添加或删除索引字段上的数据直接无视,批量添加或删除索引字段上的数据,可以先把索引删除后,等批量操作完再添加上索引。
索引的分类?
聚簇索引(聚集索引)和非聚簇索引(非聚集索引)。
面试题:聚簇索引和非聚簇索引的区别
聚簇索引(主键索引):叶子结点上存放的是数据行的数据,效率更高。非聚簇索引(唯一、普通、全文索引):叶子节点上存放的是数据行的地址。
标签:DCL,VARCHAR,字节,32,语法,索引,TABLE,主键 From: https://blog.csdn.net/m0_57463864/article/details/139621312面试题:索引失效的情况
1.最短路径算法(是走索引查询更快,还是顺序查找更快)
2.最左匹配原则
3.使用模糊查询
4.使用函数
5.使用OR
…