首页 > 代码库 > mysql 学习总结

mysql 学习总结

2024-08-30 13:50:37   219人阅读

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语句，而是被该语句检索出来的结构集。在存储了游标之后，应用程序可以根据需要滚动或浏览或更改其中的数据。
游标使用步骤：
  1. 使用前需要先声明
  2. 使用时必须打开游标（执行相关的sql查询语句），游标打开后使用FETCH语句分别访问每一行、FETCH指定检索什么数据、存储在何地，同时会将游标指向下一行（即下一条FETCH就会检索下一行）
  3. 根据需要行数据，使用完毕后必须关闭
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 #导出一张表的全部内容

mysql 学习总结


联系
我们