Mysql数据库基础（三） 数据表基本操作

#"xxx":表示数据表名
#数据表属于数据库，创建表之前先创建数据库，并切换到指定数据库，然后再创建数据库

#创建数据表
create TABLE xxx (字段名，数据类型，字段名， 数据类型);
eg.
mysql> create table test (id INT(11), name VARCHAR(25));
Query OK, 0 rows affected (0.02 sec)

#展示当前数据库中有哪些表
show tables;
eg.
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test              |
+-------------------+

#查看数据表结构
 #1.describe(desc)语句可以查看表的字段信息，包括：字段名、数据类型、是否为主键、是否有默认值
 格式：
	describe(desc) xxx;
 eg.
 mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> describe test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

NULL:表示该列是否可以存储NULL值
KEY:表示该列是否已编制索引。PRI表示是该列是主键的一部分；
							UNI表示该列是UNIQUE索引的一部分；
							MUL表示在列中某个定值允许出现多次
Default:表示该列是否有默认值，有的话显示该值
Extra: 表示可以获取的与给定列有关的附加信息，eg。AUTO_INCREMENT等

#查看表详细数据结构，格式：
show create table xxx(xxx\G)
#"\G"：使显示结果更加直观
eg.

mysql> show create table test1\G;
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> show create table test1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+

#修改表名
格式:alter table old_xxx rename new_xxx;
eg.
mysql> alter table test1 rename test5;

#修改字段数据类型
格式:alter table xxx modify 字段名 数据类型；
eg.
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> alter table test modify name varchar(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

#修改字段名,同时可修改数据类型
格式:alter table xxx change 旧字段名 新字段名 数据类型；
eg.
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> alter table test change  name username varchar(30);         
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

#添加字段
格式:alter table xxx add 新字段名 数据类型 
	 约束条件(first | after 已存在字段名)#可以选择性添加
eg.
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
1.添加无完整性约束条件的字段
mysql> alter table test add sex varchar(1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(30) | YES  |     | NULL    |       |
| sex      | varchar(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

2、有约束条件
eg.mysql> alter table test add sex varchar(1) not null;#表示新加的字段不能为空

3、在表的第一列添加一个字段
eg.mysql> alter table test add sex varchar(1) first;

4、在表的指定列后添加一个字段
eg.mysql> alter table test add sex varchar(1) after id;#在id列之后添加性别列

#删除字段
格式:alter table xxx drop 字段名;
eg.
mysql> alter table test drop sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

#修改字段的排列位置
格式:alter table xxx modify 字段1 数据类型 first|after 字段名2
"字段1"：要修改位置的字段，"数据类型": 字段1的数据类型
"first": 将字段1修改为表的第一个字段
"after 字段2": 将字段1插入到字段2后面
eg.
mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> alter table test modify id int(11) after username;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(30) | YES  |     | NULL    |       |
| id       | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(30) | YES  |     | NULL    |       |
| id       | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> alter table test modify id int(11) first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| username | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

#删除数据表
1.删除没有被关联的表
#可以同时删除多个表，用","分割
"if exists"：判断表是否存在，表不存在会警告，但可以继续执行不会结束
格式: drop table if exists 表名；
eg.
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test              |
| test2             |
| test5             |
+-------------------+

mysql> drop table if exists test2,tset6,test5;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test              |
+-------------------+



#主键约束
主键约束要求主键的数据唯一，且不允许为空，能唯一地标识表中的一条记录，
结合外键来定义不同表之间的关系，加快数据查询速度
 #1.单字段主键
 (1)在定义时指定主键,格式
	字段名 数据类型 PRIMARY KEY
 eg.
	mysql> create table test1(id int(11) primary key, name VARCHAR(25));   
 (2)在定义完所有列之后指定主键,格式
	PRIMARY KEY (字段名)
 eg.
	mysql> create table test2(id int(11), name VARCHAR(25), primary key(id));
 #2.多字段联合主键，格式
	primary key(字段名，字段名)
 eg.
	mysql> create table test2(id int(11), name VARCHAR(25), primary key(id,name));