mysql数据库及表的基本操作

1. 数据库操作

1.1 创建数据库

# 创建数据库
mysql> create database db_test; 

# 查看创建好的数据库
mysql> show create database db_test\G
*************************** 1. row ***************************
       Database: db_test
Create Database: CREATE DATABASE `db_test` /*!40100 DEFAULT CHARACTER SET utf8 */

# 使用db_test数据库
mysql> use db_test;

1.2 删除数据库

mysql> drop database db_test;

1.3 显示数据库信息

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_msyql8        |
+--------------------+
6 rows in set (0.01 sec)

2. 数据库表操作

2.1 创建数据库表

创建数据库表的基本语法规则如下：

2.1.1 表的基本创建

# 基本创建tb_emp1表
mysql> create table tb_emp1
(
 	id int(11),
	name varchar(25),
 	deptId int(11),
	salary float
 );
 Query OK, 0 rows affected (0.34 sec)
 
 # 查看所有的表
 mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| tb_emp1           |
+-------------------+
1 row in set (0.01 sec)

2.1.2 对表字段进行约束

方式一：

CREATE TABLE tb_emp2
(
	id INT(11) PRIMARY KEY AUTO_INCREMENT, 	# 主键，自增约束（自增字段必须为整数）
	name VARCHAR(25) NOT NULL,				# 不为空约束
	deptId INT(11) UNIQUE,					# 唯一性约束
	salary FLOAT DEFAULT 0.0				# 默认值约束
 );

方式二：

CREATE TABLE tb_emp3
(
	id INT(11) AUTO_INCREMENT,
	NAME VARCHAR(25) NOT NULL,
	deptId INT(11),
	salary FLOAT DEFAULT 0.0,
	PRIMARY KEY(id),# 主键约束
	CONSTRAINT costum_name_for_unique UNIQUE(deptId),# 唯一性约束
	CONSTRAINT costum_name_for_foreign_key FOREIGN KEY (deptId) REFERENCES tb_emp2(id) 
    # 外键约束：定义名称为costum_name_for_foreign_key外键，tb_emp3的deptId作为外键关联到tb_emp2表的主	   键id
 );

2.1.3 查看表的结构

# 查看表的基本结构：desc 表名; 或 describe 表名; 
mysql> describe tb_emp3; 
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(25) | NO   |     | NULL    |                |
| deptId | int(11)     | YES  | UNI | NULL    |                |
| salary | float       | YES  |     | 0       |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

# 查看表详细结构: 末尾+\G目的是清晰直观的显示结果
mysql> show  create table tb_emp3\G
*************************** 1. row ***************************
       Table: tb_emp3
Create Table: CREATE TABLE `tb_emp3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `costum_name_for_unique` (`deptId`),
  CONSTRAINT `costum_name_for_foreign_key` FOREIGN KEY (`deptId`) REFERENCES `tb_emp2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.2 修改数据表

2.2.1 修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>；TO为可选参数，选不选无所谓

mysql> alter table tb_emp2 rename tb_emp4;

2.2.2 修改字段信息

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

# 修改字段name的大小为100字符
mysql> alter table tb_emp4 modify name varchar(100);
Query OK, 0 rows affected (0.92 sec)

# 修改字段名称：将name修改为emp_name并重新指定大小为varchar(50)
mysql> alter table tb_emp4 change name emp_name varchar(50);
Query OK, 0 rows affected (0.75 sec)

2.2.3 添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST| AFTER已存在字段名]；

新字段名为需要添加的字段的名称；“FIRST”为可选参数，其作用是将新添加的字段设置为表的第一个字段；“AFTER”为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面。

# 添加字段managerId
mysql> alter table tb_emp4 add managerId int(10);
Query OK, 0 rows affected (0.61 sec)

# 查看添加结果
mysql> desc tb_emp4;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| emp_name  | varchar(50) | YES  |     | NULL    |                |
| deptId    | int(11)     | YES  | UNI | NULL    |                |
| salary    | float       | YES  |     | 0       |                |
| managerId | int(10)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

2.2.4 修改字段的排列位置

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST| AFTER <字段2>；

2.2.5 删除字段

ALTER TABLE <表名> DROP <字段名>；

mysql> alter table tb_emp4 drop managerId;
Query OK, 0 rows affected (0.69 sec)

2.2.6 修改存储引擎

ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>；

# mysql8默认使用InnoDB存储引擎
mysql> alter table tb_emp1 engine=MyISAM;

# 查看表的信息
mysql> show create table tb_emp1\G
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.2.7 删除表的外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

mysql> alter table tb_emp3 drop foreign key costum_name_for_foreign_key;

2.3 删除数据库表

2.3.1 删除没有被关联的表

DROP TABLE [IF EXISTS] 表1，表2，…表n；

mysql> drop table if exists tb_emp1;  

2.3.2 删除被关联的表

# 相关联的两个表为主表和子表，删除的情况有
# 1.先删除子表，后删除主表
# 2.删除主表，保留子表：可以先删除两个表之间的外键关系，后执行删除操作。