修改mysql的内存的命令 修改mysql语句

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