【python开发】必备SQL和表关系及授权

一、必备SQL语句

现在，需要创建如下两张表：

表info

id	name	email	age	depart_id
1	高宇星	gao.yuxing@live.com	20	1
2	alex	alex@live.com	19	1
3	闫曦月	yan.xiyue@live.com	29	2
4	Tony	tony@livw.com	22	1
5	kelly	kelly@live.com	45	3
6	james	james@live.com	52	1
7	ammy	ammy@live.com	23	1

表depart

id	title
1	开发
2	运营
3	销售

创建表格

create database deployer default charset utf8 collate utf8_general_ci;

create table depart(
id int not null auto_increment primary key,
title varchar(16) not null
) default charset=utf8; 


create table info(
id int not null auto_increment primary key,
name varchar(16) not null,
email varchar(32) not null,
age int,
depart_id int
) default charset=utf8;

插入数据

insert into depart(title) values("开发");
insert into depart(title) values("运营");
insert into depart(title) values("销售");

insert into info(name, email, age, depart_id) values("高宇星","gao.yuxing@live.com", 20, 1);
insert into info(name, email, age, depart_id) values("alex","alex@live.com", 19, 1);
insert into info(name, email, age, depart_id) values("闫曦月","yan.xiyue@live.com", 29, 2);
insert into info(name, email, age, depart_id) values("Tony","tony@live.com", 22, 1);
insert into info(name, email, age, depart_id) values("kelly","kelly@live.com", 45, 3);
insert into info(name, email, age, depart_id) values("james","james@live.com", 52, 1);
insert into info(name, email, age, depart_id) values("ammy","ammy@live.com", 23, 1);

1、精确查询

select * from info where age>30;
select * from info where id>1;
select * from info where id=1;
select * from info where id>=1;
select * from info where id !=1;
select * from info where id between 2 and 4;


select * from info where name='高宇星' and age=20;
select * from info where name='高宇星' or age=53;
select * from info where (name='高宇星' or email='yan.xiyue@live.com') and age=20;


select * from info where exists (select * from depart where id=5);
select * from info where not exists (select *<