温故而知新MYSQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了温故而知新MYSQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3849字，纯文字阅读大概需要6分钟。

内容图文

相关连接：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;

十二.常用函数

数值型函数

字符串函数

 日期和时间函数

 聚合函数和流程控制函数

内容总结

以上是互联网集市为您收集整理的温故而知新MYSQL全部内容，希望文章能够帮你解决温故而知新MYSQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端