首页 > 数据库 >sql 上课用

sql 上课用

时间:2023-02-15 11:56:57浏览次数:29  
标签:insert 上课 varchar into values key sql id


CREATE DATABASE IF NOT EXISTS `example` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `example`;

Create Table dept
(
  Deptno int primary key,
  Dname varchar(20),
  Loc varchar(10),
  sumsalary int,
  persons  int
);

Create Table emp
(
  empno int primary key,
  ename varchar(10),
  Job varchar(10),
  Mgr int,
  Hiredate datetime,
  Sal float,
  Comm float,
  deptno int ,
  Foreign key (mgr) references emp(empno),
  Foreign key (deptno) references dept(deptno)
 );


insert into dept values(1,'Research','New York',0,0);

insert into dept values(2,'Office','New York',0,0);
insert into dept values(3,'Finance','Iowa',0,0);

insert into dept values(4,'Factory','Iowa',0,0);
insert into dept values(5,'Market','New York',0,0);

insert into emp values(1,'Smith','Manager',null,'2008-10-10',2345.9,200,1);
insert into emp values(2,'John','Manager',null,'2010-1-20',4345.9,600,2);

insert into emp values(3,'Allen','SALESMAN',1,'2015-10-10',1800,180,1);
insert into emp values(4,'Ward','CLERK',null,'2013-11-20',2345.9,300,3);

insert into emp values(5,'MARTIN','SALESMAN',1,'2014-1-10',2300,180,1);
insert into emp values(6,'KING','ANALYST',4,'2013-11-20',2745.9,300,3);

insert into emp values(7,'SCOTT','ANALYST',1,'2014-1-10',2600,280,1);
insert into emp values(8,'KING','ANALYST',2,'2013-11-20',2787,320,2);

insert into emp values(9,'JAMES','CLERK',1,'2014-1-10',2300,280,1);
insert into emp values(10,'BLAKE','SALESMAN',null,'2013-11-20',2587,290,4);

insert into emp values(11,'CLARK','SALESMAN',1,'2013-5-10',2300,280,1);
insert into emp values(12,'MILLER','SALESMAN',10,'2012-9-20',2587,290,4);

insert into emp values(13,'Ali','CLERK',1,'2013-5-10',2800,290,1);
insert into emp values(14,'Kong','ANALYST',2,'2014-9-20',3587,290,2);

insert into emp values(15,'Cheng','ANALYST',10,'2013-5-10',3800,340,4);
insert into emp values(16,'Wang','ANALYST',4,'2016-9-20',3887,290,3);

insert into emp values(17,'Zhangsan','Manager',1,'2013-7-10',2350,450,1);
insert into emp values(18,'Lisi','CLERK',10,'2015-4-20',2347,480,4);

insert into emp values(19,'Wangfang','ANALYST',1,'2013-5-10',3800,390,1);
insert into emp values(20,'Kongzi','Manager',2,'2012-9-20',3587,290,2);

insert into emp values(21,'Chengdong','SALESMAN',10,'2013-5-10',3800,340,4);
insert into emp values(22,'Hewang','ANALYST',4,'2016-9-20',3887,290,3);

 

 

 

 

 

 


CREATE DATABASE teaching;

USE teaching;

create table classroom
    (building        varchar(15),
     room_number        varchar(7),
     capacity        numeric(4,0),
     primary key (building, room_number)
    );

create table department
    (dept_name        varchar(20),
     building        varchar(15),
     budget                numeric(12,2) check (budget > 0),
     primary key (dept_name)
    );

create table course
    (course_id        varchar(8),
     title            varchar(50),
     dept_name        varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department (dept_name)
        on delete set null
    );

create table instructor
    (ID            varchar(5),
     name            varchar(20) not null,
     dept_name        varchar(20),
     salary            numeric(8,2) check (salary > 29000),
     primary key (ID),
     foreign key (dept_name) references department (dept_name)
        on delete set null
    );

create table section
    (course_id        varchar(8),
         sec_id            varchar(8),
     semester        varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
     year            numeric(4,0) check (year > 1701 and year < 2100),
     building        varchar(15),
     room_number        varchar(7),
     time_slot_id        varchar(4),
     primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course (course_id)
        on delete cascade,
     foreign key (building, room_number) references classroom (building, room_number)
        on delete set null
    );

create table teaches
    (ID            varchar(5),
     course_id        varchar(8),
     sec_id            varchar(8),
     semester        varchar(6),
     year            numeric(4,0),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id, sec_id, semester, year) references section (course_id, sec_id, semester, year)
        on delete cascade,
     foreign key (ID) references instructor (ID)
        on delete cascade
    );

create table student
    (ID            varchar(5),
     name            varchar(20) not null,
     dept_name        varchar(20),
     tot_cred        numeric(3,0) check (tot_cred >= 0),
     primary key (ID),
     foreign key (dept_name) references department (dept_name)
        on delete set null
    );

create table takes
    (ID            varchar(5),
     course_id        varchar(8),
     sec_id            varchar(8),
     semester        varchar(6),
     year            numeric(4,0),
     grade                varchar(2),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id, sec_id, semester, year) references section (course_id, sec_id, semester, year)
        on delete cascade,
     foreign key (ID) references student (ID)
        on delete cascade
    );

create table advisor
    (s_ID            varchar(5),
     i_ID            varchar(5),
     primary key (s_ID),
     foreign key (i_ID) references instructor (ID)
        on delete set null,
     foreign key (s_ID) references student (ID)
        on delete cascade
    );

create table time_slot
    (time_slot_id        varchar(4),
     day            varchar(1),
     start_hr        numeric(2) check (start_hr >= 0 and start_hr < 24),
     start_min        numeric(2) check (start_min >= 0 and start_min < 60),
     end_hr            numeric(2) check (end_hr >= 0 and end_hr < 24),
     end_min        numeric(2) check (end_min >= 0 and end_min < 60),
     primary key (time_slot_id, day, start_hr, start_min)
    );

create table prereq
    (course_id        varchar(8),
     prereq_id        varchar(8),
     primary key (course_id, prereq_id),
     foreign key (course_id) references course (course_id)
        on delete cascade,
     foreign key (prereq_id) references course (course_id)
    );


标签:insert,上课,varchar,into,values,key,sql,id
From: https://www.cnblogs.com/br0sy/p/17122298.html

相关文章

  • sqlserver 游标
    cast(strastype)printcast('14.23'asdecimal(5,3))--14.230--5为精度,指除小数点外,最长为5位,优先整数位14,为2位,剩下的为小数位,这里是23,可以成功转换,结果为14.230......
  • ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib
    在Kylin服务器上安装好mysql后登录时出现以下问题:查找mysql.sock文件,命令如下:find-namemysql.sock结果如下: 如果文件不存在,mysql.sock丢失,mysqld_safe启动报错......
  • CentOS中使用Dockerfile部署初始化sql文件的mysql
    场景Docker中部署mysql数据库CentOS中使用Dockerfile部署带websocket的SpringBoot的jar包在上面部署Mysql镜像和会使用Dockerfile的基础上。如果要实现定制化一个mysql镜像,......
  • PostgreSQL中按时间月份自动创建分区表
    出处https://blog.csdn.net/xgb2018/article/details/109244096PostgreSQL中按时间月份自动创建分区表前言1.创建主表2.创建存储过程3.创建触发器4.踩过的坑(1)constrai......
  • mysql从库延迟检查脚本
    脚本如下:[root@dev-env23scripts]#vicheck_slave_delay.sh#!/bin/bashcheck_time=`date"+%Y-%m-%d,%H:%M:%S"`mysql-hlocalhost-uroot-p123456-e'showsla......
  • MySQL使用笔记
    查询结果导出到文件终端命令下直接导出除了在mysql命令行下导出查询结果,还可以在终端直接导出查询结果到文件中:mysql-uroot-p-e"select*fromtest">xxx.csv如......
  • docker-desktop 启动mysql 5.7
    1.创建文件夹:D:/mysql_workspace/mysql/etc/mysql/data(存储mysql的数据目录)    D:/mysql_workspace/mysql/etc/mysql/tmp(存储mysql的临时目录)D:/mysql......
  • (数据库系统概论|王珊)第三章关系数据库标准语言SQL-第四节:数据查询
    pdf下载:密码7281专栏目录首页:【专栏必读】(考研复试)数据库系统概论第五版(王珊)专栏学习笔记目录导航及课后习题答案详解关于数据库如何安装,表如何建立这里不再介绍,请......
  • MySQL中,把查询的结果拼接成一个字符串。
    用法:group_concat(待拼接对象)输出:用逗号进行拼接后的字符串selectgroup_concat(emp_no)asemployeesfromdept_emp;  /*结果:employees       ......
  • Hive 面试题——HiveSQL 执行顺序
    描述今天刷到了一个面试题:hivesql执行顺序,接下来就从一个带有groupby的例子看看hivesql的执行顺序执行顺序为from..on..join..where..groupby..having......