数据库 2018-03-05

分类: core

Tags: core

数据库

数据库基本概念

1. RDBMS: 关系性数据库管理系统
2. RDB: 基于二维表的数据库
3. 二维表：由表头，行，列，字段名，字段值组成
4. 主流的数据库管理系统

数据库名称	所属公司	版本	是否免费
Oracle	Oracle	Oracle9i,Oracle10g	商业版
DB2	IBM		商业版
Sqlserver	Mircosoft		商业版
mysql	SUN -> Oracle

约束的概念

当数据进入数据库的表之前，可以对数据加一些限制规则，满足条件的数据可以进入表，否则报错。

约束的种类

• 主键约束（primary key）

当给表的一个字段加了主键约束，则这个字段的值不能重复并且不能是NULL值

一张表只能有一个主键

• 外键约束（foreign key）

通过使用公共列在表之间建立一种父子（parent-child）关系，在表上定义的外键可以指向主键或者其他表的唯一键

• 唯一约束（unique）

保证在指定的列中没有重复值，在该表中的每一个值或者每一组值都将是唯一的。

• 检查约束（check）

检查在约束中指定的条件是否得到了满足

• 非空约束（NOT NULL）

防止NULL值进入指定的列，在单列基础上定义，不允许指定列有NULL值

数据库登录及查看命令

mysql数据库登录

$ mysql -u root -p

根据提示输入密码，即可登录成功

注意：-p后无空格，直接回车

查看系统中有哪些数据库

mysql> show databases;

更换当前使用的数据库

mysql> use db_name;

查看当前使用的数据库

mysql> select database(); #使用函数database()

mysql> status; #注意结果中的”Current database:”信息

查看当前使用数据库中所有表的名称

mysql> show tables; #列头信息中可看出当前使用的db，格式为：Tables_in_[db_name]

或者直接用如下命令

mysql> show tables from db_name;

查看二维表的结构

mysql> desc table_name；

mysql> describe table_name;

mysql> show columns from table_name;

mysql> show create table table_name;

或者

mysql> use information_schema;

mysql> select * from columns where table_name=‘table_name’;

查询

select * from table_name; 查看表中所有数据

数据定义语义 DDL

create database db_name;创建数据库

create table table_name(字段名 varchar(20), 字段名 varchar(20));创建表

e.g. : mysql> create table staff_info( id int primary key auto_increment, name varchar(20), age int, dept_id int, identity_num varchar(18), level_id int);

drop table table_name; 删除表

drop database database_name;删除数据库

alter table table_name;修改表

mysql> alter table salary change id staff_id int;#修改列字段名称

mysql> alter table staff_info modify sex varchar(4); #修改列类型;注意：不是任何情况下都可以去修改的，只有当字段只包含空值时才可以修改。

mysql> alter table staff_info add TEL varchar(11); #增加列

mysql> alter table staff_info drop TEL; #删除列

mysql> alter table staff_info drop column TEL; #删除列

mysql> alter table salary rename staff_salary;#更改表名

mysql> rename table staff_salary to salary;

此处有没有想过如何更改数据库的名字？

注意：直接rename数据库的话会导致数据库中表的丢失，当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。

如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。

此处介绍一种方法：

create database new_db_name;

rename table db_name.table1 to new_db_name.table1, db_name.table2 to new_db_name.table2;

drop database db_name; 如果表比较多，可以将上述操作写成shell脚本，可参考如下网址： 安全修改mysql数据库名

alter table staff_info add constraint staff_level foreign key (level_id) references level_info(id); #创建表之后添加外键约束

alter table staff_info add constraint staff_dept foreign key (dept_id) references dept_info(id); #外键必须建立索引（可以为普通，主键，唯一）

alter table staff_info drop foreign key staff_level ; #删除外键约束

alter table level_info modify id int unsigned not null auto_increment primary key; #修改字段约束条件

数据操作语言 DML

• insert 插入数据

insert into salary(base_salary,performance_pay,bonus_pay) values(3000,5000,6000),(5000,8000,10000); #插入部分字段时需要指定字段名称

insert into level_info values(1,’level1’),(2,’level2’); #全部字段插入无需指定字段名

• delete 删除数据

delete from salary where name=’Jhon’; #不加where条件将会删除表中所有记录，操作时一定要小心

• update 更新数据

update salary set bonus_pay=6000 where name=’lisi’;

事务控制语句 TCL

• 事务的概念：
  • 事务（Transaction）是由一系列相关的SQL语句组成的最小逻辑工作单元，这个单元中的数据是一个整体，不可分割。
  • 在程序更新数据库时事务事关重要，因为必须维护数据的完整性。事务由数据操作语言完成，是对数据库所做的一个或多个修改。
• 事务的四大特征：
  • 原子性：事务中的语句是一个逻辑整体，不可分割。
  • 一致性：事务中的语句要么一起执行成功，要么一起失败。
  • 隔离性：一个事务进行DML（insert,delete,update）时,如果没有提交，则数据的变化对另外一个事务而言是不可见的。
  • 持久性：一直保持这些特性。
• 事务控制语言
  • commit 提交：保存已完成的工作
  • savepoint 保存点：在事务中设置保存点，可以回滚到此处,通过保存点做到事务的部分执行成功
  • rollback 回滚：指定回滚到某个保存点位置
  • set transaction: 改变事务选项

e.g.:

set autocommit=off; #关闭自动提交功能，默认自动提交，看不到回滚效果

insert into level_info values(1,’level1’); savepoint a;

insert into level_info values(2,’level2’); savepoint b;

insert into level_info values(3,’level3’); rollback to b;

数据库提交数据有三种类型

1. 显式提交

   用 COMMIT 命令直接完成的提交为显式提交。

2. 隐式提交

   用 SQL 命令间接完成的提交为隐式提交。这些命令是：

   ALTER ， AUDIT ， COMMENT ， CONNECT ， CREATE ， DISCONNECT ， DROP ， EXIT ， GRANT ， NOAUDIT ， QUIT ， REVOKE ， RENAME 。

3. 自动提交

   若把 AUTOCOMMIT 设置为 ON ，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。

   其格式为： set autocommit=on ；

   COMMIT / ROLLBACK这两个命令用的时候要小心。 COMMIT / ROLLBACK 都是用在执行 DML语句（INSERT / DELETE / UPDATE / SELECT ）之后的。DML 语句，执行完之后，处理的数据，都会放在回滚段中（除了 SELECT 语句），等待用户进行提交（COMMIT）或者回滚 （ROLLBACK），当用户执行 COMMIT / ROLLBACK后，放在回滚段中的数据就会被删除。