从入门到精通 | MySQL知识整理（下）

{TCP|SOCKET|PIPE|MEMORY};

mysql –login-path=admin -e “SELECT VERSION()”;

# mysql
mysql> use db1;
mysql> SELECT * FROM t1;
mysql> UPDATE t1 SET id = id + 1;
mysql> COMMIT;


# mysql -uroot -p –safe-update
mysql> use db1;
mysql> UPDATE t1 SET id = id + 1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

1.?与help一样,获得帮助信息;
2.clear:清除当前输入的语句,在错误的语句后面输入\c即可;
3.connect:重新连接服务器,可以指定主机名和服务器,输入connect dbname host或或者\r dbname host即可;
4.delimiter:语句分隔符,设置执行语句的符号,执行delimiter $$即可;
5.edit:调用vi修改sql语句,在要修改的语句后面输入\e即可;
6.go:发送命令到mysql服务器;
7.pager:查询内容太多,分页显示,设置:>pager less,禁止:>nopager;
8.tee:把查询结果输入到一个文件中:>tee tmp/rst.txt,禁止notee,主要用于数据库的备份脚本输出;
9.prompt:改变提示符,如:prompt royalwzy>;则每次输入命令是提示符变为royalwzy>字符串(可以在配置文件中定义,prompt=xxx);
10.quit/exit:都是退出客户端,输入quit,exit和\q都行;
11.source:执行一个sql脚本文件,文件名为参数,source tmp/sql.txt;
12.system:执行操作系统的命令,用法:system ls -l tmp 或者\! ls -l tmp;
13.status:查看服务器信息的状态,输入status或者\s即可;
14.use:改变使用的数据库,后面跟数据库的名称,
eg:use mysql;
15.charset:修改字符集,可以通过status查看当前使用的字符集,
eg:charset latin1;
16.warnings:开启警告信息,当输入的sql语句出错时,可以通过show warning或者show errors来打印警告或者错误信息,设置:warning或者\W,关闭:nowarning或者\w;
17.rehash:设置客户端自动补全功能;
1）.在服务器的配置文件中[mysql]节点下,默认使用no-auto-rehash选项;
2）.注释no-auto-rehash选项,添加auto-rehash选项,reboot;

mysql> \s;

tee my_tee_file.txt;

1.CREATE DATABASE/TABLE:用于创建具有给定名称的数据库或表;
2.ALTER DATABASE/TABLE:可更改数据库或表的整体特性;
3.DROP DATABASE/TABLE:用于删除数据库中的所有表并删除该数据库,或者用于删除特定的表;

1.SELECT:用于从一个或多个表中检索所选的行;
2.INSERT:用于在现有表中插入新行;
3.DELETE:用于删除现有表中的行;
4.UPDATE:用于使用新值更新指定表中现有行的列;
5.JOIN:组合使用多个表以用于SELECT,多表DELETE和UPDATE语句;

>help contents;

>help administration;

>help data types;

help show; ? show;

show variables like ‘%buffer%’;

set global|session key=value;

HELP STATUS;

eg:prompt (\u@\h)[\d]\>

1.create databasename:创建数据库;
2.debug:把bug信息写入日志文件;
3.drop databasename:删除数据库;
4.extended-status:列出服务器的附加信息;
5.flush-hosts:刷新主机缓存;
6.flush-logs:刷新所有的日志;
7.flush-status:清空状态变量;
8.flush-tables:刷新表;
9.flush-threads:刷新线程缓存;
10.flush-privileges:重新加载授权表,相当于reload;
11.kill id,id,…:杀掉mysql的进程;
12.password [new-password]:以当前格式修改密码;
13.old-password [new-password]:以旧的格式修改密码;
14.ping:检查mysqld是否活动;
15.processlist:列出所有活动的进程;
16.reload:重新加载授权表;
17.refresh:刷新所有的表,并重新打开日志文件;
18.shutdown:关闭Server;
19.status:查看服务器的状态信息;
20.start-slave:启动slave;
21.stop-slave:停止slave;
22.variables:打印变量状态;
23.version:查看服务器版本信息;

CREATE TABLE t_time
(
id INT NOT NULL,
t1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
t2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
t3 TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP
);
mysql> INSERT INTO t_time(id) VALUES(1);
mysql> COMMIT;
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1 | t2 | t3 |
+—-+———————+———————+———————+
| 1 | 2015-08-19 14:00:42 | 2015-08-19 14:00:42 | 0000-00-00 00:00:00 |
+—-+———————+———————+———————+
mysql> UPDATE t_time SET t1 = ‘2000-01-01 00:00:00’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1 | t2 | t3 |
+—-+———————+———————+———————+
| 1 | 2000-01-01 00:00:00 | 2015-08-19 14:00:42 | 2015-08-19 14:01:43 |
+—-+———————+———————+———————+
1 row in set (0.00 sec)

SHOW DATABASES和SHOW TABLES:返回包含数据库和表名的列表;
SHOW COLUMNS:生成表中列的定义;
SHOW COLUMNS FROM table等于DESC table;
需要有SELECT特权才能使用SHOW语句;

INFORMATION_SCHEMA数据库
INFORMATION_SCHEMA表;

• COLUMNS:表和视图中的列
• ENGINES:存储引擎
• SCHEMATA:数据库
• TABLES:数据库中的表
• VIEWS:数据库中的视图

• PARTITIONS:表分区
• FILES:存储 MySQL NDB 磁盘数据表的文件

• COLUMN_PRIVILEGES:MySQL 用户帐户所拥有的列特权
• SCHEMA_PRIVILEGES:MySQL 用户帐户所拥有的数据库特权
• TABLE_PRIVILEGES:MySQL 用户帐户所拥有的表特权
• USER_PRIVILEGES:MySQL 用户帐户所拥有的全局特权

• CHARACTER_SETS:可用的字符集
• COLLATIONS:每个字符集的排序
• COLLATION_CHARACTER_SET_APPLICABILITY:适用于特定字符集的排序

• KEY_COLUMN_USAGE:关键列的约束
• REFERENTIAL_CONSTRAINTS:外键
• STATISTICS:表索引
• TABLE_CONSTRAINTS:表的约束

• KEY_COLUMN_USAGE:约束
• GLOBAL_STATUS:所有 MySQL 连接的状态值
• GLOBAL_VARIABLES:用于新的 MySQL 连接的值
• PLUGINS:服务器插件
• PROCESSLIST:指示哪些线程正在运行
• SESSION_STATUS:当前 MySQL 连接的状态值
• SESSION_VARIABLES:当前 MySQL 连接的生效值

• EVENTS:预定事件
• ROUTINES:存储过程和功能
• TRIGGERS:数据库中的触发器
• PARAMETERS:存储过程和功能参数以及存储函数

• INNODB_CMP 和 INNODB_CMP_RESET:对压缩的 InnoDB 表的相关操作的状态
• INNODB_CMPMEM 和 INNODB_CMPMEM_RESET:InnoDB 缓冲池中压缩页面的状态
• INNODB_LOCKS:InnoDB 事务所请求和持有的每个锁
• INNODB_LOCK_WAITS:每个阻塞的 InnoDB 事务的一个或多个行锁
• INNODB_TRX:当前正在 InnoDB 内部执行的所有事务
• TABLESPACES:活动的表空间

 SELECT CONCAT(“mysqldump -uroot -pmysql “, TABLE_SCHEMA, ” “, TABLE_NAME, ” >> “,TABLE_SCHEMA, “.sql”)  
 FROM TABLES 
 WHERE TABLE_NAME LIKE ‘Country%’  INTO OUTFILE ‘\tmp\Country_Dump.sh’

shell> \tmp\Country_Dump.sh
shell> \tmp\mysqldump -uroot -pmysql world_innodb Country >> world_innodb.sql
shell> \tmp\mysqldump -uroot -pmysql world_innodb Country_Language >> world_innodb.sql

shell>
 mysql -uroot -pmysql –silent –skip-column-names -e “SELECT 
CONCAT(‘CREATE TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘_backup LIKE ‘, 
TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’) FROM INFORMATION_SCHEMA.TABLES WHERE
 TABLE_SCHEMA = ‘world_innodb’;” | mysql -uroot -pmysql

- SHOW DATABASES:列出可用数据库的名称
- SHOW TABLES:列出默认数据库中的表
- SHOW TABLES FROM <database_name>:列出指定数据库中的表
- SHOW COLUMNS FROM <table_name>:显示表的列结构
- SHOW INDEX FROM <table_name>:显示表中有关索引和索引列的信息
- SHOW CHARACTER SET:显示可用的字符集及其默认排序
- SHOW COLLATION:显示每个字符集的排序

mysql> SHOW DATABASES;
mysql> SHOW TABLES;
mysql> SHOW TABLES FROM mysql;
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
mysql> SHOW COLUMNS FROM CountryLanguage;
mysql> SHOW FULL COLUMNS FROM CountryLanguage\G

mysql> EXPLAIN table_name;