MySQL

一、mysql(非常重要)

生活中和数据相关的都存在数据库中。

数据库都有哪些类型呢?
    关系型 : oracle(收费) mysql(免费)
    非关系型:redis  MongoDB

SQL: 结构化查询语言,就是对数据库  创建库、创建表、增删改查 等操作
SQL分类:DDL(数据定义)  DML(数据操作)  DQL(数据查询)  DCL(数据控制)  DTL(数据事物)
数据库如何存储数据?
    数据库服务器:mysql  oracle
    数据库:boke  market
    数据表:每一个库里面又有好多的表
    数据字段:一个表会有好多字段(表头),姓名、年龄、性别、身高等
    数据行:一条真实的数据
image.png
安装数据库
    windows安装:配置文件  my.ini
    linux安装: 配置文件    my.conf

基本数据类型
    数据字段,不同的字段要求有不同的数据类型
    整型:
        无符号-有符号,如何表示负数,最高位代表符号位,如果是1,代表负数,如果是0,代表正数
        整数的原码、反码、补码都一样
        原码、反码、补码
        1000 0001    -1的原码
        1111 1110    -1的反码    反码=原码符号位不变,其它按位取反
        1111 1111    -1的补码    补码=原码符号位不变,其他位取反+1=反码+1
        八位:表示无符号 0-255   表示有符号 -128-127
        int   4个字节
        tinyint 1个字节
    浮点:
        decimal(m,d)   其本质存字符串
    字符串:
        char : char(32)  最多255   定长
            中文用户名:周杰伦
            utf8编码:一个汉字需要3个字节
            不管多还是少,只给你32个
        varchar :varchar(32)      变长
            爱新觉罗穆罕默德   24个字节  
            如果字节小于32,那么就为当时字节,如果字节大于32,最多只能是32
        text :用来存储文本内容
    时间类型
        datetime : 日期时间
        timestamp ;时间戳
        int : 时间戳
    复合类型:
        set :多选  比如爱好   篮球-足球-羽毛球
        enum : 单选 性别  男-女-人妖
    autoincrement :自动增长,自增
    default : 默认值,当没有给值的时候,它的值为多少,默认为NULL
    not null : 限制一个字段的值不能为NULL
        如果一个字段不能为空,又没有默认值,那么这个字段必须插入值

DDL:
    SQL语句都要以分号结尾,如果发现指令写错了,添加 \c 清掉所有指令

day12-mysql2

1、DDL(数据定义)

连接数据库
格式:Mysql -h -u 用户名 -p
-h:表示数据库连接的地址,如果是连接本机可不填
-u:表示要登录的用户
-p:表示使用密码登录

注: -p后接密码不要直接写在后面避免有人查找到你的密码

查看数据库
show databases;

创建数据库
create database 数据库名;

删除数据库
drop database 库名;

选中数据库
use 库名;

查看表
show tables;

创建表
create table 表名(字段名1 字段类型,....字段名n 字段类型n);
create table user(username varchar(30), password char(32));

查看表结构(表字段)
desc 表名;
desc user;

查看表创建语句
show create table user;

1.删除表
drop table 表名;
drop table user;

2.清空表
truncate table 表名称;
truncate table dbo.Sys_Test
3、delete from 表名称 where 列名称 = 值
delete from dbo.Sys_Test where test='test'

创建表指定字符集和引擎
查看创建库及表语句
show create database 库名;
show create table 表名;
创建表时指定(每次指定)
存储引擎 engine=InnoDB
创建表时指定字符集default charset=utf8
设置配置文件(统一设置)
存储引擎:default-storage-engine=InnoDB
字符集:character_set_server=utf8

修改字段类型(了解)
alter table 表名 modify 字段名 varchar(20);
alter table user modify username varchar(20);

添加字段
alter table 表名 add 字段名 类型;
alter table user add email varchar(30)
添加字段到指定位置的后面
alter table 表名 add 字段名 字段类型 after 字段名;
alter table user add email varchar(30) after username;
添加id在字段的最前面(int默认11)
Alter table user add id int first;

删除字段
alter table 表名 drop 字段名;
alter table user drop age;

表字段改名
alter table 表名 change 字段原名 字段新名 字段类型;
alter table user change email em varchar(60);

修改表名
alter table 旧表名 rename 新的表名;
alter table user rename new_user;

2、DML(数据操作)

增删改查 ---create update read delta --- 简称:curd操作

(1)首先通过 进入数据库系统--->mysql -uroot -p
(2)创建数据库dudu,--->create database 数据库名;
(3)进入数据库dudu--->use dudu,
(4)然后才是创建表的过程

【注意】所有的符号都是英文。

创建表开始学习增删改
mysql> create table user(
-> id int auto_increment,
-> name varchar(30) not null,
-> money int not null,
-> province varchar(20) default null,
-> age tinyint unsigned not null,
-> sex enum('女', '男') not null,
-> primary key(id)
-> )engine=innodb default charset=utf8;
【注】单选在插入值的时候,可以直接使用1 2,那么1代表女,2代表男,

enum(单选)类型如果不给值,默认是第一个

Set(多选)类型如何插入?
set('吃', '喝', '玩', '睡', '滚')
1 2 4 8 16
4|8|16 如果是后三个,可以这么写
28 也可以加起来,这么写

1.插入记录
insert into 表 values(值1,值2,值n);
insert into star values(1, '王宝强', 1000, '河北' ,32, 2);
说明:向user表中插入值id为1,姓名为王宝强,余额为0,省份为河北,年龄为32,性别为2(男)

2.对指定字段的插入
insert into 表(字段1,字段2,字段n) values(值1,值2,值n);
insert into star(name, money, province, age, sex) values('郭德纲', 1000000, '天津', 43, 0);
说明:向user表中插入值,姓名为郭德纲,余额为1000000,省份为天津,年龄为43,性别为0(男)

如果没有赋值,就为默认值,如果默认值也没有就报错,id不用插入,系统会自动维护

3.插入多个值
insert into star(name, money, province, age, sex)
values('黄晓明', 2000000, '山东', 39, 0),
( 'angelababy', 3000000, '上海', 27, 1),
( '陈赫', 150000, '福建', 31, 0);

【注意】插入数据可以不传值的情况:
(1)自增的字段可以不用传入值(每插入一条该字段的值会自动加1)
(2)有默认值
(3)可为空的字段可不传
说明:插入记录2的方式为更常用的用法

4.删除记录
delete from 表 where 条件;
delete from star where id=5;
说明:删除表star中id为5的行

【注意】:where必须要加,否则后果自负

5.修改记录
update 表 set 字段1=值1,字段2=值2 where 条件;
update star set money=money+3, sex=1 where id=1;
说明:将star表中id为1的行的money设为原来的数字加上3,sex设为1

6.基础查询
select * from 表;
select * from star;
说明:查询star表中所有字段中的所有结果

3、可视化工具

当你去操作数据库的时候,其实有3中方式
(1)使用mysql自带的客户端进行操作
(2)使用一些第三方的可视化工具管理数据库  比如navicate、sqlyog
(3)使用代码去操作数据库,比如python、java、php

4、DQL

query : 查询
模糊查询:like  '%德%'  只要有 德 字的都符合要求
        % 代表任意多的字符
        like '柳_'   叫 柳某 的都符合要求
        _ 代表一个任意字符
limit:
    limit 2 : 在结果集中只要前两个
    limit offset, number : offset代表偏移量,number代表数量
    LIMIT number1 OFFSET number2 : number1 是数量  number2 是偏移量

上网的时候,经常会有分页,每页显示10条,
第一页:select * from table limit 0, 10
第二页:select * from table limit 10, 10
第三页:select * from table limit 20, 10
第n页:select * from table limit (n-1)*10, 10

对于查询或者排序后的结果集,如果希望只显示一部分,使用limit关键字对结果集进行数量限制。


分组:
    字段只能出现分组字段和统计信息,其它的字段出现没有意义
    select province, count(*) from user group by province;
    select province, count(*) as c from user group by province having c>=2;
    【注】having经常跟在group by的后面,where是跟在表的后面
select使用顺序
    SELECT xxx FROM xxx WHERE xxx GROUP BY xxx HAVING xxx ORDER BY xxx LIMIT xxx;
    取出拥有年龄大于30岁的明星个数大于两个且第二多的省份

基础查询
select * from 表;
select * from star;
说明:查询star表中所有字段中的所有结果

指定字段查询
select 字段 from 表;
select id, name, money from star;
说明:查询star表中id, username, money字段

指定字段组合不重复记录
select distinct 字段 from 表;
select distinct age, sex from star;
说明:查询star表中age和sex组合的不重复结果

条件查询
select 字段 from 表 where 条件;
select * from star where age=43;
说明:查询star表中age为43的所有结果

where后可接的条件

image.png

示例:
image.png

结果集排序
select 字段 from 表 order by 字段 排序关键词
select id, name, money from star order by money desc;
说明:查询star表中的id, name, money字段,按照余额进行降序排序
asc--->升序排列,从小到大(默认)
desc--->降序排列,从大到小

多字段排序
select 字段 from 表 order by 字段1 desc|asc, ... ,字段n desc|asc;
select id, name, money from star order by money desc, age asc;
说明:查询star表中的id, name, money字段,按照余额进行降序排序,若余额全都一样,则再使用age进行升序排序

限制查询的结果集
select 字段 from 表 limit 数量;
select id, name, money from star limit 5;
说明:显示前5个记录

限制排序后的结果集
select 字段 from 表 order by 字段 排序规则 limit 数量
select id, name, money from star order by money desc limit 5;
说明:按照money来排序,显示前5个最有钱的记录

结果集区间选择
select 字段 from 表 limit 偏移量,数量
select id, name, money from star limit 0,3;
说明:取从第一条开始的三条记录

常用统计函数

image.png

统计函数使用
select 函数(字段) from 表
select count(id) from star;
说明:查询star表的id总数

分组
select * from 表 group by 字段
select * from star group by province;
说明:按照province进行分组

分组统计
select * from 表 group by 字段
select count(*), province from star group by province;
说明:对分组进行单独统计

结果集过滤
select * from 表 group by 字段 having 条件
select count(province) as result , province from star group by province having result>2;
说明:对province分组并统计总数,将分组结果中大于2的分组显示出来

整体使用SQL

image.png

要求:
1.在star表中找出年龄大于30的明星
2.按照省份分组
3.找到明星个数大于1的省份
4.找到个数第二多的省份及个数

day13-mysql、redis

1、多表联合查询

多表联合
        mysql> create table user(
            -> id int auto_increment,
            -> name varchar(30) not null,
            -> gid varchar(10) default 0,
            -> primary key(id)
            -> )engine=innodb default charset=utf8;

        mysql> create table goods(
            -> gid int auto_increment,
            -> name varchar(30) not null,
            -> price int not null,
            -> category varchar(20) not null,
            -> primary key(gid)
            -> )engine=innodb default charset=utf8;

        mysql> insert into user(name, gid) values('郭德纲', 1),
            -> ('岳云鹏', 2),
            -> ('曹云金', 0),
            -> ('于谦', 3),
            -> ('牛群', 1),
            -> ('冯巩', 1),
            -> ('大兵', 4),
            -> ('马三立', 0),
            -> ('贾玲', 2);
        
        隐式内连接
            select user.name as uname, goods.name as gname from user, goods where user.gid=goods.gid;
        显示内连接
            select * from user as u join goods as g on u.gid=g.gid;
            select u.name as uname, g.name as gname from user as u join goods as g on u.gid=g.gid;
        三表链接格式
            select * from user as u join goods as g on u.gid=g.gid join price as p on g.pid=p.pid


多表联合查询分类
1.内连接--->分为隐式内连接显示内连接
选出两个表中存在连接关系的字段符合连接关系的那些记录。
2.外连接--->分为左连接外右连接
会选出其他不匹配的记录,分为外左连接和外右连接

隐式内连接
select 表1.字段 [as 别名], 表n.字段 from 表1 [别名], 表n where 条件;
select username, name from user, goods where user.gid=goods.gid;
说明:查询用户表中哪些用户购买过商品,并将商品信息显示出来

注意:以上方式称为隐式内连接,因为没有出现join关键字

显式内连接
select 表1.字段 [as 别名],表n.字段 from 表1 inner join 表2 on 条件;
select username, name from user inner join goods on user.gid=goods.gid;
说明:查询用户表中哪些用户购买过商品,并将商品信息显示出来

注意:以上方式的inner关键字换成cross同样可以,其实也可以省略

外连接之左连接
select 表1.字段 [as 别名], 表n.字段 from 表1 left join 表n on 条件;
select * from user left join goods on user.gid = goods.gid;
说明:以左边为主,查询哪些用户购买过商品,并将商品信息显示出来

左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录

外连接之右连接
select 表1.字段 [as 别名], 表n.字段 from 表1 right join 表n on 条件;
select * from user right join goods on user.gid = goods.gid;
说明:以右边为主,查询哪些商品有用户购买,并将用户信息显示出来

右连接:包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录

1.查询没有被购买过的商品
select goods.name from goods left join user on goods.gid=user.gid where user.id is NULL;

  1. 查询哪类商品是销量冠军
    select category, count(*) as c from user join goods on user.gid=goods.gid group by category order by c desc limit 1;

3.查询哪个商品是销量冠军
select goods.name, goods.price, count(*) as c from user join goods on user.gid=goods.gid group by goods.name order by c desc limit 1;

分组只能写分组字段和统计字段,写其它字段报错
    进入mysql执行如下指令
    SET @@GLOBAL.sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

子(嵌套)查询
select 字段 from 表 where 字段 in(条件)
select * from user where gid in (1,3,4); 说明:按照id 查询指定用户
select * from user where gid in (select gid from goods); 说明:将购买过商品的用户信息显示出来

记录联合
select 语句1 union[all] select 语句2
左连接 union 右连接; 说明:将商品表中的信息和用户表中的信息的组合在一起

使用 union 和 union all 关键字,将两个表的数据按照一定的查询条件查询出后,将结果合并到一起显示。两者主要的区别是把结果直接合并在一起,而 union 是将 union all 后的结果进行一次distinct,去除重复记录后的结果。

修改更新
update 表名 set 字段1=值1, …, 字段n=值n where 条件
update money set balance=balance-500 where userid = 15;
说明:修改money表,将balance余额减500。要求userid为15

两个表同时更新
update 表1,表2 set 字段1=值1, …, 字段n=值n where 条件
update user u, goods g set u.gid=0, g.price=1799 where u.id=8 and u.gid=g.gid;
说明:user表的别名设置为u,将goods表的别名设置为g;将u表的gid改为0,g表的price改为1799。执行条件是:u.id = 8并且gid相等

清空表
truncate table 表名 清空表,id会从1开始自增
delete from table 清空表,id从上次记录的值开始

注意:delete删除表中的所有数据,自增的id会继续从原有的值增加

创建用户
mysql -h ip地址 -u用户名 -p
Create user ‘用户名’@‘用户地址’ identified by ‘密码’;
create user 'test'@'ip' identified by '密码'
说明:创建用户,并且设置密码

删除用户
drop user ‘用户名’@‘用户地址’;
drop user ‘test’@‘192.168.10.1’;

授予权限
grant 权限 on . to ‘用户名’@‘用户地址’;
grant 权限 on . to ‘test’@‘192.168.10.1’;

剥夺权限
revoke 权限 on . from ‘用户名’@‘用户地址’;
revoke 权限 on . from ‘test’@‘192.168.10.1’;

2、索引

索引是什么?索引就是个东西,类似是目录的东西
数据库在查询的时候,是一条一条挨着查呢。查询效率太低了。
如何提高查询效率?索引
如果在查询的时候,经常通过某个字段查询,那就要考虑给这个字段添加索引。
索引的缺点:在插入的时候效率就比较低

索引有类型
普通索引
唯一索引 要确保唯一性
主键索引 是特殊的唯一索引,但是不能为空
全文索引
查看索引
show index from tablename;
show index from user;
查看user表的索引结构
普通索引

索引

唯一索引

image.png

主键索引

image.png

全文索引

image.png

删除索引

image.png

3、数据库导入导出

数据库里面的表可以导出来,导出来一般都是sql脚本的东西,  data.sql
【注】版本,通过指令导出和通过不同的可视化工具导出,格式也不一样。
mysql自带的指令:
    正常终端模式下
    导出:mysqldump -uroot -p 数据库名>c:\data.sql
    导入:mysql -uroot -p 数据库名<c:\data.sql
    注意,导入之前首先创建一个新的数据库
Navicat导出和导入:
    右键==》转储sql文件
    右键==》运行sql文件

4、python操作数据库

需要使用到第三方库:pymysql,在python交互界面输入 import pymysql 看有没有报错,报错了就是没有它,没报错就是有它
安装之:pip install pymysql
代码操作之
    port只能是整型
import pymysql

# 第一步,连接数据库,类似于指令里面的  mysql -uroot -p
# mysql -h地址 -uroot -p
'''
连接数据库需要用到的参数
主机:host
端口:port
用户名:user
密码:password
指定数据库:db
指定字符集:charset
'''
db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='dudu', charset='utf8')
# 连接成功之后,得到一个对象
# print(db)
# 首先根据db得到游标,游标就是执行sql语句用到的东西
# cursor = db.cursor()

# 给cursor添加一个参数,让其的到数据是一个字典
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
# 准备sql语句,执行sql语句
sql = 'select * from star'
# 返回结果rows是受影响的行数
rows = cursor.execute(sql)
# print(rows)
# 通过cursor的方法得到数据
# 返回一个元组,元组里面每个元素的值对应的就是数据表中每个字段对应的值
# 获取内容的时候,里面有个迭代器在记录你的位置
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchmany(5))
# print(cursor.fetchmany(5))

# 元组里面套元组
# print(cursor.fetchall())

# 打印得到所有的用户名
ret = cursor.fetchall()
for obj in ret:
    print('我叫%s,我来自%s,我有%s¥' % (obj['name'], obj['province'], obj['money']))
# print(ret)
# 遍历每一个元组
# for tp in ret:
#     print(tp[1])

# 最后呀,要记得关闭
cursor.close()
db.close()
import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='dudu', charset='utf8')
# print(db)
cursor = db.cursor()
sql = 'select * from start'

# 查询语句,通过try-except,让代码更加健壮
try:
    ret = cursor.execute(sql)
    print(cursor.fetchall())
except Exception as e:
    print(e)
finally:
    cursor.close()
    db.close()
import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='dudu', charset='utf8')
cursor = db.cursor()

'''
# 准备sql语句
obj = {'name': '李云龙', 'money': '20', 'province': '河南', 'age': 36, 'sex': '男'}
# 注意,这里的引号需要加
sql = 'insert into star(name, money, province, age, sex) values("%s", "%s", "%s", "%s", "%s")' % (obj['name'], obj['money'], obj['province'], obj['age'], obj['sex'])
'''

# id = 8
# sql = 'delete from star where id=%s' % id

sql = 'update star set name="马德华" where id=12'

# 注意,没有添加进去,是因为没有提交,需要提交才能成功
try:
    cursor.execute(sql)
    # 提交,写入磁盘
    db.commit()
except Exception as e:
    print(e)
    # 回滚到最初始的状态
    db.rollback()
finally:
    cursor.close()
    db.close()

import pymysql

class MyMysql(object):
    def __init__(self, host, port, user, password, db, charset):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.charset = charset
        # 链接数据库
        self.connect()
    
    def connect(self):
        # 链接数据库和获取游标
        self.db = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.db, charset=self.charset)
        self.cursor = self.db.cursor()
    
    def run(self, sql):
        ret = None
        try:
            ret = self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            self.db.rollback()
        finally:
            self.close()
        return ret
            
    def close(self):
        self.cursor.close()
        self.db.close()
    
    def insert(self, sql):
        return self.run(sql)
    
    def update(self, sql):
        return self.run(sql)
    
    def delete(self, sql):
        return self.run(sql)
    
    def read_one(self, sql):
        ret = None
        try:
            self.cursor.execute(sql)
            # 获取得到数据
            ret = self.cursor.fetchone()
        except Exception as e:
            print('查询失败')
        finally:
            self.close()
        return ret
    
    def read_many(self, sql):
        ret = None
        try:
            self.cursor.execute(sql)
            # 获取得到数据
            ret = self.cursor.fetchall()
        except Exception as e:
            print('查询失败')
        finally:
            self.close()
        return ret

5、redis安装和学习

什么是redis?是一个基于内存的数据库,计算机里面,有内存,有硬盘
文件都在硬盘中存放,代码在运行的时候,有一个变量a
内存:读写快,但是断电消失,不大
硬盘:读写慢,但是可以持久化保存,大,随便存
什么是NoSQL?  not only sql  非关系型数据库
键值对,根据键立马就可以得到值
redis、MongoDB

redis官网:redis.io   redis.cn
5种数据类型:字符串(string)、列表(list)、集合(set)、哈希(hash)、有序集合(zset)
redis官网只有linux版本的,不支持windows

学习方式:指令交互,可视化,代码操作

推荐阅读更多精彩内容