MySQL数据库基础之数据类型/表操作/库操作

一：数据类型

1.数据类型分类
数值类型
字符串类型
时间和日期类型

A.数值类型

整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT

作用：用于存储用户的年龄、游戏的Level、经验值等。

浮点数类型 FLOAT DOUBLE
作用：用于存储用户的身高、体重、薪水等
float(5.3) 5宽度 3精度

案例：

mysql> create database tnt;
Query OK, 1 row affected (0.00 sec)
#表是存放在库里面的,要先有库才能有表.每一个sql语句结束都要用分号;

mysql> use tnt;
Database changed
#进入到tnt这个库里面.

mysql> create table t1(id float(6,2)); 
Query OK, 0 rows affected (0.02 sec)
#创建一个名字为t1的表,数值是浮点数类型,宽度为6,精度为2;

mysql> insert into t1 values('6.66');
Query OK, 1 row affected (0.04 sec)

#往t1表格里面插入一个值为6.66的数据.这个值要符合之前定义的宽度和精度规范.
#宽度代表一共有几位数,不包括小数点,精度代表是小数点后可以存放几位.

mysql> insert into t1 values('6.666666');
Query OK, 1 row affected (0.01 sec)
#这里插入一个不符合精度的数值.也提示成功.

mysql> select * from t1;
#查询这个来自t1表里的所有内容.
+------+
| id   |
+------+
| 6.66 |
| 6.67 |
+------+
2 rows in set (0.00 sec)
#这里看到虽然上面显示插入成功,但它最多也只能帮我们存储2个精度的数据.并且给我们四舍五入了.

定点数类型 DEC（了解）
作用：定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。

位类型 BIT（了解）

作用：BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位

B.字符串类型

CHAR系列 CHAR VARCHAR （常见,存储名字）
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT（常见,存储内容）
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚举类型： ENUM（常见,存储性别）
集合类型： SET（常见,事先把一些内容放在一个盒子里,用到哪些内容就在这个盒子里面取）

枚举类型： 枚举列可以把一些不重复的字符串存储成一个预定义的集合
用途：存储性别等

mysql> create table t2(e enum('fish','apple','dog'));
Query OK, 0 rows affected (0.00 sec)
#创建一个表名为t2,字段是e,类型为enum枚举,预定义集合为fish,apple,dog,每一个值都要用,逗号隔开,字符串要用单引号引起来.

mysql> insert into t2 values('apple');
Query OK, 1 row affected (0.00 sec)
#往t2表里插入一个apple的值.

mysql> insert into t2 values('cat');
ERROR 1265 (01000): Data truncated for column 'e' at row 1
#再往t2表插入一个刚才没有预定义的值cat,显示插入失败.
#意思就是,在使用枚举enum类型的过程当中,如果要插入一个值,那么要保证这个值在预定义集合当中.
#比如说性别,一般定义是男和女,所以性别一般是用枚举来预定义设定.这样就可以避免别人输入一些奇葩的内容.

C.时间和日期类型： DATE TIME DATETIME TIMESTAMP YEAR

作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

mysql> create table t3(id1 timestamp NOT NULL default CURRENT_TIMESTAMP,
    -> id2 datetime default NULL
    -> );
#分行写法,可以一行写,可以这样写,遇到分号;就结束.
#创建一个名为t3的表,添加一个id1字段,类型为timestamp 参数是,NOT NULL default CURRENT_TIMESTAMP,意思是无论有没有给id1插入一个值,他都会插入一个系统时间,要是有插入值的话,系统时间就不会显示,没有插入值时,它会插入一个当前的系统时间.然后第二个字段id2,
#
Query OK, 0 rows affected (0.00 sec)

mysql> desc t3;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
| id2   | datetime  | YES  |     | NULL              |       |
+-------+-----------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)
#desc命令来查看t3的表格结构.
#field是字段（列）,type是类型,null是否为空,key有没有键,Default默认有哪些参数.extra对这个字段的额外说明.

mysql> insert into t3(id1) values('20180109000000');
Query OK, 1 row affected (0.00 sec)
#因为id1字段的类型是timestamp,所以我们这里往t1插入一个时间戳,值是20180109000000
#注意如果一个表格里面有多个字段,insert into 表格（字段）,要这样写,如果insert into 表格,这样写的话,它会提示失败,因为它不值得你要往哪个字段插入值.

mysql> select * from t3;
+---------------------+------+
| id1                 | id2  |
+---------------------+------+
| 2018-01-09 00:00:00 | NULL |
+---------------------+------+
1 row in set (0.00 sec)
#查看t3表格的所有内容.

mysql> insert into t3(id2) values('20180109000000');
Query OK, 1 row affected (0.00 sec)
#这里给id2插入一个值,然后我们要看id1的变化.

mysql> select * from t3;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2018-01-09 00:00:00 | NULL                |
| 2020-05-01 15:25:52 | 2018-01-09 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
#给id2插入一个值之后,id2正常显示值,但我们并没有给id1插入一个值,所以id1没有值就会显示系统的当前时间.因为id1有一个默认的参数.
mysql> select now();
#查系统当前时间.

二：表操作（重点）

表是数据库存储数据的基本单位,表由若干个字段和值组成,可以有多个字段,每个字段有对应的值,主要用来存储数据记录.
数据库—数据表—字段—赋值
数据都存放在里,所以表是我们数据库的基本单位.

表的操作包括:
创建表、查看表、修改表和删除表。
这些操作都是数据库管理中最基本，也是最重要的操作。
也就是表的增删改查.
创建表 create table
查看表 select table
查看表结构 desc table, show create table
修改表 alter table
复制表 create table …
删除表 drop table

一、创建表（表的基本操作）
案例一：

语法：
create table 表名(自定义)(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集];

注意：
在同一张表中，字段名不能相同
宽度和约束条件可选
字段名和类型是必须的

mysql> create database school;			#创建一个名为school的数据库.
Query OK, 1 row affected (0.00 sec)

mysql> use school;						#进入到这个school库.
Database changed
mysql> create table student1(			#在这个scool的库里面创建一个student1的表.
    -> id int,							#表里要有字段,id这个字段类型为int整数型类型.
    -> name varchar(50),				#name这个字段类型为varchar字符串类型.
    -> sex enum('m','f'),				#sex这个字段类型为enum枚举类型.
    -> age int							#age这个字段类型为int整数型类型.
    -> );								#结束.
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
+------------------+
1 row in set (0.00 sec)

mysql> desc student1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#注意,查看表只能先进到库里面才能看到.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| tnt                |
+--------------------+
6 rows in set (0.00 sec)
#查看有哪些数据库.

向表中插入内容
语法：
insert into 表名(字段1,字段2…) values(字段值列表…);
mysql> insert into student1(id,name,sex,age) values(1,‘xingdia’,‘m’,‘26’);

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
+------------------+
1 row in set (0.00 sec）
#先看一下你准备插入值的表格.主要看你想往哪插.记性好的忽略.（当前所在库的表）

mysql> insert into student1(id,name,sex,age) values(1,'TNT','m','18')；
Query OK, 1 row affected (0.00 sec)
#往student1的表里插入一些字段,id,name,sex,age.值为,1,TNT,m,18

mysql> select * from student1;
+------+------+------+------+
| id   | name | sex  | age  |
+------+------+------+------+
|    1 | TNT  | m    |   18 |
+------+------+------+------+
1 row in set (0.00 sec)
#然后查看这个表的所有内容.

mysql> select * from school.student1;
+------+------+------+------+
| id   | name | sex  | age  |
+------+------+------+------+
|    1 | TNT  | m    |   18 |
+------+------+------+------+
1 row in set (0.00 sec)
#如果不在student1这个表里面的话,可以用这种库+表的方法查.

mysql> desc student1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#desc查看表结构

mysql> select id,name from student1;
+------+------+
| id   | name |
+------+------+
|    1 | TNT  |
+------+------+
1 row in set (0.00 sec)
#也可以单独查看某个表的某个字段的数据.要用逗号隔开.

mysql> insert into student1 values(2,'TNT2','f',20),(3,'TNT3','f',30);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
#如果你想在这张表里面插入更多的数据,可以直接赋值,但是要按照表格的字段顺序来添加.
#insert into student1 values(2,'TNT2','f',20) #这是插入1条.加个逗号隔开可以继续添加第二条.

mysql> select * from student1;
+------+------+------+------+
| id   | name | sex  | age  |
+------+------+------+------+
|    1 | TNT  | m    |   18 |
|    2 | TNT2 | f    |   20 |
|    3 | TNT3 | f    |   30 |
+------+------+------+------+
#查看刚才插入的两条数值.

案例二：

mysql> create table student2( id int, name varchar(50), born_year year, birthday date, class_time time, reg_time datetime );
Query OK, 0 rows affected (0.00 sec)
#创建一个表2,并且把字段的数据类型也定义好.

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
| student2         |
+------------------+
2 rows in set (0.00 sec)
#查看创建成功.

mysql> desc student2;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(50) | YES  |     | NULL    |       |
| born_year  | year(4)     | YES  |     | NULL    |       |
| birthday   | date        | YES  |     | NULL    |       |
| class_time | time        | YES  |     | NULL    |       |
| reg_time   | datetime    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
#查看表结构.

mysql> insert into student2 values(1,'tnt',now(),now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)
#往表2里插入一条数据,按表2字段上往下的顺序插入,id为1,名字为2,其它四行now表示当前时间.并且这个时间会根据定义的数据类型取值.

mysql> select * from student2;
+------+------+-----------+------------+------------+---------------------+
| id   | name | born_year | birthday   | class_time | reg_time            |
+------+------+-----------+------------+------------+---------------------+
|    1 | tnt  |      2020 | 2020-05-01 | 23:10:28   | 2020-05-01 23:10:28 |
+------+------+-----------+------------+------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into student2 values(2,'tnt2',1982,19821120,123000,20140415162545);
Query OK, 1 row affected (0.00 sec)
#也可以用这种方式插入数值.

mysql> select * from student2;
+------+------+-----------+------------+------------+---------------------+
| id   | name | born_year | birthday   | class_time | reg_time            |
+------+------+-----------+------------+------------+---------------------+
|    1 | tnt  |      2020 | 2020-05-01 | 23:10:28   | 2020-05-01 23:10:28 |
|    2 | tnt2 |      1982 | 1982-11-20 | 12:30:00   | 2014-04-15 16:25:45 |
+------+------+-----------+------------+------------+---------------------+
2 rows in set (0.00 sec)

案例三：

mysql> create table student3( id int, name varchar(50), sex enum('male','female'), hobby set('music','book','game','disc') );
Query OK, 0 rows affected (0.00 sec)
#建立表3.指定字段和字段的数据类型.
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
| student2         |
| student3         |
+------------------+
3 rows in set (0.00 sec)

mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| id    | int(11)                           | YES  |     | NULL    |       |
| name  | varchar(50)                       | YES  |     | NULL    |       |
| sex   | enum('male','female')             | YES  |     | NULL    |       |
| hobby | set('music','book','game','disc') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into student3 values('1','tnt3','male','music');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student3 values('2','tnt4','male','music,game');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student3 values('3','tnt4','male,female','music');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

#这里特殊的是set的数据类型也是和枚举有点像,先定义好一些数值,然后只能填写预定义的数值,但枚举类型只能填1个,set类型可以同时填多个.

mysql> select * from student3;
+------+------+------+------------+
| id   | name | sex  | hobby      |
+------+------+------+------------+
|    1 | tnt3 | male | music      |
|    2 | tnt4 | male | music,game |
+------+------+------+------------+
2 rows in set (0.00 sec)

二：查看表结构
DESCRIBE查看表结构
DESCRIBE 表名;
DESC 表名;

SHOW CREATE TABLE查看表详细结构
SHOW CREATE TABLE 表名;
show create table 表名\G #如果表的数据比较大,用\G,否则看起来比较乱.

三、表完整性约束 作用：用于保证数据的完整性和一致性
约束条件与说明：
PRIMARY KEY (PK)
标识该字段为该表的主键，可以唯一的标识记录，不可以为空

FOREIGN KEY (FK)
标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联

NOT NULL
标识该字段不能为空

UNIQUE KEY (UK)
标识该字段的值是唯一的，可以为空，一个表中可以有多个

UNIQUE KEY AUTO_INCREMENT
标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT
为该字段设置默认值

说明：

1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
   sex enum(‘male’,‘female’) not null default ‘male’
   age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
   主键 primary key 外键 forengn key

案例四：

mysql>  create table school.student4( id int not null, name varchar(50) not null, sex enum('m','f') default 'm' not null, age int unsigned default 18 not null, hobby set('music','disc','dance','book') default 'book,dance' );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
| student2         |
| student3         |
| student4         |
+------------------+
4 rows in set (0.00 sec)
#创建表4

mysql> desc student4;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| id    | int(11)                            | NO   |     | NULL       |       |
| name  | varchar(50)                        | NO   |     | NULL       |       |
| sex   | enum('m','f')                      | NO   |     | m          |       |
| age   | int(10) unsigned                   | NO   |     | 18         |       |
| hobby | set('music','disc','dance','book') | YES  |     | dance,book |       |
+-------+------------------------------------+------+-----+------------+-------+
5 rows in set (0.00 sec)
#看一下表4的结构.

mysql> insert into student4 values(1,'lixiaolong','m',18,'music')；
Query OK, 1 row affected (0.00 sec)
#插入第一条数据.从上往下按顺序插入.

mysql> insert into student4(id,name) values(1,'tnt');
Query OK, 1 row affected (0.00 sec)
#插入第二条数据,然后只对字段id和name插入数值.


mysql> select * from student4;
+----+------------+-----+-----+------------+
| id | name       | sex | age | hobby      |
+----+------------+-----+-----+------------+
|  1 | lixiaolong | m   |  18 | music      |
|  1 | tnt        | m   |  18 | dance,book |
+----+------------+-----+-----+------------+
2 rows in set (0.00 sec)
#然后查看表4的内容发现刚才我们只对id和name字段插入数值,但sex和age和hobby也同样出现了数值,是为什么呢.

mysql> desc student4;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| id    | int(11)                            | NO   |     | NULL       |       |
| name  | varchar(50)                        | NO   |     | NULL       |       |
| sex   | enum('m','f')                      | NO   |     | m          |       |
| age   | int(10) unsigned                   | NO   |     | 18         |       |
| hobby | set('music','disc','dance','book') | YES  |     | dance,book |       |
+-------+------------------------------------+------+-----+------------+-------+
5 rows in set (0.00 sec)
#然后查看表4的结构会发现之前建表4的时候已经对其他字段设定了Default默认的数值.所以刚才插入数值时候不填数值也会出现默认的数值.

mysql> insert into student4 values(3,NULL,'m',40,'book');
ERROR 1048 (23000): Column 'name' cannot be null
#然后对表4插入第三条数值,然后其中一条name字段的值为空NULL,但会报错,因为同样是在建表4的时候已经设定了字段的Null值能不能为空.name的Null不能为空.所以报错

设置唯一约束 UNIQUE
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
一张表并且数据量少的时候看不出什么效果,比如电商或者表数据量大的时候,效果就会有了.

案例五：

mysql> create database company;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE company.department1 ( dept_id INT, dept_name VARCHAR(30) UNIQUE, comment VARCHAR(50) ); 
Query OK, 0 rows affected (0.00 sec)

mysql> desc company.department1
    -> ;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(30) | YES  | UNI | NULL    |       |
| comment   | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

设置主键约束 PRIMARY KEY
primary key 字段的值是不允许重复，且不允许不NULL（UNIQUE + NOT NULL） 单列做主键
案例六：

mysql> create table student6( id int primary key not null auto_increment, name varchar(50) not null, sex enum('male','female') not null default 'male', age int not null default 18 );
Query OK, 0 rows affected (0.00 sec)
#先创建表6.并对字段设定约束性的属性.

mysql> desc student6;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)           | NO   |     | NULL    |                |
| sex   | enum('male','female') | NO   |     | male    |                |
| age   | int(11)               | NO   |     | 18      |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
#查看表6的各种属性.

mysql> insert into student6 values (1,'alice','female',22);
Query OK, 1 row affected (0.00 sec)
#正常对表6所有字段都插入一个值.

mysql> select * from student6;
+----+-------+--------+-----+
| id | name  | sex    | age |
+----+-------+--------+-----+
|  1 | alice | female |  22 |
+----+-------+--------+-----+
1 row in set (0.00 sec)
#查看表6的数据.

mysql> insert into student6(name) values('TNT');
Query OK, 1 row affected (0.00 sec)
#然后再对表6的name字段插入一个值.只写一个名字.id并没有插值.然后sex和age为空的话都会有默认的值,我们主要看id的变化.

mysql> select * from student6;
+----+-------+--------+-----+
| id | name  | sex    | age |
+----+-------+--------+-----+
|  1 | alice | female |  22 |
|  2 | TNT   | male   |  18 |
+----+-------+--------+-----+
2 rows in set (0.00 sec)
#查看表结构,然后看到第二条值的Id也有了,因为在id字段里有一个约束性属性auto_increment自增.所以会自动填写.

设置字段值增 AUTO_INCREMENT
表company.department3
CREATE TABLE department3 (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(30),
comment VARCHAR(50)
);

修改表ALTER TABLE

语法：
1. 修改表名
ALTER TABLE 表名 RENAME 新表名;

2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;

3. 删除字段
ALTER TABLE 表名 DROP 字段名;

4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
| student2         |
| student3         |
| student4         |
| student6         |
+------------------+
5 rows in set (0.00 sec)

mysql> alter table student1 rename t1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student2         |
| student3         |
| student4         |
| student6         |
| t1               |
+------------------+
5 rows in set (0.00 sec)

#修改表名.

mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table t1 add game varchar(40) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
#给t1新增一个game字段,并且设定了约束性条件.
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


mysql> alter table t1 add games varchar(40) not null,add class varchar(90) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
#如果同时增加两个字段的话,设定完第一个字段后面加个逗号,继续add新字段.

mysql> alter table t1 add TNT varchar(40) not null after id;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
#如果想在已有的字段后面新加一个字段,那么add完之后after你想插入的字段名;
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| TNT   | varchar(40)   | NO   |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
| games | varchar(40)   | NO   |     | NULL    |       |
| class | varchar(90)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)


mysql> alter table t1 drop TNT;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
#删除某个字段,用drop+字段.
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
| games | varchar(40)   | NO   |     | NULL    |       |
| class | varchar(90)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)


mysql> alter table t1 modify id char;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#这里是把id字段的数据类型从int改成了char.
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | char(1)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
| games | varchar(40)   | NO   |     | NULL    |       |
| class | varchar(90)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)


mysql> alter table t1 change id idd char;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
#这里是把id改成了idd,数据类型保持不变.
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| idd   | char(1)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
| games | varchar(40)   | NO   |     | NULL    |       |
| class | varchar(90)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> alter table t1 change idd id int;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
#把idd字段名改为新字段名id并把数据类型也改为新的int.
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
| games | varchar(40)   | NO   |     | NULL    |       |
| class | varchar(90)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

例子：
1：
mysql> alter table service
-> engine=innodb;
//engine=myisam|memory|…

mysql> create table service(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> alter table service engine=innodb;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table service engine=myisam;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#修改不同类型的存储引擎.
mysql> show create table service\G
*************************** 1. row ***************************
       Table: service
Create Table: CREATE TABLE `service` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
#查看到默认引擎是MYISAM

2. 添加字段
   mysql> create table student10 (id int);
   mysql> alter table student10
   -> add name varchar(20) not null,
   -> add age int not null default 22;

mysql> alter table student10
-> add stu_num int not null after name; //添加name字段之后

mysql> alter table student10
add sex enum(‘male’,‘female’) default ‘male’ first; //添加到最前面

mysql> create table student10 (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int not null default 22;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student10;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int(11)     | NO   |     | 22      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table student10 add stu_num int not null after name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student10;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | NO   |     | NULL    |       |
| stu_num | int(11)     | NO   |     | NULL    |       |
| age     | int(11)     | NO   |     | 22      |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student10
    -> add sex enum('male','female')default 'male' first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student10;
+---------+-----------------------+------+-----+---------+-------+
| Field   | Type                  | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| sex     | enum('male','female') | YES  |     | male    |       |
| id      | int(11)               | YES  |     | NULL    |       |
| name    | varchar(20)           | NO   |     | NULL    |       |
| stu_num | int(11)               | NO   |     | NULL    |       |
| age     | int(11)               | NO   |     | 22      |       |
+---------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3. 删除字段 mysql> alter table student10
   -> drop sex;
   mysql> alter table service
   -> drop mac;

4. 修改字段类型modify
   mysql> alter table student10
   -> modify age tinyint not null ; //注意保留原有的约束条件
   mysql> alter table student10
   -> modify id int not null primary key ; //修改字段类型、约束、主键

5. 增加约束（针对已有的主键增加auto_increment）
   mysql> alter table student10 modify id int not null primary key auto_increment;
   ERROR 1068 (42000): Multiple primary key defined
   //错误，该字段已 经是primary key
   mysql> alter table student10 modify id int not null auto_increment;
   Query OK, 0 rows affected (0.01 sec)
   Records: 0 Duplicates: 0 Warnings: 0

6. 增加主键 mysql> alter table student1
   -> add primary key(id);

7. 增加主键和自动增长
   mysql> alter table student1
   -> modify id int not null primary key auto_increment;

8. 删除主键[primary key auto_increment]
   a. 删除自增约束
   mysql> alter table student10 modify id int not null;
   b. 删除主键
   mysql> alter table student10
   -> drop primary key;

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
复制表结构/记录+表结构，不会将Key复制
mysql> create table new_service select * from service;

mysql> create table t2 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
#创建一个t2的表,复制选择来自t1的所有数据.这里记成两条命令.并且这里是只复制表的结构和记录数据.key不会被复制

mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
| games | varchar(40)   | NO   |     | NULL    |       |
| class | varchar(90)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> desc t2;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
| games | varchar(40)   | NO   |     | NULL    |       |
| class | varchar(90)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> select * from t2\G;
*************************** 1. row ***************************
   id: 1
 name: TNT
  sex: m
  age: 18
 game: 
games: 
class: 
*************************** 2. row ***************************
   id: 2
 name: TNT2
  sex: f
  age: 20
 game: 
games: 
class: 
*************************** 3. row ***************************
   id: 3
 name: TNT3
  sex: f
  age: 30
 game: 
games: 
class: 
3 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from t1\G;
*************************** 1. row ***************************
   id: 1
 name: TNT
  sex: m
  age: 18
 game: 
games: 
class: 
*************************** 2. row ***************************
   id: 2
 name: TNT2
  sex: f
  age: 20
 game: 
games: 
class: 
*************************** 3. row ***************************
   id: 3
 name: TNT3
  sex: f
  age: 30
 game: 
games: 
class: 
3 rows in set (0.00 sec)

ERROR: 
No query specified

只复制表结构
mysql> create table new1_service select * from service where 1=2;
这里加入一个条件判断where 1=2 ，因为1永远不会等于2，所以判断为假，所以不会有任何数据,把空的值赋给create table ,所以会创建并复制表的结构.
//条件为假，查不到任何记录

mysql> create table t3 select * from t1 where 1=2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
| games | varchar(40)   | NO   |     | NULL    |       |
| class | varchar(90)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> desc t3;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| game  | varchar(40)   | NO   |     | NULL    |       |
| games | varchar(40)   | NO   |     | NULL    |       |
| class | varchar(90)   | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> select * from t1;
+------+------+------+------+------+-------+-------+
| id   | name | sex  | age  | game | games | class |
+------+------+------+------+------+-------+-------+
|    1 | TNT  | m    |   18 |      |       |       |
|    2 | TNT2 | f    |   20 |      |       |       |
|    3 | TNT3 | f    |   30 |      |       |       |
+------+------+------+------+------+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from t3;
Empty set (0.00 sec)

可以复制主键,只复制表结构
mysql> create table t4 like employees;

mysql> create table t5 like student6;
Query OK, 0 rows affected (0.00 sec)
#只复制表结构和主键.没有数据.

mysql> desc student6;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)           | NO   |     | NULL    |                |
| sex   | enum('male','female') | NO   |     | male    |                |
| age   | int(11)               | NO   |     | 18      |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc t5;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)           | NO   |     | NULL    |                |
| sex   | enum('male','female') | NO   |     | male    |                |
| age   | int(11)               | NO   |     | 18      |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from student6;
+----+-------+--------+-----+
| id | name  | sex    | age |
+----+-------+--------+-----+
|  1 | alice | female |  22 |
|  2 | TNT   | male   |  18 |
+----+-------+--------+-----+
2 rows in set (0.00 sec)

mysql> select * from t5;
Empty set (0.00 sec)

删除表

DROP TABLE 表名;

ysql> drop table t5;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| service          |
| student10        |
| student2         |
| student3         |
| student4         |
| student6         |
| t1               |
| t2               |
| t3               |
| t4               |
+------------------+
10 rows in set (0.00 sec)

三：库操作

#默认的四个库.
information_schema ：虚拟库,存储系统中数据库的对象信息,用户的表,列,权限,字符信息都存放在这.类似汉语字段,能够写出来的字都在这个字典上,对于它来讲,数据库里面的所有表和所有数据,都会在这个库里面存储,我们可以通过这个库来查询到我们现有的表,库,字段,等等数据信息.

performance_schema：主要存储数据库服务器的性能参数

mysql：授权库，主要存储系统用户的权限信息

sys：主要存储数据库服务器的性能参数

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

创建数据库：DDL

1. #mysqladmin -u root -p1 create db1
2. 直接去创建数据库目录并且修改权限
3. mysql> create database xingdian;

[root@localhost ~]# mysql -uroot -p123
mysql> create database db1;
#进入数据库创建.

[root@localhost ~]# mysql -u root -pTNTnb@123 -e "create database db2";
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysqladmin -u root -pTNTnb@123 create db5;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
#在命令行直接创建,适用于脚本,非交互式创建.

数据库命名规则：
区分大小写
唯一性
不能使用关键字 如 create select
不能单独使用数字

查看数据库
mysql> show databases;
mysql> show create database TNT;
mysql> select database(); 查看当前所在的库

切换数据库
mysql> use TNT;
mysql> show tables;

删除数据库
DROP DATABASE 数据库名;

扩展：

数据库的初始化，相当于重装数据库。(yum安装的数据库)
比如突然忘记数据库密码,怎么办.
systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# ss -antpl
State       Recv-Q Send-Q        Local Address:Port                       Peer Address:Port              
LISTEN      0      128                       *:22                                    *:*                   users:(("sshd",pid=988,fd=3))
LISTEN      0      100               127.0.0.1:25                                    *:*                   users:(("master",pid=1129,fd=13))
LISTEN      0      128                      :::22                                   :::*                   users:(("sshd",pid=988,fd=4))
LISTEN      0      100                     ::1:25                                   :::*                   users:(("master",pid=1129,fd=14))
[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# cat /var/log/mysqld.log |grep password
2020-04-28T17:10:18.010722Z 1 [Note] A temporary password is generated for root@localhost: &e#:EJxix0EL
2020-04-28T17:57:52.943668Z 0 [Note] Shutting down plugin 'validate_password'
2020-04-28T17:57:54.750645Z 0 [Note] Shutting down plugin 'sha256_password'
2020-04-28T17:57:54.750649Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-04-29T18:00:53.383770Z 0 [Note] Shutting down plugin 'validate_password'
2020-04-29T18:00:55.126167Z 0 [Note] Shutting down plugin 'sha256_password'
2020-04-29T18:00:55.126169Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-05-01T18:14:18.391489Z 0 [Note] Shutting down plugin 'validate_password'
2020-05-01T18:14:19.642139Z 0 [Note] Shutting down plugin 'sha256_password'
2020-05-01T18:14:19.642141Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-05-03T17:04:09.657386Z 0 [Note] Shutting down plugin 'validate_password'
2020-05-03T17:04:10.978406Z 0 [Note] Shutting down plugin 'sha256_password'
2020-05-03T17:04:10.978411Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-05-03T17:04:33.625631Z 1 [Note] A temporary password is generated for root@localhost: lf)!#siga2:Y
2020-05-03T17:06:49.063128Z 2 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2020-05-03T17:07:06.150848Z 3 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2020-05-03T17:07:23.593038Z 0 [Note] Shutting down plugin 'validate_password'
2020-05-03T17:07:25.209944Z 0 [Note] Shutting down plugin 'sha256_password'
2020-05-03T17:07:25.209945Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-05-03T17:07:42.321429Z 1 [Note] A temporary password is generated for root@localhost: FD9&Eso3Tpvy
[root@localhost ~]# mysqladmin -u root -p'FD9&Eso3Tpvy' password 'xxxxx'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost ~]# mysql -u root -pxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)