数据库

1.mysql数据库

　　概念: 数据库抽象了文件操作 ,提供并发安全记录操作的机制 ,mysql中一个库对应一个文件夹 ,一张表对应一个文件 ,表记录一行信息

　　特点: 数据结构化 ,数据由DBMS统一管理控制 ,让数据安全完整并发易恢复

　　引擎: 表默认innodb存储 ,支持事务(一组操作中一处失败全部回滚到最初) ,行级锁

　　SQL语言类型: DDL数据库定义语言 如create drop alter 对库表创建的操作

　　　　　　　　  DML数据库操纵语言 如insert delete update sleect 对表数据操作 

　　　　　　　　  DCL数据库控制语言 如用户权限GRANT REVOKE

 

2.简单常用sql语句

　　create database db1

　　show create database db1　　　　　　　　　　　　　　#查看创建库的属性

　　show databases

　　alter databases 库名 charset 编码格式　　　　　　　　　#修改库编码格式

　　use 库名

　　show tables;

　　create table 表名(字段1 类型,字段2 类型..)

　　desc 表名

　　alter table 表名 modify 字段名 数据类型 　　　　　　　　 #改变表中某字段的数据类型

　　alter table 表名 change 字段名 新字段名 数据类型　　　　 #改变表中某字段的名字和数据类型

　　insert into 表名(字段名，字段名，字段名) values(数据，数据，数据),(数据，数据，数据)；　　 #增添多条表记录

　　select 字段名，字段名 from 表名 where 字段名 = 数据； #条件查看表内容

　　update 表名 set 字段名=数据 where 字段名=数据； #改变匹配条件数据的字段

　　delete from 表名 where 字段名=数据； #删除匹配条件的记录

　　\G格式化

 

3.数据类型(约束字段)

　　数值型

　　　　数值型分为有符号无符号 ,默认有符号可以存储负数 ,无符号添加unsigned参数

　　　　int decimal(整形最大长度 ,小数点后最大长度四舍五入)

　　　　　　insert into  t4(x,y) values(21,12.917),(19.11,19.2111);

　　字符类型

　　　　char定长 ,速度快浪费存储

　　　　varchar边长 ,速度慢节省存储

　　时间类型

　　　　data　　日期

　　　　time　　时间

　　　　datetime　　日期时间

　　　　year　　年份

　　　　　　create table t7(d date,t time,dt datetime,y year);

　　　　　　insert into t7(d,t,dt,y) values(now(),now(),now(),now());　　　　 ###查看对应格式

　　枚举类型enum(单选),集合set(多选)

　　　　create table t11(hostname char(128),label set('redis','log','monitor'),diaodu enum('yes','no'));

　　　　insert into t11(hostname,label,diaodu) values(master01,log redis,yes)

4.数据完整性约束(表结构设计)

　　表字段 null   default   key　分别表示是否允许为空 ,是否有默认值 ,是否有键

　　　　字段  类型  not null

　　　　字段  类型  default  默认值

　　　　字段  类型  值  unique　　#单列唯一

　　　　unique(字段1 ,字段2)　　#组合唯一　　

　　　　create table t2(name char(20) not null,age int(10) default 20,like1 char(20) unique);

　　primary key设置主键,唯一非空

　　　　create table t4(id int,name char(20),age int,primary key(id,age));　　　　#组合主键

　　auto_increment设置自增长

　　　　create table t5(id int auto_increment,name char(20),age int,primary key(id,age));　　#唯一个字段设置自增长,默认值拥有

　　foregin key外键(多变建立关系)

　　　　foreign key(从表字段) references 主表(字段) on update cascade on delete cascade　　#跟随主表发生变化

#一对多使用外键  
主表
create table dep(
    id int primary key auto_increment,
    bumen char(10)
    );
insert into dep(bumen) values('教学部');
insert into dep(bumen) values('财务部');
insert into dep(bumen) values('系统部');
从表
create table emp(
    id int primary key auto_increment,
    name char(20) not null,
    age int default 18,
    dep_id int not null,
    foreign key(dep_id) references dep(id) on update cascade on delete cascade);        #跟随主表改变
insert into emp(name,age,dep_id) values('quguanwen',11,1),('guanwenqu',22,3),('wenguanqu',33,3);
#多对多使用第三张表记录两张表id间关系

　　单表查询

where(常规)（不让用聚合函数）
where常用约束
数值:
1.比较运算符  < > = !=
2.between  80 and 100 
通用:
1.in(匹配1,匹配2...) 
2.like '%_'        #模糊匹配 %任意字符,_任意单个字符  例如:select * from employee where age > 18 and  name like 'w%';
3.and or not 逻辑条件
group by（分组筛选）----每个什么什么就需要分组拉（每个部门有多少人，每个部门平均年龄，每个年龄的平均工资，男女的平均薪资等等）将所有的记录按照某一个字段进行分类(并且只能看被分组的字段了),    group_conncat(字段名)显示重叠记录
查看组内信息需要使用聚合函数
select * from 表 group by 字段名     
select post,count(id) from employee group by post;    将字段分组，还能统计每组个数
select post,avg(salary) from employee group by post;      将字段分组，计算每组某个字段平均值
select sex,count(1) from employee group by sex ;         看看男女每组多少人
select X,聚合函数(Y) from employee group by X;       以X字段分组，并查看每个X组中的Y字段（用X分组，并通过聚合函数查每组中的数据）
内置函数
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数


having (二次筛选)可用聚合函数
select name,salary from employee where name like '程__' having salary > 19000 ;
select post,avg(salary) as A from employee group by post having A > 10000;   将字段分组，计算每组某个字段平均值并显示大于10000的
order by（升序）
select post,avg(salary) as A from employee group by post order by A;     将字段分组，计算每组某个字段平均值并从小到大排列
select post,avg(salary) as A from employee group by post order by A desc;        将字段分组，计算每组某个字段平均值并从大到小排列
limit（限制显示行数）
select post,avg(salary) as A from employee group by post order by A desc limit 1;    将字段分组，计算每组某个字段平均值并从大到小排列并显示一行
select post,avg(salary) as A from employee group by post order by A desc limit 5,10;     将字段分组，计算每组某个字段平均值并从大到小排列并取索引（索引从0开始）5开始后的10条


逻辑顺序执行优先级：
from---where---group by---having---select---distinct(去重)---order by---limit

　　跨表查询

　　　　多表连接查询

　　　　　　select * from 主表,从表 where 主表.id=从表.外键id having 条件;　　　　# 以主表的id与从表中的关联id相等连接查询

　　　　左连接查询

　　　　　　select * from 表1 left join 表2 on 表1.id =表2.外键;　　　　　　　　# 以左表为主列出所有 右表做填充显示 ，如果而这没有关联右边就会显示null ，可以加各种条件　　　

5.pymysql

　　python的mysql驱动 ,通过pymysql模块直接在python操作mysql

　　流程使用:

　　　　1.创建socket连接db

　　　　2.创建游标对象db.cursor()

　　　　3.游标可以完成事务 ,提供的方法有:

　　　　　　cur.execute(命令)　　　　#执行sql命令

　　　　　　curl.fetchall()　　　　　　#打印上面所有输出

　　　　　　db.close()　　　　　　     #关闭socket

　　　　　　db.commit()　　　　　　  #提交数据库

　　　　　　db.rollback()　　　　　　# 回滚事务

import pymysql

db = pymysql.connect("127.0.0.1", "root", "123", "um")　　　　# 地址  用户  密码  库名           都是关键字参数def __init__(self, host=None, user=None, password="", database=None, port=0..)

cur = db.cursor()　　　　　　#创建游标
　　
cur.execute("show tables")　　#执行sql

data = cur.fetchall()　　　　　　　　　　#打印所有输出

print(data)

db.close()
#####################################################################
import pymysql
db = pymysql.connect("127.0.0.1", "root", "123", "um")
cur = db.cursor()   # 创建游标
try :
    cur.execute("drop tables t6")

    sql = """create table t6(
                name char(10),
                age char(10))"""

    cur.execute(sql)
    cur.execute("drop tables t6")
    db.commit() #提交
except:
    db.rollback()   #回滚

db.close()
########################################################################
import pymysql

db = pymysql.connect("127.0.0.1", "root", "123", "um")

cur = db.cursor()  # 创建游标

start = input('匹配开头字母的名字:')
# sql = 'select * from user where name = %s' % ('lucy',)
sql = 'select * from user where name like \'{}\''.format(start + "%")　　#就是字符串 ,直接执行

try:
    cur.execute(sql)
    print(cur.fetchall())
    db.commit()  # 提交
except:
    db.rollback()  # 回滚

db.close()