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  UNIX_timestamp('2005-05-01 00:00:00') and payment_date    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 (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) where子句 联结条件和查询条件 > group by子句分组 > having子句搜索 >order by子句结果排序 > limit显示某些记录  */      /*连接查询常用模式*/  /*1、select * from table1, table2 where table1.id=table2.id  2、select * from table1 left join table2 on table1.id = table2.id  3. select * from table1 left join table2 using(id)  4. select * from table1 left join table2 on table1.id=table2.id left join table3 on table2.id=table3.id  5. select * from table1 use index(key1,key2) where key1=1 and key2=2 and key3=3  6. slect * from table1 ignore index(key1) where key1=1 and key2=2 and key3=3  */  /*验证各种连接结果,以film, film_actor, actor为例*/  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;  /**/  /**/  /**/  	  /*查询中去掉重复字段*/  /*建立测试数据表school_report*/  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;        /*记录和分析 花费时间较多的select*/    /*首先进行如下设置，设置long_query_time时间限*/  show variables like "%long%";  set global long_query_time=2;    /*检查并开启慢查询,会显示mysql-slow.log文件的路径*/  show variables like "%slow%";  set global slow_query_log='ON';    /*这样超过long_query_time的查询会记录到mysql日志中*/    /*分组后前n条数据: http://www.php.cn/*/  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');    /*按name分组取value 最大的值的记录*/    --方法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 ;  /*   name val memo   ---------- ----------- --------------------   a 3 a3:a的第三个值   b 5 b5b5b5b5b5     方法三、四效率比较高  */     /*按name分组取val最小的值的记录*/    --方法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  (select count(*) from tb where name = a.name and val  (select count(*) from tb where name = a.name and val  (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(*)