MYsql命令練習(xí)11-18

mysql命令練習(xí)題3

代碼塊
-- 1.創(chuàng)建test數(shù)據(jù)庫
CREATE DATABASE test CHARACTER set utf8

use test

-- 2.在test數(shù)據(jù)庫中創(chuàng)建emp表户矢，表結(jié)構(gòu)如下圖所示(id設(shè)置為主鍵自增長)
CREATE table emp(
    empno int(11) PRIMARY key auto_increment not null,
    ename VARCHAR(50),
    job VARCHAR(50),
    mgr int DEFAULT null,
    hiredate date,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2) DEFAULT null,
    deptno int(11)
)

-- 3.向emp表中添加記錄如下圖所示
insert into emp VALUES(1001,"甘寧","文員",1013,"2000-12-17",8000.00,DEFAULT,20),
        (1002,"黛綺絲","銷售員",1006,"2001-02-20",16000.00,3000.00,30),
        (1003,"殷天正","銷售員",1006,"2001-02-22",12500.00,5000.00,30),
        (1004,"劉備","經(jīng)理",1009,"2001-04-02",29750.00,DEFAULT,20),
        (1005,"謝遜","銷售員",1006,"2001-09-28",12500.00,14000.00,30),
        (1006,"關(guān)羽","經(jīng)理",1009,"2001-05-01",28500.00,DEFAULT,30),
        (1007,"張飛","經(jīng)理",1009,"2001-09-01",24500.00,DEFAULT,10),
        (1008,"諸葛亮","分析師",1004,"2007-04-19",30000.00,DEFAULT,20),
        (1009,"曾阿牛","董事長",DEFAULT,"2001-11-17",50000.00,DEFAULT,10),
        (1010,"韋一笑","銷售員",1006,"2001-09-08",15000.00,0.00,30),
        (1011,"周泰","文員",1008,"2007-05-23",11000.00,DEFAULT,20),
        (1012,"程普","文員",1006,"2001-12-03",9500.00,DEFAULT,30),
        (1013,"龐統(tǒng)","分析師",1004,"2001-12-03",30000.00,DEFAULT,20),
        (1014,"黃蓋","文員",1007,"2002-01-23",13000.00,DEFAULT,10),
        (1015,"張三","文員",1007,"2002-01-23",53000.00,DEFAULT,50)

-- 1查詢表中所有內(nèi)容（10分）
select * from emp

-- 2.查詢表中姓名是張三的所有消息記錄（10分）
select * from emp  where ename ="張三"

-- 3.查詢表中姓名是三個字組成的所有員工的ename,job,sal字段的對應(yīng)信息（10分）
select * from emp where ename like '___'
select * from emp where ename like '曾%'
-- 4.查詢表中empno字段從1004至1008所有員工的記錄（10分）
select * from emp where mgr >1003 and mgr <1009

-- 5.查詢表中所有job字段是文員并且姓名是黃蓋的員工的所有信息（10分）
select * from emp where job ="文員" and  ename ="黃蓋"

-- 6.查詢表中在2001年以后入職的員工信息（10分）
select * from emp where hiredate  > "2001-1-1"

-- 7.查詢表中獎金（COMM）是NULL的員工信息（10分）

select * from emp where comm is null;

mysql命令練習(xí)題11

代碼塊
create table student(
        id int PRIMARY KEY auto_increment,
        name VARCHAR(20) not null,
        score int not null,
        address VARCHAR(50) not null,
        useremail varchar(20) not null
)
insert into student values(1,'張三',98,'北京','111111@qq,com'),
(2,'李四',88,'上海','111112@qq.com'),(3,'王五',78,'廣州','111113@qq.com'),
(4,'趙六',68,'深圳','111114@qq.com'),(5,'孫七',58,'杭州','111115@qq.com'),
(6,'小紅',48,'北京','111116@qq.com'),(7,'小黑',99,'上海','111117@qq.com'),
(8,'小綠',100,'杭州','111118@qq.com'),(9,'小粉',60,'杭州','111119@qq.com'),
(10,'小紫',70,'黑龍江','1111110@qq.com')

4.  使用sql語句查詢出表中的所有內(nèi)容
SELECT * FROM student 
5.  使用sql語句查詢出表中所有score>70的同學(xué)的id,name,score
SELECT * FROM student WHERE score>70

6.  更改name字段的數(shù)據(jù)類型為varchar(50)
    ALTER TABLE student MODIFY name varchar(50)

7.  向表中添加一個字段，字段名稱為“pingjia”,字段類型為varchar(20)
ALTER TABLE student add pringjia varchar(20)

8.  更改姓名是張三的同學(xué)的分?jǐn)?shù)為88
    UPDATE student set score= score -10 WHERE id =1

9.  如果80分為及格線锰茉，查詢出所有及格的同學(xué)的詳細信息
 SELECT * from  student WHERE score >79

10. 把姓名是“小紅”的同學(xué)的分?jǐn)?shù)在原來的基礎(chǔ)上+40
    UPDATE student set score = score +40 WHERE id =6

11. 使用關(guān)鍵字in,查詢id值是1或5或7的同學(xué)的基本信息
        SELECT * FROM student WHERE id =1 or id =5 or id=7
SELECT * FROM student WHERE id in("1","5","7")

12. 查詢id值在5至8的所有同學(xué)的基本信息
 SELECT * FROM student WHERE id between 5 and 8
 SELECT * FROM student WHERE  id>4 and id<9

13. 查詢姓名是小紅并且分?jǐn)?shù)大于60的同學(xué)的基本信息
SELECT * FROM student WHERE name = "小紅" and  score >60

14. 查詢姓名是小紅或者分?jǐn)?shù)大于90的同學(xué)的基本信息
SELECT * FROM student WHERE name = "小紅" or score >90

15. 查詢score字段值是NULL的同學(xué)的基本信息
SELECT * FROM student WHERE score is NOT null

16. 查詢score字段值不是NULL的同學(xué)的id和name
SELECT id,name FROM student WHERE score is NOT null

mysql命令練習(xí)題12

代碼塊
create table studentx(
        id int PRIMARY KEY auto_increment,
        name VARCHAR(20) not null,
        score int not null,
        address VARCHAR(50) not null,
        useremail varchar(20) not null
)
insert into studentx values(1,'張三',98,'北京','111111@qq,com'),
(2,'李四',88,'上海','111112@qq.com'),(3,'王五',78,'廣州','111113@qq.com'),
(4,'趙六',68,'深圳','111114@qq.com'),(5,'孫七',58,'杭州','111115@qq.com'),
(6,'小紅',48,'北京','111116@qq.com'),(7,'小黑',99,'上海','111117@qq.com'),
(8,'小綠',100,'杭州','111118@qq.com'),(9,'小粉',60,'杭州','111119@qq.com'),
(10,'小紫',70,'黑龍江','1111110@qq.com')


4.  使用sql語句查詢出表中id,name,和address字段的所有內(nèi)容
     SELECT id,name,address FROM studentx

5.  使用sql語句查詢出表中所有同學(xué)的id,name,score
     SELECT id,name,score FROM studentx

6.  更改useremail字段的數(shù)據(jù)類型為varchar(50)
   ALTER TABLE studentx MODIFY useremail varchar(50)

7.  向表中添加一個字段，字段名稱為“pingjia”,字段類型為varchar(20)
   ALTER TABLE studentx add pingjia varchar(20)

8.  更改姓名是張三的同學(xué)的分?jǐn)?shù)為92
   UPDATE studentx set score = score-6 WHERE id =1
    SELECT * FROM studentx

9.  如果80分為及格線恕曲，查詢出所有不及格的同學(xué)的詳細信息
   SELECT * FROM studentx WHERE score <80 

10. 把姓名是“小紅”的同學(xué)的分?jǐn)?shù)在原來的基礎(chǔ)上+20
        UPDATE studentx set score=score+20 WHERE name= "小紅"

11. 使用關(guān)鍵字in,查詢id值是1或5或7的同學(xué)的基本信息
    SELECT * FROM studentx WHERE id in("1","5","7")

12. 查詢id值在4至9的所有同學(xué)的基本信息
        SELECT * FROM studentx WHERE id BETWEEN 4 and 9

13. 查詢姓名是小紅并且分?jǐn)?shù)大于60的同學(xué)的基本信息
        SELECT * FROM studentx WHERE name="小紅" and score >60

14. 查詢姓名是小紅或者分?jǐn)?shù)大于90的同學(xué)的基本信息
    SELECT * FROM studentx WHERE name="小紅" or score >90

15. 查詢score字段值是NULL的同學(xué)的基本信息
        SELECT * FROM studentx WHERE score is NOT null

16. 查詢name不是張三的同學(xué)的id鹏氧，name，和score
        SELECT id, name,score FROM studentx WHERE name!="張三"

mysql命令練習(xí)題13

代碼塊
create table studentxx(
        id int PRIMARY KEY auto_increment,
        name VARCHAR(20) not null,
        score int not null,
        address VARCHAR(50) not null,
        useremail varchar(20) not null
)
insert into studentxx values(1,'張三',98,'北京','111111@qq,com'),
(2,'李四',88,'上海','111112@qq.com'),(3,'王五',78,'廣州','111113@qq.com'),
(4,'趙六',68,'深圳','111114@qq.com'),(5,'孫七',58,'杭州','111115@qq.com'),
(6,'小紅',48,'北京','111116@qq.com'),(7,'小黑',99,'上海','111117@qq.com'),
(8,'小綠',100,'杭州','111118@qq.com'),(9,'小粉',60,'杭州','111119@qq.com'),
(10,'小紫',70,'黑龍江','1111110@qq.com')

4.  使用sql語句查詢出表中的所有內(nèi)容
        SELECT * FROM studentxx

5.  使用sql語句查詢出表中所有同學(xué)的id,name,score
        SELECT id,name,score FROM studentxx

6.  更改name字段的數(shù)據(jù)類型為varchar(50)
        ALTER TABLE studentxx MODIFY name varchar(50)

7.  向表中添加一個字段佩谣，字段名稱為“pingjia”,字段類型為varchar(50)
        ALTER TABLE studentxx add pingjia varchar(50)

8.  更改姓名是小紫的同學(xué)的分?jǐn)?shù)為100
        UPDATE studentxx set score = score+30 WHERE name = "小紫"
    
9.  如果80分為及格線把还，查詢出所有及格的同學(xué)的詳細信息
        SELECT * FROM studentxx WHERE score>80
        
10. 把姓名是“小紅”的同學(xué)的分?jǐn)?shù)在原來的基礎(chǔ)上+30
        UPDATE studentxx set score = score+30 WHERE name = "小紅"

11. 使用關(guān)鍵字in,查詢id值是2或4或8的同學(xué)的基本信息
        SELECT * FROM studentxx WHERE id in("2","4","8")
        
12. 查詢id值在5至8的所有同學(xué)的基本信息
        SELECT * FROM studentxx WHERE id BETWEEN 5 and 8
        
13. 查詢姓名是小紅并且分?jǐn)?shù)大于60的同學(xué)的基本信息
        SELECT * FROM studentxx WHERE name="小紅" AND score>60

14. 查詢姓名是小紅或者分?jǐn)?shù)大于90的同學(xué)的基本信息
        SELECT * FROM studentxx WHERE name="小紅" or score>90

15. 查詢score字段值是不為NULL的同學(xué)的基本信息
        SELECT * FROM studentxx WHERE score is not null

16. 查詢id值在6至8的同學(xué)的name和score
        SELECT id,name,score FROM studentxx WHERE id BETWEEN 6 AND 8

mysql命令練習(xí)題14

代碼塊
create table studenta(
        id int PRIMARY KEY auto_increment,
        name VARCHAR(20) not null,
        score int not null,
        address VARCHAR(50) not null,
        useremail varchar(20) not null
)
insert into studenta values(1,'張三',98,'北京','111111@qq,com'),
(2,'李四',88,'上海','111112@qq.com'),(3,'王五',78,'廣州','111113@qq.com'),
(4,'趙六',68,'深圳','111114@qq.com'),(5,'孫七',58,'杭州','111115@qq.com'),
(6,'小紅',48,'北京','111116@qq.com'),(7,'小黑',99,'上海','111117@qq.com'),
(8,'小綠',100,'杭州','111118@qq.com'),(9,'小粉',60,'杭州','111119@qq.com'),
(10,'小紫',70,'黑龍江','1111110@qq.com')

4.  使用sql語句查詢出表中的所有內(nèi)容
        SELECT * from studenta

5.  使用sql語句查詢出表中所有同學(xué)的id,name,score
        SELECT id,name,score from studenta
6.  更改useremail字段的數(shù)據(jù)類型為varchar(50)
        ALTER TABLE studenta MODIFY useremail varchar(50)

7.  向表中添加一個字段，字段名稱為“pingjia”,字段類型為varchar(20)
        ALTER TABLE studenta add  pingjia varchar(50)

8.  更改姓名是李四的同學(xué)的分?jǐn)?shù)為48
        UPDATE studenta set  score =48 WHERE name ="李四"

9.  如果80分為及格線茸俭，查詢出所有不及格的同學(xué)的詳細信息
        SELECT * from studenta WHERE score< 80 

10. 把姓名是“小紅”的同學(xué)的分?jǐn)?shù)在48的基礎(chǔ)上+40
        UPDATE studenta set score= score+40 WHERE name="小紅"

11. 使用關(guān)鍵字in,查詢id值是3或9或10的同學(xué)的基本信息
        SELECT * from studenta WHERE id in("3","9","10")

12. 查詢id值在2至6的所有同學(xué)的基本信息
        SELECT * from studenta WHERE id BETWEEN 2 and 6

13. 查詢姓名是小紅并且分?jǐn)?shù)大于60的同學(xué)的基本信息
        SELECT * from studenta WHERE name="小紅" and  score>60

14. 查詢姓名是張三或者分?jǐn)?shù)大于90或者address字段值是杭州的同學(xué)的基本信息
        SELECT * from studenta WHERE name="張三" or score>90 or address ="杭州"

15. 查詢score字段值是NULL的同學(xué)的基本信息
        SELECT * from studenta WHERE score is not null 

16. 查詢score的值在68-80的同學(xué)的id和name
        SELECT id,name,score from studenta WHERE score>67 and score<81

mysql命令練習(xí)題15

代碼塊
-- 創(chuàng)建db_test數(shù)據(jù)庫

CREATE DATABASE db_testx CHARSET='utf8'
use db_testx

-- 在test數(shù)據(jù)庫中創(chuàng)建teacher表吊履，表結(jié)構(gòu)如下圖所示(id設(shè)置為主鍵自增長)
CREATE TABLE teacher(
Number int PRIMARY KEY auto_increment,
Name VARCHAR(30),
Sex enum('男','女'),
DepNo int,
Salary int,
Address VARCHAR(30) 
)

-- 向teacher表中添加記錄如下圖所示

INSERT INTO teacher VALUES(201,'劉蕓','女',604,4500,'河北省滄州'),
(202,'周星馳','男',601,1600,'山東省濟南'),
(203,'秦牧之','男',604,2800,'北京昌平'),
(204,'周潤發(fā)','男',602,3500,'河北省滄州'),
(205,'林雙','女',605,1200,'河北省保定'),
(206,'張帆','女',602,3000,'北京市昌平'),
(207,'陳友涼','男',604,2800,'山西省大同'),
(208,'張國榮','男',601,2500,'山東省煙臺')

-- 修改表名為”TS”
alter table teacher rename ts

-- 向表中添加記錄，字段對應(yīng)值分別為（209调鬓，毛不易率翅，男，604袖迎，4000冕臭，黑龍江齊齊哈爾）
insert into ts(Number,Name,Sex,DepNo,Salary,Address) values(201,"劉蕓","女",604,4500,"河北省滄州")

-- 查詢TS表中所有記錄
select * from ts


-- 刪除表中Number是201并且sex是女的教師信息
DELETE FROM ts WHERE Number = 201 and Sex ="女"

-- 查詢表中Number字段的值是202，205或207教師的所有記錄
select * from ts WHERE Number in('202','205','207')

-- 修改表中Number值是202教師的姓名為“馬云”
update ts set Name ="馬云" where Number ="202" and Name = "周星馳"

-- 按照工資對員工信息進行降序排序
select * from ts  order by Salary desc, Name asc;

mysql命令練習(xí)題16

代碼塊
-- 在test數(shù)據(jù)庫中創(chuàng)建teacher表燕锥，表結(jié)構(gòu)如下圖所示(id設(shè)置為主鍵自增長)
CREATE TABLE teacher(
Number int PRIMARY KEY auto_increment,
Name VARCHAR(30),
Sex enum('男','女'),
DepNo int,
Salary int,
Address VARCHAR(30) 
)

-- 向teacher表中添加記錄如下圖所示
INSERT INTO teacher VALUES(201,'劉蕓','女',604,4500,'河北省滄州'),
(202,'周星馳','男',601,1600,'山東省濟南'),
(203,'秦牧之','男',604,2800,'北京昌平'),
(204,'周潤發(fā)','男',602,3500,'河北省滄州'),
(205,'林雙','女',605,1200,'河北省保定'),
(206,'張帆','女',602,3000,'北京市昌平'),
(207,'陳友涼','男',604,2800,'山西省大同'),
(208,'張國榮','男',601,2500,'山東省煙臺')

-- 修改表名為”TS”
alter table teacher rename tsx

-- 向表中添加記錄辜贵，字段對應(yīng)值分別為（209，林青霞归形，女托慨，604，4000暇榴，黑龍江齊齊哈爾)
insert into tsx(Number,Name,Sex,DepNo,Salary,Address) values (209,"林青霞","女",604,4000,"黑龍江齊齊哈爾")

-- 查詢TS表中Number,Name,Sex字段對應(yīng)的記錄
select Number,Name,Sex from tsx 


-- 刪除表中Number是201或者Name是陳友諒的教師信息
delete from tsx  where Number=201 or Name ="陳友涼"


-- 查詢表中Number字段的值從202至206教師的所有記錄
select * from tsx where Number >201 and Number <207
select * from tsx where Number BETWEEN 202 and 206
-- 修改表中Name值是張國榮教師的性別為“女”
update tsx set Sex = "女"  where Name="張國榮" and Sex="男"

-- 按照工資對員工信息進行升序排序
select * from tsx  order by Salary asc;

mysql命令練習(xí)題17

代碼塊
CREATE TABLE teacher(
Number int PRIMARY KEY auto_increment,
Name VARCHAR(30),
Sex enum('男','女'),
DepNo int,
Salary int,
Address VARCHAR(30) 
)

3.向teacher表中添加記錄如下圖所示
INSERT INTO tsxx VALUES(201,'劉蕓','女',604,4500,'河北省滄州'),
(202,'周星馳','男',601,1600,'山東省濟南'),
(203,'秦牧之','男',604,2800,'北京昌平'),
(204,'周潤發(fā)','男',602,3500,'河北省滄州'),
(205,'林雙','女',605,1200,'河北省保定'),
(206,'張帆','女',602,3000,'北京市昌平'),
(207,'陳友涼','男',604,2800,'山西省大同'),
(208,'張國榮','男',601,2500,'山東省煙臺')

alter table teacher rename TSxx

6.向表中添加記錄厚棵，字段對應(yīng)值分別為（209，林青霞蔼紧，女婆硬，604，4000奸例，黑龍江齊齊哈爾）
insert into tsxx VALUES (209,"林青霞","女",604,4000,"黑龍江齊齊哈爾")

select * from tsxx
7.向表中添加字段Hobby,設(shè)置類型為varchar(50),設(shè)置唯一約束
ALTER TABLE tsxx ADD  Hobby varchar(50) UNIQUE 

8.使用desc語句查看teacher表的表結(jié)構(gòu)
  DESC tsxx


9.查詢表中Number字段的值從202至206教師的所有記錄
    select * from tsxx WHERE Number>201 and Number<207
    select * from tsxx WHERE Number between 202 and 206

10.修改表中Name值是張國榮教師的性別為“女”
    UPDATE tsxx SET     Sex = "女" WHERE Name = "張國榮" and Sex="男"

11.刪除表中Sex是“男”或者Name是劉蕓的記錄
 delete from tsxx WHERE Name="劉蕓" or Sex = "男"

mysql命令練習(xí)題18

代碼塊
2.在test數(shù)據(jù)庫中創(chuàng)建teacher表彬犯，表結(jié)構(gòu)如下圖所示(id設(shè)置為主鍵自增長)
CREATE TABLE teacher(
Number int PRIMARY KEY auto_increment,
Name VARCHAR(30),
Sex enum('男','女'),
DepNo int,
Salary int,
Address VARCHAR(30) 
)

3.向teacher表中添加記錄如下圖所示
INSERT INTO teacher VALUES(201,'劉蕓','女',604,4500,'河北省滄州'),
(202,'周星馳','男',601,1600,'山東省濟南'),
(203,'秦牧之','男',604,2800,'北京昌平'),
(204,'周潤發(fā)','男',602,3500,'河北省滄州'),
(205,'林雙','女',605,1200,'河北省保定'),
(206,'張帆','女',602,3000,'北京市昌平'),
(207,'陳友涼','男',604,2800,'山西省大同'),
(208,'張國榮','男',601,2500,'山東省煙臺')


5.修改表名為”TEACHER”
ALTER TABLE teacher rename TEACHERX

6.向表中添加記錄，字段對應(yīng)值分別為（209查吊，林青霞谐区，女，604逻卖，4000宋列，黑龍江齊齊哈爾）
    INSERT INTO TEACHERX VALUES(209,"林青霞","女",604,4000,"黑龍江齊齊哈爾")

7.向表中添加字段Hobby,設(shè)置類型為varchar(50),設(shè)置唯一約束
    select * from TEACHERX
ALTER TABLE TEACHERX add Hobby varchar(50) UNIQUE


8.修改DepNo字段的類型為varchar(20)
   alter table TEACHERX change DepNo DepNo varchar(20)

9.查詢表中Number字段的值從202至206教師的所有記錄
 select * from TEACHERX WHERE Number BETWEEN 202 and 206
SELECT * from TEACHERX WHERE Number>201 and Number<207
10.修改表中Name值是張國榮教師的性別為“女”
    UPDATE TEACHERX set Sex ="女" WHERE Name ="張國榮" 
    
11.刪除表中Number是203或205或207的教師的記錄
DELETE FROM TEACHERX WHERE Number in("203","205","207")

第二題

代碼塊
-- 學(xué)生
create table student(
    sno varchar(20) primary key,
    sname  varchar(10),
    ssex varchar(10),
    sbirthday datetime,
    class varchar(20)
        )

-- 課程
create table course(
    cno varchar(20),    
        cname varchar(20) not null,
        tno varchar(20) not null,
        foreign key(tno) references teacher(tno)
        );

-- 老師
create table teacher(
        tno varchar(20) primary key,
        tname varchar(20) not null,
        tsex varchar(20) not null,
        tbirthday datetime,
        pro varchar(20) not null,
        depart varchar(20) not null
        )

-- 成績
create table score(
        sno varchar(20) not null,
        cno varchar(20) not null,
        degree decimal
        
        )



select * from student;

select * from teacher;

select * from course;

select * from score;

student 數(shù)據(jù)

insert into student values
('108','曾華','男','1977-09-01','95033'),
("105","框玥","男",'1977-09-01','95031'),
("107","王麗","女",'1977-09-01','95033'),
("109","李軍","男",'1977-09-01','95033'),
("103","王芳","女",'1977-09-01','95031'),
("104","陸君","男",'1977-09-01','95031')

select * from student




score數(shù)據(jù)

103 3-245   86
109 6-166   81
105 1-105   79
104 1-105   89
106 6-166   89
107 3-245   64
102 3-105   89
101 3-105   89

insert into score values('103','3-245',86),('109','6-166',81),
('105','1-105',79),('104','1-105',79),('106','6-166',89),
('107','3-245',64),('102','3-105',89),('101','3-105',89)


teacher數(shù)據(jù)

804 李誠  男   1958-12-02 00:00:00 副教授 計算機系
856 張旭  男   1969-03-13 00:00:00 講師  電子工程系
825 王萍  女   1972-05-05 00:00:00 助教  計算機系
831 劉冰  女   1977-08-14 00:00:00 助教  電子工程系


insert into teacher values('804','李誠','男','1958-12-02 00:00:00','副教授','計算機系'),
                                                    ('856','張旭','男','1969-03-13 00:00:00','講師','電子工程系'),
                                                    ('825','王萍','女','1972-05-05 00:00:00','助教','計算機系'),
                                                    ('831','劉冰','女','1977-08-14 00:00:00','助教','電子工程系')

course數(shù)據(jù)


3-105   計算機導(dǎo)論825
3-245   操作系統(tǒng)    804
6-166   數(shù)字電路    856
9-888   高等數(shù)學(xué)    831

insert into course values('3-105','計算機導(dǎo)論','825'),
                                                    ('3-245','操作系統(tǒng)','804'),
                                                    ('6-166','數(shù)字電路','856'),
                                                    ('9-888','高等數(shù)學(xué)','831')





-簡單的查詢練習(xí):- 1-
1，查詢student表中的所有記錄
select * from student

2评也，檢查student表中的所有記錄的sname炼杖，Ssex和class戈鲁。
select sname,ssex,class from student

3、查詢老師所有的單位即不重復(fù)的depart列
select * from teacher group by depart

4.查詢socre表中成績在從68到80之間的記錄嘹叫。
select * from score where degree between 68 and 80

5.查詢score表成績中為85婆殿、86或88。-
select * from score where degree in (85,86,88)

6罩扇、查詢student表中“95031”班或性別為“女”同學(xué)的同學(xué)記錄
select * from student where class = 95031 or ssex = '女'

7.以class降序查詢student表的所有記錄
select * from student order by class desc

8.以cno升序婆芦。degree降序查詢score表的所有記錄
select * from score order by cno asc,degree desc

9、查詢“95031”班學(xué)生人數(shù)喂饥。
select count(*) from student where class = 95031

10消约、查詢score表中的最高分的學(xué)生學(xué)號和課程號（子查詢或者排序）
select sno,cno from score where degree = (select max(degree) from score)


--  11.查看每門課的平均成績 (分組查詢)
select cno,sum(degree)/count(degree) from score group by cno


--  12.查詢score表中至少有2名同學(xué)選修的并以3開頭的課程的平均分?jǐn)?shù) having(組級過濾)
select cno,avg(degree) from score where cno like '3%' group by cno having count(cno) > 1


-- 13.查詢分?jǐn)?shù)大于70，小于90的sno列（條件查詢）
select sno from score where degree between 70 and 90



-- 14.查詢所有學(xué)生的 sname,con,和degree 列员帮。（多表查詢）
select stu.sname,s.cno,s.degree from score as s inner join student as stu on s.sno = stu.sno



--  15.查詢所有學(xué)生的sno或粮，cname和degree
select s.sno,s.degree,c.cname from score as s inner join course as c on s.cno = c.cno



-- 16. 查詢所有學(xué)生的sname，cname和degree(三表聯(lián)查)
select stu.sname,c.cname,s.degree from student as stu inner join course as c inner join score as s on stu.sno = s.sno and s.cno = c.cno



17.查閱"95031"班學(xué)生每門課的平均分
select cno,AVG(degree) from score where sno in (select sno from student where class = '95031') group by cno


-- 18.查詢選修”3-105“課程的 成績高于”109“號同學(xué)成績的所有同學(xué)的記錄

-- 子查詢的練習(xí)

-- 1.先查詢”109“號同學(xué)成績
select degree from score where sno = 109


-- 2.第二步找到3-105課程成績高于109
select * from score where cno = '3-105' and degree > (select degree from score where sno = 109)




-- 19捞高。查詢成績高于學(xué)號109氯材，課程號為3-105的成績的所有記錄 子查詢
select * from score where cno = '3-105' and degree > (select degree from score where sno = 109)



-- 20.查詢和學(xué)號為108.101的同學(xué)同年出生的所有學(xué)生的son。sname和sbirthday
-- year的函數(shù)與帶in 關(guān)鍵字的子查詢
select sbirthday from student where sno in (108,101)
select sno,sname,sbirthday from student where year(sbirthday) = (select year(sbirthday) from student where sno in (108,101))

--21. 查詢”張旭“教師任課的學(xué)生成績

-- 寫這個的時候先理清表之間的關(guān)系  想好我們到底要查的是什么數(shù)據(jù) 在對表中的關(guān)系進行處理

-- 1.先查詢教師的id號
select tno from teacher where tname = '張旭'

-- 2.根據(jù)教師的id號來查詢課程id
select cno from course where tno = (select tno from teacher where tname = '張旭')

-- 3硝岗。查詢score表根據(jù)課程號查詢學(xué)生的分?jǐn)?shù)
select degree from score where cno = (select cno from course where tno = (select tno from teacher where tname = '張旭'))

第10題

代碼塊
1氢哮、功能描述

    1 創(chuàng)建一個班級表(classes)，包含字段id,name
    
    2創(chuàng)建一個學(xué)生表(students),包含字段id,name,birthday,gender,score,class_id 

3 在班級表中添加三條記錄 id:1 name:一年級型檀，id:2 name:二年級冗尤，id:3 name:三年級 
    4 在員工表里添加六條記錄，

id:1 name:’張三’ birthday:”2010-11-10” gender:’男’ score:100 class_id:1
id:2 name:’李四’ birthday:”2012-11-10” gender:’女’ score:75 class _id:1
id:3 name:’王五’ birthday:”2014-11-10” gender:’男’ score:80 class _id:2
id:4 name:’小明’ birthday:”2013-11-10” gender:’女’ score:60 class _id:2
id:5 name:’小李’ birthday:”2015-11-10” gender:’女’ score:30 class _id:2
id:6 name:’小劉’ birthday:”2008-11-10” gender:’男’ score:90 class _id:3


5 使用pymysql模塊來查詢每個班級的平均分

如 一年級  100
   二年級  80
   三年級   70
6 修改張三的名字為張五

7 刪除班級名字為”三年級”的所有學(xué)生

8 查詢一年級分?jǐn)?shù)最高的人

9 查詢有學(xué)生的年級信息（用子查詢實現(xiàn)）

10 查詢以‘小’開頭的名字為一個字學(xué)生

11查詢出生日期在2008-11-10到2012-11-10的學(xué)生


CREATE database db_test10

use db_test10

-- 創(chuàng)建班級表
CREATE TABLE classes (id int PRIMARY key auto_increment,name VARCHAR(20) not null)

-- 創(chuàng)建學(xué)生表
CREATE TABLE stu (
id int PRIMARY key auto_increment,
name VARCHAR(20) not null,
birthday date,
gender VARCHAR(20),
score int ,
class_id int)


-- 在班級表中添加數(shù)據(jù)
insert  into  classes values
(1,"一年級"),
(2,"二年級"),
(3,"三年級")


-- 在學(xué)生表中添加數(shù)據(jù)

insert into stu VALUES
(1 ,"張三","2010-11-10","男",100,1),
(2 ,"李四","2012-11-10","女",75,1),
(3 ,"王五","2014-11-10","男",80,2),
(4 ,"小明","2013-11-10","女",60,2),
(5 ,"小李","2015-11-10","女",30,2),
(6 ,"小劉","2008-11-10","男",90,3)

INSERT into stu VALUES(7 ,"小劉1","2008-11-10","男",90,3)

-- 添加外鍵胀溺，需要使用foreign key關(guān)鍵字
alter table stu add foreign key(class_id) references classes(id)


-- 5 使用pymysql模塊來查詢每個班級的平均分

-- 如 一年級  100
--    二年級  80
--    三年級   70
SELECT cla.name,avg(stu.score)as 平均分 from  stu  INNER JOIN classes as cla ON stu.class_id=cla.id GROUP BY  cla.name 


-- 6 修改張三的名字為張五
UPDATE stu set name='張五' WHERE name='張三'

SELECT* FROM stu
-- 7 刪除班級名字為”三年級”的所有學(xué)生
DELETE from stu WHERE class_id=(SELECT id FROM classes WHERE name="三年級")

SELECT * FROM stu 
-- 8 查詢一年級分?jǐn)?shù)最高的人
SELECT stu.name,MAX(score) FROM stu WHERE class_id=(SELECT id FROM classes WHERE name="一年級")

SELECT * FROM stu 

-- 9 查詢有學(xué)生的年級信息（用子查詢實現(xiàn)）
SELECT id,name  from classes WHERE id in(SELECT class_id from  stu)

-- 10 查詢以‘小’開頭的名字為一個字學(xué)生
SELECT * from stu WHERE name like "小%"

-- 11查詢出生日期在2008-11-10到2012-11-10的學(xué)生
SELECT * from stu WHERE birthday  BETWEEN "2008-11-10" and "2012-11-10"


---------------------------------------------------------------------------

1裂七、  功能描述
    1 創(chuàng)建一個分類表(cate)，包含字段id,name
2創(chuàng)建一個商品表(goods),包含字段id,name,number,create_time,price,cate_id 

3 在分類表中添加三條記錄 id:1 name:服裝仓坞，id:2 name:玩具背零，id:3 name:家電

4 在商品表里添加六條記錄，
id:1 name:’羽絨服’ create_time:”2010-11-10” number:10 price:”1.00” cate_id:1
id:2 name:’輕便服’ create_time:”2012-11-10” number:30  price:”2.00” cate _id:1
id:3 name:’汽車’ create_time:”2014-11-10” number:40  price:”2.00” cate _id:2
id:4 name:’公主’ create_time:”2013-11-10” number:50  price:”6.00” cate _id:2
id:5 name:’橡皮泥’ create_time:”2015-11-10” number:23 price:”3.50” cate _id:2
id:6 name:’電飯煲’ create_time:”2008-11-10” number:3 price:”3.00” cate _id:3

5 使用pymysql模塊來查詢所有的分類及商品
6 把名字為‘羽絨服’修改成‘棉衣’
7 刪除名字為‘電飯煲’的記錄
8 查詢‘服裝’分類下面庫存最多的商品信息
9 查詢分類下有商品分類信息
10 查詢以‘服’結(jié)束的商品信息
11查詢創(chuàng)建日期在2008-11-10到2012-11-10的之間的商品

-------------------------------------------作業(yè)---------------------------------


CREATE database db_test102

use db_test102

1扯躺、  功能描述
1 創(chuàng)建一個分類表(cate)捉兴，包含字段id,name
CREATE TABLE cate(id int PRIMARY key auto_increment,name VARCHAR(20) not null)


2創(chuàng)建一個商品表(goods),包含字段id,name,number,create_time,price,cate_id 
CREATE TABLE goods(
id int PRIMARY key auto_increment,
name VARCHAR(20)not null,
number int not null,
create_time date,
price VARCHAR(50),
cate_id int)


3 在分類表中添加三條記錄 id:1 name:服裝，id:2 name:玩具录语，id:3 name:家電
INSERT into cate VALUES(1,"服裝"),(2,"玩具"),(3,"家電")

4 在商品表里添加六條記錄，
id:1 name:’羽絨服’ create_time:”2010-11-10” number:10 price:”1.00” cate_id:1
id:2 name:’輕便服’ create_time:”2012-11-10” number:30  price:”2.00” cate _id:1
id:3 name:’汽車’ create_time:”2014-11-10” number:40  price:”2.00” cate _id:2
id:4 name:’公主’ create_time:”2013-11-10” number:50  price:”6.00” cate _id:2
id:5 name:’橡皮泥’ create_time:”2015-11-10” number:23 price:”3.50” cate _id:2
id:6 name:’電飯煲’ create_time:”2008-11-10” number:3 price:”3.00” cate _id:3

insert  into goods VALUES
(1,"羽絨服",10,"2010-11-10","1.00",1),
(2,"輕便服",30,"2012-11-10","2.00",1),
(3,"汽車"  ,40,"2014-11-10","2.00",2),
(4,"公主"  ,50,"2013-11-10","6.00",2),
(5,"橡皮泥",23,"2015-11-10","3.50",2),
(6,"電飯煲",3 ,"2008-11-10","3.00",3)

SELECT * from goods
5 使用pymysql模塊來查詢所有的分類及商品


6 把名字為‘羽絨服’修改成‘棉衣’
UPDATE  goods set name="棉衣"  WHERE name="羽絨服"

SELECT * from goods
7 刪除名字為‘電飯煲’的記錄
DELETE FROM goods WHERE name="電飯煲"

8 查詢‘服裝’分類下面庫存最多的商品信息
SELECT *FROM goods WHERE number=(SELECT MAX(number)from goods WHERE cate_id=(SELECT id from cate WHERE name="服裝"))

9 查詢分類下有商品分類信息
SELECT *  from cate WHERE id in(SELECT cate_id from  goods)


10 查詢以‘服’結(jié)束的商品信息
SELECT * from goods WHERE name LIKE "%服"

11查詢創(chuàng)建日期在2008-11-10到2012-11-10的之間的商品
SELECT * from goods WHERE create_time BETWEEN "2008-11-10" and "2012-11-10"





1.創(chuàng)建一個數(shù)據(jù)庫 cart
CREATE database db_test103

use db_test103

2.使用新創(chuàng)建的數(shù)據(jù)庫,在數(shù)據(jù)庫下建立兩張表
用戶表(users): id  姓名 身高 
create table users(id int primary key auto_increment,
                                    name varchar(20),
                                    height float(5,2))

購物車表(user_cart):id 商品名稱  購買數(shù)量  總價  是否刪除  用戶id

create table user_cart(id int primary key auto_increment,
                                            name varchar(20),
                                            number int,
                                            price decimal(7,2),
                                            isdelete int,
                                            user_id int)

5.向用戶表添加以下記錄

1張三  1.80
2李四  1.75
3小明   1.34

insert into users values
(1,'張三',1.80),
(2,'李四',1.75),
(3,'小明',1.34)


6.在購物車表中加入以下記錄（1代表未刪除禾乘，0代表刪除）

1  商品一   10  100  1  1
2  商品二   3   89   0  1
3  商品三   2   30   1  1
4  商品四  10   50   1  2
5  商品五   3   10   1  2
6  商品六   1   76   1  2


insert into user_cart values
(1,'商品一',10,100,1,1),
(2,'商品二',3,89,0,1),
(3,'商品三',2,30,1,1),
(4,'商品四',10,50,1,2),
(5,'商品五',3,10,1,2),
(6,'商品六',1,76,1,2)


7.更新李四的購物車中 商品五的名字為商品5
update user_cart set name = '商品5' where name = '商品五' and user_id = (select id from users where name = '李四')
 

8.查詢身高大于1米   并且 購物車沒刪除  的  用戶和購物車信息
select * from users as user inner join user_cart as cart on user.id = cart.user_id where user.height > 1 and cart.isdelete = 1


9.查詢購物車中刪除和沒刪除的總價和為多少
select sum(price) from user_cart


10.查詢每個用戶沒刪除的購物車的數(shù)量大于1的記錄澎埠，并按降序排列，取三條記錄
select * from user_cart where isdelete = 1 and number > 1 order by number desc limit 0,3


11.按是否刪除分組始藕，查詢平均總價格大于10的用戶和購物車信息--------------------------------------------------------錯誤

select * from user_cart as cart group by isdelete having sum(cart.price) / count(*) > 10


12.查詢有購物車的用戶和購物車信息
select * from users as user inner join user_cart as cart on user.id = cart.user_id


13.刪除張三購物車中已刪除的購物車信息
delete from user_cart where isdelete = 0 and user_id = (select id from users where name = '張三')