python学习之数据库操作

50010623

一、数据库基本操作　　
数据库操作:
　　
查看存储引擎: show engines;
　　
查看数据库: show databases; 或者show create database oldboy\G
　　
创建数据库: create database oldboy default
　　
删除数据库: drop database oldboy;
　　
进入数据库:  use oldboy;
　　

　　
数据表操作:
　　
创建数据表:
　　
create table tb_emp1 (
　　
    -> id int(11),
　　
    -> name varchar(25),
　　
    -> deptId int(11),
　　
    -> salary float
　　
    -> )engine=innodb default charset=utf8;
　　

　　
primary key:
　　
create table tb_emp2 (
　　
    -> id int(11) primary key,
　　
    -> name varchar(25),
　　
    -> deptId int(11),
　　
    -> salary float
　　
    -> )engine=innodb default charset=utf8;
　　

　　
多个primary key:
　　
create table tb_emp3 ( id int(11), name varchar(25), deptId int(11), salary float,
　　
primary key(name, deptId) )engine=innodb default charset=utf8;
　　

　　
外键约束foreign key:
　　
主表:
　　
create table tb_dept1 (
　　
    -> id int(11) primary key,
　　
    -> name varchar(22) not null,
　　
    -> location varchar(50)
　　
    -> )engine=innodb default charset=utf8;
　　

　　
从表:
　　
create table tb_emp5 (
　　
    -> id int(11) primary key,
　　
    -> name varchar(25),
　　
    -> deptId int(11),
　　
    -> salary float,
　　
    -> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
　　
    -> )engine=innodb default charset=utf8;
　　

　　
非空约束not null:
　　
create table tb_emp6 (
　　
    -> id int(11) primary key,
　　
    -> name varchar(25) not null,    #非空
　　
    -> deptId int(11),
　　
    -> salary float,
　　
    -> constraint fk_emp_dept2 foreign key(deptId) references tb_dept1(id)
　　
    -> )engine=innodb default charset=utf8;
　　

　　
唯一性约束unique,要求该列唯一，允许为空，但只能有一个值为空:
　　
create table tb_dept2 (
　　
    -> id int(11) primary key,
　　
    -> name varchar(22) unique,
　　
    -> location varchar(50)
　　
    -> )engine=innodb default charset=utf8;
　　

　　
默认值default:
　　
create table tb_emp7 (
　　
    -> id int(11) primary key,
　　
    -> name varchar(25) not null,
　　
    -> deptId int(11) default 1111,
　　
    -> salary float,
　　
    -> constraint fk_emp_dept3 foreign key(deptId) references tb_dept1(id)
　　
    -> )engine=innodb default charset=utf8;
　　

　　
自增主键auto_increment:
　　
create table tb_emp8 (
　　
    -> id int(11) primary key auto_increment,
　　
    -> name varchar(25) not null,
　　
    -> deptId int(11),
　　
    -> salary float,
　　
    -> constraint fk_emp_dept5 foreign key(deptId) references tb_dept1(id)
　　
    -> )engine=innodb default charset=utf8;
　　

　　
插入tb_emp8三条数据:
　　
insert into tb_emp8(name,salary) values('Lucy', 1000),('lura', 1200),('Kevin',1500);
　　
id自增
　　

　　
查看表结构: desc tb_emp8; 或者show create table tb_emp8\G
　　

　　

　　
修改数据表alter
　　
修改表名: alter table tb_dept2 rename tb_deptment3;
　　
修改字段类型: alter table tb_dept1 modify name varchar(30);
　　
修改字段名: alter table tb_dept1 change location loc varchar(50);
　　
添加字段: alter table tb_dept1 add managerId int(10);
　　
添加有约束条件的字段: alter table tb_dept1 add column1 varchar(12) not null;
　　
在某个位置添加字段: alter table tb_dept1 add column2 int(11) first;
　　
在某个字段后面添加新字段: alter table tb_dept1 add column3 int(11) after name;
　　
删除字段: alter table tb_dept1 drop column2;
　　
修改表的存储引擎: alter table tb_deptment3 engine=MyISAM;
　　
删除外键约束: alter table tb_emp9 drop foreign key fk_emp_dept;
　　

　　
删除数据表: drop table if exists tb_dept2;
　　
删除外键关联的主表，需要首先取消外键关联，否则删除主表失败
　　

　　

　　
数据类型
　　
整形: TINYINT 1个字节  2**8 - 1=255个值
　　
整形: smallint 2个字节
　　
整形: int 4个字节
　　
整形: bigint 8个字节
　　

　　
浮点数
　　
单精度: float 4个字节
　　
双精度: double 8个字节
　　
decimal: 不固定，一般用于财务系统
　　

　　
日期时间类型
　　
year: 1个字节,格式 YYYY
　　
time: 3个字节,格式 HH:MM:SS
　　
date: 3个字节,格式 YYYY-MM-DD
　　
datetime: 8个字节，格式 YYYY-MM-DD HH:MM:SS
　　
timestamp: 4个字节，格式YYYY-MM-DD HH:MM:SS
　　

　　
字符串类型
　　
char(n): 固定长度字符串    #浪费内存，但查询速度快
　　
varchar(n): 非固定长度字符串    #节省内存，但查询速度慢
　　
text: 存放文本
　　
longtext: 存放大数据文本
　　

　　

　　
between .. and .. 关键字使用
　　
select 4 between 4 and 6, 4 between 4 and 6, 12 between 9 and 10;
　　

　　
in, not in 关键字使用
　　
select 2 in(1,3,5,'thks'), 'thks' in(1,3,5,'thks');
　　

　　
like用来匹配字符串
　　
'%': 匹配任何数目的字符
　　
'_': 只能匹配一个字符
　　

　　
select查询数据
　　
create table fruits (
　　
    -> f_id char(10) not null,
　　
    -> s_id int not null,
　　
    -> f_name char(255) not null,
　　
    -> f_price decimal(8,2) not null,
　　
    -> primary key(f_id)
　　
    -> ) engine    -> ) engine=innodb =utf8;
　　

　　
插入字段:
　　
insert into fruits(f_id,s_id,f_name,f_price) values
　　
    -> ('a1',101,'apple',5.2),
　　
    -> ('b1',102,'blackberry',10.2),
　　
    -> ('bs1',102,'orange',11.2),
　　
    -> ('bs2',105,'melon',8.2),
　　
    -> ('t1',102,'banana',10.3),
　　
    -> ('t2',102,'grape',5.3),
　　
    -> ('o2',103,'coconut',9.2),
　　
    -> ('c0',101,'cherry',3.2),
　　
    -> ('a2',103,'apricot',2.2),
　　
    -> ('l2',104,'lemon',6.4),
　　
    -> ('b2',104,'lemon',7.6),
　　
    -> ('m1',106,'mango',15.6),
　　
    -> ('m2',105,'xbabay',2.6),
　　
    -> ('t4',107,'xbababa',3.6),
　　
    -> ('m3',105,'xxtt',11.6),
　　
    -> ('b5',107,'xxxx',3.6);
　　

　　
单表查询:
　　
查询表: select f_id,f_name from fruits;
　　
查询条件where:
　　
select f_id,f_name from fruits where f_price=10.2;    #等号 =
　　
select * from fruits where f_price < 10;        #小于
　　
select * from fruits where s_id in(101,102) order by f_name (desc);    #in关键字,按f_name排序，desc降序，asc升序
　　
select * from fruits where f_price between 2.00 and 10.20;    #between and
　　
select * from fruits where f_name like 'b%';    #like关键字匹配, %匹配任何多个字符
　　
select * from fruits where f_name like '_____y';    #_匹配任意一个字符
　　
select * from fruits where s_id='101' and f_price >=5;    #and多条件匹配
　　
select * from fruits where s_id='101' or s_id=102;    #or多条件匹配
　　

　　
order by查询结果排序
　　
select * from fruits order by f_name;
　　
select * from fruits order by f_price desc;    #desc倒叙排列
　　

　　
group by分组
　　
select s_id,count(*) as Total from fruits group by s_id; #根据s_id分组,s_id相同的数量
　　
select s_id,count(*) as Total from fruits group by s_id having count(f_name) > 1;    #having后面加上查询条件
　　

　　
limit限制查询的数量
　　
select * from fruits limit 4;    #查询四条
　　
select * from fruits limit 4,3;    #索引为4，从第五条开始返回3条
　　

　　
inner join 内连接,返回两表中都有的记录
　　
create table suppliers (
　　
    -> s_id int(11) not null auto_increment primary key,
　　
    -> s_name char(50) not null,
　　
    -> s_city char(50),
　　
    -> s_zip char(10),
　　
    -> s_call char(50) not null
　　
    -> )engine=innodb default charset=utf8;
　　

　　
以下操作是fruits和suppliers关联
　　
select suppliers.s_id, s_name, f_name, f_price from fruits inner join suppliers on
　　
    -> fruits.s_id = suppliers.s_id;    #on后面是条件查询，
　　

　　
left join    返回包括左表中的所有记录和右表连接字段的所有记录
　　
select s_name,f_price from fruits left join suppliers on fruits.s_id = suppliers.s_id;
　　

　　
子查询:
　　
select s_id, f_name from fruits where s_id=(select s1.s_id from suppliers as s1 where s1.s_city='Tianjin');
　　

　　

　　
union合并查询结果并去重
　　
union all合并查询不去重
　　
select s_id ,f_name,f_price from fruits where f_price < 9.0 union all select s_id,f_name,f_price from fruits where s_id in(101,103);