MySQL-学习笔记

MySQL

SQL

发布日期: 2019-07-10

更新日期: 2023-02-27

文章字数: 3.5k

阅读次数:

MySQL安装

Win mysql安装：Windows下安装MySQL详细教程 - m1racle - 博客园 (cnblogs.com)

Mac mysql安装：在MAC上安装mysql_码农伍的博客-CSDN博客_mac安装mysql

库操作

数据库连接

mysql -h 主机名 -P 端口 -u 用户名 -p 密码

mysql -h 127.0.0.1 -P 3306 -u root -p root

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名 CHARACTER SET 数据库采用的字符集 COLLATE 字符集的校对规则

CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf8
COLLATE:指定数据库字符集的校对规则
- utf8_bin 区分大小写
- utf8_general_ci 不区分大小写
- 默认是utf8_general_ci

CREATE DATABASE [IF NOT EXISTS] jwt CHARACTER SET utf8 COLLATE utf8_bin

删除数据库

DROP DATABASE [IF EXISTS ] 数据库名

查看所有数据库

SHOW DATABASES

查看数据库的定义信息

SHOW CREATE DATABASE `数据库名`

注：创建数据库、表的时候，为了规避关键字，可以使用反引号解决

备份数据库

#备份数据库
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 路径/文件名.sql

#备份数据库中表
mysqldump -u 用户名 -p  数据库 表1 表2 表n > 路径/文件名.sql

注：该指令要在DOS下执行

恢复数据库

Source 路径/文件名.sql

注：该指令要进入mysql下执行

数据类型

数值型(整数)

类型	字节	最小值	最大值
TINYINT	1	-128	127
TINYINT(unsigned)	1	0	255
SMALLINT	2	-32768	32767
SMALLINT(unsigned)	2	0	65535
MEDIUMINT	3	-8388508	8388607
MEDIUMINT(unsigned)	3	0	16777215
INT	4	-2147483648	2147483647
INT(unsigned)	4	0	16777215
BIGINT	8	-9223372036854775808	9223372036854775807
BIGINT(unsigned)	8	0	18446744073709551615

CREATE TABLE t1 (id TINYINT);#默认是有符号的，-128~127
CREATE TABLE t2 (id TINYINT UNSIGNED);#指定unsinged,就是无符号0~255

数值型(bit)

数值型(小数)

字符串(char&varchar)

CHAR(size)
- 固定长度字符串最大255 字符
VARCHAR(size) 0~65535 字节
- 可变长度字符串最大65532 字节【本身还需要1-3个字节用于记录存放内容长度】
- 如果表的编码是utf8 ，size = (65535-3) / 3 = 21844
- 如果表的编码是gbk ， size = (65535-3) / 2 = 32766
查询速度 CHAR > VARCHAR，因此数据是定长时，推荐使用CHAR

如果 VARCHAR 不够用，可以使用mediumtext、text、longtext

日期类型

CREATE TABLE b_name (
  birthday DATE , -- 生日
  job_time DATETIME, -- 记录年月日时分秒
  login_time TIMESTAMP
  NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP); -- 登录时间, 如果希望login_time列自动更新, 需要配置

SELECT * FROM b_name;
INSERT INTO b_name(birthday, job_time)
VALUES('2022-11-11','2022-11-11 10:10:10');
-- 如果我们更新b_name表的某条记录，login_time列会自动的以当前时间进行更新

表操作

创建表

CREATE TABLE 表名(
    field1 datatype NOT NULL AUTO_INCREMENT，PRIMARY KEY,
    field2 datatype DEFAULT 'test',
    field3 datatype,
)character set 字符集 collate 校对规则 engine 引擎

注：
field:指定列名，datatype: 指定列类型(字段类型)
character set :如不指定则为所在数据库字符集
collate:如不指定则为所在数据库校对规则
engine:引擎(这个涉及内容较多，后面单独讲解)

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
DEFAULT关键字用于定义列的默认值

CREATE TABLE `user`(
  id INT,
  name VARCHAR (255),
  password VARCHAR (255),
  birthday DATE
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB ;

查看表结构

DESC 表名  -- 显示表结构，可以查看表的所有列

删除表

DROP TABLE [IF EXISTS] 表名

添加列

ALTER TABLE 表名
ADD 列名 数据类型 [DEFAULT expr]

-- 在员工表emp中增加一个image列，varchar类型(要求在resume后面)，默认不为空，默认数据为test。
ALTER TABLE emp
ADD image VARCHAR(32) NOT NULL DEFAULT 'test'
AFTER RESUME

DESC emp -- 显示表结构，可以查看表的所有列

修改列

ALTER TABLE 表名
MODIFY 列名 数据类型 [DEFAULT expr]

-- 修改job列，使其长度为60。
ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT 'test'

删除列

ALTER TABLE 表名
DROP 列名 

-- 删除sex列。
ALTER TABLE emp
DROP sex

修改表名

RENAME TABLE 表名 TO 新表名

-- 修改表名改为employee。
RENAME TABLE emp TO employee

修改表字符集

ALTER TABLE 表名 CHARACTER SET utf8

-- 修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8

修改列名

ALTER TABLE 表名
CHANGE 列名 新列名 数据类型 [DEFAULT expr]

-- 列名name 修改为user_name
ALTER TABLE employee
CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT 'test'

CRUD语句

CRUD即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

增加(Create)

-- 普通插入
INSERT INTO 表名 VALUE (value1,value2)

-- 指定要插入数据的列
INSERT INTO 表名 (column1,column2) VALUE (value1,value2) 

-- 批量插入（应注意SQL的长度限制）
INSERT INTO TABLE VALUES (value1,value2),(value1,value2)

-- 把一个表的数据插入到另一个表中(插入全部数据)
INSERT INTO 目标表 SELECT * FROM 来源表  [WHERE key=value]

-- 把一个表的数据插入到另一个表中(指定字段)
INSERT  INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2,... FROM 来源表  [WHERE key=value]

查询(Retrieve)

SELECT column1,column2 FROM 表名

-- 查询表中所有学生的信息。
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;
-- 过滤表中重复数据。
SELECT DISTINCT english FROM student;
-- 使用表达式对查询列运算
SELECT `name`, (chinese+english+math) FROM student;
-- 在所有学生总分加10 分的情况
SELECT `name`, (chinese + english + math + 10) FROM student;
-- 使用别名表示学生分数。
SELECT `name` , (chinese + english + math + 10) AS total_score FROM student;

更新(Update)

-- 一次更新一个字段
UPDATE table SET column1 = value1 WHERE 条件 -- 如果不加WHERE条件，UPDATE语句会默认更新所有表，慎用。

-- 一次更新多个字段
UPDATE table SET column1 = value1,column2 = value2 WHERE 条件


-- 将所有员工薪水修改为5000 元。
UPDATE employee SET salary = 5000
-- 将姓名为小妖怪的员工薪水修改为3000 元。
UPDATE employee SET salary = 3000 WHERE user_name = '小妖怪'
-- 将老妖怪的薪水在原有基础上增加1000 元
UPDATE employee SET salary = salary + 1000 WHERE user_name = '老妖怪'
-- 可以修改多个列的值
UPDATE employee SET salary = salary + 1000 , job = '码农' WHERE user_name = '老妖怪'
SELECT * FROM employee;

删除(Delete)

DELETE FROM 表名 WHERE column1 = value1

-- 删除表中名称为’老妖怪’的记录。
DELETE FROM employee WHERE user_name = '老妖怪';
-- 删除表中所有记录, 慎用。
DELETE FROM employee;

事务

事务用于保证数据的一致性它由一组相关的 dml 语句组成，该组的 dml 语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。

当执行事务操作时(dml语句)，mysql会在表上加锁，防止其它用户改表的数据

事务的几个重要操作

start transaction –开始一个事务
savepoint 保存点名 –设置保存点
rollback to 保存点名 –回退事务
rollback –回退全部事务
commit –提交事务，所有的操作生效，不能回退

-- 1. 创建一张测试表
CREATE TABLE test
( id INT,
`name` VARCHAR(32));

-- 2. 开始事务
START TRANSACTION

-- 3. 设置保存点
SAVEPOINT a
-- 执行dml 操作
INSERT INTO test VALUES(100, 'tom');

SELECT * FROM test;

SAVEPOINT b
-- 执行dml 操作
INSERT INTO test VALUES(200, 'jack');

-- 回退到b
ROLLBACK TO b

-- 继续回退a
ROLLBACK TO a

-- 如果这样, 表示直接回退到事务开始的状态.
ROLLBACK

-- 提交事务
COMMIT

保存点(savepoint)：保存点是事务中的点，用于取消部分事务，
当执行回退事务(rollback)时，通过指定保存点可以回退到指定的点
当提交事务(commit)时 ,会自动的删除该事务所定义的所有保存点，当执行了 commit 语句后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。

事务细节讨论

1.如果不开始事务，默认情况下，dml 操作是自动提交的，不能回滚
2.如果开始一个事务，你没有创建保存点，你可以执行 rollback，默认就是回退到你事务开始的状态
3.你也可以在这个事务中(还没有提交时)，创建多个保存点.比如: savepoint aaa; 执行dml，savepoint bbb;
4.你可以在事务没有提交前，选择回退到哪个保存点
5, mysql的事务机制需要 innodb 的存储引擎才可以使用，myisam不好使.
6.开始一个事务 start transaction 或者 set autocommit=off;

事务隔离级别

1.多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
2.如果不考虑隔离性，可能会引发如下问题:

脏读(dirty read)
- 当一个事务读取另一个事务尚未提交的改变(update,insert,delete)时，产生脏读
不可重复读(nonrepeatable read)
- 同一查询在同事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
幻读(phantom read)
- 同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

1. 查看当前会话隔离级别
select @@tx_isolation;
2.查看系统当前隔离级别
select @@global.tx_isolation;
3. 设置当前会话隔离级别
set session transaction isolation level repeatable read;
4.设置系统当前隔离级别
set global transaction isolation level repeatable read;
5.mysql默认的事务隔离级别是repeatable read ,一般情况下，没有特殊
要求，没有必要修改(因为该级别可以满足绝大部分项目需求)

●全局修改，修改my.ini配置文件，在最后加上
#可选参数有: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
[mysqld]
transaction-isolation = REPEATABLE-READ

事务的ACID

1.原子性(Atomicity)
- 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2.一致性(Consistency)
- 事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3.隔离性(Isolation)
- 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
4.持久性(Durability)
- 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

日期函数

DAYOFWEEK(date)

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

select DAYOFWEEK('1998-02-03')
-> 3

WEEKDAY(date)

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5

DAYOFMONTH(date)

返回date的月份中日期，在1到31范围内。

mysql> select DAYOFMONTH('1998-02-03');
-> 3

DAYOFYEAR(date)

返回date在一年中的日数, 在1到366范围内。

mysql> select DAYOFYEAR('1998-02-03');
-> 34

DAYNAME(date)

返回date的星期名字。

mysql> select DAYNAME("1998-02-05");
-> 'Thursday'

MONTHNAME(date)

返回date的月份名字。

mysql> select MONTHNAME("1998-02-05");
-> 'February'

QUARTER(date)

返回date一年中的季度，范围1到4。

mysql> select QUARTER('98-04-01');
-> 2

WEEK(date)

对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。

mysql> select WEEK('1998-02-20');
-> 7

WEEK(date,first)

2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
如果第二个参数是0，星期从星期天开始，
如果第二个参数是1，从星期一开始。

mysql> select WEEK('1998-02-20',0);
-> 7

mysql> select WEEK('1998-02-20',1);
-> 8

YEAR(date)

返回date的年份，范围在1000到9999。

mysql> select YEAR('98-02-03');
-> 1998

MONTH(date)

返回date的月份，范围1到12。

mysql> select MONTH('1998-02-03');
-> 2

DAY(date)

返回date的月份，范围1到31。

mysql> select MONTH('1998-02-03');
-> 3

HOUR(time)

返回time的小时，范围是0到23。

mysql> select HOUR('10:05:03');
-> 10

MINUTE(time)

返回time的分钟，范围是0到59。

mysql> select MINUTE('98-02-03 10:05:03');
-> 5

SECOND(time)

回来time的秒数，范围是0到59。

mysql> select SECOND('10:05:03');
-> 3

PERIOD_ADD(P,N)

增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。

mysql> select PERIOD_ADD(9801,2);
-> 199803

PERIOD_DIFF(P1,P2)

返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。

mysql> select PERIOD_DIFF(9802,199703);
-> 11

DATE_ADD()

向日期添加指定的时间间隔。

DATE_ADD(date,INTERVAL expr type)

//interval是固定标志，expr 表示数量，可以为正负，表示加减，type表示日期类型 可以是yy,ww,dd等分别表示，年，周，天等

// 得到当前时间增加1个小时的结果
select date_add(now(),interval 1 hour)

// 得到当前时间增加1天的结果
select date_add(now(),interval 1 day)

// 得到当前时间减少72个小时的结果
select date_add(now(),interval -72 hour)

// 还有一些更加高级的用法，比如增加一天一小时一分一秒
// 但是如果你不太喜欢这种用法，你仍然可以使用等同于该数值的second来进行加减
date_add(now(),interval '1 1:1:1' day_second)

JOIN

SQL JOIN 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

❤️Sponsor

您的支持是我不断前进的动力，如果您恰巧财力雄厚，又感觉本文对您有所帮助的话，可以考虑打赏一下本文，用以维持本博客的运营费用，拒绝白嫖，从你我做起！🥰🥰🥰

支付宝支付	微信支付

简简

https://jwt1399.top/posts/27695.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源简简 !

MySQL

评论填上邮箱会收到评论回复提醒哦!!!

记一次漏洞挖掘

题目一：PHP网页0x01 SQl注入OWASP扫描,爆出sql注入漏洞利用爆出的万能钥匙（ZAP' OR '1'='1' -- ）进行登录登录成功 0x02 文件上传第一步登录成功后，发现疑似上传点

2019-07-12 Web

漏洞挖掘

Upload-labs学习笔记

upload-labs包含漏洞类型分类如何判断上传漏洞类型?上传的过程 Pass-01（前端JS绕过）function checkFile() { var file = document.getElementsByName('upl

2019-07-10 Web

靶场文件上传

MySQL-学习笔记

MySQL安装

库操作

数据库连接

创建数据库

删除数据库

查看所有数据库

查看数据库的定义信息

备份数据库

恢复数据库

数据类型

数值型(整数)

数值型(bit)

数值型(小数)

字符串(char&varchar)

日期类型

表操作

创建表

查看表结构

删除表

添加列

修改列

删除列

修改表名

修改表字符集

修改列名

CRUD语句

增加(Create)

查询(Retrieve)

更新(Update)

删除(Delete)

事务

事务的几个重要操作

事务细节讨论

事务隔离级别

事务的ACID

日期函数

DAYOFWEEK(date)

WEEKDAY(date)

DAYOFMONTH(date)

DAYOFYEAR(date)

DAYNAME(date)

MONTHNAME(date)

QUARTER(date)

WEEK(date)

WEEK(date,first)

YEAR(date)

MONTH(date)

DAY(date)

HOUR(time)

MINUTE(time)

SECOND(time)

PERIOD_ADD(P,N)

PERIOD_DIFF(P1,P2)

DATE_ADD()

JOIN

❤️Sponsor

你的赏识是我前进的动力