当前位置:   article > 正文

数据库(SQL)_sql数据库

sql数据库

数据库的基本概念

1.数据库的英文单词:DataBase 简称:DB
2.什么是数据库?
用于存储和管理数据的长仓库。
3.数据库的特点:
     1.持久化存储数据的,其实数据库就是一个文件系统
     2.方便存储和管理数据
     3.使用了同一的方式操作数据库—SQL
4.常见的数据库软件:例如:Oarcle和MySQL

MySQL数据库软件
开元免费的数据库,小型的数据库,已经被Oracle收购了MySQL6.x版本也开始收费

MySQL服务启动和关闭

打开服务器页面:
方法一:右键点击电脑,点开管理
方法二:在cmd控制台输入services.msc

在cmd控制台打开和关闭服务器:(控制台默认编码是gbk,数据是u8)
net start mysql:启动mysql的服务
net stop mysql:关闭mysql服务
如果想要在cmd控制台操作管理,直接操作(会报错):
在这里插入图片描述

需要以管理员的身份进入cmd:

在这里插入图片描述

MySQL的登录和退出

登录
1.mysql -uroot -p密码
2.mysql -hip -uroot -p连接目标的密码
3.mysql --host=ip --user=root --password=连接目标的密码
退出
1.exit
2.quit

通配符:
所有权限:all
所有表或者所有库:*
所有:所有主机

SQL

1.什么是SQL?
stryyctyred Query Language:结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”

SQL通用语法

1.SQL语句可以单行或多行书写,以分号结尾。(在控制台需要;结尾,回车如果有分号表示结束,没有则下一行。SQLyog上面可以不加,因为它是选中执行)
2.可使用空格和缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
4.3种注释:
       1.单行注释:
              (1)–(空格)注释内容
       如果不加分号可以还是可以运行啊?
       但是没有结束,还需要让你继续运行,然后你再输入一个空格就错误了。
               (2)#注释内容(mysql特有)这个#后面可以不用加空格。
       2.多行注释:
       2.多行注释:/* 注释 */(和java里面的注释一样)
在这里插入图片描述

SQL分类

1.DDL:数据定义语言
    用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等
2.DML数据操作语言
    用来对数据库中标的数据进行增删改。关键字:insert,delete,update等
3.DQL数据查询语言
    用来查询数据库中表的记录(数据)。关键字:select,where等
4.DCL:数据控制语言(了解)
    用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT,REVOKE等
在这里插入图片描述

DDL:操作数据库、表

1.操作数据库(database):CRUD
     1.C(Create):创建
    创建数据库:create database 数据库名称;
    如果再创建已经存在的数据库?那么就要报错。
    所以要创建数据库前判断有没有该数据库,有不创建,没有不创建:create database if not exists 数据库名称;
    指定字符集:create database 数据库名称 character set 字符集名称(例如:gbk)
    创建db4数据库,判断是否存在,并指定字符集为gbk:create database if not exists db4 character set gbk;
     2.R(Retrieve):查询
    查询所有数据库的名称:show databases;
    查询某个数据库的字符集(查询某个数据库的创建语句)(下载mysql的时候默认的是utf8):show create database 数据库名称;
     3.U(Update):修改
    修改数据库的字符集:alter database 数据库名称 character set 字符集名称;
     4.D(Delete):删除
    删除数据库:drop database 数据库名称;
    判断数据库存在,存在再删除:drop database if exists 数据库名称;
     5.使用数据库
    查询当前正在使用的数据库名称:select database();
    使用数据库:use 数据库名称;

2.操作(table)
     1.C(Create):创建
(1).语法:create table 表名

create table 表名(
	列名1 数据类型1,
	列名2 数据类型2,
	...
	列名n 数据类型n
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

数据类型:
a.  int :整数类型,例如:age int,

b.  double:
score double(5,2),(5表示这个数一共有5位,2表示这个数小数点后有2两位)

c.  date:日期,只包含年月日
yyyy-MM-dd

d.  datetime:日期,包含年月日时分秒:
yyyy-MM-dd HH:mm:ss

e.  timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss

f.  varchar:字符串
name varchar(20):姓名最大20个字符
zhangsan  8个字符
张三  2个字符

create table student(
	id int,
	name varchar(32),
	age int,
	score double(4,1),
	birthday date,
	insert_time timestamp
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

在这里插入图片描述

     2.R(Retrieve):查询(和数据库大同小异)
    查询某个数据库中所有的表名称:show tables;
    查询表结构:desc 表名;
    查询某个表的字符集(查询某个表的创建语句)(下载mysql的时候默认的是utf8):show create table 表名;

     3.U(Update):修改
1.修改表名:alter table 表名 rename to 新的表名;(不能想当然的认为可以使用if exsits)

2.修改表的字符集:alter table 表名 character set 字符集名;

3.添加一列:alter table 表名 add 列名 数据类型;

4.修改列名 类型
第一种方式:alter table 表名 change 列名 新列名 新数据类型;
第二种方式:alter table 表名 modify 列名 新数据类型;

5.删除列:alter table 表名 drop 列名;

     4.D(Delete):删除
删除表:drop table 表名;
判断存在,存在再删除:drop table if exists 表名;

     5.复制表create table 创建的表 like 已有的表;:将创建的表复制为已有的表。

DML:增删改表中数据

客户端图形化工具:SQLYog
在SQLYog中执行不看顺序,一般都是一行一行执行。

1.添加数据insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
注意:
(1).列名和值要一一对应
(2).如果表名后,不定义列名,则默认给所有列添加值,但是还是要一一对应,后面的值必须要写完:inset into 表名 values(值1,值2,...值n);这里的n必须和列的个数相等。
(3).处理数字类型,其他类型需要使用引号(单双都可以)引起来,例如日期:

INSERT INTO stu VALUES(3,'张三丰',17,99.9,'1893-11-11',NULL);
  • 1

2.删除数据delete from 表名 where 条件;
例如将id=1的删除:

DELETE FROM stu WHERE id=1;
  • 1

注意:
(1).如果不加条件,则删除表中所有记录。
(2).如果要删除所有的记录
a.delete from 表名;:不推荐使用,有多少条记录就会执行多少次删除操作。
b.truncate table 表名;:先删除表,然后再创建一张一样的表

3.修改数据update 表名 set 列名1=值1,列名2= 值2,...where 条件;
例如:(将id=3的那一列的age改为117)

UPDATE stu SET age=117 WHERE id=3;
  • 1

注意:如果不加任何where条件,则会将表中所有记录全部修改。

DQL:查询表中的记录

select * from 表名;:查看该表中所有的数据(记录)(*可以想象成所有的意思,这里可以换成列名列表都可以,*不方便阅读,但是很简便)
例如:select name,age from student;
一.语法:
select:字段列表
from:表名列表
where:条件列表
group by:分组字段
having:分组之后的条件
order by:排序
limit:分页限定

二.基础查询:
1.多个字段的查询(将上面的所有数据查询改一下)

select 字段名1,字段名2... from 表名;
  • 1

注意:如果查询所有字段,则可以使用*来代替字段列表

例如:select name,age from student;

2.去除重复
去除重复的结果集:

select distinct 列名 from 表名;
  • 1

注意:例如:select distinct name,address from student;有多条列名的去重,需要指定列名都相同才能去除,有一个不同都不会去除。

3.计算列
例如计算math和English分数之和:

select name,math,English,math+english from student;
  • 1

弊端:如果有NULL计算的结果都为NULL

改进:

select name,math,English,math+ifnull(english,0) from student;
  • 1

(ifnull(a,b):如果a为null,那么a就等于b)
但是这样一来,那一列的名字就是math+ifnull(english,0):就显得很难看,需要起别名

4.起别名
例如在studnt表中,操作name和math:
第一种:加as:

select name as 名字,math as 数学 from student;
  • 1

第二种:什么也不加,就直接空格:

select name 名字,math 数学 from student;
  • 1

三。条件查询
1.where子句后面跟条件
2.运算符

> < <= >= = <>//和!=一样的含义(不等于)
BETWEEN...AND
IN(集合)
LIKE
IS NULL
and&&
or||
not!
//java语言中的好多逻辑运算符都可以使用,但是不推荐,直接使用英文
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

例如:
查询年龄等于20岁的:

select * from student where age = 20;
  • 1

查询年龄不等于20岁:

select * from student where age !=20;
select * from student where age <> 20;
  • 1
  • 2

查询年龄大于等于20小于等于30:

select * from student where age >= 20 && age<=30;
select * from student where age >= 20 and age<=30;
select * from student where age between 20 and 30;
  • 1
  • 2
  • 3

查询年龄22岁或者19岁或者25岁的信息

select * from student where age=22 or age=19 or age=25;
select * from student where age in(22,18,25);
  • 1
  • 2

查询英语成绩为null

select * from student where English =null;//错误,null值是不能使用=(!=)判断的
select * from student where English is null;
  • 1
  • 2

查询英语成绩不为null

select * from student where English is not null; 
  • 1

like模糊查询

占位符:
_:单个任意字符
%:任意多个字符

查询性马的有哪些?like

select * from student where name like '马%';
  • 1

查询姓名第二个字是化的人

select * from student where name like '_化%';
  • 1

查询姓名是3个字的人

select * from student where name like '___';//三个“_”
  • 1

查询姓名中包含马的人

select * from student where name like '%马%';
  • 1

DQL单表查询----排序查询

语法:order by 子句

order by 排序字段1 排序方式1,排序字段2 排序方式2...//结尾可以加;也可以不加
  • 1

排序方式
第一种:ASC:升序,默认的
例如:对数学升序排列

select * from student order by math
select * from student order by math asc
  • 1
  • 2

第二种:DESC:降序:

select * from student order by math desc
  • 1

注意:如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。

案例:按照数学成绩升序排名,如果数学成绩一样,则按照英语成绩排名。

select * from student order by math asc, English asc;
  • 1

DQL单表查询----聚合函数

含义:将一列数据作为一个整体,进行纵向计算。(例如计算平均值)
聚合函数计算结果都是单行单列的。

1.count:计算个数
一般用于:
(1)一把选择非空的列:主键
(2)select count(*) from student;*的意思是这列(横排)只要有一个不为null,就算一个。

查看英语那一列有多少个数据:

select count(English) from student;//有缺陷,排除null的值
select count(ifnull(English,0)) from student;
  • 1
  • 2

2.max:计算最大值
3.min:计算最小值
4.sum:计算和
5.avg:计算平均值

注意:聚合函数的计算,排除null值(计算不会管null)。
解决方法:
1.选择不包含非空的列进行计算。
2.ifnull函数。

DQL单表查询----分组查询

语法:group by 分组字段;

案例一:
按照性别分组,分别查询男、女同学的数学平均分。

select sex,avg(math) from stundent group by sex;
  • 1

结果:

sexavg (math)
99
98

案例二:
按照性别分组、分别查询男、女同学的平均分,人数:

select sex,avg(math),count(id) from stundent group by sex;
  • 1

结果:

sexavg(math)count(id)
993
984

注意:
1.数据最好都是分组字段,和聚合函数,其他的都没有意义。

例如
案例三:
按照姓名、性别分组、分别查询男、女同学的平均分,人数:

select name,sex,avg(math),count(id) from stundent group by sex;
  • 1
namesexavg(math)count(id)
张三993
李四985

name这一列完全没有意义,只是把第一个女生的名字显示出来了。

案例四:
按照性别分组、分别查询男、女同学的平均分,人数,要求:分数低于70分的人,不参加分组

select sex,avg(math),count(id) from student where math>70 group by sex;
  • 1

案例五:
按照性别分组、分别查询男、女同学的平均分,人数,要求:分数低于70分的人,不参加分组,分组之后,人数要大于2个人。

select sex,avg(math),count(id) from student where math>70 group by sex having count(id)>2;
  • 1

注意
2.:where和having的区别?

(1).where后不可以跟聚合函数的判断,having可以进行集合函数的判断。
(2).where在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来。

DQL单表查询----分页查询

语法:
1.limit开始的索引,每页查询的条数;
每页显示3条记录

select * from student limit 0,3;-- 第一页(和数组一样,索引从0开始,也就是从第一个数据开始,显示3条记录)
select * from student limit 3,3;-- 第二页
select * from student limit 6,3;-- 第三页
  • 1
  • 2
  • 3

*2.公式:开始的索引 =(当前的页码-1)每页显示的条数
注意:最后一页的页数不够,还是有多少条记录就显示多少条。
3.分页操作时一个“方言”。只能在mysql里面使用。

约束

概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性
分类:
1.主键约束:primary key
2.非空约束:not null
3.唯一约束:unique
4.外键约束:foreign key
非空约束:not null,某一列的值不能为null
1.在创建表时添加约束

CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL-- name为非空
);-- 如果添加name为null就不能添加
  • 1
  • 2
  • 3
  • 4

2.创建表完后,添加非空约束

alter table stu modify name varchar(20) not null;
  • 1

3.删除name的非空约束

alter table stu modify name varchar(20);
  • 1

唯一约束:unique,某一列的值不能重复
1.在创建表时,添加唯一约束:

CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE-- 手机号不能重复
);
  • 1
  • 2
  • 3
  • 4

2.删除唯一约束:(和非空约束不一样)

ALTER TABLE stu MODIFY phone_number VARCHAR(20);-- 错误代码,唯一约束和非空不一样
alter table stu drop index phone_number;-- 正确写法
  • 1
  • 2

3.在表创建完后,添加唯一约束:

alter table stu modify phone_number varchar(20) unique;
  • 1

注意:唯一约束可以有null值,但是只能有一条记录为null。

主键约束:primary key
注意
1.含义:非空且唯一(重点)
2.一张表只能有一个字段为主键
3.主键就是表中记录的唯一标识

一、在创建表时,添加主键约束

create table stu(
	id int primary key,-- 给id添加主键约束
	name varchar(20)
);
  • 1
  • 2
  • 3
  • 4

二、删除主键:

alter table stu modify id int;//错误,虽然语法是正确的,但是删除主键不生效
alter table stu drop primary key;
  • 1
  • 2

三、创建完表后,添加主键:(和非空、唯一的添加方法一样)

alter table  stu modify id int primary key;
  • 1

拓展
添加复合主键:

a int,
b int,
primary key(a,b)
  • 1
  • 2
  • 3

四、自动增长
概念:如果某一列是数值类型的,使用auto_increment可以来完成值的自动增长。(一般是配合int类型的主键来使用,例如id)

1.在创建表时,添加主键约束,并且完成主键自增长

create table stu(
	id int primary key auto_increment,-- 给id添加主键约束
	name varchar(20)
);
  • 1
  • 2
  • 3
  • 4

再使用:(自动增长时候,数据只跟上一条数据有关系,中间不连续也没关系)

insert into stu values(null,'张三');//这样id那一列不会是null,而是自己增长的int类型的数据
  • 1

2.删除自动增长

alter table stu modify id int;//上面主键约束的删除不是这样删除的,所以这样不会删除主键约束,这只能删除自动增长
  • 1

再执行:insert into stu values(null,‘张三’);就会报错了,自动增长没有了,id就不能赋值null了

3.创建表后,添加自动增长:

alter table stu modify id int auto_increment;
  • 1

备注:自动增长一般和主键一起使用,但是也有不和主键一起使用的。

外键约束:foreign key
1.在创建表时,可以添加外键
语法:

create table 表名(
	...
	外键列
	constraint 外键名称 foreign key 外键列名称 references 主表名称(主表列名称)
)
  • 1
  • 2
  • 3
  • 4
  • 5

例如:
作用(好处):
(1).删除部门表时,如果员工表的外键有对应部门表的主键,就不能删除该部门
(2).添加员工表的外键数据时,对应的部门表的关系列(一般都是主键)没有该数据,则向员工表添加该数据失败。

CREATE TABLE department(-- 部门表
	id INT PRIMARY KEY AUTO_INCREMENT,	
	dep_name VARCHAR(20),
	dep_locationion VARCHAR(20)
);
CREATE TABLE employee(-- 员工表
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,-- 外键(需要它对应主表的主键)
	CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)-- 添加外键约束
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

2.删除外键:

alter table 表名 drop foreign key 外键名称;
  • 1

3.创建表之后,添加外键:

alter table employee add foreign key emp_dept_fk foreign key (dep_id) references department(id);
通用:
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);
  • 1
  • 2
  • 3

验证外键约束成功:
点击最右上角的按钮(架构设计器),然后将有关联的两张表拖入架构设计器,如果两张表有线连接,这说明两表外键约束成功。
在这里插入图片描述

外键约束_级联操作

目的:改部门id,但是id是被关联到员工表里面了,所以首先要将员工表中改动部门id相同的id置为空,然后才能改动部门id

例如:(将id=1的部门id改为5)
第一步:将员工表中等于1的id全部置为null:(员工表中的id可以为null,但是不可以是外键部门表中不存在的id值)

update employee set dep_id=null where dep_id=1;
  • 1

第二步:再将部门表中id=1的改为id=5

第三步:再将员工表中为null的数据设置成5

update employee set dep_id=5 where dep_id is null;-- null的判断用is
  • 1

级联更新on update cascade
上述操作十分复杂,所以,在添加外键约束的时候就设置级联更新
添加外键,设置级联更新

alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on update cascade;
  • 1

这样操作了,两个连接表其中一个改了,另外一个相同的数据也改了。

级联删除on delete cascade
也是在添加外键的时候
添加外键,设置级联更新,设置级联删除:

alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on update cascade on delete cascade;
  • 1

作用:删除一条记录,另外和这个记录有关的表的记录都要被删除。

数据库的设计(多表关系,范式)

1.多表之间的关系

(1)一对一(了解:没意义,直接合成一张表就可以表示):
如:人和身份证
分析:一个人只有一个身份证,一个身份证只能对应一个人
(2).一对多(多对一):
如:部门和员工
分析:一个部门有多个员工,一个员工只能对应一个部门
在这里插入图片描述

(3).多对多:(需要借助第三张表)
如:学生和课程
分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
在这里插入图片描述
2.数据库设计的范式
概念:设计数据库时,需要遵守一些规范。要遵循后面的范式要求,必须先遵循前边的所有范式要求。
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:第一范式(INF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

分类:
1.第一范式(1NF):每一列都是不可分割的原子数据项
在这里插入图片描述

2.第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
概念:
(1).函数依赖:A–>B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A
例如(前者A是属性,后者是属性组):学号–>姓名。(学号,课程名称)–>分数
(2).完全函数依赖:A–>B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值。
例如:(学号,课程名称)–>分数
(3).部分函数依赖:A–>B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中某一些值即可。
例如:(学号,课程名称)–>姓名
(4).传递函数依赖:A–>B,B–>C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值也可以确定唯一C属性的值,则成为C传递函数依赖于A。
例如:学号–>系名,系名–>系主任
(5).码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性值)为该表的码。通俗点:通过该属性(属性组)可以确定唯一的其他属性,就称为码。
例如:该表中码为:(学号,课程名称)
备注:
主属性:码属性组中的所有属性
非主属性:除了码属性组的属性

第二范式的意思:消除部分依赖
先分成非主属性完全依赖于主属性(码)的几张表,再删除重复的数据。
在这里插入图片描述
第二范式消除了第一个问题。

3.第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖),
在这里插入图片描述

数据库的备份和还原

1.命令行:
备份:mysqldump -u用户名 -p密码 > 保存的路径
在这里插入图片描述

还原:
(1).登录数据库
(2).创建数据库
(3).使用数据库
(4).执行文件。source 文件路径
在这里插入图片描述
2.在SQLyog里面直接操作

多表查询概述

单表查询:select * from 表;
擦寻两张表:select * from 表1,表2;(结果是表1和表2的笛卡尔积)
多表查询的目的:消除无用的数据。
例如:
dept表:
在这里插入图片描述
emp表:
在这里插入图片描述

emp和dept表笛卡尔积后无用的数据:
在这里插入图片描述

笛卡尔积:有两个集合A,B,取这两个集合的所有组成情况。
多表查询的分类:
1.内连接查询:
2.外连接查询:
3.子查询:

多表查询_内连接

隐式内连接和显示内连接查询的结果是一样的,这是写法不同

1.隐式内连接
使用where条件消除无用数据
查询所有员工共信息和对应的部门信息

select * from emp,dept where emp.'dept_id'=dept.'id';-- ''单引号可加可不加
  • 1

在这里插入图片描述

查询员工表的名称,性别,部门表的名称

select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id=dept.id;-- 要加前缀,要不然name区分不开是哪个表的
  • 1

改进:(用别名)

select
	t1.name,-- 分行有好处:方便加注释
	t1.gender,
	t2.name
from-- 取别名
	emp t1,
	dept t2
where
	t1.dept_id=t2.id;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

2.显式内连接:

select 字段列表 from 表名1 inner join 表名2 on 条件-- inner是可以省略不写的
  • 1

查询所有员工共信息和对应的部门信息:

select * from emp inner join dept on emp.'dept_id'=dept.'id';
  • 1

内连接重点:要找到条件是什么。

多表查询_外连接

1.左外连接:查询的是左边所有数据以及其交集部分。(内连接就是查询的是交集部分)

select 字段列表 from1 left outer join2 on 条件;-- outer可以不写
  • 1

emp表添加一个小白龙,让他的部门id为null
在这里插入图片描述
查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称

select
	t1.*,t2.name
from
	emp t1,dept t2
where
	t1.dept_id=t2.id;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

显示结果没有加的小白龙那一行数据,null被舍去了。
改正:

select t1.*,t2.name from emp t1 left join dept t2 on t1.dept_id=t2.id;
  • 1

由于小白龙是左表emp的数据,所以成功显示出来

2.右外连接: 查询的是右边所有数据以及其交集部分。

select 字段列表 from1 right outer join2 on 条件;-- outer可以省略
  • 1

由于小白龙在左边中,右外连接就不能显示小白龙。

注意:其实左外(右外)两个表换个位置等于右外(左外)了。

多表查询_子查询

概念:
查询中嵌套查询,称嵌套查询为子查询。
查询工资最高的员工信息:
不适用子查询:(分两步)
1.查询最高的工资是多少 9000

select max(salary) from emp;-- 得到最高工资
  • 1

2.查询员工信息,并且工资等于9000的

select * from emp where emp.salary=9000;
  • 1

一条sql就完成这个操作(子查询:嵌套)

select * from emp where emp.salary=(select max(salary) from emp);
  • 1

子查询不同情况:
1.子查询的结果是单行单列的:
子查询可以作为条件,使用运算符去判断。(运算符:> >= < <= =)

(上面的例子就是单行单列)
查询员工工资小于平均工资的人:

select * from emp where emp.salary<(select avg(salary) from emp);
  • 1

2.子查询的结果是多行单列的:(是一种集合的判断)
子查询可以作为条件,使用运算符in来判断

查询’财务部’和’市场部’所有的员工信息

select id from dept where name='财务部' or name = '市场部';-- 得到的dept_id是3、2
select * from emp where dept_id=3 or dept_id=2;-- 或者是 dept_id in(3,2);
  • 1
  • 2

一条sql(子查询):

select * from emp where dept_id in(select id from dept where name='财务部' or name = '市场部');
  • 1

3.子查询的结果是多行多列的:
子查询不能作为条件,可以作为一张虚拟表

查询员工入职日期是2011-11-11日之后的员工信息和部门信息
子查询:

select * from dept t1,(select * from emp where emp.join_date>'2011-11-11') t2;
where t1.id=t2.dept_id;
  • 1
  • 2

普通的内连接:

select * from emp t1,dept t2 where t1.dept_id=t2.id and t1.join_date > '2011-11-11'
  • 1

事务

1.概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
2.操作
(1).开启事务:start transaction;
(2).回滚:rollback;(在哪开的事务,就回滚到那里之前)
(3).提交:commit;
3.例子

USE db1;
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME,balance) VALUES('zhangsan',1000),('lisi',1000);
SELECT * FROM account;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

需要实现张三给李四转账500:
直接转账可以的:

-- 1.张三账户-500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
-- 2.李四账户+500
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
  • 1
  • 2
  • 3
  • 4

但是假如中间有异常:

-- 1.张三账户-500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
这里有异常不能执行......
-- 2.李四账户+500
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
  • 1
  • 2
  • 3
  • 4
  • 5

只会执行张三转账代码,就不会执行李四收账代码
就要用事务

-- 0.开启事务
START TRANSACTION;
-- 1.张三账户-500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
这里有异常不能执行......
-- 2.李四账户+500
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
-- 都还原为1000
UPDATE account SET balance=1000;

-- 发现出问题了,回滚事务
ROLLBACK;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

如果没有问题了,就不用回滚事务,需要提交
注意(重点,如果没有进行提交):数据会临时变化(没有持久化更新),没有真正变化,就是会自动回滚,将SQLyog关闭再打开,会发现数据还是事务前的数据,所以需要进行提交

-- 0.开启事务
START TRANSACTION;
-- 1.张三账户-500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
-- 这里有异常不能执行......
-- 2.李四账户+500
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
-- 都还原为1000
UPDATE account SET balance=1000;

-- 发现事务没有问题
COMMIT;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

4.MySQL数据库中事务默认自动提交
自动提交:
(1).mysql就是自动提交的
(2).一条MDL(增删改)语句会自动提交一次事务。
手动提交:
(1).Oracle数据库默认是手动提交事务的。(执行完代码,都需要commit,否则不会生效)
(2).需要先开启事务,再提交

查看事务的默认提交方式:select @@autocommit;1 代表自动提交,0 代表手动提交。
修改默认提交方式:set @@autoconmmit=0;再执行mysql语句都是临时变化,需要手动提交。
5.事务的四大特征:
1.原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
2.持久性:当事务提交或回滚后,数据库会持久化的保存数据。
3.隔离性:多个事务之间,相互独立。
4.一致性:事务操作前后,数据总量不变。

事务的隔离级别(了解)

概念多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。

存在的问题
1.脏读:一个事务,读取到另一个事务中没有提交的数据。
2.不可重复读:在同一个事务中,两次读取到的数据不一样。
3.幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。

隔离级别:
1.read uncommitted:读未提交
产生的问题:脏读、不可重复读、幻读
比喻说用户1打开事务借了钱给用户2,但是没有提交事务,用户2查询账单发现账户钱已经到账,写了欠条后,用户1又回滚事务,最后用户2的账户钱有回到了以前,被骗了。

2.read committed:读已提交(Oracle默认)
产生的问题:不可重复读、幻读

3.repeatable read:可重复读(MySQL默认)(不管事务1提交没有,事务2中显现的数据都不会变化,只有提交事务2才会显现)
产生的问题:幻读

4.serializable:串行化(锁表操作,意思是一批数据只能由一个事务处理,很好用,但是效率低)
可以解决所有的问题

注意:隔离级别从小到大安全性越来越高,但是效率越来越低。
数据库查询隔离级别:select @@tx_isolation;
数据库设置隔离级别:set global transaction isolation level 级别字符串;(再次打开编译器就会生效)

DCL管理用户,授权

DBA:数据库管理员
管理用户
1.添加用户:create user '用户名'@'主机名' identified by '密码';

create user 'zhangsan'@'localhost' identified by '123'
登录试一下:
mysql -uzhangsan -p123
  • 1
  • 2
  • 3

2.删除用户:drop user '用户名'@'主机名';
3.修改用户密码:
方式一:

UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';
  • 1

方式二:(DCL特有的方式)

SET PASSWORD FOR '用户名'@'主机名'=PASSWORD('新密码');
  • 1

mysql中忘记了root用户的密码?
(1).用管理员的身份进入cmd,输入net stop mysql 停止MySQL服务
(2).使用无验证方式启动mysql服务:mysqld --skip-grant-tables
(3).(不要关闭上一个窗口)然后打开一个新的cmd,只需要输入mysql,回车就登录了。
(4).修改密码。

use mysql;
update user set password =password('新密码') where user='root';
  • 1
  • 2

(5).关闭两个窗口,在任务管理器中手动结束mysqld.exe进程。
(6).然后再以管理员的身份进入cmd,启动MySQL服务:net start mysql
4.查询用户:
(1)切换到MySQL数据库:use mysql;
(2)查询user表:select * from user;
备注:通配符:%表示可以再任意主机使用用户登录数据库。

权限管理:
1.查询权限:show grants for '用户名'@'主机名';
2.授予权限:grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
例如:
给李四授予查询db1中stu表的权限:

grant select on db1.stu to 'lisi'@'%';
  • 1

给张三用户授予所有权限,在任意数据库任意表上

grant all on *.* to 'zhangsan'@'localhost';
  • 1

3.撤销权限:revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号