mysql命令大全 优质博客
1、初识MySQL
执行顺序:
JavaEE:企业级Java开发 Web
前端(页面:展示:数据)
后台 (连接点:连接数据库JDBC,连接前端(控制视图跳转,给前端传递数据))
数据库(存数据,Txt,Excel,Word)
只会写代码,学好数据库,基本混饭吃:
操作系统,数据结构与算法!当一个不错的程序猿!
离散数学,数字电路,体系结构,编译原理。+实战经验,优秀程序猿
1.1为什么学数据库
1、岗位需求
2、现在的世界,大数据时代,得数据者得天下
3、被迫需求:存数据
4、数据库是所有软件体系中最核心的存在
DBA
1.2 什么是数据库
数据库:(DB,DataBase)
概念:数据仓库,软件,安装在操作系统之(windows,Linux。mac)上的!SQL,可以存储大量的数据,500万!
作用:存储数据,管理数据 Excel
1.3 数据库分类
关系型数据库:(SQL)
- MySQL, Oracle, sql Server, DB2, SQLite
- 通过表和表之间,行和列之间的关系进行数据的存储
非关系型数据库:(NoSQL) Not Only SQL
- Redis, MongDB
- 非关系型数据库,对象存储,通过对象自身的属性来决定。
**DBMS(数据库管理系统) **
- 数据库的管理软件,科学有效的管理我们的数据,维护和获取
- MySQL ,数据管理系统!
1.4 MySQL简介
MySQL是一个**关系型数据库管理系统**
前世: 瑞典MySQL AB 公司
今身: 属于 Oracle 旗下产品
MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
开源的数据库软件
体积小,速度快,总体拥有成本低,招人成本比较低。
中小型网站,或者大型网站,集群
官网: https://www.mysql.com/
mysql自带4个默认数据库:
information_schema
performance_schema
mysql
test
informance_schema:
保存了MySQl服务所有数据库的信息。
具体MySQL服务有多少个数据库,各个数据库有哪些表,各个表中的字段是什么数据类型,各个表中有哪些索引,各个数据库要什么权限才能访问。
mysql:
保存MySQL的权限、参数、对象和状态信息。
如哪些user可以访问这个数据、DB参数、插件、主从
performance_schema:
主要用于收集数据库服务器性能参数
提供进程等待的详细信息,包括锁、互斥变量、文件信息;
保存历史的事件汇总信息,为提供MySQL服务器性能做出详细的判断;
对于新增和删除监控事件点都非常容易,并可以随意改变mysql服务器的监控周期,例如(CYCLE、MICROSECOND)
test:
没有东西
sakila:sakila数据集是MySQL官方提供的一个学习MySQL的很好的素材,作者是 Mike Hillyer,该数据集是用于替代之前的world数据集(Oracle也提供的有这个),world数据集比较简单,只包含3个表:国家,城市,语言。
world:示例数据库
sys:sys库所有的数据源来自:performance_schema。目标是把performance_schema的把复杂度降低,让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。sys_开头是库里的配置表,sys_config用于sys schema库的配置。
1.5连接数据库
命令行连接!
mysql -hlocalhost -uroot -p123456 --连接数据库
在DOS命令窗口输入 mysql -hlocalhost -uroot -p回车 进入mysql数据库,
其中-h表示服务器名,localhost表示本地;-u为数据库用户名,root是mysql默认用户名;-p为密码,
如果设置了密码,可直接在-p后链接输入,如:-p123456,用户没有设置密码,显示Enter password时,
直接回车即可。
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; --修改密码
flush privileges;--刷新权限
--------------------------------------------------
--所有语句使用;结尾--
show databases;--查看所有的数据库
mysql> use school--切换数据库, use 数据库名
Database changed
--
show tables;--查看数据库中所有的表
describe student;--显示数据库中所有的表的信息
create database westos;--创建一个数据库
exit;--退出连接
--单行注释(sql本来注释)
/*
多行注释
*/
2、操作数据库
操作数据库》操作数据库中的表》操作数据库中表的数据
MySQL不区分大小写
2.1操作数据库
1.创建数据库
CREATE DATABASE IF NOT EXISTS westos;
2.删除数据库
DROP DATABASE IF EXISTS westos
3.使用数据库
-- ``,如果你的表名或者字段名是一个特殊字符,需要带``
USE 'school'
4.产看数据库
SHOW DATABASES--查看所有数据库
2.2Mysql数据库的数据类型
一、MySQL的数据类型
主要包括以下五大类:
整数类型:BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型:FLOAT、DOUBLE、DECIMAL
字符串类型:CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型:Date、DateTime、TimeStamp、Time、Year
其他数据类型:BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等
1、整型
MySQL数据类型 | 含义(有符号) |
tinyint(m) | 1个字节 范围(-128~127) |
smallint(m) | 2个字节 范围(-32768~32767) |
mediumint(m) | 3个字节 范围(-8388608~8388607) |
int(m) | 4个字节 范围(-2147483648~2147483647) 常用 |
bigint(m) | 8个字节 范围(+-9.22*10的18次方) 主键常用 |
取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~256)。
int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,只在加入没有影响到显示的宽度,不知道这个m有什么用。
mysql中int(5)表达的真正含义_Night_of_light的博客-CSDN博客
2、浮点型(float和double)
MySQL数据类型 | 含义 |
float(m,d) | 单精度浮点型 8位精度(4字节) m总个数,d小数位 |
double(m,d) | 双精度浮点型 16位精度(8字节) m总个数,d小数位 |
设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位。整数部分最大是3位,如果插入数12.123456,存储的是12.1234,如果插入12.12,存储的是12.1200.
3、定点数
浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。 (金融常用)
decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位。
4、字符串(char,varchar,_text)
MySQL数据类型 | 含义 |
char(n) | 固定长度,最多255个字符 |
varchar(n) | 固定长度,最多65535个字符 常用 |
tinytext | 可变长度,最多255个字符 |
text | 可变长度,最多65535个字符 大文本常用 |
mediumtext | 可变长度,最多2的24次方-1个字符 |
longtext | 可变长度,最多2的32次方-1个字符 |
char和varchar:
1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。
2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),
所以varchar(4),存入3个字符将占用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。
varchar和text:
1.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),text是实际字符数+2个字
节。
2.text类型不能有默认值。
3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下,text的索引似乎不起作用。
5.二进制数据(_Blob)
1._BLOB和_text存储方式不同,_TEXT以文本方式存储,英文存储区分大小写,而_Blob是以二进制方式存储,不分大小写。
2._BLOB存储的数据只能整体读出。
3._TEXT可以指定字符集,_BLO不用指定字符集。
6.日期时间类型
类型名称 | 日期格式 | 日期范围 | 存储需求 |
---|---|---|---|
YEAR | YYYY | 1901 ~ 2155 | 1 个字节 |
TIME | HH:MM:SS | -838:59:59 ~ 838:59:59 | 3 个字节 |
DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 个字节 |
DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 (最常用) | 8 个字节 |
TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC (最常用) | 4 个字节 |
TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外,还有一个最大的区别是:
- DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;
- 而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。
- 若定义一个字段为TIMESTAMP ,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。
- TIMESTAMP支持使用DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP,DATETIME(mysql5.7以上)也支持使用DEFAULT DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 可以在数据类型为TIMESTAMP或DATETIME时表示默认当前时间 和更新时默认为更新的当前时间
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
Mysql时间类型详解博客:
MySQL==》DTAETIME、TIMESTAMP、DATE、TIME、YEAR(日期和时间类型)_keep one's resolveY的博客-CSDN博客
附表
MySQL日期格式化(format)取值范围。
值 | 含义 | |
---|---|---|
秒 | %S、%s | 两位数字形式的秒( 00,01, ..., 59) |
分 | %I、%i | 两位数字形式的分( 00,01, ..., 59) |
小时 | %H | 24小时制,两位数形式小时(00,01, ...,23) |
%h | 12小时制,两位数形式小时(00,01, ...,12) | |
%k | 24小时制,数形式小时(0,1, ...,23) | |
%l | 12小时制,数形式小时(0,1, ...,12) | |
%T | 24小时制,时间形式(HH:mm:ss) | |
%r | 12小时制,时间形式(hh:mm:ss AM 或 PM) | |
%p | AM上午或PM下午 | |
周 | %W | 一周中每一天的名称(Sunday,Monday, ...,Saturday) |
%a | 一周中每一天名称的缩写(Sun,Mon, ...,Sat) | |
%w | 以数字形式标识周(0=Sunday,1=Monday, ...,6=Saturday) | |
%U | 数字表示周数,星期天为周中第一天 | |
%u | 数字表示周数,星期一为周中第一天 | |
天 | %d | 两位数字表示月中天数(01,02, ...,31) |
%e | 数字表示月中天数(1,2, ...,31) | |
%D | 英文后缀表示月中天数(1st,2nd,3rd ...) | |
%j | 以三位数字表示年中天数(001,002, ...,366) | |
月 | %M | 英文月名(January,February, ...,December) |
%b | 英文缩写月名(Jan,Feb, ...,Dec) | |
%m | 两位数字表示月份(01,02, ...,12) | |
%c | 数字表示月份(1,2, ...,12) | |
年 | %Y | 四位数字表示的年份(2015,2016...) |
%y | 两位数字表示的年份(15,16...) | |
文字输出 | %文字 | 直接输出文字内容 |
数据类型的属性:
MySQL关键字 | 含义 |
NULL | 数据列可包含NULL值 |
NOT NULL | 数据列不允许包含NULL值 |
DEFAULT | 默认值 |
PRIMARY KEY | 主键 |
AUTO_INCREMENT | 自动递增,适用于整数类型 |
UNSIGNED | 无符号 |
CHARACTER SET name | 指定一个字符集 |
二、MYSQL数据类型的长度和范围
各数据类型及字节长度一览表:
数据类型 | 字节长度 | 范围或用法 |
Bit | 1 | 无符号[0,255],有符号[-128,127],天缘博客备注:BIT和BOOL布尔型都占用1字节 |
TinyInt | 1 | 整数[0,255] |
SmallInt | 2 | 无符号[0,65535],有符号[-32768,32767] |
MediumInt | 3 | 无符号[0,2^24-1],有符号[-2^23,2^23-1]] |
Int | 4 | 无符号[0,2^32-1],有符号[-2^31,2^31-1] |
BigInt | 8 | 无符号[0,2^64-1],有符号[-2^63 ,2^63 -1] |
Float(M,D) | 4 | 单精度浮点数。天缘博客提醒这里的D是精度,如果D<=24则为默认的FLOAT,如果D>24则会自动被转换为DOUBLE型。 |
Double(M,D) | 8 | 双精度浮点。 |
Decimal(M,D) | M+1或M+2 | 未打包的浮点数,用法类似于FLOAT和DOUBLE,天缘博客提醒您如果在ASP中使用到Decimal数据类型,直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。 |
Date | 3 | 以YYYY-MM-DD的格式显示,比如:2009-07-19 |
Date Time | 8 | 以YYYY-MM-DD HH:MM:SS的格式显示,比如:2009-07-19 11:22:30 |
TimeStamp | 4 | 以YYYY-MM-DD的格式显示,比如:2009-07-19 |
Time | 3 | 以HH:MM:SS的格式显示。比如:11:22:30 |
Year | 1 | 以YYYY的格式显示。比如:2009 |
Char(M) | M | 定长字符串。 |
VarChar(M) | M | 变长字符串,要求M<=255 |
Binary(M) | M | 类似Char的二进制存储,特点是插入定长不足补0 |
VarBinary(M) | M | 类似VarChar的变长二进制存储,特点是定长不补0 |
Tiny Text | Max:255 | 大小写不敏感 |
Text | Max:64K | 大小写不敏感 |
Medium Text | Max:16M | 大小写不敏感 |
Long Text | Max:4G | 大小写不敏感 |
TinyBlob | Max:255 | 大小写敏感 |
Blob | Max:64K | 大小写敏感 |
MediumBlob | Max:16M | 大小写敏感 |
LongBlob | Max:4G | 大小写敏感 |
Enum | 1或2 | 最大可达65535个不同的枚举值 |
Set | 可达8 | 最大可达64个不同的值 |
Geometry | ||
Point | ||
LineString | ||
Polygon | ||
MultiPoint | ||
MultiLineString | ||
MultiPolygon | ||
GeometryCollection |
三、使用建议
1、在指定数据类型的时候一般是采用从小原则,比如能用TINY INT的最好就不用INT,能用FLOAT类型的就不用DOUBLE类型,这样会对MYSQL在运行效率上提高很大,尤其是大数据量测试条件下。
2、不需要把数据表设计的太过复杂,功能模块上区分或许对于后期的维护更为方便,慎重出现大杂烩数据表
3、数据表和字段的起名字也是一门学问
4、设计数据表结构之前请先想象一下是你的房间,或许结果会更加合理、高效
5、数据库的最后设计结果一定是效率和可扩展性的折中,偏向任何一方都是欠妥的
Java中数据类型对应MySQL数据库中哪种类型?
MySQL Connector/J 对于 MySql 数据类型和 Java 数据类型之间的转换是很灵活的。
一般来讲,任何 MySql 数据类型都可以被转换为一个 java.lang.String,任何 MySql 数字类型都可以被转换为任何一种 Java 数字类型(当然这样也可能出一些四舍五入,溢出,精度丢失之类的问题)。
MySql 数据类型 | 可以被转换成的 Java 类型 |
---|---|
CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET | java.lang.String, java.io.InputStream, java.io.Reader, java.sql.Blob, java.sql.Clob |
FLOAT, REAL, DOUBLE PRECISION, NUMERIC, DECIMAL, TINYINT, SMALLINT, MEDIUMINT, INTEGER, BIGINT | java.lang.String, java.lang.Short, java.lang.Integer, java.lang.Long, java.lang.Double, java.math.BigDecimal |
DATE, TIME, DATETIME, TIMESTAMP | java.lang.String, java.sql.Date, java.sql.Timestamp |
MySql 类型名 | GetColumnClassName 返回值 | 返回的 Java 类 |
---|---|---|
BIT(1)(MySQL-5.0 新引入) | BIT | java.lang.Boolean |
BIT(大于 1) (MySQL-5.0 新引入) | BIT | byte[] |
TINYINT | TINYINT | 如果 tinyInt1isBit 配置设置为 true(默认为 true),是 java.lang.Boolean,存储空间 为 1;否则是为 java.lang.Integer |
BOOL, BOOLEAN | TINYINT | 参见 TINYINT。这些是 TINYINT(1) 另一种写法而已 |
SMALLINT[(M)] [UNSIGNED] | SMALLINT [UNSIGNED] | java.lang.Integer(不管是否无符) |
MEDIUMINT[(M)] [UNSIGNED] | MEDIUMINT [UNSIGNED] | java.lang.Integer;无符的话是 java.lang.Long(C/J 3.1 或更早),或者 java.lang.Integer(C/J 5.0 或更晚) |
INT,INTEGER[(M)] [UNSIGNED] | INTEGER [UNSIGNED] | java.lang.Integer;无符的话是 java.lang.Long |
BIGINT[(M)] [UNSIGNED] | BIGINT [UNSIGNED] | java.lang.Long;无符的话是 java.math.BigInteger |
FLOAT[(M,D)] | FLOAT | java.lang.Float |
DOUBLE[(M,B)] | DOUBLE | java.lang.Double |
DECIMAL[(M[,D])] | DECIMAL | java.math.BigDecimal |
DATE | DATE | java.sql.Date |
DATETIME | DATETIME | java.sql.Timestamp(也可用java.util.date) |
TIMESTAMP[(M)] | TIMESTAMP | java.sql.Timestamp(也可用java.util.date) |
TIME | TIME | java.sql.Time |
YEAR[(2|4)] | YEAR | 如果 yearIsDateType 配置设置为 false,返回的对象类型为 java.sql.Short;如果设置为 true(默认为 true),返回的对象类型是 java.sql.Date,其具体时间是为一月一日零时零分 |
CHAR(M) | CHAR | java.lang.String(除非该列字符集设置为 BINARY,那样返回 byte[]) |
VARCHAR(M) [BINARY] | VARCHAR | java.lang.String(除非该列字符集设置为 BINARY,那样返回 byte[]) |
BINARY(M) | BINARY | byte[] |
VARBINARY(M) | VARBINARY | byte[] |
TINYBLOB | TINYBLOB | byte[] |
TINYTEXT | VARCHAR | java.lang.String |
BLOB | BLOB | byte[] |
TEXT | VARCHAR | java.lang.String |
MEDIUMBLOB | MEDIUMBLOB | byte[] |
MEDIUMTEXT | VARCHAR | java.lang.String |
LONGBLOB | LONGBLOB | byte[] |
LONGTEXT | VARCHAR | java.lang.String |
ENUM('value1','value2',...) | CHAR | java.lang.String |
ET('value1','value2',...) | CHAR | java.lang.String |
原文链接: MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.5 Java, JDBC, and MySQL Types
null
- 没有值,未知
- 注意,不要使用null进行运算,结果为null
2.3数据库的字段类型(重点)
unsigened:
-
无符号的整数,unsigned 属性只针对整型
-
该列不能为负数,即:无符号
zerofill:
AUTO_INCREMENT:
- 通常理解为自增,自动在上一条记录的基础上+1
- 通常用来设计唯一的主键 index,必须是整数类型
- 可以自定义设置主键自增的起始值和步长
NOT NULL:
DEFAULT:
- 默认,设置默认的值!
COMMENT :
- 注释
Binary:
CHAR 和 VARCHAR的值根据缺省字符集以大小写不区分的方式排序和比较,即:
在这里,MySQL执行“HELLO”和“hello”的逐字符比较并返回1(因为在逐个字符的基础上,它们是等效的):
SELECT "HELLO" = "hello";
,除非给出BINARY关键字。
这里MySQL执行“HELLO”和“hello”的逐字节比较并返回0(因为在逐个字节的基础上,它们不等效):
SELECT BINARY "HELLO" = "hello";
--目标:创建一个schoo1数据库
--创建学生表(列,字段)使用SQL 创建
--学号int 登录密码varchar(20)姓名,性别varchar(2),出生日期(datatime),家庭住址,emai1--注意点,使用英文(),表的名称和字段尽量使用括起来
-- AUTO_ INCREMENT 自增
--字符串使用单引号括起来!
--所有的语句后面加,(英文的),最后一个不用加
-- PRIMARY KEY 主键,一般- 一个表只有一个唯一 -的主键!(建议将主键外键索引在一起看,详见后面索引)
CREATE DATABASE school
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型[属性][索引][注释],
`字段名` 列类型[属性][索引][注释],
...
`字段名` 列类型[属性][索引][注释]
)[表类型][表的字符集设置][注释]
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构
//CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP可以在数据类型为TIMESTAMP时表示默认当前时间
和更新时默认为更新的当前时间
2.5数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为MYISAM的两倍 |
常规使用操作:
- MYISAM 节约空间,速度较快,
- INNODB 安全性高,事务处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
本质还是文件的存储
MySQL 引擎在物理文件上的区别
- innoDB 在数据库表中,只有一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM 对应的文件
- *.frm - 表结构的定义文件
- *. MYD -数据文件
- *.MYI 索引文件
设置数据库字符集编码
CHARTSET=UTF8
不设置的话,会是mysql默认的字符集编码-(不支持中文)
可以在my.ini中配置默认的编码
character-set-server=utf8
MySql: charset 和 collation 的设置
charset 和 collation 有多个级别的设置:服务器级、数据库级、表级、列级和连接级
1.服务器级
查看设置:show global variables like 'character_set_server'; 和 show global variables like 'collation_server';
修改设置:在OPTION FILE (/etc/mysql/my.cnf)里设置:
[mysqld]
character_set_server=utf8
collation_server=utf8_general_ci
2. 数据库级
查看设置:select * from information_schema.schemata where schema_name = 'cookbook';
设置:
1.若没有显式设置,则自动使用服务器级的配置
2.显式设置:在创建库时指定
create database playUtf8 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
www.2cto.com
3.表级
查看设置:show create table course;
设置:
1.若没有显式设置,则自动使用数据库级的配置
2.显式设置:在创建表时指定
create table utf ( id int ) default charset=utf8 default collate=utf8_bin;
4.列级
查看设置:show create table course;
设置:
1.若没有显式设置,则自动使用表级的配置
2.显式设置:
CREATE TABLE Table1(column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);
5.连接级别
查看设置:
show variables like 'character_set_client'; # 服务端使用这个编码来理解客户端发来的statements
show variables like 'character_set_connection' ; # 我还不知道什么意思,等看了mysql源码再说
show variables like 'character_set_results'; # 服务端使用这个编码回送结果集和错误信息
设置:
客户端在连接时可以指定这些参数;同时,服务端也提供了一个Global范围的值,客户端未指定这些参数时,服务端就使用这个Global值。这个global值怎么设置的? 我查遍了很多文档,似乎还没看到设置的办法 (有人说通过my.cnf,或者在启动mysqld时指定命令行参数,其实都是错的)
附:connector/j传输SQL时用什么编码?
答案: "The character encoding between client and server is automatically detected upon connection. The encoding used by the driver is specified on the server using the character_set_server system variable for server versions 4.1.0 and newer."
也就是说,是在连接时查询服务器端的character_set_server值,再确定连接将使用的编码。
不过,官方文档还说,"要想覆盖客户端上的自动检测编码功能,可在用于连接到服务器的URL中使用“characterEncoding”属性。"
2.6修改删除表
修改
-- 修改表名 ALTER TABLE 旧表面 AS 新表名
ALTER TABLE student RENAME AS student1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student1 ADD age INT(11)
-- 修改表的字段(重命名,修改约束)
ALTER TABLE student1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE student1 CHANGE age age1 INT(1) -- 字段重命名
-- 删除表的字段
ALTER TABLE student1 DROP age1
删除
-- 删除表
DROP TABLE IF EXISTS student1
所有的创建和删除操作尽量加上判断,以免报错
注意点:
- `` 字段名,使用这个包裹
- 注释 – /**/
- sql 关键字大小写不敏感,建议写小写
- 所有的符号全部用英文
3、MySQL数据管理
3.1 DML语言(全记住)
数据库意义:数据存储,数据管理
DML语言:数据操作语言
- Insert
- update
- delete
3.2添加
insert
-- 插入语句(添加)
-- nsert into 表名([字段一], [字段二])values('值1'),('值2')
INSERT INTO `grade` (`gradename`) VALUES('大四')
-- 由于主键自增我们可以省略(如何不写表的字段,他会一一匹配)
INSERT INTO `grade` VALUES('大三')
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES ('大三','null')
-- 一般写插入语句,我们一定要数据和字段一一对应。
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一');
INSERT INTO `student`(`name`) VALUES ('张三')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaaa','男')
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES ('李四','aaaaa','男'),('王五','23232','女')
语法:-- insert into 表名([字段一], [字段二])values(‘值1’),(‘值2’)
注意事项:
1.字段和字段之间用逗号分开
2.字段可以省略,但是后面的值必须一一对应
3.可以同时插入多条数据,VALUES后面的值需要使用,隔开即可
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES ('李四','aaaaa','男'),('王五','23232','女')
3.3 修改
update 修改谁(条件) set 原来的值=新值
-- 修改学员名字
UPDATE `student` SET `name`='囷' WHERE id =1;
-- 不指定条件的情况下,会改动所有表
UPDATE `student` SET `name`='233'
-- 语法;
-- UPDATE 表名 set column_name,[] = value where 条件
条件:where 子句 运算符 id 等于 某个值,大于某个值,在某个区间内修改
操作符返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
!= <> | 不等于 | 5!=6 | true |
> | 大于 | ||
< | 小于 | ||
>= | |||
<= | |||
between and | 在某个范围内,闭合区间 | ||
and | && | 5>1and1>2 | false |
or | || | 5>1or1>2 | true |
注意:
-
column_name 是数据库的列,带上``
-
条件,是筛选的条件,如果没有指定,则会修改所有的列
-
value 是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用英文逗号隔开
UPDATE `student` SET `birthday`=CURRENT_TIME where `name`='李四' AND SEX = '男'
关于between的解释:
SQL中 between and是包括边界值的,not between不包括边界值,不过如果使用between and 限定日期需要注意,如果and后的日期是到天的,那么默认为00:00:00 例如:and 后的日期为2018年09月28日,就等价于2018-09-28 00:00:00 ,那么2018-09-28 11:24:54.000的数据就查不到了,如果要查到2018-09-28这一整天的数据,那么在取值的时候需要加1天,即BETWEEN '2018-06-28' AND '2018-09-29',这样返回的就是6月28日(含)到9月28日(含)的所有数据了。
3.4 删除
delete 命令
语法 delete from 表名 [where 条件]
-- 删除数据 (避免这样写)
DELETE FROM `student`
-- 删除指定
DELETE FROM `student` where id= 1
truncate和delete区别
共同点:
1)都可以清空表中的数据。
不同点:
1)truncate在使用时,不能加where条件。
2)truncate执行操作时,速度更快且不可回滚;是因为TRUNCATE操作不会记录到事务日志中,而DELETE
操作会记录到事务日志中,记录日志会耗时,所以TRUNCATE要快于DELETE 且 truncate不可回滚。
3)truncate删除表数据是:先删除整张表包括数据,再重新创建表,因此,若果表中有自增长
会把自增长id 重置成1开始。 delete删除表数据时, 每次从表中删除一行。
4)表和索引所占空间。 当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小,
而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
5)应用范围。TRUNCATE 只能对TABLE;DELETE可以是table和view
6) truncate、drop是DLL(data define language),操作立即生效,不会记录到事务日志中去
7)在没有备份情况下,谨慎使用 drop 与 truncate。要删除部分数据行采用delete
且注意结合where来约束影响范围
4、DQL查询数据(最重点)
语法:关键字顺序固定,不能换位,例如:limit不能写在order by前面去
4.1DQL
(Data Query Language) :数据查询语言
- 所有的查询操作都用它 Select
- 简单的查询,复杂的查询它都能做
- 数据库中最核心的语言
- 使用频率最高的语言
4.2指定查询字段
-- 查询 SELECT 字段 FROM 表
-- 查询指定字段 such as
SELECT `StudentNo`,`StudentName` FROM student
-- 别名,给结果起一个名字 AS 可以给字段起别名 也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName`AS 学生姓名 FROM student AS S
-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
语法: SELECT 字段 ... FROM 表
有时候,列名字不是那么见名知意。我们起别名 AS 字段名 AS 别名 表名 AS 别名
去重
作用:去除select语句查询出来的结果中重复的语句,重复的语句只显示一条
distinct去重
distinct可以对某个字段进行去重,但是如果你用了多个字段那他就是在这些字段同时满足的情况下去重,这样的去重就是去重所有的字段,换句话就是必须是完全一样的数据才会被去重(此方法用的少),如图
SELECT examid,firstanswer,thirdanswer from answer;
SELECT distinct examid,firstanswer,thirdanswer from answer ;
group by去重
group by是对某个字段分组, 换个话就是对单个字段或者查询多个字段中根据某个字段进行去重(此方法比较常见)
SELECT examid,firstanswer,thirdanswer from answer;
SELECT examid,firstanswer,thirdanswer from answer GROUP BY examid
-- 查询一下有哪些同学参加了考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
-- 查询有哪些同学参加了考试
SELECT `studentNo` FROM result
-- 发现重复数据,去重
SELECT DISTINCT `studentNo` FROM result
数据库的列(表达式)
SELECT VERSION() --查询系统版本(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment --查询自增的步长(变量)
-- 学员考试成绩+1 分 查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
数据库中的表达式: 文本值,列,Null , 函数,计算表达式,系统变量…
select 表达式
from 表
4.3where 条件子句
作用:检索数据中符合条件的值
逻辑运算符
运算符 | 语法 | 结果 |
---|---|---|
and && | a and b a&&b | 逻辑与 |
or || | a or b a||b | 逻辑或 |
Not != | not a !a | 逻辑非 |
尽量使用英文
-- 查询考试成绩在95分到100分之间
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 AND StudentResult<=100
-- 模糊查询(区间)
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学成绩
SELECT `StduentNo`,`StudentResult` FROM result
WHERE NOT StudentNo = 1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
I S NULL | a is null | 如果操作符为null 结果为真 |
IS NOT NULL | a is not null | 如果操作符为not null 结果为真 |
BETWEEN | a between b and c | 若a在b 和c之间则为真 |
LIKE | a like b | SQL匹配,如果a 匹配到b 则为真 |
IN | a in (a1,a2,a3…) | 假设a 在 a1,a2,a3其中的某一个中,为真 |
-- 查询姓刘的同学
-- like结合 %(代表0到任意字符) _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%';
-- 查询姓刘的同学,名字后只有一个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_';
-- 查询姓刘的同学,名字后只有两个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘__';
-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';
===================IN(具体的一个或者多个值)===========================
-- 查询1001 1002 1003 学员信息
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询在北京的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('安徽','河南洛阳');
===================NULL NOT NULL===================================
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=''OR address IS NULL
-- 查询有出生日期的同学 不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;
4.4 联表查询
笛卡尔积问题:
笛卡尔积是一个数学概念,在数据库中表示为两个表中所有可能的行组合。如果你有两个表,一个有
N
行,另一个有M
行,那么这两个表的笛卡尔积将包含N * M
个结果。在数据库查询中,如果没有指定连接条件,就会产生笛卡尔积,这通常不是你想要的结果。
一文搞懂SQL中的各种联结——内联结、自然联结、自联结、交叉联结_es left join-CSDN博客
(关联查询不必须是主外键关系,而且不建议使用外键,只要找到关联表中的关联字段即可)
JOIN 对比
======================联表查询 join ==============================
-- 查询参加考试的同学 (学号,姓名,考试编号,分数)
SELECT * FROM student
SELECT * FROM result
/*
1. 分析需求,分析查询的字段来自哪些表
2.确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件: 学生表中 studentNo = 成绩表中 studentNo
*/
-- JION(表) ON (判断的条件)连接查询 (关联查询不必须是主外键关系,而且不建议使用外键,只要找到关联表中的关联字段即可)
-- where 等值查询 (关联查询不必须是主外键关系,而且不建议使用外键,只要找到关联表中的关联字段即可)
--LEFT Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
--Right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo=r.studentNo
通过上面3个查询得出结论:
内连接(INNER JOIN) :只连接匹配的行;
左外连接(LEFT JOIN或LEFT OUTER JOIN) :包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行;左表有右表没找到时自动填充null
右外连接(RIGHT JOIN或RIGHT OUTER JOIN) :包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行;右表有左表没找到时自动填充null
示例1:查询缺考的同学
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE r.studentNo IS NULL
上面2个SQL查询结果在这里显示一样的:
student:
result:
查询结果: (个人理解,第一条语句通过studentresult is null判断;第二条语句通过关联的result.studentNo is null判断,因为当2张表studentNo不匹配,以左表为基准,自动给右表联合查询字段填充null,其实是给右表这1条数据,均填充了null)
示例2:-- 查询了参加考试同学的信息:学号:学生姓名:科目名:分数
SELECT s.`studentNo`,`studentName`,`SubjectName`,`studentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo=s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo
提示:无区别left join 是left outer join的简写,left join默认是outer属性的。right join等同理
结论:
FROM a LEFT JOIN b 以左表a为基准
FROM a RIGHT JOIN b 以右表b为基准
FROM a INNER JOIN b 基准是交叉部分,交叉的结果拿出来,然后把左表和右表中符合的也拿出来
------------------------------------------------------------------------------------------------------------------------------
注意:字段中可以使用select查询
select
count(*),
sum(bp.staff_num) as psum,
(
select
count(*)
from
byd_vending_machine) as msum,
(
select
sum(bp.staff_num)/ count(*)
from
byd_vending_machine) as prp
from
byd_park bp
4.5分页和排序
============================分页 limit 和排序order by=================
-- 排序: 升序ASC 降序 DESC
SELECT xx
FROM xx
JOIN xx
WHERE xx
ORDER BY xx
ASC || DESC
分页
-- 为什么要分页
-- 缓解数据库压力,给人的体验更好
-- 分页,每页显示五条数据
-- 语法: limit 当前页,页面的大小
-- limit 0,5 1-5
-- limit 1,5 1-5
-- limit 6,5
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`subjectNo`
WHERE subjectName='数据结构-1'
ORDER BY StudentResult ASC
LIMIT 0,5
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit 5*(n-1),5
语法 limit(查询起始下标,pagesize)
mysql order by 多个字段排序
工作中需用到order by 后两个字段排序,但结果却产生了一个Bug,以此备录。
【1】复现问题场景
为了说明问题,模拟示例数据库表students,效果同实例。
如下语句Sql_1:
1 SELECT * FROM students st ORDER BY st.sAge, st.sGrade DESC;
(1)预期结果:
sAge和sGrade两个字段都按降序排列
(2)实际结果:
sAge按升序排序,sGrade按降序排列
(3)分析原因:
order by 多个字段时,Sql语法理解错误导致查询结果集与期望不符。
【2】默认升序
MySql中,order by 默认是按升序排列的。
示例语句Sql_2:
1 SELECT * FROM students st ORDER BY st.sAge;
查询结果集:
显然,不写排序方式,默认是升序。
【3】多个字段时,各自定义升降序
MySql中,order by 多个字段时,需要各自分别定义升降序。
当然,正是本文开始处没有按此语法写Sql语句造成Bug的原因。
示例语句Sql_3:
1 SELECT * FROM students st ORDER BY st.sAge DESC, st.sGrade DESC;
查询结果集:
显然,Sql_1想要实现Sql_3的结果是错误的。因为不写排序方式默认是升序。
【4】多个字段时,按先后顺序排优先级
MySql中,order by 多个字段时,按字段先后顺序排优先级。
以上的示例中,我们只使用了两个字段,下面使用三个字段验证一下这个规则。
(1)按sAge升序、sGrade降序、sStuId降序查询
示例语句Sql_4:
1 SELECT * FROM students st ORDER BY st.sAge ASC, st.sGrade DESC, st.sStuId DESC;
查询结果集:
显然,前两个字段排列顺序确定后,sStuId值没有按降序排列
(2)按sAge升序、sStuId降序、sGrade降序查询
示例语句Sql_5:
1 SELECT * FROM students st ORDER BY st.sAge ASC, st.sStuId DESC, st.sGrade DESC;
查询结果集:
显然,前两个字段排列顺序确定后,sGrade值没有按降序排列
比较Sql_4与Sql_5语句的区别,再对比查询结果集,慢慢体会这两个差异点。
【5】总结
(1)在MySql中,使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。
(2)尤其非常特别重要:默认按升序(ASC)排列。
(3)order by 后可加2个(或多个)字段,字段之间用英文逗号隔开。
(4)若A用升序,B用降序,SQL该这样写:order by A ASC, B DESC; 默认同理,也可以这样写:order by A, B DESC;
(5)若A、B都用降序,必须用两个DESC,order by A DESC, B DESC;
(6)多个字段时,优先级按先后顺序而定。
4.6 子查询
where (这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
-- ===========================where=========================
-- 1.查询 数据库结构-1的所有考试结构(学号,科目编号,成绩) 降序
-- 方式一: 连接查询
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sun.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC
-- 方式二:使用子查询(由里及外)
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result`
WHERE StudentNo=(
SELECT SubjectNo FROM `subject`
WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC
-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80
-- 在这个基础上 增加一个科目 ,高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80 AND `SubjectNo`=(
SELECT Subject FROM `subject`
WHERE SubjectName='高等数学-2'
)
-- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectName`='高等数学-2'
WHERE `SubjectaName`='高等数学-2' AND StudentResult >=80
-- 再改造 (由里即外)
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =(
SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='高等数学-2'
)
)
4.7 分组
sql中 group by多个字段,对所有字段做group by-CSDN博客
语法
select
聚合函数,
列(要求出现在group by的后面)
from
表
where
筛选条件
group by
分组的列表
order by
子句
注意:除了出现在group by后面的字段,如果要在select后查询其他字段,必须用聚合函数进行聚合
特点:分组查询中的筛选条件分为两类:
分组前筛选: 数据源是原始表,用where,放在group by前面,因为在分组前筛选
分组后筛选:数据源是分组后的结果集 ,用having,放在group by后面,因为在分组后进行筛选。
group by的应用场景
我们已经知道select语句结合where查询条件获取需要的数据,但在实际的应用中,还会遇到下面这类需求,比如:公司想知道每个部门有多少名员工;班主任想统计各科第一名的成绩;某门店想掌握男、女性会员的人数及平均年龄
group by的使用
从字面上理解,group by表示根据某种规则对数据进行分组,它必须配合聚合函数进行使用,对数据进行分组后可以进行count、sum、avg、max和min等运算。
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:(根据不同的课程分组)
SELECT `SubjectName`,AVG(StudentResult),MAX(StudentResult)
FROM result r
INNER JOIN `Subject` sub
ON r.SubjectNo=sub.SubjectNo
GROUP BY r.SubjectNo -- 通过什么字段来分组
HAVING AVG(StudentResult)>80
//HAVING :过滤分组后的条件,和group by一起使用
按年-季度-月进行分组统计
#按年统计
SELECT SUM(browseNum) AS browseNum, COUNT(*) AS sheets FROM sbsm_count_picture GROUP BY DATE_FORMAT(DATE, '%Y');
#按月统计
SELECT SUM(browseNum) AS browseNum,SUM(downloadNum) AS downloadNum,SUM(telBrowseNum) AS telBrowseNum,DATE_FORMAT(DATE, '%Y-%m'),
SUM(telDownloadNum) AS telDownloadNum,
COUNT(*) AS sheets FROM sbsm_count_picture GROUP BY DATE_FORMAT(DATE, '%Y-%m');
#按季度统计
SELECT SUM(browseNum) AS browseNum,SUM(downloadNum) AS downloadNum,SUM(telBrowseNum) AS telBrowseNum,
CONCAT(DATE_FORMAT(DATE, '%Y'),FLOOR((DATE_FORMAT(DATE, '%m')+2)/3)) AS jidu,
SUM(telDownloadNum) AS telDownloadNum,
COUNT(*) AS sheets FROM sbsm_count_picture GROUP BY CONCAT(DATE_FORMAT(DATE, '%Y'),FLOOR((DATE_FORMAT(DATE, '%m')+2)/3));
————————————————
版权声明:本文为CSDN博主「星狗、」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/a116385895/article/details/100517416
5、MySQL函数
存储过程和函数存在以下几个区别:
存储过程是procedure用户定义的一系列sql语句的集合,涉及特定表或其它对象的任务,用户可以调用存储过程,而函数通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表。
1、一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。存储过程,功能强大,可以执行包括修改表等一系列数据库操作;用户定义函数不能用于执行一组修改全局数据库状态的操作。
2、对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象。函数只能返回一个变量;而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型,而函数只能有IN类~~存储过程声明时不需要返回类型,而函数声明时需要描述返回类型,且函数体中必须包含一个有效的RETURN语句。
3、存储过程,可以使用非确定函数,不允许在用户定义函数主体中内置非确定函数。
4、存储过程一般是作为一个独立的部分来执行( EXECUTE 语句执行),而函数可以作为查询语句的一个部分来调用(SELECT调用),由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程,而可以使用函数。
CALL 语句接收存储过程的名字以及需要传递给它的任意参数,基本语法形式如下:
CALL sp_name([parameter[...]]);
其中,sp_name 表示存储过程的名称,parameter 表示存储过程的参数。
5.1 常用函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('2323232') -- 返回字符串长度
SELECT CONCAT('我','233') -- 拼接字符串
SELECT INSERT('java',1,2,'cccc') -- 从某个位置开始替换某个长度
SELECT UPPER('abc')
SELECT LOWER('ABC')
SELECT REPLACE('坚持就能成功','坚持','努力')
-- 查询姓 周 的同学 ,改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间跟日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
DATE_FORMAT(date,format)
函数用于以不同的格式显示日期/时间数据,
date 参数是合法的日期,alarm_time是数据库的时间字段datetime,alarmEndTime是java.util.Date对象。
我们从前端拿到时间字符串后通过注解@JsonFormat/@DateTimeFormat转为date数据,然后在sql中比较。
format 规定日期/时间的输出格式。
mapper.xml中sql这样写,主要是把他们转换为统一的格式再进行比较
eg1:
date_format(alarm_time,'%Y%m%d%H%%i') between date_format( #{alarmStartTime},'%Y%m%d%H%%i') and date_format(#{alarmEndTime},'%Y%m%d%H%%i')
eg2: <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
and date_format(create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
</if>
eg3: <if test="updateTime != null and updateTime != ''"><!-- 更新时间检索 -->
and date_format(update_time,'%y%m%d') <= date_format(#{updateTime},'%y%m%d')
</if>
<if test="addTime != null and addTime != ''"><!-- 添加时间检索 -->
and date_format(add_time,'%y%m%d') >= date_format(#{addTime},'%y%m%d')
</if>
STR_TO_DATE(str,format)函数是将时间格式的字符串(str),按照所提供的显示格式(format)转换为DATETIME类型的值。
DATE_FORMAT(date,format)函数则是把数据库的日期或者入参的date类型(测试传入字符串也可以)转换为对应的字符串格式,比较常见,不做解释。
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
5.2 聚合函数(常用)
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
… |
MySQL的COUNT()函数理解 - FEFJay - 博客园(重要)
5.3 数据库级别MD5加密(拓展)
什么是MD5
主要增强算法复杂度不可逆性。
MD5不可逆,具体的MD5是一样的
MD5破解原理,背后有一个字典,MD5加密后的值,加密前的值
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8
-- 明文密码
INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id !=1 -- 加密全部
-- 插入时加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))
INSERT INTO testmd5 VALUES(5,'红',MD5('123456'))
-- 如何校验,将用户传递过来的密码,进行MD5加密,然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='红' AND pwd=MD5('123456')
6、事务
相关参考我的这个博客:事务MySQL---事务+事务隔离级别_逮虾户<的博客-CSDN博客
7、外键、主键、索引
7.1外键(了解)
方式一:在创建表的时候,增加约束(麻烦,比较复杂)
CREATE TABLE `grade`( `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id', `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称', PRIMARY KEY (`gradeid`) )ENGINE=INNODB DEFAULT CHARSET=utf8 -- 学生表的 gradeid 字段 要去引用年级表的gradeid -- 定义外键KEY -- 给这个外键添加约束(执行引用) references 引用 CREATE TABLE IF NOT EXISTS `student` ( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `gradeid` INT(10) NOT NULL COMMENT '学生年级', `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`), KEY `FK_gardeid` (`gradeid`), CONSTRAINT `FK_gardeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (gradeid) )ENGINE=INNODB DEFAULT CHARSET=utf8
删除有外键关系的表的时候,必须先删除引用的表(从表),再删除被引用的表(主表)
方式二: 创建表成功后添加外键
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的 gradeid 字段 要去引用年级表的gradeid
-- 定义外键KEY
-- 给这个外键添加约束(执行引用) references 引用
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE`表` ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) 引用到哪个表的哪个字段
以上的操作都是物理外键,即数据库级别外键,不建议使用外键(避免数据库过多造成困扰)
最佳实践:
数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
我们想使用多张表的数据,想使用外键(程序去实现或利用 join)
7.2 主键和索引详解
主键、索引、外键的区别:
主键:
1、主键一定是唯一性索引,而唯一性索引不一定是主键,主键自动生成唯一性索引,主键是特殊的索引。
2、一张表中只能有1个主键,主键列不允许为空值
唯一索引:
1、唯一索引列的值必须唯一,不可重复,可以允许有一条空值(印证了不能重复),这是和主键不同点
2、一张表可以有多条唯一索引
常规索引:
1、没有任何的限制,索引列的值可以重复
外键(不建议使用):
1、表的外键是另一张表的主键,外键是可以重复的,可以为空值
2、外键是用来和其他表建立联系用的(不建议通过主外键列进行关联查询,
可以使用join关键字进行关联表查询)
- 主键 (PRIMARY KEY)
主关键字(primary key)是表中的一个或多个字段,它的值用于唯一地标识表中的某一条记录
- 索引:唯一索引 (UNIQUE KEY)
- 索引:常规索引(KEY/INDEX)
- 默认的,index,key关键字来设置,不宜添加太多常规索引,影响数据的插入,删除和修改操作
- 索引:全文索引(FULLTEXT)( 只能用于MyISAM引擎的数据表,可以略过)
-
百度搜索:全文索引作用 : 快速定位特定数据注意 :只能用于MyISAM类型的数据表只能用于 CHAR , VARCHAR , TEXT 数据列类型适合大型数据集
-
-
索引: 空间索引(SPATIAL)(只能用于MyISAM引擎的数据表,可以略过)
7.3 测试索引
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
//mysql5.7以上DATETIME也支持使用CURRENT_TIMESTAMP
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
//CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP可以在数据类型为TIMESTAMP时表示默认当前时间
和更新时默认为更新的当前时间
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入100万数据
DELIMITER $$ -- 写函数之前必写
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
-- 插入语句
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100))
SELECT mock_data();
SELECT * FROM app_user WHERE `name`='用户9999' -- 接近半秒
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询99999条记录
-- id _ 表名_字段名
-- create index on 字段
CREATE INDEX id_app_user_name ON app_user(`name`); -- 0.001 s
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询一条记录
索引在小数据的时候,用处不大,但是在大数据的时候,区别十分明显
7.4 索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上,即where 或者order by 后面的字段
索引的数据结构
Hash 类型的索引
Btree: 默认innodb 的数据结构
阅读: http://blog.codinglabs.org/articles/theory-of-mysql-index.html
8、权限管理和备份
8.1用户管理
SQLyog 可视化管理
SQL命令操作
用户表:mysql.user
本质:对这张表进行,增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER sanjin IDENTIFIED BY '123456'
-- 修改密码(修改当前密码)
SET PASSWORD = PASSWORD('111111')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR sanjin = PASSWORD('111111')
-- 重命名 rename user 原名字 to 新名字
RENAME USER sanjin TO sanjin2
-- 用户授权 ALL PRIVILEGES 全部的权限 库,表
-- ALL PRIVILEGES 除了给别人授权,其他都能干
GRANT ALL PRIVILEGES ON *.* TO sanjin2
-- 查询权限
SHOW GRANTS FOR sanjin2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- 撤销权限 REVOKE 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM sanjin2
-- 删除用户
DROP USER sanjin2
8.2 MySQL备份
为什么备份:
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份的方式:
- 在SQLyog这种可视化工具中手动导出,在想要导出的表或者库中,右键选择备份和导出
2、使用命令行备份:
导出命令:
注意:如果导出出现红色字体报错,需要导出命令中加上 --column-statistics=0
C:\Users\77952>mysqldump -h 192.168.79.100 -u haiyang01 -phy1304 oceansite-base digital_inspect_equipment digital_inspect_item > D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'oceansite-base' AND TABLE_NAME = 'digital_inspect_equipment';': Unknown table 'column_statistics' in information_schema (1109)
C:\Users\77952>mysqldump --column-statistics=0 -h 192.168.79.100 -u haiyang01 -phy1304 oceansite-base digital_inspect_equipment digital_inspect_item > D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
导入命令:
source D:/init.sql 执行sql文件
或者
mysql -u用户名 -p密码 库名 < 备份的文件.sql 导入备份文件的sql数据
eg:mysql -u root -p123456798a oceansite-base < d:\oceansite-base.sql (注意-p后面没有空格)
常见选项:
-u: 用户名
-p: 密码
-P: 端口号,不写默认3306
--all-databases, -A:备份所有数据库
--databases, -B: 用于备份多个数据库,如果没有该选项,mysqldump把第一个名字参数作为数据库名,后面的作为表名。使用该选项,mysqldum把每个名字都当作为数据库名。
-d: 只导出数据库的表结构
-t: 只导出数据库的数据
--quick, -q:快速导出
--xml, -X:导出为xml文件
导出要用到MySQL的mysqldump工具,基本用法是:
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
如果你不给定任何表,整个数据库将被导出。
通过执行mysqldump --help,你能得到你mysqldump的版本支持的选项表。
注意,如果你运行mysqldump没有--quick或--opt选项,mysqldump将在导出结果前装载整个结果集到内存中,如果你正在导出一个大的数据库,这将可能是一个问题。
链接选项
-u user_name, --user=user_name
与服务器连接时,MySQL使用的用户名。缺省值是你的Unix登录名。
-pyour_pass, --password[=your_pass]
与服务器连接时使用的口令。如果你不指定“=your_pass”部分,mysqldump需要来自终端的口令。
-h, --host=...
从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。
-P port_num, --port=port_num
与一台主机连接时使用的TCP/IP端口号。(这用于连接到localhost以外的主机,因为它使用 Unix套接字。)
输出内容选项
--add-drop-database 每个数据库创建前加上drop database语句
--add-drop-table 在每个create语句之前增加一个drop table。
-n,--no-create-db 不包含数据库的创建语句
-t, --no-create-info
不写入表创建信息(CREATE TABLE语句)
-d, --no-data
不写入表的任何行信息。如果你只想得到一个表的结构的导出,这是很有用的!
输出格式选项
--compact 选项使得输出结果简洁,不包含默认选项中的各种注释。
-c, --complete-insert 使用完整的insert语句(包含列名字)。
-T, --tab=path-to-some-directory
对于每个给定的表,创建一个table_name.sql文件,它包含SQL CREATE 命令,和一个table_name.txt文件,它包含数据。 注意:这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据--fields-xxx和--lines--xxx选项来定。
--fields-terminated-by=... (域分割符)
--fields-enclosed-by=... (域引用符)
--fields-optionally-enclosed-by=... (域可选引用符)
--fields-escaped-by=... (转义字符)
这些选择与-T选择一起使用,并且有相应的LOAD DATA INFILE子句相同的含义。
LOAD DATA INFILE语法。
字符集选项
--default-character-set 将导出的字符集转换为这个参数设置的字符集
[root@kfpanda ~]# mysqldump -uroot --compact --default-character-set=utf8 test t >t.sql
[root@kfpanda ~]#
[root@kfpanda ~]# mysqldump -uroot --compact --default-character-set=gbk test t >t1.sql
[root@kfpanda ~]# file t.sql
t.sql: UTF-8 Unicode text
[root@kfpanda ~]# file t1.sql
t1.sql: ISO-8859 text
mysqldump支持的其他选项:
--add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。
--allow-keywords
允许创建是关键词的列名字。这由表名前缀于每个列名做到。
-C, --compress
如果客户和服务器均支持压缩,压缩两者间所有的信息。
--delayed
用INSERT DELAYED命令插入行。
-e, --extended-insert
使用全新多行INSERT语法。(给出更紧缩并且更快的插入语句)
-#, --debug[=option_string]
跟踪程序的使用(为了调试)。
--help
显示一条帮助消息并且退出。
-F, --flush-logs
在开始导出前,洗掉在MySQL服务器中的日志文件。
-f, --force,
即使我们在一个表导出期间得到一个SQL错误,继续。
-l, --lock-tables.
为开始导出锁定所有表。
--opt
同--quick --add-drop-table --add-locks --extended-insert --lock-tables。
应该给你为读入一个MySQL服务器的尽可能最快的导出。
-q, --quick
不缓冲查询,直接导出至stdout;使用mysql_use_result()做它。
-S /path/to/socket, --socket=/path/to/socket
与localhost连接时(它是缺省主机)使用的套接字文件。
-O var=option, --set-variable var=option设置一个变量的值。可能的变量被列在下面。
-v, --verbose
冗长模式。打印出程序所做的更多的信息。
-V, --version
打印版本信息并且退出。
-w, --where='where-condition'
只导出被选择了的记录;注意引号是强制的!
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"
最常见的mysqldump使用可能制作整个数据库的一个备份:
mysqldump --opt database > backup-file.sql
但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的:
mysqldump --opt database | mysql --host=remote-host -C database
由于mysqldump导出的是完整的SQL语句,所以用mysql客户程序很容易就能把数据导入了:
shell> mysqladmin create target_db_name
shell> mysql target_db_name < backup-file.sql
就是
shell> mysql 库名 < 文件名
导出时忽略多张表:--ignore-table=数据库名1.表名1 --ignore-table=数据库名2.表名2
例如:mysqldump --single-transaction --ignore-table=dota2_db.t1 --ignore-table=dota2_db.t2 --ignore-table=dota2_db.t3 --ignore-table=dota2_db.t4 --ignore-table=dota2_db.t5 -uroot -pk*****1 dota2_db|gzip > /mnt/dota2_db_`date +%Y%m%d`.sql.gz
================================
几个常用用例:
1.导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3.导出一个数据库结构
mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:\wcnc_db.sql
-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table
4.导入数据库
常用source 命令
进入mysql数据库控制台,
如mysql -u root -p
mysql>use 数据库
然后使用source命令,后面参数为脚本文件(如这里用到的.sql)
mysql>source d:\wcnc_db.sql
5、导出、限流并压缩
mysqldump -u[USER] -p[PASSWORD] -h[HOST] -P[PORT] --single-transacXon --master-data=2 [DB]|pv -q -L 10M | gzip > /tmp/test.gzip
备份 :mysqldump
限流 :pv ?
压缩 :gzip
6、解压缩并回复数据库
gunzip -fc /tmp/test.gz | mysql -u[USER] -h[HOST] -P[PORT] DB
解压 : gunzip
恢复 :mysql
9、规范数据库设计和视图
视图博客:MySQL学习总结{ 视图概述、视图管理(创建、查看、修改、删除)、视图数据操作(添加、修改、删除、)}_create or replace view-CSDN博客
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦,异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图 E-R图
设计数据库的步骤(个人博客)
-
收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 友链表(友链信息)
- 自定义表(系统信息,某个关键的字,或者某些主字段)
- 说说表(发表心情…id ,content ,time)
-
标识实体(把需求落地到每个字段)
-
标识实体之间的关系
- 写博客 user–>blog
- 创建分类 user–>category
- 关注 user–>user
- 友链–>links
- 评论 user–>user
9.2三大范式
为什么需要数据规范化?
-
信息重复
-
更新异常
-
插入异常
-
删除异常
- 无法正常显示异常
-
删除异常
- 丢失有效的信息
三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
(规范数据库的设计)
规范性和性能的问题
关联查询的表,不得超过三张表
- 考虑商业化的需求和目标(成本和用户体验) 数据库的性能更加重要
- 再规范性能的问题的时候,需要适当的考虑一下,规范性
- 故意给某些表加一些冗余的字段(从多表,变成单表)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引
后面还有jdbc的部分,写不下了,详见博客:javaWeb狂神_07(JDBC狂神/尚学堂+JDBC事务代码)_逮虾户<的博客-CSDN博客
10、数据库日志:
(一)概述
MySQL中存在着以下几种日志:重写日志(redo log)、回滚日志(undo log)、二进制日志(bin log)、错误日志(error log)、慢查询日志(slow query log)、一般查询日志(general log)。
MySQL中的数据变化会体现在上面这些日志中,比如事务操作会体现在redo log、undo log以及bin log中,数据的增删改查会体现在 binlog 中。本章是对MySQL日志文件的概念及基本使用介绍,不涉及底层内容。针对开发人员而言,这几种日志中最有可能使用到的是慢查询日志。
(二)redo log
redo log是一种基于磁盘的数据结构,用来在MySQL宕机情况下将不完整的事务执行数据纠正,redo日志记录事务执行后的状态。
当事务开始后,redo log就开始产生,并且随着事务的执行不断写入redo log file中。redo log file中记录了xxx页做了xx修改的信息,我们都知道数据库的更新操作会在内存中先执行,最后刷入磁盘。
redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据。
(三)undo log
undo log主要用来回滚到某一个版本,是一种逻辑日志。undo log记录的是修改之前的数据,比如:当delete一条记录时,undolog中会记录一条对应的insert记录,从而保证能恢复到数据修改之前。在执行事务回滚的时候,就可以通过undo log中的记录内容并以此进行回滚。
undo log还可以提供多版本并发控制下的读取(MVCC)。
(四)bin log
MySQL的bin log日志是用来记录MySQL中增删改时的记录日志。简单来讲,就是当你的一条sql操作对数据库中的内容进行了更新,就会增加一条bin log日志。查询操作不会记录到bin log中。bin log最大的用处就是进行主从复制,以及数据库的恢复。
通过下面的命令可以查看是否开启binlog日志
show VARIABLES like '%log_bin%'
开启binlog的方式如下:
log-bin=mysql-bin
server-id=1
binlog_format=ROW
其中log-bin指定日志文件的名称,默认会放到数据库目录下,可通过以下命令查看
show VARIABLES like '%datadir%'
(五)error log
error log主要记录MySQL在启动、关闭或者运行过程中的错误信息,在MySQL的配置文件my.cnf中,可以通过log-error=/var/log/mysqld.log 执行mysql错误日志的位置。
通过MySQL的命令
show variables like "%log_error%";
也可以获取到错误日志的位置。
(六)slow query log
慢查询日志用来记录执行时间超过指定阈值的SQL语句,慢查询日志往往用于优化生产环境的SQL语句。可以通过以下语句查看慢查询日志是否开启以及日志的位置:
show variables like "%slow_query%";
慢查询日志的常用配置参数如下:
slow_query_log=1 #是否开启慢查询日志,0关闭,1开启
slow_query_log_file=/usr/local/mysql/mysql-8.0.20/data/slow-log.log #慢查询日志地址(5.6及以上版本)
long_query_time=1 #慢查询日志阈值,指超过阈值时间的SQL会被记录
log_queries_not_using_indexes #表示未走索引的SQL也会被记录
分析慢查询日志一般会用专门的日志分析工具。找出慢SQL后可以通过explain关键字进行SQL分析,找出慢的原因。
(七)general log
general log 记录了客户端连接信息以及执行的SQL语句信息,通过MySQL的命令
show variables like '%general_log%';
可以查看general log是否开启以及日志的位置。
general log 可通过配置文件启动,配置参数如下:
general_log = on
general_log_file = /usr/local/mysql/mysql-8.0.20/data/hecs-78422.log
普通查询日志会记录增删改查的信息,因此一般是关闭的。
百度安全验证
MySQL日志详解 - 知乎(binlog的查询删除恢复数据库)