数据库语句

show databases ----查询库
drop database [数据库名] ---删除数据库
create database [数据库名] character set utf8/gbk ----新建数据库
show create database [数据库名] ---查询数据库详情
use [数据库名] --使用操作数据库
show tables ----查询所有表

表相关SQL

  • 创建表
    create table 表名 charset=utf8/gbk charset

    create table person(name varchar(10),age int) charset=utf8;

    create table student(name varchar(20),chinese int,math int,english int) charset=utf8;

查看表详情

show create table [表名]
show create tabale student;

查看表字段

desc [表名]
desc student;

删除表

drop table [表名]
drop table student;

添加表字段

最后面添加字段:alter table [表名] add [字段名] 类型;

alter table person add gender varchar(5);

最前面添加字段:alter table [表名] add [字段名] 类型 first;

alter table person add id int first;

xxx字段后面添加字段:alter table [表名] add [字段名] [类型] after [xxx字段名]

alter table person add salary int after name;

中文问题:

insert into person values(20,'刘德华',18);

删除表字段

alter table [表名] drop [字段名]
alter table person drop salary;

修改表字段

alter table [表名] changer [原名] [新名] [新类型]
alter table person change gender salary int;

DML 数据操作语言

create database mydb1 character set utf8;
use mydb1;
create table person(id int,name varchar(10),age int);
show create table person; 检查是不是utf8编码
  1. 插入
  • 全表插入格式:insert into [表名] values(值1,值2,值3);
    insert into person values(1,'wangge',18);
  • 指定字段格式:insert into 表名 values(值1,值2)
    insert into person(name ,age) values('zhuge',22);
  • 批量插入:
    insert into person values(10,'aaa',10),(11,'bbb',21),(10,'ccc',22);
    insert into person(name) values('xxx'),('yyy'),('zzz');
  • 中文文问题
    出错执行:set names gbk/utf8;
  1. 查询
  • 格式: select [字段信息] from [表名] where [条件];
    例子:select name from person; 查询表中所有的名字
    select name,age from person; 查询表中所有的名字和年龄
    select * from person; 查询表中的所有数据
    select name from person where id=1; 查询id为1的名字
  1. 修改
    格式:update [表名] set [字段名=值] where [条件];

update person set name='张三' where id=1;

  1. 删除
  • 格式:delete from [表名] where [条件];
    delete from person where id=1;
    delete from person where id>10;
    delete from person;

主键约束###主键唯一且不能为null

create table t1(id int primary key ,name varchar(10)) charset=utf8;
insert into t1 values(1,'aaa');
insert into t1 values(1,'bbb');//报错不能重复
insert into t1 values(unll,'ccc');//报错不能为null

导入sql

win:
source [盘符]
linux导入方法:
source /home/soft01/桌面/emp.sql

SQL注释

创建表时给字段添加的注释内容

create table t3(id int primary key auto_increment comment '这是主键id',name varchar(10) comment '这是个名字')charset=utf8;

查看注释查询

show create table t3;

is null 和is not null

查询没有上级领导的员工信息

select * from emp where mgr is null;

查询有领导的员工信息

select * from emp where mgr is not null;

去重 distinct

查询员工表所有不同工作

select distinct job from emp;

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

插叙工资小于等于3000的员工姓名和工资

select ename,job from emp where job!='程序员';
select ename,job from emp where job<>'程序员';

and和or

and 类似于Java中的 &&
or 类似于java中的||

  1. 1号部门工资大于2000的员工信息
    select * from emp where deptno=1 and sal>2000;
  2. 查询2号部门员工或工资高于2000的员工姓名,工资和部门编号
    select ename,sal,deptno from emp where deptno=2 or sal>2000;
  3. 查询有领导并且工资大于1500的员工姓名,工资,领导编号
    select ename,sal,mgr from emp where mgr is not null and sal>1500;

查询工资在2000到3000之间的员工姓名和工资

select ename,sal from emp where sal>=2000 and sal<=3000;
select ename,sal from emp where sal between 2000 and 3000;

in(x,y,x)

查询工资为800,3000,1500的员工信息

select * from emp where sal=800 or sal=3000 or sal =1500;
select * from emp where sal in(800,3000,1500);

模糊查询

%:代表0或多个未知的符号
_:代表一个未知的符

  1. 查询名字中姓孙开头的员工信息
    select * from emp where ename like '孙%';
    select ename,job from emp where job like '%销售%';

排序

格式: order by [字段] asc升序(默认)/desc降序

  1. 查询员工姓名和工资 按照工资升序排序
    select ename,sal from emp order by sal;
  2. 查询1号部门的员工信息 按照工资降序排序
    select * from emp where deptno=1 order by sal desc;

分页查询 limit

limit 跳过的跳数,请求的条数;

  1. 查询工资前三名的员工信息
    select * from emp order by sal desc limit 0,3
  2. 查询工资升序第2页的第5条数据
    select * from emp order by sal desc limit 5,5;
  3. 查询工资升序第三页的2条数据
    select * from emp order by sal limit 4,2;
  4. 查询工资在1000到5000之间的员工信息 按照工资降序排序第3页的3条数据
    select * from emp where sal between 1000 and 5000 order by sal desc limit 6,3;

数值计算 +- * / %

  1. 查询每个员工姓名,工资和年终奖(5个月的工资)
    select ename,sal,sal*5 from emp;
  2. 查询每个员工的的工资和长薪资5块钱之后的工资
    select sal,sal+5 from emp;

别名

select ename as '姓名' from emp;
select ename '姓名' from emp;
select ename 姓名 from emp;

聚合查询

  • 对查询的多条数据进行统计查询
  • 求平均值,最大值,最小值,求和,计数
  1. 平均值
  • 查询一号部门的平均工资
    select avg(sal) from emp where deptno=1;
  1. 最大值max
  • 查询2号部门最高的工资
    select max(sal) from emp where deptno=2;
  1. 最小值
  • 查询2号部门的最低工资
    select min(sal) from emp where deptno=2;
  1. 求和sum
  • 查询1号部门的工资总和
    select sum(sal) from emp where deptno=1;
  1. 计数count
  • 查询员工表中工资高于2000块钱的人数
    select count(*) from emp where sal>2000;
  • 查询1号部门的平均工资,最大工资,最小工资,工资总和,和人数对每个数据其别名
    select avg(sal) 平均工资,max(sal) 最大值,min(sal) 最小值,sum(sal) 求和,count(*) 人数 from emp where deptno=1;

分组查询

查询每个部门的平均工资

  1. 查询每个部门的平均工资
    select deptno,avg(sal) from emp group by deptno;
  2. 查询每个部门的最高工资
    select deptno,max(sal) from emp group by deptno;
  3. 查询每种工作的人数
    select job,count(*) from emp group by deptno;
  4. 查询每个部门工资大于1500的员工人数

having

  • where 后面只能写普通字段的条件 不能写聚合函数的条件
  • having: 结合group by使用,后面写聚合函数的条件
  • 各种关键字的顺序:
    select ......from...[表名] where.....group by.....having....
    order by......limit......
  1. 查询每个人部门的平均工资 要求平均工资高于2000
    select deptno,avg(sal) a from emp group by deptno having a>2000;
  2. 查询每个部门的平均工资,只查询工资的1000-3000之间的员工,并且过滤掉平均工资低于2000的部门
    select deptno,avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000;
  3. 查询每个部门的工资总和,值查询有上级两道的原并且要求工资总和和大于5400,最后按照工资总和降序排序,只查询结果中的第一条数据
    select deptno,sum(sal) s from emp where mgr is not null group by deptno having s>5400 order by s desc limit 0,1;

子查询(嵌套查询)

  1. 查询工资高于平均工资的员工信息
    select * from emp where sal>(select avg(sal) from emp);
  2. 查询工资最高的员工信息
    select max(sal) from emp
    select * from emp where sal=(select max(sal) from emp);
  3. 查询工资高于2号部门平均工资的员工信息
select avg(sal) from emp where deptno=2
select * from emp where sal>(select avg(sal) from emp where deptno=2) ;
  1. 查询和孙悟空相同工作的其他员工信息
    select job from emp where ename='孙悟空'
    select * from emp where job=(select job from emp where ename='孙悟空') and ename!='孙悟空';
  2. 查询白骨精的部门信息(需要用到dept表)
    select deptno from emp where ename='白骨精'
    select * from dept where deptno=(select deptno from emp where ename='白骨精');
  3. 查询有员工的部门信息(想办法过滤掉4号部门)
    select distinct deptno from emp
    select * from dept where deptno in(select distinct deptno from emp)

关联关系

  • 创建表时,表与表之间存在的业务关系称为关联关系
  • 有哪些福安息:
  1. 1对1:有AB两张表,A表中的一条数据对应B表的一条数据,同时B表中的一条对应A表中的一条;
  2. 1对多:有AB两张表,A表中的一条数据对应B表中的多条数据,同时B表中的一条对应A表中的一条;
  3. 多对多:有AB两张表,A表中的一条数据对应B表中的多条数据,同时B表中的一条对应A表中的多条;

关联查询

  • 同时查询多张表数据的查询方式称为关联查询
  • 进行县关联查询时必须写关联查询,如果不写会得到两张表的乘积(称为笛卡尔积),工作中切记不要出现
  • 关联查询的方式有三种:

等值连接

  • 格式: select * from A,B where A.x=B.x and A.age=18;
  1. 查询每个院的姓名和对应的部门名
    select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
  2. 查询工资低于2000块钱的员工姓名,工资和部门名
    select e.ename,e.sal,d.dname
    from emp e,dept d
    where e.deptno=d.deptno and e.sal<2000;

关联查询之内连接

  • 格式: select * from A join B on A.x=B.x where A.age=18;
  1. 查询每个员工的姓名和对应的部门编号
    select e.ename,d.dname
    from emp e join dept d
    on e.deptno=d.deptno;
  2. 查询工资定于2000块钱的员工姓名,工资和部门名
    select e.ename,e.sal,d.*
    from emp e join dept d
    on e.deptno=d.deptno
    where e.sal<2000;
  • 等值临界和内连接查询的都是两张表的交集数据

外连接

  • 如果需要查询一张表的全部数据和另外一张表的交集数据使用外连接
  • 格式:select * from A left/right join B on A.x=B.x where A.age=18;
  1. 查询所有部门的名称和对应的员工名字
    select e.ename,d.dname
    from emp e right join dept d
    on e.deptno=d.deptno;
  2. 查询所有员工姓名和对应的部门信息
    insert into emp (empno,ename) values(100,'灭霸');

    select e.ename,d,*
    from emp e left join dept d
    on e.deptno=d.deptno;

Last modification:November 19th, 2019 at 01:40 pm
如果觉得我的文章对你有用,请随意赞赏

One comment

  1. Typecho

    欢迎加入 Typecho 大家族

Leave a Comment