mysql学习笔记

mysql命令

启动和停止mysql服务

在windows系统中使用：

net start mysql

net stop mysql

在linux系统中使用：

systemctl start mysql

systemctl stop mysql

查看mysql版本信息

mysql --version

或者进入数据库后使用：

select version();

连接mysql服务器

mysql -u 用户名 -p -h 主机地址 -P 端口号 -D 数据库名称

例如：

mysql -u root -p -h localhost -P 3306 -D testdb1

mysql -u 用户名 -p

例如：

mysql -u root -p

说明：

-u，后面是用户名

-p，后面没有密码，回车后再输入密码

-h，后面是主机名称或IP地址

-P，后面是端口号，默认3306

基本命令：

mysql -u root -p

上述命令用root用户登录mysql系统数据库，输入-p后直接回车，按照要求输入root用户对应的密码

完整的命令格式：

mysql -u username -p -h 192.168.19.164 -P 3306 -D database_name

mysql8在-p后面不能输入密码，回车后再输入密码,-P后面是端口号，可以省略，默认3306;-D可以省略，直接写数据库名称

例如：

mysql -u test1 -p -P 3306 testdb1

退出连接客户端

\q

或 exit 或quit

显示帮助

\h

或help

清除当前输入

\c

如果输入了错误的命令或想要取消当前输入，可以使用此命令

执行外部 SQL 文件

\. filename.sql

或者source filename.sql

例如：

\. /path/to/script.sql

source /path/to/script.sql

输出命令（tee）

记录数据库操作的日志，类似oracle的spool命令

\T 或者tee

例如：\T /home/mysql/123.log

关闭输出命令：

\t  或者notee

输出命令（pager）

记录数据库操作的日志，类似oracle的spool命令

在mysql日常操作中，妙用pager设置显示方式，可以大大提高工作效率。比如select出来的结果集超过几个屏幕，那么前面的结果一晃而过无法看到，这时候使用pager可以设置调用os的more或者less等显示查询结果，和在os中使用more或者less查看大文件的效果一样。

pager用法：
实际上等于将它设置以后的所有mysql操作命令的输出通过pager设置命令执行，类似于管道符的作用

nopager命令：取消pager设置，恢复之前的输出状态。（如果不设置nopager，那么只能通过重启mysql服务才能恢复了）；或者直接使用pager命令后面不加任何参数，也可以恢复之前的输出状态

举些例子来说明吧：
1）当处理大量数据时，不想显示查询的结果，而只需知道查询花费的时间。

2）将结果输出到文件

#pager cat >> test.txt

将查询结果输出到test.txt文件中，多个查询结果会追加，如果多个查询结果需要覆盖上次查询结果，请使用：pager cat > test.txt

3）查看少量查询结果

#pager less

如果查询结果很多，用该命令可以分页查看少量结果，按q键退出查看结果

4）结合其它命令

例如，结合 grep 和 wc -l 来统计 show processlist 中的睡眠连接数

查询结果以列形式展示（换行展示）

\G命令

例如：

#select * from t_2 \G;

edit命令

edit命令是在编辑器里面进行编辑,SQL 然后再执行。因为一些长的SQL语句换行后，或长的SQL语句在终端更改麻烦。这个时候edit就可以派上用场。

使用方法：

先输入edit名，然后进入编辑界面编写执行SQL，然后按 :wq 保存编辑的SQL，回到mysql客户端界面，然后输入分号（;）执行SQL

显示所有数据库

SHOW DATABASES;

选择数据库

USE database_name;

切换数据库

\u database_name

显示当前数据库

\s  或者 status

显示数据库配置变量

show variables like 'secure_file_priv';
 

显示当前数据库中的所有表

SHOW TABLES;

显示表结构

DESCRIBE table_name;

或者

desc table_name

或者

SHOW COLUMNS FROM table_name;

查看建表语句：

show create table 表名称

查看表索引

show index from 表名称

开启事务：

START TRANSACTION; 或 BEGIN;

提交事务：

commit；

回滚事务：

rollback；

锁表：

LOCK TABLES 表名 WRITE; 或 LOCK TABLES 表名 READ;

解锁表：

UNLOCK TABLES;

将查询结果保存到文本文件

into outfile子句可以将查询结果导出到文本文件，虽然并不一定要是CSV格式，但大多数情况下我们都会选择这种格式。

要将MySQL中的数据写入到操作系统的文件中， 首先需要具有FILE权限。而且为了安全需要配置参数secure_file_priv，这个参数是限制MySQL可以写入文件的目录，如果为空，表示不限制：

show variables like 'secure_file_priv';
我们在普通的select语句最后添加into outfile ‘/path/file_name’;即可将查询结果写入文件，这里的path就是参数secure_file_priv定义的目录（文件不能已存在）：

select * from user_t where user_name like 'Li%' into outfile '/opt/mysql8.0.35/mysql-files/user.txt';
 

设置/查看变量

设置单个变量

SET @myVariable = 'some value';

设置会话级别变量

SET SESSION sort_buffer_size = 102400;

查看会话级别的变量值

SELECT @@SESSION.sort_buffer_size;

查看所有会话级别的变量值

SHOW SESSION VARIABLES;

设置全局变量

SET GLOBAL max_connections = 1000;

查看全局变量值

SELECT @@GLOBAL.max_connections;

查看指定的全局变量值

SHOW GLOBAL VARIABLES LIKE 'max_connections';

查看所有全局变量值

SHOW GLOBAL VARIABLES;

在启动MySQL时设置变量

可以在MySQL服务器的配置文件（如my.cnf或my.ini，取决于操作系统）中设置全局变量。例如：

[mysqld]
max_connections = 1000

修改配置文件后，需要重启MySQL服务来使更改生效。

查看数据库支持的字符集

show charset；

查看数据库支持的字符集校验规则

show collation;

查看数据库创建语句

show create database + 数据库名；

查看mysql连接情况

show processlist;

system命令

使用system命令，可以不用退出mysql客户端而直接使用linux命令

语法：system linux系统命令

例如:#system top

查看服务器状态

status命令可以查看服务器状态信息

#status

数据库

创建数据库

语法：CREATE DATABASE 数据库名 [OPTIONS];      

例如：

CREATE DATABASE mydb1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;   

注意：mysql中database和schema是一个概念，创建一个schema就是创建一个数据库，因此创建一个数据库可以改为：CREATE SCHEMA 数据库名 [OPTIONS];

修改数据库

语法：

alter database + 数据库名 + [alter_spacification] [alter_spacification]...;

删除数据库

DROP DATABASE database_name;

表

创建数据表：

CREATE TABLE <表名>
(
字段名1 数据类型[列级别约束条件][默认值],
字段名2 数据类型[列级别约束条件][默认值],
…
[表级别约束条件]
);

语法参考：

CREATE TABLE `test1`.`student_t1` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '学生ID',
  `no` VARCHAR(45) NULL COMMENT '学号',
  `name` VARCHAR(45) NULL COMMENT '学生姓名',
  `sex` VARCHAR(45) NULL COMMENT '性别',
  `birthday` DATE NULL COMMENT '生日',
  `creation_date` DATE NULL COMMENT '创建日期',
  PRIMARY KEY (`id`),
  INDEX `idx_name` (`name` ASC),
  UNIQUE INDEX `idx_no` (`no` ASC))
ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci COMMENT='学生表';

其中：

AUTO_INCREMENT属性为自增列，默认步长是1

可以修改步长：ALTER TABLE student_t1 AUTO_INCREMENT = 2;

comment：是给表或字段增加的备注信息

例如：
CREATE TABLE tb_emp1
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);

添加主键约束（创建表时）
CREATE TABLE tb_emp1
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);

添加主键约束（定义完所有列之后）
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
);

多字段联合主键
PRIMARY KEY[字段1，字段2，…，字段n]
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id,mame)
);

使用外键约束：首先外键是表中的一个字段，它可以不是本表（子表）的主键，但对应另外一个表的主键（父表）。外键的主要作用就是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据一致性，完整性。
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。
创建外键的语法：[CONSTRAINT<外键名> FOREING KEY 字段名1[,字段名2,…]] REFERENCES<主表名>
主键列[,主键列2,…]
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25),
location VARCHAR(50)
);
CREATE TABLE tb_emp5
(
id INT(11) ,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
CONSTRAINT fk_emp_dept1 FORRIGN KEY(deptId) REFERENCES tb_dept1(id);
);
以上语句执行成功之后，在表tb_emp5上添加了名称为fk_emp_dept1的外键约束，外键名称为deptId，其依赖于表tb_dept1的主键id。

使用非空约束
指定字段的值不能为空。
语法：字段名 数据类型 NOT NULL。
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25) NOT NULL,
location VARCHAR(50)
);

使用唯一性约束
要求该列唯一，允许为空，但只能出现一个空值，唯一约束可以确保一列或者几列不出现重复值。
语法如下：
字段名 数据类型 UNIQUE（创建表时）
[CONSTRAINT<约束名> UNIQUE<字段名>]（定义完所有列之后）
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25) UNIQUE,
location VARCHAR(50)
);
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25),
location VARCHAR(50),
CONSTRAINT STH UNIQUE(name)
);

使用默认约束
指定某列的默认值。例如将某表中的年龄一列指定为默认值20。新插入数据时，如果没有指定值，就会默认为20.
语法：字段名 数据类型 DEFAULT 默认值
例如：
CREATE TABLE tb_emp1
(
id INT(11) ,
name VARCHAR(25) ,
location VARCHAR(50) DEFA