mysql基础

一、DDL数据库操作语言

1、增删改查

insert into 表名 (列名) values (对应值)，（对应值） ;
delete from 表名 where 在哪行删；
update 表名 set 列名=值 where 在哪行改；
select  列名 from 表名 where 在哪行查； where 后 值为真假，1为真，0为假；列是变量，可以计算；

2、分组，筛选，排序，限制，合并

group by 分组统计   
  select max(shop_price)  from goods group by cat_id;   统计各栏目中最贵的商品价格。
sum 总和 ; avg 平均和; count 求行数；max 最大；min 最小
having 在结果集之后筛选（在where之后） 
  select good_id,good_name,(macket_price-shop_price) as sheng  where 1 having sheng>200; 查找省去的大于200的商品。

eg:查询出2门及2门以上不及格者的平均成绩
先查出每个人不及格的数目 
  select name,sum(score<60) as gk from stu group by name;
其次查出平均成绩 
  select name,sum(score<60) as gk,avg(score) as pj from stu group by name;
最后在筛选出2门及2门以上
  select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk>=2;

order by 排序
降序asc，升序desc ;多列排序 使用，分隔
limit 限制取出条目     
  通常与order by 搭配使用  
union 查询

• 把两条或多条查询合并成1个结果集，必须满足各语句取出的列数相同；
• 列名称以第一条select为标准，不一定相同；
• 完全相等的列名行相同会合并，此操作耗时，使用union all避免；
• 子句中不用写order  by，可以合并union之后在使用order by。

内连接 
  select 列名 from 表1 inner join 表2 on 条件；（查出两个表相互匹配的地方）。
左连接 
  select 列名 from 表1 left join 表2 on 条件； （以表1 数据为起点，去找表2 相匹配的，没有的就用null代替）。
右连接 
  select 列名 from 表1 right join 表2 on 条件； （以表2 数据为起点，去找表1 相匹配的，没有的就用null代替）。

二、DML 数据库定义语言

1、创建表

列名的数据类型 
数值型

•   tinyint 1字节 -128~127，列不为负用unsigned(无符号的)【0~255】；
•   zerofill 适合用于学号，编码扥固定宽度的数字，可以用0填充至固定宽度，属性默认决定列为unsigned。
•  int      4字节
•  float   double

字符串

•   char   ， varchar ，   text（枚举型）；
•  enmu(检举型，定义好几个，比如：男女)

日期时间
  date，time，datetime

列的默认值
not null default XXX  避免列的值为null
主键与自增
create table a (id int primary key auto_increment ,name char(20));
create table b (id int,name varchar(10),primary key(id));
auto_increment 自增, 一个表中只能有一个auto_increment，且此列要有主键。
列的增删改查
alter table 表名 add 列名 列类型 列属性；
alter table 表名 drop 列名；
alter table 表名 change 列名  新列名  列属性
alter table 表名 modify 列名  新属性

2、视图 view，虚拟表（sql的查询结果）

•   权限控制时使用，某几个列允许用户查询，其他的不可以。
•   简化复杂的查询 。
•   如果视图的每一行是与物理表一一对应的，则可以。
•   如果view的每一行是由物理表多行经过计算得到的结果则不可以更新，删除，insert。
•   create view 视图名 as select * from 表；（只要看* 这个查询的是什么）

视图algorithm

•   对于简单查询形成的view，在对view查询时，如where，order等等
•   可以把建视图的语句+查视图的语句  合并成  查物理表的语句
•   这种视图的算法叫merge（合并）

•   也可能视图的语句本身比较复杂，很难在和查询视图的语句合并
•   mysql可以先执行视图的创建语句，把结果集形成内存中的临时表，然后再查临时表
•   这种视图的算法叫temptable

create algorithm=merge/temptable view as select * from goods;

3、常用地表管理语句

查看所有表	show tables
查看表结构	desc 表名/视图名
查看建表的过程	show create table 表名
查看建视图过程	show create view 视图名
删除表	drop table 表名
删除视图	drop view 视图名
查看表详细信息	show table status（\G 加上它更加清晰）
查看某张表详细信息	show table status where 条件
改表名	rename table  旧表名 to 新表名
清除表数据	truncate 表名

4、解决乱码问题：

meta character='utf8'
create table X () charset ='utf8'
set names utf8;

5、索引

key name(name) 普通索引
unique key name(name) 唯一索引  【括号里的是列名，外面的是索引名】
primary key(id) 主键索引
fulltext全局索引

多行索引 key xm（xing,ming）;key email(email(10))
冗余索引 key xm (xing,ming) ,key ming(ming)

查看索引
  show index from 表名 
  show create table 表名
删除索引
  drop index 索引名 on 表名   
  alter table 表名 drop index 索引名
添加索引
alter table 表名 add index xm(ximg)

三、常用函数

floor（123.45）返回小于x的最大整数值 123
left（name,s） 返回字符串str中最左边的x个字符 
position（'s' in name）返回子串aubstr在字符串str中第一次出现的位置
date_format(now()，'%Y-%m-%d')依照指定的fmt格式 格式化日期date值

四、事务

start transaction;
commit;
rollback;

五、备份（数据+结构）

全量备份：mysqldump -h ip地址 -uroot -p密码 -A > 文件路径
指定库备份：mysqldump   -h ip地址  -uroot -p密码 库名>文件路径
多个库备份：mysqldump  -h ip地址  -uroot -p密码 --databases db1 db2 >文件路径

eg:每一分钟备份一次qi数据库
qi.sh:
   filename ="test_"`date +'%Y-%m-%d-%H-%M-%S'`".sql"
   mysqldump -h 172.27.0.15 -uroot -p2424rootYUNQI qi >/home/$filename
crontab -e :
   * * * * * /home.qi.sh

六、还原

1、还原全部数据库

#mysql -h IP 地址 -uroot -p密码 < 备份文件路径  在系统命令行下操作
或者
mysql > source 备份文件路径    在数据库里操作（重点）

2、还原单个数据库

mysql> use 库名
mysql> source 备份文件路径
或者
#mysql -h IP 地址 -uroot -p密码 库名 < 备份文件路径