数据库备份,高级查询

1、        修改表:

知识点:EXISTS子查询、NOT EXISTS子查询、分页查询、UNION联合查询

知识点:EXISTS子查询、NOT EXISTS子查询、分页查询、UNION联合查询

有关MySQL 事务,视图,索引,数据库备份,苏醒,mysql数据库备份

CREATE DATABASE `mybank`;
/*创建表*/
USE mybank;
CREATE TABLE `bank`(
    `customerName` CHAR(10),        #用户名
    `currentMoney` DECIMAL(10,2)         #日前余额
);
/*安排数据*/
INSERT INTO `bank` (`customerName`,`currentMoney`) VALUES
(‘张三’,1000);
INSERT INTO `bank` (`customerName`,`currentMoney`) VALUES
(‘李四’,1);

 

  2. –转账测试:张三希望因而转载,直接汇钱给李四500元

UPDATE `bank` SET `currentMoney` =`currentMoney`-500
    WHERE `customerName` = ‘张三’;
UPDATE `bank` SET `currentMoney` =`currentMoney`+500
    WHERE `customerName` = ‘李四’;

 

 3. 执行事务 并交给业务

USE mybank;

/*安装结果集以gbk编码格式呈现*/
SET NAMES gbk;

/*始发工作*/
BEGIN;
UPDATE `bank` SET `currentMoney` =`currentMoney`-500
    WHERE `customerName` = ‘张三’;
UPDATE `bank` SET `currentMoney` =`currentMoney`+500
    WHERE `customerName` = ‘李四’;
COMMIT;        #交给业务,写入硬盘

 

 

 4. 起来工作  回滚事务
BEGIN;
UPDATE `bank` SET `currentMoney` = `currentMoney` -1000 WHERE
`customerName` = ‘张三’;
/*回滚*/
ROLLBACK;

 

5.安装自动提交关闭或开启

/*闭馆工作自动提交*/
SET autocommit = 0;

/*转账*/
UPDATE `bank` SET `currentMoney` =`currentMoney`-500
    WHERE `customerName` = ‘张三’;
UPDATE `bank` SET `currentMoney` =`currentMoney`+500
    WHERE `customerName` = ‘李四’;
    /*提交*/
COMMIT;
UPDATE `bank` SET `currentMoney` = `currentMoney` -1000 WHERE
`customerName` = ‘张三’;

/*回滚事务*/
ROLLBACK;

 

 6. 创立视图

/*时下数据库*/
USE myschool;

DROP VIEW IF EXISTS `view_student_result`;

/*开创视图*/
CREATE VIEW `view_student_result`
AS

/*亟待创立视图的数额*/
SELECT `studentName` AS 姓名,student.`studentNo`
学号,`studentResult` AS 成绩,`subjectName` AS 课程名称.`examDate`
AS 考试日期
FROM `student`
INNER JOIN `result` ON `student`.`studentNo` =
`result`.`studentNo`
INNER JOIN `subject` ON `result`.`subjectNo` =
`subject`.`studentNo`
WHERE `subject`.`subjectNo`=(
    SELECT `subjectNo` FROM `subject` WHERE `subjectName` = ‘Logic
Java’
)AND `examDate` =(
    SELECT MAX(`examDate`) FROM `result`.`subject` WHERE
`result`.`subjectNo` = `subject`.`subjectNo`
    AND `subjectName` = ‘Logic Java’
);

 

7. 创立索引

USE myschool;

/*开创索引*/
CREATE INDEX `index_student_studentName`
ON `student`(`studentName`);

 

 

8. 用到DOS命令备份数据库

mysqldump -u username -h -ppassword

dbname[tbname1 [,tbname2]……..] > filename.sql

 

例:

mysqldump -u root -p myschool student > d:\backup\myschool.sql

EnterPassword:

 

9. 应用后DOS命令恢复生机数据库

  1) .复苏数据库前先在MySQL服务器中开立异的数目库.

  2). 执行 mysql -u username -p [dbname]  <filename.sql

 

例:

mysql -u root -p schoolDB <  d:\backup\myschool.sql

 

10. 选择source命令恢复生机数据库

  语法:   source  filename;

 

例:

/*创设数据库*/
CREATE DATABASE myschoolDB2;
USE myschoolDB2;
/*还原数据库*/
source ‘d:\backup\myschool.sql’;

 

11. 将表数据导出到文本文件

 语法: select 列名 from 表名 [where 条件,条件2……]

into outfile ‘filename’ ;

 

例:

SELECT * FROM `result` WHERE `subjectNo` = 

  (SELECT  `subjectNo` FROM `subject`  WHERE `subjectName` =
‘Logic Java’)

  INTO OUTFILE ‘d: /backup/result.txt’;

 

 

12. 将文件文件导入到数据表

语法: LOAD DATA INFILE ‘filename’ INTO TABLE ‘tablename ‘;

  1) . 先创建导入数据的表结构

    USE myschoolDB2;
    /*创建result表结构*/
    CREATE TABLE `result`
数据库备份,高级查询。    (
    `studentNo` INT(4) NOT NULL COMMENT ‘学号’,
    `subjectNo` INT(4) NOT NULL COMMENT ‘课程编号’,
    `examDate` DATETIME NOT NULL COMMENT ‘考试日期’,
    `studentResult` INT(4) NOT NULL COMMENT ‘考试成绩’,
    PRIMARY KEY (`studentNo`,`subjectNo`,`examDate`)
    )ENGINE = INNODB DEFAULT CHARSET utf8;

  2).导入数据
    LOAD DATA INFILE ‘路径.txt’ INTO TABLE result;
    /*查看result表数据*/
    SELECT * FROM result;

 

事务,视图,索引,数据库备份,复苏,mysql数据库备份 CREATE DATABASE
`mybank`; /*创建表*/ USE mybank; CREATE TABLE `bank`(
`customerName` CHAR(10), #用户…

(1)修改表名语法:

ALTER TABLE <旧表名> RENAME [ TO ] <新表名>;

注意:其中[TO]为可选参数,使用与否不影响结果,仅修改表名,表结构不变;

(2)添加字段语法:

ALTER TABLE 表名 ADD 字段名 数据类型 [属性];

(3)修改字段语法:

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

其中,”原字段名“指修改前的字段名,“ 新字段名”指修改后的字段名,“数据类型”指修改后的数据类型,

如果不需要修改数据类型,则和原数据类型保持一致,但“数据类型”不能为空。

注意:由于不同的类型的数据储存方式和长度不同,修改数据类型可能会影响数据表中已有的数据,因此,此种情况下不应轻易修改数据类型。

(4)删除字段语法:

ALTER TABLE 表名 DROP字段名;

Eg:

DROP TABLE IF EXISTS `demo01`;

#创建表

CREATE TABLE  `demo01`(

       `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,

       `name` VARCHAR(8) NOT NULL

);

#修改表名

ALTER TABLE `demo01` RENAME `demo02`;

#添加字段

ALTER TABLE ` demo02 ` ADD `password` VARCHAR(32) NOT NULL;

#修改字段

ALTERTABLE ` demo02 ` CHANGE `name` `username`CHAR(10) NOT NULL;

#删除字段

ALTERTABLE ` demo02 ` DROP `password`;

一.单词部分

一.单词部分

2、        添加主外键:

①exist存在②temp临时的③district区域

①exist存在②temp临时的③district区域

(1)添加主键约束语法:

ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名(主键字段);

Eg:设置grade表中gradeId字段为主键

ALTER TABLE `grade` ADD CONSTRAINT `pk_grade` PRIMARY KEY `grade`(`gradeId`);

(2)添加外键约束语法:

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFE RENCES 关联表名(关联字段);

Eg:将student表的gradeId字段和grade表的gradeId字段建立外键关联:

ALTER TABLE `student`  ADD  CONSTRAINT fk_student_grade FOREIGN KEY(`gradeId`) REFERENCES `grade` (`gradeId`);

提示:在MySQL中,InnoDB储存类型的表支持外键,

MyISAM储存类型的表不支持外键,因此对于MySISAM储存类型的表,也可以通过建立逻辑关联的方式保证数据的完整性和一致性,

这种关联实际上是依照设计之初就定义好的固有逻辑,按照这种固有逻辑存取数据即可,不需要在数据库层面进行“硬绑定”。

④content内容⑤temporary暂时的

④content内容⑤temporary暂时的

3、      DML(数据操作语言):

二.预习部分

二.预习部分

(1)插入数据记录:

(A)插入单行数据记录语法:

        INSERT INTO 表名 [(字段名列表)] VALUES(值列表);

        Eg:

           INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`)

           VALUES(‘123′,’黄小平’,1,’13956799999′,’1996-5-8′);

其中:

1.表的字段名是可选的,如果省略,则依次插入所有字段。

2.多个列表和多个值之间使用逗号分隔。

3.值列表必须和字段名列表数量相同,且数据类型相符。

4.如果插入的是表中部分数据,字段名列表必须填写。

 (B)插入多行数据语法:在MySQL中INSERT语句支持一次插入多条记录,插入时可指定多个值列表,每个值列表之间逗号隔开。

          INSERT INTO 新表(字段名列表)VALUES(值列表1),(值列表2),……(值列表n);

          注意:插入数据时需要指定具体字段名。

         Eg:

           INSERT INTO `subject`(`subjectNo`,`subjectName`,`classHour`,`gradeID`)

           VALUES(1,’Logic Java’,220,1),(2,’HTML’,160,1),(3,’Java OOP’,230,2);

 

          注意:为避免表结构发生变化引发的错误,建议插入数据时写明具体字段名!

          插入时间类型也需要 ‘ ’ 。

(2)将查询结果插入到新表语法:

        CREATE TABLE 新表(SELECT 字段1,字段2,……….FROM 原表);

         注意:新表无需事先创建,如新表已存在,将会报错!

(3)更新数据记录语法:

         UPDATE 表名 SET 字段1=值,字段2=值,。。。。字段n=值[WHERE 条件];

(4)删除数据记录语法:

         DELETE FROM 表名 [WHERE 条件];

          除此之外,也可以使用TRUNCATE语句删除表中所有行。语法:

          TRUNCATE TABLE 表名;

          注意:TRUNCATE 语句实现删除表中所有数据,删除后将重置自增列,表结构及其字段、约束、索引保持不变执行速度比DELETE语句快。

 

SQL Sever数据库与MySQL数据库把数据插入新表语法对比:

 

1.表连接都得以用子查询替换吗

1.表连接都得以用子查询替换吗

4、数据查询:

是的

是的

         ●查询回看:SQL Server中查询语句在MySQL中相同协理;

2.检测某列是还是不是存在某个范围可以在子查询中应用什么主要字

2.检测某列是不是留存某个范围可以在子查询中行使什么首要字

SQL 瑟维斯中的数据查询语法:

EXISTS

EXISTS

SELECT<字段名列表>

3.怎么sql语句能够嵌套子查询

3.什么sql语句可以嵌套子查询

FRMO<表名或视图>

较复杂的数目查询语句  需求三个表的数码的时候

较复杂的多少查询语句  须求五个表的多寡的时候

[WHERE <查询条件>]

子查询可以出未来此外表明式出现的职位

子查询可以出现在其余表达式出现的职责

[GROUP BY<分组字段名>]

三.陶冶部分

三.操练部分

[HAVING<条件>]

1.查询S2学童考试战绩音讯

1.查询S2学员考试战表音信

[OGDER BY<排序的字段名>[ASC 或 DESC]]

#上机1
SELECT `studentNo`,`subjectNo`,`studentResult`,`exameDate` FROM
`result`
WHERE EXISTS(SELECT `studentNo` FROM `student` WHERE gradeId=2)
AND studentNo IN(SELECT `studentNo` FROM `student` WHERE
gradeId=2)

#上机1
SELECT `studentNo`,`subjectNo`,`studentResult`,`exameDate` FROM
`result`
WHERE EXISTS(SELECT `studentNo` FROM `student` WHERE gradeId=2)
AND studentNo IN(SELECT `studentNo` FROM `student` WHERE
gradeId=2)

 

2.营造学生成绩单

2.创设学生战表单

Eg:

#上机2
SELECT `studentName` AS 姓名,`gradeName` AS
课程所属年级,`subjectName` AS 课程名称,`exameDate` AS 考试日期 FROM
(
SELECT `studentName`,`gradeName`,`subjectName`,`exameDate` FROM
`grade` AS gr,`result` AS re,`student` AS stu,`subject` AS sub
WHERE gr.`gradeID`=stu.`gradeID` AND
re.`studentNo`=stu.`studentNo`
AND re.`subjectNo`=sub.`subjectNo`
) AS tempt;

#上机2
SELECT `studentName` AS 姓名,`gradeName` AS
课程所属年级,`subjectName` AS 课程名称,`exameDate` AS 考试日期 FROM
(
SELECT `studentName`,`gradeName`,`subjectName`,`exameDate` FROM
`grade` AS gr,`result` AS re,`student` AS stu,`subject` AS sub
WHERE gr.`gradeID`=stu.`gradeID` AND
re.`studentNo`=stu.`studentNo`
AND re.`subjectNo`=sub.`subjectNo`
) AS tempt;

SELECT
`studentNo`,`studentName`,`phone`,`address`,`bornDate`

3.总括Logic Java课程以来一回试验学生应到人数,实到人数和缺考人数

3.计算Logic Java课程以来四回试验学生应到人数,实到人数和缺考人数

FROM `student`

领到结果到临时表

领取结果到临时表

WHERE `gradeId` = 1

#上机3
#select subjectNo from `subject` where `subjectName`=’Logic Java’;

#上机3
#select subjectNo from `subject` where `subjectName`=’Logic Java’;

ORDER BY studentNo;

#select max(`exameDate`) from result inner join `subject` on
`result`.`subjectNo`=`subject`.`subjectNo`
#where `subjectName`=’Logic Java’;

#select max(`exameDate`) from result inner join `subject` on
`result`.`subjectNo`=`subject`.`subjectNo`
#where `subjectName`=’Logic Java’;

其中:

#select `gradeID` from `subject` where `subjectName`=’Logic
Java’;
#应到人数
SELECT COUNT(*) AS 应到人数 FROM student
WHERE `gradeID`=(SELECT `gradeID` FROM `subject` WHERE
`subjectName`=’Logic Java’);
#实到人数
SELECT COUNT(*) AS 实到总人口 FROM result
WHERE `subjectNo`=(SELECT subjectNo FROM `subject` WHERE
`subjectName`=’Logic Java’)
AND exameDate=(SELECT MAX(`exameDate`) FROM result INNER JOIN
`subject` ON `result`.`subjectNo`=`subject`.`subjectNo`
WHERE `subjectName`=’Logic Java’);
#缺考人数
SELECT((SELECT COUNT(*) FROM student
WHERE `gradeID`=(SELECT `gradeID` FROM `subject` WHERE
`subjectName`=’Logic Java’))-
(SELECT COUNT(*) FROM result
WHERE `subjectNo`=(SELECT subjectNo FROM `subject` WHERE
`subjectName`=’Logic Java’)
AND exameDate=(SELECT MAX(`exameDate`) FROM result INNER JOIN
`subject` ON `result`.`subjectNo`=`subject`.`subjectNo`
WHERE `subjectName`=’Logic Java’))) AS 缺考人数;

#select `gradeID` from `subject` where `subjectName`=’Logic
Java’;
#应到人数
SELECT COUNT(*) AS 应到人数 FROM student
WHERE `gradeID`=(SELECT `gradeID` FROM `subject` WHERE
`subjectName`=’Logic Java’);
#实到人数
SELECT COUNT(*) AS 实到总人口 FROM result
WHERE `subjectNo`=(SELECT subjectNo FROM `subject` WHERE
`subjectName`=’Logic Java’)
AND exameDate=(SELECT MAX(`exameDate`) FROM result INNER JOIN
`subject` ON `result`.`subjectNo`=`subject`.`subjectNo`
WHERE `subjectName`=’Logic Java’);
#缺考人数
SELECT((SELECT COUNT(*) FROM student
WHERE `gradeID`=(SELECT `gradeID` FROM `subject` WHERE
`subjectName`=’Logic Java’))-
(SELECT COUNT(*) FROM result
WHERE `subjectNo`=(SELECT subjectNo FROM `subject` WHERE
`subjectName`=’Logic Java’)
AND exameDate=(SELECT MAX(`exameDate`) FROM result INNER JOIN
`subject` ON `result`.`subjectNo`=`subject`.`subjectNo`
WHERE `subjectName`=’Logic Java’))) AS 缺考人数;

1.字段名列表为要询问的字段名、各字段之间利用逗号分隔、若查询表中兼有字段、则接纳“* ”号表示。

/*select studentName,student.studentNo,studentResult
from student,result
where student.`studentNo`=result.`studentNo`*/
#添加到表
DROP TABLE IF EXISTS tempResult;
CREATE TABLE tempResult(
SELECT studentName,student.studentNo,studentResult
FROM student,result
WHERE student.`studentNo`=result.`studentNo`
)

/*select studentName,student.studentNo,studentResult
from student,result
where student.`studentNo`=result.`studentNo`*/
#增加到表
DROP TABLE IF EXISTS tempResult;
CREATE TABLE tempResult(
SELECT studentName,student.studentNo,studentResult
FROM student,result
WHERE student.`studentNo`=result.`studentNo`
)

2.FROM后的表名为要查询的多寡的来自,可以单个能够八个。

4.分页询问突显出租房屋音讯

4.分页询问展现出租房屋音讯

3.WHERE子句为可拔取,指定询问的尺度。

#上机4
DROP DATABASE IF EXISTS `house`;

#上机4
DROP DATABASE IF EXISTS `house`;

4.GROUP BY子句表明查询出来的数量按指定字段举行分组。

CREATE DATABASE house;
USE house;
#客户消息表
DROP TABLE IF EXISTS `sys_user`;

CREATE DATABASE house;
USE house;
#客户音信表
DROP TABLE IF EXISTS `sys_user`;

5.HAVING子句用以筛选组。

CREATE TABLE `sys_user`(

CREATE TABLE `sys_user`(

6.OGDER
BY子句指定按什么顺序突显查询出来的数目,什么字段的升序(ASC)或降序(DESC)

`uid` INT(4) NOT NULL COMMENT ‘客户编号’ AUTO_INCREMENT PRIMARY KEY,

`uid` INT(4) NOT NULL COMMENT ‘客户编号’ AUTO_INCREMENT PRIMARY KEY,

          ●LIMIT子句:MySQL查询语句中动用LIMIT子句限制结果集;

`uName` VARCHAR(50) COMMENT ‘客户姓名’,

`uName` VARCHAR(50) COMMENT ‘客户姓名’,

SELECT  <字段名列表>

FROM  <表名或视图>

[WHERE  <查询条件>]

[GROUP BY <分组的字段名>]

[ORDER BY  <排序的列名> [ASC 或DESC]]

[LIMIT [位置偏移量, ]行数];

其中:

  1. 位子偏移量:指从结果集中第几条数据开始显示(第一条记录的位置偏移量是:0,第二条记录的位置偏移量是:1,…………),

此参数可选,当省略是从第一条记录开始显示。

2.行数:指显示记录的条数。

`uPassWord` VARCHAR(50) COMMENT ‘客户密码’
);

`uPassWord` VARCHAR(50) COMMENT ‘客户密码’
);

5、常用函数:

#区县音信表
DROP TABLE IF EXISTS `hos_district`;

#区县音信表
DROP TABLE IF EXISTS `hos_district`;

聚合函数:

AVG(); 作用:返回某字段的平均值。

COUNT(); 作用:返回某字段的行数。

MAX(); 作用:返回某字段的最大值。

MIN(); 作用:返回某字段的最小值。

SUM(); 作用:返回某字段的和。

数学函数:

CEIL(x);作用:返回大于或等于数值 x 的最小整数。

举例:SELECT CEIL (2.3);返回:3

FLOOR(x);作用:返回小于或等于数值 x 的最大整数

举例:SELECT FLOOR(2.3);返回:2

RAND();作用:返回0~1的随机数。

举例:SELECT RAND();返回:0.59464161

CREATE TABLE `hos_district`(

CREATE TABLE `hos_district`(

  • ●字符串函数:

`did` INT(4) NOT NULL COMMENT ‘区县编号’ AUTO_INCREMENT PRIMARY KEY,

`did` INT(4) NOT NULL COMMENT ‘区县数码’ AUTO_INCREMENT PRIMARY KEY,

CONCAT(str1,str2,…,strln);作用:链接字符串str1,str2…..strln为一个完整的字符串。

举例:SELECT CONCAT("My","S","QL");   返回:MySQl

INSERT(str,pos,len,newstr);作用:将字符串 str 从 pos 位置开始,len 个字符长的子串替换为字符串 newstr

举例:SELECT INSERT(’这是SQL Server 数据库’,3,10,’MySQL’);  返回:这是MySQl数据库

LOWER(str);作用:将字符串 str 中所有字符变为小写

举例:SELECT LOWER(‘MySQL’);  返回:mysql

UPPER(str);作用:将字符串 str 中所有字符变为大写

举例:SELECT UPPER(‘MySQL’);   返回:MYSQL

SUBSTRING(str,num,len);作用:返回字符串 str 的第num 个位置开始长度为len 的子字符串

举例:SELECT SUBSTRING(‘JavaMySQLOracle’,5,5);  返回:MySQL

`dName` VARCHAR(50) NOT NULL COMMENT ‘区县名称’
);

`dName` VARCHAR(50) NOT NULL COMMENT ‘区县名称’
);

  • ●时间日期函数:

#街道信息表那里有一个外键
DROP TABLE IF EXISTS `hos_street`;

#马路消息表这里有一个外键
DROP TABLE IF EXISTS `hos_street`;

CURDATE();作用:获取当前日期

举例:SELECT CURDTE();   返回:2016-08-08

CURTIME();作用:获取当前时间

举例:SELECT CURTIME();  返回:19:19:26

NOW();作用:获取当前日期和时间

举例:SELECT NOW();  返回:2016-08-08 19:19:26

WEEK(date);作用:返回日期date为一年中的第几周

举例:SELECT WEEK(NOW());   返回:26

YEAR(date);作用:返回日期date的年份

举例:SELECT YEAR(NOW()); 返回:2016

HOUR(time);作用:返回时间按 time 的小时值

举例:SELECT HOUR(NOW());返回:9

MINUTE(time);作用:返回时间 time 的分钟值

举例:SELECT MINUTE(NOW()); 返回:43

DATEDIFF(date1,date2);

作用:返回日期参数 date1 和 date2 之间相隔的天数。

举例:SELECT DATEDIFF(NOW(),‘2018-8-8’);返回:2881

ADDDATE(date,n);作用:计算日期参数 date 加上 n 天后的日期。

举例:SELECT ADDDATE(NOW(),5);

返回:2016-09-02 09:37:07

CREATE TABLE `hos_street`(

CREATE TABLE `hos_street`(

6、子查询:以询问为查询条件的询问

`sid` INT(4) NOT NULL COMMENT ‘大街编号’ AUTO_INCREMENT PRIMARY KEY,

`sid` INT(4) NOT NULL COMMENT ‘大街编号’ AUTO_INCREMENT PRIMARY KEY,

      Eg1:

`sName` VARCHAR(50) COMMENT ‘大街名称’,

`sName` VARCHAR(50) COMMENT ‘大街名称’,

  eg:

`sDid` INT(4) NOT NULL COMMENT ‘区县编号’
);

`sDid` INT(4) NOT NULL COMMENT ‘区县数码’
);

 
  997755.com澳门葡京 1

#房子类型表
DROP TABLE IF EXISTS `hos_type`;

#房屋类型表
DROP TABLE IF EXISTS `hos_type`;

997755.com澳门葡京 2

CREATE TABLE `hos_type`(

CREATE TABLE `hos_type`(

一路顺风情势二:选择子查询已毕:

`hTid` INT(4) NOT NULL COMMENT ‘房屋类型编号’ AUTO_INCREMENT PRIMARY
KEY,

`hTid` INT(4) NOT NULL COMMENT ‘房屋类型编号’ AUTO_INCREMENT PRIMARY
KEY,

 997755.com澳门葡京 3

`htName` VARCHAR(50) NOT NULL COMMENT ‘房屋类型名称’
);

`htName` VARCHAR(50) NOT NULL COMMENT ‘房屋类型名称’
);

  1、 子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE
语句或其余子查询中的查询

#租借房舍音信表
DROP TABLE IF EXISTS `hos_house`;

#租借房舍音讯表
DROP TABLE IF EXISTS `hos_house`;

   2、子查询在WHERE语句中的一般用法

CREATE TABLE `hos_house`(

CREATE TABLE `hos_house`(

子查询语法:

`hMid` INT(4) NOT NULL COMMENT ‘出租房舍编号’ AUTO_INCREMENT PRIMARY
KEY,

`hMid` INT(4) NOT NULL COMMENT ‘出租房舍编号’ AUTO_INCREMENT PRIMARY
KEY,

    SELECT .... FROM
表1 WHERE 字段1 相比较运算符(子查询);

`uid` INT(4) NOT NULL COMMENT ‘客户编号’,

`uid` INT(4) NOT NULL COMMENT ‘客户编号’,

其中:

`sid` INT(4) NOT NULL COMMENT ‘区县数码’,

`sid` INT(4) NOT NULL COMMENT ‘区县数码’,

子查询语句必须放置在一对圆括号内。

`hTid` INT(4) NOT NULL COMMENT ‘房屋类型编号’,

`hTid` INT(4) NOT NULL COMMENT ‘房屋类型编号’,

正如运算符包涵:>,=,<,>=,<=。

`price` DECIMAL NOT NULL COMMENT ‘每月租金’,

`price` DECIMAL NOT NULL COMMENT ‘每月租金’,

外界询问称为父查询,圆括号中放置的询问称为子查询。

`topic` VARCHAR(50) NOT NULL COMMENT ‘标题’,

`topic` VARCHAR(50) NOT NULL COMMENT ‘标题’,

实施时,先执行子查询,在执行父查询,再次来到再次来到值。

`contents` VARCHAR(255) NOT NULL COMMENT ‘描述’,

`contents` VARCHAR(255) NOT NULL COMMENT ‘描述’,

                   
专注;将子查询和比较运算符联合利用,必须有限支撑子查询再次来到的值无法多于一个

`hTime` TIMESTAMP NOT NULL COMMENT ‘揭橥时间’ DEFAULT NOW(),

`hTime` TIMESTAMP NOT NULL COMMENT ‘公布时间’ DEFAULT NOW(),

     Eg2: 使用子查询替换表连接;

`copy` VARCHAR(255) NOT NULL COMMENT ‘备注’
);

`copy` VARCHAR(255) NOT NULL COMMENT ‘备注’
);

 997755.com澳门葡京 4997755.com澳门葡京 5

#逐条约束音信

#依次约束消息

 

#街道音讯的区县数码外键id
ALTER TABLE `hos_street` ADD CONSTRAINT fk_stree_distr
FOREIGN KEY (`sDid`) REFERENCES `hos_district` (`did`);

#街道消息的区县编号外键id
ALTER TABLE `hos_street` ADD CONSTRAINT fk_stree_distr
FOREIGN KEY (`sDid`) REFERENCES `hos_district` (`did`);

  兑现形式一:选取表连接 :内连续(等值连接)

#出租屋消息和一一表的牵连
ALTER TABLE `hos_house` ADD CONSTRAINT fk_house_user
FOREIGN KEY (`uid`) REFERENCES `sys_user` (`uid`);

#出租屋音讯和顺序表的维系
ALTER TABLE `hos_house` ADD CONSTRAINT fk_house_user
FOREIGN KEY (`uid`) REFERENCES `sys_user` (`uid`);

SELECT `studentName` FROM `student` stu

ALTER TABLE `hos_house` ADD CONSTRAINT fk_house_street
FOREIGN KEY (`sid`) REFERENCES `hos_street` (`sid`);

ALTER TABLE `hos_house` ADD CONSTRAINT fk_house_street
FOREIGN KEY (`sid`) REFERENCES `hos_street` (`sid`);

  INNER JOIN `result` r ON stu.studentNO = r.studentNo          

ALTER TABLE `hos_house` ADD CONSTRAINT fk_house_type
FOREIGN KEY (`hTid`) REFERENCES `hos_type` (`hTid`);

ALTER TABLE `hos_house` ADD CONSTRAINT fk_house_type
FOREIGN KEY (`hTid`) REFERENCES `hos_type` (`hTid`);

  INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo      

#暗中同意约束
ALTER TABLE `hos_house` ALTER COLUMN `price` SET DEFAULT 0;

#暗中认可约束
ALTER TABLE `hos_house` ALTER COLUMN `price` SET DEFAULT 0;

WHERE `studentResult` = 60 AND `subjectName` = ‘Logic Java’;

#ALTER TABLE `hos_house` ALTER COLUMN `hTime` SET DEFAULT now();

#ALTER TABLE `hos_house` ALTER COLUMN `hTime` SET DEFAULT now();

 完毕格局二:采取子查询

#添加音信

#添加音信

SELECT `studentName` FROM `student` WHERE `studentNo` = (

#用户表
INSERT INTO `house`.`sys_user` (`uName`, `uPassWord`) VALUES
(‘小漠’, ‘123’),
(‘百顺’, ‘123’),
(‘练基’, ‘123’),
(‘冬梅’, ‘123’);

#用户表
INSERT INTO `house`.`sys_user` (`uName`, `uPassWord`) VALUES
(‘小漠’, ‘123’),
(‘百顺’, ‘123’),
(‘练基’, ‘123’),
(‘冬梅’, ‘123’);

             SELECT `studentNo` FROM `result`

#区县音讯表
INSERT INTO `house`.`hos_district` (`dName`) VALUES (‘海淀区’),
(‘东城区’),
(‘南城区’),
(‘西城区’),
(‘开发区’);

#区县音信表
INSERT INTO `house`.`hos_district` (`dName`) VALUES (‘海淀区’),
(‘东城区’),
(‘南城区’),
(‘西城区’),
(‘开发区’);

             INNER JOIN `Subject` ON result.subjectNo=
subject.subjectNo

#大街消息表
INSERT INTO `house`.`hos_street` (`sName`) VALUES (‘万泉’);
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES
(‘万泉’, ‘1’);
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES
(‘中关’, ‘3’);
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES
(‘万嘉’, ‘4’);
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES
(‘海风’, ‘5’);

#街道新闻表
INSERT INTO `house`.`hos_street` (`sName`) VALUES (‘万泉’);
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES
(‘万泉’, ‘1’);
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES
(‘中关’, ‘3’);
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES
(‘万嘉’, ‘4’);
INSERT INTO `house`.`hos_street` (`sName`, `sDid`) VALUES
(‘海风’, ‘5’);

             WHERE `studentResult`=60 AND `subjectName`=’Logic
Java’

#房屋类型表
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘一室一厅’);
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘两室一厅’);
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘三室一厅’);
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘两室一卫’);
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘一室一卫’);

#房子类型表
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘一室一厅’);
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘两室一厅’);
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘三室一厅’);
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘两室一卫’);
INSERT INTO `house`.`hos_type` (`htName`) VALUES (‘一室一卫’);

);

#出租房舍新闻表
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`,
`price`, `topic`, `contents`, `hTime`, `copy`)
VALUES (‘1’, ‘1’, ‘1’, ‘530’, ‘观景房’, ‘阳台观赏大海’, ‘2017-7-7’,
‘须求的速度买’);
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`,
`price`, `topic`, `contents`, `hTime`, `copy`)
VALUES (‘2’, ‘2’, ‘2’, ‘430’, ‘大床房’, ‘舒服睡觉’, ‘2017-6-9’,
‘好舒服’);
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`,
`price`, `topic`, `contents`, `hTime`, `copy`)
VALUES (‘3’, ‘3’, ‘3’, ‘480’, ‘双人房’, ‘嘿嘿嘿’, ‘2016-9-9’,
‘懂不懂’);
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`,
`price`, `topic`, `contents`, `hTime`, `copy`)
VALUES (‘4’, ‘4’, ‘4’, ‘360’, ‘单人房’, ‘旅行必选’, ‘2015-8-8’,
‘等你来选’);

#出租房屋新闻表
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`,
`price`, `topic`, `contents`, `hTime`, `copy`)
VALUES (‘1’, ‘1’, ‘1’, ‘530’, ‘观景房’, ‘阳台观赏大海’, ‘2017-7-7’,
‘必要的进程买’);
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`,
`price`, `topic`, `contents`, `hTime`, `copy`)
VALUES (‘2’, ‘2’, ‘2’, ‘430’, ‘大床房’, ‘舒服睡觉’, ‘2017-6-9’,
‘好舒服’);
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`,
`price`, `topic`, `contents`, `hTime`, `copy`)
VALUES (‘3’, ‘3’, ‘3’, ‘480’, ‘双人房’, ‘嘿嘿嘿’, ‘2016-9-9’,
‘懂不懂’);
INSERT INTO `house`.`hos_house` (`uid`, `sid`, `hTid`997755.com澳门葡京,,
`price`, `topic`, `contents`, `hTime`, `copy`)
VALUES (‘4’, ‘4’, ‘4’, ‘360’, ‘单人房’, ‘旅行必选’, ‘2015-8-8’,
‘等您来选’);

 
1、子查询相比较灵活、方便,常作为增删改查的筛选标准,适合于决定一个表的多寡

#上机4
CREATE TEMPORARY TABLE temp_house
(SELECT * FROM `hos_house` LIMIT 2,2);
SELECT * FROM temp_house;

#上机4
CREATE TEMPORARY TABLE temp_house
(SELECT * FROM `hos_house` LIMIT 2,2);
SELECT * FROM temp_house;

  2、表连接更切合于查看多表的数目

5.询问指定客户发布的出租屋消息

5.询问指定客户发表的出租屋消息

7、IN和NOT IN 子查询:

#上机5
#select `uid` from `sys_user` where uName=’大漠’;
SELECT
`dName`,`sName`,hou.`hTid`,`price`,`topic`,`contents`,`hTime`,`copy`
FROM `hos_house` AS hou,`hos_district` AS dist,`hos_street` AS
str,`sys_user` AS us,`hos_type` AS ty
WHERE hou.`uid`=us.`uid` AND hou.`hTid`=ty.`hTid` AND
hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`
AND hou.`uid`=(SELECT `uid` FROM `sys_user` WHERE uName=’大漠’);

#上机5
#select `uid` from `sys_user` where uName=’大漠’;
SELECT
`dName`,`sName`,hou.`hTid`,`price`,`topic`,`contents`,`hTime`,`copy`
FROM `hos_house` AS hou,`hos_district` AS dist,`hos_street` AS
str,`sys_user` AS us,`hos_type` AS ty
WHERE hou.`uid`=us.`uid` AND hou.`hTid`=ty.`hTid` AND
hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`
AND hou.`uid`=(SELECT `uid` FROM `sys_user` WHERE uName=’大漠’);

               
  ●IN子查询:使用=,>等相比运算符时,须要子查询只好回去,一条或空的笔录,

6.按区县创制房屋出租清单

6.按区县打造房屋出租清单

                                   
 在MySQL中,当子查询跟随在=、!=、<、>、>=和<=时,不允许子查询重临多条记下。

#上机6
/*select sid from `hos_house` group by sid having count(sid)>1;
select `sDid` from `hos_street`
where sid=(SELECT sid FROM `hos_house` GROUP BY sid HAVING
COUNT(sid)>1);
select `dName` from `hos_district` where `did`=(SELECT `sDid`
FROM `hos_street`
WHERE sid=(SELECT sid FROM `hos_house` GROUP BY sid HAVING
COUNT(sid)>1));*/
SELECT `htName`,`uName`,`dName`,`sName`
FROM `hos_house` AS hou,`hos_district` AS dist,`hos_street` AS
str,`sys_user` AS us,`hos_type` AS ty
WHERE hou.`uid`=us.`uid` AND hou.`hTid`=ty.`hTid` AND
hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`
AND hou.sid=(SELECT sid FROM `hos_house` GROUP BY sid HAVING
COUNT(sid)>1);

#上机6
/*select sid from `hos_house` group by sid having count(sid)>1;
select `sDid` from `hos_street`
where sid=(SELECT sid FROM `hos_house` GROUP BY sid HAVING
COUNT(sid)>1);
select `dName` from `hos_district` where `did`=(SELECT `sDid`
FROM `hos_street`
WHERE sid=(SELECT sid FROM `hos_house` GROUP BY sid HAVING
COUNT(sid)>1));*/
SELECT `htName`,`uName`,`dName`,`sName`
FROM `hos_house` AS hou,`hos_district` AS dist,`hos_street` AS
str,`sys_user` AS us,`hos_type` AS ty
WHERE hou.`uid`=us.`uid` AND hou.`hTid`=ty.`hTid` AND
hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`
AND hou.sid=(SELECT sid FROM `hos_house` GROUP BY sid HAVING
COUNT(sid)>1);

 

7.按区县成立房屋出租清单

7.按区县创设房屋出租清单

#上机7 QUARTER(NOW())获取季度
/*FROM `hos_house` AS hou,`hos_district` AS dist,`hos_street`
AS str,`sys_user` AS us,`hos_type` AS ty
GROUP BY hou.`hMid`
WHERE hou.`uid`=us.`uid` AND hou.`hTid`=ty.`hTid` AND
hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`*/

#上机7 QUARTER(NOW())获取季度
/*FROM `hos_house` AS hou,`hos_district` AS dist,`hos_street`
AS str,`sys_user` AS us,`hos_type` AS ty
GROUP BY hou.`hMid`
WHERE hou.`uid`=us.`uid` AND hou.`hTid`=ty.`hTid` AND
hou.`sid`=str.`sid` AND str.`sDid`=dist.`did`*/

SELECT QQ AS ‘季度’,dist.`dName` AS ‘区县’,str.`sName` AS ‘街道’,
ty.`htName` AS ‘户型’,CNT AS ‘房屋数量’
FROM
(
SELECT QUARTER(`hTime`) AS QQ,`sid` AS SI,`hTid` AS HT,COUNT(*)
CNT
FROM `hos_house` AS hou
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime`),`sid`,`hTid`
) AS temp,`hos_district` dist,`hos_street` AS str,`hos_type` AS
ty,`hos_house` AS hou
WHERE hou.`hTid`=ty.`hTid` AND hou.`sid`=str.`sid` AND
str.`sDid`=dist.`did`

SELECT QQ AS ‘季度’,dist.`dName` AS ‘区县’,str.`sName` AS ‘街道’,
ty.`htName` AS ‘户型’,CNT AS ‘房屋数量’
FROM
(
SELECT QUARTER(`hTime`) AS QQ,`sid` AS SI,`hTid` AS HT,COUNT(*)
CNT
FROM `hos_house` AS hou
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime`),`sid`,`hTid`
) AS temp,`hos_district` dist,`hos_street` AS str,`hos_type` AS
ty,`hos_house` AS hou
WHERE hou.`hTid`=ty.`hTid` AND hou.`sid`=str.`sid` AND
str.`sDid`=dist.`did`

UNION

UNION

SELECT QUARTER(`hTime`),`hos_district`.`dName`,’ 小计 ‘,’
‘,COUNT(*) AS ‘房屋数量’
FROM `hos_house`
INNER JOIN `hos_street` ON(hos_house.`sid`=hos_street.`sid`)
INNER JOIN hos_district
ON(hos_street.`sDid`=hos_district.`did`)
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime`),hos_district.`dName`
UNION

SELECT QUARTER(`hTime`),`hos_district`.`dName`,’ 小计 ‘,’
‘,COUNT(*) AS ‘房屋数量’
FROM `hos_house`
INNER JOIN `hos_street` ON(hos_house.`sid`=hos_street.`sid`)
INNER JOIN hos_district
ON(hos_street.`sDid`=hos_district.`did`)
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime`),hos_district.`dName`
UNION

SELECT QUARTER(`hTime`),’ 合计 ‘,’ ‘,’ ‘,COUNT(*) AS ‘房屋数量’
FROM hos_house
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime`)
ORDER BY 1,2,3,4

SELECT QUARTER(`hTime`),’ 合计 ‘,’ ‘,’ ‘,COUNT(*) AS ‘房屋数量’
FROM hos_house
WHERE QUARTER(`hTime`)
GROUP BY QUARTER(`hTime`)
ORDER BY 1,2,3,4

五.总结

五.总结

UNION有点素不相识其余没什么。。。。。

UNION有点面生此外没什么。。。。。

欢迎提问,欢迎指错,欢迎研商学习新闻 有需要的私聊 发表评论即可
都能回复的

迎接提问,欢迎指错,欢迎啄磨学习消息 有亟待的私聊 公布评论即可
都能回复的

  原文在和讯有亟待可以互换扣扣:2265682997

  原文在腾讯网有必要可以联系扣扣:2265682997

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website