MySQL学习笔记1

1. 创建新用户（用户名：xlh，密码：xlh123456）
create user 'xlh' @ 'localhost' identified by 'xlh123456'

2. 给SQL用户xlh创建和操作表格的权限
grant all privileges
on *.*
to 'xlh' @ 'localhost'
with grant option

3. 创建名为tennis的数据库
create database tennis

4. 指定tennis为当前数据库，即使用tennis数据库
use tennis

5. 创建表
create table teams
(teamNo integer not null,
playerNo integer not null,
division char(6) not null,
primary key (teamNo))

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))

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))

6. 用数据填充表
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(4,1,44,3,2)

insert into penalties values(1,6,'1980-12-08',100)
insert into penalties values(2,44,'1981-05-05',75)

7. 查询表
select teamNo,playerNo
from teams
where division = 'first'
order by teamNo

select 2*3 --2的3倍是多少

8. 更新和删除行
update penalties
set amount = 200
where playerNo = 44

delete
from penalties 
where amount < 50

9.使用索引优化查询
create index penalties_amount on penalties(amount) --为amount定义名为penalties_amount的索引

10. 视图：用户可见的表，但并没有占据任何存储空间）
create view number_sets(matchNo,difference) as
select matchNo,abs(won-lost)
from matches

select * 
from number_sets

11. 用户和数据安全性
grant select
on matches
to xlh

grant select,update
on penalties
to xlh

12. 删除数据库对象
drop database tennis
drop table matches
drop view number_sets
drop index penalties_amount

13. 系统变量
select @@version --在系统变量前指定两个@符号就会返回它的值

--很多系统变量（如version和系统日期）是不能改变的，但有些（如SQL_MODE）是可以改变的，使用set语句
--新的值仅适用于当前会话，不适用于所有其他会话
set @@SQL_MODE = 'PIPES_AS_CONCAT' --SQL_MODE可能值：REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE
set @@global.sql_warnings = true --是否应该返回一条警告，默认该变量是关闭的
set @@session.sql_select_limit = 10 --select语句的结果中最大行数，默认为4294967295
set @@session.sql_select_mode = default

14. SQL语句分组
数据定义语言DDL：create table;drop table等
数据操作语言DML：select;update;delete;insert等
数据控制语言DCL：grant;revoke等
过程式语句（procedural statement）：if-then-else;while-do等

15. 获得在penalties表上定义的索引的名字
use information_schema
select distinct index_name
from statistics
where table_name = 'penalties'

16. 显示存储在information_schema数据库中的表的名字
select table_name
from tables
where table_chema = 'information_schema'
order by table_name

17. show语句
show columns from teams --获得teams表的列的描述性数据
show index from penalties --获得penalties上定义的索引的描述性数据
show databases
show tables
show create table teams
show grants for xlh@localhost
show privileges

show global variables --返回所有全局系统变量
show session variables --返回所有会话系统变量

18. 获取错误和警告
show warnings --查询所有错误、警告和提示信息
show errors --查询错误
show count(*) warnings --查询错误消息的数目

19. 日期时间
create table timestamp_table (column1 timestamp)

insert into timestamp_table values ('1980-12-08 23:59:59:59.59')

select column1
from timestamp_table -- 返回值微妙会漏掉

select *
from penalties
where payment_date = current_date --显示当天支付的所有罚款

20.为结果列分配名字
--新列名不能用在同一条select语句中，如：select won as w, w*2是不允许的
select paymentNo, amount*100 as cents --将amount*100命名为cents
from penalties 

select matchNo as primkey, 80 as eighty, won-lost as differnce, time('23:59:59') as almost_midnight
from matches
where matchNo <= 4

21. case表达式
select playerNo,joined
       case 
          when joined < 1980 then 'Seniors'
          when joined <1983 then 'Juniors'
          else 'Children end as age_Group
from players
order by joined

22. 二进制表示
select conv(6,10,2), --将6从十进制转换为二进制
       conv(10,10,2),
       bin(6), --6的二进制
       bin(10)

select conv(1001,2,10), --将9从二进制转换为十进制
       conv(111,2,10)

23. 复合字符表达式
--MySQL数据库服务器标准启动时，||运算符不会用来连接字符值，而被看做or运算符
--通过改变sql_mode值来改变这一点：
set @@sql_mode = 'pipes_as_concat'

select playerNo,town||' '||street||' '||houseNo
from players
where town = 'Stratford'

24. 复合时间表达式
create table matches_special
(matchNo integer not null,
matchNo integer not null,
matchNo integer not null,
won smallint not null,
lost smallint not null,
start_date date not null,
start_time time not null,
end_time time not null,
primary key (matchNo))

insert into matches_special values(1,1,6,3,1,'2004-10-25','14:10:12','16:50:09')
insert into matches_special values(2,1,44,3,2,'2004-10-25','17:00:00','17:55:49')

select matchNo,end_time --获取至少在午夜前6.5小时结束的比赛
from matches_special
where addtime(end_time,'06:30:00') <= '24:00:00')

25. 复合布尔表达式
select paymentNo, case paymentNo >4
                      when 1 then 'Greater than 4' else 'Less than 5' end as Greater_Less
from penalties

26. 表表达式
insert into penalties values(1,6,'1980-12-08',100) --一条insert语句添加多条记录
                            (2,44,'1981-05-06',75)
                            (3,27,'1983-09-10',100)