Mysql语法基本操作

---------------------------------------------------------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 [e-mail] 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)校验和