使用MySQL

这里介绍使用命令行的方式，可视化界面的操作请参考msql工具。
目的：连接和登录MySQL，如何用USE 选择数据库，如何用SHOW 查看MySQL数据库、表和内部信息。

连接

#链接本地
mysql -u root -p 
# 链接远程计算机
mysql -h 110.110.110.110 -u root -p 123; 
exit # 退出

数据库/表操作

查看有什么数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.07 sec)

进入数据库

mysql> use mysql;
Database changed

查看有什么表

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| user                      |
+---------------------------+
31 rows in set (0.01 sec)

查看表有什么列

mysql> show columns from db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(32)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

show支持的其他命令

mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
………………………………

示例：

显示表的创建信息：

show create table db;
CREATE TABLE `db` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`,`User`),
  KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'

显示mysql当前状态（配置）信息：

mysql> SHOW STATUS;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name                                 | Value                                            |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients                               | 0                                                |
| Aborted_connects                              | 0                                                |
| Binlog_cache_disk_use                         | 0                                                |
| Binlog_cache_use                              | 0                                                |
| Binlog_stmt_cache_disk_use                    | 0                                                |
| Binlog_stmt_cache_use                         | 0                                                |
| Bytes_received                                | 1029                                             |
| Bytes_sent                                    | 29084                                            |
| Com_admin_commands                            | 0                                                |                                       |

显示所有授权信息

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

基础

密码

# 修改密码
update user set password=password('123') where user='root' and host='localhost'; 
flush privileges; 
# 重置密码步骤：
# 关闭服务
net stop mysql
# 启动服务
mysqld --console --skip-grant-tables
# 修改密码
use mysql;
update user set password=password('abc') WHERE User='root';

管理用户

创建

CREATE USER 'usernamexxx'@'hostxxx' IDENTIFIED BY 'passwordxxx';

示例：

1. CREATE USER 'jack'@'localhost' IDENTIFIED BY '123456'; # 本地用户登录
2. CREATE USER 'rose'@'192.168.38.110_' IDENDIFIED BY '123456'; # 指定IP白名单
3. CREATE USER 'rose'@'%' IDENTIFIED BY '123456';                    # 开放所有IP
4. CREATE USER 'rose'@'%' IDENTIFIED BY '';                                # 不设置密码，和下面一样
5. CREATE USER 'rose'@'%';

授权
参考：https://blog.csdn.net/huxinguang_ios/article/details/80887175

GRANT privilegesxxx ON databasenamexxx.tablenamexxx TO 'usernamexxx'@'hostxxx'

示例：

GRANT SELECT, INSERT ON DbXXX.user TO 'jack'@'%';
GRANT ALL ON *.* TO 'jack'@'%';
GRANT ALL ON DbXXX.* TO 'jack'@'%';
# 增加一个用户test1密码为abc
grant select,insert,update,delete on *.* to [email=test1@%]test1@%[/email] Identified by “abc”; 
# 增加一个用户test2密码为abc,让他只可以在localhost上登录
grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;

常用操作

数据库

# 查看
SHOW databases; # 查看所有数据库
SHOW CREATE DATABASE 数据库名 # 查看某个数据库的定义信息
SELECT DATABASE(); # 查看正在使用的数据库
# 创建
create database db2 # 创建
CREATE DATABASE IF NOT EXISTS db2; # 创建：避免报错
# 删除
drop database mydatabase # 删除数据库
# 切换
use mydatabase # 切换使用数据库

数据表

# 新建表
create table 表名 # 新建
CREATE TABLE 新表名 LIKE 旧表名; # 根据已有表结构
# 查看表
show tables; # 查看数据库中的所有表：
desc 表名 # 查看表结构，（等效于show columns from 表名）
# 修改表
rename table 原表名 to 新表名; 
# 删除表
drop table name # 删除
DROP TABLE IF EXISTS 表名; # 不会报错

约束

常用约束

主键：PRIMARY KEY
外键：FOREIGN KEY REFERENCES
默认值：DEFAULT
唯一值：UNIQUE
不为空：NOT NULL
自增长：AUTO_INCREMENT

常用操作

# 默认约束名
字段 DEFAULT “123” # 默认值
# 自定义约束名
CONSTRAINT 约束名 PRIMARY KEY (firstName) # 主键
CONSTRAINT 约束名 FOREIGN KEY (in_dpt) REFERENCES department(dpt_name) # 外键
# 后期添加约束
ALTER TABLE Persons ADD PRIMARY KEY (firstName,lastName) # 无名称
ALTER TABLE Persons ADD [CONSTRAINT 名称] UNIQUE (Id_P) # 添加时有名称
# 删除约束
ALTER TABLE Persons DROP PRIMARY KEY 名称 # 删除主键

索引

# 增加索引
alter table 表名 add index 索引名 (字段名1[，字段名2 …]); 
# 删除索引
alter table 表名 drop index 索引名;

字段

# 新建字段
alter table 表名 add 字段 类型; # 字段增加
# 修改字段
ALTER TABLE 表名 MODIFY 列名 数据类型; # 字段修改
ALTER TABLE 表名 CHANGE 列名 新列名 数据类型; # 字段修改
# 删除字段
ALTER TABLE table_name DROP 列名; # 字段删除

视图

# 创建视图
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
# 删除视图
drop view 视图名

存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `myproc`(in p varchar(20))
BEGIN
    -- 定义所有变量
    DECLARE v1 varchar(20);
    DECLARE n int;
    -- 赋值
    set n=200;
    -- 循环
    while(n >= 0) do
        -- 执行sql，注意引号的使用
        insert into ceshi2(`udata`) values(p);
        set n=n-1;
    end while;
    -- 判断
    IF i< 10 then
        -- 字符串拼接
        SET tblname = CONCAT(dbname,'.order_release_record_',tableNm,'_0',i);
    ELSE            
        SET tblname = CONCAT(dbname,'.order_release_record_',tableNm,'_',i);
    END IF;  
END

其他

编码

修改库和表的编码 ```
修改数据库编码
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;

修改表编码

ALTER TABLE 表名 character set 字符集;

> 当我们使用DOS命令行进行SQL语句操作如有有中文会出现乱码，导致SQL执行失败
- 查看编码设置

show variables like ‘character%’;


- 临时设置（退出dos无效）
```mysql
set character_set_client=gbk;
set character_set_connection=gbk;
set character_set_results=gbk;

永久设置（不推荐）

修改MySQL安装目录下的my.ini文件，重启服务所有地方生效。此方案将所有编码都修改了[不建议]

select 特殊用法

select version();  #输出版本
select database(); # 输出当前数据库
select now(); # 显示当前时间：2017-11-29 14:33:26
SELECT DAYOFMONTH(CURRENT_DATE); # 显示日
SELECT MONTH(CURRENT_DATE); # 显示月
SELECT YEAR(CURRENT_DATE);  # 显示年
SELECT "welecome to my blog!";  # 显示字符串
select ((4 * 4) / 10 ) + 25;  # 当作计算器

导入导出

不包括数据库名，包括表视图

sql文件

# 数据库内导入
source c:/school.sql
# 数据库外导入
mysql  -uroot  -proot day03<d:\day03.sql
===================================
# 导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名 
# 导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 
#导出一个数据库结构.
mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql

# 文本-->数据库
LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字; 
# 数据库 --> 文本
SELECT 列 FROM 表名字 INTO OUTFILE '文件路径和文件名' ;

注释

1 创建表的时候写注释
create table test1
( field_name int comment '字段的注释'
)comment='表的注释';
alter table test1 comment '修改后的表的注释'; 修改表的注释
alter table test1 modify column field_name int comment '修改后的字段注释'; # 修改字段的注释
show create table test1; 查看表注
show full columns from test1; # 查看字段注释

蠕虫复制

在已有的数据基础之上，将原来的数据进行复制

INSERT INTO student2 SELECT * FROM student;

新建表示例

drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
    id int(3) auto_increment not null primary key,
    name char(10) not null,
    address varchar(50) default ‘深圳’,
    year date，
); //建表结束
//以下为插入字段
insert into teacher values(”,’allen’,'大连一中’,'1976-10-10′);
insert into teacher values(”,’jack’,'大连二中’,'1975-12-23′);

设置执行日志

参考：https://blog.csdn.net/qidaif/article/details/80931703
默认没有开启日志，如果我们想要知道所有查询情况，需要打开。
1、查看是否打开

SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------------+
| Variable_name    | Value                            |
+------------------+----------------------------------+
| general_log      | OFF                              |
| general_log_file | /var/lib/mysql/galley-pc.log |
+------------------+----------------------------------+
2 rows in set (0.00 sec)

2.1、临时打开

mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL general_log_file = '/var/log/mysql/general_log.log';

2.2、永久打开

配置my.cnf文件

general_log = 1
general_log_file = /var/log/mysql/general_sql.log

查看mysql库大小，表大小，索引大小

通过MySQL的 information_schema 数据库，可查询数据库中每个表占用的空间、表记录的行数。

查看库大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='jishi';

查看表大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='jishi' and table_name='a_ya';

查看指定库的索引大小

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'jishi';

查看指定表的索引大小

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'test' and table_name='a_yuser';

查看整个库的使用情况（行数、大小、索引）

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'memory';

mysql