mysql基础 day02

4/10/2018 8:44:16 AM

MySQL02

晨测

(1)如何登陆mysql数据库
(2)varchar与char的区别
(3)写出SQL的组成     
(4)写出DQL的完整语法格式

回顾

1. 概念
    数据库
    数据库管理软件
    关系型数据库特点

2. 搭建
    字符集的选择

3. 使用
    登陆，退出，改密码

4. 数据类型
    整型，浮点型，字符型，日期型

5. 操作数据库中数据
    SQL(DDL,DML,DQL)


    1. 普通查询

    2. 条件查询 where 条件表达式

    3. 模糊查询 like 通配符和占位符

    4. 分组查询：单位
        语法格式：group by 分组列
        聚合函数：对一组数据进行的运算操作
            min(字段)：计算指定列的最小值
            max(字段)：计算指定列的最大值
            avg(字段)：计算指定列的平均值
            count(字段)：统计指定列的数量
            sum(字段)：计算指定列的数值和

        例如：select sex , count(*) from teacher group by sex;

        注意：select tname, sex , count(*) from teacher group by sex;
              不合理，但是不报错。

        select gender from teacher group by 1 having age=64;

    注意：having和where的区别
        having 是在分组后对数据进行过滤
        wherer是在分组前对数据进行过滤

        having后面可以使用聚合函数
        where后面不可以使用聚合函数，此语句报错：select * from teacher where avg(age)>10;

    5. 排序查询
        语法格式：order by 字段  [asc|desc]

        例如：select * from teacher order by age desc ;

    6. 限制查询结果数量 
        语法格式：limit 条数
        语法格式：limit 位置，条数


        例如：
            select * from teacher limit 4;

        -- 分页的实现：每页显示5条数据 
            第一页：select * from teacher limit 0,5;
            第二页：select * from teacher limit 5,5;
            第三页：select * from teacher limit 10,5;
            第n页： select * from teacher limit 5*(n-1),5;

作业解答

今日概要

一、TCL事务控制语言
二、MySQL中的约束
三、多表查询（重点）
四、用户的创建和授权
五、MySQL中的索引 

一、TCL事务控制语言

<1>事务的概念

事务是访问并可能更新数据库中各种数据项的执行单元。
事务是一条SQL语句，一组SQL语句，或者整个程序。
事务是恢复和并发控制的基本单位。
事务四个特性：ACID特性，原子性，一致性，隔离性，持续性。  

<2>为什么要使用事务

转账操作：用户A给用户B转账1000元
隐含的条件：扣钱和加钱，要么同时成功，或者同时失败。


事务就是对数据库中的数据的多步操作，并且可以控制数据操作的一致性。对数据的多步操作要么同时成功，或者同时失败。

<3>事务的特点

1. A(Atomicity)原子性
    一个事务的执行被视为不可分割的最小单元。
    要么同时成功，或者同时失败。
2. C(consistency)一致性
    一个事务的执行不应该破坏数据库的完整性约束。
    非法数据的录入，事务会进行撤销操作。
3. I(Isolation)隔离性
    通常来说，事务和事务之间不会相互影响。
4. D(Durability)持久性
    软硬件崩溃，事务会去自动恢复。（Innodb存储引擎会自动恢复）    

<4>事务的使用

准备：
    create table account(
        name varchar(20),
        money float(6,2)
    );

    insert account value('张三',22.22);

语法：
    set autocommit = true|false; -- 设置事务的提交方式（自动提交和手动提交）
    rollback; -- 事务的回滚
    commit; -- 事务的提交
    savepoint 还原点名称； -- 保存还原点
    rollback to 还原点名称; -- 回滚到某个还原点  

例如：
    set autocommit = false;//设置手动提交
    delete from account ;
    commit;

    insert account value('张三',22.22);
    update account set money = money-0.22;
    savepoint p1;
    update account set money = money-10;
    savepoint p2;
    update account set money = money-10;
    savepoint p3;
    delete from account ;
    rollback to p2;

<5>存储引擎

5.1 概念
    存储数据用的技术（存储机制，索引技术，锁定水平等等）

5.2 查看存储引擎
    show engines;   

5.3 常用的存储引擎
    MyISAM
        相对简单的存储引擎，不支持事务，不支持外键，访问速度快
        以查询为主，对数据的完整性要求不严格，推荐使用MyISAM

    InnoDB
        支持事务，支持外键，行级别锁机制
        多重并发更新的操作比较多，推荐使用Innodb
        支持auto_increment属性
        自动灾难恢复机制，软硬件崩溃          

二、MySQL中的约束

<1>约束

约束就是一种限制，通过对表的行和列的数据进行限制，确保数据的完整性和唯一性。

<2>使用场景

在创建表的时候，创建约束

<3>分类

1. default,默认约束，指定某列的默认值，   限制此列数据的正确性。（保证数据的域完整性）
2. not null,非空约束，指定某列的值不为空，限制此列数据的正确性。（保证数据的域完整性）
3. unique ,唯一约束，指定某列或者列组合的数据不能重复，保证数据的实体完整性。
4. primary ，主键约束，指定某列或者列组合的数据不能重复并且不能为空，保证数据的实体完整性。
5. foreign key ,外键约束，子表中的一条数据参照主表中的数据，保证数据的参照完整性。
6. check ,检查约束（保证数据的域完整性），MySQL不支持检查约束。
7. unsigned,无符号约束，指定某列的数值为正数，保证数据的域完整性。

3.1 default约束

添加约束：
    <1>创建表的时候添加
        create table t1(
            id int default 1,
            name varchar(20) default '小明'
        );

    <2>通过alter...modify/change添加
        alter table t1 modify id int default 2;
        alter table t1 change name name int default '小李';

删除约束：
    通过alter...modify/change
        alter table t1 modify id int ;

3.2 not null 非空约束

添加约束：
    <1>创建表的时候添加
        create table t1(
            id int ,
            name varchar(20) not null
        );

    <2>通过alter...modify/change添加
        alter table t1 modify id int not null;

删除约束：
    通过alter...modify/change
        alter table t1 modify id int ;

3.3 唯一约束

添加约束：
    <1>创建表时添加
        create table t2(
            id int unique,
            name varchar(20)

        );
        insert t2 value(1,'小明');
        insert t2 value(1,'小张');

        create table t2(
            id int unique,
            name varchar(20)
            [constraint unique_name] unique(字段1,字段2,...)
        );

    <2>通过alter...modify/change
        alter table t2 modify/change name unique;
        alter table t2 ADD [constraint unique_name] unique(字段1,字段2,...);

    <3>删除约束
        alter table t2 drop index unique_name;
        drop index unique_name on table_name;

        注意：删除唯一约束时，如果没有给唯一约束起名称，可以使用删除建立了唯一约束的第一个列名。

3.4 主键约束

<1>创建约束
    create table t6(
        id int primary key,
        name varchar(20)
    );

<2>通过alter...modify/change
    alter table t6 modify/change id primary key;
    alter table t6 add [constraint pk_name] primary key(id,name);

<3>删除约束
    alter table t6 drop primary key;

注意：
    show create table 表名；查看当前表的约束设置

    一张表只能有一个主键
    删除主键后，当前列还保存非空约束
    删除主键前，保证当前主键列没有设置成自增长。
    create table t7(
        id int primary key auto_increment,
        name varchar(20)
    );

3.5 自增长约束

约束列的数量自动增加，设置到整数类型的列上
通常在主键上设置自增长约束

删除约束：alter...modify/change  

create table xxx() auto_increment=初始值；

3.6 外键约束

问题一：为什么要创建多张表？

    减少数据的冗余，避免资源的过度浪费       

    用户订单表：user_order
        用户名   密码    地址   联系方式  .....   订单编号   状态    日期          商品信息...
        小明      123   北京   15555225         0001      0      20180101     ...
        小明      123   北京   15555225         0002      1      20180108     ...
        小明      123   北京   15555225         0003      2      20180104     ...
        小明      123   北京   15555225         0004      3      20180101     ...
        小明      123   北京   15555225         0005      4      20180104     ...
        小明      123   北京   15555225         0006      5      20180101     ...

解决：拆表

    用户表 user     （主表）
        用户名   密码    地址   联系方式  
        小明      123   北京   15555225      


    订单表 order  （从表）
        订单编号   状态    日期          商品信息...  用户名
        0001      0      20180101     ...           小明 
        0002      1      20180108     ...           小明
        0003      2      20180104     ...           小明
        0004      3      20180101     ...           小明
        0005      4      20180104     ...           小明
        0006      5      20180101     ...           小明

建立关系

    建立关系：将主表中能够唯一的字段，添加到从表中。

添加约束

    添加外键约束

<0>准备
    create table users(
        uid int primary key auto_increment,
        uname varchar(20) not null unique,
        upwd varchar(20) not null
    );

    create table orders(
        oid int primary key auto_increment,
        stat tinyint not null,
        date datetime not null,
        uid int
    );

<1>添加约束
    create table orders(
        oid int primary key auto_increment,
        stat tinyint not null,
        date datetime not null,
        uid int,
        [constraint  fk_name] foreign key(uid) references users(uid)
    );

    通过：alter table orders add [constraint  fk_name] foreign key(uid) references users(uid);     


<2>删除外键约束
    alter table orders drop foreign key fk_name;
    如果不知道当前删除的外键的名称，show create table 表名；

问题二：表于表之间的关系？

    一对一：
    一对多：
    多对多： 订单表 ---------- 商品表 
            学生表 ---------- 课程表
                    关系表
                   sid  cid             

    需求分析:分析数据表（属性），分析表和表之间的关系
    逻辑设计：将需求转换成关系模型ER图
    表的设计：
    物理设计：

外键中的级联关系

1. on delete cascade  删除主表中的数据时，从表中的数据会随之一起删除；
2. on update cascade  更新主表中的数据时，从表中的数据会随之更新
3. on delete set null 删除主表中的数据时，从表中的数据会置空

设置级联删除：
    create table orders(
        oid int primary key auto_increment,
        stat tinyint not null,
        date datetime not null,
        uid int,
        constraint  fk_name foreign key(uid) references users(uid) on delete cascade
    );

3.7 无符号和零填充约束

添加约束：
    <1>创建表的时候添加
        create table t1(
            id int default 1 unsigned , -- 添加无符号约束
            name varchar(20) default '小明',
            age int(3) zerofill -- 添加零填充约束
        );

    <2>通过alter...modify/change添加

删除约束：
    通过alter...modify/change

三、多表查询(重点)

1. 分类

<1>联合查询、合并查询 ： union /union all 
<2>连接查询:
        内连接：inner join on 
        外连接：
            左外连接：
            右外连接：
            交叉连接：
            全外连接：
        自然连接：natural join on
        自连接： 

<3>子查询:SQL语句的嵌套 

2. 合并查询

将两个select语句查询的结果合并在一起。

合并的方式一：union ,去除重复的数据
    create table t9(
        id int primary key auto_increment,
        name varchar(20)
    );

    create table t10(
        num int primary key auto_increment,
        name2 varchar(20)
    );

    insert t9 value(null,'a1');
    insert t9 value(null,'a2');
    insert t9 value(null,'a3');

    insert t10 value(default,'a1');
    insert t10 value(default,'a2');
    insert t10 value(default,'a4');

    select * from t9 union select * from t10;           

合并的方式二：union all        
    select * from t9 union all select * from t10;   


注意：被合并的两个结果集中的属性和属性的类型必须一致。

3. 连接查询

多个表的数据的乘积。
产生笛卡尔积现象。
给两张表建立关联关系，过滤错误的数据

insert users value(1,'xiaozhang','123');
insert users value(2,'xiaoli','456');
insert users value(3,'xiaowang','789');

insert orders value(1001,1,now(),1);
insert orders value(1002,1,now(),1);

insert orders value(1003,1,now(),3);
insert orders value(1004,1,now(),3);

select * from users,orders; -- 笛卡尔积现象
select * from users,orders where users.uid=orders.uid; 

3.1 内连接

语法格式：
    select u.uid  from users u inner join orders o on u.uid=o.uid;  

3.2 外连接

左外连接：
    select u.uid,u.uname,o.oid, o.date from users  u  left outer join orders as o on u.uid=o.uid;   
    先查左表，然后查询右表。右表中满足条件的数据项显示，不满足条件的则显示null。

    select u.uid,u.uname,o.oid, o.date from orders o  left outer join user as u on u.uid=o.uid;     
    先查左表，然后查询右表。右表中满足条件的数据项显示，不满足条件的则显示null。

右外连接：
    select u.uid,u.uname,o.oid, o.date from users  u  right outer join orders as o on u.uid=o.uid;  
    先查右表，然后查询左表。左表中满足条件的数据项显示，不满足条件的则显示null;

自然连接：natural ,自动寻找主外键连接等式
        select * from users natural join orders ;   
        select * from users natural left join orders ;
        select * from users natural right join orders ;

自连接：自己连接自己
    create table employee(
        num int primary key ,
        name varchar(20) not null,
        leadnum int
    ) ;

    insert employee value(1001,'蒙奇D龙');
        insert employee value(1003,'伊万科娃',1001);
        insert employee value(1004,'暴君熊',1001);

    insert employee value(1002,'五老星');
        insert employee value(1005,'七武海',1002);
        insert employee value(1006,'黄猿',1002);
        insert employee value(1007,'赤犬',1002);
        insert employee value(1008,'青稚',1002);

    需求：打印伊万科娃和其领导的信息
        select * from employee e1 ,employee e2 where e1.num=e2.leadnum and e2.name='伊万科娃'；

        select * from employee e1 ,employee e2 where e1.num=e2.leadnum and e1.name='伊万科娃';
            查找伊万科娃的下属

4. 子查询

select查询语句的嵌套

子查询中的查询语句的位置：
    where后面：作为条件
    from后面：作为查询的数据表

例如：
    查询订单表中价格大于平均订单价格的订单信息
        select * from orders where price>avg(price);  -- 不可行

    第一步：求订单的平均价格
        select avg(price) from orders;
    第二步：查询  
        select * from orders where price>(select avg(price) from orders) ;      


练习：
    1. 查询密码和用户小刘一样的用户信息
    2. 查询订单数量大于1的用户信息

四、用户的创建和授权

授权语法格式：
    GRANT 权限  ON  数据库.数据表   TO 用户名@ip地址 IDENTIFIED  BY 密码;

例如：
    0. 使用root账号登陆数据库
    1. create database testDB;

    2. grant all privileges on testDB.* to xiaoming@localhost identified by '123';
       flush privileges;

    3. grant select ,update on testDB.* to laowang@localhost identified by '123';

    4. grant select ,update ,delete ,create ,drop ,alter on *.* to wangayi@'%' identified by '123';

    注意：'%'对非本地的主机授权，赋予所有权限可以使用：all privileges  

删除用户：
    drop user 用户名@"%"

作业：

1. 用一条sql语句查询出“每门”课程都大于80分的学生姓名
    name   kecheng   fenshu
    张三     语文       81
    张三     数学       75
    李四     语文       76
    李四     数学       90
    王五     语文       81
    王五     数学       100
    王五     英语       90




2. 添加他向student表插入记录的INSERT语句如下：
    INSERT INTO student VALUES( 1,'张老大', 'n',1985,'计算机系', '北京市海淀区');
    INSERT INTO student VALUES( 2,'张老二', 'n',1986,'中文系', '北京市昌平区');
    INSERT INTO student VALUES( 3,'张三', 'v',1990,'中文系', '湖南省永州市');
    INSERT INTO student VALUES( 4,'李四', 'n',1990,'英语系', '辽宁省阜新市');
    INSERT INTO student VALUES( 5,'王五', 'v',1991,'英语系', '福建省厦门市');
    INSERT INTO student VALUES( 6,'王六', 'n',1988,'计算机系', '湖南省衡阳市');
-- 向score表插入记录的INSERT语句如下：
    INSERT INTO score VALUES(NULL, '计算机',98,1);
    INSERT INTO score VALUES(NULL, '英语', 80,1);
    INSERT INTO score VALUES(NULL, '计算机',65,2);
    INSERT INTO score VALUES(NULL, '中文',88,2);
    INSERT INTO score VALUES(NULL, '中文',95,3 );
    INSERT INTO score VALUES(NULL, '计算机',70,4);
    INSERT INTO score VALUES(NULL, '英语',92,4);
    INSERT INTO score VALUES(NULL, '英语',94,5);
    INSERT INTO score VALUES(NULL, '计算机',90,6);
    INSERT INTO score VALUES(NULL, '英语',85,6);
-- 6).从student表中查询计算机系和英语系的学生的信息

-- 7).从student表中查询年龄28~32岁的学生信息 

-- 8).从student表中查询每个院系有多少人 

-- 9).从score表中查询每个科目的最高分

-- 10).查询李四的考试科目（c_name）和考试成绩（grade）

-- 11).用连接的方式查询所有学生的信息和考试信息

-- 12).计算每个学生的总成绩

-- 13).计算每个考试科目的平均成绩

-- 14).查询计算机成绩低于95的学生信息

-- 15).查询同时参加计算机和英语考试的学生的信息

-- 16).将计算机考试成绩按从高到低进行排序

-- 17).从student表和score表中查询出学生的学号，然后合并查询结果 

-- 18).查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

-- 19).查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩