转载请注明出处:blog.csdn.net/l1028386804/article/details/51133169

本文记录了MySQL的一些常用操作，不多说了，直接一一列出：

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 equipmentMultiSiteSELECT assetTag,'Headquarters',description,acquired FROM equipment;DROP TABLE equipment;CREATE VIEW equipment ASSELECT 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 asselect 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;endalert 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))begindeclare max_num integer default 10000;declare i integer default 0;declare rand_num integer;declare double_num char;if tab!='test_no_index'  thenselect count(id) into max_num from test_has_index;while i < pa doif max_num < 10000 thenselect 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;elseselect count(id) into max_num from test_no_index;while i < pa doif max_num < 10000 thenselect 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 commentwhere 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,t2ment_id from t1 inner join  t2 on t1.blog_id=t2.blog_id;select  t1.user_id,t1.blog_id,count(t2ment_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(t2ment_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(t2ment_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>tment_countsorder by t.user_id,tment_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(t2ment_id) as counts from t1 inner join  t2 on t1.blog_id=t2.blog_id group by t1.blog_id having count(t2ment_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) bon a.dateline = b.datelinegroup 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 integercontains sqlbegindeclare value integer;set value=0;select current_value into valuefrom sequencewhere name=seq_name;return value;end$delimiter ;select currval('MovieSeq');drop function if exists nextval;delimiter $create function nextval(seq_name varchar(50))returns integercontains sqlbegin update sequencesetcurrent_value=current_value+incrementwhere 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 integercontains sqlbeginupdate sequenceset 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 Screenshotswhere bug_id=1 and image_id=2;select screenshot_image into dumpfile 'F:\\aaa222.jpg'from Screenshotswhere 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';