数据库

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

数据库管理软件:管理-数据库  (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;
View Code

 

字符串类型

#常用字符串系列: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.
View Code

枚举类型和集合类型

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');
View Code

 

查询

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)$';
all

 

posted @ 2018-06-12 17:32  web123  阅读(186)  评论(0编辑  收藏  举报