数据库基础加练习

最新推荐文章于 2024-04-24 15:44:04 发布

weixin_30436101

最新推荐文章于 2024-04-24 15:44:04 发布

阅读量173

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/zfb123-/p/11588055.html

版权

数据库基础

1.什么是数据库

用来存储数据。数据库可在硬盘及内存中存储数据

数据库与文件存储数据的区别

数据库本质也是通过文件来存储数据，数据库的概念就是系统的管理存储数据的文件

数据库介绍
本质就是存储数据的C/S架构的socket套接字软件
数据库服务器端：存放数据的主机集群
数据库客户端：可以连接数据库的任意客户端
数据库管理员：DBA

2.数据库基本概念

库：多个表构建一个数据库
表：多条数据构建一张表
记录：存放一条条数据，本质就是文件中的一条条数据记录

字段，字段，字段。。。==》一条数据

3.数据库分类

关系型数据库(MySQL 、Oracle 、 SQL server 、access、db2)
1.有表的概念
2.以表中一条条的纪录存储数据

非关系型数据库 (mongodb、redis、memcache)
1.有表的概念
2.以key-value键值对方式存储数据

4.数据库安装、启动与连接

安装 server端h和client端
如何启动server? 如果通过client连接server

前提：配置环境变量
1、前往数据库安装路径，bin文件夹下，cmd执行MySQL启动服务器端
2、前往数据库安装路径，bin文件夹下，cmd执行MySQL启动客户端链接服务器端

将MySQL服务器添加到系统服务，在系统服务中启动MySQL，命令：mysqld --install

进入系统 win+r=> services.msc=>找到MySQL服务手动启动或关闭
连接数据库：mysql -hlocalhost -P3306 -uroot -p
通过最高权限进入数据库，要采用root用户进入，连入本地数据库： mysql -uroot -p

查看MySQL版本：select version();
查看当前用户：select user();
查看MySQL下(当前用户可以操作的）数据库：show databases;

5.密码操作：

修改密码： mysqladmin -uroot -p旧密码 password“新密码”

忘记旧密码
1.绕过授权表启动服务（安全认证的服务停止）：mysqld --skip-grant-tables;
2.以任意密码登陆root用户：mysql -uroot -p任意
3.更新密码： updata mysql.user set password=passwore("新密码")where user=root and host="localhost"
4.刷新权限：flush privileges;

6.统一字符编码

查看数据库配置信息  
1.在mysql安装目录下:创建my.ini (my.cnf) (命令:type nul>文件名.文件后缀)
    
2.设置配置信息并保存
[mysqld]
#port=7777 注释
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8

3.重启服务

库的基本操作

# 前提: 连接上数据库

1.增,创建数据库
采用默认编码集: create database db1;  # db1为数据库名
自定义编码集: create database db1 charset="gbk";

2.查,查看数据库全部信息
纵观所有数据库: show databases;
详细信息: show create database db1;

3.改,修改数据库编码集
alter database db1 charset="utf8";

4.删, 移除数据库
drop database db1;

表的基本操作

#前提: 在具体的某个库下创建表
进入指定数据库: use db1
确定当前使用的数据库: select database();

1.增, 创建表(字段1 类型, ..., 字段n 类型)
create table t1(name char, age int);

2.查,查看表信息
纵观所有数据库: show tables;
详细信息: show create table t1;
表字段结构信息: desc t1;

3.改
修改字段属性: alter table t1 modify name char(20);
修改字段名: alter table t1 change name usr char(16);
修改表名: alter table t1 rename t2;

4.删, 移除表
drop table t1;

字段的基本操作

# 前提: 知道具体操作的是哪张表
1.增, 添加字段
insert into t1 (usr, age) values ("aa", 18),("bb", 8);

2.查
select * from t1;

3.改
update t1 set age=28 where usr="aa";

4.删
delete from t1 where age>8;

引擎与数据类型

前提: 引擎是建表是规定, 提供给表使用的, 不是数据库

mysql> show engines; # 展示所有引擎
# 重点: 
# innodb(默认): 支持事务, 行级锁, 外键
# myisam: 查询效率要优于innodb, 当不需要支持事务, 行级锁, 外键, 可以通过设置myisam来优化数据库

mysql> use db1;

mysql> create table t1(id int)engine=innodb;
mysql> create table t2(id int)engine=myisam;
mysql> create table t3(id int)engine=blackhole;
mysql> create table t4(id int)engine=memory;

insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

select * from t1; ...

创建表完整语法

'''
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)engine=innodb charset=utf8;
'''
# []可选参数
# create table db1.t1(name char(3) not null);
# 数据插入时,name不能为空(null), 且最长只能存放三个字符
# 总结: 宽度和约束条件为可选参数, 用来限制存放数据的规则

数据库的模式

# sql_mode: 反映数据库的全局变量
# 数据库模式限制的是客户端对服务器操作数据的方式(是否严格)

# 两种模式
no_engine_substitution：非安全性，默认
strict_trans_tables：安全模式

# 查看当前数据库模式:
show variables like "%sql_mode%"; # %匹配0~n个任意字符 => 模糊查询

# 设置为安全模式
set global sql_mode="strict_trans_tables";

# 重启连接(客户端)
quit

# 应用
create table t1(name char(2));
insert into t1 values ("ab") # 正常
insert into t1 values ("zero") # 错误 Data too long for column 'name' at row 1

数据类型

# mysql数据库支持存放哪些数据

# 整型* | 浮点型 | 字符型* | 时间类型 | 枚举类型 | 集合类型

整型

'''类型
tinyint：1字节 -128~127 *
smallint：2字节
mediumint：3字节
int：4字节 -2147483648~2147483647 *
bigint：8字节
'''
'''约束 *
unsigned：无符号
zerofill：0填充
'''
# 不同类型所占字节数不一样, 决定所占空间及存放数据的大小限制
# eg:
create table t8(x tinyint);
insert into t8 values(200);  # 非安全模式存入,值只能到最大值127
select x from t8;


'''宽度
1.不能决定整型存放数据的宽度, 超过宽度可以存放, 最终由数据类型所占字节决定
2.如果没有超过宽度,且有zerofill限制, 会用0填充前置位的不足位
3.没有必要规定整型的宽度, 默认设置的宽度就为该整型能存放数据的最大宽度 *
'''
# eg:1
create table t9(x int(5));
insert into t9 values(123456); 
select (x) from t9; # 结果: 123456
insert into t9 values(2147483648); 
select (x) from t9; # 结果: 2147483647
insert into t9 values(10); 
select (x) from t9; # 结果: 10
# eg:2
create table t10(x int(5) unsigned zerofill); # 区域0~4294967295
insert into t10 values(10); 
select x from t10; # 结果: 00010
insert into t10 values(12345678900); 
select x from t10; # 结果: 4294967295

浮点型

'''类型
float：4字节，3.4E–38～3.4E+38 *
double：8字节，1.7E–308～1.7E+308
decimal：M，D大值基础上+2
'''
'''宽度：
限制存储宽度
(M, D) => M为总位数，D为小数位
float(255, 30)：精度最低，最常用
double(255, 30)：精度高，占位多
decimal(65, 30)：字符串存，全精度
'''
# eg:1
create table t11 (age float(256, 30)); # Display width out of range for column 'age' (max = 255)
create table t11 (age float(255, 31)); # Too big scale 31 specified for column 'age'. Maximum is 30.
# eg:2
create table t12 (x float(255, 30));
create table t13 (x double(255, 30));
create table t14 (x decimal(65, 30));

insert into t12 values(1.11111111111111111111);
insert into t13 values(1.11111111111111111111);
insert into t14 values(1.11111111111111111111);

select * from t12; # 1.111111164093017600000000000000 => 小数据,精度要求不高, 均采用float来存储 *
select * from t13; # 1.111111111111111200000000000000
select * from t14; # 1.111111111111111111110000000000

alter table t14 modify x decimal(10, 5); # 1.11111 => 限制了数据的存储宽度

字符型

'''类型
char：定长
varchar：不定长
'''
'''宽度
限制存储宽度
char(4)：以4个字符存储定长存储数据
varchar(4)：数据长度决定字符长度，为可变长度存储数据
'''
# eg:
create table t15 (x char(4), y varchar(4));
insert into t15 values("zero", 'owen'); # '' | "" 均可以表示字符
select x,y from t15; # 正常
insert into t15 values("yanghuhu", 'lxxVSegon'); # 非安全模式数据丢失,可以存放, 安全模式报错
select x,y from t15; # 可以正常显示丢失后(不完整)的数据
insert into t15 values('a', 'b');

# 验证数据所在字符长度
# 前提: 安全模式下以空白填充字符
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
# 重启连接
select char_length(x), char_length(y) from t15; # a占4 b占1

'''重点: 存储数据的方式 **  => 数据库优化
char: 一定按规定的宽度存放数据, 以规定宽度读取数据, 通常更占空间
varchar: 首先根据数据长度计算所需宽度, 并在数据开始以数据头方式将宽度信息保存起来, 是一个计算耗时过程, 取先读取宽度信息,以宽度信息为依准读取数据, 通常节省空间
'''
8: zero    egon    lxx     yanghuhu
8: 4zero4egon3lxx8yanghuhu
注: varchar的数据头占1~2字节
    规定char|varchar宽度均为4,用来存放4个字符的数据, char存取更高效,char占4字符,varchar占5字符,char更省空间

总结: 数据长度相近的数据提倡用char来存放数据, 数据需要高速存取,以空间换时间, 采用char

时间类型

'''类型
year：yyyy(1901/2155)
date：yyyy-MM-dd(1000-01-01/9999-12-31)
time：HH:mm:ss
datetime：yyyy-MM-dd HH:mm:ss(1000-01-01 00:00:00/9999-12-31 23:59:59)
timestamp：yyyy-MM-dd HH:mm:ss(1970-01-01 00:00:00/2038-01-19 ??)
'''
# eg: 1
create table t16(my_year year, my_date date, my_time time);
insert into t16 values(); # 三个时间类型的默认值均是null
insert into t16 values(2156, null, null); # 在时间范围外,不允许插入该数据
insert into t16 values(1, '2000-01-01 12:00:00', null); # 2001 2000-01-01 null
insert into t16 values(2019, '2019-01-08', "15-19-30"); # time报格式错误 => 按照时间规定格式存放数据

alter table t16 change my_year myYear year(2); # 时间的宽度修改后还是采用默认宽度 => 不需要关系宽度


# eg:2
create table t17(my_datetime datetime, my_timestamp timestamp);
insert into t17 values(null, null); # 可以为空, 不能为null,赋值null采用默认值current_timestamp
insert into t17 values('4000-01-01 12:00:00', '2000-01-01 12:00:00'); # 在各自范围内可以插入对应格式的时间数据

# datetime VS timestamp
datetime：时间范围，不依赖当前时区，8字节，可以为null
timestamp：时间范围，依赖当前时区，4字节，有默认值CURRENT_TIMESTAMP

枚举与集合

'''类型
enum：单选
set：多选
'''

create table t19(
    sex enum('male','female','wasai') not null default 'wasai', # 枚举
    hobbies set('play','read','music') # 集合
);

insert into t19 values (null, null); # sex不能设置null
insert into t19 values (); # wasai null
insert into t19 (hobbies) values ('play,read'), ('music,play'); # sex采用默认值, 对hobbies字段添加两条记录
insert into t19 (sex,hobbies) values ('male,female', 'play'); # sex字段只能单选

约束条件

"""
primary key：主键，唯一标识，表都会拥有，不设置为默认找第一个 不空，唯一 字段，未标识则创建隐藏字段
foreing key：外键
unique key：唯一性数据, 该条字段的值需要保证唯一,不能重复

auto_increment：自增，只能加给key字段辅助修饰

not null：不为空
default：默认值

unsigned：无符号
zerofill：0填充
"""

注：
1.键是用来讲的io提供存取效率
2.联合唯一
create table web (
    ip char(16),
    port int,
    unique(ip,port)
);
3.联合主键
create table web (
    ip char(16),
    port int,
    primary key(ip,port)
);

# eg:1
# 单列唯一
create table t20 (
    id int unique
);
# 联合唯一
create table web (
    ip char(16),
    port int,
    unique(ip,port)
);
# 如果联合两个字段,两个字段全相同才相同,否则为不同
insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306);

# 注: 
# 1.表默认都有主键, 且只能拥有一个主键字段(单列主键 | 联合主键)
# 2.没有设置主键的表, 数据库系统会自上而下将第一个规定为unique not null字段自动提升为primary key主键
# 3.如果整个表都没有unique not null字段且没有primary key字段, 系统会默认创建一个隐藏字段作为主键
# 4.通常必须手动指定表的主键, 一般用id字段, 且id字段一般类型为int, 因为int类型可以auto_increment

# eg:2
create table t21(id int auto_increment); # 自增约束必须添加给key的字段
# eg:3
create table t21(id int primary key auto_increment); # 自增要结合key,不赋值插入,数据会自动自增, 且自增的结果一直被记录保留
# eg:4
# 联合主键
create table t22(
    ip char(16),
    port int,
    primary key(ip,port)
);
# 如果联合两个字段,两个字段全相同才相同,否则为不同
insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306);

表关系

1.表的详细操作

1.修改表名
alter table 旧表名 rename 新表名;

2.修改表的引擎与字符编码
alter table 表名 engine="引擎名" charset="编码名";

3.复制表 *
# 结构
create table 新表名 like 旧表名;#只复制表的结构，包括约束
eg:
create table nt like tt; # 将tt的表结构复制到新表nt中, 约束条件一并复制

# 结构+数据
create table 新表名 select * from 旧表名;
注: 会复制表结构+数据, 但不会复制约束条件
eg:
create table nt1 select * from tt where 1=2; # 将tt的表结构复制到新表nt1中, 键（相关）的约束条件不会复制

4.清空表
truncate 表名;
注：表被重置，自增字段重置

表中字段的详细操作

create table t2(
    id int primary key auto_increment,
    x int,
    y int
);
insert into t2(x, y) values(10, 20), (100, 200), (1000, 2000);


1.修改字段信息
alter table 表名 modify 字段名 类型[(宽度) 约束];
alter table t2 modify x bigint default 0;  # 模式不同, 涉及精度问题

2.修改字段名及信息
alter table 表名 change 旧字段名 新字段名 类型[(宽度) 约束];
alter table t2 change y c char(10) not null; # 模式不同, 涉及类型转换问题

3.添加字段名
# 末尾添加
alter table 表名 add 字段名 类型[(宽度) 约束], ..., add 字段名 类型[(宽度) 约束];
alter table t2 add age int, add gender enum("male", "female", "wasai") default "wasai";

# 首尾添加
alter table 表名 add 字段名 类型[(宽度) 约束] first;
# 指定位添加：指定字段后
alter table 表名 add 字段名 类型[(宽度) 约束] after 旧字段名;
alter table t2 add y int after x;

4.删除字段名
alter table 表名 drop 字段名;
alter table t2 drop y;
5.移动字段名
alter table t1 modify 字段名 类型[(宽度) 约束] first;
alter table t1 modify 字段名 类型[(宽度) 约束]  after 指定字段名;


？？？
如果想删除自增约束，如何操作？
auto_increment 需跟在key后，比如自增的是id，表t1：
alter table t1 change id id int;

特殊表 (mysql.user) => 用户管理

# 操作前提：登录root用户
mysql  -uroot -p
1.重要字段
Host | User | Password

2.新建用户
create user 用户名@主机名 identified by '密码'; # 正确
create user zero@localhost identified by 'zero';

注：insert into mysql.user(Host,User,Password) values("主机名","用户名",password("密码")); # 错误

3.设置用户权限*
grant 权限们 on 数据库名.表名 to 用户名@主机名 [with grant option];
grant create on db1.* to zero@localhost with grant option;
# 权限有select,delete,update,insert,drop..., all代表所有权限
# 数据库名，表名可以用*替换，代表所有
# 设置权限时如果没有当前用户，会自动创建用户，提倡使用
*重点*: grant all on db1.* to owen@localhost identified by 'owen'; # (创建用户)设置权限

4.撤销权限
revoke 权限名 on 数据库名.表名 from 用户名@主机名;
revoke delete on db1.* from owen@localhost;

5.修改密码
set password for 用户名@主机名 = password('新密码');
set password for owen@localhost = password('123');

6.删除用户
drop user 用户名@主机名;
'''

表关系

社会中存储需要可以构建成表的数据, 它们形成的表,往往之间存储某种或某些社会关系,

mysql数据库建立表结构就是社会中产生的各种数据, 分门别类管理

但mysql建立的(代码层次的)表之间, 同样需要处理表与表之间的关系

形成了 多对一 | 多对多 | 一对一 三种关系

'''
外键直接影响数据库效率，但会提高数据的完整性（安全），一般首先效率，因为安全可以通过其他方式来处理
'''

多对一

案例：员工employees表 | 部门department表

建表规则：
先建立主表，再建立从表，在从表中设置主表的唯一字段(通常为主键)作为外键

建表语法：
create table 主表(
    id int primary key auto_increment,
    ...
);
create table dep(
    id int primary key auto_increment,
    name varchar(16),
    work varchar(16)
);
create table 从表(
    id int primary key auto_increment,
    ...
    主表_id int, # 只是在从表中起了一个名字, 该名字和主表主键对应,所有起了个见名知义的名字
    foreign key(主表_id) references 主表(唯一字段名id)
    on update cascade
    on delete cascade
);
create table emp(
    id int primary key auto_increment,
    name varchar(16),
    salary float,
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade # 设置级联
    on delete cascade
);

插入记录规则：
先插入主表数据，再插入从表数据
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1);

更新删除数据：
两表间相互影响，先从依赖数据入手，再进行更新删除操作
eg:1
删除主表dep中一个部门
delete from dep where id=1; => 从表emp中属于该部门的员工都被删除了

更新从表emp中一个员工的部门
update emp set dep_id=3 where name='egon'; <= 部门必须存在
insert into dep values(3, '管理部', '吃饭睡觉打豆豆, 明确团队方针');

多对多


案例：作者author表 | 书book表

建表规则：
新建第三张表，通过两个外键形成多对多关系

建表语法：
create table 表1(
    id int primary key auto_increment,
    ...
);
create table book(
    id int primary key auto_increment,
    name varchar(16),
    price int
);
create table 表2(
    id int primary key auto_increment,
    ...
);
create table author(
    id int primary key auto_increment,
    name varchar(16)
);
create table 关系表(
    id int primary key auto_increment,
    表1_id int,
    表2_id int,
    foreign key(表1_id) references 表1(id)
    on update cascade
    on delete cascade,
    foreign key(表2_id) references 表2(id)
    on update cascade
    on delete cascade
);
create table book_author(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);

一对一

为了提高效率，强行将海量数据中相关信息抽离成一个对应表来与依赖表形成一对一


案例：丈夫husband表 | 妻子wife表

建表规则：
未存放外键的表被依赖，称之为左表；存放外键的表示依赖表，称之为右表；先操作左边再操作右表

建表语法：
create table 左表(
    id int primary key auto_increment,
    ...
);
create table husband(
    id int primary key auto_increment,
    name varchar(16)
);
create table 右表(
    id int primary key auto_increment,
    ...
    左表_id int unique, # 一对一的外键需要唯一性
    foreign key(左表_id) references 左表(id)
    on update cascade
    on delete cascade
);
create table wife(
    id int primary key auto_increment,
    name varchar(16),
    husband_id int unique, # 一对一的外键需要唯一性
    foreign key(husband_id) references husband(id)
    on update cascade
    on delete cascade
);

练习

1.创建一个stu表，字段有：自增主键id，不为空姓名，默认值性别(枚举类型)，无限制身高

mysql> create table stu(id int primary key auto_increment,
    -> name varchar(20) not null,
    -> gender enum('male','female','wasai') not null default 'wasai',
    ->height float);

2.为stu表依次插入以下三条数据
i）插入一条包含id，name，gender，height四个信息的数据
insert into stu values(1,"xiaoming","male",170);
ii）插入一条name，gender，height三个信息的数据
insert into stu(name,gender,height) values("ming","male",170);
iii）插入一条只有name信息的数据
insert into stu(name) values("lal");

3.实现新表new_stu对已有表stu的字段、约束及数据的拷贝
create table new_stu like stu;
insert into new_stu select * from stu;  # 拷贝

4.创建一张有姓名、年龄的teacher表，在最后添加工资字段，在姓名后添加id主键字段
create table teacher(name varchar(20),age int);
alter table teacher add salary float;
alter table teacher add id int primary key after name;

5.思考：将5中id字段移到到表的最前方，形成最终字段顺序为id、姓名、年龄、工资
alter table teacher drop id;
alter table teacher add id int primary key first;

alter table teacher modify id int first;

6.完成 公民表 与 国家表 的 多对一 表关系的创建
mysql> create table country(id int primary key,
    -> name char);

mysql> create table people(id int primary key,
    -> name char,
    -> age int,
    -> country_id int,
    -> foreign key(country_id) references country(id)
    -> on update cascade
    -> on delete cascade
    -> );

7.完成 学生表 与 课程表 的 多对多 表关系的创建

create table stu1(
    id int primary key auto_increment,
    name varchar(16),
    age int
);

create table course1(
    id int primary key auto_increment,
    name varchar(16)
);

create table s_c(
    id int primary key auto_increment,
    stu1_id int,
    course1_id int,
    foreign key(stu1_id) references stu1(id)
    on update cascade
    on delete cascade,
    foreign key(course1_id) references course1(id)
    on update cascade
    on delete cascade
);

8.完成 作者表 与 作者简介表 的 一对一 表关系的创建（思考为什么要这样设计）

create table author(
    id int primary key auto_increment,
    name varchar(16)
);

create table jj(
    id int primary key auto_increment,
    name varchar(16),
    author_id int unique, 
    foreign key(author_id ) references author(id)
    on update cascade
    on delete cascade
);

练习

查询语句

sql_mode配置

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

增语法

1.所有数据按顺序插入
insert [into] 表名 values (值1, ..., 值n)[, ..., (值1, ..., 值n)];

2.指定字段匹配插入，可以任意顺序
insert [into] 表名(字段2, 字段1, ..., 字段n) values (值2, 值1, ..., 值n)[, ..., (值2, 值1, ..., 值n)];

3.插入查询结果
insert [into] 表1(字段1, ..., 字段n) select 字段1, ..., 字段n from 表2 [条件];# 表2与表1字段相同或要插入至表1的字段与表1相同时，表1后字段可以*替
# eg:1
create table t1(
    id int auto_increment,
    x int,
    y int,
    primary key(id)
);
insert t1 values(1,2,3),(2,3,4);  # 按顺序插入
insert into t1(y,x) values (5，4);  # 按规定字段顺序

create table nt1 like t1;复制表即完整结构
insert into nt1 select * from t1;  # 创建新表并按照字段顺序指定插入复制数据

删语法

1.会记录自增信息，操作会被日志记录，效率低
delete from [库名].表名 [条件];
delete from t1;  # 没有条件的情况下是清空所有数据。但会记录自增的约束
insert into t1(x,y)values(6,66)

2.清空表，会重置自增信息
truncate table 表名;
truncate table nt1;
insert into nt1(x, y) values(6, 66);

改语法

update 表名 set 字段1=值1[, ..., 字段n=值n] [条件]
updata tt1 set x=666;# 无条件，全改
update tt1 set x=777, z=555 where z<888; # 只修改满足条件的行
没有满足条件的不改变

查询语法

select [distinct] 字段1 [as 别名], ..., 字段n [as 别名] from [库名.]表名
    [
        where 约束条件
        group by 分组依据 =》查询结果只能为聚合结果或分组字段
        having 筛选，过滤条件=》对聚合结果的筛选
        order by 排序的字段=》asc|desc
        limit 限制显示的条数 =>n| i,n
    ];
注：
1.查表中所有字段用*表示
2.条件的书写规则严格按照语法顺序书写，可以缺省，但不可以错序
3.约束条件的流程：from -> where -> group by -> having -> distinct -> order by -> limit
4.字段可以起别名
5.字段可以直接做运算  select age + 1 'new_age' from emp;
6.分组后的条件均可以使用聚合函数

"""
3.
def from():
    return "查询的文件"
def where(file):
    return "条件筛选后的结果"
def group_by(res):
    return "分组后的结果"
def having(res):
    return "再次过滤后的结果"
def distinct(res):
    return "去重后的结果"
def order_by(res):
    return "排序后的结果"
def limit(res):
    return "限制条数后的结果"

def select(from=from, where=null, ..., limit=null):
    file = from()
    res = where(file) if where else file
    res = group_by(res) if group_by else res
    ...
    res = limit(res) if limit else res
    return res
select(where=where, group_by=group_by)
"""

单表依赖数据

CREATE TABLE 'emp'  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `gender` enum('男','女','未知') NULL DEFAULT '未知',
  `age` int(0) NULL DEFAULT 0,
  `salary` float NULL DEFAULT 0,
  `area` varchar(20) NULL DEFAULT '中国',
  `port` varchar(20) DEFAULT '未知',
  `dep` varchar(20),
  PRIMARY KEY (`id`)
);

INSERT INTO `emp` VALUES 
    (1, 'yangsir', '男', 42, 10.5, '上海', '浦东', '教职部'),
    (2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
    (3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
    (4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
    (5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
    (6, 'zero', '男', 28, 8.8, '中国', '黄浦', '咨询部'),
    (7, 'owen', '男', 28, 8.8, '安徽', '宣城', '教学部'),
    (9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
    (10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
    (11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
    (12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
    (13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
    (14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部');

常用内部函数

concat(字段1,...,字段n)：完成字段的拼接
concat_ws(x, 字段1,...,字段n)：完成字段的拼接，x为连接符
lower()：小写
upper()：大写
ceil()：向上取整
floor()：向下取整
round()：四舍五入

简单查询

去重前提：所查所有字段的综合结果完全一致/相同，才认为是重复的，只保留重复中的一行数据
select distinct * from emp;  # 去重，去的是所查结果完全相同的

select contat(area,'-',port)[as 'xx'] from emp;  # 上海-浦东 属于起的别名xx 列
select contat_ws("-",name,area,port)[as 'xx'] from emp; #以"-"字符拼接后面的所有字段
select upper(name) 'name', gender, age from emp; # 可以指定多个字段
select name, ceil(salary), floor(salary), round(salary) from emp where name='kevin'; # 数学函数

where 条件

1.比较运算符
= | < | > | <= | >= | !=
select * from emp where area!="上海";

2.区间运算符
between 10 and 20：10~20
in(10, 20, 30)：10或20或30
select * from emp where id between 3 and5;  # [3,5]闭合的区间
select * from emp where id in (2,4,6,8);# 分离的区间  2,4,6,8,10,12,14都会被显示

3.逻辑运算符
and | or | not
select * from emp where area="山东" and port="济南";

4.相似运算符
like '_owen%'：模糊匹配字符串owen，_表示一个字符，%表示任意字符
# 匹配的字段为en,想得到的结果为owen
select * from emp where name like '__en%'; # 在en前可以出现2个任意字符, 之后可以出现0或多个任意字符

需求：
查找姓名有数字的员工信息

正则匹配

对like的扩展，like完成模糊匹配，但功能局限，可以模糊个数，但不能模糊类型。正则可以完成类型及个数的模糊匹配

语法：字段 regexp '正则表达式'
注：只支持部分正则语法

select * from emp where name regexp '.*[0-9]+.*';

group by 分组

注意：分了组，一个组就是一个整体，你不能直接拿到组里的具体的某一条信息（安全模式下会报错），但是你可以用比如max的方法取出每个组信息里的最大值

分组：根据字段相同值形成不同的类别，不明确分组其实整个表就为一个默认大组
原因：把以值共性得到的类别作为考虑单位，不再关系单条记录，而且一组记录

结果：只能考虑组内多条数据的聚合 结果 （聚合函数结果），分组的字段同样是聚合结果，如：组内的最大最小值
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 

聚合函数：
max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：记数
group_concat()：组内字段拼接，用来查看组内其他字段
eg:1
每个部门的平均薪资
select dep, avg(salary) '平均薪资' from emp group by dep;
eg:2
每个部门都有哪些人
select dep, group_concat(name) from emp group by dep;


需求：
各性别中附属于教学部的最高薪资
select max(salary) '最高薪资', gender from emp where dep='教学部' group by gender;

思考：
想知道需求中员工的姓名 => 子查询
思考：
想知道需求中员工的姓名 => 子查询
上方结果: 男的最高薪资对应的人名, 女的最高薪资对应的人名
# select group_concat(name), max(salary) '最高薪资', gender from emp where dep='教学部' group by gender; 错误
select  max(salary)"最高薪资",gender,name from emp where dep = "教学部" group by gender;
'''

'''
1. 14条数据部门有3个, 并且每个部分有多条记录, 可以作为分组依据, 同理, 性别也可以
# select * from emp group by dep; # 非分组安全模式下, 可以查询非聚合结果, 显示的是第一条记录, 没有意义, 分组安全模式下不能查询非聚合结果的字段
select dep from emp group by dep;


2. 如果就像以姓名进行分组, 可以, 但没多大意义, 原因name值基本上都不相同, 以组考虑会导致组内大多只要一条记录(自成一组), 组的利用就不是很强烈, 此类分组是无意义的
select name from emp group by name; # 可以分组, 意义不大

考虑的三个问题: 以什么分组(相同数据较多的字段) 分组后的考虑单位(组并非组内的每一条记录) 可以查询的结果(当前分组的字段及聚合函数形成的聚合结果)

解决分组中思考题的过程

# res = select max(salary) '最高薪资', gender from emp where dep='教学部' group by gender;
# select name from emp where (salary 跟 res作比较)
# 一个查询依赖于另一个查询的结果 => 一个查询的结果作为另外一个查询的条件 => 子查询

子查询

子查询：将一条查询结果作为另外一条查询的条件
语法：一条select语句用()包裹得到的结果作为另一条select语句的条件
# 伪sql: select * from emp where salary =|in (select salary from emp where 条件)

单行子查询：
子查询语句的结果为一行数据，可以结合 = | < | > | <= | >= | != 运算符来完成父查询
select salary from emp where salary > 10; # => 作为子查询
# 查询姓名,性别.地区,基于薪资大于10的结果的查询结果
eg: 1
select name, gender, area from emp where salary = (select salary from emp where salary > 10);

多行子查询：
子查询语句的结果为多行数据，可以结合 in | all | any 运算符来完成父查询
in：任意单一值，只能考虑子查询中的一个结果
all：全部值，将子查询结果作为一个整体考虑
any：任意多个值：子查询的每一个结果都可以作为参考依据
eg: 2
# 子查询的结果 (9.4, 3)
select name from emp where salary in (select max(salary) '最高薪资' from emp where dep='教学部' group by gender);
# 遍历14条数据, 14条数据的salary在(9.4, 3)区域中,就可以完成匹配, 结果为两条(9.4和3那两条)

select * from emp where salary < all(select max(salary) '最高薪资' from emp where dep='教学部' group by gender);
# 遍历14条数据, salary要小于(9.4, 3)中的每一个, 反映就是小于3, 结果为薪资1.2,2.4的那四条数据

select * from emp where salary > any(select max(salary) '最高薪资' from emp where dep='教学部' group by gender);
# 遍历14条数据, salary大于9.4或大于3的数据均满足条件, 结果就是刨除小于等于3的那几条数据

having 筛选

why：完成在分组之后的筛选
注意：having条件是实现聚合结果层面上的筛选 => 拿聚会结果完成判断

需求：
1.各部门的平均薪资
select dep, avg(salary) '平均薪资' from emp group by dep;

2.平均薪资大于6w的部门(部门与部门的平均薪资)
解决: 以dep进行分组, 以avg(salary)作为判断条件(筛选)
select dep, avg(salary) '平均薪资' from emp group by dep having avg(salary) > 6;

# 总结: having通过聚合函数结果完成筛选
select max(salary) from emp having max(salary) > 9.4;
# 虽然没有明确书写group by, 但在having中使用了聚合函数,所以该查询就将整个表当做一个默认大表来考虑,所以查询的字段只能为聚合函数的结果

order by 排序

why：完成排序
注意：可以使用聚合函数，哪怕没有明确group by

升序 | 降序：asc | desc
eg：order by age desc => 按照年龄降序
select * from emp order by age desc;

需求：
将部门按照工资降序方式排序 
select dep, avg(salary) from emp group by dep order by avg(salary) desc;

limit 限制

why：限制最终结果的显示数据行数
注意：limit只与数字结合使用

应用：
limit 1：只能显示一行数据
limit 6,5：从第6+1行开始显示5条数据(索引从0开始)
select * from emp limit 1;
select * from emp limit 6,5;

需求:
获得薪资最高的人的一条信息
select * from emp order by salary desc limit 1;

练习

1、查询教学部山东人的平均薪资

select avg(salary)"平均薪资",area from emp where dep="教学部" group by area having area="山东";
select avg(salary)"平均薪资",dep from emp where area="山东" group by dep having dep="教学部";
select avg(salary)"平均薪资",area,dep from emp where dep="教学部" and area="山东";

2、查询姓名中包含英文字母n并且居住在上海的人的所有信息

select area,name from emp where area="上海" and name regexp ".*n.*";

3、查询姓名中包含英文字母n但不包含数字的人的所有信息

select * from emp where name regexp ".*n.*"and name not regexp ".*[0-9]+.*";

4、查看各部的平均年龄并升序排序。升序时，asc可省

 pythonx select avg(age),dep from emp group by dep order by avg(age) asc;

5、思考：按照年龄升序的基础上再姓名降序查看所有人的信息

select * from emp order by age asc,name desc;

6、查询各部门中年纪最大的人的姓名与居住地（户籍+区域）

select name,dep,concat_ws("-",area,port)"居住地" from emp where(age,dep) in (select max(age),dep from emp group by dep);

select  name, concat(area, '-', port), dep from emp where (age,dep) in (select max(age),dep from emp group by dep);

7、查询不同年龄层次平均薪资大于5w组中工资最高者的姓名与薪资

select age,name,avg(salary)"平均薪资",max(salary)"最高薪资" from emp group by age having avg(salary)>5 ; # 非安全模式下可行，但name不是聚合，不可直接查，安全模式下报错
select age,name,salary from emp where (salary,age) in(select max(salary),age from emp group by age having avg(salary)>5);

练习

多表操作

多表依赖数据

create table dep(
    id int primary key auto_increment,
    name varchar(16),
    work varchar(16)
);
create table emp(
    id int primary key auto_increment,
    name varchar(16),
    salary float,
    dep_id int
);
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);

笛卡尔积(交叉连接)

# 需求: 
# 查看每位员工的部门的所有信息
select * from emp;
select * from dep;

# 子查询, 最终结果只能显示单表的信息, 但需求是同时显示两张表的信息 => 先将两张表合成一张表
select * from emp where dep_id in (select id from dep); # Empty set (0.06 sec)

默认查询结果：笛卡尔积
select * from emp, dep; # Empty set (0.07 sec)
数学概念：A{a, b}*B{0, 1, 2} => O{{a, 1}, {a, 2}, {a, 3}, {b, 1}, {b, 2}, {b, 3}}
交叉查询：select * from emp,dep;| select * from emp course join dep;

# 做了筛选, 结果<=完整数据, 非笛卡尔积
select * from emp, dep where db2.emp.dep_id = db2.dep.id;  # 同sql语句上表现是从两张表拿数据

# 注意: 同时查询两张表形成新的表,可以称之为虚拟表, 原表与表之间可能存在重复字段, 同时使用时需要明确所属表,必要时还需明确所属数据库

合理对应关系的虚拟表

select * from emp, dep where emp.dep_id = dep.id;
注：虚拟表中可能出现重复字段，需要在sql语句中明确实体表名

多表连接(*****)

===>虚拟的单表

用专业的语法来关联多表

内连接

内连接：结果为两张表有对应关系的数据(emp有dep无\emp无dep有的数据均不会被显示)
语法：左表 inner join 右表 on 两表有关联的字段的条件
eg：
select * from emp inner join dep on emp.dep_id = dep.id;

左连接

左连接：在内连接的基础上还保留左表的记录
语法：左表 left join 右表 on 两表有关联的字段的条件
eg：
select * from emp left join dep on emp.dep_id = dep.id;

右连接

右连接：在内连接的基础上还保留右表的记录
语法：左表 right join 右表 on 两表有关联的字段的条件
eg：
select * from emp right join dep on emp.dep_id = dep.id;

全连接

#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接

全连接：在内连接的基础上分别保留这左表右表的记录
语法：mysql没有full join on语法，但可以通过去重达到效果
eg：
select * from emp left join dep on emp.dep_id = dep.id
union  #　？？？
select * from emp right join dep on emp.dep_id = dep.id;

练习

1.查询每一位员工对应的工作职责
# 每一位员工 => 左表为emp表, 那么左表的所有数据均需要被保留, 所有采用左连接
           => 左表为dep表, 那么右表的所有数据均需要被保留, 所有采用右连接
# select emp.name, dep.work from emp left join dep on emp.dep_id = dep.id;
select emp.name, dep.work from dep right join emp on emp.dep_id = dep.id;

2.查询每一个部门下的员工们及员工职责
# select max(dep.name), max(dep.work), group_concat(emp.name) from emp right join dep on emp.dep_id = dep.id group by dep_id;

# 分析过程
# 每一个部门 => dep的信息要被全部保留, 需要分组
# 员工职责 => dep.work, 由于分组不能直接被查询 => 需要用聚合函数处理
# 员工们 => emp.name做拼接 => group_concat(emp.name)
# 分组的字段 => 部门 => emp.dep_id => emp.dep_id可以直接被查询,但没有显示意义 => dep.name用来显示 => dep.name需要用聚合函数处理

select max(dep.name), max(dep.work), group_concat(emp.name) from dep left join emp on  emp.dep_id = dep.id group by emp.dep_id;

# 注: on在where条件关键词之左

转载于:https://www.cnblogs.com/zfb123-/p/11588055.html

weixin_30436101

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库基础加练习

数据库基础1.什么是数据库用来存储数据。数据库可在硬盘及内存中存储数据数据库与文件存储数据的区别数据库本质也是通过文件来存储数据，数据库的概念就是系统的管理存储数据的文件数据库介绍本质就是存储数据的C/S架构的socket套接字软件数据库服务器端：存放数据的主机集群数据库客户端：可以连接数据库的任意客户端数据库管理员：DBA2.数据库...
复制链接

扫一扫