目录
1.数据库概述
1.1什么是数据库:
数据库是一个专业的存储和管理数据的仓库
1.2什么是关系型数据库
底层以二维表(就是个表格)的形式保存数据的库就是关系型数据库
非关系型数据库:
底层以键值对形式保存数据.
常见的关系型数据库:
Oracle:甲骨文公司提供,收费,适用于一些大型或者超大型的项目中,
之前在java中占比非常高
SQL Server:微软提供,收费,适用于一些大型或中性的项目中,java
占比不高(.NET中使用的较多)
Mysql:瑞典MySQLAB公司提供,免费,适用于一些小型或者中性的项目
中,如果做Mysql集群,也可以用于一些大型或者超大型系统
mariadb其实就是Mysql的一个分支,用法和Mysql一样
DB2:IBM公司提供,用于金融/银行系统较多
Sqlite:迷你数据库,用于嵌入式设备中
1.3:数据库服务器:
- 其实就是你安装的哪个mysql软件,将数据库软件安装在电脑上,哪么这台计算机就可以作为数据库服务器使用,可以实现数据的存和取,一个数据库服务器可以创建多个数据库
数据库:
就是存储数据的仓库,通常情况下一个网站(系统)中的所有数据会存放在一个数据库中
表:
数据库中的数据安装类型存放的,一类数据往往存储在一张表中一个数据库中可以创建多张表
表记录:
一张表中包含多行表记录,一张表中用于存储一类信息,一行表记录就用就用于存储某一个具体的数据
数据库中的表对应java中的类
表记录对应java中的对象
1.4:SQL语言
SQL语言是一门操作关系型数据库的通用的语言(学会了SQL可以操作所有的关系型数据库)
SQL语言可以操作的有:
- 查看库,创建库,删除库,修改库(了解)
- 创建表,删除表,修改表,查询表
- 新增表记录,删除表记录,修改表记录,查询表记录(数据)
- 存储过程/视图/索引等
1.5:如何连接mysql服务器(cmd窗口)
方式一:mysql -uroot -proot
mysql中默认有一个超级管理员(具有所有权限),用户名就是root
方式二:mysql -u用户名 -p
在下一行键入密码
方式三:mysql -u用户名 -p -h主机名或IP地址 -p端口
-h:后面跟 的是主机名或IP地址,如果不写-h,默认连接
localhost(127.0.0.1)
扩展内容:
-- 单行注释(--后面的空格必须要)
#单行注释
/**/多行注释
\c: 取消当前SQL语句
1.数据库及表操作
1.1.创建、删除、查看数据库
01.查看mysql服务器中所有数据库
SHOW DATABASES;
-- 02.进入某一数据库(进入数据库后才能操作库中的表和表记录)
-- 语法:USE 库名;
-- 查看已进入的库
select database();
-- 03.查看当前数据库中的所有表
-- 先进入某一个库,再查看当前库中的所有表
show tables;
-- 04.删除mydb1库
-- 语法:DROP DATABASE 库名;
drop database if exists 库名;(判断如果存在则删除,如果不存在
则不执行删除操作,也不会报错)
-- 思考:当删除的库不存在时,如何避免错误产生?
加if exists 判断,加在database与库名之间
-- 05.重新创建mydb1库,指定编码为utf8
-- 语法:CREATE DATABASE 库名 CHARSET 编码;
create database mydb1 charset utf8;
-- 需要注意的是,mysql中不支持横杠(-),所以utf-8要写成utf8;
-- 如果不存在则创建mydb1;
create database if not exists mydb1 charset utf8;
(如果不存在则创建mydb1,如果已存在,则不执行创建操作,也就不会报错)
-- 06.查看建库时的语句(并验证数据库库使用的编码)
-- 语法:SHOW CREATE DATABASE 库名;
show create database 库名;
1.2.创建、删除、查看表
-- 07.进入mydb1库,删除stu学生表(如果存在)
-- 语法:DROP TABLE 表名;
drop table if exists stu;
-- 08.创建stu学生表(编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]),
建表的语法:
CREATE TABLE 表名(
列名 数据类型,
列名 数据类型,
...
列名 数据类型
);
创建stu表的SQL语句如下:
create table stu(
id int,
name varchar(50),--50表示最多存50个字符
gender varchar(10),
birthday date,
score double
);
mysql中也有数据类型,这里先使用,后面再说明
-- 09.查看stu学生表结构
-- 语法:desc 表名
desc 表名 --查看表结构
2.新增、更新、删除表记录
10.往学生表(stu)中插入记录(数据)
select * from stu;--查看表数据
-- 语法:INSERT INTO 表名(列名1,列名2,列名3...) VALUES(值1,值2,值3...);
mysql中推荐使用单引号包裹字符串和日期值(有些数据库双引号包裹会报错)!
注意:如果是要给表中所有列插入值,列名可以省略不写,值必须和表中创建的列的
个数以及顺序一致
value后面只能跟一个括号一条记录,values后面可以跟多个括号多条记录
-- 如果是在cmd中执行插入记录的语句,先 set names gbk; 再插入记录!
创建 mysql库时要指定编码utf8,这样在库创建的表也为utf8编码
如果在cmd中插入记录的语句,先set names gbk;在插入 记录
一个cmd窗口只需要设置一次
-- 11.查询stu表所有学生的信息
-- 语法:SELECT 列名 | * FROM 表名
select * from stu;
-- 12.修改stu表中所有学生的成绩,加10分特长分
-- 修改语法: UPDATE 表名 SET 列=值,列=值,列=值...[WHERE子句];\
update stu set score=score+10;
mysql不支持复合运算符
-- 13.修改stu表中编号为1的学生成绩,将成绩改为83分。
提示:where子句用于对记录进行筛选过滤,保留符合条件的记录,将不符合条件的记录剔除。
-- 14.删除stu表中所有的记录
-- 删除记录语法: DELETE FROM 表名 [where子句]
delete from stu where id<2;--仅删除复合条件的记录
delete from stu ;--没有where默认删除所有记录
3.查询表记录
3.1.基础查询
z
-- 15.查询emp表中的所有员工,显示姓名,薪资,奖金
select * from 表名;--查询所有数据
select name ,sal, bonus, from emp;--查询指定列
-- 16.查询emp表中的所有部门和职位
select dept,job from emp;
select distinct dept,job from emp;--distinct用于去除重复
记录,只保留一行!
3.2.WHERE子句查询
-- 17.查询emp表中【薪资大于3000】的所有员工,显示员工姓名、薪资
select name,sal from emp where sal>3000;
-- 18.查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工,显示员工姓名、总薪资
select name,sal+bonus from emp where sal+bonus>3500;
null和任何数值运算都是null,这里应该将null看做零来处理
--方式一:将所有的奖金为null的更新为零
update stu set bonus=0 where bonus is null;
--方式二:在查询时,将null值看做零来处理(这种方式对表的数据不产生任何影响)
-- ifnull(列名, 值)函数: 判断指定的列是否包含null值,如果有null值,用第二个值替换null值
select name,sal+ifnull(bonus,0) from emp where sal+ifnull(bonus,0)>3500;
-- 注意查看上面查询结果中的表头,如何将表头中的 sal+bonus 修改为 "总薪资"
select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp where sal+ ifnull(bonus,0)>3500;
-- 使用`as`可以为表头指定别名(另外as可以省略)
-- 19.查询emp表中【薪资在3000和4500之间】的员工,显示员工姓名和薪资
select name,sal from emp where sal>3000 and sal<4500;
--也可以使用between 值1 and 值2 来完成,表示判断某个列的值是否在值1和
值2之间
and是并的意思(相当于java中的&),要求表中的记录要同时满足and两边的条件才
算满足
-- 20.查询emp表中【薪资为 1400、1600、1800】的员工,显示员工姓名和薪资
方式一:select name,sal from emp where sal=1400 or
sal=1600 sal=1800;
方式二:select name,sal from emp where sal in(1400,1600,1800);--意思是只要员工的薪资等于in括号里面的任何一个值就算满足条件
-- 21.查询薪资不为1400、1600、1800的员工,显示员工姓名和薪资
方式一:select name,sal from emp where not(sal=1400 or
sal=1600 sal=1800);
方式二:select name,sal from emp where sal not in(1400,1600,1800);
-- 22.查询emp表中薪资大于4000和薪资小于2000的员工,显示员工姓名、薪资。
select name,sal from emp where sal>4000 or sal<2000;
-- 23.查询emp表中薪资大于3000并且奖金小于600的员工,显示员工姓名、薪资、奖金。
--对null值进行处理select name,sal,ifnull(bonus,0) from emp where sal>3000 and ifnull(bonus,0)<600;
-- 24.查询没有部门的员工(即部门列为null值)
select * from emp where dept is null;
--判断一个列中的值是不是null值不能用等号
-- 思考:如何查询有部门的员工(即部门列不为null值)
方式一:select * from emp where not(dept is null);
方式二:select * from emp where dept is not null;
3.3.模糊查询(like)
select * from emp where name='王海涛';
%:是通配符 ,可以表示0个或多个任意字符
_:是通配符,只能表示1个任意字符
-- 25.查询emp表中姓名中以"刘"字开头的员工,显示员工姓名。
select name from emp where name like '刘%';
--查询姓名列中以'刘'开头的名字,%表示'刘'的后面可以是0个 或1个或任意多个字符
-- 26.查询emp表中姓名中包含"涛"字的员工,显示员工姓名。
select name from emp where name like '%涛%';
--%涛%可以匹配三种情况:
1)当第一个%匹配0个字符时,表示已涛开头
2)第二个%匹配0个字符时,表示已涛结尾
3)当前后两个%至少匹配一个字符,包含涛在中间的某个位置
-- 27.查询emp表中姓名以"刘"开头,并且姓名为两个字的员工,显示员工姓名。
select name from emp where name like '刘_';
--'刘_',可以匹配姓名中以'刘'开头,并且后面只能有一个字符
--'刘__',可以匹配姓名中以'刘'开头,并且后面只能有一个字符
3.4.多行函数查询
多行函数也叫做聚合函数(聚集函数 ),常见函数:
(多行函数会默认过滤null值,即不统计NULL值)
count(列名 或*):
count(列名):表示统计当前列的值有多少个
count(*):以行为单位,统计查询结果中有多少行
max(列名):表示当前这一列中所有值中的最大者
min(列名):表示当前这一列中所有值中的最小者
sum(列名):表示统计当前这一列中所有值的和(也就是说会将这一列中所有的值加在一起返回)
avg(列名):表示当前这一列中所有值的平均值(这一列中所有值的和/不是null值的个数)
-- 28.统计emp表中薪资大于3000的员工个数
select count(*) from emp where sal>3000;
--在进行统计时,不要添加额外的列,因为没有任何意义(这里的name只会将第一行的name显示出来)
-- 29.求emp表中的最高薪资
select max(sal) from emp;
--在进行统计时,不要添加额外的列,因为没有任何意义(这里的name只会将第一行的name显示出来)
-- 30.统计emp表中所有员工的薪资总和(不包含奖金)
select sum(sal) from emp;
-- 31.统计emp表员工的平均薪资(不包含奖金)
select avg(sal) from emp;
3.5.分组查询
语法:SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
-- 32.对emp表,按照部门对员工进行分组,查看分组后效果。
select * from emp group by dept;
--任何证明上面查询的结果是三组,而不是三条记录
--可以通过多行函数对分组后的数据进行统计,分成了几组就会统计出几个结果
select count(*),dept from emp group by dept;
--如果没有分组,在通过多行函数进行统计时,不要添加额外的列,因为没有任何意义(这里的name只会将第一行的name显示出来)
-- 33.对emp表按照职位进行分组,并统计每个职位的人数,显示职位和对应人数
select count(*),job from emp group by job;
-- 34.对emp表按照部门进行分组,求每个部门的最高薪资(不包含奖金),显示部门名称和最高薪资
select count(*),dept,max(sal) from emp group by dept;
3.6.排序查询
语法:SELECT 列名 FROM 表名 `ORDER BY 列名 [ASC|DESC]`
ASC(默认)升序,即从低到高;DESC 降序,即从高到低。
-- 35.对emp表中所有员工的薪资进行升序(从低到高)排序,显示员工姓名、薪资。
select sal,name from emp order by sal asc;
--默认就是asc(从低到高,升序),可以省略
-- 36.对emp表中所有员工的奖金进行降序(从高到低)排序,显示员工姓名、奖金。
select name,bonus from emp order by bonus desc;
--按照奖金相许排序,如果奖金相同,再按照薪资降序排序
select name,bonus,sal from emp order by bonus desc,
sal desc;
3.7.分页查询
在mysql中,通过limit进行分页查询,查询公式为:
`limit (页码-1)*每页显示记录数, 每页显示记录数`
-- 37.查询emp表中的所有记录,分页显示:每页显示3条记录,返回所有页的数据
--查询emp表中的记录,每页3条,查询第1页
select * from emp limit 0,3;
--查询emp表中的记录,每页3条,查询第2页
select * from emp limit 3,3;
-- 38.求emp表中薪资最高的前3名员工的信息,显示姓名和薪资
select name,sal from emp order by sal desc limit 0,3;
3.8.其他函数
curdate()--获取当前日期:年月日
curtime()--获取当前时间:时分秒
sysdate()/now()--获取当前日期加时间
year()--返回当前日期中的年份
month()--返回当前日期中的月份
day()--返回当前日期中的天数
hour()--返回时间中的小时
minute()--返回当前时间的分钟
second()--返回当前时间的秒数
concat_ws(值1,值2,值3)--将三个值拼接一起,并且每两两拼接时会通过x作为分隔符进行拼接,字符加引号,
select concat_ws(',','王海涛','1995-05-02',1520);
-- 39.查询emp表中所有【在1993和1995年之间出生】的员工,显示姓名、出生日期。
select name birthday from emp where birthday between
1993 and 1995;
--由于birthday是日期格式,而1993和1995是数值,没法比较
方式一:select name,birthday from emp where birthday between '1993-1-1' and '1995-12-31';
--将1993和1995两个数值转成日期格式,再和birthday比较.
方式二:将birthday中的年份用year函数提取出来,再和1993和1995进行比较
select name,birthday from emp where year(birthday) between 1993 and 1995;
-- 40.查询emp表中本月过生日的所有员工
--首先 他会month函数从当前日期中获取本月是几月:month(now())
--再通过month函数从员工的birthday中获取出生年月日:month(birthday)
select * from emp where month(now()) = month(birthday);
-- 41.查询emp表中员工的姓名和薪资(薪资格式为: xxx(元) )
select name,concat(sal,'(元)') from emp;
-- 补充练习:查询emp表中员工的姓名和薪资(薪资格式为: xxx/元 )
select name,concat_ws('/',sal,'元') from emp;
补充内容1:mysql的数据类型
数值类型:
tinyint--一个字节,相当于Java中的byte,小整数
smallint--2个字节,相当于Java中的short,大整数
int--4个字节,相当于Java中的int,大整数
bigint--8个字节,相当于Java中的long,极大整数
float--4个字节,相当于Java中的float,单精度浮点数值
double--8个字节,相当于Java中的double,双精度浮点数值
decimal--存储精确的小数值,用于在数据库存储精确的数值
用法: 列名 decimal(p,d);
p:是表示有效数的精度.范围为1~65.
d:是表示小数点后的位数,范围是0~30.mysql要求D小于或等于 p
字符串类型:
char--0~255字符,定长字符串,char(n)n的范围为0~255个字符
char类型之所以叫做定长字符串,是因为一旦确定了n的最大字符数,不管存的数据是多少,该数据占用的空间就是n个字符.如果有剩余空间,会用空格补齐,因此可能会浪费空间
varchar--0~65535字节,变长字符串,varchar(n),n的范围为:0~?字符
varchar类型之所以叫变长字符串,是因为n只是限制该列中最多能存的字符数,如果你实际存的数据量小于n,剩余的空间还可以留给别的数据使用,如果有剩余空间,会留给其他数据使用.
所以char类型适合存储长度固定的数据,相比较varchar类型速度要快一些,因为只需要判断一个数据是否能存入该列中,而不需将剩余的空间留给别的数据
范围与编码有关,编码不同,范围也不同
char与varchar有什么区别:
1)char和varchar存的数量不同,char类型最多能存255个字符,varchar类型最多能存65535个字节
2)char类型如果存的数据量小于最大长度,剩余空间会使用空格填充,因此可能会浪费空间,所以char类型适合 存储长度固定的数据,这样既不会浪费空间,效率还比varchar略高
3)varchar类型如果存的数据量小于最大长度,剩余的空间会留给别的数据使用,所以varchar类型适合存储长度不固定的数据,这样虽然没有char存储效率高,但至少不会浪费空间
mediumtext--0~16777215字节,约16M,大文本/长文本
mediumblob--0~16777215字节,约16M,二进制
日期类型:
date;日期类型,格式:年月日
time:时间类型,格式:时分秒
datetime:日期+时间,格式:年月日 时分秒
timestamp:时间截,格式和datetime相同,也是年月日 时分秒,和 datetime不同的是:
1)范围上:datetime范围是:1000~9999(年份)
timestamp范围是:1970~2038年
2)实际存的数据:datetime实际存的就是一个'年月日 时分秒'格式是日期+时间,而timestamp实际存储的是这个从1970年1月1日到这个日期+时间的时间毫秒值
3)在使用上:timestamp可以设置自动获取当前时间作为值插入到表中,而datetime不可以
补充内容2:mysql的字段约束
1)主键约束
如果一个列添加了主键约束,那么这个列的值就必须是非空且不能重复,主键通常用于唯一的表示一行表 记录(就像人的身份证号),一张表中,通常都会有且只有一个主键,可以设置为多个列(通常只设置一个列)
添加主键约束的格式:
create table stu(
id int primary key auto_increment,--给id添加主键且自增,设置自增必须要是数值类型
);
--上面
alter table stu modify id int primary key auto_increment;
--如果要删除主键及自增,先删除自增
alter table stu modify id int,--删除自增
alter table stu drop primary key,--删除主键
在设置为你自增之后,表中会维护一个auto_increment的值,这个值从1开始,如果插入主键时没有给主键赋值,就会从auto_increment这里获取一个值再作为主键插入到表中,再用完以后会自动加一
2)非空约束
如果一个列添加了非空约束后,那么这个列的值就不能为空(null),可以重复
create table stu(
gender varchar(10) not null,--不能为空,但可以重复
);
--如果建表时没有添加非空约束,也可以在建表之后再添加
alter table stu modify gender char(1) not null--添加非空约束,也可以改变类型
3)唯一约束
如果一个列添加了唯一约束,那么这个列的值就不能重复,但可以为空
create table stu(
email varchar(20) unique,--添加唯一约束
);
即添加非空约束又添加唯一约束格式:
create table stu(
username varchar(50) unique not null--即不能重复又不能为空
);
主键约束和(非空+唯一约束)有什么区别:
1)主键约束和(唯一+)特点是相同的,都是不能个为空和重复
2)主键约束除了非空且不能重复之外,还可以表示唯一一行记录,即作为表记录的唯一表识
补充内容3:mysql的外键约束
外键约束不同于主键,非空,唯一约束,外键约束是用于表示两张表的对应关系
3.1如何保存 部门和员工的对应关系:
可以在员工表中加一个列(比如:dept_id)用于保存部门的编号,就可以保存员工和部门的对应关系(可以将dept_id设置为外键,也可以不加)
3.2加外键与不加外键的区别:
1)如果不添加外键:
对于数据库来说,dept_id这个列就是一个普通的列,数据库也不会知道 dept与emp两张表存在任何关系,自然也不会帮我们去维护这层关系
假如,现在要删除某个部门,删除后就会造成emp表中的要删除这个部门的员工找不到部门,这个数据就会变成 冗余数据,这样会破坏数据库中数据的完整性和一致性
2)如果将dept_id添加为外键:
将dept_id这个列添加为外键就等同于通知数据库,部门表和员工表之间存在对应关系,dept_id列中的数据要参考部门的主键,数据库一旦知道部门和员工表之间存在关系,就会帮我们维护这层关系
添加外键格式:
foreign key(dept_id) references dept(id)
4.表关系:
1对多(多对1):在这种关系中,往往会在多的一方添加列,保存一的一方的主键(可以设置外键,也可以不设,看需求)
1对1:在这种关系中,在任意一方添加列保存另一方的主键(可以设置外键,也可以不设,看需求)
多对多:在任何一方添加列保存另一方的主键都不合适,此时可以再创建一张表,在这张表中分别添加两个列(stuid,teaid),分别用于保存学生表的主键和教师表的主键,以此来保存学生和教师的对应关系
4.多表查询
4.1.连接查询
-- 42.查询部门和部门对应的员工信息
select * from dept,emp;
以上查询有一个名字叫做笛卡尔积查询
笛卡尔积查询:其实就是同时查询两种表,其中一张表有m条记录,另外一张表有n条记录,查询的结果是m*n条,但这种查询结果中包含了大量错误信息,所以我们一般不会直接 使用这种查询
select * from dept,emp where dept.id=emp.dept_id;
内连接查询:select * from dept inner join emp on dept.id=emp.dept_id;--查询结果与上面一样
4.2.连接查询
-- 43.查询【所有部门】及部门对应的员工,如果某个部门下没有员工,员工显示为null
如果两张表在连接查询时,要求查询出其中一张表的所有数据,此时可以使用左外连接查询或者右外连接查询
select * from dept left join emp on dept.id=emp.dept_id;--左外连接查询
--left,左连接查询
--right,右连接查询
--如果要查询部门表中的所有数据,而部门表在左边,那么此时可以使用左外连接查询,就可以查询相互所有的部门信息(而员工信息只显示和部门对应点)
【左外连接查询】:可以将左边表中的所有记录都查询出来,右边表只显示和左边相对应的数据,如果左边表中某些记录在右边没有对应的数据,右边显示为null即可。
-- 44.查询【所有员工】及员工所属部门,如果某个员工没有所属部门,部门显示为null
select * from dept right join emp on dept.id=emp.dept_id;
【右外连接查询】:可以将右边表中的所有记录都查询出来,左边表只显示和右边相对应的数据,如果右边表中某些记录在左边没有对应的数据,可以显示为null。
--查询所有部门以及员工,如果部门没有对应员工,可以显示为null,如果员工没有对应的部门,也可以对应null,这种情况使用全外连接
可以使用全外连接查询,但mysql不支持全外连接查询,但可以通过union来模拟这种查询,union关键字是用于将两个查询结果上下合并,并且去除重复记录,
union all关键字是用于将两个查询结果上下合并在仪器显示,不会去除重复,能使用这两个语句必须符合:
1)两条SQL语句查询结果列数必须相同
2)两条SQL语句查询的结果列名必须相同(低版本mysql要求)
select * from dept right join emp on dept.id=emp.dept_id;
union all
select * from dept left join emp on dept.id=emp.dept_id;
4.3.子查询练习
45.列出薪资比'王海涛'的薪资高的所有员工,显示姓名、薪资
select name,sal from emp where sal > (select sal from emp where name='王海涛');
--将SQL语句的执行结果作为另一条SQL语句的条件来执行,这就是子查询
-- 46.列出与'刘沛霞'从事相同职位的所有员工,显示姓名、职位。
select name,job from emp where job = (select job from emp where name = '刘沛霞');
4.4.多表查询练习
-- 47.列出在'培优部'任职的员工,假定不知道'培优部'的部门编号,显示部门名称,员工名称。
select dept.name,emp.name from emp,dept where dept.name='培优部' and dept.id=dept_id;
-- 48.(自查询)列出所有员工及其直接上级,显示员工姓名、上级编号,上级姓名
emp e1(员工表) emp e2(上级表)
查询的列:select e1.name,e2.id,e2.name
查询的表:from emp e1, emp e2
筛选条件:where e1.topid=e2.id
--select e1.name,e2.id,e2.name from emp e1, emp e2 where e1.topid=e2.id;
-- 49.列出最低薪资大于1500的各种职位,显示职位和该职位的最低薪资
select job,min(sal) from emp group by job having min(sal)>1500;
不能用where的原因:
1)where应该放在from子句后,group by子句前面
2)where中不能使用多行函数(列别名也不能用在where中)
3)where是在分组前执行,先过滤掉一些记录,再基于 剩余的记录进行分组,而本题 是先分组,再过滤,所以不能使用where,应该使用having.
where与having的区别:
1)where哈having都是用于对表中的记录进行筛选过滤
2)where用于在分组之前对记录进行筛选过滤,而having用于对分组之后进行筛选过滤
3)where子句中不能使用多行函数 和 列别名,但可以使用表别名
select name as 姓名,sal as 薪资 from emp e;
--其中上面的'姓名',;'薪资'都是列别名,e是表别名
4)having子句中可以使用断行函数 和 列别名 以及 表别名
-- 50.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量,平均薪资。
select dept_id 部门编号,count(*) 员工人数,avg(sal) 平均薪资 from emp group by dept_id;
select dept.name 部门名称,count(*) 员工人数,avg(sal) 平均薪资 from emp,dept group by dept_id;
-- 51.列出受雇日期早于直接上级的所有员工,显示员工编号、员工姓名、部门名称。
emp e1(员工表), emp e2(上级表)
查询的列:select e1.id,e1.name,d.name,e2.id,e2.name
查询的表:from emp e1, emp e2,dept d
连接条件:where e1.topid=e2.id and d.id=e1.dept_id
筛选条件:and e1.hdate < e2.hdate
select e1.id,e1.name,d.name,e2.id,e2.name
from emp e1, emp e2,dept d
where e1.topid=e2.id and d.id=e1.dept_id
and e1.hdate < e2.hdate;
1.索引
- 索引是一种排好序的快速查找的数据结构,他帮助数据库高效的进行数据的检索.在数据之外,数据库系统还维护着满足特定查找算法的数据结构(额外的存储空间),这些数据结构以某种方式指向数据,这样就可以在这些结构上实现高效的查找算法
1. 2.索引分类:
1)单值索引:一个索引只包括一个列,一个表可以有多个列
2)唯一索引:索引列的值必须唯一,但允许有空值;主键会自动创建唯一索引
3)复合索引:一个索引同时包括多列
1.3.索引语法:
查看索引:主键自动创建索引 SHOW INDEX FROM (表名)
普通索引:create index (定义名称) on 表名(列名)
唯一索引:alter table (表名) unique(列名)
复合索引:alter table (表名) add index (定义名称)(多个列名)
复合索引使用时:使用时遵循最左特性: 优先使用最左边
复合唯一索引:alter table (表名) add unique (定义名称)(列名)
删除索引:alter table (表名) drop index cardno
explain:解释SQL的执行过程,主要是看有没有使用索引(key是否有值)
1.4.索引优点:
1)提高查询效率
缺点:
1)索引不适合有太多,占用内存,太多有可能也不会提高效率
1.5.视图:
视图本质就是一个查询,和我们自己查询的区别是,它执行完会有缓存,下次查询就直接使用。但其也因为事先缓存,无法做优化,大型项目中禁止使用。
注意:视图只需创建一次,后面就可以类似表来使用,只是用来查询不能更新和删除
语法: create view (视图名) as (SQL语句)
使用视图语法: select * from (视图名)
优点:优化了SQL,复杂的SQL语句只写一次,结果就会缓存在视图中,下次查视图就可以了
缺点:数据都是重复的,占内存,无法优化
补充内容3set names gbk的作用:
用在cmd窗口中,用来通知数据库服务器,当前cmd窗口发送给 服务器的数据是GBK的,那么服务器就会按照GBK编码来接受,cmd窗口发送过来的数据,再将GBK的数据转换成utf8编码是数据存入数据库
这个命令只能用在cmd窗口!而且每次重新开一个cmd窗口都需要重新设置一次
像Navicat/SQLYoga等工具底层已经设置过编码了
补充内容4.数据库的备份和恢复:
备份数据库:
1)备份单个数据库:
在cmd窗口(未登录,未连接到mysql服务器的界面)中,可以通过以下命令对指定的数据库进行备份
mysqldunp -u用户名 -p密码 库名 > 备份文件的位置
示例1:对db40中的数据(表,标记录)进行备份 ,备份到d:/db40.SQL
mysqldump -uroot -proot db40 > d:/db40.sql
备份单个数据库,其实只会备份这个库中的表和表记录,并不会备份库本身
2)备份多个数据库:
在cmd窗口(未登录,未连接到mysql服务器的界面)中
mysqldump -u用户名 -p密码 --databases 库名1 库名 2 ... > 备份文件的位置
示例2:对db20和db40库中的数据进行备份,备份到d:/db2040. sql
mysqldump -uroot -proot --databases db20 db40 > d:/db2040.sql
备份多个数据库,不仅会备份这个库中表和表记录,还会备份库本身
如果想备份mysql服务器中的所有库以及库中的表和表记录 ,可以通过如下命令:
mysqldump -u用户名 -p密码 -A > d:/dball.sql
--ERROR是错误,警告不是错误可以忽略
恢复数据库:
1)恢复数据库方式一(单个数据库):
在cmd窗口中(未登录的状态下),可以通过如下命令对指定的数据库进行恢复
mysql -u用户名 -p密码 库名 < 备份文件的位置
示例1:将d:/db40.sql 文件中的数据恢复到 db60 库中
1)先在cmd窗口中(在已登录的状态下),创建一个db60库
create database db60 charset utf8;
2)再回到cmd窗口中(未登录的状态中),执行下面恢复的命令
mysql -uroot -proot db60 < d:/db40.sql
2)恢复数据库方式二(多个数据库):
在cmd窗口中(已登录的状态下),可以通过source命令来执行指定位置的sql文件中的sql语句:
source sql文件的位置
示例2:将 d:/db40.sql 文件中的数据恢复到 db80 库中
1)先创建db80库,并进入到db80库
create database db80 charset utf8;
2)再通过source命令执行 d:/db40.sql 文件中的sql语句
source d:/db40.sql
示例3:将d:/db2040.sql 文件中的数据恢复回来
1)将db20,db40库删除(模拟数据丢失)
drop database db20;
drop database db40;
2)再通过source命令执行d:/db2040.sql 文件中的sql语句
source d:/db2040.sql