精华内容
下载资源
问答
  • mysql 创建学生表

    万次阅读 多人点赞 2018-07-31 14:31:18
    – 如果存在名为school的数据库就删除它 drop database if exists school; – 创建名为school的数据库并设置默认字符集为utf8 ...create database school default ...– 创建学院 create table tb_coll...

    – 如果存在名为school的数据库就删除它

    drop database if exists school;

    – 创建名为school的数据库并设置默认字符集为utf8

    create database school default charset utf8;

    – 切换到school数据库

    use school;

    – 创建学院表

    create table tb_college
    (
    colid int not null comment '学院编号',
    colname varchar(20) not null comment '学院名称',
    coltel varchar(20) not null comment '联系电话',
    colwebsite varchar(255) comment'网站地址',
    primary key(colid)
    
    );

    – 给学院网址加上唯一约束

    alter table tb_college add constraint uni_website unique (colwebsite);

    – 创建老师表

    create table tb_teacher 
    (
    tid int not null,
    colid int not null,
    tod int not null,
    tname varchar(4) not null,
    primary key (tid)
    
    );
    alter table tb_teacher add constraint fk_teacher_colid foreign key (colid) references tb_college (colid);

    – 创建学生表(tb_student)

    create table tb_student
    (
    stuid int not null,
    stuname varchar(4) not null,
    stusex bit default 1,
    stuaddr varchar(50),
    colid int not null comment '学院编号',
    primary key (stuid)
    );
    alter table tb_student add constraint fk_student_colid foreign key (colid) references tb_college (colid);
    insert into tb_college (colid,colname,coltel) values 
    (10, '计算机学院','028-88556678'),
    (20, '外国学院','028-88556668'),
    (30, '经济管理学院','028-88556658');

    – 插入数据

    insert into tb_student values 
    (1001,'小强',1,'四川成都',30),
    (1002,'花月',1,'四川成都',10),
    (1003,'小红',1,'四川成都',20),
    (1004,'小白',1,'四川成都',10),
    (1005,'小青',1,'四川成都',30),
    (1006,'小黑',1,'四川成都',10),
    (1007,'白龙',1,'四川成都',20),
    (1008,'小花',1,'四川成都',20),
    (1009,'白马',1,'四川成都',30),
    (1010,'冷面',1,'四川成都',30),
    (1011,'白洁',1,'四川成都',20),
    (1012,'紫薇',1,'四川成都',20),
    (1013,'杨洋',1,'四川成都',20);

    – 创建课程表

    create table tb_course
    (
    cid int not null comment '课程编号',
    cname varchar(20) not null comment '课程名称',
    ccredit int not null comment'学分',
    tid int not null,
    primary key (cid)
    
    );
    alter table tb_course add constraint fk_course_tid foreign key (tid) references tb_teacher (tid);

    – 创建学生选课表(tb_sc)

    create table tb_sc
    (
    -- 自动增减
    scid int not null auto_increment comment'选课记录号',
    stuid int not null comment'学号',
    cid int not null comment '课程编号',
    scdate datetime default now(),
    score decimal(4,1) comment'成绩',
    primary key (scid)
    
    );
    alter table tb_sc add constraint fk_sc_stuid 
    foreign key (stuid) references tb_student (stuid);
    
    alter table tb_sc add constraint fk_sc_cid
    foreign key (cid) references tb_course (cid);
    展开全文
  • MySql练习4:创建学生表和成绩表索引并查看索引.zip MySql练习4:创建学生表和成绩表索引并查看索引.zip MySql练习4:创建学生表和成绩表索引并查看索引.zip
  • DROP TABLE IF EXISTS `db_school`.`Student`; create table Student ( Sno varchar(20), Sname varchar(50), primary key (Sno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `db_school`.`...

    https://blog.csdn.net/Yan456jie/article/details/52557140 

    DROP TABLE IF EXISTS `db_school`.`Student`;
    create table Student
    (
    	Sno varchar(20),
    	Sname varchar(50),	
    	primary key (Sno)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
    DROP TABLE IF EXISTS `db_school`.`Course`;
    create table Course
    (
    	Cno varchar(20),
    	Cname varchar(50),	
    	Tno varchar(20),
    	primary key (Cno)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
    DROP TABLE IF EXISTS `db_school`.`SC`;
    create table SC
    (
    	Sno varchar(20),
    	Cno varchar(20),	
    	score int,
    	primary key (Sno,Cno)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
    DROP TABLE IF EXISTS `db_school`.`Teacher`;
    create table Teacher
    (
    	Tno varchar(20),
    	Tname varchar(50),	
    	primary key (Tno)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
    INSERT INTO `Student`(Sno,Sname) VALUES ('001','陈一');
    INSERT INTO `Student`(Sno,Sname) VALUES ('002','郭二');
    INSERT INTO `Student`(Sno,Sname) VALUES ('003','张三');
    INSERT INTO `Student`(Sno,Sname) VALUES ('004','李四');
    INSERT INTO `Student`(Sno,Sname) VALUES ('005','王五');
     
     
    INSERT INTO `Teacher`(Tno,Tname) VALUES ('001','张老师');
    INSERT INTO `Teacher`(Tno,Tname) VALUES ('002','王老师');
    INSERT INTO `Teacher`(Tno,Tname) VALUES ('003','钱老师');
    INSERT INTO `Teacher`(Tno,Tname) VALUES ('004','刘老师');
    INSERT INTO `Teacher`(Tno,Tname) VALUES ('005','胡老师');
     
     
    INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('001','语文','张老师');
    INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('002','数学','王老师');
    INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('003','英语','钱老师');
    INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('004','物理','刘老师');
    INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('005','政治','胡老师');
     
     
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','001',50);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','002',60);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','003',70);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','004',80);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','005',90);
     
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','001',90);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','002',80);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','003',70);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','004',60);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','005',50);
     
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','001',81);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','002',82);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','003',83);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','004',84);
    INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','005',85);

    展开全文
  • mysql 创建学生表并查询成绩

    千次阅读 2020-12-22 11:16:31
    创建表 create table `users` ( `userid` bigint (100), `username` varchar (300), `sex` varchar (6), `age` int (3), `num` bigint (100) ); 查询数据 1,查询男女各有多少总分数 SELECT sex, sum...

    创建表

    CREATE TABLE testdb.users (
      `userid` INT(20) PRIMARY KEY NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(20) NOT NULL,
      `sex` CHAR (3) DEFAULT NULL,
      `age`  INT (3) DEFAULT NULL,
      `num` BIGINT (200) DEFAULT NULL
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    查询表数据

    1,查询男女各有多少总分数

    SELECT sex, sum(num) FROM testdb.users GROUP BY sex;

    2, 查询每个用户的基本信息,分数在90为优秀,[80,60)为良好,小于60为不及格。

    select
    	userid ,
    	username,
    	case
    		when num >90 then '优秀'
    		when num <80
    		and num>60 then '良好'
    		else '其他'
    	end as '成绩'
    from
    	testdb.users u

     

    展开全文
  • 学生表以及课程、老师、成绩表的创建 -- 删除表 drop table if exists score; drop table if exists student; drop table if exists course; drop table if exists teacher; 学生表 create table student( stuNo ...

    学生表以及课程、老师、成绩表的创建

    -- 删除表
    drop table if exists score;
    drop table if exists student;
    drop table if exists course;
    drop table if exists teacher;
    
    

    学生表

    create table student(
    	stuNo	char(5) primary key, -- 学生学号
    	stuName varchar(32) not null, -- 学生姓名
    	gender char(1) not null	default '男', -- 学生性别
    	age int not null -- 学生年龄
    )charset='utf8';
    
    -- 添加学生数据
    insert into student(stuNo,stuName,gender,age) values('001','李志','男',14);
    insert into student(stuNo,stuName,gender,age) values('002','宋东野','男',23);
    insert into student(stuNo,stuName,gender,age) values('003','赵雷','男',34);
    insert into student(stuNo,stuName,gender,age) values('004','马頔','男',32);
    insert into student(stuNo,stuName,gender,age) values('005','陈粒','女',18);
    insert into student(stuNo,stuName,gender,age) values('006','筠子','女',4);
    insert into student(stuNo,stuName,gender,age) values('007','寸铁','男',56);
    insert into student(stuNo,stuName,gender,age) values('008','狗毛','男',47);
    insert into student(stuNo,stuName,gender,age) values('009','崔健','男',39);
    insert into student(stuNo,stuName,gender,age) values('010','草东','男',40);
    insert into student(stuNo,stuName,gender,age) values('011','张悬','女',36);
    insert into student(stuNo,stuName,gender,age) values('012','撒娇','女',25);
    insert into student(stuNo,stuName,gender,age) values('013','宋东野','男',35);
    

    教师表

    create table teacher(
    	tNo char(5) primary key, -- 教师号
    	tName varchar(32) not null -- 教师名字
    )charset='utf8';
    
    -- 添加老师数据
    insert into teacher(tNo,tName) values('t001','张三');
    insert into teacher(tNo,tName) values('t002','李四');
    insert into teacher(tNo,tName) values('t003','王二');
    insert into teacher(tNo,tName) values('t004','王五');
    

    课程表

    create table course(
    	cNo char(5) primary key, -- 课程号
    	cName varchar(32) not null unique, -- 课程名
    	tNo char(5), -- 教师号
    	foreign key(tNo) references teacher(tNo)
    )charset='utf8';
    
    
    -- 添加课程数据
    insert into course(cNo,cName,tNo) values('c001','数据结构','t001');
    insert into course(cNo,cName,tNo) values('c002','操作系统','t002');
    insert into course(cNo,cName,tNo) values('c003','计算机网络','t001');
    insert into course(cNo,cName,tNo) values('c004','软件工程','t003');
    insert into course(cNo,cName,tNo) values('c005','计算机组成原理','t002');
    insert into course(cNo,cName,tNo) values('c006','数据库系统','t002');
    insert into course(cNo,cName,tNo) values('c007','C语言','t003');
    insert into course(cNo,cName,tNo) values('c008','高等数学','t004');
    

    成绩表

    create table score(
    	sNo char(5) not null, -- 教师号
    	cNo char(5) not null, -- 课程号
    	score double not null, -- 成绩
    	primary key(sNo,cNo)
    );
    
    -- 添加成绩数据
    insert into score(sNo,cNo,score) values('001','c001',56);
    insert into score(sNo,cNo,score) values('001','c002',87);
    insert into score(sNo,cNo,score) values('001','c003',69);
    insert into score(sNo,cNo,score) values('001','c004',92);
    insert into score(sNo,cNo,score) values('002','c001',76);
    insert into score(sNo,cNo,score) values('002','c003',69);
    insert into score(sNo,cNo,score) values('002','c005',90);
    insert into score(sNo,cNo,score) values('003','c002',53);
    insert into score(sNo,cNo,score) values('003','c003',96);
    insert into score(sNo,cNo,score) values('004','c004',67);
    insert into score(sNo,cNo,score) values('005','c008',69);
    insert into score(sNo,cNo,score) values('005','c007',78);
    insert into score(sNo,cNo,score) values('005','c002',70);
    insert into score(sNo,cNo,score) values('005','c003',81);
    insert into score(sNo,cNo,score) values('006','c004',93);
    insert into score(sNo,cNo,score) values('006','c008',62);
    insert into score(sNo,cNo,score) values('007','c001',58);
    insert into score(sNo,cNo,score) values('007','c002',76);
    insert into score(sNo,cNo,score) values('008','c003',65);
    insert into score(sNo,cNo,score) values('008','c004',89);
    insert into score(sNo,cNo,score) values('009','c005',92);
    insert into score(sNo,cNo,score) values('009','c006',90);
    insert into score(sNo,cNo,score) values('009','c007',42);
    insert into score(sNo,cNo,score) values('009','c008',80);
    insert into score(sNo,cNo,score) values('010','c002',75);
    insert into score(sNo,cNo,score) values('010','c004',69);
    insert into score(sNo,cNo,score) values('011','c004',92);
    insert into score(sNo,cNo,score) values('011','c005',99);
    insert into score(sNo,cNo,score) values('011','c002',100);
    insert into score(sNo,cNo,score) values('011','c006',76);
    insert into score(sNo,cNo,score) values('012','c003',71);
    insert into score(sNo,cNo,score) values('012','c002',83);
    insert into score(sNo,cNo,score) values('013','c004',80);
    insert into score(sNo,cNo,score) values('013','c007',96);
    
    展开全文
  • Course课程表 Cpno先行课 Ccredit学分 Cno课号 Sc表(选课表)Sno学号 Cno课号 Grade成绩 Student表 Sno学号 Sname姓名 Ssex性别 Sage年龄 Sdept 所在系 ...学生表:Student(Sno,Sname,Ssex,Sage,Sdept) 课
  • Mysql练习3:为学生表、科目表、成绩表添加数据.zip
  • MYsql建立学生成绩

    千次阅读 2020-02-16 20:14:01
    mysql> show databases; mysql> create database gradesystem; mysql> use gradesystem; mysql> create table tb_class -> ( -> clid int not null comment '班级编号', -> clname ...
  • ·数据库创建及查询 文章目录学习笔记任务过程第一步:分析需求第二步:新建数据库第三步:新建1.Grade2.Student3.subject第三步:插入数据1.Grade2.Student3.subject第四步:查询测试1.查询学号...
  • – 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 -- 1.这样就查出来了 满足 查询"01"课程比"02"课程成绩高的学生 的id select distinct sc1.s_id from score sc1, score sc2 where sc1.c_id = '01' and ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 53,594
精华内容 21,437
关键字:

mysql创建学生表

mysql 订阅