mysql 命令记录

参考书：《mysql开发者 sql权威指南》

# 创建用户和设置密码

create user 'booksql'@'localhost' identified by 'booksql';

create user 'redhat'@'%' identified by 'redhat';

# 授权 所有权限

grant all privileges on *.* to 'redhat'@'%' with grant option;

grant all privileges on *.* to 'booksql'@'localhost' with grant option;

grant select,update on number_sets to booksql@'localhost';

show grants for  booksql@'localhost';

# 产看用户权限

show grants；

show grants for redhat;

show grants for booksql@localhost;

# 简单命令

show databases;
use mysql;
show tables;
select * from user;
create database tennis;
use tennis;
show tables;

# 创建数据库

create table if not exists players(
    playerno    integer     not null,
    name        char(15)    not null,
    initials    char(3)     not null,
    birth_date  date                ,
    ***         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)
);

drop table if exists team;

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

alter table team rename as teams;

create table if not exists 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 if not exists 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 if not exists committee_members(
    playerno    integer     not null,
    begin_date  date        not null,
    end_date    date                ,
    position    char(20)            ,
    primary key (playerno, begin_date)
);

insert into players values(
    6,'Parmenter','R','1964-06-25','M',1977,
    'Haseltine Line','80','1234KK','Stratford',
    '070-476537','8467'
);

insert into players values(
    7,'Wise','GWS','1963-05-11','M',1981,
    'Edgecombe Way','39','9758VB','Stratford',
    '070-346837',Null
);

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

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'
);
use tennis;

show tables;
select * from committee_members;
select * from matches;
select * from penalties;
select * from players;
select * from teams;


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

select playerno
from players
where joined > 1970
order by playerno;

select *
from penalties
order by paymentno,amount,playerno;

insert into penalties values(
    2,44,'1981-05-05',166
);

update penalties
set amount = 77
where playerno = 44 and paymentno = 2;

select *
from penalties
where playerno = 44
order by paymentno,amount;

update penalties
set amount = 110
#where playerno = 44; 执行不成功，需要关闭安全选项
# Edit --> Preferences --> SQL Edit ( Safe updates（选项）) --> reconnect
where playerno = 44 and paymentno = 2;

delete
from penalties
where amount > 100;

# 比较索引，通过查看时间；如果数据内容很多，可以看出效率

select * from penalties where amount > 100;

create index penalties_amount on penalties(amount);

select * from penalties where amount > 100;

# 创建视图，并没有包含真正的数据，不占用空间；

select * from matches;

select matchno,abs(won - lost) from matches;

create view number_sets(matchno,difference) as
select matchno,abs( won - lost )
from matches;

select * from number_sets;

# 删除database、table、view、index

drop database db_name；

drop table tb_name；

drop view view_name；

drop index index_name；

# 系统变量

select @@version;
'5.5.13'

select @@sql_mode;
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# 设置系统变量 set
# set @@sql_mode = 'pipes_as_concat';
# 使用一条常规的set语句会覆盖掉所有的设置；
# 如果想要添加设置，可以使用如下语句：

set @@sql_mode = concat(@@sql_mode,
                    CASE @@sql_mode WHEN '"' then '"' ELSE ',' end,
                    'no_zero_in_date'
                    );

# MySQL中concat函数
# http://axislover.blog.163.com/blog/static/10776515200891551236219/
# 使用方法：
# CONCAT(str1,str2,…)  
# 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
# 注意：
# 如果所有参数均为非二进制字符串，则结果为非二进制字符串。
# 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
# 一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如：
# SELECT CONCAT(CAST(int_col AS CHAR), char_col)
# 举例：

select concat('11','22','33');
112233

select concat('11','22','null');
1122null

select concat('11','22',null);
null

# case when 使用方法举例

SELECT CASE
WHEN 10*2=30 THEN '30 correct'
WHEN 10*2=40 THEN '40 correct'
ELSE 'Should be 10*2=20'
END;

SELECT CASE 10*2
WHEN 20 THEN '20 correct'
WHEN 30 THEN '30 correct'
WHEN 40 THEN '40 correct'
END;

select case @@sql_mode
when '"' then '"'
when '\'' then '.

# 查看表结构

show columns from players;

desc players;

# 查看索引

show index from penalties;