数据库

数据库服务器：运行数据库管理软件

数据库管理软件：管理-数据库  （mysql）

数据库：即文件夹，用来组织文件/表

表：即文件，用来存放多行内容/多条记录

 

数据库命名规则：

可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位

show create table t1\G; #查看表详细结构，可加\G

 

修改表结构

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;

mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;

 

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2;        //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;

 

字符串类型

#常用字符串系列：char与varchar
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡

#其他字符串系列（效率：char>varchar>text）
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列    TINYBLOB BLOB MEDIUMBLOB LONGBLOB 
BINARY系列 BINARY VARBINARY

text：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
longtext：A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

枚举类型和集合类型

enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female

set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

MariaDB [db1]> create table consumer( 
    -> name varchar(50),
    -> sex enum('male','female'),
    -> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一
    -> hobby set('play','music','read','study') #在指定范围内，多选多
    -> );

MariaDB [db1]> insert into consumer values  
    -> ('egon','male','vip5','read,study'),
    -> ('alex','female','vip1','girl');

 

查询

SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

重点中的重点：关键字的执行优先级
from   　　找到表
where　　  条件（一次过滤）
group by　 分组
having　　 过滤（二次过滤）
select　　 
distinct   去重
order by   排序
limit　　  限制条数

 

group by 

单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;

GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

having

#！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

 

触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

删除/更新：tri_before_delete_tb1 
                tri_before_update_tb1
                DELETE/UPDATE        

#准备表
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

#创建触发器
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
    IF NEW.success = 'no' THEN #等值判断只有一个等号
            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
      END IF ; #必须加分号
END//
delimiter ;


#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');


#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
|  2 | useradd xxx     | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
rows in set (0.00 sec)

插入后触发触发器

 

事务

务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);

#原子操作
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
commit;

#出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
rollback;
commit;
mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | wsb  |    1000 |
|  2 | egon |    1000 |
|  3 | ysb  |    1000 |
+----+------+---------+
rows in set (0.00 sec)

#介绍
delimiter //
            create procedure p4(
                out status int
            )
            BEGIN
                1. 声明如果出现异常则执行{
                    set status = 1;
                    rollback;
                }

                开始事务
                    -- 由秦兵账户减去100
                    -- 方少伟账户加90
                    -- 张根账户加10
                    commit;
                结束

                set status = 2;


            END //
            delimiter ;

#实现
delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN 
    DECLARE exit handler for sqlexception 
    BEGIN 
        -- ERROR 
        set p_return_code = 1; 
        rollback; 
    END; 

    DECLARE exit handler for sqlwarning 
    BEGIN 
        -- WARNING 
        set p_return_code = 2; 
        rollback; 
    END; 

    START TRANSACTION; 
        DELETE from tb1; #执行失败
        insert into blog(name,sub_time) values('yyy',now());
    COMMIT; 

    -- SUCCESS 
    set p_return_code = 0; #0代表执行成功

END //
delimiter ;

#在mysql中调用存储过程
set @res=123;
call p5(@res);
select @res;

#在python中基于pymysql调用存储过程
cursor.callproc('p5',(123,))
print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p5_0;')
print(cursor.fetchall())

 

 

 

 

 

 

 

 

 

 

 

 

 

 

'''
一：基本操作
    1.开始服务
    管理员cmd    net start mysql57
    cmd          mysql -u root -p

    2.停止服务
    管理员cmd   net stop 服务名称（mysql57）

    3.连接数据
    格式：mysql -u 用户名 -p
    示例：mysql -u root -p
    输入密码（安装时设置）

    4.退出登录（断开连接）
    ctrl + z

    5.查看版本（连接后执行）
    示例：终端 mysql> select version();
    6.显示当前时间（连接后执行）
    示例：终端 mysql> select now();

    7.远程连接(当前未登录)
    格式：（C:>）mysql -h ip地址 -u 用户名

二、数据库操作
    1.创建数据库
    格式：create database  数据库名  charset=utf8;
    e.g.：create database  crt charset=utf8;

    2.删除数据库
    格式：drop database  数据库名;
    e.g.：drop database  crt;

    3.切换数据库
    格式：use 数据库名;
    e.g.：use crt;

    4.查看当前数据库
    select database();

三、表操作
    1.查看数据库中所有表
    show tables;
    2.创建表
    格式：create table 表名(列及类型);
    说明：auto_increment = 自生长
          primary key  =  主键
          not null   =   不为空
          default  =  默认值
    e.g.：create table stu(id    int auto_increment primary key,
                           name  varchar(20) not null,
                           age     int       not null,
                           gender  bit     default 1,
                           address varchar(20),
                           isDelete  bit    default 0);
    level enum('vip1','vip2','vip3'),  枚举类型
    hobbies set('play','music','read','run')  集合类型

    3.删除表
    格式：drop table 表明;
    e.g.：drop table ;

    4.查看表结构(不要修改表结构)
    格式：desc 表名;
    e.g.：desc stu;

    5.查看建表语句
    格式：show createtable 表名;
    e.g.：show create table stu;

    6.重命名表名
    格式：rename table 原表名 to 新表名;
    e.g.：rename table stu to studt;

    7.修改表
    格式： alter table 表明 add\change\drop 列名 类型;
    e.g.：alter table stu add gender bit default 1;

四、数据操作
    1.增加数据
      a、全列插入
        说明：主键列是自动增长，但在全列插入时需要占位
                通常使用0，插入后以实际数据为准
        格式：insert into 表名 values(...)
        e.g.：insert into studt values(0,'tom',19,1,'北京',0);
      b、缺省插入
        格式：insert into 表名(列1，列2，...) values(...);
        e.g.：insert into studt(name,age,address) values('ll',16,'上海');
      c、同时插入多条数据
        格式：insert into 表名 values(...),(...),...;
        e.g.：insert into studt values(0,'hh',18,0,'hhh',0),(0,'zz',18,1,'zzz',0);
    2.删
        格式：delete from 表名 where 条件;
        e.g.：delete from studt where id=4;
        如果  <delete from 表名;>  删除所有数据
    3.改(表内数据)
        格式：update 表名 set 列1=值1,列2=值2,... where 条件
        e.g.：update studt set age=100 where id=5;
        如果不指定where，整个列全修改
    4.查
        说明：查询表中全部数据
        格式：select *from 表名;
        e.g.：select *from studt;

五、查  《对于表》
    1.基本语法
        格式：select * from 表名
        说明：
            a、from关键字后边是表名，表示数据来源于该表
            b、select后面写表中的列名，*表示所有列
            c、在select的列名部分，可以使用as为列名起别名
                这个别名显示在结果集中
            d、如果要查询多个列，之间使用逗号分隔
        e.g.：
            select * from studt;
            select name,age from studt;
            select name as a,age from studt;
    2.消除重复行
        在select后面列的前面使用distinct可消除重复的行
        e.g.:
            select name from studt;
            select distinct name from studt;
    3.条件查询
        a、语法
            select * from 表名 where 条件;
        b、比较运算符
            > \ < \ = \ >= \ <= \ !=
            需求：查询id值大于8的所有数据
            select * from studt where id>8;
        c、逻辑运算符
            and
            or
            not
            需求：查询id值大于7的女同学
            select * from studt where id>7 and gender = 0;
        d、模糊查询
            like
                %表示任意多个字符
                _表示一个任意字符
            需求：查询姓习的同学
            e.g.：select * from studt where name like '习%';

            # 正则表达式
            select * from employee where name regexp '^jin.*(g|n)$';

        e、范围查询
            in ：表示在一个非连续的范围内
            between a and b ：表示在一个连续的范围内
            需求：
                >:查询编号为5，7，9
                  e.g.：select * from studt where id in (5,7,9);

                >:查询编号为5到9
                e.g.：select * from studt where id between 5 and 9;

        f、空判断
           insert into studt(name,age) values('Trangp',86);

            注意：null 与''(空字符串)不同
            判断空：is null
            判断非空：is not null
            需求：查询没有地址的同学
            e.g.：select * from studt where address is null;
                select * from studt where address is not null;

        g、优先级
            小括号、not、比较运算符、逻辑运算符
            and比or的优先级高，可结合括号指定优先级

    4.聚合
        为了快速得到统计的数据，提供了5个聚合函数
        a、count(*) 表示计算总行数，括号中可以是*或列名
        b、max(列) 表示求此列的最大值
        c、min(列)最小值
        d、sum(列)  求和
        e、avg(列)  平均值

        查询学生总数
            select count(*) from studt;
        查询女生编号的最大
            select max(id) from studt where gender = 0;
        查询所有年龄的和
            select sum(age) from studt;

    5.分组：
    group by 在where 之后使用
        按照字段分组，表示此字段相同的数据会被放到一个集合中
        分组后，只能查询出相同的数据列，对于有差异的数据列
                无法显示在结果集中
        可以对分组后的数据进行统计，做聚合运算

        语法：select 列1，列2，聚合... from 表名 group by 列1，
                列2，列3...
        需求：查询男女生总数
        e.g.：select gender,count(*) from studt group by gender;
           select name,gender,count(*) from studt group by name,gender;

        分组后的数据筛选：select 列1，列2，聚合... from 表名 group
            by 列1，列2，列3... having 列1，... 聚合...
            e.g.：select gender,count(*) from studt group by gender
                    having gender;  或gender=0

            where：对表进行筛选，对原始数据的筛选
            having：对group by 的结果进行筛选，是对结果集的筛选（二次筛选）

    6.排序
        语法：select * from 表名 order by 列1;
            asc|desc,列2 asc|desc,...;

        说明：
            a、将数据按照列1进行排序，如果某些列1的值相同，
                    则按列2排序
            b、默认按照从小到大的顺序排序
            c、asc升序
            d、desc降序序

        需求：按年龄排序
        e.g.：select * from studt order by age;
        select * from studt where isDelete=0 order by age desc;
        # where 在order by 前面
        select * from studt where isDelete=0 order by age desc,id desc;
        出现相同的可再指定一种排序规则

    7.分页
        语法：select * from 表名 limit start,count;
        说明：start索引从零开始，count为数量
        e.g.：select * from studt limit 0,3;
              select * from studt limit 3,3;
              select * from studt where gender=1 limit 0,3;
六、关联
    建表语句
    1.create table class(id int auto_increment primary key,
                        name varchar(20) not null,
                        stuNum int not null);
    2.create table student(id int auto_increment primary key,
                             name varchar(20) not null,
                             gender bit default 1,
                             classid int not null,
                foreign key(classid) references class(id)
                on delete cascade   # 被关联表删除时，主表删除对应字段
                on update cascade   # 被关联表更新时，。。。
                );

    输入一些数据：
    insert into class values(0,'Py01',50),(0,'Py01',50),
                    (0,'Py01',50),(0,'Py01',50);

    insert into student values(0,'toy',1,1);
    insert into student values(0,'toy',1,10);报错
    insert into student values(0,'jack',1,2);

    select * from student;
    关联查询：
    select student.name,class.name from class inner join
        student on class.id=student.classid;


    分类：
    1、内连接：表Ai inner join 表B
        表示A与B匹配的行会出现在结果集中
    2、左连接：表Ai left join 表B
         表示A与B匹配的行会出现在结果集中，外加表A中独有的数据
                未对应的数据使用null填充
    3、右连接：表Ai right join 表B
        表示A与B匹配的行会出现在结果集中，外加表B中独有的数据
                未对应的数据使用null填充
    4、全外连接：在内连接的基础上保留左右两表没有对应关系的记录
        select * from employee left join department on employee.dep_id = department.id
        union
        select * from employee right join department on employee.dep_id = department.id ;
'''

select distinct 字段1,字段2,字段3 from 库.表
        distinct 去重    from 找到表
    where 条件
    group by 分组条件
    having 过滤
    order by 排序字段
    limit n;
写的顺序：distinct > from > where > group by > having > order by > limit
执行顺序：from > where > group by > having > distinct>  order by > limit

#正则表达式
select * from employee where name like 'jin%';
select * from employee where name regexp '^jin';
select * from employee where name regexp '^jin.*(g|n)$';