MySql常用详细语句

1、- 1.有些东西没加到，有额外的可以给我说下- 2.以下语法在一定条件下全部都可以运行，并非自上而下，比如：删除主键，需要先去掉主键的自增，之类的顺序问题USER test- 创建表CREATE TABLE Student(StuId INT(3),StuName VARCHAR(20),StuSex ENUM('男','女'),StuAge INT(3),ClassId INT(3) CREATE TABLE Class(CalssId INT(3) PRIMARY KEY,ClassName VARCHAR(20)- 删除表DROP TABLE Studen

2、t- 修改表名ALTER TABLE Student RENAME Student1- 修改字段名ALTER TABLE Student CHANGE StuAge StuAge1 INT(3)- 修改字段类型ALTER TABLE Student MODIFY COLUMN StuAge INT(4)- 添加字段ALTER TABLE Student ADD ClassID1 INT(4)- 删除字段ALTER TABLE Student DROP ClassID1- 创建主键 约束ALTER TABLE Student ADD CONSTRAINT PK_STUID PRIMARY KEY

3、(StuId)- 删除主键 删除外键前 先要删掉 主键设置的自增ALTER TABLE Student DROP PRIMARY KEY- 创建外键 约束ALTER TABLE Student ADD CONSTRAINT FK_CLASSID FOREIGN KEY (CLASSID) REFERENCES CLASS(CLASSSID)- 删除外键ALTER TABLE Student DROP FOREIGN KEY FK_CLASSID- 设置主键增自增ALTER TABLE Student MODIFY StuId INT UNSIGNED AUTO_INCREMENT- 删除自增A

4、LTER TABLE Student CHANGE StuId StuId INT - 改变列的位置ALTER TABLE Student MODIFY StuAge INT(3) AFTER StuSex- 查看表的状态DESC student- 插入数据- 1.INSERT INTO Student (StuName,StuSex,StuAge,Classid)VALUES('命天1','男',22,1)- 2.多个值用逗号隔开INSERT INTO Student (StuName,StuSex,StuAge,Classid) VALUES('队长

5、','男',22,2),('队友1','女',22,3),('队友2','女',22,4),('队友3','女',22,5)- 删除数据 需要给个条件，不给条件删全表所有值DELETE FROM Student WHERE Stuid=3- 修改UPDATE Student SET ClassId=4 WHERE StuId=7- 复制表的类型- 创建一个表并且把student的类型复制过去，如果where成立则会将整个表的数据也复制过去CREATE TABLE studen

6、t2ASSELECT * FROM student WHERE 1=1- 查询SELECT StuId FROM StudentSELECT S.StuId FROM Student AS S- 改变显示的值的列的名字SELECT StuId AS '学号' FROM StudentSELECT StuId'学号'FROM Student- 数学运算 只能运算int类型的字段 + - * / % SELECT StuId+StuAge FROM Student SELECT StuAge/StuId FROM Student - 将两个表的结果组合在一起 SEL

7、ECT StuId,StuName FROM Student UNION SELECT classsId,className FROM class - 关系运算 SELECT * FROM Student WHERE StuId=1 SELECT * FROM Student WHERE StuId>1 SELECT * FROM Student WHERE StuId>=1 SELECT * FROM Student WHERE StuId<1 SELECT * FROM Student WHERE StuId<=1 SELECT * FROM Student WHE

8、RE StuId<>1 SELECT * FROM Student WHERE StuId!=1 - 逻辑运算符 SELECT * FROM student WHERE stuId=1 AND stuName='命天1' SELECT * FROM student WHERE stuId=1 OR stuName='命天1' SELECT * FROM student WHERE NOT(stuId=1) - null SELECT * FROM student WHERE Classid IS NULL SELECT * FROM student

9、WHERE Classid IS NOT NULL - in SELECT * FROM student WHERE stuId IN(1,2,4,5,6) SELECT * FROM student WHERE stuId NOT IN(1,2,3,4,5) - betweenSELECT * FROM student WHERE stuId BETWEEN 1 AND 5 - 包含1和5SELECT * FROM student WHERE stuId NOT BETWEEN 1 AND 5 - like % 任意多个任意字符 _一个任意字符SELECT * FROM student WH

10、ERE stuName LIKE '%天_'- 分页SELECT * FROM student LIMIT 0,3 - 不包含 0- 查询班级是100001的所有学生的信息SELECT * FROM Student WHERE Classid=(SELECT ClasssID FROM class WHERE classname='100001')- 多张表数据的查询条件SELECT S.StuId,S.StuName,S.StuSex,S.StuAge,S.ClassID,C.ClassName FROM Student AS S,Class AS CWHER

11、E S.ClassID=C.ClasssID- 排序 默认为主键的升序SELECT * FROM Student ORDER BY Stuid- 降序SELECT * FROM Student ORDER BY StuId DESC- 联合排序SELECT * FROM Student ORDER BY StuId,StuSex DESC - 联合排序会跟第一个值来排，在第一个值相等时根据第二个值来排- 统计分组SELECT ClassId FROM Student GROUP BY ClassID- 多列分组SELECT ClassId,StuName FROM Student GROUP

12、BY ClassId ,StunameSELECT ClassId,StuSex FROM Student WHERE StuId<10 GROUP BY StuSex,classid- 过滤掉重复项 DISTINCTSELECT DISTINCT * FROM Student- 统计按照性别分组，统计每个组的人员数SELECT stuSex,COUNT(stuId),MAX(stuAge) FROM student GROUP BY stuSexCOUNT(列名)- 按照列计算记录数- null 不计算 ,以下为聚合函数SELECT COUNT(stuId) FROM studentS

13、ELECT MAX(stuAge) FROM studentSELECT MIN(stuAge) FROM studentSELECT SUM(stuId) FROM studentSELECT AVG(stuId) FROM student- 链接查询(1:内连接(inner join) 两个表的存在关系的数据(交集) SELECT S.stuId,S.stuName,S.stuSex,S.stuAge, C.classNameFROM Student AS S INNER JOIN Class AS CON S.ClassId=C.classsId- 链接查询(2:外链接 2-1:左外链接

14、 2-2 右外链接)- 左外链接(以left左边的表为主表(主表的数据全部出现) - 有链接right的右边的表作为主表SELECT S.stuId,S.stuName,S.stuSex,S.stuAge, C.classNameFROM Class AS C LEFT JOIN Student AS SON S.Classid=C.classsId- 只要查询语句有结果集(当前表达式就返回true) - 没有结果集返回就返回false- 有一点用问题 当子查询的where 里面的值不是class表的字段而是Studen的字段是会发生查到了一个SELECT * FROM student WHE

15、RE EXISTS( SELECT * FROM class WHERE classsId=1)- Any 取返回的最小值SELECT * FROM student WHERE stuId>=ANY( SELECT classsId FROM class WHERE classsId>2)- All 取返回的最小值SELECT * FROM student WHERE stuId>=ALL( SELECT classsId FROM class WHERE classsId>2)- 改和删都可以用上面的 逻辑- 添加上次所讲的 内容UNIQUE()- -20151207

16、添加内容- -本次添加为MySql的最终添加- - 1.索引 - 2.视图 - 3.变量 - 4.存储过程 - 5.函数 - 6.触发器- -索引 关键字(INDEX)- - 1.在创建表之后的额外的添加 CREATE INDEX Student_StuAge_Index ON Student(StuAge) - 2.在创建表的时候直接最后面加 INDEX 索引名（字段名）- 删除索引 DROP INDEX Student_StuAge_Index ON Student ALTER TABLE Student DROP INDEX Student_StuAge_Index- 创建唯一索引 -

17、1. CREATE UNIQUE INDEX UN_STUNAME ON student(sname) - 2.在创建表的时候加 UNIQUE（字段名） 会自动创建唯一索引 - -视图 关键字(VIEW)- - 视图(提高查询的效率) - 视图一张内存表(虚拟表,不是物理表,视图不存在真是数据) - 视图的数据都是来源于其他的物理的数据 - 修改试图中的数据会影响到真实数据表，视图可以查询多张表，但是不能操作影响多张表， - 当视图影响多张表时，只能 查 改 ，不可以 增 删 - 创建视图create View Student_Class_VIEW asSELECT S.StuId,S.Stu

18、Name,S.StuAge,S.StuSex,C.ClassName FROM Student AS SINNER JOIN Class AS CON S.ClassId=C.ClasssId - 使用视图SELECT * FROM Student_Class_VIEW - 修改视图 有这个视图则修改这个视图，没有则添加一个视图CREATE OR REPLACE VIEW Student_Class_VIEW AS SQl语句 - 删除视图DROP VIEW Student_Class_VIEW - -变量- - 用户变量(不需要定义可直接使用(变量名称前加上符号) - set 给变量赋值的关

19、键字(set 可以赋值为字面量)SET num=10; - 查询SELECT num; - 局部变量 DECLARE num INT(3) DEFAULT 1;- 只能在begin和 end中使用 默认值为1. - -存储过程 关键字(PROCEDURE)- - (优点:1:编译好存储在服务器 - (1:执行效率高(存储过程的语句都是先编译(创建存储过程时候就编译好了) - 2:安全性高(把不公开的数据或者业务封装在存储过程中,外部不需要知道内部的实现细节) - 3:减少网络流量 - 4:支持模块化) - 修改mysql 的结束语句 DELIMITER / - 删除名字为proc的存储过程DR

20、OP PROCEDURE IF EXISTS proc; - 创建存储过程DELIMITER / CREATE PROCEDURE proc()BEGIN SELECT '存储过程'END / - 使用存储过程CALL proc(); - 带参数的存储过程 - 1.默认的参数为 in - 2.out - 3.inout - in DROP PROCEDURE IF EXISTS proc;DELIMITER / CREATE PROCEDURE proc(i INT(3)BEGIN SELECT i;END /CALL proc(10); - out DROP PROCEDUR

21、E IF EXISTS proc;DELIMITER / CREATE PROCEDURE proc(i INT(3),j INT(3),OUT result INT(3)BEGIN SET result =i+j;END /SET result;CALL proc(10,20,result);SELECT result; - inout DROP PROCEDURE IF EXISTS proc;DELIMITER /CREATE PROCEDURE proc(i INT(3),j INT(3),INOUT result INT(3)BEGIN SELECT result AS result

22、; SET result=result+i+j;END /SET result=30;CALL proc(10,20,result)SELECT result - -各种循环- ifDROP PROCEDURE IF EXISTS proc;DELIMITER /CREATE PROCEDURE proc(i INT(3)BEGIN IF i<10 THEN SELECT '小于10' ELSEIF i>10 THEN SELECT '大于10' ELSE SELECT '等于10' END IF;END /- switch case

23、 DROP PROCEDURE IF EXISTS proc;DELIMITER /CREATE PROCEDURE proc(i INT(3)BEGIN CASE i WHEN 1 THEN SELECT '星期一' WHEN 2 THEN SELECT '星期二' WHEN 3 THEN SELECT '星期三' WHEN 4 THEN SELECT '星期四' WHEN 5 THEN SELECT '星期五' WHEN 6 THEN SELECT '星期六' WHEN 7 THEN SELEC

24、T '星期天' ELSE SELECT '错误的数据' END CASE;END /- case 范围DROP PROCEDURE IF EXISTS proc;DELIMITER /CREATE PROCEDURE proc(i INT(3)BEGIN CASE WHEN i>10 THEN SELECT '星期一' WHEN i<10 THEN SELECT '星期二' WHEN i=10 THEN SELECT '星期三' ELSE SELECT '错误的数据' END CASE;

25、END /- 循环DROP PROCEDURE IF EXISTS proc;DELIMITER /CREATE PROCEDURE proc(i INT(3)BEGIN DECLARE num INT(3) DEFAULT 1; - 定义局部变量(循环的次数) myloop:LOOP - 定义循环的块名称 SELECT num; - 循环没有结束 输出 SET num=num+1; - 循环变量递增 IF num<i THEN ITERATE myloop; - 当num<i 的时候 流程跳转到myloop开始位置 ELSE LEAVE myloop; - 当num<i 不

26、满足是 流程跳到myloop结束的位置 END IF; END LOOP myloop; - myloop块的结束END /- REPEAT UNTIL 循环DROP PROCEDURE IF EXISTS proc;DELIMITER /CREATE PROCEDURE proc(i INT(3)BEGIN DECLARE num INT(3) DEFAULT 1; - 定义局部变量(循环的次数) REPEAT - 开始循环 SELECT num; SET num=num+1; UNTIL num>i - 循环的条件(当条件满足则退出循环) END REPEAT;END /- whil

27、e DROP PROCEDURE IF EXISTS proc;DELIMITER /CREATE PROCEDURE proc(i INT(3)BEGIN DECLARE num INT(3) DEFAULT 1; - 定义局部变量(循环的次数) WHILE num<i DO SELECT num; SET num=num+1; END WHILE;END / - -函数 关键字(FUNCTION)- 在创建函数时报1418错误SET GLOBAL log_bin_trust_function_creators=TRUE;- 函数(不能有select语句) 必须定义返回值 在函数体内必

28、须有return语句DROP FUNCTION IF EXISTS myfun;DELIMITER /CREATE FUNCTION myfun(i INT(3) RETURNS INT(3)BEGIN DECLARE mysum INT DEFAULT 0; DECLARE num INT DEFAULT 1; WHILE num<=i DO SET mysum=mysum+num; SET num=num+1; END WHILE; RETURN mysum;END /- 调用SELECT myfun(100) - -触发器 关键字(TRIGGER)- 触发器(特殊的存储过程)- 1:自动调用的- 触发器也是数据对象(触发器按照执行的顺序分为 1:after 触发器 2:before触发器- 按照监听的动作分为 1:inser