Mysql语法基本操作

蔚落 2021-09-26 00:12 420阅读 0赞

\---------------------------------------------------------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)校验和