MySQL
一、数据库操作
1、显示（查看）数据库（所有数据库）
1 SHOW DATABASES;
2、创建数据库
1 # 简明 2 create database test_db; 3 4 5 6 # utf-8 7 CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; 8 9 # gbk 10 CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
3、使用数据库（单个数据库）
1 USE db_name;
显示当前使用的数据库中所有表：SHOW TABLES;
4、查看当前使用数据库中某一张表的内容
1 desc test_table
5、用户管理
1 创建用户 2 create user '用户名'@'IP地址' identified by '密码'; 3 删除用户 4 drop user '用户名'@'IP地址'; 5 修改用户 6 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';; 7 修改密码 8 set password for '用户名'@'IP地址' = Password('新密码') 9 10 PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
6、授权管理
1 show grants for '用户'@'IP地址' -- 查看权限 2 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 3 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
1 grant all privileges on db1.tb1 TO '用户名'@'IP' 2 3 grant select on db1.* TO '用户名'@'IP' 4 5 grant select,insert on *.* TO '用户名'@'IP' 6 7 revoke select on db1.tb1 from '用户名'@'IP'
1 # 启动免授权服务端 2 mysqld --skip-grant-tables 3 4 # 客户端 5 mysql -u root -p 6 7 # 修改用户名密码 8 update mysql.user set authentication_string=password('666') where user='root'; 9 flush privileges;
二、数据表操作
1、创建表
1 create table 表名( 2 列名 类型 是否可以为空， 3 列名 类型 是否可以为空 4 )ENGINE=InnoDB DEFAULT CHARSET=utf8 5 6 # or 7 8 create table 表名( 9 列名 类型 是否可以为空， 10 列名 类型 是否可以为空 11 )
2、删除表
1 drop table 表名
3、清空表
1 delete from 表名 #第一种方式 2 truncate table 表名 #第二种方式
4、查看表中所有内容
desc table_name
5、修改表
1 添加列：alter table 表名 add 列名 类型 2 删除列：alter table 表名 drop column 列名 3 修改列： 4 alter table 表名 modify column 列名 类型; -- 类型 5 alter table 表名 change 原列名 新列名 类型; -- 列名，类型 6 7 添加主键： 8 alter table 表名 add primary key(列名); 9 删除主键： 10 alter table 表名 drop primary key; 11 alter table 表名 modify 列名 int, drop primary key; 12 13 添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段); 14 删除外键：alter table 表名 drop foreign key 外键名称 15 16 修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; 17 删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
三、表内容操作
1、增（内容）
1 insert into 表 (列名,列名...) values (值,值,值...) 2 insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) 3 insert into 表 (列名,列名...) select (列名,列名...) from 表
2、删（内容）
1 delete from 表 2 delete from 表 where id＝1 and name＝'alex'
3、改（内容）
1 update 表 set name ＝ 'alex' where id>1
4、查（内容）
1 select * from 表 2 select * from 表 where id > 1 3 select nid,name,gender as gg from 表 where id > 1
5、其他
1 a、条件 2 select * from 表 where id > 1 and name != 'alex' and num = 12; 3 4 select * from 表 where id between 5 and 16; 5 6 select * from 表 where id in (11,22,33) 7 select * from 表 where id not in (11,22,33) 8 select * from 表 where id in (select nid from 表) 9 10 b、通配符 11 select * from 表 where name like 'ale%' - ale开头的所有（多个字符串） 12 select * from 表 where name like 'ale_' - ale开头的所有（一个字符） 13 14 c、限制 15 select * from 表 limit 5; - 前5行 16 select * from 表 limit 4,5; - 从第4行开始的5行 17 select * from 表 limit 5 offset 4 - 从第4行开始的5行 18 19 d、排序 20 select * from 表 order by 列 asc - 根据 “列” 从小到大排列 21 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 22 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序 23 24 e、分组 25 select num from 表 group by num 26 select num,nid from 表 group by num,nid 27 select num,nid from 表 where nid > 10 group by num,nid order nid desc 28 select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid 29 30 select num from 表 group by num having max(id) > 10 31 32 特别的：group by 必须在where之后，order by之前 33 34 f、连表 35 无对应关系则不显示 36 select A.num, A.name, B.name 37 from A,B 38 Where A.nid = B.nid 39 40 无对应关系则不显示 41 select A.num, A.name, B.name 42 from A inner join B 43 on A.nid = B.nid 44 45 A表所有显示，如果B中无对应关系，则值为null 46 select A.num, A.name, B.name 47 from A left join B 48 on A.nid = B.nid 49 50 B表所有显示，如果B中无对应关系，则值为null 51 select A.num, A.name, B.name 52 from A right join B 53 on A.nid = B.nid 54 55 g、组合 56 组合，自动处理重合 57 select nickname 58 from A 59 union 60 select name 61 from B 62 63 组合，不处理重合 64 select nickname 65 from A 66 union all 67 select name 68 from B
ORM-sqlite
一、关于Django下数据库
1 django默认支持sqlite，mysql, oracle,postgresql数据库。
<1> sqlite
django默认使用sqlite的数据库，默认自带sqlite的数据库驱动 , 引擎名称：django.db.backends.sqlite3
<2> mysql
引擎名称：django.db.backends.mysql
2 mysql驱动程序
- MySQLdb(mysql python)
- mysqlclient
- MySQL
- PyMySQL(纯python的mysql驱动程序)
3 在django的项目中会默认使用sqlite数据库，在settings里有如下设置：
如果我们想要更改数据库，需要修改如下：
setting中设置：
1 DATABASES = { 2 3 'default': { 4 5 'ENGINE': 'django.db.backends.mysql', 6 7 'NAME': 'books', #你的数据库名称 8 9 'USER': 'root', #你的数据库用户名 10 11 'PASSWORD': '', #你的数据库密码 12 13 'HOST': '', #你的数据库主机，留空默认为localhost 14 15 'PORT': '3306', #你的数据库端口 16 17 } 18 19 }
注意：
1 NAME即数据库的名字，在mysql连接前该数据库必须已经创建，而上面的sqlite数据库下的db.sqlite3则是项目自动创建 2 3 USER和PASSWORD分别是数据库的用户名和密码。 4 5 设置完后，再启动我们的Django项目前，我们需要激活我们的mysql。 6 7 然后，启动项目，会报错：no module named MySQLdb 8 9 这是因为django默认你导入的驱动是MySQLdb，可是MySQLdb对于py3有很大问题，所以我们需要的驱动是PyMySQL 10 11 所以，我们只需要找到项目名文件下的__init__,在里面写入： 12 13 import pymysql 14 pymysql.install_as_MySQLdb() 15 16 问题解决！
表的操作(增删改查)：
1、增
1 from app01.models import * 2 3 #create方式一: Author.objects.create(name='Alvin') 4 5 #create方式二: Author.objects.create(**{"name":"alex"}) 6 7 #save方式一: author=Author(name="alvin") 8 author.save() 9 10 #save方式二: author=Author() 11 author.name="alvin" 12 author.save()
############# 一对一 一对多 ############# #一对多(ForeignKey): #方式一: 由于绑定一对多的字段,比如publish,存到数据库中的字段名叫publish_id,所以我们可以直接给这个 # 字段设定对应值: Book.objects.create(title='php', publisher_id=2, #这里的2是指为该book对象绑定了Publisher表中id=2的行对象 publication_date='2017-7-7', price=99) #方式二: # <1> 先获取要绑定的Publisher对象: pub_obj=Publisher(name='河大出版社',address='保定',city='保定', state_province='河北',country='China',website='http://www.hbu.com') OR pub_obj=Publisher.objects.get(id=1) # <2>将 publisher_id=2 改为 publisher=pub_obj #多对多(ManyToManyField()): author1=Author.objects.get(id=1) author2=Author.objects.filter(name='alvin')[0] book=Book.objects.get(id=1) book.authors.add(author1,author2) #等同于: book.authors.add(*[author1,author2]) book.authors.remove(*[author1,author2]) #------------------- book=models.Book.objects.filter(id__gt=1) authors=models.Author.objects.filter(id=1)[0] authors.book_set.add(*book) authors.book_set.remove(*book) #------------------- book.authors.add(1) book.authors.remove(1) authors.book_set.add(1) authors.book_set.remove(1) #注意: 如果第三张表是通过models.ManyToManyField()自动创建的,那么绑定关系只有上面一种方式 # 如果第三张表是自己创建的: class Book2Author(models.Model): author=models.ForeignKey("Author") Book= models.ForeignKey("Book") # 那么就还有一种方式: author_obj=models.Author.objects.filter(id=2)[0] book_obj =models.Book.objects.filter(id=3)[0] s=models.Book2Author.objects.create(author_id=1,Book_id=2) s.save() s=models.Book2Author(author=author_obj,Book_id=1) s.save()
2、删
1 >>> Book.objects.filter(id=1).delete() 2 (3, {'app01.Book_authors': 2, 'app01.Book': 1})
我们表面上删除了一条信息，实际却删除了三条，因为我们删除的这本书在Book_authors表中有两条相关信息，这种删除方式就是django默认的级联删除。
如果是多对多的关系： remove()和clear()方法：
1 #正向 2 book = models.Book.objects.filter(id=1) 3 4 #删除第三张表中和女孩1关联的所有关联信息 5 book.author.clear() #清空与book中id=1 关联的所有数据 6 book.author.remove(2) #可以为id 7 book.author.remove(*[1,2,3,4]) #可以为列表,前面加* 8 9 #反向 10 author = models.Author.objects.filter(id=1) 11 author.book_set.clear() #清空与boy中id=1 关联的所有数据
3、改
1 #---------------- update方法直接设定对应属性---------------- 2 models.Book.objects.filter(id=3).update(title="PHP") 3 ##sql: 4 ##UPDATE "app01_book" SET "title" = 'PHP' WHERE "app01_book"."id" = 3; args=('PHP', 3) 5 6 7 #--------------- save方法会将所有属性重新设定一遍,效率低----------- 8 obj=models.Book.objects.filter(id=3)[0] 9 obj.title="Python" 10 obj.save() 11 # SELECT "app01_book"."id", "app01_book"."title", "app01_book"."price", 12 # "app01_book"."color", "app01_book"."page_num", 13 # "app01_book"."publisher_id" FROM "app01_book" WHERE "app01_book"."id" = 3 LIMIT 1; 14 # 15 # UPDATE "app01_book" SET "title" = 'Python', "price" = 3333, "color" = 'red', "page_num" = 556, 16 # "publisher_id" = 1 WHERE "app01_book"."id" = 3;
4、查
1 #--------------------对象形式的查找-------------------------- 2 # 正向查找 3 ret1=models.Book.objects.first() 4 print(ret1.title) 5 print(ret1.price) 6 print(ret1.publisher) 7 print(ret1.publisher.name) #因为一对多的关系所以ret1.publisher是一个对象,而不是一个queryset集合 8 9 # 反向查找 10 ret2=models.Publish.objects.last() 11 print(ret2.name) 12 print(ret2.city) 13 #如何拿到与它绑定的Book对象呢? 14 print(ret2.book_set.all()) #ret2.book_set是一个queryset集合
5、重要 多表增删改查操作总结（一对一、一对多、多对多）
[留空] 待补充
6、双下划线只单表查询、多表查询
1 #---------------了不起的双下划线(__)之单表条件查询---------------- 2 3 # models.Tb1.objects.filter(id__lt=10, id__gt=1) # 获取id大于1 且 小于10的值 4 # 5 # models.Tb1.objects.filter(id__in=[11, 22, 33]) # 获取id等于11、22、33的数据 6 # models.Tb1.objects.exclude(id__in=[11, 22, 33]) # not in 7 # 8 # models.Tb1.objects.filter(name__contains="ven") 9 # models.Tb1.objects.filter(name__icontains="ven") # icontains大小写不敏感 10 # 11 # models.Tb1.objects.filter(id__range=[1, 2]) # 范围bettwen and 12 # 13 # startswith，istartswith, endswith, iendswith, 14 15 #----------------了不起的双下划线(__)之多表条件关联查询--------------- 16 17 # 正向查找(条件) 18 19 # ret3=models.Book.objects.filter(title='Python').values('id') 20 # print(ret3)#[{'id': 1}] 21 22 #正向查找(条件)之一对多 23 24 ret4=models.Book.objects.filter(title='Python').values('publisher__city') 25 print(ret4) #[{'publisher__city': '北京'}] 26 27 #正向查找(条件)之多对多 28 ret5=models.Book.objects.filter(title='Python').values('author__name') 29 print(ret5) 30 ret6=models.Book.objects.filter(author__name="alex").values('title') 31 print(ret6) 32 33 #注意 34 #正向查找的publisher__city或者author__name中的publisher,author是book表中绑定的字段 35 #一对多和多对多在这里用法没区别 36 37 # 反向查找(条件) 38 39 #反向查找之一对多: 40 ret8=models.Publisher.objects.filter(book__title='Python').values('name') 41 print(ret8)#[{'name': '人大出版社'}] 注意,book__title中的book就是Publisher的关联表名 42 43 ret9=models.Publisher.objects.filter(book__title='Python').values('book__authors') 44 print(ret9)#[{'book__authors': 1}, {'book__authors': 2}] 45 46 #反向查找之多对多: 47 ret10=models.Author.objects.filter(book__title='Python').values('name') 48 print(ret10)#[{'name': 'alex'}, {'name': 'alvin'}] 49 50 #注意 51 #正向查找的book__title中的book是表名Book 52 #一对多和多对多在这里用法没区别
---------->聚合查询和分组查询
<1> aggregate(*args,**kwargs):
通过对QuerySet进行计算，返回一个聚合值的字典。aggregate()中每一个参数都指定一个包含在字典中的返回值。即在查询集上生成聚合。
1 from django.db.models import Avg,Min,Sum,Max 2 3 从整个查询集生成统计值。比如，你想要计算所有在售书的平均价钱。Django的查询语法提供了一种方式描述所有 4 图书的集合。 5 6 >>> Book.objects.all().aggregate(Avg('price')) 7 {'price__avg': 34.35} 8 9 aggregate()子句的参数描述了我们想要计算的聚合值，在这个例子中，是Book模型中price字段的平均值 10 11 aggregate()是QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的 12 标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。如果你想要为聚合值指定 13 一个名称，可以向聚合子句提供它: 14 >>> Book.objects.all().aggregate(average_price=Avg('price')) 15 {'average_price': 34.35} 16 17 18 如果你也想知道所有图书价格的最大值和最小值，可以这样查询： 19 >>> Book.objects.all().aggregate(Avg('price'), Max('price'), Min('price')) 20 {'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}
<2> annotate(*args,**kwargs):
可以通过计算查询结果中每一个对象所关联的对象集合，从而得出总计值(也可以是平均值或总和)，即为查询集的每一项生成聚合。
查询alex出的书总价格
查询各个作者出的书的总价格,这里就涉及到分组了，分组条件是authors__name
查询各个出版社最便宜的书价是多少
---------->F查询和Q查询
仅仅靠单一的关键字参数查询已经很难满足查询要求。此时Django为我们提供了F和Q查询：
1 # F 使用查询条件的值,专门取对象中某列值的操作 2 3 # from django.db.models import F 4 # models.Tb1.objects.update(num=F('num')+1) 5 6 7 # Q 构建搜索条件 8 from django.db.models import Q 9 10 #1 Q对象(django.db.models.Q)可以对关键字参数进行封装，从而更好地应用多个查询 11 q1=models.Book.objects.filter(Q(title__startswith='P')).all() 12 print(q1)#[<Book: Python>, <Book: Perl>] 13 14 # 2、可以组合使用&,|操作符，当一个操作符是用于两个Q的对象,它产生一个新的Q对象。 15 Q(title__startswith='P') | Q(title__startswith='J') 16 17 # 3、Q对象可以用~操作符放在前面表示否定，也可允许否定与不否定形式的组合 18 Q(title__startswith='P') | ~Q(pub_date__year=2005) 19 20 # 4、应用范围： 21 22 # Each lookup function that takes keyword-arguments (e.g. filter(), 23 # exclude(), get()) can also be passed one or more Q objects as 24 # positional (not-named) arguments. If you provide multiple Q object 25 # arguments to a lookup function, the arguments will be “AND”ed 26 # together. For example: 27 28 Book.objects.get( 29 Q(title__startswith='P'), 30 Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)) 31 ) 32 33 #sql: 34 # SELECT * from polls WHERE question LIKE 'P%' 35 # AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06') 36 37 # import datetime 38 # e=datetime.date(2005,5,6) #2005-05-06 39 40 # 5、Q对象可以与关键字参数查询一起使用，不过一定要把Q对象放在关键字参数查询的前面。 41 # 正确： 42 Book.objects.get( 43 Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)), 44 title__startswith='P') 45 # 错误： 46 Book.objects.get( 47 question__startswith='P', 48 Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)))
未来的你，会感谢现在努力的你！
