-----------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%'; --执行编码显示