数据库操作

SQL操作分类：

• DDL:数据定义语言，负责数据库和表相关的操作
• DML:数据操作语言，负责数据的增删改查
• DQL:数据查询语言，只包括查询数据
• TCL:事务控制语言，处理事务相关的
• DCL:数据控制语言，负责处理和用户权限相关

DDL数据定义语言

• 使用数据库软件保存数据需要先建库，再建表，在表里对数据进行操作
• mysql里面可以创建N个数据库，每个数据库里面可以创建N个表，每个表里可以存N条数据

1、数据库相关的SQL语句

1.1查询所有数据库
		格式：show databases;
	1.2创建数据库
		格式：create database 数据库名;
		指定字符集格式：create database character set uft8/gbk;
		举例：create database db1;
			create database db2 character set utf8;
			create database db3 character set gbk;
	1.3查看数据库详情
		格式：show create database 数据库名;
	1.4删除数据库
		格式：drop database 数据库名;
		drop database db1;
		drop database db2;		
		drop database db3;	
    1.5使用数据库
    	在进行表相关和数据相关操作时必须使用了某个数据库
    	格式：use 数据库名;
    	create database db1;
    	use db1;

 2、数据库表相关的SQL语句

1.创建表
格式：create table 表名 (字段1 类型，字段2  类型)charset=utf8/gbk；

例：
create table person(name varchar(10),age int)

charset=utf8;

创建学生表，字段：名字，年龄，性别

create table student(name varchar(10),age int,gender varchar(1));

charset=utf8;

2.查询所有表
格式：show tables；

3.查询表详情
格式: show create table 表名；
show create table person;

4.删除表
格式：drop table 表名;
drop table student;

5.修改表名
格式：rename table 原名 to 新名;
rename table person to t_person;

6.查看表字段
格式： desc 表名;
	desc t_person;

7.添加表字段
最后添加格式：alter table 表名 add 字段名 类型：
alter table t_person add gender varchar(1);
最前面添加格式：alter table b add 字段名 类型 first;
alter table t_person add id int first;
在某个字段后面添加：alter table 表名 add 字段名 类型 after 字段名;
alter table t_person add sal int after name;

8.删除表字符
格式： alter table 表名 drop 字段名;
alter table t_person drop gender;

9.修改表字段
格式：alter table 表名 change 原名 新名 新类型；
alter table t_person change sal salary int;
desc t_person;

DML数据操作语言

执行此类操作必须先使用数据库。

1、增：插入数据

全表插入格式：
	insert into 表名 values(值1,值2,值3);值的数量和顺序和表字段一致
	insert into emp values(1,'TOM',13);
指定字段插入格式：
	insert into 表名(字段1名,字段2名)values(值1,值2);
	insert into emp(id,age)values(1,18);
中文问题：	
	insert into emp values(3,'刘德华',50);
	如果出现插入中文错误，执行set names gbk;
批量插入，在插入数据的values后面添加多组值即可
	insert into emp values(4,'刘备',19),(5,'关羽',18),(6,'张飞',17);

2、查：查询数据

格式：select 字段信息 from 表 where 条件；
select id,name,chinese,enlish,math,from student;
select*from student;查询Student表中所有的字段信息
select name from emp;查询所有的名字
select name,age from emp where age<20;查询年龄小于20的名字和年龄
select*from student where name='猪八戒';查询名字是猪八戒的同学

3、改：修改数据

格式：update 表名 set xxx=xxx,xxx=xxx where 条件;
update student set math=25 where id=3;
把张飞的id修改为9，年龄改为666
update emp set id=9,age=666 where name='张飞';
如果条件为NULL 不能用= 要用is
update emp set name='唐僧'where name is null

4、删：删除数据

格式 delete from 表名 where 条件;
delete from emp where age<20;
delete from emp where name='张飞';
delete from emp;

DQL数据查询语言

去重distinct

用法：

查询的数据，去掉重复

查询员工表中出现的不同的工作

查询员工表中出现的部门编号，去掉重复

select distinct deptno from emp;

比较运算符 > < >=<= != 和<>

查询工资小于3000的员工信息
select *from emp where sal<=3000;
查询不是程序员的员工姓名和工作
select ename,job from emp where job!='程序员';
select ename,job from emp where job<>'程序员';

and和or

and类似于java中的&& 查询多个条件同时满足时使用and

or类似于java中的|| 查询多个条件时满足一个就行的时候使用or

查询1号部门工资小于3000的员工信息
查询3号部门或工作是程序员的员工信息
查询有上级领导的员工中工资小于2000的员工姓名，工资和领导编号
select *from emp where deptno=1 and sal<3000;
select *from emp where deptno=3 or job='程序员';
select ename,sal,mgr from emp where mgr is not null  and sal<2000;

in关键字

当查询某个字段的值为多个值时使用

查询工资为3000，5000，1500的员工姓名和工资
select ename,sal from emp where sal  in(3000,5000,1500);
查询工资不是3000和5000的员工信息
select * from emp where sal not in(3000,5000);

between x and y

当查询某个字段的值在两者之间时使用 包含x和y

 查询工资在1000-2000之间的员工信息   

   select * from emp where sal between 1000 and 2000;

 查询工资在1000-2000之外的员工信息

   select * from emp where sal not between 1000 and 2000;

模糊查询like

• %: 代表0或多个未知字符

• _:代表1个未知字符

• 举例:

  1. 以x开头   x%

  2. 以x结尾   %x

  3. 第二个是   x _x%

  4. 包含x   %x%

  5. 第一个是x倒数第三个是y    x%y__

1. 查询名字中包含悟字的员工姓名

   select ename from emp where ename like '%悟%';

2. 查询工作中第二个字是售 的员工信息

   select * from emp where  job like '_售%';

3. 查询名字中以精结尾的员工姓名

   select ename from emp where ename like '%精';

4. 查询姓孙的员工信息

   select * from emp where ename like '孙%';

 

排序order by

• 对查询的结果按照某个字段进行排序

• asc代表升序(默认) desc代表降序

1. 查询每个员工的姓名和工资,按照工资升序排序

   select ename,sal from emp order by sal asc;

   select ename,sal from emp order by sal;

2. 查询每个员工的姓名和工资,按照工资降序排序

   select ename,sal from emp order by sal desc;

• 多字段排序:在order by后面写多个字段 通过逗号分隔

1.查询每个员工的姓名,工资和部门编号,按照部门编号升序排序,如果部门编号一致则按照工资降序排序

select ename,sal,deptno from emp order by deptno, sal desc

分页查询limit

• 指定查询某一部分数据

• limit 跳过的条数,请求的条数(也代表每页的条数)

1. 查询第一页的3条数据

   select * from emp limit 0,3;

2. 查询第二页的4条数据

   select * from emp limit 4,4;  

3. 查询第三页的3条数据     

   - 请求第三页就是跳过2页,请求多少条数据就是每页有多少条, 这样跳过的就是2页*每页3条,跳过的就是6条

   select * from emp limit  6,3;

数值计算 + - * / %

对查询的某个字段的数据可以之间进行计算

1、查询每个员工的姓名，工资和年终奖（年终奖 =工资*5）
select ename,sal,sal*5 from emp;
2、给1号部门的员工涨薪5快
update emp set sal=sal+5 where deptno=1;

别名：

给查询的字段起个名字

select  ename as '名字' from emp;

select  ename  '名字' from emp;

select  ename  名字,sal 工资 from emp;

聚合函数

聚合函数就是对查询的多条数据进行统计查询

统计方式：1.平均值 2.最小值 3.最小值 4.求和 5.计数

1.平均值 avg(字段名)

查询2号部门的平均工资

select avg(sal) from emp where deptno = 2;


2.最小值 min(字段)

查询没有领导的员工中最低工资

select min(sal) from emp where mgr is null;


3.最大值 max(字段)

查询1号部门的最高工资

select max(sal) from emp where deptno =1


4.求和 sum(字段名)

查询2号部门的工资总和

select sum(sal) from emp where deptno =2;


5.计数 count(*)

查询工资大于2000快的员工人数

select count(*) from emp where sal>2000;

 分组查询 group by

分组查询可以将某个字段相同的数值划分为一组，以组为单位进行统计查询(聚合函数)

1、查询每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;


2、查询每种工作的人数

select job,count(*) from emp group by job;


3、查询每个部门的最高工资

select deptno,max(sal) from emp group by deptno;


4、查询每个部门的人数

select deptno,count(*) from emp group by deptno;


5、查询1号部门每种工作的人数

select job ,count(*) from emp where deptno=1 group by job;

having

关系：where 早于 group by 早于 having

注意：having where后面只能写普通的字段条件，聚合函数的条件不能写在where后面

查询数据时，条件为聚合函数的条件则使用having关键字，此关键字还要结合group by使用 having写在group by后面。

1.查询每个部门的平均工资要求工资大于2000 
select deptno,avg(sal) a from emp group by deptno having a>2000; 
2.查询每个部门的人数，只查询部门人数为3的 
select deptno,count(*) c from emp group by deptno having c=3; 
3.查询每个部门的平均工资，只查询工资在1000-3000之间的，并且 过滤掉平均工资低于2000的部门 
select deptno,avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)>=2000;

子查询（嵌套查询）

第一次查询出来的结果作为第二次查询的条件

1.查询工资高于1号部门平均工资的员工信息 ：
    1号部门的平均工资 select avg(sal) from emp where deptno=1; 
select * from emp where sal> (select avg(sal) from emp where deptno=1);


2.查询和孙悟空相同工作的其他员工信息 ：
    得到孙悟空的工作：select job from emp where ename='孙悟空'; 
select *from emp where job = (select job from emp where ename='孙悟空'); 


3.查询拿最低工资员工的同事们的信息(同事指的是同一部门)：
     得到最低工资：select min(sal) from emp; 
    通过最低工资得到部门编号: select deptno from emp where sal =(select min(sal) from emp)； 

select *from emp where deptno = ( select deptno from emp where sal =(select min(sal) from emp));