MySQL学习笔记1

cmd命令行：    
    net start/stop mysql  #启动/停止MySQL服务(windows)
    mysql -u root -p root -P3306 -h 127.0.0.1
      127.0.0.1  #本地回环地址
    mysql -u root -p root --prompt \h  #提示符变为服务器名称
      \h  服务器名称
      \d  数据库名
      \u  用户名
      \D  完整日期

1. 创建新用户（用户名：xlh，密码：xlh123456）
create user 'xlh' @ 'localhost' identified by 'xlh123456'
select version();  --服务器版本
select user();  --用户名

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

3. 创建名为tennis的数据库
create database tennis
create database if not exists db1;  --若db1存在则给出警告
show warnings;
show create database db1;  --按照默认编码方式utf-8
create database db1 character set gbk;

alter database db1 character set = utf-8;
drop database if exists db1;  --删除表

4. 指定tennis为当前数据库，即使用tennis数据库
use tennis
select database();  --当前使用的数据库名

5. 创建和删除表
create table teams  --若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))

show columns from penalties; 
create table tb2(id smallint unsigned auto_increment primary key,   --主键自动为not null
                           username varchar(20) not null); 
create table tb3(id smallint unsigned auto_increment primary key,   --自动编号
                          username varchar(20) unique key,  ----唯一约束可以为null，也可以多个字段都有唯一约束
                          sex enum('1','2','3') default '3');  --默认值
create table tb4(id smallint unsigned auto_increment primary key,   
                          username varchar(20) unique key,
                          pid smallint,
                          foreign key(pid) references tb3(id),  --tb4成为子表，tb3成为父表
                         );  
show indexes from tb4;  --显示索引（外键列自动创建索引）
外键约束的参照操作：cascade： 从父表删除或更新时，自动删除或更新子表中匹配的行
                                    set null：从父表删除或更新时，设置子表中的外键列为NULL（保证子表列没有指定not null）
                                    restrict：拒绝对父表的删除或更新操作
                                    no action：与restrict相同
create table tb4(id smallint unsigned auto_increment primary key,   
                          username varchar(20) unique key,
                          pid smallint unsigned,
                          foreign key(pid) references tb3(id) on delete cascade);
show create table tb4;

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)

数据类型：int(tinyint,smallint,mediumint,int,bigint),
                  float[(M,D)],double[(M,D)]  --M是数字总位数，D是小数点后面的位数
                  year,time,date,datetime,timestamp      
                  char,varchar(M),tinytext,text,mediumtext,longtext,enum('value1','value2',...),set('value1','value2',...)

insert tb3(username) values('Tom');
insert tb3(username) values('Bob');
insert tb4(username,pid) values('Alice',2);
select * from tb4;
delete from tb3 where id=2;
select * from tb4;

alter table tb3 add score smallint unsigned not null;  --在最后一列插入
alter table tb3 add age smallint unsigned not null after sex;  --在sex之后插入
alter table tb3 add nickname varchar(10) not null first;  --在首列插入

alter table tb3 drop nickname,drop score;  --删除列
create table tb5(id smallint unsigned not null, 
                          username varchar(20) not null, 
                          pid smallint unsigned,
                          sex enum('1','2','3'));
alter table tb5 add constraint PK1 primary key(id);  --添加主键约束
alter table tb5 add unique key(username);  --添加唯一性约束
alter table tb5 add foreign key(pid) references tb3(id);  --添加外键约束
show columns from tb5; 
alter table tb5 alter sex set default '3';  --添加默认值约束     

alter table tb5 drop primary key;  --删除主键，因为主键唯一，所以无需指定列
alter table tb5 drop index username;  --删除唯一性约束
show indexes from tb5; 
alter table tb5 drop foreign key pid;  --删除外键约束

alter table tb5 modify username  varchar(30) first;  --改变username类型，同时将其位于列的最前面      
alter table tb5 change sex gender;  --更改列名sex为gender

alter table tb5 rename tb56;  --更改表名
rename table tb5 to tb55;

insert tb55 set username='Ben',id=1,gender='1'; 
insert tb55 set username=(select username from tb3 where id=1),id=2,gender='2'; 

update tb55 set gender = '3';  --所有的gender都变为3
update tb55 set gender = '2' where username='Tom';

delete from tb55 where id=1;  --删除id=1的记录
select now();  --当前系统时间      
select username,gender from tb55 group by 1 desc;  --按照第一列group by
select username,gender from tb55 group by username having gender = '2';
select * from tb55 group by username,gender asc;  --按照username和gender来group by，并且对于username相同的按照gender升序
select * from tb55 limit 3,2;  --从第三条开始返回两条记录（MySQL下标从1开始）
select * from tb55 order by username; 

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)
27. 字符函数：
         concat(A,B)：连接字符A和B
         concat_ws('|',A,B,C)：使用指定的分隔符'|'连接字符A,B,C
         format()：数字格式化
         lower()：转为小写
         upper()：转为大写
         left(A,2)：获取字符A的前2个字符
         right()：获取右侧字符
         length()：获取字符长度
         ltrim()：去掉前导空格
         rtrim()：去掉后续空格
         trim()
         substring(A,1,3)：截取字符A，起始位置为1，长度为3
         [not]like    --%表示任意字符，_表示任意一个字符
         replace()
         数值运算符与函数：
         ceil()
         div：整数除法，如：3 div 4得0
         floor()
         mod：取余数，等价于运算符%，如：4 mod 5得4
         power()：幂运算
         round()
         truncate(128.89,0)：截取得到整数128
         比较运算符与函数：
         [not]between...and...
         [not]in()
         is[not] null
         日期时间函数：
         now()：当前日期和时间
         curdate()：当前日期
         curtime()：当前时间
         date_add()：日期变化
         datediff()：日期差值
         date_format()：日期格式化
         信息函数：
         connection_id()：连接ID
         database()：当前数据库
         last_insert_id()：最后插入记录
         user()：当前用户
         version()：版本信息
         聚合函数：
         avg()
         count()
         max()
         min()
         sum()
         加密函数：
         md5()：信息摘要算法
         password():密码算法

         自定义函数：
         create function nowChinese() returns varchar(30)
         return date_format(now(),'%Y年%m月%d日 %H点%i分%s秒');
         select nowChinese();

         create function f1(num1 smallint unsigned,num2 smallint unsigned) returns float(10,2) unsigned
         return (num1+num2)/2
         
         drop function f1;

28. 创建存储过程
        create procedure sp1() select * from tb55;
        call sp1()  --调用存储过程
        
        delimiter //  --将结束符;换成//
        create procedure sp2(in p_id int unsigned)  --in类型的存储过程
        begin 
        delete from tb55 where  id = p_id;
        end //
        delimiter ;
        call sp2(1);
        drop procedure sp2;
        
        delimiter //  --将结束符;换成//
        create procedure sp3(in p_id int unsigned,out userNums int unsigned)
        begin 
        delete from tb55 where  id = p_id;
        select count(id) from tb55 into userNums;
        end //
        delimiter ;
        call sp3(2,@nums);
        select @nums;

        drop procedure sp2;

29. 并发控制：当多个连接记录进行修改时保证数据的一致性和完整性
        共享锁（读锁）：同一时段内，多个用户可读取同一资源，读取过程中数据不会发生任何变化
        排他锁（写锁）：任何时候只有一个用户写入资源，当进行写锁时阻塞其他的读锁或写锁操作        
        
        锁颗粒：
           表锁：开销最小的锁策略
           行锁：开销最大的锁策略     
      事务处理：事务用于保证数据库的完整性
      事务的特性：原子性、一致性、隔离性、持久性
      外键：保证数据一致性的策略
      索引：对数据表中一列或多列的值进行排序的一种结构

30. 存储引擎特点：
         MyISAM：最大存储限制为256TB、表锁、支持索引、支持数据压缩
         InnoDB：最大存储限制为64TB、支持事务安全、支持索引、行锁、支持外键
         Memory：支持索引、表锁
         Archive：行锁、支持数据压缩