mysql常用操作

1、查询数据库所有表名

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname'

2、查询表的所有行

select column_name from information_schema.columns where table_schema='dbname' and table_name='tbname';

复制表结构

CREATE TABLE b2 SELECT * FROM a

3、设置增长起始值

alter table table_name AUTO_INCREMENT=10000;

4、排序

SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]

asc升序 desc降幂

5、正则表达式

查找name字段中以'st'为开头的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

6、 重复数据处理

INSERT IGNORE INTO（忽略重复数据不会报错）

INSERT INTO（插入重复数据会报错）

replace into （插入重复数据时替换重复数据）

以上的重复数据是指设置了唯一索引的数据

统计重复数据条数sql;

select count(*),t.name from table_name t group by t.name having count(t.name)>1

删除重复记录 

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

7、 防止sql注入

sql注入是指通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

java中hiberate框架尽量避免拼接字符串，如果用了做代码审核。mybatis查询避免使用${}

在PHP脚本中我们可以使用addcslashes()函数处理参数

8、导入导出数据

导出数据

SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/tutorials.txt';

导入数据

方法太多，直接copy语句或者使用工具执行sql

9、常用sql语句

常用简单的语句

查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙

排序：select * from table1 order by field1,field2 [desc] 

总数：select count as totalcount from table1 

求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1 

高级查询

UNION 运算符通过组合其他两个结果表（消除重复行）

UNION ALL 运算符通过组合其他两个结果表（不消除重复行）

使用外连接 

A、left outer join： 左外连接（左连接）：

结果集几包括连接表的匹配行，也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 

B：right outer join: 

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 

C：full outer join： 

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

10、复制表

1.只复制表结构到新表

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2

或者

CREATE TABLE 新表 LIKE 旧表

2.复制表结构及数据到新表

CREATE TABLE 新表 SELECT * FROM 旧表

3.复制旧表的数据到新表(假设两个表结构一样)

INSERT INTO 新表 SELECT * FROM 旧表

4.复制旧表的数据到新表(假设两个表结构不一样)

INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

11、常用函数

GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串

CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔

FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置

日期和时间函数

CURDATE()或CURRENT_DATE() 返回当前的日期

CURTIME()或CURRENT_TIME() 返回当前的时间

DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);

DATE_FORMAT(date,fmt)  依照指定的fmt格式格式化日期date值

DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);

DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)

DAYOFMONTH(date)  返回date是一个月的第几天(1~31)

DAYOFYEAR(date)   返回date是一年的第几天(1~366)

DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);

FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts

HOUR(time)   返回time的小时值(0~23)

MINUTE(time)   返回time的分钟值(0~59)

MONTH(date)   返回date的月份值(1~12)

MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);

NOW()    返回当前的日期和时间

QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);

WEEK(date)   返回日期date为一年中第几周(0~53)

YEAR(date)   返回日期date的年份(1000~9999)

加密函数

AES_ENCRYPT(str,key)  返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储

AES_DECRYPT(str,key)  返回用密钥key对字符串str利用高级加密标准算法解密后的结果

DECODE(str,key)   使用key作为密钥解密加密字符串str

ENCRYPT(str,salt)   使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str

ENCODE(str,key)   使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储

MD5()    计算字符串str的MD5校验和

PASSWORD(str)   返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

SHA()    计算字符串str的安全散列算法(SHA)校验和

12、附件

根据format字符串格式化date值:

%S, %s 两位数字形式的秒（ 00,01, ..., 59）

%I, %i 两位数字形式的分（ 00,01, ..., 59）

%H 两位数字形式的小时，24 小时（00,01, ..., 23）

%h 两位数字形式的小时，12 小时（01,02, ..., 12）

%k 数字形式的小时，24 小时（0,1, ..., 23）

%l 数字形式的小时，12 小时（1, 2, ..., 12）

%T 24 小时的时间形式（hh:mm:ss）

%r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）

%p AM或PM

%W 一周中每一天的名称（Sunday, Monday, ..., Saturday）

%a 一周中每一天名称的缩写（Sun, Mon, ..., Sat）

%d 两位数字表示月中的天数（00, 01,..., 31）

%e 数字形式表示月中的天数（1, 2， ..., 31）

%D 英文后缀表示月中的天数（1st, 2nd, 3rd,...）

%w 以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, ..., 6=Saturday）

%j 以三位数字表示年中的天数（ 001, 002, ..., 366）

%U 周（0, 1, 52），其中Sunday 为周中的第一天

%u 周（0, 1, 52），其中Monday 为周中的第一天

%M 月名（January, February, ..., December）

%b 缩写的月名（ January, February,...., December）

%m 两位数字表示的月份（01, 02, ..., 12）

%c 数字表示的月份（1, 2, ...., 12）

%Y 四位数字表示的年份

%y 两位数字表示的年份

%% 直接值“%”