SQL数据库

数据库： database

SQL：Structured Query Language 结构化查询语言

常见的数据库
 Oracle  DB2  SqlServer  MySql  Access ....

SQLite（简化的）:嵌入式，无服务器，零配置，只有一个文件

操作语言：
DDL:数据定义语言
 用来定义数据库的结构，对数据库或者数据表创建、修改结构、删除库（表）
DML：数据操纵语言
 增删改查 CURD
 Create 增加
 Update 更新
 Retrieve 查询
 Delete 删除
DCL：数据控制语言
 主要用来设置或者分配用户权限等

E-R图 实体关系图

三大范式

创建数据库：
.open 数据库名.db  如果存在则打开，如果不存在则创建
.open my.db

常用命令   以.开头 ，以回车结束
.help 显示帮助信息
.tables 查看所有表
.schema <表名>  显示表结构（建表语句）


sql语句：以;结尾

创建表：
create table 表名
(
 字段名  数据类型 [约束],
 ....
);

表相等于实体集合。
数据完整性：
 实体完整性：行
 域完整性：列
 引用完整性：两张表或者多张表
 自定义完整性：自定义规则

约束：
 主键  primary key  非空
 外键  foreign key
 唯一  unqiue  允许有一个null
 检查  check
 非空  not  null
 默认  default

数据类型：
 int
 char(10):固定长度的字符，最多可以存储10个字节的数据
 varchar(10):可变长度的字符，最多可以存储10个字节的数据

sqlite> create table student(stuno int ,sname varchar(10),age int ,email varchar
(50),address varchar(100));


CREATE TABLE employee(
eid integer primary key,
ename varchar(20) not null,
sex char(2) check(sex='男' or sex='女'),
address varchar(50) default ('北京'),
cardNo char(18) unique
);

插入数据：
insert into 表名[(字段名)] values (值);
insert into 表名[(字段名)] values (值列表1)，(值列表2);

insert into employee values(1,'Tom','男','上海','123456789012345678');


显示数据
select * from 表名;

select * from employee;


设置显示表头标题
.header on

设置显示的样式
.mode column

外键创建
CREATE TABLE salary(
id integer primary key autoincrement,
money int,
eid references employee(eid));


开启外键
pragma foreign_keys = on;


添加一列：
alter table employee add column age  int;
update employee set age = eid+15 ;

修改数据
update 表名 set 字段1=值1[,字段2=值2] [where 条件]

将所有员工的年龄+1岁
update employee set age = age + 1;

将所有女员工的年龄-1岁
update employee set age = age-1 where sex='女';

将所有北京的员工年龄改为19岁
update employee set age = 19 where address='北京';

将北京的女员工年龄改为20
update employee set age = 20 where address = '北京' and sex = '女';

将上海的男员工年龄-5
update employee set age = age - 5 where address = '上海'  and sex ='男' ;

将北京或者上海的员工性别改为女的
update employee set sex = '女' where address = '北京' or address = '上海';
update employee set sex = '女' where address  in('北京','上海');

将学号是偶数的员工年龄改为25岁
update employee set age =25 where eid%2=0;

not>and>or

删除数据
delete from 表名 [where 条件]


查询：
select 列名  from  表名
[where 条件]
[group by 分组字段]
[having 筛选条件]
[order by 排序字段 [asc|desc]]
[limit n]

1 查找年龄在19岁以下的北京的女员工
select * from employee where age<19 and sex='女' and address='北京';

2 查询名字叫Tom的员工
select * from employee where ename = 'Tom';

3 查询年龄在18岁以上的员工编号为奇数的信息
select * from employee where age > 18 and eid%2=1;
select * from employee where age > 18 and eid%2<>0;
select * from employee where age > 18 and eid%2!=0;

4 查询年龄在20-25岁之间并且年龄是偶数的男员工
select * from employee where age>=20 and age<=25 and age%2=0 and sex='男';
select * from employee where age between 20 and 25 and age%2=0 and sex='男';

5 查询地址为北京的员工的姓名
select ename from employee where address='北京';

6 查询年龄大于平均年龄的员工
select * from employee where  age >(select avg(age) from employee);

7 把上海的男员工搬到北京，把北京的女员工搬到上海。
查询男、女的平均年龄，并显示年龄在男员工平均年龄以上的女员工的信息
sqlite> update employee set address = '北京' where address='上海' and sex='男';
sqlite> update employee set address = '上海' where address='北京' and sex='女';
select sex,avg(age) from employee group by sex;
select * from employee where age > (select avg(age) from employee where sex='男') and sex='女';

8 按照年龄降序排列员工信息
select * from employee order by age desc;
如果年龄相同，按照学号降序显示
select * from employee order by age desc ,eid desc;

9 查询名字中包含y的员工信息
select * from employee where ename like '%y%'

查询第二个字母为a的员工信息
select * from employee where  ename like '_a%'

10 查询北京和上海的员工信息
select * from employee where  address in ('北京','上海')；

11 查询年龄最大的员工信息
select * from employee where  age = (select max(age) from employee);

select * from employee where age = (select age from employee order by age desc limit 1);

12 查询每个地区的平均年龄
select address,avg(age) from employee group by address ;

13 查询地区平均年龄在20岁以上
select address,avg(age) from employee group by address having avg(age)>20;

14 显示身份证为空的员工信息
select * from employee where cardNo is null;


select * from employee where cardNo is not null;


聚合函数：一行一列
avg():平均值
sum（）：和
count（）：个数
max（）最大值
min（）最小值

模糊查询：like
_:任意一个字符
%:任意多个字符


CREATE TABLE employee(
eid integer primary key,
ename varchar(20) not null,
sex char(2) check(sex='男' or sex='女'),
address varchar(50) default ('北京'),
cardNo char(18) unique
);

1 查询和学号是2的同一个城市的员工信息
2 查询男女的平均年龄的差
3 查询男女最大年龄差（max（男）-min（女））
4 查询所有名字包含y的女员工的年龄的和
5 查询超过平均年龄的员工的个数
6 查询第二大年龄的员工信息
7 统计员工名字出现次数在一次以上的所有员工
8 降序显示年龄在每个地区最大年龄的平均年龄以上的员工信息
9 查询年龄大于20的每个地区的员工个数
10 查询年龄在上海平均年龄以上的北京男员工的信息
11 查询每个地区的平均年龄在20岁以上的地区人数，按照人数降序显示
12 查询每个地区的男女人数，按照男人数降序，女人数升序
13 查询每个地区年龄最小的员工的姓名
14 查询每个地区姓名包含i的年龄的平均值，并且按照平均值降序显示
15 查询身份证号包括2的在上海的女性的年龄的平均值
16 显示北京女的年龄倒数第三的信息（按照年龄降序显示）
17 查询各个地区男女的平均年龄，显示平均年龄差（大-小）
18 查询年龄大于18岁的员工的姓名，地址
19 查询每个地区年龄最大的员工信息
20 删除北京地区年龄最小的信息

.open XXX.db
create table 表名(
 字段  数据类型  [约束],

)
insert into 表名[(字段名)] values （值）；
insert into 表名[(字段名)] values （值），（值）；

修改
update 表名 set 字段=值 [where 条件]；

删除
delete from 表名 [where 条件]

查询
select 字段名 from 表名
[where 条件]
[group by 分组字段]
[having 筛选条件]
[order by 排序字段[asc|desc]]
[limit n]

select * from employee limit 2,3;  //跳过2条数据，显示3条数据

select distinct address from address; //去掉重复数据


模糊查询
like
通配符：
_ 任意一个字符
% 任意多个字符

between ... and ....

in ('a','b','c')

聚合函数：一行一列
max()
min()
count()
avg()
sum()