MySQL

最新推荐文章于 2022-06-26 21:38:42 发布

ersheng_

最新推荐文章于 2022-06-26 21:38:42 发布

阅读量457

点赞数

文章标签： java

本文链接：https://blog.csdn.net/ersheng_/article/details/120700602

版权

前言

知识无底，学海无涯

一、SQL简述

1.SQL的概述

Structure Query Language(结构化查询语言)简称SQL，它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库；定义和操作数据，维护数据的完整性和安全性。

2.SQL的优点

1、简单易学，具有很强的操作性 2、绝大多数重要的数据库管理系统均支持SQL 3、高度非过程化；用SQL操作数据库时大部分的工作由DBMS自动完成

3.SQL的分类

1、DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等；常用语句：CREATE、 ALTER、DROP 2、DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE 3、DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别；常用语句：GRANT、DENY 4、DQL(Data Query Language) 数据查询语言，用来查询数据常用语句：SELECT

二、数据库的三大范式

1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。

2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。

如果表是单主键，那么主键以外的列必须完全依赖于主键；

如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。

3、第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。

表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。

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

        设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
        目前关系数据库有六种范式：第一范式（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基础上消除传递依赖）

三、数据库的数据类型

使用MySQL数据库存储数据时，不同的数据类型决定了 MySQL存储数据方式的不同。为此，MySQL数据库提供了多种数据类型，其中包括整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型、二进制…等等数据类型。

1.整数类型

根据数值取值范围的不同MySQL 中的整数类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的，

数据类型字节数无符号数的取值范围有符号数的取值范围 TINYINT 1 0~255 -128~127 SMALLINT 2 0~65535 -32768~32768 MEDIUMINT 3 0~16777215 -8388608~8388608 INT 4 0~4294967295 -2147483648~ 2147483648 BIGINT 8 0~18446744073709551615 -9223372036854775808~9223372036854775808

2.浮点数类型和定点数类型

在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。下图列举了 MySQL中浮点数和定点数类型所对应的字节大小及其取值范围：

数据类型字节数有符号的取值范围无符号的取值范围 FLOAT 4 -3.402823466E+38~-1.175494351E-38 0和1.175494351E-38~3.402823466E+38 DOUBLE 8 -1.7976931348623157E+308~2.2250738585072014E-308 0和2.2250738585072014E-308~1.7976931348623157E+308 DECIMAL（M,D） M+2 -1.7976931348623157E+308~2.2250738585072014E-308 0和2.2250738585072014E-308~1.7976931348623157E+308 从上图中可以看出：DECIMAL类型的取值范围与DOUBLE类型相同。但是，请注意：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(6,2)的数据6.5243 插人数据库后显示的结果为6.52

3.字符串类型

在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串。当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1

插入值 CHAR(3) 存储需求 VARCHAR(3) 存储需求 ‘’ ‘’ 3个字节 ‘’ 1个字节 ‘a’ ‘a’ 3个字节 ‘a’ 2个字节 ‘ab’ ‘ab’ 3个字节 ‘ab’ 3个字节 ‘abc’ ‘ab’ 3个字节 ‘abc’ 4个字节 ‘abcd’ ‘ab’ 3个字节 ‘abc’ 4字节

4.d字符串类型

文本类型用于表示大文本数据，例如，文章内容、评论、详情等，它的类型分为如下4种：

数据类型储存范围 TINYTEXT 0~255字节 TEXT 0~65535字节 MEDIUMTEXT 0~16777215字节 LONGTEXT 0~4294967295字节

5.日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值：

数据类型字节数取值范围日期格式零值 YEAR 1 1901~2155 YYYY 0000 DATE 4 1000-01-01~9999-12-31 YYYY-MM-DD 0000-00-00 TIME 3 -838：59：59~ 838：59：59 HH:MM:SS 00:00:00 DATETIME 8 1000-01-01 00:00:00~9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 0000-00-00 00:00:00 TIMESTAMP 4 1970-01-01 00:00:01~2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 0000-00-00 00:00:00

5.1 YEAR类型

YEAR类型用于表示年份，在MySQL中，可以使用以下三种格式指定YEAR类型的值。 1、使用4位字符串或数字表示，范围为’1901’—'2155’或1901—2155。例如，输人 ‘2019’或2019插人到数据库中的值均为2019。 2、使用两位字符串表示，范围为’00’—‘99’。其中，‘00’—'69’范围的值会被转换为 2000—2069范围的YEAR值，‘70’—'99’范围的值会被转换为1970—1999范围的YEAR 值。例如，输人’19’插人到数据库中的值为2019。 3、使用两位数字表示，范围为1—99。其中，1—69范围的值会被转换为2001— 2069范围的YEAR值，70—99范围的值会被转换为1970—1999范围的YEAR值。例如，输人19插入到数据库中的值为2019。请注意：当使用YEAR类型时，一定要区分’0’和0。因为字符串格式的’0’表示的YEAR值是2000而数字格式的0表示的YEAR值是0000。

5.2 TIME类型

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS，其中，HH表示小时， MM表示分,SS表示秒。在MySQL中，可以使用以下3种格式指定TIME类型的值。 1、以’D HH:MM:SS’字符串格式表示。其中，D表示日可取0—34之间的值, 插人数据时，小时的值等于(DX24+HH)。例如，输入’2 11:30:50’插人数据库中的日期为59:30:50。 2、以’HHMMSS’字符串格式或者HHMMSS数字格式表示。例如，输人’115454’或115454,插入数据库中的日期为11:54:54 3、使用CURRENT_TIME或NOW()输人当前系统时间。

5.3 DATETIME类型

DATETIME类型用于表示日期和时间，它的显示形式为’YYYY-MM-DD HH: MM:SS’，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS 表示秒。在MySQL中，可以使用以下4种格式指定DATETIME类型的值。 以’YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式表示的日期和时间，取值范围为’1000-01-01 00:00:00’—‘9999-12-3 23:59:59’。例如，输人’2019-01-22 09:01:23’或 ‘20140122_0_90123’插人数据库中的 DATETIME 值都为 2019-01-22 09:01:23。 1、以’YY-MM-DD HH:MM:SS’或者’YYMMDDHHMMSS’字符串格式表示的日期和时间，其中YY表示年，取值范围为’00’—‘99’。与DATE类型中的YY相同，‘00’— '69’范围的值会被转换为2000—2069范围的值，‘70’—'99’范围的值会被转换为1970—1999范围的值。 2、以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。例如，插入20190122090123或者190122090123,插人数据库中的DATETIME值都为 2019-01-22 09:01:23。 3、使用NOW()来输人当前系统的日期和时间。

5.4 TIMESTAMP类型

TIMESTAMP类型用于表示日期和时间，它的显示形式与DATETIME相同但取值范围比DATETIME小。在此，介绍几种TIMESTAMP类型与DATATIME类型不同的形式： 1、使用CURRENT_TIMESTAMP输人系统当前日期和时间。 2、无任何输人或输人NULL时系统会输人系统当前日期和时间。

6.二进制类型

在MySQL中常用BLOB存储二进制类型的数据，例如：图片、PDF文档等。BLOB类型分为如下四种：

数据类型储存范围 TINYBLOB 0~255字节 BLOB 0~65535字节 MEDIUMBLOB 0~16777215字节 LONGBLOB 0~4294967295字节

五、数据表的约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下：

约束条件说明 PRIMARY KEY 主键约束用于唯一标识对应的记录 FOREIGN KEY 外键约束 NOT NULL 非空约束 UNIQUE 唯一性约束 DEFAULT 默认值约束，用于设置字段的默认值

以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。


ALTER TABLE student5 DROP PRIMARY KEY; ALTER TABLE student5 MODIFY id INT PRIMARY KEY; ALTER TABLE student5 MODIFY id INT; ALTER TABLE student5 MODIFY id int AUTO_INCREMENT;	主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。语法：字段名数据类型 primary key;	主键约束PRImary key
ALTER TABLE student5 DROP INDEX idname; ALTER TABLE student5 MODIFY idname VARCHAR(20) UNIQUE;	唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现，语法：字段名数据类型 UNIQUE;	唯一性约束UNIQUE
ALTER TABLE student5 MODIFY name VARCHAR(20); ALTER TABLE student5 MODIFY name VARCHAR(20) NOT NULL;	非空约束即 NOT NULL指的是字段的值不能为空，基本的语法格式如下所示：语法：字段名数据类型 NOT NULL;	非空约束即 NOT NULL
ALTER TABLE student5 MODIFY sex VARCHAR(20); ALTER TABLE student5 MODIFY sex VARCHAR(20) default '男';	默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；语法：字段名数据类型 DEFAULT 默认值；	默认值约束即DEFAULT
ALTER TABLE 从表ADD constraint 外键名 foreign key (从表字段) references 主表(id); ALTER TABLE 从表 DROP FOREIGN KEY 外键名 ; 级联操作:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;	外键约束即FOREIGN KEY常用于多张表之间的约束。语法如下： -- 在创建数据表时语法如下： CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表(主键字段) -- 将创建数据表创号后语法如下： ALTER TABLE 从表名 ADD 【CONSTRAINT 外键名】 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);	外键约束即FOREIGN KEY

1.主键约束PRI

主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。

语法：

字段名数据类型 primary key;

设置主键约束(primary key)的**第一种方式**

create table student(
id int **primary key,**
name varchar(20)
);

设置主键约束(primary key)的**第二种方式**

create table student01(
id int
name varchar(20),
**primary key(id)**
);

alert table student2 modlfy name varchar(20) primary key;
ALTER TABLE stu DROP PRIMARY KEY;

删除主键之前必须删除自增长；因为自增长必须定义在键上面；

2.非空约束

非空约束即 NOT NULL指的是字段的值不能为空，基本的语法格式如下所示：

字段名数据类型 NOT NULL;

create table student02(
id int
name varchar(20) **not null**
);

alert table student2 modlfy name varchar(20) not null;
alert table student2 modlfy name varchar(20);

ALTER TABLE employee MODIFY sex VARCHAR(10) not null DEFAULT 'male';

3.默认值约束DEF

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；其基本的语法格式如下所示：

字段名数据类型 DEFAULT 默认值；

create table student03(
id int,
name varchar(20),
gender varchar(10) **default 'male'**
);

alert table student2 modlfy name varchar(20) default 'male';
alert table student2 modlfy name varchar(20);

4.唯一性约束UNI

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现，其基本的语法格式如下所示：

字段名数据类型 UNIQUE;

create table student04(
id int,
name varchar(20) **unique**
);

alert table student2 modlfy name varchar(20) unique;
alert table stu drop index id;

5.外键约束**

外键约束即FOREIGN KEY常用于多张表之间的约束。基本语法如下：

定义外键，给从表定义的（字段），绑定到（主表字段）；

一主表，多从表；

主外键编码不一致需要加：

ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 在创建数据表时语法如下： CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表(主键字段)

-- 将创建数据表创号后语法如下： ALTER TABLE 从表名 ADD 【CONSTRAINT 外键名】 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

create table student05(
id int primary key,
name varchar(20)
);

create table class(
classid int primary key,
studentid int
);

示例：学生表作为主表，班级表作为副表设置外键， MySQL命令：
alter table class add 【constraint fk_class_studentid】 
            foreign key(studentid) references student05(id);

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

============================
外键之上级联操作（了解，最好不用）
ALTER TABLE 表名 ADD 【CONSTRAINT 外键名称】 
                    FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
1. 级联更新：ON UPDATE CASCADE 
2. 级联删除：ON DELETE CASCADE

* 外键约束：foreign key,让表于表产生关系，从而保证数据的正确性。
    1. 在创建表时，可以添加外键
        * 语法：
            create table 表名(
                ....
                外键列
                constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
            );

    2. 删除外键
        ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

    3. 创建表之后，添加外键
        ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
        -- 准备数据
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);

-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT -- 外键对应主表的主键
-- 创建外键约束
-- constraint emp_depid_fk foreign key (dep_id) references department(id)
)
-- 添加2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
4. 级联操作（了解）
        1. 添加级联操作
            语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
                    FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
        2. 分类：
            1. 级联更新：ON UPDATE CASCADE 
            2. 级联删除：ON DELETE CASCADE

5.1 数据一致性概念

建立外键是为了保证数据的完整和统一性。

如果主表中的数据被删除或修改

从表中对应的数据也应该被删除或修改

5.2 删除外键

alter table 从表名 drop foreign key 外键名；

alter table class drop foreign key fk_class_studentid;

5.3 关于外键约束需要注意的细节

1、从表里的外键通常为主表的主键 2、从表里外键的数据类型必须与主表中主键的数据类型一致 3、主表发生变化时应注意主表与从表的数据一致性问题

自动增长:(只能定义键)

概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

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

create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);
ALTER TABLE stu MODIFY id INT;
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

SQL

1.什么是SQL？
	Structured Query Language：结构化查询语言
	其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。
	
2.SQL通用语法
	1. SQL 语句可以单行或多行书写，以分号结尾。
	2. 可使用空格和缩进来增强语句的可读性。
	3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
	4. 3 种注释
		* 单行注释: -- 注释内容 或 # 注释内容(mysql 特有) 
		* 多行注释: /* 注释 */
	
3. SQL分类
	1) DDL(Data Definition Language)数据定义语言
		用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
	2) DML(Data Manipulation Language)数据操作语言
		用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
	3) DQL(Data Query Language)数据查询语言
		用来查询数据库中表的记录(数据)。关键字：select, where 等
	4) DCL(Data Control Language)数据控制语言(了解)
		用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

DDL

1.数据库的基本操作

MySQL安装完成后，要想将数据存储到数据库的表中，首先要创建一个数据库。创建数据库就是在数据库系统中划分一块空间存储数据，语法如下：

create database 数据库名称;

创建一个叫db1的数据库MySQL命令：

语法	功能
use db1;	切换数据库

create database db1;	创建一个叫db1的数据库
CREATE DATABASE java_20 CHARACTER SET utf8 COLLATE utf8_general_ci;
drop database db1;	删除数据库

alter database db1 character set gbk;	将数据库的字符集修改为gbk

show create database db1;	查看该数据库基本信息
show databases;	查询出MySQL中所有的数据库

select database();	查看当前使用的数据库

2.数据表的基本操作

数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意：在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作，否则会抛出“No database selected”错误。语法如下：

create table 表名( 字段1 字段类型 COMMENT '编号', 字段n 字段类型 );

字段说明：

COMMENT '编号'

语法		功能
create table student();		创建数据表
COMMENT '编号'
alter table stu drop address;		删除字段
alert table student2 dropprimary key;		删除约束键
drop table 表名;		删除数据表

alter table stu add address varchar(50);		增加字段
alter table student rename to stu;		修改表名
alter table stu change name sname varchar(10);		修改字段名&数据类型
alter table stu modify sname int;		修改字段数据类型
alert table student2 modlfy name varchar(20) primary key;		约束（可覆盖减去不是键的约束）

show tables;		查看所有数据表
show create table student;		查表的基本信息创表语句
desc student;		查看表字段信息
select * from stu；		查看表内容信息

DML

六、insert数据表插入数据

示例	语法	功能
insert into student values(1,'bob',16,'male');	INSERT INTO 表名 VALUES (值 1,值 2,...);	为表中所有字段插入数据
insert into** student (id,name,age,gender) values (1,'bob',16,'male');	INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);	为表中指定字段插入数据
insert into student (id,name,age,gender) values (2,'lucy',17,'female'),(3,'jack',19,'male');	INSERT INTO 表名 [(字段名1,字段名2,...)] VALUES (值 1,值 2,…),(值 1,值 2,…),...;	同时插入多条记录

alter table employees add 总工资 double(8,2); insert into employees select *,salary+IFNULL(bonus,0) 总工资 from employee;	新空表多个字段，添加查询字段	#添加算数后的值到新表

七、update更新数据

示例	语法	功能
update student set age=18;	UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];	UPDATE更新全部数据
update student set age=20,gender='female' where name='tom';		UPDATE更新部分数据

alter table employee add 总工资 double(8,2); update employee set 总工资=salary+IFNULL(bonus,0);	新增字段，更新字段	#添加算数后的值到表

八、delete删除数据

示例	语法	功能
delete from student where age=14;	DELETE FROM 表名 [WHERE 条件表达式];	DELETE删除部分数据
delete from student;		DELETE删除全部数据
TRUNCATE TABLE student	TRUNCATE TABLE 表名	TRUNCATE删除表，新建无数据表

TRUNCATE和DETELE的区别

TRUNCATE和DETELE都能实现删除表中的所有数据的功能，但两者也是有区别的： 1、DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录。 2、使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1 3、DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句

DQL

九、MySQL数据表简单查询

select => from => where => group by => having => order by => limit

1.简单查询概述

简单查询即不含where的select语句。在此，我们讲解简单查询中最常用的两种查询：查询所有字段和查询指定字段。在此，先准备测试数据，代码如下：

示例语法	功能
select * from student;	查询所有字段
select sid,sname from student;	查询指定字段
select sid,sname,'2021-03-02' from student;	常数的查询日期标记
select distinct gender from student;	从查询结果中过滤重复数据
select sname,age+10 from student;	算术运算符10年后的年龄
select ，salary+bonus 总工资 from* 表名；	多查一列所需数据

别名

表名 [AS] 表的别名

查询用到的表为表别名

SELECT 字段名1 [AS] 别名1，

查询内容的字段名为字段别名

SELECT 别名.本表字段，别名.本表字段 from emp e，dept d

select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;

十、函数

1.聚合函数

在开发中，我们常常有类似的需求：统计某个字段的最大值、最小值、平均值等等。为此，MySQL中提供了聚合函数来实现这些功能。所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。聚合函数使用规则： 只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。

查询 having 分组

例如，在WHERE子句中使用聚合函数是错误的。

接下来，我们学习常用聚合函数。

示例语法	功能	功能注意
select count(*) from student;\|\|(DISTINCT expr,[expr...])	查询有多少该表中有多少人count（去重 X，X）	统计表中数据的行数或者统计指定列其值不为NULL的数据个数
select max(age) from student;	查询该学生表中年纪最大的学生max（）	计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算
select sname,min(age) from student;	查询该学生表中年纪最小的学生min（）
select sum(age) from student;	查询该学生表中年纪的总和sum（）	计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0
select avg(age) from student;	查询该学生表中年纪的平均数avg（）
select avg(ifnull(age,0)） from student;	查询该学生表中age为null时改为0参与运算	判断空值改为0
IS NULL为空	IS NOT NULL不为空
DISTINCT 字段		去重

2.其他常用函数

这里我就不一一举例了，基本混个眼熟，以后用到再细说

2.1、时间函数

SELECT NOW(); SELECT DAY (NOW()); SELECT DATE (NOW()); SELECT TIME (NOW()); SELECT YEAR (NOW()); SELECT MONTH (NOW()); SELECT CURRENT_DATE(); SELECT CURRENT_TIME(); SELECT CURRENT_TIMESTAMP(); SELECT ADDTIME('14:23:12','01:02:01'); SELECT DATE_ADD(NOW(),INTERVAL 1 DAY); SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH); SELECT DATE_SUB(NOW(),INTERVAL 1 DAY); SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH); SELECT DATEDIFF('2019-07-22','2019-05-05');

2.2、字符串函数

--连接函数

SELECT CONCAT ()

SELECT INSTR (); --统计长度 SELECT LENGTH();

2.3、数学函数

-- 绝对值 SELECT ABS(-136); -- 向下取整 SELECT FLOOR(3.14); -- 向上取整 SELECT CEILING(3.14);

十一、条件查询

数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过WHERE子句指定查询条件对查询结果进行过滤。 having 分组之后指定查询条件对查询结果进行过滤，后面可跟聚合函数

	条件判断
select * from student where sid in ('S_1002','S_1003');\|\|not in\|\| <、>、=、!=、、、、、	\|\|查询sid为，S_1002，S_1003的学生的信息	使用IN关键字查询\|运算符
select * from student where age between 15 and 18;\|\|not between	\|\|查询15到18岁的学生信息	使用BETWEEN AND关键字查询
select * from student where sname is not null;	\|\|查询sname不为空值	使用空值查询
select * from student where age>15 and gender='male';	\|\|查询年纪大于15且(或者)性别为male	使用AND(OR)关键字查询
select * from student where sname like 'wang'; select * from student where sname like 'li%'; select * from student where sname like 'zx_ _ ';\|\|like '_g';	\|\|查询sname中与wang匹配的学生信息查询学生以li开始的记录\|\| 以zx开头长度为4	使用LIKE关键字查询\|\|%通配符（多字符）\|\|_通配符（单字符）or后面需要再次like
select * from student order by age asc\|desc limit 3;	\|\|查询学生表中年纪最小\|最大的3位同学	使用LIMIT限制查询结果的数量
	分组 group by 【having
select count(), departmentnumber from employee group by departmentnumber;	\|\|统计各部门员工个数	使用GROUP BY进行分组查询
select count(), departmentnumber from employee where departmentnumber>1001 group by departmentnumber;	\|\|统计部门编号大于1001的各部门员工个数	聚合函数and GROUP BY
select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;	\|\|统计工资总和大于8000的部门	聚合函数and GROUP BY\|having
SELECT sex,AVG(IFNULL(math,0))+AVG(IFNULL(english,0)) 平均分,COUNT(* ) FROM student4 WHERE math>70 OR english>70 GROUP BY sex HAVING COUNT(*)>2;	-- 按照性别分组。分别查询男、女同学的平均分,人数要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人	聚合函数and GROUP BY\|having
	排序 order by age
select * from student order by age asc ; age desc; 别名： FROM student ORDER BY 总分 desc;	\|\|查询所有学生并按照年纪大小升序排列\|\|查询所有学生并按照年纪大小降序排列	使用ORDER BY对查询结果排序asc/desc，以“，”再次排，sal asc
	分页 limit
select * from stu limit 0,3	公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

-- 每页显示3条记录 SELECT * FROM student LIMIT 0,3; -- 第1页开始的索引 = （1 - 1） * 3=0 SELECT * FROM student LIMIT 3,3; -- 第2页开始的索引 = （2 - 1） * 3=3 SELECT * FROM student LIMIT 6,3; -- 第3页开始的索引 = （3 - 1） * 3=6 SELECT * FROM student LIMIT 9,3; -- 第4页开始的索引 = （4 - 1） * 3=9

开始的索引 = （当前的页码 - 1） * 每页显示的条数

十二、多表连接查询

1.交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。

所以，交叉连接也被称为笛卡尔连接，其语法格式如下：

隐式内连接：使用where条件消除无用数据

SELECT * FROM 表1 【CROSS JOIN】表2;

在该语法中：CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。由于这个交叉连接查询在实际运用中没有任何意义，所以只做为了解即可

select e.ename,e.sal,d.dname from emp e,dept d
	where e.deptno = d.deptno;

2.内连接查询

内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。其语法格式如下：

显式内连接：

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段 where 。。；

在该语法中：INNER JOIN用于连接两个表，ON来指定连接条件；其中INNER可以省略。

查询员工姓名及其所属部门名称 MySQL命令：

select employee.ename,department.dname from department inner join employee 
	on department.did=employee.departmentid;

3.外连接查询

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。

但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。

外连接又分为左(外)连接和右(外)连接。其语法格式如下：

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

由此可见，外连接的语法格式和内连接非常相似，只不过使用的是LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN关键字。其中，关键字左边的表被称为左表，关键字右边的表被称为右表；OUTER可以省略。

在使用左(外)连接和右(外)连接查询时，查询结果是不一致的，具体如下： 1、LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。 2、RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

准备这组数据有一定的特点，为的是让大家直观的看出左连接与右连接的不同之处 1、班级编号为1004的PHP班级没有学生 2、学号为5的学生王跃跃班级编号为1009，该班级编号并不在班级表中

3.1 左（外）连接查询

左(外)连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。查询每个班的班级ID、班级名称及该班的所有学生的名字 MySQL命令：

select class.cid,class.cname,student.sname 
	from class left outer join student 
	on class.cid=student.classid;

展示结果分析： 1、分别找出Java班、C++班、Python班的学生 2、右表的王跃跃不满足查询条件故其没有出现在查询结果中 3、虽然左表的PHP班没有学生，但是任然显示了PHP的信息；但是，它对应的学生名字为NULL

3.2 右（外）连接查询

右(外)连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。查询每个班的班级ID、班级名称及该班的所有学生的名字 MySQL命令：

select class.cid,class.cname,student.sname 
	from class right outer join student 
	on class.cid=student.classid;

展示结果分析： 1、分别找出Java班、C++班、Python班的学生 2、左表的PHP班不满足查询条件故其没有出现在查询结果中 3、虽然右表的jack没有对应班级，但是任然显示王跃跃的信息；但是，它对应的班级以及班级编号均为NULL

十三、子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

1.带比较运算符的子查询

比较运算符前面我们提到过得，就是>、<、=、>=、<=、!=等查询张三同学所在班级的信息 MySQL命令：

select * from class 
	where cid IN (select classid from student where sname='张三');

查询比张三同学所在班级编号还大的班级的信息 MySQL命令：

select * from class 
	where cid>(select classid from student where sname='张三');

2.带EXISTS关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询，它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行 假如王五同学在学生表中则从班级表查询所有班级信息 MySQL命令：

select * from class 
	where exists (select * from student where sname='王五');

3.带ANY关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

查询比任一学生所属班级号还大的班级编号 MySQL命令：

select * from class 
	where cid > any (select classid from student);

4.带ALL关键字的子查询

ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。

查询比所有学生所属班级号还大的班级编号 MySQL命令：

select * from class 
	where cid > all (select classid from student);

* 多表查询练习

-- 部门表
			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,'销售部','广州'),
-- 职务表，职务名称，职务描述
		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 
				t1.`id`, -- 员工编号
				t1.`ename`, -- 员工姓名
				t1.`salary`,-- 工资
				t2.`jname`, -- 职务名称
				t2.`description` -- 职务描述
			FROM 
				emp t1, job t2
			WHERE 
				t1.`job_id` = t2.`id`;
				
		-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
				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.查询员工姓名，工资，工资等级
			SELECT 
				t1.ename ,
				t1.`salary`,
				t2.*
			FROM emp t1, salarygrade t2
			WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;	
			-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等
			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.查询出部门编号、部门名称、部门位置、部门人数
			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.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询

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

DCL

权限

SQL分类：

DDL：操作数据库和表

DML：增删改表中数据

DQL：查询表中数据

DCL：管理用户，授权

DBA：数据库管理员
DCL：管理用户，授权

管理用户

添加用户：

语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

删除用户：

语法：DROP USER '用户名'@'主机名';

修改用户密码：

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;

	* 通配符： % 表示可以在任意主机使用用户登录数据库
权限管理：

查询权限： -- 查询权限 SHOW GRANTS FOR '用户名'@'主机名'; SHOW GRANTS FOR 'lisi'@'%';

授予权限： -- 授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; -- 给张三用户授予所有权限，在任意数据库任意表上

GRANT ALL ON . TO 'zhangsan'@'localhost';

给initiation授予库just的全部权限

GRANT ALL PRIVILEGES ON just.* TO 'initiation'@'localhost'

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

-- 撤销张三一切权限 REVOKE ALL ON . FROM 'zhangsan'@'localhost';

1. usage
连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。
mysql> grant usage on *.* to ‘p1′@’localhost’ identified by ‘123′;
该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。
2. select
必须有select的权限，才可以使用select table
mysql> grant select on pyt.* to ‘p1′@’localhost’;
mysql> select * from shop;
3. create
必须有create的权限，才可以使用create table
mysql> grant create on pyt.* to ‘p1′@’localhost’;
4. create routine
必须具有create routine的权限，才可以使用{create |alter|drop} {procedure|function}
mysql> grant create routine on pyt.* to ‘p1′@’localhost’;
当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：
mysql> show grants for ‘p1′@’localhost’;
	+—————————————————————————+
	Grants for p1@localhost
	+————————————————————————–+
	| GRANT USAGE ON *.* TO ‘p1′@’localhost’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′ |
	| GRANT SELECT, CREATE, CREATE ROUTINE ON `pyt`.* TO ‘p1′@’localhost’|
	| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro_shop1` TO ‘p1′@’localhost’ |
	+————————————————————————————-+
5. create temporary tables(注意这里是tables，不是table)
必须有create temporary tables的权限，才可以使用create temporary tables.
mysql> grant create temporary tables on pyt.* to ‘p1′@’localhost’;
[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt
mysql> create temporary table tt1(id int);
6. create view
必须有create view的权限，才可以使用create view
mysql> grant create view on pyt.* to ‘p1′@’localhost’;
mysql> create view v_shop as select price from shop;
7. create user
要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
mysql> grant create user on *.* to ‘p1′@’localhost’;
或：mysql> grant insert on *.* to p1@localhost;
8. insert
必须有insert的权限，才可以使用insert into ….. values….
9. alter
必须有alter的权限，才可以使用alter table
alter table shop modify dealer char(15);
10. alter routine
必须具有alter routine的权限，才可以使用{alter |drop} {procedure|function}
mysql>grant alter routine on pyt.* to ‘p1′@’ localhost ‘;
mysql> drop procedure pro_shop;
Query OK, 0 rows affected (0.00 sec)
mysql> revoke alter routine on pyt.* from ‘p1′@’localhost’;
[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt
mysql> drop procedure pro_shop;
ERROR 1370 (42000): alter routine command denied to user ‘p1′@’localhost’ for routine ‘pyt.pro_shop’
11. update
必须有update的权限，才可以使用update table
mysql> update shop set price=3.5 where article=0001 and dealer='A';
12. delete
必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)
13. drop
必须有drop的权限，才可以使用drop database db_name; drop table tab_name;
drop view vi_name; drop index in_name;
14. show database
通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。
对于p1@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库：
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema|
| pyt |
| test |
+——————–+
15. show view
必须拥有show view权限，才能执行show create view。
mysql> grant show view on pyt.* to p1@localhost;
mysql> show create view v_shop;
16. index
必须拥有index权限，才能执行[create |drop] index
mysql> grant index on pyt.* to p1@localhost;
mysql> create index ix_shop on shop(article);
mysql> drop index ix_shop on shop;
17. excute
执行存在的Functions,Procedures
mysql> call pro_shop1(0001,@a)；
+———+
| article |
+———+
| 0001 |
| 0001 |
+———+
mysql> select @a;
+——+
| @a |
+——+
| 2 |
+——+
18. lock tables
必须拥有lock tables权限，才可以使用lock tables
mysql> grant lock tables on pyt.* to p1@localhost;
mysql> lock tables a1 read;
mysql> unlock tables;
19. references
有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。
20. reload
必须拥有reload权限，才可以执行flush [tables | logs | privileges]
mysql> grant reload on pyt.* to p1@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> grant reload on *.* to ‘p1′@’localhost’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables;
21. replication client
拥有此权限可以查询master server、slave server状态。
mysql> show master status;
ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
mysql> grant Replication client on *.* to p1@localhost;
或：mysql> grant super on *.* to p1@localhost;
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000006 | 2111 | | |
+——————+———-+————–+——————+
mysql> show slave status;
22. replication slave
拥有此权限可以查看从服务器，从主服务器读取二进制日志。
mysql> show slave hosts;
ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
mysql> show binlog events;
ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
mysql> grant replication slave on *.* to p1@localhost;
mysql> show slave hosts;
Empty set (0.00 sec)
mysql>show binlog events;
+—————+——-+—————-+———–+————-+————–+
| Log_name | Pos | Event_type | Server_id| End_log_pos|Info | 
+—————+——-+————–+———–+————-+—————+
| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 | |mysql-bin.000005|98|Query|1|197|use `mysql`; create table a1(i int)engine=myisam|
…………………………………
23. Shutdown
关闭MySQL：
[mysql@mydev ~]$ mysqladmin shutdown
重新连接：
[mysql@mydev ~]$ mysql
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
[mysql@mydev ~]$ cd /u01/mysql/bin
[mysql@mydev bin]$ ./mysqld_safe &
[mysql@mydev bin]$ mysql
24. grant option
拥有grant option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）
mysql> grant Grant option on pyt.* to p1@localhost;
mysql> grant select on pyt.* to p2@localhost;
25. file
拥有file权限才可以执行 select ..into outfile和load data infile…操作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患。
mysql> grant file on *.* to p1@localhost;
mysql> load data infile ‘/home/mysql/pet.txt’ into table pet;
26. super
这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS。
mysql> grant super on *.* to p1@localhost;
mysql> purge master logs before ‘mysql-bin.000006′;
27. process
通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。
mysql> show processlist;
+—-+——+———–+——+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——+———+——+——-+——————+
| 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |
+—-+——+———–+——+———+——+——-+——————+
另外，
管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟*.*
mysql> grant super on pyt.* to p1@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> grant super on *.* to p1@localhost;
Query OK, 0 rows affected (0.01 sec)

十五、表的关联关系

在实际开发中数据表之间存在着各种关联关系。在此，介绍MySQL中数据表的三种关联关系。

多对一

多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系中，应将外键建在多的一方否则会造成数据的冗余。

多对多

多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。

一对一

在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。接下来，我们来学习在一对多的关联关系中如果添加和删除数据。先准备一些测试数据，代码如下：

DROP TABLE IF EXISTS student; DROP TABLE IF EXISTS class;

1.关联查询查询Java班的所有学生 MySQL命令：

select * from student where classid=(select cid from class where cname='Java');

2.关于关联关系的删除数据请从班级表中删除Java班级。在此，请注意：班级表和学生表之间存在关联关系；要删除Java班级，应该先删除学生表中与该班相关联的学生。否则，假若先删除Java班那么学生表中的cid就失去了关联删除Java班 MySQL命令：

delete from student where classid=(select cid from class where cname='Java');
delete from class where cname='Java';

1. 多表之间的关系
	1. 分类：
		1. 一对一(了解)：
			* 如：人和身份证
			* 分析：一个人只有一个身份证，一个身份证只能对应一个人
		2. 一对多(多对一)：
			* 如：部门和员工
			* 分析：一个部门有多个员工，一个员工只能对应一个部门
		3. 多对多：
			* 如：学生和课程
			* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
	2. 实现关系：
		1. 一对多(多对一)：
			* 如：部门和员工
			* 实现方式：在多的一方建立外键，指向一的一方的主键。
		2. 多对多：
			* 如：学生和课程
			* 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
		3. 一对一(了解)：
			* 如：人和身份证
			* 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

	3. 案例
		-- 创建旅游线路分类表 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)
		);

总结重要（从关键字分析）：查询语句的书写顺序和执行顺序 select => from => where => group by => having => order by => limit 查询语句的执行顺序 from => where => group by => having => select => order by => limit

事务

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;
		-- 0. 开启事务
		START TRANSACTION;
		UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
		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）
			* 产生的问题：不可重复读、幻读
			读取事务要等到这个更新操作事务提交后才能读取数据，可以解决脏读问题。（大多数数据库默认的隔离级别，比如Oracle 、Sql Server）
		3. repeatable read：可重复读 （MySQL默认）
			* 产生的问题：幻读
			开始读取数据（事务开始）时，不允许修改操作（即update操作）。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;

脏读：一个事务，读取到另一个事务中没有提交的数据

数据库备份和还原

命令行：
- 语法：
  - 备份： mysqldump -u用户名 -p密码数据库名称 > 保存的路径
  - 还原：
    1. 登录数据库
    2. 创建数据库
    3. 使用数据库
    4. 执行文件。source 文件路径
图形化工具：

———————————————— MySQL 有这一篇就够（呕心狂敲37k字，只为博君一点赞！！！）_老赖的小弟的博客-CSDN博客

ersheng_

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL

前言知识无底，学海无涯一、SQL简述1.SQL的概述Structure Query Language(结构化查询语言)简称SQL，它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库；定义和操作数据，维护数据的完整性和安全性。2.SQL的优点1、简单易学，具有很强的操作性 2、绝大多数重要的数据库管理系统均支持SQL 3、高度非过程化；用SQL操作数据库时大部分的工作由DBM
复制链接

扫一扫