1.查询库----------------show databases;
2.使用库----------------use 库名;
3.查看所有表------------show tables;
 
创建表------------------create table 表名

4.查看某一表
所有内容-----------------select * from 表名;

显示指定内容-------------select 列名1,列名2... from 表名 where 条件;
select * from 表名 where 列名 between 1 and 3(显示1-3之间的内容);
select * from 表名 where 列名 in(1,2,3)(显示id为1,2,3的内容);
select * from 表名 where 列名 not 2(除了2其他都显示);
分组查询
select max(列名) from 表名 group by 要分组的列名;

 

5.增列-------------------alter table 表名 add 增加的列名 类型;

6.改
改列名-------------------alter table 表名 change 旧列名 新列名 新列类型;
改指定内容--------------update 表名 set 列1=值1,列2=值2 where 条件;

7.删
删表--------------------drop table 表名;
删列--------------------alter table 表名 drop 列名;
删除指定内容------------delete from 表名 where 条件;

8.设定默认值------------列名 类型 default (默认值);

9.定义主键(key)自增------------列名 类型 primary key auto_increment;

10.having筛选      查看差价最大的   (chajia没有存储,临时的,where chajia在表格里面没有就找不到,having chajia可以找到前面临时定义的)having 与 where后面加的表达式类似,where优先执行条件,having从前面依次联合执行(优先级问题),区别于前面描述;
select goods_name,(goods_price - goods_jinjia) as chajia from good3 having chajia >2000;

各关键词的先后顺序:where  group  having  order  limit

 

11.函数方法:
求和:sum(列);
平均值:avg(列);
最大值:max(列);
最小值:min(列);
总行数:count(*//或者任意列名);

12.排序 order by
升序:select * from 表名 order by 列名 asc;
降序:select * from 表名 order by 列名 desc;

13.显示限制 limit A,B;
select * from 表名 limit A,B(开始值的前一位,限制的条数);

14.获取满足条件的行数--------sum(条件)

15.对表分组排序
select * from  表名 order by name,score(分组的列名,排序的列名);

 

16.子查询
where       型子查询,指把内层查询的结果作为外层查询的比较条件
                        例:查询最新商品(以id为最大为最新)
                        select * from goods where goods_id = (select max(goods_id) from goods)

From        型子查询 把内层的查询结果当成临时表,供外层sql再次查询
注意:把临时查询出来的结果看成临时表,加as临时表名

Exists  子查询   把外层的查询结果,拿到内层,看内层的查询是否成立
比如查询还在就读的学生:select * from stu where exists( select * from classes where stu_id = classes.id);

 

17.连接
union 联合
单独用union,相同的行会合并(合并:去重),union all连接就不会去重

在子句中 order by 配合limit使用才有意义,如果order by 不配合limit使用,会被语法分析起优化分析时去除。

 

外连接:
left join 以左表为为主,显示左表中所有的行,右表里没有的值用null填充    right join 以右表为主,显示右表中所有的行,左表里没有的值用null填充

select 列名,列名 from 表名1 left join 表名2 on 表名1.列名=表名2.列名(条件);

内连接:
inner join
只显示两个表同时满足的
select 列名,列名 from 表名1  inner join 表名2 on 表名1.列名=表名2.列名(条件);

full join
现在mysql不支持full join;可以通过left join union right join替代

nature join
自然连接,两张表连接
例:select * from boy  nature join girl ;
效果显示:
1 张三 1 1 萧九 3
2 李四 2 1 萧九 3
3 王凯 3 1 萧九 3
1 张三 1 2 王s 1
2 李四 2 2 王s 1
3 王凯 3 2 王s 1
1 张三 1 3 王柯 2
2 李四 2 3 王柯 2
3 王凯 3 3 王柯 2

 18.设置外键
外键的作用:
保持数据一致性,完整性,主要目的是控制存储在外键表中的数据。 使两张表形成关联,外键只能引用外表中的列的值!
两张表关联设置的外键必须类型保持一致;

视图:
创建临时表................reate table new表名 like 旧表名;
旧表内容加入新表..........insert into 新表名 select * from 旧表名 where 条件 ;

19.创建视图...............create view 表名 as select * from 旧表名;可加 where 条件;(view是虚拟表)

20.删除视图...............drop view 视图名;

21.查看视图...............show tables;

22.创建临时虚拟表......... create  algorithm = temptable view 视图名 as select * from 表名 order by id desc;

视图的algorithm
algorithm = merge/temptable/undefined
merge:当引用视图时,引用视图的语句与定义视图的语句合并意味着视图知识一个规则,当查询视图时,把查询视图的语句(比如where那些)与创建时的语句where子句等合并,分析形成一条select语句     
temptable:当引用视图时,根据视图的创建语句建立一个临时表
temptable时根据创建语句瞬间创建一张临时表,然后查询视图的语句从该临时表查询数据。   
undefined:未定义,自动让系统帮你选。
语法:create [algorithm=merge/temptable/undefined] view viewname as select ....

 

字符集:
23.设置服务器接收的字符集...............set character_set_client = gbk;
24.设置服务器转换成什么编码.............set character_set_connection=gbk;
25.设置服务器(查询结果)什么编码.......set character_set_results = gbk;

26.查看编码集:show charater set;
 查看utf8编码集.........................show collection like "utf8%";
 查看搜所有的校对集.....................show collation  
 查看所有的字符集........................show character set
27.校对集:
设置校对集............create table  t1(name varchar(10)) charset utf8 collate utf8_bin;

28.总结
客户端使用的字符集:set character_set_client = gbk/utf8
转换器转换后的字符:set character_set_connection = gbk/utf8
返回给客户端的字符集:set character_set_results = gbk/utf8
如果以上三者都是字符集一样的N那么刻意简写为set names N

 

触发器:

29.delimiter $/.....  替换结束符
30.create trigger 触发器名 (after/before) insert/update/delete(监听事件) on 数据更新的表名 for each row begin update 数据要被改变的表名 set kuncun=kucun-new.num where id=new.good3_id; end$
31.库存减去订单上的数据:create trigger gc after  insert on 订单表 for each row begin update 库存表 set kucun=kucun-new.num where id=new.good3_id; end$

32.添加删除的数据..................create trigger gc1 after  delete on o for each row begin update good3 set kucun=kucun+old.num where id=old.good3_id; end$
33.删除触发器:drop trigger 触发器名;

 

34.创建触发器语法:
create trigger triggername after/before insert/update/delete on 表名
 for each row (这句话在mysql里面是固定的)
 begin
 sql语句(一句或多句,insert/update/delete 范围内;delimiter (改变结束标记))
 end;

35.删除触发器的语法:drop trigger 触发器名
 
36.如何在触发器引用行的值
a.对于insert 而言,新增的行 用new来表示,行中的每一列的值用new.列名来表示
 
b.对于delete来说,原本有一行,后来被删除,想引用被删除的这一行,old来表示,old.列名就可以引用被删除行中的值
 
c.对于update来说
 被修改的行
 修改前的数据,用old表示,old.列名引用被修改之前行中的值
 修改后的数据,用new来表示,new.列名引用被修改之后行中的值

36.alter和before的区别
 after是先完成数据的增删改再触发触发器
    触发的语句先于监听的增删改,无法影响前面的增删改动作。
 before 是先完成触发,再增删改。
    触发的语句先于监听的增删改,我们有机会判断,修改即将发生的操作。

 

存储引擎:

37.引擎用engine声明
指定的引擎:create table t(id int) engine myisam charset utf8;
例:create table users(id int,name varchar(20),money int) engine myisam charset utf8;

38.常用引擎:
myisam: 批量插入速度快,不支持事物 ,锁表
innodb:批量插入相对较慢,支持事物,锁行
全文索引:目前5.5版本myisam和inactionnodb都支持

 

事物:
38.特点:
a.产生的影响不能撤销------持久性
b.数据总额不变---一致性
c.指一组操作,要么都成功操作,要么都失败-----原子性
d.在所有的操作都没有执行完毕之前,其他的中间的改变过程都不能看到 ---隔离性 (Consistency)

开启事物............start transaction
提交................commit
回滚................rollback

 

备份与恢复:

39.导出:
mysqldump 可以导出,用终端导出,windows执行语句直接回车执行,不能在语句后面加分号;
(注意:终端没有权限导出,用管理员身份运行终端,用命令cd进入文件夹知道mysqldump文件位置)

a.表
/usr/local/mysql-5.7.18-macos10.12-x86_64/bin/mysqldump -h localhost -uroot -p密码 day01 表1 表2>/Users/chenbo/Desktop/day.sql 导出的是建表语句和insert语句

b.导出库中的所有表:
/usr/local/mysql-5.7.18-macos10.12-x86_64/bin/mysqldump -h localhost -uroot -p day01>/Users/chenbo/Desktop/day.sql

c.导出一个库:
/usr/local/mysql-5.7.18-macos10.12-x86_64/bin/mysqldump -h localhost -uroot -p -B 库1 库2 库3 > 地址/备份文件名称

d.导出所有库:
/usr/local/mysql-5.7.18-macos10.12-x86_64/bin/mysqldump -h localhost -uroot -p -A > 地址/备份文件名称

40.恢复
其实我们备份的就是sql语句,我们只要可以把我们的sql语句运行起来就可以了

a.登陆到mysql命令行
对于库级的备份文件
Mysql>source 目录/filename.sql(备份文件地址)
对于表级的备份文件
Mysql>source 目录/filename.sql(备份文件地址)

b.不登陆到mysql命令行
针对库级的备份文件
mysql -u root -p < 备份文件地址
针对表级的备份文件地址
mysql -u root -p 库名< 备份文件地址

 

索引:

41.定义:对字段建立索引,即针对数据建立的目录
优点:提高查询速度;
缺点:降低增删改速度,增大表的文件大小

索引:
btree..............二叉树索引 查询次数为:log2 N
hash...............哈希索引 查询次数为1

42.索引类型:
普通索引 index..............加快查询速度
唯一索引 unique index.......行上的值不能重复
主键索引 primary key........主键不能重复

主键必唯一,唯一索引不一定是主键,一张表中只能有一个主键,但是可以有多个唯一全文索引
全文索引   fulltext index,在mysql默认中,不支持中文索引,因为英文有空格,标点符号来拆成单词,进而对单词进行索引,
而中文,没有空格来隔开单词,mysql无法识别每个中文词,如is,you,this 高频率出现的词不能索引出来;

43.全文索引的用法.............select * from 表名 where match(全文索引名) against ('单词');

44.查询索引.......show index from 表名

45.创建索引:
alter table 表名 add index/unique/fulltext  索引名(列名)
alter table 表名 add primary key(列名) 不要加索引名,因为主键只有一个

46.删除索引.........alter table 表名 drop index 索引名;

存储过程:
47.查看现有的存储过程.......show procedure status;

48.创建存储过程:

无传参的存储过程:
create procedure 存储过程名()
begin
select * from peoson;(sql语句)
end$

传参的存储过程:
create procedure qq1(n int,t char(1))
begin
if t="d"
then
select * from good3 where id>n;
else
select * from good3 where id<n;
end if;
end$

求1-100的和:
create procedure qq2(n tinyint)
BEGIN
declare s int;声明变量s;
declare i int;声明变量i;
set s=0;设置s的初始值;
set i=1;设置i的初始值;
while i<=n do
set s =s+i;设置累加
set i=i+1;设置i递增
end while;
select s;输出s;
end$

48.调用存储过程..........call 存储过程名;

49.删除存储过程..........drop procedure 存储过程名