Mysql学习总结_20190313

--------查看数据文件路径

mysql> show variables like '%datadir%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| datadir       | /u01/mysql/data/ |
+---------------+------------------+
1 row in set (0.00 sec)

--------创建数据库 

mysql> create database test;
Query OK, 1 row affected (0.12 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

--------创建表 

mysql> use test;
Database changed

mysql> create table emp1(id int(11),name varchar(25),deptid int(11),salary float);
Query OK, 0 rows affected (0.24 sec)

int(11) int(10) int(3) 只是显示上的区别 不影响数据的存储 int存储为4字节23位

-------user表
用户列：host，user，authentication_string

权限列：

资源控制列：

安全列：

------给指定ip的指定用户赋权

如果没有用户的话会自动创建用户，如果user表的某一user对应的host同时存在'%'和'ip'的话 先以'ip'的为准：

mysql> grant all privileges on *.* to 'testuser'@'192.169.10.1' identified by '121212' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

 ------收回权限

mysql> revoke all privileges on *.* from 'root'@'192.169.10.1';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

 -----mysql修改用户密码的方法

1.set password方法修改

mysql> set password for root@192.169.10.1 = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

 2.mysqladmin方法修改(mysqladmin的前提是保证有权限用现有的用户名和密码登陆)

[root@rhel6 ~]# mysqladmin -uroot -p1111 password Cqmyg14dss
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

[root@rhel6 ~]# mysqladmin -uroots -p111112  password 111111
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'roots'@'localhost' (using password: YES)'

3.update user表直接修改

mysql> use mysql;

Database changed

mysql> update user set authentication_string=password('121212') where user='roots' ;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. |
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)