sql语句—实例及答案

sql 语句大致可以分类两大类SQL(结构化查询语言)

针对数据库database和表table的操作

创建create

查看show

修改alter

删除drop

// 创建一个数据库

create database mydb;

// 查看所有的数据库库

show databases;

// 删除 mydb

drop 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 mydatabase

create 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列，使其长度为60

alter table employee modify job varchar(60);

删除sex列。

alter table employee drop sex;

表名改为users。

rename table employee to user;

列名name修改为username

alter 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.sql

create 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 where

english+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 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 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 by

create 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 price>20 group by product order by price;

四、表的约束

我们可以在创建表的同时为字段增加约束，对将来插入的数据做一些限定

唯一约束 unique

create table a

(

name varchar(20) unique

);

insert into a values('aaa');

insert into a values('aaa'); 错 name有唯一约束

非空约束 not null

create table b

(

id int,

name varchar(20) not null

);

insert into b values(1,'aaa');

insert into b (id) values(2); 错，name有非空约束

主键每张表都应该有个主键方便我们找到记录

主键必须有唯一约束、非空约束

主键约束 primary key

create 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

主键可以定义为自动增长，注意主键类型必须是int

create 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 https://www.360docs.net/doc/5116689270.html,='销售部';

最终答案

select e.* from employee e,department d where e.departmentid=d.id and https://www.360docs.net/doc/5116689270.html,='销售部';

多对多

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 and

ts.teaid=2;

查询老方的所有学生

select s.* from student s, teacher t, tea_stu ts where ts.stuid=s.id and ts.teaid=t.id and https://www.360docs.net/doc/5116689270.html,='老方';

多表查询

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 https://www.360docs.net/doc/5116689270.html,='lisi';