MySQL 練習(xí)2

*   Mysql基礎(chǔ)練習(xí)
    *   -- 創(chuàng)建數(shù)據(jù)庫(kù)
    *   create database b default CHARACTER set utf8 collate utf8_general_ci;

    *   -- 使用數(shù)據(jù)庫(kù)
    *   USE a;

    *   -- 創(chuàng)建表
    *   CREATE TABLE b (id INT(4),NAME VARCHAR(12),sex VARCHAR(2));

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

    *   -- 插入數(shù)據(jù)
    *   INSERT INTO b VALUES (
    *   '1','ZhangSan','F');

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

    *   --修改數(shù)據(jù)
    *   UPDATE b SET NAME = 'WangWu' WHERE id = '2';

    *   --刪除數(shù)據(jù)
    *   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;

    *   -- 簡(jiǎn)單的多表查詢
    *   -- 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;

*   左連接  右連接    分組   統(tǒng)計(jì) 求和  平均值  包含  不包含  前幾條  第幾條  修改列名  增加列名  刪除列名

*   存儲(chǔ)過(guò)程

*   設(shè)置主鍵

*   題目1
    *   新建表 a(id,name), b(id,English,Math)
    *   表中插入數(shù)據(jù)a(id=1,2,3,4), b(id=1,2,3)，其中a、b表的id字段都設(shè)置為主鍵，其他字段值自己隨便輸入
    *   題目：
        *   查詢出每個(gè)學(xué)生的姓名及總分
        *   查詢出英語(yǔ)成績(jī)排第2的學(xué)生的姓名

*   題目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' , '錢(qián)電' , '1990-12-21' , '男');
    *   INSERT INTO c VALUES('03' , '孫風(fēng)' , '1990-05-20' , '男');
    *   INSERT INTO c VALUES('04' , '李云' , '1990-08-06' , '男');
    *   INSERT INTO c VALUES('05' , '孫風(fēng)' , '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' , '錢(qián)電' , '1990-12-21' , '男');
    *   INSERT INTO c VALUES('03' , '孫風(fēng)' , '1990-05-20' , '男');
    *   INSERT INTO c VALUES('04' , '李云' , '1990-08-06' , '男');
    *   INSERT INTO c VALUES('05' , '孫風(fēng)' , '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包含有哪些字段及字段的數(shù)據(jù)類型
            *   desc c;
        *   修改sid字段類型為int(4),默認(rèn)值為88
            *   ALTER table c MODIFY sid int(4) default 88;
        *   給表c新增字段address，字段類型為varchar(20)，并且為非空，默認(rèn)值設(shè)置為‘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設(shè)置為主鍵
            *   alter table c add PRIMARY key (sid);
        *   查詢不重復(fù)的記錄
            *   select DISTINCT * from c;
        *   刪除前面5條記錄
            *   DELETE from c limit 5;
        *   清空表c所有數(shù)據(jù)
            *   truncate c;
        *   刪除后10行數(shù)據(jù)
            *   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;
        *   統(tǒng)計(jì)
            *   select count(*) from c;

*   請(qǐng)用SQL語(yǔ)句查詢部門(mén)平均成績(jī)，要求按照部門(mén)的字符串降序排序，其部門(mén)名不含有 “Services”
    *   Employee 表結(jié)構(gòu)如下：
    *   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;
        *   *   -- 請(qǐng)用SQL語(yǔ)句查詢部門(mén)平均成績(jī)，要求按照部門(mén)的字符串降序排序，其部門(mén)不含有 “Services”
            *   select depart_name,avg(salary) from Employee where depart_name not like '%Services%' group by depart_id order by depart_name desc ;

*   成績(jī)排名（第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
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容