mysqlln_mysql

关系型数据库和菲关系型数据库的区别

关系型数据库

优点

关系型数据库层级结构清晰

易于维护&＃xff1a;都是使用表结构&＃xff0c;格式一致&＃xff1b;

使用方便&＃xff1a;sql语言通用&＃xff0c;可用于复杂查询&＃xff1b;

复杂操作&＃xff1a;支持sql&＃xff0c;可用于一个表以及多个表之间非常复杂的查询

缺点&＃xff1a;

读写性能非常差&＃xff0c;尤其是海量数据的高效读写&＃xff1b;

固定的表结构&＃xff0c;灵活度欠缺&＃xff1b;

高并发读写需求&＃xff0c;传统关系型数据库来说&＃xff0c;硬盘I/O是一个很大的瓶颈&＃xff1b;

非关系型数据库

优点

格式灵活&＃xff0c;存储数据的格式为key&＃xff0c;values&＃xff0c;文档形式&＃xff0c;图片形式等等&＃xff0c;使用灵活&＃xff1b;

应用场景广泛&＃xff0c;关系型数据库只支持基础操作&＃xff1b;

速度快&＃xff1a;nosql可是使用硬盘或者随机存储器作为载体&＃xff0c;关系型数据库只能用硬盘&＃xff1b;

高扩展&＃xff1b;

成本低&＃xff1a;nosql数据库部署简单&＃xff0c;基本都是开源软件&＃xff1b;

缺点

不提供sql支持&＃xff0c;学习和使用成本高&＃xff1b;

无事务处理&＃xff1b;

数据结构相对复杂,复杂查询方面稍欠。

非关系型数据库的分类和比较:

1、文档型

2、key-value型

3、列式数据库

4、图形数据库

MYSQL的概念 mySQL (关 系型数据库管理系统)

MySQL是一个关系型数据库管理系统&＃xff0c;

MySQL是最流行的关系型数据库管理系统之一&＃xff0c;

在WEB应用方面&＃xff0c;MySQL是最好RDBMS(Relational Database Management System,关系数据库管理系统)应用软件之MySQL是一种关系数据库管理系统&＃xff0c;

关系数据库将数据保存在不同的表中&＃xff0c;增加了速度并提高了灵活性。

进入mysq|

打开cmd,输入

>>>mysql -U root(用户名) -p

>>>Enter password: **** #输入密码

mysq|>

mysql -D 数据库名 -u root(用户名) -p

退出mysql

quit; 或 exit;

操作库之前要进行的步骤&＃xff1a;

修改加密规则

ALTER USER &＃39;用户名&＃39; &＃64;&＃39;localhost&＃39; IDENTIFIED BY &＃39;密码&＃39; PASSWORD EXPIRE NEVER;

修改密码

ALTER USER ’用户名‘ &＃64;&＃39;localhost&＃39; IDENTIFIED WITH mysql native_password BY &＃39;密码&＃39;&＃xff1b;

刷新数据

FLUSH PRIVILEGES;

远程连接&＃xff1a; mysql -h 要连接的用户ip地址 -u 用户名 -p 回车输密码

操作库

创建数据库&＃xff1a; create database a;

完整创建数据库的写法&＃xff1a;create database if not exists test default character set &＃61; &＃39;utf8mb4&＃39;;

显示数据库&＃xff1a; show databases;

删除数据库&＃xff1a; drop database a;

连接&＃xff0c;进入数据库&＃xff1a; use 库名&＃xff1b;

查看当前使用的数据库&＃xff1a;select database();

查看当前数据库包含的表信息&＃xff1a; show tables&＃xff1b;

操作表

创建表&＃xff1a;create table b(字段名 数据类型 约束, ...);

完整创建表的写法&＃xff1a; create table if exists user(

id int(10) unsigned【unsigned指的是无符号的类型】 auto_increment key【这里的key指的是主键】comment 【comment指的是注释】&＃39;用户ID&＃39;, ......)engine&＃61;innodb,charset&＃61;utf8mb4;

查看表结构&＃xff1a; desc 表名&＃xff1b;

查看表的创建语句: show create table a;

删除表&＃xff1a;drop table b;

更改表名&＃xff1a;rename table 原表名 to 新表名;

​ 删除t1表中的id列(不需要指明类型): alter table t1 drop id;

alter table a modify 字段名 数据类型;

alter table a change 字段1 字段2 类想

engine 是引擎 charset 是编码格式

ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;4 DEFAULT CHARSET&＃61;utf8mb4 COLLATE&＃61;utf8mb4_0900_ai_ci COMMENT&＃61;&＃39;用户表&＃39;

增加数据

增加全部字段

insert into 表名 values(值....);

增加自定义字段

insert into 表名(字段1, 字段2...) values(值1, 值2...);

还有一种用到了set

insert 表名 set 字段1&＃61;值1&＃xff0c;字段2&＃61;值2&＃xff1b;

删除数据

delete from 表名 where 条件;

更改数据

update 表名 set 字段1&＃61;值1 where 条件;

如果将一个字段设为默认值&＃xff1a;

update 表名 set 字段1&＃61;default where 条件;

优先级&＃xff1a;

from > where > group by > 聚合函数 > having > order by

查看数据

简单查询

select * from student;

select 字段 from student;

去重&＃xff1a;

select distinct(去重) 字段1,字段2 from 表名;

函数&＃xff1a;IFNULL(x,y)

如果x不是null&＃xff0c;IFNULL() 返回的是x&＃xff0c;否则返回y

条件查询

select 字段 from 表名 where 条件;

查询字段书否为空

select 字段 from 表名 where 字段 is null / not nul; 是否为空

查询字段满足条件的字段

select 字段 from 表名 where 字段 &＃43; 算数运算 / 逻辑运算;

模糊查询

查询名字里包含小的所有字段

select * from aa where name like &＃39;%小%&＃39;

范围查询

查询id为1或3或6的学生的所有信息

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

查询学生id为3到6的学生的所有信息(包含6)

select * from students where id between 3 and 6;

聚合查询

select count(字段名) from 表名 where 条件; 查询个数

select max(字段名) from 表名 where 条件; 查询最大值

select min(字段名) from 表名 where 条件; 查询最小值

select sum(字段名) from 表名 where 条件; 查询他们的和

select avg(字段名) from 表名 where 条件; 查询平均值

分组查询

select 字段1 from 表名 group by 字段2

分组后的数据筛选

查询男生总人数

方案一

select count(*) from students where gender&＃61;&＃39;男;

方案二:

select gender,count(*) from students group by gender having gender&＃61;&＃39;男;

where和having的区别

where是对from后面指定的表进行数据筛选&＃xff0c;属于对原始数据的筛选

having是对group by的结果进行筛选

排序查询

select 字段1 from 表名 order by 字段1

分页查询

select * from 表名 limit 从第几行开始&＃xff0c;显示几行 【acs 升序 desc 降序】

一对多表

必须在已存在的字段上设置外键

级联操作的类型&＃xff1a;

restrict(限制) 抛出异常

cascade 级联

set null 外键设置为空

no action 什么都不做

通过修改创建外键&＃xff1a;

第一步&＃xff1a;先增加子表字段a_id

第二步&＃xff1a;****alter table 表名 add foreign key(a_id) references 主表名(字段名 eg:id)

constraint 是指定外键名称&＃xff0c;在用的时候会用到 不影响foreign key后面的加入 可写可不写

或者&＃xff1a;****alter table 表名 add constraint iid foreign key a_id references 主表名(字段名 eg:id)

多对多表 (和一对多相似&＃xff0c;只是创了一张中间表)

修改已存在的表结构&＃xff1a;

第一步&＃xff1a;先增添字段

第二步&＃xff1a;alter table a add foreign key(id) references aa(id)

内连接查询

从左表中取出每一条记录&＃xff0c;与右表中的所有记录进行匹配&＃xff0c;

在左表和右表相同的公共部分&＃xff0c;才会保留结果&＃xff0c;否则不保留。

注意&＃xff1a;

内连接可以没有on&＃xff0c;系统会保留所有结果&＃xff0c;就是笛卡尔积&＃xff1b;

还可以用where代替on&＃xff0c;但是效率没有on高&＃xff1b;

select * from 左表名 inner join 右表名 on 条件&＃xff1b;

左外连接查询

包含左边表的全部行(不管右边的表中是否存在与他们匹配的行)&＃xff0c;以及右表中全部匹配的行

select * from 左表名 left join 右表名 on 条件&＃xff1b;

右外连接查询

包含右边表的全部行(不管右边的表中是否存在与他们匹配的行)&＃xff0c;以及左表中全部匹配的行

select * from 左表名 right join 右表名 on 条件&＃xff1b;

自查询【自己关联自己】

select * from grade as a inner join grade as b on 条件&＃xff1b;

字符串函数

查看字符串的ascii码值 【与下面的char可以相互转化】 select ascii(str);

查看ascii码值对应的字符 select char(数字);

拼接字符串 【凭借后都是字符串】 select concat(str1,str2);

包含字符个数 select length(&＃39;abc&＃39;);

返回几个左端数据 select left(&＃39;abc123&＃39;,个数);

返回几个右端数据 select right(&＃39;abc123&＃39;,个数);

从第几个元素开始&＃xff0c;返回几个数 select substring(str,start,len);

返回由n个空格组成的字符串 select space(10);

返回删除了左空格的字符串 select ltrim(str);

返回删除了右空格的字符串 select rtrim(str);

返回从某侧删除remstr后的字符串str select trim(方向【both&＃xff0c;leading左&＃xff0c;trailing右】remstr from str);

替换字符串 select replace(str, start_str, end_str);

大小写转换 select lower(str); select upper(str);

数学函数

求绝对值 select abs(n);

余数 select mod(m,n);

展示不大于n的最大整数 【地板floor(n)】 select floor(n);

求四舍五入值 select round(n【原数】,b【小数位置】);

求幂 select pow(2,3);

获取圆周率 select PI();

随机数 select rand(0-1的浮点数);

日期时间函数

select year(date); 返回date的年份&＃xff1b;

select month(date); 返回date的月份&＃xff1b;

select day(date); 返回date的日期&＃xff1b;

select hour(time); 返回time的小时数&＃xff1b;

select minute(time); 返回time的分钟数&＃xff1b;

select second(time); 返回time的秒数&＃xff1b;

日期格式化 select date_format(date,format),

日期计算 select &＃39;2016-12-23&＃39;&＃43;interval 1day;

当前时间 select current_time();

当前日期 select current_date();

当前日期时间 select now();

视图

【可以使用户操作方便&＃xff0c;并保证数据库系统安全】

视图就是虚拟表&＃xff0c;作用和真实表一样 。

create view 视图名(自定义) as select 字段 from 表名;

drop view 视图名;

事务

提交事务 数据库最小执行的单元

事务是保证每次对数据库操作的准确性和安全性

只有使用了innodb数据库引擎的数据库或表才支持事务

【用来管理insert update delete语句】

创建表时设置引擎 alter table &＃39;表名&＃39;(字段1 类型)engine&＃61;innodb

修改表的引擎 alter table &＃39;表名&＃39; engine&＃61;innodb;

事务处理的两种方法&＃xff1a;

直接用set来改变mysql的自动提交模式

set autocommit&＃61;0 禁止自动提交

set autocommit&＃61;1 开启自动提交

用begin&＃xff0c;rollback&＃xff0c;commit实现

begin 开始一个事务

rollback 事务回滚

commit 事务确认

事务测试

回滚

提交

事务的四大特性(ACID)

原子性A&＃xff1a;不可分割&＃xff0c;不能分开执行

一致性C&＃xff1a;几个并发执行的事务&＃xff0c;执行结果必须与某一顺序的串行执行的结果相一致

隔离性I&＃xff1a;完全相互独立

持久性D&＃xff1a;对于已提交事务&＃xff0c;系统必须保证该事务对数据库的改变不被丢失&＃xff0c;即使数据库出现故障

MySQL的事务隔离级别

未提交读(Read Uncommitted)&＃xff1a;允许脏读&＃xff0c;其他事务只要修改了数据&＃xff0c;即使未提交&＃xff0c;本事务也能看到修改后的数据值。也就是可能读取到其他会话中未提交事务修改的数据

提交读(Read Committed)&＃xff1a;只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。

可重复读(Repeated Read)&＃xff1a;可重复读。无论其他事务是否修改并提交了数据&＃xff0c;在这个事务中看到的数据值始终不受其他事务影响。

串行读(Serializable)&＃xff1a;完全串行化的读&＃xff0c;每次读都需要获得表级共享锁&＃xff0c;读写相互都会阻塞

MySQL数据库(InnoDB引擎)默认使用可重复读( Repeatable read)

与python3交互

简单步骤&＃xff1a;

import pymesql

首先&＃xff1a;建立有数据库的连接conn &＃61; pymysql.connect(host&＃61;连接的mysql主.&＃xff0c;port&＃61;端口&＃xff0c;db&＃61;数据库名称,user&＃61;连接的用户名,password&＃61;密码,charset&＃61;编码方式,autocomment&＃61;是否自动提交&＃xff0c;默认为false)

第二步&＃xff1a;创建游标 cur&＃61;conn.cursor()

第三步&＃xff1a;执行sql命令cur.execute("......")

第四步&＃xff1a;1.提交生效 conn.commit()

2.放弃操作 conn.rollback()

第五步&＃xff1a;关闭连接conn.close()

cursor的属性

rowcount返回数据条数或影响行数

connection 获得当前连接对象

connection对象的方法

close()关闭连接

commit()事务&＃xff0c;所以需要提交才会生效

rollback()事务&＃xff0c;放弃之前的操作

cursor()返回Cursor对象&＃xff0c;用于执行sql语句并获得结果

import pymysql

# 建立与数据库的连接

conn &＃61; pymysql.connect(host&＃61;&＃39;localhost&＃39;,port&＃61;3306,db&＃61;&＃39;students&＃39;,user&＃61;&＃39;huhu&＃39;,password&＃61;&＃39;root&＃39;)

# 创建游标

cur &＃61; conn.cursor()

# 执行操作语句

# pymysql 里的自增要加下划线 sqlite3不加

# pymysql占位符 是 % sqlite3占位符 是 ?

# cur.execute("create table demo13(id int primary key auto_increment, name char(10))")

# sqlite3 可以赋值到一个变量&＃xff0c;用变量进行查询&＃xff1b; pymysql 只能通过游标取出数据

cur.execute("select * from grade")

a &＃61; cur.fetchall()

for i in a:

print(list(i))

conn.close()

import pymysql

conn&＃61; pymysql.connect(host&＃61;&＃39;localhost&＃39;,port&＃61;3306,user&＃61;&＃39;huhu&＃39;,password&＃61;&＃39;root&＃39;,db&＃61;&＃39;students&＃39;)

cur&＃61;conn.cursor()

cur.execute("create table aaaa(id int primary key auto_increment,name char(10))")

try:

cur.execute("insert into aaaa values(0,&＃39;%&＃39;)" %&＃39;vv&＃39;)

conn.commit()

except:

conn.rollback()

conn.close()