首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >java核心技术第二篇之数据库SQL语法

java核心技术第二篇之数据库SQL语法

作者头像
海仔
发布2019-08-05 18:44:41
1K0
发布2019-08-05 18:44:41
举报
文章被收录于专栏:海仔技术驿站海仔技术驿站

#查询products表记录 SELECT * FROM products WHERE price > 2000; – 单行注释 /* 多行注释 */ #创建数据库 CREATE DATABASE hei66_day19_db;hei66_day19_db #查看数据库 SHOW DATABASES; #查看某个数据库的定义信息 SHOW CREATE DATABASE hei66_day19_db; #删除数据库 DROP DATABASE hei66_day19_db;

#切换数据库 USE day21_267; SELECT * FROM products; #查看当前工作的数据库 SELECT DATABASE(); #创建学员信息表: CREATE TABLE student( stuNo INT, stuName VARCHAR(200), stuSex CHAR(5), stuAge INT ); #查看数据库中有哪些表: SHOW TABLES;

#查看表结构: DESC account;

#删除表 DROP TABLE student;

#为student表添加一个address列 ALTER TABLE student ADD address VARCHAR(200);

#将student表的address字段修改为500个长度 ALTER TABLE student MODIFY address VARCHAR(500);

#删除address列 ALTER TABLE student DROP address;

11.表记录操作相关的语句: 1.添加数据:insert into 两种格式: 1.insert into 表名 values(值1,值2,…,值n)–全字段添加 注意: 1).后面值列表中的数量必须跟表中列的数量匹配,而且顺序也要匹配。 2).值:数值类型,可以不用单引号(用也可以) 字符串类型,一定要使用单引号。 2.insert into 表名(字段1,字段2,…,字段n) values(值1,值2,…,值n)–部分字段添加,剩余字段添加:NULL

	#把sc表的查询出来的数据,再添加到sc1表中(重难点)
	INSERT INTO sc1 (SELECT * FROM sc);
	INSERT INTO sc1 (SELECT * FROM sc);
	添加数据的时候,可以直接添加查询出来的所有数据
	INSERT INTO sc SELECT sno, 3, (SELECT AVG(score) FROM sc WHERE cno = 2) FROM student WHERE sno NOT IN (SELECT sno FROM sc WHERE cno = 3);
	
	  注意:
	  1).字段列表:可以是表的部分字段,也可以不按照定义顺序;
	  2).值列表:必须跟字段列表的数量和顺序要匹配。
	  3).未指定的字段,添加:NULL值。(前提是:字段允许NULL值)
2.修改数据:update(在有外键的表中不能查询的同时,再进行修改)
	格式:update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... , 字段名n = 值n   where 条件;
	
	
3.删除数据:delete from
	格式:delete from 表名 where 条件;
  清空表:
	1).delete from 表名;逐行删除,效率低;不清空auto_increment记录数
	2).truncate 表名;先摧毁表,然后按照原结构再创建一个新表,效率高;auto_increment将置为零,从新开始
	3).TRUNCATE TABLE sc;//恢复表

#1.添加记录–全字段 #1.insert into 表名 values(值1,值2,…,值n)–全字段添加 INSERT INTO student VALUES(2,章子怡,女,20);

#2.添加记录–部分字段 #2.insert into 表名(字段1,字段2,…,字段n) values(值1,值2,…,值n)–部分字段添加,剩余字段添加:NULL INSERT INTO student(stuName,stuNo) VALUES(‘汪峰’,22);

#3.修改记录 UPDATE student SET stuAge = 30 WHERE stuNo = 22;

#4.修改记录:修改汪峰:年龄为:23,性别:男 UPDATE student SET stuage = 23,stuSex = ‘男’ WHERE stuNo = 22; UPDATE student SET stuAge = 30 WHERE stuSex = ‘女’

#5.删除数据 day21_267

DELETE FROM products WHERE price > 2000;

/* 练习: 1.添加一个学员信息:1,‘黄渤’,‘男’,22 2.添加一个需要信息:2,‘孙红雷’,(空),23 3.修改:stuNo为3的,姓名改为:小沈阳,性别:男,年龄:20 4.删除:删除stuName='小沈阳’的记录

*/ INSERT INTO student VALUES(1,‘黄渤’,‘男’,22); INSERT INTO student VALUES(2,‘孙红雷’,NULL,23); INSERT INTO student(stuNo,stuName,stuAge) VALUES(3,‘孙红雷’,23); UPDATE student SET stuName = ‘小沈阳’,stuSex=‘男’,stuAge = 20 WHERE stuNo = 3; DELETE FROM student WHERE stuName = ‘小沈阳’;

4.查询数据:select 字段名 from 表名 where 字段的筛选条件
	1.简单查询:
		1).查询所有字段的所有记录:
			select pid,pname,price,categoryName from product;
			或者
			select * from product;
			
			查询条件是大写的单词
			select * from 表名 where binary name = 'TOM';
			
		2).查询部分字段的所有记录:
			select pname,price from product;
		3).使用别名:
			a).列别名:
				SELECT pname AS '商品名称' , price AS '价格' FROM product;
			b).表别名:
				SELECT p.pname,p.price FROM product p;//一般在多表中使用别名
		4).去掉重复值
				SELECT DISTINCT price FROM product;
		5).对查询结果进行运算:
			例如:将所有查询结果的商品的价格加100显示:
			select pname,price + 100 from product;
		        注意:只对查询结果进行更改,原数据没有更改。
	2.条件查询:
		1).比较运算符:
			1).">":大于。例如:查询价格大于2000元的商品		--针对数值类型查询
				select * from product where price > 2000;
			2)."<":小于。例如:查询价格小于2000元的商品		--针对数值类型查询
				select * from product where price < 2000;
			3).">=":大于等于。例如:查询价格大于等于2000元的商品	--针对数值类型查询
				select * from product where price >= 2000;
			4)."<=":小于等于。例如:查询价格小于等于2000元的商品	--针对数值类型查询
				select * from product where price <= 2000;
			5)."<>":不等于。例如:查询价格不等于2000元的商品	--针对各种类型
				select * from product where price <> 2000;
			   "!=":不等于						--针对各种类型
				select * from product where price != 2000;
			6)."=" :等于.例如:查询价格等于2000元的商品		--针对各种类型
				select * from product where price = 2000;
		2).逻辑运算符:
			1).and : 语义:并且
				例如:查询所有商品价格大于2000元的电脑类商品
				select * from product where price > 2000 and categoryName = '电脑';
			2).or  : 语义:或者
				例如:查询所有商品价格大于2000元,或者价格低于1000元的所有商品
				select * from product where price > 2000 or price < 1000;
			3).not : 语义:非
				例如:查询商品价格不大于2000元的所有商品
				select * from product where not(price > 2000);

			注意:如果多个and和or运算,中间不要加逗号,可以使用()改变运算顺序。
			例如:查询所有价格大于2000元的电脑类商品或者服装类商品
				select * from product where price > 2000 and (categoryName = '电脑' or categoryName = '服装');
		3).范围查询:between ... and ...(可以用于数值类型,也可以用于日期类型)
			1).用于查询数值范围:between(包含)....and(包含)...
			   例如:查询价格在1000元(包含)到2000元(包含)之间的所有商品
				select * from product where price >= 1000 and price <= 2000;
				或者
				select * from product where price between 1000 and 2000;
			2).用于查询日期范围:
			    例如:查询生产日期在2017年1月份的所有商品
				select * from product where proDate between '2017-01-01' and '2017-01-31';
		4).多个值的判断:in(值列表)
			例如:查询商品价格为200元,500元,1000元,2000元的商品信息
				select * from product where price = 200 or price = 500 or price = 1000 or price = 2000;
				或者使用in查询
				select * from product where price in (200,500,1000,2000);

		扩展:all和any用法
		create table #A(id int)
		Go
		insert into #A values(1)
		insert into #A values(2)
		insert into #A values(3)
		insert into #A values(4)
		go
		--All:对所有数据都满足条件,整个条件才成立,例如:5大于所有返回的id
		select *
		from #A
		where 5>All(select id from #A)
		go
		--Any:只要有一条数据满足条件,整个条件成立,例如:3大于1,2
		select *
		from #A
		where 3>any(select id from #A)
		go
		--Some和Any一样 
		备注:Any的用法中,在作数字比对时,也可以改用先select subquery的min/max value的方法,某些情况下效率更高

				
				
		5).模糊查询:like 两个通配符:1)"%" : 任意的0到多个字符;2)"_":任意的1个字符
			例如:查询商品名称中包含"花"的商品信息
				select * from product where pname like '%花%';
			      查询商品名称中以"花"字开头的商品:
			        select * from product where pname like '花%';
			      商品名称以"花花"开头,全名是四个字的商品:
			        select * from product where pname like '花花__';
		6).查询空字段:
			1).添加一条记录时,不添加的字段可以指定为NULL值,例如:
				insert into product values(14,'果10',200,'食品',NULL);
			   要查询所有"生产日期"没有添加的所有商品:
				select * from product where proDate IS NULL;
			3).添加一条记录:
				insert into product values(15,'果11',300,'',null);
			   要查询出来这条记录:
				select * from product where proDate = '';

#1.查询所有字段的所有记录 SELECT pid,pname,price,categoryName FROM product; #或者 SELECT * FROM product; #2.查询部分字段: SELECT pname,price FROM product;

#3.使用别名 SELECT pname AS ‘商品名称’ , price AS ‘价格’ FROM product; #表别名 SELECT p.pname,p.price FROM product p; #4.去掉重复值 SELECT DISTINCT price FROM product; #5.对查询结果进行运算 SELECT pname,price + 100 FROM product; #6.查询价格大于2000元的商品 SELECT * FROM product WHERE price > 2000; #7.查询价格小于2000元的商品 SELECT * FROM product WHERE price < 2000; #8.查询价格小于等于2000元的商品 SELECT * FROM product WHERE price <= 2000; #9.查询价格不等于2000元的商品 SELECT * FROM product WHERE price <> 2000; #10.查询价格等于2000元的商品 SELECT pname,price FROM product WHERE price = 2000;

#11.查询商品类别不等于’电脑’ SELECT * FROM product WHERE categoryName <> ‘电脑’;

#12.查询所有商品价格大于2000元的电脑类商品 SELECT * FROM product WHERE price > 2000 AND categoryName = ‘电脑’; #13.查询所有商品价格大于2000元,或者价格低于1000元的所有商品 SELECT * FROM product WHERE price > 2000 OR price < 1000; #14.查询商品价格不大于2000元的所有商品 SELECT * FROM product WHERE NOT(price > 2000); #15.查询所有价格大于2000元的电脑类商品或者服装类商品 SELECT * FROM product WHERE price > 2000 AND (categoryName = ‘电脑’ OR categoryName = ‘服装’); #16.查询生产日期在2017年1月份的所有商品 SELECT * FROM product WHERE proDate BETWEEN ‘2017-01-01’ AND ‘2017-01-31’; #17.查询商品名称中包含"花"的商品信息 SELECT * FROM product WHERE pname LIKE ‘%花%’;

INSERT INTO product VALUES(14,‘果10’,200,‘食品’,NULL); SELECT * FROM product WHERE proDate IS NULL; INSERT INTO product VALUES(15,‘果11’,300,’’,NULL); SELECT * FROM product WHERE categoryName = ‘’;

学习目标总结: 1,能够理解数据库的概念 说出数据库的概念 1).数据库就是存储数据的仓库,其本质是一个文件系统, 数据库按照特定的格式将数据存储起来,用户可以对数据库中的数据进行增加,修改,删除及查询操作。 说出数据库的作用 2).存储、管理大量的数据。 说出数据库与表的关系 一个逻辑数据库包含多个表 说出常见的数据库 MySQL Oracle SQLServer

2,能够安装MySQL数据库 参考安装文档。 3,能够启动,关闭及登录MySQL 1.启动和关闭MySQL服务: 1).我的电脑–>右键–>管理–>服务和应用程序–>服务–>MySQL–>启动/停止 2.登录MySQL 1).命令行: C:>mysql -uroot -p密码 (回车) 2).SQLYog: 启动,填写连接信息,登录。

4,能够使用SQL语句操作数据库 写出创建数据库的SQL语句 create database 数据库名; 或者 create database 数据库名 character set 字符集; 写出删除数据库的SQL语句 drop database 数据库名; 写出查看所有数据库的SQL语句 show databases; 写出切换数据库的SQL语句 ues 数据库名; 5,能够使用SQL语句操作表结构 写出创建表的SQL语句 create table 表名( 字段名1 数据类型[长度] [约束], 字段名2 数据类型[长度] [约束], … 字段名n 数据类型[长度] [约束] ); 写出删除表的SQL语句 drop table 表名; 写出添加一列的SQL语句 alter table 表名 add 列名 类型(长度) [约束]; 写出删除一列的SQL语句 alter table 表名 drop 列名; 写出查看当前数据库下所有表的SQL语句 show tables; 写出查看表结构的SQL语句 desc 表名; 6,能够使用SQL语句进行数据的添加修改和删除的操作 写出添加数据的SQL语句 insert into 表名 values(值1,值2,…,值n); 或者 insert into 表名(字段1,字段2,… ,字段n) values(值1,值2,… , 值n) 写出修改数据的SQL语句 update 表名 set 字段1 = 值1 , 字段2 = 值2 , … ,字段n = 值n where 条件; 写出删除数据的SQL语句 delete from 表名 where 条件;

在dos中怎么解决乱码?

win7系统中使用DOS命令是出现乱码的解决方法 方法一:设置cmd显示字体 1、win+R打开运行窗口->输入cmd->回车,打开命令行提示符窗口 win7系统运行窗口 win7系统DOS命令行提示窗口

2、在命令行标题栏上点击右键,选择”属性”->”字体”,将字体修改为True Type字体”Lucida Console”,然后点击确定将属性应用到当前窗口,确定 DOS命令窗口右键菜单 DOS命令行,设置字体

方法二:修改注册表 1、快捷键win+R,打开运行窗口,输入“regedit”,打开注册表编辑器 运行窗口打开注册表编辑器 2、修改注册表的参数值, 正常显示路径:HKEY_CURRENT_USER\Console%“SystemRoot”%_system32_cmd.exe 健名:CodePage 修改前的值为:437(十进制),改为:936(十进制); 温馨提示:涉及注册表修改,请做好注册表备份

01.SQL高级查询_排序: 1.使用的关键字:order by 字段名 ASC(升序–默认) / DESC(降序) 例如:查询所有商品,要求结果按价格从小到大排序 SELECT * FROM product ORDER BY proDate ASC; 2.注意:升序可以不写asc关键字,例如: select * from product order by proData;//升序 3.排序: 升序(ASC):从小到大; 降序(DESC):从大到小; 4.对多列进行排序: 例如:对多列进行排序:按金额排序,如果金额相同,按生产日期升序排序 SELECT * FROM product ORDER BY price ASC,proDate ASC; 先按第一个字段排序,在第一个字段值相同的情况下,再按第二个字段排。 5.如果有查询条件,写法: select * from 表名 where 条件 order by 字段 … ; 02.SQL高级查询_聚合函数: 1.我经常会有需求,对某列进行汇总,这就需要使用"聚合函数"; 2.今天我们掌握的五个聚合函数: a).count(/字段名):统计指定列不为NULL的记录行数–任何数据类型 例如:查询电脑类别的商品,共有多少种 SELECT COUNT() FROM product WHERE categoryName = ‘电脑’; b).sum(列名):计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0–数值类型的列 例如:查询电脑类商品的价格总数是多少? select sum(price) from product where categoryName = ‘电脑’; c).max(列名):计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算–数值类型、日期类型 例如:查询电脑类商品的最高价格? select max(price) from product where categoryName = ‘电脑’; d).min(列名):计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算 例如:查询电脑类商品的最低价格? select min(price) from product where categoryName = ‘电脑’; e).avg(列名):计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0 例如:查询电脑类商品的平均价格? select avg(price) from product where categoryName = ‘电脑’; 注意:计算记录的总数量时,不包含NULL的记录。 所以如果计算的列中有NULL值,则结果不准确。 3.注意:聚合查询的结果,只能包含"聚合结果列",不要包含其他列,要包含,其结果是无意义的。 聚合的结果是"计算的结果",跟某行数据无关,所以不能关联显示其它字段。

03.SQL高级查询_分组: 1.分组:对某列中"相同的值"作为一组,进行分组。分组只是手段,后续经常需要进行汇总: 2.例如:一条语句查询出每种商品的最高价格是多少? SELECT categoryName,MAX(price) FROM product GROUP BY categoryName; 练习:查询每种商品的价格的总和 SELECT categoryName,SUM(price) FROM product GROUP BY categoryName; 练习:查询每种商品的商品数量是多少 SELECT categoryName,COUNT(*) FROM product GROUP BY categoryname; 3.注意: 1).分组查询的结果字段中,只能包含"分组字段",“聚合结果字段”。不能再包含其他字段,如果包含,其结果也是无意义的。 4.having子句: 1).由于where不能对聚合后的结果进行筛选。所以要对聚合后的结果进行筛选,需要使用having子句。 例如:查询每种商品的价格总额,结果保留大于1000元的。 select categoryName,sum(price) from product group by categoryName having sum(price) > 1000; 5.对多列进行分组: 收支流水表:trans id 收支项 账户 金额 1 工资收入 工商银行 1000 2 红包收入 工商银行 500 3 收入 交通银行 3000 4 支出 工商银行 300 5 支出 交通银行 770

    需求:查询出每个账户的收支总额,分别是多少?
	账户		收支项		总金额
	工商银行	收入		1500
	工商银行	支出		300
	交通银行	收入		3000
	交通银行	支出		770
   
   select 账户,收支项,sum(金额) from trans group by 账户,收支项;//先按账户分,再按收支项分。

04.SQL语句的执行顺序: 1).from 2).where 3).group by 4).having 5).select 6).distinct 7).order by

SQL语句的编写顺序: select … from … where … group by … having … order by …; 05.分页查询: 1).基本语句:select * from 表名 limit M,N; M值:从第几条(第一条记录为0)记录开始取。 N值:取几条记录 2).例如:查询所有的商品,每页显示5条: 第一页: select * from product limit 0,5; 第二页: select * from product limit 5,5; 第三页: select * from product limit 10,5

	固定算法:
	select * from product limit (当前的页数 - 1) * 每页显示的条数

例题:SELECT * FROM products LIMIT 2; //表示从0页开始,取2条记录.

3).注意:M值和N值,只要是正数,不会抛异常,可能会返回空结果集。
         但如果是负数,会抛异常。

06.备份和恢复数据库: 1).备份:在要备份的数据库上右键–>备份/导出–>以SQL转储文件备份数据库 2).恢复:在SQLYog左侧右键–>导入–>从SQL转储文件导入数据库 07.SQL的约束: 1).主键约束: 1).主键的作用:唯一标识表中一条记录。用于作为条件,方便的进行增删改查操作。 2).定义主键: create table product( pid int primary key, …其它字段… … )

	?方式二:创建表时,在constraint约束区域,声明指定字段为主键:

?格式:[constraint 名称] primary key (字段列表) ?关键字constraint可以省略,如果需要为主键命名,constraint不能省略,主键名称一般没用。 ?字段列表需要使用小括号括住,如果有多字段需要使用逗号分隔。声明两个以上字段为主键,我们称为联合主键。 CREATE TABLE Persons ( FirstName varchar(255), LastName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (FirstName,LastName) ) 或 CREATE TABLE Persons ( FirstName varchar(255), LastName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (FirstName) )

先来看SQL Serve的ISNULL函数:

ISNULL(check_expression,replacement_value)

1、check_expression与replacement_value的数据类型必须一致。 2、如果check_expression为NULL,则返回replacement_value。 3、如果check_expression为NULL,则返回check_expression。

再来看Mysql的IFNULL函数:

IFNULL(expr1,expr2) 如果expr1不是NULL,IFNULL()返回expr1,否则返回expr2。

下面来看下SQLServer中ISNULL与NULLIF的具体用法。 SQL Server里的 ISNULL 与 ASP 中的 IsNull不同,SQL Server 中有两个参数。

一,ISNULL 语法: ISNULL(check_expression, replacement_value)

1,check_expression 与 replacement_value 数据类型必须一致 2,如果 check_expression 为 NULL,则返回 replacement_value 3,如果 check_expression 不为 NULL,则返回 check_expression

二,NULLIF 用于检查两个表达式。 语法: NULLIF(expression, expression)

说明: 如果两个 expression 相等,则返回 NULL,该 NULL 为第一个 expression 的数据类型。 如果两个 expression 不相等,则返回第一个 expressio。

	?方式三:创建表之后,通过修改表结构,声明指定字段为主键:
		ALTER TABLE Persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
		
	3).一个表中只能有一个主键;
	4).一个主键,可以由一个或多个字段组成[很少用];复合主键,联合主键
		客户信息表:将"客户姓名" + "工作单位" 同时作为一个主键
		客户姓名	工作单位	性别	年龄
		张三		人事部		男	20
		李四		人事部		女	22
		张三		业务部		男	23
		张三		人事部		男	18 //错误的数据
	5).任何类型的字段都可以做主键。当前使用int类型。后期varchar
	6).为某个字段添加了"主键约束",也同时自动添加:唯一约束、非空约束,主键也不能为NULL.
	7).删除主键约束:
		ALTER TABLE 表名 DROP PRIMARY KEY;
2).自动增长:
	1).自动增长:让某列的值根据某个基数,进行自增。这种约束通常用于"主键".
	2).添加自动增长约束:
		create table product(
			pid int primary key auto_increment,
			....
		)
	3).清空表对自动增长列的基数的变化:
		1).delete from 表名:逐行删除。不改变自动增长的基数。
		2).truncate 表名【效率高】:摧毁表,重建表。将自动增长的基数重新设置为1.
		
	?扩展:默认地,AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下列 SQL 语法:	

ALTER TABLE Persons AUTO_INCREMENT=100

3).非空约束:NOT NULL
	1).作用:强制某列的数据不能包含NULL值;
	2).添加非空约束:
		create table product(
			pid int primary key,
			pname varchar(200) not null,
			....
		)
		如下添加,会抛出异常:
		insert into product values(null,null,...);//第二个null是错误,pname字段不允许null值
	3).删除非空约束 
		ALTER TABLE 表名 MODIFY 列名 数据类型[长度] (后面不出现not null约束即可,就表示删除了not NULL约束) 
4).唯一约束:unique
	1).作用:表示本列的值是唯一的
	2).添加唯一约束:
		create table product(
			pid int primary key,
			pname varchar(200) unique,
			...
		)
		
	?方式2:创建表时,在约束区域,声明唯一:
				CREATE TABLE Persons
				(
				Id_P int,
				LastName varchar(255) NOT NULL,
				FirstName varchar(255),
				Address varchar(255),
				City varchar(255),
				CONSTRAINT 名称UNIQUE (Id_P)
				如果添加唯一约束时,没有设置约束名称,默认是当前字段的字段名
				)

?方式3:创建表后,修改表结构,声明字段唯一: ALTER TABLE Persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)

	   如果向pname字段添加重复的值,数据库会抛出异常。
	3).如果字段设置了唯一约束,可以写入"空字符串",但只能有一条。
	   也可以写入NULL值,可以写入多条。
	4).删除唯一约束:
		ALTER TABLE 表名 DROP INDEX 名称;
		如果添加唯一约束时,没有设置约束名称,默认是当前字段的字段名
	5).主键与唯一约束的区别:
		主键:代表:唯一、非空;一个表只能有一个主键;
		唯一:只代表:唯一;可以有多个NULL值;一个表可以有多个字段被设置为唯一约束;
5).默认约束:default 值;
	1).作用:可以设置某列的默认值,在添加数据时,可以不指定这列的数据,而使用默认值。
	2).设置默认约束:
		create table student(
			id	int	primary key auto_increment,
			stuName	varchar(20)	not null,
			sex	char(5)	default '男'
		)
		在添加时,如果要使用默认值:
		INSERT INTO student VALUES(NULL,'bbb',DEFAULT);
	3).删除默认约束:
		ALTER TABLE 表名 MODIFY 列名 数据类型[长度](后面不要出现default关键字即可)

08.多表_分表的作用: 1.在制作表时要注意:一个表只描述一件事情。如果需要描述多件事情,可以创建多表,然后通过某个字段去引用 另一个表的数据。这样可以使每个表的数据单独管理,互不影响。 2.分表后: 主表:被其它表引用的表; 从表:引用其它表的表; 09.多表_表和表之间的关系: 1.一对多关系【最常用】: 1).应用场景:客户和订单;分类和商品;部门和员工 客户表:主表 订单表:从表 外键 ---------------------------------------------------------------------------------------- 客户ID 登录名 支付宝 订单ID 订单时间 总金额 客户ID 001 zhangsan xxx 001 xxx xxx 001 002 xxx xxx 001

	2).建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键.
		
2.多对多关系【较常用】:
	1).应用场景:订单和商品、用户和角色
		订单表					商品表:
		-----------------------------------------------------------------------------------------
		订单ID	订单日期   总金额  		商品ID	名称	单价	
		d001	2017-07-04  100			p001	奥利奥	5.5
		d002	2017-07-05  200			p002	红牛	4
										p003	啤酒	2.00

					订单_商品_关系表
					订单Id		商品ID	数量	总价
					d001		p001	2	11
					d001		p002	3	12
					d001		p003	
					d002		p001
	2).建表原则:需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。
3.一对一关系【不建议用】:
	1).客户信息表:						地址表
		姓名	性别	年龄	地址ID			id	省	市	区	街道门牌
		张三	男	22	01			01	北京	北京	顺义	99号
		李四	女	23	02			02	河北	廊坊	安次	88号
	------------------------------------------------------------------------------------------------
	   合并为一个客户表:
		姓名	性别	年龄	省	市	区	街道门牌
		张三	男	22	北京	北京	顺义	99号
		李四	女	23	河北	廊坊	安次	88号

10.外键约束: 1).作用:设置在"从表"的外键字段上,可以强制外键字段的值必须参考主表中的主键字段的值。 2).设置外键约束: alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键); 例如:ALTER TABLE product1 addconstraint fk_product1 FOREIGN KEY(categoryId) REFERENCES category(cid); 3).使用外键目的: 保证数据完整性(在有外键的表中不能查询的同时,再进行修改表中数据)

如何删除外键?

sp_help 表名 可以看到外键名 alter table xxx drop constraint fk_xxx

学习目标总结: 3,能够使用SQL语句进行排序 a, 说出排序语句中的升序和降序关键字 order by 字段名 ASC(升序-默认) / DESC(降序) b, 写出排序语句 select * from product order by price desc; 4,能够使用聚合函数 a, 写出获取总记录数的SQL语句 select count(*) from product; b, 写出获取某一列数据总和的SQL语句 select sum(price) from product; c, 写出获取某一列数据平均值的SQL语句 select avg(price) … d, 写出获取某一列数据的最大值的SQL语句 select max(price) … e, 写出获取某一列数据的最小值的SQL语句 select min(price) … 5,能够使用SQL语句进行分组查询 a, 写出分组的SQL语句 group by 字段名 b, 写出分组后条件过滤器的SQL语句 gruup by 字段名 having 聚合函数 条件; 6,能够完成数据的备份和恢复 1.备份:在要备份的数据库上右键–>备份/导出–>以SQL转储文件备份数据库 2.恢复:在SQLYog左边右键–>导入–>以SQL转储文件导入数据库。 7,能够使用可视化工具连接数据库,操作数据库 使用SQLYog连接数据库。操作数据库

8,能够说出多表之间的关系及其建表原则 a, 说出一对多的应用场景及其建表原则 1).应用场景:客户和订单,分类和商品,部门和员工. 2).在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键. b, 说出多对多的应用场景及其建表原则 1).应用场景:学生和课程、用户和角色 2).需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键. 9,能够理解外键约束 a, 说出外键约束的作用 作用:强制外键字段的值必须参考主表中主键字段的值。 b, 写出创建外键的SQL语句 alter table product add constraint fk_fkname foreign key (categoryid) references category (cid); c, 通过sql语句能够建立多表及其关系 创建表的外键,并且创建外键约束。

能够使用dos窗口的方式进行数据的备份和恢复(不要求记忆,但是要求保留一份最标准的操作步骤)

数据库备份 数据库的备份是指将数据库转换成对应的sql文件。数据库导出sql脚本的命令如下: ?mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径; 以上备份数据库的命令中需要用户名和密码,即表明该命令要在用户没有登录的情况下使用 数据库恢复 数据库的恢复指的是使用备份产生的sql文件恢复数据库,即将sql文件中的sql语句执行就可以恢复数据库内容。因为数据库备份只是备份了数据库内容,所以备份产生的sql文件中没有创建数据库的sql语句,在恢复数据库之前需要自己动手创建数据库。 ?在数据库外恢复 mysql -u root -p 数据库名 < 文件路径 注意:要求数据库必须先创建出来. ?.在数据库内恢复 source SQL脚本路径:使用这种方式恢复数据,首先要登录数据库 【笔试面试题】 无 【重点总结】 备份 Mysqldump –u root –p abc mydb>c:/mydb.sql 恢复 1.mysql –u root –p mydb<c:/mydb.sql source c:/mydb.sql

/ /

在架构设计器上,把从表的外键拉向主表的主键

01.多表查询_交叉查询【了解】 1.查询结果=左表的总记录数 * 右表的总记录数 – 笛卡尔积 02.多表查询_内连接查询【重点掌握】 1.隐式内连接【常用】: 1).格式:select 字段列表 from 表1,表2 where 表1和表2的等值关系; 2).例如:查询商品信息,要显示所对应类别信息 select * from products , category where products.category_id = catetory.cid; 只保留两个表的部分字段,使用表别名: SELECT p.pname,p.price,c.cname FROM products p , category c WHERE p.category_id = c.cid; 3).练习:查询"市"的所有信息,并且显示对应的"省名" SELECT c.cname AS ‘市’,p.pname AS ‘省’ FROM city c,province p WHERE c.pid = p.pid;

2.显示内连接:
	1).格式:select 字段列表 from 表1 INNER JOIN 表2 ON 等值关系;
	2).例如:查询商品信息,要显示所对应类别信息
		select * from products p inner join category c on p.category_id = c.cid;
	3).练习:查询"市"的所有信息,并且显示对应的"省名"
		SELECT c.cname AS '市',p.pname AS '省' from city c inner join province p on c.pid = p.pid;
注意:
1.内连接的查询结果:两个表中的等值记录;
2.两种内连接都可以再添加其它where条件:
	隐式内连接:select .. from 表1,表2 where 等值条件 and 其它条件...
	显示内连接:select .. from 表1 inner join 表2 on 等值条件 where 其它条件....
3.两种查询的格式说明:
	隐式内连接:select .. from 表1,表2 on 等值条件//错误
	显示内连接:select .. from 表1 inner join 表2 where 等值条件//OK的

03.多表查询_外连接查询【重点掌握】 1.左外连接查询: 1).格式:select 字段列表 from 表1 left join 表2 on 等值关系; 2).查询结果:左表的所有记录,和右表的等值记录; 3).例如:需求:查询出所有商品(包括没有类别的商品),有类别的商品要显示类别名称。 SELECT * FROM products p LEFT JOIN category c ON p.category_id = c.cid;

2.右外连接查询:
	1).格式:select 字段列表 from 表1 right join 表2 on 等值关系;
	2).查询结果:右表的所有记录,和左表中的等值记录;
	3).例如:需求:查询出所有的商品类别,如果类别下有商品的,要同时显示商品信息;
		SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;

04.子查询【重点掌握】 1.在一个查询内部,可以再写一个查询,这个写在内部的查询就叫:子查询; 2.子查询的结果可以作为另一个查询:判断条件,表使用。 3.例子:查询价格高于"劲霸"的商品信息; SELECT * FROM products WHERE price > (SELECT price FROM products WHERE pname = ‘劲霸’); 4.练习: 1).查询化妆品类别的商品信息 a).使用多表连接查询: select * from products p , category c where p.category_id = c.cid and c.cname = ‘化妆品’; b).使用子查询(单表查询) SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = ‘化妆品’); c).使用子查询作为第三张表:select * from (子查询) SELECT * FROM products p ,(SELECT * FROM category WHERE cname = ‘化妆品’) c WHERE p.category_id = c.cid; 2).查询所有"家电","服饰"类商品的信息: select * from products where category_id = 1 or category_id = 2; 改进: select * from products where category_id in (1,2); 改进: select * from products where category_id in (select cid from category where cname in (‘家电’,‘服饰’));

学习目标总结: 1,能够使用内连接进行多表查询 a, 说出内连接的两种查询方式 1.隐式内连接 2.显示内连接 b, 写出显式内连接的SQL语句 select * from products p inner join category c on p.category_id = c.cid; c, 写出隐式内连接的SQL语句 select * from products p , category c where p.category_id = c.cid; 2,能够使用外连接进行多表查询 a, 说出外连接的两种查询方式 1.左外查询 2.右外查询 b, 写出左外连接的SQL语句 select * from products p left join category c on p.category_id = c.cid;//所有左表中的记录,和右表的等值记录 c, 写出右外连接的SQL语句 select * from products p right join category c on p.category_id = c.cid;//所有右表中的记录,和左表的等值记录 3,能够使用子查询进行多表查询 select * from products where category_id in (select cid from category where cname in (‘家电’,‘服饰’));

扩展:三表联查: 1.隐式内连接:使用user表,role表,user_role表进行测试 select * from users u , role r,user_role ur where u.uid = ur.uid and ur.rid = r.rid; 2.显示内连接: select * from users u inner join user_role ur on u.uid = ur.uid inner join role r on ur.rid = r.rid;

#1.多表交叉查询 SELECT * FROM products,category; #2.内连接–隐式内连接 SELECT * FROM category,products WHERE products.category_id = category.cid; #3.只显示部分字段 SELECT p.pname,p.price,c.cname FROM products p , category c WHERE p.category_id = c.cid; #4.查询"市"的所有信息,并且显示对应的"省名" SELECT c.cname AS ‘市’,p.pname AS ‘省’ FROM city c,province p WHERE c.pid = p.pid; #5.显示内连接 查询商品信息,要显示所对应类别信息 SELECT p.pname,p.price,c.cname FROM products p INNER JOIN category c ON p.category_id = c.cid;

#6.左外连接查询 #需求:查询出所有商品(包括没有类别的商品),有类别的商品要显示类别名称。 SELECT * FROM products p LEFT JOIN category c ON p.category_id = c.cid; #7.右外连接查询 #需求:查询出所有的商品类别,如果类别下有商品的,要同时显示商品信息; SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;

SELECT * FROM products WHERE price > (SELECT price FROM products WHERE pname = ‘劲霸’);

SELECT * FROM products p , category c WHERE p.category_id = c.cid AND c.cname = ‘化妆品’; SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = ‘化妆品’); SELECT * FROM products p ,(SELECT * FROM category WHERE cname = ‘化妆品’) c WHERE p.category_id = c.cid;

SELECT * FROM users u , role r,user_role ur WHERE u.uid = ur.uid AND ur.rid = r.rid; SELECT * FROM users u INNER JOIN user_role ur ON u.uid = ur.uid INNER JOIN role r ON ur.rid = r.rid;

单列多行子查询 可以使用in,any或all操作

any:大于子查询中的最小值。 all: 大于子查询中的最大值。 <any:小于子查询中的最大值。 <all: 小于子查询中的最小值。 =any:大于等于子查询中的最小值。 =all:大于等于子查询中的最大值。 <=any:小于等于子查询中最大值。 <=all:小于等于子查询中最小值。 !=any或<>any:不等于子查询中的任意值。 !=all或<>all:不等于子查询中的所有值。 =any:等于子查询中任意值。 =all:等于子查询中所有值(无意义)

SQL语句中: 查询:select * from 表名 where 0/1; 0表示查询不到任何表信息,1表示可以查询任何信息 例如:or '1=1 : 就是利用这个sql注入问题,返回一个true,表示登录成功

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2019年07月28日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • INSERT INTO product VALUES(14,‘果10’,200,‘食品’,NULL); SELECT * FROM product WHERE proDate IS NULL; INSERT INTO product VALUES(15,‘果11’,300,’’,NULL); SELECT * FROM product WHERE categoryName = ‘’;
  • sp_help 表名 可以看到外键名 alter table xxx drop constraint fk_xxx
  • 04.子查询【重点掌握】 1.在一个查询内部,可以再写一个查询,这个写在内部的查询就叫:子查询; 2.子查询的结果可以作为另一个查询:判断条件,表使用。 3.例子:查询价格高于"劲霸"的商品信息; SELECT * FROM products WHERE price > (SELECT price FROM products WHERE pname = ‘劲霸’); 4.练习: 1).查询化妆品类别的商品信息 a).使用多表连接查询: select * from products p , category c where p.category_id = c.cid and c.cname = ‘化妆品’; b).使用子查询(单表查询) SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = ‘化妆品’); c).使用子查询作为第三张表:select * from (子查询) SELECT * FROM products p ,(SELECT * FROM category WHERE cname = ‘化妆品’) c WHERE p.category_id = c.cid; 2).查询所有"家电","服饰"类商品的信息: select * from products where category_id = 1 or category_id = 2; 改进: select * from products where category_id in (1,2); 改进: select * from products where category_id in (select cid from category where cname in (‘家电’,‘服饰’));
  • 学习目标总结: 1,能够使用内连接进行多表查询 a, 说出内连接的两种查询方式 1.隐式内连接 2.显示内连接 b, 写出显式内连接的SQL语句 select * from products p inner join category c on p.category_id = c.cid; c, 写出隐式内连接的SQL语句 select * from products p , category c where p.category_id = c.cid; 2,能够使用外连接进行多表查询 a, 说出外连接的两种查询方式 1.左外查询 2.右外查询 b, 写出左外连接的SQL语句 select * from products p left join category c on p.category_id = c.cid;//所有左表中的记录,和右表的等值记录 c, 写出右外连接的SQL语句 select * from products p right join category c on p.category_id = c.cid;//所有右表中的记录,和左表的等值记录 3,能够使用子查询进行多表查询 select * from products where category_id in (select cid from category where cname in (‘家电’,‘服饰’));
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档