style="color: #000000"> style="color: #008000;" />

澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

分桶表的详解和创建实例,Postgresql生成大量测试

2019-10-21 作者:数据库网络   |   浏览(97)

MySQL的常规知识

MySQL的常规知识

在PostgreSQL中如何用简单的几条SQL语句生成大量的测试数据呢?

style="font-family: "Microsoft YaHei"; font-size: 18px; color: #0000ff"> style="color: #000000"> style="color: #008000; font-size: 14pt">阅读目录: style="color: #ff6600">MySQL的字符集和校对规则

  • style="font-family: "Microsoft YaHei"; font-size: 18px; color: #0000ff"> style="color: #000000">MySQL的字符集
    • style="font-family: "Microsoft YaHei"; font-size: 18px">MySQL与字符集
    • style="font-family: "Microsoft YaHei"; font-size: 18px">正确使用字符集
    • style="font-family: "Microsoft YaHei"; font-size: 18px">MySQL客户端与字符集
    • style="font-size: 18px; font-family: "Microsoft YaHei"; color: #ff0000">字符集编码转换原理
    • style="font-size: 18px; font-family: "Microsoft YaHei"; color: #ff0000">字符集常见处理操作
    • style="font-size: 18px; font-family: "Microsoft YaHei"; color: #ff0000">字符集的正确实践
  • style="font-size: 18px; font-family: "Microsoft YaHei"; color: #993300">MySQL的校对规则

我们学习一下分桶表,其实分区和分桶这两个概念对于初学者来说是比较难理解的。但对于理解了的人来说,发现又是如此简单。

标准的SQL语句通常可分为如下的几种类型:

show

 

一、字符集(Character set)

 

1,DCL(Database Control Language) :数据控制语言,主要由grant和revoke关键字组成。

style="font-family: SimSun; font-size: 24px; color: #3333ff; background-color: #ffffff;">查看数据库

show databases;

style="font-family: SimSun; font-size: 24px; color: #3366ff;">查看表

style="font-family: SimSun; font-size: 24px; color: #3366ff; background-color: #ff0000;"> style="font-family: SimSun; font-size: 18px; color: #ffff00;">查看编码格式

style="font-family: SimSun; font-size: 24px; color: #000000;"> style="font-family: SimSun; font-size: 18px;"> style="font-family: SimSun; font-size: 18px;"> style="color: #555555; font-family: 'microsoft yahei'; font-size: 15px;">show variables like 'character%';

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">存在的所有表

show tables;

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建表的命令

show create table table_name;

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">表的结构

show index from table_name;

style="font-family: SimSun; font-size: 18px;">图片 1

show columns from table_name;

style="font-family: SimSun; font-size: 18px;">图片 2

> style="color: red;">注: > > > style="color: red;">“show columns from table_name; > > > style="color: red;">” > > > style="color: red;">和“ > > > style="color: red;">desctable_name;” > > > style="color: red;">效果是一样的。

show table status like "table_name"G

style="font-family: SimSun; font-size: 18px;">图片 3

> style="color: red;">注: > > > style="color: red;">”G” > > > style="color: red;">是为了让数据显示更清晰易懂,也可以不设置。

style="font-family: SimSun; font-size: 24px; color: #3333ff;">查看服务器

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">服务器状态

show status;

style="font-family: SimSun; font-size: 18px; background-color: #ff0000;"> > style="color: #ffcc00;">服务器配置变量

show variables;

此处,我简单的写一个例子,经过测试的:

  是多个字符(英文字符,汉字字符,或者其他国家语言字符)的集合,字符集种类较多,每个字符集包含的字符个数不同。

我们先建立一个分桶表,并尝试直接上传一个数据

2.DDL(Database Define Language):数据定义语言,主要由Create、Drop、Alter和Truncate关键字组成。

use

(1)准备知识

特点:

create table student4(sno int,sname string,sex string,sage int, sdept string) clustered by(sno) into 3 buckets row format delimited fields terminated by ',';
set hive.enforce.bucketing = true;强制分桶。
load data local inpath '/home/hadoop/hivedata/students.txt' overwrite into table student4;

3,DML(Database Manipulate Language):数据控制语法,主要由insert、update和delete关键字组成。

style="font-family: SimSun; font-size: 24px; color: #3366ff;">使用数据库

use database_name;

 

  ①字符编码方式是用一个或多个字节表示字符集中的一个字符

 图片 4

4,DQL(Database Query Language):数据查询语言,主要由select关键字组成

create

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">针对 Postgres生成数据方法

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"><1>生成序列====》           style="background-color: #ffff00; color: #ff0000;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;">SELECT * FROM generate_series(1,5);

style="color: #454545; font-family: tahoma,helvetica,arial; font-size: 14px; line-height: 21px; white-space: pre;"><2>生成date style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">====》    style="background-color: #ffff00; color: #ff0000;">SELECT date(generate_series(now(), now() '1 week', '1 day'));

 

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="white-space: pre;"> style="color: #454545; font-family: tahoma,helvetica,arial; line-height: 21px;"><3>生成integer 随机数=》 style="background-color: #ffff00; color: #ff0000;">SELECT (random()*(2*10^9))::integer;

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="white-space: pre;"> style="color: #454545; font-family: tahoma,helvetica,arial; line-height: 21px;"> style="background-color: #ffff00; color: #ff0000;"> style="background-color: #ffffff; color: #000000;"><4> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;">生成numeric 随机数=》select (random()*100)::numeric(4,2);

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="white-space: pre;"> style="color: #454545; font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="white-space: pre;"> style="color: #454545; font-family: tahoma,helvetica,arial; line-height: 21px;"><5>生成字符串==》    select substr('abcdefghijklmnopqrstuvwxyz',1,(random()*26)::integer);

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px; background-color: #ffffff; color: #000000;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"><6>生成重复串==>    select repeat('1',(random()*40)::integer);

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px; background-color: #ffffff; color: #000000;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;">举例:

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px; background-color: #ffffff; color: #000000;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="background-color: #ffff00; color: #ff0000;">SELECT generate_series(1,10) as key,(random()*100.)::numeric(4,2),repeat('1',(random()*25)::integer) ORDER BY random();

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px; background-color: #ffffff; color: #000000;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> key | numeric |          repeat          
----- --------- --------------------------
   8 |   26.04 | 111
  10 |   83.44 | 1
   9 |   46.72 |
   3 |   57.84 | 1111111111111
   4 |   29.61 | 1111111111111111111
   5 |   11.32 | 1111111111111
   7 |   69.69 |
   2 |   42.23 | 11111111111111111
   6 |   12.32 | 111111111111111111111111
   1 |   84.92 | 111111

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px; background-color: #ffffff; color: #000000;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;">二、

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">如果您想知道执行该sql的时间,请在执行上述命令前设置:
style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">postgres=# timing on
style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">Timing is on.

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">(1)测试参考SQL,可以把生成的随机值改的大一些;

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px; background-color: #ffffff; color: #000000;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="white-space: pre;"> style="font-family: tahoma,helvetica,arial; line-height: 21px;"># style="background-color: #ff9900; color: #ff0000;">生成新表===>

select i,'text:'||i as text into test from generate_series(1,10) as i;

style="background-color: #ff9900; color: #ff0000;">#在新表中插入测试数据===>

insert into test(i,text) select i,'text:'||i from generate_series(1,10) as i;

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">(2)查看表test占用的存储空间

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">图片 5

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">若查看其中的index的空间或整个relation的空间,请参考:

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">          或:

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="color: #000000;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">(3) style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">查看整个数据库占用的硬盘空间:**

 

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">图片 6

 

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">(4) style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">如果想用delete清空该表,然后真正清空硬盘空间

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.200000762939453px;">图片 7

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> 从上面的步骤可以看出 style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;">,vacuumdb后空间回收数据又减少到最初的6.9M的空间了。

style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;"> style="font-family: Tahoma,Helvetica,SimSun,sans-serif,Hei; font-size: 14px; line-height: 25.2px;">参考自:

  ②每种字符集都有自己特有的编码方式,因此同一个字符,在不同字符集的编码方式下,会产生不同的二进制

我们看到虽然设置了强制分桶,但实际student表下面只有一个students一个文件。分桶也就是分区,分区数量等于文件数,所以上面方法并没有分桶。

5,TCL(Tranlasion Control Language):事物控制语言,主要由commit、rollback和savePoint关键字组成。

style="font-family: SimSun; font-size: 24px; color: #3366ff;">创建数据库

create databasedatabase_name;

style="color: #3366ff; font-family: SimSun; font-size: 24px;">创建表

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建非临时表

create table student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

> style="font-family: SimSun; font-size: 18px; color: #cc0000;">注: > > > style="color: red;">1 > > > > style="color: red;">, > > > > style="color: red;">auto_increment > > > > style="color: red;">属性的对象只能是整形,在进行值插入时若不指定具体的值,或指定其为 > > > > style="color: red;">null > > > > style="color: red;">或 > > > > style="color: red;">0 > > > > style="color: red;">,则其自动赋值为当前列的最大值再加上 > > > > style="color: red;">1 > > > > style="color: red;">的值。 > > > style="color: red;">2 > > > > style="color: red;">, > > > > style="color: red;">primary key() > > > > style="color: red;">是设置主键 > > > > style="color: red;">, > > > > style="color: red;">一个表可以不止一个主键。

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建临时表

create temporary table Student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

> style="color: red;">注:创建临时表要用关键字 > > > style="color: red;">”temporary” > > > style="color: red;">,临时表主要是用于保存一些临时数据,临时表的数据只是在当前连接可用,当断开链接后就会, > > > style="color: red;">MySQL > > > style="color: red;">就会自动删除临时表。

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建复制旧表的数据和结构的新表

create table new_table select * from old_table;

> style="color: red;">注:这种方法会将旧表中的所有内容都拷贝过来,当然我们也可以用 > > > style="color: red;">delete > > > style="color: red;">、insert into > > > style="color: red;">来删除、增加。不过这种方法最不好的地方就是没有了旧表的 > > > style="color: red;">primary key > > > style="color: red;">, > > > style="color: red;">extra(auto_incremet) > > > style="color: red;">等属性。需要自己用 > > > style="color: red;">alter > > > style="color: red;">手动添加。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">创建复制旧表的结构的新表

create table new_table select * from old_table where 1 = 2;

> style="color: red;">注:这种方法只是复制表结构,当然旧表中的 > > > style="color: red;">primary key > > > style="color: red;">  > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">等属性都是复制过来了。

 

常见字符集:

 

show

desc

 

  ASCII字符集:基于罗马字母表的一套字符集,它采用1个字节的低7位表示字符,高位始终为0。

现在,我们用插入的方法给另外一个分桶表传入同样数据

create table student4(sno int,sname string,sex string,sage int, sdept string) clustered by(sno) into 3 buckets row format delimited fields terminated by ',';
set hive.enforce.bucketing = true;强制分桶。
load data local inpath '/home/hadoop/hivedata/students.txt' overwrite into table student4;
我们看到虽然设置了强制分桶,但实际STUDENT表下面只有一个STUDENTS一个文件。
分桶也就是分区,分区数量等于文件数,所以上面方法并没有分桶。
#创建第2个分桶表
create table stu_buck(sno int,sname string,sex string,sage int,sdept string)
clustered by(sno) 
sorted by(sno DESC)
into 4 buckets
row format delimited
fields terminated by ',';

#设置变量,设置分桶为true, 设置reduce数量是分桶的数量个数
set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;
#开会往创建的分通表插入数据(插入数据需要是已分桶, 且排序的)
#可以使用distribute by(sno) sort by(sno asc)   或是排序和分桶的字段相同的时候使用Cluster by(字段)
#注意使用cluster by  就等同于分桶 排序(sort)
insert into table stu_buck
select sno,sname,sex,sage,sdept from student distribute by(sno) sort by(sno asc);

Query ID = root_20171109145012_7088af00-9356-46e6-a988-f1fc5f6d2e13
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 4
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1510197346181_0014, Tracking URL = http://server71:8088/proxy/application_1510197346181_0014/
Kill Command = /usr/local/hadoop/bin/hadoop job  -kill job_1510197346181_0014
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 4
2017-11-09 14:50:59,642 Stage-1 map = 0%,  reduce = 0%
2017-11-09 14:51:38,682 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.04 sec
2017-11-09 14:52:31,935 Stage-1 map = 100%,  reduce = 50%, Cumulative CPU 7.91 sec
2017-11-09 14:52:33,467 Stage-1 map = 100%,  reduce = 67%, Cumulative CPU 15.51 sec
2017-11-09 14:52:39,420 Stage-1 map = 100%,  reduce = 83%, Cumulative CPU 22.5 sec
2017-11-09 14:52:40,953 Stage-1 map = 100%,  reduce = 92%, Cumulative CPU 25.86 sec
2017-11-09 14:52:42,243 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 28.01 sec
MapReduce Total cumulative CPU time: 28 seconds 10 msec
Ended Job = job_1510197346181_0014
Loading data to table default.stu_buck
Table default.stu_buck stats: [numFiles=4, numRows=22, totalSize=527, rawDataSize=505]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 4   Cumulative CPU: 28.01 sec   HDFS Read: 18642 HDFS Write: 819 SUCCESS
Total MapReduce CPU Time Spent: 28 seconds 10 msec
OK
Time taken: 153.794 seconds 

图片 8

我们设置reduce的数量为4,学过mapreduce的人应该知道reduce数等于分区数,也等于处理的文件数量。


style="font-family: 仿宋; font-size: 18px; color: #0000ff">把表或分区划分成bucket有两个理由

style="font-family: 仿宋; font-size: 18px; color: #0000ff">1,更快,桶为表加上额外结构,链接相同列划分了桶的表,可以使用map-side join更加高效。

style="font-family: 仿宋; font-size: 18px; color: #0000ff">2,取样sampling更高效。没有分区的话需要扫描整个数据集。

style="font-family: 仿宋; font-size: 18px; color: #0000ff"> 

style="font-family: 仿宋; font-size: 18px; color: #0000ff">hive> create table bucketed_user (id int,name string)

> clustered by (id) sorted by (id asc) into 4 buckets;

style="font-family: 仿宋; font-size: 18px; color: #0000ff">重点1:CLUSTERED BY来指定划分桶所用列和划分桶的个数。HIVE对key的hash值除bucket个数取余数,保证数据均匀随机分布在所有bucket里。

style="font-family: 仿宋; font-size: 18px; color: #0000ff">重点2:SORTED BY对桶中的一个或多个列另外排序

style="font-family: 仿宋; font-size: 18px; color: #0000ff"> 

style="font-family: 仿宋; font-size: 18px; color: #0000ff"> 

style="font-family: 仿宋; font-size: 18px; color: #0000ff">总结:我们发现其实桶的概念就是MapReduce的分区的概念,两者完全相同。 style="color: #ff0000">物理上每个桶就是目录里的一个文件,一个作业产生的桶(输出文件)数量和reduce任务个数相同。

style="font-family: 仿宋; font-size: 18px; color: #0000ff">而分区表的概念,则是新的概念。 style="color: #ff0000">分区代表了数据的仓库,也就是文件夹目录。每个文件夹下面可以放不同的数据文件。通过文件夹可以查询里面存放的文件。但文件夹本身和数据的内容毫无关系。

style="font-family: 仿宋; font-size: 18px; color: #0000ff">桶则是按照数据内容的某个值进行分桶,把一个大文件散列称为一个个小文件。

style="font-family: 仿宋; font-size: 18px; color: #0000ff"> 

style="font-family: 仿宋; font-size: 18px; color: #0000ff">这些小文件可以单独排序。如果另外一个表也按照同样的规则分成了一个个小文件。两个表join的时候,就不必要扫描整个表,只需要匹配相同分桶的数据即可。效率当然大大提升。

style="font-family: 仿宋; font-size: 18px; color: #0000ff">同样,对数据抽样的时候,也不需要扫描整个文件。只需要对每个分区按照相同规则抽取一部分数据即可。

 

style="font-family: SimSun; font-size: 24px; color: #3333ff; background-color: #ffffff;">查看数据库

show databases;

style="font-family: SimSun; font-size: 24px; color: #3366ff;">查看表

style="font-family: SimSun; font-size: 24px; color: #3366ff; background-color: #ff0000;"> style="font-family: SimSun; font-size: 18px; color: #ffff00;">查看编码格式

style="font-family: SimSun; font-size: 24px; color: #000000;"> style="font-family: SimSun; font-size: 18px;"> style="font-family: SimSun; font-size: 18px;"> style="color: #555555; font-family: 'microsoft yahei'; font-size: 15px;">show variables like 'character%';

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">存在的所有表

show tables;

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建表的命令

show create table table_name;

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">表的结构

show index from table_name;

style="font-family: SimSun; font-size: 18px;">图片 9

show columns from table_name;

style="font-family: SimSun; font-size: 18px;">图片 10

> style="color: red;">注: > > > style="color: red;">“show columns from table_name; > > > style="color: red;">” > > > style="color: red;">和“ > > > style="color: red;">desctable_name;” > > > style="color: red;">效果是一样的。

show table status like "table_name"G

style="font-family: SimSun; font-size: 18px;">图片 11

> style="color: red;">注: > > > style="color: red;">”G” > > > style="color: red;">是为了让数据显示更清晰易懂,也可以不设置。

style="font-family: SimSun; font-size: 24px; color: #3333ff;">查看服务器

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">服务器状态

show status;

style="font-family: SimSun; font-size: 18px; background-color: #ff0000;"> > style="color: #ffcc00;">服务器配置变量

show variables;

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看表结构

style="font-family: SimSun; font-size: 18px; background-color: #ffffff;">desc table_name;

 

  LATIN1字符集:相对于ASCII字符集做了扩展,仍然使用一个字节表示字符,但启用了高位,扩展了字符集的表示范围。

use

drop

 

  GBK字符集:支持中文,字符有一字节编码和两字节编码方式。

style="font-family: SimSun; font-size: 24px; color: #3366ff;">使用数据库

use database_name;

 

 

  UTF8字符集:Unicode字符集的一种,是计算机科学领域里的一项业界标准,支持了所有国家的文字字符,utf8采用1-4个字节表示字符。

create

style="font-size: 18px;">撤销数据库

style="font-family: "Microsoft YaHei"; font-size: 14pt; color: #0000ff">1、MySQL与字符集

style="font-family: SimSun; font-size: 24px; color: #3366ff;">创建数据库

create database if not exists database_name;

style="color: #3366ff; font-family: SimSun; font-size: 24px;">创建表

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建非临时表

create table student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

> style="font-family: SimSun; font-size: 18px; color: #cc0000;">注: > > > style="color: red;">1 > > > > style="color: red;">, > > > > style="color: red;">auto_increment > > > > style="color: red;">属性的对象只能是整形,在进行值插入时若不指定具体的值,或指定其为 > > > > style="color: red;">null > > > > style="color: red;">或 > > > > style="color: red;">0 > > > > style="color: red;">,则其自动赋值为当前列的最大值再加上 > > > > style="color: red;">1 > > > > style="color: red;">的值。 > > > style="color: red;">2 > > > > style="color: red;">, > > > > style="color: red;">primary key() > > > > style="color: red;">是设置主键 > > > > style="color: red;">, > > > > style="color: red;">一个表可以不止一个主键。

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建临时表

create temporary table Student_info(StuID int not null auto_increment,StuName varchar(15) not null,Telephone bigInt not null,primary key(StuID));

> style="color: red;">注:创建临时表要用关键字 > > > style="color: red;">”temporary” > > > style="color: red;">,临时表主要是用于保存一些临时数据,临时表的数据只是在当前连接可用,当断开链接后就会, > > > style="color: red;">MySQL > > > style="color: red;">就会自动删除临时表。

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">创建复制旧表的数据和结构的新表

create table new_table select * from old_table;

> style="color: red;">注:这种方法会将旧表中的所有内容都拷贝过来,当然我们也可以用 > > > style="color: red;">delete > > > style="color: red;">、insert into > > > style="color: red;">来删除、增加。不过这种方法最不好的地方就是没有了旧表的 > > > style="color: red;">primary key > > > style="color: red;">, > > > style="color: red;">extra(auto_incremet) > > > style="color: red;">等属性。需要自己用 > > > style="color: red;">alter > > > style="color: red;">手动添加。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">创建复制旧表的结构的新表

style="background-color: #ffffff; color: #000000; font-family: SimSun; font-size: 18px;"> > style="font-family: SimSun; font-size: 18px;">  create table new_table select * from old_table where 1 = 2;

style="background-color: #ffffff; color: #000000; font-family: SimSun; font-size: 18px;"> > style="font-family: SimSun; font-size: 18px;">     > style="font-size: 18px;"> > style="color: red;">注:这种方法只是复制表结构,当然旧表中的 > style="color: red;">primary key > style="color: red;"> 和 > style="color: red;">auto_increment > style="color: red;">等属性都是复制过来了。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;"> > style="color: #ffcc00; font-family: SimSun; font-size: 18px;">创建表时指定引擎和编码方式

style="font-family: SimSun; font-size: 18px;">  create table new_table(id int not null,name varchar(20) not null)engine=InnoDB,default charset=UTF-8;

style="font-family: SimSun; font-size: 18px;">     > style="color: #ff0000;">注:因为默认的引擎就是InnoDB,默认的编码方式就是utf-8,所以如果不改变引擎和编码方式可以不添加engine=InnoDB,default charset=utf-8

style="font-family: SimSun; font-size: 18px; color: #00ffff; background-color: #ff0000;"> > style="font-family: SimSun; font-size: 18px;"> > style="font-family: SimSun; font-size: 18px;"> > style="font-family: SimSun; font-size: 18px;">创建表的时候,指定默认的值

style="font-family: SimSun; font-size: 18px;">  create table new_table(id int not null,name varchar(20) not null,salary int not null default 1200)engine=InnoDB,default charset=UTF8;

style="font-family: SimSun; font-size: 18px;">    注:salary int not null default 1200,表示插入所有行中salary的默认值都是1200。

 

  只要涉及到文字的地方,就会存在字符集和编码方式。MySQL系统变量值:

 

drop database databasename;

图片 12 

 

 

style="font-family: "Microsoft YaHei"; font-size: 14pt; color: #0000ff">2、正确使用字符集

 

style="font-size: 18px;">撤销表

drop table if exists table_name;

style="font-family: SimSun; font-size: 18px; color: #ff0000;">注:dropexists的用法是不一样的,drop是永久删除表,而delete只是删除表中的数据。

  数据库服务端的字符集具体要看存储什么字符

 

 

图片 13

 

delete

以上这些参数如何起作用:

desc

style="font-family: SimSun; font-size: 18px; color: #3366ff;">删除表数据

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">删除表的所有数据

delete from table_name;

style="color: yellow; background: red;">删除指定表中的数据

delete from table_name where column_name > value;

1.库、表、列字符集的由来

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看表结构

style="font-family: SimSun; font-size: 18px; background-color: #ffffff;">desc table_name;

insert

  ①建库时,若未明确指定字符集,则采用character_set_server指定的字符集。

drop

style="font-family: SimSun; font-size: 18px; color: #3366ff;">插入数据

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">插入完整元组

insert into Student_info values (10,'宋文',123456789);

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">插入非完整元组

insert into Student_info(StuName,Telephone) values ('小吴,'123456789');

> style="font-family: SimSun; font-size: 18px;">注:在决定插入元组数据中的属性个数时,主要依据表中属性的完整性来决定。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">复制表数据

style="font-family: SimSun; font-size: 18px; color: #3366ff;"> > style="color: #ffcc00; font-family: SimSun; font-size: 18px; background-color: #ff0000;">复制旧表的数据到新表

insert into 新表 select * from 旧表

> style="font-size: 18px;">注: > style="font-size: 18px;">这里两个表的结构要求要一样 > style="color: red;">这里只是复制数据,旧表的 > > > style="color: red;">primary key > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">还是需要自己添加。下面的方法也可以得到两个完全一样的表 > style="color: red;">方法一:首先用“ > > > style="color: red;">creat table  > > > style="color: red;">新表 like  > > > style="color: red;">旧表”将旧表的结构复制过来,然后使用“ > > > style="color: red;">insert into > > > style="color: red;">新表 select from 旧表 > > > style="color: red;">”将表中的数据复制过去。 > style="color: red;">方法二: > > > style="color: red;">show create table > > > style="color: red;">旧表*命令得到创建表的命令,复制粘贴再更改名称最后运行,得到一个数据结构和属性一样的新表 > > > style="color: red;">( > > > style="color: red;">除名称 > > > style="color: red;">) > > > style="color: red;">,再用这里 > > > style="color: red;">insert > > > style="color: red;">命令,将数据复制过去,就可以得到完全一样的表了。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">复制旧表的部分数据到新表

insert into 新表 (字段1,字段2......)select 字段1,,字段2,.....from 旧表

> style="font-family: SimSun; font-size: 18px;">注: > style="font-family: SimSun; font-size: 18px;">1,这里两个表的匹配字段数据类型要一致。 > style="color: red;">2,这里只是复制数据,旧表的 > > > style="color: red;">primary key > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">还是需要自己添加。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">insert ignore into 和 insert into 的区别

INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。INSERT IGNORE INTO当插入数据时,在设置了记录的唯一性后,如果插入重复数据,将不返回错误,只以警告形式返回。 而REPLACE INTO into如果存在primary 或 unique相同的记录,则先删除掉。再插入新记录。

  ②建表时,若未明确指定字符集,则采用当前库所采用的字符集。

 

select

  ③新增时,修改表字段时,若未明确指定字符集,则采用当前表所采用的字符集。

style="font-size: 18px;">撤销数据库

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看当前日期

select current_date;

style="font-family: SimSun; font-size: 18px;">图片 14

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看服务器版本

select version();

style="font-family: SimSun; font-size: 18px;">图片 15

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看当前数据库名称

select database();

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查看当前用户名

select user();

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查询表中数据的语句

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">查询表中指定字段无重复的数据

select distinct column_name from table_name wherecondition;

> style="font-family: SimSun; font-size: 18px;">注: > style="color: red;">1 > > > style="color: red;">, > > > style="color: red;">distinct > > > style="color: red;">用于显示的数据内容不存在重复 > style="color: red;">2 > > > style="color: red;">, > > > style="color: red;">* > > > style="color: red;">表示所有数据 > style="color: red;">3 > > > style="color: red;">, > > > style="color: red;">condition > > > style="color: red;">可以用大于、小于等

style="font-size: 18px; color: #3333ff;">查询数据之动态指定条件语句

 比如有两个表,分别是table1和table2,在table1中有一个id1属性,在table2中有一个id2属性。如果要查询符合id2中的id1的数据,不需要分两句书写,可以直接将其写到一起,比如:

select table1.id1 from table1 where id1=(select table2.id2 from table2 where id2=1);

必须注意:在条件语句中的得出table2的数据只能是一个明确的数值,不能是一个数组。

连接查询

style="font-size: 18px; color: #ffff00; background-color: #ff0000;">语法:

select 属性 from 表1,表2 where (表1.属性 = 表2.属性);

注:“表1.属性 = 表2.属性” 是很重要的,它表示两个表连接的条件。

style="font-size: 18px; color: #ffff00; background-color: #ff0000;">简单连接查询

select * from student_info,test_table where( StuID >2) and(StuID < 5) and (student_info.StuID = test_table.test_StuID);

> style="color: red;">注:查询Student_info表中StuIDtestable表中tesStuID的值相同的所有数据,并且满足Student_info中的StuID在2到5的条件。

style="font-size: 18px;">图片 16

style="font-size: 18px;">图片 17

2.更新、查询涉及到得字符集变量

 

 

  更新流程字符集转换过程:character_set_client-->character_set_connection-->表字符集。

drop database databasename;

自连接查询

style="font-size: 14px; color: #000000; background-color: #ff0000;"> style="font-size: 18px; color: #ffff33;">语法:

  使用别名

  查询流程字符集转换过程:表字符集-->character_set_result

 

例如:

3.character_set_database

style="font-size: 18px;">撤销表

drop table if exists table_name;

style="font-family: SimSun; font-size: 18px; color: #ff0000;">注:dropexists的用法是不一样的,drop是永久删除表,而delete只是删除表中的数据。

图片 18

打印这个表中所有的id,name,但是这个name对应的id至少有一次出现在manager_id中(如果把id看成员工编号,manager_id看成员工上司的id号,那么请打印所有的管理层)。

select distinct z.id,z.name from zilianjie z,zilianjie j where j.manager_id=z.id;

 

超连接查询

style="font-size: 18px; color: #ffff33; background-color: #ff0000;">语法:

内部连接:select 属性1,属性2....... from 表1 inner join 表2 on 表1.属性 = 表2.属性

左连接: select属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

右连接 : select 属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

内部连接

inner join 形式的连接称为内部连接,也可以写为join。内部连接表示只有满足条件的记录才会显示到查询结果中

select * from student_info inner join test_table on student_info.StuID = test_table.test_StuID;

这个语句等价于select * from student_info,test_table where(student_info.StuID =test_table.test_StuID);

style="background-color: #ffffff;">图片 19

style="background-color: #ffffff;">图片 20

 

 

左连接

left join 形式的连接称为左连接。查询结果包含join左侧表中的所有记录以及右侧表满足条件的记录

右连接

right join 形式的连接称为右连接。查询结果包含join右侧表中的所有记录以及左侧表中满足条件的记录

并运算查询

语法

union表示并运算

style="background-color: #ff0000; color: #ffff33; font-size: 18px; font-family: SimSun;">实例

style="background-color: #ffffff;">将两个表中满足条件的数据显示

select StuID,StuName,Telephone from student_info where StuName = "第三个名称"

union

select test_StuID,test_Name from test_table where test_StuID= 4;

style="background-color: #ffffff;">图片 21

style="font-family: SimSun; font-size: 18px; color: #ff9966;">注:这里两个表需要查询出来的属性数目要一致,否则就会出现“ERROR 1222 (21000): The used SELECT statements have a different number of columns”的错误。

  当前默认数据库的字符集,比如执行use xxx后,当前数据库变为xxx,若xxx的字符集为utf8,那么此变量值就变为utf8(供系统设置,无需人工设置)。

 

update

style="font-family: "Microsoft YaHei"; font-size: 14pt; color: #0000ff">3、MySQL客户端与字符集

delete

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更新部分数据

update table_name set StuName = "易建联" where StuID = 1;

style="font-family: SimSun; font-size: 18px;">图片 22

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更新整列的值

update table_name set Telephone= Telephone 1;

style="font-family: SimSun; font-size: 18px;">图片 23

1.对于输入来说:

style="font-family: SimSun; font-size: 18px; color: #3366ff;">删除表数据

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">删除表的所有数据

delete from table_name;

style="color: yellow; background: red;">删除指定表中的数据

delete from table_name where column_name > value;

alter

  客户端使用的字符集必须通过character_set_client、character_set_connection体现出来:

 

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更改表

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">在表中增加一个属性

alter table 表 add column 属性 数据类型 [列的完整性];

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">改变表中某个属性

alter table 表 change 属性 属性 数据类型 [列的完整性];

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">撤销表中的某个属性

alter table 表 drop 属性;

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">更改表的名称

alter table 旧表名 rename 新表名;

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">撤销和更改表的主键

alter table 表 drop primary key,add primary key(属性);

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">在第一行添加属性

alter table table_name add I [….]first;

> style="color: red;">注: > style="color: red;">这里的 > > > style="color: red;">first > > > style="color: red;">表示在第一行添加属性 > > > style="color: red;">I > style="color: red;">[….] > > > style="color: red;">用于表示列的完整性 > style="color: red;">比如: > style="color: red;">alter table userinfo add userAge varchar(2) not null first;

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">在属性I后面添加J属性

alter table table_name add I […] after J;

> style="font-family: SimSun; font-size: 18px;">注: > style="color: red;">[…] > > > style="color: red;">用于表示列的完整性 > style="color: red;">这里的 > > > style="color: red;">I[…] after J > > > style="color: red;">表示在 > > > style="color: red;">J > > > style="color: red;">后面添加属性 > > > style="color: red;">I

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">删除字段I的默认值

alter table table_name alter I drop default;

> style="color: red;">注:使用 ALTER命令及 > > > style="color: red;"> DROP > > > style="color: red;">子句来删除字段的默认值

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">更改表的引擎

alter table table_name engine=engineName;

> style="font-family: SimSun; font-size: 18px;">注:将数据表的引擎更改为指定的引擎。

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">修改表序列的起始值

alter table table_name auto_increment=value;

> style="font-size: 18px;">注: > style="color: red;">更改表的序列起始值为 > > > style="color: red;">value > > > style="color: red;">。 > style="color: red;">这里的 > > > style="color: red;">value > > > style="color: red;">依据设定 > > > style="color: red;">auto_increment > > > style="color: red;">的列的数据类型而定,如 > > > style="color: red;">int > > > style="color: red;">则可以为整数,如 > > > style="color: red;">varchar(4) > > > style="color: red;">可以为字符串

    ①在客户端对数据进行编码(Linux:utf8、windows:gbk)

truncate

视图

    ②MySQL接到SQL语句后(比如insert),发现有字符,询问客户端通过什么方式对字符编码:客户端通过character_set_client参数告知MySQL客户端的编码方式(所以此参数需要正确反映客户端对应的编码)

 

style="font-family: SimSun; font-size: 18px; color: #3333ff;">创建视图

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">创建视图语法:

create view 视图名[字段名1,字段名2,.....] as 查询语句

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">从单个表中派生视图

如:

create view v1 as select StuID,StuName from Student_info;

style="font-family: SimSun; font-size: 18px;">图片 24

style="color: #ffff00; font-family: SimSun; font-size: 18px; background-color: #ff0000;">从多个表中派生视图

如:

create view v2 as select * from student_info,test_table where student_info.StuID = test_table.test_StuID;

style="font-family: SimSun; font-size: 18px;">图片 25

style="font-family: SimSun; font-size: 18px;">图片 26

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">视图虚字段

如:

create view v3 as select StuName,Telephone as oritel,telephone 1 as retel from student_info;

> style="font-size: 18px; color: #ff6600;">注:这里重新创建了一个视图,有三个属性分别为StuName、oritel、retel,其中oritel属性就是Telephone,retel属性是Telephone 1的值。因为这里的oritel和retel属性在原表中是没有的,需要通过计算而来的,所有被成为虚字段。

style="font-size: 18px;">图片 27

style="font-family: SimSun; font-size: 18px; color: #3333ff;">删除视图

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">删除视图语法:

drop view 视图名

  • > style="font-family: SimSun; font-size: 18px;">如:*

drop view v3;

style="color: #3333ff; font-family: SimSun; font-size: 18px;">视图的说明

style="font-family: SimSun; font-size: 18px;">MySQL中的视图是可更新的,任何对原表的更改都会反应到视图中,或者任何对视图的更改都会反应到原表上。

    ③当MySQL发现客户端的client所传输的字符集与自己的connection不一样时,会将client的字符集转换为connection的字符集

style="font-family: SimSun; font-size: 18px; color: #3366ff;">删除表数据

正则表达式

    ④MySQL将转换后的编码存储到MySQL表的列上,在存储的时候再判断编码是否与内部存储字符集(按照优先级判断字符集类型)上的编码一致,如果不一致需要再次转换

 

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查找指定的数据

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">查找name字段中以'st'为开头的所有数据:

select name from table_name where name regexp'^st';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以'ok'为结尾的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中包含'mar'字符串的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以元音字符开头且以'ok'字符串结尾的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]. ok$';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以元音字符开头或者以'ok'字符串结尾的所有数据:

SELECT name FROM person_tbl WHERE nameREGEXP '^[aeiou]|ok$';

2.对于查询来说:

style="font-family: SimSun; font-size: 18px; color: #ffcc00;">删除表的所有数据

NULL运算符

  客户端使用的字符集必须通过character_set_results来体现,服务器询问客户端字符集,通过character_set_results将结果转换为与客户端相同的字符集传递给客户端。(character_set_results默认等于character_set_client)

 

style="font-family: SimSun; font-size: 18px; color: #3366ff;">说明:

IS NULL: 当列的值是NULL,此运算符返回true。

IS NOT NULL: 当列的值不为NULL, 运算符返回true。

<=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。

style="font-family: SimSun; font-size: 18px; color: #3333ff;">例子:

比如:

select * from table where column_name is null;

style="font-family: SimSun; font-size: 18px;">注:将会显示column_name为null的所有数据,不能使用select * from table where column_name = null。

style="font-family: "Microsoft YaHei"; font-size: 14pt; color: #0000ff">4、MySQL字符编码转换原理:

truncate table_name;

 

  问:若character_set_client为UTF8,而character_set_database为GBK,则会出现需要进行编码转换的情况,字符集转换的原理是什么?

 

select * from table where column_nameis not null;

style="font-family: SimSun; font-size: 18px;">注:将会显示column_name不为空的所有数据,不能使用select * from table where column_name != null。

 

select null <=> null;

style="font-family: SimSun; font-size: 18px;">注:将会显示1。

 

select null <=>"NotEmpty";

style="font-family: SimSun; font-size: 18px;">注:将会显示0。

  答:假设gbk字符集的字符串“你好”,需要转为utf8字符集存储,实际就是对于“你好”字符串中的每个汉字去utf8编码表里面查询对应的二进制,然后存储。

 

SQL注入

图片 28 

 

style="font-family: SimSun; font-size: 18px; color: #3333ff;">sql注入是什么

style="font-family: SimSun; font-size: 18px;">就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。

style="color: #3333ff; font-family: SimSun; font-size: 18px;">举例

style="font-family: SimSun; font-size: 18px;">MySQL特点,执行语句的时候如果出现这样的语句将会不报错,并且继续执行,

select * from table_name where user =value;show tables;

style="font-family: SimSun; font-size: 18px;">这样回车就是两个语句,这里是会执行两个语句的,如果在表单中查询用户的信息,那么在表单输入中的用户名一栏写入,

'jame;delete from user;'

style="font-family: SimSun; font-size: 18px;">那么最后就会删除user表。

图解字符集转换过程:

  注:truncate和delete的区别,对于大部分数据库而言,truncate都被当成DDL处理,truncate被称为“截断”某个表-它的作用是删除该表里的全部数据,但保留数据结构。相当于DML中的delete命令,truncate的速度要快得多,而且truncate不像delete可以删除指定的记录,truncate只能删除表中的全部记录。MySQL对truncate的处理比较特殊-如果使用非InnoDB的存储引擎,truncate要比delete快;如果使用InnoDB的存储引擎,在MySQL5.0.3之前,truncate和delete完全一样,在5.0.3之后,truncate比delete要快,但如果该表被外键约束所参照,truncate又变成delete操作。在5.0.13之后,快速truncate总是可用的,即比delete的速度要快。在使用

 

  ①MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;

insert

事务

  ②进行内部操作前将请求数据从character_set_connection转换为内部操作字符集

style="font-family: SimSun; font-size: 18px; color: #3366ff;">插入数据

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">插入完整元组

insert into Student_info values (10,'宋文',123456789);

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">插入非完整元组

insert into Student_info(StuName,Telephone) values ('小吴,'123456789');

> style="font-family: SimSun; font-size: 18px;">注:在决定插入元组数据中的属性个数时,主要依据表中属性的完整性来决定。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">复制表数据

style="font-family: SimSun; font-size: 18px; color: #3366ff;"> > style="color: #ffcc00; font-family: SimSun; font-size: 18px; background-color: #ff0000;">复制旧表的数据到新表

insert into 新表 select * from 旧表

> style="font-size: 18px;">注: > style="font-size: 18px;">这里两个表的结构要求要一样 > style="color: red;">这里只是复制数据,旧表的 > > > style="color: red;">primary key > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">还是需要自己添加。下面的方法也可以得到两个完全一样的表 > style="color: red;">方法一:首先用“ > > > style="color: red;">creat table  > > > style="color: red;">新表 like  > > > style="color: red;">旧表”将旧表的结构复制过来,然后使用“ > > > style="color: red;">insert into > > > style="color: red;">新表 select from 旧表 > > > style="color: red;">”将表中的数据复制过去。 > style="color: red;">方法二: > > > style="color: red;">show create table > > > style="color: red;">旧表*命令得到创建表的命令,复制粘贴再更改名称最后运行,得到一个数据结构和属性一样的新表 > > > style="color: red;">( > > > style="color: red;">除名称 > > > style="color: red;">) > > > style="color: red;">,再用这里 > > > style="color: red;">insert > > > style="color: red;">命令,将数据复制过去,就可以得到完全一样的表了。

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">复制旧表的部分数据到新表

insert into 新表 (字段1,字段2......)select 字段1,,字段2,.....from 旧表

> style="font-family: SimSun; font-size: 18px;">注: > style="font-family: SimSun; font-size: 18px;">1,这里两个表的匹配字段数据类型要一致。 > style="color: red;">2,这里只是复制数据,旧表的 > > > style="color: red;">primary key > > > style="color: red;">和 > > > style="color: red;">auto_increment > > > style="color: red;">还是需要自己添加。

style="font-family: SimSun; font-size: 18px; color: #3366ff;">insert ignore into 和 insert into 的区别

INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。INSERT IGNORE INTO当插入数据时,在设置了记录的唯一性后,如果插入重复数据,将不返回错误,只以警告形式返回。 而REPLACE INTO into如果存在primary 或 unique相同的记录,则先删除掉。再插入新记录。

一般来说,事务是必须满足4个条件(ACID):Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

确定步骤:

select

1、事务的原子性:一组事务,要么成功;要么撤回。

--使用每个数据字段的CHARACTER SET设定值;

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看当前日期

select current_date;

style="font-family: SimSun; font-size: 18px;">图片 29

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看服务器版本

select version();

style="font-family: SimSun; font-size: 18px;">图片 30

style="font-family: SimSun; font-size: 18px; color: #3366ff;">查看当前数据库名称

select database();

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查看当前用户名

select user();

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查询表中数据的语句

style="font-family: SimSun; font-size: 18px; color: #ffcc00; background-color: #ff0000;">查询表中指定字段无重复的数据

select distinct column_name from table_name wherecondition;

> style="font-family: SimSun; font-size: 18px;">注: > style="color: red;">1 > > > style="color: red;">, > > > style="color: red;">distinct > > > style="color: red;">用于显示的数据内容不存在重复 > style="color: red;">2 > > > style="color: red;">, > > > style="color: red;">* > > > style="color: red;">表示所有数据 > style="color: red;">3 > > > style="color: red;">, > > > style="color: red;">condition > > > style="color: red;">可以用大于、小于等

style="font-size: 18px; color: #3333ff;">查询数据之动态指定条件语句

 比如有两个表,分别是table1和table2,在table1中有一个id1属性,在table2中有一个id2属性。如果要查询符合id2中的id1的数据,不需要分两句书写,可以直接将其写到一起,比如:

select table1.id1 from table1 where id1=(select table2.id2 from table2 where id2=1);

必须注意:在条件语句中的得出table2的数据只能是一个明确的数值,不能是一个数组。

连接查询

style="font-size: 18px; color: #ffff00; background-color: #ff0000;">语法:

select 属性 from 表1,表2 where (表1.属性 = 表2.属性);

注:“表1.属性 = 表2.属性” 是很重要的,它表示两个表连接的条件。

style="font-size: 18px; color: #ffff00; background-color: #ff0000;">简单连接查询

select * from student_info,test_table where( StuID >2) and(StuID < 5) and (student_info.StuID = test_table.test_StuID);

> style="color: red;">注:查询Student_info表中StuIDtestable表中tesStuID的值相同的所有数据,并且满足Student_info中的StuID在2到5的条件。

style="font-size: 18px;">图片 31

style="font-size: 18px;">图片 32

2、稳定性 : 有非法数据(外键约束之类),事务撤回。

--若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值;

 

3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。

--若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;

自连接查询

style="font-size: 14px; color: #000000; background-color: #ff0000;"> style="font-size: 18px; color: #ffff33;">语法:

  使用别名

4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

--若上述值不存在,则使用character_set_server设定值;

例如:

 

  ③将操作结果从内部操作字符集转换为character_set_results。

图片 33

打印这个表中所有的id,name,但是这个name对应的id至少有一次出现在manager_id中(如果把id看成员工编号,manager_id看成员工上司的id号,那么请打印所有的管理层)。

select distinct z.id,z.name from zilianjie z,zilianjie j where j.manager_id=z.id;

 

超连接查询

style="font-size: 18px; color: #ffff33; background-color: #ff0000;">语法:

内部连接:select 属性1,属性2....... from 表1 inner join 表2 on 表1.属性 = 表2.属性

左连接: select属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

右连接 : select 属性1,属性2....... from 表1 left join 表2 on 表1.属性 = 表2.属性

内部连接

inner join 形式的连接称为内部连接,也可以写为join。内部连接表示只有满足条件的记录才会显示到查询结果中

select * from student_info inner join test_分桶表的详解和创建实例,Postgresql生成大量测试数据。table on student_info.StuID = test_table.test_StuID;

这个语句等价于select * from student_info,test_table where(student_info.StuID =test_table.test_StuID);

style="background-color: #ffffff;">图片 34

style="background-color: #ffffff;">图片 35

 

 

左连接

left join 形式的连接称为左连接。查询结果包含join左侧表中的所有记录以及右侧表满足条件的记录

右连接

right join 形式的连接称为右连接。查询结果包含join右侧表中的所有记录以及左侧表中满足条件的记录

并运算查询

语法

union表示并运算

style="background-color: #ff0000; color: #ffff33; font-size: 18px; font-family: SimSun;">实例

style="background-color: #ffffff;">将两个表中满足条件的数据显示

select StuID,StuName,Telephone from student_info where StuName = "第三个名称"

union

select test_StuID,test_Name from test_table where test_StuID= 4;

style="background-color: #ffffff;">图片 36

style="font-family: SimSun; font-size: 18px; color: #ff9966;">注:这里两个表需要查询出来的属性数目要一致,否则就会出现“ERROR 1222 (21000): The used SELECT statements have a different number of columns”的错误。

双索引

style="font-family: "Microsoft YaHei"; font-size: 14pt; color: #0000ff">5、字符集常见处理操作

update

primary key(column1,column2);

1.查看字符集编码设置

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更新部分数据

update table_name set StuName = "易建联" where StuID = 1;

style="font-family: SimSun; font-size: 18px;">图片 37

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更新整列的值

update table_name set Telephone= Telephone 1;

style="font-family: SimSun; font-size: 18px;">图片 38

设置了双索引的话,那么满足两个数据都重复的数据不会存入,有一个数据重复则可以存储。

  mysql> show variables like '%character%';

alter

图片 39

2.设置字符集编码

style="font-family: SimSun; font-size: 18px; color: #3333ff;">更改表

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">在表中增加一个属性

alter table 表 add column 属性 数据类型 [列的完整性];

style="background-color: #ff0000; color: #ffcc00; font-family: SimSun; font-size: 18px;">改变表中某个属性

alter table 表 change 属性 属性 数据类型 [列的完整性];

alter table 表 modify 属性 数据类型 [列的完整性];

style="font-family: SimSun; font-size: 18px;">注:change可以改变属性名称,但是modify只能在原来的属性上进行修改。

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">撤销表中的某个属性

alter table 表 drop 属性;

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">更改表的名称

alter table 旧表名 rename 新表名;

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">撤销和更改表的主键

alter table 表 drop primary key,add primary key(属性);

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">在第一行添加属性

alter table table_name add I [….]first;

> style="color: red;">注: > style="color: red;">这里的 > > > style="color: red;">first > > > style="color: red;">表示在第一行添加属性 > > > style="color: red;">I > style="color: red;">[….] > > > style="color: red;">用于表示列的完整性 > style="color: red;">比如: > style="color: red;">alter table userinfo add userAge varchar(2) not null first;

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">在属性I后面添加J属性

alter table table_name add I […] after J;

> style="font-family: SimSun; font-size: 18px;">注: > style="color: red;">[…] > > > style="color: red;">用于表示列的完整性 > style="color: red;">这里的 > > > style="color: red;">I[…] after J > > > style="color: red;">表示在 > > > style="color: red;">J > > > style="color: red;">后面添加属性 > > > style="color: red;">I

style="background-color: #ff0000; color: #ffff33; font-family: SimSun; font-size: 18px;">删除字段I的默认值

alter table table_name alter I drop default;

> style="color: red;">注:使用 ALTER命令及 > > > style="color: red;"> DROP > > > style="color: red;">子句来删除字段的默认值

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">更改表的引擎

alter table table_name engine=engineName;

> style="font-family: SimSun; font-size: 18px;">注:将数据表的引擎更改为指定的引擎。

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">修改表序列的起始值

alter table table_name auto_increment=value;

> style="font-size: 18px;">注: > style="color: red;">更改表的序列起始值为 > > > style="color: red;">value > > > style="color: red;">。 > style="color: red;">这里的 > > > style="color: red;">value > > > style="color: red;">依据设定 > > > style="color: red;">auto_increment > > > style="color: red;">的列的数据类型而定,如 > > > style="color: red;">int > > > style="color: red;">则可以为整数,如 > > > style="color: red;">varchar(4) > > > style="color: red;">可以为字符串

order by

  mysql> set names 'utf8';

视图

语法:

相当于同时:

style="font-family: SimSun; font-size: 18px; color: #3333ff;">创建视图

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">创建或替换视图语法:

create or replace view 视图名[字段名1,字段名2,.....] as 查询语句

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">从单个表中派生视图

如:

create view v1 as select StuID,StuName from Student_info;

style="font-family: SimSun; font-size: 18px;">图片 40

style="color: #ffff00; font-family: SimSun; font-size: 18px; background-color: #ff0000;">从多个表中派生视图

如:

create view v2 as select * from student_info,test_table where student_info.StuID = test_table.test_StuID;

style="font-family: SimSun; font-size: 18px;">图片 41

style="font-family: SimSun; font-size: 18px;">图片 42

style="background-color: #ff0000; color: #ffff00; font-family: SimSun; font-size: 18px;">视图虚字段

如:

create view v3 as select StuName,Telephone as oritel,telephone 1 as retel from student_info;

> style="font-size: 18px; color: #ff6600;">注:这里重新创建了一个视图,有三个属性分别为StuName、oritel、retel,其中oritel属性就是Telephone,retel属性是Telephone 1的值。因为这里的oritel和retel属性在原表中是没有的,需要通过计算而来的,所有被成为虚字段。

style="font-size: 18px;">图片 43

style="font-family: SimSun; font-size: 18px; color: #3333ff;">删除视图

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">删除视图语法:

drop view 视图名

  • > style="font-family: SimSun; font-size: 18px;">如:*

drop view v3;

style="color: #3333ff; font-family: SimSun; font-size: 18px;">视图的说明

style="font-family: SimSun; font-size: 18px;">MySQL中的视图是可更新的,任何对原表的更改都会反应到视图中,或者任何对视图的更改都会反应到原表上。

order by 属性1,属性2,...... [asc,desc];

  set character_set_client = utf8;

正则表达式

注:asc为升序(默认为升序)

  set character_set_results = utf8;

style="font-family: SimSun; font-size: 18px; color: #3333ff;">查找指定的数据

style="font-family: SimSun; font-size: 18px; color: #ffff00; background-color: #ff0000;">查找name字段中以'st'为开头的所有数据:

select name from table_name where name regexp'^st';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以'ok'为结尾的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中包含'mar'字符串的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以元音字符开头且以'ok'字符串结尾的所有数据:

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]. ok$';

style="font-family: SimSun; font-size: 18px; color: #ffff33; background-color: #ff0000;">查找name字段中以元音字符开头或者以'ok'字符串结尾的所有数据:

SELECT name FROM person_tbl WHERE nameREGEXP '^[aeiou]|ok$';

desc为降序

  set character_set_connection = utf8;

NULL运算符

  当有多个属性值进行排序的时候,按照先后顺序进行排序。

3.修改数据库字符集

style="font-family: SimSun; font-size: 18px; color: #3366ff;">说明:

IS NULL: 当列的值是NULL,此运算符返回true。

IS NOT NULL: 当列的值不为NULL, 运算符返回true。

<=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。

style="font-family: SimSun; font-size: 18px; color: #3333ff;">例子:

比如:

select * from table where column_name is null;

style="font-family: SimSun; font-size: 18px;">注:将会显示column_name为null的所有数据,不能使用select * from table where column_name = null。

:

  mysql> alter database database_name character set xxx;

 

select * from Student_info order byStuID,StuName desc;

只修改库的字符集,影响后续创建的表的默认定义;对于已创建的表的字符集不受影响。(一般在数据库实现字符集即可,表和列都默认采用数据库的字符集)

select * from table where column_nameis not null;

style="font-family: SimSun; font-size: 18px;">注:将会显示column_name不为空的所有数据,不能使用select * from table where column_name != null。

 

select null <=> null;

style="font-family: SimSun; font-size: 18px;">注:将会显示1。

 

select null <=>"NotEmpty";

style="font-family: SimSun; font-size: 18px;">注:将会显示0。

先按照StuID进行降序排序,再按照StuName进行升序排序。

4.修改表的字符集

SQL注入

 

  mysql> alter table table_name character set xxx;

style="font-family: SimSun; font-size: 18px; color: #3333ff;">sql注入是什么

style="font-family: SimSun; font-size: 18px;">就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。

style="color: #3333ff; font-family: SimSun; font-size: 18px;">举例

style="font-family: SimSun; font-size: 18px;">MySQL特点,执行语句的时候如果出现这样的语句将会不报错,并且继续执行,

select * from table_name where user =value;show tables;

style="font-family: SimSun; font-size: 18px;">这样回车就是两个语句,这里是会执行两个语句的,如果在表单中查询用户的信息,那么在表单输入中的用户名一栏写入,

'jame;delete from user;'

style="font-family: SimSun; font-size: 18px;">那么最后就会删除user表。

删除某个数据库下的所有表

只修改表的字符集,影响后续该表新增列的默认定义,已有列的字符集不受影响。

 

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')

  mysql> alter table table_name convert to character set xxx;

事务

FROM information_schema.tables

同时修改表字符集和已有列字符集,并将已有数据进行字符集编码转换。

一般来说,事务是必须满足4个条件(ACID):Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

WHERE table_schema = 'mydb';

5.修改列字符集

1、事务的原子性:一组事务,要么成功;要么撤回。

将mydb换成需要删除的数据库就可以了。

格式:

2、稳定性 : 有非法数据(外键约束之类),事务撤回。

 

ALTER TABLE table_name MODIFY

3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。

binary

column_name {CHAR | VARCHAR | TEXT} (column_length)

4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

binary用于可以用于区分大小写

    [CHARACTER SET charset_name]

 

MySQL中本来是不区分大小写的,

    [COLLATE collation_name]

双索引

比如:“select from table_name where name = "Jame";*”可以搜索出table_name 表中Jame、jame、jAme等数据,

mysql> alter table table_name modify col_name varchar(col_length) character set xxx;

primary key(column1,column2);

但是“select from table where binary name ="Jame";*”就只能搜索出Jame数据。

style="font-family: "Microsoft YaHei"; font-size: 14pt; color: #0000ff">6、字符集的正确实践:

设置了双索引的话,那么满足两个数据都重复的数据不会存入,有一个数据重复则可以存储。

addcslashes() 

MySQL软件工具本身是没有字符集的,主要是因为工具所在的OS的字符集(Windows:gbk、Linux:utf8),所以字符集的正确实践非常重要:

图片 44

 

  1.对于insert来说,character_set_client、character_set_connection相同,而且正确反映客户端使用的字符集

order by

addcslashes() 函数在指定的字符前添加反斜杠。

  2.对于select来说,character_set_results正确反映客户端字符集

语法:

参数    描述

  3.数据库字符集取决于我们要存储的字符类型

order by 属性1,属性2,...... [asc,desc];

string 必需。规定要检查的字符串。

  4.字符集转换最多发生一次,这就要求character_set_client、character_set_connection相同

注:asc为升序(默认为升序)

characters     可选。规定受 addcslashes() 影响的字符或字符范围。

  5.所有的字符集转换都发生在数据库端

desc为降序

 

综述:

  当有多个属性值进行排序的时候,按照先后顺序进行排序。

select coalesce(a,b,c)

  1、建立数据库的时候注意字符集(gbk、utf8);

:

参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。

  2、连接数据库以后,无论是执行dml还是select,只要涉及到varchar、char列,就需要设置正确的字符集参数。

select * from Student_info order byStuID,StuName desc;

如:

 

先按照StuID进行降序排序,再按照StuName进行升序排序。

创建一个employee_tbl表:

二、校对规则collation校对

 

 

  字符集是一套符号和对应的编号

删除某个数据库下的所有表

CREATE TABLE `employee_tbl`(`id`int(11) NOT NULL,`name`char(10) NOT NULL DEFAULT '',`date` datetime NOT NULL,`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看数据库支持的所有字符集(charset):

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')

向表中插入数据:

mysql> show character set;

FROM information_schema.tables

 

校对规则(collation):

WHERE table_schema = 'mydb';

INSERT INTO `employee_tbl` VALUES ('1','小明','2016-04-22 15:25:33','1'),('2','小王','2016-04-20 15:25:47','3'),('3','小丽','2016-04-19 15:26:02','2'),('4','小王','2016-04-07 15:26:14','4'),('5','小明','2016-04-11 15:26:40','4'),('6','小明','2016-04-04 15:26:54','2');

  是在字符集内用于字符比较和排序的一套规则,比如有的规则区分大小写,有的则无视。

将mydb换成需要删除的数据库就可以了。

查询表中数据:

mysql> create table t1(id int,name varchar(20));    #t1建表没有指定校对规则

 

 

图片 45

binary

mysql> SELECT * FROM employee_tbl; ---- -------- --------------------- -------- | id | name   | date                | singin | ---- -------- --------------------- -------- |1|小明|2016-04-2215:25:33|1||2|小王|2016-04-2015:25:47|3||3|小丽|2016-04-1915:26:02|2||4|小王|2016-04-0715:26:14|4||5|小明|2016-04-1115:26:40|4||6|小明|2016-04-0415:26:54|2| ---- -------- --------------------- -------- 

 mysql> show collation;  #查看数据库支持的所有校对规则

binary用于可以用于区分大小写

 

mysql> show variables like 'collation_%';  #查看当前字符集和校对规则设置

MySQL中本来是不区分大小写的,

 

图片 46

比如:“select from table_name where name = "Jame";*”可以搜索出table_name 表中Jame、jame、jAme等数据,

接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:

校对规则特征:

但是“select from table where binary name ="Jame";*”就只能搜索出Jame数据。

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name; -------- ---------- | name   | COUNT(*)| -------- ---------- |小丽|1||小明|3||小王|2| -------- ---------- 3 rows inset(0.01 sec)

  ①两个不同的字符集不能有相同的校对规则;

addcslashes() 

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。

例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:

mysql> SELECT name, SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP; -------- -------------- | name   | singin_count | -------- -------------- |小丽|2||小明|7||小王|7|| NULL   |16| -------- -------------- 4 rows inset(0.00 sec)

如果不使用With Rollup则结果不会出现NULL字段

图片 47

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:

select coalesce(a,b,c);

参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。

以下实例中如果名字为空我们使用总数代替:

 

mysql> SELECT coalesce(name,'总数'), SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP; -------------------------- -------------- | coalesce(name,'总数')| singin_count | -------------------------- -------------- |小丽|2||小明|7||小王|7||总数|16| -------------------------- -------------- 4 rows inset(0.01 sec)

 

参考文章连接:

 

  ②每个字符集有一个默认校对规则;

 

  ③存在校对规则命名约定:以其相关的字符集名开始,中间包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束。

addcslashes() 函数在指定的字符前添加反斜杠。

注意:

参数    描述

  系统使用utf8字符集,若使用utf8_bin校对规则执行SQL查询时区分大小写,使用utf8_general_ci不区分大小写(默认的utf8字符集对应的校对规则是utf8_general_ci)。

string 必需。规定要检查的字符串。

示例:

characters     可选。规定受 addcslashes() 影响的字符或字符范围。

mysql> create table t2(id int,name varchar(20)) character set=gbk collate=gbk_bin;    #t2建表指定校对规则(区分大小写)

 

图片 48

select coalesce(a,b,c)

参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。

如:

创建一个employee_tbl表:

 

CREATE TABLE `employee_tbl`(`id`int(11) NOT NULL,`name`char(10) NOT NULL DEFAULT '',`date` datetime NOT NULL,`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

向表中插入数据:

 

INSERT INTO `employee_tbl` VALUES ('1','小明','2016-04-22 15:25:33','1'),('2','小王','2016-04-20 15:25:47','3'),('3','小丽','2016-04-19 15:26:02','2'),('4','小王','2016-04-07 15:26:14','4'),('5','小明','2016-04-11 15:26:40','4'),('6','小明','2016-04-04 15:26:54','2');

查询表中数据:

 

mysql> SELECT * FROM employee_tbl; ---- -------- --------------------- -------- | id | name   | date                | singin | ---- -------- --------------------- -------- |1|小明|2016-04-2215:25:33|1||2|小王|2016-04-2015:25:47|3||3|小丽|2016-04-1915:26:02|2||4|小王|2016-04-0715:26:14|4||5|小明|2016-04-1115:26:40|4||6|小明|2016-04-0415:26:54|2| ---- -------- --------------------- -------- 

 

 

接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name; -------- ---------- | name   | COUNT(*)| -------- ---------- |小丽|1||小明|3||小王|2| -------- ---------- 3 rows inset(0.01 sec)

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。

例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:

mysql> SELECT name, SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP; -------- -------------- | name   | singin_count | -------- -------------- |小丽|2||小明|7||小王|7|| NULL   |16| -------- -------------- 4 rows inset(0.00 sec)

如果不使用With Rollup则结果不会出现NULL字段

图片 49

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:

select coalesce(a,b,c);

参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。

以下实例中如果名字为空我们使用总数代替:

 

mysql> SELECT coalesce(name,'总数'), SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP; -------------------------- -------------- | coalesce(name,'总数')| singin_count | -------------------------- -------------- |小丽|2||小明|7||小王|7||总数|16| -------------------------- -------------- 4 rows inset(0.01 sec)

 

参考文章连接:

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:分桶表的详解和创建实例,Postgresql生成大量测试

关键词: