【MySQL】— —熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法；熟练掌握MySQL的编码、数据类型等基础知识；掌握实体完整性的定义和维护方法、掌握参照完整性

CREATE TABLE Student          
      (Sno   CHAR(9)  PRIMARY  KEY,  /* 列级完整性约束条件,Sno是主码*/                  
        Sname  CHAR(20)  UNIQUE,     /* Sname取唯一值*/
        Ssex    CHAR(2),
        Sage   SMALLINT,
        Sdept  CHAR(20)
      )charset utf8; 
 
插入数据：
INSERT INTO student VALUES
("201215121","李勇","男",20,"CS"),
("201215122","刘晨","女",19,"CS"),
("201215123","王敏","女",18,"MA"),
("201215125","张立","男",19,"IS");

CREATE TABLE  Course
          (Cno       CHAR(4)  PRIMARY  KEY,
           Cname  CHAR(40),            
           Cpno     CHAR(4),                                      
            Ccredit  SMALLINT,
            FOREIGN KEY (Cpno) REFERENCES  Course(Cno) 
          ) charset utf8;
插入数据：
 
INSERT INTO course (Cno,Cname,Ccredit) VALUES
("1","数据库",4),
("2","数学",2),
("3","信息系统",4),
("4","操作系统",3),
("5","数据结构",4),
("6","数据处理",2),
("7","PASCAL语言",4);
 
update course set Cpno = case Cno
  when '1' then '5'
  when '3' then '1'
  when '4' then '6'
  when '5' then '7'
  WHEN '7' then '6'
  end
WHERE Cno IN('1','3','4','5','7');

CREATE TABLE  SC
          (Sno  CHAR(9), 
           Cno  CHAR(4),  
           Grade  SMALLINT,
           PRIMARY KEY (Sno,Cno),  
           FOREIGN KEY (Sno) REFERENCES Student(Sno),
           FOREIGN KEY (Cno)REFERENCES Course(Cno)
        ) charset utf8;
插入数据：
INSERT INTO sc(Sno,Cno,Grade) values
("201215121","1",92),
("201215121","2",85),
("201215121","3",88),
("201215122","2",90),
("201215122","3",80);

CREATE TABLE S
(SNO CHAR(4) PRIMARY KEY,
SNAME CHAR(4),
STATUS CHAR(4),
CITY CHAR(4)
)CHARSET utf8;
插入数据：
INSERT INTO s VALUES
("S1","精益","20","天津"),
("S2","盛锡","10","北京"),
("S3","东方红","30","北京"),
("S4","丰泰盛","20","天津"),
("S5","为民","30","上海");

CREATE TABLE p
(PNO CHAR(4) PRIMARY KEY,
PNAME CHAR(4),
COLOR CHAR(4),
WEIGHT char(4)
)CHARSET utf8;
插入数据：
INSERT INTO p VALUES
("P1","螺母","红","12"),
("P2","螺丝","绿","17"),
("P3","螺丝刀","蓝","14"),
("P4","螺丝刀","红","14"),
("P5","凸轮","蓝","40"),
("P6","齿轮","红","30");

CREATE TABLE j
(JNO CHAR(4),
JNAME CHAR(4),
CITY CHAR(4)
)CHARSET utf8;  
插入数据：
INSERT INTO j VALUES
("J1","三建","北京"),
("J2","一汽","长春"),
("J3","弹簧厂","天津"),
("J4","造船厂","天津"),
("J5","机车厂","唐山"),
("J6","无线电厂","常州"),
("J7","半导体厂","南京");

CREATE TABLE spj
(SNO CHAR(4),
PNO CHAR(4),
JNO CHAR(4),
QTY char(4),
PRIMARY KEY (SNO,PNO,JNO),
FOREIGN KEY (SNO) REFERENCES s(SNO),
FOREIGN KEY (PNO) REFERENCES p(PNO),
FOREIGN KEY (JNO) REFERENCES j(JNO)
)CHARSET utf8;
插入数据：
INSERT INTO spj VALUES
("S1","P1","J1","200"),
("S1","P1","J2","5000"),
("S1","P1","J3","100"),
("S1","P1","J4","700"),
("S1","P2","J2","100"),
("S2","P3","J1","400"),
("S2","P3","J2","200"),
("S2","P3","J4","500"),
("S2","P3","J5","400"),
("S2","P5","J1","400"),
("S2","P5","J2","100"),
("S3","P1","J1","200"),
("S3","P3","J1","200"),
("S4","P5","J1","100"),
("S4","P6","J3","300"),
("S4","P6","J4","200"),
("S5","P2","J4","100"),
("S5","P3","J1","200"),
("S5","P6","J2","200"),
("S5","P6","J4","500");
SELECT *FROM spj;