3.1 提出问题，引入“表“的概念与思维模式 table
表的概念：
数据库类似于厂库，而表呢就是对数据进行抽象分类的货架
注意：在创建数据库的时候一定要记得设置字符编码
3.2 引用数据库和查看数据库中的表
1. 引用数据库
mysql> use student; Database changed
2. 查看数据库中的表
mysql> show tables; Empty set (0.00 sec)
3.3 创建表
创建表
mysql> create table student( -> id int, -> name varchar(30), -> age int, -> salary int -> ); Query OK, 0 rows affected (0.04 sec) mysql> show tables -> ; +-----------------------+ | Tables_in_student | +-----------------------+ | empolyee | +-----------------------+
注意：在MySQL中字符串的语句是不使用String的，而是使用varchar，需要表明长度，同时一级一级的写语句能更加整洁明了
3.4 创建表（企业用，有B格）
mysql> create table if not exists teacher( -> id int auto_increment primary key comment'主键id', -> name varchar(30) not null comment '老师的名字', -> phone varchar(20) comment '电话号码', -> address varchar(100) default '暂时未知' comment '住址' -> )engine=innodb; Query OK, 0 rows affected (0.03 sec)
注意：在创建表的时候，在default单引号中间的内容要和所定义的数据类型一样，最后一个属性写完不要用“,”
关键词解释
auto_increment: 对字段进行自动增长primary key: 主键，是关系型数据库连接桥梁，必须填写不能空着comment: 注释
not null: 不能为空，必须填写default: 如果为空那么表中这个数据默认为’ '中的内容，默认值engine = innodb: 表示数据库引擎
两种方式创建的有什么区别呢？
简单的
mysql> create table empolyee( -> id int, -> name varchar(30), -> age int, -> salary int -> ); Query OK, 0 rows affected (0.04 sec) mysql> show create table empolyee; +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | empolyee | CREATE TABLE `empolyee` ( `id` int(11) DEFAULT NULL, `name` varchar(30) DEFAULT NULL, `age` int(11) DEFAULT NULL, `salary` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
复杂的
mysql> create table staff( -> id int auto_increment primary key comment'主键id', -> name varchar(30) not null, -> age int comment'年龄', -> salary int default '0' comment'薪水' -> )engine=innodb; Query OK, 0 rows affected (0.03 sec) mysql> show create table staff; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | staff | CREATE TABLE `staff` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `name` varchar(30) NOT NULL, `age` int(11) DEFAULT NULL COMMENT '年龄', `salary` int(11) DEFAULT '0' COMMENT '薪水', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
即使没有写default，但还是会默认default
3.5 查看表结构
1. 显示出表的sql语句
show create table teacher;
mysql> show tables; +-------------------+ | Tables_in_student | +-------------------+ | teacher | +-------------------+ 1 row in set (0.00 sec) mysql> show create table teacher; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | teacher | CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `name` varchar(30) NOT NULL COMMENT '老师的名字', `phone` varchar(20) DEFAULT NULL COMMENT '电话号码', `address` varchar(100) DEFAULT '暂时未知' COMMENT '住址', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
2. 查看表的结构
desc teacher;
mysql> desc teacher; +---------+--------------+------+-----+----------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+----------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | | phone | varchar(20) | YES | | NULL | | | address | varchar(100) | YES | | 暂时未知 | | +---------+--------------+------+-----+----------+----------------+ 4 rows in set (0.01 sec)
Null：代表的是否能为空Key PRI：代表是唯一值Default：说明是否设立defaultExtra：代表的是规则，上述文件是自动增加
3.6 删除表
1. 删除单个表
drop table if exists staff ;
2. 删除多个表
drop table if exists staff,empolyee;
3.7 修改表
1. 添加一个新的字段
alter table 表名 add 字段名 字段类型
mysql> alter table student add name varchar(30); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
2. 指定位置添加一个新的字段
在某字段之后
alter table 表名 add 字段名 字段类型 after 字段名
mysql> alter table student add age int after id; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | age | int(11) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
在第一行添加
alter table 表名 add 字段 字段类型 first
mysql> alter table student add phone int(20) first; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | phone | int(20) | YES | | NULL | | | id | int(4) | YES | | NULL | | | age | int(11) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
3. 删除字段
alter table 表名 drop 字段
mysql> alter table student drop phone; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | age | int(11) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
4. 修改字段（名、类型)
修改名和类型（完全修改）
alter table 表名 change 字段名 新的字段名 字段类型
mysql> alter table student change age phone varchar(20); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | phone | varchar(20) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
修改类型
alter table 表名 modify 字段名 要改成的字段类型
mysql> alter table student modify phone int(20); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | phone | int(20) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
5. 修改表名
alter table 表名 rename to 新表名
mysql> alter table student rename to empolyee; Query OK, 0 rows affected (0.02 sec) mysql> show tables; +-----------------------+ | Tables_in_qiu_company | +-----------------------+ | empolyee | +-----------------------+ 1 row in set (0.00 sec)
