Django与数据库中操作对应

在SQL中,很多关键词在删、改、查时都是可以用的,如order by、 like、in、join、union、and、or、not等等,我们以查询为例,说一下django如何映射SQL的这些关键字的(查、删、改中这些关键字的使用基本相同)。

No1 F类(无对应SQL关键字)

前面提到的filter/exclude中的查询参数值都是常量,如果我们想比较model的两个字段怎么办呢?Django也提供了方法,F类,F类实例化时,参数也可以用双下划线,也可以逻辑运算,如下

>>> from django.db.models import F
>>> Entry.objects.filter(n_comments__gt=F('n_pingbacks'))
>>> from datetime import timedelta
>>> Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3))
>>> Entry.objects.filter(authors__name=F('blog__name'))

No2 Q类(对应and/or/not)

如果有or等逻辑关系呢,那就用Q类,filter中的条件可以是Q对象与非Q查询混和使用,但不建议这样做,因为混和查询时Q对象要放前面,这样就有难免忘记顺序而出错,所以最好如果使用Q对象,那就全部用Q对象。Q对象也很简单,就是把原来filter中的各个条件分别放在一个Q()即可,不过我们还可以使用或与非,分别对应符号为”|”和”&”和”~”,而且这些逻辑操作返回的还是一个Q对象,另外,逗号是各组条件的基本连接符,也是与的关系,其实可以用&代替(在python manage.py shell测试过,&代替逗号,执行的SQL是一样的),不过那样的话可读性会很差,这与我们直接写SQL时,各组条件and时用换行一样,逻辑清晰。

from django.db.models import Q
>>> Poll.objects.get( Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
question__startswith='Who') #正确,但不要这样混用
>>> Poll.objects.get( Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)),
Q(question__startswith='Who')) #推荐,全部是Q对象
>>> Poll.objects.get( (Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)))&
Q(question__startswith='Who')) #与上面语句同意,&代替",",可读性差

Q类中时应该可以用F类,待测试。

No3 annotate(无对应SQL关键字)

函数原型annotate( *args , **kwargs )

返回QuerySet

往每个QuerySet的model instance中加入一个或多个字段,字段值只能是聚合函数,因为使用annotate时,会用group by,所以只能用聚合函数。聚合函数可以像filter那样关联表,即在聚合函数中,Django对OneToOne、OneToMany、ManyToMany关联查询及其反向关联提供了相同的方式,见下面例子。

>>> from django.contrib.auth.models import User
>>> from django.db.models import Count
#计算每个学员的usertask数量,字段命名为ut_num,返回的QuerySet中的每个object都有
#这个字段在UserTask中定义User为外键,在Task中定义与User是ManyToMany,中间表为
#notification_task_users
>>> a = User.objects.filter(is_active=True, usertask__is_active=True). annotate(n=Count('usertask')) #一对多反向连接。User与UserTask是onetomany关联
>>> b = User.objects.filter(is_active=True, task__is_active=True).annotate(n=Count('task__name')) #多对多反向连接,User与Task是manytomany关联
>>> len(a) #这里才会对a求值
>>> len(b)

a对应的SQL语句为(SQL中没有为表起别名,u、ut是我加的):

SELECT AUTH.USER.*,COUNT(UT.ID) AS UT_NUM
FROM AUTH_USER AS U
LEFT OUTER JOIN UT ON U.ID = UT.USER_ID
WHERE U.IS_ACTIVE=TRUE AND UT.IS_ACTIVE=TRUE
GROUP BY U.*

b对应的SQL语句为(SQL中没有为表起别名,u、t、r是我加的):

SELECT U.*,COUNT(T.NAME) AS N
FROM AUTH_USER AS U
LEFT OUTER JOIN NOTIFICATION.TASK_USERS AS R ON U.ID=R.USER_ID
LEFT OUTER JOIN NOTIFICATION_TASK AS T ON R.TASK_ID=T.ID
WHERE T.IS_ACTIVE=TRUE AND U.IS_ACTIVE=TRUE
GROUP BY U.*

No4 order_by–对应order by

函数原型 order_by(**kwargs)

返回QuerySet

正向的反向关联表跟filter的方式一样。如果直接用字段名,那就是升序asc排列;如果字段名前加-,就是降序desc

No5 distinct–对应distinct

原型 distinct()

一般与values()、values_list()连用,这时它返回ValuesQuerySet、ValuesListQuerySet

这个类跟列表很相似,它的每个元素是一个字典。它没有参数(其实是有参数的,不过,参数只在PostgreSQL上起作用)。使用方法为

>>> a=Author.objects.values_list(name).distinct()
>>> b=Author.objects.values_list(name,email).distinct()

对应的SQL分别为

SELECT DISTINCT NAME
FROM AUTHOR

SELECT DISTINCT NAME,EMAIL
FROM AUTHOR

No6 values()和values_list()–对应’select 某几个字段’

函数原型values(*field), values_list(*field)

返回ValuesQuerySet, ValuesListQuerySet

Author.objects.filter(**kwargs)对应的SQL只返回主表(即Author表)的所有字段值,即使在查询时关联了其它表,关联表的字段也不会返回,只有当我们通过Author instance用关联表时,Django才会再次查询数据库获取值。当我们不用Author instance的方法,且只想返回几个字段时,就要用values(),它返回的是一个ValuesQuerySet对象,它类似于一个列表,不过,它的每个元素是字典。而values_list()跟values()相似,它返回的是一个ValuesListQuerySet,也类型于一个列表,不过它的元素不是字典,而是元组。一般的,当我们不需要model instance的方法且返回多个字段时,用values(*field),而返回单个字段时用values_list(‘field’,flat=True),这里flat=True是要求每个元素不是元组,而是单个值,见下面例子。而且我们可以返回关联表的字段,用法跟filter中关联表的方式完全相同。

>>> a = User.objects.values('id','username','userex__account_name')
>>> type(a)
<class 'django.db.models.query.ValuesQuerySet'>
>>> a
[{'id':0,'username':u'test0',' userex__account_name': u'mlp-gikoo-test0@gk.cn'},{'id':1,'username':u'test1',
'userex__account_name': u'mlp-gikoo-test1@gk.cn'},{'id':2,'username':u'test2', ' userex__account_name': u'mlp-gikoo-test2@gk.cn'}]
>>> b= User.objects.values_list('username',flat=True)
>>> b
[u'gikoo', u'test1' ,u'test2']

No7 select_related()–对应返回关联表字段,嵌套。

这部分要比SQL语句强大

原型select_related(*filed)

返回QuerySet

它可以指定返回哪些关联表model instance,这里的field跟filter()中的键一样,可以用双下划线,但也有不同。可以参考 ForeignKey 或者 OneToOneField获取对应信息。QuerySet中的元素中的OneToOne关联及外键对应的是都是关联表的一条记录,如my_entry=Entry.objects.get(id=1),my_entry.blog就是关联表的一条记录的对象。select_related()不能用于OneToMany的反向连接,和ManyToMany,这些都是model的一条记录对应关联表中的多条记录。前面提到了对于a = Author.objects.filter(**kwargs)这类语句,对应的SQL只返回主表,即Author的所有字段,并不会返回关联表字段值,只有当我们使用关联表时才会再查数据库返回,但有些时候这样做并不好。看下面两段代码,这两段代码在1.1中提到过。在代码1中,在遍历a前,先执行a对应的SQL,拿到数据后,然后再遍历a,而遍历过程中,每次都还要查询数据库获取关联表。代码2中,当遍历开始前,先拿到Entry的QuerySet,并且也拿到这个QuerySet的每个object中的blog对象,这样遍历过程中,就不用再查询数据库了,这样就减少了数据库读次数。

代码1

a = Entry.objects.all()
for e in a:
    print (e.blog.name)

代码2

a = Entry.objects.select_related('blog')
for e in a:
    print (e.blog.name)

No8 prefetch_related (*field) –对应返回关联表字段

嵌套这部分要比SQL语句强大

函数原型 prefetch_related (*field)

返回的是QuerySet

可以用双下划线。前面提到select_related不能用于OneToMany的反向连接,及ManyToMany的情况,这部分由prefetch_related()来实现。其实,prefetch_related()也能做select_related()的事情,但由于策略不同,可能相比select_related()要低效一些,所以建议还是各管各擅长的。select_related是用select ……join来返回关联的表字段,而prefetch_related是用多条SQL语句的形式查询,一般,后一条语句用IN来调用上一句话返回的结果。

class Restaurant(models.Model):
    pizzas = models.ManyToMany(Pizza, related_name='restaurants')
    best_pizza = models.ForeignKey(Pizza, related_name='championed_by')
>>> Restaurant.objects.prefetch_related('pizzas__toppings')
>>> Restaurant.objects.select_related('best_pizza').prefetch_related('best_pizza__toppings')

先用select_related查到best_pizza对象,再用prefetch_related 从best_pizza查出toppings

至此,我们总结了查询数据库时,返回的数据形式,主要有三种:返回QuerySet,每个object只包含主表字段;返回QuerySet,每个object除了包含主表所有字段,还包含某些关联表的object,这种情况要用select_related()和prefetch_related();返回ValuesQuerySet, ValuesListQuerySet,它们的每个元素包含若干主表和关联表的字段,不包含任何实体和关联实例,这种情况要用values()和values_list()。

No9 extra()–实现复杂的where子句

函数原型:extra(select=None, where=None, params=None, tables=None, order_by=None, select_params=None)

基本上,查询时用django提供的方法就够用了,不过有时where子句中包含复杂的逻辑,这种情况下django提供的方法可能不容易做到,还好,django有extra(), extra()中直接写一些SQL语句。不过,不同的数据库用的SQL有些差异,所以尽可能不要用extra()。需要时再看使用方法吧。

No10 aggregate(*args, **kwargs)–对应聚合函数

参数为聚合函数,最好用**kwargs的形式,每个参数起一个名字。如

>>> from django.db.models import Count

>>> q = Blog.objects.aggregate(Count(‘entry’)) #这是用*args的形式,最好不要这样用

>>> q = Blog.objects.aggregate(number_of_entries=Count(‘entry’)) #这是用**kwargs的形式

{‘number_of_entries’: 16}

No11 exists()、count()、len()

如果只是想知道一个QuerySet是否为空,而不想获取QuerySet中的每个元素,那就用exists(),它要比len()、count()、和直接进行if判断效率高。如果只想知道一个QuerySet有多大,而不想获取QuerySet中的每个元素,那就用count();如果已经从数据库获取到了QuerySet,那就用len()

No12 contains/startswith/endswith–对应like

字段名加双下划线,除了它,还有icontains,即Case-insensitive contains,这个是大小写不敏感的

No13 in–对应in

字段名加双下划线

No14 gt/gte/lt/lte–对应于>,>=,<,<=

字段名加双下划线

No15 range–对应于between and

字段名加双下划线,range后面值是列表

No16 isnull–对应于is null

Entry.objects.filter(pub_date__isnull=True)对应的SQL为SELECT … WHERE pub_date IS NULL;

No17 QuerySet切片–对应于limit

QuerySet的索引只能是非负整数,不支持负整数,所以QuerySet[-1]错误

a=Entry.objects.all()[5:10]

b=len(a) #执行Entry.objects.all()[5:8],对于不同的数据库,SQL语句不同,Sqlite 的SQL语句为select * from tablename limit 3 offset 5; MySQL的SQL语句为select * from tablename limit 3,5

 

Django对数据库的封装——QuerySet

Django对数据库的操作分用到三个类:Manager、QuerySet、Model。Manager的主要功能定义表级方法(表级方法就是影响一条或多条记录的方法),我们可以以models.Manager为父类,定义自己的manager,增加表级方法;QuerySet是Manager的方法返回的,是一个可遍历结构,包含一个或多个元素,每个元素都是一个Model 实例,它里面的方法也是表级方法,前面说了,Django给我们提供了增加表级方法的途径,那就是自定义manager类,而不是自定义QuerySet类,一般的我们没有自定义QuerySet类的必要;Model是一条记录的类,它的功能很强大,里面包含外键实体等,它的方法都是记录级方法(都是实例方法,无类方法),不要在里面定义类方法,比如计算记录的总数,查看所有记录,这些应该放在自定义的manager类中。

1.QuerySet

1.1 简介

每个Model都有一个默认的manager类,名为objects,QuerySet有两种来源:通过manager的方法得到、通过QuerySet的方法得到。QuerySet的最初来源就是通过manager的方法。mananger的方法和QuerySet的方法大部分同名,同意思,如filter(),update()等,但也有些不同,如manager有create()、get_or_create(),而QuerySet有delete()等。一个QuerySet包含一个或多个model instance。QuerySet类似于Python中的list,list的一些方法QuerySet也有,比如切片,遍历。

>>> from gk_user.models import UserEx
>>> type(UserEx.objects)
<class 'django.db.models.manager.Manager'>
>>> a = UserEx.objects.all()
>>> type(a)
<class 'django.db.models.query.QuerySet'>

QuerySet是延迟获取的,只有当用到这个QuerySet时(有下面几种情况),才会求值,即查询数据库。另外,查询到的QuerySet又是缓存的,当再次使用同一个QuerySet时,并不会再查询数据库,而是直接从缓存获取(不过,有一些特殊情况)。一般而言,当对一个没有求值的QuerySet进行的运算返回的不是QuerySet、ValuesQuerySet、ValuesListQuerySet、Model实例时,一般的会立即查询数据库;反之,运算返回的是QuerySet、ValuesQuerySet、ValuesListQuerySet、Model实例时,一般不会查询数据库。下面介绍几种(并非全部)对QuerySet求值的场景。

class Blog(models.Model):
    name = models.CharField(max_length=100)
    tagline = models.TextField()
def __unicode__(self):
    return self.name
     
class Author(models.Model):
    name = models.CharField(max_length=50)
    email = models.EmailField()
def __unicode__(self):
    return self.name
    
class Entry(models.Model):
    blog = models.ForeignKey(Blog)
    headline = models.CharField(max_length=255)
    body_text = models.TextField()
    pub_date = models.DateField()
    mod_date = models.DateField()
    authors = models.ManyToManyField(Author)
    n_comments = models.IntegerField()
    n_pingbacks = models.IntegerField()
    rating = models.IntegerField()
def __unicode__(self):
    return self.headline

我们以上面的models为例。

I 遍历

a = Entry.objects.all()
for e in a:
    print (e.headline)

当遍历时,先从数据库执行查询select * from Entry得到a,然后再遍历a。注意:这里只是查询Entry表,返回的a的每条记录只包含Entry表的字段值。不管Entry的model中是否有onetoone、onetomany、manytomany字段,都不会关联查询。这遵循的是数据库最少读写原则。我们修改一下代码,如下,遍历一开始也是先执行查询得到a,但当执行print (e.blog.name)时,还需要再次查询数据库获取blog实体。

from django.db import connection

l = connection.queries #l是一个列表,记录SQL语句

a = Entry.objects.all()

for e in a:

print (e.blog.name)

len(l)

遍历时,每次都要查询数据库,l长度每次增1,Django提供了方法可以在查询时返回关联表实体,如果是onetoone或onetomany,那用select_related,不过对于onetomany,只能在主表(定义onetomany关系的那个表)的manager中使用select_related方法,即通过select_related获取的关联对象是model instance,而不能是QuerySet,如下,e.blog就是model instance。对于onetomany的反向和manytomany,要用prefetch_related,它返回的关联对象是QuerySet。

a = Entry.objects.select_related(‘blog’)

for e in a:

print (e.blog.name)

len(l)

可以看到从开始到结束,l的长度只增加1。另外,通过查询connection.queries[-1]可以看到Sql语句用了join。

II 切片

切片不会立即执行,除非显示指定了步长,如a= Entry.objects.all()[0:10:2],步长为2。

III 序列化,即Pickling

序列化QuerySet很少用

IV repr()

和str()功能相似,将对象转为字符串,很少用。

V len()

计算QuerySet元素的数量,并不推荐使用len(),除非QuerySet是求过值的(即evaluated),否则,用QuerySet.count()获取元素数量,这个效率要高。

VI list()

将QuerySet转为list

VII bool() ,判断是否为空

if Entry.objects.filter(headline="Test"):
    print("There is at least one Entry with the headline Test")

同样不建议这种方法判断是否为空,而应该使用QuerySet.exists(),查询效率高

1.2 QuerySet的方法

数据库的常用操作就四种:增、删、改、查,QuerySet的方法涉及删、改、查。后面还会讲model对象的方法,model方法主要是增、删、改、还有调用model实例的字段。

(1) 删delete()

原型:delete()

返回:None

相当于delete-from-where, delete-from-join-where。先filter,然后对得到的QuerySet执行delete()方法就行了,它会同时删除关联它的那些记录,比如我删除记录表1中的A记录,表2中的B记录中有A的外键,那同时也会删除B记录,那ManyToMany关系呢?不清楚。实际中,delete用的很少,对于没有用的记录,一般是update为不可用。由于有些数据库,如Sqlite不支持delete与limit连用,所以在这些数据库对QuerySet的切片执行delete()会出错。如

>>> a = UserEx.objects.filter(is_active=False)
>>> b = a[:3]
>>> b.delete() #执行时会报错

解决:UserEx.objects.filter(pk__in=b).delete()

in后面可以是一个QuerySet,见 https://docs.djangoproject.com/en/1.6/ref/models/querysets/#in

(2) 改 update()

批量修改,返回修改的记录数。不过update()中的键值对的键只能是主表中的字段,不能是关联表字段,如下:

Entry.objects.update(blog__name='foo') #这是错误的,无法修改关联表字段,只能修改Entry
#表中的字段
Entry.objects.filter(blog__name='foo').update(comments_on=False) #正确,filter中的字段可以
#是关联表

最好的方法是先filter,查询出QuerySet,然后再执行QuerySet.update()。

由于有些数据库,不支持update与limit连用,所以在这些数据库对QuerySet的切片执行update()会出错。

(3)查询 filter(**kwargs)、exclude(**kwargs)、get(**kwargs)

相当于select-from-where,select-from-join-where,很多网站读数据库操作最多。可以看到,filter()的参数是变个数的键值对,而不会出现>,<,!=等符号,这些符号分别用__gt,__lt,~Q或exclude(),不过对于!=,建议使用Q查询,更不容易出错。可以使用双下划线对OneToOne、OneToMany、ManyToMany进行关联查询和反向关联查询,而且方法都是一样的,如:

>>> Entry.objects.filter(blog__name='Beatles Blog') 
#限定外键表的字段下面是反向连接,不过要注意,这里不是entry_set,
#entry_set是Blog 实例的一个属性,代表某个Blog对象的关联的所有entry,
#而QuerySet的方法中反向连接是直接用model的小写,不要把两者搞混。
#反过来也是一样,如果想根据现有对象找原对象,将原类型对象小写即可。
>>> Blog.objects.filter(entry__headline__contains='Lennon')
>>> Blog.objects.filter(entry__authors__name='Lennon') #ManyToMany关系,反向连接
>>> myblog = Blog.objects.get(id=1)
>>> Entry.objects.filter(blog=myblog) 
#正向连接。与下面一句等价,既可以用实体,也可以用实体的主键。
#其实即使用实体,也是只用实体的主键而已。这两种方式对OneToOne、
#OneToMany、ManyToMany的正向、反向连接都适用。
>>> Entry.objects.filter(blog=1)
>>> myentry = Entry.objects.get(id=1)
>>> Blog.objects.filter(entry=myentry) #ManyToMany反向连接。与下面一句等价
>>> Blog.objects.filter(entry=1)

OneToOne的关系也是这样关联查询,可以看到,Django对OneToOne、OneToMany、ManyToMany关联查询及其反向关联查询提供了相同的方式,真是牛逼啊。对于OneToOne、OneToMany的主表,也可以使用下面的方式

Entry.objects.filter(blog_id=1),因为blog_id是数据库表Entry的一个字段, 这条语句与Entry.objects.filter(blog=1)生成的SQL是完全相同的,都是只在Entry表查询,没用join,django为我们封装了对数据库的操作,所以建议只用model中的字段,而blog_id不是model定义的字段,不建议用。

与filter类似的还有exclude(**kwargs)方法,这个方法是剔除,相当于select-from-where not,select-from-join-where not。可以使用双下划线对OneToOne、OneToMany、ManyToMany进行关联查询和反向关联查询,方法与filter()中的使用方法相同。

>>> Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3), headline=’Hello’)

转为SQL为

SELECT *
FROM Entry
WHERE NOT (pub_date > '2005-1-3' AND headline = 'Hello')

SQLite数据库管理的相关命令

1.创建数据库

启动命令行,通过输入如下命令打开Shell模式的CLP:
[codesyntax lang=”text”]

sqlite3 test.db

[/codesyntax]
虽然我们提供了数据库名称,但如果该数据库不存在,SQLite实际上就未创建该数据库,直到在数据库内部创建一些内容时,SQLite才创建该数据库。

2.创建数据表

[codesyntax lang=”text”]

sqlite> create table Member(id integer primary key, name text, age integer,addr text);

[/codesyntax]
[codesyntax lang=”text”]

注:id为主键,该列默认具备自动增长的属性。

[/codesyntax]

3.插入数据

[codesyntax lang=”text”]

sqlite> insert into Member values(0,'wwl',21,'上海');//id=0的列必须不存在,否则会出错

或者
sqlite> insert into Member(name,age,addr) values('wwl',21,'上海');

[/codesyntax]

4.查询数据

sqlite>.mode column
sqlite>.headers on
sqlite> select * from Member;

注:select语句前面的两个命令(.headers和.mode)用于改善显示格式,可以不要。

5.创建视图和索引

[codesyntax lang=”text”]

sqlite> create view schema as select * from Member;
sqlite> create index Member_Idx on Member(id)

[/codesyntax]

6.导出数据

使用.dump命令可以将数据库对象导出成SQL格式。不带任何参数时,.dump将整个数据库导出为数据库定义语言(DDL)和数据库操作语言(DML)命令,适合重新创建数据库对象和其中的数据。如果提供了参数,Shell将参数解析作为表名或视图,导出任何匹配给定参数的表或视图,那些不匹配的将被忽略。

默认情况下.dump 命令的输出定向到屏幕。如:.dump

如果要将输出重定向到文件,请使用.dump[filename]命令,此命令将所有的输出重定向到指定的文件中。若要恢复到屏幕的输出,只需要执行.output stdout命令就OK了。
[codesyntax lang=”text”]

sqlite>.output file.sql 
sqlite>.dump 
sqlite>.output stdout

[/codesyntax]
注:如果file.sql不存在,将在当前工作目录中创建该文件。如果文件存在,它将被覆盖。

7.导入数据

有两种方法可以导入数据,用哪种方法取决于要导入的文件格式。如果文件由SQL语句构成,可以使用.read命令导入文件中包含的命令。如果文件中包含由逗号或其他分隔符分割的值(comma-swparated values,CSV)组成,可使用.import[file][table]命令,此命令将解析指定的文件并尝试将数据插入到指定的表中。

.read命令用来导入.dump命令创建的文件。如果使用前面作为备份文件所导出的file.sql,需要先移除已经存在的数据库对象,然后用下面的方法重新导入:
[codesyntax lang=”text”]

sqlite>drop table Member; 
sqlite>drop view schema; 
sqlite>.read file.sql

[/codesyntax]

8.备份数据库

有两种方式可以完成数据库的备份,具体使用哪一种取决于你希望的备份类型。SQL转储许是移植性最好的备份。

生成转储的标准方式是使用CLP.dump命令:sqlite3 test.db .dump >test.sql

在Shell中,可以将输出重定向到外部文件,执行命令,恢复到屏幕输出,如:
[codesyntax lang=”text”]

sqlite>.output file.sql 
sqlite>.dump 
sqlite>.output stdout 
sqlite>.exit

[/codesyntax]
同样,容易将SQL转储作为CLP的输入流实现数据库导入:

sqlite3 test.db <test.sql

备份二进制数据库知识比复制文件稍多做一点工作。备份之前需要清理数据库,这样可以释放一些已删除对象不再使用的空间。这数据库文件就会变小,因此二进制的副本也会较小:
[codesyntax lang=”text”]

sqlite3 test.db vacuum
cp test.db test.Backup

[/codesyntax]

9.其它命令

[codesyntax lang=”text”]

sqlite>select last_insert_rowid();  //获得最后插入的自动增长量值 
sqlite>.tabes            //返回所有的表和视图 
sqlite>.indices Member       //查看一个表的索引 
sqlite>.schema Member       //得到一个表或视图的定义(DDL)语句,如果没有提供表名,则返回所有数据库对象(table,view,index,triger)的定义语句

[/codesyntax]

DB2 IMPORT 详解

IMPORT 实用程序概述

IMPORT 实用程序用一个输入文件将数据填充到一个表中,输入文件的文件类型可以是 ASC、DEL、IXF 或 WSF。目标是一个表、一个类型化表(typed table)或者一个视图。但是,不能导入到系统表、临时表和物化查询表。建议使用 MESSAGES 子句,以便记录错误、警告和包含有用信息的消息。

要想成功地导入数据,必须拥有 SYSADM 或 DBADM 权限,或者目标表或数据库上的底层特权(SELECT、INSERT、CONTROL 或 CREATETAB),这取决于使用什么选项。为了将数据导入到一个包含受保护的行和列的表中,必须拥有允许对表中所有受保护数据进行写访问的 LBAC 凭证。此外,将数据导入包含受保护行的表时,要求您的 LBAC 凭证是保护表的安全策略的一部分。

下面显示的 IMPORT 命令具有五个不同的选项:

[codesyntax lang=”sql”]

IMPORT FROM file_name OF file_type
   MESSAGES message_file
   [ INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE | CREATE ]
   INTO target_table_name
   

[/codesyntax]

INSERT 选项将导入的数据插入表中。目标表必须已经存在。 INSERT_UPDATE 将数据插入表中,或者更新表中具有匹配主键的行。目标表必须已经存在,并且定义了一个主键。 REPLACE 选项删除所有已有的数据,并将导入的数据插入到一个已有的目标表中。 使用 REPLACE_CREATE 选项时,如果目标表已经存在,则导入实用程序删除已有的数据,并插入新的数据,就像 REPLACE 选项那样。如果目标表还没有定义,那么首先创建这个表以及它的相关索引,然后再导入数据。正如您可能想像的那样,输入文件必须是 PC/IXF 格式的文件,因为那种格式包含对导出表的结构化描述。如果目标表是被一个外键引用的一个父表,那么就不能使用 REPLACE_CREATE。 CREATE 选项首先创建目标表和它的索引,然后将数据导入到新表中。该选项惟一支持的文件格式是 PC/IXF。还可以指定新表所在表空间的名称。 例子:

[codesyntax lang=”sql”]

IMPORT FROM emp.ixf OF IXF
   MESSAGES msg.out
   CREATE INTO employee IN datatbsp INDEX IN indtbsp

[/codesyntax]

IMPORT 选项

IMPORT 基本上是用于成批插入数据的一个实用程序。这种成批插入操作就像一般的插入语句一样,也涉及到活动的日志记录、索引的更新、参照完整性检查和表约束检查。默认情况下,IMPORT 只在操作结束时提交一次。如果将大量的行一次性导入或插入到表中,那么需要有足够的事务记录用于回滚和恢复。此外也可以采用周期性的提交,以防日志写满。通过定期地提交插入,还可以减少导入操作期间出现失败时丢失的行数。COMMITCOUNT 选项规定在导入一组记录后强制执行 COMMIT。还可以指定 AUTOMATIC 选项,该选项允许导入实用程序在内部决定何时需要执行提交。该实用程序将考虑发出一个提交命令,以避免日志写满或者避免锁升级。下面是关于如何使用 COMMITCOUNT 选项的一个例子:

[codesyntax lang=”sql”]

IMPORT FROM myfile.ixf OF IXF
   COMMITCOUNT 500
   MESSAGES msg.out
   INSERT INTO newtable
   

[/codesyntax]

如果由于某种原因导致以上命令在执行期间遭到失败,那么可以使用消息文件来确定成功导入且已提交的最后一行。然后,可以使用 RESTARTCOUNT 选项重新开始导入。注意,SKIPCOUNT 选项的行为与 RESTARTCOUNT 是相同的。在下面的命令中,该实用程序在开始 IMPORT 操作之前,将忽略前 30,000 条记录。

[codesyntax lang=”sql”]

IMPORT FROM myfile.ixf OF IXF
   COMMITCOUNT 500 RESTARTCOUNT 30000 ROWCOUNT 100000
   MESSAGES msg.out
   INSERT INTO newtable

[/codesyntax]

注意,这个例子中还使用了 ROWCOUNT 选项。该选项指定要导入的物理记录的条数。由于使用了 RESTARTCOUNT 选项,导入实用程序将忽略前 30,000 条记录,并且将剩下的 100,000 条记录导入到表中。

默认情况下,在插入任何行之前,导入实用程序将获得目标表上的一个排它锁。一旦导入完成,这个排它锁将被释放。这是 ALLOW NO ACCESS 选项的行为。为了允许并发应用程序访问表数据,可以使用 ALLOW WRITE ACCESS 选项。注意,该选项与 REPLACE、CREATE 或 REPLACE_CREATE 导入选项不兼容。下面是关于 ALLOW WRITE ACCESS 选项的一个例子。

[codesyntax lang=”sql”]

IMPORT FROM myfile.ixf OF IXF
   ALLOW WRITE ACCESS
   MESSAGES msg.out
   INSERT INTO newtable

[/codesyntax]

导入 XML 数据

为了导入 XML 文件,可以使用 XML FROM 选项指定 XML 文件所在的一个或多个路径。否则,导入实用程序将在当前目录中查找 XML 文件。您可以选择如何解析 XML 文档;是去掉空白还是保留空白。如果没有指定 XMLPARSE 选项,那么将根据 CURRENT XMLPARSE OPTION 专用寄存器来决定对 XML 文档的解析行为。下面是关于 XML FROM 和 XMLPARSE 选项的一个例子。

[codesyntax lang=”sql”]

IMPORT FROM myfile.ixf OF IXF
   XML FROM d:\xmlpath
   XMLPARSE PRESERVE WHITESPACE
   MESSAGES msg.out
   INSERT INTO newtable
   

[/codesyntax]

当插入或更新一个 XML 文档时,您可能想确定 XML 文档的结构、内容和数据类型是否有效。导入实用程序还通过 XMLVALIDATE 选项提供了对 XML 验证的支持。下面是可用的三种方法。

USING XDS — 回想一下,您可以导出 XML 模式信息并将它存储在 XML Data Specifier (XDS) 的 SCH 属性中。SCH 属性的值将用于执行验证。如果在 XDS 中没有 SCH 属性,则考虑 DEFAULT、IGNORE 或 MAP 三者之中的一个值。 USING SCHEMA schema-sqlid — 使用这个子句中指定的 XML 模式。 USING SCHEMALOCATION HINTS — 根据源 XML 文档中 XML 模式位置提示所标识的模式来验证 XML 文档。

[codesyntax lang=”sql”]

IMPORT FROM myfile.ixf OF IXF
   XML FROM d:\xmlpath
   XMLPARSE PRESERVE WHITESPACE
   XMLVALIDATE USING XDS
   DEFAULT S1.SCHEMA_A
   IGNORE (S1.SCHEMA_X, S1.SCHEMA_Y, S1.SCHEMA_Z)
   MAP (S1.SCHEMA_A, S1.SCHEMA_B)
   COMMITCOUNT 500 RESTARTCOUNT 30000
   MESSAGES msg.out
   INSERT INTO newtable
   

[/codesyntax]

前面的 IMPORT 命令将:

插入 myfile.ixf 和 d:\xmlpath 下的 XML 文件中的数据。 当解析 XML 文档时,保留空白。 使用 XDS 的 SCH 属性标识的模式信息对每个 XML 文档进行验证。但是,如果用于任何特定行的 XDS 没有包含 SCH 属性,那么使用 S1.SCHEMA_A。 如果 SCH 属性被指定为 S1.SCHEMA_X 或 S1.SCHEMA_Y 或 S1.SCHEMA_Z,那么对于导入的 XML 文档不执行验证。 如果 SCH 属性被指定为 S1.SCHEMA_A,它将被映射到 S1.SCHEMA_B。注意,尽管 DEFAULT 子句指定了 S1.SCHEMA_A,但是后面的任何映射都将不会执行。 每导入 500 行之后,导入实用程序将发出一次提交命令。 导入操作从第 30,001 条记录开始。前 30,000 条记录被忽略。 任何错误、警告和包含信息的消息都写到 msg.out 文件中。 将新数据插入(或附加)到 newtable 中。 这个例子只是让您对如何验证导入的 XML 文档有一些认识。DB2 Information Center 中还有更多的例子来演示 XMLVALIDATE 选项的威力。

文件类型修饰符

IMPORT 实用程序还支持五种文件类型修饰符来定制导入操作。在 DB2 Command Reference 的 IMPORT 小节下有这些修饰符的一个完整的列表。下面列出了其中一些修饰符:

compound=x 使用非原子复合 SQL 插入数据。x 是每次将尝试的语句数量。 indexschema=schema 使用索引创建期间指定的用于索引的模式。 striptblanks 将数据装载到可变长度字段时,截去结尾空白。 lobsinfile 表明将导入 LOB 数据。导入实用程序将检查 LOBS FROM 子句,以获得输入 LOB 文件的路径。

下面是使用这些文件类型修饰符的一个例子:

[codesyntax lang=”sql”]

IMPORT FOR inputfile.asc OF ASC
   LOBS FROM /u/db2load/lob1, /u/db2load/lob2
   MODIFIED BY compount=5 lobinsfile
   INSERT INTO newtable
   

[/codesyntax]

使用 Control Center 执行导入

Control Center 提供了易于使用的图形界面来执行导入操作。这个界面中还提供了前一屏中讨论的所有导入选项和文件修饰符。

常见数据库分页技术实现

随着数据库的发展,如今的数据库可以储存大量的数据,内存也是越来越大,但是无论您的内存多大,内存总是显得不够用,这时就要涉及到分页,下文中将为大家带来详细的数据库分页操作。

 1.Oracle:

[codesyntax lang=”sql”]
select * from ( select row_.*, rownum rownum_ from ( query_SQL ) row_
where rownum =< max) where rownum_ >= min
[/codesyntax]

 2.SQL Server:

[codesyntax lang=”sql”]
select top @pagesize * from tablename where id not in
(select top @pagesize*(@page-1) id from tablename order by id) order by id
[/codesyntax]

 3.MySQL

[codesyntax lang=”sql”]
select * from tablename limit position, counter
[/codesyntax]

 4.DB2

[codesyntax lang=”sql”]
select * from (select *,rownumber() as ROW_NEXT from tablename)
where ROW_NEXT between min and max
[/codesyntax]

 分页方案一:(利用Not In和SELECT TOP分页)效率次之

语句形式:

[codesyntax lang=”sql”]
SELECT TOP 10 * FROM TestTable
WHERE
(ID NOT IN (SELECT TOP 20  id FROM  TestTable  ORDERBY  id))   ORDERBYID
SELECT  TOP 页大小 * FROM TestTable
WHERE( ID NOT IN (SELECT  TOP  每页大小-1*待查询页数-1  id  FROM  表
ORDERBY  id)) ORDERBYID
[/codesyntax]
思路:先查询出待查询页之前的全部条数的id,查询ID不在这些ID中的指定数量条数。

 分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高

语句形式:

[codesyntax lang=”sql”]
SELECT  TOP  10 *   FROM  TestTable
WHERE(ID>(SELECT MAX(id) FROM(SELECT TOP20 id  FROM  TestTable ORDERBYid)
AS T))ORDERBY ID
SELECT  TOP  页大小* FROM  TestTable
WHERE(ID>(SELECT MAX(id) FROM(SELECT TOP 每页大小*待查询页数-1  id FROM 表
ORDERBY id)AS T)) ORDERBY ID
[/codesyntax]
思路:先获得待查询页的之前全部条数id,获得它们当中最大的ID号,以此最大ID号为标志,查找比这个ID号大的指定条数。

 分页方案三:

[codesyntax lang=”sql”]
SELECT TOP PageSize * FROM
(SELECT TOP nPage*PageSize * from YOURTABLE order by id) as a order by id desc
SELECT TOP 每页条数 * FROM
(SELECT TOP 待查询页*每页条数) * from YOURTABLE order by id)as a order by id desc
[/codesyntax]
思路:先正排序查询出待查询页之前(包括当前页)的全部条数,然后将其倒排序,取指定条数。

MySQL常用备份方法

mysql按照备份恢复方式分为逻辑备份和物理备份。逻辑备份是备份sql语句,在恢复的时候执行备份的sql语句实现数据库数据的重现,物理备份就是备份数据文件了,比较形象点就是cp下数据文件,但真正备份的时候自然不是的cp这么简单。

这2种备份各有优劣,一般来说,物理备份恢复速度比较快,占用空间比较大,逻辑备份速度比较慢,占用空间比较小

下面介绍以下3种常用的备案方法

一、mysqldump工具备份

mysqldump由于是mysql自带的备份工具,所以也是最常用的mysql数据库的备份工具。支持基于InnoDB的热备份。但由于是逻辑备份,所以速度不是很快,适合备份数据量比较小的场景。
mysqldump完全备份+二进制日志 —>实现时间点恢复

温备:

在使用MyISAM引擎中,只能使用温备份,这时候要防止数据的写入,所以先加上读锁

这时候可以进入数据库手动加读锁。这样比较麻烦,在mysqldump工具中直接有一个加锁的选项

[codesyntax lang=”sql”]
mysqldump –databases mydatabase –lock-all-tables –flush-logs> /tmp/backup-`date +%F-%H-%M`.sql
[/codesyntax]

如果是针对某张表备份,只要在数据库名称后面加上表名称就行了

这里注意,要实现时间点的恢复,加上–flush-logs选项,在使用备份文件恢复后,然后再基于二进制日志进行时间点的恢复

时间点的恢复方法

[codesyntax lang=”sql”]
mysqlbinlog mysql-bin.000000x > /tmp/PointTime.sql
[/codesyntax]

然后用mysql命令导入这个sql脚本就行了

热备:如果使用的是InnoDB引擎,就不必进行对数据库加锁的操作,加一个选项既可以进行热备份:–single-transaction

[codesyntax lang=”sql”]
mysqldump –databases mydb –single-transaction –flush-logs –master-data=2 > /tmp/backup-`date +%F-%H-%M`.sql”
[/codesyntax]

注意:
恢复的时刻关闭二进制日志
mysql>set sql_log_bin=0;
因为这是基于逻辑备份方式,在恢复日志时会执行sql语句插入数据,而恢复时候插入数据的日志没有意义。

二、基于LVM快照备份

在物理备份中 ,有基于文件系统的物理备份(LVM的快照),也可以直接用tar之类的命令打包。但这些只能进行冷备份
不同的存储引擎能备份的级别也不一样,MyISAM能备份到表级别,而InnoDB不开启每表一文件的话就只能备份整个数据库。

下面就介绍下使用LVM的快照功能进行备份:

1. 为了安全 在数据库上施加读锁

[codesyntax lang=”sql”]
mysql>FLUSH TABLES WITH READ LOCK;
[/codesyntax]

2. 刷新一下二进制日志,便于做时间点恢复

[codesyntax lang=”sql”]
mysql>FLUSH LOGS;
[/codesyntax]

3. 创建快照卷

[codesyntax lang=”sql”]
lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata
[/codesyntax]

4. 进入数据库释放读锁

[codesyntax lang=”sql”]
UNLOCK TABLES;
[/codesyntax]

5. 挂载快照卷进行备份

[codesyntax lang=”sql”]
mount –r /dev/myvg/data-snap /mnt/snap
[/codesyntax]

6. 对/mnt/snap下的文件进行打包备份
还原的时候,关闭mysqld,然后备份二进制日志后将原来备份的文件还原进去,然后通过二进制日志还原到出错的时间点(通过二进制还原时间点的时候不要忘了暂时关闭二进制日志)

三、使用percona提供的xtrabackup(推荐)

支持InnoDB的物理热备份,支持完全备份,增量备份,而且速度非常快,而且支持InnoDB引擎的数据在不同数据库迁移
为了让xtrabackup支持更多的功能扩展,配置InnoDB每表一个文件的功能
在my.cnf的mysqld中加入此项: innodb_file_per_table=1
此项不启用将不支持备份单独的表
但如果之前没有启用这个选项,要实现单表一文件的话,可以用mysqldump导出数据,然后启用该选项,恢复回去后就是单表一文件了

首先下载xtrabackup,下载地址:http://www.percona.com/software/percona-xtrabackup,可以直接下载rpm包安装即可。

xtrabackup有完全备份,增量备份和部分备份(前面开启innodb每表一文件,就是为了此功能)

1.完全备份整个数据库

[codesyntax lang=”sql”]
innobackupex –user=root –password=123456 /tmp/backup
[/codesyntax]

此时会在/tmp/backup目录下生成以时间为名的文件夹,里面是备份文件

在这里,备份的数据还不能直接用来还原。因为备份数据中会含有尚未提交的事务或者未同步到数据文件中的事物。这里需要用prepare回滚事物使数据文件处于一致性。

[codesyntax lang=”bash”]
innobackupex –apply-log /tmp/backup/dir
[/codesyntax]

处理完成后才能用来还原数据,用此命令还原

[codesyntax lang=”bash”]
innobackupex –copy-back /tmp/backup/dir
[/codesyntax]

要实现时间点还原,还是需要使用二进制日志

2.增量备份

增量备份支持Innodb,对于MyISAM只能完全备份

[codesyntax lang=”bash”]
innobackupex –incremental /tmp/backup/incremental –incremental-basedir=/tmp/backup/dir
[/codesyntax]

在进行一次增量备份–incremental-basedir要指向上一次增量备份的目录

如果要进行还原,先进行prepare处理

这里处理的方式,将备份合并

[codesyntax lang=”bash”]
innobackupex –apply-log –redo-only /tmp/backup/dir
innobackupex –apply-log –redo-only /tmp/backup/dir –incremental-dir=/tmp/backup/incremental
[/codesyntax]

3. 使用全备份还原

至于差异备份,只要每次将basedir指向完全备份文件夹就行了

最后再废话一句:要实现时间点还原,是需要使用二进制日志的,所以备份好二进制日志至关重要。除非在恢复时间点和上一次备份时间点这段时间的数据对你来说无所谓。

本文参考至: 威易网

MySQL:Can’t connect to local MySQL server through socket

今天启动MySQL的时候 突然间提示:

Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

上网搜索了一下解决方法,结果发现这个问题很有可能是由于MySQL服务器未启动导致的 于是,赶紧用service命令查看一下mysqld的启动情况

[codesyntax lang=”bash”]

service mysqld status

[/codesyntax]

结果的确发现服务未启动  于是赶紧将服务启动,结果就可以正常使用了

[codesyntax lang=”bash”]

service mysqld start

[/codesyntax]

不过  后来在网上查看了一下 ,结果发现出现这个问题也有可能是在安装MySQL的时候mysql.sock文件出现问题,如使用上述方法仍旧出现报错的话,则需要查看一下mysql.sock文件的情况了

[codesyntax lang=”bash”]

mysql -u [user] -p -S /var/lib/mysql/mysql.sock

[/codesyntax]

好了 就这样问题就完全解决了~~

db2look导出ddl 详细用法

最近需要对DB2中的数据进行数据备份  虽然可以用db2backup进行整个数据库的全备份
但是如果只要备份其中的部分表项或者是要进行跨平台数据备份的话 使用db2look未尝不是一个好的选择
而在进行数据备份的时候 第一步需要做的事情就是导出表的表结构  而在此时使用db2look则是一个最优的方式

导出所有对象的ddl:db2look -d -e -a -l -o dbname.ddl
导出特定表ddl:db2look -d -e -t -o tableName.ddl
说明:(d指定数据库,e表示要导出ddl,a表示所有对象,l表示表空间及缓冲池对象,o表示DDL文件的输出地址)

好吧,话不多说 DB2的db2look命令诠释如下:
db2look 版本 8.2
db2look:生成 DDL 以便重新创建在数据库中定义的对象
语法:
[codesyntax lang=”text”]

db2look -d DBname [-e] [-u Creator] [-z Schema] [-t Tname1 Tname2...TnameN] [-tw Tname] [-h] [-o Fname] [-a]
[-m] [-c] [-r] [-l] [-x] [-xd] [-f] [-fd] [-td x] [-noview] [-i userID] [-w password]
[-v Vname1 Vname2 ... VnameN]
[-wrapper WrapperName] [-server ServerName] [-nofed]
db2look -d DBname [-u Creator] [-s] [-g] [-a] [-t Tname1 Tname2...TnameN]
[-p] [-o Fname] [-i userID] [-w password]
db2look [-h]

-d: 数据库名称 这必须指定,不然怎么知道是需要对哪个表进行操作呢~~
-e: 抽取复制数据库所需要的 DDL 文件。此选项将生成包含 DDL 语句的脚本。
可以对另一个数据库运行此脚本以便重新创建数据库对象
此选项可以和 -m 选项一起使用
-u: 创建程序标识:若 -u 和 -a 都未指定,则将使用 $USER
如果指定了 -a 选项,则将忽略 -u 选项
-z: 模式名:如果同时指定了 -z 和 -a,则将忽略 -z
联合部分的模式名被忽略
-t: 生成指定表的统计信息
可以指定的表的数目最多为 30
-tw: 为名称与表名的模式条件(通配符)相匹配的表生成 DDL
当指定了 -tw 选项时,-t 选项会被忽略
-v: 只为视图生成 DDL,当指定了 -t 时将忽略此选项
-h: 更详细的帮助消息
-o: 将输出重定向到给定的文件名
如果未指定 -o 选项,则输出将转到 stdout
-a: 为所有创建程序生成统计信息
如果指定了此选项,则将忽略 -u 选项
-m: 在模拟方式下运行 db2look 实用程序
此选项将生成包含 SQL UPDATE 语句的脚本
这些 SQL UPDATE 语句捕获所有统计信息
可以对另一个数据库运行此脚本以便复制初始的那一个
当指定了 -m 选项时,将忽略 -p、-g 和 -s 选项
-c: 不要生成模拟的 COMMIT 语句
除非指定了 -m 或 -e,否则将忽略此选项
将不生成 CONNECT 和 CONNECT RESET 语句
省略了 COMMIT。在执行脚本之后,需要显式地进行落实。
-r: 不要生成模拟的 RUNSTATS 语句
缺省值为 RUNSTATS。仅当指定了 -m 时,此选项才有效
-l: 生成数据库布局:数据库分区组、缓冲池和表空间。
-x: 如果指定了此选项,则 db2look 实用程序将生成授权 DDL
对于现有已授权特权,不包括对象的原始定义器
-xd: 如果指定了此选项,则 db2look 实用程序将生成授权 DDL
对于现有已授权特权,包括对象的原始定义器
-f: 抽取配置参数和环境变量
如果指定此选项,将忽略 -wrapper 和 -server 选项
-fd: 为 opt_buffpage 和 opt_sortheap 以及其它配置和环境参数生成 db2fopt 语句。
-td: 将 x 指定为语句定界符(缺省定界符为分号(;))
应该与 -e 选项一起使用(如果触发器或者 SQL 例程存在的话)
-p: 使用明文格式
-s: 生成 postscript 文件
此选项将为您生成 postscript 文件
当设置了此选项时,将除去所有 latex 和 tmp ps 文件
所需的(非 IBM)软件:LaTeX 和 dvips
注意:文件 psfig.tex 必须在 LaTeX 输入路径中
-g: 使用图形来显示索引的页访存对
必须安装 Gnuplot,并且 <psfig.tex> 必须在您的 LaTeX 输入路径中
还将随 LaTeX 文件一起生成 <filename.ps> 文件
-i: 登录到数据库驻留的服务器时所使用的用户标识
-w: 登录到数据库驻留的服务器时所使用的密码
-noview: 不要生成 CREATE VIEW ddl 语句
-wrapper: 为适用于此包装器的联合对象生成 DDL
生成的对象可能包含下列各项:
包装器、服务器、用户映射、昵称、类型映射、
函数模板、函数映射和索引规范
-server: 为适用于此服务器的联合对象生成 DDL
生成的对象可能包含下列各项:
包装器、服务器、用户映射、昵称、类型映射、
函数模板、函数映射和索引规范
-nofed: 不要生成 Federated DDL
如果指定此选项,将忽略 -wrapper 和 -server 选项

[/codesyntax]

示例:

[codesyntax lang=”sql”]

 db2look -d TEST -u iprayz -e -o db2look.sql
– 这将生成由用户 iprayz 创建的所有表和联合对象的 DDL 语句
– db2look 输出被发送到名为 db2look.sql 的文件中

 db2look -d TEST -z myscm1 -e -o db2look.sql
– 这将为模式名为 MYSCM1 的所有表生成 DDL 语句
– 还将生成 $USER 创建的所有联合对象的 DDL。
– db2look 输出被发送到名为 db2look.sql 的文件中

 db2look -d TEST -u iprayz -m -o db2look.sql
– 这将生成 UPDATE 语句以捕获关于用户 iprayz 创建的表/昵称的统计信息
– db2look 输出被发送到名为 db2look.sql 的文件中

db2look -d TEST -u iprayz -e -wrapper W1 -o db2look.sql
– 这将生成由用户 iprayz 创建的所有表的 DDL 语句
– 还将生成适用于包装器 W1 的用户 iprayz 所创建所有联合对象的 DDL
– db2look 输出被发送到名为 db2look.sql 的文件中

 db2look -d TEST -u iprayz -e -server S1 -o db2look.sql
– 这将生成由用户 iprayz 创建的所有表的 DDL 语句
– 还将生成适用于服务器 S1 的用户 iprayz 所创建所有联合对象的 DDL
– db2look 输出被发送到名为 db2look.sql 的文件中

[/codesyntax]至此,表结构就出来了。
接下来要做的就是将数据填充到该表中了…..

提升大数据查询速度

1、对查询进行优化,应尽量避免全表扫描。首先应考虑在 where 及 order by 涉及的列上建立索引。

2、应尽量避免在 where 子句中对字段进行 null 值判断。否则将导致引擎放弃使用索引而进行全表扫描,如:

[codesyntax lang=”sql”]

select id from t where num is null

[/codesyntax]

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

[codesyntax lang=”sql”]

select id from t where num=0

[/codesyntax]

3、应尽量避免在 where 子句中使用!=或<>操作符。否则将引擎放弃使用索引而进行全表扫描。

4、应尽量避免在 where 子句中使用 or 来连接条件。否则将导致引擎放弃使用索引而进行全表扫描,如:

[codesyntax lang=”sql”]

select id from t where num=10 or num=20

[/codesyntax]

可以这样查询:

[codesyntax lang=”sql”]

select id from t where num=10
 union all
select id from t where num=20

[/codesyntax]

5、in 和 not in 也要慎用,否则会导致全表扫描,如:

[codesyntax lang=”sql”]

select id from t where num in(1,2,3)

[/codesyntax]

对于连续的数值,能用 between 就不要用 in 了:

[codesyntax lang=”sql”]

select id from t where num between 1 and 3

[/codesyntax]

6、下面的查询也将导致全表扫描:

[codesyntax lang=”sql”]

select id from t where name like '%abc%'

[/codesyntax]

若要提高效率,可以考虑全文检索。

7、如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

[codesyntax lang=”sql”]

select id from t where num=@num

[/codesyntax]

可以改为强制查询使用索引

[codesyntax lang=”sql”]

select id from t with(index(索引名)) where num=@num

[/codesyntax]

8、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

[codesyntax lang=”sql”]

select id from t where num/2=100

[/codesyntax]

应改为:

[codesyntax lang=”sql”]

select id from t where num=100*2

[/codesyntax]

9、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

[codesyntax lang=”sql”]

select id from t where substring(name,1,3)='abc'
    --name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0
    --‘2005-11-30’生成的id

[/codesyntax]

应改为:

[codesyntax lang=”sql”]

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate< '2005-12-1'

[/codesyntax]

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

12、不要写一些没有意义的查询,如需要生成一个空表结构:

[codesyntax lang=”sql”]

select col1,col2 into #t from t where 1=0

[/codesyntax]

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

[codesyntax lang=”sql”]

create table #t(...)

[/codesyntax]

13、很多时候用 exists 代替 in 是一个好的选择:

[codesyntax lang=”sql”]

select num from a where num in(select num from b)

[/codesyntax]

用下面的语句替换:

[codesyntax lang=”sql”]

select num from a where exists(select 1 from b where num=a.num)\

[/codesyntax]

14、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16、应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

17、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

18、尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

19、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

20、尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

21、避免频繁创建和删除临时表,以减少系统表资源的消耗。

22、临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

23、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

24、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

25、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

26、使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

27、与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29、尽量避免大事务操作,提高系统并发能力

30、尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

Shell访问MySQL数据库方法

原有搭建了两台Mysql主从数据库服务器

先准备每日从主服务器进行数据增量备份 然后在子机上进行数据增量恢复

而实现数据库的每日增量备份还原

 

主机数据库的备份比较好弄  直接mysqldump就可以了

但是子机上的还原 有那么一点小麻烦

上网搜索了一下 然后自己整理了一下用脚本访问数据库的两种方法:

1. 直接利用mysql的参数进行控制

[codesyntax lang=”bash”]

#!/bin/sh
#数据库基本信息
DBUSER=bak
DBPASSWD=backup
#数据库连接
mysql -u${DBUSER} -p${DBPASSWD}

[/codesyntax]
这种方法 只能是直接进入到数据库 但是后续的处理还需要进行二次编写

 

2. 直接编写expect脚本进行控制

首先下载expect,这个比较好处理  直接yum或者是sudo apt-get就可以了

然后就是编写脚本 也没有想象中的要困难
[codesyntax lang=”bash”]

#!/usr/bin/expect
spawn mysql -ubak -p
expect "Enter password:"
send "backup\n"
expect "mysql>"
send "select * from mysql.user;\n"
expect "mysql>"
send "show databases;\n"
#echo 正在显示用户数据
expect eof

[/codesyntax]
用第二种方法至少可以进行数据库的相对操作,毕竟expect本来就是进行交互式脚本运行的

 

当然还有其他的方式进行的  这个后续进行补充吧……

毕竟现在主要的是将任务给完成