sql语句—实例及答案

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','goodgirl');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 english>90;查询总分大于200分的所有同学select name,english+chinese+math from student whereenglish+chinese+math>200;运算符查询英语分数在 80－90之间的同学。

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 math>80 and chinese>80;查询英语>80或者总分>200的同学select *,chinese+math+english from student where english>80 or chinese+english+math>200;order by 子句对数学成绩排序后输出。

select * from student order by math;对总分排序后输出，然后再按从高到低的顺序输出select *,chinese+math+english from student order bychinese+math+english desc;对姓李的学生成绩排序输出select * from student where name like '李%' order bychinese+math+english;合计函数count统计一个班级共有多少学生？select count(*) from student;统计数学成绩大于90的学生有多少个？select count(*) from student where math>90;统计总分大于230的人数有多少？select count(*) from student where chinese+math+english>230;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 havingsum(price)>100;// 将单价大于20 的商品进行归类显示按照价格排序select * from orders where price>20 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 whereemployee.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 whereemployee.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) values(2,3);insert into tea_stu(teaid, stuid) values(2,4);insert into tea_stu(teaid, stuid) values(2,5);insert into tea_stu(teaid, stuid) values(2,7);insert into tea_stu(teaid, stuid) values(3,2);insert into tea_stu(teaid, stuid) values(3,4);insert into tea_stu(teaid, stuid) values(3,1);insert into tea_stu(teaid, stuid) values(3,5);insert into tea_stu(teaid, stuid) values(3,6);// 查询查询2号老师的学生信息select s.* from student s, tea_stu ts where s.id=ts.stuid andts.teaid=2;查询老方的所有学生select s.* from student s, teacher t, tea_stu ts where ts.stuid=s.id and ts.teaid=t.id and ='老方';多表查询n张表联合查需要去掉废数据需要n-1个条件条件都是外键列=参照列一对一create table person(id int primary key auto_increment,name varchar(20));create table idcard(id int primary key,location varchar(20),constraint personid_FK foreign key(id) references person(id) );insert into person (name) values('zhangsan');insert into person (name) values('lisi');insert into idcard (id,location) values(2,'天津');insert into idcard (id,location) values(1,'上海');查李四的身份证select idcard.* from person,idcard where idcard.id=person.id and ='lisi';。