常用数据库介绍
- MySQL:早期是免费的,开源。属于中小型数据库。在管理5000万条数据的时候与Oracl性能差不多。目前已被Oracle(甲骨文公司)收购,从6.x版本开始收费
- Oracle:收费数据库,Oracle公司出品,属于大型数据库软件。。目前世界上最具竞争力的,顶级数据库软件
- SQLServer:微软公司的。中型数据库软件,收费的。管理界面非常友好,使用方便。
- DB2:IBM公司的数据库产品,收费的。常应用在银行系统中。
- 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
- 创建数据库:create database 数据库名;create database 数据库名 character set “字符集”;
- 查看数据库MySQL服务器中的所有的数据库:show databases;
- 删除数据库:drop database 数据库名称;
- 查看正在使用的数据库:select database();//调用了一个函数
- 切换数据库:use 据库名;
- 创建表语句(同时指定列):列于列中间用,隔开。
create table 表名( name varchar(100),//列名 数据类型(长度) [约束] sex char(1),//列名2 数据类型(长度) [约束] age int //列名3 数据类型(长度) [约束] );
- 查看数据库中的所有表:show tables;
- 查看表结构:desc 表名;//查看有哪几个列,每一行是哪个字段
- 删除表:drop table 表名;
- 修改表之添加列:alter table 表名 add 列名 类型(长度) [约束]
//为分类表添加一个新的字段为`desc`类型varchar(20)
alter table category add `desc` varchar(20);
- 修改表之修改列的类型长度:alter table 表名 modify 列名 类型(长度) [约束]
//为分类表的描述字段进行修改,类型varchar(50),添加约束not null
alter table category add `desc` varchar(20);
- 修改表之修改列名(可以在修改列名的同时修改类型长度):alter table 表名 change 旧列名 新列名 类型(长度) [约束]
//为分类表的分类名称字段进行更换,更换为description varchar(30)
alter table category change `desc` description varchar(30);
- 修改表之删除列:alter table 表名 drop 列名
//删除分类表中的description这列
alter table category drop description
- 修改表之修改表名:rename table 表名 to 表名
//为分类表category 改名成category2
rename table category to category2;
- 修改表之修改字符集:alter table 表名 character set 字符集;
// 为分类表category 的编码表进行修改,修改成gbk
alter table category character set gbk;
DML
-
插入一条数据:有两种格式:
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)没有出现在字段列表的其他列,在本次添加中自动添加NULL3)怎么选择这两个方法:
a.第一种适合:对于一个表的所有字段,或者绝大部分字段进行添加。
b.第二种适合:对一个表的少部分字段进行添加时,大部分字段使用NULL -
修改记录:update 表名 set 列名 = 值 ,列名 = 值,…,列名 = 值 ( where 条件);
1)修改张三的年龄为23岁:
update student set age = 23 where name =‘张三’;
2)修改张三的年龄为28岁,同时修改姓名张三丰
update student set age = 28 ,name = ‘张三丰’ where name = ‘张三’; -
删除记录:delete from 表名 where 条件;(符号条件的记录全部删除)
1)删除"李四"的那条数据
delete from student where name = ‘李四’;
如果没有条件,就会直接把表清空, 2)关于"清空表"
1)delete from 表名;–>逐条删除,效率低;
delete from 表名;是一条一条删除数据,不清空auto_increment记录数。
2)truncate 表名;–>先记录表结构,然后摧毁表(直接删掉数据文件,把表也删除,),最后按照原表的原结构重新创建一个新表 --效率高。会将auto_increment重置为零,从新开始。
DQL
-
简单查询:
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; -
条件查询_比较运算符:select…from…where 条件
“>”:大于,用于判断某些字段的值是否大于某个值(常用于数值类型和日期类型)
例如:查询商品价格大于2000的商品
selectfrom product where price > 2000;
查询生产日期大于2017-10-01的商品
selectfrom product where prodate > ‘2017-10-01’;“>”:大于、"<":小于、">=":大于等于、"<=":小于等于、"=":等于、"!=":不等于
注意:from进行表定位,然后where进行行筛选,最后select进行列定位 -
条件查询_逻辑运算符:对多个判断,最终产生一个结果。
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混合使用的时候,就需要添加括号了
-
条件查询_范围查询: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’; -
条件查询_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(‘服装’,‘电脑’); -
模糊查询_like;
两个通配符:
(1)%:通配任何长度的任何字符
(2)(下划线):通配一个长度任意的字符
"李":以李开头的两个字符,即李后只有一个字符,后面有几个字符,就是有几个下划线。
“花%”:以花开头,
“%花”:以花结尾,
“花”:名字就是花例如:查询商品中包含"花"的商品:
selectfrom product where pname like ‘%花%’;
例如:查询商品名称以"花"开头的;
selectfrom product where pname like’花%’;
例如查询生产日期是2017年10月生产的商品
select*from product where proDate like’2017-10%’; -
查询"空":
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) 降序:从大到小
- 排序使用的语句:order by 排序字段 asc(升序–默认)/desc(降序)
1)查询所有的商品信息,查询结果按价格升序排序。
selectfrom product order by price asc;
2)查询所有的服装类商品,结果按生产日期降序排序;
selectfrom produc where category_id = ‘服装’ order by proDate desc; - 对多列进行排序:(先按第一个排序语句排序,如果第一个排序语句中的数据值相同,在用第二个排序语句排序)
需求:查询所有的服装类商品,要求按:价格升序排序,如果价格相同,按生产日期降序排序;
select*from product where category_id = ‘服装’ order by price asc ,proDate desc;
说明:
对多列排序,只有当第一列排序列的值相同时,才会对第二排序列进行排序,否则会忽略第二排序列。
聚合函数
- 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(*);
-
sum(字段名):计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为‘0’;
(1)查询服装类商品的总价格:
select sum(price) from product where category_id = ‘服装’; -
max(字段名):计算指定列的最大值,如果指定列是字符串类型,那么就是用字符串排序运算。
(1)查询服装类商品的最低价格
select max(price) from product where category_id = ‘服装’; -
min(字段名):计算指定列的最小值,
(1)查询服装类商品的最低价格
select min(price)from product where category_id = ‘服装’; -
avg(字段名):计算指定列的平均值
(1)查询服装类商品的平均价格
select avg(price) from product where category_id = ‘服装’;注意:
1)聚合函数的查询结果,只能包含聚合函数的结果列,不能包含其他列。即使包含也是没有意义的。因为聚合的结果是计算的结果,是和表中的任何字段没有任何关系,都不匹配,所以聚合函数的结果不要包含其他字段,即使包含了也不匹配,所以无意义。
例如:查询服装类商品的最低价格,同时显示商品名称:
select min(price),pname from product where category_id = ‘服装’;//错误的SQL语句,pname会显示一个无关的值。
分组
-
需求:一条语句查询出每种商品的最高价格;
1)首先要想到查询结果;
类别 最高价格
电脑 5000
服装 2000
食品 10002)对种类进行分组,对价格进行聚合
指定分组列,自动进行分组,每一类都是一组。
然后对每组中的指定排序列,进行聚合,显示;
select category_id,max(price) from product group by category_id; //先分组,再聚合。。。 -
练习:有如下表: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; -
分组的查询结果的列:只能包含"分组列"和聚合结果列,不能包含其他列,否则也是无意义的(因为即使包含也是不匹配的了)。
having语句:(对"聚合函数"的结果进行筛选)
-
我们在"分组"聚合运算后,可能会对"聚合的结果"进行筛选, 例如:查询每种商品 最高价格,结果只保留最高价格大于1000元的记录,其他的结果不需要显示。。。
select category_id,max(price) from product where max(price) > 1000 group by category_id ;//错误,首先这条语句就是错误的,运行就会报错。
而且因为:where语句先执行,聚合函数后执行。所以在where时,聚合函数还没有执行。所以:where语句不能对"聚合函数"的结果进行筛选。 -
要对"聚合函数"的结果进行筛选,要使用:having语句:
select category_id,max(price) from product group by category_id having max(price) > 1000;//先分组,然后再聚合,再having判断各类商品最高价格大于1000的,显示出来having与where的区别:
having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤
having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
编写顺序和执行顺序
- 编写顺序
select…from…where…group by …having…order by… - 执行顺序
(1)from
(2) where
(3)group by
(4)聚合函数
(5)having
(6)select
(7)order by
如果where 中包含聚合函数,那么会先执行这个中的聚合函数吗???????不会,这个语句就是错误的,写了只会报错,不会执行。
多表查询
多表查询:
-
什么是"多表查询"?
迪卡尔集
select*from product,user ;
出现的结果就是product中的个数 × user中数据的个数。
即product中的数据任意匹配了user中的数据(交叉连接),所以会出现这么多的数据。这样是不对的,所以需要添加限制条件,进行筛选,形成内连接。 -
多表的查询的方式;
一、内连接:又叫:等值连接,可以查询出左表和右表等值部分的记录(主键和外键的值相等)。查从表,从表中含有外键字段需要关联主表的主键字段,需要把主表的主键字段的值显示出来。
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;
-
三表以上的连接查询:(先想查询结果,判断需要几表联查)
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的等值关系; -
关于"表别名":
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后使用"表全名"了,只能使用"表别名"了。
子查询
-
需求:查询商品价格高于"海尔"的商品信息。
select * from product where price > 3000;这样不行,因为如果价格发生改变就需要修改代码
select * from product where price > (select price from product where pname = “海尔”);
先运行内部查询,然后拿内部查询的结果进行外部查询
这个写在另一个查询内部的查询,就叫:子查询 -
子查询通常作为外部查询的:
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;