python-day42_MySQL数据库

MySQL

单机程序（自己DB）

单机程序（公用DB）

MySQL：是用于管理文件的一个软件
- 服务端软件
- socket服务端
- 本地文件操作
- 解析指令【SQL语句】
- 客户端软件（各种各样）
- socket客户端
- 发送指令
- 解析指令【SQL语句】

PS:
- DBMS数据库管理系统
- SQL语句

技能：
- 安装 服务端和客户端
- 连接
- 学习SQL语句规则；指示服务端做任意操作

其他类似软件：
关系型数据库：sqllite,db2,oracle,access,sql server MySQL
非关系型数据库：MongoDB,redis

 

1. MySQL安装

https://dev.mysql.com/downloads/

环境变量的配置：
E:\wupeiqi\mysql-5.7.16-winx\mysql-5.7.16-winx\bin
mysqld

windows服务：
E:\wupeiqi\mysql-5.7.16-winx\mysql-5.7.16-winx\bin\mysqld --install

E:\wupeiqi\mysql-5.7.16-winx\mysql-5.7.16-winx\bin\mysqld --remove

net start mysql
net stop mysql

 

2. 关于连接

文件夹【数据库】
　　文件【表】
　　　　数据行【行】
　　　　数据行
　　　　数据行

连接：

默认：用户root


show databases;

use 数据库名称;

show tables;

select * from 表名;

select name,age,id from 表名;

mysql数据库user表
use mysql;
select user,host from user;

1)创建用户：
　　create user 'alex'@'192.168.1.1' identified by '123123';
　　create user 'alex'@'192.168.1.%' identified by '123123';
　　create user 'alex'@'%' identified by '123123';
2)授权：
　　权限 人

　　grant select,insert,update on db1.* to 'alex'@'%';
　　grant all privileges on db1.t1 to 'alex'@'%';
　　grant SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE,Show view,Create view,Index on national.* to 'national'@'%' identified by '123456';

　　show grants for fangming;　　# 查看用户权限
　　revoke all privileges on db1.t1 from 'alex'@'%';

 

3. 学习SQL语句规则

操作文件夹
　　create database db2;
　　create database db2 default charset utf8; ***** 建数据库时指定了字符集后，新建表时可以不用再指定字符集
　　show databases;
　　drop database db2;

操作文件
　　show tables;
　　create table t1(id int,name char(10)) default charset=utf8;
　　create table t1(id int,name char(10))engine=innodb default charset=utf8;
　　create table t3(id int auto_increment,name char(10))engine=innodb default charset=utf8; *****

　　# innodb 支持事务，原子性操作
　　# myisam myisam

　　create table t1(
　　　　列名 类型 null, # 表示本列内容插入的数据可以为空，默认就是这个
　　　　列名 类型 not null,
　　　　列名 类型 not null auto_increment primary key,
　　　　id int,
　　　　name char(10)
　　)engine=innodb default charset=utf8;

auto_increment 表示：自增
primary key： 表示 约束(不能重复且不能为空); 加速查找
not null: 是否为空
数据类型：

数字：
tinyint
int
bigint

FLOAT
　　0.00000100000123000123001230123
DOUBLE
　　0.00000000000000000000100000123000123001230123
　　0.00000100000123000000000000000
decimal
　　0.1

字符串：
char(10) 　　#无论多少个字符，按最大定长存放。 速度快()
root
root
varchar(10) 　　#按实际使用的字符长度+1(字符长度值)存放，如实际是2个字符，就存2+1个字符的位置。 节省空间
root
PS: 创建数据表时，定长的列往前面放

text

上传文件：
　　文件存硬盘
　　db存路径
时间类型
　　DATETIME

enum　　枚举类型
set　　　　里面的元素可以多个组合


create table t1(
　　id int signed not null auto_increment primary key,
　　num decimal(10,5),
　　name char(10)
)engine=innodb default charset=utf8;

清空表：
　　delete from t1; # 如后续再新增表项，新增表项的数字，是从以前的表项数基础上自动新增
　　truncate table t1; # 新增表项时，表项数字从1开始新增
删除表：
　　drop table t1;

查看表结构：

　　desc t10;　　 #查看表结构（各列的属性设置）

操作文件中内容
　　插入数据：
　　　　insert into t1(id,name) values(1,'alex');

　　　　insert into tb1(name,age) values('alex',18),('egon',19),('yuan',20);
　　删除：
　　　　delete from t1;
　　　　delete from t1 where id<6
　　修改：
　　　　update t1 set age=18;
　　　　update t1 set age=18 where age=17;
　　查看数据：
　　　　select * from t1;

外键：
　　create table userinfo(
　　　　uid int auto_increment primary key,
　　　　name varchar(32),
　　　　department_id int,
　　　　xx_id int,
　　　　constraint fk_user_depar foreign key (department_id) references department(id)
　　)engine=innodb default charset=utf8;

　　create table department(
　　　　id bigint auto_increment primary key,
　　　　title char(15)
　　)engine=innodb default charset=utf8;

 # 关联的外键必须是主键，否则关联不成功

 

4 对于自增补充：
show create table t10;

show create table t10 \G; #查看创建这个表的命令, G为改为按列看

alter table t10 AUTO_INCREMENT=20; #修改这个表


MySQL: 自增步长
　　基于会话级别：
　　　　show session variables like 'auto_inc%'; 　　查看全局变量
　　　　set session auto_increment_increment=2; 　　 设置会话步长
　　　　# set session auto_increment_offset=10; 　　 起始值
　　基于全局级别：
　　　　show global variables like 'auto_inc%'; 　　 查看全局变量
　　　　set global auto_increment_increment=2; 　　 设置会话步长
　　　　# set global auto_increment_offset=10;


SqlServer：自增步长：
　　基础表级别：
　　CREATE TABLE `t5` (
　　　　`nid` int(11) NOT NULL AUTO_INCREMENT,
　　　　`pid` int(11) NOT NULL,
　　　　`num` int(11) DEFAULT NULL,
　　　　PRIMARY KEY (`nid`,`pid`)　　#多个键做为一个组合主键
　　) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8

　　CREATE TABLE `t6` (
　　　　`nid` int(11) NOT NULL AUTO_INCREMENT,
　　　　`pid` int(11) NOT NULL,
　　　　`num` int(11) DEFAULT NULL,
　　　　PRIMARY KEY (`nid`,`pid`)
　　) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8


5. 唯一索引

create table t1(
　　id int ....,
　　num int,
　　xx int,
　　unique 唯一索引名称 (列名,列名),
　　constraint ....
)

PS:
　　唯一：
　　　　约束不能重复（可以为空）
　　　　PS: 主键不能重复（不能为空）
　　加速查找

1. 外键的变种
        
        a. 用户表和部门表
        
            用户：
                1 alex     1
                2 root       1
                3 egon       2
                4 laoyao   3
                
            部门:
                1 服务
                2 保安
                3 公关
            ===》 一对多
        b. 用户表和博客表
            用户表：
                1 alex    
                2 root       
                3 egon       
                4 laoyao   
            博客表：
                                  FK() + 唯一
                1   /yuanchenqi/   4
                2    /alex3714/    1
                3    /asdfasdf/    3
                4    /ffffffff/    2
                
            ===> 一对一
            
                create table userinfo1(
                    id int auto_increment primary key,
                    name char(10),
                    gender char(10),
                    email varchar()
                )engine=innodb default charset=utf8;

                create table admin(
                    id int not null auto_increment primary key,
                    username varchar() not null,
                    password VARCHAR() not null,
                    user_id int not null,
                    unique uq_u1 (user_id),
                    CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
                )engine=innodb default charset=utf8;
            
        c. 用户表（百合网） 相亲记录表
        
            示例1：
                用户表
                相亲表
                
            示例2：
                用户表
                主机表
                用户主机关系表
            ===》多对多 多个对象之间的相互关系，单独进行存档
    
                create table userinfo2(
                    id int auto_increment primary key,
                    name char(10),
                    gender char(10),
                    email varchar()
                )engine=innodb default charset=utf8;

                create table host(
                    id int auto_increment primary key,
                    hostname char()
                )engine=innodb default charset=utf8;

                create table user2host(
                    id int auto_increment primary key,
                    userid int not null,
                    hostid int not null,
                    unique uq_user_host (userid,hostid),
                    CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
                    CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
                )engine=innodb default charset=utf8;

 

6，SQL数据操作补充

增: insert
    insert into tb11(name,age) values('alex',12);
    
    insert into tb11(name,age) values('alex',12),('root',18);    #批量插入数据
    
    insert into tb12(name,age) select name,age from tb11;    # 从别一个表拷数据过来
删: delete
    delete from tb12;
    delete from tb12 where id !=2 
    delete from tb12 where id =2 
    delete from tb12 where id > 2 
    delete from tb12 where id >=2 
    delete from tb12 where id >=2 or name='alex'

改: update
    update tb12 set name='alex' where id>12 and name='xx'
    update tb12 set name='alex',age=19 where id>12 and name='xx'

查: select
    
    select * from tb12;
    
    select id,name from tb12;
    
    select id,name from tb12 where id > 10 or name ='xxx';
    
    select id,name as cname from tb12 where id > 10 or name ='xxx';
    
    select name,age,11 from tb12;    # 增加一个常量(11)，常量会在结果中做为列显示出来
    
    其他：
select * from tb12 where id != 1
select * from tb12 where id in (1,5,12);
select * from tb12 where id not in (1,5,12);
select * from tb12 where id in (select id from tb11)
select * from tb12 where id between 5 and 12;
    
    
通配符：

select * from tb12 where name like "a%"　　#%指任意多个字符，_指一个字符
select * from tb12 where name like "a_"

 

6，分页：limit

    select * from tb12 limit 10;    #10条一页
    
    select * from tb12 limit 0,10;    #从第0条开始，10条一页
    select * from tb12 limit 10,10;
    select * from tb12 limit 20,10;
    
    select * from tb12 limit 10 offset 20;
    从第20行开始读取，读取10行；

    结合Python分页：
    # page = input('请输入要查看的页码')
    # page = int(page)
    # (page-1) * 10
    # select * from tb12 limit 0,10; 1 
    # select * from tb12 limit 10,10;2
 7，排序： order by select * from tb12 order by id desc; 大到小 select * from tb12 order by id asc; 小到大 select * from tb12 order by age desc,id desc; 取后10条数据 select * from tb12 order by id desc limit 10;

8，分组：

    select count(id),max(id),part_id from userinfo5 group by part_id;
    
    count
    max
    min
    sum
    avg
    
    **** 如果对于聚合函数结果进行二次筛选时，必须使用having ****
　　即： where后面不能跟函数，having后面可以跟函数

    select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
    
    select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;

9，连表操作：
　　由LEFT JOIN,RIGHT JOIN,INNER JOIN连接
　　WHERE只能放在连表后面

    select * from userinfo5,department5;
    
    select * from userinfo5,department5 where userinfo5.part_id = department5.id
　　# userinfo5,department5这2张表，根据userinfo5.part_id和department5.id相等的关系，连在一起输出

　　select * from userinfo5 left join department5 on userinfo5.part_id = department5.id;
　　 select * from department5 left join userinfo5 on userinfo5.part_id = department5.id;
　　 # 按userinfo5表在左边全部显示 

　　# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
　　# 按department5右边表的所有项全部显示

　　select * from userinfo5 inner join department5 on userinfo5.part_id = department5.id

　　#inner join，会将出现null的行隐藏。即左边有数据行，右边表又没有数据的行，进行隐藏

　　 # 多张表相关联
 　 select * 
　　　　from department5
　　 　　left join userinfo5 on userinfo5.part_id = department5.id
 　　　　left join userinfo6 on userinfo5.part_id = department5.id;

 　 select score.sid, student.sid
　　 from score
　　 　　left join student on score.student_id = student.sid　　#score.student_id和student.sid关联起来

　　 　　left join course on score.course_id = course.cid　　#score.course_id和course.cid，把第3个表关联起来
　　　　 left join class on student.class_id = class.cid　　#第4个表class也关联起来
　　 　　left join teacher on course.teacher_id=teacher.tid;
　　　　#5个表同时关联起来，一起输出

 

10，between and

 

11，数据库作业：