mysql基础
基础知识
一、数据库的存储引擎
1、数据库存储引擎类型
# mysql show databases;create database my_db;show engines; # 显示数据库支持的引擎show variables like ‘hava%‘; # 显示变量中含有have开头的任意变量 ，% 代表的任意字符，show variables like ‘storage_engine%‘ # 数据库的搜索引擎性能不大一样，默认的innodb
1
# mysql
2
show databases;
3
create database my_db;
4
show engines; # 显示数据库支持的引擎
5
show variables like ‘hava%‘; # 显示变量中含有have开头的任意变量 ，% 代表的任意字符，
6
show variables like ‘storage_engine%‘
7
8
# 数据库的搜索引擎性能不大一样，默认的innodb
2、数据类型
2.1 整形数据类型
从小到大tinyint 1字节small int 2字节mediumint 3字节int/integer 4字节bigint 8字节
1
从小到大
2
tinyint 1字节
3
small int 2字节
4
mediumint 3字节
5
int/integer 4字节
6
bigint 8字节
2.2 浮点数、定点数、位类型
float 4字节double 8字节dec(M, D)/decimal(M, D) # (M+2)字节,D表示精确到小数点后D位bit(M) 1-8字节， M的取值范围就是1-8， 最小值是bit(1),最大值bit(64)
1
float 4字节
2
double 8字节
3
dec(M, D)/decimal(M, D) # (M+2)字节,D表示精确到小数点后D位
4
bit(M) 1-8字节， M的取值范围就是1-8， 最小值是bit(1),最大值bit(64)
2.3 日期和时间类型
date 4字节 # 一般表示年月日用这个datetime 8字节 # 年月日时分秒 用这个timestamp 4字节 # 如果要经常插入或者更新日期为当前系统时间，一般用这个time 3字节 # 表示时分秒用这个year 1字节 # 表示年份用这个
1
date 4字节# 一般表示年月日用这个
2
datetime8字节# 年月日时分秒 用这个
3
timestamp4字节# 如果要经常插入或者更新日期为当前系统时间，一般用这个
4
time 3字节# 表示时分秒用这个
5
year1字节# 表示年份用这个
2.4 字符串类型
——————————————————————————————char系列char(M) M字节，M为0~255之间的整数varchar(M) M字节，M为0~65535之间的整数——————————————————————————————text系列tinytext 0~255text 0~65535mediumtextlongtext——————————————————————————————binary系列，binary可以存储二进制文件，图片音乐视频binary(M) M字节varbinary(M) M字节 数据长度经常变换可以选择varbinary ——————————————————————————————blob系列,可以存储二进制数据，比如图片音乐视频，存储大小更大tinyblob 0~255字节blob 0~2^16字节mediumblob 0~2^24字节longblob 0~2^32字节
1
——————————————————————————————char系列
2
char(M)M字节，M为0~255之间的整数
3
varchar(M)M字节，M为0~65535之间的整数
4
5
——————————————————————————————text系列
6
tinytext0~255
7
text0~65535
8
mediumtext
9
longtext
10
11
——————————————————————————————binary系列，binary可以存储二进制文件，图片音乐视频
12
binary(M)M字节
13
varbinary(M)M字节数据长度经常变换可以选择varbinary
14
15
——————————————————————————————blob系列,可以存储二进制数据，比如图片音乐视频，存储大小更大
16
tinyblob 0~255字节
17
blob0~2^16字节
18
mediumblob0~2^24字节
19
longblob0~2^32字节
二、表的操作
1、表的基础
表的属性，列(columns)，索引(index)，触发器(triggers)创建一个表create table tbl_blog( blog_id int not null, blog_title varchar(30) not null, blog_author varchar(30) not null );
1
表的属性，列(columns)，索引(index)，触发器(triggers)
2
创建一个表
3
create table tbl_blog(
4
blog_id int not null,
5
blog_title varchar(30) not null,
6
blog_author varchar(30) not null
7
);
2、表的增、删、改、查
2.1 增
create table tbl_blog( blog_id int not null, blog_title varchar(30) not null, blog_author varchar(30) not null );
1
create table tbl_blog(
2
blog_id int not null,
3
blog_title varchar(30) not null,
4
blog_author varchar(30) not null
5
);
2.2 删
# 删除表drop table tbl_blog;
1
# 删除表
2
drop table tbl_blog;
2.3 改
# 修改表名alter table tbl_blog rename new_tbl_blog;# 在表的最后一列，增加字段alter table tbl_blog add post_main mediumtext # 在表的第一列，增加字段alter table tbl_blog add post_main mediumtext first;# 在表指定字段后，增加字段alter table tbl_blog add post_main mediumtext after blog_id ;# 删除字段alter table tbl_blog drop post_main;#修改字段属性alter table tbl_blog modify post_main longtext;# 修改字段顺序alter table tbl_blog modify blog_title varchar(30) not null first;alter table tbl_blog modify blog_title varchar(30) not null after blog_title;# 修改字段名alter table tbl_blog change post_main new_post_main longtext;# 修改字段名和属性alter table tbl_blog change post_main new_post_main text;
1
# 修改表名
2
alter table tbl_blog rename new_tbl_blog;
3
4
# 在表的最后一列，增加字段
5
alter table tbl_blog add post_main mediumtext
6
7
# 在表的第一列，增加字段
8
alter table tbl_blog add post_main mediumtext first;
9
10
# 在表指定字段后，增加字段
11
alter table tbl_blog add post_main mediumtext after blog_id ;
12
13
# 删除字段
14
alter table tbl_blog drop post_main;
15
16
#修改字段属性
17
alter table tbl_blog modify post_main longtext;
18
# 修改字段顺序
19
alter table tbl_blog modify blog_title varchar(30) not null first;
20
alter table tbl_blog modify blog_title varchar(30) not null after blog_title;
21
22
# 修改字段名
23
alter table tbl_blog change post_main new_post_main longtext;
24
# 修改字段名和属性
25
alter table tbl_blog change post_main new_post_main text;
26
2.4 查
desc tbl_blog; # 等价 describe tbl_blog;show create table tbl_blog; # 显示表的创建语法；
1
desc tbl_blog; # 等价 describe tbl_blog;
2
show create table tbl_blog; # 显示表的创建语法；
3、操作表的约束
有约束的时候，最后填一下约束名constraint name， 给约束起个名而已,如果不写约束名也可以,系统会自动帮你生产一个,
但删除约束时就挺麻烦的,要查系统表得到自动分配的那个约束名才能删除.
但删除约束时就挺麻烦的,要查系统表得到自动分配的那个约束名才能删除.
3.1 default
create table tbl_student1( student_score int default 0 );
1
create table tbl_student1(
2
student_score int default 0
3
);
3.2 primary key
# 单字段主键create table tbl_student1( student_score int default 0, student_num int, constraint pk_stu_num primary key(student_num) ); # 多字段主键create table tbl_student1( student_score int default 0, student_num int, personal_id int, constraint pk_self primary key(student_num, personal_id) );
1
# 单字段主键
2
create table tbl_student1(
3
student_score int default 0,
4
student_num int,
5
constraint pk_stu_num primary key(student_num)
6
);
7
8
# 多字段主键
9
create table tbl_student1(
10
student_score int default 0,
11
student_num int,
12
personal_id int,
13
constraint pk_self primary key(student_num, personal_id)
14
);
3.3 unique
create table tbl_student1( student_score int default 0, student_num int, constraint uk_stu_num unique(student_num) );
1
create table tbl_student1(
2
student_score int default 0,
3
student_num int,
4
constraint uk_stu_num unique(student_num)
5
);
3.4 foreign key
mysql> create table tbl_class( -> class_num int unique, -> class_name varchar(30) unique -> );mysql> create table tbl_student( -> students_num int primary key auto_increment, -> student_name varchar(30) not null, -> student_class varchar(20) unique -> ); alter table tbl_student add CONSTRAINT fk_class foreign key(student_class) references tbl_class(class_name);
1
mysql> create table tbl_class(
2
-> class_num int unique,
3
-> class_name varchar(30) unique
4
-> );
5
6
mysql> create table tbl_student(
7
-> students_num int primary key auto_increment,
8
-> student_name varchar(30) not null,
9
-> student_class varchar(20) unique
10
-> );
11
12
13
alter table tbl_student add CONSTRAINT fk_class foreign key(student_class) references tbl_class(class_name);
3.5 not null
create table tbl_student1( student_score int not null );
1
create table tbl_student1(
2
student_score int not null
3
);
3.6 auto_increment
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keymysql> create table tbl_student( -> students_num int primary key auto_increment, -> student_name varchar(30) not null, -> student_class varchar(20) unique -> );
1
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
2
3
mysql> create table tbl_student(
4
-> students_num int primary key auto_increment,
5
-> student_name varchar(30) not null,
6
-> student_class varchar(20) unique
7
-> );
三、索引的操作
1、为什么要用索引
创建索引可以提高从表中检索数据的速度索引是创建在数据库表对象上的，由表中的一个字段或者多个字段生成的键组成，这些键存储在数据结构（B-tree或者hash表）中通过mysql可以快速有效的查找与键值相关联的字段，根据索引的存储类型，可以将索引分为B型树索引（BTREE），和哈希索引（hash）# InnoDB和MyISAM支持BTREE索引，MEMORY存储引擎支持HASH索引，默认索引为BTREE索引数据库对象索引的出现，能够带来的好处1、提高数据检索速度2、可以保证字段唯一性，从而实现数据的完整性缺点：过多索引会占据许多的磁盘空间MySQL支持6种索引，普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引以下情况适合创建索引1、经常被查询的字段，即在where子句中出现的字段2、存在分组的字段，即在group by子句中出现的字段3、存在依赖关系的子表和父表之间的联合查询，即主键或外键字段4、设置唯一完整性约束的字段以下情况不适合创建索引1、很少被查询到的字段2、有许多重复值的字段
1
创建索引可以提高从表中检索数据的速度
2
3
索引是创建在数据库表对象上的，由表中的一个字段或者多个字段生成的键组成，这些键存储在数据结构（B-tree或者hash表）中
4
通过mysql可以快速有效的查找与键值相关联的字段，
5
6
根据索引的存储类型，可以将索引分为B型树索引（BTREE），和哈希索引（hash）
7
8
# InnoDB和MyISAM支持BTREE索引，MEMORY存储引擎支持HASH索引，默认索引为BTREE索引
9
10
数据库对象索引的出现，能够带来的好处
11
1、提高数据检索速度
12
2、可以保证字段唯一性，从而实现数据的完整性
13
缺点：
14
过多索引会占据许多的磁盘空间
15
16
MySQL支持6种索引，普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引
17
18
以下情况适合创建索引
19
1、经常被查询的字段，即在where子句中出现的字段
20
2、存在分组的字段，即在group by子句中出现的字段
21
3、存在依赖关系的子表和父表之间的联合查询，即主键或外键字段
22
4、设置唯一完整性约束的字段
23
24
以下情况不适合创建索引
25
1、很少被查询到的字段
26
2、有许多重复值的字段
2、创建索引
2.1 创建普通索引
1、建表是创建普通索引mysql> create table tbl_school( -> sc_name varchar(30) unique, -> sc_num int primary key auto_increment, -> sc_student_sum int not null， -> index index_sc_num(sc_num) -> );INSERT INTO tbl_school(sc_name, sc_student_sum)VALUES(‘HUAYING‘, 1000);explain select * from tbl_school where sc_num=1 \G2、在已存在的表上创建普通索引mysql> create index index_stu_num on tbl_student(students_num);3、通过alter语句创建普通索引mysql> alter table tbl_class add index index_c_num(class_num);
1
1、建表是创建普通索引
2
mysql> create table tbl_school(
3
-> sc_name varchar(30) unique,
4
-> sc_num int primary key auto_increment,
5
-> sc_student_sum int not null，
6
-> index index_sc_num(sc_num)
7
-> );
8
9
INSERT INTO tbl_school
10
(sc_name, sc_student_sum)
11
VALUES(‘HUAYING‘, 1000);
12
13
explain select * from tbl_school where sc_num=1 \G
14
15
2、在已存在的表上创建普通索引
16
mysql> create index index_stu_num on tbl_student(students_num);
17
18
3、通过alter语句创建普通索引
19
mysql> alter table tbl_class add index index_c_num(class_num);
2.2 创建唯一索引
1、建表时创建唯一索引2、建表后创建唯一索引3、通过aler创建唯一索引
1
1、建表时创建唯一索引
2
2、建表后创建唯一索引
3
3、通过aler创建唯一索引
3、查看索引
explain select * from tbl_school where sc_num=1 \G
1
explain select * from tbl_school where sc_num=1 \G
4、删除索引
drop index index_name on tbl_name;
1
drop index index_name on tbl_name;
相关推荐
emmm00 2020-11-17
王艺强 2020-11-17
aydh 2020-11-12
世樹 2020-11-11
zry 2020-11-11
URML 2020-11-11
spurity 2020-11-10
yifangs 2020-10-13
Andrea0 2020-09-18
Ida 2020-09-16
ltd00 2020-09-12
tufeiax 2020-09-03
xjd0 2020-09-10
greatboylc 2020-09-10
adsadadaddadasda 2020-09-08
疯狂老司机 2020-09-08
CoderToy 2020-11-16
ribavnu 2020-11-16
bianruifeng 2020-11-16