数据库和数据表创建与管理操作

数据库和数据表创建与管理操作

MySQL中，一个完整的而数据存储过程主要分成4步：

• 创建数据库
• 确认字段
• 创建数据表
• 插入数据

标识符命名规则

• 数据库名、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起来
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据 类型在一个表里是整数，那在另一个表里可就别变成字符型了

创建与管理数据库

创建数据库

使用默认的字符集进行创建

CREATE DATABASE mytest1;

指定字符集进行创建

mysql> CREATE DATABASE mytest2 CHARACTER SET 'gbk'
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| arcsoft            |
| atguigudb          |
| mysql              |
| mytest1            |
| mytest2            |
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

mysql> SHOW CREATE DATABASE mytest2;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| mytest2  | CREATE DATABASE `mytest2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

如果要创建的数据库已经存在，则创建不成功，但不会报错。如果要创建的数据库不存在，则创建成功

mysql> CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'utf8';
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW CREATE DATABASE mytest2;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| mytest2  | CREATE DATABASE `mytest2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

管理数据库

查看数据库的创建信息

mysql> SHOW CREATE DATABASE mytest1;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| mytest1  | CREATE DATABASE `mytest1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

查看当前链接中的数据库

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| arcsoft            |
| atguigudb          |
| mysql              |
| mytest1            |
| mytest2            |
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

切换数据库

mysql> USE atguigudb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

查看数据中保存的数据表

mysql> SHOW tables;
+---------------------+
| Tables_in_atguigudb |
+---------------------+
| countries           |
| departments         |
| emp_details_view    |
| employees           |
| job_grades          |
| job_history         |
| jobs                |
| locations           |
| order               |
| regions             |
+---------------------+
10 rows in set (0.00 sec)

查看当前使用的数据库

mysql> SELECT DATABASE() FROM DUAL;
+------------+
| DATABASE() |
+------------+
| atguigudb  |
+------------+
1 row in set (0.00 sec)

查看指定数据库下保存的数据表

mysql> SHOW TABLES FROM  arcsoft
    -> ;
+-------------------+
| Tables_in_arcsoft |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

修改数据库

更改数据库的字符集

mysql> ALTER database mytest2 CHARACTER SET 'utf8';
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE mytest2;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| mytest2  | CREATE DATABASE `mytest2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

数据库名不能修改，一些可视化工具可以改名，主要是键了新库，将所有的表复制到信标，再删除旧表所完成的

删除数据库

mysql> DROP DATABASE mytest1;
Query OK, 0 rows affected (0.00 sec)

如果删除数据库存在，则删除成功。如果不存在，则结束，不会报错

mysql> DROP DATABASE IF EXISTS mytest2;
Query OK, 0 rows affected (0.00 sec)

创建与管理数据表

创建数据表

需要用户具有创建表的权限

CREATE TABLE IF NOT EXISTS myemp1(
	id INT,
	emp_name VARCHAR(15),
    #varchar定义字符串，必须指明varchar所具有的最大长度
	hire_date DATE	
);

基于现有的表进行创建,查询语句中字段的别名，可以作为新创建的表的字段名称

查询语句中的结构可以比较丰富

CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM atguigudb.employees;

拓展：复制表中的结构，但不包含数据

CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM atguigudb.employees;
where 1=3

管理数据库

查看表结构

DESC myemp1;

查看创建表语句结构

SHOW CREATE TABLE myemp1;

修改表

添加字段

默认添加到表中的最后一个字段对的位置

ALTER TABLE myemp1
ADD salary DOUBLE(10,2);

DESC myemp1;

添加到指定位置

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;

修改字段：数据类型，长度，默认值

修改长度

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25)

修改默认值

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'person'

重命名字段

ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE (10,2)

删除字段

ALTER TABLE myemp1
DROP COLUMN email;

重命名表

RENAME TABLE myemp1
TO myemp3;

ALTER TABLE myemp3
RENAME TO myemp1;

删除表

不光表结构删除掉，同时表中的数据也删除掉，释放表空间

删除表不能撤销，回滚

DROP TABLE IF EXISTS myemp2;

清空表

表示清空表中的所有数据，但是表结构保留

TRUNCATE TABLE myemp2;

COMMIT

COMMIT:提交数据，一旦执行commit操作，则数据将被永久的保存在数据库中，意味着数据将不可进行回滚操作

ROLLBACK

ROLLBACK：回滚数据，一旦执行ROLLBACK，则可以实现数据的回滚。回滚到最近一次COMMIT之后

TRUNCATE TABLE 和DELETE FROM

相同点：都可以实现对表中所有数据删除，并保留表结构

不同点：

• TRUNCATE TABLE:一旦执行此操作，表数据全部清楚。同时，数据将无法回滚
• DELETE FROM :一旦执行此操作，表数据可以全部清除（不带WHERE）。同时，数据可支持回滚

COMMIT

SELECT *
FROM mytest.myemp1;

SET autocommit = FALSE;

DELETE FROM mytest.myemp1;

ROLLBACK;

SELECT *
FROM mytest.myemp1;

阿里开发规范： 【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无 事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。 说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

DDL和DML说明

• DDL 的操作一旦执行，就不可回滚。指令SET autocommit =false 对DDL操作无效(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作将不受SET autocommit=FALSE 影响的)
• DML的操作默认情况，一旦执行，也是不支持回滚。但是，如果在执行DML前，执行 SET autocommit =false ，则执行的DML操作可以实现回滚