MySQL基础复习

参考手册
安装MySQL https://downloads.mysql.com/archives/community/

安装完成之后为MySQL配置admin密码

• 8.0.22版本更贴心一些,多了卸载按钮
• MySQL文件目录配置

MySQL目录结构

• MySQL安装目录,默认装在/usr/local/mysql-8.0.22-macos10.15-x86_64目录下
  

• 一级目录结构如下

  ├── LICENSE 
  ├── README. 
  
  ├── bin  
  
  ├── data
  
  ├── docs
  
  ├── include
  
  ├── keyring
  
  ├── lib
  
  ├── man
  
  ├── share
  
  └── support-files
  

• bin目录: binary,也就是可执行文件,提供了mysql的常用快捷命令,我们对mysql图形化的操作基本都可以在这些命令中找到

  • 关键命令
    • -p, --password[=name]: 通过-p执行密码
    • -P, --port=#: 大写P指定端口
    • --protocol=name: 执行数据的连接协议 (tcp, socket, pipe,memory).
    • -u, --user=name: 指定连接的userName
    • -h, --host=name: 指定连接hostname
  • 创建连接:
    • mysql [-h127.0.0.1] [-P3306] -uroot -p12345

• data目录: 用于存储日志文件以及数据库

• include目录: 放置一些头文件，如：mysql.h、mysql_ername.h等

• lib目录: 用于放置一系列库文件,同时也包括一些插件

• share目录: 用于存放字符集、语言等信息。

• man目录: 命令的帮助文档信息

• 部分文件夹无法打开，可以通过chown修改权限后查看。

数据库连接找不到mysql.sock

• 提示mysql.sock,mysql的套节字无法找到

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

• mysql.server重启 shell xxx@xxx lib % /usr/local/mysql-8.0.22-macos10.15-x86_64/support-files/mysql.server
Usage: mysql.server {start|stop|restart|reload|force-reload|status} [ MySQL server options ]
• 如果还是连接不上,配置/usr/local/etc/my.cnf,指定socket的路径Mac下默认没有cnf文件),如下所示 [client]
default-character-set=utf8
password = your_password
port = 3306
socket = /usr/local/var/mysql/mysql.sock
[mysqld]
character-set-server=utf8
init_connect='SET NAMES utf8
port = 3306
socket = /usr/local/var/mysql/mysql.sock

连接数据库测试

• 通过终端访问:连接数据库mysql -uroot -p回车,输入密码

xxx@xxx ~ % mysql -uroot -p 
Enter password: 

• 通过其他编程语言访问数据库: 测试连接数据,换成代码连接,需要执行用户名,密码,主机地址。如下为php测试
• 示例 php <?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpassword = 'xxxx';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if ($conn) {
die('could not connect: '. mysqli_error());
}
echo '数据库连接成功！';
mysqli_close($conn);
?>
• 连接成功 shell Warning: mysqli_connect(): (HY000/2002): No such file or directory in /Users/xxx/mysqldemo/php_demo.php on line 5
数据库连接成功！
Warning: mysqli_close() expects parameter 1 to be mysqli, bool given in /Users/xxx/mysqldemo/php_demo.php on line 10
xxx@xxx mysqldemo % ## mysql交互式命令操作
• 首先需要登录数据库,通常需要指定ip,port,password,userName mysql -h localhost -uroot -p123456
• mysql交互式命令每一句话都以分号结尾。通过help;可以查看所有支持的命令

mysql> help;

产品介绍: http://www.mysql.com/
操作手册: http://dev.mysql.com/
购买MySQL商业版本: https://shop.mysql.com/

所有的mysql命令必须以';'结尾
?         相当于 `help;`,寻求帮助命令
clear     清空当前终端,
connect   (
) Reconnect to the server. Optional arguments are db and host.
delimiter (d) Set statement delimiter.
edit      (e) Edit command with $EDITOR.
ego       (G) Send command to mysql server, display result vertically.
exit      (q) Exit mysql. Same as quit.
go        (g) Send command to mysql server.
help      (h) Display this help.
nopager   (
) Disable pager, print to stdout.
notee     (	) Don't write into outfile.
pager     (P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (p) Print current command.
prompt    (R) Change your mysql prompt.
quit      (q) Quit mysql.
rehash    (#) Rebuild completion hash.
source    (.) Execute an SQL script file. Takes a file name as an argument.
status    (s) Get status information from the server.
system    (!) Execute a system shell command.
tee       (T) Set outfile [to_outfile]. Append everything into given outfile.
use       (u) Use another database. Takes database name as argument.
charset   (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (W) Show warnings after every statement.
nowarning (w) Don't show warnings after every statement.
resetconnection(x) Clean session context.

For server side help, type 'help contents'

mysql> clear;
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runoob             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

MySQL基本数据类型介绍

数值类型

• TINYINT: tiny int,微小的整数 有符号(-128，127),无符号(0，255)
• SMALLINT: 2字节，小整数， 类似shortInt
• MEDIUMINT: medium int, 3字节
• INT或者INTEGER: 4
• BIGINT: 8， Long，long
• FLOAT： 4
• DOUBLE: 8, double
• DECIMAL: decimal,对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2,小数值

时间类型
DATE: 3. 1000-01-01/9999-12-31 ,最多1w年
TIME: 3
YEAR: 1
DATIME: 8
TIMESTAMP: 4

字符串类型

CHAR 0~255bytes, 定长字符串, 这里只字符串
VARCHAR： 65535， 2-》16
TINYBLOB: 0-255 bytes, 不超过 255 个字符的二进制字符串
TINYTEXT: 0-255 bytes
BLOB: 65535， 2-》16,长二进制
TEXT: 0-65 535 bytes,长文本数据
MEDIUMBLOB: 二进制形式的中等长度文本数据
MEDIUMTEXT: 中等长度文本数据
LONGBLOB: LONGBLOB二进制形式的极大文本数据
LONGTEXT: 极大文本数据

小结,按照字节划分4个档次
Tiny， 1
small, 2
medium, 3
normal, 4
long 8
按照种类来分,二进制字符串;文本。 方便编码

MySQL创建表

• CREATE TABLE table_name (column_name column_type);

CREATE TABLE runoob_tbl(
   -> runoob_id INT NOT NULL AUTO_INCREMENT,
   -> runoob_title VARCHAR(100) NOT NULL,
   -> runoob_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( runoob_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看表

mysql> desc runoob_tbl;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| runoob_id       | int          | NO   | PRI | NULL    | auto_increment |
| runoob_title    | varchar(100) | NO   |     | NULL    |                |
| runoob_author   | varchar(40)  | NO   |     | NULL    |                |
| submission_date | date         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

• 创建表时命令都采用了大小,这是为了统一规范,一眼就能看出哪些是命令数据,哪些是用户输入表的数据
• 创建时首先采用IF NOT EXSITS对表进行检测,避免重复创建
• 其次,一般首列为索引,例如我们excel表,自动加上索引，方便查找数据
• 添加字段一般换行,保持代码的可读性
• 添加字段的格式 [字段名] [类型] [约束]
• 最后需要指定一个PRIMARY KEY (字段名),指定一个主键,通常为索引,具有很强的唯一性,可以用来和其它表进行联合查询。

• ENGINE存储引擎，CHARSET设置编码。

  • 存储引擎有三个关键的概念
    • Buffer Pool: 大脑
    • 事务日志: 草稿
    • Datafile: 正稿
  • 可以形象的比喻为我们上课做笔记，参考这篇博客: https://www.cnblogs.com/personblog/p/11177940.html

• 删除表

ERROR 1051 (42S02): Unknown table 'runoob.ruboob_tbl'
mysql> drop TABLE runoob_tbl;
Query OK, 0 rows affected (0.01 sec

....

常用MYSQL语句

• 删除表: DROP TABLE table_name;
• 插入数据:
  
  • 关键英文单词: INSERT INFO table_name
  • 两个元组,key为一个组,value为一个元组
  • 中间通过VALUES将它们串在一起

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
                       
mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());

• 查找: select关键字,选择,正好对应英文意思， select * from demo_tbl.

• 查询数据:

  • 查询语句汇总，可以使用一个或者多个表
  • SELECT可以指定多个字段
  • WHERE为条件判断
  • LIMIT限定记录条数
  • OFFSET查询时的偏移量，从第几条开始查询,默认为0

SELECT column_name, column_name
FROM table_name
[WHERE Clasue]
[LIMIT N] [OFFSET M]

• WHERE语句: 可以组合多个条件, AND, OR
• UPDATE: 配合set使用,指定表名，set后面接 key-value，附加条件语句

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• DELETE:
  • DELETE FROM table_name [WHERE Clause] ,从xxx中删除,通常为局部删除

• LIKE: 喜欢,像，使用%来匹配任意字符,类似unix中的*
  SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM';

  • UNION: 联合操作符
  • 将两组select的数据合并为一条

  SELECT expression1, expression2, ... expression_n
  FROM tables
  
  [WHERE conditions]
  
  UNION [ALL | DISTINCT] //默认为distinct
  
  SELECT expression1, expression2, ... expression_n
  
  FROM tables
  
  [WHERE conditions];
  

  • ORDER BY:
  • 主谓宾,修饰

 SELECT * from runoob_tbl ORDER BY submission_date ASC;

分组

• 这个功能和创建Excel中的数据透视表很相近
• SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
• 例mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
  • as相当于取别名，alias
  • FOREIGN_KEY_CHECKS为外键
  • 关闭外键
    • SET FOREIGN_KEY_CHECKS = 0;
    • 执行数据库操作,完成后再开启外键
    • SET FOREIGN_KEY_CHECKS = 1;
  • 查看当前外键的值
    • SELECT @@FOREIGN_KEY_CHECKS;
• 可以使用coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法
  • select coalesce(a,b,c);

SQL 连接使用

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

匹配两个表中作者相同的数据,并将它们显示到一起(按照指定的key)

• LEFT JOIN:

• RIGHT JOIN:

• tab [INNER|LEFT|RIGHT] tab ON condition: 描述2个tab之间的关系集合关系，附加condition过滤条件

SQL 中NULL的处理

• select * , columnName1+ifnull(columnName2,0) from tableName;
  • 使用ifnull标注,对变量columnName指定默认值
  • 用到了相加的操作符,column的类型需为int
  • name varchar(40) NOT NULL, 约束
  • givenname varchar(40) NULL DEFAULT

正则表达式

• .匹配除 以外的任何字符
• [...]集合字符
• [^{...]负字符集合:} 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
• mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
  • 通过REGXP来进行条件运算

MYSQL事务

• 主要用于批量处理数据,在移动端也有类似的操作,比如批量写入缓存.
  • 行成信息的实时同步
  • 界面状态切换时,列表刷新的缓存数据同步等
  • 多个动画属性设置并运行等
  • 图片的组装与合,流媒体的合成等
• 它的作用是为了保证多条SQL语句批量执行,语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句
• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  • 事务隔离分为不同级别，包括读未提交（Read uncommitted）、
  • 读提交（read committed）、
  • 可重复读（repeatable read）
  • 串行化（Serializable）
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句：

• BEGIN 或 START TRANSACTION 显式地开启一个事务；

• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

• ROLLBACK TO identifier 把事务回滚到标记点；

• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

事务的设计思路理解

• 由于事务数据量大,为了避免部分失败导致前功尽弃,所以扩展了设置事务中间状态的保存点用于回滚操作
• 事务处理时间过久会阻塞其它工作,所以增加事务隔离级别,视紧急程度设置优先级
• 语法糖,commit直接提,书写方便快捷
• begin/start可读性强

事务的处理方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

My SQL的ALERT命令

• 当修改表名和数据字段时使用ALRT,表明这是一个重要操作,可能出现重大异常

• 一般在数据迁移时或者版本更新时会出现
  mysql> ALTER TABLE testalter_tbl DROP i;

• 修改表名字

ALERT TABLE test_tbl MODIFY c CHAR(10)

• 修改字段名字,联想记忆单词

//使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

ALERT TABLE test_tbl CHANGE i j BIGINT;
 
ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;
    
ALTER TABLE testalter_tbl RENAME TO alter_tbl;

索引

• 通过INDEX来修饰 CREATE INDEX indexName ON table_name (column_name) ALTER table tableName ADD INDEX indexName(columnName)

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

复制表

• SHOW CREATE TABLE:获取创建命令
• CREATE TABLE: 创建表
  ## 获取服务器元数据
• SHOW VARIABLES
• SELECT VERSION( ) 服务器版本信息
• SELECT DATABASE( ) 当前数据库名 (或者返回空)
• SELECT USER( ) 当前用户名
• SHOW STATUS

SQL Concat(str1,str2)

• Concat(str1, str2): 将多个列名的key对应的值合并成一个
• select concat (id, name, score) as info from tt2;

碰到的各种错误

• ERROR 1045 (28000)
• ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
• ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/mysql.sock' (2)
• 碰到错误不要盲目百度,查看具体的错误信息,再参考对应版本的操作手册查询关键字或章节,理解后再处理问题
  • /usr/local/mysql-5.7.31-macos10.14-x86_64/data/mysqld.local.err
  • /usr/local/mysql-5.7.31-macos10.14-x86_64/data/电脑名.err
• 常用命令
  • ps ef | grep mysql,查看占用端口, kill -9 端口号

各种权限的问题终结方案

参考手册
参照对应版本的官方文档,

• 设置密码,此方式不安全,慎用 /usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

删除数据库

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*

参考:

MySQL 5.7 Reference Manual
菜鸟教程