数据库常用命令

AGEE
329 阅读8分钟

数据库的操作

创建一个数据库设置字符集
create database 数据库名 character set utf8;
查询所有的数据库
show databases;
使用指定的数据库
use 数据库名;
查询数据库的详情
show create 数据库名;
删除数据库
drop database 数据库名;

创建数据库中的表

创建表
create table 表名 (字段1名 类型,字段2名 类型) character =uft8/gbk;
查询所有表
show datables;
查看表详情
show create table 表名;
查看表字段
desc 表名;
删除表
drop table 表名;
修改表名
rename table 原名 to 新名;
添加表字段
alter 表名 add 表字段 字段类型 first/after xxx;
默认添加最后面
id int first 添加到最前面
salary int after name 添加到名字后面
删除表字段
alter table 表名 drop 字段名;
修改表字段
alter table 表名 change 原名 新名 新类型

数据库的使用（更新数据）

插入数据
全表插入格式：
        insert into 表名 values (值1, 值2);
指定插入格式：
        insert into 表名 (字段名1,字段名2) values (值1, 值2);
批量插入格式：
        insert into 表名 values (值1, 值2),(值1, 值2),(值1, 值2);
insert into person values('Tom',18);
insert into person(name)values('Jerry');
insert into person values('Lucy',58),('Lily',59);
insert into person(name) values('LiLei'),('Hanmeimei');
简单查询
select 字段信息 from 表名 where 条件
select * from person; 查询表中所有的数据
select name from person; 查询所有的名字
select * from person where age >50; 查询年龄大于50岁的信息
修改数据
 update 表名 set xxx=xxx where name ='jerry';
 update person set age =30 where name ='Jerry';
 update person set age =20 where age is null;
删除数据
delete from 表名 where 条件;
delete from person where age =20;
delete from person where name ='Tom';
delete from person;

数据类型与约束

数据类型 int和bigint(long) zerofill数据长度不足时用0补位
 create table t1(
        name varchar(10),
        age int(10) zerofill
 );
zerofill表示这个列的数据长度不足时前面用0补位
        insert into t1 values('aaa',18);
数据库约束
  • 什么是主键：表示数据唯一性的字段称为主键
  • 什么是约束：创建表时，给表字段添加的限制条件
主键约束效果就是当前列的数据在表中唯一且非空
 create table t2(
        int int primary key,
        name varchar(10)
 );
insert into t2 values(1,'aaa'); //成功
insert into t2 values(1,'bbb'); //报错，不能重复
insert into t2 values(null,'ccc'); //报错 不能为null
主键约束加自增
自增规则：从历史最大值+1,只增不减
 create table t3(
        int int primary key auto_increment,
        name varchar(10)
 );
insert into t3 values(null,'aaa'); //1
insert into t3 values(null,'bbb'); //2
insert into t3 values(10,'ccc'); //10
insert into t3 values(null,'ddd'); //11
delete from t3 where id >=10;
insert into t3 values(null,'eee'); //12
非空约束
被设置非空约束的列的数据不能为空
 create table t4(
        int int primary key auto_increment,
        name varchar(10) not null,
        addr varchar(30)
 );
insert into t4 values(null,'aaa',null); //ok
insert into t4 values(null,null,'beijing'); //fail
insert into t4 values(null,'','shanghai'); //ok
唯一约束
被设置唯一约束的列数据不能出现重复的值
 create table t5(
        int int primary key auto_increment,
        name varchar(10) not null,
        phone varchar(11) unique
 );
insert into t4 values(null,'aaa',null); //ok
insert into t4 values(null,'bbb','123'); //ok
insert into t4 values(null,'ccc','234'); //ok
insert into t4 values(null,'eee','123'); //fail
一个列可以同时设置非空约束和唯一约束，但是这样修饰的列并不是主键

数据查询

source 数据导入=>数据库上运行
linux上运行：source /home/emp.sql
windows上运行：source d:/emp.sql
判断null：(is null 和 is not null)
查询没有上级领导的员工信息
select * from emp where mgr is null;
查询有上级领导的员工姓名
select ename from emp where mgr is not null;
关系运算符：>, <, >=, <=, =, !=和<>
查询员工工资小于等于3000的姓名和工资
select ename,sal from emp where sal <=3000;
查询工作不是程序员的员工姓名和工作
select ename,job from emp where job !='程序员'; select ename,job from emp where job <>'程序员';
逻辑运算符：and 和 or 类似java中的&& 和 ||
查询1号部门工资高于2000的员工信息

select * from emp where deptno =1 and sal >2000;

查询工作是人事或工资大于3000的员工姓名,工资,工作

select ename,sal,job from emp where job ='人事' or sal >3000;

between关键字：between x and y（查询x和y直接，包含x和y）
查询工资在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关键字：in(x,y,z)
查询员工工资为3000,1500,5000的员工姓名和工资
select ename,sal from emp where sal =3000 or sal =1500 or sal =5000;
select ename,sal from emp where sal in(3000,1500,5000);

数据查询-模糊查询

1.通配符%：代表0个或多个任意字符
2.通配符_：代表1个任意字符
 1.以x开头：'x%'	//单引号，百分号
 2.以x结尾：'%x'
 3.包含x：	'%x%'
 4.第二个字符是x：			'_x%'
 5.倒数第三个字符是x：		'%x__'
 6.以x开头倒数第二个是y： 	'x%y_'
查询姓孙的员工信息

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

查询工作中包含销售的员工姓名和工作

select ename,job from emp where job like '%销售%';

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

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

数据查询

排序查询：order by 字段名 asc/desc
  • 格式select 条件 from 表名 升序降序
  • 当查询某个东西的大体信息前面用*,后面用where查询+排序+查什么
  • 查询详细信息 select +要查什么+在哪里查+排序+根据什么查+倒序还是正序
查询员工姓名和工资按照工资降序排序

select ename,sal from emp order by sal desc;

查询1号部门的员工信息,按照工资升序排序

select * from emp where deptno =1 order by sal;

有时需依靠多个字段进行排序
格式：order by 字段1 asc/desc;
查询员工姓名,工资和部门编号,按照部门编号升序排序,如果部门编号一致则按照工资降序排序

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

分页查询
limit x,y => x跳过的页数 y请求的条数

select * from emp order by sal limit 0,5 分页查询写到最后面'

数据查询（数值计算和聚合函数）
数值计算：可在出现数据的位置进行计算，包括且不限于+-*/等运算符,使用非常灵活
查询每个员工的姓名,工资及年终奖(5个月的工资)

select ename,sal,5*sal 年终奖 from emp;

查询每个员工的姓名,工资及涨薪5块钱之后的工资

select ename,sal,sal+5 涨薪后 from emp;

聚合函数
1.求和-sum：查询2号部门的工资总和

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

2.计数-count：查询工资大于2000块钱的员工数

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

3.平均值-avg：查询2号部门的平均工资

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

查询销售的平均工资

select avg(sal) from emp where job ='销售';

4.最大值-max：查询员工表中工资最大值

select ename,max(sal) from emp;

查询1号部门的最大奖金comm是多少

select max(comm) from emp where deptno =1;

最小值-min：查询1号部门的最低工资

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

分组查询
1.group by关键字
在需求中出现每个或每种这样的关键字时使用分组查询
查询每个部门的平均工资

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

2.having 关键字
  • where后面只能写普通字段的条件不能写聚合函数的条件
  • having结合分组查询group by使用,在having后面写聚合函数的条件
查询每个部门的平均工资只查询平均工资大于2000的

select ename,avg(sal) as tt from emp group by deptno having tt >2000;

几个关键字的总结
使用查询语句时应注意这些关键字的含义和顺序
select 字段信息
from 表名
where 条件
group by 字段
having 聚合条件
order by 排序字段
limit 跳过几条,查询几条
子查询(嵌套查询)
1.定义：在一条sql语句中,出现表达式的位置又编写了另一条sql语句

就是一个sql语句中嵌套了另一个sql查询语句

2.为何需要：一般情况下适用于比较复杂的查询需求

如我们至今为止学习到的sql查询语句只能针对一张数据表，如果需要多张数据表就可能需要子查询

3.使用：可通过下面的查询需求，理解子查询的使用和意义：
查询工资高于1号部门平均工资的员工信息
select avg(sal) from emp where deptno =1
select * from emp where sal >(select avg(sal) from emp where deptno =1)
查询工资最高的员工信息
select max(sal) from emp; 
select * from emp where sal =(select max(sal) from emp);
查询工资高于2号部门最低工资的员工信息
select min(sal) from emp where deptno =2;
select * from emp where sal >(select min(sal) from emp where deptno =2);

关联查询

  1. 定义：同时查询多张表数据的查询
  2. 查询方式：等值连接,内连接,外连接
1.等值连接
  • 格式：select 字段信息 from A,B where 关联关系 and 其他条件
  • 关联查询必须写关联关系，若不写会得到2张表结果的乘积,这个乘积称为'笛卡尔积',这是一个错误的查询结果，工作中切记不要出现。
查询每个员工的姓名和对应的部门名

select e.ename,d.dname from emp e,dept d where e.deptno =d.deptno;

查询工资高于2000的员工姓名,工资和部门所在地

select e.ename,e.sal,d.loc from emp e,dept d where e.deptno =d.deptno and e.sal >2000;

2.内连接：查询结果跟等值连接查询的结果一样，建议使用内连接
  • 格式：select 字段信息 from A join B on 关联条件 where 其他条件
查询每个员工的姓名和对应的部门名

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

查询1号部门的员工的姓名,工作,部门名和部门所在地

select e.ename,e.job,d.dname,d.loc from emp e join dept d on e.deptno =d.deptno where e.deptno =1;

3.外连接：等值连接和内连接查询到的是2张表的交集数据
  • 外连接查询1张表的全部数据以及另外1张表的交集数据
  • 格式：select 字段信息 from A left/right join B on 关联条件 where 其他条件
查询所有部门名以及部门对应的员工姓名

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

查询所有员工姓名和对应的部门信息

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

4.关联查询总结

  1. 3种查询方式(等值连接，内连接,外连接)

  2. 若查询的是2张表的交集数据则使用等值连接或内连接(推荐)

  3. 若查询的是1张表的全部和另外1张表的交集数据则使用外连接

avatar
文章
阅读
粉丝