MySQL基本命令行

1、MySQL概述

1、什么是数据库

数据库是一个存储数据的仓库

 

2、MySQL的特点

1、关系型数据库

1、数据是以行和列的形式去存储的
2、这一系列的行和列称为表
3、表中的每一行叫一条记录
4、表中的每一列叫一个字段
5、表和表之间的逻辑关联叫关系
6、关系型数据库的核心内容是关系即二维表

 

2、跨平台

可以在Unix Linux Windows上运行数据库服务

 

3、支持多种编程语言

python、java、php ... ...

 

2、基本SQL命令

1、SQL命令的使用规则

1、每条命令必须以 ; 结尾
2、SQL命令不区分字母大小写
3、使用 \c 终止命令的执行

 

2、库的管理

1、库的基本操作

1、查看已有的库：show databases;
2、创建库：create database 库名 charset=utf8;
3、切换库：use 库名;
4、查看当前所在库：select database();
5、查看库中已有的表：show tables;
6、删除库：drop database 库名;
注意：
    指定了库的字符编码，库中所有表的默认字符编码和库的字符编码相同

2、库的命名规则

1、可以使用数字、字母、_,但是不能是纯数字
2、库名区分字母大小写
3、库名具有唯一性
4、不能使用特殊字符和mysql关键字

3、表的管理

1、创建表：create table 表名(字段名 数据类型,... ...) charset=utf8;
2、查看表结构：desc 表名;
3、插入记录：insert into 表名 values(值1),(值2),..... ;
4、查看表记录：select * from 表名 where 条件;
5、删除表记录：delete from 表名 where 条件;
6、更改表记录：update 表名 set 字段名=值，字段名=值，... where 条件;
7、删除表：drop table 表名;
注意：
    1、所有的数据都是以文件的形式存储在数据库目录下
    2、数据库目录：/var/lib/mysql

练习：

1、查看所有的库
    show databases;
2、创建一个新库studb，指定字符编码为utf8
    create databases studb charset='utf8';
3、创建一张表t1，字段有 id name age salary，字段的数据类型自己定义
    create table t1 value(id int(2), name char(20), age int(2), salary int) charset='utf8';
4、查看t1的表结构
    desc t1;
5、在t1中随便插入3条记录，id分别为1,2,3
    insert into t1 value(id=1),(id=2),(id=3);
6、查看t1表中的所有记录
    select * from t1;
7、删除t1表中id为2的记录
    delete from t1 where id=2;
8、把t1表中id为3的记录的salary改为8888
    update t1 set salary=8888 where id=3;
9、删除表t1
    drop table t1;
10、删除数据库studb
    drop database studb;

 

3、数据类型

1、数值类型

1、整型
    1、int 大整型
    2、tinyint  微小整型
        1、有符号(默认) -128~127
        2、无符号unsigned 0~255
2、浮点型
    1、float  float(n,m) --> n为总位数，m为小数位位数
    2、double
    3、decimal decimal(M,D) --> M为总位数，D为小数位位数

2、字符类型

1、char 定长
2、varchar 变长
3、char 和 varchar 的特点
    1、char浪费存储空间，但是性能高
    2、varchar节省存储空间，但是性能低
4、字符类型的宽度和数值类型的宽度的区别
    1、数值类型的宽度为显示宽度，只用于select查询时显示，和占用存储空间大小无关，用zerofill可以展示效果
    2、字符类型的宽度超过则无法存储
    注意：
    1、varchar没有宽度默认值，必须指定宽度
    2、在使用的时候char和varchar都要给定一个宽度，但不能超过各自的范围

3、枚举类型

1、enum 单选  sex enum("M","F")
2、set  多选  likes set("study","Python","MySQL")

 

4、日期时间类型

1、year     YYYY
2、date     YYYYMMDD
3、time     HHMMSS
4、datetime YYYYMMDDHHMMSS

 

练习：

1、先创建一个库 MoShou
    create database MoShou;
    
2、在库 Moshou中创建一个表sanguo,要求字符编码为utf8，且字段如下：
    id 整型，显示宽度为2，位数不够用0补位
    name 定长，宽度为20
    gongji 整型
    fangyu 微小整型，不可以输入负数
    sex 枚举类型，只能从"M","F"中选择一个
    country 变长，宽度为20
    
    create table sanguo(
    id int(2) zerofill,
    name char(20),
    gongji int,
    fangyu tinyint unsigned,
    sex enum("M","F"),
    country varchar(20))charset=utf8;
    
3、在表sanguo中插入如下几条记录
    insert into sanguo values
        (1,'诸葛亮',120,20,'M','蜀国'),
        (2,'司马懿',119,25,'M','魏国'),
        (3,'关羽',188,60,'M','蜀国'),
        (4,'赵云',200,66,'M','魏国'),
        (5,'孙权',110,20,'M','吴国'),
        (6,'貂蝉',666,10,'F','魏国'),
        (7,null,1000,99,'F','蜀国'),
        (8,'',1005,88,'F','蜀国'),
        (9,'魏延',110,30,'M','蜀国');
        
4、查找所有蜀国人的信息
    select * from sanguo where country="蜀国";
​
5、查找一下女人的信息，只显示她的姓名、国家和性别
    select name,country,sex from sanguo where sex="F";
​
6、把魏延的性别改为"F",把他的国籍改为 泰国
    update sanguo set sex="F",country="泰国" where name="魏延";
​
7、删除所有泰国的人
    delete from sanguo where country="泰国";  

4、运算符操作(配合查询、修改、删除)

1、数值比较

1、运算符：=、!=、>、>=、<、<=
2、示例
    1、查找攻击力大于等于150的英雄的名字和攻击力
        select name,gongji from sanguo where gongji >= 150;
    2、把id为2的英雄的攻击力改为888
        update sanguo set gongji=888 where id = 2;

2、字符比较

1、运算符：= 、!=
2、示例
    1、查找蜀国的英雄的信息
        select * from sanguo where country="蜀国";
    2、把貂蝉的名字改为 小乔
        update sanguo set name="小乔" where name="貂蝉";

3、逻辑比较

1、运算符：and  or
    2、示例
        1、查找攻击力大于200的蜀国英雄的 姓名，国家 和攻击力
            select name,country,gongji from sanguo where gongji > 200 and country="蜀国";
        2、查找名字是 小乔 或者 魏国人
            select * from sanguo where name="小乔" or country="魏国";   

4、范围内比较

1、运算符：between and 、in 、not in
2、语法
    字段名 between 值1 and 值2
    字段名 in (值1,值2,... ...)
    字段名 not in (值1,值2，... ...)
3、示例
    1、找到攻击力在100-200之间的英雄的记录
        select * from sanguo where gongji between 100 and 200;
        select * from sanguo where gongji between 100 and 200;
    2、找到id在1,3,5,7中的英雄的id和姓名
        select id,name from sanguo where id in (1,3,5,7);

5、匹配空 非空

1、空 ：is null
2、非空 ：is not null
3、示例
    1、查找名字为 空值 的英雄的名字和国家
    select name,country from sanguo where name is null;
注意：
    1、null 和 ""不同
    null是空值，必须用is 或者 is not去操作
    ""是空字符串，用= 或者 != 去操作

练习：

1、查找id在3,5,7中，并且攻击力大于150的 蜀国的英雄的记录
    select * from sanguo where id in (3,5,7) and gongji > 150;
    
2、查找国家不是蜀国和吴国，或者防御力大于60的英雄的名字 防御力 和 国家
    select name,fangyu,country from sanguo where country not in("蜀国","吴国") or fangyu>60;
​
3、将国家为吴国，并且攻击力在1-100之间的防御力改为88
    update sanguo set fangyu=88 where country="吴国" and gongji between 1 and 100;
​
4、在表中插入一条记录，id为99，名字为黄月英，其他信息自己定
    insert into sanguo values(99,"黄月英",200,90,"M","蜀国");
​
5、删除表中id为99，并且名字为黄月英的记录
    delete from sanguo where id = 99;

6、模糊比较

1、语法
    字段名 like 表达式
2、表达式
    1、_ : 匹配单个字符
    2、% : 匹配0到多个字符
示例：
    1、select id,name from sanguo where name like "_%_"; # 名字至少有两个字符的
    2、select id,name from sanguo where name like "%"; # 匹配所有的记录
    3、select id,name from sanguo where name like "___"; # 匹配名字为三个字的记录
    4、select id,name from sanguo where name like "赵%"; # 匹配所有姓赵的记录

 

5、SQL高级操作

1、order by

1、作用：
    给查询的结果进行排序
​
2、排序方式
    1、ASC(默认) ：升序
    2、DESC ：降序
示例：
    1、按照英雄的攻击力降序排列
        select * from sanguo order by gongji desc;
    2、按照英雄的防御力升序排列
        select * from sanguo order by fangyu asc;

 

2、limit

1、作用
    限制显示查询记录的记录个数
2、用法
    1、limit n -->显示几条记录
    2、limit m,n 
    m表示从第几条记录开始显示，n表示显示几条
注意：
    m的值是从0开始计算的，3则表示第四条记录
3、示例：
    1、查找攻击力前三名的蜀国的英雄的姓名和攻击力
        select name,gongji from sanguo where country="蜀国" order by gongji desc limit 3;
    2、查找防御力 倒数第二名 至 倒数第四名 的英雄的记录
        select * from sanguo order by fangyu limit 1,3;

 

3、聚合函数

1、avg(字段名) : 求字段的平均值
​
2、sum(字段名) : 求字段的和
​
3、max(字段名) : 求字段的最大值
​
4、min(字段名) : 求字段的最小值
​
5、count(字段名) : 统计该字段的记录的个数
​
示例：
    1、统计一下sanguo表中一共有多少条记录
        select count(*) from sanguo;
​
    2、统计一下表中id,name字段分别有多少条记录
        select count(id),count(name) from sanguo;
        # 空值NULL不会被统计，空字符串""会被统计
​
    3、计算蜀国英雄的总攻击力
        select sum(gongji) from sanguo where country = "蜀国";
​
    4、攻击力最强的攻击力是多少
        select max(gongji) as best from sanguo;

 

4、group by

1、作用：给查询的结果进行分组
​
2、示例：
    1、查询sanguo表中一共有几个国家
        select co untry from sanguo group by country;
    2、计算所有国家的平均攻击力
        select country,avg(gongji) from sanguo group by country;
注意：
    1、group by后的字段名必须要为select之后的字段名
    2、group by处理的是group by之后的所有字段，如果查询字段和group by之后的字段不一致，则必须要对该字段值做聚合处理(聚合函数)

 

5、distinct

1、作用
    不显示字段的重复值
2、示例
    1、sanguo表中一共有多少个国家
        select distinct country from sanguo;
3、注意：
    1、distinct处理的是distinct与from之间的所有字段，所有字段必须全部相同才能去重
    2、distinct不能对任何字段做聚合处理

 

6、查询表记录时做数学运算

1、运算符
    +  -  *  /  %
​
2、查询显示时所有英雄攻击力全部 * 10
    select id,name,country,gongji*10 from sanguo;

 

6、连接查询

​ 创建两张表

1、老师表(t)
编号(t_id)  姓名(name)
1        魏老师
2        赵老师
3        闵老师
create table t(
t_id int primary key auto_increment,
name varchar(20))charset=utf8;
insert into t values(0,"魏老师"),(0,"赵老师"),(0,"闵老师");

2、学生表(s)
    学号(stu_id) 姓名(name) 性别(gender) 成绩(score) t_id
    171001       '唐伯虎'    男           90          1
    171002       '秋香'      女           99          2
    171003       '祝枝山'    男           80          2
    171004       '石榴'      女           100         NULL
    171005       '文征明'    男           100         NULL
​
​
    create table s(
        stu_id char(7),
        name varchar(15),
        gender enum("男","女"),
        score tinyint unsigned,
        t_id int)charset=utf8;
​
    insert into s values
        ('171001','唐伯虎','男',90,1),
        ('171002','秋香','女',99,2),
        ('171003','祝枝山','男',80,2),
        ('171004','石榴','女',100,NULL),
        ('171005','文征明','男',100,NULL);

1、内连接
      1、定义
        把所有符合连接条件的记录显示在结果集中，不符合条件的直接过滤掉
      2、示例
        1、查询所有学生的学号，姓名和老师
        写法1：
            select s.stu_id,s.name,t.name from s,t where s.t_id = t.t_id;
        写法2：
            select s.stu_id,s.name,t.name from s inner join t on s.t_id = t.t_id;

2、外连接
    1、左连接
        1、定义
            以左表为准，去右表找数据，如果没有匹配的数据，则以null补空位，所以输出结果数>=左表原数据数
        2、语法
            select 字段名 from 表1 left join 表2 on 条件;
        3、示例
            1、查询所有学生的学号，姓名和老师
                select s.stu_id,s.name,t.name from s left join t on s.t_id = t.t_id;
2、右连接
    1、用法同左连接，以右边的表为主显示查询结果
    2、示例
        1、查询所有学生的学号，姓名和老师
            select s.stu_id,s.name,t.name from s right join t on s.t_id = t.t_id;

7、约束

1、作用

 为了保证数据的完整性、一致性、有效性的规则
 可以限制无效的数据插入到数据表里面

2、约束分类

1、默认约束(default)

2、非空约束(not null)

3、主键约束(primary key)

1、规则
​
    1、一个表中只能有一个主键(primary)字段
    2、对应字段的值不允许重复 且 不能为空
    3、主键字段的key标志为 PRI
    4、把表中能够唯一标识一条记录的字段设置为主键，通常把表中记录编号的字段设置为主键
​
2、创建主键(primary key)
​
    1、在创建表时创建主键
        示例：
        1、第一种方式
            create table t4(
            id int primary key,
            name varchar(15),
            sex enum("boy","girl") default "girl"
            );
        2、第二种方式
            create table t5(
            id int,
            name char(30),
            likes set("girl","study","mysql"),
            primary key(id,name) 复合主键
            );
            
        2、在已有表中添加主键限制
            语法：
                alter table 表名 add primary key(字段名);
                
        3、删除主键限制
            语法：
                alter table 表名 drop primary key;
                
        4、自增长属性 auto_increment
            通常和主键一起配合使用
            1、在创建表时添加自增长属性
                create table t5(
                id int primary key auto_increment,
                name char(20),
                age tinyint unsigned
                );
            2、在已有表中添加自增长属性
                语法
                    alter table 表名 modify 字段名 数据类型 primary key auto_increment;

 

4、唯一约束(unique key)

1、使用规则
    1、一个表里面可以有个unique字段
    2、unique字段的值不允许重复，但可以为空
    3、unique的key标志是 UNI
    
2、创建唯一约束
    1、在创建表时创建唯一约束
        方式一：
            create table t7(
            id int unique,
            name char(15)
            );
        方式二：
            create table t10(
            id int,
            name char(15),
            unique(id),
            unique(name)
            );

 

5、外键约束

 

1、定义
    让当前表字段的值在另一个表的范围内选择
​
2、语法
    foreign key(参考字段名) 
    references 被参考表名(被参考字段名)
    on delete 级联动作(cascade)
    on update 级联动作(cascade)
​
3、使用规则
    1、两张表被参考字段和参考字段数据类型要一直
    2、被参考字段必须是key的一种，通常primary key
    
    表1：缴费信息表(财务)
        学号   姓名   班级     缴费金额
        1    唐伯虎  AID1711   28000
        2    秋香    AID1711   20000
​
    表2：学生信息表(班主任)
        学号   姓名  缴费金额
        1    唐伯虎  28000
        2    秋香
​
    1、创建表
        1、缴费信息表(主表)
        create table jftab(
        id int primary key,
        name char(20),
        class varchar(25),
        money int
        )default charset=utf8;
​
        insert into jftab values
        (1,"唐伯虎","AID1711",20000),
        (2,"秋香","AID1711",28000),
        (3,"祝枝山","AID1711",25000);
​
    2、创建学生信息表(从表)
        create table bjtab(
        stu_id int,
        name char(20),
        money int,
        foreign key(stu_id) references jftab(id)
        on delete cascade
        on update cascade
        )default charset=utf8;
​
        insert into bjtab values
        (1,"唐伯虎",20000),
        (2,"秋香",28000);
4、级联动作
    1、cascade数据级联更新
        on delete cascade on update cascade
        当主表删除记录或更改被参照字段的值时从表会级联更新
    2、restrict(默认)
        on delete restrict on update restrict
            1、当主表删除记录时，如果从表中有相关联记录则不允许主表删除
            2、当主表更改主键字段值时，如果从表有相关记录则不允许更改
            3、set null
        on delete set null on update set null
            1、当主表删除记录时，从表外键字段值变为null
            2、当主表更改主键字段值时，从表外键字段值变为null
            4、no action
            同 restrict，都是立即检查外键限制
5、删除外键约束
    语法：
        alter table 表名 drop foreign key 外键名;
    注意：
        1、外键名的查看方式
            show create table 表名;
6、在已有表中添加外键
    alter table 表名 add 
    foreign key(参照字段名)
    references 表名(被参照字段名)
    on delete cascade
    on update cascade;
    注意：
        在已有表中添加外键时，会受到原有数据的限制       

8、数据备份与恢复(在Linux终端中操作)

1、数据备份

1、语法

mysqldump -u用户名 -p 源库名 > 路径/XXX.sql

2、示例

1、备份MoShou库
    mysqldump -uroot -p MoShou > /home/tarena/mydata/MoShou.sql

3、源库名的表示方式

备份所有库:
    --all-databases
    语法:
        mysqldump -uroot -p --all-databases > /home/tarena/mydata/mysql.sql
指定单个库:
    库名
备份多个库:
    -B 库1 库2 ... 
备份指定库的指定表:
    库名 表名       

2、数据恢复

1、语法
    mysql -u用户名 -p 目标库名 < 路径/XXX.sql
示例
    1、恢复MoShou库
        mysql -uroot -p MoShou < /home/tarena/mydata/MoShou.sql
    2、恢复所有的库
        mysql -uroot -p < /home/tarena/mydata/mysql.sql
    3、从mysql.sql中恢复MoShou
        mysql -uroot -p MoShou --one-database < /home/tarena/mydata/mysql.sql
注意：
    在数据恢复时如果要恢复的库不存在，则先要创建空库

 

9、索引(index)

1、定义

对数据库中表的一列或者多列的值进行排序的一种结构(MySQL中用二叉树Btree方式)

2、优点

可以加快数据的检索速度

3、缺点

1、当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，降低了数据的维护速度
2、索引需要占用物理空间

4、索引的使用规则

一般把经常用来查询的字段设置为索引字段

5、索引(index)的使用规则

1、一个表中可以有多个index字段
2、字段的值可以有重复，且可以为null值
3、经常把做查询条件的字段设置为index字段
4、index字段的key标志是MUL         

6、索引(index)的创建

 1、创建表时创建
     index(字段名1),index(字段名2)
     create table t1(
     id int,
     name char(20),
     age tinyint unsigned,
     index(id),
     index(name)
     );
 2、在已有表中创建index
     1、语法
        create index 索引名 on 表名(字段名);
     注意：
        索引名一般和字段名一样
 3、查看普通索引
         1、desc 表名;  --> 查看key标志为MUL
         2、show index from 表名;
         3, show index from 表名\G;
     作用:以列的形式显示查询结果
 4、删除索引
     drop index 索引名 on 表名;
     drop index id on t1;
     drop index name on t1;
    注意：
        删除普通索引只能一个一个删除

10、事务和事务回滚

1、事务定义

一件事从开始发生到结束的整个过程

2、作用

确保数据的一致性
    注意：
        1、mysql中默认sql语句结果会自动commit到数据库
        2、开始写start transaction，自动commit会被禁用，直到我们用commit;或者rollback；终止这个transaction
    格式：
        mysql> start transaction;
        mysql> sql语句1
        mysql> sql语句2
        ...
        mysql> commit; 或者 rollback;
    注意：
        回滚rollback只针对于对表记录的操作：增、删、改
        对库和表的操作无效
    示例：
    表1：CCB
        create table CCB(
        name char(20),
        money int
        );
        
        insert into CCB values("Zhuanqian",10000);
    
    表2：ICBC
        create table ICBC(
        name char(20),
        money int
        );
        
        insert into ICBC values("Shouqian",4000);
    开始转账：
    start transaction;
        update CCB set money = 5000 where name = "Zhuanqian";
        update ICBC set money = 9000 断电了，宕机了;
        rollback;
    验证：
        select * from CCB;
        select * from ICBC;•            

11、python数据库编程

1、python数据库接口(Python DB-API)

1、定义
    为开发人员提供的数据库应用编程接口

2、支持的数据库服务软件

MySQL、Oracle、SQL_Server、Sybase... ... 

3、Python提供的操作MySQL的模块

pymysql, sqlclicent

4、pymysql使用流程

1、建立数据库连接
2、创建游标对象
3、使用游标的方法和SQL语句操控MySQL数据库
4、提交commit
5、关闭游标
6、关闭数据库连接

5、建立数据库连接

1、语法
    对象名 = pymysql.connect("主机名","用户名","密码","库名")
    示例：
        db = pymysql.connect("localhost","root","123456","python")
2、connect对象支持的方法
    1、cursor() 创建一个游标对象db.cursor()
    2、commit() 提交到数据库(增删改)
    3、rollback() 回滚 db.rollback()
    4、close() 关闭数据库连接 db.close()
3、关于cursor对象支持的方法
    1、execute(op) 执行数据的基本SQL命令
    2、fetchone()     取得结果集的第一行记录
    3、fetchmany(size) 取得结果集的size行记录
    4、fetchall() 取得结果集的所有行
    5、close() 关闭游标对象
        

12. 在python中操作mysql库

import pymysql
​
# 链接到数据库
db = pymysql.connect("localhost", "root", "123456")
​
# 创建游标对象
cursor = db.cursor()
​
# 创建一个数据库
cursor.execute("create database python;")
​
# 创建一个表
cursor.execute("use python;")
cursor.execute("create table t1(\
    id int primary key auto_increment,\
    name varchar(20),\
    age tinyint unsigned);")
​
# 插入数据
sql_insert = "insert into t1 values\
    (0,'Green',25),\
    (0,'Lucy',30),\
    (0,'Bob',20);"
​
# 执行 sql 语句
cursor.execute(sql_insert)
​
# 提交数据
db.commit()
​
# 关闭游标对象
cursor.close()
​
# 断开数据库链接
db.close()
​