数据库学习交流2

2 表的操作

2.1 显示所有表

show tables;

2.2 创建表

create table if not exits 表名
(字段名 数据类型 [null/not null] [auto_increment] [primary key] [comment],
字段名 数据类型 [default]…
)engine = 存储引擎

default 默认值
auto_increment 自动增长
primary key 主键
comment 备注
engine 引擎（innodb mysiam memory）

mysql> create table per(
    -> id int,
    ->  name varchar(20)
    ->
   -> );

mysql> create table if not exists teacher(
    -> id int auto_increment primary key comment '主键',
    -> name varchar(10) not null comment '姓名',
    -> phone varchar(20) ,
    -> `add` varchar(100) default '地址不详' comment '地址'
    -> )engine=innodb;
ERROR 1067 (42000): Invalid default value for 'add'
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

只要带有中文就写 set names gbk;然后就行了

mysql> create table if not exists teacher(
    -> id int auto_increment primary key comment '主键',
    ->  name varchar(10) not null comment '姓名',
    ->  phone varchar(20) ,
    ->  `add` varchar(100) default '地址不详' comment '地址'
    -> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| per              |
| stu              |
| teacher          |
+------------------+
3 rows in set (0.00 sec)

在其它数据库中创建表
create table 数据库名.表名

mysql> create table data.stu(
    -> id int ,
    -> name varchar(100));
    mysql> use data;
Database changed
mysql> show tables;
+----------------+
| Tables_in_data |
+----------------+
| stu            |
+----------------+
1 row in set (0.00 sec)

查询创建表语句
show create table 表名

mysql> show tables;
+----------------+
| Tables_in_data |
+----------------+
| stu            |
+----------------+
1 row in set (0.00 sec)

mysql> show create table stu;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.3 查看表结构

describe 表名

mysql> describe teacher;
+-------+--------------+------+-----+--------------+----------------+
| Field | Type         | Null | Key | Default      | Extra          |
+-------+--------------+------+-----+--------------+----------------+
| id    | int(11)      | NO   | PRI | NULL         | auto_increment |
| name  | varchar(10)  | NO   |     | NULL         |                |
| phone | varchar(20)  | YES  |     | NULL         |                |
| add   | varchar(100) | YES  |     | 鍦板潃涓嶈     |                |
+-------+--------------+------+-----+--------------+----------------+
4 rows in set (0.19 sec)

出现乱码：set names gbk;

mysql> describe teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(10)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
+-------+--------------+------+-----+----------+----------------+
4 rows in set (0.12 sec)

也可以用简写desc

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(10)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
+-------+--------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)

2.4 删除表

drop table [id exists] 表1，表2…

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| per              |
| stu              |
| teacher          |
+------------------+
3 rows in set (0.00 sec)

mysql> drop table stu,per;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| teacher          |
+------------------+
1 row in set (0.00 sec)

mysql> drop table stu;
ERROR 1051 (42S02): Unknown table 'stu'

mysql>  drop table if exists stu;
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.5 修改表

语法：alter table 表名
1、添加字段 alter table 表名 add [column] 字段名 数据类型 [位置]

mysql> alter table teacher add marry varchar(20) default '未婚';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(10)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
| marry | varchar(20)  | YES  |     | 未婚        |                |
+-------+--------------+------+-----+----------+----------------+
5 rows in set (0.12 sec)

在第一顺序添加字段

mysql> alter table teacher add age int not null first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| age   | int(11)      | NO   |     | NULL     |                |
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(10)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
| marry | varchar(20)  | YES  |     | 未婚        |                |
+-------+--------------+------+-----+----------+----------------+
6 rows in set (0.00 sec)

在某个字段后面添加字段after

mysql> alter table teacher add sex varchar(2) after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| age   | int(11)      | NO   |     | NULL     |                |
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(10)  | NO   |     | NULL     |                |
| sex   | varchar(2)   | YES  |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
| marry | varchar(20)  | YES  |     | 未婚        |                |
+-------+--------------+------+-----+----------+----------------+
7 rows in set (0.00 sec)

切记：修改时确认字段位置的关键字只有两个—first和after,!!!没有before之类的

2、删除字段
alter table 表名 drop [column] 字段名；

mysql> alter table teacher drop sex;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| age   | int(11)      | NO   |     | NULL     |                |
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(10)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
| marry | varchar(20)  | YES  |     | 未婚        |                |
+-------+--------------+------+-----+----------+----------------+
6 rows in set (0.00 sec)

注意：删除表的时候可以一次删除多个，但是删除字段的时候不行

3、修改
改名字改类型—alter table 表名 change [column] 原字段名 新字段名 数据类型 []；

mysql> alter table teacher change age Age varchar(20) default '永远18';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| Age   | varchar(20)  | YES  |     | 永远18      |                |
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(10)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
| marry | varchar(20)  | YES  |     | 未婚        |                |
+-------+--------------+------+-----+----------+----------------+
6 rows in set (0.01 sec)

只修改数据类型
语法 alter table 表名 modify Age int ;

mysql> alter table teacher modify Age int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe teacher;
+-------+--------------+------+-----+----------+----------------+
| Field | Type         | Null | Key | Default  | Extra          |
+-------+--------------+------+-----+----------+----------------+
| Age   | int(11)      | YES  |     | NULL     |                |
| id    | int(11)      | NO   | PRI | NULL     | auto_increment |
| name  | varchar(10)  | NO   |     | NULL     |                |
| phone | varchar(20)  | YES  |     | NULL     |                |
| add   | varchar(100) | YES  |     | 地址不详        |                |
| marry | varchar(20)  | YES  |     | 未婚        |                |
+-------+--------------+------+-----+----------+----------------+
6 rows in set (0.00 sec)

修改引擎
alter table 表名 engine =新引擎名
引擎是存储数据的一种方式，引擎不同数据存储方式不听，主要有三种：innodb 、 mysiam 、 memory。

mysql> alter table teacher engine = myisam;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table teacher \G;
*************************** 1. row ***************************
       Table: teacher
Create Table: CREATE TABLE `teacher` (
  `Age` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `phone` varchar(20) DEFAULT NULL,
  `add` varchar(100) DEFAULT '地址不详' COMMENT '地址',
  `marry` varchar(20) DEFAULT '未婚',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改表名
alter table 表名 rename to 新表名；

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| teacher          |
+------------------+
1 row in set (0.00 sec)

mysql> use itcast;
Database changed
mysql> alter table teacher rename to stu;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| stu              |
+------------------+
1 row in set (0.00 sec)