mysql ln_mysql

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

关系型数据库

优点

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

易于维护：都是使用表结构，格式一致；

使用方便：sql语言通用，可用于复杂查询；

复杂操作：支持sql，可用于一个表以及多个表之间非常复杂的查询

缺点：

读写性能非常差，尤其是海量数据的高效读写；

固定的表结构，灵活度欠缺；

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

非关系型数据库

优点

格式灵活，存储数据的格式为key，values，文档形式，图片形式等等，使用灵活；

应用场景广泛，关系型数据库只支持基础操作；

速度快：nosql可是使用硬盘或者随机存储器作为载体，关系型数据库只能用硬盘；

高扩展；

成本低：nosql数据库部署简单，基本都是开源软件；

缺点

不提供sql支持，学习和使用成本高；

无事务处理；

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

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

1、文档型

2、key-value型

3、列式数据库

4、图形数据库

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

MySQL是一个关系型数据库管理系统，

MySQL是最流行的关系型数据库管理系统之一，

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

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

进入mysq|

打开cmd,输入

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

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

mysq|>

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

退出mysql

quit; 或 exit;

操作库之前要进行的步骤：

修改加密规则

ALTER USER '用户名' @'localhost' IDENTIFIED BY '密码' PASSWORD EXPIRE NEVER;

修改密码

ALTER USER ’用户名‘ @'localhost' IDENTIFIED WITH mysql native_password BY '密码'；

刷新数据

FLUSH PRIVILEGES;

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

操作库

创建数据库： create database a;

完整创建数据库的写法：create database if not exists test default character set = 'utf8mb4';

显示数据库： show databases;

删除数据库： drop database a;

连接，进入数据库： use 库名；

查看当前使用的数据库：select database();

查看当前数据库包含的表信息： show tables；

操作表

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

完整创建表的写法： create table if exists user(

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

查看表结构： desc 表名；

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

删除表：drop table b;

更改表名：rename table 原表名 to 新表名;

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

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

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

engine 是引擎 charset 是编码格式

ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表'

增加数据

增加全部字段

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

增加自定义字段

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

还有一种用到了set

insert 表名 set 字段1=值1，字段2=值2；

删除数据

delete from 表名 where 条件;

更改数据

update 表名 set 字段1=值1 where 条件;

如果将一个字段设为默认值：

update 表名 set 字段1=default where 条件;

优先级：

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

查看数据

简单查询

select * from student;

select 字段 from student;

去重：

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

函数：IFNULL(x,y)

如果x不是null，IFNULL() 返回的是x，否则返回y

条件查询

select 字段 from 表名 where 条件;

查询字段书否为空

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

查询字段满足条件的字段

select 字段 from 表名 where 字段 + 算数运算 / 逻辑运算;

模糊查询

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

select * from aa where name like '%小%'

范围查询

查询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='男;

方案二:

select gender,count(*) from students group by gender having gender='男;

where和having的区别

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

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

排序查询

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

分页查询

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

一对多表

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

级联操作的类型：

restrict(限制) 抛出异常

cascade 级联

set null 外键设置为空

no action 什么都不做

通过修改创建外键：

第一步：先增加子表字段a_id

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

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

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

多对多表 (和一对多相似，只是创了一张中间表)

修改已存在的表结构：

第一步：先增添字段

第二步：alter table a add foreign key(id) references aa(id)

内连接查询

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

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

注意：

内连接可以没有on，系统会保留所有结果，就是笛卡尔积；

还可以用where代替on，但是效率没有on高；

select * from 左表名 inner join 右表名 on 条件；

左外连接查询

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

select * from 左表名 left join 右表名 on 条件；

右外连接查询

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

select * from 左表名 right join 右表名 on 条件；

自查询【自己关联自己】

select * from grade as a inner join grade as b on 条件；

字符串函数

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

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

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

包含字符个数 select length('abc');

返回几个左端数据 select left('abc123',个数);

返回几个右端数据 select right('abc123',个数);

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

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

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

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

返回从某侧删除remstr后的字符串str select trim(方向【both，leading左，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的年份；

select month(date); 返回date的月份；

select day(date); 返回date的日期；

select hour(time); 返回time的小时数；

select minute(time); 返回time的分钟数；

select second(time); 返回time的秒数；

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

日期计算 select '2016-12-23'+interval 1day;

当前时间 select current_time();

当前日期 select current_date();

当前日期时间 select now();

视图

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

视图就是虚拟表，作用和真实表一样 。

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

drop view 视图名;

事务

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

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

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

【用来管理insert update delete语句】

创建表时设置引擎 alter table '表名'(字段1 类型)engine=innodb

修改表的引擎 alter table '表名' engine=innodb;

事务处理的两种方法：

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

set autocommit=0 禁止自动提交

set autocommit=1 开启自动提交

用begin，rollback，commit实现

begin 开始一个事务

rollback 事务回滚

commit 事务确认

事务测试

回滚

提交

事务的四大特性(ACID)

原子性A：不可分割，不能分开执行

一致性C：几个并发执行的事务，执行结果必须与某一顺序的串行执行的结果相一致

隔离性I：完全相互独立

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

MySQL的事务隔离级别

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

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

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

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

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

与python3交互

简单步骤：

import pymesql

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

第二步：创建游标 cur=conn.cursor()

第三步：执行sql命令cur.execute("......")

第四步：1.提交生效 conn.commit()

2.放弃操作 conn.rollback()

第五步：关闭连接conn.close()

cursor的属性

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

connection 获得当前连接对象

connection对象的方法

close()关闭连接

commit()事务，所以需要提交才会生效

rollback()事务，放弃之前的操作

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

import pymysql

# 建立与数据库的连接

conn = pymysql.connect(host='localhost',port=3306,db='students',user='huhu',password='root')

# 创建游标

cur = conn.cursor()

# 执行操作语句

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

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

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

# sqlite3 可以赋值到一个变量，用变量进行查询； pymysql 只能通过游标取出数据

cur.execute("select * from grade")

a = cur.fetchall()

for i in a:

print(list(i))

conn.close()

import pymysql

conn= pymysql.connect(host='localhost',port=3306,user='huhu',password='root',db='students')

cur=conn.cursor()

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

try:

cur.execute("insert into aaaa values(0,'%')" %'vv')

conn.commit()

except:

conn.rollback()

conn.close()