MySQL数据库（二）：MySQL使用指南

作者：陆金龙发表时间：2023-06-04 05:09

关键词：

1.字符集和排序规则

创建数据库时选项：

字符集：utf8 -- UTF-8 Unicode

排序规则：使用utf8_general_ci而不是utf8_unicode_ci

ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的;

bin 是二进制, a 和 A 会别区别对待.

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。

utf8_general_ci校对速度快，但准确度稍差。

utf8_unicode_ci准确度高，但校对速度稍慢。

如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了。

2.mysql命令group by报错处理

this is incompatible with sql_mode=only_full_group_by

解决方法：命令行输入：

set @@sql_mode='';

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

set @@GLOBAL.sql_mode='';

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

默认关掉ONLY_FULL_GROUP_BY！

这个时候在用工具select 一下 SELECT @@sql_mode;和SELECT @@GLOBAL.sql_mode;不存在ONLY_FULL_GROUP_BY，可以执行相关的sql不会报错了。但是如果你重启Mysql服务的话，发现ONLY_FULL_GROUP_BY还是会存在的。

3.MySQL常用命令

cmd窗口中进行相关操作。

（1）新建用户

登录MYSQL：

　　>mysql -u root -p

　　>密码

创建用户：

mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));

这样就创建了一个名为：test 密码为：1234 的用户。

（2）为用户授权

授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　

登录MYSQL（有ROOT权限），这里以ROOT身份登录：

　　@>mysql -u root -p

　　@>密码

首先为用户创建一个数据库(testDB)：

mysql>create database testDB;

授权test用户拥有testDB数据库的所有权限：

mysql>grant all privileges on testDB.* to test@localhost identified by '1234';

mysql>flush privileges;//刷新系统权限表

如果想指定部分权限给一用户，可以这样来写:

mysql>grant select,update on testDB.* to test@localhost identified by '1234';

mysql>flush privileges; //刷新系统权限表

如果授权test用户拥有所有数据库的某些权限：　

mysql>grant select,delete,update,create,drop on *.* to test@"%" identified by "1234";

@"%" 表示对所有非本地主机授权，不包括localhost。

king用户创建不了数据库，按如下方式,以root用户登录mysql，修改king的权限：

# mysql -u root -p

mysql> GRANT ALL PRIVILEGES ON *.* to 'king'@'%';

mysql> flush privileges;

（3）删除用户

@>mysql -u root -p

@>密码

mysql>Delete FROM user Where User='test' and Host='localhost';

mysql>flush privileges;

删除账户及权限：>drop user 用户名@'%';

　　　　　　　　 >drop user 用户名@ localhost;

（4）修改指定用户密码

@>mysql -u root -p

@>密码

mysql>update mysql.user set password=password('新密码') where User="test" and Host="localhost";

mysql>flush privileges;

（5）操作数据库

列出所有数据库

mysql>show database;

切换数据库

mysql>use '数据库名';

列出所有表

mysql>show tables;

显示数据表结构

mysql>describe 表名;

删除数据库和数据表

mysql>drop database 数据库名;

mysql>drop table 数据表名;

4.解决不能远程登陆问题

可能是帐号不允许从远程登陆，只能在localhost。这个时候只要在MySQl服务所在电脑，登入mysql后，更改 “mysql” 数据库里的 “user” 表里的 “host” 项，从”localhost”改称”%”。

mysql -u root -p vmwaremysql>use mysql;

mysql>update user set host = ‘%’ where user = ‘root’;

mysql>select host, user from user;

@"%" 表示对所有非本地主机授权，不包括localhost。

如果需要同时支持本地和远程连接，则需要保留user表原有记录，添加一条新的记录，host设置为'%'。

5.密码过期问题处理

mysql> use jira

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

密码过期了，修改密码，设置永不过这期。

SET PASSWORD = PASSWORD('pw123456');

ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

flush privileges;

6.MySQL表结构查询脚本

select table_name 表名,column_name 列名,column_type 数据类型,column_comment 注释

from information_schema.columns

where table_schema ='数据库名'

7.更新一个表的字段等于另一个表的字段

有时我们需要把两个关联表中的某个字段同步，即把表Table1中字段A的值赋值到表Table2的字段B中，表1和表2通过字段C关联。SQL语句如下所示（Sql sever 和MySql的写法稍有不同）:

MySQL:

update Table2 inner join Table1 on Table2.C=Table1.C set Table2.B=Table1.A

SQL Sever：

写法1： update Table2 set Table2 .B=Table1.A

from Table2,Table1 where Table2.C=Table1.C

写法2（相关子查询）： update Table2 set B = (SELECT A FROM Table1 WHERE C=Table2.C)

8.避开特殊字符

存数据库中的内容不要包含注释用的--和星号。