MySQL-学习笔记

小简

发布于 2022-12-27 14:17:07

3410

发布于 2022-12-27 14:17:07

文章被收录于专栏：简言之简言之

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 种用法。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019-07-10，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

云数据库 SQL Server

utf8

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度