MySQLの共通操作と基礎知識

#1. mysql サーバーを起動してシャットダウンします:

service mysql start
service mysql stop

2. MySQL サービスを再起動します:

service mysql restart

3. 起動が成功したかどうかを確認します。mysql ノードは LISTEN 状態になっており、これは起動が成功したことを意味します:

sudo netstat -tap | grep mysql

#4. mysql シェル インターフェイスを入力します:

mysql -u root -p

5. MYSQL に接続します:

形式: mysql -h host address -uユーザー名 -p ユーザーパスワード (注: u と root は必要ありません。スペースを追加してください。他の場合も同様です)

1. このマシン上の MYSQL に接続します

mysql のインストール ディレクトリを見つけます通常、コマンド mysql -uroot -p を直接入力でき、Enter キーを押した後にプロンプ​​トが表示されます。パスワードを入力してください。MYSQL がインストールされたばかりの場合、スーパー ユーザー root にはパスワードがないため、Enter キーを押してください。 MYSQL.

2 を入力します。リモート ホスト上の MYSQL に接続します。

リモート ホストの IP が 10.0.0.1、ユーザー名が root、パスワードが 123 であると仮定します。次に、次のコマンドを入力します:

mysql -h10.0.0.1 -uroot -p123

6. MYSQL コマンドを終了します:

exit （回车）

7. mysql が実行しているプロセスをクエリします:

show processlist;

8. ユーザーの表示:

use mysql；
select * from user;

9. 新しいユーザーの作成:

CREATE USER  'user_name'@'host'  IDENTIFIED BY  'password'；

user_name: 作成するユーザーの名前作成されます。

host: 新しく作成したユーザーがログインできるコンピュータを示します。ローカル マシンからのログインのみを許可する場合は、「localhost」と入力します。リモートからのログインを許可する場合は、「%」を入力します。

password: 新しく作成したユーザーのログインデータベースのパスワード パスワードがない場合は、記述する必要はありません。

例:

CREATE USER  ‘aaa’@‘localhost’ IDENTIFED BY ‘123456’； //表示创建的新用户，名为aaa，这个新用户密码为123456，
只允许本机登陆
CREATE USER  'bbb'@'%' IDENTIFED BY '123456'；//表示新创建的用户，名为bbb，这个用户密码为123456，
可以从其他电脑远程登陆mysql所在服务器
CREATE USER  ‘ccc’@‘%’ ；//表示新创建的用户ccc，没有密码，可以从其他电脑远程登陆mysql服务器

10. 認可されたユーザー:

GRANT privileges ON  databasename.tablename  TO  ‘username’@‘host’

privileges: どのような権限が付与されるかを示します。 、挿入、削除、更新など、すべての権限を付与したい場合は、ALL

databasename.tablename: ユーザーの権限をどのデータベースとどのテーブルで使用できるかを示します。すべてのデータベース、テーブルに適用するアクセス許可を入力します。* . *、* はすべてを示すワイルドカード文字です。

'username'@'host': どのユーザーが許可されているかを示します。

例:

grant all on *.* to 'test'@'localhost'; #给test用户授权，让test用户能给所有库所有表实行所有的权力
GRANT  select，insert  ON  zje.zje  TO ‘aaa’@‘%’；//表示给用户aaa授权，让aaa能给zje库中的zje表 实行 insert 
和 select。

注:

上記のコマンドで認証されたユーザーは、他のユーザーを認証できません。このユーザーに他のユーザーを認証できるようにするには、次の手順を実行する必要があります。 On WITH GRANT OPTION の後に追加します。

例: GRANT ALL ON *.* TO 'aaa'@'%' WITH GRANT OPTION;

関連する推奨事項: 「

mysql チュートリアル#」 ##"

11. IP ログインを制限する:

たとえば、172.29.8.72 と 192.168.3.39 のみが接続できるように mysql を設定します

GRANT ALL ON *.* TO 'username'@'172.29.8.72' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL  ON *.* TO 'username'@'192.168.3.39' IDENTIFIED BY 'password' WITH GRANT OPTION;
flush privileges;

##12. ユーザーの削除:

コマンド: DROP USER 'user_name'@'host'例:

drop user 'test'@'%';  #删除用户test

13.データ テーブル モードの表示:

データベース名を使用; テーブル名の列を表示;

14.mysql データ型:

MySQL は複数のタイプをサポートしています。タイプは、数値タイプ、日付/時刻タイプ、文字列 (文字) タイプの 3 つのカテゴリに大別できます。 MySQL は、すべての標準 SQL 数値データ型をサポートします。 SQL 標準の拡張として、MySQL は整数型 TINYINT、MEDIUMINT、BIGINT

15 もサポートします。データの挿入:

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

データが文字型の場合は、単一引用符または二重引用符 ("value" など) を使用する必要があります。

16. クエリ データ:

   SELECT column_name,column_name
    FROM table_name
    [WHERE Clause]
    [LIMIT N][ OFFSET M]

クエリ ステートメントでは 1 つ以上のテーブルを使用できます。テーブルを区切るにはカンマ (,) を使用し、WHERE ステートメントを使用します。 . クエリ条件を設定します。 SELECT コマンドは 1 つ以上のレコードを読み取ることができます。

アスタリスク (*) を使用して他のフィールドを置き換えることができ、SELECT ステートメントはテーブルのすべてのフィールド データを返します。

WHERE ステートメントを使用して任意の条件を含めることができます。

LIMIT 属性を使用して、返されるレコードの数を設定できます。

OFFSET を使用して、SELECT ステートメントがクエリを開始するデータ オフセットを指定できます。デフォルトでは、オフセットは 0 です。

17. 制限とオフセットの使用法

mysql のページングは​​通常、limit1 で実装されます。記事から選択* LIMIT 1,3

2. select * from Article LIMIT 3 OFFSET 1

上記 2 つの書き方はどちらも 2、3、4 の 3 つのデータを取ることを意味します

limit に従った場合2 つのパラメータにより、最初の数字はスキップする数量を示し、最後の桁は取得する数量を示します。たとえば、記事 LIMIT 1,3 の

select* は 1 つのデータをスキップすることを意味し、 2 番目のデータから取得を開始、3 つのデータを取得、つまり 2、3、4 つのデータを取得

limit の後にパラメータが続く場合、パラメータはデータの数を示しますto be fetched

たとえば、記事 LIMIT 3 の select* は、sqlserver の先頭の構文と同様に、最初の 3 つのデータを直接フェッチすることを意味します。

limit と offset を組み合わせて使用​​する場合、limit の後に指定できるパラメータは 1 つだけで、取得する量を示し、offset はスキップする量を示します。

例えば、記事から * を選択 LIMIT 3 OFFSET 1 は、2 番目のデータから 1 個のデータをスキップし、2、3、4 個のデータを 3 個取得することを意味します

18. データの更新:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

1 つ以上のフィールドを同時に更新できます。

你可以在 WHERE 子句中指定任何条件。

你可以在一个单独表中同时更新数据。

19、模糊查询：

select c field1,field2,…fieldN
from table_name
where field1 like condition1 [and[or]] field2=‘somevalue’;

通配符的分类:

%百分号通配符: 表示任何字符出现任意次数 (可以是0次).

_下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符。

20、排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

可以添加 WHERE…LIKE 子句来设置条件

21、分组

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

例子：

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;

22、null值处理

MySQL 中处理 NULL 使用 IS NULL 、 IS NOT NULL 、运算符。

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。

23、in

in常用于where表达式中，其作用是查询某个范围内的数据。

用法：select * from table where field in (value1,value2,value3,…);

例子：

查询book表中id为2和4的所有数据：

select * from book where id in（2，4）

24、not in

not in与in作用相反，用法和示例如下：

用法：select * from where field not in (value1,value2,value3,…);

25、exists

本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名：

SELECT title
FROM titles
WHERE EXISTS
    (SELECT *
    FROM publishers
    WHERE pub_id = titles.pub_id
    AND city LIKE 'B%')

用IN：

SELECT title
FROM titles
WHERE pub_id IN
    (SELECT pub_id
    FROM publishers
    WHERE city LIKE 'B%')

26、导入数据：

mysql 命令导入

使用 mysql 命令导入语法格式为：

mysql -u用户名    -p密码    <  要导入的数据库数据(runoob.sql)

实例：

# mysql -uroot -p123456 < runoob.sql

以上命令将将备份的整个数据库 runoob.sql 导入。

source 命令导入

source 命令导入数据库需要先登录到数库终端：

mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

使用 mysqlimport 导入数据

mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

$ mysqlimport -u root -p --local mytbl dump.txt
password *****

27、导出数据：

使用SELECT…INTO OUTFILE语句来简单的导出数据到文本文件上。

#将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中: 
mysql> SELECT * FROM runoob_tbl    
    -> INTO OUTFILE '/tmp/runoob.txt';
    
#通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：
    mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';
#生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。
    SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM test_table;

SELECT … INTO OUTFILE 语句有以下属性:

LOAD DATA INFILE是SELECT … INTO

OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT … INTO

OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。

SELECT…INTO OUTFILE

'file_name’形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。

输出不能是一个已存在的文件。防止文件数据被篡改。

你需要有一个登陆服务器的账号来检索文件。否则 SELECT … INTO OUTFILE 不会起任何作用。

导出表作为原始数据

mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。

以下实例将数据表 runoob_tbl 导出到 /tmp 目录中：

$ mysqldump -u root -p --no-create-info 
–tab=/tmp RUNOOB runoob_tbl
password ******

导出 SQL 格式的数据

导出 SQL 格式的数据到指定文件，如下所示：

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

在源主机上执行以下命令，将数据备份至 dump.txt 文件中:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

如果完整备份数据库，则无需使用特定的表名称。

如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：

$ mysql -u root -p database_name < dump.txt
password *****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

$ mysqldump -u root -p database_name 
| mysql -h other-host.com database_name

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

28、 MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

29、字符集设置：

Ubuntu下设置MySQL字符集为utf8

1.mysql配置文件地址

/etc/mysql/my.cnf

2.在[mysqld]在下方添加以下代码

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

3.重启mysql服务

sudo service mysql restart

4.检测字符集是否更新成utf8.

进入mysql，mysql -u root -p,输入show variables like '%character%' 查看字符集

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

注意事项：在修改字符集之前已经建立的数据库，character_set_database值不会发生改变，往数据库中插入中文数据仍然会显示乱码，所以最好在安装完MySQL后就将字符集改成utf8，否则后续修改会较麻烦。

字符集修改：

show character set; #查看当前MySQL服务实例支持的字符集、字符序以及字符集占用的最大字节长度等信息

如下图：

show variables like 'character%';#查看当前MySQL会话使用的字符集

结果如图：

character_set_client: 客户端来源数据使用的字符集

character_set_connection: 数据通信链路的字符集，当MySQL客户机向服务器发送请求时，数据以该字符集进行编码

character_set_database: 数据库字符集

character_set_filesystem: MySQL服务器文件系统的字符集，该值是固定的binary。

character_set_results: 结果集的字符集，MySQL服务器向MySQL客户机返回执行结果时，执行结果以该字符集进行编码

character_set_server: 内部操作字符集(MySQL服务实例字符集) character_set_system: 元数据(字段名、表名、数据库名等)的字符集默认为utf8

修改字符集：

set character_set_results=gbk;

如图：

30、时间同步：

tzselect设置时区命令，根据选项选到中国的北京时间即可。

执行如下命令：

vi .bash_profile

在末尾加入这两行：

TZ='Asia/Shanghai'
export TZ

然后运行命令刷新配置：

source .bash_profile

再次运行date命令发现时区已经成功修改：

hadoop@Master:~$ date -R
Tue, 30 Jul 2019 19:42:41 +0800

linux时区重新设置后，发现mysql插入的数据还是原来时区的时间，下面是重新设置mysql时区的方法：

进入mysql控制台运行如下指令查看mysql时间。

select CURTIME();

查询后发现和date命令查询出来的时间不一样，下面开始修改：

运行临时解决命令：

SET time_zone = '+8:00';

刷新配置：

flush privileges;

修改配置文件使得下次重启mysql服务之后永久生效

vi /etc/my.cnf

添加如下配置：

[mysqld]
default_time_zone = '+8:00'

31、grep命令：

1.作用

Linux系统中grep命令是一种强大的文本搜索工具，它能使用正则表达式搜索文本，并把匹 配的行打印出来。grep全称是Global Regular Expression Print，表示全局正则表达式版本，它的使用权限是所有用户。

2.格式

grep [options]

简单实例：

$ grep ‘test’ d* #显示所有以d开头的文件中包含 test的行。
$ grep ‘test’ aa bb cc  #显示在aa，bb，cc文件中匹配test的行。

32、awk命令：

awk是一个强大的文本分析工具，相对于grep的查找，sed的编辑，awk在其对数据分析并生成报告时，显得尤为强大。简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。

使用方法 ： awk '{pattern + action}' {filenames}

以上がMySQLの共通操作と基礎知識の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。