MySQL数据库基本语句(2)

desc mysql.user;和describe user；作用一样。

mysql> create database auth; //创建数据库名为auth Query OK, 1 row affected (0.00 sec) mysql> use auth; //切换到auth数据库 Database changed mysql> create table test (id int,name varchar(10)); //在auth数据库创建test表（表列的参数自定义） Query OK, 0 rows affected (0.10 sec) mysql> insert into test values (1,'xioazhang'); //向表中插入数据 Query OK, 1 row affected (0.00 sec) mysql> alter table test add comment varchar(100) null; //向表中添加一列 Query OK, 0 rows affected (0.01 sec) mysql> insert into test values (1,'xiaozhang','renshi'); //向表中插入一条数据记录 Query OK, 1 row affected (0.00 sec) mysql> select * from auth.test; //查看表中内容 +------+-----------+---------+ | id | name | comment | +------+-----------+---------+ | 1 | xiaozhang | renshi | +------+-----------+---------+ 1 row in set (0.00 sec) mysql> update auth.test set id=10 -> where name='xiaozhang'; //修改表中数据，小张的ID改为10 Query OK, 1 row affected (0.00 sec) mysql> select * from auth.test; //再次查看表中内容 +------+-----------+---------+ | id | name | comment | +------+-----------+---------+ | 10 | xiaozhang | renshi | +------+-----------+---------+ 1 row in set (0.00 sec) mysql> delete from auth.test -> where id=10; //删除表中ID等于10的用户 Query OK, 1 row affected (0.00 sec) mysql> select * from auth.test; //再次查看表中已没有数据 Empty set (0.00 sec) mysql> drop table auth.test; //删除auth数据库中test表 Query OK, 0 rows affected (0.00 sec) mysql> drop database auth; //删除数据库auth Query OK, 0 rows affected (0.00 sec)

数据库用户授权

GRANT语句：专门用于设置数据库用户的访问权限。当指定用户不存在时，GRANT语句将会自动创建新的用户；如果用户已经存在，则GRANT语句用于修改用户信息。
使用GRANT语句时，需要注意的事项：

mysql> grant all on mysql.* to 'xiaoli'@'%' identified by '123456'; //创建名为xiaoli的数据库用户，密码为123456，并允许其在任何客户端登录访问，对mysql数据库有绝对权限。 Query OK, 0 rows affected (0.00 sec)

其中%表示所有，localhost表示本机。

mysql> show grants for 'xiaoli'@'%'; //查看用户xiaoli在所有客户端的权限 +-------------------------------------------------------------------------------------------------------+ | Grants for xiaoli@% | +-------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'xiaoli'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT ALL PRIVILEGES ON `mysql`.* TO 'xiaoli'@'%' | +-------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)