数据库

一、数据库进入退出：

1.mysql -uroot -p 进入数据库 + 回车
2.exit; 退出数据库

二、数据库相关：

1.show databases; 查询数据库

2.create database db1; 创建一个数据库

3.show create database 数据库名; 查看数据库详情

4.create database 数据库名 character set gbk/utf8; 创建数据库指定字符集

5.drop database 数据库名; 删除数据库 注意！

6.use db1; 使用数据库

表相关

1.show tables; 查询所有表

2.show create table 表名; 查看表详情

3.create table 表名(字段1名字 字段1类型 ,字段2名字 字段2类型)engine=myisam charset=gbk/utf-8; 创建表指定引擎和字符集

4.desc 表单名; 查看表字段信息 desc db1;

5.drop table 表名; 删除表 drop table db1;

6.rename table 原名to新名 修改表名; rename table student to stu;

7.alter table 表名 engine=myisam charset=gbk/utf-8 修改引擎和字符集 alter table stu engine=myisam charset=gbk/utf-8

8.alter table 表名 drop 字段名; 删除表字段

9.alter table 表名 change 原名 新名 新类型; 修改字段名和类型

10.alter table 表名 modify 字段名 新类型 first/after xxx 修改字段类型和位置

- 查询语句书写顺序： select – from- where- groupby- having- order by -limit

- 查询语句执行顺序： from - where -group by -having - select - order by -limit

三、插入数据：

1.全表插入格式：insert into 表名 value(值1，值2); insert into db1 value(‘Tom’,30);

2.指定字段格式： insert into 表名(字段名1,字段2)value（值1,值2); insert into db1(name)value(‘jerry’);

3.批量插入: insert into db1 value(‘aa’,10),(‘bb’,20),(‘cc’,30); insert into user(name)value(‘xxx’),(‘yyy’),(‘ccc’);

4.插入中文数据： insert into db1 value(‘刘德华’,50); 如果报错执行以下命令: set names gbk;

5.查询数据： select 字段信息 from 表名 where 条件; select name/age/* from db1 条件;
select * from db1 where age<30; 查询年龄小与30的

6.修改数据： update 表名 set 字段名=值 where 条件; update db1 set age=18 where name=‘刘德华’;

7.删除数据： delete from 表名 where 条件; delete from db1 where name=‘xxx’;

四、SQL分类

1)DDL Data Definition Language
数据结构定义语言 - 用来定义数据库对象：库、表、列等；
包含:create,alter,drop语句 - 重点面试题 注意DDL不支持事务 即DDL操作后无法回滚
2)DML Data Manipulation Language
数据更改语言 - 包含:insert,update,delete
重点面试题 注意DML支持事务 即在非自动提交模式下 操作后可以回滚 -
3)DQL Data Query Language
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。 查询返回的结果集是一张虚拟表。
数据查询语言 - 包含:select
4)TCL Transaction Control Language
事务控制语言 - 包含:commit,rollback,savepoint保存点​
5)DCL Data Control Language
数据库控制语言,用来管理数据库的权限 - 包含：grant授权 revoke 取消授权

五、条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
– like模糊查询,%是通配符替代一个或多个字符
select * from item where name like ‘%联想%’;
– and,or
select * from item where name like ‘%ThinkPad%’ and detail like ‘%14%’;
select * from item where name like ‘%联想%’ or name like ‘%戴尔%’;
– >,<,>=,<=,!=,<>
select * from item where name like ‘%显示器%’ and price<=1000;
select * from item where name like ‘%显示器%’ and price<=1000 and price>=900;
select * from item where name like ‘%显示器%’ and price!=1299;
select * from item where name like ‘%显示器%’ and price<>1299;
– between m and n
select * from item where price between 500 and 1000;
– in,is,not
select * from item where category_id in (7,8,11,12);
select * from item where category_id=9 and image is not null;
select * from item where category_id=10 and name not like ‘%冰冷%’;
select * from item where price not between 500 and 1000;
select * from item where category_id not in (7,8,11,12);

六、排序

– order by 字段 asc/desc
– order by放在where之后
– asc是升序,desc降序
select * from item where name like ‘%联想%’ order by price asc;
– 省略排序方式时,默认为升序
select * from item where name like ‘%联想%’ order by price;
– 可以用多个字段同时排序
select * from item order by category_id desc,price asc

七、分页

– limit begin,size
– 写在order by之后
– begin是当前页的起始行号(从0开始)
– size是本页要显示的最大行数
select * from item order by price limit 10,10;

八、处理显示结果

– 拼接字符串
select concat(name,’ ‘,price,‘元’) as title,detail,stock from item;
– 计算(+ - * / %)
select name,detail,price*stock as total from item;
– 格式化日期
select name,price,date_format(upload_time,’%Y年%c月%d日 %H时%i分%s秒’) from item;
– 空值处理
select name,price,ifnull(image,‘无’) as image,ifnull(detail,‘无’) as detail from item;

九、聚合函数

– sum:求和
select sum(stock) as total_stock from item;
select category_id,sum(stock) as total_stock from item where category_id=7;
– 合计时,附带id,name等细节数据是无意义的-- 使用其他的聚合函数进行统计时,也是这样的
select id,name,sum(stock) as total_stock from item;
– avg:求平均值
select avg(price) as avg_price from item;
select category_id,avg(price) as total_price from item where category_id=7;
– count:求返回数据的行数-- 括号内写要统计的列的名字
select count() from item;
select count(id) from item;
select count(name) from item;
select count() from item where name like ‘%联想%’;
– max/min:求最大值/最小值
select max(price) from item;
select min(price) from item where name like ‘%联想%’;
​
特别注意 null值聚合函数一般都不计算

十、分组统计

– group by 字段
select category_id,sum(stock) from item group by category_id;
select category_id,avg(price) from item group by category_id;
– 数据库执行SQL的顺序– from -> where -> group -> select
– 下面SQL是错误的, 因为在where里用了聚合函数
select category_id,sum(stock) from item where sum(stock)>1000 group by category_id;
– 对分组数据进行筛选,应该在group by中写条件(having)
select category_id,sum(stock) from item group by category_id having sum(stock)>1000;
----​having与where的区别:
1.
having是在分组后对数据进行过滤；
where是在分组前对数据进行过滤 ；
2.
having后面可以使用分组函数(统计函数)；
where后面不可以使用分组函数；
3.
having是对分组后数据的约束​；
where是对分组前记录的条件，如果某行记录没有满足where子句的条件，那么这行记录不会参加分组；

– 非统计的条件可以写在where或having内
– 能写在where内的条件就写在这里,效率高
select category_id,sum(stock) from item where category_id>10 group by category_id;
select category_id,sum(stock) from item group by category_id having category_id>10;
– 查询学生文具和打印机的平均单价
select category_id,avg(price) from item where category_id in (11,13) group by category_id;
– 查询平均单价小于100的商品分类
select category_id,avg(price) from item group by category_id having avg(price)<100;
– 分组后排序
select category_id,max(price) from item group by category_id order by category_id desc;
select category_id,sum(pricestock) from item group by category_id order by category_id;
select category_id,sum(pricestock) as s from item group by category_id order by s desc;
​
注意： 凡和聚合函数同时出现的列名，则一定要写在group by 之后
例如：查询每个部门的部门编号以及每个部门的人数：
SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;

十一、关联查询

– 等值连接:查询出A和B相匹配的数据

select
  	u.id,
  	u.username,
  	u.password,
  	a.name,
  	a.address
from user as u,address as a
where u.id=a.user_id;

– 内连接:其作用与等值连接相同
– inner可以省略不写

select
  	u.id,
  	u.username,
  	u.password,
  	a.name,
  	a.address
from user as u
inner join address as a on u.id=a.user_id
where 1=1;

– 左外连接-- A left outer join B on 条件-- A left join B on 条件

select
  	u.id,
  	u.username,
  	a.name,
  	a.address
from user u
left join address a on u.id=a.user_id;

– 右外连接-- A right outer join B on 条件-- A right join B on 条件

select
  	u.id,
 	u.username,
 	a.name,
 	a.address
from user u
right join address a on u.id=a.user_id;

特别注意：笛卡尔积经常出面试题
当两表关联时，若没有写关联条件，则返回的总条目数是两表条目数的乘积，这个乘积就叫做笛卡尔积

–自关联

– 查询所有的分类,以及他们的父分类

select
 	c.id as cid,
	c.name as cname,
	p.id as pid,
  	p.name as pname
from category c
left join category p on c.parent_id=p.id;

– 1.查询出所有的二级分类,以及他们的父分类-- 查询一级分类:父亲为空的分类.
select id from category where parent_id is null;
– 查询二级分类:父亲是一级分类.

select * from category
where parent_id 
in(
  select id from category where parent_id is null
);

– 查询二级分类,以及他们的父亲.

select
	c.id cid,c.name cname,p.id pid,p.name pname
from category c
left join 
	category p on c.parent_id=p.id
where c.parent_id in(
	select id from category where parent_id is null
);

– 2.查询出所有的三级分类,以及他们的父分类

select
	c.id cid,c.name cname,p.id pid,p.name pname
from category c
left join
	 category p on c.parent_id=p.id
where c.id 
in (
	select category_id from item);

– 父亲为空的分类是一级分类
– pid为空的分类是一级分类select * from category where parent_id is null;
– 爷爷为空的分类就是二级分类-- 父亲的pid为空的分类是二级分类
– c-孩子;p-父亲;p.parent_id-爷爷;

select *
from category c
inner join category p on c.parent_id=p.id
where p.parent_id is null;

– 太爷爷为空的分类分类是三级分类-- 爷爷的pid为空的分类是三级分类
– c:孩子;p:父亲;g:爷爷;g.parent_id:太爷爷;

select
	c.id cid,c.name cname,
	p.id pid,p.name pname,
	g.id gid,g.name gname
from category c
inner join category p on c.parent_id=p.id
inner join category g on p.parent_id=g.id
where g.parent_id is null;

十二、索引

– 在没有创建索引的情况下,查询数据
– 1.0x秒select * from item2 where title=‘100’;
​
– 创建索引-- create index 索引名 on 表名(字段,字段,字段,…)
create index index_of_item2 on item2(title(10),selling_point(10),price);
​
– 在创建索引的情况下,再次查询数据
– 0.0x秒select * from item2 where title=‘100’;
​
– 删除索引drop index 索引名 on 表名;

十三、约束

数据库约束是为了保证数据的完整性(正确性)而实现的一套机制
1、主键约束(PK) primary key constraint 唯一且不为空

alter table Student  
add constraint PK_Student primary key(sId)

其中constraint 表示约束，PK_Student为约束名，primary key(sId)指定为哪一列添加主键约束，其中的sId表示添加约束的字段。
2、唯一约束(UQ) unique constraint唯一，允许为空，即可以再其中出现null值，但只能出现一次

alter table Student  
add constraint UQ_Student_sNo unique(sNo)  

unique(sNo)中的sNo表示添加unique约束的字段名
3、默认约束(DF) default constraint默认值

alter table Student  
add constraint DF_Student_sSex default('男') for sSex 

为student表中的sSex字段设置默认值为‘男’，即当该字段为填入数据时，数据库将自行为其加上数据内容为‘男’的数据，其中DF_Student_sSex为默认约束名。
注：以上创建的主键约束、唯一约束均存在与数据库的‘ 键’目录下，而默认约束在位于‘约束’目录下
​
4、检查约束(CK) check constraint范围以及格式限制

alter table Student  
add constraint CK_Student_sSex check (sSex='男' or sSex='女') 

为student表中的sSex字段添加内容检查约束，只允许添加的内容为‘男’或者是‘女’
5、非空约束(NN) not null
create table student(id int primary key auto_increment,age int not null);
6、外键约束(FK)foreign key constraint
表关系

alter table student  
add constraint FK_Student_sClassId foreign key (sClassId) references Class(cId) 

删除主表中数据的时候，必须先删除子表中对应的数据，否则执行SQL语句时，数据库报错
提到外键，不得不提的是级联删除以及级联修改

alter table Student  
add constraint FK_Student_sClassId foreign key (sClassId) references Class(cId)  

–级联删除 on delete cascade
–级联修改 on update cascade 即当删除主表中的数据时，子表中与其有关的数据都将被删除。因此，此外键的创建方法在使用时需谨慎考虑

删除约束

alter table Student
drop constraint CK_Student_sAge

十四、事务

​ （１）什么是事务？

​ 指的是为了完成某个业务而对数据库进行一系列操作，这些操作要么全部成功，要么全部失败。、

​ （２）事务有哪些特点（ACID）？

​ １、Atomicity(原子性): 即事务所涉及的各个操作要么全部成功，要么全部失败。

​ ２、Consistency(一致性)：即事务完成之后，只有合法的数据才能写入数据库。

​ 3、Isolation(隔离性)：多个事务可以同时执行，能一定程度上保证互不影响。

​ 4、Durability(持久性)：事务提交之后，数据最终会保存在数据库当中（硬盘上）。

注：mysql数据库要将数据库引擎设置为"innodb"，才能支持事务。

十五、视图

（１）什么是视图？

在原有表或者视图的基础上建立的虚拟表。
​ 注：数据库只存储视图的定义

（２）如何创建视图?

​ create view 视图名[字段清单] as select语句
注：对视图（单表）进行插入等操作，同时会影响到原来的表。

（３）如何删除视图?

​ drop view 视图名

（４）为什么要使用视图？

​ 1）安全性：用户（开发人员）只能查询或者修改他们所能看到见的数据。
​ 比如有一些表，不能够让开发人员直接访问。
​ ２）简单化：可以将一些复杂的查询（比如多张表的join查询）创建为一个视图，调用者就不用关心如果写这个复杂的查询了。
​ ３）逻辑独立性：可以屏蔽真实表结构变化带来的影响。

视图的优点

(1)视图能简化用户的操作
(2)视图机制可以使用户以不同的方式查询同一数据
(3)视图对数据库重构提供了一定程度的逻辑独立性
(4)视图可以对机密的数据提供安全保护

参考代码：
create database jsd1912db default character set utf8;
use jsd1912db;

create table t_emp(
	id int primary key auto_increment,
	ename varchar(50),
	salary decimal(8,2),
	age int
);
insert into t_emp values(null,'Sally',10000,22);
insert into t_emp values(null,'Tom',20000,32);

create view v_emp as select * from t_emp;

select * from v_emp;

create view v_emp2(ename,salary) as select ename,salary from t_emp;

select * from v_emp2;

insert into v_emp values(null,'King',30000,34);

create table t_dept(
	id int primary key,
	name varchar(50),
	location varchar(100)
);
insert into t_dept values(100,'财务部','北京');
insert into t_dept values(200,'技术部','上海');

create table t_staff(
	id int primary key auto_increment,
	name varchar(50),
	age int,
	dept_id int
);
insert into t_staff values(null,'张三',33,100);
insert into t_staff values(null,'李四',35,100);
insert into t_staff values(null,'王五',22,200);

create view v_staff_dept(sname,dname,location) 
as select s.name,d.name,d.location from t_staff s join t_dept d 
on s.dept_id = d.id;

十六、存储过程

1、什么是存储过程？
是一组存储在数据库中为了完成某个特定功能的sql语句。

2、如何创建存储过程？　
​create procedure 存储过程名(参数)
注：共有三种参数类型(IN,OUT,INOUT)
create procedure 存储过程名([IN | OUT | INOUT] 参数名　参数类型)

IN: (缺省值), 表示这是一个输入参数，在调用存储过程时，该参数的值必须指定，在存储过程中修改该参数的值不能返回。
​OUT: 表示这是一个输出参数，该参数值可以在存储过程内部被改变，并且可返回。
IN/OUT参数：表示这是一个输入、输出参数。

3、如何调用存储过程?
​ call 存储过程名(参数值)

参考代码：
delimiter //这句话的作用是将结束符号由";"改为"//"。
避免存储过程在执行时不完整。

delimiter //
create procedure getEmp()
begin
	select * from t_emp;
end
//
delimiter ;

call getEmp();
call getEmp;

delimiter //
create procedure getEmp2(IN sid int)
begin
	select * from t_emp where id=sid;
end
//
delimiter ;

call getEmp2(1);


delimiter //
create procedure getEmp3(out s1 decimal(8,2))
begin
   select max(salary) into s1 from t_emp;
end
//
delimiter ;

call getEmp3(@sal);

select @sal;

@sal为会话变量（打开另外一个终端，该变量会失效）

public class CallProcedureDemo {
	
	/**
	 * 不带参的存储过程
	 */
	public static void demo1() throws Exception {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			//调用存储过程，要用CallableStatement
			CallableStatement cs = 
					conn.prepareCall("{call getEmp}");
			ResultSet rs = cs.executeQuery();
			while(rs.next()) {
				int id = rs.getInt("id");
				String ename = rs.getString("ename");
				BigDecimal salary = 
						rs.getBigDecimal("salary");
				int age = rs.getInt("age");
				System.out.println(id + " " + ename 
						+ " " + salary + " " + age);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally{
			DBUtil.close(conn);
		}
		
	}
	
	/**
	 * 带有IN参数的存储过程
	 */
	public static void demo2() throws Exception {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			//调用存储过程，要用CallableStatement
			CallableStatement cs = 
					conn.prepareCall("{call getEmp2(?)}");
			cs.setInt(1, 2);
			ResultSet rs = cs.executeQuery();
			while(rs.next()) {
				int id = rs.getInt("id");
				String ename = rs.getString("ename");
				BigDecimal salary = 
						rs.getBigDecimal("salary");
				int age = rs.getInt("age");
				System.out.println(id + " " + ename 
						+ " " + salary + " " + age);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally{
			DBUtil.close(conn);
		}
	}
	
	/**
	 * 带有out参数的存储过程
	 */
	public static void demo3() throws Exception {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			//调用存储过程，要用CallableStatement
			CallableStatement cs = 
					conn.prepareCall("{call getEmp3(?)}");
			//输出参数要使用registerOutParameter方法
			cs.registerOutParameter(1, Types.DECIMAL);  
			cs.execute();
			System.out.println(cs.getBigDecimal(1));
			
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally{
			DBUtil.close(conn);
		}
	}
	

	public static void main(String[] args) throws Exception {
		   demo3();
	}	

}

如何优化SQL语句？

1. 建索引；
2. 减少表之间的关联；
3. 简化查询字段，尽量返回少量数据；
4. 尽量使用PreparedStatement查询，不要用Statement；
5. 优化SQL，不要让SQL语句做全表扫描，应该表索引。把数据量大的表放在前面。

面试题

​