剑指数据仓库-SQL01

一、上次课程回顾

二、二、MySQL语法一（创建database && user && show processlist && kill进程）

• 2.1、MySQL字段类型（数值类型 && 字符串类型 && 日期类型）
• 2.2、MySQL中的SQL类型 && 创建表 && 设置Unique_key
• 2.3、建表规范
• 2.4、MySQL中的字符集
• 2.5、数据的增删改查 && 注意事项

三、本次课程面试题&&作业

一、上次课程回顾

• https://blog.youkuaiyun.com/SparkOnYarn/article/details/104876632
• 软连接（软件升级、日志迁移），日志迁移场景，磁盘爆了，把50G硬盘的数据移动到2T硬盘中去，ln -s进行软连接。

二、MySQL语法一（创建database && user && show processlist && kill进程）

1. 创建Database && User：

    create database ruozedata;
    grant all privileges on *.* to 'root'@'%' identified by '960210';
    flush privileges;
   

• 大数据中对于我们而言，用到表是最多的，其余视图、索引、存储过程其它开发人员用的比较多。
  

2. MySQL中杀进程，使用终端登录

    1、命令行不能带有密码，否则history查看会有泄露
    hadoop:mysqladmin:/usr/local/mysql:>mysql -u root -p960210
    mysql>
    hadoop:mysqladmin:/usr/local/mysql:>mysql -u root -p 960210
    Enter password: 
   
    2、show processlist;		查看正在运行的进程：
    mysql> show processlist;
    +----+------+--------------------+-----------+---------+------+----------+------------------+
    | Id | User | Host               | db        | Command | Time | State    | Info             |
    +----+------+--------------------+-----------+---------+------+----------+------------------+
    |  9 | root | 122.96.50.84:35845 | ruozedata | Sleep   |  716 |          | NULL             |
    | 10 | root | 122.96.50.84:36362 | NULL      | Sleep   |  692 |          | NULL             |
    | 14 | root | localhost          | NULL      | Query   |    0 | starting | show processlist |
    +----+------+--------------------+-----------+---------+------+----------+------------------+
    3 rows in set (0.00 sec)
   
    3、因为此时我们正在使用海狸连接，我们断开dbeaver连接，再次查看进程发现已经少了两个进程了。
    mysql> show processlist;
    +----+------+-----------+------+---------+------+----------+------------------+
    | Id | User | Host      | db   | Command | Time | State    | Info             |
    +----+------+-----------+------+---------+------+----------+------------------+
    | 14 | root | localhost | NULL | Query   |    0 | starting | show processlist |
    +----+------+-----------+------+---------+------+----------+------------------+
    1 row in set (0.00 sec)
   
    4、kill id杀掉进程
    mysql> kill 15;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> kill 16;
    Query OK, 0 rows affected (0.00 sec)
   
    5、注意：看时间，找到哪个消耗时间长的，有可能导致mysql的服务夯住，别导致锁死 --》 这个SQL的目的一定要搞清楚，到底能不能被kill掉。
   

2.1、MySQL字段类型

1、数值类型：

类型	释义
int	整数型
long	长整型
double	双精度
float	单精度
decimal	小数值（与金额挂钩）

2、字符串类型：

类型	释义
char	定长，0-255个字节，比如一个英文字母a，它占存储会自动补全255字节，浪费存储空间
varchar	变长，字符串，存储0-65535字节，比如一个英文字母b，它本身多大就占多少存储空间

3、日期类型：

类型	释义
date	日期类型，YYYY-MM-DD
time	时间 HH：MM：SS
datetime	年月日时分秒，YYYY-MM-DD HH:MM:SS
timestamp	年月日时分秒，YYYY-MM-DD HH:MM:SS

作业：datetime和timestamp的区别？

2.2、MySQL中的SQL类型&创建数据表

DDL：create drop 数据定义语言
DML：insert update delete select 增删改查，数据操作语言
DCL：grant 数据控制语言

1、规范的创建rzdata数据表，关键最后5行
create table rzdata(
id int(10) not null auto_increment,			//id不为空自增长
name varchar(30),
age int(3),
createuser varchar(200),							
createtime timestamp not null default current_timestamp,					//创建时间不为空且默认为当前时间
updateuser varchar(200),
updatetime timestamp not null default current_timestamp on update current_timestamp,			//修改时间也不为空，当数据那一刹那发生改变就会留下一个记录时间。
primary key (id)		//id做主键
);

2、数据准备：
insert into ruozedata.rzdata(name,age,createuser,updateuser) values ('john',24,'sail','sail')

3、测试对这条数据进行更新：
update ruozedata.rzdata set age=18 where name='john';

• 修改了john的年纪为18，就会有一条记录：
  

2、设置name的唯一约束，使得不能插入同名的数据：
图形化界面操作：

• 一层层的打开到表，点击约束，右键新建约束，选择name字段，设置为unique_key，点击确定，下角有一个保存，需要点击执行。
  

insert into ruozedata.rzdata(name,age,createuser,updateuser) values ('john',24,'sail','sail');

insert into ruozedata.rzdata(name,age,createuser,updateuser) values ('john',25,'fox','fox');
出现错误提示：
SQL 错误 [1062] [23000]: Duplicate entry 'john' for key 'rzdata_un'

//下次遇见这个错误就应该能够明白：是设置了唯一值，所以值不能被重复。

• J总公司每一条数据都有createuser和updateuser和upadatetime，

2.3、MySQL建表规范

a. 表名称、字段名称 不能是中文、不能是汉语拼音；需要统一风格
b. 统一风格：比如你建表的时候要去看已经存在的表的风格是什么样的：以一个字段创建者为例子，有很多种写法：create_user、cuser、createuser；不确定的话可以拿去个leader check。
到时候来新项目的风格就是by you.
举例：以创建时间为例：ctime、createtime、cretime、cre_time、create_time；
c. MySQL表的第一个字段是id自增长的，设置为主键，它是没有意义的；为了mysql能够进行高性能的查询。
d. 一张表只能有一个主键：primary key ==> id，主键字段必须唯一，unique + not null
e.后四个字段都需要增加：createuser、createtime、updateuser、updatetime；这条记录无论是做新增、还是做修改，直观明了了。
f. 双击表下的“列”，然后拉到最后，会有注释栏目，把注释加上，直观明了的知道每个字段的含义，最后点击保存。

面试题：自增长主键和UUID方式的对比

2.4、MySQL字符集

1、展示创建数据库的建表语句，创建数据库的时候并没有设置字符集，默认设定为了latin字符集：

• show create database ruozedata;
• CREATE DATABASE ruozedata /*!40100 DEFAULT CHARACTER SET latin1 */

//数据库database有字符集，表table有字符集，列column也有字符集。

2、服务端也有字符集：

• show variables like ‘%char%’;

    character_set_client	utf8
    character_set_connection	utf8
    character_set_database	latin1
    character_set_filesystem	binary
    character_set_results	
    character_set_server	latin1
    character_set_system	utf8
    character_sets_dir	/usr/local/mysql-5.7.11-linux-glibc2.5-x86_64/share/charsets/
  

2.5、MySQL的增删改查

insert into ruoedata.rzdata(name,age,createuser,updateuser) values (‘john’,24,‘root’,‘root’);

select * from ruozedata.rzdata;

update ruozedata.rzdata set age = 19 where name = ‘john’;

delete from ruozedata.rzdata where name = ‘john’;

注意：切记在生产上不要使用*，写一个limit 10；

select * from rzdata; //切记生产写sql不要加*
select id,name,age from rzdata;

• 财务系统，delete from rzdata没有加上where条件，就会导致所有的数据都会被删除；通过binlog文件进行恢复（arch/mysql-bin）。

未来在工作中无论是update还是delete切忌加上where条件，1条还是多条数据，createtime >=‘2019-01-01 00:00:00’

举例：数据表有一亿数据，select * 会导致不能对外提供服务，select id,name,age from rzdata limit 3; 查看样本数据就使用limit 10

生产上关于MySQL是如何做的：

• MySQL的主从复制，生产上的主库尽量都做写的操作，从库进行写的操作。
• 业务ERP APP --> A服务器上的MySQL：写 --> 数据会自动的同步到B服务器上的MySQL： 读 ；这就叫读写分离。
• 确认清楚select和delete的where使用情况。

三、本次课程面试题&&作业

面试题

1、datetime和timestamp的区别？

2、加入id自增长主键超额了，该怎么办？

3、ENGINE=InnoDB 是什么？和MyISAM的区别是什么

作业

1、创建MySQL表规范

2、整理DDL、DML语句，依次执行

3、整理三句话，修改密码、赋予权限、刷新权限