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的文件没有增加，所以索引和表的数据是放在一个数据文件里的