匿名通过本文主要向大家介绍了MySQL，基本操作等相关知识,希望本文的分享对您有所帮助
本文记录了MySQL的一些常用操作，不多说了，直接一一列出：
/*
Author: liuyazhuang
Date:2016-04-12
*/
use test;
DROP table if exists equipment;
create table equipment(
assetTag INTEGER primary key,
description varchar(20),
acquired Date
);
/*数据表插入语句*/
INSERT INTO equipment values (50431,'21寸监视器', DATE '2013-01-23');
INSERT INTO equipment values (50532,'pad', DATE '2013-01-26');
/*数据表增加新的列，并初始化*/
ALTER Table equipment ADD COLUMN office VARCHAR(20);
UPDATE equipment SET office='Headquarters';
/*向修改后的表中插入记录*/
INSERT INTO equipment(assetTag,description,acquired) values (50432,'IBM-PC',DATE '2013-01-01');
/*创建代替表的新表（复制）*/
DROP TABLE if EXISTS equipmentMultiSite;
CREATE TABLE equipmentMultiSite(
assetTag INTEGER PRIMARY KEY,
office varchar(20) DEFAULT 'Headquarters',
description varchar(20),
acquired DATE
);
INSERT INTO equipmentMultiSite
SELECT assetTag,'Headquarters',description,acquired FROM equipment;
/*删除现有equipment用视图代替*/
DROP TABLE equipment;
CREATE VIEW equipment AS
SELECT assetTag,description,acquired 'dateAcquired',
FROM equipment WHERE office='Headquarters';
/*基础的查询*/
select sum(amount) from payment where customer_id=1; /*某顾客从开户以来的消费总额*/
select count(*) from payment where customer_id=1; /*某顾客从开户以来的消费次数*/
/*查询某顾客在2005年5月份和6月份消费总额,用"between”或者"<"，">"来建立条件*/
select sum(amount) from payment where customer_id=1 and extract(year from payment_date)=2005 and extract(month from payment_date) between 05 and 06;
select sum(amount) from payment where customer_id=1 and payment_date >= '2005-05-01 00:00:00' and payment_date < '2005-07-01 00:00:00';
/*下面用法报警了*/
select sum(amount) from payment where customer_id=1 and payment_date > UNIX_TIMESTAMP('2005-05-01 00:00:00') and payment_date
< UNIX_TIMESTAMP('2005-07-01 00:00:00');
/*查询某顾客在2005年一年的总消费*/
select sum(amount) from payment where customer_id=1 and extract(year from payment_date)=2005;
select sum(amount) from payment where customer_id=1 and extract(year from payment_date)='2005'; /*年份加单引号的写法也能通过，但不够简练*/
/*针对公司，查询2005年总的销售额*/
select sum(amount) from payment where extract(year from payment_date)=2005;
/*针对公司，查询2005年总的销售额，payment_copy没有建立索引*/
select sum(amount) from payment_copy where extract(year from payment_date)=2005;
explain select sum(amount) from payment where extract(year from payment_date)=2005;
/*为payment_copy建立索引*/
create index cust_id on payment_copy(customer_id);
/*利用索引提高查询效率*/
select sum(amount) from payment_copy where customer_id=1 and extract(year from payment_date)=2005;
/*删除索引*/
drop index cust_id on payment_copy;
create index pay_date on payment(payment_date);
drop index pay_date on payment;
/*建立视图*/
/*利用视图查询*/
select title from film_list;
/*建立临时表*/
create temporary table if not exists tmp_user(
id integer not null auto_increment COMMENT '用户ID',
name varchar(20) not null default '' COMMENT '名称',
sex integer not null default 0 COMMENT '0为男，1为女',
primary key(id)
)engine=MyISAM default charset=utf8 auto_increment=1;
/*显示临时表的细节,show table显示包括table和view但不包括临时表*/
desc tmp_user;
/*mysql不支持在临时表上建立视图，会报错*/
/*create view v_tmp_user as select * from tmp_user;*/
/*
mysql> create view v_tmp_user as select * from tmp_user;
ERROR 1352 (HY000): View's SELECT refers to a temporary table 'tmp_user'
*/
/*创建预处理语句*/
create view pay_view as
select sum(amount) from payment where extract(year from payment_date)=2005;
/*mysql中连接字符串用concat函数，||仅作逻辑运算用*/
create view pay_view as
select concat(c.first_name,' ',c.last_name) as name, sum(p.amount) as amount from payment_copy As p,customer As c
where extract(year from p.payment_date)=2005 and p.customer_id=c.customer_id group by p.customer_id;
select * from pay_view;
/*输出前10条数据*/
select * from pay_view limit 10;
/*输出第11条到20条数据*/
select * from pay_view limit 11,20;
/*随机抽取5条数据,order by rand()*/
select * from pay_view order by rand() limit 5;
drop view pay_view;
/*不能给视图添加索引，只能在基本表上添加索引*/
/*create index pay_view_index on pay_view(amount);*/
/*ERROR 1347 (HY000): 'test.pay_view' is not BASE TABLE*/
/*特别注意：如果视图和基本表一一对应，视图的更新可以达到同步修改基本表的目的；如果进行抽取，运算等操作得到视图，对视图的操作不能同步到
基本表，视图中数据和基本表中的数据不一致，视图中的数据在内存中，做临时显示使用，有必要时才将数据同步到基本表*/
/*事务，mysql中默认每个sql语句是一个事务，就自动提交一次。考虑到性能，多个语句放在一个事务块中*/
begin
drop view pay_view;
create view pay_view as
select concat(c.first_name,' ',c.last_name) as name, sum(p.amount) as amount from payment_copy As p,customer As c
where extract(year from p.payment_date)=2005 and p.customer_id=c.customer_id group by p.customer_id;
select * from pay_view limit 10;
end
/*更改表的存储引擎*/
alert table payment_copy engine=InnoDB;
/*创建mysql定时执行的事件*/
set global event_scheduler=1;
create table testduty(
time varchar(20) default null
)engine=myisam default charset=latin1;
create event if not exists test_event on schedule every 10 second do insert into testduty(time) values(now());
/*删除定时任务*/
drop event test_event;
/*优化数据表*/
optimize table payment;
/*测试加入索引的性能*/
/*建立两张表：一个建立索引，另一个不建立*/
create table if not exists test_has_index(
id integer not null auto_increment,
num integer not null default 0,
d_num varchar(30) not null default '0',
primary key(id)
)engine=MyISAM default charset=utf8 auto_increment=1;
create table if not exists test_no_index(
id integer not null auto_increment,
num integer not null default 0,
primary key(id)
)engine=MyISAM default charset=utf8 auto_increment=1;
/*创建存储过程，用于初始化数据表*/
delimiter |
create procedure i_test(pa integer,tab varchar(30))
begin
declare max_num integer default 10000;
declare i integer default 0;
declare rand_num integer;
declare double_num char;
if tab!='test_no_index' then
select count(id) into max_num from test_has_index;
while i < pa do
if max_num < 10000 then
select cast(rand()*100 as unsigned) into rand_num;
select concat(rand_num,rand_num) into double_num;
insert into test_has_index(num,d_num) values(rand_num,double_num);
end if;
set i=i+1;
end while;
else
select count(id) into max_num from test_no_index;
while i < pa do
if max_num < 10000 then
select cast(rand()*100 as unsigned) into rand_num;
insert into test_no_index(num) values(rand_num);
end if;
set i=i+1;
end while;
end if;
end |
delimiter ;
/*数据表中记录较少时，索引反而会影响性能*/
call i_test(10,'test_has_index'); /*call调用存储过程，并传入参数*/
select num from test_has_index where num!=0;
explain select num from test_has_index where num!=0;
/*Tips: where后的条件，order by ,group by 等这样过滤时，后面的字段最好加上索引。
根据实际情况，选择PRIMARY KEY、UNIQUE、INDEX等索引，但是不是越多越好，要适度。*/
select a.num as num1, b.num as num2
from test_no_index as a
left join test_has_index as b
on a.num=b.num;
explain select a.num as num1, b.num as num2
from test_no_index as a
left join test_has_index as b
on a.num=b.num;
/*Tips:数据量特别大的时候，最好不要用联合查询，即使你做了索引??*/
/*mysql中使用外键，必须选用innodb存储引擎，myisam不