mysql -uroot -p
退出 exit
启动mysql服务 net start mysql
停止mysql服务 net stop mysql
创建数据库create database db1;
删除数据库drop database db1;
查看所有数据库show databases;
使用数据库 use db1;
查看当前数据库 select database();
创建数据库并指定编码 create database db1 charset=‘utf8’;
修改数据库编码 alter database db1 charset=‘gbk’;
案例
use db1;
创建表
create table user1(
username varchar(16),
age tinyint
);
创建表结构
create table user2(
id int primary key auto_increment,
username varchar(16),
password varchar(16) not null unique,
gender tinyint default 0,
account decimal(12,2)default 0,
vip boolean default 0,
);
插入数据
insert into user2(username,password ,gender,account,vip)values(‘yuan’,‘123’,‘0’,‘120’,‘0’),(。。。。。);
select * from user3;
select username,password from user3;
select * from user3 where 条件
select password from user3 where username=‘王伟’;
select * from user3 where username is not null;
select username from user3 where account>5;
select * from user3 where username like ‘%秀%’;
select * from user3 where username like ‘王_ _’;
select * from user3 where username in (‘yuan’,’yan’);
select * from user3 where username = ‘yuan’ or username = ’yan’;
select * from user3 group by vip;
select count(*),vip from user3 group by vip;
select count(*),vip from user3 group by vip having count(*)>3;
select * from user3 order by account desc,id asc;
select * from user3 limit 3,5;3开始,拿5条数据
修改数据
update user3 set username=namenew,password=passnew where id=3;
删除数据
delete from user3 where id>=2;
Update 更改表内数据
alter table 更改表结构
rename table 更改表名字
alter table user3 add tel tinyint unique;
alter table user3 change tel téléphone varchar(16) unique;
alter table user3 drop téléphone;
delete 删除表内行数据
drop table 删除整个表,可以恢复
truncate 删除表内的所有数据,速度快,不可恢复
delete from user3 where id>=2;
drop table user3;
truncate user3;
===================================
一对多
create table user(
id int primary key auto_increment,
username varchar(16),
);
create table forum(
id int primary key auto_increment,
topic varchar(200),
user_id int,
constraint fu1 foreign key(user_id) references use(id) on delete cascade, #fu1约束名字,任意起
);
on delete cascade 主表被删除,相关的表也会被删除
on delete restrict 主表被删除,默认值,抛异常,不允许被删除
on delete set null 主表被删除,相关联的表外键设置为空
on delete no action。什么也不做,主表被删除,相关联的表外键不影响
多对多
create table user_forum(
id int primary key auto_increment,
user_id int,
forum_id int,
constraint c1 foreign key(user_id) references use(id) on delete cascade, #fu1约束名字,任意起
constraint c2 foreign key(forum_id) references forum(id) on delete cascade,
);
一对一
1.单张表内字段数太多,分表
2.敏感信息分离
create index username_index on user3 (username(16));
drop index username_index on user3;
=========================================
Python mange.py shell
user1=User.objects.get(id=1)
user=User.objects.get(id=1)
User object(1)对象
user_s=User.objects.all() #所有对象 QuerySet对象
User object
for user in user_s:
print(user)
user_s=User.objects.filter(id=1) #QuerySet对象,列表,可迭代
user_s[0]
user_s=User.objects.exclude(id=1) #QuerySet对象,列表,id不是1的
user_s=User.objects.all().count() ===>int
user_s=User.objects.filter(id=1).exists() ===>boolean
user_s=User.objects.all().order_by(‘createDatetime’,’-birthday’)
==============================================
字段名_条件关键字=比较值
条件关键字:exact,gt,gte,lt,lte,isnull
查询id大于等于2
user_s=User.objects.filter(id__gte=2)
user_s=User.objects.filter(id__exact=2)
user_s=User.objects.filter(account__gte=100)
user_s=User.objects.filter(password__isnull=True)
user_s=User.objects.filter(username__contains=‘1’)
user_s=User.objects.filter(username__startswith=‘1’)
user_s=User.objects.filter(username__endswith=‘1’)
user_s=User.objects.filter(username__in=[‘yuan’,’yan’])
===================================================
F对象
同行数据字段互相比较
from django.db.models import F
user_s=User.objects.filter(updateDatetime__gt=F(‘creatDatetime’))
Q对象
逻辑比较
from django.db.models import Q
user_s=User.objects.filter(money_gt=5000,gender=0)
user_s=User.objects.filter(Q(money_gt=5000) & Q(gender=0))
user_s=User.objects.filter(Q(money_gt=5000,gender=0) | Q(money_gt=2000,gender=1))