mysql學習二
mysql日常操作
建立db
[root@node1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database testdb;
Query OK, 1 row affected (0.09 sec)
mysql>
mysql>
--建立使用者
mysql> create user 'test' identified by 'test';
Query OK, 0 rows affected (0.31 sec)
mysql>
mysql>
mysql> exit
Bye
登入新使用者
[root@node1 ~]# mysql -u test -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> exit
Bye
-為新使用者賦權
[root@node1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> grant all on testdb.* to test;
Query OK, 0 rows affected (0.04 sec)
mysql>
[root@node1 ~]# cd /var/lib/mysql
[root@node1 mysql]# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql.sock private_key.pem server-key.pem
ca-key.pem client-key.pem ib_logfile0 mysql mysql.sock.lock public_key.pem sys
ca.pem ib_buffer_pool ib_logfile1 mysqld_safe.pid performance_schema server-cert.pem testdb
[root@node1 mysql]# cd testdb
[root@node1 testdb]# ls
db.opt
[root@node1 testdb]#
登入新使用者建立表
[root@node1 testdb]# mysql -u test -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql>
mysql> create table test1 (id number);
ERROR 1046 (3D000):
mysql>
mysql>
mysql> select database ();
+-------------+
| database () |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)
mysql> use testdb
Database changed
mysql>
mysql>
mysql> create table test1 (id int(11));
Query OK, 0 rows affected (0.50 sec)
mysql>
mysql>
mysql>
mysql>
mysql> create table test2 (id int(11));
Query OK, 0 rows affected (0.02 sec)
mysql>
[root@node1 mysql]# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql.sock private_key.pem server-key.pem
ca-key.pem client-key.pem ib_logfile0 mysql mysql.sock.lock public_key.pem sys
ca.pem ib_buffer_pool ib_logfile1 mysqld_safe.pid performance_schema server-cert.pem testdb
[root@node1 mysql]# cd testdb
[root@node1 testdb]# ls
在testdb目錄下有兩張表的資訊，.frm檔案是表的結構等資訊，.ibd是資料，每張表都有這兩個檔案
[root@node1 testdb]# ls -trl
total 228
-rw-r----- 1 mysql mysql 65 Feb 21 23:08 db.opt
-rw-r----- 1 mysql mysql 8556 Feb 21 23:28 test1.frm
-rw-r----- 1 mysql mysql 98304 Feb 21 23:28 test1.ibd
-rw-r----- 1 mysql mysql 8556 Feb 21 23:30 test2.frm
-rw-r----- 1 mysql mysql 98304 Feb 21 23:30 test2.ibd
驗證mysql事務屬性
[root@node1 testdb]#
mysql> insert into test2 values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values(5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test2 values(6);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
---另外一個會話
[root@node1 testdb]# mysql -u test -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
ERROR 1046 (3D000):
mysql> show tables
-> ;
ERROR 1046 (3D000):
mysql>
mysql>
mysql>
mysql>
mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables
-> ;
+------------------+
| Tables_in_testdb |
+------------------+
| test1 |
| test2 |
+------------------+
2 rows in set (0.01 sec)
mysql>
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
+------+
3 rows in set (0.00 sec)
在預設模式下，MYSQL是autocommit模式的，所有的資料庫更新操作都會即時提交，所以在預設情況下，mysql是不支援事務的。
但是如果你的MYSQL表型別是使用InnoDB Tables 或 BDB tables的話，你的MYSQL就可以使用事務處理,使用SET AUTOCOMMIT=0就可以使MYSQL允許在非autocommit模式，
在非autocommit模式下，你必須使用COMMIT來提交你的更改，或者用ROLLBACK來回滾你的更改
設定autocommit=0
mysql> set autocommit=0;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test2 values(7);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
| 7 |
+------+
4 rows in set (0.00 sec)
---提交之前另外一個會話無法看到新插入的資料
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
+------+
3 rows in set (0.04 sec)
--提交之後
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql>
---另外一個會話可以看到新資料
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
| 7 |
+------+
mysql建立索引
mysql> create index test1_id_ind on test1(id);
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql>
[root@node1 testdb]# ls -trl
total 244
-rw-r----- 1 mysql mysql 65 Feb 21 23:08 db.opt
-rw-r----- 1 mysql mysql 8556 Feb 21 23:30 test2.frm
-rw-r----- 1 mysql mysql 98304 Feb 21 23:35 test2.ibd
-rw-r----- 1 mysql mysql 8556 Feb 21 23:48 test1.frm
-rw-r----- 1 mysql mysql 114688 Feb 21 23:48 test1.ibd
[root@node1 testdb]#
test1的檔案沒有增加，所以索引和表的資料是放在一個資料檔案裡的
建立db
[root@node1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database testdb;
Query OK, 1 row affected (0.09 sec)
mysql>
mysql>
--建立使用者
mysql> create user 'test' identified by 'test';
Query OK, 0 rows affected (0.31 sec)
mysql>
mysql>
mysql> exit
Bye
登入新使用者
[root@node1 ~]# mysql -u test -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> exit
Bye
-為新使用者賦權
[root@node1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> grant all on testdb.* to test;
Query OK, 0 rows affected (0.04 sec)
mysql>
[root@node1 ~]# cd /var/lib/mysql
[root@node1 mysql]# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql.sock private_key.pem server-key.pem
ca-key.pem client-key.pem ib_logfile0 mysql mysql.sock.lock public_key.pem sys
ca.pem ib_buffer_pool ib_logfile1 mysqld_safe.pid performance_schema server-cert.pem testdb
[root@node1 mysql]# cd testdb
[root@node1 testdb]# ls
db.opt
[root@node1 testdb]#
登入新使用者建立表
[root@node1 testdb]# mysql -u test -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql>
mysql> create table test1 (id number);
ERROR 1046 (3D000):
mysql>
mysql>
mysql> select database ();
+-------------+
| database () |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)
mysql> use testdb
Database changed
mysql>
mysql>
mysql> create table test1 (id int(11));
Query OK, 0 rows affected (0.50 sec)
mysql>
mysql>
mysql>
mysql>
mysql> create table test2 (id int(11));
Query OK, 0 rows affected (0.02 sec)
mysql>
[root@node1 mysql]# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql.sock private_key.pem server-key.pem
ca-key.pem client-key.pem ib_logfile0 mysql mysql.sock.lock public_key.pem sys
ca.pem ib_buffer_pool ib_logfile1 mysqld_safe.pid performance_schema server-cert.pem testdb
[root@node1 mysql]# cd testdb
[root@node1 testdb]# ls
在testdb目錄下有兩張表的資訊，.frm檔案是表的結構等資訊，.ibd是資料，每張表都有這兩個檔案
[root@node1 testdb]# ls -trl
total 228
-rw-r----- 1 mysql mysql 65 Feb 21 23:08 db.opt
-rw-r----- 1 mysql mysql 8556 Feb 21 23:28 test1.frm
-rw-r----- 1 mysql mysql 98304 Feb 21 23:28 test1.ibd
-rw-r----- 1 mysql mysql 8556 Feb 21 23:30 test2.frm
-rw-r----- 1 mysql mysql 98304 Feb 21 23:30 test2.ibd
驗證mysql事務屬性
[root@node1 testdb]#
mysql> insert into test2 values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values(5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test2 values(6);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
---另外一個會話
[root@node1 testdb]# mysql -u test -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
ERROR 1046 (3D000):
mysql> show tables
-> ;
ERROR 1046 (3D000):
mysql>
mysql>
mysql>
mysql>
mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables
-> ;
+------------------+
| Tables_in_testdb |
+------------------+
| test1 |
| test2 |
+------------------+
2 rows in set (0.01 sec)
mysql>
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
+------+
3 rows in set (0.00 sec)
在預設模式下，MYSQL是autocommit模式的，所有的資料庫更新操作都會即時提交，所以在預設情況下，mysql是不支援事務的。
但是如果你的MYSQL表型別是使用InnoDB Tables 或 BDB tables的話，你的MYSQL就可以使用事務處理,使用SET AUTOCOMMIT=0就可以使MYSQL允許在非autocommit模式，
在非autocommit模式下，你必須使用COMMIT來提交你的更改，或者用ROLLBACK來回滾你的更改
設定autocommit=0
mysql> set autocommit=0;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test2 values(7);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
| 7 |
+------+
4 rows in set (0.00 sec)
---提交之前另外一個會話無法看到新插入的資料
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
+------+
3 rows in set (0.04 sec)
--提交之後
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql>
---另外一個會話可以看到新資料
mysql> select * from test2;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
| 7 |
+------+
mysql建立索引
mysql> create index test1_id_ind on test1(id);
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql>
[root@node1 testdb]# ls -trl
total 244
-rw-r----- 1 mysql mysql 65 Feb 21 23:08 db.opt
-rw-r----- 1 mysql mysql 8556 Feb 21 23:30 test2.frm
-rw-r----- 1 mysql mysql 98304 Feb 21 23:35 test2.ibd
-rw-r----- 1 mysql mysql 8556 Feb 21 23:48 test1.frm
-rw-r----- 1 mysql mysql 114688 Feb 21 23:48 test1.ibd
[root@node1 testdb]#
test1的檔案沒有增加，所以索引和表的資料是放在一個資料檔案裡的
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29108064/viewspace-1992694/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql學習（二）MySql
- MYSQL學習（二） --MYSQL框架MySql框架
- MySql 學習筆記二：索引MySql筆記索引
- MySQL高階學習筆記(二)MySql筆記
- 【Mysql 學習】MyISAM儲存引擎(二)。MySql儲存引擎
- MySQL 學習筆記（二）MVCC 機制MySql筆記MVC
- Mysql學習積累之二[網摘收藏個人學習參考]MySql
- Mysql基礎學習第二天MySql
- MYSQL練習二MySql
- MySQL事務學習筆記(二) 相識篇MySql筆記
- 【Mysql 學習】 MERGE表方面的問題(二)MySql
- 學習MySQLMySql
- MySQL 學習MySql
- Mysql學習MySql
- Golang 學習——interface 介面學習（二）Golang
- Go學習【二】學習資料Go
- hdfs學習（二）
- TypeScript 學習（二）TypeScript
- docker學習（二）Docker
- MyBatis學習(二)MyBatis
- Kaldi學習（二）
- 學習webpack（二）Web
- CSS學習（二）CSS
- Bootstrap學習(二)boot
- RMAN 學習（二）
- Drools學習(二)
- DWR學習(二)
- MySQL學習 - 索引MySql索引
- MySQL深度學習MySql深度學習
- Mysql學習教程MySql
- mysql學習（一）MySql
- 【Mysql 學習】字串MySql字串
- Mysql 5.7 Gtid內部學習(十) 實際案例(二)MySql
- MySQL學習（二）圖形介面管理工具Navicat for MySQL安裝和使用MySql
- 【Mysql 學習】Mysql 日誌(一)MySql
- 【Mysql 學習】mysql 字符集MySql
- 【Mysql 學習】Mysql 儲存引擎MySql儲存引擎
- 函式學習二函式