mysql权限修改的语句_MySQL权限更改示例及SQL语句练习

MySQL各种权限的列表

1.创建MySQL的登录用户

[root@virtualserver@vmware ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 10

Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, 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 databases;

+--------------------+

| Database       |

+--------------------+

| information_schema |

| db_test1        |

| mysql              |

| test               |

+--------------------+

4 rows in set (0.00 sec)

mysql> create user 'mengix'@'localhost' identified by 'mengix';

更换终端2：

[root@virtualserver@vmware ~]# mysql -u mengix -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 13

Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, 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>

2.对登录用户进行授权

对于mengix用户，给test库授予select权限

登入终端一：

mysql> grant select on test.* to 'mengix'@'localhost' identified by 'mengix'

-> with grant option;

Query OK, 0 rows affected (0.00 sec)

登入终端二：

mysql> use test;

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_test |

+----------------+

| tb_1           |

+----------------+

1 row in set (0.00 sec)

mysql> select * from tb_1;

+------+-------+------+

| tno  | tname | tsex |

+------+-------+------+

|    1 | ja    | mal  |

|    2 | lu    | fem  |

+------+-------+------+

2 rows in set (0.00 sec)

mysql> create table tb_2(tno int,tname varchar(10),tsex varchar(6));

ERROR 1142 (42000): CREATE command denied to user 'mengix'@'localhost' for table 'tb_2'

#无CREATE权限

登入终端二：

mysql> show databases;   #此时没有看到root用户创建的库db_test1

+--------------------+

| Database           |

+--------------------+

| information_schema |

| test               |

+--------------------+

2 rows in set (0.00 sec)

登入终端一：

mysql> grant create on db_test1.* to 'mengix'@'localhost' identified by 'mengix'

-> with grant option;

Query OK, 0 rows affected (0.00 sec)

登入终端二：

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| db_test1           |

| test               |

+--------------------+

3 rows in set (0.00 sec)

mysql> use db_test1;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> create table tb_2(no int, name varchar(5), sex varchar(6));

Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_2 values (1,'McGready','male');

ERROR 1142 (42000): INSERT command denied to user 'mengix'@'localhost' for table

'tb_2'

#无INSERT权限

mysql> select * from tb_2;

ERROR 1142 (42000): SELECT command denied to user 'mengix'@'localhost' for table

'tb_2'

#无SELECT权限

SQL语句练习

mysql> alter table tb_2 add nation varchar(30);

mysql> update tb_2 set nation = 'USA' where no = 23;

mysql> insert into tb_2 value (10,'cacy','male','UK');

mysql> select version();select now();select current_date();

+-----------+

| version() |

+-----------+

| 5.1.71    |

+-----------+

1 row in set (0.00 sec)

+---------------------+

| now()               |

+---------------------+

| 2015-06-29 23:36:29 |

+---------------------+

1 row in set (0.00 sec)

+----------------+

| current_date() |

+----------------+

| 2015-06-29     |

+----------------+

1 row in set (0.00 sec)

这里也要注意提示符，在你输入c以后，它切换回到mysql>，提供反馈以表明mysql准备接受一个新命令。

mysql> select user();

+------------------+

| user()           |

+------------------+

| mengix@localhost |

+------------------+

mysql> GRANT ALL ON DbName.* TO 'your_mysql_username'@'your_client_host';

your_client_host要连接的主机，例如localhost

mysql> DESCRIBE pet;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| name    | varchar(20) | YES  |     | NULL    |       |

| owner   | varchar(20) | YES  |     | NULL    |       |

| species | varchar(20) | YES  |     | NULL    |       |

| sex     | char(1)     | YES  |     | NULL    |       |

| birth   | date        | YES  |     | NULL    |       |

| death   | date        | YES  |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

6 rows in set (0.23 sec)

mysql> truncate table pet;

mysql> load data local infile 'pet.txt' into table pet;

Query OK, 1 row affected, 1 warning (0.05 sec)

Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> select * from pet;

+--------+--------+---------+------+------------+-------+

| name   | owner  | species | sex  | birth      | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |

+--------+--------+---------+------+------------+-------+

1 row in set (0.02 sec)

mysql> select name,birth from pet order by birth desc;

+----------+------------+

| name     | birth      |

+----------+------------+

| Puffball | 1999-03-30 |

| Chirpy   | 1998-09-11 |

| Whistler | 1997-12-09 |

| Slim     | 1996-04-29 |

| Claws    | 1994-03-17 |

| Fluffy   | 1993-02-04 |

| Fang     | 1990-08-27 |

| Buffy    | 1989-05-13 |

| Bowser   | 1979-08-31 |

+----------+------------+

mysql> select name,birth,

(year(curdate()) - year(birth)) -(right(curdate(),5)

from pet order by birth desc;

+----------+------------+------+

| name     | birth      | age  |

+----------+------------+------+

| Puffball | 1999-03-30 |   16 |

| Chirpy   | 1998-09-11 |   16 |

| Whistler | 1997-12-09 |   17 |

| Slim     | 1996-04-29 |   19 |

| Claws    | 1994-03-17 |   21 |

| Fluffy   | 1993-02-04 |   22 |

| Fang     | 1990-08-27 |   24 |

| Buffy    | 1989-05-13 |   26 |

| Bowser   | 1979-08-31 |   35 |

+----------+------------+------+

如果当前月份是12月，就有点复杂了。你不能只把1加到月份数(12)上并寻找在13月出生的动物，因为没有这样的月份。相反，你应寻找在1月出生的动物(1月) 。

你甚至可以编写查询，不管当前月份是什么它都能工作。采用这种方法不必在查询中使用一个特定的月份，DATE_ADD( )允许在一个给定的日期上加上时间间隔。如果在NOW( )值上加上一个月，然后用MONTH()提取月份，结果产生生日所在月份：

#当前日期6.30

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE

(),INTERVAL 2 MONTH));

+--------+------------+

| name   | birth      |

+--------+------------+

| Fang   | 1990-08-27 |

| Bowser | 1979-08-31 |

+--------+------------+

2 rows in set (0.00 sec)

完成该任务的另一个方法是加1以得出当前月份的下一个月(在使用取模函数(MOD)后，如果月份当前值是12，则“回滚”到值0：

#当前日期6.30

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = mod(month(curdate()),12)

+2;

+--------+------------+

| name   | birth      |

+--------+------------+

| Fang   | 1990-08-27 |

| Bowser | 1979-08-31 |

+--------+------------+

2 rows in set (0.00 sec)

正则表达式的匹配  like

扩展正则表达式的一些字符是：

‘.’匹配任何单个的字符。

字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

“ * ”匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符。

如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。

为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。

为了找出以“b”开头的名字，使用“^”匹配名字的开始：

mysql> SELECT name FROM pet WHERE name REGEXP '^b';

+--------+

| name   |

+--------+

| Buffy  |

| Bowser |

+--------+

为了找出以“fy”结尾的名字，使用“$”匹配名字的结尾：

mysql> SELECT name FROM pet WHERE name REGEXP 'fy$';

+--------+

| name   |

+--------+

| Fluffy |

| Buffy  |

+--------+

mysql> SELECT name FROM pet WHERE name REGEXP 'w';

+----------+

| name     |

+----------+

| Claws    |

| Bowser   |

| Whistler |

+----------+

为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例

在两者之间：

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

你也可以使用“{n}”“重复n次”操作符重写前面的查询：

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

根据天计算访问量

mysql> SELECT year,month,BIT_COUNT(BIT_OR(1<

year,month;

+------+-------+------+

| year | month | days |

+------+-------+------+

| 2000 |    01 |    3 |

| 2000 |    02 |    2 |

+------+-------+------+

mysql> SELECT * FROM t1;

+------+-------+------+

| year | month | day  |

+------+-------+------+

| 2000 |    01 |   01 |

| 2000 |    01 |   20 |

| 2000 |    01 |   30 |

| 2000 |    02 |   02 |

| 2000 |    02 |   23 |

| 2000 |    02 |   23 |

+------+-------+------+

使用AUTO_INCREMENT

可以通过AUTO_INCREMENT属性为新的行产生唯一的标识：

CREATE TABLE animals (

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (id)

);

INSERT INTO animals (name) VALUES

('dog'),('cat'),('penguin'),

('lax'),('whale'),('ostrich');

mysql> select * from animals;

+----+---------+

| id | name    |

+----+---------+

|  1 | dog     |

|  2 | cat     |

|  3 | penguin |

|  4 | lax     |

|  5 | whale   |

|  6 | ostrich |

+----+---------+