1.初识数据库
数据库(Database)是按照数据结构来组织、 存储和管理数据的仓库。在1990年以后,数据管理不再是存储和管理数据,而是转变成用户所需要的各种数据管理的方法。数据库具有能存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的作用。数据库系统在各个方面都得到了广泛的应用。
1.1数据库分类
①关系型数据库:关系型数据库模型是把复杂的数据结构归结为简单的二元关系(即二维表格形式)。在关系型数据库中,对数据的操作几乎全部建立在一个或多个关系表格上,通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。其实,目前关系型数据库的具体产品有:Oracle和MySQL。其中Oracle在数据库领域是占领导地位的。
②键值数据库:键值数据库就类似传统语言中使用的哈希表。可以通过key来添加、查询或者删除数据库,因为使用key主键访问,所以会获得很高的性能及扩展性。键值数据库主要使用一个哈希表,这个表中有一个特定的键和一个指针指向特定的数据。Key/value模型对于IT系统来说的优势在于简单、易部署、高并发;典型产品:Memcached、Redis、MemcacheDB
③列存储数据库:列存储数据库将数据存储在列族中,一个列族存储经常被一起查询的相关数据,比如人类,我们经常会查询某个人的姓名和年龄,而不是薪资。这种情况下姓名和年龄会被放到一个列族中,薪资会被放到另一个列族中。这种数据库通常用来应对分布式存储海量数据;这种数据库的典型产品有:Cassandra、HBase
④面向文档数据库:面向文档数据库会将数据以文档形式存储。每个文档都是自包含的数据单元,是一系列数据项的集合。每个数据项都有一个名词与对应值,值既可以是简单的数据类型,如字符串、数字和日期等;也可以是复杂的类型,如有序列表和关联对象。数据存储的最小单位是文档,同一个表中存储的文档属性可以是不同的,数据可以使用XML、JSON或JSONB等多种形式存储。这种数据库的典型产品有:MongoDB、CouchDB;
⑤图形数据库:图形数据库允许我们将数据以图的方式存储。实体会被作为顶点,而实体之间的关系则会被作为边。比如我们有三个实体,Steve Jobs、Apple和Next,则会有两个“Founded by”的边将Apple和Next连接到Steve Jobs。这种数据库的典型产品有:Neo4J、InforGrid。
其中,关系型数据库是使用最广泛,也是使用人数最多的。
1.2数据库语言
①DDL(数据定义语言)
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等
②DCL(数据控制语言)
数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等。
③DML(数据操作语言)
数据操纵语言DML主要有三种形式:
1) 插入:INSERT
2) 更新:UPDATE
3) 删除:DELETE
④DQL(数据查询语言)
数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE子句组成的查询块:
2.语言介绍
2.1DDL(数据定义语言)
①创建数据库
CREATE {DATABASE | SCHEMA} db_name
②创建数据表
CREATE [TEMPORARY] TABLE tablename(数据名 数据类型);
③修改数据库表格
ALERT TABLE tablename CHANGE 原列名 修改列名 修改后的数据类型;
2.2DCL(数据控制语言)
①创建用户
#创建用户
#create user '用户名'@'主机名' identified by '密码';
#注意:主机名可以是localhost,任意主机可以为%,表示限制登陆ip
create user 'test'@'localhost' identified by '123456';
②修改用户密码
普通用户修改密码
#mysql 8.0之前版本
#普通用户:登陆自己的账号后 set password = password('密码');
set password = password('123321');
#root用户给普通用户修改密码 set password for '用户名'@'主机名' = password ('密码');
set password for 'test'@'localhost' = password ('123');
#mysql8.0之后
#alter user "用户名"@"主机名" identified with mysql_native_password by '新密码'
alter user "test"@"localhost" identified with mysql_native_password by '123321';
root用户修改密码
mysql版本在8.0之前
#root用户修改自己的密码
#set password = password('新密码');
set password = password('123321');
mysql版本在8.0之后
#mysql8.0之后
#alter user "用户名"@"主机名" identified with mysql_native_password by '新密码'
alter user "test"@"localhost" identified with mysql_native_password by '123321';
③用户授权
#给用户授予权限,权限可以是select、update、delete、create、reload、drop等等
#如果要全部授予可以直接all
#授予的对象可以是库或者库下的某张表
grant select,update,delete on blog.test01 to ‘test’@’localhost’;
#授予所有库下所有表的所有权限
grant all on *.* to ‘test’@’localhost’;
④查看用户权限
#查看用户的权限
#show grants for ‘用户名’@’主机名’;
show grants for 'test'@'localhost';
⑤撤销用户权限
#撤销用户权限
#revoke 权限1,权限2,权限3... on 库名.表名 from '用户名'@'主机名';
revoke select on blog.test01 from 'test'@'localhost';
⑥删除用户
#删除用户
#drop user '用户名'@'主机名';
drop user 'test'@'localhost';
2.3DML(数据操作语言)
drop database dbname
drop table tablename / alter table tablename drop colum 字段
修改表:
alter table tablename MODIFY[COLUMN] 字段 约束
增加表字段:
alter table tablename ADD[COLUMN] 字段 约束
修改字段名字:
alter table tablename change 字段 字段1 约束
修改字段排列顺序:
放在字段2后:
alter table tablename add 字段1 date after 字段2
放在最前面:
alter table tablename modify 字段 约束
更该表名:
alter table tablename RENAME[to]new_tablename
更新表数据:
update tablename SET file1=value1,file2=value2,…filen=valuen
where condition
更新多张表:
update t1,t2,t3…tn SET t1.file1=expr1…tn.filen=exprn
where condition
删除记录:
delete from tablename where condition
删除多个表的数据:
delete t1,t2,t3…tn from t1,t2,…tn
where condition
排序限制查询:
多条件查询:
select * from tablename
where 字段=condition and 字段<condition
排序:
select * from tablename
where condition ORDER BY file1 [DESC\ASC],file2[DESC],…filen[ASC]
排序限制:
select * from tablename order by file1 limit 3;
聚合:
select [file1,file2,…,filen]fun_name
from tablename
[WHERE where_condition]
[GROUP BY file1,file2,…,filen[WITH ROLLUP]]
[HAVING where_condition]
fun_name:要做的聚合操作,聚合函数有(sum)求和,count(*)(记录数),max,min
GROUP BY:关键字表示要进行分类聚合的字段
WITH ROLLUP:聚合后的结果进行再汇总
HAVING:对分类后的结果在进行条件过滤
having,where的区别在于:
having是对聚合后的结果进行调节的过滤
where是在聚合前对记录进行过滤。(先where后having效率最大)
表连接:
select file1,file2 from tablename1,tablename2
where tablename1.file=tablename2.file
左连接:
右连接:
子查询:
select * from tablename1
where file in(select file from tablename2);
子查询转化为表连接:
select tablename.* from tablename1,tablename2
where tablename1.file=tablename2.file;
2.4DQL(数据查询语言)
SELECT * FROM 表名;
查询指定列
SELECT 列1 [, 列2, ... 列N] FROM 表名;
完全重复的记录只一次
当查询结果中的多行记录一模一样时,只显示一行。一般查询所有列时很少会有这种情况,但只查询一列(或几列)时,这总可能就大了!
SELECT DISTINCT * | 列1 [, 列2, ... 列N] FROM 表名;
-- 查询员工表的工资,如果存在相同的工资只显示一次
SELECT DISTINCT sal FROM emp;
列运算:数量类型的列可以做加、减、乘、除运算
SELECT sal*1.5 FROM emp;
SELECT sal+comm FROM emp;
字符串类型可以做连续运算
SELECT CONCAT('$', sal) FROM emp;
转换NULL值
有时需要把NULL转换成其它值,例如com+1000时,如果com列存在NULL值,那么NULL+1000还是NULL,而我们这时希望把NULL当前0来运算。
/*IFNULL(comm, 0):如果comm中存在NULL值,那么当成0来运算*/
SELECT IFNULL(comm, 0)+1000 FROM emp;
/*如果student_name列为null,则返回'没有名字'*/
select ifnull(student_name,'没有名字') from student_table;
/*如果student_name列等于'张三',则返回null*/
select nullif(student_name,'张三') from student_table;
/*如果student_name列为null,则返回'没有名字',否则返回'有名字'*/
select if(isnull(student_name),'没有名字','有名字') from student_table;
给列起别名
你也许已经注意到了,当使用列运算后,查询出的结果集中的列名称很不好看,这时我们需要给列名起个别名,这样在结果集中列名就显示别名了
-- 其中AS可以省略
SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;
条件控制(条件查询)
与UPDATE和DELETE语句一样,SELECT语句也可以使用WHERE子句来控制记录
SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
SELECT empno,ename,job FROM emp WHERE job IN ('经理', '董事长');
模糊查询
-- 模糊查询需要使用运算符:LIKE,其中_匹配一个任意字符,注意,只匹配一个字符而不是多个。下面语句查询的是姓张,名字由两个字组成的员工
SELECT * FROM emp WHERE ename LIKE '张_';
-- 姓名由3个字组成的员工
SELECT * FROM emp WHERE ename LIKE '___';
-- 其中%匹配0~N个任意字符,所以上面语句查询的是姓张的所有员工
SELECT * FROM emp WHERE ename LIKE '张%';
-- 千万不要认为上面语句是在查询姓名中间带有阿字的员工,因为%匹配0~N个字符,所以姓名以阿开头和结尾的员工也都会查询到
SELECT * FROM emp WHERE ename LIKE '%阿%';
-- 这个条件等同与不存在,但如果姓名为NULL的查询不出来
SELECT * FROM emp WHERE ename LIKE '%';
排序
-- 按sal排序,升序,其中ASC是可以省略的
SELECT * FROM emp ORDER BY sal ASC;
-- 按comm排序,降序,其中DESC不能省略
SELECT * FROM emp ORDER BY comm DESC;
-- 使用sal升序排,如果sal相同时,使用comm的降序排
SELECT * FROM emp ORDER BY sal ASC, comm DESC;
聚合函数:聚合函数用来做某列的纵向运算
- COUNT
-- 计算emp表中所有列都不为NULL的记录的行数
SELECT COUNT(*) FROM emp;
-- 计算emp表中comm列不为NULL的记录的行数
SELECT COUNT(comm) FROM emp;
- MAX
-- 查询最高工资
SELECT MAX(sal) FROM emp;
- MIN
-- 查询最低工资
SELECT MIN(sal) FROM emp;
- SUM
-- 查询工资合
SELECT SUM(sal) FROM emp;
- AVG
-- 查询平均工资
SELECT AVG(sal) FROM emp;
分组查询 分组查询是把记录使用某一列进行分组,然后查询组信息。
-- 使用deptno分组,查询部门编号和每个部门的记录数
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
-- 使用job分组,查询每种工作的最高工资
SELECT job, MAX(SAL) FROM emp GROUP BY job;
-- 以部门分组,查询每组记录数。条件为记录数大于3,组条件只能是分组字段或分组函数
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;
limit子句(方言): LIMIT用来限定查询结果的起始行,以及总行数。
-- 查询起始行为第5行,一共查询3行记录,其中4表示从第5行开始,其中3表示一共查询3 行。即第5、6、7行记录。
SELECT * FROM emp LIMIT 4, 3;
-- 一页的记录数:10行,查询第3页(公式:(当前页-1) * 每页记录数)
select * from emp limit 20, 10;
3.视图与存储功能
3.1视图
1、创建视图
可以先查询出想要的数据,再在前面加上 create view 视图名称 as 就行了。
例如:create view userviewo1 as select * from user where( 如果不希望往视图里面插入数据的时候进行校验,则这样。如果希望进行where后面的校验, 则在 最 后加入 with check option)
2、往视图里面插入数据(将创建视图看做一张表)
3.2存储过程
1、create procedure
存储过程在navicat中显示在函数中,可以直观的看出这是一个函数,被调用
-- 存储过程
create procedure userpro(in name varchar(20), out num int(3))
begin
if name is null or name = "" then
select * from user;
else
select * from user where name like concat('%', name , '%');
end if;
select foun_rows() into num;
end;
call userpro(null, @num);
select @num;