mysql数据查询方法(查询数据库版本、数据表创建与插入数据等)

---------------------where表达式中的列别名----------------------
select t, srcuser, dstuser, size/1024 as kilobytes
from mail where kilobytes > 500;

select t, srcuser, dstuser, size/1024 as kilobytes
from mail where size/1024 > 500;

---------------------调试比较表达式----------------------
select * from mail where srcuser < 'c' and size > 500;
select srcuser, srcuser < 'c',  size, size > 500 from mail;
select srcuser, srcuser < 'c',  size, size > 500 from mail where srcuser < 'c' and size > 500;

---------------------使查询结果唯一化---------------------
select srcuser from mail;
select distinct srcuser from mail;
select distinct year(t), month(t), dayofmonth(t) from mail;
select count(distinct srcuser) from mail;

----------------------如何处理null值-----------------------

--创建表
CREATE TABLE taxpayer
(
  name  CHAR(20),
  id    CHAR(20)
);

--插入数据
INSERT INTO taxpayer (name,id) VALUES ('bernina','198-48');
INSERT INTO taxpayer (name,id) VALUES ('bertha',NULL);
INSERT INTO taxpayer (name,id) VALUES ('ben',NULL);
INSERT INTO taxpayer (name,id) VALUES ('bill','475-83');

select * from taxpayer;
select * from taxpayer where id = null;
select * from taxpayer where id != null;
select * from taxpayer where id is null;
select * from taxpayer where id is not null;
select null = null, null <=> null;

select if(id is null, "unknown", id) as 'id' from taxpayer;
select name, ifnull(id, 'unknown') as 'id' from taxpayer;

----------------------结果集排序-----------------------
select * from mail where size > 1000000 order by size;
select * from mail where dstuser = 'tricia' order by srchost, srcuser;
select * from mail where size > 50000 order by size desc;

----------------------使用视图简化查询-----------------------
select date_format(t, '') as date_sent,
        concat(srcuser, '@', srchost) as sender,
        concat(dstuser, '@', dsthost) as recipient,
        size
from mail;

create view mail_view as
select date_format(t, '%M %e, %Y') as date_sent,
        concat(srcuser, '@', srchost) as sender,
        concat(dstuser, '@', dsthost) as recipient,
        size
from mail;

select date_sent, sender, size from mail_view
where size > 100000
order by size;

----------------------多表查询-----------------------

--创建表
CREATE TABLE profile_contact
(
  profile_id   INT UNSIGNED NOT NULL, # ID from profile table
  service      CHAR(20) NOT NULL,     # messaging service name
  contact_name CHAR(25) NOT NULL,     # name to use for contacting person
  INDEX (profile_id)
);

--插入数据
INSERT INTO profile_contact
  VALUES
    (1, 'AIM', 'user1-aimid'),
    (1, 'MSN', 'user1-msnid'),
    (2, 'AIM', 'user2-aimid'),
    (2, 'MSN', 'user2-msnid'),
    (2, 'Yahoo', 'user2-yahooid'),
    (4, 'Yahoo', 'user4-yahooid')
;

select * from profile_contact order by profile_id, service;

select id, name, service, contact_name
from profile inner join profile_contact on id = profile_id;

select * from profile_contact
where profile_id = (select id from profile where name = 'Mort');

----------------------从查询结果集头或尾取出部分行-----------------------
select * from profile;
select * from profile limit 1;
select * from profile limit 5;
select * from profile order by birth limit 1;
select * from profile order by birth desc limit 1;

select name, date_format(birth, '%m-%d') as birthday
from profile
order by birthday
limit 1;

----------------------在结果集头中间选取部分行-----------------------
select * from profile order by birth limit 2, 1;
select * from profile order by birth desc limit 2, 1;

select count(*) from profile;
select * from profile order by name desc limit 0, 4;
select * from profile order by name desc limit 4, 4;
select * from profile order by name desc limit 8, 4;

select sql_calc_found_rows * from profile order by id limit 4;
select found_rows();

----------------------选择合适的limit参数-----------------------

--创建表
CREATE TABLE al_winner
(
  name CHAR(30),
  wins INT
);

--初始化数据 al_winner-2001.txt
Mulder, Mark    21
Clemens, Roger  20
Moyer, Jamie    20
Garcia, Freddy  18
Hudson, Tim     18
Abbott, Paul    17
Mays, Joe       17
Mussina, Mike   17
Sabathia, C.C.  17
Zito, Barry     17
Buehrle, Mark   16
Milton, Eric    15
Pettitte, Andy  15
Radke, Brad     15
Sele, Aaron     15

--导入数据数据
load data local infile 'al_winner-2001.txt' into table al_winner;

set @n = 0;
set @val = 0;
select @n:=if(@val=wins, @n, @n+1) as rank,name, @val:=wins as wins
from al_winner order by wins desc, name;

selectname, wins from al_winner
order by wins desc, name;

select name, wins from al_winner
order by wins desc, name
limit 4;

select name, wins from al_winner
order by wins desc, name
limit 3, 1;

select name, wins from al_winner
where wins >= 18
order by wins desc, name;

select name, wins from al_winner
where wins >=
(
    select wins from al_winner
    order by wins desc, name
    limit 3, 1
)
order by wins desc, name;

select distinct wins from al_winner
order by wins desc, name
limit 3, 1;

select name, wins from al_winner
where wins >= 17
order by wins desc, name;

select name, wins from al_winner
where wins >=
(
    select distinct wins from al_winner
    order by wins desc, name
    limit 3, 1
)
order by wins desc, name;

----------------------当limit需要“错误”的排列顺序时做什么-----------------------

select name, birth from profile order by birth desc limit 4;
select count(*) from profle;
select name, birth from profile order by birth limit 6, 4;

select name, birth
from
(
    select name, birth from profile order by birth desc limit 4
) as t
order by birth;

----------------------从表达式中计算limit值-----------------------

--limit只允许数字作为其参数，不能使用表达式
select * from profile limit 5+5;
select * from profile limit @skip_count, @show_count;

--------------------------表关联-------------------------

----------------------克隆表-----------------------------

--仅仅克隆表结构(不克隆源表外键定义)
create table new_table like original_table;
create table mail2 like mail;
select * from mail2;

--转移表数据
insert into new_table select * from original_table;
insert into mail2 select * from mail where srcuser = 'barb';
select * from mail2;

----------------------将查询结果保存到表中-----------------------------
--如果目的表已经存在
insert into dsttbl(i, s) select val, name from src_tal;
insert into dst_tbl select * from src_tbl;  /*目的表与源表结构相同，拷贝全部数据*/
insert into dst_tbl select * from src_tbl where val > 1000 and name like 'A%';  /*目的表与源表结构相同，拷贝部分数据*/
insert into dst_tbl(i, s) select count(*), name from src_tbl group by name;     /*目的表与基于源表进行统计的结果结构相同，将源表统计结果插入目的表*/

--如果目的表不存在
create table dst_tbl select * from src_tbl;
create table dst_tbl select * from src_tbl where 0;     /*仅克隆表结构，不克隆约束、键等，不包含数据*/
create table dst_tbl select b, c from src_tbl;  /*依照源表部分列建表*/
create table dst_tbl select c, b, a from src_tbl;  /*改变源表列定义顺序（假设源表为a、b、c）*/
create table dst_tbl (id int not null auto_increment), primary key(id)) select a, b, c from src_tbl;  /*在create table子句中插入列定义，此列不存在于源表中*/
/*
create table dst_tbl
(
    id int not null auto_increment),
    primary key(id)
)
select a, b, c from src_tbl; 
*/

create table hoho
(
  id int not null auto_increment,
  primary key (id)
)
select * from limbs;

create table dst_tbl select inv_no, sum(unit_cost * quantity) as total_cost from src_tbl group by inv_no;  /*在create table子句中为表达式赋予别名，此列不存在于源表中*/
/*
create table dst_tbl
select inv_no, sum(unit_cost * quantity) as total_cost
from src_tbl
group by inv_no;
*/

create table dst_tbl (primary key(id), index(state, city)) select * from src_tbl;  /*根据源表定义目的表主键、索引，假设源表主键为id，state、city列上有索引*/

/*间接复制列属性与默认值*/
create table dst_tbl (primary key(id)) select * from src_tbl;
alter table dst_tbl modify id int unsigned not null auto_increment;

----------------------使用临时表-----------------------------

--普通建表
create temporary table tbl_name(...)

--克隆表
create temporary table new_table like original_table;

--根据查询结果建表
create temporary table tbl_name select ...;

--创建与已存在的表同名的临时表，暂时屏蔽非临时表
create temporary table mail select * from mail;
select count(*) from mail;
delete from mail;
select count(*) from mail;
drop table mail;
select count(*) from mail;
drop temporary table if exists tbl_name;

----------------------检查或改变某个表的存储引擎-----------------------------

--innodb BDB myisam
--检测information_schema
select engine from information_schema.tables where table_schema = 'cookbook' and table_name = 'mail';
show table statusG
show table status like 'mail'G
show create table mailG

--改变表引擎
alter table mail engine = InnoDB;
alter table mail engine = BDB;
alter table mail engine = MyISAM;

--生成唯一的表名
drop table if exists tbl_name;
select connection_id();