溫故而知新MYSQL

一.建立資料庫

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;      

十二.常用函數

數值型函數

字元串函數

 日期和時間函數

 聚合函數和流程控制函數