sql语句—实例及答案.doc

sql 语句大致可以分类两大类 SQL(结构化查询语言)针对数据库database和表table的操作创建create查看show修改alter删除drop/ 创建一个数据库create database mydb;/ 查看所有的数据库库show databases;/ 删除 mydbdrop database mydb;/ 删除 user 表drop table user;针对表中的记录的操作增 insert删 delete改 update查 select一、 操作数据库创建一个名称为mydb1的数据库create database mydb1;查看当前数据库服务器中的所有数据库show databases;删除前面创建的mydb3数据库drop database mydb3;二、操作表1. 创建表创建员工表Id整形name字符型sex字符型或bit型brithday日期型Entry_date日期型job字符型Salary小数型resume大文本型use database mydatabasecreate table employee(id int,name varchar(20),sex varchar(6),brithday date,entry_date date,job varchar(20),salary double,resume text);2. 修改表在上面员工表的基础上增加一个image列alter table employee add colm1 int;修改job列，使其长度为60alter table employee modify job varchar(60);删除sex列。alter table employee drop sex;表名改为users。rename table employee to user;列名name修改为usernamealter table user change column name username varchar(20);查看所有表show tables;删除 user 表drop table user;三、数据的增删改查1. insert语句create table employee(id int primary key,name varchar(20) not null,sex varchar(10),birthday date,salary float not null,entry_date date,resume text);/ 向员工表插入三条记录insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(1,zhangsan,male,1987-11-23,1500,2010-2-18,good boy);insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,wangwu,male,1988-11-23,1200,2010-2-18,good boy);insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,xiaohong,female,1983-11-23,1800,2010-2-18,good girl);insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(4,王小明,男,1986-11-23,3800,2011-2-18,handsome boy);insert into employee values(5,王小明,男,1986-11-23,3800,2011-2-18,handsome boy);insert into employee(id,name, salary) values(6,王小明, 3800);/ 查看表的所有记录select * from employee;2. update语句将所有员工薪水修改为5000元update employee set salary=5000;将姓名为zhangsan的员工薪水修改为3000元。update employee set salary=3000 where name=zhangsan;将姓名为lisi的员工薪水修改为4000元,sex改为female。update employee set salary=4000,sex=female where name=wangwu;将xiaohong的薪水在原有基础上增加1000元update employee set salary=salary+1000 where name=xiaohong;3. delete语句删除表中name为赵楠的记录。delete from employee where name=赵楠;删除表中所有记录。delete from employee;4. select语句student.sqlcreate table student(id int,name varchar(20),chinese float,english float,math float);insert into student(id,name,chinese,english,math) values(1,张小明,89,78,90);insert into student(id,name,chinese,english,math) values(2,李进,67,53,95);insert into student(id,name,chinese,english,math) values(3,王五,87,78,77);insert into student(id,name,chinese,english,math) values(4,李一,88,98,92);insert into student(id,name,chinese,english,math) values(5,李来财,82,84,67);insert into student(id,name,chinese,english,math) values(6,张进宝,55,85,45);insert into student(id,name,chinese,english,math) values(7,黄蓉,75,65,30);查询表中所有学生的信息。select * from student;查询表中所有学生的姓名和对应的英语成绩。select name,english from student;过滤表中重复数据。select distinct english from student;在所有学生分数上加10分特长分。select name,english+10,chinese+10,math+10 from student;统计每个学生的总分。select name,english+chinese+math as sum from student;使用别名表示学生分数。where 子句查询姓名为李一的学生成绩select * from student where name=李一;查询英语成绩大于90分的同学select * from student where english90;查询总分大于200分的所有同学select name,english+chinese+math from student where english+chinese+math200;运算符查询英语分数在 8090之间的同学。select * from student where english between 65 and 85;查询数学分数为89,90,91的同学。select name,math from student where math in(89,90,91);查询所有姓李的学生成绩。select * from student where name like 李%;/ 查询姓李的两个字的学生select * from student where name like 李_;查询数学分80，语文分80的同学。select * from student where math80 and chinese80;查询英语80或者总分200的同学select *,chinese+math+english from student where english80 or chinese+english+math200;order by 子句对数学成绩排序后输出。select * from student order by math;对总分排序后输出，然后再按从高到低的顺序输出select *,chinese+math+english from student order by chinese+math+english desc;对姓李的学生成绩排序输出select * from student where name like 李% order by chinese+math+english;合计函数count统计一个班级共有多少学生？select count(*) from student;统计数学成绩大于90的学生有多少个？select count(*) from student where math90;统计总分大于230的人数有多少？select count(*) from student where chinese+math+english230;sum统计一个班级数学总成绩？select sum(math) from student;统计一个班级语文、英语、数学各科的总成绩select sum(math),sum(chinese),sum(english) from student;统计一个班级语文、英语、数学的成绩总和select sum(math+chinese+english) from student;统计一个班级语文成绩平均分select sum(chinese)/count(*) from student;缺考的不参与计算select sum(chinese)/count(chinese) from student;avg语文平均分select avg(chinese) from student;max/min语文最高分select max(chinese) from student;group bycreate table orders(id int,product varchar(20),price float);insert into orders(id,product,price) values(1,dianshi,900);insert into orders(id,product,price) values(2,xiyiji,100);insert into orders(id,product,price) values(3,xiyifen,90);insert into orders(id,product,price) values(4,orange,9);insert into orders(id,product,price) values(5,xiyifen,90);将商品归类select * from orders group by product;显示单类商品总结select *,sum(price) from orders group by product;商品分类 显示单类商品总价大于100的select *,sum(price) from orders group by product having sum(price)100;/ 将单价大于20 的商品进行归类显示 按照价格排序select * from orders where price20 group by product order by price;四、表的约束我们可以在创建表的同时为字段增加约束，对将来插入的数据做一些限定唯一约束 uniquecreate table a(name varchar(20) unique);insert into a values(aaa);insert into a values(aaa); 错 name有唯一约束非空约束 not nullcreate table b(id int,name varchar(20) not null);insert into b values(1,aaa);insert into b (id) values(2);错，name有非空约束主键 每张表都应该有个主键 方便我们找到记录主键必须有唯一约束、非空约束主键约束 primary keycreate table c(id int primary key,name varchar(20) not null);insert into c (id,name) values(1,aaaa);insert into c(id,name) values(1,bbbb); 错，主键重复insert into c(name) values(ccc); 错，主键不能为null主键可以定义为自动增长， 注意主键类型必须是intcreate table d(id int primary key auto_increment,name varchar(20);insert into d(name) values(ccc);insert into d(name) values(aaaa);delete from d where id=4;create table e(id int,name varchar(20);/ 增加主键约束alter table e modify id int primary key;/ 删除主键约束alter table e drop primary key;/ 创建联合主键create table f(firstname varchar(20),lastname varchar(20),primary key(firstname, lastname);insert into f values(zhao,nan);insert into f values(li, nan);最重要的约束 外键约束create table husband(id int primary key,name varchar(20);create table wife(id int primary key auto_increment,name varchar(20),husbandid int,constraint husbandid_FK foreign key(husbandid) references husband(id);insert into husband (id,name) values(3,liuxiaoshuai);insert into wife (name, husbandid) values(fengjie, 3);delete from husband where id=3;create table aaa(id int);/ 为aaa 加外键约束alter table aaa add constraint husid_FK foreign key(id) references husband(id);/ 删除外键约束alter table aaa drop foreign key husid_FK;五、对象和表的关系javabean 一张表多对一在多的一方建外键 参照一的一方的主键多对多需要创建中间表描述关系中间表需要两列 作为联合主键 同时又是外键分别参照两张表的主键一对一分清主从关系在从的一方建外键参照主的一方的主键由于在一对一的关系中外键不允许为空和重复(必须要找到主的一方，否则从的一方就没有存在的意义)干脆将从的一方的主键直接作为外键六、多表操作创建表 多表查询多对一 多的一方加外键create table department(id int primary key auto_increment,name varchar(20);create table employee(id int primary key auto_increment,name varchar(20),departmentid int,constraint departmentid_FK foreign key(departmentid) references department(id);/ 插入三个部门insert into department (name) values(开发部);insert into department (name) values(销售部);insert into department (name) values(人事部);/ 插入5个员工insert into employee (name, departmentid) values (张三, 1);insert into employee (name, departmentid) values (王五, 2);insert into employee (name, departmentid) values (李四, 3);insert into employee (name, departmentid) values (赵六, 2);insert into employee (name, departmentid) values (田七, 1);/ 查询1号部门的员工select * from employee where departmentid=1;销售部所有的员工select id from department where name=销售部;select * from employee where departmentid=2;select * from employee where departmentid=(select id from department where name=销售部);/ 多表查询select * from employee,department;+-+-+-+-+-+| id | name | departmentid | id | name |+-+-+-+-+-+| 1 | 张三 | 1 | 1 | 开发部 | 1 | 张三 | 1 | 2 | 销售部 | 1 | 张三 | 1 | 3 | 人事部 | 2 | 王五 | 2 | 1 | 开发部 | 2 | 王五 | 2 | 2 | 销售部 | 2 | 王五 | 2 | 3 | 人事部 | 3 | 李四 | 3 | 1 | 开发部 | 3 | 李四 | 3 | 2 | 销售部 | 3 | 李四 | 3 | 3 | 人事部 | 4 | 赵六 | 2 | 1 | 开发部 | 4 | 赵六 | 2 | 2 | 销售部 | 4 | 赵六 | 2 | 3 | 人事部 | 5 | 田七 | 1 | 1 | 开发部 | 5 | 田七 | 1 | 2 | 销售部 | 5 | 田七 | 1 | 3 | 人事部 |+-+-+-+-+-+笛卡尔积结果中有很多不匹配的数据 废数据去除不匹配数据 (废数据) 一张表的外键列=参照表的主键列select * from employee,department where employee.departmentid=department.id;+-+-+-+-+-+| id | name | departmentid | id | name |+-+-+-+-+-+| 1 | 张三 | 1 | 1 | 开发部 | 5 | 田七 | 1 | 1 | 开发部 | 2 | 王五 | 2 | 2 | 销售部 | 4 | 赵六 | 2 | 2 | 销售部 | 3 | 李四 | 3 | 3 | 人事部 |+-+-+-+-+-+加上我们的帅选条件select * from employee,department where employee.departmentid=department.id and =销售部;最终答案select e.* from employee e,department d where e.departmentid=d.id and =销售部;多对多create table teacher(id int primary key auto_increment,name varchar(20);create table student(id int primary key auto_increment,name varchar(20);/ 中间表create table tea_stu(teaid int,stuid int,primary key(teaid, stuid),constraint teaid_FK foreign key(teaid) references teacher(id),constraint stuid_FK foreign key(stuid) references student(id);/ 插入三个老师记录insert into teacher (name) values(老张);insert into teacher (name) values(老黎);insert into teacher (name) values(老方);/ 插入7个学生记录insert into student(name) values(张三);insert into student(name) values(李四);insert into student(name) values(王五);insert into student(name) values(赵楠);insert into student(name) values(刘小帅);insert into student(name) values(王芳);insert into student(name) values(刘红);/ 插入中间表 描述关系insert into tea_stu(teaid, stuid) values(1,1);insert into tea_stu(teaid, stuid) values(1,2);insert into tea_stu(teaid, stuid) values(1,3);insert into tea_stu(teaid, stuid) values(1,4);insert into tea_stu(teaid, stuid)