Mysql一些操作
操作文件夹(库)
增 create database db1 charset utf8; 查 show create database db1; show databases; 改 alter database db1 charset gbk; 删 drop database db1;
操作文件(表)
切换文件夹:use db1; 查看当前所在文件夹:select database(); 增 create table t1(id int,name char); 查 show create table t1; show tables; desc t1; 改 alter table t1 modify name char(6); alter table t1 change name NAME char(7); 删 drop table t1;
操作文件内容(记录)
增 insert t1(id,name) values(1,'egon1'),(2,'egon2'),(3,'egon3'); 改 update db1.t1 set name='SB'; update db1.t1 set name='ALEX' where id=2; 删 delete from t1; delete from t1 where id=2;
truncate t20; #应该用它来清空表
主键 primary key
unique key 单列唯一 #方式一 create table department( id int unique, name char(10) unique ); primary key 约束:not null unique 存储引擎(innodb):对于innodb存储引擎来说,一张表内必须有一个主键 # 单列主键 create table t17( id int primary key, name char(16) ); # 复合主键 create table t19( ip char(15), port int, primary key(ip,port) );
约束:not null unique
存储引擎(innodb):对于innodb存储引擎来说,一张表内必须有一个主键
单表查询
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n;
例子:
select distinct post,count(id) as emp_count from employee where salary > 1000 group by post having count(id) > 1 order by emp_count desc
循序
def select():
f=from('db1','t1')
lines=where('id>3',f)
group_res=group(lines)
having_res=having(group_res)
distinct_res=distinct(having_res)
order_res=order(distinct_res)
res=limit(order_res)
print(res)
return res
#where
select name,post,salary from employee where post='teacher' and salary > 8000; select name,salary from employee where salary between 20000 and 30000; select * from employee where age in (73,81,28); select * from employee where post_comment is Null; select * from employee where post_comment is not Null; select * from employee where name like "jin%"; select * from employee where name like "jin___";
#正则表达式
select * from employee where name like 'jin%';
select * from employee where name regexp '^jin';
select * from employee where name regexp '^jin.*(g|n)$';
表关系
两张表之间的关系: 1、多对一 出版社 书(foreign key(press_id) references press(id)) 2、多对多 作者 书 egon: 九阳神功 九阴真经 alex: 九阳神功 葵花宝典 yuanhao: 独孤九剑 降龙十巴掌 葵花宝典 wpq: 九阳神功 insert into author2book(author_id,book_id) values (1,1), (1,2), (2,1), (2,6); 3、一对一 customer表 student表
多表查询
内连接:只取两张表的共同部分 select * from employee inner join department on employee.dep_id = department.id ; 左连接:在内连接的基础上保留左表的记录 select * from employee left join department on employee.dep_id = department.id ; 右连接:在内连接的基础上保留右表的记录 select * from employee right join department on employee.dep_id = department.id ; 全外连接:在内连接的基础上保留左右两表没有对应关系的记录 select * from employee full join department on employee.dep_id = department.id ; select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id ;