1、数据库的基本概念
1、数据库是一种能够高效的存储和处理数据的介质
2、关系型数据库：数据保存在磁盘，是一种建立在关系模型上的数据库(二维表),具有完整性约束
缺点：浪费磁盘，即使某个数据不存在但是系统也要分配磁盘空间进行存储
小型:access,sybase 中型:mysql,sql-server 大型:oracle DB2
3、非关系型数据库：运行在内存介质的数据库,数据存储是键值对
Memcached,mongodb,redis(同步磁盘机制)
2、SQL基本概念(structured query language)
1、DDL(data definition language)设计结构 create,drop
2、DML(data manipulation language)数据操作语言(insert,delete,update,select)
包含DQL(Data query language)数据库查询语言
3、DCL (data control language)数据库控制语言(权限管理):grant remove
3、库操作
基本语法:create database 数据库名 [数据库选项：字符集/校对集的设置]
Charset set=utf8,collate=utf_general_ci
Show databases
Show databases like 'mysql_'
//后边接一个字符
Show datebases like 'mysql%'
// 以mysql开头的模糊匹配
4、表操作
1、创建数据表
Show create database 数据库名称
Alter database 数据库名 库选项
// 修改数据库(msyql5不能修改库名,只能修改库名称)
Drop database 数据库名
Create table 表名(
字段名1 数据类型
字段名1 数据类型
字段名1 数据类型
...
)[表选项]
表选项:Charset(字符集设置) |collate(校对集设置) | engine(存储引擎)
2、查看数据表
Show tables
Show tables like 'mysql_'/'mysql%'
Show create table 表名
Desc/describe/show columns from 表名
3、修改数据表
Alter table 原表名 rename to 新表名
Alter table 表名[表选项] --charset gbk/charset=gbk
4、修改字段名
修改字段：Alter table 表名 change 原字段 新字段 数据类型[frist/after 字段]
修改类型：Alter table 表名 modify 字段名 数据类型 [frist/after]
新增字段：Alter table 表名 add 新字段 数据类型 [frist/after]
删除字段：Alter table 表名 drop 字段名
5、删除数据表
Drop table 数据表名
5、数据操作
Insert into 表名 [(字段列表)] values(值列表)
Select */字段列表 from 表名 [where条件]
Update 表名 set 字段名1=值1,字段名2=值2... [where条件]
Delete from 表名 [where条件]
6、字符集
Show character set // 查看字符集
Show variables like 'character_set%' // 设置系统变量
Set names gbk
7、校对集
在order by 排序时使用,需要在数据表设置的时候进行设置
_bin:binary 二进制(区分大小写)
utf8_bin
_cs:case sensitive 区分大小写
utf8_general_cs
_ci:case insensitive 不区分大小写 utf8_general_ci
8、数据类型
1、整型(括号内的数值为显示宽度)
Tinyint:1bety-->256种状态 unsigned -128~127
Smallint:2bety-->65535种状态
Int:4bety
2、小数型
Float/float(M,D) M--代表总长度 D--小数部分长度
Decimal(M,D) M--代表总长度 D--小数部分长度
// 不会丢失精度(动态分配空间)
3、日期型
Datetime 日期时间
Date 日期
Time 时间段
Timestamp 自动更新
Year 年
Timestamp 时间被动更新
4、字符串类型
Char 字符长度最大为255
Varchar 当存储状态大于255时会分配2个字节去记录数据长度(见)
Text 超过255个长度的字符串一定使用text进行存储
Enum(男,女) 实际存储为1/2
Set(元素列表)
9、mysql记录长度与字段长度
mysql任意一条记录的长度的最大长度不能超过65535个字节，会将65535这些直接全部分配给当前表中所有的字段使用
都是按照字符集来进行分配的当为utf8时varchar中文则有 (65535-2)%3 = 21844 [2为记录数据的长度,当小于255时就为1个字节来记录数据的长度；3为中文占用的字节数,GBK为2，Latin为1]
在mysql中: 是所有的字段长度加起来不能超过65535, 但是如果有任何一个字段或者多个字段允许为NULL,那么系统需要占用一个字节来存储NULL
Mysql中,text文本字符串是不占用记录长度: 额外开辟存储空间. 但是text本身也是一个字段,需要占用10个字节来保存字段本身.
10、列属性
desc tabelname
空/非空 、默认值、主键、唯一键、自增长、字段描述
null或not null 指定数据是否可以为空
当数据not null 时可以通过default来进行缺省，此时添加数据的时候在指定字段列表的时候不要指定该缺省字段，或者指定了全部字段后，使用default来进行添补;
扩展：在auto_increment 中也可以使用null和default 来进行占位
auto_increment 必须与key索引搭配使用
11、主键
主键：primary key 使用主键描述的字段不能为空,整个表中该字段中不能出现重复的数据(确保字段的唯一性)
增加主键：alter table tablename add primary key (id) ;
删除主键: alter table tablename drop primary key ;
复合主键: 可以在创建表和在没有定义主键的表中使用alter主键进行添加
当id存在和name分别存在或共同存在的时候将会冲突
12、自增长
通常情况下自增长是搭配主键来进行使用的
查看自增长相关变量数据: show variables like ‘auto_increment%’;
自增长的删除修改：alter table tablename modify id int
自增长的属性修改：alter table tablename nodify id int auto_increment;
13、唯一键
增加唯一键：在创建表的时候进行唯一键unique 的修饰和在所有语句的后边指定字段进行修饰，或者是alter table tablename add unique key(fieldname)
唯一键字段不允许重复，但是可以为空
唯一键不能修改只能先删除后增加,唯一键与主键完全不一样没有主键的权限,被当做了普通的索引
Alter table tablename drop index 索引名(所有索引的删除方法)
14、数据的高级操作
新增操作
Insert into tablename [(字段列表)] valules (值列表)
当主键冲突,执行更新操作
Insert into tablename[(字段列表)] values(值列表) on duplicate key update field = value
直接替换，先删除再进行替换
Replace into 表名[(字段列表)] values(值列表)
蠕虫复制(表的测试)
复制表结构：create table 表名 like 旧表名
复制数据：Insert into 表名[(字段列表)] select 字段列表 from 表名 //去除主键防止冲突
修改操作
Update 表名 set 字段名 = 新值 [where条件] [limit 操作];
删除操作
delete from 表名 [where条件] [limit操作];
表的重置(先删除表再重新创建)
Truncate 表名;
查询操作
Select [select选项] */字段列表[字段别名] from 数据源 [where子句] [group by子句] [having子句] [order by子句] [limit子句];
在哪里(where)分组(grounp by )有(having)排序(order by )限制(limit)
五子句操作
1、Select distinct(all) * from tablename;
--distinct 去重
2、where子句查询
Where field inarray()
Where id between 2 and 3;
Where id=? Or id=? // 使用||
Where id=? And id =? // 使用&&
Where id not? // !
3、group by分组
①、Select id from tablename group by id // !!! 分组的字段必须包含在select字段中
分组的时候可以配套count(field),max(),min(),sun(),avg()来进行对字段的统计
注：以上函数只统计非null的字段，若要统计为null的数据,则使用
②、Select * from tablename where id = ? is[not] null;
③、concat(b.attr_name,':',a.attr_value)
将一行中的两个字段按照’:’进行合并
group_concat(concat(b.attr_name,':',a.attr_value) separator '
')
将多行中的数据通过指定的符号进行合并
④、进行分组排序
单字段分组：
Select * from tablename group by field [desc/asc]
多字段分组：
Select * from tablename group by field1 desc,field2 asc
回溯统计
Select * from tablename group by field desc with rollup
4、having
Having是在group by 之后,当分组后的事情可以交给having进行处理，having的作用同where一样，但是where不能使用统计函数说生成的值,因为统计函数是在where之后生成的,having就可以使用统计函数的值来再次处理结果
5、order by排序
Order by 可以进行多字段的排序，根据第一次排序后的结果在次进行排序,没有价值
6、limit offset,num
15、数据库设计
表的关系：
一对一：将一张表中常用的部分和不常用的部分分层两张表共用相同的id
一对多：在多表中增加一个唯一的字段来指向一表中的主键
多对多：在两张表之间增加一张表将两张表联系起来,这张表中含有两张表的主键(唯一键)
范式：
范式是一种规范的方式
范式的六层要求太严格,数据库如果完全按照范式6层设计: 几乎就没有效率可言
第一范式: 字段设计必须满足原子性(不可拆分: 不需要拆分)
第二范式: 将非主键字段对主键的部分依赖取消: 取消部分依赖.
第三范式: 取消传递依赖,所有的字段都必须依赖主键
原本数据表的设计应该满足范式(更高级别范式)的需求: 范式要求:凡是数据能够通过其他范式查询出来,就一定不允许数据重复(数据冗余)
但是如果通过多表数据的查询会导致效率的降低: 有时候为了提升效率,会主动的增加数据冗余(磁盘空间与访问效率的冲突)
16、文件备份
Mysql的数据备份方式有：文件备份、单表备份、sql备份、增量备份
【文件备份】
将存储数据的表的结构以及索引文件和数据文件直接copy备份
Innodb: 表的结构和ibdata1文件(数据和索引)
Myisam:表的三个文件(结构、数据、索引)，将文件移动到制定的数据库下进行覆盖即可
【单表数据备份】
一次只能对一张表进行备份：只能备份数据，不能备份结构
Select */字段列表 into outfile 外部文件路径 from 表名
【SQL备份】
通常将整个表的结构和数据都已SQL指令的形式进行备份
库外备份：
mysqldump.exe –hlocalhost –P3306 –uroot –proot dbname [tablename] > 外部存储路径/a.sql
库外还原：
Mysqldump.exe –hpup 数据库名字 < SQL文件路径
库内还原：
Source 还原的文件路径.sql
【增量备份】
备份文件日志，断点备份
17、事务处理
存储引擎必须是innodb：只有innodb和BDB支持事务安全
本质：数据的操作不是直接将数据写入到数据库中,而是写操作有一个中间状态(事务日志)
自动操作：
Show variable like ‘autocommit%’ // 查看自动提交变量的开启情况
Set autocommit = off/0 (on/1)
Commit // 提交数据
Rollback // 回滚
手动操作：
Start transaction --开启事务
Commit --提交事务
Rollback --清空数据
Savepoint --设置回滚点名字
Rollback to --部分操作结束(回滚到设定的回滚点)
事务操作的特性[ACID]：
原子性：事务操作为一个整体，不可分割，要么全部成功，要么全部失败
一致性：在事务操作的前后，数据表中的数据不会因为事务操作的改变而改变
隔离性：事务的操作都是独立的，彼此事务之间的操作互补影响
持久性：事务一旦提交(提交或回滚)，所做的操作都是永久的要么写入要么被清空
18、变量
在mysql中 := 相当于php中的赋值符号，= 相当于比较符号
【系统变量：】
Show variable; // 查看所有的系统变量
Select @@变量名； // 查看单个系统变量
修改系统变量：
会话级别：set 变量名 = 值 // 通常情况下为临时修改
全局修改：set global 变量名 = 值
【自定义变量】
Set @变量名 = 值
//从数据表中查询数据为变量赋值【多条】
Select @变量1:=字段1,@变量2:=字段2 From 表名 where 条件
Select 字段列表 from 表名 into 变量列表 // 单条赋值
【自定义变量的访问】
Select @变量名
19、触发器
触发器：实现写好一段代码,为代码绑定某个事件，当事件被触发时，代码将会自动执行
触发类型：增删改(insert/delete/update)
触发时间：操作前和操作后(before/after)
表和行：触发器绑定在表的行上
语法:
Delimiter $$(自定义语句结束符结束)
Create trigger 触发器名 触发时间 时间类型 on 表名 for each row
Begin
--触发器内部写好的代码；
End
$$(自定义语句结束符结束)
Delimiter; // 将结束符改回原样
Show triggers; // 查看所有的触发器
Show create trigger 触发器名字； // 查看触发器创建语句
20、系统变量和函数
21、权限管理
① 创建用户
Create user useaname@host identified by ‘密码’
Host:不存在则代表所有的用户,或者是ip用_和%进行通配
②分配权限
Grant 权限列表/all privileges on 数据库.表(*) to user(username@host) // 所有的权限
③回收权限
Revoke 权限列表/all privileges on 库.表(*) from user(username@host)
④删除用户
Drop user user@host;
--一次的创建用户的方式
grant 权限 on 数据库和表 to ‘用户名’@’主机的ip地址’ identified by ‘密码’
22、子查询
-- subord 为子表(小弟) linder为主表(大哥) ws_id指向主表id
1、标量子查询(一行一列)
select * from subord where ws_id=(select id from linder where linder_name='康熙大帝');
2、行子查询(一列多行)
select * from subord where ws_id in (select id from linder);
# 与in 相同的几个符号
=Any表示任意一个(与some没有任何区别)
=Some表示任意一个
=All表示全部
！=any 不等于其中的一个
！=some 不等于其中的一个
！=all
3、列子查询(返回的是多列的结果)
select * from subord where (weight,height)=(select max(weight),max(height) from subord);
4、表查询
-- 先分组再排序(from 表 从某个表中获取数据)
select * from (select * from subord order by height) as a group by ws_id; //不加别名无法获取
5、exists 查询(返回0/1)
select * from subord where ws_id=2 and exists (select * from linder where id=2);
697
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
