Python笔记（八）MySQL

一、数据库简介

传统记录数据的缺点：

不易保存、备份困难、查找不便

文件：

①使用简单，例如python中的open可以打开文件，用read/write对文件进行读写，close关闭文件

②对于数据容量较大的数据，不能够很好的满足，而且性能较差

③不易扩展

数据库：

①持久化存储

②读写速度极高

③保证数据的有效性

④对程序支持性非常好，容易扩展

数据库就是一种特殊的文件，存储着需要的数据

存放路径：/var/lib/mysql (需要切换到root用户才能打开)

关系型数据库的元素：

①数据行(记录)

②数据列(字段)--能够唯一标记某个字段，称为主键

③数据表(数据行的集合)

④数据库(数据表的集合)

RDBMS：关系数据库管理系统

所谓的关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据

关系型数据库的主要产品：

• oracle：在以前的大型项目中使用,银行,电信等项目
• mysql：web时代使用最广泛的关系型数据库
• ms sql server：在微软的项目中使用
• sqlite：轻量级数据库，主要应用在移动平台

C/S架构，通过SQL语句来操作数据库：

二、SQL与MySQL简介

（1）SQL简介：

SQL是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库

• SQL语句主要分为：
  • DQL：数据查询语言，用于对数据进行查询，如select
  • DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
  • TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
  • DCL：数据控制语言，进行授权与权限回收，如grant、revoke
  • DDL：数据定义语言，进行数据库、表的管理等，如create、drop
  • CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor
• 对于web程序员来讲，重点是数据的crud（增删改查），必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作，其它语言如TPL、DCL、CCL了解即可
• SQL 是一门特殊的语言,专门用来操作关系数据库
• 不区分大小写

（2）MySQL简介：

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，后来被Sun公司收购，Sun公司后来又被Oracle公司收购，目前属于Oracle旗下产品

• 使用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更改功能
• 复制全局事务标识
• 复制无崩溃从机
• 复制多线程从机

开源 免费 不要钱 使用范围广,跨平台支持性好,提供了多种语言调用的 API

是学习数据库开发的首选

（3）MySQL安装：

安装服务器端

            sudo apt-get install mysql-server

服务器用于接收客户端的请求、执行sql语句、管理数据库

服务器端一般以服务方式管理，名称为mysql

启动服务

            sudo service mysql start

查看进程中是否存在mysql服务

            ps ajx|grep mysql

停止服务

            sudo service mysql stop

重启服务

            sudo service mysql restart

配置

• 配置文件目录为/etc/mysql/mysql.cnf

• 进入conf.d目录，打开mysql.cnf，发现并没有配置
• 进入mysql.conf.d目录，打开mysql.cnf，可以看到配置

bind-address表示服务器绑定的ip，默认为127.0.0.1

port表示端口，默认为3306

datadir表示数据库目录，默认为/var/lib/mysql

general_log_file表示普通日志，默认为/var/log/mysql/mysql.log

log_error表示错误日志，默认为/var/log/mysql/error.log

命令行客户端

• 在终端运行如下命令，按提示填写信息

            sudo apt-get install mysql-client

• 详细连接的命令可以查看帮助文档

mysql --help

• 最基本的连接命令如下，输入后回车（-u 用户名 -p密码）

mysql -u root -pmysql

• 查看版本号

            mysql> select version();

• 按ctrl+d或输入 quit 或 exit 退出

图形化界面客户端Navicat

下载或拷贝到Linux，然后解压

            tar -zxvf navicat120_premium_cs_x64.tar.gz 

进入解压后的目录，运行start文件

./start_navicat

选择不安装wine（wine的作用是使得Windows的软件可以在Linux中运行）

选择试用

打开后如果出现乱码，双击打开start_navicat文件，然后修改字符集

找到 export LANG=en_US.UTF-8

改成 export LANG=zh_CN.UTF-8

打开软件，选择连接---输入密码---测试连接

左侧的数据库图表双击才可以启动

新建数据库

三、数据类型和约束

在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束

（一）数据类型

够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间

常用数据类型如下：

• 整数：int，bit
• 小数：decimal
• 字符串：varchar,char
• 日期时间: date, time, datetime
• 枚举类型(enum)：枚举中的下标从1开始，可以直接用下标赋值

特别说明的类型如下：

• decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
• char表示固定长度的字符串，如char(3)，如果填充‘ab‘时会补一个空格为‘ab ‘
• varchar表示可变长度的字符串，如varchar(3)，填充‘ab‘时就会存储‘ab‘
• 字符串text表示存储大文本，当字符大于4000时推荐使用
• 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

更全的数据类型可以参考http://blog.csdn.net/anxpp/article/details/51284106

数值类型(常用)

字符串

日期时间类型

（二）约束

• 主键primary key：物理上存储的顺序
• 非空not null：此字段不允许填写空值
• 惟一unique：此字段的值不允许重复
• 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
• 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
• 说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制

四、数据库操作

链接数据库

SQL语句最后要有分号结尾

mysql -uroot -p
mysql -uroot -pmysql

退出数据库

exit 或 quit 或 ctrl+D

显示数据库版本

            select version();

显示时间

            select now();

查看所有数据库

show databases;

创建数据库

create database 数据库名 charset=utf8;

            create
            database python02 charset=utf8;
createdatabase python01;

查看创建数据库的语句

show create database ...

show createdatabase python02;

删除数据库

drop database 数据库名;

            drop
            database
             python01;

            drop
            database `python-04`; 使用tab键上方的`来分开字符串

查看当前使用的数据库

            select
            database();

使用数据库

use 数据库名

            use python02;

查看当前数据库中所有表

show tables;

创建表

auto_increment 自动增长

not_null 不能为空

primary key 主键

default 默认值

create table 数据表名字 （字段 类型 约束，字段 类型 约束）

            create
            table test1(id int,name varchar(30));

创建class表（id，name）

mysql>createtable class
    -> (id intprimarykeynotnull auto_increment,
    -> name varchar(30) notnull);

查看表的结构

desc 数据表的名字；

            desc test1;

创建student表

mysql>createtable students( 
    -> id int unsigned primarykeynotnull auto_increment,
    -> name varchar(30) notnull,
    -> age tinyint unsigned,
    -> high decimal(5,2),
    -> gender enum(‘男‘,‘女‘,‘无性别‘,‘保密‘) default‘保密‘,
    -> cls_id int unsigned
    -> );

查看创建表的语句

show create table 表名字;

show createtable students;

修改表-添加字段

alter table 表名 add 列名 类型；

            alter
            table students add birthday datetime;

修改表-修改字段

alter table 表名 modify 列名 类型及约束；

            alter
            table students modify birthday date;

修改表-修改字段

alter table 表名 change 原名 新名 类型及约束；

            alter
            table students change birthday birth date default "2000-01-01";

修改表-删除字段

alter table 表名 drop 列名；

            alter
            table students drop high;

删除表

            drop
            table test1;

五、增删改查

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

增加

全列插入

insert into 表名 values(...)；

主键字段可以用0 null default来占位

            insert
            into classes values(0,"大数据");

部分插入

insert into 表名(列名...) values(值...)；

            insert
            into students(name, age) values(‘xq‘,14);

多行插入

            insert
            into students(name, age) values(‘大乔‘,14),(‘孙尚香‘,15);

修改

update 表名 set 列1=值1，列2=值2... where 条件;

不写where就会全改

修改多个字段用逗号隔开

            update students set cls_id=1where id=2;

删除

物理删除

delete from 表名 where 条件；

逻辑删除

用一个字段表示这条信息是否已经不能再使用了

alter table students add is_delete bit default 0;

update students set is_delete=1 where id=6;

查询

查询所有列

            select
            *
            from students;

指定条件查询

            select
            *
            from students where age=14;

查询指定列

字段的顺序和查询时写的顺序一致

            select name,gender from students where age=14;

可以使用as起别名

            select name as "姓名",gender as "年龄" from students where age=14;

六、查询

（1）基本查询

查询所有字段

            select
            *
            from students;

查询指定字段　　

            select name,age from students;

使用as给字段起别名

            select name as 姓名,age as 年龄 from students;

            select students.age,students.gender from students;

使用as给表起别名

            select s.age,s.gender from students as s;

清除重复行

distinct 字段

            select
            distinct gender from students;

（2）条件查询

比较运算符

大于18岁

            select
            *
            from students where age>18;

等于18岁 只有一个=

            select
            *
            from students where age=18;

不等于18岁 不推荐<>，用!=

            select
            *
            from students where age!=18;

逻辑运算符

and 18-28岁的学生信息

            select
            *
            from students where age>18and age<28;

or 18以上或身高超过180的

            select
            *
            from students where age>18or height>=180;

not 不在18以上的女性

            select
            *
            from students wherenot age>18and gender=‘女‘;

（3）模糊查询

like

%表示任意多个任意字符

_表示一个任意字符

查询以小开头的名字

            select name from students where name like "小%";

查询有两个字的名字

            select name from students where name like "__";

查询至少有两个字的名字

            select name from students where name like "__%";

rlike 正则

查询以周开头，伦结尾的名字

            select name from students where name rlike "^周.*伦$";

（4）范围查询

            select name,age from students where age in (12,18,34);

            select name,age from students where age notin (12,18,34);

            select name,age from students where age between18and34;

            select name,age from students where age notbetween18and34;

判断空

            select
            *
            from students where height isnull;

            select
            *
            from students where height isnotnull;

（5）排序

order by 字段

asc从小到大排列，即升序。默认按照列值从小到大排列（asc）

desc从大到小排序，即降序。

查询年龄18-34之间男性，按年龄从小到大排序

            select
            *
            from students where age>18and age<34and gender="男" orderby age;

查询年龄18-34之间女性，按身高从大到小排序

            select
            *
            from students where age>=18and age<=34and gender=2orderby height desc;

查询所有女性，按身高从大到小排序，身高相同则按照年龄从小到大排序

            select
            *
            from students where gender=2orderby height desc,age;

（6）聚合函数

总数

count

女性总数

            select
            count(*) as 总数 from students where gender=2;

最大值

max

            select
            max(age) from students;

最小值

min

            select
            min(age) from students;

求和

sum

所有人年龄总和

            select
            sum(age) from students;

平均值

avg

计算平均年龄

            select
            avg(age) from students;

            select
            sum(age)/count(*) from students;

round(原数，保留小数位数)   四舍五入

            select
            round(avg(age),2) from students;

（7）分组

group by

按照性别分组，查询每种性别的人数

            select gender,count(*) from students groupby gender;

看每一组包括哪些人

            select gender,group_concat(name) from students groupby gender;

计算男性的人数，where写在group前面

            select gender,count(*) from students where gender=1groupby gender;

            select gender,group_concat(name," ",age) from students where gender=1groupby gender;

having 对分组进行条件判断

查询平均年龄超过30的性别

            select gender,group_concat(name) from students groupby gender havingavg(age)>30;

（8）分页

限制查询结果的个数

查询前两个结果

            select
            *
            from students limit 2;

limit start，count

从第7个开始查5个

            select
            *
            from students limit 6,5;

分页

limit （第n页-1）*每页个数，每页个数

但是不能用SQL直接计算，以后在程序中实现

limit必须写在最后

（9）连接查询

（10）自关联

（11）子查询

原文：https://www.cnblogs.com/ysysyzz/p/11604290.html