SQL常用数据库语句-新建库,新建表,数据库表中插入数据,数据库表中删除数据,数据库表中更新数据

Cheryl

常用数据库语句

创建数据库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');

阅读 1.3k

Java技术栈 [链接]

13 声望
0 粉丝
0 条评论

Java技术栈 [链接]

13 声望
0 粉丝
文章目录
宣传栏