我最近练习的 mysql 语句

-----------2014-02-28 mysql 作业 脚本-----------
create database mydata_0228;
use mydata_0228;

create table if not exists book # 图书表
(
	id int primary key auto_increment comment '主键',
	book_id varchar(32) not null comment '图书ID',
	author_id varchar(32) not null comment '作者ID', 
	book_name varchar(32) not null comment '图书名', ,
	pages int not null comment '图书页码数', ,
	press varchar(32) not null comment '图书出版社'
) engine=innodb default charset=utf8 comment='图书表';
----
insert into book values(null, 'b100', 'A', 'Shell编程', 128, '清华出版社');  
insert into book values(null, 'b200', 'B', 'JAVA开发实战经典', 256, '清华出版社');
insert into book values(null, 'b300', 'C', 'Hadoop实战', 512, '北大出版社');  
insert into book values(null, 'b400', 'D', 'Maven实战', 1024, '北大出版社');  
insert into book values(null, 'b500', 'E', 'Linux私房菜', 2048, '清华出版社');
insert into book values(null, 'b600', 'E', 'Windows编程', 2048, '清华出版社');
insert into book values(null, 'b700', 'D', 'Maven入门', 512, '清华出版社');
insert into book values(null, 'b600', 'D', 'Maven高级编程', 1024, '清华出版社');

create table if not exists author # 作者表
(
    id int primary key auto_increment comment '主键', -- 自动递增 
	author_id varchar(32) not null comment '作者ID',
	author_name varchar(32) not null comment '作者名',
    content varchar(32) not null comment '作者内容简介'
) engine=innodb default charset=utf8 comment='作者表';
----
insert into author values(null, 'A', '张三S', '91年出生，擅长SHELL');  
insert into author values(null, 'B', '李四J', '92年出生，擅长JAVA');
insert into author values(null, 'C', '王五H', '93年出生，擅长Hadoop');  
insert into author values(null, 'D', '朱六M', '94年出生，擅长Maven');  
insert into author values(null, 'E', '王二LW', '95年出生，擅长Linux, Windows'); 
----
create table if not exists award # 奖项表
(
	id int primary key auto_increment comment '主键',
	book_id varchar(32) not null comment '图书ID',
	author_id varchar(32) not null comment '作者ID',
    cup_type varchar(32) not null comment '奖项类型',
    cup_time timestamp not null default now() comment '时间'
) engine=innodb default charset=utf8 comment='奖项表';

insert into award values(null, 'b100', 'A', '金奖', now());  
insert into award values(null, 'b200', 'B', '银奖', now());
insert into award values(null, 'b300', 'C', '铜奖', now());  
insert into award values(null, 'b400', 'D', '金奖', now());  
insert into award values(null, 'b500', 'E', '银奖', now()); 
insert into award values(null, 'b700', 'D', '银奖', now());
insert into award values(null, 'b800', 'D', '银奖', now()); 
----
--二 建立索引
alter table book add index idx_book_book_id(book_id);
alter table book add index idx_book_author_id(author_id);
alter table book add index idx_book_pages(pages);
alter table award add index idx_award_book_id(book_id);
alter table award add index idx_award_author_id(author_id);
alter table award add index idx_award_cup_time(cup_time);
alter table author add index idx_author_author_id(author_id);
----
--三，完成以下SQL

--1. 查询姓王的作者有多少  ac
select count(*) from author where author_name LIKE '王%';

--2. 查询页数最多的前5名作者姓名和书名  ac
select author_name,book_name from book a inner join author b on a.author_id=b.author_id order by a.pages desc limit 5;  

--3. 查询获奖最多的作者姓名，获奖时间,   思路是 ： award 与 author 连接   ac
select author_name, cup_time from award a inner join author b on a.author_id=b.author_id group by a.author_id order by count(*) desc limit 1;
 
--4. 查询获奖作者总人数  ac
select count(distinct author_id) from award;
------------------------------------------------

--5. 查询最近获奖的一本书名和出版社  ac
select book_name,cup_time from book a inner join award b on a.book_id=b.book_id order by cup_time desc limit 1;

--6. 查询同时获得过金奖、银奖的作者姓名  ac
select author_name from award a inner join award b on a.author_id=b.author_id inner join author c  on b.author_id=c.author_id where a.cup_type='金奖' and b.cup_type='银奖';
 
--7. 查询获得金奖的图书有多少本，银奖的有多少本  ac
select cup_type, count(*) from award where cup_type='金奖' OR cup_type='银奖' group by cup_type;

--8. 查询最近一年内获过奖的作者姓名
select author_name from author where author_id in (
          select distinct(author_id) from award where cup_time >= (select date_sub(now(), interval 1 year))
        );
--9. 查询每位作者各自出版的图书名  ac
select a.author_id, author_name, book_name from book a inner join author b on a.author_id=b.author_id  order by a.author_id;


---------------Mysql 的自我学习----------------------------------
Mysql 解决乱码问题，在配置文件中，现在以ubuntu为例子，/etc/mysql/my.cnf 添加
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set=utf8

show variables like 'character%'; --执行编码显示


安装后mysql后，可以在软件中心直接安装，root用户登陆mysql, Ctrl + L 清理屏幕
mysql -uroot -p

show variables like 'character%'; --执行编码显示