每天进步一点点之Mysql内建函数汇总

mysql数据库中提供了很丰富的函数。mysql函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数,可以简化用户的操作。

 

简单介绍几类函数的使用范围:

数学函数:这类函数只要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数、获取随机数函数等。

字符串函数:这类函数主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母变成小写或大写字母的函数、获取子串的函数等。

日期和时间函数:这类函数主要用于处理日期和时间。其中包括取当前时间的函数、获取当前日期的函数、返回年份的函数、返回日期的函数等。

流程函数:这类函数主要用于在SQL语句中控制条件选择。其中包括IF语句、CASE语句、WHEN语句等。

系统信息函数:这类函数主要用于获取mysql数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数、获取数据库版本的函数等。

加密函数:这类函数主要用于对字符串进行加密解密。其中包括字符串加密函数、字符串解密函数等。

其他函数:包括格式化函数、锁函数等。

 

 

1.数学函数

函数 作用
ABS(x) 返回x的绝对值
CEIL(x),CEILING(x) 返回大于或等于x的最小整数(向上取整)
FLOOR(x) 返回小于或等于x的最大整数(向下取整)
RAND() 返回0~1的随机数
RAND(x) 返回0~1的随机数,x值相同时返回的随机数相同
SIGN(x) 返回x的符号,x是负数、0、正数分别返回-1、0、1
PI() 返回圆周率
TRUNCATE(x,y) 返回数值x保留到小数点后y位的值
ROUND(x) 返回离x最近的整数(四舍五入)
ROUND(x,y) 保留x小数点后y位的值,但截断时要四舍五入
POW(x,y),POWER(x,y) 返回x的y次方
SQRT(x) 返回x的平方根
EXP(x) 返回e的x次方
MOD(x,y) 返回x 除以y以后的余数
LOG(x) 返回自然对数(以e为底的对数)
LOG10(x) 返回以10为底的对数
RADIANS(x) 讲角度转换为弧度
DEGREES(x) 讲弧度转换为角度
SIN(x) 求正弦值
ASIN(x) 求反正弦值
COS(x) 求余弦值
ACOS(x) 求反余弦值
TAN(x) 求正切值
ATAN(x),ATAN(x,y) 求反正切值
COT(x) 求余切值

 

2.字符串函数

函数 作用
CHAR_LENGTH(s) 返回字符串s的字符数
LENGTH(s) 返回字符串s的长度
CONCAT(s1,s2,…..) 将字符串s1,s2等多个字符串合并为一个字符串
CONCAT_WS(x,s1,s2,….) 同COUCAT(s1,s2,…..),但是每个字符串之间要加上x
INSERT(s1,x,len,s2) 将字符串s2替换s1的x位置开始长度为len的字符串
UPPER(s),UCASE(s) 讲字符串s的所有字符都变成大写字母
LOWER(s),LCASE(s) 讲字符串s的所有字符都变成小写字母
LEFT(s,n) 返回字符串s的前n个字符
RIGHT(s,n) 返回字符串s的后n个字符
LPAD(s1,len,s2) 字符串s2来填充s1的开始处,使字符串长度达到len
RPAD(s1,len,s2) 字符串s2来填充s1的结尾处,使字符串长度达到len
LTRIM(s) 去掉字符串s开始处的空格
RTRIM(s) 去掉字符串s结尾处的空格
TRIM(s) 去掉字符串s开始处和结尾处的空格
TRIM(s1 FROM s) 去掉字符串s中开始处和结尾处的字符串s1
REPEAT(s,n) 将字符串s重复n次
SPACE(n) 返回n个空格
REPLACE(s,s1,s2) 用字符串s2代替字符串s中的字符串s1
STRCMP(s1,s2) 比较字符串s1和s2
SUBSTRING(s,n,len) 获取从字符串s中的第n个位置开始长度为len的字符串
MID(s,n,len) 同SUBSTRING(s,n,len)
ATE(s1,s),POSTTION(s1  IN s) 从字符串s中获取s1的开始位置
INSTR(s,s1) 从字符串s中获取s1的开始位置
REVERSE(s) 将字符串s的顺序反过来
ELT(n,s1,s2…) 返回第n个字符串
FIELD(s,s1,s2…) 返回第一个与字符串s匹配的字符串的位置
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置
MAKE_SET(x,s1,s2…) 按x的二进制数从s1,s2……sn中选取字符串

 

3.日期和时间函数

函数 作用
CURDATE(),CURRENT_DATE() 返回当前日期
CURTIME(),CURRENT_TIME() 返回当前时间
NOW(),CURRENT_TIMESTAMP()

LOCALTIME(),SYSDATE()

LOCALTIMESTAMP()

返回当前日期和时间
UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(d) 将时间d以UNIX时间戳的形式返回
FROM_UNIXTIME(d) 把UNIX时间戳的时间转换为普通格式的时间
UTC_DATE() 返回UTC(国际协调时间)日期
UTC_TIME() 返回UTC时间
MONTH(d) 返回日期d中的月份值,范围是1~12
MONTHNAME(d) 返回日期d中的月份名称,如january
DAYNAME(d) 返回日期d是星期几,如Monday
DAYOFWEEK(d) 返回日期d是星期几,1表示星期日,2表示星期2
WEEKDAY(d) 返回日期d是星期几,0表示星期一,1表示星期2
WEEK(d) 计算日期d是本年的第几个星期,范围是0-53
WEEKOFYEAR(d) 计算日期d是本年的第几个星期,范围是1-53
DAYOFYEAR(d) 计算日期d是本年的第几天
DAYOFMONTH(d) 计算日期d是本月的第几天
YEAR(d) 返回日期d中的年份值
QUARTER(d) 返回日期d是第几季度,范围1-4
HOUR(t) 返回时间t中的小时值
MINUTE(t) 返回时间t中的分钟值
SECOND(t) 返回时间t中的秒钟值
EXTRACT(type FROM d) 从日期d中获取指定的值,type指定返回的值,如YEAR,HOUR等
TIME_TO_SEC(t) 将时间t转换为秒
SEC_TO_TIME(s) 将以秒为单位的时间s转换为时分秒的格式
TO_DAYS(d) 计算日期d到0000年1月1日的天数
FROM_DAYS(n) 计算从0000年1月1日开始n天后的日期
DATEDIFF(d1,d2) 计算日期d1到d2之间相隔的天数
ADDDATE(d,n) 计算开始日期d加上n天的日期
ADDDATE(d, INTERVAL  expr type) 计算起始日期d加上一个时间段后的日期
SUBDATE(d,n)                           计算起始日期d减去n天的日期
SUBDATE(d, INTERVAL  expr type) 计算起始日期d减去一个时间段后的日期
ADDTIME(t,n) 计算起始时间t加上n秒的时间
SUBTIME(t,n) 计算起始时间t减去n秒的时间
DATE_FORMAT(d,f) 按照表达式f的要求显示日期d
TIME_FORMAT(t,f) 按照表达式f的要求显示时间t
GET_FORMAT(type,s) 根据字符串s获取type类型数据的显示格式

 

 

4.条件判断函数

条件判断函数用来在SQL语句中进行条件判断。更加是否满足判断条件,SQL语句执行不同的分支。

4.1 IF(expr,v1,v2)函数

IF(expr,v1,v2)函数中,如果表达式expr成立,返回结果v1,否则,返回结果v2。

mysql> selectname,if(price>2,‘OK‘,‘ON‘) from food;

+————–+———————–+

| name         | if(price>2,‘OK‘,‘ON‘) |

+————–+———————–+

| 光磊饼干     | OK                    |

| 宪政牛奶     | OK                    |

| 兴周果冻     | ON                    |

| GG咖啡       | OK                   |

| XX奶糖       | OK                    |

+————–+———————–+

5 rows in set (0.00sec)

 

4.2CASE函数

CASEWHEN expr1 THEN v1 [WHEN expr2 THEN v2…][ELSE vn] END

mysql> selectname,case when price =1.5 then ‘OK‘ when price >3.5 then ‘on my god‘ endfrom food;

+————–+———————————————————————+

| name         | case when price =1.5 then ‘OK‘ whenprice >3.5 then ‘on my god‘ end |

+————–+———————————————————————+

| 光磊饼干     | NULL                                                               |

| 宪政牛奶     | NULL                                                               |

| 兴周果冻     | OK                                                                  |

| GG咖啡       | on my god                                                          |

| XX奶糖       | on my god                                                          |

+————–+———————————————————————+

5 rows in set (0.00sec)

 

 

5.系统信息函数

系统信息函数用来查询mysql数据库的系统信息。例如查询数据库的版本,查询数据的当前用户等。

函数 作用
VERSION() 返回数据库的版本号
CONNECTION_ID() 返回服务器的连接数,也就是到现在为止mysql服务的连接次数
DATABASE(),SCHEMA() 返回当前数据库名
USER() 返回当前用户的名称
CHARSET(str) 返回字符串str的字符集
COLLATION(str) 返回字符串str的字符排列方式
LAST_INSERT_ID()                          返回最后生成的auto_increment值

 

6.加密解密函数

加密函数是mysql中用来对数据进行加密的函数。

函数 作用
PASSWORD(str) 对字符串str进行加密
MD5(str) 对字符串str进行加密
ENCODE(str,pswd_str) 使用字符串pswd_str来加密字符串str,加密结果是一个二进制数,必须使用BLOB类型来保持它
DECODE(crypt_str,pswd_str) 解密函数,使用字符串pswd_str来为crypt_str解密

 

7.其他函数

函数 作用
FORMAT(x,n) 格式化函数,可以讲数字x进行格式化,将x保留到小数点后n位,这个过程需要进行四舍五入。
ASCII(s) 返回字符串s的第一个字符的ASSCII码
BIN(x) 返回x的二进制编码
HEX(x) 返回x的十六进制编码
OCT(x) 返回x的八进制编码
CONV(x,f1,f2) 将x从f1进制数变成f2进制数
INET_ATON(IP) 将IP地址转换为数字表示,IP值需要加上引号
INET_NTOA(n) 可以将数字n转换成IP的形式
GET_LOCT(name,time) 加锁函数,定义一个名称为name、持续时间长度为time秒的锁,如果锁定成功,返回1,如果尝试超时,返回0,如果遇到错误,返回NULL.
RELEASE_LOCK(name) 解除名称为name的锁,如果解锁成功,返回1,如果尝试超时,返回0,如果解锁失败,返回NULL。
IS_FREE_LOCK(name) 判断是否使用名为name的锁,如果使用,返回0,否则返回1.
CONVERT(s USING cs) 将字符串s的字符集变成cs
CAST(x AS type),CONVERT(x,type) 这两个函数将x变成type类型,这两个函数只对BINARY,CHAR,DATE,DATETIME,TIME,SIGNED  INTEGER,UNSIGNED INTEGER这些类型起作用,但这两种方法只是改变了输出值得数据类型,并没有改变表中字段的类型。

 

每天进步一点点之Mysql基础教程

Mysql技术总结

1. 用户管理

a) 用户登录

格式: mysql -h主机地址 -u用户名 -p用户密码

例:mysql -h110.110.110.110 -uroot -p123

本地可以直接mysql –uroot -p

b) 用户退出

exit、quit

c) 添加用户

1)insert into

mysql.user表保存的是用户的登录信息。因此可以直接对其进行插入数据的操作,这样可以实现添加用户

insert into mysql.user (host,user,password) values(‘%’,’jifei’,PASSWORD(‘jifei’)); 最新版本这个会出现问题。

注:对于上面的命令首先是password字段已经改名为authentication_string。

其次改正之后还是会报

mysql> insert into mysql.user (host,user,authentication_string) values('%','jifei',PASSWORD('jifei')); 
  ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

2)grant

grant select on 数据库.* to ‘用户名’@’登录主机’ identified by ‘密码’ WITH GRANT OPTION;

注意:上述的数据库名和数据表名不需要引号。

GRANT命令说明:
ALL PRIVILEGES 是表示所有权限,你也可以使用select、update等权限。

ON 用来指定权限针对哪些库和表。

*.* 中前面的*号用来指定数据库名,后面的*号用来指定表名。

TO 表示将权限赋予某个用户。

jack@’localhost’ 表示jack用户,@后面接限制的主机,可以是IP、IP段、域名以及%,%表示任何地方。注意:这里%有的版本不包括本地,以前碰到过给某个用户设置了%允许任何地方登录,但是在本地登录不了,这个和版本有关系,遇到这个问题再加一个localhost的用户就可以了。

IDENTIFIED BY 指定用户的登录密码。

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意:经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

备注:可以使用GRANT重复给用户添加权限,权限叠加,比如你先给用户添加一个select权限,然后又给用户添加一个insert权限,那么该用户就同时拥有了select和insert权限。

mysql> grant select,insert on test_db.test_table to 'test_user'@'localhost' identified by '123456' with grant option;
  Query OK, 0 rows affected, 1 warning (0.00 sec)

d) 用户权限

1)添加权限

grant select on 数据库.* to ‘用户名’@’登录主机’ identified by ‘密码’ WITH GRANT OPTION;

这个命令跟创建用户的命令是一模一样的。

权限列表:

权限 权限级别 权限说明
CREATE 数据库、表或索引 创建数据库、表或索引权限
DROP 数据库或表 删除数据库或表权限
GRANT OPTION 数据库、表或保存的程序 赋予权限选项
REFERENCES 数据库或表
ALTER 更改表,比如添加字段、索引等
DELETE 删除数据权限
INDEX 索引权限
INSERT 插入权限
SELECT 查询权限
UPDATE 更新权限
CREATE VIEW 视图 创建视图权限
SHOW VIEW 视图 查看视图权限
ALTER ROUTINE 存储过程 更改存储过程权限
CREATE ROUTINE 存储过程 创建存储过程权限
EXECUTE 存储过程 执行存储过程权限
FILE 服务器主机上的文件访问 文件访问权限
CREATE TEMPORARY TABLES 服务器管理 创建临时表权限
LOCK TABLES 服务器管理 锁表权限
CREATE USER 服务器管理 创建用户权限
PROCESS 服务器管理 查看进程权限
RELOAD  

 

服务器管理

执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT 服务器管理 复制权限
REPLICATION SLAVE 服务器管理 复制权限
SHOW DATABASES 服务器管理 查看数据库权限
SHUTDOWN 服务器管理 关闭数据库权限
SUPER 服务器管理 执行kill线程权限

2)撤销权限

revoke 权限 on 数据库.表 from ‘用户名’@’登录主机’;   //将to改为from

例:revoke all on *.* from ‘jifei’@’%’;

mysql> revoke insert on test_db.test_table from 'test_user'@'localhost';
  Query OK, 0 rows affected (0.00 sec)

3)查看权限

show grants; //自己

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

show grants for dba@localhost; //指定用户指定host

mysql> show grants for 'test_user'@'localhost';
+-------------------------------------------------------------------------------------+
| Grants for test_user@localhost                                                      |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'localhost'                                       |
| GRANT SELECT ON `test_db`.`test_table` TO 'test_user'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

e) 删除用户

(方法一)drop user 用户名;

语法:drop user 用户名;

作用:删除已经存在的用户,例如要删除yan这个用户,(drop user yan;)默认删除的是yan@”%”这个用户,如果还有其他用户,例如yan@”localhost”,yan@”ip”,则不会一起被删除。如果只存在一个用户yan@”localhost”,使用语句(drop user yan;)会报错,应该用(drop user yan@”localhost”;)如果不能确定(用户名@机器名)中的机器名,可以在mysql中的user表中进行查找,user列对应的是用户名,host列对应的是机器名。

mysql> drop user test_user;
  ERROR 1396 (HY000): Operation DROP USER failed for 'test_user'@'%'
mysql> drop user test_user@'localhost';
  Query OK, 0 rows affected (0.00 sec)

(方法二)delete from user where user=”用户名” and host=”localhost”;(不鼓励用这个方法)

delete也是删除用户的方法,例如要删除yan@”localhost”用户,则可以(delete from user where user=”yan” and host=”localhost”;)

注:drop删除掉的用户不仅将user表中的数据删除,还会删除诸如db和其他权限表的内容。而(方法二)只是删除了user表的内容,其他表不会被删除,后期如果命名一个和已删除用户相同的名字,权限就会被继承。

f) 修改密码

1) mysqladmin命令

格式如下(其中,USER为用户名,PASSWORD为新密码):

mysqladmin -u USER -p password PASSWORD  (建议不要把密码写到命令中)

该命令之后会提示输入原密码,输入正确后即可修改。

mysqladmin -utest_user -p password 
  Enter password: 
  New password: 
  Confirm new password: 
  Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

2)UPDATE user 语句

这种方式必须是先用root帐户登入mysql,然后执行:

UPDATE user SET password=PASSWORD(‘新密码’) WHERE user=’账号名’;   (注意新版的password字段改名为authentication_string)
FLUSH PRIVILEGES;

mysql> update user set authentication_string=PASSWORD('123456') where user='test_user';
  ERROR 1046 (3D000): No database selected

mysql> use mysql   //首先要选择数据库,否则会报错
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> update user set authentication_string=PASSWORD('123456') where user='test_user';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3) SET PASSWORD语句

这种方式也需要先用root命令登入mysql,然后执行:

SET PASSWORD FOR 用户名=PASSWORD(‘新密码’);

mysql> set password for test_user=password('654321');
  ERROR 1133 (42000): Can't find any matching row in the user table

mysql> set password for test_user@'localhost'=password('654321');
  Query OK, 0 rows affected, 1 warning (0.00 sec)

上述产生的原因是:我并没有用户test_user@‘%’,所以这个时候改不了test_user的密码。因此要用下边的语句修改密码。

4)忘记root密码

i) 首先确认服务器出于安全的状态,也就是没有人能够任意地连接MySQL数据库。

因为在重新设置MySQL的root密码的期间,MySQL数据库完全出于没有密码保护的状态下,其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对外的端口封闭,并且停止Apache以及所有的用户进程的方法实现服务器的准安全状态。最安全的状态是到服务器的Console上面操作,并且拔掉网线。

ii) 修改MySQL的登录设置:

# vi /etc/my.cnf

在[mysqld]的段中加上一句:skip-grant-tables

例如:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip-grant-tables

保存并且退出vi。

iii) 重新启动mysqld

# /etc/init.d/mysqld restart

Stopping MySQL: [ OK ]

Starting MySQL: [ OK ]

iv) 登录并修改MySQL的root密码
# /usr/bin/mysql 
  Welcome to the MySQL monitor. Commands end with ; or \g. 
  Your MySQL connection id is 3 to server version: 3.23.56 
  Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
  mysql> USE mysql ; 
    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> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ; 
    Query OK, 0 rows affected (0.00 sec) 
    Rows matched: 2 Changed: 0 Warnings: 0 
  mysql> flush privileges ; 
    Query OK, 0 rows affected (0.01 sec) 
  mysql> quit 
    Bye
v) 将MySQL的登录设置修改回来

# vi /etc/my.cnf

将刚才在[mysqld]的段中加上的skip-grant-tables删除

保存并且退出vi。

vi) 重新启动mysqld

# /etc/init.d/mysqld restart

Stopping MySQL: [ OK ]

Starting MySQL: [ OK ]

g) 其他一些权限操作

1)刷新权限

flush privileges;

2)对账户重命名

rename user ‘oldusername’@’oldhost’ to ‘newusername’@’newhost’

mysql> rename user 'test_user'@'localhost' to 'test_user1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

3) 查看当前用户

select user();

mysql> select user();
+----------------------+
| user()               |
+----------------------+
| test_user1@localhost |
+----------------------+
1 row in set (0.00 sec)

2. 数据库管理

a) 创建数据库

方法原型:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name    [create_specification]

create_specification:    [DEFAULT] CHARACTER SET [=] charset_name  | [DEFAULT] COLLATE [=] collation_name

注意:在mysql中DATABASE和SCHEMA是一个东西,但是在其他的关系型数据库中就不一定。

例:CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

mysql> create database if not exists test_db1 charset utf8;
Query OK, 1 row affected (0.00 sec)


mysql> create database if not exists test_db1 character set utf8;
Query OK, 1 row affected (0.00 sec)

注意:CHARACTER跟charset 是一个东西

b) 删除数据库(慎用)

方法一:

方法原型:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

例: drop database xhkdb;

mysql> drop database if exists test_db1;
Query OK, 0 rows affected (0.00 sec)

方法二:

使用普通用户登陆mysql服务器,你可能需要特定的权限来创建或者删除 MySQL 数据库。

所以我们这边使用root用户登录,root用户拥有最高权限,可以使用 mysql mysqladmin 命令来创建数据库。

在删除数据库过程中,务必要十分谨慎,因为在执行删除命令后,所有数据将会消失。

以下实例删除数据库TUTORIALS(该数据库在前一章节已创建):

[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******
执行以上删除数据库命令后,会出现一个提示框,来确认是否真的删除数据库:

1

2

3

4

5

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

 

Do you really want to drop the ‘TUTORIALS’ database [y/N] y

Database “TUTORIALS” dropped

c) 得到数据库

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_db            |
+--------------------+
2 rows in set (0.00 sec)

d) 选择数据库

use databasename;

mysql> use test_db
  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

3. 数据表管理

a) 创建表

 

b) 删除表

drop table命令用于删除数据表。

drop table命令格式:

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

例如,删除表名为 MyClass 的表:
mysql> drop table MyClass;

mysql> drop table if exists test_table;
Query OK, 0 rows affected (0.14 sec)

DROP TABLE用于删除一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消,所以使用本语句要小心!

注意:对于一个带分区的表,DROP TABLE会永久性地取消表定义,取消各分区,并取消储存在这些分区中的所有数据。DROP TABLE还会取消与被取消的表有关联的分区定义(.par)文件。

对于不存在的表,使用IF EXISTS用于防止错误发生。当使用IF EXISTS时,对于每个不存在的表,会生成一个NOTE(警告)。

RESTRICT和CASCADE可以使分区更容易。目前,RESTRICT和CASCADE不起作用。

c) 清空表

方法一:truncate

TRUNCATE [TABLE] tbl_name

方法二:delete

delete from 表名;

注:

不带where参数的delete语句可以删除mysql表中所有内容,使用truncate table也可以清空mysql表中所有内容。效率上truncatedelete快,但truncate删除后不记录mysql日志,不可以恢复数据。

delete的效果有点像将mysql表中所有记录一条一条删除到删完,而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表。

d) 修改表结构

 

f) 得到表结构

mysql> desc MyClass;
mysql> show columns from MyClass;

mysql> desc test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> show columns from test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

4. 数据操作(增删改查)

a) 插入数据

 

b) 删除数据

 

c) 更新数据

 

d) 查询数据

 

5. 数据库备份

 

每天进步一点点之Redis使用详细教程【转】

一、Redis基础部分:

1、redis介绍与安装比mysql快10倍以上

*****************redis适用场合****************

1.取最新N个数据的操作

2.排行榜应用,取TOP N 操作

3.需要精确设定过期时间的应用

4.计数器应用

5.Uniq操作,获取某段时间所有数据排重值

6.实时系统,反垃圾系统7.Pub/Sub构建实时消息系统

7.Pub/Sub构建实时消息系统8.构建队列系统

9.缓存

=============================================

SET操作每秒钟 110000 次,GET操作每秒钟 81000 次,服务器配置如下:

Linux 2.6, Xeon X3320 2.5Ghz.

stackoverflow 网站使用 Redis 做为缓存服务器。

同时也会将数据写到硬盘上。所以数据是安全的(除突然断电外,重启服务会写到dump.rdb文件中)

1.安装:

tar zxvf redis-2.6.9.tar.gz

cd redis-2.6.9

make

cd src && make install

2.移动配置文件位置(为了便于管理)

cd /usr/local/

mkdir -p /usr/local/redis/bin

mkdir -p /usr/local/redis/etc

mv /lamp/redis-2.6.9/redis.conf /usr/local/redis/etc

cd /lamp/redis-2.6.9/src

mv mkreleasehdr.sh redis-benchmark redis-check-aof redis-check-dump redis-cli redis-server /usr/local/redis/bin

3.修改配置文件

vi /usr/local/redis/etc/redis.conf

一、将daemonize no 中no改为yes[yes指后台运行]

4.启动/随机启动:

cd /usr/local/redis/bin

./redis-server /usr/local/redis/etc/redis.conf#启动redis并指定配置文件。

#vi /etc/rc.local #设置随机启动。

/usr/local/redis/bin/redis-server /usr/local/redis/etc/redis.conf

5.查看是否启动成功

ps -ef | grep redis

netstat -tunpl | grep 6379#查看端口是否占用。

6.进入客户端/退出

cd /usr/local/redis/bin

./redis-cli#进入

quit#退出

7.关闭redis

pkill redis-server#关闭

./redis-cli shutdown#关闭

************************************Redis安全************************************

Redis的安全性???(由以下4种方式)

1.用ACL控制器安全性。

2.在redis.conf配置文件增加下面这一行配置,即可把redis绑定在单个接口上(但并不是只有接受这个网卡的数据)。

bind 127.0.0.1

3.给redis加上较长密码(无需要记住)

4.在redis.conf配置启用认证功能。

5.SSL代理

6.禁用指定命令。

************************************** Redis配置 **********************************************

daemonize 如果需要在后台运行,把该项改为yes

pidfile 配置多个pid的地址 默认在/var/run/redis.pid

bind 绑定ip,设置后只接受来自该ip的请求

port 监听端口,默认为6379

timeout 设置客户端连接时的超时时间,单位为秒

loglevel 分为4级,debug、verbose、notice、warning

logfile 配置log文件地址

databases 设置数据库的个数,默认使用的数据库为0

save 设置redis进行数据库镜像的频率

rdbcompression 在进行镜像备份时,是否进行压缩

Dbfilename 镜像备份文件的文件名

Dir 数据库镜像备份的文件放置路径

Slaveof 设置数据库为其他数据库的从数据库

Masterauth 主数据库连接需要的密码验证

Requirepass 设置登录时需要使用的密码

Maxclients 限制同时连接的客户数量

Maxmemory 设置redis能够使用的最大内存

Appendonly 开启append only模式

以下了解即可:

Appendfsync 设置对appendonly.aof文件同步的频率

vm-enabled 是否开启虚拟内存支持

vm-swap-file 设置虚拟内存的交换文件路径

vm-max-memory 设置redis使用的最大物理内存大小

vm-page-size 设置虚拟内存的页大小

vm-pages 设置交换文件的总的page数量

vm-max-threads 设置VM IO同时使用的线程数量

Glueoutputbuf 把小的输出缓存存放在一起

hash-max-zipmap-entries 设置hash的临界值

Activerehashing 重新hash

*******************************************************************

5种数据类型:字符串、哈希、链表、集合、有序集合。

支持:push/pop、add/remove 、取交集、并集、差集、排序。

redis<===同步====>mysql

同时也会将数据写到硬盘上。所以数据是安全的(除突然断电外,重启服务会写到dump.rdb文件中)

*******************************************************************

select num#选择库,默认在0库,共16个库

auth liweijie#授权用户所需密码(密码就是redis.conf中配置的密码)

flushdb#清空数据库。

String(字符串)类型:

set name lijie#设置键name的值为lijie

get name#获取name的值。

keys *#查询所有的键。

setnx name liweijie#如果键已存在则返回0,不更新,防止覆盖。

setex haircolor 10 red #设置键的值的有效期为10秒。

setrange email 6 lampbre.com#替换键的值从第6个字符开始换为lampbre.com

mset name1 李大伟 name2 李小伟#设置多个键的值。

msetnxname1 张三 name3 李四#判断键是否存在,不存在则设置,否则不设置返回0

mget name1 name2 name3#一次获取多个键的值。

getset name1 Tom#重新设置键的值,并返回旧的键值。

getrange email 6 18#截取email键的值,从第6-18位间的字符。

incr uid#每次自增1 (如果key中uid不存在,则设置并从0开始,下同)

incrby uid 5#每次自增5

incrby uid -5#每次自减5

decr uid #每次自减1

decrby uid 5#每次自减5

appendname1 @126.com#给name1的值,添加字符串@126.com

strlenname1#返回键name1的值的长度。

*************************************************************************

Hashes(哈希)类型:

hset user:001 name liweijie#哈希设置用户user:001的name键值为liweijie

hset user:001 age 21#同样,增加一个age键值为21

hsetnx user:001 age 22#同上,但检测键是否存在。若不存在创建。

hmset user:002 name liweijie2 age 26 sex 1#同时设置多个键的值。

hget user:001 name#哈希获取用户user:001的name键的值。

hget user:001 age #同上。

hmget user:001 name age sex#获取多个指定的键的值。

hgetall user:001#获取所有键的值。

hincrbyuser:001 age -8#在指定键上加上给定的值。

hexists user:001 sex#检测指定的键值是否存在。

hlen user:001#返回指定哈希的键个数/字段个数。

hdel user:001 sex#删除指定(user:001)哈希的指定字段或是键值。

hkeys user:003#返回哈希里所有字段或是键值。

*********************************************************************

Lists(链表)类型及操作(棧或队列):

lpush mylist “world”#从头部插入字符串

lpush mylist “hello”#同上

lrange mylist 0 -1#获取从0到最后一个如[1) “hello” 2) “world”]

rpush mylist “jiejie”#在尾部插入

linsert mylist before “hello” “this is linsert” #指定插入位置(在hello之前插入)。

lset mylist 0 “what”#设置修改指定下标的值。

lrem mylist 1 “hello”#删除(1个)一个值为hello的元素。(n<0从尾部删除,n=0全部删除)

ltrim mylist 1 2 #保留表中下标为1/2的元素。

lpop mylist#弹出开头元素并返回。

rpop mylist#弹出尾部元素并返回。

rpoplpush mylist mylist2 #从mylist尾部弹出插入到mylist2的头部。

lindex mylist 0#获取表下标为0的元素值。

llen mylist#返回表元素个数(相当于count($arr ))。

*********************************************************************

sets(集合)类型及操作(好友推荐、blog、tag功能):

smembers myset#查看myset集合中所有元素值。

sadd myset “hello”#向mysets集合中添加一个值hello

srem myset “hello”#删除myset集合中名称为hello的元素。

spop myset #随机弹出并返回mysets中的一个元素。

sdiff myset2 myset3#返回myset2中的与myset3的差集(以myset2为准)。

sdiffstore myset4 myset2 myset3#返回myset2中的与myset3的差集,并存入myset4中去。

sinter myset2 myset3#返回myset2与myset3的交集。

sinterstore myset5 myset2 myset3#返回myset2与myset3的交集,并存入myset5中去。

sunion myset2 myset3#求并集(去重复)

sunionstore myset6 myset2 myset3#求并集,并存入myset6中去。

smove myset2 myset3 “three”#将myset2中的three移到myset3中去。

scard myset2#返回元素个数。

sismember myset2 “one”#判断元素one是不是myset2集合的(相当于is_array())。

srandmember myset2#随机返回myset2集合中的一个元素,但不删除(相当于array_rand())。

*********************************************************************

sorted sets(有序集合)类型及操作(以scores排序):

zadd myzset 1 “one”#向顺序1的添加元素one

zadd myzset 2 “two”#同上。

zadd myzset 3 “two”#相当于更新顺序为2的值

zrange myzset 0 -1 withscores#查看所有元素并带上排序(默认升序)。

zrem myzset “two”#删除two

zincrby myzset 2 “two”#将two的顺序值加上2

zrank myzset “two”#返回集合中元素的索引下标值。

zrevrank myzset two#元素反转并返回新下标值。

zrevrange myzset 0 -1 withscores#按顺序反转(相当于降序排序)

zrangebyscore myzset 1 10 withscores#返回顺序为1-10的元素(可做分页)。

zcount myzset 1 10 #返回顺序在1-10之间元素的个数。

zcard myzset#返回集合中所有元素的个数。

zremrangebyrank myzset 1 2#删除集合中下标为1到2的元素。

zremrangebyscore myzset 1 10#删除集合中顺序为1到10的元素。

Redis常用命令

键/值相关命令。

keys * #查询所有

keys user*#查询指定的

exists user:001#判断是否存在。

del name#删除指定的键。

expire addr 10#设置过期时间

ttl addr#查询过期时间

select 0 #选择数据库

move age 1#将age移到1数据库。

get age #获取

persist age#移除age的过期时间。

randomkey#随机返回一个key

rename name1 name2#重命名键

type myset#返回键的类型。

ping #测试redis连接是否存活。

echo lamp#输出一个lamp

select 10#选择数据库。

quit/exit/crtl+C#退出客户端

dbsize#返回库里的键的个数。

服务器相关命令:

info#显示redis服务器的相关信息。

config get */loglevel #返回所有/指定的配置信息。

flushdb#删除当前库中的所有键/表。

flushall#删除所有数据库中的所有键/表

二、Redis高级部分:

1、Redis安全性:

1.用ACL控制器安全性。

2.给redis加上较长密码

# requirepass foobared

requirepass beijing

3.在redis.conf配置启用认证功能。

方式一:Auth beijing

方式二:./redis-cli -a beijing

4.在redis.conf配置文件增加下面这一行配置,即可把redis绑定在单个接口上(但并不是只有接受这个网卡的数据)。

bind 127.0.0.1(单台机器的时候可以配置,分布式或主从复制时最好不要配置)

5.SSL代理

6.禁用指定命令。

2、Redis主从复制:

redis只需在从服务器(slave)上配置即可:

slaveof 211.122.11.11 6379 #指定master 的ip 和端口

masterauth beijing#这是master主机的密码

Info#查看主/从服务器的状态。

3、Redis事务处理:

Redis事务很不完善。

4、Redis持久化机制:

1.两种方式:一、备份数据到磁盘(快照)[ snapshotting(快照)也是默认方式]

二、记录操作命令[ Append-only file(缩写aof)的方式]

一、备份数据到磁盘(快照)[ snapshotting(快照)也是默认方式]

save 900 1 #900秒内如果超过1个key被修改,则发起快照保存

save 300 10 #300秒内容如超过10个key被修改,则发起快照保存

save 60 10000

二、记录操作命令[ Append-only file(缩写aof)的方式](较安全持久化)

appendonly yes #启用aof 持久化方式

# appendfsync always //收到写命令就立即写入磁盘,最慢,但是保证完全的持久化

appendfsync everysec //每秒钟写入磁盘一次,在性能和持久化方面做了很好的折中

# appendfsync no //完全依赖os,性能最好,持久化没保证

每天进步一点点之Mysql数据简明使用教程【转】

1、启动MySQL服务器
实际上上篇已讲到如何启动MySQL。两种方法:
一是用winmysqladmin,如果机器启动时已自动运行,则可直接进入下一步操作。
二是在DOS方式下运行
d:/mysql/bin/mysqld

2、进入mysql交互操作界面
在DOS方式下,运行:
d:/mysql/bin/mysql -u root -p
出现提示符,此时已进入mysql的交互操作方式。
如果出现 “ERROR 2003: Can‘t connect to MySQL server on ‘localhost‘ (10061)“,说明你的MySQL还没有启动。

3、退出MySQL操作界面
在mysql>提示符下输入quit可以随时退出交互操作界面:
mysql> quit
Bye
你也可以用control-D退出。

4、第一条命令
mysql> select version(),current_date();
+—————-+—————–+
| version()   | current_date() |
+—————-+—————–+
| 3.23.25a-debug | 2001-05-17   |
+—————-+—————–+
1 row in set (0.01 sec)

此命令要求mysql服务器告诉你它的版本号和当前日期。尝试用不同大小写操作上述命令,看结果如何。
结果说明mysql命令的大小写结果是一致的。
练习如下操作:
mysql>Select (20+5)*4;
mysql>Select (20+5)*4,sin(pi()/3);
mysql>Select (20+5)*4 AS Result,sin(pi()/3); (AS: 指定假名为Result)

5、多行语句
一条命令可以分成多行输入,直到出现分号“;”为止:
mysql> select
-> USER()
-> ,
-> now()
->;
+——————–+—————————–+
| USER()               | now()                           |
+——————–+—————————–+
| ODBC@localhost| 2001-05-17 22:59:15  |
+——————–+—————————–+

6、使用SHOW语句找出在服务器上当前存在什么数据库:

mysql> SHOW DATABASES;
+———-+
| Database |
+———-+
| mysql  |
| test   |
+———-+
3 rows in set (0.00 sec)

7、创建一个数据库abccs
mysql> CREATE DATABASE abccs;
注意不同操作系统对大小写的敏感。

8、选择你所创建的数据库
mysql> USE abccs
Database changed
此时你已经进入你刚才所建立的数据库abccs.

9 创建一个数据库表
首先看现在你的数据库中存在什么表:
mysql> SHOW TABLES;
Empty set (0.00 sec)
说明刚才建立的数据库中还没有数据库表。下面来创建一个数据库表mytable:

我们要建立一个你公司员工的生日表,表的内容包含员工姓名、性别、出生日期、出生城市。
mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1),
-> birth DATE, birthaddr VARCHAR(20));
Query OK, 0 rows affected (0.00 sec)

由于name、birthadd的列值是变化的,因此选择VARCHAR,其长度不一定是20。可以选择从1到255的任何长度,如果以后需要改变它的字长,可以使用ALTER TABLE语句。);
性别只需一个字符就可以表示:”m”或”f”,因此选用CHAR(1);birth列则使用DATE数据类型。

创建了一个表后,我们可以看看刚才做的结果,用SHOW TABLES显示数据库中有哪些表:
mysql> SHOW TABLES;
+———————+
| Tables in menagerie |
+———————+
| mytables      |
+———————+

10、显示表的结构:
mysql> DESCRIBE mytable;
+————-+————-+——+—–+———+——-+
| Field    | Type    | Null | Key | Default | Extra |
+————-+————-+——+—–+———+——-+
| name    | varchar(20) | YES |   | NULL  |    |
| sex     | char(1)   | YES |   | NULL  |    |
| birth    | date    | YES |   | NULL  |    |
| deathaddr  | varchar(20) | YES |   | NULL  |    |
+————-+————-+——+—–+———+——-+

11、查询所有数据:
mysql> select * from mytable;
+———-+——+————+———-+
| name   | sex | birth   | birthaddr |
+———-+——+————+——–+
| abccs  |f  | 1977-07-07 | china   |
| mary   |f  | 1978-12-12 | usa    |
| tom   |m  | 1970-09-02 | usa    |
+———-+——+————+———-+
3 row in set (0.00 sec)

12、修正错误记录:
假如tom的出生日期有错误,应该是1973-09-02,则可以用update语句来修正:
mysql> update mytable set birth = “1973-09-02” where name = “tom”;
再用2中的语句看看是否已更正过来。

13、选择特定行
上面修改了tom的出生日期,我们可以选择tom这一行来看看是否已经有了变化:
mysql> select * from mytable where name = “tom”;
+——–+——+————+————+
| name  |sex | birth   | birthaddr     |
+——–+——+————+————+
| tom  |m  | 1973-09-02 | usa    |
+——–+——+————+————+
1 row in set (0.06 sec)

上面WHERE的参数指定了检索条件。我们还可以用组合条件来进行查询:
mysql> SELECT * FROM mytable WHERE sex = “f” AND birthaddr = “china”;
+——–+——+————+————+
| name  |sex | birth   | birthaddr     |
+——–+——+————+————+
| abccs |f  | 1977-07-07 | china   |
+——–+——+————+————+
1 row in set (0.06 sec)

14 多表操作

前面我们熟悉了数据库和数据库表的基本操作,现在我们再来看看如何操作多个表。

在一个数据库中,可能存在多个表,这些表都是相互关联的。我们继续使用前面的例子。前面建立的表中包含了员工的一些基本信息,如姓名、性别、出生日期、出生地。我们再创建一个表,该表用于描述员工所发表的文章,内容包括作者姓名、文章标题、发表日期。

1、查看第一个表mytable的内容:
mysql> select * from mytable;
+———-+——+————+———–+
| name   | sex | birth   | birthaddr |
+———-+——+————+———–+
| abccs  |f   | 1977-07-07 | china   |
| mary   |f   | 1978-12-12 | usa    |
| tom   |m   | 1970-09-02 | usa    |
+———-+——+————+———–+

2、创建第二个表title(包括作者、文章标题、发表日期):
mysql> create table title(writer varchar(20) not null,
-> title varchar(40) not null,
-> senddate date);

向该表中填加记录,最后表的内容如下:
mysql> select * from title;
+——–+——-+————+
| writer | title | senddate  |
+——–+——-+————+
| abccs | a1  | 2000-01-23 |
| mary  | b1  | 1998-03-21 |
| abccs | a2  | 2000-12-04 |
| tom  | c1  | 1992-05-16 |
| tom  | c2  | 1999-12-12 |
+——–+——-+————+
5 rows in set (0.00sec)

3、多表查询
现在我们有了两个表: mytable 和 title。利用这两个表我们可以进行组合查询:
例如我们要查询作者abccs的姓名、性别、文章:
mysql> SELECT name,sex,title FROM mytable,title
-> WHERE name=writer AND name=‘abccs‘;
+——-+——+——-+
| name | sex | title |
+——-+——+——-+
| abccs | f  | a1  |
| abccs | f  | a2  |
+——-+——+——-+

上面例子中,由于作者姓名、性别、文章记录在两个不同表内,因此必须使用组合来进行查询。必须要指定一个表中的记录如何与其它表中的记录进行匹配。

注意:如果第二个表title中的writer列也取名为name(与mytable表中的name列相同)而不是writer时,就必须用mytable.name和title.name表示,以示区别。

再举一个例子,用于查询文章a2的作者、出生地和出生日期:
mysql> select title,writer,birthaddr,birth from mytable,title
-> where mytable.name=title.writer and title=‘a2‘;
+——-+——–+———–+————+
| title | writer | birthaddr | birth   |
+——-+——–+———–+————+
| a2  | abccs | china   | 1977-07-07 |
+——-+——–+———–+————+

15、增加一列:
如在前面例子中的mytable表中增加一列表示是否单身single:
mysql> alter table mytable add column single char(1);

16、修改记录
将abccs的single记录修改为“y”:
mysql> update mytable set single=‘y‘ where name=‘abccs‘;

现在来看看发生了什么:
mysql> select * from mytable;
+———-+——+————+———–+——–+
| name   | sex | birth   | birthaddr | single |
+———-+——+————+———–+——–+
| abccs  |f   | 1977-07-07 | china   | y   |
| mary   |f   | 1978-12-12 | usa    | NULL  |
| tom   |m   | 1970-09-02 | usa    | NULL  |
+———-+——+————+———–+——–+

17、增加记录
前面已经讲过如何增加一条记录,为便于查看,重复与此:
mysql> insert into mytable
-> values (‘abc‘,‘f‘,‘1966-08-17‘,‘china‘,‘n‘);
Query OK, 1 row affected (0.05 sec)
查看一下:
mysql> select * from mytable;
+———-+——+————+———–+——–+
| name   | sex | birth   | birthaddr | single |
+———-+——+————+———–+——–+
| abccs  |f   | 1977-07-07 | china   | y   |
| mary   |f   | 1978-12-12 | usa    | NULL  |
| tom   |m   | 1970-09-02 | usa    | NULL  |
| abc   |f   | 1966-08-17 | china   | n   |
+———-+——+————+———–+——–+

18、删除记录
用如下命令删除表中的一条记录:
mysql> delete from mytable where name=‘abc‘;
DELETE从表中删除满足由where给出的条件的一条记录。

再显示一下结果:
mysql> select * from mytable;
+———-+——+————+———–+——–+
| name   | sex | birth   | birthaddr | single |
+———-+——+————+———–+——–+
| abccs  |f   | 1977-07-07 | china   | y   |
| mary   |f   | 1978-12-12 | usa    | NULL  |
| tom   |m   | 1970-09-02 | usa    | NULL  |
+———-+——+————+———–+——–+

19、删除表:
mysql> drop table ****(表1的名字),***表2的名字;
可以删除一个或多个表,小心使用。

20、数据库的删除:
mysql> drop database 数据库名;
小心使用。

21、数据库的备份:
退回到DOS:
mysql> quit
d:mysqlbin
使用如下命令对数据库abccs进行备份:
mysqldump –opt abccs>abccs.dbb
abccs.dbb就是你的数据库abccs的备份文件。

22、用批处理方式使用MySQL:

首先建立一个批处理文件mytest.sql,内容如下:
use abccs;
select * from mytable;
select name,sex from mytable where name=‘abccs‘;

在DOS下运行如下命令:
d:mysqlbin mysql < mytest.sql
在屏幕上会显示执行结果。

如果想看结果,而输出结果很多,则可以用这样的命令:
mysql < mytest.sql | more

我们还可以将结果输出到一个文件中:
mysql < mytest.sql > mytest.out

23. 请先用root登录到mysql,方法:
c:/mysql/bin/mysql -u root -p

24.创建一个用户

mysql> GRANT ALL PRIVILEGES ON javatest.* TO javauser@”%”
->   IDENTIFIED BY “javadude” ;