数据库的概述及其增删改查
数据库-->理论+常用命令篇
数据库：
按照数据结构来组织，存储，管理数据的仓库
数据库模型分类：
网状数据库、
结点描述数据，节点的联系就是数据的关系
能够直接描述客观世界，可以表示实体间多种复杂关系，一个结点可以有多个父结点，结点之间可以多对多关联
层次数据库、
以树形结构表示实体及其之间的联系，关系只支持一对多，代表数据库IBM,IMS
关系型数据库
使用行列组成的二维表来组织数据和关系，表中行(记录)，即可描述实体，也可以描述实体间的关系
关系模型比网状模型，层次模型更简单，不需要关系数存储的物理细节，专心于数据的逻辑构建，而且关系模型有论文的严格的数据理论基础支撑
1970年，IBM研究员E.F.Codd 发表了名为"A Relational Model of Data for large Shared Data Banks"的论文，提出了关系模型的概念，奠定了关系模型的理论基础
Oracle的发展
larry ellison 在研究了E.F.Codd的论文后受到启发，开发了Oracle1.0，推广发行，但是最后到1992年,Oracle7 才稳定下来，2001年的9i版本开始广泛应用
2009年4月20日，甲骨文宣布公司收购 sun公司，2010年1 月，完成收购
2013年，甲骨文超过IBM，成为继微软后的全球第二大软件公司
Mysql的发展 1985年几个瑞典人为大型零售商的项目设计了一种利用索引顺序存取数据的软件，这就是MyISAM的前身 1996年 MySql 1.0发布，随后发布了3.11.1，并开始移植平台 2000年Mysql采用GPL协议开源，Mysql4.0开始支持MyISAM,InnoDB, 2005年 Mysql 5.0 成为了里程碑版本 2008年1月被Sun公司收购 2009年1月，在Oracle收购Mysql之前,Monty Widenius担心被收购，于是从Mysql上拉了个分支5.5的版本，并起名为MariaDB
Mysql的引擎是插件化的，可以支持多种引擎，默认InnoDB
MyISASM，不支持事务，插入，查询速度快
InnoDB,支持事务，行级锁，MySQL 5.5的默认引擎
去IOE概念
即去除：
I：IBM的小型机
O：Oracle数据库
E：EMC存储设备
传统上，这三个是一个高端数据中心不可缺失的重要组成部分，而且，核心架构也在别人手中掌握着
阿里巴巴采用开源，开放的系统架构，但并不是阿里巴巴发明的，国外的GOOGLE,FACEBOOK,AMZON早已做过 ，但是他们一开始就没有采用IT公司的架构，所以他们不用去IOE
去IOE的后果：
廉价的架构，稳定性下降，需要较高的运维技术人员
NoSQL
NoSQL是对非SQL，非传统关系型数据库的统称
1998年诞生，2009年被指出 非关系型，分布式，不提供ACID的数据库设计模式
随着互联网时代的到来，数据爆发式增长，数据库发展日新月异，需要新的业务需求，
随着移动互联网，物联网的到来，大数据的技术中NoSQL也同样重要
2017.12月更新：
数据库的流行度排名
MySQL
是一种关系型数据库管理软件，支持网络访问，默认端口3306
MySQL通信使用mysql协议
mysql使用什么网络协议？？
考虑数据的有序性，完整性，mysql采用的TCP协议传输数据
安装
使用yum安装，建议下载5.5以上的版本，推荐安装MariaDB,Percona、、版本
SQL
SQL是结构化查询语言，1987年，被ISO组织标准化
所有主流的关系型数据库都支持SQL，NoSQL也有很大一部分支持SQL
SQL语句为
DDL数据定义语言，负责数据库定义，数据库对象定义，由CREATE、ALTER与DROP三个语法锁组成
DML数据操作语言，负责对数据库对象的操作，CRUD增删改查
DCL数据控制语言，负责数据库权限访问控制，有GRANT和REVOKE两个指令组成
TCL事务控制语言，负责处理ACID事务，支持commit，rollback指令
SQL语句大小写不敏感
SQL语句末尾应该使用分号结束
GRANT 授权，REVOKE撤销：
GRANT ALL ON 数据库.* TO "root"@"%" IDENTIFIED by "root" ;
--授予--全部权限--作用在数据库的所有内容上--使用账号为root的任何机器-- 认同者为 'root' ;
删除用户（慎用）
DROP USER user1;
创建数据库 库是数据的集合，所有数据按照数据模型组织在数据库中 CREATE DATABASE `test2` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci' ; CHARACTER SET 指定字符集 utf8mb4是utf8的扩展，支持4字节utf8mb4，需要mysql5.5.3+ COLLATE指定字符集的校对规则，用来做字符串的比较的，例如 a A 谁大
删除数据库
DROP DATABASE IF EXISTS gogs;
创建表 表分为行和列，MySQL是行存数据库，数据是一行行存的，列必须固定多少列 行Row，也称为Record，元组 列Column，也称为字段Field CREATE TABLE `test`.`Untitled` ( `emp_no` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, 无符号的，自动递增项 `birth_date` date NOT NULL, `first_name` varchar(14) NULL, `last_name` varchar(16) NULL, `gender` enum('M','F') NOT NULL, ，枚举项，可选值为( M,F ) `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`) ，主键设置为 emp_no项，主键不可重复，有唯一性 );
上述代码生成的数据库表为-->
反引号标注的名称，被认为是非关键字
DESC
查看列信息
DESC emp_no 查看emp_no的整列信息
DESC emp_no “%name” 查看列中带有name 的 行信息
新建一个表，保存用户的登录名信息等等 CREATE TABLE `test`.`Untitled` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `password` varchar(128) NOT NULL, `name` varchar(64) NULL DEFAULT NULL, PRIMARY KEY (`id`) );
分析用户的登录名长度限制 #
分析用户的登录密码长度限制 #考虑 使用MD5加密方式，长度应定义为加密方式长度
分析用户的用户名长度限制
设置主键为ID，自增项，且不重复，唯一性
PRIMARY KEY主键
表中一列或者多列组成的唯一的KEY，也是通过这一个或者多个列能唯一的标识一条记录
主键列的值不能包含控制null，主键往往是设置为 整型，长整型，且自增AUTO_INCREMENT
表中可以没有主键，但是，一般设计中，都会有主键
索引index
可以看作是一本大字典的目录，快速检索，空间换时间，显著提升查询效率
可以对一列或者多列设定索引
主键索引，主键会自动建立主键索引，主键本身就是为了快速定位唯一记录的
唯一键，表中的索引列 组成的索引必须唯一，但可以为空，非空值必须唯一，
普通索引，没有唯一性的要求，就是建了一个字典的目录而已
约束
UNIQUE约束（唯一键约束）
定义了唯一键索引，就定义了唯一键约束、
PRIMARY KEY约束
定义了主键，就定义了主键约束
外键约束
外键，在B中的列，关联着A中的主键，表B中的列 就是外键
如果在表B中插入一条数据，B的外键列插入了一个值，这个值必须是表A中存在的主键值
修改表B的外键值也是同样，外键值同样要在表A中存在
如果表A删除一条记录，那么就等于删除一个主键，那么如果表B中引用到了这个主键，就必须先删除表B中引用这个主键的记录，然后才能删除表A的记录，否则删除失败
修改表A的主键，由于主键的唯一性，修改的主键相当于插入新主键，那么表B引用过这个主键
将阻止表A的主键修改，必须删除表B的相关记录后，才可以修改表A的主键
外键约束，为了保证数据完整性，一致性，杜绝数据冗余，数据讹误
视图
视图，也称为虚表，看起来像表，它是由查询语句生成的，可以通过视图进行CRUD操作
视图的作用
简化操作，将复杂查询SQL语句定义为视图，可以简化查询
数据安全，视图可以只显示真实表的部分列，或计算后的结果，隐藏真实表的数据
数据类型
MySQL的数据类型
|
类型
|
含义
|
|
tinyint
|
1字节，有符号范围(-128)-127，无符号范围0-255,bool或boolean，非0即真
|
|
smaillint
|
2字节，带符号范围-327678到32767，无符号范围0-65535
|
|
int
|
4字节，整型，同integer，带符号范围-2147483648到2147483647，无符号范围0-4294967295
|
|
bigint
|
8字节，长整型，带符号范围-9223372036854775808到9223372036854775807,无服务范围0到18446744073709551615
|
|
float
|
单精度浮点数精确到大约7为小数位
|
|
double
|
双精度浮点数精确到大约15位小数位
|
|
DATE
|
日期，支持范围"1000-01-01"到“9999-12-31"
|
|
DATETIME
|
范围"1000-01-01 00:00:00"到9999-12-31 23:59:59"
|
|
TIMESTAMP
|
时间戳 "1970-01-01 00:00:00"到2037年
|
|
char(M)
|
固定长度，右边填充空格以到达长度要求，M为长度，范围0~255,M指的是 字符个数
|
|
varchar(M)
|
可变长度字符串，M表示最大列长度，M的范围是0-65535，但不能突破行最大字节数65535
|
|
text
|
大文本，最大长度为65535(2^16-1)个字符
|
|
BLOB
|
大字节，最大长度为65535(2^16-1)字节的BLOB列
|
LENGTH函数返回字节数，而char和varchar的定义的M是字符数限制
char可以将字符串编程等长的，空间换时间，效率略高，varchar变长，省了空间
DML
CRUD增删改查
insert语句 INSERT INTO table_name(列_name...)VALUES(value1...) 向表中插入一行数据，自增字段，缺省值字段，可为空字段可以不写 INSERT INTO table_name SELECT ... 将select查询的结果插入到表中 INSERT IN TO table)name(clo_name1...)VALUES(value1,...) ON DUPLICATE KEY UPDATE col_name = value1,...., 如果主键冲突，唯一键冲突就执行UPDATE后的设置，上句含义 主键不再新增记录，主键在就更新部分字段 INSERT IGNORE INTO table_naem(col_name..)VALUES(value1,...)； 如果主键冲突，唯一键冲突就忽略错误，返回一个警告
Update语句 UPDATE[IGNORE] tble_name SET col_name1 =expr1[,clo_name2=expr2...] [WHERE where_definition] #where 对象 对谁操作 IGONRE 意义同insert语句 UPDATE reg SET name="张三" WHERE id=5;
Delete语句 DELETE[IGNORE]FROM tbl_name [WHERE where_definition] #where 对象 对谁操作 删除符合条件的记录
Select语句
FOR UPDATE会把进行写锁定 | 排他锁
查询 查询的结果成为集recordset SELECT 1; --最简单的查询 SELECT * FROM `employees` --查询employees中的所有数据 SELECT emp_no,first_name +last_name FROM employees; SELECT emp_no as `number`,CONCAT(`first_name`,`last_name`)as name FROM employees; --字符串合并,使用CONCAT函数，将两个列信息组合在一起显示 AS 定义别名，可写可不写，写是一个好习惯 LIMIT子句 SELECT * FROM employees as emp LIMIT 5; --返回5条记录 SELECT * FROM employees as emp LIMIT 1 OFFSET 18; SELECT * FROM employees LIMIT 18,1; --18条，并返回1条记录
where子句
|
运算符
|
描述
|
|
=
|
等于
|
|
<>
|
不等于
|
|
>、<、>=、<=、
|
大于， 小于，大于等于，小于等于
|
|
BETWEEN
|
在某个范围之内，between a and b 等价于[a,b]
|
|
LIKE
|
字符串模式匹配，%表示任意多个字符，_表示一个字符
|
|
IN
|
指定针对某个列的多个可能值
|
|
AND
|
与
|
|
OR
|
或
|
如果很多表示式需要使用AND OR 计算逻辑表达式时，考虑结合律，可以使用小括号避免错误产生
--条件查询 SELECT * FROM employees WHERE emp_no < 10015 AND last_name LIKE 'P%'; 查询employees库中的emp_no表，id小于10015且last_name中以P开头的 SELECT * FROM employees WHERE emp_no BETWEEN 10010 AND 10015 AND last_name LIKE 'p%'; 查询employees数据库，emp_no表中 id 介于10010-10015之间，且last_name中以P开头的数据 SELECT * FROM employees WHERE emp_no in (10001,10002,10010); 查询employees中，emp_no在（10001，10002，10010）元组中的数据
Order by子句 对查询结果进行排序，可以升序ASC、降序DESC、 --降序 SELECT * FROM employees WHERE emp_no in (10001,10002,10010)ORDER BY emp_no DESC; --升序 SELECT * FROM employees WHERE emp_no in (10001,10002,10010)ORDER BY emp_no ASC;
DISTINCT
不返回重复记录
SELECT DISTINCT dept_no FROM dept_emp;
查询dept_emp中 dept_no不重复的数据
SELECT DISTINCT emp_no FROM dept_emp;
查询dept_emp中 emp_no不重复的数据
SELECT DISTINCT emp_no,dept_no FROM dept_emp;
只能满足一个可选项，靠左优先
聚合函数
|
函数
|
描述
|
|
COUNT(expr)
|
返回记录中记录的数目，如果指定列，则返回非NULL值的行数
|
|
COUNT(DISTINCT expr,...)
|
返回不重复的非NULL值的行数
|
|
AVG(DISTINCT expr)
|
返回平均值，返回不同值的平均值
|
|
MIN(expr),MAX(expr)
|
最大值，最小值
|
|
SUM(DISTINCT expr)
|
求和，Distinct返回不同值求和
|
--聚合函数
SELECT COUNT(*),AVG(emp_no),SUM(emp_no),MIN(emp_no),MAX(emp_no) FROM employees;
分组查询 使用Group by子句，如果有条件，使用Having子句过滤分组，聚合过的结果 -- 将聚合后的数据结果，按照分组条件，分组 SELECT emp_no,SUM(salary),AVG(salary),COUNT(emp_no) FROM salaries WHERE emp_no < 10003 GROUP BY emp_no; --HAVING子句对分组结果过滤 SELECT emp_no,SUM(salary),AVG(salary) AS sal_avg,COUNT(emp_no) FROM salaries GROUP BY emp_no HAVING sal_avg >60000; 将查询到的数据，按照sal_avg的条件进行过滤 SELECT emp_no,SUM(salary),AVG(salary) AS sal_avg,COUNT(emp_no) FROM salaries GROUP BY emp_no HAVING sal_avg >60000 ORDER BY sal_avg; 将查询到的数据，按照sal_avg的条件过滤后，并对其进行排序
子查询 查询语句可以嵌套，内部查询就是子查询 子查询必须在一组小括号中 子查询中不能使用Order by SELECT * FROM employees WHERE emp_no in (SELECT emp_no FROM employees WHERE emp_no >10015)ORDER BY emp_no DESC; 嵌套查询并倒序排序 SELECT emp.emp_no,emp.first_name,gender FROM (SELECT * from employees WHERE emp_no > 10015) AS emp WHERE emp.emp_no < 10019 ORDER BY emp_no DESC; 将嵌套内的查询结果定义为一个别名为emp的变量名称，外层查询便可以对其变量名的数据进行查询，排序
posted on 2018-01-03 18:22 pythonerLau 阅读(370) 评论(0) 收藏 举报
浙公网安备 33010602011771号
