数据库基础入门

一、基本概念

目前比较流行的开源数据库是mysql，掌握好mysql，其它类型数据库原理也类似。

mysql数据库使用sql语言进行操作，其规范具有如下特点：

1、sql语言不区分大小写

2、默认是以" ; "结尾（使用delimiter+"特定符号"可以修改结束符，如常用的delimiter $$）

3、sql语句可以换行写，但是同一个单词不能跨行写

4、使用空格和缩进来提高语句的可读性（配合换行进行）

5、单行注释使用 " --"进行注释，多行注释使用“/*               */”进行注释

二、常用命令

数据库是一个简称，真实定义应该是叫做DBMS（数据库管理软件）。所以实际中在一块存储介质上可以存在多个数据库，然后在每个数据库中可以存在多张数据表，每张数据表中存在多条数据，这就是它们的逻辑结构。

1、登陆数据库

安装好数据库后使用如下命令登陆数据库：

mysql -uroot -p

按要求输入密码后即可登陆。

2、创建及使用数据库

  SHOW DATABASES; --显示所有存在数据库
  CREATE DATABASE IF NOT EXISTS example;--如果数据库example不存在，则创建它
  DROP DATABASE example;--删除数据库example
  SHOW CREATE DATABASE example;--显示创建数据库example时的原始信息
  ALTER DATABASE example CHARACTER SET UTF8;--修改数据库字符编码
  USE example;--切换到example数据库中进行操作
  SELECT DATABASE();--显示当前所在数据库
  SELECT NOW();--显示当前系统时间

3、创建数据表以及数据表的增删改查

(1)数据表的增删改操作

  CREATE TABLE exampleTable1(        --创建数据表exampleTable1
     id TINYINT PRIMARY KEY AUTO_INCREMENT,  --定义id字段，类型为tinyint，设为主键，自动增长
     name VARCHAR(20),--定义name字段，类型为varchar占用20个字节
     age  INT ,-- 定义age字段 ，类型为int
     salary FLOAT (10,2), --定义salary字段，类型为float，一共十位数，小数位占两位
     infor VARCHAR(30) DEFAULT "无"-- 定义infor字段，类型varcahr，占用30个字节，默认值为无

  );


DESC exampleTable1;-- 显示数据表exampleTable1的结构
ALTER TABLE exampleTable1 ADD gender TINYINT AFTER name;--给数据表exampleTable1增加gender 字段，类型为tinyint ，并将其列在name后，另外还有一个参数是first，置于首位
ALTER TABLE exampleTable1 DROP infor;-- 删除数据表exampleTable1的infor字段
ALTER TABLE exampleTable1 MODIFY age TINYINT DEFAULT 18;--更改数据表exampleTable1的age字段的属性，设置默认值为18
ALTER TABLE exampleTable1 CHANGE age old INT;--将数据表exampleTable1的age字段名更改为old
RENAME TABLE exampleTable1 TO exampleTable2;--将数据表exampleTable1的名字改为exampleTable2
DROP TABLE exampleTable2; --删除数据表exampleTable2

INSERT INTO exampleTable1(id,name,age,salary,infor)VALUES(1,'a1',22,3000,'like apple'),-- 往数据表exampleTable1中插入两条记录
                                                          (2,'a2',23,4000,'like banlana');
UPDATE exampleTable1 SET salary=5000 WHERE id=2;--更改id=2的记录中的salary字段值为5000
DELETE FROM exampleTable1 WHERE id=2;--删除数据表exampleTable1中id=2的记录
DELETE FROM exampleTable1 ;--一条一条的将数据表exampleTable1中的记录删掉
TRUNCATE TABLE exampleTable1;--直接将整个数据表exampleTable1都删掉然后重新建一张新的同名同字段的数据表

SELECT * FROM exampleTable1 WHERE id>1;
/* 这里>可以换成>=、<=、!、between、in、like等，然后前面的*部分可以增加distinct字段，用以实现去重的功能*/

 （2）数据表的分组排序操作

SELECT * FROM exampleTable1 WHERE salary >1000 ORDER BY salary; --按照工资升序将大于1000的记录筛选出来
SELECT * FROM exampleTable1 WHERE salary >1000 ORDER BY 6 DESC;--按照第六个字段降序将大于1000的记录筛选出来
SELECT SUM(id+age) FROM exampleTable1 WHERE salary >1000 ORDER BY salary;-- 默认按照薪水升序排列，并且将大于1000的显示出来
SELECT age,SUM(salary) FROM exampleTable1 GROUP BY age HAVING SUM(salary)>10000;--按年龄分组，并且将每组的工资总和大于10000的筛选出来
SELECT * FROM exampleTable1 LIMIT 3,1--将数据表的第三条后的一条筛选出来，如果只跟一个数字，则是将数据表的前n条数据筛选出来

总结：在数据库mysql语句中，语句执行顺序为from->where->select->group by->having->order by。另外在配合分组时的操作还有几个聚合函数分别是sum、avg、max、min、count。

（3）外键与主键

在数据表中，外键的作用是形成约束，在主键删除时会报错。并且外键一定要跟关联的主键类型完全保持一致。

如在数据表example2中定义外键key2,并将其关联到数据表example1中的主键key1，则定义如下：

CREATE TABLE exampleTable2(
  key2 TINYINT PRIMARY KEY AUTO_INCREMENT ,
  FOREIGN KEY(key2) REFERENCES exampleTable1(key1)
)

但是当设置外键后又想要在删除主键表中的记录时同步删除外键表中的记录则可以设置级联删除，则只需要在添加外键时增加下列语句：

CREATE TABLE exampleTable2(
  key2 TINYINT PRIMARY KEY AUTO_INCREMENT ,
  FOREIGN KEY(key2) REFERENCES exampleTable1(key1) ON DELETE CASCADE
)

（4）数据表查询之内连接查询和外连接查询

首先介绍一下笛卡尔积，如下所示：

SELECT * FROM exampleTable1,exampleTable2;--显示两个表乘积的结果

内连接查询，即是在笛卡儿积的基础上增加where约束条件，如下所示：

SELECT * FROM exampleTable1,exampleTable2 WHERE exampleTable1.id=exampleTable2.id;--增加两个表中id相等的约束条件

外连接查询，包括左外连接和右外连接。分别是left join和right join。语句规范如下：

SELECT * FROM exampleTable1 RIGHT JOIN exampleTable2 ON exampleTable1.key1 = exampleTable2.key2--右外连接以左表为主，全部显示左表，根据左表中的每一项对应去右表中查找

子查询，判断是否存在：

SELECT * FROM exampleTable1 WHERE id IN (1,2,3)--判断表中是否存在id为1，2，3的数据项
SELECT * FROM exampleTable1 WHERE EXISTS (id=1)--根据结果返回true或者false

(5)创建维护数据表索引

创建和维护索引的过程非常耗时和耗费资源，但是却可以极大的提高查询的速度。

CREATE TABLE exampleTable1(
  id TINYINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  INDEX name_index(name)   --创建索引name_index，并且将name置为索引
)
CREATE UNIQUE INDEX name_index ON exampleTable1(name);-- 创建索引name
ALTER TABLE exampleTable1 ADD UNIQUE INDEX name_index(name);-- 创建索引name
DROP INDEX name_index ON exampleTable1;--删除索引name

（6）pymysql模块的使用

在python中，使用pymysq模块可以实现跟在命令行中运行mysql语句一样的功能，以下是其语法实现：

##导入模块
import pymysql
#连接
coon=pymysql.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="luqiankun")
#拿到游标对象
cursor=coon.cursor(cursor=pymysql.cursors.DictCursor)
#执行返回ret对象，ret对象包含报错等信息
ret=cursor.execute("select * from emp")
#取出查找对象
cursor.fetchall()#取出所有记录
cursor.fetchmany(3)#取出前三条记录
cursor.fetchone()#取出光标所在的一行记录

#移动光标
cursor.scroll(1,mode="relative")#相对当前位置移动一位
cursor.scroll(3,mode="absolute")#相对第一行移动3位

#提交关闭，在mysql中存在事务机制，用来防止执行一系列连锁操作时发生错误造成数据错误的情况
coon.commit()
cursor.close()
coon.close()

（7）数据库事务处理

事务中，使用start transaction 开启事务，rollback回滚事务到指定还原点，save point建立还原点。如果没有还原点，则直接回到start transction 。如果有还原点，则回到建立的还原点，并且还原点之后的所有更改、插入，删除操作全部取消。

START TRANSACTION ;--开启事务
DELETE FROM exampleTable1 WHERE id=1;--删除数据表中id为1的记录
SAVE POINT delete1;--设置还原点delete1
DELETE FROM exampleTable1 WHERE id=2;--删除数据表中id为2的记录
ROLLBACK delete1;--回滚到还原点1，撤销删除id为2的操作
COMMIT ;--提交事务处理