mysql 基础操作

数据库基本语法

链接数据库
用户名     密码
mysql  -uroot     -p123

创建     数据库     数据库名
create   database    he

删除库
语法     库         要删除的库名
drop   databases     he;

备注：语法写错  \c   退出

显示所有数据库
show databases;

显示所有数据表
show tables;

选择数据库语法

选择  数据库名
use    he

创建数据表
create table types(
id int primary key auto_increment,
name varchar(30) not null,
age int not null,
area varchar(30) not null
)engine=myisam charset=utf8;


修改表名
语法       表         旧表名     新表名；
rename table oldname to newname;

删除表
语法    表     要删除的表名
drop   table    types;

查看表详情（表结构）
语法   表名
desc tablename

查看建表语句

show create table test(表名);

-------------------------------------------------------------------

设置字符集
set names utf8;

插入数据
语句
insert into msg
(title,name,content)
values
("测试标题","用户名","内容介绍");

更新数据
语句
update msg
set
name = "小王",
content = "中国最强"
where
id = 1

批量添加
insert into msg
(title,name,content)
values
("明日","小张","内容介绍"),
("日月","小明","内容介绍"),
("回忆","小小王","内容介绍");

删除数据
语句
删除一条数据
delete from msg where id = 1;
批量删除数据
delete from msg where id in(1,2,3);
------------------------------------------------------------

注意：查询顺序按 where,group by,having,order by,limit


查询数据
语句
查询某几个字段
select id,title,name from msg;
查询所有字段
select * from msg;

not 或 ！                    #逻辑非
or  或 ||                    #逻辑或
and 或 &&                    #逻辑与

where  id=1
where  id!=1 or id <> 1      #不等于
where  id>= 5                #大于等于
where  id<= 5 #小于等于
where id in(1,5)             #取出1和5 的id
where between 200 and 300    #在某范围值
----------------------------------------------------------
模糊查询

%      #通配任意字符

_      #通配单个字符

like   #像

where title like '%博士%'
-----------------------------------------------------------
group  by   #分组
注意：group by 要配合以下5个聚合函数才有意义
（sum,avg,max,min,count与group综合运用）

max          #求最大
min          #求最小
sum          #求总和
avg          #求平均
count        #求总行数

取出number最大的值，按 cat_id 分组进行查询
select title,max(number) from goods group by cat_id

把列当成变更来看可以进行运算

#查询出本店每个商品比市场价格低多少钱
例：
select id,name,markey_price-shop_price from goods

#查询每个栏目下面积压的货款
例：
select cat_id,sum(shop_price * goods_number) as hk from group by cat_id

-----------------------------------------------------------------

having   对查询结果进行筛选

sheng为查询结果，having再对谁查询结果进行筛选
select id,name,markey_price-shop_price as sheng having sheng > 200
加条件where
select id,name,markey_price-shop_price as sheng where id = 3 having sheng > 200

#错误
select name,分数<60 as n count(科目) kh from stu having avg(n) and kh < 2;
#正确
select name,sum(分数<60) as gk,avg(分数) as pj from stu group by name having gk >=2;

------------------------------------------------------------------

order by 排序

asc     #（升序）默认排序 从小到大
desc    #降序排序  从大到小
select title,name from goods order by id desc
多字段排序
select title,name from goods order by id,number,name desc

------------------------------------------------------------------

limit    #限制条数
#取5条数据，默认从第0条开始取
select * from goods limit(5)

select * from goods limit(0,5)

mysql> select * from t1 limit 0,5;
+----+--------+---------+
| id | name   | title   |
+----+--------+---------+
|  1 | hello  | gggg    |
|  2 | kdks   | kfjsdf  |
|  3 | kfjslf | fisefkj |
|  4 | hello  | gggg    |
|  5 | kdks   | kfjsdf  |
+----+--------+---------+

------------------------------------------------------------------

#建立一经临时表
create table g2 like goods;

#清空g2表
truncate g2

------------------------------------------------------------------

where 子查询:指把内层查询的结果作为外层查询的比较条件

例：查询最新数据
select * from goods where id (select * from max(id)from goods)

select title,name,from goods where id in (select id,max(id) from goods group by cat_id)

from 子查询：把内层的查询结果当成临时表，供外层sql再次查询

例：取出分数小于60和平均分
select name,avg(分数) from stu where name in(select name from (select name,count(*)as gk from stu where 分类=2 as tmp)group by name

exists 子查询：把外层查询结果，拿到内层，看内层的查询是否成立

例：取出所有商品的栏目
select cat_id,cat_name from category where exists (select * from goods where goods.cat_id = category.cat_id)

-------------------------------------------------------------------

union   联合查询：把2次或多次查询结果合并
要求：两次查询的列数一致
推荐：查询的每一列，相对应的列类型也一样
多次sql语句取出的列名可以不一致，以第一次取出的列名为准
如果不同的语句中取出的行，有完全相同（每个列的值都相同）那么相同的行将会合并（去重复）
如果不去重复，可以加all来指定
如果子句中有order by，limit 用括号（）包起来
在子句中，order by 配合 limit 使用才有意义，如果order by 不配合 limit 使用，会被语法分析器优化分析去除


例句：
select id,sum(num) from (select * from ta union all select * from th) as tmp group by id;

例：
select name,title from goods where number >100 union select name,contont from conntent where number <900;

--------------------------------------------------------------------

集合的特性：无序性、唯一性
左右链接

left join     左连  (注：以左表为准,不存在以null补集)
right join    右连  (注：以右表为准)
inner join    内链接  查询左右表都有的数据，即：不要左右null的那一部分，
内连接是：左右链接的交集

select a.*,b.* from a inner join b on a.bid=b.id;

select * from goods left join select * from category on goods.cat_id=category.id


--------------------------------------------------------------------

表的增加列、修改列、删除列

增加列：
alter table 表名 add unmber int
可以用 after 来声明新增列在哪一列后面
alter table 表名 add number int after title
如果新增列放在最前面
alter table 表名 add number int first

修改列：
alter table 表名 change 旧名(要修改列名) 列声名

删除列：
alter table 表名 drop 列名

--------------------------------------------------------------------

视图：view

视图的创建语法

create view 视图名 select 语句

想看视图语句

show create view 视图名

视图删除语法

drop view 视图

视频作用：可以简化查询，权限控制

把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据

大数据分表时可以用到

比如表的行数超过200万行时就会变慢
可以把一张表的数据拆成四张表来存放
news 表
newsid，1，2，3，4
news1，news2，news3，news4 表

把一张的数据分散到4张表里，分散的方法很多，
最常用可以用id取模来计算

id%4+1=【1，2，3，4】

比如：$_GET['id'] = 17

17%4+1=2,$tableName = 'news'.'2'

select * from news2 where id = 17;

还可以用视图把4张表形成一张视图

create view news as select from n1 union select from n2 union....


视图修改：

alter view as select xxxxxxxx

视图与表的关系
视图是表的查询结果，自然表的数据改变了，影响视图的结果

视图改变
1：视图增删除改也会影响表
2：但是，视图并不是总能增删改的

视图的数据与表的数据一一对应时。可以修改
对于视图insert还应注意，视图必须包含表中没有默认值的列

视图的 algorithm
algorithm merge/temptable/undefined
merge:当引用视图时，引用视图的语句与定义视图的语句合并
temptable:当引用视图时，根据视图的创建语句建立一个临时表
undefined：示定义，自动，让系统帮你选

merge,意味着视图只是一个规则语句规则，当查询时,把查询视图的语句（比如where那些）与创建时的语句where子句等合并，分析形成一条select语句

create altorethm=temptable view g2 as select name,title from goods order by cat_id asc,price desc;

--------------------------------------------------------------------

字符集与校对集(校对集是指字符集的排序规则，默认utf8_general_ci规则，也可以按二进制来排序)
1.告诉服务器,我给你发送的数据是什么编码?character_set_client=gbk/utf8
2.告诉转换器，转换成什么编码？character_set_connection=gbk/utf8
3.查询的结果用什么编码？character_set_results=gbk/utf8

如果以上3者都为字符集N，则可以简写为 set names N

connection 和服务器的字符集比 client小时会发生数据丢失

--------------------------------------------------------------------

触发器：trigger
作用：监视某种情况并触某种操作，
监视触发：增、删、改

after      #是先完成数据的增、删、改，再触发
before     #是先完成触发再增、删、改（执行触发前先审查）

创建触发器语法
create trigger triggerName
after/before insert/update/delete on tableName(表名)

begin
sql 语句  #一条或多条sql语句
end

实例：

delimiter $   #告诉服务器遇到 $ 结束语句（修改定界符）

或者 \d $     #修改定界符

create trigger tg1   #创建触发器 tg1
after insert on a    #a表执行insert之后，执行触发器
for each row         #固定语句
begin                #触发器开始
update b set num=num-3 where id = 1; #触发语句
end$                 #触发器结束

删除触发器语法
drop trigger tg1

#新增订单，减少库存
create trigger tg2   #创建触发器 tg1
after insert on a    #a表执行insert之后，执行触发器
for each row         #固定语句
begin                #触发器开始
update b set num=num-new.much where id = new.gid; #触发语句
end$                 #触发器结束

much和gid：是a表中的列名


在触发器引用行的值
对于insert而言，新增的行用new来表示，
行中的每一列的值，用new列名来表示

#删除订单，商品增加库存
create trigger tg3   #创建触发器 tg1
after delete on a    #a表执行insert之后，执行触发器
for each row         #固定语句
begin                #触发器开始
update b set num=num+old.much where id = old.gid; #触发语句
end$                 #触发器结束


对于 delete 而言，新增的行用 old 来表示，
行中的每一列的值，用 old 列名来表示

#修改订单，商品库存发生变化
create trigger tg4   #创建触发器 tg1
after update on a    #a表执行insert之后，执行触发器
for each row         #固定语句
begin                #触发器开始
update b set num=num+old.much where id = old.gid; #触发语句
update b set num=num-new.much where id = new.gid; #触发语句
end$                 #触发器结束

查看触发器
show trigger

触发前执行判断
create trigger tg5   #创建触发器 tg1
before insert on a    #a表执行insert之前，执行触发器
for each row         #固定语句
begin                #触发器开始
if new.much > 5 then      #判断数据
set new.much =5;
end if;
update b set num=num-new.much where id = new.gid; #触发语句
end$                 #触发器结束


--------------------------------------------------------------------

存储引擎与其特点

myisam：批量插入速度快，不支持事务

innodb：批量插入速度慢，支持事务（bdb也支持事务，使用得少）

事务：（A）原子性、（C）一致性、（I）隔离性、（D）持久性
指一组操作，要么都成功执行，要么都不执行--》原子性
在所有的操作没有执行完毕之前，其他会话不能看到中间改变的过程--》隔离性
事务发生前和发生后，数据的总额依然匹配--》一致性
事务产生的影响不能够撤消--》持久性
如果出了错误，事务也不请允许我撤消，只能通过“补偿性事务”

开启事务：start transaction;
sql...
sql...
结束事务：commit(提交)/rollback(回滚)

注意：有些语句会造成事件的隐式提交，比如：start transaction

事务的基本原理


1：开启事务操作------》
start transaction
update 语句
sql...
sql...
2：事务日志文件-----》
sql... 的影响
sql... 的影响
3：提交事务-----》
commit
4：表数据文件
影响表


--------------------------------------------------------------------

备份与恢复

系统支行中，增量备份与整体备份
例：每周日整体备份一次，周一到周六备份当天
如果周五出问题，可以用周日的整体+周一，二，三，四来恢复

备份的工具
有第3方的收费备份工具
目前我们所学的是系统自带的备份功能，mysqldump

mysqldump 可以导出库、表

例如：导出aa库下的a表

mysqldump -uroot -p123 aa a >地址/备份文件名

mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 地址/备份文件名

导出的是建表语句及insert语句

导出库下的所有表

mysqldump -uroot -p123 库名 > 地址/备份文件名

以库为单位导出

mysqldump -u用户名 -p密码 -B 库1 库2 库3 > 地址/备份文件名


导出所有库

mysqldump -u用户名 -p密码 -A > 地址/备份文件名


恢复：
1：登录mysql情况下
恢复表要先use库下再执行恢复命令

sourec 地址/文件名

2：不登录mysql情况下
针对库级的备份文件

mysql -u用户名 -p密码 < 地址/库级备份文件名
mysql -u用户名 -p密码 库名< 地址/表级备份文件名

--------------------------------------------------------------------

索引：
普通索引：仅仅是加快查询
唯一索引：行上的值不能重复
主键索引：不能重复，主键必唯一，但唯一索引不一定是主键，一张表上只能有一个主键，但是可以用一个或多个唯一索引
全文索引：fulltext index

查看一张表上所有索引
show index from 表名

建立索引
alter table 表名 add index/unique/fulltext 索引名 （列名）

/primary key  主键
alter table 表名 add primary key (列名) //不要加索引名，因为主键只有一个

普通索引：alter table 表名 add index tel (tel);
唯一索引：alter table 表名 add unique (tel);
全文索引：alter table 表名 add fulltext (tel);
主键索引：alter table 表名 add primary key (tel);

删除索引

alter table 表名 drop index 索引名;

删除主键索引
alter table 表名 drop primary key;

全文索引：在mysql的默认情况下对中文意义不大
全文索引不针对非常频繁出现的词做索引
如：this，is，you，my等等...

全文索引的用法
select * from match (全文索引名) against ('keyword') from aa

-------------------------------------------------------------------

存储过程：procedur
类似函数，就是把一段代码封装起来，当要执行一段代码的时候可以通过调用该存储过程来实现，在封装的语句体里面，可以用 if/else,case,while等控制结构。可以进行sql编程。

查看现在有的存储过程
show procedure status

删除存储过程
drop procedure 存储过程的名字


在mysql中存储过程和函数的区别，
一个是名称不同，二个就是存储过程没有返回值

调用存储过程
call p1();



创建存储过程：
delimiter $   #告诉服务器遇到 $ 结束语句(修改结束符)

封装：
create procedure p1()     #创建存储过程
begin
select * from a;
end$

传参数：
create procedure p2(n int)     #创建存储过程
begin
select * from a where num > n;
end$

控制结构：
create procedure p3(n int,j char(1))     #创建存储过程
begin
if j='h' then
select * from a where num > n;
else
select * from a where num < n;
end if;
end$


循环：
create procedure p4(n int)     #创建存储过程
begin
declare i int;
declare s int;
set i = 1;
set s = 0;
while i <= n do
set s = s+i;
set i = i+1;
end while;
end$