mysql show para_温故而知新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;

十二.常用函数

数值型函数

字符串函数

日期和时间函数

聚合函数和流程控制函数

标签：温故而知新,SET,int,company,userId,user,MYSQL,SELECT

来源： https://www.cnblogs.com/vic-tory/p/12767891.html