mysql数据表添加数值_MySQL中数据表和数据的操作

一.数据库表的操作

1.数据表的创建

mysql> create tablet_user(-> 　　id int unsigned not null auto_increment primary key,-> 　　name varchar(40) not null,-> 　　sex enum('man', 'woman', 'secret') default 'man',-> 　　age tinyintunsigned-> ) engine=innodb default charset=utf8mb4;

Query OK,0 rows affected (0.01 sec)

not null:该字段不能为空

auto_increment 自动增长

primary key:主键，主键对应的列不能重复

default :可以设置默认值，当插入时，如果没有插入该列对应的值，则会插入默认值

engine :引擎

default charset:默认编码

2.数据表的查询

use db_itheima;切换到db_itheima数据库下

show tables：展示db_itheima数据库下的所有数据表

mysql> usedb_itheima;Databasechanged

mysql>show tables;+----------------------+

| Tables_in_db_itheima |

+----------------------+

| it_article |

| it_student |

| t_user |

+----------------------+

3 rows in set (0.00 sec)

或

show create table t_user:查看t_user数据表的创建语句

mysql> show create tablet_user;+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t_user | CREATE TABLE`t_user` (

`　　id`int(10) unsigned NOT NULLAUTO_INCREMENT,

`　　name`varchar(40) NOT NULL,

`　　sex` enum('man','woman','secret') DEFAULT 'man',

`　　age`tinyint(3) unsigned DEFAULT NULL,PRIMARY KEY(`　　id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

3.数据表的修改

可以修改的内容

(1)数据表的列(列名,数据类型)

添加列

# alter table t_user add birth date first;# 添加在最前面

alter table t_user add birth date after age;#添加到age列后

mysql> alter table t_user addbirth date after age;

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql> desct_user;+-------+------------------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(40) | NO | | NULL | |

| sex | enum('man','woman','secret') | YES | | man | |

| age | tinyint(3) unsigned | YES | | NULL | |

| birth | date | YES | | NULL | |

+-------+------------------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

修改列名和数据类型

alter table t_user change name NAME varchar(30);

修改数据类型

alter table t_user modify age int unsigned;

删除列

alter table t_user drop age;

(2)数据表名

alter table t_user rename it_user;

(3)引擎

alter table t_user engine=myisam;

alter table t_user engine=innodb;

(4)编码类型

alter table t_user default charset=utf8;

4.数据表的删除

drop table t_user;

二.数据的操作

1.添加数据

insert into t_user values(null,'xianqian','woman',23,'1996-09-23');# 所有列的值都需要写

insert into t_user(id,name) values(null, 'xiaoming');# 只插入id和name值

insert into t_user values (null, 'tom','man',34,'1985-03-24'),(null,'jack','man',10,'2009-03-20');# 可以插入多条数据

mysql> insert into t_user values(null,'xianqian','woman',23,'1996-09-23');# 所有列的值都需要写insert into t_user(id,name) values(null, 'xiaoming'); # 只插入id和name值insert into t_user values (null, 'tom','man',34,'1985-03-24'),(null,'jack','man',10,'2009-03-20'); # 可以插入多条数据 Query OK, 1 row affected (0.02sec)

mysql> insert into t_user(id,name) values(null, 'xiaoming');# 只插入id和name值

Query OK,1 row affected (0.00sec)

mysql> insert into t_user values (null, 'tom','man',34,'1985-03-24'),(null,'jack','man',10,'2009-03-20');# 可以插入多条数据

Query OK,2 rows affected (0.00sec)

Records:2 Duplicates: 0 Warnings: 0mysql> select * fromt_user;+----+----------+-------+------+------------+

| id | name | sex | age | birth |

+----+----------+-------+------+------------+

| 1 | xianqian | woman | 23 | 1996-09-23 |

| 2 | xiaoming | man | NULL | NULL |

| 3 | tom | man | 34 | 1985-03-24 |

| 4 | jack | man | 10 | 2009-03-20 |

+----+----------+-------+------+------------+

4 rows in set (0.00 sec)

2.查询数据

select * from t_user;#查询所有列的内容

3.修改数据

update t_user set age=19;# 所有记录的age列都会被改为19

update t_user set age=18,age=20 where name='jack';# name=jack的记录的age列被改为18;

update t_user set sex='man' where age =18 or name ='jack';#所有name='jack'或age=18的记录的sex列被改为'man'

4.删除数据

delete from t_user where name='jack';

delete from t_user where name='tom' and age=20;

5.清空数据

delete from t_user;# 删除所有数据

truncate t_user;

6.delete与truncate的区别

delete:

属于DML语句，删除所有数据

执行时会被记录事务，可以执行rollback回滚操作

删除大量数据时速度慢，并且不会回收高水位线

可以带条件删除

truncate:

属于DDL语句，删除所有数据，实际上是重新创建表结构

执行时不会记录事务，不能执行rollback回滚操作

删除大量数据时速度快，可以回收高水位线

不能带条件删除

扩展知识

1.整数类型后的括号里的数字什么含义？

int(11),tinyint(3)

括号中的数字通常都是无效的，要想让它有效，需要在定义时加上unsigned和zerofill

create table it_int(

id tinyint(3) unsigned zerofill

);