第一章 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 playerswhere town='Stratford'order by name;

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

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

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

select * from penalties;

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

select 33*121;

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

select playerno,amountfrom penaltieswhere 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  penaltiesset amount=200where playerno=44;/*查看一下修改成功没有*/select * from penaltieswhere playerno=44;

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

deletefrom penaltieswhere amount>100;select * from penalties;

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

create index penalties_amount onpenalties(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;