增 Insert insert into t1(id, name) value(1, '张三'); insert into t1(name, age) value('张三', 17), ('李四', 18); insert into t1(name, age) select name, age from t2; 删 Delete delete from t1; delete from t1 where id = 2; delete from t1 where id < 6 and name = '张三'; 改 Update update t1 set age = 18 where age = 17; update t1 set name = '张四', age = 19 where id = 1; 查 Select select * from t1; select id, name from t1; select id, name as cname from t1; select id, name, 11 from t1; -- 11为额外的列 select id, name, 11, (select num from t2) as number from t1; -- 子查询语句，必须单值 select id, name, 11, (select num from t1 as b where b.student_id = a.student_id) as number from t1 as a group by a.student_id; -- 子查询语句可以使用外层表 select case when min(num) < 10 then 0 else min(num) end from t1; select if(min(num)<10, 0, min(num)) from t1; -- 同上 select distinct name from t1; -- 去重，但效率不高，可用group by去重 使用函数查询 if(,,) isnull() 条件查询 where select * from t1 where id != 1; select * from t1 where id > 10 or name = '张三'; select * from t1 where id not in(1,5,12); select * from t1 where id between 5 and 12; -- 包含 5, 12 select * from t1 where id in (select nid from t2); like select * from t1 where name like 'a%'; select * from t1 where name like 'a_'; limit select * from t1 limit 5; -- 前5行 select * from t1 limit 5 offset 2 -- 从第3行开始的5行 select * from t1 limit 2, 5; -- 从第3行开始的5行 order by select * from t1 order by id asc; select * from t1 order by id desc; select * from t1 order by id asc, name desc; group by select count(id), part_id from t1 group by part_id; select max(id), part_id from t1 group by part_id; select min(id), part_id from t1 group by part_id; 聚合函数 count、max、min、sum、avg 聚合函数二次筛选，必须使用 having select count(id), part_id from t1 group by part_id having count(id) > 1; 连表查询 左右连表 left join, right join, inner join where连表 select * from a1, b1 where a1.part_id = b1.id; left join连表 a1是左表, b1是右表，left join为左表数据全部显示 select * from a1 left join b1 on a1.part_id = b1.id; right join连表 a1是左表，b1是右表，right join为右表数据全部显示 select * from a1 right join b1 on a1.part_id = b1.id; inner join连表 a1是左表, b1是右表, inner join，把出现null的行隐藏 select * from a1 inner join b1 on a1.part_id = b1.id; 上下连表 union union连表，去重 select id, name from tb1 union select num, sname from tb2; union all连表，不去重 select id, name from tb1 union all select num, sname from tb2; 临时表查询 select * from (select * from score where num > 60) as B;
数字类型 整数 类型 范围 tinyint -128 ~ 127 tinyint unsigned 0 ~ 255 smallint -32768 ~ 32767 smallint unsigned 0 ~ 65535 int -2147483648 ~ 2147483647 int unsigned 0 ~ 4294967295 bigint -9223372036854775808 ~ 9223372036854775807 bigint unsigned 18446744073709551615 小数 类型 说明 float 单精度 double 双精度 decimal (总位数, 小数位数)：十进制小数（字符串法存） 字符串类型 （定长往前放，变长往后放） 类型 长度、特点 char(10) 255，速度快 （定长） varchar(10) 255，节省空间，速度慢 （变长） text 65535 mediumtext 16777215 longtext 4294967295 时间类型 类型 格式 datetime YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59) date YYYY-MM-DD(1000-01-01/9999-12-31) time HH:MM:SS('-838:59:59'/'838:59:59') year YYYY(1901-2155) timestamp YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037年某时) 枚举、集合类型 类型 用例 说明 enum sex ENUM('male', 'female') 单选 set col SET('a', 'b', 'c', 'd') 多选，如 'a, b'、'c, b'
主键索引 约束：不能重复且不能为空； 加速查找 id int unsigned auto_increment primary key, primary key (id,name), # 多列主键 唯一索引 约束：不能重复，可以为空； 加速查找 unique 约束名 (id, name), 外键 约束：值必须是另一张表的主键 constraint fk_user_depar foreign key(department_id) references department(id), 外键的变种： 一对一 blog表中，fk(user_id) + unique(user_id) 如：一个博客属于一个用户，一个用户只能有一个博客 一对多 student表中，fk(class_id) 如：一个学生属于一个班级，一个班级可以有多个学生 多对多 fk(aid) + fk(bid) + unique(aid,bid) 如：关系表、组合配对
创建表 show tables; -- 显示表 create table t1(id int, name char(10)); -- 创建表 create table t1(id int, name char(10)) default charset=utf8; -- 创建指定字符编码的表 创建详细的表 create table t1( 列名 类型 null, -- 允许空 列名 类型 not null, -- 不许空 列名 类型 not null default 1, -- 默认值 列名 类型 auto_increment primary key, -- 自增，主键（约束：不能重复且不能为空；加速查找） id int, name char(10) -- 末行不加"," ) engine=innodb default charset=utf8; -- 指定 innodb引擎 引擎的区别： 引擎 功能 innodb 支持事务，原子性操作 myisam 不支持事务 参考 mysql使用索引与外键 mysql的数据类型 清空表 delete from t1; -- 自增id不归零 truncate table t1; -- 自增id归零 删除表 drop table t1; 查看表的描述description desc t1; 查看表的SQL创建语句 show create table t1; 修改自增变量值 alter table t1 set AUTO_INCREMENT=2;
创建用户 create user '用户名'@'IP地址' identified by '密码'; create user 'zhangsan'@'192.168.1.1' identified by '123123'; create user 'zhangsan'@'192.168.1.%' identified by '123123'; create user 'zhangsan'@'%' identified by '123123'; 用户授权（权限、人） show grants for '用户'; -- 查询权限 grant 权限类型 on 数据库.表 to '用户'@'IP地址'; -- 授权 revoke 权限类型 on 数据库.表 from '用户'@'IP地址'; -- 取消权限 grant select,insert,update on db1.t1 to 'zhangsan'@'%'; grant all privileges on db1.* to 'zhangsan'@'%'; 常见的权限类型： 权限类型 中文解释 all privileges 所有权限 select 读取 delete 删除 update 更新 create 创建 drop 删除数据库、数据表 修改用户名 rename user '用户名'@'IP地址' to '新用户名'@'IP地址'; 修改密码 alter user '用户名'@'IP地址' identified by '新密码'; 删除用户 drop user '用户名'@'IP地址'; 查看用户 use mysql; select user, host from user;
下载 mysql.zip 解压缩 mysql.zip 至 c:\mysql（注意目录不要有中文） 添加环境变量：配置Path，添加mysql下的bin目录 初始化 >mysqld --install # 安装服务 >mysqld --initialize-insecure # 初始化mysql >net start MySQL # 开启mysql服务 登录并更改密码 >mysql -u root -p # 输入生成的密码 mysql>alter user 'root'@'localhost' identified by '123' # 更改新密码为：123 mysql>exit >mysql -u root -p # 输入新密码 关闭mysql服务 >net stop MySQL
数据库配置 创建数据库 配置 settings.py DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'XXX', 'USER': 'XXX', 'PASSWORD': 'xxx', 'HOST': 'localhost', 'PORT': 3306, } } 修改 __init__.py，更改 mysqldb 为 pymysql import pymysql pymysql.install_as_MySQLdb() 操作表 创建表、修改表、删除表 >python manage.py makemigrations >python manage.py migrate 操作行 增加 models.UserGroup.objects.create(title='销售部') models.UserInfo.objects.create(username='root', password='123', usergroup_id=1) 删除 usergroups = models.UserGroup.objects.filter(id=1).delete() 改变 usergroups = models.UserGroup.objects.filter(id=1).update(title='公关部') from django.db.models import F models.UserInfo.objects.all().update(age=F('age')+1) # update userinfo set age = age + 1; 查找 一般查找 userlist = models.UserGroup.objects.all()#.first() for row in userlist: print(row.id, row.title) userlist = models.UserGroup.objects.filter(id=1, title='aaa') # and userlist = models.UserGroup.objects.exclude(id=1) # != userlist = models.UserGroup.objects.filter(id__gt=1) # > userlist = models.UserGroup.objects.filter(id__lt=1) # < 进阶查找 # 连表查找 # usertype是userinfo的fk字段 obj = models.UserInfo.objects.all().first() print(obj.name, obj.age, obj.usertype_id, obj.usertype.title) # (正向)连表查找 obj = models.UserType.objects.all().first() print(obj.id, obj.title, obj.userinfo_set.all()) # (反向) print(obj.id, obj.title, obj.userinfo_set.filter(name='xx')) # 过滤 # 获取的类型 models.UserInfo.objects.all() # 返回UserInfo类型的QuerySet数组 models.UserInfo.objects.all().values('id', 'name') # 返回字典类型的QuerySet数组 models.UserInfo.objects.all().values_list('id', 'name') # 返回元组类型的QuerySet数组 # 排序 Order By models.UserInfo.objects.all().order_by('id') # 升序 models.UserInfo.objects.all().order_by('-id') # 降序 models.UserInfo.objects.all().order_by('id', 'name') # 多列排序 # 分组 Group By ... Having ... from django.db.models import Count, Sum, Max, Min, Avg v = models.UserInfo.objects.filter(id__gt=2).values('usertype_id').annotate(xxxx=Count('id')).filter(xxxx__gt=2) # 注意前后filter表示的含义不同 print(v.query) # 查看生成的SQL语句 # select usertype_id, count(id) as xxxx from userinfo where id > 2 group by usertype_id having count(id) > 2; # in; between ... and ...; gt lt gte lte ...; models.UserInfo.objects.filter(id__in=[1,2,3]) # in models.UserInfo.objects.filter(id__range=[1,3]) # between...and... models.UserInfo.objects.filter(id__gt=1) # > models.UserInfo.objects.filter(id__lt=1) # < models.UserInfo.objects.filter(id__gte=1) # >= models.UserInfo.objects.filter(id__lte=1) # <= # not ... models.UserInfo.objects.exclude(id__in=[1,2,3]) # in models.UserInfo.objects.exclude(id__range=[1,3]) # between...and... models.UserInfo.objects.exclude(id__gt=1) # > models.UserInfo.objects.exclude(id__lt=1) # < models.UserInfo.objects.exclude(id__gte=1) # >= models.UserInfo.objects.exclude(id__lte=1) # <= # like % models.UserInfo.objects.filter(name__startswith='a') # like '%a' models.UserInfo.objects.filter(name__endswith='a') # like 'a%' models.UserInfo.objects.filter(name__contains='a') # like '%a%' # not like % models.UserInfo.objects.exclude(name__startswith='a') # not like '%a' models.UserInfo.objects.exclude(name__endswith='a') # not like 'a%' models.UserInfo.objects.exclude(name__contains='a') # not like '%a%' # and condition = {'id': 1, 'name': 'root'} models.UserInfo.objects.filter(**condition) # 不推荐 # and or混合，对象方式 (不推荐) from django.db.models import Q models.UserInfo.objects.filter(Q(id=1) | Q(id=2)) # or models.UserInfo.objects.filter(Q(id=1) & Q(id=2)) # and # and or混合，方法方式 (推荐) q1 = Q() q1.connector = 'OR' q1.children.append(('id', 1)) q1.children.append(('id', 10)) q1.children.append(('id', 9)) q2 = Q() q2.connector = 'OR' q2.children.append(('c1', 1)) q2.children.append(('c1', 10)) q2.children.append(('c1', 9)) con = Q() con.add(q1, 'AND') con.add(q2, 'AND') models.UserInfo.objects.filter(con) # select id, name, (select ...) as n from xb; models.UserInfo.objects.all().extra( select={ 'n': 'select ... where id > %s and id < %s', 'm': 'select ... where id = %s and id = %s', }, select_params=[1, 3, 4, 5]) # where (select ... ) models.UserInfo.objects.all().extra( where=["id=%s or id=2", "name=%s"], # and params=[1, 'alex']) # select * from app01_userinfo, app01_usertype; models.UserInfo.objects.all().extra( tables=['app01_usertype'], where=['app01_usertype.id = app01_userinfo.user'] ) 分页 obj = models.UserInfo.objects.all()[0:10]
