MySQL系列：基本操作（建库、建表、CURD、视图、存储过程、函数、游标）

相关连接：

mysql和sqlserver的区别：https://www.cnblogs.com/vic-tory/p/12760197.html

sqlserver基本操作：https://www.cnblogs.com/vic-tory/p/12760871.html

一.创建数据库

CREATE DATABASE IF NOT EXISTS student;

DROP DATABASE `student`;

二.表

CREATE TABLE IF NOT EXISTS `user`(
   userId int PRIMARY KEY AUTO_INCREMENT,
   userName varchar(20) NOT NULL,
   userSex char(1) CHECK(userSex='男' OR userSex='女'), #MYSQL不支持检查约束
   createTime timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   userPhone varchar(20) UNIQUE
  );

CREATE TABLE company (`companyId` int PRIMARY KEY AUTO_INCREMENT,`companyName` varchar(20) NOT NULL);

ALTER TABLE `user` ADD `userAge` int COMMENT '用户年龄'; #添加字段

ALTER TABLE `user` MODIFY COLUMN `userAge` int  NOT NULL COMMENT '用户年龄'; #修改字段

ALTER TABLE `user` DROP COLUMN `userAge`; #删除字段

ALTER TABLE `user` ADD CONSTRAINT FK_userId_companyId FOREIGN KEY (`companyId`) REFERENCES `company`(`companyId`);

DESC `user`;

DROP TABLE `user`;

三.添加

INSERT INTO `user` SET `userName`='Robin',`userSex`='男',  `userPhone`='123456789',`userAge`=18;

INSERT INTO `user`(`userName`,`userSex`,`userPhone`,`userAge`,`createTime`) VALUES ('Robin','男','123456','18','2019-04-06');

INSERT INTO `user` SELECT * FROM `#user`;#一般用于表的数据添加到主表

四.查询

SELECT MAX(`createTime`),MIN(`createTime`),COUNT(*) AS count,AVG(`userAge`),SUM(`userAge`),userId,userName
FROM `user` 
WHERE  `userSex` ='男' 
GROUP BY `userSex`,`userId`,`userName`
HAVING MIN(`createTime`)>'2019-04-05'
ORDER BY `userId` DESC /*ASC*/ ;

SELECT * FROM `user` WHERE `userId` IN /*NOT IN*/(SELECT `userId` FROM `user`);

SELECT * FROM `user` LIMIT 0,1;

SELECT * FROM `user` WHERE  EXISTS /*NOT EXISTS*/(SELECT * FROM  `user` WHERE `userId`='3'); #EXISTS 返回TRUE 或者FALSE

SELECT * FROM `user` u  LEFT JOIN  /*INNER JOIN,LEFT OUTER JOIN,RIGHT OUTER JOIN,RIGHT JOIN ,CROSS JOIN*/ `company` c ON u.companyId=c.companyId ;

SELECT * FROM `user` UNION /*UNION ALL*/ SELECT * FROM `user`;

SELECT DISTINCT `userName`,`userSex` FROM `user`;

五.更新

UPDATE `user` SET `userAge`=20 WHERE `userId` =1;

UPDATE `user` u JOIN `company` c ON u.companyId=c.companyId
SET c.companyName='BCD',u.userName='RoBin'
WHERE u.userId='1'

六.删除

DELETE FROM `user` WHERE `userId`='1';

TRUNCATE `user`;

七.视图

CREATE VIEW v_user_company AS
SELECT userId,c.companyId FROM `user` AS u JOIN `company` AS c 
ON  u.companyId=c.companyId
WHERE u.userId=1;

SELECT * FROM v_user_company;

ALTER VIEW v_user_company AS SELECT * FROM `user`;

DROP TABLE IF EXISTS v_user_company;

八.存储过程

CREATE PROCEDURE sp_user_company(
  IN inPara int,
  OUT outPara int,
  INOUT inOutPara int
  )
  BEGIN
   DECLARE count int ;
   SELECT COUNT(*) INTO count FROM `user`;
   SET outPara=count;
   SET inOutPara=count;
  END;

#SET @outPara=0; #可省略
#SET @inOutPara=0;
CALL sp_user_company(1,@outPara,@inOutPara);
SELECT @inOutPara;
SELECT @outPara;

#MYSQL 不提供存储过程中的代码修改

DROP PROCEDURE `sp_user_company`;

九.事务

SHOW GLOBAL VARIABLES LIKE 'autocommit';

SET GLOBAL AUTOCOMMIT=0;

SET GLOBAL AUTOCOMMIT=1;

START TRANSACTION;

ROLLBACK;

COMMIT;

十.自定义函数

CREATE FUNCTION fn_user_company() RETURNS int
BEGIN
DECLARE count int ;
SELECT COUNT(*) INTO count FROM `user`;
RETURN count;
END;

SELECT fn_user_company();

CREATE FUNCTION fn_user_company1(count int) RETURNS int
BEGIN
 RETURN count;
END;


SELECT fn_user_company1(1);

SHOW CREATE FUNCTION `fn_user_company`;

#函数不能内部的内容

DROP FUNCTION `fn_user_company`;

十一.游标

CREATE PROCEDURE `sp_logic`()
  BEGIN 
  DECLARE stuId int;
  DECLARE stuName varchar(20) CHARACTER SET UTF8;
  DECLARE done int DEFAULT 0;
  DECLARE myCursor CURSOR FOR  SELECT * FROM `user`;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  OPEN myCursor;
  SET total=0;
  c:LOOP
    FETCH myCursor INTO stuId,stuName;
    IF done=1 THEN
      LEAVE c;
    END IF ;
    SET total =total+1;
    END LOOP; 
  CLOSE myCursor;
  SELECT total;              
  END;