温故而知新MYSQL -云博客

温故而知新MYSQL

# 数据库 2024-05-05 12:47 0 33 来源：云博客

相关连接：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,userNameFROM `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 或者FALSESELECT * 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.companyIdSET c.companyName=‘BCD‘,u.userName=‘RoBin‘WHERE u.userId=‘1‘

六.删除

DELETE FROM `user` WHERE `userId`=‘1‘;TRUNCATE `user`;

七.视图

CREATE VIEW v_user_company ASSELECT userId,c.companyId FROM `user` AS u JOIN `company` AS c ON u.companyId=c.companyIdWHERE 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 intBEGINDECLARE count int ;SELECT COUNT(*) INTO count FROM `user`;RETURN count;END;SELECT fn_user_company();CREATE FUNCTION fn_user_company1(count int) RETURNS intBEGIN 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;