MySQL基本操作Linux-MySQL-运维笔记-技术文档

命令

#查看版本

mysql --version

#进入mysql 命令

mysql -u root -p

mysql -u root@localhost (没有密码的情况)

#创建数据库

create database [dataname]

#删除数据库

drop database [dataname]

#查看数据库

show databases;

#进入具体数据库

use [dataname]

#查看数据库表

show tables;

#删除表

drop table [tablename]

#查看具体表的结构

desc [tablename]

#查找数据

select * from tablename

#插入数据

insert insto [tablename](103,'test');

#修改数据

update [tablename] set name='' and id=103

#删除数据

delete [tablename] where name=''

#添加索引

alter table [tablename] add fulltext index([columnname]);

#查看索引

show index from [tablename] G

启动开关: service mysql {start|stop|status|restart|condrestart|try-restart|reload|force-reload}

修复表

查看表状态

show table status like 'tablename' G;

检测表

check table tablename

修复表

repair table tablename

压缩表

查看数据文件位置

show global variables like '%datadir%';

压缩文件

myisampack *.MYD

#mysql 备份

冷备份

备份

停掉mysql 服务，在操作基本备份mysql 数据库

重启mysql服务，备份重启以后生产binlog

逻辑恢复

mysql -u root -p [databasename]<[backname].sql

逻辑备份

导出整个数据库

mysqldump -u root -p [databasename] -F >[backname].sql

导出一个表

mysqldump -u root -p [databasename] [tablename]>[table].sql

导出数据库结构

mysqldump -u root -p -d --add-drop-table [databasename]>[database].sql

-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

下面命令尝试可用：
　　　　mysqladmin -u root -p password + 回车（若之前有密码，则会显示在password之后）
　　　　Enter password: 输入新密码（不可见）
　　mysql> STATUS(s)　　- 列出当前mysql的相关状态信息
　　mysql> SHOW DATABASES;　　- 显示数据库列表
　　mysql> USE DB_name;　　- 选中数据库DB_name
　　mysql> SHOW TABLES;　　- 显示DB_name下的TABLES列表
　　mysql> CREATE DATABASE DB_name;　　- 创建一个新的数据库，当然，首先你应该以root用户登录，普通用户并没有创建数据库的权利
　　mysql> GRANT SELECT,INSERT,DELETE,UPDATE ON DB_name TO user_name IDENTIFIED
　　　　 > by 'user_psd';　　- 授予新用户user_name对于数据库DB_name的指定权限，登录密码user_psd。该用户拥有的权限：SELECT,INSERT,DELETE,UPDATE
　　mysql DB_name -u user_name -p　　- 用新创建的用户user_name登录数据库DB_name
　　mysql mytest -u root -p　　- 用root用户登录数据库DB_name，当我们需要为数据库DB_name创建新表时，需要root用户的权限，如下，创建新表的过程：
　　mysql> CREATE TABLE employees (
　　　　- > empid int not null,　　- 该字段列值非空
　　　　-> lastname varchar(30),
　　　　-> firstname varchar(30),
　　　　-> salary float,
　　　　-> primary key (empid));　　- 该字段列值唯一，"primary key" 表示该列是表的主键, MySQL将自动索引该列
　　mysql> SHOW COLUMNS FROM employees;　　- 显示employees各字段信息（有时我们需要知道表的结构才能确定插入内容的格式）
　　mysql> SHOW CREATE TABLE employees;　　- 显示表employees的创建过程，同样可以查看其内部结构
　　mysql> INSERT INTO table VALUES (...);　　- 向表table插入一条信息，如：mysql> INSERT INTO employees VALUES (1, 'Blum', 'Rich', 25000.00);
　　　　若：mysql> INSERT INTO employees VALUES (1, 'Blum', 'Barbara', 45000.00);
　　　　则：ERROR 1062 (23000): Duplicate entry '1' for key 1
　　mysql> DELETE FROM employees WHERE empid = 1;　　- 删除empid = 1的那条信息
　　mysql> DELETE FROM employees;　　- 删除指定数据库表employees的所有项
　　mysql> SELECT * FROM employees;　　- 查询表employees的所有字段内容，SELECT为查询命令
　　mysql> SELECT datafields FROM table;　　- 查询字段列表datafields指定的内容（个字段间用“,”分割）。常用的三个过滤器修饰符如下：
　　　　WHERE：显示符合特定条件的数据行子集。如：mysql> SELECT * FROM employees WHERE salary > 40000;
　　　　ORDER BY：以指定顺序显示数据行。
　　　　LIMIT：只显示数据行的一个子集。
　　E.G. ：
　　　　mysql> SELECT * FROM employees;
　　　　+-------+----------+-----------+--------+
　　　　| empid | lastname | firstname | salary |
　　　　+-------+----------+-----------+--------+
　　　　| 0 | ER | ZHANG | 4500 |
　　　　| 1 | SAN | ZHANG | 5500 |
　　　　| 2 | SI | ZHANG | 6500 |
　　　　| 3 | WU | ZHANG | 7500 |
　　　　| 4 | LIU | ZHANG | 8500 |
　　　　| 5 | QI | ZHANG | 9500 |
　　　　+-------+----------+-----------+--------+
　　mysql> SELECT lastname, salary FROM employees WHERE salary > 5000 && salary < 8000;
　　　　+----------+--------+
　　　　| lastname | salary |
　　　　+----------+--------+
　　　　| SAN | 5500 |
　　　　| SI | 6500 |
　　　　| WU | 7500 |
　　　　+----------+--------+
　　mysql> SELECT * FROM employees1 ORDER BY lastname (ASC/DECS);　　- 按字段lastname排序查询表employees1
　　mysql> SELECT * FROM employees1 WHERE salary LIKE '6%';　　- 限制salary为数字6开头的项，LIKE字句可以代替“=”使用
　　mysql> exit/quit　　- 退出数据库软件

2. 新建数据库之后的相关操作：
　　ALTER：
　　　　mysql> ALTER TABLE table_name ...;　　- 创建表之后，有时我们需要对表的结构进行修改，就用这个作为命令头部
　　　　mysql> ALTER TABLE table_name RENAME (AS) table_new_name;　　- 表重命名
　　　　mysql> ALTER TABLE table_name ADD 列名列数据类型 [AFTER 插入位置];　　- 在表中新增一个字段信息
　　　　mysql> ALTER TABLE table_name CHANGE 列名称列新名称新数据类型;　　- 指定列重命名
　　　　mysql> ALTER TABLE table_name DROP 列名称;　　- 删除指定列
　　DROP：
　　　　mysql> DROP TABLE table_name;　　- 删除指定表
　　　　mysql> DROP DATABASE database_name;　　- 删除指定数据库
　　　　mysqladmin -u root -p drop database_name;　　- 同样可以使用mysqladmin命令在mysql软件之外删除指定数据库
　　UPDATE：
　　　　+-------+----------+-----------+--------+
　　　　| empid | lastname | firstname | salary |
　　　　+-------+----------+-----------+--------+ +-------+----------+-----------+--------+
　　　　| 5 | QI | ZHANG | 9500 |　　　　 | 5 | QI | ZHANG | 9500 |
　　　　| 7 | BA | ZAHNG | 9500 | -->　 | 6 | BA | ZAHNG | 9500 |
　　　　+-------+----------+-----------+--------+ +-------+----------+-----------+--------+
　　　　mysql> UPDATE employees1
　　　　　　-> SET empid = 6
　　　　　　-> WHERE lastname = 'BA';　　- 限定条件

3. 数据库表间数据复制：http://www.jb51.net/article/47562.htm
　　同一数据库表间复制：
　　　　INSERT (INTO) table1 select * from table2;　　- 完全复制（mysql测试可用）
　　　　INSERT (INTO) table1 select distinct * from table2;　　- 不复制重复纪录（mysql测试不可用）
　　　　INSERT (INTO) table1 select top 5 * from table2;　　- 前五条纪录（mysql测试不可用）
　　跨数据库表间复制：
　　　　INSERT (INTO) (current.)table1 select * from src_database.table2;　　- 完全复制
　　　　INSERT (INTO) (current.)table1 select distinct * from src_database.table2;　　- 不复制重复纪录
　　　　INSERT (INTO) (current.)table1 select top 5 * from src_database.table2;　　- 前五条纪录
　　若table1不存在，则首先应该创建表，并使其结构与src_database结构相同方可copy：
　　　　CREATE TABLE table1 LIKE (src_database.)table2;　　- +(src_database.)取决于是否在同一个数据库
　　　　INSERT table1 SELECT * FROM (src_database.)table2;

4. 数据库重命名的几种方法：http://www.cnblogs.com/allenhua/p/5393189.html
　　以方法四为例（mysqldump导出数据再导入）：mytest -> mytest1
　　mysqldump -u root -p mytest > mytest_dump.SQL
　　mysql -u root -p -e "CREATE DATABASE mytest1"
　　mysql -u root -p mytest1 < mytest_dump.SQL
　　mysql -u root -p -e "DROP DATABASE mytest"

5. 数据库用户权限相关：
　　mysql> GRANT ALL PRIVILEGES ON mytest1.* TO test@localhost IDENTIFIED BY 'test';　　- 授予用户test对于数据库mytest1的所有权限
　　mysql> GRANT SELECT, INSERT, DELETE,... ON mytest1.* TO test@localhost IDENTIFIED BY 'test';　　- 授予用户test指定权限
　　mysql> DELETE FROM user WHERE user = 'test';　　- 删除用户test
　　mysql> FLUSH PRIVILEGES;　　- 刷新各用户权限
　　mysql> select * from user;　　- 查询所有用户的权利

6. 多表查询：
　　mysql> SELECT salary FROM employees1
　　　　-> UNION (ALL)
　　　　-> SELECT salary FROM employees2;　　- 合并employees1与employees2两表salary结果，无重复。+(ALL)全列出可重复
　　　　　　+--------+
　　　　　　| salary |
　　　　　　+--------+
　　　　　　| 4500 |
　　　　　　| 5500 |
　　　　　　| 6500 |
　　　　　　| 7500 |
　　　　　　| 8500 |
　　　　　　| 9500 |
　　　　　　| 10500 |
　　　　　　| 11500 |
　　　　　　+--------+
　　三种JOIN查询方式：http://www.runoob.com/mysql/mysql-join.html

　　　　两个实例数据库结构如下：
　　　　mysql> SELECT * FROM tcount_tbl;
　　　　+---------------+--------------+
　　　　| runoob_author | runoob_count |
　　　　+---------------+--------------+
　　　　| 菜鸟教程　　 | 　　10　　 |
　　　　| RUNOOB.COM | 　　20　　 |
　　　　| Google　　　　 | 　　22　　 |
　　　　+---------------+--------------+
　　　　mysql> SELECT * from runoob_tbl;
　　　　+-----------+---------------+---------------+-----------------+
　　　　| runoob_id | runoob_title | runoob_author | submission_date |
　　　　+-----------+---------------+---------------+-----------------+
　　　　| 1　　　　 | 学习 PHP　 | 菜鸟教程　　 | 2017-04-12 |
　　　　| 2　　　　 | 学习 MySQL | 菜鸟教程　　 | 2017-04-12 |
　　　　| 3　　　　 | 学习 Java　 | RUNOOB.COM | 2015-05-01 |
　　　　| 4　　　　 | 学习 Python | RUNOOB.COM | 2016-03-06 |
　　　　| 5　　　　 | 学习 C　　 | FK 　　　　　　 | 2017-04-05 |
　　　　+-----------+---------------+---------------+-----------------+
　　　　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;
　　　　　　等价于：
　　　　　　mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b
　　　　　　　　-> WHERE a.runoob_author = b.runoob_author;
　　　　　　+-------------+-----------------+----------------+
　　　　　　| a.runoob_id | a.runoob_author | b.runoob_count |
　　　　　　+-------------+-----------------+----------------+
　　　　--> | 1 | 菜鸟教程 | 10 |
　　　　--> | 2 | 菜鸟教程 | 10 |
　　　　　　| 3 | RUNOOB.COM | 20 |
　　　　　　| 4 | RUNOOB.COM | 20 |
　　　　　　+-------------+-----------------+----------------+
　　　　LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录

　　　　　　　　　　　　　　　　
　　　　　　mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a
　　　　　　　　 -> LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
　　　　　　+-------------+-----------------+----------------+
　　　　　　| a.runoob_id | a.runoob_author | b.runoob_count |
　　　　　　+-------------+-----------------+----------------+
　　　　--> | 1 | 菜鸟教程 | 10 |
　　　　--> | 2 | 菜鸟教程 | 10 |
　　　　　　| 3 | RUNOOB.COM | 20 |
　　　　　　| 4 | RUNOOB.COM | 20 |
　　　　　　| 5 | FK | NULL |
　　　　　　+-------------+-----------------+----------------+
　　　　RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录

　　　　　　　　　　　　　　　　
　　　　　　mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a
　　　　　　　　 -> RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
　　　　　　+-------------+-----------------+----------------+
　　　　　　| a.runoob_id | a.runoob_author | b.runoob_count |
　　　　　　+-------------+-----------------+----------------+
　　　　--> | 1 | 菜鸟教程 | 10 |
　　　　--> | 2 | 菜鸟教程 | 10 |
　　　　　　| 3 | RUNOOB.COM | 20 |
　　　　　　| 4 | RUNOOB.COM | 20 |
　　　　　　| NULL | NULL | 22 |
　　　　　　+-------------+-----------------+----------------+