mysql基本操作

用户

用户的使用
  用于限制其他用户登入权限
  
###查看mysql中的所有用户
###方法一
MariaDB [(none)]> select distinct concat('user:''',user,'''@''',host,''';') as query from mysql.user;
###方法二
#####查看user，host
MariaDB [mysql]> select user,host from user;
####若此步执行错误执行MariaDB [(none)]> use mysql;


###查看某个用户的权限

MariaDB [(none)]> select * from mysql.user where user='root' \G;

###查看当前用户
MariaDB [(none)]> select user();

###创建用户
####创建用户test1 主机地址是localhost 密码000000
MariaDB [(none)]> create user 'test1'@'localhost' identified by '000000';

###修改用户密码
####修改用户密码必须要先进入mysql库下
MariaDB [(none)]> use mysql
MariaDB [mysql]> update user set password=PASSWORD('111111') where user='test1';

###用户授权
###创建test2用户并授可查(select) 可改权限(update)
MariaDB [mysql]> grant select,update on mysql.user to 'test2'@'%';
#####%：host

###给test1用户授可查 可改权限
MariaDB [mysql]> grant select,update on mysql.user to 'test1'@'%';

###给test1 增加一个查看视图的权限，并刷新
MariaDB [mysql]> grant show view  on mysql.user to 'test1'@'%'; 
MariaDB [mysql]> flush privileges;

###查看test1的权限
MariaDB [mysql]> show grants for 'test1'@'%';
+------------------------------------------------------------------+
| Grants for test1@%                                               |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%'                                |
| GRANT SELECT, UPDATE, SHOW VIEW ON `mysql`.`user` TO 'test1'@'%' |
+------------------------------------------------------------------+

###撤销test1可查看权限
MariaDB [mysql]> revoke select on  mysql.user from 'test1'@'%';


###删除test2用户
MariaDB [mysql]> drop user test2@'%';

###给主机127.0.0.1的用户test3分配可对数据库mysql的db表进行select,update操作的权限，并设置口令为000000

MariaDB [mysql]> grant select,update on mysql.db to test3@'127.0.0.1' identified by '000000';  

测试
###登入
[root@localhost ~]# mysql -utest3 -h 127.0.0.1 -p000000
####进入库
MariaDB [(none)]> use mysql;
###查看表
MariaDB [mysql]> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| db              |
+-----------------+


####创建user4赋予对所有对表所有库操作权限，并允许所有主机 
MariaDB [mysql]> grant all  on *.* to user4@'%' identified by '000000'; 

###测试
[root@localhost ~]# mysql -uuser4 -p000000
MariaDB [(none)]> use mysql;
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |

库和表

数据库的基本使用
   1 先创建库
   2 在创建的库中创建表
   3 在表中插入数据

###查看有哪些库
MariaDB [(none)]> show databases;

###创建库名为db1的库
MariaDB [(none)]> create database db1;

###创建库名为db2编码格式为utf8
#####查看字符集
MariaDB [(none)]> show charset;  ###可以查看到有utf8
MariaDB [(none)]> create database db2 charset=utf8;

###修改字符集
MariaDB [(none)]> alter database db2 charset=gbk;

###查看
MariaDB [(none)]> show create database db2;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+


###删除db2库
MariaDB [(none)]> drop database db2;

###进入db1库中
MariaDB [(none)]> use db1;


###创建表
###创建名为stu的表(id int类型  name 字符串类型)
MariaDB [db1]> create table stu(
    -> id int,
    -> name varchar(16)	###长度最长为16
    -> );
#####和上面创建相同
MariaDB [db1]> create table stu( id int, name varchar(16) );

###查看表
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| stu           |
+---------------+

###插入数据
MariaDB [db1]> insert into stu (id,name) values(1,'zhangsan');
MariaDB [db1]> insert into stu (id,name) values(2,'lisi');


###查看全部数据
MariaDB [db1]> select * from stu;
+------+------+
| id   | name |
+------+------+
|    1 | zhangsan   |
|    2 | lisi   |
+------+------+

###只查看id
MariaDB [db1]> select id from stu; 
+------+
| id   |
+------+
|    1 |
|    2 |
+------+


###查看表结构
MariaDB [db1]> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

###修改数据
MariaDB [db1]> update stu set name='wangwu' where id=1;

MariaDB [db1]> select * from stu;                      
+------+--------+
| id   | name   |
+------+--------+
|    1 | wangwu |
|    2 | lisi   |
+------+--------+


###删除指定数据
MariaDB [db1]> delete from stu where id=2;

MariaDB [db1]> select * from stu;         
+------+--------+
| id   | name   |
+------+--------+
|    1 | wangwu |
+------+--------+

###删除表
MariaDB [db1]> drop table stu;

###创建名db5的库并再库中创建stu1和stu2的表，表中记录id，年龄，姓名
#####创建库
MariaDB [(none)]> create database db5;
MariaDB [(none)]> use db5;


###创建表
MariaDB [db5]> create table stu1(
    -> id int,
    -> age int,
    -> name varchar(30)
    -> );
    
MariaDB [db5]> create table stu2(id int,age int,name varchar(30));

MariaDB [db5]> show tables;
+---------------+
| Tables_in_db5 |
+---------------+
| stu1          |
| stu2          |
+---------------+

###向上面创建stu1表中插入数据 id=1001，1002 age=22，23 name = zhangsan，lisi

MariaDB [db5]> insert into stu1(id,age,name) values(1001,22,'zhangsan');

MariaDB [db5]> insert into stu1(id,age,name) values(1002,23,'lisi');

MariaDB [db5]> select * from stu1;
+------+------+----------+
| id   | age  | name     |
+------+------+----------+
| 1001 |   22 | zhangsan |
| 1002 |   23 | lisi     |
+------+------+----------+

####查找id=1001的
MariaDB [db5]> select id,age,name from stu1 where id=1001;
+------+------+----------+
| id   | age  | name     |
+------+------+----------+
| 1001 |   22 | zhangsan |
+------+------+----------+