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;
创建一个使用utf-8字符集的mydb2数据库。
create database mydb2 character set utf8;
创建一个使用utf-8字符集，并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_general_ci;

查看当前数据库服务器中的所有数据库
show databases;
查看前面创建的mydb2数据库的定义信息
show create database mydb2;
删除前面创建的mydb3数据库
drop database mydb3;

将mydb2的字符集修改为gbk
alter database mydb2 character set gbk;

// 备份数据库
use mydb2;
create table a
(
name varchar(20)
);
// 插入数据
insert into a (name) values("zhangsan");
insert into a (name) values("wangwu");

// 备份数据库
mysqldump -uroot -p mydb2 > d:\a.sql

// 删除 mydb2
drop database mydb2;

// 恢复数据库
create database mydb22;
source d:\a.sql

// source 命令
指定一个sql脚本

二、操作表
1. 创建表
创建员工表
Id 整形
name 字符型
sex 字符型或bit型
brithday 日期型
Entry_date 日期型
job 字符型
Salary 小数型
resume 大文本型

create table employee
(
id int,
name varchar(20),
sex varchar(6),
brithday date,
entry_date date,
job varchar(20),
salary double,
reshme text
);

2. 修改表
在上面员工表的基础上增加一个image列
alter table employee add image blob;

修改job列，使其长度为60
alter table employee modify job varchar(60);

删除sex列。
alter table employee drop sex;

表名改为users。
rename table employee to user;

修改表的字符集为utf-8
alter table user character set utf8;

列名name修改为username
alter table user change column name username varchar(20);

查看所有表
show tables;

查看表的创建语句
show create table user;

查看表结构
desc user;

删除 user 表
drop table user;

三、数据的增删改查
1. insert语句
employee.sql
create table employee
(
id int,
name varchar(20),
sex varchar(10),
birthday date,
salary float,
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','绝种好男人');

// 查看表的所有记录
select * from employee;

// 查看数据使用的所有编码
show variables like 'character%';

// 修改客户端的编码 为 gbk
set character_set_client=gbk;

// 修改结果集的编码为gbk
set character_set_results=gbk;

// insert 语句的其他写法
create table a
(
username varchar(20),
password varchar(20)
);

insert into a values('zs','111');

insert into a values('wangwu','2222'),('lisi','3333');

insert a values('aaa','bbb');

insert a (username) values('aaa');

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;
使用truncate删除表中记录。 摧毁表再创建表
truncate 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,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',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 department.name='销售部';

最终答案
select e.* from employee e,department d where e.departmentid=d.id and d.name='销售部';

多对多
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 t.name='老方';

多表查询
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 person.name='lisi';