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;
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;
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');
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';
select sum(amount) from payment where extract(year from payment_date)=2005;
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;
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;
desc tmp_user;
create view pay_view
as
select sum(amount) from payment where extract(year from payment_date)=2005;
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;
select * from pay_view limit 10;
select * from pay_view limit 11,20;
select * from pay_view order by rand() limit 5;
drop view pay_view;
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;
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');
select num from test_has_index where num!=0;
explain select num from test_has_index where num!=0;
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;
create table test_user(
id int(10) 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=innodb
default
charset=utf8 auto_increment=1;
insert into test_user(name,sex) values(
"Han"
,1),(
"Max"
,2);
create table test_order(
order_id int(10) not null auto_increment comment '订单ID',
u_id int(10) not null
default
0 comment '用户ID',
username varchar(20) not null
default
'' comment '用户名',
money int(10) not null
default
0 comment '钱数',
datetime timestamp not null
default
current_timestamp comment '生成时间',
primary key(order_id),
index(u_id),
foreign key order_f_key(u_id) references test_user(id)
)engine=innodb
default
charset=utf8 auto_increment=1;
insert into test_order(u_id,username,money,datetime) values(1,'Han',223,current_timestamp);
insert into test_order(u_id,username,money,datetime) values(2,'Max',423,current_timestamp);
delete
fromm user where id=1;
insert into test_order(u_id,username,money,datetime) values(5,Sophe,223,current_timestamp);
show create test_order;
alter table test_order drop foreign key test_order_ibfk_1;
alter table test_order add foreign key(u_id) references test_user(id) on
delete
cascade on update cascade;
update test_user set id=11 where id=1;
create table comment(
c_id int(10) not null auto_increment comment '评论ID',
u_id int(10) not null comment '用户ID',
name varchar(20) not null
default
'' comment '用户名',
content varchar(1000) not null
default
'' comment '评论内容',
datetime timestamp not null
default
current_timestamp,
num1 int(10)
default
null,
num2 int(10)
default
null,
primary key(c_id)
)engine=myisam
default
charset=utf8 auto_increment=1;
insert into comment(u_id,name,content,num1,num2)
values (1,'test1','3445212',4,23),(2,'test2','up!!',43,21),(3,'test3','a3235b',23,23);
select c_id, greatest(num1,num2)
as
max, least(num1,num2)
as
min, num1,num2 from comment
where num1!=
""
and
num2!=
""
;
select concat_ws(',',name, content,datetime) from comment;
select concat(',',name, content,datetime) from comment;
select concat(name, ',',content,',',datetime) from comment;
select * from comment where datetime <= (select now()-interval 10 hour
as
time_start);
select last_insert_id();
select * from comment where content regexp '[A-z][0-9]+';
select * from comment where content regexp '^u';
select cast(rand()*1000
as
unsigned)
as
rand_num;
select day(now())
as
day;
select substring(now(),9,2)
as
day;
drop table
if
exists user1;
create table
if
not exists user1(
id int(10) not null auto_increment,
name varchar(20) not null
default
'',
sex integer not null
default
0,
primary key(id)
)engine=myisam
default
charset=utf8 auto_increment=1;
drop table
if
exists user2;
create table
if
not exists user2(
id int(10) not null auto_increment,
name varchar(20) not null
default
'',
sex integer not null
default
0,
primary key(id)
)engine=myisam
default
charset=utf8 auto_increment=1;
insert into user1(name,sex) values('Alice',0),('Apple',1);
insert into user2(name,sex) values('Bob',1),('Band',0);
drop table
if
exists t1;
create table
if
not exists t1(
user_id int(10) not null,
blog_id int(10) not null
);
drop table
if
exists t2;
create table
if
not exists t2(
blog_id int(10) not null,
comment_id int(10) not null
);
insert into t1 values(1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(2,7),(3,8);
insert into t2 values(2,1),(2,2),(2,3),(2,4),(3,5),(4,6),(4,7),(4,8),(5,9),(5,10);
alter t2 change user_id blog_id int(10);
alter t2 change blog_id comment_id int(10);
insert into t1 values(1,9),(1,10);
insert into t2 values(9,11),(9,12),(10,13);
alter table t2 change user_id blog_id int(10);
select t1.user_id,t1.blog_id,t2.comment_id from t1 inner join t2 on t1.blog_id=t2.blog_id;
select t1.user_id,t1.blog_id,
count
(t2.comment_id) from t1 inner join t2 on t1.blog_id=t2.blog_id
group by t1.blog_id;
select t1.user_id,t1.blog_id,
count
(t2.comment_id)
as
counts
from t1 inner join t2 on t1.blog_id=t2.blog_id
group by t1.blog_id
order by counts desc
limit 1;
drop table
if
exists t1_t2;
create table
if
not exists t1_t2(
user_id int(10),
blog_id int(10),
comment_counts int(10)
);
insert into t1_t2 values(2,5,2),(1,9,2),(1,3,1),(1,10,1),(1,2,4),(2,4,3);
insert into t1_t2 (select t1.user_id,t1.blog_id,
count
(t2.comment_id)
as
counts
from t1 inner join t2 on t1.blog_id=t2.blog_id
group by t1.blog_id
order by counts desc);
select t.user_id,t.blog_id from t1_t2 t where 2>(select
count
(*) from t1_t2
where user_id=t.user_id
and
comment_counts>t.comment_counts
order by t.user_id,t.comment_counts)
order by t.user_id;
select t1.user_id,t1.blog_id from t1 inner join t2 on t1.blog_id=t2.blog_id order by counts desc;
group by t1.blog_id ;
select t1.user_id,t1.blog_id,
count
(t2.comment_id)
as
counts
from t1 inner join t2 on t1.blog_id=t2.blog_id
group by t1.blog_id
having
count
(t2.comment_id)<=2;
order by counts desc;
limit 1;
create table alluser like user1;
alter table alluser engine=merge union(user1,user2);
alter table alluser insert_method=last;
desc alluser;
select * from alluser;
insert into alluser(name,sex) values('Merry',1),('Han',0);
select * from user1;
select * from user2;
update alluser set sex=replace(sex,1,0) where id=2;
select a.actor_id,b.film_id from actor
as
a inner join film_actor
as
b
on a.actor_id = b.actor_id limit 10;
select a.actor_id,film_id from actor
as
a, film_actor
as
b
where a.actor_id = b.actor_id limit 10;
select f.title,fa.actor from film f left join film_actor fa on f.film_id = fa.film_id limit 10;
select film.*,film_actor.* from film left join film_actor on film.film_id = film_actor.film_id limit 10;
select film.title,film_actor.actor_id from film left join film_actor on film.film_id = film_actor.film_id limit 10;
select film.title,film_actor.actor_id from film left join film_actor using(film_id) limit 10;
select film.title,film_actor.actor_id from film left join film_actor using(film_id) group by film.film_id limit 10;
select film.title,film_actor.actor_id from film inner join film_actor on film.film_id = film_actor.film_id limit 10;
drop table
if
exists school_report;
create table school_report(
id int(10) not null auto_increment comment '表ID',
u_id int(10) not null comment '学生ID',
name varchar(20) not null
default
'' comment '学生姓名',
score varchar(4) not null
default
0 comment '学生成绩',
message varchar(50) not null
default
'',
dateline timestamp not null
default
current_timestamp,
primary key(id)
)engine=innodb
default
charset=utf8 auto_increment=1;
insert into school_report(u_id,name,score,message)
values(1,'张三',89,'helloworld'),(1,'张三',90,'hello'),(2,'李四',92,'helloworld'),
(3,'王五',93,'world');
select distinct name,score from school_report;
select *,
count
(distinct name) from school_report group by name;
select *
from school_report a inner join(
select max(dateline)
as
dateline
from school_report group by u_id) b
on a.dateline = b.dateline
group by id order by a.dateline desc;
show variables like
"%long%"
;
set
global
long_query_time=2;
show variables like
"%slow%"
;
set
global
slow_query_log='ON';
drop table
if
exists tb;
create table tb (
name varchar(10),
val int,
memo varchar(20)
);
insert into tb values('a', 2, 'a2(a的第二个值)'),('a',1,'a1--a第一个值'),
('a',3,'a3--a第三个值'),('b',1,'b1--b第一个值'),('b',3,'b3--b第三个值'),
('b',2,'b3--b2b2b2'),('b',4,'b4b4b4'),('b',5,'b5b5b5b5');
--方法1:select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name
and
val > a.val);
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name
and
a.val = b.val order by a.name;
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name
and
a.val = b.val order by a.name ;
--方法5
select a.* from tb a where 1 > (select
count
(*) from tb where name = a.name
and
val > a.val ) order by a.name ;
--方法1:select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name
and
val < a.val);
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name
and
a.val = b.val order by a.name ;
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name
and
a.val = b.val order by a.name ;
--方法5
select a.* from tb a where 1 > (select
count
(*) from tb where name = a.name
and
val < a.val) order by a.name ;
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
select a.* from tb a where 2 > (select
count
(*) from tb where name = a.name
and
val < a.val ) order by
a.name,a.valselect a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select
count
(*) from tb where name = a.name
and
val < a.val having
Count
(*) < 2) order by a.name
select a.* from tb a where 2 > (select
count
(*) from tb where name = a.name
and
val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select
count
(*) from tb where name = a.name
and
val > a.val having
Count
(*) < 2) order by a.name
drop table
if
exists sequence;
create table sequence(
name varchar(50) not null,
current_value int not null,
increment int not null
default
1,
primary key(name)
)engine=InnoDB;
insert into sequence values('MovieSeq',3,5);
Drop
function
if
exists currval;
delimiter $
create
function
currval(seq_name varchar(50))
returns integer
contains sql
begin
declare
value integer;
set value=0;
select current_value into value
from sequence
where name=seq_name;
return
value;
end
$
delimiter ;
select currval('MovieSeq');
drop
function
if
exists nextval;
delimiter $
create
function
nextval(seq_name varchar(50))
returns integer
contains sql
begin
update sequence
set current_value=current_value+increment
where name=seq_name;
return
currval(seq_name);
end
$
delimiter ;
select nextval('MovieSeq');
select nextval('MovieSeq');
drop
function
if
exists setval;
delimiter $
create
function
setval(seq_name varchar(50),val integer)
returns integer
contains sql
begin
update sequence
set current_value=val;
where name = seq_name;
return
currval(seq_name);
end
$
delimiter ;
select setval('MovieSeq',150);
select curval('MovieSeq');
select nextval('MovieSeq');
drop table
if
exists Issues;
create table Issues (
issue_id integer auto_increment primary key
);
drop table
if
exists Bugs;
create table Bugs(
issue_id integer primary key,
foreign key(issue_id) references Issues(issue_id)
);
insert into Issues values();
insert into Issues values();
select * from Issues;
select * from Bugs;
insert into Bugs values(4);
insert into Bugs values(2);
select * from Bugs;
drop table
if
exists Num;
create table Num(
id integer auto_increment primary key,
price float not null
default
'0.0',
sum float not null
default
'0.0',
);
alter table Num add column price2 numeric(9,2)
default
'0.0';
drop table
if
exists PersonalContacts;
Create table PersonalContacts(
id integer auto_increment primary key,
salutation varchar(5) check (salutation in ('Mr.','Mrs.','Ms.','Dr.'))
);
drop table
if
exists PersonalContacts;
Create table PersonalContacts(
id integer auto_increment primary key,
salutation enum('Mr.','Mrs.','Ms.','Dr.')
);
insert into PersonalContacts(salutation) values('Mz.');
select * from PersonalContacts;
insert into PersonalContacts(salutation) values('Ms.');
select * from PersonalContacts;
alter table PersonalContacts modify column salutation enum('Mr.','Mrs.','Ms.','Dr.','Mz.');
insert into PersonalContacts(salutation) values('Mz.');
select * from PersonalContacts;
drop table
if
exists Salutation;
create table Salutation(
status varchar(5) primary key
);
insert into Salutation(status) values('Mr.','Ms.');
drop table
if
exists PersonalContacts2;
create table PersonalContact2(
id integer auto_increment primary key,
status varchar(5),
foreign key(status) references Salutation(status) on update cascade
);
select status from Salutation order by status;
insert into Salutation(status) values('Mss.');
update Salutation set status='Dr.' where status='Mss';
alter table Salutation add column active enum('inactive','active') not null
default
'active';
update Salutation set active='inactive' where status='Dr.';
select status from Salutation where active='active';
drop table
if
exists Bugs;
create table Bugs(
bug_id integer auto_increment primary key
);
drop table
if
exists Screenshots;
create table Screenshots(
bug_id integer not null,
image_id serial not null,
screenshot_image BLOB,
caption varchar(100),
primary key (bug_id,image_id),
foreign key (bug_id) references Bugs(bug_id)
);
insert into Bugs values();
insert into Bugs values();
insert into Bugs values();
insert into Bugs values();
insert into Bugs values();
insert into Bugs values();
insert into Screenshots(bug_id,screenshot_image) values(1,load_file('f:\aaa.jpg'));
select bug_id,image_id from Screenshots;
select screen_image from Screenshots;
insert into Screenshots(bug_id,screenshot_image) values(1,load_file('f:\bbb.jpg'));
insert into Screenshots(bug_id,screenshot_image) values(2,load_file('f:\ccc.jpg'));
delete
from Screenshots where bug_id=1
and
image_id=3;
select screenshot_image into dumpfile 'F:\aaa111.jpg'
from Screenshots
where bug_id=1
and
image_id=2;
select screenshot_image into dumpfile 'F:\\aaa222.jpg'
from Screenshots
where bug_id=1
and
image_id=2;
drop table
if
exists Bugs1;
create table Bugs1(
bug_id serial primary key,
date_reported
date
not null,
summary varchar(80) not null,
status varchar(10) not null,
hours numeric(9,2),
index(bug_id,date_reported,status)
);
drop table
if
exists Tags;
create table Tags(
bug_id integer not null,
tag varchar(20),
primary key(bug_id,tag),
foreign key(bug_id) references Bugs(bug_id)
);
insert into Tags(bug_id,tag) values('1','crash'),('2','performance'),('2','printing'),('2','crash'),(3,'printing');
select * from Tags where bug_id=2;
select * from Bugs join Tags using(bug_id) where tag='performance';
select * from Bugs
join Tags
as
t1 using (bug_id)
join Tags
as
t2 using (bug_id)
where t1.tag='printing'
and
t2.tag='performance';
insert into Tags(bug_id,tag) values (3,'save');
delete
from Tags where bug_id=2
and
tag='crash';