mysql 设置某个字段等于某个字段_MySQL基本使用

SQL简介

1.什么是SQL?

Structured Query Language : 结构化查询语言

定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。

2. SQL的通用语法

  • SQL 语句可以单行或多行书写,以分号结尾。

  • 可使用空格和缩进(Tab/4 x space)来增强语句的可读性。

  • MySQL 数据库的SQL语句不区分大小写,关键字建议使用大写。

SELECT * FROM student;
  • 注释

    单行注释:

       多行注释:

    /*    注释内容1    注释内容2    ...*/

    注意:单行注释时,需要将关键字 “--” 和 空格 连接 。建议在做单行注释的时候,使用通用的SQL注释。

  1. -- 注释内容
  2. # 注释内容   (MySQL特有的注释)

3. SQL 分类

  • DDL(Data Definition Language)数据定义语言

    用来定义数据库对象:数据库,表,列等。

    关键字:create,drop,alter

  • DML(Data Manipulation Language)数据操作语言

    用来对数据库中表的数据进行增删改查。

    关键字:insert,delete,update

  • DQL(Data Query Language)数据查询语言

    用来查询数据库中标的记录(数据)。

    关键字:select,where

  • DCL(Data Control Language)数据控制语言

    用来定义数据库的访问权限和安全级别,及创建用户。

    关键字:grant,revoke

DDL 操作数据库、表

1. 操作数据库:CRUD

  • C(Create):

    1. 创建数据库

    CREATE DATABASE DB1;

    2.   创建数据库前先判断是否存在

    CREATE DATABASE if not exists DB1;

    3. 创建db4数据库,判断是否存在,并指定字符集为gbk。

    create database if not exists db4 character set gbk;
  • R(Retrieve):查询

    1. 查询所有数据库名称:

    show databases;

    mysql 设置某个字段等于某个字段_MySQL基本使用_第1张图片

    2. 查询某个数据库的字符集:

    SHOW CREATE DATABASE 数据库名称;

    mysql 设置某个字段等于某个字段_MySQL基本使用_第2张图片

    查询某个数据库的创建语句

    3. 查询当前所使用的数据库名称

    select database();
  • U(Update):修改

    1. 修改数据库的字符集

    alter database 数据库名称 character set 字符集名称;
  • D(Delete):删除

    1. 删除数据库

    drop database 数据库名称;

    2. 删除数据库时判断当前指定的数据库是否存在

    drop database if exists 数据库名称;
  • 使用数据库

    use 数据库名称;

2. 操作数据表:CRUD

  • C(Create):

    创建一个表的基本语法

    create table 表名(列名1 数据类型1,    列名2 数据类型2,    ...    列名n 数据类型n);

    * 注意:最后一列不需要加逗号","

    数据库类型:

    int:整数类型

    double:小数类型

    date:日期类型,包含年月日  yyyy-MM-dd

    datatime:日期类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss

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

    * 如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值。

    varchar:字符串

    举个栗子

    CREATE TABLE student(    id INT,    name VARCHAR(32),    age INT,    score DOUBLE(4,1),    birthday DATE,    insert_time TIMESTAMP);

    复制表

    create table 新表名 like 目标表;-- 被复制的是STUDENT,新的表是 STUCREATE TABLE STU LIKE STUDENT;
  • R(Retrieve):

    查询某个数据库中所有的表名称

    show tables;

    查询表结构

    desc 表名;
  • U(Update):

    修改表的表名

    alter table 表名 rename to 新的表名;

    修改表的字符集

    -- 查看表的字符集show create table 表名;-- 修改表的字符集alter table 表名 character set 字符集名称;

    修改列名称 类型

    -- alter table 表名 change 旧列名 新列名 数据类型;alter table stu change gender sex varchar(20);-- alter table 表名 modify 列名 新数据类型;alter table stu modify sex varchar(10);

    添加一列

    alter table 表名 add 列名 数据类型;

    删除列

    alter table stu drop sex;
  • D(Delete):

    删除数据表

    drop table 表名;DROP TABLE STU;drop table if exists 表名;DROP TABLE IF EXISTS STU;

DML:增删改表中的数据

1. 添加数据

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);-- 如果为空的,用null代替insert into 表名 values(值1,值2,...,值n);  
  • 注意:

    • 列名与值一一对应

    • 如果表名后,不定义列名,则默认添加所有列的值

    • 除了数字类型,其他类型需要使用引号(单双均可)引起来

2. 删除数据

语法

delete from 表名 [where 条件];
  • 注意:

    • 如果不加条件,则删除表中所有记录。

清空表

delete from 表名; -- 不推荐使用,有多少条记录就会执行多少次删除操作TRUNCATE TABLE 表名; -- 推荐使用,效率更高。先删除表,然后再创建一张一样的表

3. 修改数据

语法

update 表名 set 列名1 = 值1 , 列名2 = 值2 ,... [where 条件];
  • 注意:

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

DQL:查询表中的记录

最简单的查询

select * from 表名;

1. 语法

select 字段列表from 表名列表where 条件列表group by分组字段having分组之后的条件order by排序limit 分页

2. 基础查询

  • 多个字段的查询

    select 字段名1,字段名2... from 表名;# 查询 姓名 和 年龄SELECT   NAME, -- 姓名  age   -- 年龄FROM  student ; -- 学生表
    • 如果查询所有字段,则可以使用 * 来代替字段列表。

    • 注意:

  • 去除重复

    select distinct * from 表名;-- 去除重复结果集SELECT DISTINCT address FROM student;
  • 计算列

    一般可以使用四则运算计算一些列的值。(一般只会进行数值型得到计算)

    ifnull(表达式1,表达式2): -- nul参与的运算,计算结果都为null    /*        表达式1:哪个字段需要判断是否为null        如果该字段为null后的替换值    */SELECT NAME,math,english,math+english FROM student;SELECT NAME,math,english,math+ IFNULL(english,0) FROM student;
  • 起别名

    as   -- as可以省略-- 起别名SELECT NAME,math,english,math+ IFNULL(english,0) AS 总分 FROM student;SELECT NAME 姓名,math 数学,english 英语,math+ IFNULL(english,0) 总分 FROM student;

3. 条件查询

  • where 子句后跟条件

  • 运算符

    • SELECT * FROM student WHERE english IS NOT NULL;
    • -- 查询年龄22岁,19岁,25岁的信息SELECT * FROM student WHERE age = 19 OR age = 22 OR age = 25;
    • -- 查询年龄大于等于20, 小于等于30SELECT * FROM student WHERE age >=20 AND age <=30;-- 不推荐使用SELECT * FROM student WHERE age >=20 && age <=30;
    • -- 查询英语成绩为nullSELECT * FROM student WHERE english IS NULL;
    • 占位符:

    • 例子:

    • _ : 单个任意字符

    • %: 多个任意字符

    • -- 查询姓马的有哪些?SELECT * FROM student WHERE NAME LIKE '马%';
    • -- 查询姓名第二个字是化的人SELECT * FROM student WHERE NAME LIKE '_化%';
    • -- 查询姓名是三个字的人SELECT * FROM student WHERE NAME LIKE '___';
    • -- 查询姓名中包含德的人SELECT * FROM student WHERE NAME LIKE '%德%';
    • -- 查询年龄大于等于20, 小于等于30  即:20~30之间SELECT * FROM student WHERE age BETWEEN 20 AND 30;
    • -- 查询年龄大于20的学生select * from student where age > 20;
    • -- 查询年龄不小于20的学生select * from student where age >= 20;
    • -- 查询年龄等于20的学生select * from student where age = 20;
    • -- 查询年龄不等于20的学生  != 属于mysql的方言select * from student where age != 20
    • -- 查询年龄不等于20的学生   <>  表示不等于SELECT * FROM student WHERE age <> 20;
    • -- 查询年龄大于等于20, 小于等于30SELECT * FROM student WHERE age >=20 AND age <=30;-- 不推荐使用SELECT * FROM student WHERE age >=20 && age <=30;
    • >、=、=、<>

    • BETWEEN...AND

    • IN

    • LIKE

    • IS NULL

    • and或&&

    • or ||

    • not !  

4. 排序查询

  • 语法:

    • order by 子句
    • order by 排序字段1 排序方式1, 排序字段2 排序方式2,...
  • 排序方式:

    • ASC  --  升序,默认的
    • DESC  -- 降序
  • 例子:

    • # 按照数学成绩排名,如果教学成绩一样,则按照英语成绩排名SELECT * FROM student ORDER BY math ASC,english ASC;

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

5.聚合函数

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

  • count:计算个数

    • 一般选择非空的列:主键

    • count(*)

  • max:计算最大值

    • MAX(列名)

  • min:计算最小值

    • MIN(列名)

  • sum:计算和

    • SUM(列名) 计算整个列的和

  • avg:计算平均值

    聚合函数的计算,排除null值。

    解决方案:

    • AVG(列名)计算该列的平均值

  1. 选择不包含非空的列进行计算

  2. IFNULL函数

6. 分组查询

  • 语法:

    • group by 分组字段;

      注意:

    • 案例:

      -- 按照性别分组,分别查询男、女同学的平均分SELECT sex,AVG(math) FROM student GROUP BY sex;-- 按照性别分组,分别查询男、女同学的平均分,分别的人数SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;-- 按照性别分组,分别查询男、女同学的平均分,分别的人数  要求:分数低于70分的人,不参与分组SELECT sex,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;-- 按照性别分组,分别查询男、女同学的平均分,分别的人数  要求:分数低于70分的人,不参与分组,分组之后, 人数要大于2个人SELECT sex,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;SELECT sex,AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
  1. 分组之后查询的字段:分组字段、聚合函数

  2. 在where和having的区别?

  1. where 在分组之前进行限定,如果不满足条件,则不参与分组。having 在分组之后进行限定,如果不满足结果,则不会被查询出来

  2. where 后不可以跟聚合函数,having 可以进行聚合函数的判断。

7. 分页查询

  • 语法:limit 参数1,参数2     | 参数1 开始的索引,参数2 每页查询的条数;

    • -- 每页显示3条记录select * from student limit 0,3;-- 第1页select * from student limit 3,3;-- 第2页
    • 分页公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数

    • Limit 是一个MySQL的“方言”

约束

1. 概念

对表中的数据进行限定,保证数据的正确性、有效性和完整性。

2. 分类

  • 主键约束:primary key

  • 非空约束:not null

  • 唯一约束:unique

  • 外键约束:foreign key

3. 非空约束

  1. 创建表时添加非空约束

    CREATE TABLE stu (  id INT,  NAME VARCHAR (20) NOT NULL-- name 为非空) ;
  2. 创建表完后,添加非空约束

    alter table stu modify name varchar(20) not null;
  3. 删除name的非空约束

    alter table stu modify name varchar(20);

4.唯一约束

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

    CREATE TABLE stu (  id INT,  phone_number VARCHAR(20) UNIQUE  -- 手机号不能重复,添加了唯一约束) ;
  2. 删除唯一约束

    ALTER TABLE stu DROP INDEX phone_number;  -- 唯一约束也称为  唯一索引
  3. 在创建表后,添加唯一约束

    ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
  • mysql 中,唯一约束限定的列的值可以有多个null

5. 主键约束

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

    CREATE TABLE stu (  id INT PRIMARY KEY,  -- 给id添加主键约束  NAME VARCHAR(20)) ;
  2. 删除主键

    -- 错误  alter table stu modify id int;
    alter table stu drop primary key; -- 表中只有一个主键,所以不用指定
  3. 创建完表后,添加主键

    alter table stu modify id int primary key;
  4. 自动增长:(一般联合主键使用)

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

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

      CREATE TABLE stu (    id INT PRIMARY KEY AUTO_INCREMENT,  -- 给id添加主键约束并完成主键自增长    NAME VARCHAR(20));
    3. 删除自动增长

      alter table stu modify id int;
    4. 添加自动增长

      alter table stu modify id int auto_increment;

      自动增长只关注上一条记录的值

注意:

  1. 非空且唯一

  2. 一张表只能有一个字段为主键

  3. 一张表中记录的唯一标识

6. 外键约束

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

    create table 表名(...    外键列    constraint 外键名称 foreign key 外键列的名称 references 主表名称(主表列名称));
    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));
  2. 删除外键

    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;-- 删除外键ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
  3. 添加外键

    -- 添加外键ALTER TABLE employee ADD constraint 外键名称 foreign key 外键列的名称 references 主表名称(主表列名称)ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
  4. 级联操作

    级联操作可以单独使用也可以多个同时使用;

    1. 级联更新:ON UPDATE CASCADE

    2. 级联删除:ON DELETE CASCADE

    3. 添加级联操作

      ALTER TABLE employee ADD constraint 外键名称 foreign key 外键列的名称 references 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
    4. 分类

数据库的设计

多表之间的关系

分类

  • 一对一

    • 例如:人与身份证的关系

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

  • 一对多(多对一)

    • 例如:部门与员工的关系

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

  • 多对多

    • 例如:学生与课程的关系

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

实现关系

  • 一对多(多对一)

    • 例如:部门与员工

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

  • 多对多

    • 例如:学生与课程

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

  • 一对一

    • 例如:人与身份证

    • 实现方式:可以在任意一方添加唯一外键(unique foreign key)指向另一方的主键。

一般情况下,一对一都整合为一张表

数据库设计的范式

  1. 概念:设计数据库时,需要遵循的一些规范。

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

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

  2. 分类

    1. 第一范式(1NF):

      数据库表的每一列都是不可分割的原子数据项,不能是集合、数组等非原子数据项。即表中的某个列有多个值时,必须拆分为不同的列。简而言之,第一范式每一列不可再拆分,称为原子性。

    2. 第二范式(2NF):

      在满足第一范式的前提下,表中的每一个字段都完全依赖于主键。

      所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之,第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候,才会发生不符合第二范式的情况。

    3. 第三范式(3NF):

      在满足第二范式的前提下,表中的每一列都直接依赖于主键,而不是通过其它的列来间接依赖于主键。

      简而言之,第三范式就是所有列不依赖于其它非主键列,也就是在满足 2NF 的基础上,任何非主列不得传递依赖于主键。

      所谓传递依赖,指的是如果存在"A → B → C"的决定关系,则 C 传递依赖于 A。因此,满足第三范式的数据库表应该不存在如下依赖关系:主键列 → 非主键列 x → 非主键列 y

数据库的备份和还原

1. 命令行方式

  • 语法:

    • 登录数据库

    • 创建数据库

    • 使用数据库

    • 执行文件

    • mysql -u用户名 -p密码
    • mysql -uroot -proot
    • create database 数据库名称;
    • create database db1;
    • use 数据库名称;
    • use db1;
    • source 文件路径;
    • source d://db1.sql;
    • 备份:

      mysqldump -u用户名 -p密码 数据库名称 > 保存路径mysqldump -uroot -proot db1 > d://db1.sql
    • 还原:

2. 图形化工具(SQLyog)

  1. 备份

    mysql 设置某个字段等于某个字段_MySQL基本使用_第3张图片

    mysql 设置某个字段等于某个字段_MySQL基本使用_第4张图片

  2. 还原

mysql 设置某个字段等于某个字段_MySQL基本使用_第5张图片

mysql 设置某个字段等于某个字段_MySQL基本使用_第6张图片

多表查询

笛卡尔积

  • 有两个集合A,B,去这两个集合的所有组合情况,被称为笛卡尔积。

  • 而多表查询,需要消除无用的数据。

多表查询的分类

  1. 内连接查询

    1. 从哪些表中查询数据

    2. 条件是什么

    3. 查询哪些字段

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

      SELECT   e.`NAME`,  e.`gender`,  d.`NAME` FROM  emp e,  dept dWHERE e.`dept_id` = d.`id` 
    5. 显式内连接

      SELECT   e.`NAME`,e.`gender`,d.`NAME`FROM   emp e INNER JOIN dept d ON e.`dept_id` = d.`id`;  SELECT   e.`NAME`,e.`gender`,d.`NAME`FROM   emp e JOIN dept d ON e.`dept_id` = d.`id`;  
    6. 内连接查询

  2. 外连接查询

    1. 语法:

      select 字段列表 from 表1 right [outer] join 表2 on 条件;
    2. 查询的是右表所有数据以及其交集部分。

    3. 语法:

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

      outer 可以省略

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

    5. 左外连接

    6. 右外连接

  3. 子查询

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

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

    3. 子查询的结果是多行多列的

    4. 子查询可以作为条件,使用运算符去判断

      -- 查询员工工资小于平均工资的人SELECT * FROM emp e WHERE e.`salary` < (SELECT AVG(salary) FROM emp);
    5. 子查询可以作为条件,使用运算符 IN 来判断

      -- 查询财务部和市场部所有的员工信息SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN ('财务部','市场部'));
    6. 子查询可以作为一张虚拟表参与查询

      -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息-- 子查询SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2WHERE t1.`id` = t2.dept_id;-- 普通内连接SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11';
    7. 概念:

      查询中嵌套查询,称为嵌套查询为子查询。

    8. 例子:

      -- 查询工资最高的员工信息-- 1. 查询最高的工资是多少  9000SELECT MAX(salary) FROM emp;-- 2. 查询员工信息,并且工资等于9000的SELECT * FROM emp WHERE salary = 9000;-- 一步到胃,一条sql就完成这个操作SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
    9. 子查询的不同情况

  4. 练习:

    -- 部门表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.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述-- 普通查询SELECT   e.`id`, -- 员工编号  e.`ename`, -- 员工姓名  e.`salary`, -- 工资  j.`jname`, -- 职务名称  j.`description` -- 职务描述FROM   emp e,job jWHERE   e.`job_id` = j.`id`;-- 左查询SELECT   e.`id`,e.`ename`,e.`salary`,j.`jname`,j.`description`FROM emp e JOIN job j ON e.`job_id` = j.`id`;  -- 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置SELECT   e.`id`, -- 员工编号  e.`ename`, -- 员工姓名  e.`salary`, -- 工资  j.`jname`, -- 职务名称  j.`description`, -- 职务描述  d.`dname`, -- 部门名称  d.`loc` -- 部门位置FROM   emp e,job j,dept dWHERE   e.`dept_id` = d.`id`  AND e.`job_id` = j.`id`;  -- 3.查询员工姓名,工资,工资等级SELECT   e.`ename`,  e.`salary`,  s.`grade`FROM  emp e,salarygrade sWHERE   e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;-- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级SELECT  e.`ename`,e.`salary`,j.`jname`,j.`description`,d.`dname`,d.`loc`,s.`grade`FROM  emp e,job j,dept d,salarygrade sWHERE  e.`dept_id` = d.`id`  AND e.`job_id` = j.`id`  AND e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;-- 5.查询出部门编号、部门名称、部门位置、部门人数 SELECT   d.`id`,d.`dname`,d.`loc`,COUNT(e.`id`) `部门人数` FROM   deptd,emp e WHERE    d.`id` = e.`dept_id` GROUP BY   d.`id`;  SELECT   t1.`id`,t1.`dname`,t1.`loc`,t2.totalFROM   dept t1,  (SELECT      dept_id,COUNT(id) total   FROM      emp   GROUP BY dept_id) t2  WHERE t1.`id` = t2.`dept_id`;  -- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询SELECT  t1.`ename`,t1.`mgr`,t2.`id`,t2.`ename`FROM   emp t1 LEFT JOIN emp t2 ON t1.`mgr` = t2.`id`

事务

事务基本介绍

  • 概念

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

  • 操作

  1. 开启事务:start transaction;

  2. 回滚:rollback;

  3. 提交:commit;

演示:

-- 张三给李四转账 500 元START TRANSACTION;-- 1. 张三账户 -500UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';-- 出错啦。。。-- 发现问题,回滚事务ROLLBACK;-- 2. 李四账户 + 500UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';-- 发现没有问题,提交事务COMMIT;

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

  • 查看事务的默认提交方式

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

    set @@autocommit = 0;
  • 自动提交

  • 手动提交

  • 一条DML(增删改)语句会自动提交一次

  • MySQL就是自动提交的

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

  • 需要先开启事务,再进行提交

  • 事务提交两种方式:

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

事务的四大特征

1. 原子性

不可分割的最小操作单位,要么同时成功,要么同时失败。

2. 持久性

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

3. 隔离性

多个事务之间,相互独立

4. 一致性

事务操作前后,数据总量不变。

事务的隔离级别

概念

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

存在的问题

  • 脏读

  • 不可重复读

  • 幻读

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

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

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

隔离级别

  1. read uncommitted

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

read committed(Oracle 默认的)

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

repeatable read(MySQL默认的)

  • 产生的问题:幻读

serializable

  • 可以解决所有的问题

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

  • 数据库查询隔离级别:

    • select @@tx_isolation;

  • 设置事务隔离级别

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

DCL

1. 管理用户

1.1. 添加用户

create user '用户名'@'主机名' identified by '密码'CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';-- '%' 代表通配符,表示任意位置的主机可以使用用户CREATE USER 'lisi'@'%' IDENTIFIED BY '123456';

1.2. 删除用户

drop user '用户名'@'主机名';-- 删除张三DROP USER 'zhangsan'@'localhost';

1.3. 修改用户密码

-- 修改lisi用户密码为 abc  -- mysql  5.7后的方式UPDATE USER SET authentication_string = PASSWORD('新密码') WHERE USER = '用户名';FLUSH PRIVILEGES;-- mysql 5.5 版本UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';-- 通用方式SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
  • mysql中忘记了root用户的密码的解决方案

  1. 找到my.ini配置文件:C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

  2. [mysqld]一栏中添加skip-grant-tables

  1. cmd -- > net stop mysql57 停止mysql服务  需要以管理员身份运行cmd

  2. 使用无验证方式启动mysql服务

  3. cmd -- > net start mysql57 启动mysql服务  需要以管理员身份运行cmd

  4. 输入mysql 即可免密登陆,然后修改密码

    update user set authentication_string = password('新密码') where user = 'root';flush privileges;  -- 类似commit的效果

1.4. 查询用户

-- 1. 切换到mysql数据库USE mysql;-- 2. 查询user表SELECT * FROM USER;

2. 授权

2.1. 查询权限

-- 查询权限SHOW GRANTS FOR '用户名'@'主机地址';SHOW GRANTS FOR 'root'@'localhost';SHOW GRANTS FOR 'lisi'@'%';

2.2. 授予权限

-- 授予权限GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机地址';GRANT SELECT ON db3.account TO 'lisi'@'%';GRANT UPDATE ON db3.account TO 'lisi'@'%';-- 给张三用户授予所有权限,在任意数据库任意表上GRANT ALL ON *.* TO 'zhangsan'@'localhost';

2.3. 撤销权限

-- 撤销权限REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';REVOKE UPDATE ON db3.account FROM 'lisi'@'%';

配置MySQL的默认字符集

  • WIN10

1. 找到location

C:\ProgramData\MySQL\MySQL Server 5.7

2.找到 my.ini 文件

备份my.inimy-backup.ini

3. 添加配置

[client]default-character-set=utf8[mysql]default-character-set=utf8[mysqld]character-set-server=utf8

重启服务器,mysql service restart;

或者:

服务——>MySQLx.x.x ——> 重启动

你可能感兴趣的