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