网站首页该怎么做/在线推广

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);