一.数据库表的操作
1.数据表的创建
mysql> create table t_user( -> id int unsigned not null auto_increment primary key, -> name varchar(40) not null, -> sex enum('man', 'woman', 'secret') default 'man', -> age tinyint unsigned -> ) 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> use db_itheima; Database changed 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 table t_user; +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t_user | CREATE TABLE `t_user` ( ` id` int(10) unsigned NOT NULL AUTO_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 add birth date after age; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t_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.02 sec) mysql> insert into t_user(id,name) values(null, 'xiaoming');# 只插入id和name值 Query OK, 1 row affected (0.00 sec) 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.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t_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
);