mysql 学习总结

2017-01-07 11:18:14来源:作者:人点击

MYSQL的增、删、查、改

注册、授权

#创建一个对数据库中的表有一些操作权限的用户,其中OPERATION可以用all privileges替换,DBNAME、TABLENAME可以用*替换,表示全部
mysql> grant OPERATION on DBNAME.TABLENAME to 'USERNAME'@'IP_ADDR' [identified by 'PASSWD'];
mysql> create user 'USERNAME'@'IP_ADDR' identified by 'PASSWD';
mysql> revoke OPERATION on DBNAME.TABLENAME from USERNAME; #收回用户的某些权限
mysql> show grants for 'USERNAME'[@'IP_ADDR']; #查询用户权限
$ mysqladmin -u USERNAME -p [OLD_PWD] password NEW_PWD #更新密码(注:如果原来没有密码OLD_PWD就不要写)

mysql字段的数据类型

int[(M)]#整型
double[(M,D)] #双精度浮点型
date #日期类型 格式YYYY-MM-DD 范围1000-01-01——》9999-12-31
char(M) #字符类型
blob text #定长字符串(会用空格填满)
varchar #变长字符串类型
timestamp #时间戳

mysql数据类型相关函数

INET_ATON(expr) #将一个表示ip地址的字符串转换为整数
INET_NTOA(expr) #将一个整数转换为表示ip地址的字符串
NOW() #获取当前时间戳
TO_DAYS(timestamp) #一年中的哪一天
DAYOFWEEK(expr) #一周中的哪一天,周日开始,从1计数
WEEKDAY(expr) #一周中的哪一天,周一开始,从0计数
DAYOFMONTH(expr) #一月中的哪一天,从1计数
DAYOFYEAR(expr) #一年中的哪一天,从1计数
MONTH(expr) #月份1-12
DAYNAME(expr) #星期的名字
MONTHNAME(expr) #月份的名字
QUARTER(expr) #一年中季度1-4
WEEK(expr) #一年中的周数0-52
YEAR(expr) #年份
HOUR(expr) #小时0-23
MINUTE(expr) #分钟0-59
SECOND(expr) #秒0-59
PERIOD_ADD(expr1, expr2) #增加N个月,expr1为日期字符串,expr2为增加的时间(与expr1最小单位一样)
PERIOD_DIFF(expr1, expr2) #两个最小单位相同的日期字符串比较
DATE_ADD(expr1, INTERVAL expr2 date_type) #增加以date_type为单位的expr2个时间间隔
DATE_SUB(expr1, INTERVAL expr2 date_type) #减少以date_type为单位的expr2个时间间隔

DATA_FROMAT(expr1, format) #将expr1表示的时间以format格式输出,其中format的相关字符含义如下:
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。

数据库的增、删、查

mysql> create database DBNAME; #创建数据库
mysql> use DBNAME; #连接数据库
mysql> select database(); #查看当前连接的数据库
mysql> show databases; #查看所有的数据库
mysql> drop database DBNAME; #删除数据库

表的增、删、查、改

mysql> drop table if exists TABLENAME; create table if not exists TABLENAME (KEY1 varchar(128) TYPENAME1, KEY2 TYPENAME2, ....); #建表
mysql> show tables; #查看当前连接数据库中所有的表名
mysql> desc TABLENAME; #查看表结构
mysql> rename table TABLENAME_OLD to TABLENAME_NEW; #表的重命名
mysql> drop table TABLENAME; #删除表
mysql> alter table TABLENAME add KEY TYPENAME; #表中增加一列
mysql> alter table TABLENAME modify column KEY1 TYPENAME1 [(befor | after) KEY2] ; #修改字段类型
mysql> alter table TABLENAME drop KEY; #删除表的一个字段
mysql> insert into TABLENAME [(KEY1, KEY2, ....)] values(VALUE1, VALUE2, ....); #表中插入数据
mysql> select KEY1, KEY2, ... from TABLENAME where EXPRESSION; #查询表中数据
mysql> delete from TABLENAME where EXPRESSION; #删除表中数据
mysql> update TABLENAME set KEY1=VALUE1, KEY2=VALUE2, .... where EXPRESSION; #更新表中数据

注:由于mysql中不支持datetime字段设置默认值,所以只能采用timestamp,但是timestamp只能到2038年

表查询的高级用法

谓词:ALL(符合条件的全部)、DISTINCT(相同字段数据只返回一条)、DISTINCTROW(相同记录只返回一条)、TOP(头尾的若干记录,当使用百分比的时候,为TOP N PERCENT,N为数字)、AS(为结果字段取别名,AS前面是原名、后面为别名)

比较符:
= #等于> #大于< #小于>= #大于等于
<= #小于等于 <> #不等于!> #不大于!< #不小于 NOT #用于比较表达式前表示相反

模式匹配(必须在LIKE之后):
% #替代一个或者多个字符
- #仅替代一个字符
[charlist] #字符列中任何单一字符
[^charlist] #不在字符列中的任何单一字符

[NOT] BETWEEN ... AND .. #指定要搜索的闭区间[之外]的范围
[NOT] IN #用于[不]匹配列表中的任何一个值
ORDER BY KEY1 (ASC|DESC), KEY2 (ASC|DESC) ... #将结果以KEY的(升/降)序排列,默认是ASC
... AND ... #同时满足两个条件
GROUP BY KEY1, KEY2... [HAVING CONDITION] #以KEY1、KEY2等进行分组,HAVING 调用一些聚集函数来过滤分组的查询结果

聚集函数:
SUM(KEY) #求和
AVG(KEY) #求均值
COUNT(KEY) #计数
COUNT(*) #所有记录计数
MAX(KEY) #最大值
MIN(KEY) #最小值
VAR(KEY) #方差
STDEV(KEY) #标准差
FIRST(KEY) #第一个
LAST(KEY) #最后一个
CONCAT(KEY1, KEY2, ...) #将keys连接起来成为字符串

#将查询结果写入到另一个表TABLENAME1中
mysql> select KEY1, KEY2, ... into TABLENAME1 from TABLENAME2 where EXPRESSION;

#将从TABLENAME1、TABLENAME2两个表中的查询数据合并展示
mysql> select KEY1, KEY2, ... from TABLENAME1 where EXPRESSION1 union select KEY3, KEY4, .. from TABLENAME2 where EXPRESSION2;

#TABLENAME1表中key3大于子查询结果的记录
mysql> select KEY1, KEY2, ... from TABLENAME1 where KEY3 (>|=|<|<>) (ANY|ALL|SOME) (select KEY3 from TABLENAME2 where EXPRESSION);

#TABLENAME1表中key3[不]属于子查询结果列表中的记录
mysql> select KEY1, KEY2, ... from TABLENAME1 where KEY3 [NOT] IN (select KEY3 from TABLENAME2 where EXPRESSION);

#根据子查询的结果来决定是否执行从TABLENAME1表中进行查询
mysql> select KEY1, KEY2, ... from TABLENAME1 where exists (select KEY3 from TABLENAME2 where EXPRESSION);



查询(删除)5min之前的内容

select * from email_info where minute(now() - c_time) > 5; 该方法如果跨整点就会有问题
select * from email_info where timestampdiff(minute, c_time, now()) > 5;
delete from email_info where timestampdiff(minute, c_time, now()) > 5;

注:minute是将时间转换成分钟的函数,类似的还有year、dayofyear、month、monthname、dayofmonth、week、weekday、dayname、hour、minute、second等


索引、视图、触发器、存储过程、游标、事务

视图是虚拟表,相当于一个sql语句的别名,特定情况下可以对视图进行增、删、改操作,前提是:没有group by分组、没有union连接、没有子查询、没有并、没有聚集函数、没有DISTINCT、导出(计算)列
索引、视图的增、删、改

mysql> create index INDEX_NAME on TABLENAME(KEY); #创建索引
mysql> show index from TABLENAME; #查询索引
mysql> drop index INDEX_NAME; #删除索引

mysql> create view VIEW_NAME(KEY1, KEY2, ...) as select KEY3, KEY4 from TABLENAME; #创建视图
mysql> drop view VIEW_NAME; #删除视图


存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件,虽然它们的作用不仅限于批处理。存储过程3个主要的好处:简单、安全、高性能。
存储过程的创建、删除、调用

#创建一个存储过程PROC_NAME, 其中DELIMITER//告诉命令行实用程序使用//作为新的语句结束符,可以看到表示存储过程结束的END定义END//而不是END;作为语句结束。最后使用DELIMITER; 恢复原来的语句结束符,因为mysql中;默认为结束符,为了存储过程正常使用,所以需要替换procedure中的存储过程。
#参数中的IN/OUT表示参数是传入的还是输出的,参数是无类型的,可以是一个简单变量,也可以是一个表名(此时可以直接select @arg来查询结果),通常存储过程使用select ... into ... 语句将结果保存到输出变量中

delimiter //
create procedure PROC_NAME([IN/OUT] ARG1 TYPENAME, [IN/OUT]ARG2 TYPENAME, ...)
BEGIN
SQL_SENTENCES;
END//
delimiter ;

mysql> call PROC_NAME(@arg1, @arg2, ...); #调用存储过程,mysql中变量前面需要加@
mysql> drop procedure PROC_NAME if exists; #删除存储过程
mysql> show create procedure PROC_NAME; #查看创建存储过程的sql语句
mysql> show procedure status like 'EXP'; #查看存储过程的相关信息


游标是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结构集。在存储了游标之后,应用程序可以根据需要滚动或浏览或更改其中的数据。
游标使用步骤:

使用前需要先声明

使用时必须打开游标(执行相关的sql查询语句),游标打开后使用FETCH语句分别访问每一行、FETCH指定检索什么数据、存储在何地,同时会将游标指向下一行(即下一条FETCH就会检索下一行)

根据需要行数据,使用完毕后必须关闭

declare CURSOR_NAME cursor for select KEY1, KEY2, ... from TABLENAME; #声明一个游标
open CURSOR_NAME; #打开游标
FETCH CURSOR_NAME into TABLENAME2; #将游标获取的一行记录存到数据表中。
close CURSOR_NAME;#关闭游标


触发器是MySQL响应INSERT、UPDATE、DELETE三个中的任意一个语句而自动执行的一条sql语句。触发器创建条件:名称唯一、有具体表关联(视图、临时表不行)、与相关的操作(INSERT/UPDATE/DELETE)关联响应、在关联语句执行前/后执行。
触发器的创建、删除

mysql> create trigger TRI_NAME (before/after) (INSERT/UPDATE/DELETE) on TABLENAME for each row BEGIN SQL_SENTENSE END;
mysql> drop trigger TRI_NAME;

事务处理可以用来维护数据库的完整性,它保证成批的mysql操作要么完全执行,要么完全不执行。管理事务处理的关键在于将sql语句组分解成逻辑快,并明确规定数据何时应该回退、何时不回退。 start transaction 表示事务的开始。
事务的创建

start transaction; #事务开始
SQL_SENTENCE1;
SQL_SENTENCE2;
...
(commit/rollback); #事务提交/回滚
#注:通常情况下是判断上一条sql语句的执行结果,如果执行失败,则执行rollback进行回滚操作,若事务中所有的sql语句成功执行,则执行commit将更改实际写到数据库中。



数据导入、导出

导入txt格式的数据

mysql> load data local infile 'FILENAME.txt' into table TABLENAME; #注:txt中各字段用tab分隔

导入sql格式的数据库 (注:这里的sql文件是包含建表语句和表中数据)

mysql> use DBNAME; source FILENAME.sql #方法一
$ mysqldump -u USERNAME -p DBNAME <FILENAME.sql #方法二
$ mysql -u USERNAME -p -D DBNAME <FILENAME.sql #方法三


导出整个数据库中所有的表结构

$ mysqldump -uUSERNAME -p [-hIP_ADDR] DBNAME>FILENAME.sql #包含建表语句以及插入数据的insert语句
$ mysqldump -uUSERNAME -p -d [-hIP_ADDR] DBNAME >FILENAME.sql #仅包含建表语句
$ mysqldump -uUSERNAME -p [-hIP_ADDR] --no-create-info DBNAME>FILENAME.sql #仅包含插入数据的insert
$ mysqldump -uUSERNAME -p [-hIP_ADDR] DBNAME TABLENAME >FILENAME.sql #导出一张表的全部内容

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台