mysql的数据表操作

1.字符串的数据类型

　　（1）数值类型

　　　　TINYINT     SMALLINT INT或INTEGER   BIGINT     FLOAT  DOUBLE    DECMAL

　　（2）字符串类型　　

　　　　CHAR （定长字符串）　　VARCHAR（变长字符串）　　TINYBLOB（不超过255个字符的二进制字符串）　　TINYTEXT（短文本字符串）　　BLOB（二进制形式的长文本数据）　　TEXT（长文本数据）

　　　　MEDIUMBLOB（二进制形式的中等长度文本数据）　　MEDIUMTEXT（中等长度文本数据）　　LONGBLOB（二进制形式的极大文本数据）　　LONGTEXT（极大文本数据）

　　（3）时间数据类型

　　　　DATE（日期值）　　TIME（时间值或持续时间）　　YEAR（年份值）　　DATETIME（混合日期和时间值）　　TIMESTAMP（混合日期和时间值，时间戳）

2.创建表（类似于一个excel表）

　　create table tab_name(

　　　　field1 type [完整性约束条件]，

　　　　field2 type,

　　　　...

　　　　fieldn type

　　　　)  []

 CREATE TABLE employee(
    ->
    ->         id TINYINT PRIMARY KEY auto_increment,
    ->         name VARCHAR(25),
    ->         gender boolean,
    ->         age  INT,
    ->         department VARCHAR(20),
    ->         salary DOUBLE(7,2)

3..查看表信息

　　（1）通过 show tables； 查看当前数据库中所有的表

show tables;
运行结果：
+--------------+
| Tables_in_s1 |
+--------------+
| employee     |
+--------------+
1 row in set (0.00 sec)

　　（2）通过 desc tab_name； 查看表结构

desc employee;
运行结果：
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |　　　　　　　　type：数据类型　　Null：是否可以为空　　key：主键  default：默认值
+------------+-------------+------+-----+---------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(25) | YES  |     | NULL    |                |
| gender     | tinyint(1)  | YES  |     | NULL    |                |
| age        | int(11)     | YES  |     | NULL    |                |
| department | varchar(20) | YES  |     | NULL    |                |
| salary     | double(7,2) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

　　（3）通过 show create table tab_name;  查看当前数据库建表语句

 show create table employee;
运行结果：
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                    |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `department` varchar(20) DEFAULT NULL,
  `salary` double(7,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

　　（4）show columns from tab_name

4.修改表结构

　　（1）增加列（字段） alter table tab_name add [column] 列名 类型[完整性约束条件]  [first|after 字段名 ]；

　　　　　　　　　　　例　alter table user（数据库名） add addr（列名，字段） varchar(20)（数据类型）  not null unique（完整性约束条件） first/after username;

　　　　添加多个字段　　alter table tab_name add 列名 类型，

　　　　　　　　　　　　　　　　　　　　　add 列名  类型，

　　　　　　　　　　　　　　　　　　　　　add 列名 类型；

 alter table employee add is_married tinyint(1);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
运行结果：
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(25) | YES  |     | NULL    |                |
| gender     | tinyint(1)  | YES  |     | NULL    |                |
| age        | int(11)     | YES  |     | NULL    |                |
| department | varchar(20) | YES  |     | NULL    |                |
| salary     | double(7,2) | YES  |     | NULL    |                |
| is_married | tinyint(1)  | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> alter table employee add entry_date date not null;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
运行结果：
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(25) | YES  |     | NULL    |                |
| gender     | tinyint(1)  | YES  |     | NULL    |                |
| age        | int(11)     | YES  |     | NULL    |                |
| department | varchar(20) | YES  |     | NULL    |                |
| salary     | double(7,2) | YES  |     | NULL    |                |
| is_married | tinyint(1)  | YES  |     | NULL    |                |
| entry_date | date        | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

mysql> alter table employee add A INT,
    -> ADD B VARCHAR(20);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
运行结果：
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(25) | YES  |     | NULL    |                |
| gender     | tinyint(1)  | YES  |     | NULL    |                |
| age        | int(11)     | YES  |     | NULL    |                |
| department | varchar(20) | YES  |     | NULL    |                |
| salary     | double(7,2) | YES  |     | NULL    |                |
| is_married | tinyint(1)  | YES  |     | NULL    |                |
| entry_date | date        | NO   |     | NULL    |                |
| A          | int(11)     | YES  |     | NULL    |                |
| B          | varchar(20) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

　　（2）修改一列类型

　　　　alter table tab_name modify 列名 类型 [完整性约束条件] [ first|after 字段名]；

alter table employee modify age smallint unique default 18 after id;　　　　　　此处 anmllint unique default 18 就是完整性约束条件
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| age        | smallint(6) | YES  | UNI | 18      |                |
| name       | varchar(25) | YES  |     | NULL    |                |
| gender     | tinyint(1)  | YES  |     | NULL    |                |
| department | varchar(20) | YES  |     | NULL    |                |
| salary     | double(7,2) | YES  |     | NULL    |                |
| is_married | tinyint(1)  | YES  |     | NULL    |                |
| entry_date | date        | NO   |     | NULL    |                |
| B          | varchar(20) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

　　（3）修改列名

　　　　alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件] [ first |after 字段名]；

 alter table employee change department depart varchar(20) after salary;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee
    -> ;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| age        | smallint(6) | YES  | UNI | 18      |                |
| name       | varchar(25) | YES  |     | NULL    |                |
| gender     | tinyint(1)  | YES  |     | NULL    |                |
| salary     | double(7,2) | YES  |     | NULL    |                |
| depart     | varchar(20) | YES  |     | NULL    |                |
| is_married | tinyint(1)  | YES  |     | NULL    |                |
| entry_date | date        | NO   |     | NULL    |                |
| B          | varchar(20) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

　　（4）删除一列　（删除多列与增加多列的语句类似）

　　　　alter table tab_name drop [column] 列名；

　　（5）修改表名 

　　　　rename table 表名 to 新表名；

 rename table employee to emp;
Query OK, 0 rows affected (0.09 sec)

show tables;
+--------------+
| Tables_in_s1 |
+--------------+
| emp          |
+--------------+
1 row in set (0.00 sec)

 5.表记录之增、删、改

　　（1）增加一条记录 insert　　insert into tab_name  (filed1 , field2,......)  values (value1, value2,....);

mysql> insert into emp (id , age , name , gender , salary , depart , is_married)
    ->           values(1 , 38 , 'alex' , 0 , 1700 , '技术部' , 1 );
Query OK, 1 row affected (1.67 sec)


mysql>  select * from emp;
+----+------+------+--------+---------+--------+------------+
| id | age  | name | gender | salary  | depart | is_married |
+----+------+------+--------+---------+--------+------------+
|  1 |   38 | alex |      0 | 1700.00 | 技术部 |          1 |
+----+------+------+--------+---------+--------+------------+
1 row in set (0.31 sec)

select * from emp;
+----+------+------+--------+----------+--------+------------+
| id | age  | name | gender | salary   | depart | is_married |　　　　　　　　因为在创建时，id 是可以自增的，gender可以为空， age 默认为18 ， is_married也可以为空 ，因此可以选择不设置值

+----+------+------+--------+----------+--------+------------+
|  1 |   38 | alex |      0 |  1700.00 | 技术部 |          1 |
|  2 |   18 | 呱   |   NULL | 30000.00 | python |       NULL |
+----+------+------+--------+----------+--------+------------+
2 rows in set (1.64 sec)

　　        增加多条数据          1> insert into table_name  (field1,field2...)  ， values (value1,value2,...)  ，(value1,value2,...)   (value1,value2,...)

　　　　　　　　　　　    　2> insert into table_name values (value1,value2,...)  ，(value1,value2,...)     (此方法必须保证没有漏值)

方法1
mysql> insert into emp (age ,name , salary ,depart) values(19,'小鱼',5000,'销售部'),
    ->                                                    (20,'i冰冰',10000,'销售部');
Query OK, 2 rows affected (1.67 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from emp;
+----+------+-------+--------+----------+--------+------------+
| id | age  | name  | gender | salary   | depart | is_married |
+----+------+-------+--------+----------+--------+------------+
|  1 | 38   | alex  |      0 |  1700.00 | 技术部 |          1 |
|  2 | 18   | 呱    |   NULL | 30000.00 | python |       NULL |
|  5 | 19   | 小鱼  |   NULL |  5000.00 | 销售部 |       NULL |
|  6 | 20   | i冰冰 |   NULL | 10000.00 | 销售部 |       NULL |
+----+------+-------+--------+----------+--------+------------+
4 rows in set (0.00 sec)



方法2
mysql> insert into emp values(7,22,'呱呱',0,60000,'技术部',1);     应用此方法时，每个键都要设置对应的值。并且，可以发现 id并不是依次增加，此时若再往里面添加数据，应从8开始，不能再2 - 5 之间添加数据
Query OK, 1 row affected (0.07 sec)

mysql> select * from emp ;
+----+------+-------+--------+----------+--------+------------+
| id | age  | name  | gender | salary   | depart | is_married |
+----+------+-------+--------+----------+--------+------------+
|  1 | 38   | alex  |      0 |  1700.00 | 技术部 |          1 |
|  2 | 18   | 呱    |   NULL | 30000.00 | python |       NULL |
|  5 | 19   | 小鱼  |   NULL |  5000.00 | 销售部 |       NULL |
|  6 | 20   | i冰冰 |   NULL | 10000.00 | 销售部 |       NULL |
|  7 | 22   | 呱呱  |      0 | 60000.00 | 技术部 |          1 |
+----+------+-------+--------+----------+--------+------------+
5 rows in set (0.00 sec)

　　     

　　　　set插入 ： insert  into tab_name set 字段名 = 值  （即以键值对的方式插入）

　　　　　　　　　　insert into tab_name set   id = 12 ,name = 'gua' ,...;

　　(2)修改表记录 update tab_name set field1 =value1 ,field2 =value2,......[where 语句] ;

　　　　　　　　　(update 语法可以用新值更新原有表行中的各列

　　　　　　　　　　set 子句指示要修改的那些列和要给与哪些值

　　　　　　　　　　where 子句指定应更新哪些行，如果没有 where 子句，则更新所有行

给名字为 ‘呱’ 的人工资加2000
mysql> update emp set salary = salary + 2000 where name = '呱';
Query OK, 1 row affected (1.69 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp ;
+----+------+-------+--------+----------+--------+------------+
| id | age  | name  | gender | salary   | depart | is_married |
+----+------+-------+--------+----------+--------+------------+
|  1 | 38   | alex  |      0 |  1700.00 | 技术部 |          1 |
|  2 | 18   | 呱    |   NULL | 32000.00 | python |       NULL |
|  5 | 19   | 小鱼  |   NULL |  5000.00 | 销售部 |       NULL |
|  6 | 20   | i冰冰 |   NULL | 10000.00 | 销售部 |       NULL |
|  7 | 22   | 呱呱  |      0 | 60000.00 | 技术部 |          1 |
|  8 | 26   | 小晴  |   NULL |     NULL | NULL   |       NULL |
+----+------+-------+--------+----------+--------+------------+
6 rows in set (0.00 sec)

　　（3）删除表记录 delect from tab_name [where ...];

　　　　　　(如果不跟 where 语句则删除整张表中的数据

　　　　　　 delete 只能用来删除一行记录

　　　　　　delete语句只能删除表中的的内同，不能删除表本身，想要删，用drop

　　　　　　truncate table 也可以删除表中的所有数据，此语句首先摧毁表，在新建表。此种方式删除的数据不能再事务中恢复)

mysql> delete from emp where id = 7 ;
Query OK, 1 row affected (1.70 sec)

mysql> select * from emp;
+----+------+-------+--------+----------+--------+------------+
| id | age  | name  | gender | salary   | depart | is_married |
+----+------+-------+--------+----------+--------+------------+
|  1 | 38   | alex  |      0 |  1700.00 | 技术部 |          1 |
|  2 | 18   | 呱    |   NULL | 32000.00 | python |       NULL |
|  5 | 19   | 小鱼  |   NULL |  5000.00 | 销售部 |       NULL |
|  6 | 20   | i冰冰 |   NULL | 10000.00 | 销售部 |       NULL |
|  8 | 26   | 小晴  |   NULL |     NULL | NULL   |       NULL |
+----+------+-------+--------+----------+--------+------------+
5 rows in set (0.00 sec)