mysql数据库总结。

mysql

mysql语法
mysql采用结构化查询语言sql (structured query language)语言来操作数据库
sql语句必须以 ; 结束
sql语句分类
ddl(数据定义语言): create、drop、alter、truncate
dql(数据查询语言): select、show
dml(数据操作语言): insert、update、delete
dcl (数据控制语言): grant、revoke、begin transaction、commit、roolback

mysql存储引擎
mysql数据表类型由存储引擎来决定,主要有6种类型：innodb 、bdb、myisam、isam、、heap、merge
innodb
支持事务
支持外键
不支持数据压缩
写速度较慢
mysql5.5开始支持全文索引
占用更多的内存和磁盘空间来保留数据和索引

myisam
不支持事务
不支持外键
访问速度快
支持全文索引

数据库的备份还原
#方法一
mysqldump -h服务器 -p端口 -u用户名 -p密码 数据库名称 > 备份文件路径
source 备份文件路径

#方法二
直接备份数据库文件（注意：这种方法不适用于innodb存储引擎的表，而对于myisam存储引擎的表很方便。同时，还原时mysql的版本最好相同）

#方法三
使用第三方工具 例如：navicat for mysql

命令行链接数据库
mysql -u root -p

数据库操作
#添加数据库
create database [if not exists] 库名 [[default] character set 编码方式]

#查看数据库
show databases 查看mysql下有哪些数据库
show create database 库名 查看某个数据库的详细信息

#修改数据库
alter database 库名 [default] character set 编码方式

#删除数据库
drop database [if exists] 库名

数据表操作
#打开数据表所在的数据库
use 库名

#添加数据表
create table [if not exists] 表名(
字段名 数据类型 [约束],
字段名 数据类型 [约束],
......
字段名 数据类型 [约束]
)[engine=myisam default charset=utf8 collate utf8_general_ci] ;

#查看数据表
show tables 查看当前数据下有哪此数据表
show create talbe 表名 查看建表信息
desc 表名 查看表结构

#修改数据表
#增加字段
alter table 表名 add [colum] 字段名 数据类型 [约束]

#修改字段名
alter table 表名 change 字段名 新字段名 数据类型 [约束]

#删除字段
alter table 表名 drop [colum] 字段名

#更改表名
alter table 表名 rename [to] 新表名

#删除数据表
drop table 表名

#清空数据表
truncate table 表名

 

数据库的增删改查
#插入记录
#插入一条新的记录
insert into 表名 [(字段名1,字段名2,…字段名n)] values(‘值1’,‘值2’,…,’值n’);
#插入多条记录
insert into 表名 [(字段名1,字段名2,…字段名n)]
values(‘值1’,‘值2’，…，‘值n’),
(‘值1’,‘值2’，…，‘值n’),
......
(‘值1’,‘值2’,…,’值n’);

#删除记录
#根据条件删除记录
delete from 表名 [where 条件子句] [order by 字段 asc|desc ] [limit 条数]
#在where子句中，条件可以由一个或多个逻辑表达式组成，
#在where子句中，可以通过逻辑操作符和比较操作符指定基本的表达式条件
#删除全部记录
truncate table 表名

#更新记录
update 表名 set 字段名=新值,字段名=新值，....... [where 条件子句] [order by 字段 asc|desc ] [limit 条数]

#查询记录
select 字段1,字段2,...... from 表名 [where 条件子句] [group by 字段][haveing 条件子句][order by 字段] [limit [起始值] 条数]

 

alias 别名
#通过使用 sql，可以为字段名称和表名称指定别名
select 字段名 as 别名 from 表名
select * from 表名 as 表别名

 

distinct 去重复
#关键词 distinct 用于返回唯一不同的值
select distinct 字段名称 from 表名称

聚合函数
avg() 平均值
count() 总记录数，不包括null
max() 最大值
min() 最小值
sum() 求和

时期函数
now()
curdate()
curtime()
unix_timestamp()
from_unixtime()

 

 

数学函数
abs()绝对值
ceil() 向上取整
floor 向下取整
round() 四舍五入
rand() 随机数
mod() 取余

字符串函数
concat() 连接字符串
replace() 替换
ucase() 大写
lcase() 小写
ltrim() 去左侧空格
rtrim() 去右侧空格
trim() 去两边空格

 

 

加密函数
password()
md5()

信息函数
database() 当前数据库
user() 当前用户
version 当前mysql版本
last_insert_id() 最后一条插入记录的主键id

多表插入
#将查询到的记录插入到已存在的表中
insert 表名(字段列表) select 语句
insert brand(brand_name) select brand_name from goods group by brand_name;

#创建新表并将查询到的记录插入到新表中
create 表名[(
字段名 数据类型 [约束]，
............
)] select 语句 ;

create table category(
cid smallint unsigned key auto_increment
)
select cate_name from goods group by cate_name;

 

多表查询
select 字段列表 from 表1 [[as] 别名] 连接类型 表2 [[as] 别名] on 连接条件 where ......

连接类型
内连接 inner join
外联接
左外连接 left [outer] join
右外连接 right [outer] join

#内连接：显示左表及右表中符合连接条件的记录
#左外连接：显示左表全部记录及右表中符合连接条件的记录
#右外连接：显示右表全部记录及左表中符合连接条件的记录

类似于内连接的多表查询
select 字段列表 from 表1,表2,表3...... where 连接条件1 and 连接条件2 .......

select id,goods_name,cate_name,brand_name,price from goods as g,brand as b,category as c where g.brand_id=b.bid and g.cate_id=c.cid;

比较操作符

 

like模糊查询通配符
% 通配0个或多个字符 %李%
_ 通配一个字符
[abcdef] 通配字符列中的任何单一字符
[^abcdef] 通配不在字符列中的任何单一字符

逻辑操作符

 

mysql数据类型
整型

int(10) unsigned zerofill //最大10位数字的无符号整数

浮点型

 

float[(总位数,小数点后位数)] 最大精确到小数点后7位
double[(总位数,小数点后位数)] 最大精确到小数点后15位
注：超过限定长度将进行四舍五入计算

字符型

 

布尔型
true false
日期型

 

实际开发当中，存储日期时，我们一般使用整型来存储时间戳，这样做便于我们进行日期的计算

注意事项
char的长度范围在0~255之间
varchar类型的长度范围0~65535
在使用char和varchar类型时，当我们传入的实际的值的长度大于指定的长度，字符串会被截取至指定长度
在使用char类型时，如果我们传入的值的长度小于指定长度，实际长度会使用空格补至指定长度
在使用varchar类型时，如果我们传入的值的长度小于指定长度，实际长度即为传入字符串的长度，不会使用空格填补
char要比varchar效率更高，但占用空间较大
blob和text类型是可以存放任意大数据的数据类型
blob区分大小写，text不区分大小写
enum和set类型是特殊的的串类型，其列值必须从固定的串集中选择
enum只能选择其中一个值，set可以选择多个值

约束
约束保证数据的完整性和一致性
约束分为表级约束和列级约束
约束类型包括
非空约束 not null
主键约束 primary key
唯一约束 unique key
默认约束 default
外键约束 foreign key

非空约束
空约束，字段值允许为null
非空约束，字段值不允许为null，必须赋值
#增加非空约束
create table mebmer(
username varchar(30) not null
);

 

自增长约束
必须与主键组合使用
默认情况下，起始值为1，增量为1
更改auto_increment初始值:
alter table 表名称 auto_increment=1

create table mebmer(
id int unsigned primary key auto_increment
);

主键约束
[primary] key
主键约束
每张表只能存在一个主键
主键必须保证记录的唯一性
主键自动为not null
主键不必与自增长约束同时存在

create table mebmer(
id int unsigned auto_increment,
primary key (id)
);

 

默认约束
default
默认约束，当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
create table mebmer(
sex enum('male','female') default 'male'
);

外键约束
foreign key
外键约束
数据表的存储引擎只能为innodb
外键列和参照列必须具有相似的数据类型
参照列必须存在索引
create table group(
id tinyint unsigned primary key auto_increment,
group varchar(20) unique not null
);
create table mebmer(
gid tinyint unsigned not null,
constraint member_group_key foreign key (gid) references group (id)
);

增加约束
#除外键约束外的其它约束
alter table 表名 change 字段名 新字段名 数据类型 约束
alter table 表名 add unique/primary key(字段名称)
#外键约束
alter table 表名 add [constraint [约束名]] foreign key (外键字段名) references 被参照表 (被参照字段名)

删除约束
#删除唯一约束
alter table 表名 drop index 字段名
#删除主键约束
alter table 表名 drop primary key
如果主键列存在自增长约束，删除主键约束前必须先删自增长约束
#删除外键约束
alter table 表名 drop foreign key 约束名
#删除默认值
alter table 表名 alter 字段名 drop default；

 

done。