---------------------------------------------------------Mysql Studey---------------------------------------------------
--登录数据库
mysql -u root -p
--修改密码
mysqladmin -u用户名 -p旧密码 password '新密码';
例子:
mysqladmin-uroot -pab12 password djg345;
shell>mysql -u root -p
mysql> update user setpassword=password(”xueok654123″) where user=’root’;
--添加用户
grant select on 数据库.* to 用户名@登录主机 identified by '密码';
例子:grant select,insert,update,deleteon *.* to test2@localhost identified by 'abc'/'';
--创建数据库
CREATE DATABASE DATABASE_NAME;
--使用数据库
use DATABASE_NAME;
--授权
GRANT ALL PRIVILEGES ON bdc.* TObdc@'%' IDENTIFIEDBY 'bdc123' WITH GRANT OPTION;
flush privileges;
mysql >grant select, insert,update, delete, create,drop on fangchandb.* to custom@it363.com identified by ‘passwd’
--删除授权
mysql> revoke all privilegeson *.* from root@”%”;
mysql> delete from user whereuser=”root” and host=”%”;
mysql> flush privileges;
--查询全部表
show tables;
--查询表结构
desc table_name;
--创建表
CREATE TABLE CLASS(
ID int(11) DEFAULT NULL,
CLASS_NAME varchar(255) DEFAULT NULL,
COUNT int(11) DEFAULT NULL,
SCORE int(255) DEFAULT NULL,
TEACHER_ID int(11) DEFAULT NULL
)
-- 添加单列/多列
ALTER TABLE_NAME ADD(COLUMN_NAME1 char(10),COLUMN_NAME2 char(10)...);
-- 修改列
ALTER TABLE_NAME MODIFY COLUMN_NAME char(20) not null;
-- 修改列默认值
ALTER TABLE_NAME ALTER COLUMN_NAME SET DEFAULT '值';
-- 删除列默认值
ALTER TABLE_NAME ALTER COLUMN_NAME DROP DEFAULT;
-- 删除列
ALTER TABLE_NAME DROP COLUMN COLUMN_NAME;
-- 删除表
DROP TABLE TBALE_NAME;
-- 清空表
DELETE FROM TABLE_NAME;
-- 重命名表
ALTER TABLE T1 RENAME T2;
--添加主外键
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(列名)/REFERENCES table_name(列名);
--删除主/外键
ALTER TABLE TABLE_NAME DROP FOREIGN KEY '主/外键名';
------------插入------------
-- 插入单列
INSERT INTO TABLE_NAME(列名) VALUES(值);
INSERT INTO TABLE_NAME VALUES(值);
-- INSERT INTO SELECT
INSERT INTO TABLE_NAME SELECT 列名 FROM TABLE_NAME;
-- INSERT 多条记录
INSERT INTO TABLE_NAME(列名) VALUES(值),(值),(值)...;
------------修改------------
-- 单表更新单列/多列/单列+条件/多列+条件
UPDATE TABLE_NAME SET 列名='值'/列名='值',SET 列名='值',SET 列名='值'.../列名='值',列名='值' WHERE XX = XX (相应条件);
-- 2表关联根据子查询更新
UPDATE TABLE_A SET TABLE_A.COLUMN_A = TABLE_B.COLUMN_B FROM (SELECT COLUMN_B FROM TABLE_B WHERE XX = XX(相应条件));
-- 2表关联 连接/左连接/右连接更新单列/多列/单列+条件/多列+条件
UPDATE TABLE_A INNER JOIN/LEFT JOIN/RIGHT JOIN TABLE_B ON TABLE_A.COLUMN_A = TABLE_B.COLUMN_B SET 列名='值' / 列名='值',SET 列名='值',SET 列名='值'.../列名='值',列名='值' WHERE XX = XX (相应条件);
-- 2表同时更新
UPDATE TABLE_A,TABLE_B SET TABLE_A.COLUMN_A = '值',TABLE_B.COLUMN_B = '值';
-- 批量更新同一列名数据
UPDATE TABLE_NAME SET 列名='值' WHERE 列名 IN('值');
-- 批量更新不同列同一列名数据
UPDATE TABLE_NAME SET 列名='值' WHEN 列名 THEN 值,WHEN 列名 THEN 值... WHERE 列名 IN('值');
-- 批量更新不同列不同列名数据
UPDATE TABLE_NAME SET 列名='值' WHEN 列名 THEN 值,WHEN 列名 THEN 值... END,列名='值' WHEN 列名 THEN 值,WHEN 列名 THEN 值... END WHERE 列名 IN('值');
------------查询-------------
-- 单表 单条件/多条件
SELECT */列名 FROM TABLE_NAME WHERE 列名='值'/列名 IN ('值1','值2'...);
-- 多表 无条件
SELECT */列名 FROM TABLE_A INNER JOIN/LEFT JOIN/RIGHT JOIN TABLE_B... ON TABLE_A.COLUMN = TABLE_B.COLUMN ...;
-- 多表关联 单条件/多条件
SELECT */列名 FROM TABLE_A INNER JOIN/LEFT JOIN/RIGHT JOIN TABLE_B... ON TABLE_A.COLUMN = TABLE_B.COLUMN ... WHERE 列名='值'/列名 IN ('值1','值2'...);
-- 单表 单条件/多条件 FOR UPDATE
SELECT */列名 FROM TABLE_NAME WHERE 列名='值'/列名 IN ('值1','值2'...) FOR UPDATE;
------------删除------------
-- 删除单条记录
DELETE FROM TABLE_NAME WHERE 列名='值';
-- 删除多条记录
DELETE FROM TABLE_NAME WHERE 列名 IN ('值1','值2','值3'...);
-- 批量删除
DELETE FROM TABLE_NAME WHERE 列名='值' ORDER BY 列名 LIMIT 10000;
-- DELETE FROM
DELETE FROM TABLE_NAME WHERE 列名=(SELECT MAX('值') FROM TABLE_NAME);
-- DELETE 多表关联
DELETE TABLE_A.COLUMN_A, TABLE_B.COLUMN_B FROM TABLE_A,TABLE_B,TABLE_C... WHERE TABLE_A.COLUMN_A = TABLE_B.COLUMN_B AND TABLE_B.COLUMN_B = TABLE_C.COLUMN_C...;
-- DELETE USING 多表关联
DELETE FROM T1,T2 USING T1,T2,T3... WHERE T1.COLUMN = T2.COLUMN AND T2.COLUMN = T3.COLUMN...;
-- DELETE 多表连接/左连接/右连接
DELETE TABLE_A.COLUMN,TABLE_B.COLUMN FROM TABLE_A LEFT JOIN/RIGHT JOIN/INNER JOIN TABLE_B ON TABLE_A.COLUMN = TABLE_B.COLUMN WHERE TABLE_A.COLUMN = '值'/ TABLE_A IN ('值');
------------------------------------分页----------------------------
SELECT * FROM TABLE_NAME LIMIT M,N;
SELECT * FROM TABLE_NAME WHERE 主键或者索引 > (页码 * 每页显示条数) LIMIT M;
SELECT * FROM TABLE_NAME WHERE 主键或者索引 > (页码 * 每页显示条数) ORDER BY 主键或者索引 ASC LIMIT M;
PREPARE TABLE_NAME FROM SELECT * FROM TABLE_NAME WHERE 主键或者索引 > (页码 * 每页显示条数) ORDER BY 主键或者索引 ASC LIMIT M;
SELECT * FROM TABLE_NAME WHERE 主键或者唯一键 >=1000 ORDER BY 主键或者唯一键 ASC LIMIT 0,20;
SELECT * FROM TABLE_NAME WHERE 列名 <= (SELECT 列名 FROM TABLE_NAME ORDER BY 列名 desc LIMIT (页码-1)* 页数
ORDER BY 列名 desc LIMIT 页数;
SELECT * FROM TABLE_NAME AS t1 JOIN(SELECT 列名 FROM TABLE_NAME ORDER BY 列名 desc LIMIT (页码-1)* 页数 AS t2
WHERE t1.列名 <= t2.列名 ORDER BY t1.列名 desc LIMIT 页数;
SELECT FOUND_ROWS();
---------------------------------存储过程---------------------------
-- 基本函数
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string[,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
-- 输入参数和输出参数存储过程
DROP PROCEDURE IF EXISTS GET_STUDENT_ID;
CREATE PROCEDURE GET_STUDENT_ID(IN stu_name varchar(200),OUT stu_id int)
BEGIN
SELECT student.id into stu_id FROM student WHERE student.`name`=stu_name;
END;
-- 输入参数存储过程
DROP PROCEDURE IF EXISTS GET_STUDENT_INFO;
CREATE PROCEDURE GET_STUDENT_INFO(in num int)
BEGIN
SELECT student.id,student.`name`,student.sex,student.age,student.address,
class.class_name,teacher.teacher_name
FROM class
JOIN teacher on class.teacher_id = teacher.id
JOIN student on student.teacher_id = teacher.id
WHERE student.id=num;
END;
-- 测试调用过程
call GET_STUDENT_ID('xiao S',@id);
select @id;
call GET_STUDENT_INFO(@id);
--------------------------------游标-------------------------
DROP PROCEDURE IF EXISTS GET_STUDENT;
CREATE PROCEDURE GET_STUDENT()
BEGIN
DECLARE id INT;
DECLARE name varchar(200);
DECLARE class_id int;
DECLARE sex varchar(20);
DECLARE age int;
DECLARE address varchar(500);
DECLARE teacher_id int;
DECLARE done INT DEFAULT 1;
DECLARE cur CURSOR FOR SELECT * from student;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur;
read_loop:LOOP
FETCH cur INTO id,name,class_id,sex,age,address,teacher_id;
IF done = 1 THEN
LEAVE read_loop;
END IF;
END LOOP read_loop;
insert into test(id,name,class_id,sex,age,address,teacher_id) values(id,name,class_id,sex,age,address,teacher_id);
CLOSE cur;
END;
call GET_STUDENT();
select * from test;
---------------------------------------触发器-----------------------------
----插入数据 触发器
DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;
CREATE TRIGGER ADD_STUDENT
AFTER INSERT ON student
FOR EACH ROW
BEGIN
insert into test(id,name,class_id,sex,age,address,teacher_id) values(new.id,new.name,new.class_id,new.sex,new.age,new.address,new.teacher_id);
END;
insert into student(id,name,class_id,sex,age,address,teacher_id) values(9,'xiao Y',9,'man',22,'xxxxx',3);
select * from student;
select * from test;
----删除数据 触发器
DROP TRIGGER IF EXISTS del_student;
CREATE TRIGGER del_student
AFTER DELETE ON student
FOR EACH ROW
BEGIN
delete from test where id=old.id;
END;
delete from student where id = 9;
commit;
select * from student;
select * from test;
---------------------------------------Job------------------------------------
-- 创建job 每天的凌晨0点10分执行
CREATE EVENT `event_test` ON SCHEDULE EVERY 1 DAY STARTS '2016-08-04 00:10:00'
ON COMPLETION NOT PRESERVE ENABLE
DO CALL GET_STUDENT();
-- 删除job
drop event event_test;
-------------------------------------存储过程基本操作------------------------
-- 查看存储过程
SELECT NAME FROM mysql.proc WHERE db ='数据库名';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
SHOW PROCEDURE STARTS WHERE db='数据库名';
-- 修改存储过程
ALTER PROCEDURE PROCEDURE_NAME CONTAINS SQL/NO SQL/READS SQL DATA/MODIFIES SQL DATA/
SQL SECURITY DEFINER/INVOKER
COMMENT '相应操作'
-- 删除存储过程
DROP PROCEDURE PROCEDURE_NAME;
-------------------------------------循环------------------------------------
-- WHILE
drop procedure if exists pro_while;
CREATE PROCEDURE pro_while()
BEGIN
DECLARE cout INT;
SET cout=1;
WHILE cout<11 DO
INSERT INTO user_profile VALUES(cout);
SET cout=cout+1;
END WHILE;
END;
delete from user_profile;
CALL pro_while();
select * from user_profile;
-- REPEAT
drop PROCEDURE IF EXISTS TS_REPEAT;
CREATE PROCEDURE TS_REPEAT()
BEGIN
DECLARE i INT;
SET i = 1;
REPEAT
insert into user_profile(uid) values(i);
set i = i +1;
UNTIL i >= 20
end REPEAT;
end;
delete from user_profile;
call TS_REPEAT();
select * from user_profile;
--- LOOP
drop PROCEDURE if EXISTS ts_loop;
create PROCEDURE ts_loop()
BEGIN
DECLARE i INT;
set i = 1;
lp1 : LOOP
set i = i+1;
if i > 30 THEN
LEAVE lp1;
end if;
end loop;
end;
call ts_loop();
---------------------------------------------日期时间函数------------------------------------
ADDTIME (date2 ,time_interval )//将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0
SQRT(number2) //开平方
---------------------------------数学函数--------------------------
ABS(x) 返回x的绝对值
BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
CEILING(x) 返回大于x的最小整数值
EXP(x) 返回值e（自然对数的底）的x次方
FLOOR(x) 返回小于x的最大整数值
GREATEST(x1,x2,...,xn)返回集合中最大的值
LEAST(x1,x2,...,xn) 返回集合中最小的值
LN(x) 返回x的自然对数
LOG(x,y)返回x的以y为底的对数
MOD(x,y) 返回x/y的模（余数）
PI()返回pi的值（圆周率）
RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(x,y)返回参数x的四舍五入的有y位小数的值
SIGN(x) 返回代表数字x的符号的值
SQRT(x) 返回一个数的平方根
TRUNCATE(x,y) 返回数字x截短为y位小数的结果
--------------------------------聚合函数---------------------------
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
------------------------------加密函数-----------------------------
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)校验和
