Mysql(增删改查)+视图+储存过程【常用】

最新推荐文章于 2023-06-17 10:00:00 发布

gcl_guyunlige

最新推荐文章于 2023-06-17 10:00:00 发布

阅读量521

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/gaokcl/article/details/83503896

版权

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

一， MySQL的触发器参考： https://www.cnblogs.com/jevo/p/3272505.html

二，MySQL的 replace 语句： https://www.cnblogs.com/sunss/p/4493803.html

当存在pk冲突的时候是先delete再insert PRIMARY KEY (`a`),--------------------------存在PK约束，主键约束
当存在uk冲突的时候是直接update UNIQUE KEY `uk_bc` (`b`,`c`)----------------唯一索引约束

三，mysql导入数据load data infile用法 https://blog.csdn.net/zhengfeng2100/article/details/53487736

四，MySql中 delimiter 详解 https://blog.csdn.net/yuxin6866/article/details/52722913

五，MySQL数据库通过cmd命令导入和导出sql文件 https://blog.csdn.net/jisuanjiguoba/article/details/79359439

六，mysql 数据库常用单词 https://www.cnblogs.com/wangyaoyu/p/7182755.html

*****************************************************************************************************

参考： https://blog.csdn.net/xiao__jia__jia/article/details/87890614

Mysql时间字段格式如何选择，TIMESTAMP，DATETIME，INT？

       首先 DATETIME 和 TIMESTAMP 类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为1970-01-01 08:00:01到2038-01-19 11:14:07。所以可以看到TIMESTAMP支持的范围比DATATIME要小,容易出现超出的情况.

       其次，TIMESTAMP类型在默认情况下，insert、update 数据时，TIMESTAMP列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。最主要的区别-受时区影响不同。timestamp会跟随设置的时区变化而变化，而datetime保存的是绝对值不会变化。
一个timestamp字段，一个datetime字段，修改时区SET TIME_ZONE = "america/new_york";后，timestamp字段的值变了!
      因此，如果应用场景有跨时区要求的要特别注意这点。

       第三，TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响。

       所以一般来说，我比较倾向选择DATETIME，至于你说到索引的问题，选择DATETIME作为索引，如果碰到大量数据查询慢的情况，也可以分区表解决。

      INT存时间戳。占用资源少，查询速度快。条件范围搜索使用between没什么问题。查询条件自由拼接。用int还是比较好的。如果你要在表示时间字段上建立索引，那么使用INT索引效率要高。

整型

最小整型       tinyint(m)                1个字节范围(-128~127)

小整型           smallint(m)            2个字节范围(-32768~32767)

中整型           mediumint(m)              3个字节范围(-8388608~8388607)

整型，默认11      int(m)              4个字节范围(-2147483648~2147483647)

大整型            bigint(m)              8个字节范围(+-9.22*10的18次方)

字符串型

char(n) 固定长度，最多255个字符

varchar(n) 固变长度，最多65535个字符

text 可变长度，最多65535个字符 长度较长的数据字段，包括 varchar / varbinay / text / blob

***************************************************************************************************

时间字段类型：

datetime： “yyyy-mm-dd hh:mm:ss”格式【最常用】 date("y-m-d H:i:s") php添加

timestamp： “yyyymmddhhmmss”格式表示的时间戳值 year： “yyyy”格式的年份值。

||| timestamp 19700101000000 到2037 年的某个时刻 4字节 数据库表中更新数据自动增加

字段类型      存储长度                           时间范围 
timestamp     4字节                  '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC  
datetime      8字节（5.7占5字节）     '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

date “1000-01-01”到“9999-12-31” 3字节

**************************************************************************************************

`dPublishTime` datetime(8) NOT NULL COMMENT '发布时间',

`dCreateTime` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

******************************************************************************************************************************************************

一，字段类型：

二，group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator '分隔符'] )

一，MySQL（一般）：

1，创建数据库：

SET FOREIGN_KEY_CHECKS=0;   // 修改外键约束（ Mysql中如果表和表之间建立的外键约束，则无法删除表及修改表结构 ）
DROP TABLE IF EXISTS `user`;  // DROP TABLE会永久性地取消表定义，取消各分区，并取消储存在这些分区中的所有数据

CREATE TABLE `user` (
  `iUserID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `sAccount` varchar(100) NOT NULL COMMENT '账号',
  `sPassword` varchar(100) NOT NULL COMMENT '密码',
  `dCreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `iStatus` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态字段：1-正常，2-删除',
  PRIMARY KEY (`iUserID`)  // 主键
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

--------------------------------------------------------------------------------------------------------
2，mysql常用函数


DISTINCT1      // 去重

in(1,2)        // 相当于  is_array() 或者 || 

concat()函数   // 将多个字符串连接成一个字符串。 

concat_ws()函数  //  和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符  

group_concat( [DISTINCT]  要连接的字段   [Order BY 排序字段 ASC/DESC]   [Separator '分隔符'] )

like:   select sRoleID,GROUP_CONCAT(iUserID) as UId from  reports  where iStatus=1  group by sRoleID;

---------------------------------------------------------------------------------------------------------



3,时间字段类型：

datetime： “yyyy-mm-dd hh:mm:ss”格式 【 最常用 】

timestamp： “yyyymmddhhmmss”格式表示的时间戳值   year： “yyyy”格式的年份值。

|||  timestamp 19700101000000 到2037 年的某个时刻 4字节

date “1000-01-01”到“9999-12-31” 3字节

*************************************************************************************

`dPublishTime` datetime NOT NULL COMMENT '发布时间',

`dCreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

*************************************************************************************

---------------------------------------------------------------------------------------------------------

4，插入数据库：

INSERT INTO `t_user` VALUES ('1', 'admin', '123456', '2018-01-01 09:00:00', '1');

---------------------------------------------------------------------------------------------------------

5，当数据库中存在某个记录时，执行这条语句会更新它，而不存在这条记录时，会插入它

CREATE TABLE `t_stock_chg` (
  `f_market` varchar(64) NOT NULL COMMENT '市场',
  `f_stockID` varchar(10) NOT NULL DEFAULT '' COMMENT '股票代码',
  `f_updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入时间戳',
  `f_name` varchar(16) DEFAULT NULL COMMENT '股票名称',
  PRIMARY KEY (`f_market`,`f_stockID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

语法：INSERT INTO tablename(field1,field2, field3, ...) VALUES(value1, value2, value3, ...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...;

INSERT INTO t_stock_chg(f_market, f_stockID, f_name) VALUES('SH', '600000', '白云机场') ON DUPLICATE KEY UPDATE f_market='SH', f_name='浦发银行';

---------------------------------------------------------------------------------------------------------

6.GROUP_CONCAT  ( [DISTINCT]  要连接的字段   [Order BY 排序字段 ASC/DESC]   [Separator '分隔符'] )

参考：https://www.cnblogs.com/jkko123/p/6294718.html

select sRoleID,GROUP_CONCAT(iUserID) as UId from  reports  where iStatus=1  group by sRoleID;

---------------------------------------------------------------------------------------------------------

7，修改字段值：

// concat_ws()函数  //  和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符  

UPDATE `graphicitem` SET memo = CONCAT_WS(' ',memo , '原创画稿') WHERE memo LIKE '%pop原创%';

UPDATE `fabricgallery_item` SET memo = CONCAT_WS(' ',memo , '原创画稿') WHERE memo LIKE '%pop原创%';

---------------------------------------------------------------------------------------------------------

8，增加字段，在  AFTER `sName`  后面：

ALTER TABLE `positions` ADD `sEnsName` varchar(255) NOT NULL COMMENT '英文职务名称' AFTER `sName` ;

---------------------------------------------------------------------------------------------------------

9, MySQL 修改字段类型或长度

 alter table 表名 modify column 字段名 类型;
 
---------------------------------------------------------------------------------------------------------
 
10,修改字段类型、设置默认值，以及添加注释：

ALTER TABLE m_cultureact_gzl MODIFY COLUMN SIGN INT(1) DEFAULT  1 COMMENT '为了给前台区分是列表订票还是票务中

===========================================================================================================

二，MySQL视图：

参考：https://blog.csdn.net/moxigandashu/article/details/63254901

      https://www.cnblogs.com/geaozhang/p/6792369.html

// 两张表联查，中文表report的数据库表字段的值不变，翻译表随着操作（增删改查）改变

// 强制使用ALGORITHM = MERGE函數

DROP VIEW `translate_view`;

CREATE ALGORITHM = MERGE VIEW `translate_view` (
 iTopicId,
 iOriginColumn,
 iGender,
 sIndustry,
 iSeason,
 dPubTime,
 sTitle,
 sImgPath,
 ml_id,
 ml_sTitle,
 ml_iTranslatorId,
 ml_iTranslateStatus,
 ml_dTranslateTime,
 ml_iVerifyId,
 ml_iVerifyStatus,
 ml_dVerifyTime,
 ml_iPublishId,
 ml_iPublishStatus,
 ml_dPublishTime
) AS (
 SELECT
  `A`.`iTopicId` AS `iTopicId`,
  `A`.`iOriginColumn` AS `iOriginColumn`,
  `A`.`iGender` AS `iGender`,
  `A`.`sIndustry` AS `sIndustry`,
  `A`.`iSeason` AS `iSeason`,
  `A`.`dPubTime` AS `dPubTime`,
  `A`.`sTitle` AS `sTitle`,
  `A`.`sImgPath` AS `sImgPath`,
  `B`.`id` AS `ml_id`,
  `B`.`sTitle` AS `ml_sTitle`,
  `B`.`iTranslatorId` AS `ml_iTranslatorId`,
  ifnull(`B`.`iTranslateStatus`, 2) AS `ml_iTranslateStatus`, // IFNULL(expr1,expr2) ;如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2
  `B`.`dTranslateTime` AS `ml_dTranslateTime`,
  `B`.`iVerifyId` AS `ml_iVerifyId`,
  `B`.`iVerifyStatus` AS `ml_iVerifyStatus`,
  `B`.`dVerifyTime` AS `ml_dVerifyTime`,
  `B`.`iPublishId` AS `ml_iPublishId`,
  `B`.`iPublishStatus` AS `ml_iPublishStatus`,
  `B`.`dPublishTime` AS `ml_dPublishTime`
 FROM
  (
   `report` `A`
   LEFT JOIN `report_translate` `B` ON (
    (
     `A`.`iTopicId` = `B`.`iTopicId`
    )
   )
  )
 WHERE
  (
   (`A`.`iStatus` = 0)
   AND (
    (`B`.`iSubTopicId` = 0)
    OR isnull(`B`.`iSubTopicId`)
   )
  )
);

=================================================================================================

三，储存过程（call）[ navicat 数据库下的 函数]

参考：https://www.cnblogs.com/cxxjohnson/p/5965194.html

// declare :  定义的变量类似java类中的局部变量，仅在类中生效。即只在存储过程中的begin和end之间生效

// 自定义变量：DECLARE   a INT ; SET a=100;    可用以下语句代替：DECLARE a INT DEFAULT 100;

// concat :   mysql CONCAT（）函数用于将多个字符串连接成一个字符串

// floor：函数只返回整数部分，小数部分舍弃  || round：函数四舍五入，大于0.5的部分进位，不到则舍弃


create procedure sp_name()  // 创建储存过程

----------------------------------------------

BEGIN
 DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';// 验证码字符串
 DECLARE return_str varchar(255) DEFAULT t;
 DECLARE i INT DEFAULT 0;
 WHILE i < l DO
  SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
  SET i = i +1;
    END WHILE;
 RETURN return_str;
END

--------------------------------------------
call sp_name()  // 调用储存过程

drop procedure sp_nam // 删除储存过程

gcl_guyunlige

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql(增删改查)+视图+储存过程【常用】

一， MySQL的触发器参考： https://www.cnblogs.com/jevo/p/3272505.html二，MySQL的 replace 语句： https://www.cnblogs.com/sunss/p/44...
复制链接

扫一扫