SQL个人学习

于 2020-10-15 11:32:13 发布
阅读量1.2k 收藏 2
点赞数
分类专栏： SQL个人学习 文章标签： mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Felix_Peng/article/details/109068762
版权

1 数据库概述

所谓的数据库就是指存储和管理数据的仓库

分类

早期: 层次式数据库、网络型数据库
现在：关系型数据库、非关系型数据库

关系型数据库

底层以二维表的形式保存数据的库就是关系型数据库
常见的关系型数据库

  • Sql Server：微软提供，收费，适用于一些中型或大型的项目中，在java中的使用占比不高（.NET中使用的较多）
  • Oracle：甲骨文公司提供，收费，适用于一些大型或者超大型的项目中，在java中的使用占比非常高
  • mysql：瑞典MySQLAB公司提供，免费开源，适用于一些小型或者中型的项目中，在Java中的使用占比较高（小巧轻量）
    mariadb其实就是MySQL的一个分支，用法和MySQL完全一样。
  • DB2：IBM公司提供，收费，在一些银行、金融等行业中使用较多。在java中的使用占比也不高。
  • Sqlite：迷你数据库，嵌入式设备中（安卓、苹果手机、pad）

数据库服务器

数据库服务器就是一个软件（比如mysql软件）将数据库软件安装在电脑上，当前电脑就是一个数据库服务器。就可以对外提供存取数据的服务

SQL语言

SQL是一门用于操作关系型数据库的通用的语言（使用SQL可以操作所有的关系型数据库）
(1)创建数据库、删除数据库、修改数据库、查询数据库
(2)创建表、删除表、修改表、查询表
(3)新增表记录、删除表记录、修改表记录、查询表记录

连接mysql服务器

mysql -uroot -p密码

**-u：**后面的root是用户名，这里使用的是超级管理员root；
**-p：(小写的p)**后面的是密码，这是在安装MySQL时就已经指定的密码；

mysql -uroot -proot -h127.0.0.1 -P3306

**-h：**后面给出的127.0.0.1是服务器主机名或ip地址，可以省略的，默认连接本机；

**-P：(大写的P)**后面的3306是连接端口，可以省略，默认连接3306端口；

退出客户端命令

quit或exit或**\q**

常见问题

在这里插入图片描述
**解决方法：**复制mysql安装目录下的bin目录的路径，将bin目录的路径添加到path环境变量中

2.SQL语句

创建、删除、查看数据库

  • SQL语句对大小写不敏感。推荐关键字使用大写，自定义的名称（库名，表名，列名等）使用小写
  • 在自定义名称时，针对多个单词不要使用驼峰命名，而是使用下划线连接。(例如：tab_name，而不是 tabName )
SHOW DATABASES; -- 查看当前数据库服务器中的所有库
CREATE DATABASE mydb1; -- 创建mydb1库

1.创建数据库并设置编码

create database 库名 charset utf8;

2.如果不存在则创建mydb1

create database if not exists mydb1 charset utf8;

3.查看mysql服务器中所有数据库

show databases;

4.进入使用某一数据库

use 库名;

5.查看已进入的库

select database();

6.查看当前数据库中的所有表

show tables;

7.删除数据库

drop database 库名;

drop database if exists 库名; -- 避免删除的库不存在产生错误

8.查看建库时的语句（并验证数据库库使用的编码）

show create database mydb1;

创建、删除、查看表

1.删除stu学生表(如果存在)

drop table stu;

drop table if exists stu;-- 如果存在,则删除stu表

2.创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]）

CREATE TABLE 表名(
	列名 数据类型,
	列名 数据类型,
	...
  	列名 数据类型
);
create table stu(
	id int,
   	name varchar(30),
    gender varchar(10),
    birthday date,
    score double
);

3.查看stu学生表结构

desc stu;

4.修改表操作
alter、modify、add、drop

alter table stu add score double; -- 往stu表中添加score列，double类型
alter table stu modify id int primary key; -- 修改id列，将id设置为主键
alter table stu modify id int auto_increment; -- 将id主键设置为自动增长
alter table stu drop score; -- 删除stu表中的score列
alter table emp drop foreign key emp_ibfk_1; -- 删除外键，emp_ibfk_1是在创建表时，数据库为外键约束指定的一个名字

新增、更新、删除表记录

1.往学生表(stu)中插入记录(数据)

set names gbk;-- 如果是在cmd中执行插入记录的语句,先 set names gbk; 再插入记录!
create table stu(
	id int primary key auto_increment,-- id设置了主键自增,插入记录不用赋值
   	name varchar(30),
    gender varchar(10) not null,
    birthday date,
    score double
);
insert into stu(id,name,gender,birthday,score) value(null,'tom','man','2014-5-5',85.5);
insert into stu value(null,'felix','woman','2000-1-1',90);-- 默认给所有的列赋值
insert into stu value(null,'jerry','man','1996-6-6',55);
-- 查询学生表中的所有记录
select * from stu;

提示：(1)当为所有列插入值时，可以省写列名，但值的个数和顺序必须和声明时列的个数和顺序保持一致！
(2)SQL语句中的值为字符串或日期时，值的两边要加上单引号（有的版本的数据库双引号也可以，但推荐使用单引号）。
(3)(针对cmd窗口)在插入数据之前，先设置编码：set names gbk;
或者用以下命令连接mysql服务器：
mysql --default-character-set=gbk -uroot -proot
等价于：
mysql -uroot -proot
set names gbk;

2.查询stu表所有学生的信息

  • SELECT 列名 | * FROM 表名
select * from stu;
select id from stu;

3.修改stu表记录
-UPDATE 表名 SET 列=值,列=值,列=值…[WHERE子句];
修改stu表中所有学生的成绩，加10分特长分

update stu set score=score+10;

修改stu表中编号为1的学生成绩，将成绩改为83

update stu set score=83 where id=1;

4.删除stu表记录

  • DELETE FROM 表名 [where子句]

删除stu表中所有的记录

delete from stu;

仅删除符合条件

delete from stu where score<70;

查询表记录

基础查询

  • SELECT 列名称 | * FROM 表名

1.查询emp表中的所有员工，显示姓名，薪资，奖金

select name,sal,bonus from emp;

2.查询的结果中，存在大量重复的记录，剔除重复记录DISTINCT

select distinct dept,job from emp;

WHERE子句查询

  • SELECT 列名称 | * FROM 表名称 WHERE 列 运算符 值

运算符
在这里插入图片描述
1.查询emp表中【薪资大于3000】的所有员工，显示员工姓名、薪资

select name,sal from emp where sal>3000;

2.查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工，显示员工姓名、总薪资

select name,sal+bonus from emp where sal+bonus>3500;

3.ifnull(列名,值)函数判断指定的列是否包含null值，如果有用第二个值替换null值

select name,sal+ifnull(bonus,0) from emp where sal+ifnull(bonus,0)>3500;

4.as为表和列指定别名，as可以省略

select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp where sal+ifnull(bonus,0)>3500;

或

select name 姓名,sal+ifnull(bonus,0) 总薪资 from emp where sal+ifnull(bonus,0)>3500;

5.查询emp表中【薪资在3000和4500之间】的员工

  • 方式一：
select name,sal from emp where sal>=3000 and sal<=4500;
  • 方式二：between…and…
select name,sal from emp where sal between 3000 and 4500;-- 包括3000和4500

6.查询emp表中【薪资为 1400、1600、1800】的员工

  • 方式一：
select name,sal from emp where sal=1400 or sal=1600 or sal=1800;
  • 方式二： in
select name,sal from emp where sal in(1400,1600,1800);

7.查询薪资不为1400、1600、1800的员工

select name,sal from emp 
where not(sal=1400 or sal=1600 or sal=1800);-- not取反
-- 或者
select name,sal from emp where sal not in(1400,1600,1800);

8.查询没有部门的员工（即部门列为null值）

select * from emp where dept is null;

9.查询有部门的员工（即部门列不为null值）

select * from emp where dept is not null;

模糊查询

  • SELECT 列 | * FROM 表名 WHERE 列名 LIKE 值

可以和通配符（%、）配合使用，其中**"%“表示0或多个任意的字符,”"表示一个任意的字符**

1.查询emp表中姓名中以"刘"字开头的员工

select name from emp where name like '刘%';

2.查询emp表中姓名中包含"涛"字的员工

select name from emp where name like '%涛%';

3.查询emp表中姓名以"刘"开头，并且姓名为两个字的员工

select name from emp where name like '刘_';

多行函数查询

多行函数也叫做聚合(聚集)函数，根据某一列或所有列进行统计
常见的多行函数：
在这里插入图片描述
提示：(1)多行函数不能用在where子句中
(2)多行函数和是否分组有关，分组与否会直接影响多行函数的执行结果不分组只有一个，分多少组产生多少个结果
(3)多行函数在统计时会对null值进行过滤，直接将null值丢弃，不参与统计

1.统计emp表中薪资大于3000的员工个数

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

2.求emp表中的最高和最低薪资

select max(sal) from emp;
select min(sal) from emp;

3.统计emp表中所有员工的薪资总和

select sum(sal) from emp;-- sal薪资总和,对null值进行过滤，直接将null值丢弃，不参与统计

4.统计emp表员工的平均薪资

select avg(sal) from emp;

分组查询

GROUP BY 语句根据一个或多个列对结果集进行分组

  • SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列

1.对emp表，按照部门对员工进行分组，查看分组后效果

select name,dept from emp group by dept; #显示每组的第一个内容
select name,count(*) from emp group by dept;/*会统计出多个结果*/

2.对emp表按照职位进行分组，并统计每个职位的人数，显示职位和对应人数

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

3.对emp表按照部门进行分组，求每个部门的最高薪资，显示部门名称和最高薪资

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

排序查询

ORDER BY 子句将结果集中记录根据指定的列排序后再返回

  • SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC]

1.ASC(默认)升序，即从低到高

select name,sal from emp order by sal;-- 默认升序,asc可省略
select name,sal from emp order by sal asc;-- 默认升序,asc可省略

2.DESC 降序，即从高到低

select name,bonus from emp order by bonus desc;

分页查询

通过limit进行分页查询，公式为：
limit (页码-1)*每页显示记录数, 每页显示记录数
1.查询emp表中的所有记录，分页显示：每页显示3条记录，返回所有页的数据

select * from emp limit 0,3;-- 每页显示3条,查询第1页数据
select * from emp limit 3,3;-- 每页显示3条,查询第2页数据
select * from emp limit 6,3;-- 每页显示3条,查询第3页数据
select * from emp limit 9,3;-- 每页显示3条,查询第4页数据

2.求emp表中薪资最高的前3名员工的信息，显示姓名和薪资

select name,sal from emp order by sal desc limit 0,3;

其他函数

在这里插入图片描述
1.查询emp表中所有【在1993和1995年之间出生】的员工

  • 方式一：
select name,birthday from emp where birthday>='1993-1-1' and birthday<='1995-12-31';
  • 方式二：
select name,birthday from emp where year(birthday)>=1993 and year(birthday)<=1995;

2.查询emp表中本月过生日的所有员工

select * from emp where month(birthday)=month(now());

3.查询emp表中员工的姓名和薪资（薪资格式为: xxx(元) ）

select name,concat(sal,'(元)') from emp;

4.查询emp表中员工的姓名和薪资（薪资格式为: xxx/元 ）

select name,concat(sal,'/元') from emp; 
select name,concat_ws('/',sal,'元') from emp;-- 用concat_实现

mysql的数据类型

数值类型

tinyint：占用1个字节，相对于java中的byte
smallint：占用2个字节，相对于java中的short
int：占用4个字节，相对于java中的int
bigint：占用8个字节，相对于java中的long
其次是浮点类型即：float和double类型
float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double

字符串类型

  • char(n) 定长字符串，最长255个字符。n表示字符数
    当插入的数据的长度小于指定的长度时，剩余的空间会用空格填充（浪费空间）
    char类型往往用于存储长度固定的数据，存储效率略高\
  • varchar(n) 变长字符串，最长不超过65535个字节，n表示字符数
    当插入的数据的长度小于指定的长度时，剩余的空间可以留给别的数据使用。（节省空间）
    总结：长度固定的数据，用char类型，这样不会浪费空间，效率也比较高
    如果长度不固定，使用varchar类型，这样不会浪费空间
  • text text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储

日期类型

date：年月日
time：时分秒
datetime：年月日 时分秒
timestamp：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同。两者的区别是：

  • timestamp最大表示2038年

mysql的字段约束

主键约束

  • 唯一且不能为空
    添加主键约束
create table stu(
	id int primary key,
	...
);
  • 主键设置为自增
create table stu(
	id int primary key auto_increment,
	...
);

非空约束

create table user(
	password varchar(50) not null,
	...
);

唯一约束

create table user(
	username varchar(50) unique not null,
	...
);

外键约束

  • 外键其实就是用于通知数据库两张表数据之间对应关系的这样一个列
create table emp(
	id int,
	name varchar(50),
	dept_id int,
	foreign key(dept_id) references dept(id)
);
  • 添加外键方式二（建表后）
alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);

其中 fk_dept_id (名字由自己定义)，是指外键约束名称，也可以将【constraint fk_dept_id】省略，MySQL会自动分配一个外键名称，将来可以通过该名称删除外键。

  • 添加外键约束(多对多)
create table stu_tea(
    stu_id int, -- 学生编号
    tea_id int, -- 教师编号
    primary key(stu_id,tea_id), -- 设置联合主键
    foreign key(stu_id) references stu(stu_id), -- 添加外键
    foreign key(tea_id) references tea(tea_id) -- 添加外键
);

其中为了防止重复数据，将stu_id和tea_id设置为联合主键。

在这里插入图片描述

多表查询

连接查询

1.笛卡尔积查询：所谓笛卡尔积查询就是指，查询两张表，其中一张表有m条记录，另一张表有n条记录，查询的结果是m*n条

select * from dept,emp;

2.where子句将错误数据剔除

select * from dept,emp
where emp.dept_id=dept.id;-- dept.id表示dept这个表里的id

3.内连接查询

select * from dept inner join emp
on emp.dept_id=dept.id;-- 内连接查询, 和上面的查询结果一样

左连外接查询

可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可
1.查询【所有部门】及部门对应的员工，如果某个部门下没有员工，员工显示为null

select * from dept left join emp on emp.dept_id=dept.id;

在这里插入图片描述

右外连接查询

select * from dept right join emp on dept.id=emp.dept_id;

拓展

  • 可以使用union将左外连接查询的结果和右外连接查询的结果合并在一起，并去除重复的记录
select * from dept left join emp on emp.dept_id=dept.id
union
select * from dept right join emp on dept.id=emp.dept_id;

子查询

列出薪资比’王海’的薪资高的所有员工

select name,sal from emp where sal>(select sal from emp where name='王海');

列出与’刘一’从事相同职位的所有员工

select name,job from emp where job=(select job from emp where name='刘一');

多表查询案例

1.列出在’人事部’任职的员工，假定不知道’人事部’的部门编号

select dept.name,emp.name from emp,dept 
where emp.dept_id=dept.id 
and dept.name='人事部';
-- 扩展:给表名加别名,加了别名后就需要使用别名替换表名
select d.name,e.name from dept as d,emp as e-- as也可以省略 
where e.dept_id=d.id 
and d.name='人事部';-- 所有的表名全部换成别名

2.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名

/*将emp同时看做员工表和上级表
emp e1 看做员工表  emp e2 看做上级表
查询的表: emp e1 , emp e2 
查询的列: e1.name(员工姓名)  e2.id(上级编号) e2.name(上级姓名)
查看条件:  员工所属上级编号=上级员工编号
 			e1.topid=e2.id	
*/
select e1.name,e2.id,e2.name from emp e1,emp e2
where e1.topid=e2.id;

3.列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资

-- 根据职位进行分组,再求出每个职位的最低薪资
select job,min(sal) from emp group by job;
-- 列出最低薪资大于1500的各种职位
select job,min(sal) from emp group by job
having min(sal)>1500;-- 筛选过滤数据,where和having的区别
  • where和having子句的区别:
    (1)相同点: where和having都可以对记录进行筛选过滤。
    (2)区别：where是在分组之前,对记录进行筛选过滤,并且where子句中不能使用多行函数以及列别名(但是可以使用表别名)
    (3)区别：having是在分组之后,对记录进行筛选过滤,并且having子句中可以使用多行函数以及列别名、表别名。

4.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资

select dept_id,count(*),avg(sal) from emp group by dept_id;

5.查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数

select dept.id,dept.name,dept.loc,count(*) 
from dept,emp
where dept.id=emp.dept_id
group by dept.name
having count(*)>0;

6.列出受雇日期早于直接上级的所有员工，显示员工编号、员工姓名、部门名称

/*
查询的表:emp e1 , emp e2 ,dept d
查询的列:e1.id,e1.name,d.name
连接条件:e1.topid=e2.id
		e1.dept_id=d.id
筛选条件:e1.hdate<e2.hdate
*/
select e1.id,e1.name,d.name
from emp e1,emp e2,dept d
where e1.topid=e2.id
	and e1.dept_id=d.id
	and e1.hdate<e2.hdate;

数据库备份与恢复

备份数据库

mysqldump -u用户名 -p 数据库的名字 > 备份文件的位置

  • 示例对db40库中的数据(表，表记录)进行备份，备份到 d:/db40.sql文件中
mysqldump -uroot -p db40 > d:/db40.sql

键入密码

mysql服务器中所有的数据库进行备份，备份到 d:/all.sql文件中

mysqldump -uroot -p --all-database > d:/all.sql

键入密码

恢复数据库

  • 方式一
    mysql -u用户名 -p 数据库的名字 < 备份文件的位置

在cmd窗口中（已登录的状态下），先创建db60库

create database db60 charset utf8;

在cmd窗口中（未登录的状态下）

mysql -uroot -p db60 < d:/db40.sql
  • 方式二
    source sql文件的位置

在cmd窗口中（已登录的状态下），先创建db80库，进入db80库

create database db80 charset utf8;
use db80;

再通过source执行指定位置下的sql文件

source d:/db40.sql

级联更新、级联删除

create table emp(
    id int primary key auto_increment, -- 员工编号
    name varchar(20), -- 员工姓名
    dept_id int, -- 部门编号
    foreign key(dept_id) references dept(id) -- 指定外键
    on update cascade -- 级联更新
    on delete cascade -- 级联删除
);

SQL语句的书写顺序

select * | 列名 -- 确定要查询的列有哪些
from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤，剔除不符合条件的记录
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount -- 指定返回第几页记录以及每页显示多少条

SQL语句的执行顺序

from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤，剔除不符合条件的记录
select * | 列名 列别名 -- 确定要查询的列有哪些，
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值