常用数据库语句
创建数据库guns
DROP DATABASE IF EXISTS guns;
CREATE DATABASE IF NOT EXISTS guns DEFAULT CHARSET utf8 COLLATE utf8_general_ci;DEFAULT CHARACTER SET utf8：设置数据库的默认数据库字符集为utf8；COLLATE utf8_general_ci:数据库校对规则。该三部分分别为数据库字符集、解释不明白、区分大小写
创建表
CREATE TABLE t_user(
id INT(11) auto_increment PRIMARY KEY COMMENT '主键ID',
username VARCHAR(255) NOT NULL UNIQUE COMMENT '用户名'
)auto_increment=1001;展示数库表状态
SHOW TABLE STATUS向表中插入数据
INSERT INTO t_user(username) VALUES('rachel');更新表数据
UPDATE t_user SET email = 'google@gmail.com' WHERE id = 1001;删除表数据
DELETE FROM t_user WHERE id = 1010新增表字段
ALTER TABLE t_user ADD loginacct VARCHAR(255);
ALTER TABLE t_user ADD userpswd VARCHAR(255);修改表字段
ALTER TABLE t_user CHANGE COLUMN station email LONGTEXT NULL DEFAULT NULL;修改表字段注释
ALTER TABLE tb_shop MODIFY COLUMN create_time datetime COMMENT '默认创建后不再改变';修改主键自增长适用于数据库没有值的时候，如果有值，可重置主键计数器，小心使用
ALTER TABLE t_permission DROP COLUMN id;
ALTER TABLE t_permission ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY first;修改自增长从4开始增长只能改大不能改小
alter table t_permission AUTO_INCREMENT=4; 修改字段默认值
ALTER TABLE t_permission ALTER pid SET DEFAULT 1修改表名
alter table NewTable rename to sys_user;在指定列后插入字段
ALTER TABLE `sys_acl_module` ADD COLUMN `operator` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '操作者' AFTER `remark`新建普通索引
ALTER TABLE product ADD INDEX index_name(`name`);
SHOW INDEX FROM product;添加外键
alter table testb add CONSTRAINT `FK_ID` FOREIGN KEY (bid) REFERENCES book(id);关闭mysql的严格模式
set sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";动态SQL
网址
https://www.cnblogs.com/ysoce...
navicat使用存储过程处理事务范例
在navicat中begin是关键字，不能使用它开启一个事务，可以考虑使用procedure实现事务
/*使用存储过程实现事务操作*/
DROP PROCEDURE IF EXISTS proc;
CREATE PROCEDURE proc()
BEGIN
DECLARE error_no INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_no = 1;
START TRANSACTION;
UPDATE product p SET p.price = p.price - 20.00 WHERE id = 10;
UPDATE product p SET p.price = p.price + 20.00 WHERE id = 11;
IF error_no = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END;
CALL proc();
SELECT * FROM product;Spring事务中propagation事务传播特性取值
- required-必须有事务，如果调用者已经有事务，不会重新开始事务，而是直接和这个事务一起
- supports-支持但是不主动要求
- required_new-和required不同的是子方法如果是这个配置必须创建一个独立的事务
- mandatory(义务)-有就加入没有就报错
- not_supported-不支持，谁调用我先挂起事务，我不加入事务，执行完后继续原来的事务
- nerver-绝不支持，谁支持我就报错
- nested-内嵌，如果有就创建内嵌的一个，没有就创建一个新的
- supports-支持但是不主动要求
事务的并发管理
/*查询事务的隔离级别*/
SELECT @@tx_isolation;
/*设置事务的隔离级别*/
-- 最低级别: READ UNCOMMITTED | 允许数据脏读
-- 使用最多的级别: READ COMMITTED | 不允许数据脏读，不允许数据重复读(事务之间的数据可交互，但必须commit，不然就是脏读)
-- 一般系统使用的最高级别: REPEATABLE READ | 允许数据数据重复读(即事务之间的数据不可交互)
-- 实际数据库支持的最高级别: SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;https://www.cnblogs.com/Zande...
SQL优化
https://www.cnblogs.com/dc-ea...
悲观锁和乐观锁
http://www.imooc.com/article/...
来自网友整理的数据库语句，不保证准确性
新建表：
create table [表名]
(
[自动编号字段] int IDENTITY (1,1) PRIMARY KEY ,
[字段1] nVarChar(50) default '默认值' null ,
[字段2] ntext null ,
[字段3] datetime,
[字段4] money null ,
[字段5] int default 0,
[字段6] Decimal (12,4) default 0,
[字段7] image null ,
)
删除表：
Drop table [表名]
插入数据：
INSERT INTO [表名] (字段1,字段2) VALUES (100,'51WINDOWS.NET')
删除数据：
DELETE FROM [表名] WHERE [字段名]>100
更新数据：
UPDATE [表名] SET [字段1] = 200,[字段2] = '51WINDOWS.NET' WHERE [字段三] = 'HAIWA'
新增字段：
ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL
删除字段：
ALTER TABLE [表名] DROP COLUMN [字段名]
修改字段：
ALTER TABLE [表名] ALTER COLUMN [字段名] NVARCHAR (50) NULL
重命名表：(Access 重命名表，请参考文章：在Access数据库中重命名表)
sp_rename '表名', '新表名', 'OBJECT'
新建约束：
ALTER TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= '2000-1-1')
删除约束：
ALTER TABLE [表名] DROP CONSTRAINT 约束名
新建默认值
ALTER TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT '51WINDOWS.NET' FOR [字段名]
删除默认值
ALTER TABLE [表名] DROP CONSTRAINT 默认值名sql综合运用
-- 函数，在SQL中，查询输出系统当前日期、当前年份、当前月份。
select date(now());
select year(now());
select month(now());
-- 熟悉提供的数据脚本的结构和数据，实现以下查询功能：
-- 1.分页，分页显示查询房屋出租信息
SELECT hd.DNAME as 区县, hs.SNAME as 街道, ht.HTNAME as 户型, hm.PRICE as 租金, hm.TOPIC 住址细分, hm.CONTENTS as 详情, hm.HTIME as 租出时间, hm.COPY as 住址副本
FROM hos_house hm
INNER JOIN sys_user u ON hm.UID = u.UID
INNER JOIN hos_type ht ON hm.HTID = ht.HTID
INNER JOIN hos_street hs ON hm.SID = hs.SID
INNER JOIN hos_district hd ON hs.SDID = hd.DID
LIMIT 5,5;
-- 2.表联结查询，查询指定客户（张三）发布的出租房屋信息
SELECT hd.DNAME as 区县, hs.SNAME as 街道, ht.HTNAME as 户型, hm.PRICE as 租金, hm.TOPIC 住址细分, hm.CONTENTS as 详情, hm.HTIME as 租出时间, hm.COPY as 住址副本
FROM hos_house hm
INNER JOIN sys_user u ON hm.UID = u.UID
INNER JOIN hos_type ht ON hm.HTID = ht.HTID
INNER JOIN hos_street hs ON hm.SID = hs.SID
INNER JOIN hos_district hd ON hs.SDID = hd.DID
WHERE u.UNAME = '张三';
-- 3.排序，以中文顺序排序按区县制作房屋出租清单
SELECT ht.HTNAME as 户型, u.UNAME as 户主姓名, hd.DNAME as 区县, hs.SNAME as 街道
FROM hos_house hm
INNER JOIN sys_user u ON hm.UID = u.UID
INNER JOIN hos_type ht ON hm.HTID = ht.HTID
INNER JOIN hos_street hs ON hm.SID = hs.SID
INNER JOIN hos_district hd ON hs.SDID = hd.DID
ORDER BY CONVERT(HTNAME USING GBK), hs.SNAME;
-- 4.综合运用表联结查询，子查询，聚合分组等技巧，按季度统计当年发布的房屋出租数量
SELECT r.`季度`, r.`区县`, r.`街道`, r.`户型`, r.`房屋数量` FROM (
SELECT * FROM (
SELECT *, count(*) as 房屋数量 FROM (
SELECT QUARTER(hm.HTIME) * 10 as priority, QUARTER(hm.HTIME) * 10 as sub_priority, QUARTER(hm.HTIME) as 季度, hd.DNAME as 区县, hs.SNAME as 街道, ht.HTNAME as 户型
FROM hos_house hm
INNER JOIN sys_user u ON hm.UID = u.UID
INNER JOIN hos_type ht ON hm.HTID = ht.HTID
INNER JOIN hos_street hs ON hm.SID = hs.SID
INNER JOIN hos_district hd ON hs.SDID = hd.DID
) sub_query
GROUP BY `季度`, `区县`, `街道`, `户型`
UNION ALL
-- 季度总计
SELECT QUARTER(hm.HTIME) * 10 + 2 as priority, QUARTER(hm.HTIME) * 10 as sub_priority, QUARTER(hm.HTIME) as 季度, '合计' as 区县, NULL as 街道, NULL as 户型, count(*) as 房屋数量
FROM hos_house hm
INNER JOIN sys_user u ON hm.UID = u.UID
INNER JOIN hos_type ht ON hm.HTID = ht.HTID
INNER JOIN hos_street hs ON hm.SID = hs.SID
INNER JOIN hos_district hd ON hs.SDID = hd.DID
GROUP BY `季度`
UNION ALL
-- 区县小计
SELECT QUARTER(hm.HTIME) * 10 as priority,QUARTER(hm.HTIME) * 10 + 1 as sub_priority, QUARTER(hm.HTIME) as 季度, hd.DNAME as 区县, '小计' as 街道, NULL as 户型, count(*) as 房屋数量
FROM hos_house hm
INNER JOIN sys_user u ON hm.UID = u.UID
INNER JOIN hos_type ht ON hm.HTID = ht.HTID
INNER JOIN hos_street hs ON hm.SID = hs.SID
INNER JOIN hos_district hd ON hs.SDID = hd.DID
GROUP BY `季度`, `区县`
) parent_query
GROUP BY `季度`, `区县`, `街道`, `户型`
ORDER BY `季度`, priority DESC, CONVERT(`区县` USING GBK) ASC, sub_priority DESC, CONVERT(`街道` using GBK) ASC, CONVERT(`户型` using GBK) ASC
) r;
-------------------------------分割线--------------------------
-- 题目sql
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 50712
Source Host : localhost:3306
Source Database : house
Target Server Type : MYSQL
Target Server Version : 50712
File Encoding : 65001
Date: 2019-09-28 22:39:19
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `hos_district`
-- ----------------------------
DROP TABLE IF EXISTS `hos_district`;
CREATE TABLE `hos_district` (
`DID` int(11) NOT NULL AUTO_INCREMENT,
`DNAME` varchar(20) NOT NULL,
PRIMARY KEY (`DID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of hos_district
-- ----------------------------
INSERT INTO `hos_district` VALUES ('1', '海淀区');
INSERT INTO `hos_district` VALUES ('2', '西城区');
INSERT INTO `hos_district` VALUES ('3', '东城区');
INSERT INTO `hos_district` VALUES ('4', '朝阳区');
INSERT INTO `hos_district` VALUES ('5', '景山区');
INSERT INTO `hos_district` VALUES ('6', '宣武区');
INSERT INTO `hos_district` VALUES ('7', '大兴');
INSERT INTO `hos_district` VALUES ('8', '丰台');
-- ----------------------------
-- Table structure for `hos_house`
-- ----------------------------
DROP TABLE IF EXISTS `hos_house`;
CREATE TABLE `hos_house` (
`HMID` int(11) NOT NULL AUTO_INCREMENT,
`UID` int(11) NOT NULL,
`DID` int(11) DEFAULT NULL,
`SID` int(11) NOT NULL,
`HTID` int(11) NOT NULL,
`PRICE` decimal(18,2) NOT NULL,
`TOPIC` varchar(20) NOT NULL,
`CONTENTS` varchar(100) NOT NULL,
`HTIME` datetime NOT NULL,
`COPY` varchar(20) DEFAULT NULL,
PRIMARY KEY (`HMID`),
KEY `FK_SID` (`SID`),
KEY `FK_HTID` (`HTID`),
CONSTRAINT `FK_HTID` FOREIGN KEY (`HTID`) REFERENCES `hos_type` (`HTID`),
CONSTRAINT `FK_SID` FOREIGN KEY (`SID`) REFERENCES `hos_street` (`SID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of hos_house
-- ----------------------------
INSERT INTO `hos_house` VALUES ('1', '1', null, '1', '2', '2600.00', '中关村', '中关村一条街', '2009-01-02 00:00:00', '中关村');
INSERT INTO `hos_house` VALUES ('2', '2', null, '2', '3', '3600.00', '苏州街', '苏州街一条街', '2009-01-03 00:00:00', '苏州街');
INSERT INTO `hos_house` VALUES ('3', '3', null, '3', '4', '4600.00', '万泉庄', '万泉庄一条街', '2009-01-04 00:00:00', '万泉庄');
INSERT INTO `hos_house` VALUES ('4', '1', null, '3', '2', '1500.00', '万泉庄附近', '万泉庄附近一条街', '2009-07-02 00:00:00', '万泉庄附近');
INSERT INTO `hos_house` VALUES ('5', '1', null, '5', '2', '2700.00', '东单', '东单很多美食', '2009-09-02 00:00:00', '东单');
INSERT INTO `hos_house` VALUES ('6', '3', null, '1', '2', '2600.00', '中关村', '中关村电脑城', '2009-04-01 00:00:00', '中关村');
INSERT INTO `hos_house` VALUES ('7', '4', null, '4', '1', '2000.00', '东四', '东四一条街', '2009-04-02 00:00:00', '东四');
INSERT INTO `hos_house` VALUES ('8', '5', null, '6', '3', '3600.00', '西四', '西四一条街', '2009-01-02 00:00:00', '西四');
INSERT INTO `hos_house` VALUES ('9', '5', null, '7', '2', '3600.00', '西单', '西单购物城', '2009-04-02 00:00:00', '西单');
INSERT INTO `hos_house` VALUES ('10', '6', null, '2', '2', '2600.00', '苏州街', '苏州街美食', '2009-02-02 00:00:00', '苏州街');
INSERT INTO `hos_house` VALUES ('11', '7', null, '8', '3', '2900.00', '朝阳东湖', '朝阳东湖一景', '2009-03-02 00:00:00', '朝阳');
INSERT INTO `hos_house` VALUES ('12', '8', null, '3', '1', '700.00', '万泉庄', '万泉庄一条街', '2009-05-02 00:00:00', '万泉庄');
INSERT INTO `hos_house` VALUES ('13', '3', null, '2', '3', '4200.00', '苏州街', '苏州街二条街', '2009-01-03 00:00:00', '苏州街');
INSERT INTO `hos_house` VALUES ('14', '4', null, '2', '3', '4100.00', '苏州街', '苏州街西街', '2009-01-03 00:00:00', '苏州街');
-- ----------------------------
-- Table structure for `hos_street`
-- ----------------------------
DROP TABLE IF EXISTS `hos_street`;
CREATE TABLE `hos_street` (
`SID` int(11) NOT NULL AUTO_INCREMENT,
`SNAME` varchar(100) NOT NULL,
`SDID` int(11) NOT NULL,
PRIMARY KEY (`SID`),
KEY `FK_TYPE` (`SDID`),
CONSTRAINT `FK_TYPE` FOREIGN KEY (`SDID`) REFERENCES `hos_district` (`DID`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of hos_street
-- ----------------------------
INSERT INTO `hos_street` VALUES ('1', '中关村', '1');
INSERT INTO `hos_street` VALUES ('2', '苏州街', '1');
INSERT INTO `hos_street` VALUES ('3', '万泉庄', '1');
INSERT INTO `hos_street` VALUES ('4', '东四', '3');
INSERT INTO `hos_street` VALUES ('5', '东单', '3');
INSERT INTO `hos_street` VALUES ('6', '西四', '2');
INSERT INTO `hos_street` VALUES ('7', '西单', '2');
INSERT INTO `hos_street` VALUES ('8', '东湖', '4');
INSERT INTO `hos_street` VALUES ('9', '八里庄', '4');
INSERT INTO `hos_street` VALUES ('10', '双井', '5');
INSERT INTO `hos_street` VALUES ('11', '陶然亭', '5');
INSERT INTO `hos_street` VALUES ('12', '南菜园', '6');
INSERT INTO `hos_street` VALUES ('13', '兴丰街', '7');
INSERT INTO `hos_street` VALUES ('14', '黄村', '7');
INSERT INTO `hos_street` VALUES ('15', '南苑街', '8');
INSERT INTO `hos_street` VALUES ('16', '东铁营', '8');
-- ----------------------------
-- Table structure for `hos_type`
-- ----------------------------
DROP TABLE IF EXISTS `hos_type`;
CREATE TABLE `hos_type` (
`HTID` int(11) NOT NULL AUTO_INCREMENT,
`HTNAME` varchar(20) NOT NULL,
PRIMARY KEY (`HTID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of hos_type
-- ----------------------------
INSERT INTO `hos_type` VALUES ('1', '一室一卫');
INSERT INTO `hos_type` VALUES ('2', '一室一厅');
INSERT INTO `hos_type` VALUES ('3', '两室一卫');
INSERT INTO `hos_type` VALUES ('4', '两室一厅');
INSERT INTO `hos_type` VALUES ('5', '三室一厅');
INSERT INTO `hos_type` VALUES ('6', '三室两厅');
-- ----------------------------
-- Table structure for `sys_user`
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`UID` int(11) NOT NULL AUTO_INCREMENT,
`UNAME` varchar(20) NOT NULL,
`UPASSWORD` varchar(20) NOT NULL,
PRIMARY KEY (`UID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES ('1', '张三', 's217701');
INSERT INTO `sys_user` VALUES ('2', '李四', 's217702');
INSERT INTO `sys_user` VALUES ('3', '王鑫', 's217703');
INSERT INTO `sys_user` VALUES ('4', '张建', 's217704');
INSERT INTO `sys_user` VALUES ('5', '李剑', 's217705');
INSERT INTO `sys_user` VALUES ('6', '蒋以然', 's217706');
INSERT INTO `sys_user` VALUES ('7', '王晓超', 's217707');
INSERT INTO `sys_user` VALUES ('8', '张冬雪', 's217708');
INSERT INTO `sys_user` VALUES ('9', '孙鹏', 's217709');
INSERT INTO `sys_user` VALUES ('10', '蒋连昌', 's217710');
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。