mysql基本指令

mysql -h localhost -u username -p password //地址，用户名，密码

exit //退出

show databases;
creat database test;
drop database test;

use test; //选中数据库
show tables; //查看表
drop table test;

alter table goods //增加列
add price int after name //增加的列位置在哪里
add price int first //增加的列在第一行

insert into text //插入数据
(id,age)
values
(….)

update text //修改数据
set
id=2,
age=45
where …

delete text //删数据
where id=2

select * from text //查数据
select * from text where price in (100,200)
select * from text where price between 100 and 200
select * from text where id!=2 && id!=3
select * from text where name like ‘诺基亚%’ //模糊查询

select max(price) from goods
selext max(price) from goods group by id //先id分组，再取最大值
select sum(number) from goods //查询总量
select avg(price) from goods //查平均价格
select count(*) from goods //查有多少行

select id,sum(price*number) as zongjiaqian from goods //取别名用as
select * from goods where price2-price1>10 //筛选
select id,price2-price1 as chajia having chajia>10 //筛选having的用法

select * from goods order by id //排序
select * from goods order by id desc //降序排列，默认为升序排列
select * from goods order by id，price //当id相同时，靠price排序
select * from goods order by price desc limit 3,4 //取出价格从高到底的4、5、6、7名,不写3的话，默认前四名
where, group, having,order by,limit

select * from goods where id=(select max(id) from goods) //where子查询

select avg(price) from (select * from goods where price>100) as tmp //from子查询

select * from goods where price > 200 union select * from goods where price < 100 //union合并两个表

insert into mugua.brand //从shop库中导入到mugua库中
select
brand_id,brand_name
from shop.brand

select //合并表
goods_id,good.cat_id,cat_name,goods_name,shop_price
from
goods left join category // 左连接
on goods.cat_id = category.cat_id //条件

create view xixi as select * from class where id > 2 //创建视图，保存查询结果供下次使用

create trigger tg1 //创建触发器，这里是在订单表中增数据触发货物表数据改动
after insert on ordertable
for each row
begin
update goodtable set num = num - new.much where id = new.gid;
end $

create trigger tg2 创建触发器，这里是在订单表中删数据触发货物表数据改动
after delete on ordertable
for each row
begin
update goodtable set num = num + old.much where id = old.gid;
end $

create trigger tg3 创建触发器，这里是在订单表中修改数据触发货物表数据改动
after delete on ordertable
for each row
begin
update goodtable set num = num + old.much -new.much where id = old.gid;
end $

mysqldump -u root -p 111111 php goods > D:\text.sql //导出php数据库下的goods表
mysqldump -u root -p 111111 php > D:\text.sql //导出php数据库下的所有表
mysqldump -u root -p 111111 -B php mugua > D:\text.sql //导出php与mugua两个库
mysqldump -u root -p 111111 -A > D:\text.sql //导出所有库

source D:\php.sql //恢复php库，其实就是将php.sql里面sql语句执行一遍

use php
source D:\goods.sql //恢复php库下goods表

mysql -u root -p 111111 < D:/php.sql //不登陆mysql恢复php库
mysql -u root -p 111111 php < D:/goods.sql //不登陆mysql恢复php库下的goods表

show index from goods //查看goods表中索引
alter table goods add index //给表中price列增加索引
alter table goods drop index price //删除表中price列索引