一、简介
SQL是结构化查询语言(Structured Query Language)的缩写。
(1)DDL:数据定义语言,定义数据库、数据表的结构。常用指令如create、drop、alter;
(2)DML:数据操纵语言,主要用来操作数据。常用指令如insert、update、delete;
(3)DCL:数据控制语言,定义访问权限、取消访问权限、安全设置等。常用指令如grant;
(4)DQL:数据查询语言。常用指令如select * from 子句 where 子句;
二、数据库CRUD操作
(1)创建数据库
创建数据库的三种方式如下:
1)采用默认字符集和校对规则 create database 数据库名称;(注意分号结尾) 2)指定字符集 create database 数据库名称 character set utf8;(指定字符集) 3)指定字符集和校对规则 create database 数据库名称 character set utf8 & collate 校对规则;(指定字符集和校对规则)
(2)查看数据库
1)查看数据库 show databases; 2)查看指定数据库的创建过程 show create database 数据库名称;
(3)修改数据库
# 修改数据库的字符集 alter database 数据库名称 character set 字符集;
(4)删除数据库
drop database 数据库名称;
(5)切换数据库
use 数据库名称;
(6)查看当前使用的数据库
select database();
三、数据表CRUD操作
(1)创建数据表
# 建表(备注:长度为字符个数,汉字和字母一样) create table 表名称 ( 字段 类型(长度) 约束, ... ... ); # java 和 sql的比较: int int char/String char(固定长度)/varchar(可变长度) 如:char(3)=>a--,varchar(3)=>a double double float float boolean boolean date 1)date yyyy-MM-dd 2)time hh:mm:ss 3)datetime 4)timestamp null txt(存放文本) null blob(二进制)约束:primary key,unique,not null;唯一约束:列里面内容唯一,但可以为空;主键约束:不能为空,且必须唯一;外键都是指向另外一张表的主键(唯一约束不可以);一张表只有一个主键(而唯一约束可以有多个)。
(2)查看数据表
# 查看表 show tables; # 查看表结构 desc 数据表名称; # 查看表定义 show create table 数据表名称;
(3)表中数据插入
insert into 数据表名 (列名1, 列名2, ...) values (值1, 值2, ...);
(4)修改数据表
1)添加列(add) alter table 表名称 add 列名 类型 约束; 2)修改列(modify) alter table 表名称 modify 列名 类型 约束; 3)修改列名(change) alter table 表名称 change 列名 新列名 类型; 4)删除列(drop) alter table 表名称 drop 列名; 5)修改表名(rename) rename table 表名称 to 新表名称; 6)修改表的字符集 alter table 表名称 character set 新字符集;
(5)删除数据表
drop table 表名称;
(6)查看表中数据
select * from 表名称;
(7)批量插入
insert into 数据表名 (列名1, 列名2, ...) values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...)...;
(8)删除表中指定记录
delete from 表名称 where 子句;
(9)delete和truncate的区别?
1)delete(DML):一条一条删除表中记录;
2)truncate(DDL):先删除表,再重建表;
其中,哪个效率高要看表中数据量。如果数据少,则delete高效;如果数据多,则truncate高效。
(10)更改表记录
update 表名 set 列名1 = 列的值, 列名2 = 列的值 [where条件];
(11)查询记录
格式:select [distinct] [*] [列名1, 列名2] from 表名 [where条件],其中distinct关键字的作用是去除重复数据。
1)简单查询
# 别名查询(as关键字,可以省略) select p.pname, p.price from product p; select p.pname, p.price from product as p; select pname as 名1, price as 名2 from product; # 去掉重复的值 select distinct price from product; # 运算查询(仅在查询结果上做了运算) select *, price * 1.5 from product; # 条件查询(where关键字) a.所有price > 60(>, >=, <=, =, !=, <>) b.逻辑运算(and, not,&nbs***bsp; c.between 小值 and 大值(price在60-100) # 模糊查询(like) a._代表一个字符; b.%代表多个字符;
2)复杂查询
# in:在某个范围内取值 where price in (1, 4, 5); # 排序查询(order by关键字) a.asc:ascend升序,desc:descend降序; b.查询默认升序 select * from product order by price asc;(默认升序) c.需求为名称中有小,再降序 select * from product where pname like "%小%" order by price desc; # 聚合函数(sum(), avg(), count(), max(), min()) a.查询价格总和 select sum(price) from product; b.注意:where后面不能接聚合函数; c.子查询:需求为查询price > 平均的全部商品 select * from product where price > (select avg(price) from product) # 分组(group by) a.根据cno分组,分组后统计商品个数 select cno, count(*) from product group by cno; b.按照cno分组,分组统计每组商品平均价格,且平均价格大于60 select cno, avg(price) from product group by cno having avg(price) > 60; c.注意:having 可以接聚合函数,出现在分组之后;where 不可接聚合函数,出现在分组之前;
3)注意:
a.编写顺序:select --> from --> where --> group by --> having --> order by;
b.执行顺序:from --> where --> group by --> having --> select --> order by。
四、多表查询
(1)修改表—添加外键约束
# 外键约束:foreign key,给product表cno加外键约束 alter table product add foreign key (cno) references category (cid); # 从分类表category中删除分类为5的记录:会删除失败 首先删除product中国呢分类为5的商品;
(2)一对多、多对多之间的建表原则
# 建库原则 通常一个项目/应用建一个数据库; # 多表之间的建表原则 a.一对多 如商品和分类。原则为:在多的一方添加外键,指向一的一方; b.多对多 如老师和学生,学生和课程。原则为:多建一张中间表,将多对多拆成一对多,至少要与2个外键,这两个外键分别指向原来的那张表; c.一对一 如公民和身份证。原则为:当成一对多,将其中任一表加一个外键【唯一】指向另外一张表。
五、复杂查询
(0)交叉连接查询(笛卡尔积)
查询出来的是两张表的乘积。如:select * from product, category where cno = cid;
(1)多表查询内连接(inner & outer可以省略)
1)隐式内连接
select * from product p, category c where p.cno = c.cid;
2)显式内连接
select * from product p inner join category c on p.cno = c.cid;
3)两者的区别
隐式是在查询出来结果的基础上做的where条件过滤;显式是带着条件去查询结果,显然显式效率更高。
(2)多表查询外连接
1)左外连接
# 左表全查出来,右表对应全为null select * from product p Left outer join category c on p.cno = c.cid;
2)右外连接
# 右表全查出来,左表对应全为null select * from product p Right outer join category c on p.cno = c.cid;
(3)分页查询(limit关键字)
# 每页数据10条 select * from product limit 0,10; # 起始索引从0开始 index表示显示第n页,每页显示3条; startIndex = (index - 1) * 3
(4)子查询
# 查询分类名称为手机数码的全部商品 select * from product where cno = (select cid from category wehre cname = "手机数码"); # 商品名称,分类信息 select pname, (select cname from category c where p.cno = c.cid) from product p;