MySQL001：第一次接触与表的相关操作

第一种登录，直接密码在外，

mysql -h 127.0.0.1 -uroot -p

mysql ：mysql数据库命令
-h : 后面加上服务器IP
127.0.0.1： 本地计算机IP地址
-u: 后面接用户名
root: 此处用root用户登录
-p: 后面跟密码

第二种登录：密码隐藏

mysql -uroot -p

创建数据库，删除数据库 CREATE TABLE && SHOW && CREATE DATABASE

DROP DATABASE TEST2_DB;

SHOW DATABASES;

CREATE DATABASE TEST2_DB;

查看新建的数据库与其中的表 USE && SHOW && SHOW COLUMNS FROM && DESCRIBE && DESC

SHOW DATABASES; 

USE TEST_DB;

SHOW TABLES;

SHOW COLUMNS FROM MYTEST_TABLE;

DESCRIBE MYTEST_TABLE;

CREATE TABLE t_class(
classno INT,
cname VARCHAR(20),
loc VARCHAR(40),
stucount INT);

DESCRIBE t_class

DESC T_CLASS;

DESCRIBE 可以缩写为DESC

HELP SHOW

HELP SHOW;

部分SHOW

SHOW TABLES;

SHOW CREATE TABLE MYTEST_TABLE;

\G显示规范格式

修改表名 ALTER TABLE && RENAME

ALTER TABLE t_class RENAME tab_class;
desc tab_class;

ALTER TABLE TAB_CLASS RENAME tac_class;
DESC TAC_CLASS;

删除表 DROP TABLE

DROP TABLE TAC_CLASS;

增加字段，在表的最后一个位置增加 ALTER TABLE && ADD

ALTER TABLE TAC_CLASS ADD advisor VARCHAR(20);

删除字段 ALTER TABLE && DROP

ALTER TABLE TAC_CLASS DROP advisor;

增加字段，在表的第一个位置增加 ALTER TABLE && ADD

ALTER TABLE TAC_CLASS ADD advisor VARCHAR(20) FIRST;

增加字段，在指定字段之后添加 ALTER TABLE && ADD && AFTER

ALTER TABLE TAC_CLASS ADD advisor VARCHAR(15) AFTER CLASSNO;

修改字段—修改字段的名字 ALTER TABLE && CHANGE

ALTER TABLE TAC_CLASS CHANGE CLASSNO CLASSNOID INT;
DESCRIBE TAC_CLASS;

修改字段—只修改字段类型 ALTER TABLE && MODIFY

ALTER TABLE tac_class MODIFY classno VARCHAR(10);

修改字段—同时修改字段的名字和类型 ALTER TABLE && CHANGE

ALTER TABLE tac_class CHANGE classno classid VARCHAR(25);
DESCRIBE TAC_CLASS;

修改字段—修改字段顺序

移动到表头 ALTER TABLE && MODIFY && FIRST

ALTER TABLE tac_class MODIFY cname varchar(20) FIRST;

移动到指定字段之后 ALTER TABLE && MODIFY && AFTER

ALTER TABLE tac_class MODIFY stucount int AFTER cname;

操作表的约束

完整性约束条件

PRIMARY KEY	标识该属性为该表的主键，可以唯一的标识对应的元组
FROEIGN KEY	标识该属性为该表的外键，是与之联系的某表的主键
NOT NULL	标识该属性不能为空
UNIQUE	标识该属性的值是唯一的
AUTO_INCREMENT	标识该属性的值是自动增加的
DEFAULT	为该属性设置默认值

设置表字段的非空约束 NOT NULL

创建时对某些字段加入NOT NULL约束条件，如果用户插入的记录中该字段为空值，直接报错

CREATE TABLE t_class(
classno INT(11) NOT NULL,
cname VARCHAR(20),
loc VARCHAR(40),
stucount INT(11)
);

SHOW TABLES;

SHOW COLUMNS FROM T_CLASS;

设置表字段的默认值 DEFAULT

向数据库表中插入一条新记录的时候，如果没有为某些字段赋值，数据库系统自动为其写入默认值

 CREATE TABLE x_class(
 classno INT(11) NOT NULL,
 cname VARCHAR(20) DEFAULT 'SDSSD',
 loc VARCHAR(40),
 stucount INT(11)
 );

 SHOW COLUMNS FROM X_CLASS;

设置表字段唯一约束 UNIQUE

当数据库表中某个字段的内容不允许重复的时候，可以使用唯一约束进行设置，唯一约束在创建的时候为某些字段加入UNIQUE即可实现

INSERT INTO a_class(classno,cname,loc,stucount) VALUES(2,'class_3','loc_4',4);

DESC A_CLASS;

SELECT * FROM A_CLASS;

UNIQUE标识的字段，被重复插入重复值得新记录的时候报错

设置表字段的主键约束 PRIMARY KEY

什么是主键

主键用来标识每个记录，每个记录的主键值都不同
设置表的主键是指创建表时设置某个字段为该表的主值
主键必须是非空值
主键可以是单一的字段，也可以是多个字段的组合

单字段主键 && 插入新记录 INSERT INTO && VALUES

CREATE TABLE b_class(
stuno INT PRIMARY KEY,
sname VARCHAR(20),
sage INT,
sgender VARCHAR(4)
);

INSERT INTO b_class VALUES(1,'FRAND',25,'D');

INSERT INTO b_class VALUES(1,'Alan',19,'f');

SELECT * FROM B_CLASS;

多字段主键 CONSTRAINT

CREATE TABLE c_class(
stuno INT,
sname VARCHAR(20),
sage INT(10),
sgender VARCHAR(4),
CONSTRAINT pk_stuno_sname PRIMARY KEY(stuno,sname)
);

DESCRIBE C_CLASS;

INSERT INTO C_CLASS VALUES(1,'JACK',15,'A');
INSERT INTO C_CLASS VALUES(1,'ALAN',19,'F');
INSERT INTO C_CLASS VALUES(2,'LINDA',20,'B');

INSERT INTO C_CLASS VALUES(2,'LINDA',25,'C');

SELECT * FROM C_CLASS;

主键值必须唯一确定

设置表字段值自动增加 AUTO_INCREMENT

AUTO_INCREMENT是MySQL唯一拓展的完整性约束，当为数据库表插入新记录时，字段上的值会自动生成唯一的ID。一个数据库表中只能有一个字段使用该约束，该字段的数据类型必须是整数类型，由于生成唯一ID，通常会同时设置为PK约束（主键）

CREATE TABLE d_class(
classno INT(11) PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
loc VARCHAR(40),
stucount INT(11)
);

DESCRIBE D_CLASS;

INSERT INTO D_CLASS VALUES(15,'ALAN','F',20);
INSERT INTO D_CLASS VALUES(25,'FRIEND','A',25);

指定前面没有出现过的插入值的时候，不进行自动增长

插入值为0或者NULL时 使用自动增长列

设置表字段的外键约束 FOREIGN KEY

外键（FOREIGN KEY，FK），是表的一个特殊字段，外键约束用于保证多个表（通常为两个表）之间的参照完整性，即构建与两个表字段之间的参照关系

设置外键的两个表之间具有父子关系，即字表中某个字段的取值范围由父表确定

班级表和学生表，学生表有一个班级编号classno，其依赖于班级表的主键，这样字段classno就是学生表的外键，通过该字段，班级表与学生表建立了关系

设置FK约束的时候，设置FK约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以设置为NULL

设置外键时，子表外键的数据类型必须与该外键所依赖的父表的主键的数据类型保持一致

建立存在主键的父表

CREATE TABLE e_class(
classno INT(11) PRIMARY KEY,
cname VARCHAR(20),
loc VARCHAR(40),
stucount INT(11)
);

DESC E_CLASS;

依赖于此父表设置带外键的子表 CONSTRAINT && FOREIGN KEY && REFERENCES

CREATE TABLE e_student(
stuno INT PRIMARY KEY,
sage INT,
sgender VARCHAR(20),
classno INT,
CONSTRAINT fk_classno FOREIGN KEY(classno) REFERENCES e_class(classno)
);

SHOW COLUMNS FORM E_CLASS;
SHOW COLUMNS FORM E_STUDENT;

INSERT INTO E_CLASS VALUES(15,'CS192','HEBUT',28);
INSERT INTO E_STUDENT VALUES(195779,19,'ALAN',15);
INSERT INTO E_STUDENT VALUES(195778,19,'HANZONG',15);

INSERT INTO E_STUDENT VALUES(195780,20,'ZENGBO',14);

父表没有任何记录的时候不能直接插入子表的记录

先插入父表的记录（班级），再插入子表的记录（这个班级的一个学生），如果classno不一致（插入的不是这个班级的学生）会报错

查看mysql支持的存储引擎

SHOW ENGINES;

SHOW ENGINES \G;

engine： 存储引擎名称
support ： yes 系统支持该存储引擎 no 不支持
default ： 系统默认支持的存储引擎
transactions : 系统引擎是否支持事务 yes 支持， no 不支持
XA： 存储引擎所支持的分布式是否符合XA规范，yes 支持 no 不支持
savepoints ： 存储引擎是否支持事务处理的保存点， yes支持，no 不支持

另一种办法查看所默认支持的存储引擎

SHOW VARIABLES LIKE 'default_storage_engine';

默认存储引擎： InnoDB存储引擎

MySQL5.6版本之后，除系统数据库外默认存储引擎都从MyISAM改为InnoDB 。MySQL8.0之后在原先的基础上将系统数据库的存储引擎也改成了InnoDB。

InnoDB存储引擎支持自动增长列AUTO_INCREMENT。自动增长列的值不能为空，且值必须唯一

MySQL中规定自动增长列必须为主键

在插入值时，如果自动增长列不输入值，插入的值就为自动增长后的值

如果输入的值为0或空（NULL），插入的值也为自动增长后的值

如果插入某个确定的值，且该值在前面没有出现过，就可以直接插入

InnoDB存储引擎支持外键（FOREIGN KEY）。外键所在的表为子表，外键所依赖的表为父表。父表中被子表外键关联的字段必须为主键。当删除、更新父表的某条信息时，子表必须有相应的改变

InnoDB优点：良好的事务管理、崩溃修复能力和并发控制

InnoDB缺点： 读写效率稍差，占用的数据空间相对较大

MyISAM 存储引擎

MyISAM存储引擎的表支持三种存储格式：静态型、动态型、压缩型

优点：占用空间小，处理速度快

缺点：不支持事务的完整性和并发性

MEMORY 存储引擎

使用存储在内存的内容来创建表，而且所有数据都放在内存中，数据处理速度快，但是安全性不高

存储引擎总结

在同一个数据库中，不同的表可以使用不同的存储引擎

如果一个表要求较高的事务处理，就可以选择InnoDB存储引擎

如果一个表会被频繁查询，就可以选择MyISAM存储引擎

如果是一个用于查询的临时表，就可以选择MEMORY存储引擎