Web阶段第四天（SQL语句）

day04
1.数据库概述
数据库就是一个存储数据的仓库。
数据库的发展：层次式数据库，网络型数据库，关系型数据库。
关系型数据库：以行和列的形式存储数据。
注：数据库中的列也叫做字段或域。
数据库四大特性：原子性、一致性、隔离性、持久性
2.SQL语言
Structured Query Langugae 结构化查询语言
非过程性语言（上下句之间没有逻辑关系）
2.1操作数据库
创建数据库
create database database_name;
查看数据库
show databases;
修改数据库
alter database mydb2 character set utf8;
删除数据库
drop database database_name;
选择数据库
use database;
2.2操作数据库中的表
首先使用数据库use database_name;
创建表
create table tbale_name(field1 datatype,field2 datatype);
查看表
show tables;
查看当前数据库表建表语句
show create table tabName;
修改表
使用 alter table 语句追加, 修改, 删除列的语法：可以添加或者修改多个字段，各字段之间用逗号隔开。
alter table table_name add column_name datatype;
alter table table_name modify column_name datatype;
alter table table_name drop column_name;
rename table 表名 to 新表名;
修改列的名称：
alter table table_name change old_column new_column typefiled;// typefiled表示字段类型。
修改表的字符集：
alter table user character set utf8;
删除表
delete table table_name;
2.3约束关系
2.3.1
主键约束 primary key 主键不允许为空，不允许重复。
删除主键：alter table tablename drop primary key ;
2.3.2
自动增长约束 auto_increment 只有主键才可以设置自动增长，而且type必须为int类型。
2.3.3
非空约束 not null
例：salary double not null;
2.34
唯一约束 unique
例：name varchar(20) unique;
外键约束 foreign key
用来通知数据库表与表字段之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键
外键作用: 维护数据的完整性 一致性
定义外键约束
foreign key(ordersid) references orders(id);
例：create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key(dept_id) references dept(id)
);
拓展：外键的使用场景。
在面对高吞吐的开发场景时，如果使用外键则可能会导致大量数据无法进入数据库中，造成数据流失，形成不可逆的后果。高吞吐场景下应该优先保证数据入库，然后再将入库的数据做过滤和筛选，选择出合理正确的数据。所以在真实开发中外键使用的情景基本没有。
2.4表间关系
在数据库中可能会涉及到两张及以上表的操作，多个表之间存在一定的关系，关系模型有以下三种：
一对一：在一对一中可以在任意一张表中建立另一张表的id字段，这个字段就是用来维护两张表之间的关系的字段。
一对多：在一对多中必须在多的关系表中建立另一张表的id字段，这个字段用来维护两张表之间的关系。
多对多：在多对多的关系中可以通过中间表来维护表间关系，中间表需要填写两张表的id字段，通过这两个id字段维护表间关系。
2.5操作数据库表中的数据（CRUD）create read update delete
2.5.1
插入数据
insert into table_name value(column_value,column_value,…);
2.5.2
更新数据
update table_name set column_name = value;
例：update employee set salary = 5000;
在更新语句中，可以添加where子句，where子句当中包含的就是判断条件，符合条件的数据才会发生更新报错。若不添加，则表中的对应列中所有内容都会改变。
update employee set salary = 10000 where name = ‘曹洋’;
2.5.3
删除数据
delete from employee;
在删除数据的时候如果不使用where则表示将全表数据都删除，使用where表示删除满足条件的数据。
delete from table_name where column_name = value;
delete from employee where id = 2;
truncate 删除数据：
truncate employee;
truncate在删除数据的时候，将整个表格摧毁并重建，所以表中原有的表关系和索引等内容都会重新建立。
truncate在面对多表关系时不适用，因为表间关系可能无法重建，适用于单表删除。
delete删除表中数据，不会重建表结构，仍然沿用已有的索引，在多表和单表中都适用。
2.5.4
查询数据
基本查询语句
slecet * from table_name
select 查询
* 全部字段名称
from 选择表
select * 表示查询全部字段，还可以使用select具体的字段名称来查询指定的字符。
思考： select * 和 select id,name,gender,birthday,entry_date,job,salary,resume两种查询方式谁的查询效率更高？
答：
第二种方式效率更高。因为第一个种查询方式无法使用索引。第二种在字段名称中有索引字段存在，所以可以使用索引查询，使用索引查询效率会高于不使用索引。
distinct去重
select distinct |colunmn_name from table_name;（要么写 要么写上条件列）
employee表中原6条数据，去重结果查询如下：
select distinct name from table_name;
在select语句中使用表达式对查询的列进行运算（不会改变表中原有数据的内容）
select * column_name as 别名 from table_name;
例：select * username as name from user;
使用where语句进行过滤查询
select * column_name from table_name where …
where子句中常用的运算符
比较运算符：< > <= >= = <> 小于 大于 小于等于 大于等于 等于 不等于
between…and… 显示在某一区间的值。
in(set) 显示在in列表中的值，例：in(“计算机系”,“英语系”);
like ‘张pattern’ 模糊查询，pattern表示表达式 %表示出现任意个字符 _表示只能出现一个字符。 例：first_name like ‘_a%’;
is null 判断是否为空 例：select * from user where id is null;
ifnull(原值,替代值);如果原值为null，则使用替代值 例：select ifnull(score,0) from exam;原值一般为列名。
逻辑运算符：and 多个条件同时成立
or 多个条件任一成立
not 不成立 例：where not(salary>100);
使用order by子句排序查询结果
select column_name from tbale_name order by column_name asc|desc
order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
asc 升序（默认） desc 降序
注：order by 子句应该位于select语句的结尾，而且order by在排序的过程中不能使用别名。
聚集函数 —— count
count（列名）返回某一列中行的总数。
select count(*)|count(列名) from tbale_name where …;
聚集函数 —— sum
sum函数返回满足where条件的行的和。
select sum(列名) from table_name where…;
聚集函数 —— avg
avg函数返回满足where条件的一列的平均值。
select avg(列名) from table_name where…;
聚集函数 ——max/min
max/min函数返回满足where条件的一列的最大/最小值。
select max/min(列名) from table_name where…;
select语句分组
使用group by子句对列进行分组。
select column_name from table_name group by column_name having …;
where和having区别：where在分组前进行条件过滤，having在分组后进行条件过滤。使用where的地方都可以用having替换。但是having可以使用分组函数，而where后不可以使用
select语句中的执行优先级
from–where–group by–having–select–order by。
*多表查询
笛卡尔积查询：在查询语句中，选择两张表，表结果数据会是左边表的行数，乘以右边表的行数（左边表m行，右边表n行，结果行数为mn）
select * from dept,emp where …;
内连接查询
在笛卡尔积的查询基础之上，获取左边表有且右边表也有的数据，这种查询方式称之为内连接查询。
从笛卡尔积中选择满足条件的结果数据：
select * from dept,emp where dept.id = emp.dept_id;
inner join…on 关键字表示内连接查询。
select * from dept inner join emp on dept.id = emp.dept_id;
外连接查询：基于内连接
左外连接查询：在内连接查询的基础之上，获取左边表有且右边表没有的数据，这种查询称之为左外连接查询。
left join … on 关键字 表示左外连接查询。
select * from dept
left join emp
on
dept.id = emp.dept_id;
表使用别名查询：
select * from dept d
left join emp e
on
d.id = e.dept_id;
右外连接查询：在内连接查询的基础之上，获取右边表有且左边表没有的数据，这种查询称之为右外连接查询。
right join … on 关键字表示右外连接查询。
全外连接查询：在内连接查询的基础之上，获取左边表有且右边表没有的数据和右边表有且左边表没有的数据，这种查询称之为全外连接查询。
full join —mysql中没有这个关键字。
union ---- 联合 将两个查询结果合并到一张表中展示，重复的数据会去掉。
select * from dept
left join emp
on
dept.id = emp.id
union
select * from dept
right jion emp
on
dept.id = emp.id;