一:Insert
- 三种方式
- 1: 省略列名, 直接全部字段插入
- 2: 部分插入, 列名和 值名 一一对应
- 3: 全部插入列名, 顺序变化, 值也做相应的调整。
二 : 常用命令
- create database: 创建数据库
- use database: 使用数据库
- create table 表名 : 建表
- drop table 表名: 删表
- desc 表名 : 查看表结构
- insert : 插入数据。未插入数据的表中的默认是 NULL
- select * from 表名:查询
三: 奇怪的东东
NULL : 既不表示 0 , 也不表示 空“ ‘ ’”, 表示的是未定义
- 在创建表的时候, 可以使用
not null
进行定义。DEFAULT :默认值
- 可以在建表的时候进行指定。
where 子句: 为 RDBMS 提供特定的查询条件。
- 注意事项:
- datatime, time, timestamp :中 使用 引号: ‘ ’
- dec, decjmal 的缩写时, 不能使用 引号
- int, integer 不适用引号
- char, character 通常使用引号
- blob : 必用引号
- date: 必用引号
" * " : 含义: 告诉 RDBMS 返回查询表中所有的 列。
dec(6,2): dec 用于表示小数类型: 共6位数, 小数点后两位
如果想要保留特殊的字符时, 可以使用 “\" 进行字符的转义。
- 在 插入数据时, 可能出现 错误。warning
- 解决: (1): 加入转义字符, (2): 加入" ’ "
select + 特定的列 时, : 会加快检索的速度。
三: 外键
- 外键是表中的某一列, 他引用到另一个的主键
外键可能与他引用的主键的名称不同
外建使用的主键的也被称为父键(parent Key)
主键所在的表有被称为父表(Parent Table)
外键能用于确认一张表中的行与另一张表中的行相对应
外键的值可以是 NULL, 即使主键值不可以为 NULL
外键值不需要唯一 事实上, 外键通常都没有唯一性。
- 外键为 NULL, 表示在 父表中没有相符合的主键
- 但是我们可以确认外键所包含的意义, 已存在父表中的值, 通过约束(constrant ) 来实现。
- 注意:
- 插入外键列的值必须已经存在于 父表的来源中, 这是引用完整性(referntial integrity)
- 你可以使用外键来引用父表中某个唯一的值
- 外键不一定是父表中的主键, 但必须有唯一性。
外键的作用:
- 创建外键约束后,就只能插入已经存在父表中的值,有助于加强两张表间的链接。
- 如果试图删除主键表中的行或者改变主键值,主键的其他外键约束就会发出警告。
- 如果
四: 原子性(atom)
- 它们是一块无法或者应该分割的信息
- 对于数据而言, 当数据具有(atomic), 就表示已经分割成最小块,已经不能在进行分割了。
- 原子性数据的两个规则:
- 1: 具有原子性表示在同一个列中不会存在多个数据类型相同的数据。
- 2:不会用多个列来存在类型相同数据。
五:主键
- 主键不可以为 NULL,
- 插入的新纪录时,必须指定主键值
- 主键必须简洁
- 主键不可以被修改
show create table table_name:
可以查看创建表的语句1
在 RDBMS 中,有时无法分辨列名,:就可以以 SQL 保留字作为列名。
六: 一些常用的语句:
alter tabel Student add column contact_id int not null auto_increment first, add primary key (contact_id); -- first : 新加字段位于 首部 -- auto_increment : 主键自增。 -------------------------------------------------------------------------- alter table Student add column phone varchar(10) after time_size; -- after : 在某个列名的下一个。默认添加的时候在 最后。 -- before, last, second , third ------------------------------------------------------------------------------- alter table projeka rename to new_projeke; -- 修改表名 ---------------------------------------------------------------------------------- alter talbe table_name drop primary key; -- 删除主键 ------------------------------------------------------------------------------ alter table table_name change you_id int(11) not null auto_increment; -- 删除字段自增:(去掉关键字:auto_increment 即可); ----------------------------------------------------------------------------------- update talbe_name set column_name case when drama = '1' then 'true' when drama = '2' then 'false' when drama1 = '1' then 'true' when drama2 = '2' then 'false' else 'music' end; ---------------------------------------------------------------------------- select * from table_name order by Grade limit(0, 5); -- index 0, 1, 2, 3, 4 -- 查询下标为 : 0,1,2,3,4: 的数据。共计五条数据 -- limit(第一个数据的下标,总共几条)。 ---------------------------------------------------------------------- 创建带有外键的表 create table interests ( int_id int not null auto_increment primary key, interest varchar(50) not null, contact_id int not null, constraint my_contacts contact_id -- constraint 告知我们外键的来源,: -- 命名方式:键的来源表+ 键的名称 说明是个外键 foreign key (contact_id) -- 括号中代表外键可以随意命名 references my_contacts (contact_id)-- my_contacts : 外键的来源, contact_id:外键在另一张表中的名称 ); ------------------------------------------------------------------------------ SELECT -- ########分组###### ccm_no_n, count( ccm_no_n ) FROM t6c0108 where ccm_no_n > 5 -- 分组中 where 使用 GROUP BY CCM_NO_N HAVING -- 分组中 having 使用 count( * ) >= 2 ORDER BY -- 排序 ccm_no_n DESC; ------------------------------------------------------------------------------------ -- Oracle 中显示创建表的语句 select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual ; -- table_name 表名必须大写。 --------------------------------------------------------------------------------- create table profession ( id int(11) not null auto_increment primary key, profession varchar(20) ) as -- 引用某个查询的结果插入到另一个表中 select profession from my_contacts group by profession order by progession; -------------------------------- create table profession as -- 如果不指定列,默认创建一个和select相同的列和字段类型。 select profession from my_contacts group by profession order by profession; alter table profession add column id not null auto_increment first, add primary key (id); ----------------------------- as : 可以作为起别名, 只在查询的表中起到临时的作用。 select profession as mc_prof -- as 可以省略, 给表名和 column_name 去取名字 from my_contacts as mc group by mc_prof order by mc_prof;
七: 关键字:
change : 可以同时改变现有列的名称和数据类型。
modify: 修改现有列的数据类型和位置
add: 在当前的表中添加一列: 可自定数据类型
drop: 从表中删除数据: (可以先使用: select 进行一遍数据查询。)
order by: 排序
group by : 分组
distinct : 去重。
八: SQL 的关键术语
- Schema : 数据库模式
- 数据库模式, 描述数据库中的数据, 其他相关对象,以及这些对象的连接方式。
- One-to-one
- 一对一的关系,
- 父表中的一行记录只与子表中的一行记录相关联。
- One-to-Many
- 一对多的关系
- 一张表中的一行记录可能与另一张表中的多行记录相关联,但是一张表中的任何一行记录只会与前一张表中的一行记录相关联。
- Many-to-Many
- 多对多的关系
- 两个通过 junction table 连接的表,让一张表中的多行记录能与另一张表中的多行记录相关联,反之亦然。
- Fisrt normal form(1NF)
- 第一范式:
- 列中只包含原子性数据,而且列内没有重复的数据组。
- Transitive function dependency
- 传递函数依赖
- 指任何非键列依赖于另一个非键列
- Second normal form(2NF)
- 第二范式
- 表必须符合 1NF, 同时不能包含部分函数依赖性,才算满足 2NF
- Third normal form (3NF)
- 第三范式
- 表必须先符合 2NF, 同时不包含可传递函数依赖
- Foreign key
- 外键
- 引用其他表的主键的列
- Composite key
- 组合键:
- 由多个列构成的主键,这些列形成唯一的键值。
- Not exists :
- 表示数据在表中不存在。
- temporary : 创建临时表
create temporary table table_name as select * from table_name1
九:一些常用的函数
对多个字段进行截取
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QSo530PR-1593333307841)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200615080135293.png)]
在一个列中取出第一个分隔符后面的数据之后,删除前一个字段
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YlIDEmVb-1593333307843)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200615080247237.png)]
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UjSyCDvU-1593333307846)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200615080321733.png)]
字符串函数进行截取操作
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ppnkT2BZ-1593333307849)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200615080409282.png)]
交叉连接 (cross join) :
- 组合出两个列表中的组合的所有情况。
- cross join : 返回出两种表中每一行相乘的结果。
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NrrSoumo-1593333307850)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200615082742943.png)]
内连接 – inner join
内连接就是: 通过查询中的条件移除了 某些结果数据行的交叉连接。
语法格式
select somecolumns from table1 inner join table2 on(where) somecondition;
- 解释:somecolumns : 需要查询的 列
- table1 和 table2 数据来源的两张表
- on 也可以使用 where : 进行条件的判断。
- inner join : 利用条件式里的比较运算符结合张表。
内连接的分类
- 相等连接 (equi-join):判断条件相等
- 不等连接(non-equi-join): 判断条件不相等
- 自然连接 (natural-join) : 就是两张表中有相同的列字段名。
十 : 多条件查询语句
(1)使用关键字:in
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FTC8LguH-1593333307852)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200615092314633.png)]
(2)函数子查询的语句
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KaVEPU67-1593333307854)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200615092726790.png)]
- 大多数情况下, 内层查询只能返回单一值, 就是一个列里的一行。外层查询根据这个进行查询
- 因为 在 IN 运算符中会寻找一组值 的集合, 如果使用 比较运算符, 如 “=”, 就只能接受一个值,与列中的其他值进行比较。
(4): 非关联子查询 (noncorrelated subquery)
- 如果子查询可以独立运行且不会引用外层查询的任何结果, 即称为非关联子查询。
- 非关联子查询使用 IN 或 Not IN 来检查子查询返回的值是否为集合的成员之一。
Outer query (Inner query)
- Outer query : 外层查询较晚, 他的查询结果取决于内层查询
- Inner query : 内层查询可以单独运行,连接会先处理这个部分。
(5): 交叉连接和关联子查询都是会拖慢查询效率的。
(6): (联合) UNION 的使用
- Union 把多张的表的查询结果整合到一张表中
- union 只能接受一个 order by 且必须位于语句的末端。
- 原因: union 已经把多个 select 语句的查询结果串联起来并分组了。
- SQL 联合规则
- 每个 select 语句中列的数量必须一致。
- 不可以第一个语句选取了一列, 其他的选取了两列。
- 每个 select 语句包含的表达式与统计函数也必须相同
- select 语句的顺序不重要,不会改变结果
- SQL 默认会清除联合的结果中重复的值
- 列的数量类型必须相同或者可以相互转换。
- 类型转换的时候,进行的是: intger 转换为 varchar 类型。
- 如果想要看到重复的数据, 可以使用 union all 运算符,
- 这个运算符返回每个相符的记录, 而不只是有重复的记录。
- 两个关键字 (不在 Mysql 中使用)
- intersect : 交集
- except : 差集
- 以上两者的用法和 union 相同
- 联结和子查询都可以实现, 查询效果
- 但是效率方面: 联接的效率相对高一点。
(6) :小结汇总
self-join : 自联接
- self-join 能用 一张表作出联接两张完全的效果
self-referencing foreign key
- 自引用外键:这种外键就是同一张表的主键, 但作为其他的用途
union 和 union all
- union : 根据select 指定的列合并两个或者多个查询结果为一张表
- union :默认是隐藏重复的值, union all : 则可包含重复的值。
create tabel as
- 使用本命令从任何 select 语句的结果创建表。
left outer join
- 左外连接
- 接受左表中的所有记录,并从右表对比出相符合的记录
right outer in
- 右外连接
- 接受右表中的所有记录,并从左表对比出相符合的记录。
intersect
- 使用这个关键字返回同时存在于第一个和第二个查询中的 值
except
- 使用这个关键字返回在第一个查询中但不在第二个查询中的值。
十一: 约束、视图与实务。
(1):约束
- 约束(constraint): 限定了可以插入列的内容, 而在我们创建表时, 就要加入约束
- 如:not null, primary key, foreign key, unique
- check : 约束
- check 检查约束限定允许插入的某个列的值, 他与where 子句都是用相同的条件表达式。
- check constraints
- 检查约束, 可以只让特定值插入或更新至表里。
- check option
- 创建可更新视图时, 使用这个关键字强迫所有插入与更新的数据都需要满足视图里的where 条件。
(2): 视图
何为视图
- 基本上视图是一个只有在查询中使用 view 时,才存在的表,他被视为虚拟表(virtual table), 因为其行为和表一样,也能执行表可用的操作。
- 虚拟表不会一直保存在数据库。
视图的优点
- 视图把复杂的查询简化为一个命令,带来更轻松的生活。
- 即使一直改变数据库结构,也不会破坏依赖表的应用程序。
- 创建视图可以隐藏读者无需看到的信息。
案例:(简化查询)
create view job_raises as select mc.first_name, mc.last_name, mc.email, mc.phone, jc.contact_id, jd.salazy_low,jd.salazy_low - jc.salazy as raise from job_cuzzent jc inner join job_desized jd inner join my_contacts mc where jc.contact_id = jd.cotact_id and jc.contact_id = mc.contact_id; ---------------------------------------------------------------------- -- 创建视图之后, 查询部分使用了 两个 inner join , -- 这是一个复杂的查询, 简化为视图之后, 就可以使用:select * from job_raises. -- 就能看到想要的信息。
3: 事务 (transaction)
- 是 一群可以完成一组工作的 SQL 语句。
- 事务的特性: ACID 检测
- A: atomicity : 原子性
- 事务里的每一个步骤都必须完成, 否则都完成不了。
- C: consistency: 一致性
- 事务完成后应该维持数据库的一致性。
- I : isolation : 隔离性
- 表示每次事务都会看到具有一致性的数据库,无论其他事务有什么行动
- D: durability: 持久性
- 事务完成后, 数据库需要正确地存储数据并保护数据免收断电或其他的伤害。
- 事务的三种工具
start Transaction
: 开始事务
- 持续追踪后续所有 SQL 语句, 知道你输入 commit 或者 rollback.
- 开始事务时, 必须使用。这样才可以确定:追踪事务开始的地方。
- transaction log :事务日志。
- commit : 提交
- 当所有的 SQL 语句完成, 满意时就可以输入 commit 进行提交。
- rollback : 回滚
- 如果执行 SQl 语句中出现了问题, 可以回到 start transaction 前的状态。
(4): 存储引擎
BDB 和 InnoDB 都是支持事务的引擎之一。
修改存储引擎的语句
alter table your_table type = InnoDB
(5) : 用户权限
- mySQl :
set password for ‘root'@'localhost' = password('123456');
- localhost : 表示 本机
- Oracle
alter user root identified by new-password
(6): 权限管理
- grant: 赋予权限 --> to
grant select on table_name to user_name
user_name 获的 表 table_name 的 select 权限。- 使用 databas_name.* 可以把 权限范围运用到数据库中的每张表上。
- 与 select 语句中的通配符 (*) 很像。代表数据库中的所有表。
- remove : 撤销权限 --> from
remove select on table_name from user_name
remove grant option on delete on chores from happy, sleepy
- 移除了 happy,sleepy 用户对 chores 表格权限管理的权限, 但是可以操作表
- 把数据库中的所有表的程序权限授权于 新用户
grant select on *.* to user_name
- 第一个星号: 代表所有的数据库
- 第二个星号: 代表所有的表。
- cascade : 是默认值。连锁撤销。
十二: 不常用的知识
(1): 类型转换: cast( )
- 整型转化为 浮点型
- select cast(2, as decimal); 2 --> 2.00
- 浮点型转化为整数不可以。
- 字符型时间转换为日期:
- select cast(‘2005-01-01’ as date);
(2):创建临时表
- temporary : 创建临时表的关键字
(3): Mysql 存储日期和时间数据格式
- date : YYYY–MM–DD
- DATETIME: YYYY–MM–DD HH:MM:SS
- TIMESTAMP : YYYYMMDDHHMMSS
- TIME : HH:MM:SS