mysql 张三的语文成绩_mysql的增删改查（列子）

1、创建数据库test

2、创建学生表student1

(sid,sname,sage,ssex) Sid学号 ,sname学生姓名，sage学生年龄，ssex学生性并指定值为男或女 设置默认值为男 Sid为主键自增，sname设置为唯一

-- CREATE DATABASE test;

USE test;

CREATE TABLE student1(

sid INT primary key auto_increment,

sname char(10) unique,

sage INT,

ssex enum('女','男') not null default '男'

)charset='utf8';

#添加charset='utf8'是因为ssex中的中文字符无法识别，会报错

3、创建课程表course(cid,cname,tid) cid为主键自增

CREATE TABLE course(

cid INT PRIMARY KEY auto_increment,

cname CHAR(10),

tid INT

);

4、创建成绩表cj(sid,cid,score)sid,cid为外键字段，依次关联学生表和课程表

CREATE TABLEscore(

scoreINT PRIMARY KEYauto_increment

stu_sidINT,FOREIGN key(stu_sid) REFERENCESstudent(sid),

cour_cidINT,FOREIGN key(cour_cid) REFERENCEScourse(cid)

);

5、教师表：t(tid,sname)    tid教师编号为主键，tname教师名字

CREATE TABLEteacher( tidINT PRIMARY key, snameCHAR(10) );

6、学生表中插入数据

INSERT INTO student1(sname,sage) VALUES('张三',16);INSERT INTO student1(sname,sage) VALUES('李四',15);INSERT INTO student1(sname,sage) VALUES('王五',16);INSERT INTO student1(sname,sage) VALUES('马六',17);INSERT INTO student1(sname,sage) VALUES('陈七',14);

7、教师表中插入数据

INSERT INTO teacher(tid,sname) VALUES(1,'包包');INSERT INTO teacher(tid,sname) VALUES(2,'老向');INSERT INTO teacher(tid,sname) VALUES(3,'老张');INSERT INTO teacher(tid,sname) VALUES(4,'春哥');

8、课程表中插入数据

INSERT INTO course(cid,cname,tid) VALUES(1,'语文',1);INSERT INTO course(cid,cname,tid) VALUES(2,'数学',2);INSERT INTO course(cid,cname,tid) VALUES(3,'英语',3);INSERT INTO course(cid,cname,tid) VALUES(4,'日语',4);

9、成绩表中插入数据

INSERT INTO cj(sid,cid,score) VALUES(1,1,30);INSERT INTO cj(sid,cid,score) VALUES(1,2,60);INSERT INTO cj(sid,cid,score) VALUES(1,3,70);INSERT INTO cj(sid,cid,score) VALUES(2,1,60);INSERT INTO cj(sid,cid,score) VALUES(2,2,70);INSERT INTO cj(sid,cid,score) VALUES(3,2,30);INSERT INTO cj(sid,cid,score) VALUES(3,3,80);INSERT INTO cj(sid,cid,score) VALUES(4,1,70);INSERT INTO cj(sid,cid,score) VALUES(4,2,60);INSERT INTO cj(sid,cid,score) VALUES(4,3,80);INSERT INTO cj(sid,cid,score) VALUES(4,4,90);INSERT INTO cj(sid,cid,score) VALUES(5,1,90);INSERT INTO cj(sid,cid,score) VALUES(5,4,90);

10、修改教师表的sname为tname,并且数据类型设置为varchar2(20),删除学生表SSEX字段

ALTER TABLE teacher CHANGE sname tname VARCHAR(20)

ALTER TABLE student1 DROP ssex

11、修改张三的语文成绩为80分

UPDATE cj SET score=80 WHERE sid=1 and cid=1

12、插入一条数据到cj，插入的数据如下(1,4,90)

INSERT INTO cj(sid,cid,score) VALUES(1,4,90);

13、删除陈七日语成绩

DELETE FROM cj WHERE sid=5 AND cid=4

14、查询(一条一个查询)

select * fromstudent1;select sid fromstudent1;SELECT * FROM student1 WHERE sage LIKE '%6%'

SELECT * FROM student1 WHERE sage BETWEEN 14 AND 16

SELECT * FROM student1 WHERE sage IN(1,14,15)SELECT * FROM student1 WHERE sage is NULL

SELECT * FROM student1 WHERE sage is not NULL

SELECT * FROM student1 WHERE sage NOT IN(14,15)