建表建库数据输入

create database test;

use test;

create table s (

sno char(10) not null primary key,

sn varchar(45) not null,

sex enum ('男','女') default '男' comment '性别',

date DATE not null,

maj varchar(45) not null,

dept varchar(45) not null,

phone char(11) unique);

create table c (

cno char(10) not null primary key,

cn varchar(45) not null,

cre decimal(3,1) not null,

ct int not null,

teacher varchar(45) not null

);

create table sc(

cno CHAR(10) NOT NULL COMMENT '课程号',

sno CHAR(10) NOT NULL COMMENT '学号',

cst DATETIME NOT NULL COMMENT '选课时间',

dp DECIMAL(4,1) COMMENT '平时成绩',

gpa DECIMAL(2,1) NOT NULL COMMENT '平时成绩比重',

sc DECIMAL(4,1) COMMENT '考试成绩'

);

select * from s;

insert into s values

('0433','张艳','女','2000-9-13','计算机','信息学院',null),

('0496','李越','女','2001-1-23','信息','信息学院','1381290××××'),

('0529','赵欣','男','2002-2-27','信息','信息学院','1350222××××'),

('0531','张志国','女','2002-10-10','自动化','工学院','1331256××××'),

('0538','于兰兰','男','2002-2-1','数学','理学院','1331200××××'),

('0591','王丽丽','女','2003-3-21','计算机','信息学院','1332080××××'),

('0592','王海强','女','2003-9-1','数学','理学院',null);

select * from c;

insert into c values

('K001','计算机图形学','2.5',40,'胡晶晶'),

('K002','计算机应用基础','3.0',48,'任泉'),

('K006','数据结构','4.0',64,'马跃先'),

('M001','政治经济学','4.0',64,'孔繁新'),

('S001','高等数学','3.0',48,'赵晓尘');

select * from sc;

insert into sc values

('K001','0433','2017-8-23 16:14:11',90.5,0.4,93.5),

('K001','0529','2017-8-24 8:15:11',85,0.3,90),

('K001','0531','2017-8-25 10:18:34',57,0.4,75),

('K001','0591','2017-8-24 15:20:24',81.5,0.4,71.5),

('K002','0496','2018-2-25 15:20:24',null,0.3,null),

('K002','0529','2018-2-24 10:15:21',70,0.4,83),

('K002','0531','2018-2-25 13:20:19',75,0.2,81.5),

('K002','0538','2018-2-24 14:20:24',70.5,0.4,73),

('K002','0592','2018-2-15 9:18:12',85,0.4,null),

('K006','0531','2018-8-25 19:17:25',93,0.3,86),

('K006','0591','2018-8-24 13:19:45',85,0.4,82),

('M001','0496','2019-2-23 10:8:11',83,0.3,91),

('M001','0591','2019-2-25 12:14:12',92.5,0.4,89),

('S001','0531','2019-8-26 13:15:12',82.5,0.4,77),

('S001','0538','2019-8-27 15:10:12',75.2,0.3,null);

update s

set dept='理学院'

where sno='0591';

update c

set cre=3,

ct=3*3

where cre<3;

set sql_safe_updates=0;

delete from sc;

insert into sc values

('K001','0433','2017-8-23 16:14:11',90.5,0.4,93.5),

('K001','0529','2017-8-24 8:15:11',85,0.3,90),

('K001','0531','2017-8-25 10:18:34',57,0.4,75),

('K001','0591','2017-8-24 15:20:24',81.5,0.4,71.5),

('K002','0496','2018-2-25 15:20:24',null,0.3,null),

('K002','0529','2018-2-24 10:15:21',70,0.4,83),

('K002','0531','2018-2-25 13:20:19',75,0.2,81.5),

('K002','0538','2018-2-24 14:20:24',70.5,0.4,73),

('K002','0592','2018-2-15 9:18:12',85,0.4,null),

('K006','0531','2018-8-25 19:17:25',93,0.3,86),

('K006','0591','2018-8-24 13:19:45',85,0.4,82),

('M001','0496','2019-2-23 10:8:11',83,0.3,91),

('M001','0591','2019-2-25 12:14:12',92.5,0.4,89),

('S001','0531','2019-8-26 13:15:12',82.5,0.4,77),

('S001','0538','2019-8-27 15:10:12',75.2,0.3,null);

delete from c

where cno='S001';

delete from sc

where cno='S001';

select * from s;

select distinct sno from sc;

select * from s where sex='女' and dept='信息学院';

select cno,cn from c where ct>=50 and ct<=60;

select sno,sn,maj from s where dept = '工学院'or '计算机学院' ;

select date from s where date > '2002-2-27';

select sno,sn,dept from s where phone is null;

select cno,cn,cre from c where cn like '%计算机%';

select sno,sn from s where sn like '王%';

select dp*gpa+sc*(1-gpa) from sc;

select count(sno) from sc where cno = 'K002';

select count(distinct cno),max(sc) ,min(sc) ,avg(sc) from sc where sno = '0592';