数据库和数据表创建与管理操作
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 -ADatabase 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)。同时,数据可支持回滚
COMMITSELECT *
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操作可以实现回滚