一、SQL语句分类
二、常用SQL语句
1、DDL
–操作数据库
- 创建
a. create database db1 ;
b. create database if not exits db1; - 查询
a. show databases; - 删除数据库
a. drop database db1;
b. drop database if exits db1; - 使用数据库
a. select database(); --查看当前数据库
b. use db1; --进入
–操作表(CRUD)
- 创建(create)
create table 表名(
字段名1 数据类型1,
字段名2 数据类型2,
字段名3 数据类型3,
…
字段名n 数据类型n
); - 查询(Retrieve)
● 当前数据库的所有表
○ show tables;
● 查看表的结构
○ desc func; - 修改(Update)
1)修改表名
alter table 表名 rename to 新的表名;
2)添加一列
alter table 表名 add 列名 数据类型;
3)修改数据类型
alter table 表名 modify 列名 新数据类型;
4)修改列名和数据类型
alter table 表名 change 列名 新列名 新数据类型
5)删除列
alter table 表名 drop addr; - 删除(Delete)
1)删除表
drop table 表名;
2)删除表时候先判断
drop table if exits 表名;
2、DML
–对数据的增删改的操作 - 添加
1)给指定地列添加数据
insert into 表名(列名1,列名2,…) values(值1,值2,…);
2)给全部列添加数据
insert into 表名(所有列名) values(所有列的值)
insert into 表名 values(… …);
3)批量添加数据
insert into 表名 values(),(),(); - 修改
update 表名 set 列名1=值1,列名2=值2,… [where 条件]; - 删除
delete from 表名 [where 条件];
3、DQL
–对数据查的操作(单表)
查询语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定 - 基础查询
1)查询多个字段
select 字段列表 from 表名 [where 条件];
select * from 表名;
2)去除重复记录
select distinct 字段列表 from 表名;
3)起别名
AS;as也可以省略 - 条件查询(WHERE)
select 字段列表 from 表名 where 条件列表
in (1,2,3)
例题:
● 查询英语成绩为null的同学
select * from student where english is null;
注意:null值得比较只能使用is /is not
模糊查询
通配符:
● _ 单个任意字符
● % 多个任意字符
例题:
● 查询姓张的
select * from student where name like ‘张%’;
● 查询第二个字是花的
select * from student where name like ‘_花%’;
● 查询名字包含‘德’的学生
select * from student where name like ‘%德%’
- 分组查询(GROUP BY)
聚合函数:
● count(列名):统计数量(一般选用不为null的列)
● max(列名):最大值
● min(列名):最小值
● sum(列名):求和
● avg(列名):平均值
特点:
null值不参与聚合函数运算
使用;
select 聚合函数(列名) from 表名;
语法:
select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤];
where和having的区别;
● 执行时机不一样:where是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤
● 可判断的条件不一样:where不能对聚合函数进行判断,having可以
● 执行顺序:where>聚合函数>having
例题:
注意:
○ where查询的是分组字段和聚合函数字段,其他无意义
○ 因为where先于group by执行,当执行where的时候还没有分组,没有东西可以数,所以聚合函数只能放在比group by先执行的指令里
例题
● 查询男同学和女同学的各自的数学平均分
select sex,avg(math) from student group by sex;
● 查询男同学和女同学的各自的数学平均分,以及各自人数
select sex,avg(math),count(*) from student group by sex;
● 查询男同学和女同学的各自的数学平均分,以及各自人数,要求:分数低于70的不参与分组
!!肯定是分组前排除了呀!!
select sex,avg(math),count(*) from student where score>=70 group by sex;
● 查询男同学和女同学的各自的数学平均分,以及各自人数,要求:分数低于70的不参与分组,人数>2
select sex,avg(math),count(*) from student where score>=70 group by sex having count(*)>2;
- 排序查询(ORDER BY)
语法:
select 字段列表 from 表名 order by 排序字段名1 [排序方式1],排序字段名1 [排序方式1],…;
排序方式:
ASC:升序(默认)
DESC:降序
例题:
● 按照数学成绩降序,数学成绩一样的,按照英语成绩升序
select * from student order by math desc,english asc;
- 分页查询(LIMIT)
语法:
select 字段列表 from 表名 limit 起始索引,查询条目数;
计算:起始索引=(当前页码-1)*每页显示的条数
注意:
limit是MySQL的
Oracle分页使用rownumber
SQL Server使用top
例题:
● 从0开始,查3条
select * from stu limit 0,3;
● 每页显示3条数据,查询第一(二、三)页数据
用公式算出起始索引
4、创建索引
- 使用 CREATE INDEX 语句
可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引,但该语句不能创建主键。
语法:CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC]) - 使用 CREATE TABLE 语句
索引也可以在创建表(CREATE TABLE)的同时创建。在 CREATE TABLE 语句中添加以下语句。语法格式:
语法:
● 索引 KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)
● 主键索引:CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)
● 唯一索引:UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…) - 使用 ALTER TABLE 语句
CREATE INDEX 语句可以在一个已有的表上创建索引,ALTER TABLE 语句也可以在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时,可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项。
语法:
● 索引:ALTER TABLE table_name ADD INDEX index_name ( column )
● 主键:ALTER TABLE table_name ADD PRIMARY KEY ( column )
● 唯一:ALTER TABLE table_name ADD UNIQUE ( column )
● 组合:ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
三、约束
–对表的数据进行限制:保证数据的正确性
概念:
● 作用于表中列上的规则,用于限制加入表的数据
● 约束的存在保证了数据库的数据写、有效性和完整性
约束的分类:
● 非空约束 NOT NULL :非空
● 唯一约束 UNIQUE :只能有一个
● 主键约束 PRIMARY KEY :非空+唯一
● 检查约束 CHECK :保证列中的值满足某一条件
● 默认约束 DEFAULT :未指定值使用默认值
● 外键约束 FOREIGH KEY :外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性
使用:
create table emp(
id int prmary key, -- 主键
ename varchar(10) not null unique,-- 非空唯一
data not null,
salary not null,
bonua double(7,2) dedault 0 -- 默认值
);
外键约束
//必须先创建部门表,在创建员工表
create table dept(
id primary key,
dep_name varchar(10),
addr varchar(10),
);
create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键dep_id关联dept的主键
constraint fk_emp_dep foreign key(dep_id) reference dep(dep_id)
);
//使用外键约束来关联两张表:保证数据一致性和完整性
语法:
1)添加外键约束
● 创建表时添加外键约束
create table 表名(
列名 数据类型,
…
[constraint] [外键名称] foreign key(外键列名) reference 主表(主表列名);
);
● 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key(外键名称) reference 主表(主表列名);
2)删除外键约束
alter table 表名 drop foreign key 外键名称;
四、数据库设计
–业务系统有那些数据,数据用那些表保存,表与表之间的关系
-
简介
a. 建立数据库中的表结构以及标语表之间关联关系的过程
b. 数据库设计的步骤:
ⅰ. 需求分析
ⅱ. 逻辑分析(E-R图–实体-关系图)
ⅲ. 物理设计
ⅳ. 维护设计 -
表关系之一对多
部门和员工
实现方式:
在多的一方建立外键,指向另一方的主键 -
多对多
商品和订单
实现方式:
建立第三张中间表,表中至少包含两个外键,分别关联两方主键 -
一对一
a. 用户和用户详情
b. 一对一关系常用于表的拆分,常用的放在一张,提高查询性能
c. 实现方式:在任意一方加入外键,关联另一方主键,并设置外键为唯一UNIQUE
五、多表查询
–在多张表联合查询数据
-
连接查询
a. 内连接
ⅰ. AB
ⅱ. 语法- 隐式内连接:select 字段列表 from 表1,表2… where 条件;
select * from dept,emp where dept.id=emp.dep_id; - 显示内连接:select 字段列表 from 表1 [inner] join 表2 on 条件;
select * from emp inner join dept on emp.dep_id=dept.id;
b. 外连接
ⅰ. 左外连接 - A+AB
- 语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;
select * from emp left join dept on emp.dept_id=dept.id;
ⅱ. 右外连接 - B+AB
- 语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;
select * from emp right join dept on emp.dept_id=dept.id;
- 隐式内连接:select 字段列表 from 表1,表2… where 条件;
-
子查询
a. 可以根连接查询转换
b. 嵌套查询
c. 查询结果不同、作用不同
ⅰ. 单行单列- 作为条件值,使用= != > <等条件进行条件判断
- 语法:select 字段列表 from 表 where 字段名=(子查询);
- 例如:查询工资高于猪八戒的员工信息
select * from emp where salary >(select salary from emp where name=‘猪八戒’);
ⅱ. 多行单列 - 作为条件值,使用in等关键字进行条件判断
- 语法:select 字段列表 from 表 where 字段名 in (子查询);
- 例如:查询财务部和市场部所有的员工信息
select * from emp where dept_id in
(select did from dept where dname=‘财务部’ or dname=‘市场部’);
ⅲ. 多行多列 - 作为虚拟表
- 语法:select 字段列表 from (子查询) where 条件;
- 例如:查询入职日期是‘2011-11-11’之后的员工信息和部门信息
a. 查询入职日期是‘2011-11-11’之后的员工信息
select * from emp where date>‘2011-11-11’;
b. 查询员工信息和部门信息
select * from emp ,dept where emp.dept_id=dept.id;
总:先在员工中进行筛选,在做内连接
select * from (select * from emp where date>‘2011-11-11’) t1 ,dept t2
where t1.dept_id=t2.id;
-
多表查询案例
说明:emp的薪资等级在salarygrade查看
题目:
1) 查询所有的员工信息。员工编号、姓名、工资、职务名称、职务描述
select t1.id,t1.ename,t1.salary,t2.jname,t2.description
from emp t1,job t2 where t1.job_id=t2.id;
2)查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
select t1.id,t1.name,t1.salary,t2.jname,t2.description,t3.dname,t3.loc
from emp t1,job t2,dept t3 where t1.job_id=t2.id and t1.dep_id=t3.id;
3)查询员工姓名,工资,工资等级
select t1.name,t1.salary,t2.grade from emp t1,salarygrade t2
where t1.salary between t2.losalary and t2.hisalary;
4)查询员工姓名、工资、职务名称、职务描述、部门名称、部门位置、工资等级
内连接的隐士连接
select t1.name,t1.salary,t3.jname,t3.description,t4.dname,t4.loc,t2.grade
from emp t1,salarygrade t2,job t3,dept t4
where t1.job_id=t3.job and t1.dept_id=t4.did t1.salary between t2.losalary and t2.hisalary;
5)查询部门编号、部门名称、部门位置、部门人数
select t2.id,t2.dname,t2.loc,count(*) from emp,dept
where emp.dept_id=dept.id groupy by t1.dept_id;
``