数据库之MySQL

MySQL

MySQL数据库是一种关系型数据库管理系统（Relational Database Management System）由瑞典MySQL AB公司开发，后来被Sun公司收购，Sun公司后来又被Oracle公司收购，目前属于Oracle旗下产品
所谓的关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

特点

• 使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性
• 支持多种操作系统，如Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris等
• 为多种编程语言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等
• 支持多线程，充分利用CPU资源
• 优化的SQL查询算法，有效地提高查询速度
• 提供多语言支持，常见的编码如GB2312、BIG5、UTF8
• 提供TCP/IP、ODBC和JDBC等多种数据库连接途径
• 提供用于管理、检查、优化数据库操作的管理工具
• 大型的数据库。可以处理拥有上千万条记录的大型数据库
• 支持多种存储引擎
• MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库
• MySQL使用标准的SQL数据语言形式
• Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统
• 在线DDL更改功能
• 复制全局事务标识、复制无崩溃从机、复制多线程从机##服务端安装

服务端安装

安装服务端:sudo apt-get install mysql-server
启动服务:sudo service mysql start
查看进程中是否存在mysql服务:ps ajx|grep mysql
停止服务:sudo service mysql stop
重启服务:sudo service mysql restart

数据库类型

char和varchar的区别是：CHAR的长度是固定的，而VARCHAR2的长度是可以变化的， 比如，存储字符串“abc＂，对于CHAR (10)，表示你存储的字符将占10个字节（包括7个空字符），而同样的VARCHAR2 (10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储；CHAR的效率比VARCHAR2的效率稍高。

数据库操作

连接数据库:mysql -u root -p
退出:quit 或是 exit 或是Ctrl+D
查看版本:select version();
显示当前时间:select now();
查看所有数据库:show databases;
使用数据库:use 数据库名;
查看当前使用的数据库:select database();

创建数据库

create database 数据库名 charset=utf8;
例：
create database python charset=utf8;

删除数据库

drop database 数据库名;
例：
drop database python;

数据表操作

查看当前数据库中所有表:show tables;  
查看表结构:desc 表名;

创建表 auto_increment表示自动增长

例：创建班级表
create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(10)
);
例：创建学生表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','人妖','保密'),
    cls_id int unsigned default 0
)

修改表-添加字段

alter table 表名 add 列名 类型;
例：
alter table students add birthday datetime;

修改表-修改字段：重命名版

alter table 表名 change 原名 新名 类型及约束;
例：
alter table students change birthday birth datetime not null;

修改表-修改字段：不重命名版

alter table 表名 modify 列名 类型及约束;
例：
alter table students modify birth date not null;

修改表-删除字段

alter table 表名 drop 列名;
例：
alter table students drop birthday;

删除表

drop table 表名;
例：
drop table students;

查看表的创建语句

show create table 表名;
例：
show create table classes;

MySQL数据库的增删改查（CURD）

curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）

查询

查询所有列

select * from 表名;
例：
select * from classes;

查询指定列 可以使用as为列或表指定别名

select 列1,列2,... from 表名;
例：
select id,name from classes;
select id as 序号, name as 名字, gender as 性别 from students;

消除重复行

select distinct 列1,... from 表名;
例：
select distinct gender from students;

条件查询 可以使用比较运算符（>,<,=,!=或<>,>=,<=）逻辑运算符（and，or，not） 例如：查询编号大于3的女同学

select * from students where id > 3 and gender=0;

模糊查询（like，%表示任意多个任意字符，_表示一个任意字符）
查询姓黄的学生

select * from students where name like '黄%';

查询姓黄并且“名”是一个字的学生

select * from students where name like '黄_';

查询姓黄或叫靖的学生

select * from students where name like '黄%' or name like '%靖';

范围查询（in表示在一个非连续的范围内，between ... and ...表示在一个连续的范围内）
查询编号是1或3或8的学生

select * from students where id in(1,3,8);

查询编号是3至8的男生

select * from students where (id between 3 and 8) and gender=1;

空判断（判空is null，判非空is not null）
查询没有填写身高的学生

select * from students where height is null;

查询填写了身高的男生

select * from students where height is not null and gender=1;

优先级
优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符and比or先运算，如果同时出现并希望先算or，需要结合()使用排序：默认asc（asc从小到大排列，即升序，desc从大到小排序，即降序）

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

聚合函数（总数：count(*)，最大值：max（列），最小值：min（列），平均值：avg（列）求和：sum(列)）
查询男生的总年龄

select sum(age) from students where gender=1;
select sum(age)/count(*) from students where gender=1;

分组：group by：显示性别分组

select gender from students group by gender;

group by + group_concat()：显示性别分组并显示分组有哪些成员

select gender,group_concat(name) from students group by gender;

group by + 集合函数：显示性别并显示每个成员的年龄

select gender,group_concat(age) from students group by gender;

group by + having：having作用和where一样，但having只能用于group by

select gender,count(*) from students group by gender having count(*)>2;

group by + with rollup：在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*) from students group by gender with rollup;

分行：查询前3行男生信息

select * from students where gender=1 limit 0,3;

分页：已知：每页显示m条数据，当前显示第n页，求第n页的数据

select * from students where is_delete=0 limit (n-1)*m,m

连接查询（inner join，left join， right join）
使用内连接查询班级表与学生表

select * from students inner join classes on students.cls_id = classes.id;

增加

全列插入：值的顺序与表中字段的顺序对应

insert into 表名 values(...)
例：
insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2');

部分列插入：值的顺序与给出的列顺序对应

insert into 表名(列1,...) values(值1,...)
例：
insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');

全列多行插入：值的顺序与给出的列顺序对应

insert into 表名 values(...),(...)...;
例：
insert into classes values(0,'python1'),(0,'python2');

insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：
insert into students(name) values('杨康'),('杨过'),('小龙女');

修改

update 表名 set 列1=值1,列2=值2... where 条件
例：
update students set gender=0,hometown='北京' where id=5;

删除

delete from 表名 where 条件
例：
delete from students where id=5;

逻辑删除，本质就是修改操作

update students set isdelete=1 where id=1;

备份

运行mysqldump命令

mysqldump –uroot –p 数据库名 > python.sql;
mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql;

恢复

连接mysql，创建新的数据库 退出连接，执行如下命令

mysql -uroot –p 新数据库名 < python.sql

数据库设计

关系型数据库一般在E-R模型上设计，常用的设计软件有power designer，db desinger等
E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表
R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多

数据库设计遵循三范式(Normal Form)

第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列：如联系人（姓名，联系方式，地址等）
第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分：如订单详情表设计为（【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName））
第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况：如订单表设计为（【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity））

MySQL与Python交互

from pymysql import *

def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='db_name',user='root',password='password',charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()
    conn.commit()
    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()
if __name__ == '__main__':
    main()

为了防止SQL注入，可以使用sql语句的参数化
#非安全的方式
#输入 " or 1=1 or "   (双引号也要输入)

MySQL视图作用

• 提高了重用性，就像一个函数
• 对数据库重构，却不影响程序的运行
• 提高了安全性能，可以对不同的用户
• 让数据更加清晰
  什么是视图
  通俗的讲，视图就是一条SELECT语句执行后返回的结果集，视图是对若干张基本表的引用，一张虚表，查询语句执行的结果
  定义视图

  create view 视图名称 as select语句;
  

  查看时视图

  show tables;
  

  使用视图

  select * from 视图名称;
  

  删除视图

  drop view 视图名称;
  

事物

• 修改数据的命令会自动的触发事务，包括insert、update、delete
• 而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据
  开启事物：开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

  begin; 或是 start transaction;
  

  提交事物：将缓存中的数据变更维护到物理表中

  commit;
  

  回滚事务，命令如下：放弃缓存中变更的数据

  rollback;
  

索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引创建的目的

索引的目的在于提高查询效率，原理类似于B-tree，建立太多的索引也会影响查询效率，并且还会占用磁盘内存   

查看索引

show index from 表名;

创建索引

create index 索引名称 on 表名(字段名称(长度))

删除索引：

drop index 索引名称 on 表名;

账户授权，查看所有用户,在MySQL数据库中

select host,user,authentication_string from user;

创建账户和授权

grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

修改权限

grant 权限名称 on 数据库 to 账户@主机 with grant option;

修改密码

update user set authentication_string=password('新密码') where user='用户名';

刷新权限

flush privileges

删除账户

drop user '用户名'@'主机(%)'