MMysql1
基础知识
数据模型
层次
网状
关系 --> 二维表【行、列】
非关系
存储引擎是什么？
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。
MySQL使用插件式存储引擎
并发性：读写阻塞
读锁：共享锁
写锁：独占锁
数据库锁：锁管理器
表锁
页锁
行锁 （死锁时，让锁定资源少的先释放）--写并发增强，管理复杂度更高，
httpd-2.4 MPM
prefork 一个进程相应一个请求
worker 一个线程响应一个请求
event 一个线程响应多个请求
MySql：一个线程响应一个用户请求（如果是进程，难以实现同步）
复用：线程池（thread pool），允许多少用户连接，使用完后不会被销毁，数据清理后复用
事务：ACID
原子性(Atomicity)： 事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。
一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。
隔离性(Isolation)： 事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。比如--single-transaction
持久性(Durability)： 对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。一个事务一旦被提交，它对数据库中的数据就永久改变
为了保证持久性，事务提交，立即写入磁盘。
数据库重启自我恢复功能：（事务日志，保证持久性）
已提交的，应用于数据库
未提交的，撤销
事务隔离级别，支持多版本读
读未提交 READ UNCOMMITED
读提交 READ COMMITED
可重读 REPEATAL READ（默认）
可串行化 SERIABLIZABLE
SQL
DML： Insert、Update、Delete、Select
DDL： CREATE、DROP、ALTER
DCL： GRANT、REVOKE
MySQL日志
错误日志
查询日志
慢查询日志
事务日志
二进制日志
中继日志
SQL：结构化查询语言（sql解释器）
ANSI规范，sql-86,sql-92,sql-99,sql-03
各厂商扩展：
SQL-Server:T-sql
Oracle:pl/sql
Mysql:sql
mysql -连接->mysqld
linux上：
本地：mysql.sock
远程：tcp/ip
自定义程序-->mysql
ODBC驱动
php:mysql_connect（pdo轻量级）
数据类型 （变长，定长）
VARCHAR（20） root 5B 如果变为redis，原空间无法存（往后移动或换行存）
CHAR（20） root 20B 如果变为redis，无影响。如果变为CHAR（21），导致全部重新写。
定长记录
数据块：行数固定
变长记录
数据块：行数变化
需要解决
如果描述一条记录，以实现快速记录获取
如何存储一条记录，已实现快速记录存储
无序记录：堆文件
有序记录：聚簇索引
散列文件
MyISAM表
数据文件 table_name.MYD
索引文件 table_name.MYI
表定义 table_name.frm
InnoDB表
表空间：多张表可放置于同一个表空间，表空间多个数据库可共享。也支持单独表空间（工作一般会使用，默认没设置）
表定义文件：每张表的表定义文件在数据库目录中
数据字典
保存数据库服务器上的元数据库（多少数据库，数据库各有多少表等，即系统目录）
MySQL的字典统计类信息都存放在information_schema库中，性能类performance_schema
information_schema：将mysql各种内部数据结构统一为关系模型结构的接口
每个关系中属性的个数
每个关系中行的个数
每个关系的存储方法
缓存置换策略
LRU 最近最少使用
MRU 最近最使用常
pinned block 被钉住的块
块的强制写出
innodb引擎调优
mysql> show {global|session} variables; ==> mysql>set ...
mysql> show {global|session} status
mysql.sock
rpm: /var/lib/mysql/mysql.sock
二进制：/tmp/msyql.sock
MySQL安装方式
1、二进制格式
rpm
二进制包
2、源码格式
DBA
开发DBA 数据库设计、SQL语句、存储过程、存储函数、触发器
管理DBA 安装、升级、SQL语句、备份、恢复、用户权限管理、监控、性能分析、基准测试
Mysql5.5系列安装
[root@node84 ~]# yum install cmake ncurses-devel openssl-devel -y
[root@node84 ~]# useradd mysql -s /bin/nologin -M
[root@node84 ~]# mkdir /data
[root@node84 ~]# chown -R mysql.mysql /data
host设置
==cmake方式与以往方式不同点==
./configure ==> cmake .
./configure --help ==> cmake . -LH
shell> cmake .. -L # overview
shell> cmake .. -LH # overview with help text
shell> cmake .. -LAH # all params with help text
shell> ccmake .. # interactive display
shell> make clean
shell> rm CMakeCache.txt
==cmake========================
[root@node84 tools]# tar -xf mysql-5.5.49.tar.gz
[root@node84 ~]# ls /home/tools/mysql-5.5.49/storage/
archive blackhole csv example federated heap innobase myisam myisammrg ndb perfschema
csv 将数据保存为文本格式，所以会损失精度
myisammrg 实现多个结构相同的表合并成一个表
heap 数据存储为内存中的存储引擎，没有持久能力
Percona InnoDB-兼容-XtraDB
Xtrabackup：备份InnoDB及XtraDB数据库
[root@node84 tools]# cd mysql-5.5.49
[root@node84 tools]#cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.5.49 \
-DMYSQL_DATADIR=/data \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_PROFILING=1 \
-DWITH_DEBUG=0
[root@node84 mysql-5.5.49]# make
[root@node84 mysql-5.5.49]# make install
[root@node84 mysql-5.5.49]#ln -s /usr/local/mysql5.5.49 /usr/local/mysql
[root@node84 mysql-5.5.49]#/bin/cp /home/tools/mysql-5.5.49/support-files/my-large.cnf /etc/my.cnf
[root@node84 mysql-5.5.49]#chown -R mysql.mysql /usr/local/mysql
root@node84 mysql-5.5.49]# vi /etc/my.cnf
增加
datadir = /data
innodb_file_per_table = 1
[root@node84 mysql-5.5.49]# /bin/cp /home/tools/mysql-5.5.49/support-files/mysql.server /etc/init.d/mysqld
[root@node84 mysql-5.5.49]# chmod +x /etc/init.d/mysqld
[root@node84 mysql-5.5.49]# chkconfig --add mysqld
[root@node84 mysql-5.5.49]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile
[root@node84 mysql-5.5.49]# source /etc/profile
[root@node84 mysql-5.5.49]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
[root@node84 mysql-5.5.49]# cd /usr/local/mysql/scripts
[root@node84 scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/ --user=mysql
[root@node84 scripts]# ls /data
mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index performance_schema test
[root@node84 scripts]# /etc/init.d/mysqld start
Starting MySQL... SUCCESS!
[root@node84 scripts]# ls -l /data
total 29820
-rw-rw---- 1 mysql mysql 18874368 Jan 30 04:05 ibdata1 innodb公共表空间
-rw-rw---- 1 mysql mysql 5242880 Jan 30 04:05 ib_logfile0 事务日志
-rw-rw---- 1 mysql mysql 5242880 Jan 30 04:05 ib_logfile1 事务日志
drwx------ 2 mysql root 4096 Jan 30 04:04 mysql
-rw-rw---- 1 mysql mysql 27690 Jan 30 04:04 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1115497 Jan 30 04:04 mysql-bin.000002
-rw-rw---- 1 mysql mysql 107 Jan 30 04:05 mysql-bin.000003
-rw-rw---- 1 mysql mysql 57 Jan 30 04:05 mysql-bin.index
-rw-r----- 1 mysql root 2040 Jan 30 04:05 node84.err
-rw-rw---- 1 mysql mysql 6 Jan 30 04:05 node84.pid
drwx------ 2 mysql mysql 4096 Jan 30 04:04 performance_schema
drwx------ 2 mysql root 4096 Jan 30 04:04 test
增加密码方式
/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h node84 password 'new-password'
修改密码方式
方式1 #mysqladmin -uUSERNAME -hHOST password "newpass"
方式2 mysql>set password for username@host=password("newpass")
方式3 mysql> use mysql;
mysql> update mysql.user set password=password('newpass') where user='USERNAME' and host='HOST';
mysql> flush privileges;
ps：with grant option 权限转移权限
找回丢失的root密码步骤
1、关闭数据库 #/etc/init.d/mysql stop
2、使用--skip-grant-tables启动mysql，忽略授权登陆验证
mysqld_safe --skip-grant-tables --user=mysql & [可选 --skip-networking]
=====>登陆时密码为空 mysql>update mysql.user set password=password("newpassword") where user='root'
/etc/init.d/mysqld
是个shell脚本，启动时调用mysqld_safe脚本,最后调用mysqld主程序启动mysql，
如下，/etc/init.d/mysqld脚本中调用mysqld_safe $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &
my.cnf
[root@node84 scripts]# grep "^\[" /etc/my.cnf [client] [mysqld] [mysqldump] [mysql] [myisamchk] [mysqlhotcopy]
socket文件及使用模式
Mysql连接 remote client tcp/ip方式 local client tcp/ip方式 ipc (sock）
使用模式
交互式模式
批处理模式
-h --host=
-u --user==
-p --password=
-D --database=
mysql客户端命令
\g
\G
\q
\! 执行shell
\.
\d 设定语句结束符号
\c 取消sql
[root@node84 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 #显示线程ID号 Server version: 5.5.49-log Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
MySQL约束
MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息； 常用5种约束： not null: 非空约束，指定某列不为空 unique: 唯一约束，指定某列和几列组合的数据不能重复 primary key: 主键约束，指定某列的数据不能重复、唯一 foreign key: 外键，指定该列记录属于主表中的一条记录，参照另一条数据(innodb) check: 检查，指定一个表达式，用于检验指定数据
mysql> show global variables; http://mageedu.blog.51cto.com/4265610/1058357
mysql> show global variables like "innodb%"; 查看全局innodb变量配置
mysql> show global status like "innodb%"; 全局查看innodb状态
mysql> show global status like "Com%"; mysql> show global status like "C_m%"; 下划线匹配单个字符
mysql> show session status； 当前用户状态
set global 对全局有效，但是不会立即生效
set session 只对当前会话有效，立刻生效
例子:永久修改引擎为MyISAM
mysql> show global variables like "%engine%";
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | InnoDB |
| engine_condition_pushdown | ON |
| storage_engine | InnoDB |
+---------------------------+--------+
mysql> set global default_storage_engine="MyISAM";
mysql> show global variables like "%engine%"; 对全局有效，但是对当前会话不会立即生效,重启后失效(mysql> select @@global.default_storage_engine;)
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | MyISAM |
| engine_condition_pushdown | ON |
| storage_engine | MyISAM |
+---------------------------+--------+
mysql> show session variables like "%engine%"; 全局修改没有对当前会话立即生效
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | InnoDB |
| engine_condition_pushdown | ON |
| storage_engine | InnoDB |
+---------------------------+--------+
如需永久生效
#vi /etc/my.cnf
[mysqld]
default_storage_engine = MyISAM
SQL语言
DDL数据定义
DML数据操作
授权DCL
完整性定义语言：DDL的一部分功能
视图定义
事务控制
嵌入式SQL和动态SQL
mysql数据类型 https://dev.mysql.com/doc/refman/5.6/en/data-types.html
[root@node84 ~]# mysql -u root -e "help create table" | sed -n '40,76p' data_type: BIT[(length)] | TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL] | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | YEAR | CHAR[(length)] [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | VARCHAR(length) [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | BINARY[(length)] | VARBINARY(length) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | TEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | MEDIUMTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | LONGTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name]
内置类型（字符型）：
SET（a,b）: a, b, ab, ba
EMUN(a,b): a,b
5.1.8 Server SQL Modes sql模式
常见sql_mod取值,默认为空,宽松模式
strict_all_tables
strict_trans_tables
tranditional
The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable.
DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements. Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.
mysql> show variables like "%sql_mod%"; 默认为空
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
mysql> create table tb1(name CHAR(5));
mysql> insert into tb1 values ("hello");
mysql> insert into tb1 values ("helloo");
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
mysql> select * from tb1; #截断
+-------+
| name |
+-------+
| hello |
| hello |
+-------+
mysql> set session sql_mode='strict_all_tables'; 修改为strict模式
mysql> insert into tb1 values ("saltstack");
ERROR 1406 (22001): Data too long for column 'name' at row 1
DML
mysql> help insert INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... OR INSERT INTO tbl_name SET col_name1=value，col_name2=value... OR INSERT INTO tbl_name(col_name) SELECT clause 数值数据： 不需要引号 字符数据： 引号（必须） 空值： NULL mysql> help replace; 替换插入(假如表中的一个旧记录与一个用于PRIMARYKEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。)（慎用） Name: 'REPLACE' Description: Syntax: REPLACE [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... Or:REPLACE [INTO] tbl_name SET col_name={expr | DEFAULT}, ... Or:REPLACE [INTO] tbl_name [(col_name,...)] SELECT ... mysql> help update Single-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] #注意点，不加限定，全部更新（sql_safe_updates参数设定） [ORDER BY ...] [LIMIT row_count] mysql> help delete Single-table syntax: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] mysql> show create table tb3\G #查看建表语句 *************************** 1. row *************************** Table: tb3 Create Table: CREATE TABLE `tb3` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, `age` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> insert into tb3 (name,age) values ('wangyi',10),('wanger',20),('wangsan',30),('wangwu',40); mysql> select * from tb3; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | wangyi | 10 | | 2 | wanger | 20 | | 3 | wangsan | 30 | | 4 | wangwu | 40 | +----+---------+------+ mysql> delete from tb3 where id=4; mysql> insert into tb3 (name,age) values ('wangliu',60); mysql> select * from tb3; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | wangyi | 10 | | 2 | wanger | 20 | | 3 | wangsan | 30 | | 5 | wangliu | 60 | +----+---------+------+ mysql> show table status like "tb3"\G 查看表状态 *************************** 1. row *************************** Name: tb3 Engine: InnoDB Version: 10 Row_format: Compact 行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段。 Rows: 4 表中的行数。对于非事务性表，这个值是精确的，对于事务性引擎，这个值通常是估算的。因为myisam立马写入数据，事务型先写事务日志，再同步数据（truncate清空重置） Avg_row_length: 4096 平均每行包含的字节数 Data_length: 16384 整个表的数据量（字节） Max_data_length: 0 表可以容纳的最大数据量，字节 Index_length: 0 索引占用磁盘的空间大小 Data_free: 9437184 对于MyISAM引擎，标识已分配但现在未使用的空间，并且包含了已被删除行的空间。 Auto_increment: 6 下一条自增记录为6 （select last_insert_id()），NULL代表未设置自动增长的字段。代表具有自动增长属性的字段上，下一个自动增长的值 Create_time: 2017-02-01 05:44:17 Update_time: NULL Check_time: NULL 使用 check table 或myisamchk工具检查表的最近时间 Collation: utf8_general_ci 表的默认字符集和字符排序规则 Checksum: NULL 如果启用，则对整个表的内容计算时的校验和 Create_options: 指表创建时的其他所有选项 Comment: 包含了其他额外信息，对于MyISAM引擎，包含了注释信息，如果表使用的是innodb引擎，将显示表的剩余空间。如果是一个视图，注释里面包含了VIEW字样。 mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 5 |
+------------------+
mysql> show engine innodb status\G 查看innodb引擎状态
mysql> show engine innodb status\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 170204 5:10:05 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 14 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 202 1_second, 202 sleeps, 14 10_second, 73 background, 73 flush srv_master_thread log flush and writes: 202 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 29, signal count 29 Mutex spin waits 2, rounds 60, OS waits 2 RW-shared spins 27, rounds 810, OS waits 27 RW-excl spins 0, rounds 0, OS waits 0 Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 552 Purge done for trx's n:o < 549 undo n:o < 0 History list length 16 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 12, OS thread handle 0x7f1a28343700, query id 239 localhost root show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 298 OS file reads, 258 OS file writes, 136 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276707, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1673142 Log flushed up to 1673142 Last checkpoint at 1673142 0 pending log writes, 0 pending chkp writes 83 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 62419 Buffer pool size 8192 Free buffers 7994 Database pages 197 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 159, created 38, written 264 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 197, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 1768, id 139749687596800, state: waiting for server activity Number of rows inserted 50, updated 5, deleted 0, read 375 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
清空整张表
mysql>delete from table_name
mysql>truncate table table_name (清空 increment记录)
设置自增
mysql> show variables like "%auto_increment%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 | 步长
| auto_increment_offset | 1 | 起始值
+--------------------------+-------+
SQL查询
单表查询SQL语句执行顺序
start
(1)FROM
(2)WHERE(存储引擎)
(3)GROUP BY
(4)HAVING
(5)ORDER BY
(6)SELECT（Mysql server）
(7)LIMIT
end
对于select表查询，只要有存在一个可用索引，完成查询至少有两条路径
全表扫描
使用索引
select col1，col2...（投影） from table where（选择） clause
select聚合函数
max()
min()
avg()
count()
sum()
where子句之比较
算数表达式（age+20>60导致无法使用索引）
比较表达式
between val1 and val2
in （val1，val2......集合）
is null / is not null
like %或_
rlike 正则 . * ^ \>
where子句之组合条件
and &&
or ||
not ！
XOR 异或
算数运算符号 + - * / %
逻辑运算符号 = <> != < <= > >=
![]()
select查询
单表查询
联结查询（join）
联合查询（union）
基础知识回顾
事务特性
A：原子性
C：一致性
I：隔离性
D：持久性（事务日志）
随机IO-->顺序IO
存储和缓存
memcached
程序局部性原理
空间局部性
时间局部性
mysqld加载配置文件顺序 mysqld --help --verbose 命令行选项为配置文件中的可用参数
[root@node84 ~]# mysqld --print-defaults
mysqld would have been started with the following arguments:
--port=3306 --socket=/tmp/mysql.sock --skip-external-locking --key_buffer_size=256M --max_allowed_packet=1M
--table_open_cache=256 --sort_buffer_size=1M --read_buffer_size=1M --read_rnd_buffer_size=4M --myisam_sort_buffer_size=64M
--thread_cache_size=8 --query_cache_size=16M --thread_concurrency=8 --datadir=/data --innodb_file_per_table=1
--log-bin=mysql-bin --binlog_format=mixed --server-id=1
# mysqld --help --verbose | head -20 | tail -10
/etc/mysql/my.cnf --> /etc/my.cnf --> --default-extra-file= --> ~/.my.cnf
找回root密码参数
--skip-grant-table --skip-networking
查看mysql变量
show {global|session} variable [like clause]|[where clause]
select @@{global|session}.variable_name
查看mysql状态
show {global|session} status
查看表状态
show table status like “table_name”
show table status where name="table_nam"
变量修改
mysql> show global variables like "%engine%"; 全局非立即生效，新会话生效
mysql> show session variables like "%engine%"; 会话级别立即生效
数据类型
是否支持索引
排序方式及比较方式
修饰符：NULL,NOT NULL DEFAULT,UNSIGNED,AUTO_INCREMENT
约束类型：PRIMARY KEY,UNIQUE KEY,FOREIGN KEY,CHECK
SQL语句
DDL：数据定义语言
数据库、表、索引、视图、存储过程、存储函数、约束、触发器、事件调度器
创建数据库
create database|schema [if not exists] dbname [如果创建的数据库已存在出现error，加了if则出现的是warning]
CHARACTER SET [=] charset_name
COLLATE [=] collation_name
删除数据库 drop database dbname
更改数据库 alter dbname （5.1升级至5.5数据字典不兼容，UPGRADE DATA DIRECTORY NAME）
创建表
create table tbname
create table tbname select... 从其他表复制数据生成新表
create table tbname like othertbname 从其他表复制表结构生成新表（包括索引）
table options
engine=enginename
delay_key_write={0|1} 更新数据后是否立即更新索引数据
tablespace tablespace_name
表改名：rename table tbname to newtbname 或 alter table tbname to newtbname
表定义更改：alter table
add col_name defination [{first|after col_name}]
drop colname
modify col defination [{first|after col_name}]
change col new_col_name defination [{first|after col_name}]
DML：数据操作语言
CRUD：Insert Select Update、Delete
DCL：数据控制语言
GRANT REVOKE
事务：start transaction，commit，rollback，save point
存储引擎，也称作表类型
MyISAM引擎：
tbname.MYD
tbname.MYI
tbname.frm
Innodb引擎
table space：ibdata1 公共表空间 （不建议）
独立表空间--全局参数 innodb_file_per_table (mysql> set global innodb_file_per_table=1;因为此参数只有全局，所以立即生效)
tbname.frm
tbname.idb 数据和索引
建议：同一个数据库中的表最好使用相同的存储引擎（事务回滚会出问题）
创建表时有delay_key_write选项 决定是否在修改数据时更新索引，影响了性能，增加了精确性
Mysql查询
简单单表查询 select [distinct] col1,col2... from tb1 where condition group by col having condition order by col limit [m,]n 组合查询 联合查询union （查询结果合并） 连接查询join 交叉连接 select * from A,B A记录数*B记录数 内连接：自然连接 select * from A,B where A.col=B.col 外连接： 左外连接 left join ...on... 右外连接 right join ...on... 全外连接 自连接 sometable as alias1 inner join sometable as alias2 on alias1.field=alias2.field 子查询：据说mysql对于子查询优化很有限，建议少使用 用于where子句 select clause from tb1 where 1、用于比较表达式中的子查询 mysql> select Name,Age from students where age>(select avg(Age) from students); 2、用于exists中的子查询 3、用于in中的子查询 mysql>select ClassID from coc where CourseID not in (select CourseID from courses); 用于from中的子查询 select col....from (select clause） where condition
CREATE TABLE `students` ( `StuID` int(10) NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') NOT NULL, `ClassID` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> insert into students (Name,Age,Gender,ClassID) values ('Shi Zhongyu',22,'M',2),('Shi Potian',22,'M',1),('Xie Yanke',53,'M',2),('Ding Dian',32,'M',4),('Yu Yuntong',26,'M',3),('Shi Qing',46,'M',5); mysql> insert into students (Name,Age,Gender,ClassID) values ('Xi Ren',19,'F',3),('Lin Daiyu',17,'F',7),('Ren Yingying',22,'F',6),('Yue Lingshan',19,'F',3); mysql> select * from students;
+-------+--------------+-----+--------+---------+
| StuID | Name | Age | Gender | ClassID |
+-------+--------------+-----+--------+---------+
| 1 | Shi Zhongyu | 22 | M | 2 |
| 2 | Shi Potian | 22 | M | 1 |
| 3 | Xie Yanke | 53 | M | 2 |
| 4 | Ding Dian | 32 | M | 4 |
| 5 | Yu Yuntong | 26 | M | 3 |
| 6 | Shi Qing | 46 | M | 5 |
| 7 | Xi Ren | 19 | F | 3 |
| 8 | Lin Daiyu | 17 | F | 7 |
| 9 | Ren Yingying | 22 | F | 6 |
| 10 | Yue Lingshan | 19 | F | 3 |
+-------+--------------+-----+--------+---------+ mysql> select ClassID,count(Name) From students group by ClassID; 每个班级有多少人 mysql> select ClassID,avg(Age) From students group by ClassID; 每班平均年龄 mysql> select ClassID,count(Name) as counts From students group by ClassID having count(name)>=2; mysql> select ClassID,count(Name) as counts From students group by ClassID having counts>=2; mysql> select distinct gender from students;
mysql> select Gender,avg(Age) from students group by Gender;
+--------+----------+
| Gender | avg(Age) |
+--------+----------+
| F | 19.2500 |
| M | 33.5000 |
+--------+----------+
CREATE TABLE `classes` ( `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `Class` varchar(100) DEFAULT NULL, `NumOfStu` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ClassID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> insert into classes (Class,NumOfStu) values ('Shaoling Pai',10),('Emei Pai',7),('QingCheng Pai',11),('Wudang Pai',12),('Riyue Pai',15),('Liangshan Pai',19),('Xiaoyao Pai',25);
交叉连接
mysql> select * from students,classes
内连接，自然连接
mysql> select * from students,classes where students.ClassID=classes.ClassID; mysql> insert into students (Name,Age,Gender,ClassID) values ('xuxian',19,'F',NULL),('bainiangzi',17,'F',NULL); 左外连接
mysql> select Name,Class from students left join classes on students.ClassID=classes.ClassID;
mysql> select Name,Class from students as s left join classes as c on s.ClassID=c.ClassID; 别名 mysql> alter table students add TeacherID int unsigned; mysql> update students set TeacherID=3 where StuID=1; mysql> update students set TeacherID=4 where StuID=3; mysql> update students set TeacherID=6 where StuID=8;
自连接 mysql> select s.name as students,t.name as Teacher from students as s inner join students as t where s.TeacherID=t.StuID; CREATE TABLE `courses` ( `CourseID` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `Course` varchar(100) NOT NULL, PRIMARY KEY (`CourseID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> insert into courses (Course) values ('hama gong'),('huihuabaodian'),('jinshe jianfa'),('taiji quan'),('dagoubang'),('computer'),('yuwen'),('shuxue');
多表查询
mysql> select Name,Course from students,classes,coc,courses where students.ClassID=classes.ClassID and classes.ClassID=coc.ID and coc.CourseID=courses.CourseID; CREATE TABLE `coc` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `ClassID` tinyint(3) unsigned NOT NULL, `CourseID` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> insert into coc (ClassID,CourseID) values (1,2),(1,5),(2,2),(2,6),(3,1),(3,7),(4,5),(4,2); CREATE TABLE `teacher` ( `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `Age` tinyint(3) unsigned NOT NULL, `Gender` enum('F','M') DEFAULT NULL, PRIMARY KEY (`TID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
联合查询 mysql> insert into teacher (Name,Age,Gender) values ('songjiang',45,'M'),('teacher2',67,'F'),('songjiang',31,'F'); mysql> select Name,Age from teacher where Age>50 union select Name,Age from students where Age>40; CREATE TABLE `toc` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `CourseID` smallint(5) unsigned DEFAULT NULL, `TID` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `scores` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `StuID` int(10) unsigned NOT NULL, `CourseID` smallint(5) unsigned NOT NULL, `score` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
浙公网安备 33010602011771号