mysql数据库笔记

时间：2018-10-29 13:09:15 阅读：180 评论：0 收藏：0 [点我收藏+]

Python数据库

1、数据库概念

数据：文本、图片、视频、音频、网页……….

数据库：按照一定的数据格式、结构存储数据的仓库

2、为什么使用数据库存数据？

内存：

优点：存取速度快

缺点：

1、容量小

2、断电后，数据不会保存

文件：

优点：数据可以持久化保存

缺点：

1、读取速度慢

2、编码格式不好控制

数据库：

1、容量大

2、读取速度快

3、统一的编码格式

3、怎样操作数据库？

数据库管理软件 Database Manage system

概念：简称DBMS，是一种操作数据库的软件

使用人员：

1、程序员

2、 DBA Database Administrator 数据库管理员

应用的本质是什么？

对于开发人员：尽可能的用一种友好的方式，将数据传输给用户。

对于用户而言：获取我们想要的数据。

4、数据库管理软件怎样使用数据库？

5、常用数据库管理软件 SQL

1、 MySQL

2、 Oracle

3、 MS SQL Server

4、 Sqlite

6、MySQL

概念：MsSQL是一个关系型数据库管理系统。

优点：开源、免费、不要钱、跨平台性支持好、提供了多种语言调用的API

Aplication interface 应用程序接口。函数

使用范围广。

7、啥叫关系型？

关系模型概念：以行和列的形式进行数据的存储

8、数据库为什么要采用关系模型进行数据存储？

便于用户理解

9、采用关系模型数据库存储的数据

关系模型	数据表
关系	表
属性	字段(列)
域	字段(列)的取值范围
元祖	一行记录（一条记录）

结论：

多行+多列=表

多表=数据库

10、MySQL安装

1、服务端的安装

1、为啥安装服务端？

画图演示

3、安装命令

sudo apt-get install mysql-server

sudo apt isntall mysql-client

sudo apt install libmysqlclient-dev

3、验证安装结果？

1、查看服务是否开启，命令：ps aux|grep mysql

2、 mysql服务相关命令

a) 启动：sudo service mysql start

b) 停止：sudo service mysql stop

c) 重启：sudo service mysql restart

2、客户端的安装

1、为啥安装客户端？

2、安装流程

a) 登录navicat官网下载

b) 将压缩包拷贝ubuntu中进行解压，解压命令：tar zxvf navicat.tar.gz

c) 进入解压目录，运行命令./start

d) 删除./navicat……

3、登录数据库

1、通过客户端软件登录

2、通过命令登录

登录远程主机：mysql [-h 192.168.205.129 –p 3306] –uroot –pmysql（注意：远程登录需要配置）

3、退出客户端：exit quit ctrl+d

4、登录本机：mysql –uroot -pmysql

3、配置msyql服务允许远程登录访问

数据文件夹：

数据库配置文件：

允许mysql远程登录：配置文件：对程序的设置。 config

1、防火墙

2、 sudo apt-get install ufw

3、 sudo ufw enable  ubuntu firewall

4、 sudo ufw status

5、 sudo ufw disable

6、软件

a) 更改配置文件 bind-address

7、权限

a) update `user` set host=”%” where user=”root”

4、重启服务

允许指定用户访问：

GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘10.10.11.12‘ IDENTIFIED BY ‘FEFJay‘ WITH GRANT OPTION; flush privileges;

而IP这里，可以使用%来表示所有IP

第一行中，10.10.11.12是一个ip地址，这个是允许远程访问的IP的值。自行改为其他值。
root是账户名，后面的FEFJay是密码。
即，允许来自10.10.11.12的连接并使用root账户和FEFJay这个密码进行访问。
ALL PRIVILEGES ON 后面的*.*表示所有数据库，即完全访问权限，可以指定为特定数据库。
而IP这里，可以使用%来表示所有IP。

11、MySQL默认数据库介绍

information_schema:元数据

mysql：当前数据库信息

performance_schema:数据库优化

sys:information_schema+performance_schema

SQL：操作数据的语言。结构化查询语言

Summer SUMMER summer

12、创建数据库

1、使用客户端软件

2、使用命令

create database 数据库名

create database 数据库名 character set utf8

show create database 数据库名

3、修改数据库编码

alter database 数据库名 character set utf8

4、删除数据库

drop database 数据库名

5、切换、使用数据库

use 数据库名

6、显示当前数据库

select database()

7、展示所有数据库

show databases

13、创建数据表

1、语法：

create table 表名

(

字段1 字段类型,

字段2 字段类型,

字段3 字段类型……

)

2、MySQL常用字段类型

1、整数

2、小数

Float	Double	Decimal
Float	Double	Decimal
Float(m,n)	Double(m,n)	Decimal(m,n)
7个有效位	15个有效位	28个有效位
Float(5,2)999.99	Decimal(5,2) 999.99

m表示该值得总长度，D表示小数点后的位数

3、字符串

char、varchar、text、blob 超大型的文本，也不会存储到数据库中图片文件

char 固定长度 char(10) 张三

varchar 可变长度 varchar(10) 张三 varchar(2)

地址

图片：

/home/python/abc.jpg

图片的二进制

char 固定长度 char(10) 张三

varchar 可变长度 varchar(10) 张三张三 varchar（2）

char(8) varchar(8)

show full fields from table

4、enum 枚举

enum(‘男’,’女’)

5、日期和时间

date:yy:mm:dd

datetime:yy-mm-dd hh:mm:ss

time:hh:mm:ss

14、练习，创建如下表

练习一：

SID	SNAME	SAGE	SGENDER	SBIR	SADD
1	张三	18	男	1990-01-01	中国北京
2	李四	19	女	1990-01-01	中国北京

练习二：

创建数据库 School

TblStudent学生表

--tSId --学生编号

-- tSName --姓名

-- tSGender--性别

-- tSAddress --地址

-- tSPhone --电话

-- tSAge --年龄

-- tSBirthday --生日

-- tSCardId --身份证号

-- tSClassId --班级Id

TblScore成绩表：

-- tScoreId（成绩id,主键,自动编号）

-- tSId（学生编号）

-- tEnglish（英语成绩）

-- tMath（数学成绩）

TblClass班级表：

-- cId –班级ID

-- cName –班级名称

-- cDesciption –班级描述

TblTeacher老师表：

-- tTId

-- tTName

-- tTGender

-- tTAge

-- tTSalary

pyt-- tTBirthday

***数据导出

mysqldump –uroot –p(注意不要输入密码) 要导出的数据库名到导出的数据库表 > 目标文件.sql

mysqldump –uroot –p school > /home/python/Desktop/目标文件.sql

***数据导入

1、在数据库中创建一个空数据库

2、执行命令：mysql -uroot -p 新数据库的名称 < 要导入的数据库名.sql

***对数据表进行增删改查操作

15、select查询

作用：查询数据表中数据

查询数据表中全部的行和列

查询表的部分列

给查询出来的数据列设置别名

DISTINCT关键字的使用

1、查询数据表中全部的行和列

语法：

1、 select col1,col2,col3….from table

2、 select * from table

2、查询表的部分列

select col1,col2,col3…from table

3、给查询出来的数据列设置别名

select col1 as “别名1”,col2 as ‘别名2’…from table

select col1 ‘别名1’,col2 ‘别名2’….from table

注意多表查询重名问题

4、DISTINCT关键字的使用

作用：消除结果集中的重复数据

语法：select distinct col from table

注意：要求所有的字段都相同才会去重

5、LIMIT关键字的使用

作用：指定结果的显示范围

语法：

1、 select * from table limit m,n

m：起始的位置

n：显示的数量

2、 select * from table limit m

m:从第一条开始共显示m条数据

16、插入数据

所有列都插入值

为特定列插入值

一次性插入多条记录

1、所有列都插入值

语法：insert into table values(v1,v2,v3….)

特点：列值同数，列值同序

2、为指定列插入值

语法：insert into table(col1,col2,col3) values(v1,v2,v3)

特点：指定顺序，列值对应

3、一次性插入多条记录

语法：insert into table(co1,col2,col3…)values

(v1,v2,v3),

(v1,v3,v3)…..

17、修改数据

修改指定数据

修改全部数据

1、修改指定数据

语法：update table set {col1=value1}[…n]where expressioin

2、修改全部数据

语法：update table set {col1=value1}[…n]

18、删除数据

使用delete命令删除数据

使用truncate命令删除数据

使用逻辑删除

1、使用delete命令删除数据

语法：delete from table where expression

2、逻辑删除

3、使用truncate命令删除数据

truncate table

4、区别

1、Delete语句删除数据，自动编号没有恢复到默认值。但是truncate重新设置了自动编号

2、通过truncate语句删除数据表数据，不能根据条件删除，而是一次性删除，delete语句可以根据条件进行删除

3、truncate在清空表中数据的时候，速度要比delete语句快的多

19、对列进行增删改查

增加一列

alter table tablename add 列名数据类型

删除一列

alter table tablename drop 、列名

修改列的数据类型

alter table tablename modify 列名数据类型

修改列的数据类型并且改名

alter table tablename change old_colname new_colname 数据类型

20、约束

问题1：数据冗余

问题二：失去了数据完整性

问题三：数据缺少唯一标识

问题四：失去了实体完整性

问题五：失去了引用完整性

问题六：失去了域完整性

约束概念：限定数据库中数据的一套规则

约束作用：保证数据的准确性、完整性、可靠性、联动性。

数据库常用约束：

主键约束
自动增长
唯一约束
非空约束
默认约束
检查约束
外键约束

主键约束

作用：让数据具有唯一标识

语法：

create table table_primarykey

(

id int primary key

)

特点：自动设置非空约束

自动增长

作用：使数据自动增长，一般给主键设置

语法：

create table table_autoincrement

(

id int primary key auto_increment

)

唯一约束

作用：保证数据的准确性

语法：

create table table_unique

(

qqnumber int unique

)

特点：可以为多列设置唯一约束

非空约束

作用：保证数据不为空

语法：

create table table_notnull

(

name varchar(30) not null

)

默认约束

作用：给字段设置默认值

语法：

create table table_default

(

sex char(2) default ‘男’

)

检查约束

作用：检查数据的完整性

语法：

create table table_check

(

sex char(2) check(‘男’ or ‘女’)

)

create table table_enum

(

sex enum(‘男’,’女’)

)

外键约束

作用：让两表之间产生联动关系

语法：

create table class

(

id int primary key auto_increment,

classname varchar(30) not null

)

create table score

(

id int primary key auto_increment,

chinese_score int not null,

foreign key(id) references class(id)

)

要想删除class表和score表，首先要删除score表

21、为数据增补约束

添加/删除主键约束

1、添加主键约束

语法：alter table table_name add constrain con_name primary key(col_name)

2、删除主键约束

外键约束

1、添加外键约束

语法：alter table table_name add constrain con_name foreign key(col_name) references table(col_name)

2、删除外键约束

检查约束

1、添加检查约束

语法：alter table table_name add constraint con_name check(expression)

2、删除检查约束

默认约束

1、添加默认约束

语法：alter table table_name alter col_name default value

2、删除默认约束

自动增长

1、添加自动增长

语法：alter table table_name modify column col_name type auto_increment

2、删除自动增长

22、约束练习

新建一张表：员工信息表

createtable Employees

(

EmpId int,

EmpName varchar(50),

EmpGender char(2),

EmpAge int,

EmpEmail varchar(100),

EmpAddress varchar(500)

)

createtable Department

(

DepId int ,

DepName varchar(50)

)

--手动删除一列(删除EmpAddress列)

--手动增加一列(增加一列EmpAddr varchar(1000))

--手动修改一下EmpEmail的数据类型(varchar(200))

--手动修改一下EmpEmail的数据类型及名称

--为EmpId增加一个主键约束

--非空约束,为EmpName增加一个非空约束

--为EmpName增加一个唯一约束

--为年龄增加一个默认约束，默认为0

--为年龄增加一个检查约束：年龄必须在-120岁之间，含0岁与120岁。

--创建一个部门表，然后为Employee表增加一个DepId列。

约束查询添加删除添加列删除列

主键约束 alter table tableName add constraint PK_name primary key(列名)

外键约束 alter table tablename add constraint FK_外键的列名_主键的列名 foreign key (外键列名) references 主键表名(主键名)

默认约束

alter table tableName modify 列名类型 default 默认值

唯一约束

alter table tableName add constraint UQ_列名 unique 列名

非空约束

alter table tableName modify 列名类型 not null

检查约束

altert table tableName add constraint CK_列名 check(表达式)

自动增长

alter table tableName modify 列名类型 auto_increment

--增加外键约束

createtable Employees

(

EmpId int,

EmpName varchar(50),

EmpGender char(2),

EmpAge int,

EmpEmail varchar(100),

EmpAddress varchar(500)

Deptid int

)

createtable Department

(

DepId int ,

DepName varchar(50)

)

primary key add constraint

foreign key add constraint

check

unique

modify

not null

default

注意：Department DepId主键问题

--删除主键约束

1、先删除主键的自动增长

2、删除主键约束

alter table Employees drop primary key

--删除外键约束

alter table Employees drop foreign key FK_DepId

--删除唯一约束

alter table Employees drop index UQ_EmpName

23、数据表设计思想

设计流程 QQ号昵称性别得分….

需求分析：根据用户的需求，分析出需要记录的数据

需求设计：根据分析出的数据，设计E-R模型图

详细设计：将E-R模型图转换成数据表

三大范式：使用数据库三大范式的设计思想对数据表进行审核

E-R模型图笛卡尔积

概念：Entity-Relationship,实体关系图

组成元素：

举例，将下面三张表用E-R模型图表示出来

三大范式

概念：三大范式是数据库的一种设计规范，主要用来检查我们的数据库是否存在冗余数据。

第一范式：每一列都具有原子性，也就是不能再分割

第二范式：要求每一个表，只描述一件事情

第三范式：要求表中不存在冗余字段

create table Score

(

sId int primary key auto_increment,

studentId int not null,

english float,

math float

)

create table teacher

(

tId int auto_increment primary key,

tName varchar(50) not null,

tSex char(2),

tAge int,

tSalary decimal

)

create table Class

(

cId int auto_increment primary key,

cName nvarchar(50) not null,

cDesciption text

)

createtable Student

(

SId intauto_incrementprimarykey,

SName varchar(50),

SGender char(2)notnull,

SAddress varchar(300),

SPhone varchar(100),

SAge int,

SBirthday datetime,

SCardId varchar(18)null,

SClassId intnotnull

)

24、条件查询

普通条件查询

模糊查询

查询空值的运算符

1、普通条件查询

语法：select * from table where expression

select * from table where id != 1

where：将查询到的数据，通过where后的expression一条一条的进行筛选，符合要求则显示，不符合要求则去除。

2、模糊查询

语法：

1、between….and….

2、范围查询 in 、or

3、like 通配符 %和_ % 随便 _一个

3、查询空值的运算符

is null is not null

25、数据排序

作用：对查询出的数据进行升序或降序排列

语法：select col11,col2,col3…from table order by order_by_collist[asc/desc]

多列排序：

关注点：升序、降序、优先级

26、数据分组

语法：select col1..col2.. from table Group by col

分组配合排序

注意：如果使用了group by分组，那么select不允许出现其他列，除非这些列包含在分组中。

27、聚合函数

作用：对多条数据做统计功能

注意：在使用聚合函数后，select后不允许出现其他列，除非这些列包含在分组中或者聚合函数中

常用聚合函数

聚合函数与Group by语句配合使用

练习：判断下列sql语句是否正确

select c_class_id,count(*),c_age from t_student group by c_class_id

select c_class_id,count(*),count(c_age) from t_student group by c_class_id

28、Having by语句

作用：having 为group by之后得到数据进行进一步的筛选

类似于select 和 where的关系。Where为select后的数据进行进一步的筛选。

Having 为group by后的数据进行筛选

Select c_gender, avg(c_age) as ‘平均年龄‘ from t_student group by c_gender having 平均年龄 > 40;

综合练习：

order_info表

1、热销售商品排名表【即按照每种商品的总销售数量排序】

根据商品名称进行分组
通过 count() sum() 销售数量总和
排序降序

select 商品名称,sum(销售数量) as ‘总销售数量’ from order_info group by 商品名称 order by 总销售数量 desc

2、请统计销售总结超过400元的商品名称和销售总价，并按照销售总价降序排列

select 商品名称,sum(销售数量*销售价格) as ‘销售总价’from order_info group by 商品名称 having 销售总价>400 order by 销售总价 desc

3、统计每个销售员对“乐百氏果冻”的销售量

1、根据销售员进行分组

2、筛选乐百氏果冻

select 销售员,sum(销售数量) from order_info group by 销售员

29、Limit关键字的使用

语法：

select * from table limit m

select * from table limit m,n

30、sql语句执行顺序

1—from 表名

2—where

3—group by

4—select distinct *

5—having

6—order by

7—limit

Select c_gender, avg(c_age) as ‘平均年龄‘ from t_student where c_age > 10 group by c_gender having 平均年龄 > 40 order by 平均年龄 asc limit 2

31、连接查询

当查询结果的数据来自多张表的时候，需要将多张表连接成一个大的数据集，再选择合适的列进行返回。

内连接：选择两种表中交叉的数据进行返回

左连接：选择左表全部数据以及右边中和左表相同的数据

右连接：选择右表全部数据以及左表中和右表相同的数据

笛卡尔积：两张表数据行的乘积

32、自关联

概念：

让某张表自己和自己进行连接。

思考：

设计省信息的表结构

create table province

(

id int,

ptitle varchar(30)

)

create table city
(

id int,

ptitle varchar(30),

pid int 存储当前城市所属省的id

)

create table xiancheng
(

id int,

ptitle varchar(30),

pid int

)

问题：

如果还有县城的信息表、乡镇的信息表、村子的信息表，怎么做？

解决方案：

把他们放到一张表中。

Create table areas

(

id int,

atitle varchar(100),

pid int

)

解释：

1、省份不需要填写pid，设置为null即可

2、城市的pid，填写所属省份的id

3、这就是自关联，表中的某一列关联了表中的另外一列，但是他们的业务含义是不一样的。

4、在这个表中，不需要改变结构，可以添加线程、乡镇、村子的信息表，只需要在pid填写所属的上级行政区的id即可。

导入数据：

source /home/python/Desktop/areas.sql

33、子查询

概念：

将一个查询结果在另一个查询中使用，称之为子查询。

语法：

select * from (select col1,col2,col3 from table) as t

子查询分类：

独立子查询：

子查询可以独立运行

子查询注意点：

1、如果主查询使用到子查询的数据，则必须给子查询起一个表名。

2、在子查询使用关系运算符的时候要注意，因为子查询有可能返回多个值。

34、SQL演练

创建jing_dong数据库

-- 创建 "京东" 数据库

create database jing_dong charset=utf8;

-- 使用 "京东" 数据库

use jing_dong;

-- 创建一个商品goods数据表

create table goods(

    id int unsigned primary key auto_increment not null,

    name varchar(150) not null,

    cate_name varchar(40) not null,

    brand_name varchar(40) not null,

    price decimal(10,3) not null default 0,

    is_show bit not null default 1,

    is_saleoff bit not null default 0

);



-- 向goods表中插入数据

insert into goods values(0,‘r510vc 15.6英寸笔记本‘,‘笔记本‘,‘华硕‘,‘3399‘,default,default);

insert into goods values(0,‘y400n 14.0英寸笔记本电脑‘,‘笔记本‘,‘联想‘,‘4999‘,default,default);

insert into goods values(0,‘g150th 15.6英寸游戏本‘,‘游戏本‘,‘雷神‘,‘8499‘,default,default);

insert into goods values(0,‘x550cc 15.6英寸笔记本‘,‘笔记本‘,‘华硕‘,‘2799‘,default,default);

insert into goods values(0,‘x240 超极本‘,‘超级本‘,‘联想‘,‘4880‘,default,default);

insert into goods values(0,‘u330p 13.3英寸超极本‘,‘超级本‘,‘联想‘,‘4299‘,default,default);

insert into goods values(0,‘svp13226scb 触控超极本‘,‘超级本‘,‘索尼‘,‘7999‘,default,default);

insert into goods values(0,‘ipad mini 7.9英寸平板电脑‘,‘平板电脑‘,‘苹果‘,‘1998‘,default,default);

insert into goods values(0,‘ipad air 9.7英寸平板电脑‘,‘平板电脑‘,‘苹果‘,‘3388‘,default,default);

insert into goods values(0,‘ipad mini 配备 retina 显示屏‘,‘平板电脑‘,‘苹果‘,‘2788‘,default,default);

insert into goods values(0,‘ideacentre c340 20英寸一体电脑 ‘,‘台式机‘,‘联想‘,‘3499‘,default,default);

insert into goods values(0,‘vostro 3800-r1206 台式电脑‘,‘台式机‘,‘戴尔‘,‘2899‘,default,default);

insert into goods values(0,‘imac me086ch/a 21.5英寸一体电脑‘,‘台式机‘,‘苹果‘,‘9188‘,default,default);

insert into goods values(0,‘at7-7414lp 台式电脑 linux ）‘,‘台式机‘,‘宏碁‘,‘3699‘,default,default);

insert into goods values(0,‘z220sff f4f06pa工作站‘,‘服务器/工作站‘,‘惠普‘,‘4288‘,default,default);

insert into goods values(0,‘poweredge ii服务器‘,‘服务器/工作站‘,‘戴尔‘,‘5388‘,default,default);

insert into goods values(0,‘mac pro专业级台式电脑‘,‘服务器/工作站‘,‘苹果‘,‘28888‘,default,default);

insert into goods values(0,‘hmz-t3w 头戴显示设备‘,‘笔记本配件‘,‘索尼‘,‘6999‘,default,default);

insert into goods values(0,‘商务双肩背包‘,‘笔记本配件‘,‘索尼‘,‘99‘,default,default);

insert into goods values(0,‘x3250 m4机架式服务器‘,‘服务器/工作站‘,‘ibm‘,‘6888‘,default,default);

insert into goods values(0,‘商务双肩背包‘,‘笔记本配件‘,‘索尼‘,‘99‘,default,default);

SQL习题：

查询类型cate_name为 ‘超极本‘ 的商品名称、价格
显示商品的种类
求所有电脑产品的平均价格,并且保留两位小数
显示每种商品的平均价格
查询每种类型的商品中最贵、最便宜、平均价、数量
查询所有价格大于平均价格的商品，并且按价格降序排序
查询每种类型中最贵的类型电脑信息

思路：

1、每种分类最贵的

2、连接所有信息表

3、选择数据

问题：如果删除了某一个商品类型的所有商品会出现什么问题？

创建“商品分类”表

-- 创建商品分类表

create table if not exists goods_cates(

    id int unsigned primary key auto_increment,

    name varchar(40) not null

);

查询goods表中商品的种类
将分组结果写入到goods_cates数据表

insert into goods_cates(name) select cate_name from goods group by cate_name

同步数据表

通过goods_cates数据表来更新goods表
通过create...select来创建数据表并且同时写入记录,一步到位

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

创建“商品品牌表”

-- select brand_name from goods group by brand_name;

-- 在创建数据表的时候一起插入数据

-- 注意: 需要对brand_name 用as起别名，否则name字段就没有值

create table goods_brands (

    id int unsigned primary key auto_increment,

    name varchar(40) not null)

查询goods_brands表中的brand_name

将分组结果写入到goods_brand数据表

同步数据

通过goods_brands数据表来更新goods数据表

update goods  name=b.id;

修改表结构

查看 goods 的数据表结构,会发现 cate_name 和 brand_name对应的类型为 varchar 但是存储的都是数字

desc goods;

通过alter table语句修改表结构

alter table goods

change cate_name cate_id int unsigned not null,

change brand_name brand_id int unsigned not null;

外键

分别在 goods_cates 和 goods_brands表中插入记录

insert into goods_cates(name) values (‘路由器‘),(‘交换机‘),(‘网卡‘);

insert into goods_brands(name) values (‘海尔‘),(‘清华同方‘),(‘神舟‘);

在 goods 数据表中写入任意记录

insert into goods (name,cate_id,brand_id,price)

values(‘LaserJet Pro P1606dn 黑白激光打印机‘, 12, 4,‘1849‘);

查询所有商品的详细信息 (通过内连接)

select g.id,g.name,c.name,b.name,g.price from goods as g

inner join goods_cates as c on g.cate_id=c.id

inner join goods_brands as b on g.brand_id=b.id;

查询所有商品的详细信息 (通过左连接)

select g.id,g.name,c.name,b.name,g.price from goods as g

left join goods_cates as c on g.cate_id=c.id

left join goods_brands as b on g.brand_id=b.id;

如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢? 可以使用之前讲过的外键来解决

外键约束:对数据的有效性进行验证

关键字: foreign key,只有 innodb数据库引擎支持外键约束

对于已经存在的数据表如何更新外键约束

·         -- 给brand_id 添加外键约束成功

·         alter table goods add foreign key (brand_id) references goods_brands(id);

·         -- 给cate_id 添加外键失败

·         -- 会出现1452错误

·         -- 错误原因:已经添加了一个不存在的cate_id值12,因此需要先删除

·         alter table goods add foreign key (cate_id) references goods_cates(id);

如何在创建数据表的时候就设置外键约束呢?

注意: goods 中的 cate_id 的类型一定要和 goods_cates 表中的 id 类型一致。

create table goods(

    id int primary key auto_increment not null,

    name varchar(40) default ‘‘,

    price decimal(5,2),

    cate_id int unsigned,

    brand_id int unsigned,

    is_show bit default 1,

    is_saleoff bit default 0,

    foreign key(cate_id) references goods_cates(id),

    foreign key(brand_id) references goods_brands(id)

);

如何取消外键约束

·         -- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称

·         show create table goods;

·         -- 获取名称之后就可以根据名称来删除外键约束

·         alter table goods drop foreign key 外键名称;

35、数据库设计

36、使用pymysql对数据进行增删改查

1、安装pymysql

命令：pip3 install pymysql

2、pymysql执行流程

3、引入模块

在py文件中引入pymysql模块

from pymysql import *

4、Connection 对象

用于建立与数据库的连接
创建对象：调用connect()方法

conn=connect(参数列表)

参数host：连接的mysql主机，如果本机是‘localhost‘
参数port：连接的mysql主机的端口，默认是3306
参数database：数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，推荐使用utf8

对象的方法

close()关闭连接
commit()提交
cursor()返回Cursor对象，用于执行sql语句并获得结果
用于执行sql语句，使用频度最高的语句为select、insert、update、delete
获取Cursor对象：调用Connection对象的cursor()方法

5、Cursor对象

cs1=conn.cursor()

6、对象的方法

close()关闭
execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

对象的属性

rowcount只读属性，表示最近一次execute()执行后受影响的行数
connection获得当前连接对象

制作京东案例：

提供用户 1、查询所有商品信息 2查询所有分类信息

3、查询所有品牌信息

以后一定习惯画图演示代码执行过程

数据的备份和恢复

备份

mysqldump –uroot –p 数据库名 > python.sql

恢复

连接mysql数据库，创建新的数据库

退出连接，执行如下命令

source:

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

37、SQL注入

create table user_info

(

id int unsigned primary key auto_increment not null,

name varchar(40) not null,

password varchar(40) not null

)

insert into user_info values(0,’张三’,’123abc’),(0,’李四’,’abc123’)

sql注入

如果用户输入’#,则会造成sql注入

解决方案：将参数以列表的方式传入execute方法。

38、视图

问题：

如果我有一个很复杂的sql语句，但是我还会经常使用该怎么办？

查询goods和goods_cates和brands_cates的信息。

概念：

1、视图是一个虚拟表。

2、视图同样跟真实表一样包含行和列，但是视图没有在数据库中真实的存储数据

3、视图中行和列的数据都来自于其引用的真实表中行和列的数据

定义视图：

create view 视图名称 as select 语句。

查询goods和goods_cates和brands_cates的信息。

注意列同名问题。

create view v_detail as select a.id,a.name,a.cate_id,a.brand_id,a.price,b.name as ‘b_name‘ ,c.name as ‘c_name‘ from goods as a inner join goods_cates as b inner join goods_brands as c on a.cate_id =b.id and a.brand_id = c.id;

查看视图：

查看所有的表，同时也会将所有的视图显示出来

show tables

使用视图：

select * from 视图名称

删除视图：

drop view 视图名称

好处：

1、安全性

2、方便操作，减少编写复杂的重复的sql语句

3、易于扩展

例：a表、b表删除，使用c表

视图Demo：

查询goods和goods_cates和goods_brands三张表中的信息

create view v_goods_details as select a.name as ‘aname‘,a.price ,b.name as ‘bname‘,c.name as ‘cname‘ from goods as a inner join goods_cates as b on a.cate_id=b.id inner join good

39、事务

为什么会需要事务？

如，转账问题：

将A账户中的10000元，转入到B账户中，需要三步：

检查A的账户是否大于10000元
将A账户中的总金额减少10000元
将B账户中的总金额增加10000元

如果执行完第二步程序出问题了呢？

概念：

所谓事物，他就是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务四大特性（ACID）

原子性
一致性
隔离性
持久性

原子性：

一个事务被看作为一个最小的工作单位，整个事务中的所有操作，要么都提交成功，要么都提交失败，不可能只执行事务中的某一个部分。

一致性：

数据库总是从一个一致性的状态转移到另一个一致性的状态。事务中如果有操作没有提交成功，其他操作也不可能提交成功。

隔离性：

一个事务在提交前，对其他事务是不可见的.

持久性：

一旦事务提交，则其所作的所有修改会永久性保存到数据库中。

开启事务

开启事务后，修改的数据会存储到本地缓存中，而不会修改物理表

begin

或者

start transaction

提交事务

将缓存中的数据变更到物理表中

commit

回滚事务

放弃缓存中的变更数据

rollback

注意点：

1、使用insert、update、delete会自动触发事务。

可以通过set autocommit = 0进行命令行设置

2、表的insert、update、delete可以rollback

表的create/drop 不可以rollback

40、索引

1. 思考

在图书馆中是如何找到一本书的？

一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作)，

而且插入操作和更新操作很少出现性能问题，

遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

2. 解决办法

当数据库中数据量很大时，查找数据会变得很慢

优化方案：索引

3. 索引是什么

索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。

更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

4. 索引目的

索引的目的在于提高查询效率

5. 索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果.

6. 索引的使用

查看索引
创建索引
如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分
删除索引：

show index from 表名;

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

drop index 索引名称 on 表名;

7. 索引demo

7.1. 创建测试表testindex

create table test_index(title varchar(10));

7.2 使用python程序（ipython也可以）通过pymsql模块向表中加入十万条数据

from pymysql import connect

def main():

    # 创建Connection连接

    conn = connect(host=‘localhost‘,port=3306,database=‘jing_dong‘,user=‘root‘,password=‘mysql‘,charset=‘utf8‘)

    # 获得Cursor对象

    cursor = conn.cursor()

    # 插入10万次数据

    for i in range(100000):

        cursor.execute("insert into test_index values(‘ha-%d‘)" % i)

    # 提交数据

    conn.commit()

if __name__ == "__main__":

    main()

7.3. 查询

开启运行时间监测：
查找第1万条数据ha-99999
查看执行的时间：
为表title_index的title列创建索引：
执行查询语句：
再次查看执行的时间

set profiling=1;

select * from test_index where title=‘ha-99999‘;

show profiles;

create index title_index on test_index(title(10));

select * from test_index where title=‘ha-99999‘;

show profiles;

8. 注意：

要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

建立索引会占用磁盘空间

8、 MySQL主从同步配置

1. 主从同步的定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

使用主从同步的好处：

通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

2. 主从同步的机制

Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。

每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。

主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

3. 配置主从同步的基本步骤

有很多种配置主从同步的方法，可以总结为如下的步骤：

在主服务器上，必须开启二进制日志机制和配置一个独立的ID
在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
在开始复制进程前，在主服务器上记录二进制文件的位置信息
如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置

4. 详细配置主从同步的方法

主和从的身份可以自己指定，我们将虚拟机Ubuntu中MySQL作为主服务器，将Windows中的MySQL作为从服务器。在主从设置前，要保证Ubuntu与Windows间的网络连通。

4.1 备份主服务器原有数据到从服务器

如果在设置主从同步前，主服务器上已有大量数据，可以使用mysqldump进行数据备份并还原到从服务器以实现数据的复制。

4.1.1 在主服务器Ubuntu上进行备份，执行命令：

mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

说明

-u ：用户名
-p ：示密码
--all-databases ：导出所有数据库
--lock-all-tables ：执行操作时锁住所有表，防止操作时有数据修改
~/master_db.sql :导出的备份数据（sql文件）位置，可自己指定

4.1.2 在从服务器Windows上进行数据还原

找到Windows上mysql命令的位置

新打开的命令窗口，在这个窗口中可以执行类似在Ubuntu终端中执行的mysql命令

将从主服务器Ubuntu中导出的文件复制到从服务器Windows中，可以将其放在上面mysql命令所在的文件夹中，方便还原使用

在刚打开的命令黑窗口中执行还原操作:

mysql –uroot –pmysql < master_db.sql

4.2 配置主服务器master（Ubuntu中的MySQL）

4.2.1 编辑设置mysqld的配置文件，设置log_bin和server-id

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

4.2.2 重启mysql服务

sudo service mysql restart

4.2.3 登入主服务器Ubuntu中的mysql，创建用于从服务器同步数据使用的帐号

mysql –uroot –pmysql

GRANT REPLICATION SLAVE ON *.* TO ‘slave‘@‘%‘ identified by ‘slave‘;

FLUSH PRIVILEGES;

4.2.4 获取主服务器的二进制日志信息

SHOW MASTER STATUS;

File为使用的日志文件名字，Position为使用的文件位置，这两个参数须记下，配置从服务器时会用到

4.3 配置从服务器slave（Windows中的MySQL）

4.3.1 找到Windows中MySQL的配置文件

4.3.2 编辑my.ini文件，将server-id修改为2，并保存退出。

4.3.3 打开windows服务管理

可以在开始菜单中输入services.msc找到并运行

4.3.4 在打开的服务管理中找到MySQL57，并重启该服务

5. 进入windows的mysql，设置连接到master主服务器

change master to master_host=‘10.211.55.5‘, master_user=‘slave‘, master_password=‘slave‘,master_log_file=‘mysql-bin.000006‘, master_log_pos=590;

注：

master_host：主服务器Ubuntu的ip地址
master_log_file: 前面查询到的主服务器日志文件名
master_log_pos: 前面查询到的主服务器日志文件位置

6. 开启同步，查看同步状态

7. 测试主从同步

在Ubuntu的MySQL中（主服务器）创建一个数据库

在Windows的MySQL中（从服务器）查看新建的数据库是否存在

mysql数据库笔记

原文：https://www.cnblogs.com/jinyanjun/p/9870053.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)

mysql数据库笔记

Python数据库

1、 数据库概念

2、 为什么使用数据库存数据？

3、怎样操作数据库？

4、数据库管理软件怎样使用数据库？

5、常用数据库管理软件 SQL

6、MySQL

7、啥叫关系型？

8、数据库为什么要采用关系模型进行数据存储？

9、采用关系模型数据库存储的数据

10、MySQL安装

1、服务端的安装

1、为啥安装服务端？

3、 安装命令

3、验证安装结果？

2、客户端的安装

1、为啥安装客户端？

2、安装流程

3、登录数据库

3、配置msyql服务允许远程登录访问

数据文件夹：

数据库配置文件：

允许mysql远程登录： 配置文件：对程序的设置。 config

11、MySQL默认数据库介绍

12、创建数据库

1、使用客户端软件

2、使用命令

3、修改数据库编码

4、删除数据库

5、切换、使用数据库

6、显示当前数据库

7、展示所有数据库

13、创建数据表

1、语法：

2、MySQL常用字段类型

1、整数

2、小数

3、字符串

5、日期和时间

14、练习，创建如下表

15、select查询

1、查询数据表中全部的行和列

2、查询表的部分列

3、给查询出来的数据列设置别名

4、DISTINCT关键字的使用

5、LIMIT关键字的使用

16、插入数据

1、所有列都插入值

2、为指定列插入值

3、一次性插入多条记录

17、修改数据

1、修改指定数据

2、修改全部数据

18、删除数据

1、使用delete命令删除数据

2、逻辑删除

3、使用truncate命令删除数据

4、区别

19、对列进行增删改查

增加一列

删除一列

修改列的数据类型

修改列的数据类型并且改名

20、约束

问题1：数据冗余

问题二：失去了数据完整性

问题三：数据缺少唯一标识

问题四：失去了实体完整性

问题五：失去了引用完整性

问题六：失去了域完整性

主键约束

自动增长

唯一约束

非空约束

默认约束

检查约束

外键约束

21、为数据增补约束

添加/删除主键约束

1、数据库概念

2、为什么使用数据库存数据？

3、安装命令

允许mysql远程登录：配置文件：对程序的设置。 config

2、删除自动增长

设计流程 QQ号昵称性别得分….

E-R模型图笛卡尔积