java基础-----SQL

常用数据库介绍

1. MySQL：早期是免费的，开源。属于中小型数据库。在管理5000万条数据的时候与Oracl性能差不多。目前已被Oracle(甲骨文公司)收购，从6.x版本开始收费
2. Oracle：收费数据库，Oracle公司出品，属于大型数据库软件。。目前世界上最具竞争力的，顶级数据库软件
3. SQLServer：微软公司的。中型数据库软件，收费的。管理界面非常友好，使用方便。
4. DB2：IBM公司的数据库产品，收费的。常应用在银行系统中。
5. SQLLite：嵌入式的小型数据库，应用在手机端

SQL语言的分类

普通话：任何数据库软件都遵循的标准SQL语言
方言：某种数据库软件，根据自己数据库的特点，开发的新的语法，这个语法只在这个数据库上有效。

1.DDL：数据定义语言，用于创建、修改、删除–逻辑数据库，表，列等使用的命令语句：create(创建)，alter（修改），drop（删除）
2.DCL：数据控制语言。用于创建用户，以及为用户分配权限等
3.DML(Data Manipulate Language)：数据操作语言。用于操作表中的记录的。用于添加(insert),修改(update)，删除(delete)记录
4.DQL(Data Query Language)：数据查询语言，也是用于查询表中的记录的。使用的命令语句：select…from…where…

DDL

1. 创建数据库：create database 数据库名;create database 数据库名 character set “字符集”;
2. 查看数据库MySQL服务器中的所有的数据库：show databases；
3. 删除数据库：drop database 数据库名称；
4. 查看正在使用的数据库：select database();//调用了一个函数
5. 切换数据库：use 据库名;
6. 创建表语句(同时指定列)：列于列中间用,隔开。

   create table 表名(
      name varchar(100),//列名 数据类型(长度) [约束]
      sex char(1),//列名2 数据类型(长度) [约束]
      age int   //列名3 数据类型(长度) [约束]
      );
   

7. 查看数据库中的所有表：show tables;
8. 查看表结构：desc 表名；//查看有哪几个列，每一行是哪个字段
9. 删除表：drop table 表名；
10. 修改表之添加列：alter table 表名 add 列名 类型(长度) [约束]

	  //为分类表添加一个新的字段为`desc`类型varchar(20)	
	  alter table category add `desc` varchar(20);

11. 修改表之修改列的类型长度：alter table 表名 modify 列名 类型(长度) [约束]

	  //为分类表的描述字段进行修改，类型varchar(50)，添加约束not null
	  alter table category add `desc` varchar(20);

12. 修改表之修改列名(可以在修改列名的同时修改类型长度)：alter table 表名 change 旧列名 新列名 类型(长度) [约束]

	  //为分类表的分类名称字段进行更换，更换为description varchar(30)
	  alter table category change `desc` description varchar(30);

13. 修改表之删除列：alter table 表名 drop 列名

	  //删除分类表中的description这列
	  alter table category drop description

14. 修改表之修改表名：rename table 表名 to 表名

	  //为分类表category 改名成category2
	  rename table category to category2;

15. 修改表之修改字符集：alter table 表名 character set 字符集；

	  // 为分类表category 的编码表进行修改，修改成gbk
	  alter table category character set gbk;

DML

1. 插入一条数据：有两种格式：
   1）insert into 表名 values(值1，值2，值3，…值n);
   insert into student values(’张三’,‘男’,20);//数据顺序添加要按照表中的字段顺序,

   如果出现乱码的错误，就需要修改dos窗口的显示字符集，修改为GBK，set names gbk;
   set character_set_results=gbk;这是修改数据库的编码格式；
   注意：
   1）单引号的问题：在SQL语句中，对于"字符串类型"或者"日期类型"的值必须要加一对单引号，或者一对双引号；对于"数值"类型的值，可以不加单引号或者双引号。
   2)values(值列表)：“值列表"中出现的值的"数量”，和"顺序"必须和表中定义的字段的数量和顺序匹配，如果哪个值不填，可以用NULL占位。因为如果不写的话，什么都不写就会抛异常
   例：insert into student values(‘李四’,null,22);

   2）insert into 表名(字段1，字段2，…,字段n)values(值1，值2，…,值n)；
   只向指定的字段中填写值，剩下的字段自动填充NULL
   例如：
   insert into student(age,name) values(23,‘王五’);
   注意：
   1）字段列表:可以是表中的部分字段，而且可以不按顺序出现。。。
   2）后面的值列表：必须跟前面的字段列表的数量和顺序一致。
   3）没有出现在字段列表的其他列，在本次添加中自动添加NULL

   3）怎么选择这两个方法：
   a.第一种适合：对于一个表的所有字段，或者绝大部分字段进行添加。
   b.第二种适合：对一个表的少部分字段进行添加时，大部分字段使用NULL

2. 修改记录：update 表名 set 列名 = 值 ,列名 = 值,…,列名 = 值 （ where 条件）；
   1）修改张三的年龄为23岁：
   update student set age = 23 where name =‘张三’;
   2）修改张三的年龄为28岁，同时修改姓名张三丰
   update student set age = 28 ,name = ‘张三丰’ where name = ‘张三’；

3. 删除记录：delete from 表名 where 条件;(符号条件的记录全部删除)
   1)删除"李四"的那条数据
   delete from student where name = ‘李四’;
   如果没有条件，就会直接把表清空， 2）关于"清空表"
   1）delete from 表名;–>逐条删除，效率低；
   delete from 表名；是一条一条删除数据，不清空auto_increment记录数。
   2)truncate 表名;–>先记录表结构，然后摧毁表(直接删掉数据文件,把表也删除，)，最后按照原表的原结构重新创建一个新表 --效率高。会将auto_increment重置为零，从新开始。

DQL

1. 简单查询：
   select (字段列表) from (表名) where（条件）。
   分析：先执行from寻找表格，再执行条件where定位行，再执行select 定位列 筛选数据显示在结果

   1）使用别名：（给列名设定别名）
   select pname as ‘商品名称’,price as '价格’from product;
   说明:(1)as 是关键字，可以省略；不建议省略
   设置完别名之后，如果不保存，仅限此次不退出的访问可以使用别名进行访问。(查询语句不会改变表格原数据，所以设置别名不会改变字段名，只是此次可以使用，退出重启就不能再次使用了)

   2）去掉重复值：distinct
   比如：查看所有的商品价格，重复的价格只显示一次即可；
   select distinct price from product;

   3）对查询结果进行运算；
   查看所有商品的：商品名称和商品价格，将所有商品价格上涨100元显示；（表中的原数据不会更改，只会显示一下）
   select pname,price + 100 from product;

2. 条件查询_比较运算符:select…from…where 条件
   “>”:大于，用于判断某些字段的值是否大于某个值(常用于数值类型和日期类型)
   例如：查询商品价格大于2000的商品
   selectfrom product where price > 2000;
   查询生产日期大于2017-10-01的商品
   selectfrom product where prodate > ‘2017-10-01’;

   “>”：大于、"<"：小于、">="：大于等于、"<="：小于等于、"="：等于、"!="：不等于
   注意：from进行表定位，然后where进行行筛选，最后select进行列定位

3. 条件查询_逻辑运算符：对多个判断，最终产生一个结果。
   1）and : 与，语意"并且"
   例如：查询价格大于2000元的商品，并且生产日期大于2017-10-01的。
   注意：日期需要加单引号修饰。
   select*from product where price > 2000 and proDate > ‘2017-10-01’;

   2）or :或，语义"或者"
   例如：查询所有的服装商品，或者电脑类商品，
   select*from product where category_id = ‘服装’, or category_id =‘电脑’;

   3）not：非，语义"不是…"
   例如：查询所有的"非电脑"类商品；
   使用比较运算符：
   selectfrom product where category_id != ‘电脑’;
   使用逻辑运算符
   selectfrom product where not category_id = ‘电脑’;

注意：and 和 or 有优先级顺序，先执行and 在执行or ，如果and和or混合使用的时候，就需要添加括号了

4. 条件查询_范围查询：between…and…(常用于数值和日期)也可以用于查询字母a-z A-Z。
   between(包含—小值)…and(包含——大值)…
   1)例如：查询价格在2000元(包含)到8000元(包含)之间的商品信息
   使用比较运算符和条件运算符：
   selectfrom product where price >= 2000 and price <= 8000;
   使用between…and…
   selectfrom product where price between 2000 and 8000;

   查询生产日期在2017-10月份生产的商品
   select*from product where proDate bwteen ‘2017-10-01’ and ‘2017-10-31’;

5. 条件查询_in查询：代替多个or的等值查询
   1）例如：查询价格等于1000，2000，3000，4000，5000的商品信息
   使用比较运算符和条件运算符：
   selectfrom product where price = 1000 or price = 2000 or price = 3000 or price = 4000 or price = 5000;
   使用in语句：
   selectfrom product where price in(1000,2000,3000,4000,5000);
   查询所有的服装类、电脑类商品
   select*from product where category_id(‘服装’,‘电脑’);

6. 模糊查询_like;
   两个通配符:
   (1)%：通配任何长度的任何字符
   (2)（下划线）：通配一个长度任意的字符
   "李":以李开头的两个字符，即李后只有一个字符,后面有几个字符，就是有几个下划线。
   “花%”:以花开头，
   “%花”：以花结尾,
   “花”：名字就是花

   例如：查询商品中包含"花"的商品：
   selectfrom product where pname like ‘%花%’;
   例如：查询商品名称以"花"开头的；
   selectfrom product where pname like’花%’;
   例如查询生产日期是2017年10月生产的商品
   select*from product where proDate like’2017-10%’;

7. 查询"空"：
   1)添加数据：insert into product values(null,‘山东大鸭梨’，20，null,‘2017-10-10’);//category_id这列填null,
   查询:category_id 这列为null的记录；
   selectfrom product where category_id is null;
   2）添加数据【不建议使用】：insert into product values(null,‘山东大鸭梨’，20，’’,‘2017-11-10’);//category_id这列填：""空字符串
   查询：category_id 这列为空字符串的记录
   selectfrom product where category_id =’’";

排序
1) 升序：从小到大：
2) 降序：从大到小

1. 排序使用的语句：order by 排序字段 asc(升序–默认)/desc(降序)
   1）查询所有的商品信息，查询结果按价格升序排序。
   selectfrom product order by price asc;
   2)查询所有的服装类商品，结果按生产日期降序排序；
   selectfrom produc where category_id = ‘服装’ order by proDate desc;
2. 对多列进行排序：(先按第一个排序语句排序，如果第一个排序语句中的数据值相同，在用第二个排序语句排序)
   需求：查询所有的服装类商品,要求按：价格升序排序，如果价格相同，按生产日期降序排序；
   select*from product where category_id = ‘服装’ order by price asc ,proDate desc;
   说明:
   对多列排序，只有当第一列排序列的值相同时，才会对第二排序列进行排序，否则会忽略第二排序列。

聚合函数

1. count(字段名):统计指定列不为null的记录行数；
   1）查询所有服装类商品的数量
   selectfrom product where category_id =‘服装’;
   select count() from product where category_id =‘服装’;

如果pname 中有一个值为null
select count(pname) from product where category_id =‘服装’;//服装中有一个pname字段值为null，所以count会少一个，所以常用count(*);

2. sum(字段名):计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为‘0’；
   （1）查询服装类商品的总价格：
   select sum(price) from product where category_id = ‘服装’；

3. max(字段名)：计算指定列的最大值，如果指定列是字符串类型，那么就是用字符串排序运算。
   （1）查询服装类商品的最低价格
   select max(price) from product where category_id = ‘服装’；

4. min(字段名)：计算指定列的最小值，
   （1）查询服装类商品的最低价格
   select min(price)from product where category_id = ‘服装’;

5. avg（字段名）：计算指定列的平均值
   （1）查询服装类商品的平均价格
   select avg(price) from product where category_id = ‘服装’;

   注意：
   1）聚合函数的查询结果，只能包含聚合函数的结果列，不能包含其他列。即使包含也是没有意义的。因为聚合的结果是计算的结果，是和表中的任何字段没有任何关系，都不匹配，所以聚合函数的结果不要包含其他字段，即使包含了也不匹配，所以无意义。
   例如：查询服装类商品的最低价格，同时显示商品名称：
   select min(price),pname from product where category_id = ‘服装’;//错误的SQL语句，pname会显示一个无关的值。

分组

1. 需求：一条语句查询出每种商品的最高价格；
   1）首先要想到查询结果；
   类别 最高价格
   电脑 5000
   服装 2000
   食品 1000

   2)对种类进行分组，对价格进行聚合
   指定分组列，自动进行分组，每一类都是一组。
   然后对每组中的指定排序列，进行聚合，显示；
   select category_id,max(price) from product group by category_id; //先分组，再聚合。。。

2. 练习：有如下表：student
   id name sex age km
   1 张三 男 20 Java
   2 李四 男 22 Java
   3 王五 女 18 IOS
   4 周六 男 19 IOS
   5 周日 女 21 Java

   需求：
   1）查询每科学员的总数量
   select km,count() from student group by km;
   2).查询男、女学员各有多少人
   select sex,count() from student group by sex;
   3).查询男、女学员的最低年龄分别是多少？
   select sex,min(age) from student group by sex;
   4).对多列进行分组：查询各科男、女学员各有多少人？
   select km,sex,count(*) from student group by km,sex;

3. 分组的查询结果的列：只能包含"分组列"和聚合结果列，不能包含其他列，否则也是无意义的（因为即使包含也是不匹配的了）。

having语句:（对"聚合函数"的结果进行筛选）

1. 我们在"分组"聚合运算后，可能会对"聚合的结果"进行筛选， 例如：查询每种商品 最高价格，结果只保留最高价格大于1000元的记录，其他的结果不需要显示。。。
   select category_id,max(price) from product where max(price) > 1000 group by category_id ;//错误，首先这条语句就是错误的，运行就会报错。
   而且因为：where语句先执行，聚合函数后执行。所以在where时，聚合函数还没有执行。所以：where语句不能对"聚合函数"的结果进行筛选。

2. 要对"聚合函数"的结果进行筛选，要使用:having语句：
   select category_id,max(price) from product group by category_id having max(price) > 1000;//先分组，然后再聚合，再having判断各类商品最高价格大于1000的，显示出来

   having与where的区别:
   having是在分组后对数据进行过滤.
   where是在分组前对数据进行过滤
   having后面可以使用分组函数(统计函数)
   where后面不可以使用分组函数。

编写顺序和执行顺序

1. 编写顺序
   select…from…where…group by …having…order by…
2. 执行顺序
   (1)from
   (2) where
   (3)group by
   (4)聚合函数
   (5)having
   (6)select
   (7)order by
   如果where 中包含聚合函数，那么会先执行这个中的聚合函数吗？？？？？？？不会，这个语句就是错误的，写了只会报错，不会执行。

多表查询

多表查询：

1. 什么是"多表查询"？
   迪卡尔集
   select*from product,user ;
   出现的结果就是product中的个数 × user中数据的个数。
   即product中的数据任意匹配了user中的数据(交叉连接)，所以会出现这么多的数据。这样是不对的，所以需要添加限制条件，进行筛选，形成内连接。

2. 多表的查询的方式；
   一、内连接：又叫：等值连接，可以查询出左表和右表等值部分的记录(主键和外键的值相等)。查从表，从表中含有外键字段需要关联主表的主键字段，需要把主表的主键字段的值显示出来。
   1）隐式内连接：
   select * from 表1，表2 where 表1和表2的等值关系
   select * from product,category where product.category_id = category.id and 其他条件
   2）显示內连接：
   select * from 表1 inner join 表2 on 表1 和表2 的等值关系 where 其他条件；
   先根据on条件筛选出符合条件的数据，再根据where条件进行筛选

select * from product inner join category on product.category_id = category.id;
	

注意：from后面的表名，谁在左，谁在右都可以。谁在左，查询结果中这个字段会出现在最左边，但不影响

二、外连接
1）.左外连接：可以查询出"左表"的所有记录(包括等值的和不等值的)，右表的等值记录。(左表就是写在left join的左边的表)(右表的等值记录就是on后面的比较，相等的记录，即符号on后面的条件)

select * from 表1 left join 表2 on 表1 和表2 的等值关系where 其他条件
例如：
select * from product left join category on product.category_id = category.id;(可以查询出product表的所有记录，包括等值的和不等值的，以及右表中的等值部分的记录)
其实显示的就是显示左表，然后显示左表中引用右表中的主键字段的信息。

2）.右外连接：可以查询出"右表"的所有记录(包括等值的和不等值的)，左边的等值记录。（右表就是写在 right join的右边的表）（左表的等值记录就是on后面的比较，相等的记录，即符合on后面的条件的记录）

select * from 表1 right join 表2 on 表1和表2的等值关系 where 其他条件
例如：
select * from product right join category on product.category_id = category.id;

3. 三表以上的连接查询：(先想查询结果，判断需要几表联查)
   1）內连接：
   a）隐式內连接：(多对多)
   selectfrom 表1 ,表2,表3 where 表1和表2的等值关系 and 表2和表3的等值关系。(表2为中间表)。
   b）显示內连接：
   select from 表1 inner join 中间表 on 表1 和中间表的等值关系 inner join 表2 on 中间表和表2的等值关系。。。
   2)外连接：
   a)左外连接：
   select * from 表1 left join 中间表 on 表1和中间表的等值关系 left join 表2 on 中间表和表2的等值关系

   b）右外连接：
   select * from 表1 right join 中间表 on 表1和中间表的等值关系 right join 表2 on 中间表和表2的等值关系；

4. 关于"表别名"：
   1)当我们进行多表查询时，当时用某个"列名"时，很有可能两个表中有同名的列。这时就需要使用"表名"进行限定：
   例：select * from product,category where product.cid = category.cid;
   2）如果表名太长，使用不方便，这时可以为表起个别名:
   注意表别名，不用’‘修饰，只有列别名需要’'修饰。
   select p.name ,p.price,c.cname from product p, category c where p.cid = c.cid;
   注意：一旦为表起了别名，就不能在where 和 select后使用"表全名"了，只能使用"表别名"了。

子查询

1. 需求：查询商品价格高于"海尔"的商品信息。
   select * from product where price > 3000;这样不行，因为如果价格发生改变就需要修改代码
   select * from product where price > (select price from product where pname = “海尔”);
   先运行内部查询，然后拿内部查询的结果进行外部查询
   这个写在另一个查询内部的查询，就叫：子查询

2. 子查询通常作为外部查询的：
   1）查询条件使用;
   查询商品价格高于"海尔"的商品信息
   select * from product where price > (select price from product where pname = “海尔”);

2）作为虚拟表：
   1 查询商品价格高于"海尔"的商品信息
	select*from (select * from product where price > (select price from where pname = '海尔')) c;
注意：子查询作为虚拟表的时候，必须给这个虚拟表设定名称。否则不能算是一个完整的表。

   2 查询'化妆品'类的商品信息：(两个表关联查询)
 select * from product p ,(select * from category where cname = '化妆品') c where p.category_id = c.cid;