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 t2where t1.stuid=t2.stu_id;</span>