mysql学习笔记

mysql学习笔记

mysql笔记

用户设置

修改root密码

set password for root@（本机地址，默认localhost） = password(‘新密码’);
//exit;退出重新登陆就行啦
//也可以使用update对user直接进行修改呦

添加用户

GRANT 用户权限列表 ON 数据库 TO ‘用户名’@‘访问主机’ IDENTIFIED BY ‘密码’;
//添加只有本地可以连接且只有查询插入test库下所有表的（.* 代表该数据库下所有的表,不能只写数据库）权限的用户
GRANT SELECT,INSERT ON test.* TO ‘user1’@‘localhost’ IDENTIFIED BY’12345’;
all privileges 表示所有权限
//查询是否添加成功
use mysql;
//查看是否出现自己添加的用户名即可
SELECT User FROM user;

删除用户

//使用root用户登录
drop user ‘用户名’@'主机’

数据库备份

mysqldump -uroot -p 数据库名称 > test.sql

数据库恢复

mysql -uroot -p 新数据库名称 < test.sql

mysql基本命令

查询所有的库

SHOW DATABASES

进入某数据库中

USE 数据库名称
例： USE mysql;

查询数据库中所有的表

• 进入需要查询的库后执行

SHOW TABLES;

查询表的详细信息

• 显示该表的属性，类型，主键，是否为空，…

SHOW COLUMNS FROM 表名;

查看储存引擎

SHOW ENGINES

查看索引信息

• 查看表的索引信息，包括主键

SHOW INDEX FROM 表名;

使用数据库

创建数据库

CREATE DATABASE 数据库名称;

• 如果数据库不存在则创建，存在则不创建*

CREATE DATABASES IF NOT EXISTS 名称;

删除数据库

DROP DATABASE 数据库名称;

创建数据表

• 若字段值不能为空则给字段加： NOT NULL
• 字段自增长属性： AUTO_INCREMENT
• 设置主键 ：PRIMARY KEY(字段名称)，(通常用于主键，若加在字段后则不用写字段名)
• ENGINE ：设置储存引擎
• CHARSET：设置字符集*
• 在添加字段时可以在每个末尾处加上COMMENT ‘备注’，便于提高识别度

CREATE TABLE 表名 (
字段1,
字段2,
字段3
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

修改数据表名称（使用TO或者AS都可以，也可以省略不用）

ALTER TABLE 原始表名 RENAME TO 新表名 
//第二种方法（不能省略TO|AS）
RENAME TABLE 原始表名 TO 新表名

• 修改字段名称

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型

• 修改字段默认值

ALTER TABLE 表名 ALTER 字段 SET DEFAULT 默认值;

• 删除默认值

ALTER TABLE 表名 ALTER 字段 DROP DEFAULT;

• 添加字段

ALTER TABLE 表名 ADD 新字段名称 属性 ;
//将需要添加的字段添加到某个字段之后
LATER TABLE 表名 ADD 新字段名称 属性 AFTER 字段名称;

• 删除字段

ALTER TABLE 表名 DROP 字段;
//如果数据表中只剩余一个字段则无法使用DROP来删除字段。

• 删除表的全部数据和表结构，释放磁盘空间*

DROP TABLE 表名

• 查看表详细信息的三种方法

DESC 表名;
SHOW COLUMNS FROM 表名;
DESCRIBE 表名;

• 查看建表语句

SHOW CREATE TABLE 表名;

增删改查

插入数据

• 如果添加的是表的全部字段数据可以使用:VALUES（不建议）

INSERT INTO 表名 (字段1,字段2,字段3)
VALUE
(‘字段1内容’,‘字段2内容’,‘字段3内容’);

• 如果添加多行数据可以直接在字段后接着()*

INSERT INTO 表名 VALUES(‘字段1内容’,‘字段2内容’,‘字段3内容’)

• 数值类型不需要加‘’号，字段、日期类型需要加‘’号

INSERT INTO 表名 VALUES(‘字段1内容’,‘字段2内容’,‘字段3内容’),(‘字段1内容’,‘字段2内容’,‘字段3内容’)

更新数据

//修改添加外键
##ALTER TABLE 表1 ADD FOREIGN KEY(表1需要添加外键的字段名称) REFERENCES 表2(主建)
UPDATE 表名 SET 字段1=修改的内容,字段2=修改的内容;
//可以同时更新一个活多个字段内容
UPDATE 表名 SET 字段1=‘修改的内容’ WHERE 字段=‘条件’
//在更新字段时，若某些字段不需要改变时可以加条件语句:WHERE来限制更新条数

删除数据

• 删除所有数据

DELETE FROM 表名;

• 建议所有的DELETE操作在写的时候先加上WHERE条件，以免造成误删。

DELETE FROM 表名 WHERE 字段=‘条件’;

mysql删除表的语句有三种，DELETE、DROP、TRUNCATE
DELETE是属于**DML**语句，操作完以后如果没有提交事务可以产生回滚。
TRUNCATE和DROP是**DDL**语句，操作完成后立刻生效，无法产生回滚

打个比方，打个比方，delete 是发微信说分手，趁她没看见
后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，
不能反悔的呢种。

• MySQL语言四大类

类别	关键词	类型
DDL	CREATE、ALTER、DROP、TRUNCATE	数据定义语言
DML	INSERT、UPDATE、DELETE	数据操作语言
DQL	SELECT	数据查询语言
DCL	用户、权限、事务	数据控制语言

查询数据

• 简单的查询表中所有数据*

SELECT * FROM 表名;

• 查询表中部分数据*

SELECT 字段1,字段2 FROM 表名;

条件查询

• WHERE语句相当于程序中的“if”语句*

SELECT 字段1,字段2 FROM user WHERE id=1;

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

模糊查询，LIKE子句

SELECT 需要查询的字段 FROM user WHERE 字段1 LIKE ‘%条件’;

• LIKE迷糊查询匹配需要与特殊字符结合使用

符号	作用
‘%a’	查询以a为结尾的数据
‘a%’	查询以a为开头的数据
‘%a%’	查询包含a的数据
‘_ a_’	查询a前后各一个字符的数据，例如：bac
‘_a’	查询a包含a且a前一个字符的数据，例如：ba
‘a_’	查询a包含a且a后一个字符的数据 ，例如：ac
‘[]’	括号中所列字符中的任意一个（类似正则表达式）
[^]	括号中所列的单个字符，取值与“[]”相同

查询连接符

• UNION不会选取相同的值，如果需要列出相同的值需要使用UNION ALL*
  在查询语句中添加条件更利于精准查询，加上排序可以使结果更清晰*

SELECT 字段1 FROM 表名1
UNION
SELECT 字段1 FROM 表名2

查询排序

• 查询排序时可以使用多个字段来排序，可以使用ASC（升序）和DESC（降序）来进行相应的需求，还可以使用WHERE …LIKE子句来设置条件控制查询精确度* 且如果如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY，如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：ORDER BY CONVERT(字段1 using
  gbk);

SELECT 字段1，字段2，字段3 FROM 表名 ORDER BY 字段1 ASC;

查询分组

• 使用各种函数与分组进行结合使用可以使数据展示的更加清晰，如果GROUP
  BY不与上述函数一起使用，那么查询结果就是字段取值的分组情况。字段中取值相同的记录为一组，但是只显示该组的第一条记录。

SELECT 字段，函数 FROM 表名 GROUP BY 字段；

• 由于mysql语句中WHERE执行的顺序要高于GROUP
  BY分组，所以如果我们需要筛选分组过滤后的数据WHERE已经无法满足我们了，这时就要使用到HAVING函数了

SELECT 字段1,函数() FROM 表名 GROUP BY 字段2 HAVING 条件;

• 分组排序查询

SELECT 字段1,函数() FROM 表名 GROUP BY 字段 HAVING 条件 ORDER BY 字段;

• 使用WITH ROLLUP可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）

SELECT 字段1, 函数(字段2) FROM 表名 GROUP BY 字段 WITH ROLLUP;

函数名称	函数作用
COUNT（）	用于统计记录的条数
SUM（）	用于计算字段值的总和
AVG （）	用于计算字段值的平均值
MAX（）	用于查询字段的最大值
MIN（）	用于查询字段的最小值

分页查询

• 使用 limit start, count 分页查询

##查询第一页从0到5的的数据
SELECT 字段 FROM 表 LIMIT 0, 5;

连接的使用

名称	定义
INNER JOIN	内连接，或等值连接，即获取两个表中字段匹配的关系记录（使用时可省略INNER关键字）
LEFT JOIN	左连接，即获取左表所有记录，即使右表没有对应匹配的记录
RIGHT JOIN	右连接，与左连接相反，用于获取右表的所有记录，即使左表没有与其对应的数据

• INNER JOIN 内连接， 结果集只有表一的字段必须对应表二中的字段值

SELECT 表1.字段1，表2.字段2，… FROM 表一，表二 INNER JOIN 表一.字段1=表二.字段1

• LEFT JOIN 左连接，结果集会显示左表中的所有数据且显示右表中字段与左表字段相匹配的值

SELECT 表1.字段1，表2.字段2，… FROM 表一，表二 LEFT
JOIN 表一.字段1=表二.字段1

• RLGHT JOIN 右连接，与LEFT JOIN结果相反，结果集会显示右表中的所有数据且显示左表中字段与右表字段相匹配的值

SELECT 表1.字段1，表2.字段2，… FROM 表一，表二 LEFT
JOIN 表一.字段1 = 表二.字段1

正则表达式查询

SELECT 字段1 FROM 表名 WHERE 字段1 REGEXP ‘正则匹配符’

匹 配 符	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
.	匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用象 ‘[.\n]’ 的模式
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1	p2
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。
x	y
[a-z]	字符范围。匹配指定范围内的任意字符。例如，’[a-z]’ 可以匹配 ‘a’ 到 ‘z’ 范围内的任意小写字母字符。
[^a-z]	负值字符范围。匹配任何不在指定范围内的任意字符。例如，’[^a-z]’ 可以匹配任何不在 ‘a’ 到 ‘z’ 范围内的任意字符。
\b	匹配一个单词边界，也就是指单词和空格间的位置。例如， ‘er\b’ 可以匹配"never" 中的 ‘er’，但不能匹配 “verb” 中的 ‘er’。
\B	匹配非单词边界。‘er\B’ 能匹配 “verb” 中的 ‘er’，但不能匹配 “never” 中的 ‘er’。
\cx	匹配由 x 指明的控制字符。例如， \cM 匹配一个 Control-M 或回车符。x 的值必须为 A-Z 或 a-z 之一。否则，将 c 视为一个原义的 ‘c’ 字符。
\d	匹配一个数字字符。等价于 [0-9]。
\D	匹配一个非数字字符。等价于 [^0-9]。
\f	匹配一个换页符。等价于 \x0c 和 \cL。
\n	匹配一个换行符。等价于 \x0a 和 \cJ。
\r	匹配一个回车符。等价于 \x0d 和 \cM。
\s	匹配任何空白字符，包括空格、制表符、换页符等等。等价于 [ \f\n\r\t\v]。
\S	匹配任何非空白字符。等价于 [^ \f\n\r\t\v]。
\t	匹配一个制表符。等价于 \x09 和 \cI。
\v	匹配一个垂直制表符。等价于 \x0b 和 \cK
\w	匹配字母、数字、下划线。等价于’[A-Za-z0-9_]’。
\W	匹配非字母、数字、下划线。等价于 ‘[^A-Za-z0-9_]’。
\xn	匹配 n，其中 n 为十六进制转义值。十六进制转义值必须为确定的两个数字长。例如，’\x41’ 匹配 “A”。’\x041’ 则等价于 ‘\x04’ & “1”。正则表达式中可以使用 ASCII 编码。
\num	匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。例如，’(.)\1’ 匹配两个连续的相同字符。
\n	标识一个八进制转义值或一个向后引用。如果 \n 之前至少 n 个获取的子表达式，则 n 为向后引用。否则，如果 n 为八进制数字 (0-7)，则 n 为一个八进制转义值。
\nm	标识一个八进制转义值或一个向后引用。如果 \nm 之前至少有 nm 个获得子表达式，则 nm 为向后引用。如果 \nm 之前至少有 n 个获取，则 n 为一个后跟文字 m 的向后引用。如果前面的条件都不满足，若 n 和 m 均为八进制数字 (0-7)，则 \nm 将匹配八进制转义值 nm。
\nml	如果 n 为八进制数字 (0-3)，且 m 和 l 均为八进制数字 (0-7)，则匹配八进制转义值 nml。
\un	匹配 n，其中 n 是一个用四个十六进制数字表示的 Unicode 字符。例如， \u00A9 匹配版权符号 (?)。

• MySQL事务

条件名称	描述
原子性（Atomicity，或称不可分割性）	一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）	在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性（Isolation，又称独立性）	数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性（Durability）	事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。