2014.12.02MySQL

1.创建表格

<span style="font-size:18px;">#删除tb_sc如果表已经存在
DROP TABLE IF EXISTS tb_sc;
#删除tb_tc如果表已经存在
DROP TABLE IF EXISTS tb_tc;

#删除tb_student如果表已经存在
DROP TABLE IF EXISTS tb_student;

#创建学生表(tb_student)
CREATE TABLE tb_student
(
stu_id INT,
stu_name VARCHAR(20) NOT NULL,
stu_gender BIT DEFAULT 1,
stu_age INT,
stu_addr VARCHAR(50) DEFAULT '四川成都'
);

#为学生表创建主键
ALTER TABLE tb_student ADD CONSTRAINT pk_student PRIMARY KEY (stu_id);
ALTER TABLE tb_student ADD CONSTRAINT ck_stu_age CHECK (stu_age >= 18 AND stu_age <= 25);

#删除tb_course如果表已经存在
DROP TABLE IF EXISTS tb_course;

#创建课程表(tb_course)
CREATE TABLE tb_course
(
cos_id INT,
cos_name VARCHAR(20) NOT NULL,
cos_hour INT NOT NULL,
cos_credit INT NOT NULL
);

#为课程表创建主键
ALTER TABLE tb_course ADD CONSTRAINT pk_course PRIMARY KEY (cos_id);

#删除tb_teacher如果表已经存在
DROP TABLE IF EXISTS tb_teacher;

#创建老师表(tb_teacher)
CREATE TABLE tb_teacher
(
tch_id INT PRIMARY KEY AUTO_INCREMENT,
tch_name VARCHAR(20) NOT NULL,
tch_title CHAR(4) NOT NULL
);

#为老师表创建主键
#ALTER TABLE tb_teacher ADD CONSTRAINT pk_teacher PRIMARY KEY (tch_id);
#ALTER TABLE tb_teacher MODIFY tch_id INT AUTO_INCREMENT;

#为老师表添加一个新列
ALTER TABLE tb_teacher ADD tch_birth DATE;
#删除老师表的tch_birth列
ALTER TABLE tb_teacher DROP tch_birth;

#创建学生选课表(tb_sc)
CREATE TABLE tb_sc
(
stuid INT,
cosid INT,
score DECIMAL(4, 1),
PRIMARY KEY (stuid, cosid)	#创建复合主键(联合主键)
);

#添加两个外键约束
ALTER TABLE tb_sc ADD CONSTRAINT fk_sc_stuid FOREIGN KEY (stuid) REFERENCES tb_student (stu_id) ON DELETE CASCADE;
ALTER TABLE tb_sc ADD CONSTRAINT fk_sc_cosid FOREIGN KEY (cosid) REFERENCES tb_course (cos_id) ON DELETE CASCADE;

#创建老师开课表(tb_tc)
CREATE TABLE tb_tc
(
tchid INT,
cosid INT,
maxmember INT NOT NULL,
deadline DATE NOT NULL
);

#创建复合主键
ALTER TABLE tb_tc ADD CONSTRAINT pk_tc PRIMARY KEY (tchid, cosid);

#添加两个外键约束
ALTER TABLE tb_tc ADD CONSTRAINT fk_tc_tchid FOREIGN KEY (tchid) REFERENCES tb_teacher (tch_id);
ALTER TABLE tb_tc ADD CONSTRAINT fk_tc_cosid FOREIGN KEY (cosid) REFERENCES tb_course (cos_id);

#创建唯一约束
ALTER TABLE tb_student ADD CONSTRAINT uni_stu_name UNIQUE (stu_name);
</span>

2.添加删除

<span style="font-size:18px;">insert into tb_student values (1001, '猪三', 1, 34, default);
insert into tb_student values (1002, '周一', 1, 30, '四川成都');
insert into tb_student values (1003, '万姐', 0, 23, null);

insert into tb_student values 
(1005, '李雪', 1, 20, '云南昆明'),
(1006, '李小雪', 0, 18, '云南大理');

insert into tb_student (stu_id, stu_name) values (1007, '张大千');

#删除学号为1001的学生
#delete from tb_student WHERE stu_id=1001;
#删除所有姓李的学生
#delete from tb_student where stu_name like '李%';
#删除姓李的名字有三个字的学生
#delete from tb_student where stu_name like '李__';
#删除姓李的男学生
#delete from tb_student where stu_name like '李%' and stu_gender=1;
#删除姓李的学生和男学生
#delete from tb_student where stu_name like '李%' or stu_gender=1;

#TRUNCATE TABLE tb_student;

#将姓名为万洁的学生家庭住址修改为北京
update tb_student set stu_addr='北京' where stu_name='万洁';

#向课程表插入四条记录
insert into tb_course values 
(1111, 'Java程序设计', 120, 3),
(2222, 'C++程序设计', 180, 4),
(3333, 'HTML网页编程', 60, 2),
(4444, '数据库原理', 96, 3);

#向老师表插入五条记录
insert into tb_teacher VALUES
(default, '刘忙', '教授'),
(default, '秦寿', '讲师'),
(default, '范建', '副教授'),
(default, '王大锤', '教授'),
(default, '张三丰', '助教');

#向学生选课表添加记录
insert into tb_sc VALUES
(1001, 1111, 99),
(1001, 3333, 60),
(1001, 4444, null),
(1002, 1111, 60),
(1002, 2222, null),
(1003, 2222, null),
(1005, 1111, 78),
(1006, 1111, 80);

#向老师开课表添加记录
insert into tb_tc VALUES
(1, 1111, 60, '2015-3-1'),
(1, 3333, 120, '2015-3-1'),
(1, 4444, 60, CURDATE()),
(2, 1111, 90, '2015-3-1'),
(3, 2222, 30, ADDDATE(CURDATE(),INTERVAL 7 DAY)),
(4, 2222, 150, ADDDATE(CURDATE(),INTERVAL -7 DAY)),
(5, 1111, 300, '2015-3-1'),
(3, 1111, 60, '2015-3-1');

insert into tb_tc VALUES (2, 4444, 60, ADDDATE(CURDATE(),INTERVAL 1 MONTH));</span>

3.查询

<span style="font-size:18px;">select stu_id as 学号, stu_name as 姓名, 
stu_age+4 as 毕业年龄, stu_addr as 家庭住址
from tb_student where stu_gender<>1 and stu_age < 20;

select * from tb_student where stu_age BETWEEN 18 and 25 
or stu_age BETWEEN 35 and 65;

select * from tb_student where stu_age is null;

select DISTINCT stu_addr from tb_student;

select * from tb_course limit 1, 3;

select * from tb_student where stu_name in ('周毅', '万洁', '王大锤');

select * from tb_student order by stu_age DESC, stu_gender DESC;

select count(*) from tb_sc where cosid=1111;

select count(*) from tb_sc where stuid=1001;

select * from tb_sc;

select count(DISTINCT stu_addr) from tb_student;

select SUM(tch_salary) as 总金额, AVG(tch_salary) as 平均工资 from tb_teacher;

select CONVERT(AVG(score), decimal(4,2)) as 平均分 from tb_sc where cosid=1111;

select max(score) from tb_sc where cosid=1111;

select min(score) from tb_sc where cosid=1111;

select cosid as 课程编号, convert(avg(score), decimal(4,1)) as 平均分 
from tb_sc group by cosid;

select t2.stu_name as 姓名, t1.平均分 from
(select stuid, avg(score) as 平均分
from tb_sc group by stuid) as t1, tb_student as t2
where t1.stuid=t2.stu_id;</span>