DDL—数据定义语言(Create，Alter，Drop，DECLARE)
概述：DDL（data definition language）主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DML—数据操纵语言(Select，Delete，Update，Insert)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL*
DQL---数据查询语言(select)
1-DDL-数据定义语句（create,alter,drop,declare）
1-1create创建 /alter修改数据库-字符集
mysql> create database db character set = "utf8" # 创建字符集
-> ; #修改字符集（转语言时）-先做备份-字符集转换会出乱码
mysql> show databases; 每条SQL语句结束时要以;做为结束符.(除了use命令)
+--------------------+
| Database |
+--------------------+
| information_schema |
| db |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> alter database db character set="latin1"; # 修改字符集为 latin1
mysql> show create database db;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| db | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
mysql> drop database db; # 删除创建的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
1-2建立表，使用表
mysql> create database db; # 建立一个数据库
Query OK, 1 row affected (0.00 sec)
mysql> use db; # 先使用数据库
Database changed
1-3 数字类型
数字类型 --数值类型 unsigned
整数类型 字节 范围（有符号） 范围（无符号） 用途
TINYINT 1字节 (-128，127) (0，255) 小整数值
SMALLINT 2字节 (-32768，32767) (0，65535) 大整数值
MEDIUMINT 3字节 (-838 8608，838 8607) (0，1677 7215) 大整数值
INT或INTEGER 4字节 (-21 4748 3648，21 4748 3647) (0，42 9496 7295) 大整数值
BIGINT 8字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
浮点型：
FLOAT 4字节/7 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值 (7个有效位)
DOUBLE 8字节/15 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值 (15个有效位)
DECIMAL 不存在精度损失，常用于银行帐目计算。（28个有效位）
字符串类型
create table t1(id int(6),name char(50)); 定长
create table t1(id int(6),name varchar(50)); 变长
日期时间类型：date类型、time类型、year类型--年份的范围00-69为2000-2069&&70-99为1970-1999
datetime和timestamp类型
ENUM和SET类型
1.整数类型：smallint
mysql> create table t1(id smallint); # 建立表以及查询 （0-65535）
mysql> insert into t1 values(1);
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
mysql> insert into t1 values(32767);
mysql> select * from t1;
+-------+
| id |
+-------+
| 1 |
| 32767 |
+-------+
2.整数类型：tinyint unsigned --age
mysql> create table t2(age tinyint unsigned); # （创建的无符号类型 范围在 0-255）
mysql> insert into t2 values(255);
mysql> insert into t2 values(0);
mysql> insert into t2 values(-1);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
3.浮点类型：float
mysql> create table t3(sal float(7,2)); # 创建浮点型，7个有效位，保留2位小数
mysql> insert into t3 values(12345.56);
mysql> insert into t3 values(12345.567);
mysql> insert into t3 values(12345.561-0.005);
mysql> select * from t3;
+----------+
| sal |
+----------+
| 12345.56 |
| 12345.57 |
| 12345.56 |
+----------+
mysql> insert into t3 values(66666.89889);
mysql> select * from t3;
+----------+
| sal |
+----------+
| 12345.56 |
| 12345.57 |
| 12345.56 |
| 66666.90 |
+----------+
mysql> create table t4(sal float(10,2)); #
mysql> insert into t4 values(6666654.89889);
mysql> select * from t4;
+------------+
| sal |
+------------+
| 6666655.00 |
+------------+
4.select --sql_mode 数据的校验模式
mysql5.0以上版本支持三种sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。
ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
mysql> set @@sql_mode=ANSI; # 临时修改模式：可以插入数据，报warning.
mysql> insert into t2 values(500);
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1264 | Out of range value for column 'age' at row 1 |
+---------+------+----------------------------------------------+
mysql> select * from t2;
+------+
| age |
+------+
| 255 |
| 0 |
| 255 |
+------+
3 rows in set (0.00 sec)
mysql> set @@sql_mode=STRICT_TRANS_TABLES;
5.char / char(定长）
mysql> create table t5(name char(10));
mysql> insert into t5 values('abcdefghij');
mysql> select * from t5;
+------------+
| name |
+------------+
| abcdefghij |
+------------+
mysql> insert into t5 values('lmh');
mysql> select * from t5;
+------------+
| name |
+------------+
| abcdefghij |
| lmh |
+------------+
mysql> create table t7(id int(10),name char(10));
mysql> insert into t7 values(521,'hxj');
mysql> select * from t7;
+------+------+
| id | name |
+------+------+
| 521 | hxj |
+------+------+
6.修饰符---zerofill 用零补齐
mysql> create table t8(id int(5) zerofill,name char(10));
mysql> insert into t8 values(0815,'lsy');
mysql> select * from t8;
+-------+------+
| id | name |
+-------+------+
| 00815 | lsy |
+-------+------+
mysql> show tables; # 显示创建的表
+--------------+
| Tables_in_db |
+--------------+
| t1 |
| t2 |
··· |
| t8 |
+--------------+
7.date类型-time类型 /datetime / timestamp类型
mysql> create table t10(mydate date,mytime time);
mysql> insert into t10 values('2023-08-17','14:20:20');
mysql> insert into t10 values('2023/08/17','14:20:20');
mysql> insert into t10 values(20230817,142020);
mysql> select * from t10;
+------------+----------+
| mydate | mytime |
+------------+----------+
| 2023-08-17 | 14:20:20 |
| 2023-08-17 | 14:20:20 |
| 2023-08-17 | 14:20:20 |
+------------+----------+
mysql> insert into t10 values(20230817,1111);
mysql> insert into t10 values(20230817,'11:11');
mysql> select * from t10;
+------------+----------+
| mydate | mytime |
+------------+----------+
| 2023-08-17 | 14:20:20 |
| 2023-08-17 | 14:20:20 |
| 2023-08-17 | 14:20:20 |
| 2023-08-17 | 00:11:11 |
| 2023-08-17 | 11:11:00 |
+------------+----------+
mysql> create table t12(mydt datetime);
mysql> insert into t12 values(20230816101010);
mysql> insert into t12 values('2023-08-16 10:10:10');
mysql> select * from t12;
+---------------------+
| mydt |
+---------------------+
| 2023-08-16 10:10:10 |
| 2023-08-16 10:10:10 |
+---------------------+
mysql> create table t13(myts timestamp);
mysql> insert into t13 values('2023-08-16 10:10:10');
mysql> insert into t13 values(20230816101010);
mysql> select * from t13;
+---------------------+
| myts |
+---------------------+
| 2023-08-16 10:10:10 |
| 2023-08-16 10:10:10 |
+---------------------+
8.year类型 --年份的范围00-69为2000-2069&&70-99为1970-1999
mysql> create table t11(myyear year);
mysql> insert into t11 values('2023');
mysql> insert into t11 values(2023);
mysql> insert into t11 values(70);
mysql> insert into t11 values(60);
mysql> insert into t11 values(99);
mysql> select * from t11;
+--------+
| myyear |
+--------+
| 2023 |
| 2023 |
| 1970 |
| 2060 |
| 1999 |
+--------+
9.enum 类型
mysql> create table t15(name char(10),gender enum('M','F'));
mysql> insert into t15 values('xj','M');
mysql> insert into t15 values('mh','F');
mysql> select * from t15;
+------+--------+
| name | gender |
+------+--------+
| xj | M |
| mh | F |
+------+--------+
10.set类型
mysql> create table t16(name char(10),hobby set('a','b','c','d'));
mysql> insert into t16 values('nb','a,b,c');
mysql> select * from t16;
+------+-------+
| name | hobby |
+------+-------+
| nb | a,b,c |
+------+-------+
结合练习1-char-datetime-timestamp 区别
mysql> create table t14(name char(10),mydt datetime,myts timestamp);
mysql> desc t14;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| name | char(10) | YES | | NULL | |
| mydt | datetime | YES | | NULL | |
| myts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
mysql> insert into t14 values('hh',20230817101010,20230817101010);
mysql> insert into t14 values('hj',null,null);
mysql> select * from t14;
+------+---------------------+---------------------+
| name | mydt | myts |
+------+---------------------+---------------------+
| hh | 2023-08-17 10:10:10 | 2023-08-17 10:10:10 |
| hj | NULL | 2023-08-17 14:30:29 |
+------+---------------------+---------------------+
mysql> insert into t14 values('mh',now(),now());
mysql> insert into t14 values('xj',now(),now());
mysql> select * from t14;
+------+---------------------+---------------------+
| name | mydt | myts |
+------+---------------------+---------------------+
| hh | 2023-08-17 10:10:10 | 2023-08-17 10:10:10 |
| hj | NULL | 2023-08-17 14:30:29 |
| mh | 2023-08-17 14:31:10 | 2023-08-17 14:31:10 |
| xj | 2023-08-17 14:31:33 | 2023-08-17 14:31:33 |
+------+---------------------+---------------------+
mysql> update t14 set name='nb' where name="hj";
mysql> select * from t14;
+------+---------------------+---------------------+
| name | mydt | myts |
+------+---------------------+---------------------+
| hh | 2023-08-17 10:10:10 | 2023-08-17 10:10:10 |
| nb | NULL | 2023-08-17 14:33:08 |
| mh | 2023-08-17 14:31:10 | 2023-08-17 14:31:10 |
| xj | 2023-08-17 14:31:33 | 2023-08-17 14:31:33 |
+------+---------------------+---------------------+
综合练习2
mysql> create table test(
-> id int,
-> name char(10),
-> money double(10,2),
-> gender enum('M','F'),
-> hobby set('陪hxj打牌'，'和hxj吃饭'，'和hxj看电影'),
-> email varchar(50),
-> qq char(15),
-> idcard char(18),
-> jointime datetime);
mysql> insert into test values('12','mh','100','M','和hxj吃饭','16098','178963','18','20230816101010');
mysql> select * from test;
+------+------+--------+--------+--------------+-------+--------+--------+---------------------+
| id | name | money | gender | hobby | email | qq | idcard | jointime |
+------+------+--------+--------+--------------+-------+--------+--------+---------------------+
| 12 | mh | 100.00 | M | 和hxj吃饭 | 16098 | 178963 | 18 | 2023-08-16 10:10:10 |
+------+------+--------+--------+--------------+-------+--------+--------+---------------------+
mysql> select * from test;
+------+------+--------+--------+--------------+-------+--------+---
| id | name | money | gender | hobby | email | qq | id
+------+------+--------+--------+--------------+-------+--------+---
| 12 | mh | 100.00 | M | 和hxj吃饭 | 16098 | 178963 | 18
+------+------+--------+--------+--------------+-------+--------+---
1-4 约束
1.修饰符：not null 约束
mysql> create table t17(id int,name char(10) not null);
mysql> desc t17;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2.default 约束
mysql> create table t18(id int,name char(10),money int default 10);
mysql> desc t18;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| money | int(11) | YES | | 10 | |
+-------+----------+------+-----+---------+-------+
mysql> insert into t18(id,name) values(1,'tom');
mysql> select * from t18;
+------+------+-------+
| id | name | money |
+------+------+-------+
| 1 | tom | 10 |
+------+------+-------+
3.索引建立
mysql> create table t19(id int,name char(10),index(id)); # index(id)向已有表添加索引
mysql> desc t19;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> show index from t19\G # 查询索引
*************************** 1. row ***************************
Table: t19
Non_unique: 1
Key_name: id # 添加的索引
···
mysql> select * from t19 where id=1;
Empty set (0.01 sec)
mysql> drop index id on t19;
mysql> create index myid on t19(id); # 向已有表添加索引 方式2
mysql> show index from t19\G;
*************************** 1. row ***************************
Table: t19
Non_unique: 1
Key_name: myid # 添加的索引
···
mysql> drop index myid on t19; # 删除索引
mysql> alter table t19 add index(id); # 添加索引的方式3
mysql> show index from t19\G
*************************** 1. row ***************************
Table: t19
Non_unique: 1
Key_name: id # 索引
mysql> create table t20(id int,name char(10),email
mysql> show index from t20\G
*************************** 1. row ***************************
Table: t20 ···
Collation: A
Cardinality: 0
····
mysql> insert into t20 values(1,'mh','mh@mh.com');
mysql> insert into t20 values(1,'mh',null);
mysql> show index from t20\G
*************************** 1. row ***************************
Table: t20 ···
Collation: A
Cardinality: 2
···
mysql> create index email on t20(email);
mysql> desc t20;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| email | varchar(50) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> drop index email on t20;
mysql> create unique index email on t20(email); # UNIQUE索引(允许空值)
mysql> desc t20;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| email | varchar(50) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
4.primary key(主键约束,值唯一;uniq和not null的结合)
mysql> create table t21(id int,name char(10),email varchar(50),primary key(id));
mysql> desc t21;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> show index from t21\G # \G 以列的形式查看
*************************** 1. row ***************************
Table: t21
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
···
mysql> alter table t21 drop primary key; # 删除主键 （或者是： mysql> drop index `PRIMARY` on t21; #注意是``，不是单引号）
mysql> alter table t21 add primary key(id); # 向已有表添加主键
5.在多个列上建立主键：一张表中只能有一个主键，但是一个主键可以作用到多个列上
mysql> create table t22(id int,name char(10),primary key(id,name));
mysql> desc t22;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | PRI | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> show index from t22\G
*************************** 1. row ***************************
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
···
*************************** 2. row ***************************
Key_name: PRIMARY
Seq_in_index: 2
Column_name: name
···
mysql> insert into t22 values(1,'robin');
mysql> insert into t22 values(2,'robin');
mysql> insert into t22 values(2,'zorro');
mysql> select * from t22;
+----+-------+
| id | name |
+----+-------+
| 1 | robin |
| 2 | robin |
| 2 | zorro |
+----+-------+
在多个列上建立主键
create table t19(id int,name char(10),primary key(id,name));
create table t19(id int primary key auto_increment, name char(10)); 自动增长
全文本索引
create table t22(id int,name char(10),fulltext(id));
···
综合练习：创建表 test2
1-添加约束
--id name money gender hobby email qq idcard jointime
mysql> create table test2(
-> id int primary key,
-> name char(20) not null,
-> money float(10,2) default 0,
-> gender enum('M','F') not null,
-> hobby set('a','b','c'),
-> email varchar(10) unique,
-> qq char(15) unique,
-> idcard char(18) not null,
-> jointime datetime not null);
Query OK, 0 rows affected (0.01 sec)
mysql> desc test2;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| id | int(11) | NO | PRI | NULL |
| name | char(20) | NO | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | NO | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | UNI | NULL |
| qq | char(15) | YES | UNI | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | NO | | NULL |
+----------+------------------+------+-----+---------+-------
2-删除掉所有约束
1.mysql> alter table test2 drop primary key; # 删除primary key
2.mysql> alter table test2 modify id int; # 删除 not null
mysql> desc test2;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| id | int(11) | YES | | NULL |
| name | char(20) | NO | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | NO | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | UNI | NULL |
| qq | char(15) | YES | UNI | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | NO | | NULL |
+----------+------------------+------+-----+---------+-------
3.mysql> alter table test2 modify name char(10); # 修改NULL状态
mysql> desc test2;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| id | int(11) | YES | | NULL |
| name | char(10) | YES | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | NO | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | UNI | NULL |
| qq | char(15) | YES | UNI | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | NO | | NULL |
+----------+------------------+------+-----+---------+-------
4.mysql> alter table test2 modify gender enum('M','F');
mysql> desc test2;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| id | int(11) | YES | | NULL |
| name | char(10) | YES | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | YES | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | UNI | NULL |
| qq | char(15) | YES | UNI | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | NO | | NULL |
+----------+------------------+------+-----+---------+-------
5.mysql> drop index email on test2;
6.mysql> drop index qq on test2;
mysql> desc test2;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| id | int(11) | YES | | NULL |
| name | char(10) | YES | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | YES | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | | NULL |
| qq | char(15) | YES | | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | NO | | NULL |
+----------+------------------+------+-----+---------+-------
3-修改表结构
7.mysql> alter table test2 modify jointime datetime; # 修改NULL值状态
mysql> desc test2;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| id | int(11) | YES | | NULL |
| name | char(10) | YES | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | YES | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | | NULL |
| qq | char(15) | YES | | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | YES | | NULL |
+----------+------------------+------+-----+---------+-------
8.mysql> alter table test2 modify id int primary key auto_incrent; # 自动增长
mysql> desc test2;
+----------+------------------+------+-----+---------+--------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------
-------+
| id | int(11) | NO | PRI | NULL | auto_irement |
| name | char(10) | YES | | NULL | |
| money | float(10,2) | YES | | 0.00 | |
| gender | enum('M','F') | YES | | NULL | |
| hobby | set('a','b','c') | YES | | NULL | |
| email | varchar(10) | YES | | NULL | |
| qq | char(15) | YES | | NULL | |
| idcard | char(18) | NO | | NULL | |
| jointime | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+--------------+
9.mysql> rename table test2 to mybabe; # 修改表名称
mysql> desc mybabe;
+----------+------------------+------+-----+---------+-------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------------+
| id | int(11) | NO | PRI | NULL | auto_iement |
| name | char(10) | YES | | NULL | |
| money | float(10,2) | YES | | 0.00 | |
| gender | enum('M','F') | YES | | NULL | |
| hobby | set('a','b','c') | YES | | NULL | |
| email | varchar(10) | YES | | NULL | |
| qq | char(15) | YES | | NULL | |
| idcard | char(18) | NO | | NULL | |
| jointime | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+-------------+
10.mysql> alter table mybabe change id uid int; # 修改id名称
mysql> desc mybabe;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| uid | int(11) | NO | PRI | NULL |
| name | char(10) | YES | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | YES | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | | NULL |
| qq | char(15) | YES | | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | YES | | NULL |
+----------+------------------+------+-----+---------+-------
11.mysql> alter table mybabe add phone char(11); # 添加列
mysql> desc mybabe;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| uid | int(11) | NO | PRI | NULL |
| name | char(10) | YES | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | YES | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | | NULL |
| qq | char(15) | YES | | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | YES | | NULL |
| phone | char(11) 添加到这 | YES | | NULL |
+----------+------------------+------+-----+---------+-------
12.mysql> alter table mybabe drop phone; # 删除列 注意:会删除数据
mysql> desc mybabe;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| uid | int(11) | NO | PRI | NULL |
| name | char(10) | YES | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | YES | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | | NULL |
| qq | char(15) | YES | | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | YES | | NULL |
+----------+------------------+------+-----+---------+-------
13.mysql> alter table mybabe add phone char(12) first; # 添加列到第一列
mysql> desc mybabe;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| phone | char(12) | YES | | NULL |
| uid | int(11) | NO | PRI | NULL |
| name | char(10) | YES | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | YES | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | | NULL |
| qq | char(15) | YES | | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | YES | | NULL |
+----------+------------------+------+-----+---------+-------
14.mysql> alter table mybabe change phone myphone char(12); # 修改列的名字
mysql> desc mybabe;
+----------+------------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+----------+------------------+------+-----+---------+-------
| myphone | char(12) | YES | | NULL |
| uid | int(11) | NO | PRI | NULL |
| name | char(10) | YES | | NULL |
| money | float(10,2) | YES | | 0.00 |
| gender | enum('M','F') | YES | | NULL |
| hobby | set('a','b','c') | YES | | NULL |
| email | varchar(10) | YES | | NULL |
| qq | char(15) | YES | | NULL |
| idcard | char(18) | NO | | NULL |
| jointime | datetime | YES | | NULL |
+----------+------------------+------+-----+---------+-------
2-DML 数据库操作语句---insert into/ update/ delete
mysql> insert into test2(name,money,gender,hobby,email,qq,idca,jointime) values('robin',0,'M','a','aa@aa.com','123','1233',()); # DML 数据库操作语句--insert
mysql> insert into test2(name,money,gender,hobby,email,qq,idca,jointime) values('robin',0,'M','a','aa@aa.com','123','1233',());
mysql> select * from mybabe;
+-------+-----+-------+-------+--------+-------+-----------+---+--------+---------------------+
| phone | uid | name | money | gender | hobby | email | | idcard | jointime |
+-------+-----+-------+-------+--------+-------+-----------+---+--------+---------------------+
| NULL | 1 | robin | 0.00 | M | a | aa@aa.com | | 1233 | 2023-08-17 19:35:47 |
| NULL | 2 | robin | 0.00 | M | a | aa@aa.com | | 1233 | 2023-08-17 19:36:29 |
+-------+-----+-------+-------+--------+-------+-----------+---+--------+---------------------+
mysql> update mybabe set name='God'; # update 修改name为God
mysql> select * from mybabe;
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
| phone | uid | name | money | gender | hobby | email | q | idcard | jointime |
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
| NULL | 1 | God | 0.00 | M | a | aa@aa.com | 1 | 1233 | 2023-08-17 19:35:47 |
| NULL | 2 | God | 0.00 | M | a | aa@aa.com | 1 | 1233 | 2023-08-17 19:36:29 |
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
mysql> update mybabe set name='fly' where uid=1; # 修改uid为1的name为fly
mysql> select * from mybabe;
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
| phone | uid | name | money | gender | hobby | email | q | idcard | jointime |
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
| NULL | 1 | fly | 0.00 | M | a | aa@aa.com | 1 | 1233 | 2023-08-17 19:35:47 |
| NULL | 2 | God | 0.00 | M | a | aa@aa.com | 1 | 1233 | 2023-08-17 19:36:29 |
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
mysql> update mybabe set name='yep' where uid=2; # 修改uid为2的name为yep
mysql> select * from mybabe;
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
| phone | uid | name | money | gender | hobby | email | q | idcard | jointime |
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
| NULL | 1 | fly | 0.00 | M | a | aa@aa.com | 1 | 1233 | 2023-08-17 19:35:47 |
| NULL | 2 | yep | 0.00 | M | a | aa@aa.com | 1 | 1233 | 2023-08-17 19:36:29 |
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
mysql> delete from mybabe where uid=100 or name='yep'; # delete 删除
mysql> delete from mybabe where uid=100 and name='yep';
mysql> select * from mybabe;
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
| phone | uid | name | money | gender | hobby | email | q | idcard | jointime |
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
| NULL | 1 | fly | 0.00 | M | a | aa@aa.com | 1 | 1233 | 2023-08-17 19:35:47 |
+-------+-----+------+-------+--------+-------+-----------+---+--------+---------------------+
