常用SQL语句集

11-sql语句

　　数据库： 保存在硬盘上的文件
数据库产品： 采用一种优良的数据结构来保存数据，方便我们对数据进行查询和修改

　　sql语句： 用于操作数据库的语句

　　数据库database 表table

　　创建create
查看show
修改alter
删除drop

　　所有对数据库和表单的操作 就是上面六个单词的组合

　　// 创建一个数据库
create database mydb;

　　// 删除一张表
drop table mytable;

　　一、 数据库的操作
创建一个名称为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;
查看数据库的创建语句
show create database mydb2;

　　删除前面创建的mydb3数据库
drop database mydb3;
drop database if exists mydb3;  如果存在才会删除

　　数据库的修改
数据库一旦创建名称无法修改  ， 字符集和校对规则可以改
把mydb2的字符集修改为gb2312
alter database mydb2 character set gb2312;

　　备份数据库
// 使用数据库
use mydb2;
// 创建表
create table a
(
 name varchar(20)
);
// 插入数据
insert into a(name) values('aaa');
insert into a(name) values('bbb');

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

　　删除 mydb2
drop database mydb2;

　　恢复数据库  实际上是恢复数据库中的数据
创建数据库 使用数据库
create database mydb3;
use mydb3;
source d:\a.sql

　　实际上source命令用于执行一段sql脚本

　　二、 表的操作
创建表
id 整形
name 字符型
gender 字符型或bit型
brithday 日期型
entry_date 日期型
job 字符型
Salary 小数型
resume 大文本型

　　create table employee
(
 id int,
 name varchar(20),
 gender varchar(6),
 birthday date,
 entry_date date,
 job varchar(20),
 salary float,
 resume text
);

　　修改表
在上面员工表的基础上增加一个image列
alter table employee add image blob;
修改job列，使其长度为60
alter table employee modify job varchar(60);
删除gender列。
alter table employee drop gender;
表名改为users。
rename table employee to users;
修改表的字符集为utf-8
alter table users character set utf8;
列名name修改为username
alter table users change column name username varchar(40);

　　查看所有的表
show tables;
查看表的创建语句
show create table users;
查看表的结构
desc users;

　　// 删除表
drop table employee;

　　三、 表中数据的操作 (重点)
crud： create read update delete

　　sql语句
1. insert语句  增加数据
employee.sql
create table employee
(
 id int,
 name varchar(20),
 gender varchar(10),
 birthday date,
 salary float,
 entry_date date,
 resume text
);
插入三条记录
insert into employee(id,name,gender,birthday,salary,entry_date,resume) values(1,'zhangsan','male','1980-1-1',1000,'2000-3-16','good boy');
insert into employee(id,name,gender,birthday,salary,entry_date,resume) values(2,'lisi','male','1983-1-1',1000,'2010-3-16','good boy');
insert into employee(id,name,gender,birthday,salary,entry_date,resume) values(3,'xiaohong','female','1984-1-1',1000,'2008-3-16','good girl');

　　insert into employee(id,name,gender,birthday,salary,entry_date,resume) values(4,'王五','男','1983-1-1',1000,'2010-3-16','一个好男孩');

　　// 查看数据库的所有编码
show variables like 'character%';
character_set_client 使用的客户端编码
character_set_results 结果集的编码

　　设置的方式
set character_set_client=gbk
set character_set_results=gbk

　　// 创建一张a表
create table a
(
 id int,
 name varchar(20)
);

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

　　insert into a(name) values('bbbb');

　　insert a(id,name) values(2,'cccc'),(3,'dddd');

　　规范的方式书写
insert into a(id,name) values(6,'eeeee');

　　查看表中的数据
select * from employee;

　　2. update语句 更新数据
将所有员工薪水修改为5000元。
update employee set salary=5000;
将姓名为’zhangsan’的员工薪水修改为3000元。
update employee set salary=3000 where name='zhangsan';
将姓名为’lisi’的员工薪水修改为4000元,gender改为female。
update employee set salary=4000,gender='female' where name='lisi';
将xiaohong的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name='xiaohong';

　　3. delete语句 删除数据
删除表中name为’zhangsan’的记录。
delete from employee where name='zhangsan';
删除表中所有记录。
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);
insert into student(id,name,chinese,english,math) values(8,null,75,65,30);

　　查询表中所有学生的信息。
select * from student;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
过滤表中重复数据。
select distinct english from student;

　　在所有学生分数上加10分特长分。
select name,chinese+10 as chinese,math+10,english+10 from student;
统计每个学生的总分。
select name,chinese+math+english from student;
使用别名表示学生分数。
select name,chinese c,english e from student;

　　查询姓名为李一的学生成绩
select * from student where name='李一';
查询英语成绩大于90分的同学
select * from student where english>90;
查询总分大于200分的所有同学
select * from student where english+chinese+math>200;

　　查询英语分数在 80－90之间的同学。 两头的值都包含
select * from student where english between 80 and 90;
查询数学分数为89,90,91的同学。
select * from student where math in(89,90,30);
查询所有姓李的学生成绩。
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+english+math as sss from student where english>80 or chinese+english+math>200;

　　对数学成绩排序后输出。
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;

　　合计函数 --  count
统计一个班级共有多少学生？
select count(*) from student;
统计数学成绩大于90的学生有多少个？
select count(*) from student where math>90;
统计总分大于230的人数有多少？
select count(*) from student where math+chinese+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;

　　求一个班级数学平均分？
select avg(chinese) from student;
求一个班级总分平均分
select avg(chinese+math+english) from student;

　　求班级语文最高分和最低分
select max(chinese) from student;

　　group by 子句 用于分类查询
orders.sql
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 product,sum(price) from orders group by product;

　　查询购买了几类商品，并且每类总价大于100的商品
select product,sum(price) from orders group by product having sum(price)>100;

　　四、表的约束
有的时候针对某个列，我们不希望别人插入任意的数据，这样会导致错误
例如插入null值，取数据的时候就会出错
例如有的列我们希望数据是有唯一性的(不允许重复)
not null 非空约束   这一列不允许插入null值
create table a
(
 name varchar(20) not null
);

　　insert into a(name) values('aaa');

　　insert into a(name) values(null);

　　// id需要唯一  unique 唯一约束   限定了列的值不能重复
create table b
(
 id int not null unique,
 name varchar(20)
);

　　insert into b(id,name) values(1,null);

　　insert into b(id,name) values(2,'zhangsan');

　　insert into b(id,name) values(2,'lisi');

　　// 只用唯一约束
create table c
(
 id int unique,
 name varchar(20)
);

　　insert into c(name) values('aaa');
insert into c(name) values('bbb');

　　insert into c(id,name) values(1,'ccc');

　　// 通常来讲我们会根据id来查询某条记录   id一般会加非空约束、唯一约束
// 主键约束   =  非空约束 + 唯一约束
create table d
(
 id int primary key,
 name varchar(20)
);

　　insert into d(name) values('aaa'); 不行 ，因为非空
insert into d(id,name) values(1,'aaa');
insert into d(id,name) values(1,'bbb'); 不行，唯一要唯一

　　通常来讲 如果主键定义为int 我们为了方便  会定义主键自定增长
create table e
(
 id int primary key auto_increment,
 name varchar(20)
);

　　insert into e(name) values('aaa');
insert into e(id,name) values(4,'bbb');

　　// 声明联合主键
create table f
(
 firstname varchar(20),
 lastname varchar(20),
 primary key(firstname,lastname)
);

　　insert into f(firstname,lastname) values('aaa','bbb');

　　insert into f(firstname,lastname) values('aaa','ccc');

　　insert into f(firstname) values('eee');

　　// 增加主键约束
create table g
(
 id int,
 name varchar(20)
);

　　// 增加两条记录
insert into g values(1,'aaa');

　　// 增加主键约束   但是如果主键列有重复或者为空的情况，主键约束加不上
alter table g add primary key(id);

　　// 删除主键约束
create table h
(
 id int primary key,
 name varchar(20)
);

　　alter table h drop primary key;

　　夫妻关系系统
创建老公表
create table husband
(
 id int primary key auto_increment,
 name varchar(20)
);

　　insert into husband(name) values('张三');
insert into husband(name) values('李四');
insert into husband(id,name) values(3,'王五');

　　创建老婆表
create table wife
(
 id int primary key auto_increment,
 name varchar(20),
 husbandid int
);
insert into wife(name,husbandid) values('小红',2);
insert into wife(name,husbandid) values('小兰',3);
insert into wife(name,husbandid) values('小黑',1);

　　insert into wife(name,husbandid) values('小黄',8);

　　// 小兰来找老公
select * from husband where id=3;

　　// 删除王五
delete from husband where id=3;

　　// 外键约束
某一列数据是参照另一张表的某一个列  这是就需要加外键约束
好处： 外键列只能插入参照列存在的值， 参照列被参照的值是不能删除的

　　create table wife
(
 id int primary key auto_increment,
 name varchar(20),
 husbandid int,
 constraint husbandid_FK foreign key(husbandid) references husband(id)
);

　　表的关系 有三种情况
多对一
在多的一方建立外键

　　多对多
需要创建中间表描述关系
中间表有两个字段都是外键参照两个表的主键列，同时这两列又是联合主键

　　一对一
分清主从关系
在从的一方建立外键  此时应将主键直接作为外键

　　多对一
创建部门表
create table department
(
 id int primary key auto_increment,
 name varchar(20)
);
创建员工表
drop table if exists employee;
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('人事部');

　　insert into employee(name,departmentid) values('张三',1);
insert into employee(name,departmentid) values('李四',1);
insert into employee(name,departmentid) values('王五',2);
insert into employee(name,departmentid) values('赵六',2);
insert into employee(name,departmentid) values('田七',2);
insert into employee(name,departmentid) values('周八',3);

　　多表查询
查出1号部门所有的员工
select * from employee where departmentid=1;
查出销售部所有的员工
select * from department where name='销售部';
select * from employee where departmentid=2;
多表查询
select * from employee,department;

　　笛卡尔集  ：  两张表所有记录的组合
+----+------+--------------+----+--------+
| id | name | departmentid | id | name   |
+----+------+--------------+----+--------+
|  1 | 张三     |            1 |  1 | 开发部       |
|  1 | 张三     |            1 |  2 | 销售部      |
|  1 | 张三     |            1 |  3 | 人事部      |
|  2 | 李四     |            1 |  1 | 开发部       |
|  2 | 李四     |            1 |  2 | 销售部      |
|  2 | 李四     |            1 |  3 | 人事部      |
|  3 | 王五     |            2 |  1 | 开发部       |
|  3 | 王五     |            2 |  2 | 销售部      |
|  3 | 王五     |            2 |  3 | 人事部      |
|  4 | 赵六     |            2 |  1 | 开发部       |
|  4 | 赵六     |            2 |  2 | 销售部      |
|  4 | 赵六     |            2 |  3 | 人事部      |
|  5 | 田七     |            2 |  1 | 开发部       |
|  5 | 田七     |            2 |  2 | 销售部      |
|  5 | 田七     |            2 |  3 | 人事部      |
|  6 | 周八    |            3 |  1 | 开发部       |
|  6 | 周八    |            3 |  2 | 销售部      |
|  6 | 周八    |            3 |  3 | 人事部      |
+----+------+--------------+----+--------+

　　去掉废数据  (不匹配的数据 错误的数据)
参照表的外键列等于被参数表的主键列(被参照的列)
select * from employee,department where employee.departmentid=department.id;
+----+------+--------------+----+--------+
| id | name | departmentid | id | name   |
+----+------+--------------+----+--------+
|  1 | 张三     |            1 |  1 | 开发部       |
|  2 | 李四     |            1 |  1 | 开发部       |
|  3 | 王五     |            2 |  2 | 销售部      |
|  4 | 赵六     |            2 |  2 | 销售部      |
|  5 | 田七     |            2 |  2 | 销售部      |
|  6 | 周八    |            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(1,5);
insert into tea_stu(teaid,stuid) values(2,7);
insert into tea_stu(teaid,stuid) values(2,6);
insert into tea_stu(teaid,stuid) values(2,4);
insert into tea_stu(teaid,stuid) values(2,2);
insert into tea_stu(teaid,stuid) values(3,1);
insert into tea_stu(teaid,stuid) values(3,3);
insert into tea_stu(teaid,stuid) values(3,4);
insert into tea_stu(teaid,stuid) values(3,5);
insert into tea_stu(teaid,stuid) values(3,7);

　　// 查询
2号老师的所有学生的id
select stuid from tea_stu where teaid=2;
2号老师的所有的学生信息
select s.* from student s,tea_stu ts where ts.stuid=s.id and ts.teaid=2;
查看老黎的所有学生
select s.* from student s,teacher t,tea_stu ts where ts.teaid=t.id and ts.stuid=s.id and t.name='老黎';

　　n张表联合查询，需要写n-1个条件来去掉废数据， 然后加上真正的筛选条件来查
参照表的外键列=被参照表的被参照列(主键)