数据库基本语法

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

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

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

备注:语法写错  \c   退出

显示所有数据库
show databases;

显示所有数据表
show tables;


查看库中所有表的具体信息

show table status


选择数据库语法

选择  数据库名
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$