Windows下MySQL配置使用

设置 Windows 环境变量

新建系统变量名：MYSQL_HOME ，值：D:\Zerow\mysql-winx64
值设置为MySQL安装目录
修改Path值，新增一条：%MYSQL_HOME%\bin

安装MySQL

下载 MySQL ZIP 压缩包后解压文件。

根目录新建 my.ini 文件

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\Zerow\mysql-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\Zerow\mysql-winx64\Data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
#错误日志
log_error=error.log
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

以管理员身份打开 cmd，进入 bin 目录执行：
1
$ mysqld -install MySQL --defaults-file="D:\Zerow\mysql-winx64\my.ini"
安装一个命名为 “MySQL” 的实例，如果显示 “Service successfully installed.” 则代表安装成功。

初始化数据库，保存在 Data 文件夹

1	$ mysqld --initialize-insecure --user=mysql

登录，默认密码为空
1
$ mysql -u root -p
显示所有数据库
1
$ mysql> show databases;

启动/停止 MySQL

启动
1
$ net start MySQL
停止
1
$ net stop MySQL
卸载:
1
$ sc delete MySQL

登录 MySQL

命令格式

1	$ mysql [-h 主机名] -u 用户名 -p -P 端口

-h：MySQl登录主机名，比如：localhost
-u：登录用户名，比如：root
-p：通知服务器即将使用密码登录
-P：设置端口，比如：3306

创建数据库

创建数据库命令

1	$ mysql> create database 数据库名 [其他选项];

1	$ mysql> create database samp_db character set gbk;

编码设置为 gbk
命令行模式下 MySQL 语句以分号（;）结尾

显示所有数据库

1	$ mysql> show databases;

选择指定数据库

登录时指定

1	$ mysql -D 所选择的数据库名 -h 主机名 -u 用户名 -p

例如

1	$ mysql -D samp_db -u root -p

use 语句指定
1
$ mysql> use MyDatabase

数据库操作

创建表

方法①

$ mysql> use samp_db;
Database changed
$ mysql> CREATE TABLE tutorials_tbl(
   -> tutorial_id INT NOT NULL AUTO_INCREMENT,
   -> tutorial_title VARCHAR(100) NOT NULL,
   -> tutorial_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY (tutorial_id)
   -> );
Query OK, 0 rows affected (0.16 sec)

方法②

创建一个 sql 文件（例如：D:/CreateTb.sql），文件内容：

CREATE TABLE tutorials_tbl (
	tutorial_id INT NOT NULL AUTO_INCREMENT,
	tutorial_title VARCHAR ( 100 ) NOT NULL,
	tutorial_author VARCHAR ( 40 ) NOT NULL,
	submission_date DATE,
    statu default "-"
PRIMARY KEY ( tutorial_id ) 
);

AUTO_INCREMENT :自增列
PRIMARY KEY：主键
default ：默认值

执行命令

1	$ mysql -D samp_db -u root -p < D:/CreateTb.sql

查看已创建表名称

1	$ mysql> show tables;

查看已创建表详细信息

1	$ mysql> describe 表名

删除表

1	$ mysql> DROP TABLE table_name ;

插入数据

1	$ mysql> insert into students values(NULL,'张三','男',18,'13800138000');

或者指定列

1	$ mysql> insert into students(name,sex,age) values('李四','女',18)

查询数据

$ mysql> select id,name,sex,age,tel from students [OFFSET M ][LIMIT N] ;

$ mysql> select * from students;

$ mysql> select * from students where sex='男';

运算符	说明	范例
`=`	检查两个操作数是否相等。如果相等，则条件为真。	`(A = B)`非真
`!=`	检查两个操作数是否相等。如果不相等，则条件为真。	`(A != B)`为真
`>`	检查左侧操作数是否大于右边的操作数。如果大于，则条件为真。	`(A > B)`非真
`<`	检查左侧操作数是否小于右侧操作数。如果小于，则条件为真。	`(A < B)`为真
`>=`	检查左侧操作数是否大于或等于右侧操作数。如果是，则条件为真。	`(A >= B)`非真
`<=`	检查左侧操作数是否小于或等于右侧操作数。如果是，则条件为真

where 子句不仅仅支持 “where 列名 = 值” 这种名等于值的查询形式, 对一般的比较运算的运算符都是支持的, 例如 =、>、<、>=、<、!= 以及一些扩展运算符 is [not] null、in、like 等等。
在 SELECT 将要返回记录的位置处，使用 OFFSET 可以指定一个偏移。默认偏移为0。
可以使用 LIMIT 属性来限制返回记录的数量。

判断是否为 NULL

IS NULL和IS NOT NULL

更新数据

1	$ mysql> update students set age=20 where id=2;

删除数据

1 2	$ mysql> delete from students where age=20; $ mysql> delete from students;

修改表结构

添加列

1	$ mysql> alter table students add ClassName char(10) [after age];

after age：可选，表示在age字段后面添加

修改列

1	$ mysql> alter table students change ClassName className char(16);

删除列

1	$ mysql> alter table students drop className;

重命名表

1	$ mysql> alter table students rename MyStudents;

删除整张表
1
$ mysql> drop table MyStudents
删除整个数据库
1
$ mysql> drop database testdemo;

修改 root 密码

1	$ mysqladmin -u root -p password 新密码

执行命令后输入旧密码回车完成修改。

MySQL 正则表达式

模式	模式匹配对象
`^`	字符串的开始位置
`$`	字符串的结尾
`.`	单个字符
`[...]`	一对方括号之间的字符
`[^...]`	未在一对方括号之间的字符
`p1	p2
`*`	匹配前面元素的零个或多个实例
`+`	匹配前面元素的一个或多个实例
`{n}`	匹配前面元素的n个实例
`{m,n}`	匹配前面元素的m~n个实例，m <= n

示例：

1	$ mysql> select * from students where sname regexp '^李';

MySQL 索引

创建索引

1 2	CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)

添加与删除 INDEX 的 ALTER 命令
为表添加索引，可以采用 4 种语句。
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
  该语句添加一个主键。意味着索引值必须是唯一的，不能为 NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
  该语句为必须唯一的值（除了 NULL 值之外，NULL 值可以多次出现）创建索引。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list)
  语句为可能多次出现的值创建一般索引。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
  语句创建专用于文本搜索的 FULLTEXT 索引。
- 下面这个范例将为现有表添加索引。
1
$ mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
可以使用 DROP 子句以及 ALTER 命令删除索引，通过下面这个范例来删除之前创建的索引。
1
$ mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
- 利用 ALTER 命令来添加与删除主键
添加主键也采用类似方式，但要保证主键一定在列上，是 NOT NULL。
下面这个范例将在现有表中添加主键，先使列为 NOT NULL，然后再将其作为主键。
1
2
$ mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
$ mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
同样，也可以使用 ALTER 命令删除一个主键。
1
$ mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
如果要删除非主键的索引，则必须指定索引名称。

显示索引信息
使用 SHOW INDEX 命令可以列出表的所有索引。以垂直格式输出（标识为 \G）会比较便于查看，可避免单行内容过长。语法格式如下：
1
$ mysql> SHOW INDEX FROM table_name\G

数据库信息

| 命令               | 描述                          |
| :---------------- | :-----------------------------|
| SELECT VERSION()  | 表明服务器版本的字符串            |
| SELECT DATABASE() | 当前数据库名称（如果没有则为空值）  |
| SELECT USER()     | 当前用户名                      |
| SHOW STATUS       | 服务器状态指示器                 |
| SHOW VARIABLES    | 服务器配置变量                   |

MySQL函数

MySQL Group By 子句 MySQL 的 GROUP BY 语句常与 SUM 这样的 SQL 聚合函数一起使用，可对特定列的结果集采取多种方式进行组合。
MySQL IN 子句一个可以与任何 MySQL 查询组合使用，用来指定条件的子句。
MySQL BETWEEN 子句一个可以与任何 MySQL 查询组合使用，用来指定条件的子句。
MySQL UNION 关键字使用 UNION 操作，将多个结果集合并为一个结果集。
MySQL COUNT 函数用来计算数据库表中的行数的聚合函数。
MySQL MAX 函数能够选择列中最大值的聚合函数。
MySQL MIN 函数能够选择列中最小值的聚合函数。
MySQL AVG 函数选择列的平均值的聚合函数。
MySQL SUM 函数选择数字型列的总数值的聚合函数。
MySQL SQRT 函数计算指定数字的平方根。
MySQL RAND 函数用于生成随机数的函数。
MySQL CONCAT 函数用于连接 MySQL 命令中任意字符串的函数。
MySQL 日期与时间方面的函数与日期和时间相关的一系列 MySQL 函数。
MySQL 数值函数用于处理数值的一系列 MySQL 函数。
MySQL 字符串函数处理字符串的一系列 MySQL 函数。

管理用户

查询所有用户

1	$ mysql> select user,host,plugin,grant_priv,authentication_string from mysql.user;

新建用户
1
2
$ mysql> CREATE USER firstUser IDENTIFIED BY '123';
$ mysql> CREATE USER 'secondUser'@'localhost' IDENTIFIED BY '123';
- firstUser 是用户名，123是密码，记得密码加''（英文单引号）
- 管理员 root 的 host 是 localhost，代表仅限 localhost 本地登录访问。如果要允许开放其他 IP 登录，则需要添加新的 host。如果要允许所有 IP 访问，可以把 localhost 直接修改成 “%”
- 如果报错：
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123' at line 1；
  密码加引号即可。
删除用户
1
2
$ mysql> DROP USER firstUser;
$ mysql> DROP USER 'secondUser'@'localhost';
- 如果使用了 @’localhost’ 创建用户的，后面的删除、授权等都需要加上 @‘localhost’；
- 切换使用新用户前先授权；

用户权限管理

授权

1 2	$ mysql> GRANT ALL PRIVILEGES ON . TO 'secondUser'@'localhost'; $ mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON . TO firstUser;

可以赋予全部权限，也可以挑选基本的增删改查；
第一个*可以填写成数据库,将该数据库赋权限给 firstUser：
1
$ mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON testDB.\* TO firstUser;

查询权限
1
$ mysql> show grants for firstUser;

撤销权限

1	$ mysql> REVOKE ALL PRIVILEGES ON \.\ FROM firstUser;

刷新权限
1
$ mysql> FLUSH PRIVILEGES;
切换用户
1. 退出登录
  1
  $ mysql> exit;
2. 重新登录
  1
  $ mysql -u firstUser -p
  不要写分号，要切换的用户一定要授权过
3. 选择数据库
  1
  $ mysql> use testDB;
4. 查询当前用户
  1
  $ mysql> select user();
5. 查询当前使用数据库
  1
  $ mysql> select database();
6. 用户 firstUser 新建用户授权时无权限
  在创建 firstUser 用户时没有设置 grant_priv 权限，加上：
  1
  $ mysql> CREATE USER firstUser IDENTIFIED BY '123' with grant option;