*   Mysql基础练习
    *   -- 创建数据库
    *   create database b default CHARACTER set utf8 collate utf8_general_ci;

    *   -- 使用数据库
    *   USE a;

    *   -- 创建表
    *   CREATE TABLE b (id INT(4),NAME VARCHAR(12),sex VARCHAR(2));

    *   -- 查询b表 *表示所有字段
    *   SELECT * FROM b;

    *   -- 插入数据
    *   INSERT INTO b VALUES (
    *   '1','ZhangSan','F');

    *   INSERT INTO b VALUES (
    *   '2','ww','M');

    *   --修改数据
    *   UPDATE b SET NAME = 'WangWu' WHERE id = '2';

    *   --删除数据
    *   DELETE FROM b WHERE id = '1';

    *   CREATE TABLE c (id INT(4),course INT(6));

    *   INSERT INTO c VALUES (
    *   '1','88');

    *   INSERT INTO c VALUES (
    *   '2','98');

    *   INSERT INTO c VALUES (
    *   '3','90');

    *   INSERT INTO c VALUES (
    *   '4','67');

    *   SELECT id,course FROM c;

    *   -- 简单的多表查询
    *   -- id =2 , name,course
    *   SELECT b.name,c.course
    *   FROM b,c
    *   WHERE b.id = c.id;

    *   -- join on
    *   SELECT b.`name`,c.course
    *   FROM b
    *   JOIN c
    *   ON b.id = c.id;

    *   -- 排序 desc   asc
    *   SELECT *
    *   FROM c
    *   ORDER BY course ASC;

*   左连接  右连接    分组   统计 求和  平均值  包含  不包含  前几条  第几条  修改列名  增加列名  删除列名

*   存储过程

*   设置主键

*   题目1
    *   新建表 a(id,name), b(id,English,Math)
    *   表中插入数据a(id=1,2,3,4), b(id=1,2,3)，其中a、b表的id字段都设置为主键，其他字段值自己随便输入
    *   题目：
        *   查询出每个学生的姓名及总分
        *   查询出英语成绩排第2的学生的姓名

*   题目2
    *   use test;
    *   DROP TABLE IF EXISTS c;
    *   CREATE TABLE c(Sid VARCHAR(10),Sname VARCHAR(10),Sage DATETIME,Ssex VARCHAR(10));
    *   INSERT INTO c VALUES('01' , '赵雷' , '1990-01-01' , '男');
    *   INSERT INTO c VALUES('02' , '钱电' , '1990-12-21' , '男');
    *   INSERT INTO c VALUES('03' , '孙风' , '1990-05-20' , '男');
    *   INSERT INTO c VALUES('04' , '李云' , '1990-08-06' , '男');
    *   INSERT INTO c VALUES('05' , '孙风' , '1991-12-01' , '男');
    *   INSERT INTO c VALUES('06' , '吴兰' , '1992-03-01' , '女');
    *   INSERT INTO c VALUES('07' , '郑竹' , '1989-07-01' , '女');
    *   INSERT INTO c VALUES('08' , '王菊' , '1990-01-20' , '女');
    *   INSERT INTO c VALUES('01' , '赵雷' , '1990-01-01' , '男');
    *   INSERT INTO c VALUES('02' , '钱电' , '1990-12-21' , '男');
    *   INSERT INTO c VALUES('03' , '孙风' , '1990-05-20' , '男');
    *   INSERT INTO c VALUES('04' , '李云' , '1990-08-06' , '男');
    *   INSERT INTO c VALUES('05' , '孙风' , '1991-12-01' , '男');
    *   INSERT INTO c VALUES('06' , '吴兰' , '1992-03-01' , '女');
    *   INSERT INTO c VALUES('07' , '郑竹' , '1989-07-01' , '女');
    *   INSERT INTO c VALUES('08' , '王菊' , '1990-01-20' , '女');
        *   查询表test.c包含有哪些字段及字段的数据类型
            *   desc c;
        *   修改sid字段类型为int(4),默认值为88
            *   ALTER table c MODIFY sid int(4) default 88;
        *   给表c新增字段address，字段类型为varchar(20)，并且为非空，默认值设置为‘cdtest'
            *   alter table c add address varchar(20) not null default 'cdtest';
        *   将列address改名为addr
            *   alter table c change column address addr varchar(20);
        *   删除列address
            *   ALTER table c drop COLUMN address;
        *   将表c中字段Sid设置为主键
            *   alter table c add PRIMARY key (sid);
        *   查询不重复的记录
            *   select DISTINCT * from c;
        *   删除前面5条记录
            *   DELETE from c limit 5;
        *   清空表c所有数据
            *   truncate c;
        *   删除后10行数据
            *   delete from c where Sid = 10 and id not in(select [t.id](http://t.id) from (select id from ShouyeData where funcid = 10 limit 10) as t)
        *   修改表名
            *   ALTER  TABLE table_name RENAME TO new_table_name
        *   删除pid的primary key约束
            *   alter table products drop primary key;
        *   统计
            *   select count(*) from c;

*   请用SQL语句查询部门平均成绩，要求按照部门的字符串降序排序，其部门名不含有 “Services”
    *   Employee 表结构如下：
    *   employee_id, employee_name, depart_id, depart_name, salary
        *   use test;

        *   create
        *   table
        *   Employee(
        *   employee_id int(6),
        *   employee_name varchar(20),
        *   depart_id int(6),
        *   depart_name varchar(20),
        *   salary int(10)
        *   );

        *   insert
        *   into
        *   Employee
        *   values('1','ZhangSan','01','Sales','10021'
        *   );

        *   insert
        *   into
        *   Employee
        *   values('2','LiSi','01','Sales','18021'
        *   );

        *   insert
        *   into
        *   Employee
        *   values('3','WangWu','02','Services','6800'
        *   );

        *   insert
        *   into
        *   Employee
        *   values('4','ZhaoLiu','02','Services','7990'
        *   );

        *   insert
        *   into
        *   Employee
        *   values('5','Zhao5','03','CaiWu','6600'
        *   );

        *   insert
        *   into
        *   Employee
        *   values('6','Zhao6','03','CaiWu','8600'
        *   );

        *   select * from test.Employee;
        *   *   -- 请用SQL语句查询部门平均成绩，要求按照部门的字符串降序排序，其部门不含有 “Services”
            *   select depart_name,avg(salary) from Employee where depart_name not like '%Services%' group by depart_id order by depart_name desc ;

*   成绩排名（第2）：
    *   USE test;

    *   CREATE TABLE tt(id INT(4),NAME VARCHAR(20),score DOUBLE);

    *   INSERT INTO tt VALUE ('1','z','23');
    *   INSERT INTO tt VALUE ('2','z1','99.5');
    *   INSERT INTO tt VALUE ('3','z2','96.5');
    *   INSERT INTO tt VALUE ('4','z3','96.5');

    *   SELECT * FROM tt;

    *   SELECT * FROM tt ORDER BY score DESC LIMIT 1,1;

        [图片上传失败...(image-9b4831-1543979146713)]

    *   SELECT * FROM tt ORDER BY score DESC LIMIT 1,2;

        [图片上传失败...(image-3e2f8f-1543979146713)]

    *   SELECT t.score,(SELECT COUNT(s.score)+1 FROM tt s WHERE s.score>t.score) rank FROM tt t ORDER BY t.score DESC;

        [图片上传失败...(image-58edc7-1543979146713)]

    *   SELECT * FROM
    *   (SELECT t.score,(SELECT COUNT(s.score)+1 FROM tt s WHERE s.score>t.score) rank FROM tt t ORDER BY t.score DESC)aa
    *   WHERE aa.rank = '2';
    *   [图片上传失败...(image-282ddc-1543979146713)]

    *   SELECT t.score,(SELECT COUNT(s.score)+1 FROM (SELECT s.score,COUNT(s.score) FROM tt s GROUP BY score ORDER BY score DESC) s WHERE s.score>t.score) rank FROM tt t ORDER BY t.score DESC;

        [图片上传失败...(image-8a1706-1543979146713)]

*   SELECT a.sid FROM
*   (SELECT * FROM sc WHERE cid='01') a,
*   (SELECT * FROM sc WHERE cid='02') b
*   WHERE a.sid=b.sid AND a.score > b.score;
*   或：
*   SELECT aa.sid FROM
*   sc AS aa,
*   sc AS bb
*   WHERE aa.Sid=bb.sid
*   AND aa.cid='01'
*   AND bb.cid='02'
*   AND aa.`score`>bb.score