mysql筆記 &lt;一&gt;

一、mysql基本管理

1.啟動mysql

/etc/init.d/mysqld是一個shell啟動腳本，啟動後最終會調用mysqld_safe腳本，最後調用mysqld服務啟動mysql,如下:

/etc/init.d/mysqld start 或/usr/local/mysql/bin/mysqld_safe &

root 26221 1 0 08:55 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid

mysql 27071 26221 0 08:55 ? 00:02:26 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/mysql.pid --socket=/tmp/mysql.sock --port=3306

2.關閉mysql:

mysqladmin -uroot shutdown -p123 平滑關閉服務

/etc/init.d/mysqld stop 實際是調用上面的指令

Kill –USR2 `cat path/pid` 直接殺死pid來關閉服務，不推薦

3.連接配接資料庫

mysql -uroot -p123 -D test -S /var/lib/mysql/mysql.sock -h 127.0.0.1 -P 3306

選項：

-u 指定使用者

-p 指定使用者密碼

-h 指定伺服器位址

-P 指定端口号

-S 指定SOCK路徑

-D 指定資料庫名，直接連接配接到資料庫

-C 資料傳輸壓縮

-e 非互動式執行SQL語句

4.資料庫互動式指令

\? 幫助資訊

\c 清除目前輸入語句

\r 重新連接配接到伺服器，可選資料庫和主機

\d 設定語句分隔符。

\e 使用$ EDITOR編輯指令。

\G 發送指令到mysql server，垂直顯示結果。

\g 發送指令到mysql伺服器。

\h 顯示此幫助。

\n 禁用尋呼機，列印到stdout。

\t 不要寫入outfile。

\P 設定PAGER [to_pager]。通過PAGER列印查詢結果。

\p 列印目前指令。

\R 更改您的mysql提示符。

\q 退出mysql。

\# 重建完成哈希。

\. 執行SQL腳本檔案。擷取檔案名作為參數。

\s 從伺服器擷取狀态資訊。

\！執行系統shell指令。

\T 設定outfile [to_outfile]。将所有内容附加到給定的檔案中。

\u 使用另一個資料庫。将資料庫名稱作為參數。

\C 切換到另一個字元集。可能需要處理具有多位元組字元集的binlog。

\W 每個聲明後顯示警告。

\w 每個語句後都不要顯示警告。

\x 清除會話上下文。

二、mysql資料庫變量管理

mysql中變量可分為系統變量與狀态變量，系統變量配置了mysql的運作環境屬性，狀态變量顯示了msyql運作過程中的狀态資訊，而系統變量從作用域劃分，又可以分為全局變量和會話變量。

系統變量：

全局級别的變量：global variables，作用域為整個msyql伺服器

會話級别的變量：session variables,作用域為目前會話。

狀态變量：

狀态變量也分為全局級别與會話級别，但是使用者無法設定狀态變量，隻能檢視。

1.全局系統變量查詢

全局系統變量查詢：

show global variables \G;

檢視字元集相關的全部設定：

show global variables like 'character%';

檢視日志配置是否開啟或關閉：

show global variables where variable_name like '%log%' and value='off';

2.會話系統變量查詢

show session variables;

目前會話字元集查詢

show session variables like 'character%';

注意：有些變量隻存在于全局級别中，而有些變量隻存在于會話級别中，如果不指明global或session，則表示檢視會話級别的變量值。

3.使用select檢視單個全局系統變量或會話系統變量

select @@global.character_set_server; #檢視系統字元集變量

select @@session.wait_timeout; #檢視目前會話逾時

如不指定global或session，@@var_name表示優先從會話級别擷取變量值。

4.改變變量的值

mysql> select @@session.wait_timeout;

+------------------------+

| @@session.wait_timeout |

| 28800 |

1 row in set (0.00 sec)

mysql> set @@session.wait_timeout=14400;

Query OK, 0 rows affected (0.00 sec)

| 14400 |

set global var_name=value;

set @@global.var_name=value;

set session var_name=value;

set @@session.var_name=value;

不指定級别表示預設設定會話級别的變量

狀态變量對應使用者來說是隻讀的，是以隻改變系統變量的值。

在修改系統變量的值時并非永久生效的，重新開機後将失效。

運作時修改global級别的變量，對修改之前建立的會話沒有影響，僅對修改後建立的會話有效。

運作時修改session級别的變量，僅對目前會話有效，且立即生效。

如果要使設定永久生效，需要設定配置檔案。

三、使用者帳戶管理

1.檢視目前庫中的使用者

select user,host,authentication_string from mysql.user;

+------------+-----------+-------------------------------------------+

| user | host | authentication_string |

| root | localhost | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |

注意：從5.7以後密碼字段改為authentication_string

host：表示使用者能夠通過哪些用戶端主機IP登入到目前伺服器上的mysql服務，host可以為主機名，也可以為IP位址，但是mysql認為主機名和Ip位址屬于不同的主機；

可以使用通配符：

_ 表示任意單個字元

% 表示任意長度的任意字元

2.建立使用者

1)使用create user指令建立使用者

mysql> create user 'linux'@'localhost';

Query OK, 0 rows affected (0.01 sec)

mysql> create user 'linuxse'@'%' identified by '123';

2)使用grant授權使用者時自動建立使用者

grant all on *.* to 'zzz'@'localhost' identified d by '123';

3.删除使用者

注意使用delete删除使用者時，不會删除使用者授權，是以下次再建立相同的使用者時就會報錯，可以使用drop删除使用者授權，重新建立。

mysql> delete from mysql.user where user='zzz' and host='localhost';

Query OK, 1 row affected (0.00 sec)

mysql> create user 'zzz'@'localhost';

ERROR 1396 (HY000): Operation CREATE USER failed for 'zzz'@'localhost'

mysql> drop user zzz@localhost;

4.重命名使用者

rename user OldName to NewName; 指定舊和新使用者名

mysql> select user,host from mysql.user;

+------------+-----------+

| user | host |

| linuxse | % |

| mysql.sys | localhost |

| root | localhost |

8 rows in set (0.00 sec)

mysql> rename user linuxse to aaaa;

| aaaa | % |

5.修改使用者密碼

1)系統指令中修改密碼

mysqladmin -uzhang -p123 password 123

如果沒有密碼就不用指定密碼

mysqladmin -uzhang password 123

2)登入到mysql修改密碼

set password for 'zhang'@'%'=password('123');

flush privileges;

3)修改表字段修改密碼，注意5.7以後密碼字段為authentication_string

mysql> update mysql.user set authentication_string=password('456') where user='zhang' and host='%';

Query OK, 1 row affected, 1 warning (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges;

4)忘記密碼修改，重置

停止資料庫

/etc/init.d/mysqld stop

繞過使用者驗證啟動mysql

/usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

登入mysql:

mysql -uroot

修改使用者密碼：

mysql> update mysql.user set authentication_string=password('456') where user='root' and host='localhost';

Query OK, 1 row affected, 1 warning (0.01 sec)

重新整理後退出：

mysql>\q

重新正常啟動

mysqladmin-uroot -p123 shutdown

ps-ef|grep -v grep|grep mysql

/etc/init.d/mysqldstart

四、mysql授權管理

Create: 建立新的資料庫或資料表。

Delete: 删除表的記錄。

Drop: 删除資料表或資料庫。

INDEX: 建立或删除索引。

Insert: 增加表的記錄。

Select: 顯示/搜尋表的記錄。

Update: 修改表中已存在的記錄。

全局管理MySQL使用者權限：

file: 在MySQL伺服器上讀寫檔案。

PROCESS: 顯示或殺死屬于其它使用者的服務線程。

RELOAD: 重載通路控制表，重新整理日志等。

SHUTDOWN: 關閉MySQL服務。

特别的權限：

ALL: 允許做任何事(和root一樣)。

USAGE: 隻允許登入--其它什麼也不允許做。

1、使用者權限

授權文法：

grant all [privileges] on db.table to 'username'@'host' identified by 'password'

授權zhang使用者對所有庫所有表所有權限，并設定密碼，允許在任何地方登入

mysql> grant all privileges on *.* to 'zhang'@'%' identified by '123.com';

Query OK, 0 rows affected, 1 warning (0.01 sec)

授權zzz使用者對zabbix庫中的所有表有insert,delete,update,select權限：

mysql> grant insert,delete,update,select on zabbix.* to zzz@'localhost';

授權使用者遠端登入時，必須使用SSL建立會話：

mysql> grant usage on *.* to 'xin'@'%' require ssl;

撤銷強制使用SSL建立會話的限制：

mysql> grant usage on *.* to 'xin'@'%' require none;

使用with grant option使授權使用者也擁有授予其他使用者同樣的權限：

授權使用者xin有select權限，同時它可以授權别的使用者同樣的權限。

grant select on *.* to 'xin'@'%' with grant optiontion;

其他限制：

MAX_QUERIES_PER_HOUR：限制使用者每個小時執行的查詢語句次數

MAX_UPDATES_PER_HOUR：限制使用者每小時執行的更新語句次數

MAX_CONNECTIONS_PER_HOUR：限制使用者每小時連接配接資料庫的次數

MAX_USER_CONNECTIONS:限制使用者使用目前帳号同時連接配接伺服器的連接配接數

grant select on *.* to 'xin'@'%' with max_queries_per_hour 20;

grant select on *.* to 'xin'@'%' with max_updates_per_hour 10;

grant select on *.* to 'xin'@'%' with max_connections_per_hour 15;

grant select on *.* to 'xin'@'%' with max_user_connections 2;

上面的資料改為0表示不限制。

2.檢視授權

show grants for xin@'%';

從資料庫中檢視使用者授權：

文法：select * from mysql.db where Db="資料庫名"

select * from mysql.db where Db='mysql'\G

3.删除授權

文法：revoke 權限 on 資料庫.表 from 使用者@host;

删除使用者建立視圖權限：

revoke create view on *.* from zhang@'%';

五、庫管理語句

1.建立資料庫

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

在其他關系型資料庫中，database與schema是有差別的，但在mysql 5.0.2以後，我們建立的資料庫時可以不加區分的使用database和schema

if not exists表示在對應的資料庫不存在的時候才會建立，它是可選的。

create_specitification表示我們可以在建立資料庫時指定對應的資料庫規範：

可以指定資料庫的字元集，使用character set對應的字元集名稱，可以指定資料庫字元集，如果使用了default關鍵字，那麼這個資料庫中建立的所有表預設都繼承這個資料庫的字元集，可以使用"show character set"指令檢視所有可用的字元集，同時我們還可以指定建立資料庫時指定資料庫的排序規則，排序規則是針對字元集的，每個字元集都有自己預設的排序規則，使用"show collation"指令可以檢視所有可用的排序方式，以及排序對應的字元集，default為可選指令，與指定字元集作用相同。

建立testdb資料庫：

mysql> create database testdb;

如果testdb資料庫不存在，則建立：

mysql> create database if not exists testdb;

如果testdb資料庫不存在，則建立，并指定其預設字元集為utf8：

mysql> create database if not exists testdb default character set utf8;

建立testdb資料庫，指定字元集為utf8并指定排序方式：

create database if not exists testdb default character set utf8 collate utf8_general_ci;

2.查詢資料庫

檢視所有存在的資料庫：

show databases;

檢視資料庫語句結構：

show create database testdb;

檢視可用的字元集：

show character set;

檢視字元集排序方式：

show collation;

檢視目前資料庫與目前連接配接的概要資訊：

root@mysql 11:50:50>use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

遇到上面的提示資訊，表示mysql要預讀資料庫資訊，如果資料過大會導緻連接配接變慢，可以在連接配接資料時指定-A連接配接關閉此功能，也可以在配置檔案my.cnf的用戶端中添加字段：no-auto-rehash重新加載，關閉預讀功能。

Database changed

root@mysql 11:56:20>status

--------------

mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

Connection id: 1315 #目前連接配接的ID号

Current database: mysql #目前選中使用的資料庫

Current user: root@localhost #目前連接配接的登入使用者

SSL: Not in use #是否使用了ssl

Current pager: stdout #

Using outfile: ''

Using delimiter: ; #目前會話行終結符

Server version: 5.7.16-log Source distribution #版本号

Protocol version: 10 #協定版本

Connection: Localhost via UNIX socket #使用的連接配接類型，通過本機的套接字檔案進行連接配接

Server characterset: utf8 #伺服器使用的字元集

Db characterset: utf8 #資料庫使用的字元集

Client characterset: utf8 #用戶端使用的字元集

Conn. characterset: utf8 #目前連接配接使用的字元集

UNIX socket: /tmp/mysql.sock #套接字檔案路徑

Uptime: 23 hours 26 min 5 sec #資料庫的啟動時長

Threads: 17 Questions: 54562 Slow queries: 0 Opens: 1920 Flush tables: 1 Open tables: 99 Queries per second avg: 0.646

3.修改資料庫

ALTER {DATABASE | SCHEMA} [db_name]

alter_specification ...

ALTER {DATABASE | SCHEMA} db_name

UPGRADE DATA DIRECTORY NAME

alter_specification:

檢視字元集：

show variables like 'character%';

修改資料庫字元集：

alter database testdb character set gbk;

alter database testdb default character set gbk;

4.删除資料庫

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

删除testdb資料庫：

drop database if exists testdb;

六、表管理語句

1.檢視表

use mysql

show tables;

檢視表的具體屬性資訊：

show table status\G

指定檢視某張表的屬性：

show table status like 'user'\G

檢視表結構語句：

desc table_name

desc user\G

檢視表的建立語句：

show create table user\G

2.建立表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_options]

[partition_options]

auto_increment設定字段為自增長，此字段必須被定義為key或者索引。預設隻設定字段主鍵為自增

primary key(col1[,col2,...])用于定義主鍵，一個表中隻能有一個主鍵，一個主鍵可以包含多個字段。

unique key(col1[col2,...])用于定義唯一鍵，一個表中可以有多個唯一鍵。

foreign key用于定義外鍵

chech(expr)用于定義檢查性限制

index ind_name(id)|key ind_name(id)可以使用兩種方法建立索引

fulltext|spatial如果資料庫存儲引擎為myisam可以建立全文索引和空間索引，innodb引擎不支援全文索引和空間索引。

執行個體：

建立表：

建立資料庫指定字元集和排序方式：

root@mysql 01:20:53>create database test default character set utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.01 sec)

root@mysql 01:22:47>use test;

建立test表指定字段id和name的類型分别為int和varchare

root@mysql 01:23:12>create table test1(

-> id int(11),

-> name varchar(58)

-> );

Query OK, 0 rows affected (0.02 sec)

在目前字段設定主鍵，并設定字段不能為空，添加注釋字段：

root@mysql 01:31:20>create table test2(

-> id int(11) key,

-> name varchar(58) not null comment 'student name'

在表級别定義主鍵：

root@mysql 01:32:28>create table test3(

-> name varchar(34) not null comment 'sutdent name',

-> primary key(id)

建立聯合主鍵：

root@mysql 01:34:28>create table test4(id int(10),name varchar(34) not null,primary key(id,name));

建立索引，可以使用index ind_name()和key ind_name()兩種方法建立索引：

root@mysql 01:36:24>create table test5(id int(11) auto_increment,name varchar(58) not null,primary key(id),index ind_name(name));

Query OK, 0 rows affected (0.03 sec)

root@mysql 01:41:39>create table test6(id int(11) auto_increment,name varchar(58) not null,primary key(id),key ind_name(name));

root@mysql 01:43:44>create table test7(id int(11) auto_increment,name varchar(58) not null,primary key(id),index(name));

複制表資料：

還可以根據查詢的資料建立一個新表，将資料填充到建立的表中，需要注意字段中的限制無法複制過去：

root@mysql 01:49:31>desc test7;

+-------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

2 rows in set (0.00 sec)

root@mysql 01:49:38>select * from test7;

+----+-------+

| id | name |

| 4 | haha |

| 3 | hehe |

| 1 | zhang |

| 2 | zhang |

4 rows in set (0.00 sec)

root@mysql 01:49:56>create table test8 select id,name from test7;

Query OK, 4 rows affected (0.03 sec)

Records: 4 Duplicates: 0 Warnings: 0

root@mysql 01:51:26>desc test8;

+-------+-------------+------+-----+---------+-------+

| id | int(11) | NO | | 0 | |

root@mysql 01:51:39>select * from test8;

複制表結構：

使用like來将test5的表結構包括字段限制和索引都複制到建立的新表中test9：

root@mysql 01:57:19>desc test5;

root@mysql 01:57:26>create table test9 like test5;

root@mysql 01:57:53>desc test9;

使用engine選項還指定資料引擎，還可以指定自增長和字元集等：

root@mysql 01:58:04>create table test10(id int(11) primary key auto_increment,name varchar(34) not null,index(name))engine=InnoDB auto_increment=2 default charset=utf8;

其他資訊可檢視幫助資訊：

root@mysql 02:06:41>help create table;

3.删除表

drop table table_name;

drop table if exists table_name1,table_name2;

4.修改表

重命名表名：

alter table test1 rename sa test2;

添加字段：

alter table test add column age int(11);

修改表的字段，可以省略column：

alter table test add age int(11);

為表添加字段，同時為添加字段設定限制：

alter table test add age int not null default 0;

alter table test column age int not null default 0;

為表添加字段，同時指定添加字段在表中的位置，下面為添加字段為第一個字段：

alter table test add id int first;

指定添加字段的位置為name字段的後面：

alter table test add column age int after name;

删除字段

alter table test drop name;

重命名字段，修改字段名稱

alter table test change name name1 varchar(48);

修改字段類型

alter table test change age age char(10);

alter table test modify age int;

本文轉自 80後小菜鳥 51CTO部落格，原文連結:http://blog.51cto.com/zhangxinqi/1927980

mysql筆記 <一>

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

mysql筆記 &lt;一&gt;

繼續閱讀

mysql筆記 <一>