MySQL-NoSQL整体笔记---持续输出中

MySQL部分

一、搭建 MySQL 数据库服务器

1、下载并上传glibc版本的Mysql

2、新建用户以安全方式运行进程

[root@template ~]# groupadd -r -g 306 mysql
[root@template ~]# useradd -g 306 -r -u 306 mysql

3、安装并初始化mysql

[root@template ~]# tar xf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/
[root@template ~]# cd /usr/local/
[root@template local]# ln -s mysql-5.7.36-linux-glibc2.12-x86_64/ mysql
[root@template local]# chown -R mysql.mysql mysql/*
[root@template local]# /usr/local/mysql/bin/mysqld  --initialize --user=mysql --datadir=/usr/local/mysql/data

记录初始密码：
Zz6_k2hkWAir

## 4、修改mysql提供主配置文件

vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

[mysqld_safe]
log-error=mysql.log
pid-file=mysql.pid

## 5、为mysql提供sysv服务脚本

[root@template local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@template local]# chkconfig --add mysqld
[root@template local]# chkconfig mysqld on

6、启动服务

[root@template local]# systemctl restart mysqld

7、配置环境变量

[root@template local]# cat  > /etc/profile.d/mysql.sh  << EOF
export PATH=/usr/local/mysql/bin:$PATH
EOF
[root@template local]# source /etc/profile.d/mysql.sh

8、修改密码

[root@template local]# mysqladmin -uroot -p'Zz6_k2hkWAir' password '123456'
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.

二、数据库基础

1、SQL语句的分类

2、MySQL基本操作

mysql> create database bbsdb;
Query OK, 1 row affected (0.01 sec)

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

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

mysql> drop database BBSDB;
Query OK, 0 rows affected (0.00 sec)

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

mysql> use bbsdb;
Database changed

mysql> select database();
+------------+
| database() |
+------------+
| bbsdb      |
+------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> create table bbsdb.user(name char(10),age int,homedir char(10));
Query OK, 0 rows affected (0.01 sec)

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

mysql> desc user;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name    | char(10) | YES  |     | NULL    |       |
| age     | int(11)  | YES  |     | NULL    |       |
| homedir | char(10) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.04 sec)

mysql> insert into bbsdb.user values("bob",19,"USA");
Query OK, 1 row affected (0.00 sec)
mysql> insert into bbsdb.user values("CHAN",18,"SC");
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+------+------+---------+
| name | age  | homedir |
+------+------+---------+
| bob  |   19 | USA     |
| CHAN |   18 | SC      |
+------+------+---------+
2 rows in set (0.00 sec)

mysql> select name  from user;
+------+
| name |
+------+
| bob  |
| CHAN |
+------+
2 rows in set (0.00 sec)

mysql> select name ,age from user;
+------+------+
| name | age  |
+------+------+
| bob  |   19 |
| CHAN |   18 |
+------+------+
2 rows in set (0.00 sec)

mysql>

mysql> update user set age=21 where name= "CHAN";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+------+------+---------+
| name | age  | homedir |
+------+------+---------+
| bob  |   19 | USA     |
| CHAN |   21 | SC      |
+------+------+---------+
2 rows in set (0.00 sec)

mysql> update user set homedir="CHINA";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from user;
+------+------+---------+
| name | age  | homedir |
+------+------+---------+
| bob  |   19 | CHINA   |
| CHAN |   21 | CHINA   |
+------+------+---------+
2 rows in set (0.00 sec)

mysql> alter table user drop column name;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> select * from user;
+------+---------+
| age  | homedir |
+------+---------+
|   19 | CHINA   |
|   21 | CHINA   |
+------+---------+
2 rows in set (0.00 sec)

mysql> delete from user where age = 19;
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+------+---------+
| age  | homedir |
+------+---------+
|   21 | CHINA   |
+------+---------+
1 row in set (0.00 sec)

mysql> delete from user;
Query OK, 1 row affected (0.00 sec)

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

三、数据库类型

1、信息种类

字符类型

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


mysql> use bbs;
Database changed


mysql> create table t1(name char(5),email varchar(15));
Query OK, 0 rows affected (0.00 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | char(5)     | YES  |     | NULL    |       |
| email | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table t2(name char,email varchar(3));
Query OK, 0 rows affected (0.00 sec)

mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | char(1)    | YES  |     | NULL    |       |
| email | varchar(3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> # #char类型不指定存储几个字符，默认存储一个
mysql> insert into t2 values("a","a");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values("aaa","aaa");
ERROR 1406 (22001): Data too long for column 'name' at row 1

表中插入中文字符
mysql> show create table bbs.t1 \G;		
#查看建表命令，\G 竖着来显示数据 ###默认表的字符集CHARSET为latin1，不能在表中插入中文

mysql> create table 学生表(姓名 char(15),地址 varchar(50)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> desc 学生表;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| 姓名   | char(15)    | YES  |     | NULL    |       |
| 地址   | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into 学生表 values("张三丰","武当山");
Query OK, 1 row affected (0.00 sec)

2、数值类型

mysql> create  table t3(name char(15),age tinyint unsigned,level tinyint);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t3 values("bob",18,6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values("tom",-18,-6);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> insert into t3 values("tom",0,-6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values("GGbong",0,-127);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values("GG",0,-129);
ERROR 1264 (22003): Out of range value for column 'level' at row 1
mysql>
mysql> insert into t3 values("jim",21.5,9);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+--------+------+-------+
| name   | age  | level |
+--------+------+-------+
| bob    |   18 |     6 |
| tom    |    0 |    -6 |
| GGbong |    0 |  -127 |
| jim    |   22 |     9 |
+--------+------+-------+
4 rows in set (0.00 sec)

存小数，四舍五入问题

mysql> insert into t3 values("jim",21.5,9);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+--------+------+-------+
| name   | age  | level |
+--------+------+-------+
| bob    |   18 |     6 |
| tom    |    0 |    -6 |
| GGbong |    0 |  -127 |
| jim    |   22 |     9 |
+--------+------+-------+
4 rows in set (0.00 sec)

mysql> insert into t3 values("jim",21.3,9);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+--------+------+-------+
| name   | age  | level |
+--------+------+-------+
| bob    |   18 |     6 |
| tom    |    0 |    -6 |
| GGbong |    0 |  -127 |
| jim    |   22 |     9 |
| jim    |   21 |     9 |
+--------+------+-------+
5 rows in set (0.00 sec)

浮点数

float(7,2) # 7指整个浮点数的最大位数，2指7位数字中有两位是小数位, 则取值范围为：-99999.99 ~ 99999.99

float(5,3) #5指整个浮点数的最大位数，3指5位数字中有三位是小数位, 则取值范围为：-99.999 ~ 99.999

float(数字1,数字2)

数字1：总的位数
数字2：小数位的个数

mysql> create table t4 (name char(5),pay float(5,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t4 values("john",1000.88);
ERROR 1264 (22003): Out of range value for column 'pay' at row 1
mysql> insert into t4 values("john",999.88);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values("jim",-999.99);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+------+---------+
| name | pay     |
+------+---------+
| john |  999.88 |
| jim  | -999.99 |
+------+---------+
2 rows in set (0.00 sec)

mysql> insert into t4 values("jim",9);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+------+---------+
| name | pay     |
+------+---------+
| john |  999.88 |
| jim  | -999.99 |
| jim  |    9.00 |
+------+---------+
3 rows in set (0.00 sec)

3、日期时间类型

mysql> # 创建与日期时间相关的表，指定名称，年份，上课时间，生日，聚会时间
mysql> create table t5(name char(15),s_year year ,uptime time ,birthday date,party datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t5 values("bob",2002,083000,20231120,20230214183000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+------+--------+----------+------------+---------------------+
| name | s_year | uptime   | birthday   | party               |
+------+--------+----------+------------+---------------------+
| bob  |   2002 | 08:30:00 | 2023-11-20 | 2023-02-14 18:30:00 |
+------+--------+----------+------------+---------------------+
1 row in set (0.00 sec)

mysql>

mysql> select curtime();			#获取当前的系统时间
mysql> select curdate();			#获取当前的系统日期
mysql> select now();			#获取当前的系统日期和系统时间
mysql> select year(now());		#从当前系统时间中只取出年份
mysql> select month(now());		#从当前系统时间中只取出月份
mysql> select day(now());		#从当前系统时间中只取出天数
mysql> select date(now());		#从当前系统时间中只取出年月日
mysql> select time(now());		#从当前系统时间中只取出时分秒

根据时间函数在t5表中插入一条数据

mysql>  insert into t5 values("tom",2000,time(now()),curdate(),now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+------+--------+----------+------------+---------------------+
| name | s_year | uptime   | birthday   | party               |
+------+--------+----------+------------+---------------------+
| bob  |   2002 | 08:30:00 | 2023-11-20 | 2023-02-14 18:30:00 |
| tom  |   2000 | 20:08:11 | 2023-08-06 | 2023-08-06 20:08:11 |
+------+--------+----------+------------+---------------------+
2 rows in set (0.00 sec)

关于日期时间字段：当未给timestamp字段赋值时，自动以当前系统时间赋值，而datetime值为NULL（空）
创建t6表，指定姓名，约会时间，聚会时间，验证timestamp和datetime的区别

mysql> create table t6(name char(10), meetting datetime, party timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t6 values("zs", now(), now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+------+---------------------+---------------------+
| name | meetting            | party               |
+------+---------------------+---------------------+
| zs   | 2023-08-06 20:09:52 | 2023-08-06 20:09:52 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> # t6表中重新插入一条数据，只插入name和metting字段的值，party字段采用默认值
mysql> insert into t6(name,party) values("john", 19731001223000 );
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+------+---------------------+---------------------+
| name | meetting            | party               |
+------+---------------------+---------------------+
| zs   | 2023-08-06 20:09:52 | 2023-08-06 20:09:52 |
| john | NULL                | 1973-10-01 22:30:00 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> # t6表中重新插入一条数据，只插入name和metting字段的值，party字段采用默认值
mysql> insert into t6(name,meetting) values("bob", 20231120224058);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+------+---------------------+---------------------+
| name | meetting            | party               |
+------+---------------------+---------------------+
| zs   | 2023-08-06 20:09:52 | 2023-08-06 20:09:52 |
| john | NULL                | 1973-10-01 22:30:00 |
| bob  | 2023-11-20 22:40:58 | 2023-08-06 20:12:45 |
+------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> # t6表中重新插入一条数据，只插入name和party字段的值，meetting字段采用默认值
mysql> insert into t6(name,party) values("john", 19731001223000 );
Query OK, 1 row affected (0.01 sec)

mysql> select * from t6;
+------+---------------------+---------------------+
| name | meetting            | party               |
+------+---------------------+---------------------+
| zs   | 2023-08-06 20:09:52 | 2023-08-06 20:09:52 |
| john | NULL                | 1973-10-01 22:30:00 |
| bob  | 2023-11-20 22:40:58 | 2023-08-06 20:12:45 |
| john | NULL                | 1973-10-01 22:30:00 |
+------+---------------------+---------------------+
4 rows in set (0.00 sec)

4、year类型

要求使用4位赋值
当使用2位数赋值时：01-99
01 ~ 69 视为 2001 ~ 2069
70 ~ 99 视为 1970 ~ 1999

插入数据，只给t5表中的s_year字段赋值，其他默认或者为NULL

mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| 学生表        |
| t1            |
| t2            |
| t3            |
| t4            |
| t5            |
| t6            |
+---------------+
7 rows in set (0.00 sec)

mysql> desc t5;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(15) | YES  |     | NULL    |       |
| s_year   | year(4)  | YES  |     | NULL    |       |
| uptime   | time     | YES  |     | NULL    |       |
| birthday | date     | YES  |     | NULL    |       |
| party    | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select s_year from t5;
+--------+
| s_year |
+--------+
|   2002 |
|   2000 |
+--------+
2 rows in set (0.00 sec)

mysql> insert into t5(s_year) values(03),(81);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select s_year from t5;
+--------+
| s_year |
+--------+
|   2002 |
|   2000 |
|   2003 |
|   1981 |
+--------+
4 rows in set (0.00 sec)

5、枚举类型

字段的值不能自己输入，必须在设置的范围内选择(有单选和多选之分)
enum 单选
格式：字段名 enum（值1，值2，值N）
仅能在列表里选择一个值
set 多选
格式：字段名 set(值1，值2，值3)
在列表里选择一个或多个值

创建t7表，指定字段：姓名(name)，性别(sex)，爱好(likes)


mysql> create table t7(name char(15), sex enum("boy", "girl", "no"), likes set("eat", "money", "game", "music"));
Query OK, 0 rows affected (0.00 sec)

mysql> desc t7;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| name  | char(15)                          | YES  |     | NULL    |       |
| sex   | enum('boy','girl','no')           | YES  |     | NULL    |       |
| likes | set('eat','money','game','music') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>  insert into t7 values('bob','boy','eat,game,music');
Query OK, 1 row affected (0.01 sec)

mysql> desc t7;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| name  | char(15)                          | YES  |     | NULL    |       |
| sex   | enum('boy','girl','no')           | YES  |     | NULL    |       |
| likes | set('eat','money','game','music') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from t7;
+------+------+----------------+
| name | sex  | likes          |
+------+------+----------------+
| bob  | boy  | eat,game,music |
+------+------+----------------+
1 row in set (0.00 sec)

mysql> insert into t7 values('bob','man','girl,book');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> insert into t7 values('bob','no','girl,book');
ERROR 1265 (01000): Data truncated for column 'likes' at row 1

四、约束条件，修改表结构

1、约束条件

约束条件：限制字段赋值

mysql> desc t6;
+----------+-----------+------+-----+-------------------+-----------------------------+
| Field    | Type      | Null | Key | Default           | Extra                       |
+----------+-----------+------+-----+-------------------+-----------------------------+
| name     | char(10)  | YES  |     | NULL              |                             |
| meetting | datetime  | YES  |     | NULL              |                             |
| party    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------+-----------+------+-----+-------------------+-----------------------------+
Null   Key   Default   Extra    #这四列为约束条件
Null      #指是否允许为字段赋空值； 
 #YES，允许给字段赋空值，默认也是允许赋空值；
 #NO， 不允许给字段赋空值；
Default   #当不给字段赋值时，则使用默认值，初始默认值为NULL，可以修改
Extra     #额外的设置, 例如：可以设置学号为自动增长的

设置约束条件
null 允许为空（默认设置）
not null 不允许为null（空）
key 键值类型
default 设置默认值，缺省为NULL
extra 额外设置

建表时指定默认值不能为空，创建t8表,表字段包含：名字(name)，年龄(age)，班级(class)

mysql> create table t8(name char(10) not null, age tinyint unsigned default 19, class char(7) not null default "nsd2002");
Query OK, 0 rows affected (0.00 sec)

mysql> desc t8;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
| class | char(7)             | NO   |     | nsd2002 |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t8(name) values("john");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
+------+------+---------+
| name | age  | class   |
+------+------+---------+
| john |   19 | nsd2002 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> # 向t8表中插入数据，所有字段自己定义，可以不使用默认值
mysql> insert into t8 values("tom", 29, "nsd2003");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
+------+------+---------+
| name | age  | class   |
+------+------+---------+
| john |   19 | nsd2002 |
| tom  |   29 | nsd2003 |
+------+------+---------+
2 rows in set (0.00 sec)

验证null值和"null"值
#null 指的是没有任何的数据
#“null” 指的是有数据，但数据的内容为"null"

mysql> desc t8;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 19      |       |
| class | char(7)             | NO   |     | nsd2002 |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t8 values(null,null,null);
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into t8 values("null",null,null);
ERROR 1048 (23000): Column 'class' cannot be null
mysql> insert into t8 values("null",null,"");
Query OK, 1 row affected (0.01 sec)

mysql> select * from t8;
+------+------+---------+
| name | age  | class   |
+------+------+---------+
| john |   19 | nsd2002 |
| tom  |   29 | nsd2003 |
| null | NULL |         |
+------+------+---------+
3 rows in set (0.00 sec)

2、修改表结构

语法结构
用法： mysql> alter table 库名.表名执行动作;

添加新字段

mysql> desc t5;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(15) | YES  |     | NULL    |       |
| s_year   | year(4)  | YES  |     | NULL    |       |
| uptime   | time     | YES  |     | NULL    |       |
| birthday | date     | YES  |     | NULL    |       |
| party    | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table t5 add email varchar(30) not null default "[email protected]";
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t5;
+----------+-------------+------+-----+-------------+-------+
| Field    | Type        | Null | Key | Default     | Extra |
+----------+-------------+------+-----+-------------+-------+
| name     | char(15)    | YES  |     | NULL        |       |
| s_year   | year(4)     | YES  |     | NULL        |       |
| uptime   | time        | YES  |     | NULL        |       |
| birthday | date        | YES  |     | NULL        |       |
| party    | datetime    | YES  |     | NULL        |       |
| email    | varchar(30) | NO   |     | [email protected] |       |
+----------+-------------+------+-----+-------------+-------+
6 rows in set (0.00 sec)

mysql> select * from t5;
+------+--------+----------+------------+---------------------+-------------+
| name | s_year | uptime   | birthday   | party               | email       |
+------+--------+----------+------------+---------------------+-------------+
| bob  |   2002 | 08:30:00 | 2023-11-20 | 2023-02-14 18:30:00 | [email protected] |
| tom  |   2000 | 20:08:11 | 2023-08-06 | 2023-08-06 20:08:11 | [email protected] |
| NULL |   2003 | NULL     | NULL       | NULL                | [email protected] |
| NULL |   1981 | NULL     | NULL       | NULL                | [email protected] |
+------+--------+----------+------------+---------------------+-------------+
4 rows in set (0.00 sec)

在这里插入图片描述

mysql> alter table t5 add stu_id char(9) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t5;
+----------+-------------+------+-----+-------------+-------+
| Field    | Type        | Null | Key | Default     | Extra |
+----------+-------------+------+-----+-------------+-------+
| stu_id   | char(9)     | YES  |     | NULL        |       |
| name     | char(15)    | YES  |     | NULL        |       |
| s_year   | year(4)     | YES  |     | NULL        |       |
| uptime   | time        | YES  |     | NULL        |       |
| birthday | date        | YES  |     | NULL        |       |
| party    | datetime    | YES  |     | NULL        |       |
| email    | varchar(30) | NO   |     | [email protected] |       |
+----------+-------------+------+-----+-------------+-------+
7 rows in set (0.00 sec)

mysql> select * from t5;
+--------+------+--------+----------+------------+---------------------+-------------+
| stu_id | name | s_year | uptime   | birthday   | party               | email       |
+--------+------+--------+----------+------------+---------------------+-------------+
| NULL   | bob  |   2002 | 08:30:00 | 2023-11-20 | 2023-02-14 18:30:00 | [email protected] |
| NULL   | tom  |   2000 | 20:08:11 | 2023-08-06 | 2023-08-06 20:08:11 | [email protected] |
| NULL   | NULL |   2003 | NULL     | NULL       | NULL                | [email protected] |
| NULL   | NULL |   1981 | NULL     | NULL       | NULL                | [email protected] |
+--------+------+--------+----------+------------+---------------------+-------------+
4 rows in set (0.00 sec)

mysql>

在这里插入图片描述

mysql> alter table t5 add sex enum("boy", "girl") default "boy" after name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t5;
+----------+--------------------+------+-----+-------------+-------+
| Field    | Type               | Null | Key | Default     | Extra |
+----------+--------------------+------+-----+-------------+-------+
| stu_id   | char(9)            | YES  |     | NULL        |       |
| name     | char(15)           | YES  |     | NULL        |       |
| sex      | enum('boy','girl') | YES  |     | boy         |       |
| s_year   | year(4)            | YES  |     | NULL        |       |
| uptime   | time               | YES  |     | NULL        |       |
| birthday | date               | YES  |     | NULL        |       |
| party    | datetime           | YES  |     | NULL        |       |
| email    | varchar(30)        | NO   |     | [email protected] |       |
+----------+--------------------+------+-----+-------------+-------+
8 rows in set (0.00 sec)

mysql> select * from t5;
+--------+------+------+--------+----------+------------+---------------------+-------------+
| stu_id | name | sex  | s_year | uptime   | birthday   | party               | email       |
+--------+------+------+--------+----------+------------+---------------------+-------------+
| NULL   | bob  | boy  |   2002 | 08:30:00 | 2023-11-20 | 2023-02-14 18:30:00 | [email protected] |
| NULL   | tom  | boy  |   2000 | 20:08:11 | 2023-08-06 | 2023-08-06 20:08:11 | [email protected] |
| NULL   | NULL | boy  |   2003 | NULL     | NULL       | NULL                | [email protected] |
| NULL   | NULL | boy  |   1981 | NULL     | NULL       | NULL                | [email protected] |
+--------+------+------+--------+----------+------------+---------------------+-------------+
4 rows in set (0.00 sec)

在这里插入图片描述

3、修改字段类型

mysql> # 此案例针对sex字段操作，被修改的字段类型不能与表中已有数据冲突
mysql> desc t5;
+----------+--------------------+------+-----+-------------+-------+
| Field    | Type               | Null | Key | Default     | Extra |
+----------+--------------------+------+-----+-------------+-------+
| stu_id   | char(9)            | YES  |     | NULL        |       |
| name     | char(15)           | YES  |     | NULL        |       |
| sex      | enum('boy','girl') | YES  |     | boy         |       |
| s_year   | year(4)            | YES  |     | NULL        |       |
| uptime   | time               | YES  |     | NULL        |       |
| birthday | date               | YES  |     | NULL        |       |
| party    | datetime           | YES  |     | NULL        |       |
| email    | varchar(30)        | NO   |     | [email protected] |       |
+----------+--------------------+------+-----+-------------+-------+
8 rows in set (0.00 sec)

mysql> # 使用modify修改t5表的sex字段，设置默认值为man
mysql>
mysql> # #修改失败，字段里需要包含原表中的数据类型boy,否则冲突
mysql> alter table t5 modify sex enum("man", "woman") default "man";
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql>
mysql>
mysql> alter table t5 modify sex enum("man", "woman", "boy") default "man";
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> #修改成功，sex字段中存在和表中数据相同的类型'boy'
mysql>
mysql>
mysql> desc t5;
+----------+---------------------------+------+-----+-------------+-------+
| Field    | Type                      | Null | Key | Default     | Extra |
+----------+---------------------------+------+-----+-------------+-------+
| stu_id   | char(9)                   | YES  |     | NULL        |       |
| name     | char(15)                  | YES  |     | NULL        |       |
| sex      | enum('man','woman','boy') | YES  |     | man         |       |
| s_year   | year(4)                   | YES  |     | NULL        |       |
| uptime   | time                      | YES  |     | NULL        |       |
| birthday | date                      | YES  |     | NULL        |       |
| party    | datetime                  | YES  |     | NULL        |       |
| email    | varchar(30)               | NO   |     | [email protected] |       |
+----------+---------------------------+------+-----+-------------+-------+
8 rows in set (0.00 sec)

mysql>

在这里插入图片描述修改t5表中的name字段类型，修改为varchar(15)

案例实现字段值的位置调换
使用modify命令，将email字段移到sex字段的后面

在这里插入图片描述

4、删除字段

删除t5表中的多个字段(email和birthday)

mysql> alter table t5 drop email,drop birthday;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t5;
+--------+---------------------------+------+-----+---------+-------+
| Field  | Type                      | Null | Key | Default | Extra |
+--------+---------------------------+------+-----+---------+-------+
| name   | varchar(15)               | YES  |     | NULL    |       |
| sex    | enum('man','woman','boy') | YES  |     | man     |       |
| s_year | year(4)                   | YES  |     | NULL    |       |
| uptime | time                      | YES  |     | NULL    |       |
| party  | datetime                  | YES  |     | NULL    |       |
+--------+---------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

5、修改字段名

修改t5表中s_year的字段名，使用change命令将字段s_year的名字改为csny


mysql>  alter table t5 change s_year csny year;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t5;
+--------+---------------------------+------+-----+---------+-------+
| Field  | Type                      | Null | Key | Default | Extra |
+--------+---------------------------+------+-----+---------+-------+
| name   | varchar(15)               | YES  |     | NULL    |       |
| sex    | enum('man','woman','boy') | YES  |     | man     |       |
| csny   | year(4)                   | YES  |     | NULL    |       |
| uptime | time                      | YES  |     | NULL    |       |
| party  | datetime                  | YES  |     | NULL    |       |
+--------+---------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

6、修改表名

使用rename命令来修改t5表的表名为stuinfo

mysql> alter table t5 rename stuinfo;
Query OK, 0 rows affected (0.01 sec)

mysql> desc t5;
ERROR 1146 (42S02): Table 'bbs.t5' doesn't exist
mysql> desc stuinfo;
+--------+---------------------------+------+-----+---------+-------+
| Field  | Type                      | Null | Key | Default | Extra |
+--------+---------------------------+------+-----+---------+-------+
| name   | varchar(15)               | YES  |     | NULL    |       |
| sex    | enum('man','woman','boy') | YES  |     | man     |       |
| csny   | year(4)                   | YES  |     | NULL    |       |
| uptime | time                      | YES  |     | NULL    |       |
| party  | datetime                  | YES  |     | NULL    |       |
+--------+---------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| 学生表        |
| stuinfo       |
| t1            |
| t2            |
| t3            |
| t4            |
| t6            |
| t7            |
| t8            |
+---------------+
9 rows in set (0.00 sec)

五、普通索引

1、MySQL键值概述

键值类型

index、primary key、foreign key #生产环境一定会用到的键值类型

索引介绍
类似于书的目录
对表中字段值进行排序
索引算法：Btree、B+tree、hash
Btree算法(二叉树)：

#1》查找数字5时，先用数字5和数字4对比；
#2》当数字5大于数字4，则直接从数字4的右分支进行查找；
#3》接下来用要查找的数字5和数字6对比；
#4》当数字5小于数字6，则直接从数字6的左分支进行查找；
#5》按照以上的方式继续比对查找，直到查找到数据为止；
索引的优缺点
生产环境下，对数据查的请求远远高于对数据写的请求；

2、普通索引

创建索引
建表的时候创建索引：index(字段名), index(字段名)…

创建t9表时，将name字段和class字段设置为索引

mysql> create table t9(name char(10), class char(9), sex enum("m", "w"), index(name), index(class));
Query OK, 0 rows affected (0.00 sec)

mysql> desc t9;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name  | char(10)      | YES  | MUL | NULL    |       |
| class | char(9)       | YES  | MUL | NULL    |       |
| sex   | enum('m','w') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#约束条件Key变为MUL(索引的标志)
在已有的表里创建索引：create  index  索引名  on   表名(字段名);

在已有表t4表中为字段创建索引xxx(索引名称可以随便定义)

mysql> desc t4;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | char(5)    | YES  |     | NULL    |       |
| pay   | float(5,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create index xxx on t4(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t4;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | char(5)    | YES  | MUL | NULL    |       |
| pay   | float(5,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show   index  from   t4 \G;
*************************** 1. row ***************************
        Table: t4
   Non_unique: 1
     Key_name: xxx
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified

删除索引
drop index 索引名 on 表名;
删除t9表中的索引name

mysql> drop index xxx on t4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> drop index name on t9;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t9;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name  | char(10)      | YES  |     | NULL    |       |
| class | char(9)       | YES  | MUL | NULL    |       |
| sex   | enum('m','w') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show index from t9\G;
*************************** 1. row ***************************
        Table: t9
   Non_unique: 1
     Key_name: class
 Seq_in_index: 1
  Column_name: class
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified