MYSQL大全

-----------------------------------------------------------------------------------------------------------------
DML数据管理语言
-----------------------------------------------------------------------------------------------------------------
一、库的管理

1、显示所有库：show databeses;
2、显示当前库(X库)的所有数据表：show tables (from X库);
3、查询当前库：select database();
4、创建库：create database (if not exists) X库;
5、库的修改(更改库的字符集)：alter database X库 character set utf8/gbk;
6、库的删除：drop database (if exists) X库;
7、使用库： use X库;

二、表的管理

1、表的创建：
create table 表名 (
		列名 列的类型 【(长度)约束】，
		列名 列的类型 【(长度)约束】，
		……
)
【实例】
create table book(
		id int,  # 书ID
		bname varchar(20),  # 书名
		price double,  # 价格
		authorID int,  # 作者ID
		publishData datetime  # 出版日期
);
desc book;  # 查看book表
create table author(
		id int,
		au_name varchar(20),
		nation varchar(10)
); 
desc author;  # 查看author表

2、表的修改:
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】
【实例】
修改列名
alter table book change column publishdate pubDate datetime;

修改列的类型或约束
alter table book modify column pubdate timestamp;

添加新列
alter table author add column annual double;

删除列
alter table author drop column annual;

修改表名
alter table author rename to book_author;

3、表的删除：
drop table (if exists) 表名;

4、表的复制:
insert into author values
(1, '村上春树','日本'),
(2, '莫言','中国'),
(3, '冯唐','中国'),
(4, '金庸','中国');
select * from Author;
select * from copy;
<1>、仅仅只复制表的结构
create table copy like author;
<2>、复制表的结构+数据
create table copy2
select * from author;
  只复制部分数据
create table copy3
select id, au_name
from author
where nation='中国';
  仅仅复制某些字段
create table copy4
select id, au_name
from author
where 0;

建库的通用写法：
drop database if exists 旧库名;
create database 新库名;
新建表的通用写法:
drop table if exists 旧表名;
create table 新表名(...);

-----------------------------------------------------------------------------------------------------------------
数值类型
-----------------------------------------------------------------------------------------------------------------
《一、整数》

整数类型（字节）	范围
Tinyint (1)	有符号：-128 ~ 127；无符号：0 ~ 255
Smallint (2)	有符号：-32768 ~ 32767；无符号：0 ~ 65535
Mediumint (3)	有符号：-8388608 ~ 8388607； 无符号：0 ~ 1677215
Int、integer (4)	有符号：-2147483648 ~ 2147483647；无符号：0 ~ 4294967295
Bigint （8）	反正很大，不写了

特点：
<1> 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字.
<2> 如果插入的数值超出了整数的范围，会报out of range异常,并且插入临界值.
<3> 如果不设置长度，会有默认的长度。 <注意>：长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用.
测试

drop table if exists tab_int;
create table tab_int(
	t1 int(7) zerofill,
	t2 int(7) unsigned
);
insert into tab_int values(123, 123);

《二、小数》

浮点数类型 （字节）	范围
float (4)	±1.75494351E-38~±3.4028234666E+38
double (8)	很大，就不写了

定点数类型（字节）	范围
DEC(M, D) DECIMAL(M, D) (M+2)	最大值范围与double相同，给定decimal的有效值范围由M和D决定

测试

create table tab_float(
	f1 FLOAT(5, 2),
	f2 DOUBLE(5, 2),
	f3 DECIMAL(5, 2)
);
insert into tab_float values(123.45, 123.45, 123.45);
insert into tab_float values(123.456, 123.456, 123.456);
insert into tab_float values(123.4, 123.4, 123.4);
insert into tab_float values(1523.45, 1523.45, 1523.45);#报错
select * from tab_float;

特点：
<1>
M: 整数部位+小数部位
D: 小数部位
如果超过范围，则插入临界值
<2>
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
<3>
定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑
《三、文本》

较短的文本：

字符串（最多字符数）	描述及存储需求
char(M)	M为0~255之间的整数 特点：固定长度、耗费空间、效率高，M可省略，默认为1
varchar(M)	M为0~65535之间的整数 特点：可变长度、节省空间、效率低，M不可省略

较长的文本：
text
blob(较大的二进制，如图片数据）

其他的文本
《位类型》

位类型（字节）	范围
Bit(M) (1~8)	Bit(1)~Bit(8)

《binary和varbinary类型》
说明：类似于char和varchar，不同的是它们包含二进制字符串而不包含非二进制字符串。

《Enum类型》
枚举类型，要求插入的值必须属于列表中指定的值之一。
例

create table tab_char(
				c1 Enum('a', 'b', 'c')
		);
insert into tab_char values('a');
insert into tab_char values('b');
insert into tab_char values('c');
insert into tab_char values('d'); # 报错
insert into tab_char values('A');

《Set类型》
说明：和Enum类型类似，里面可以保存0~64个成员。和Enum类型最大的区别是：Set类型一次可以选取多个成员，而Enum只能选一个；根据成员个数不同，存储所占的字节也不同

create table tab_set(
				s1 set('a', 'b', 'c', 'd')
		);
insert into tab_set values('a');
insert into tab_set values('a', 'b');
insert into tab_set values('a', 'c', 'd');

《四、日期型》

timestamp时间戳相较于datetime,只能从1970年到2038年，因此，字节也较小；此外，timestamp反映的是实际时区的时间，datetime只能反映插入时的当地时区。

常见约束
六大约束：

1. NOT NULL：非空、用于保证该字段的值不能为空， 比如姓名、学号等
2. default：默认保证该字段有默认值，比如性别默认为男
3. primary key：主键，用于保证该字段有唯一性，且非空，比如学号，员工编号等
4. unique：唯一，用于保证该字段有唯一性，可以为空，比如座位编号
5. check：检查约束【mysql不支持】用于保证该字段的值在一个范围内，比如性别只能为男和女
6. primary key：外键，用于限制两个表之间的关系，用于保证该字段的值必须来自于主表的关联列的值，比如学生的专业编号，课程编号

添加约束的时机：

1. 创建表时
2. 修改表时

约束的添加分类：

1. 列级约束：六大约束语法上都支持，但外键约束没有效果

创建表时：
create table 表名(
	字段名 字段类型 字段约束
	表级约束
	)
修改表时：
alter table 表名 modify column 字段名 字段类型 字段约束 

创建表时：
create table stuinfo(
	id int primary key, # 主键约束
	majorName varchar(20) not null, # 非空约束
	gender char(1) check(gender='男' or gender='女'), # 检查约束
	seat int unique # 唯一约束
	age int default 18 # 默认约束
	majorID int references major(id) # 外键约束  但是没有效果
	);
create table major(
	id int primary key, 
	majorName varchar(20)
	);
修改表时：
alter table stuinfo modify column majorName varchar(20) not null; # 添加非空约束
alter table stuinfo modify column age int default 18; # 添加默认值
alter table stuinfo modify column id int primary key;  # 添加主键
alter table stuinfo modify column seat int unique;  # 添加唯一约束

2. 表级约束：除了非空、默认

创建表时语法：在各字段后加
【constraint 约束名】 约束类型(字段名)
修改表时语法：alter table add 【constraint 约束名】 约束类型(字段类型) 【外键的引用】;  

创建表时：
drop table if exists stuinfo;
create table stuinfo(
	id int,
	majorName varchar(20),
	gender char(1),
	seat int,
	age int,
	majorID int,

	constraint pk primary key(id), # 主键
	constraint uq unique(seat), # 唯一键
	constraint ck check(gender='男' or gender='女'), # 检查
	constraint fk_stuinfo foreign key(majorID) references major(id) # 外键
	);
修改表时：
alter table stuinfo add primary key(id);  # 添加主键(表级约束) 
alter table stuinfo add unique(unique);  # 添加唯一约束(表级约束)
alter table stuinfo add 【constraint fk_stuinfo_major】 foreign key(majorID) references major(id); # 添加外键