MySQL数据库基础回顾【详细】

database

目录

  • 数据库的de增删改查【CRUD】
    • 创建数据库
    • 查询数据库
    • 修改数据库
    • 删除数据库
    • 使用数据库
    • 综合练习:
  • 数据库表的de增删改查【CRUD】
    • 数据库里的数据类型:
    • C(Create)
    • R(Retrieve)
    • U(Update)
    • D(Delete)
  • 数据库表中的记录的基本操作
    • DML【增删改表中的数据】
    • 添加数据
    • 删除数据
    • 修改数据
    • DQL【查询表中的记录】
      • 单表查询
        • 1.语法
        • 2.基础查询
          • 多个字段的查询
          • 去除重复
          • 计算列
          • 起别名
        • 3.条件查询
        • 练习
        • 4.模糊查询
        • 5.排序查询
        • 6.聚合函数
        • 7.分组查询
        • 8.分页查询
  • 约束
    • 非空约束
    • 唯一约束
    • 主键约束
    • 外键约束
  • 数据库的设计
  • 数据库的备份和还原
    • 数据库的备份和还原
      • 语法
  • 今日内容
    • 多表查询:
    • 事务
    • DCL

数据库的de增删改查【CRUD】

DDL【操作数据库、表】

创建数据库

  • CREATE DATABASE db1;【db1是我要创建的数据库的名称,如果已经存在数据库db1,则会报错】
  • CREATE DATABASE IF NOT EXISTS db2;【先判断是否存在数据库db2,如果存在就不会创建且不会报错,如果不存在就会创建数据库db2】
  • ==CREATE DATABASE db3 CHARACTER SET GBK;=【设置db3数据库的字符集为GBK】=

查询数据库

  • SHOW DATABASES;【查询所有数据库名称】
  • SHOW CREATE DATABASE db1;【显示数据库db1的字符集是什么】

修改数据库

  • alter database db3 character set utf8;【修改数据库db3字符集为utf-8】

删除数据库

  • drop database db3;【删除数据库db3,没有加判断,这样如果不存在数据库db3,运行语句之后会报错】
  • drop database if exists db3;【如果存在数据库db3,就删除(加一个判断,这样如果不存在数据库db3,运行语句之后也不会报错)】

使用数据库

  • select database();【查询正在使用的数据库名称】
  • use db4;【使用数据库db4】

综合练习:

CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET GBK;【创建数据库db4(先判断是否存在数据库db4)并且设置字符集为gbk】

数据库表的de增删改查【CRUD】

查表之前,要先使用数据库,例如:use db4;【使用数据库db4,接下来的操作表即操作的是数据库db4的表 】

数据库里的数据类型:

  1. int:整数类型【如:age int,】
  2. double:小数类型【如:score double(5,2),表示这个小数最多计算到5位,但只保留小数点后两位】
  3. date:日期类型【只包含年月日,yyyy-MM-dd】
  4. datetime:日期类型【包含年月日和时分秒,yyyy-MM-dd-HH:mm:ss】
  5. timestamp:时间戳类型【包含年月日和时分秒,yyyy-MM-dd-HH:mm:ss】【如果不赋值,就会使用系统时间自动赋值】
  6. varchar:字符串类型【如:name varchar(20),表示姓名最大字符长度位20】

C(Create)

create table student(

id int,

name varchar(32),

age int,

score double(4,1),

birthday date,

insert_time timestamp

);【创建表】

create table stu like student;【复制表student并放到一个新的stu表中,此时数据库中将存在两个只有名字不一样的表】

R(Retrieve)

  • show tables;【查询所有数据库表】
  • desc host;【查询表host的表结构】

U(Update)

  • 修改表名:alter table student rename to newstudent;【修改表student的名字为newstudent】
  • 修改字符集:alter table newstudent character gbk;【修改表newstudent的字符集为gbk】
  • 添加一列:alter table newstudent add gender varchar(10);【给表newstudent添加一列(性别)gender】
  • 修改列名称和类型:alter table newstudent change gender sex varchar(20);【即修改了列名称为sex,又修改了数 据类型为varchar(20)】
  • 只修改列的数据类型:alter table newstudent modify sex varchar(32);【只改了列sex的数据类型】
  • 删除列:alter table newstudent drop sex;【删除表newstudent中的sex列】

D(Delete)

  • drop table stu;【删除表stu,如果存在就删除,如果不存在表stu,就会报错】
  • drop tableif existe stu;【先判断是否存在表stu,如果存在就删除,如果不存在就不会删除且不会报错】

数据库表中的记录的基本操作

DML【增删改表中的数据】

添加数据

  • INSERT INTOstu(id,NAME,age,score,birthday) VALUES(1001,"牛牛",18,99,"2001-05-17");【列名和添加的值顺序要一一对应】
  • INSERT INTOstuVALUES(1001,"牛牛",18,99,"2001-05-17",NULL);【如果 给所有列添加数据,前面的列名就可以省略不写】

删除数据

  • DELETE FROM stu WHERE age=20;【删除age为20的那一行数据】
  • DELETE FROM stu;【删除表stu中的所有数据】
  • TRUNCATE TABLE stu;【删除表stu,并创建一个一摸一样的空的stu表】【这种删除表中所有数据的方式比上面要好 你 】

修改数据

  • UPDATE stu SET age = 118 WHERE id = 1001;【修改age为118,限制条件:id为1001的那一行数据】
  • UPDATE stu SET age=200,score=100 WHERE id=1002;【修改age为200、score为100,限制条件:id为1002的那一行数据】
  • UPDATE stu SET age=300;【不加where限制条件,就会把表中所有的age修改为300】

DQL【查询表中的记录】

单表查询

select * from stu;【查看表】

1.语法

  • ​ select

    ​ 字段列表

    ​ from

    ​ 表名列表

    ​ where

    ​ 条件列表

    ​ group by

    ​ 分组字段

    ​ having

    ​ 分组之后的条件

    ​ order by

    ​ 排序

    ​ limit

    ​ 分页限定

2.基础查询

多个字段的查询

  • SELECT

    NAME, -- 姓名

    age, -- 年龄

    sex, -- 性别

    address, -- 地址

    math, -- 数学

    english -- 英语

    FROM student3; -- 表名

去除重复

  • SELECT DISTINCT address FROM student3;-- 去重【查询address列,并去除重复的数据】

计算列

  • SELECT NAME,math,english,math+english FROM student3;【查询并计算math+english的值】
  • SELECT NAME,math,english,IFNULL(math,0)+IFNULL(english,0) FROM student3;【查询并计算math+english的值,且添加了ifnull函数判断,如果为null,则将其替换为0代入计算】

起别名

  • SELECT NAME,math,english,IFNULL(math,0)+IFNULL(english,0)AS 总分 FROM student3;【查询并计算math+english的值,且添加了ifnull函数判断,如果为null,则将其替换为0代入计算】【且将计算得到的结果的那一列重命名为总分】【省略AS不写也行

3.条件查询

比较运算符

说明

> 、< 、<= 、>= 、= 、<>

<>在 SQL 中表示不等于,在 mysql 中也可以使用!= 没有==

BETWEEN...AND

在一个范围之内,如:between 100 and 200 相当于条件在 100 到 200 之间,包头又包尾

IN( 集合)

集合表示多个值,使用逗号分隔

LIKE " 张%"

模糊查询

IS NULL

查询某一列为 NULL 的值,注:不能写=NULL

逻辑运算符

说明

and 或 &&

与,SQL 中建议使用前者,后者并不通用。

or 或 ||

not 或 !

练习

  • SELECT NAME,age FROM student3 WHERE age>20;【查询年龄大于20的人,并只显示name和age】

  • SELECT NAME,age FROM student3 WHERE age>=====20;【查询年龄大于等于20的人并只显示name和age】

  • SELECT NAME,age FROM student3 WHERE age=====20;【查询年龄等于20的人,并只显示name和age】

  • SELECT NAME,age FROM student3 WHERE age<20;【查询年龄小于20的人,并只显示name和age】

  • SELECT NAME,age FROM student3 WHERE age<=20;【查询年龄大于20的人,并只显示name和age】

  • SELECT NAME,age FROM student3 WHERE age!=20;【查询年龄大于20的人,并只显示name和age】

  • SELECT NAME,age FROM student3 WHERE age<>20;【查询年龄大于20的人,并只显示name和age】

  • SELECT * FROM student3 WHERE age>=20 AND age<=30;【查询年龄大于等于20并且小于等于30的人】

  • SELECT * FROM student3 WHERE age BETWEEN 20 AND 30;【查询年龄大于等于20并且小于等于30的人】

  • SELECT * FROM student3 WHERE age=20 or age=18 or age=25;【查询年龄22岁、18岁、25岁的信息】

  • SELECT * FROM student3 WHERE age IN (22,18,25);【查询年龄22岁、18岁、25岁的信息】

  • SELECT * FROM student3 WHERE english IS NULL;【查询英语成绩为空的信息】

  • SELECT * FROM student3 WHERE english IS NOT NULL;【查询英语成绩不为空的信息】

4.模糊查询

  • SELECT * FROM student3 WHERE NAME LIKE "马%";【查询NAME是马字开头的信息】
  • SELECT * FROM student3 WHERE NAME LIKE "_化%";【查询NAME第二个字是化的信息】
  • SELECT * FROM student3 WHERE NAME LIKE "___";【三个 _ ,表示查询NAME是3个字的信息】
  • SELECT * FROM student3 WHERE NAME LIKE "%德%";【查询NAME包含德字的信息】

5.排序查询

语法:order by 子句

例如:select 列名 from 表名 order by排序字段1 排序方式1排序字段2 排序方式2排序字段3 排序方式3,......

  • SELECT * FROM student3 ORDER BY math;-- 从上到下,默认升序排序
  • SELECT * FROM student3 ORDER BY math ASC;-- ASC,从上到下,升序排序
  • SELECT * FROM student3 ORDER BY math DESC;-- DESC,从上到下,降序排序
  • SELECT * FROM student3 ORDER BY math ASC,english ASC;-- 按照数学成绩排名,如果数学成绩一样,则按照英语成绩排名【如果有多个排序条件,则当前面的排序条件的值一样是,才会判断第二个条件,以此类推,判断多个排序条件】

6.聚合函数

将一列数据作为一个整体,进行纵向的计算。

注意:聚合函数的计算是排除null值的;

解决方案:

  1. 选择非空的列:主键,比如id
  2. ifnull函数;

count:计算个数

  • SELECT COUNT(age) FROM student3;-- age这一列有8个数据

max:计算最大值

  • SELECT MAX(math) FROM student3;

min:计算最小值

  • SELECT MIN(math) FROM student3;

sum:求

  • SELECT SUM(math) FROM student3;
  • SELECT SUM(english) FROM student3;

avg:计算平均值

  • SELECT AVG(math) FROM student3;

7.分组查询

语法:group by 分组字段;

注意:

分组之后查询的字段:分组字段、聚合函数

where 和 having的区别?

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

例如:select 列名 from 表名 order by排序字段1 排序方式1排序字段2 排序方式2排序字段3 排序方式

-- 按照性别,分别查询男、女同学的平均分

SELECT sex,AVG(math) FROM student3 GROUP BY sex;

SELECT sex,AVG(english) FROM student3 GROUP BY sex;

-- 按照性别,分别查询男、女同学的平均分、人数,且分数低于70分的人不参与分组

SELECT sex,AVG(math),COUNT(id) FROM student3 WHERE math>70GROUP BY sex;

SELECT sex,AVG(english),COUNT(id) FROM student3 WHERE english>70GROUP BY sex;

-- 按照性别,分别查询男、女同学的平均分、人数,且分数低于70分的人不参与分组,且分组之后人数大于2

SELECT sex,AVG(math),COUNT(id) FROM student3 WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;

SELECT sex,AVG(math),COUNT(id) AS 人数 FROM student3 WHERE math>70 GROUP BY sex HAVING 人数>2;

8.分页查询

-- 每页显示三条记录

SELECT * FROM student3 LIMIT 0,3;-- 第一页

SELECT * FROM student3 LIMIT 3,3;-- 第二页

SELECT * FROM student3 LIMIT 6,3;-- 第一页

-- 公式:开始的索引:(当前的页码-1)* 每页显示的条数

语法: limit开始的索引,每页查询的条数;

公式:开始的索引:(当前的页码-1) * 每页显示的条数

limit是一个MysQL"方言",用来分页查询

约束

概念︰对表中的数据进行限定,保证数据的正确性有效性完整性

分类:

  1. 主键约束: primary key; b
  2. 非空约束: not null
  3. 唯一约束:unique
  4. 外键约束:foreign key

非空约束

not null

1.创建表时添加约束

CREATE TABLE stu1(

id INT,

NAME VARCHAR( 20) NOT NULL -- name为非空

);

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

ALTER TABLE stu1 MODIFY NAME VARCHAR(20) NOT NULL;

3.删除name的非空约束(其实就是更改表的列)

ALTER TABLE stu1 MODIFY NAME VARCHAR(20);

唯一约束

unique:值不能重复

注意:MySQL中,唯一约束的列的值可以有多个null;

1.创建表时添加唯一约束

CREATE TABLE stu1(

id INT,

NAME VARCHAR(20) UNIQUE-- 给name添加唯一约束

);

2.创建表完后,添加唯一约束

ALTER TABLE stu1 MODIFY NAME VARCHAR(20) UNIQUE;

3.删除name的唯一约束(不是更改表的列,是删除列索引index)

ALTER TABLE stu1 DROP INDEX NAME;

主键约束

primary key非空且唯一【且一张表只能有唯一一个主键约束】

1.创建表时添加主键约束

CREATE TABLE stu1(

id INT PRIMARY KEY,-- 给id添加主键约束

NAME VARCHAR(20)

);

2.创建表完后,添加主键约束

ALTER TABLE stu1 MODIFY id INT PRIMARY KEY;

3.删除id的主键约束(不是更改表的列,是删除primary key)

ALTER TABLE stu1 DROP PRIMARY KEY;

4.自动增长:如果某一列是数值类型的,使用auto_incremrnt 可以来完成值的自动增长

-- 创建表时,添加主键约束,并配合添加自动增长功能

CREATE TABLE stu1(

id INT PRIMARY KEY AUTO_INCREMENT,-- 给id添加主键约束,并让它自动增长

NAME VARCHAR(20)

);

5.删除自动增长

ALTER TABLE stu1 MODIFY id INT;-- 不会删除PRIMARY KEY,但是会删除掉自动增长AUTO_INCREMENT

6.创建表完后,添加自动增长

ALTER TABLE stu1 MODIFY id INT AUTO_INCREMENT;

外键约束

foreign key:让表于表产生关系,从而保证数据的正确性。

1.在创建表时,可以添加外键

语法:

create table 表名(

....

外键列

constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)

);

2.删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

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

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

4.级联操作

添加级联操作

语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;

分类:

级联更新:ON UPDATE CASCADE

级联删除:ON DELETE CASCADE

数据库的设计

多表之间的关系

分类

一对一(了解)

如:人和身份证

分析:一个人只有一个身份证,一个身份证只能对应一个人

一对多(多对一)

如:部门和员工

分析:一个部门有多个员工,一个员工只能对应一个部门

多对多

如:学生和课程

分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择

实现关系

一对多(多对一)

如:部门和员工

实现方式:在多的一方建立外键,指向一的一方的主键。

多对多

如:学生和课程

实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键

一对一(了解)

如:人和身份证

实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。

案例

创建旅游线路分类表 tab_category

-- cid 旅游线路分类主键,自动增长

-- cname 旅游线路分类名称非空,唯一,字符串 100

CREATE TABLE tab_category (

cid INT PRIMARY KEY AUTO_INCREMENT,

cname VARCHAR(100) NOT NULL UNIQUE

);

创建旅游线路表 tab_route

rid 旅游线路主键,自动增长

rname 旅游线路名称非空,唯一,字符串 100

price 价格

rdate 上架时间,日期类型

cid 外键,所属分类

CREATE TABLE tab_route(

rid INT PRIMARY KEY AUTO_INCREMENT,

rname VARCHAR(100) NOT NULL UNIQUE,

price DOUBLE,

rdate DATE,

cid INT,

FOREIGN KEY (cid) REFERENCES tab_category(cid)

);

创建用户表 tab_user

uid 用户主键,自增长

username 用户名长度 100,唯一,非空

password 密码长度 30,非空

name 真实姓名长度 100

birthday 生日

sex 性别,定长字符串 1

telephone 手机号,字符串 11

email 邮箱,字符串长度 100

CREATE TABLE tab_user (

uid INT PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(100) UNIQUE NOT NULL,

PASSWORD VARCHAR(30) NOT NULL,

NAME VARCHAR(100),

birthday DATE,

sex CHAR(1) DEFAULT "男",

telephone VARCHAR(11),

email VARCHAR(100)

);

创建收藏表 tab_favorite

rid 旅游线路 id,外键

date 收藏时间

uid 用户 id,外键

rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次

CREATE TABLE tab_favorite (

rid INT, -- 线路id

DATE DATETIME,

uid INT, -- 用户id

-- 创建复合主键

PRIMARY KEY(rid,uid), -- 联合主键

FOREIGN KEY (rid) REFERENCES tab_route(rid),

FOREIGN KEY(uid) REFERENCES tab_user(uid)

);

数据库设计的范式

概念:设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求

设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。

目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

分类:

1.第一范式(1NF):每一列都是不可分割的原子数据项

2.第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)

几个概念

1.函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于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.图形化工具:略

今日内容

1. 多表查询

2. 事务

3. DCL

多表查询:

* 查询语法:

select

列名列表

from

表名列表

where....

* 准备sql

# 创建部门表

CREATE TABLE dept(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(20)

);

INSERT INTO dept (NAME) VALUES ("开发部"),("市场部"),("财务部");

# 创建员工表

CREATE TABLE emp (

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(10),

gender CHAR(1), -- 性别

salary DOUBLE, -- 工资

join_date DATE, -- 入职日期

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键,关联部门表(部门表的主键)

);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("孙悟空","男",7200,"2013-02-24",1);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("猪八戒","男",3600,"2010-12-02",2);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("唐僧","男",9000,"2008-08-08",2);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("白骨精","女",5000,"2015-10-07",3);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("蜘蛛精","女",4500,"2011-03-14",1);

* 笛卡尔积:

* 有两个集合A,B .取这两个集合的所有组成情况。

* 要完成多表查询,需要消除无用的数据

* 多表查询的分类:

1. 内连接查询:

1. 隐式内连接:使用where条件消除无用数据

* 例子:

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

SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

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

SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

SELECT

t1.name, -- 员工表的姓名

t1.gender,-- 员工表的性别

t2.name -- 部门表的名称

FROM

emp t1,

dept t2

WHERE

t1.`dept_id` = t2.`id`;

2. 显式内连接:

* 语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件

* 例如:

* SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;

* SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;

3. 内连接查询:

1. 从哪些表中查询数据

2. 条件是什么

3. 查询哪些字段

2. 外连接查询:

1. 左外连接:

* 语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;

* 查询的是左表所有数据以及其交集部分。

* 例子:

-- 查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称

SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

2. 右外连接:

* 语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;

* 查询的是右表所有数据以及其交集部分。

* 例子:

SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

3. 子查询:

* 概念:查询中嵌套查询,称嵌套查询为子查询。

-- 查询工资最高的员工信息

-- 1 查询最高的工资是多少 9000

SELECT MAX(salary) FROM emp;

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

SELECT * FROM emp WHERE emp.`salary` = 9000;

-- 一条sql就完成这个操作。子查询

SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

* 子查询不同情况

1. 子查询的结果是单行单列的:

* 子查询可以作为条件,使用运算符去判断。 运算符: > >= < <= =

*

-- 查询员工工资小于平均工资的人

SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

2. 子查询的结果是多行单列的:

* 子查询可以作为条件,使用运算符in来判断

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

SELECT id FROM dept WHERE NAME = "财务部" OR NAME = "市场部";

SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

-- 子查询

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = "财务部" OR NAME = "市场部");

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;

-- 普通内连接

SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > "2011-11-11"

* 多表查询练习

-- 部门表

CREATE TABLE dept (

id INT PRIMARY KEY PRIMARY KEY, -- 部门id

dname VARCHAR(50), -- 部门名称

loc VARCHAR(50) -- 部门所在地

);

-- 添加4个部门

INSERT INTO dept(id,dname,loc) VALUES

(10,"教研部","北京"),

(20,"学工部","上海"),

(30,"销售部","广州"),

(40,"财务部","深圳");

-- 职务表,职务名称,职务描述

CREATE TABLE job (

id INT PRIMARY KEY,

jname VARCHAR(20),

description VARCHAR(50)

);

-- 添加4个职务

INSERT INTO job (id, jname, description) VALUES

(1, "董事长", "管理整个公司,接单"),

(2, "经理", "管理部门员工"),

(3, "销售员", "向客人推销产品"),

(4, "文员", "使用办公软件");

-- 员工表

CREATE TABLE emp (

id INT PRIMARY KEY, -- 员工id

ename VARCHAR(50), -- 员工姓名

job_id INT, -- 职务id

mgr INT , -- 上级领导

joindate DATE, -- 入职日期

salary DECIMAL(7,2), -- 工资

bonus DECIMAL(7,2), -- 奖金

dept_id INT, -- 所在部门编号

CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),

CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)

);

-- 添加员工

INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES

(1001,"孙悟空",4,1004,"2000-12-17","8000.00",NULL,20),

(1002,"卢俊义",3,1006,"2001-02-20","16000.00","3000.00",30),

(1003,"林冲",3,1006,"2001-02-22","12500.00","5000.00",30),

(1004,"唐僧",2,1009,"2001-04-02","29750.00",NULL,20),

(1005,"李逵",4,1006,"2001-09-28","12500.00","14000.00",30),

(1006,"宋江",2,1009,"2001-05-01","28500.00",NULL,30),

(1007,"刘备",2,1009,"2001-09-01","24500.00",NULL,10),

(1008,"猪八戒",4,1004,"2007-04-19","30000.00",NULL,20),

(1009,"罗贯中",1,NULL,"2001-11-17","50000.00",NULL,10),

(1010,"吴用",3,1006,"2001-09-08","15000.00","0.00",30),

(1011,"沙僧",4,1004,"2007-05-23","11000.00",NULL,20),

(1012,"李逵",4,1006,"2001-12-03","9500.00",NULL,30),

(1013,"小白龙",4,1004,"2001-12-03","30000.00",NULL,20),

(1014,"关羽",4,1007,"2002-01-23","13000.00",NULL,10);

-- 工资等级表

CREATE TABLE salarygrade (

grade INT PRIMARY KEY, -- 级别

losalary INT, -- 最低工资

hisalary INT -- 最高工资

);

-- 添加5个工资等级

INSERT INTO salarygrade(grade,losalary,hisalary) VALUES

(1,7000,12000),

(2,12010,14000),

(3,14010,20000),

(4,20010,30000),

(5,30010,99990);

-- 需求:

-- 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述

/*

分析:

1.员工编号,员工姓名,工资,需要查询emp表 职务名称,职务描述 需要查询job表

2.查询条件 emp.job_id = job.id

*/

SELECT

t1.`id`, -- 员工编号

t1.`ename`, -- 员工姓名

t1.`salary`,-- 工资

t2.`jname`, -- 职务名称

t2.`description` -- 职务描述

FROM

emp t1, job t2

WHERE

t1.`job_id` = t2.`id`;

-- 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置

/*

分析:

1. 员工编号,员工姓名,工资 emp 职务名称,职务描述 job 部门名称,部门位置 dept

2. 条件: emp.job_id = job.id and emp.dept_id = dept.id

*/

SELECT

t1.`id`, -- 员工编号

t1.`ename`, -- 员工姓名

t1.`salary`,-- 工资

t2.`jname`, -- 职务名称

t2.`description`, -- 职务描述

t3.`dname`, -- 部门名称

t3.`loc` -- 部门位置

FROM

emp t1, job t2,dept t3

WHERE

t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;

-- 3.查询员工姓名,工资,工资等级

/*

分析:

1.员工姓名,工资 emp 工资等级 salarygrade

2.条件 emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary

emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary

*/

SELECT

t1.ename ,

t1.`salary`,

t2.*

FROM emp t1, salarygrade t2

WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;

​ -- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级

​ /*

分析:

1. 员工姓名,工资 emp , 职务名称,职务描述 job 部门名称,部门位置,dept 工资等级 salarygrade

2. 条件: emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary

*/

SELECT

t1.`ename`,

t1.`salary`,

t2.`jname`,

t2.`description`,

t3.`dname`,

t3.`loc`,

t4.`grade`

FROM

emp t1,job t2,dept t3,salarygrade t4

WHERE

t1.`job_id` = t2.`id`

AND t1.`dept_id` = t3.`id`

AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;

-- 5.查询出部门编号、部门名称、部门位置、部门人数

/*

分析:

1.部门编号、部门名称、部门位置 dept 表。 部门人数 emp表

2.使用分组查询。按照emp.dept_id完成分组,查询count(id)

3.使用子查询将第2步的查询结果和dept表进行关联查询

*/

SELECT

t1.`id`,t1.`dname`,t1.`loc` , t2.total

FROM

dept t1,

(SELECT

dept_id,COUNT(id) total

FROM

emp

GROUP BY dept_id) t2

WHERE t1.`id` = t2.dept_id;

-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询

/*

分析:

1.姓名 emp, 直接上级的姓名 emp

* emp表的id 和 mgr 是自关联

2.条件 emp.id = emp.mgr

3.查询左表的所有数据,和 交集数据

* 使用左外连接查询

*/

/*

select

t1.ename,

t1.mgr,

t2.`id`,

t2.ename

from emp t1, emp t2

where t1.mgr = t2.`id`;

*/

SELECT

t1.ename,

t1.mgr,

t2.`id`,

t2.`ename`

FROM emp t1

LEFT JOIN emp t2

ON t1.`mgr` = t2.`id`;

事务

1. 事务的基本介绍

1. 概念:

* 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。

2. 操作:

1. 开启事务: start transaction;

2. 回滚:rollback;

3. 提交:commit;

3. 例子:

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;

UPDATE account SET balance = 1000;

-- 张三给李四转账 500 元

-- 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";

-- 发现执行没有问题,提交事务

COMMIT;

-- 发现出问题了,回滚事务

ROLLBACK;

4. MySQL数据库中事务默认自动提交

* 事务提交的两种方式:

* 自动提交:

* mysql就是自动提交的

* 一条DML(增删改)语句会自动提交一次事务。

* 手动提交:

* Oracle 数据库默认是手动提交事务

* 需要先开启事务,再提交

* 修改事务的默认提交方式:

* 查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交

* 修改默认提交方式: set @@autocommit = 0;

2. 事务的四大特征:

1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。

2. 持久性:当事务提交或回滚后,数据库会持久化的保存数据。

3. 隔离性:多个事务之间。相互独立。

4. 一致性:事务操作前后,数据总量不变

3. 事务的隔离级别(了解)

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

* 存在问题:

1. 脏读:一个事务,读取到另一个事务中没有提交的数据

2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。

3. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。

* 隔离级别:

1. read uncommitted:读未提交

* 产生的问题:脏读、不可重复读、幻读

2. read committed:读已提交 (Oracle)

* 产生的问题:不可重复读、幻读

3. repeatable read:可重复读 (MySQL默认)

* 产生的问题:幻读

4. serializable:串行化

* 可以解决所有的问题

* 注意:隔离级别从小到大安全性越来越高,但是效率越来越低

* 数据库查询隔离级别:

* select @@tx_isolation;

* 数据库设置隔离级别:

* set global transaction isolation level 级别字符串;

* 演示:

set global transaction isolation level read uncommitted;

start transaction;

-- 转账操作

update account set balance = balance - 500 where id = 1;

update account set balance = balance + 500 where id = 2;

DCL

* SQL分类:

1. DDL:操作数据库和表

2. DML:增删改表中数据

3. DQL:查询表中数据

4. DCL:管理用户,授权

* DBA:数据库管理员

* DCL:管理用户,授权

1. 管理用户

1. 添加用户:

* 语法:CREATE USER "用户名"@"主机名" IDENTIFIED BY "密码";

2. 删除用户:

* 语法:DROP USER "用户名"@"主机名";

3. 修改用户密码:

UPDATE USER SET PASSWORD = PASSWORD("新密码") WHERE USER = "用户名";

UPDATE USER SET PASSWORD = PASSWORD("abc") WHERE USER = "lisi";

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

SET PASSWORD FOR "root"@"localhost" = PASSWORD("123");

* mysql中忘记了root用户的密码?

1. cmd -- > net stop mysql 停止mysql服务

* 需要管理员运行该cmd

2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables

3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功

4. use mysql;

5. update user set password = password("你的新密码") where user = "root";

6. 关闭两个窗口

7. 打开任务管理器,手动结束mysqld.exe 的进程

8. 启动mysql服务

9. 使用新密码登录。

4. 查询用户:

-- 1. 切换到mysql数据库

USE myql;

-- 2. 查询user表

SELECT * FROM USER;

* 通配符: % 表示可以在任意主机使用用户登录数据库

2. 权限管理:

1. 查询权限:

-- 查询权限

SHOW GRANTS FOR "用户名"@"主机名";

SHOW GRANTS FOR "lisi"@"%";

2. 授予权限:

-- 授予权限

grant 权限列表 on 数据库名.表名 to "用户名"@"主机名";

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

GRANT ALL ON *.* TO "zhangsan"@"localhost";

3. 撤销权限:

-- 撤销权限:

revoke 权限列表 on 数据库名.表名 from "用户名"@"主机名";

REVOKE UPDATE ON db3.`account` FROM "lisi"@"%";

以上是 MySQL数据库基础回顾【详细】 的全部内容, 来源链接: utcz.com/z/535663.html

回到顶部