mysql 学习总结

yzwj2004

　　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>>
　　mysql>>
　　mysql>>　　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 (>|=| 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 #仅包含建表语句
　　$ mysqldump -uUSERNAME -p [-hIP_ADDR] --no-create-info DBNAME>FILENAME.sql #仅包含插入数据的insert
　　$ mysqldump -uUSERNAME -p [-hIP_ADDR] DBNAME TABLENAME >FILENAME.sql #导出一张表的全部内容