第一章 mysql概述

/* 
1.引入一个名为BOOKSQL的新用户，他的密码是BOOKSQLPW

添加用户
grant all on 数据库名.* to 用户名@localhost identified by '密码';
grant all on gamesp.* to newuser@localhost identified by 'password';

添加一个远程用户，名为username密码为password
GRANT ALL PRIVILEGES ON *.* TO username@"%" IDENTIFIED BY 'password'
说明：

（1）grant all 赋予所有的权限
（2）gamesp.* 数据库 gamesp 中所有的表
（3）newuser 用户名
（4）@localhost 在本地电脑上的 mysql server 服务器
（5）identfified by 'password' 设置密码

*/

CREATE USER 'BOOKSQL'@'LOCALHOST' IDENTIFIED BY 'BOOKSQLPW';
create user 'houjie410782'@'localhost' identified by '630529';

/*2.给SQL用户BOOKSQL创建和操作表的权限。*/

grant all privileges on *.* to 'BOOKSQL'@'LOCALHOST' with grant option;
grant all privileges on *.* to 'houjie410782'@'localhost' with grant option;

/*3.创建数据库tennis*/

create database tennis;

/*4.指定tennis为当前数据库。*/

use tennis;

/*mysql查看全部用户*/

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

/*查看MySQL用户权限*/

show grants for root@'localhost';

/*5.创建表players，teams，matches，penalties，committee_members。
int 和 integer 没有什么区别，为了方便而写成int。查看表的结构的时候你会发现表的结构也写成int。
smallint 从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据。存储大小为 2 个字节。
*/

/*一个球员表  -球员的号码、名字、出生日期、性别、加入俱乐部,   街道、房子号、邮政编码、城镇、手机号，团队号码*/
 create table players(
	playerno integer not null,
	name char(15) not null,
	initials char(3) not null,
	birth_date date,
	sex char(1) not null,
	joined smallint not null,
	street varchar(30) not null,
	houseno char(4),
	postcode char(6),
	town varchar(30) not null,
	phoneno char(13),
	leagueno char(4),
	primary key (playerno)
);



/*创建一个teams 团队表  团队号码 ，队员号，教练部门*/

create table teams(
	teamno integer not null,
	playerno integer not null,
	division char(6) not null,
	primary key (teamno)
);


/*创建赛程表matches  赛程表号，团队号，球员号， 赢，输*/

create table matches(
	matchno integer not null,
	teamno integer not null,
	playerno integer not null,
	won smallint not null,
	lost smallint not null,
	primary key (matchno)
);



/*处罚表 付款委托书号，球员号，日期 ，总额 

   说明：
   1.DECIMAL从MySQL 5.1引入，列的声明语法是DECIMAL(M,D)；
   M是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254），M 的默认值是10。
   D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。
   当数值在其取值范围之内，小数位多了，则直接截断小数位。若数值在其取值范围之外，则用最大(小)值对其填充。
   
   2.
   
   一、TIMESTAMP
		显示格式：YYYY-MM-DD HH:MM:SS
		时间范围:[ '1970-01-01 00:00:00'到'2037-12-31 23:59:59']
   二、DATETIME
		显示格式：YYYY-MM-DD HH:MM:SS
		时间范围:[ '1000-01-01 00:00:00'到'9999-12-31 23:59:59']
   三、DATE
		显示格式：YYYY-MM-DD
		时间范围：['1000-01-01'到'9999-12-31']
   四、日期格式转换
		1、字符串转日期
		select STR_TO_DATE('2013-01-29 13:49:18', '%Y-%m-%d %H:%i:%s')
		2、日期转字符串
		select DATE_FORMAT('2013-01-29 13:49:18', '%Y-%m-%d %H:%i:%s')
        
   五、日期的中常用的年月日时分秒星期月份等获取方法
		select TIMESTAMP('2013-01-29 13:50:27');
		select DATE('2013-01-29 13:50:27');
		select YEAR('2013-01-29 13:50:27');
		select MONTH(('2013-01-29 13:50:27');
		select WEEK('2013-01-29 13:50:27');
		select DAY('2013-01-29 13:50:27');
		select TIME('2013-01-29 13:50:27');
		select CURTIME();
		select CURDATE();
		select CURRENT_DATE;
		select CURRENT_TIME;
		select CURRENT_TIMESTAMP;
		select NOW()
 
	六、日期的运算：
		SELECT DATE_ADD('2013-01-29 13:50:27', INTERVAL 1 DAY);
						-> '2013-01-30 13:50:27'
		SELECT DATE_ADD('2013-01-29 13:50:27', INTERVAL 1 HOUR);
						-> '2013-01-29 14:50:27'
		SELECT DATE_ADD('2013-01-29 13:50:27', INTERVAL 1 MONTH);
					   -> '2013-02-28 13:50:27'   
				
        
*/

create table penalties(
	paymentno integer not null,
	playerno integer not null,
	payment_date date not null,
	amount decimal(7,2) not null,
	primary key (paymentno)
);



/*委员会 成员表 队员号 ，（任职）开始日期，（任职）结束日期，职位*/

create table committee_members(
	playerno integer not null,
	begin_date date not null,
	end_date date,
	position char(20),
	primary key (playerno,begin_date)
);


/*查看一下表的结构*/

desc players;
desc teams;



/*查看一下数据库里面有多少张表*/
show tables;

/*6.分别为表中填充数据。*/

insert into players values(2,'Everett','R','1948-09-01','M',1975,'Stoney Road','43','3575NH','Stratford','070-237893','2411');
insert into players values(7,'Wise','GWS','1963-05-11','M',1981,'Edgecombe Way','39','9758VB','Stratford','070-347689',NULL);
insert into players values(8,'NewCastle','B','1962-07-08','F',1980,'Station Road','4','6584R0','Inglewood','070-458458','2983');
insert into players values(27,'Collins','DD','1964-12-28','F',1983,'Long Drive','804','8457DK','Eltham','079-234857','2513');
insert into players values(28,'Collins','C','1963-06-22','F',1983,'Old Mian Road','10','1294QK','Midhurst','071-659599',NULL);
insert into players values(39,'Bishop','D','1956-10-29','M',1980,'Eaton Square','78','9629CD','Stratford','070-393435',NULL);
insert into players values(44,'Baker','E','1963-01-09','M',1980,'Lewis Street','23','4444LJ','Inglewood','070-368753','1124');
insert into players values(57,'Brown','M','1971-08-17','M',1985,'Edgecombe Way','16','4377CB','Stratford','070-473458','6409');
insert into players values(83,'Hope','PK','1956-11-11','M',1982,'Magdalene Road','16A','1812UP','Stratford','070-353548','1608');
insert into players values(95,'Miller','P','1963-05-14','M',1972,'High Street','33A','5746OP','Douglas','070-867564',NULL);
insert into players values(100,'Parmanter','P','1963-02-28','M',1979,'Haseltine Lane','80','1234KK','Stratford','070-494593','6524');
insert into players values(104,'Moorman','D','1970-05-10','F',1984,'Stout Street','65','9437A0','Eltham','079-987571','7060');
insert into players values(112,'Bailey','IP','1963-10-01','F',1984,'Vixen Road','8','6392LK','Plymouth','010-548745','1319');
insert into players values(6,'parmenter','R','1964-06-25','M',1977,'Haseltine Lane','80','1234KK','Stratford','070-476537','8467');



insert into teams values(1,6,'first');
insert into teams values(2,27,'second');



insert into matches values(1,1,6,3,1);
insert into matches values(2,1,6,2,3);
insert into matches values(3,1,6,3,0);
insert into matches values(4,1,44,3,2);
insert into matches values(5,1,83,0,3);
insert into matches values(6,1,2,1,3);
insert into matches values(7,1,2,1,3);
insert into matches values(8,1,8,0,3);
insert into matches values(9,2,27,3,2);
insert into matches values(10,2,104,3,2);
insert into matches values(11,2,112,2,3);
insert into matches values(12,2,112,1,3);
insert into matches values(13,2,8,0,3);



insert into penalties values(1,6,'1980-12-08',100.00);
insert into penalties values(2,44,'1981-05-05',75.00);
insert into penalties values(3,27,'1983-09-10',100.00);
insert into penalties values(4,104,'1984-12-08',50.00);
insert into penalties values(5,44,'1980-12-08',25.00);
insert into penalties values(6,8,'1980-12-08',25.00);
insert into penalties values(7,44,'1982-12-30',30.00);
insert into penalties values(8,27,'1984-11-12',75.00);



insert into committee_members values(2,'1990-01-01','1992-12-31','Chairman');
insert into committee_members values(2,'1994-01-01',NULL,'Member');
insert into committee_members values(6,'1990-01-01','1990-12-31','Secretary');
insert into committee_members values(6,'1991-01-01','1992-12-31','Member');
insert into committee_members values(6,'1992-01-01','1993-12-31','Treasurer');
insert into committee_members values(6,'1993-01-01',NULL,'Chairman');
insert into committee_members values(8,'1990-01-01','1990-12-31','Treasurer');
insert into committee_members values(8,'1991-01-01','1991-12-31','Secretary');
insert into committee_members values(8,'1993-01-01','1993-12-31','Member');
insert into committee_members values(8,'1994-01-01',NULL,'Member');
insert into committee_members values(27,'1990-01-01','1990-12-31','Member');
insert into committee_members values(27,'1991-01-01','1991-12-31','Treasurer');
insert into committee_members values(27,'1993-01-01','1993-12-31','Treasurer');
insert into committee_members values(57,'1992-01-01','1992-12-31','Secretary');
insert into committee_members values(95,'1994-01-01',NULL,'Treasurer');
insert into committee_members values(112,'1992-01-01','1992-12-31','Member');
insert into committee_members values(112,'1994-01-01',NULL,'Secretary');

/*查看一下这些表格的数据*/

select * from players;
select * from  teams;

select * from matches;

select * from penalties;

select * from committee_members;

/*7. 获取 居住在Stratford的每个球员的号码、名字和出生日期，按照名字的顺序排列结果。*/

select playerno,name,birth_date 
from players
where town='Stratford'
order by name;

/*
8.获取在1980年后加入俱乐部并且居住在Stratford的每个球员号码，按照球员号码排序。
注意：大于某个日期表示，在这个日期之后，如果要之前的话，需要小于该日期。
*/

select playerno  from players 
where town='Stratford' and joined>1980
order by playerno;

/*9.获取有关罚款的所有信息。*/

select * from penalties;

/*
10. 121的33倍是多少？
说明 数据库可以直接运算
*/

select 33*121;

/*11.获取44号球员的罚款数额，并把该球员的金额改为200美元。*/

select playerno,amount
from penalties
where playerno=44;

/*
更新的时候出现了个问题
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
原因是：MySQL Workbench的安全设置。当要执行的SQL语句是进行批量更新或者删除的时候就会提示这个错误。


解决方法：
方法一：SET SQL_SAFE_UPDATES = 0
方法二：
打开Workbench的菜单[Edit]->[Preferences...]
切换到[SQL Editor]页面
把[Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)]之前的对勾去掉
点击[OK]按钮
最后一步记得要重启一下Workbench，否则你仍然会得到这个错误提示。
        最后：重启之后需要加上用哪个数据库。use tennis;
*/

use tennis;
update  penalties
set amount=200
where playerno=44;

/*查看一下修改成功没有*/

select * from penalties
where playerno=44;

/*12.删除penalties表中罚款金额大于100美元的每一笔罚款。*/

delete
from penalties
where amount>100;

select * from penalties;

/*13.在penalties表的amount列上创建一个索引。
说明：MySQL索引作用
在索引列上，除了上面提到的有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。
特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
*/

create index penalties_amount on
penalties(amount);

/*查看索引值*/
show index from penalties;

/*14.创建一个视图，记录每场比赛赢得的局数和输掉的局数。
说明：
1.视图也称为虚表，即虚拟的表，是一组数据的逻辑表示。
2.视图对应着一条select语句，结果集被赋予一个名字，即视图的名字。accessible
3.视图本身不包含任何数据，它只是映射到基表的一条select语句。当基表的数据发生改变的时候，视图的数据也发生了改变。

视图的作用：
1.简化复杂的查询
2.限制数据访问。
ABS(X)  返回X 的绝对值。
*/

/*创建视图*/
create view win_lost 
as 
(
	select won,lost from matches
);


/*查看一下视图*/

select * from win_lost;

desc win_lost;

/*修改一下视图，想增加一个比赛号进去*/

create or replace view win_lost 
as 
(
	select matchno,won,lost from matches
);

/*重新查看一下视图*/

select * from win_lost;

desc win_lost;