第一章:数据库概览
本学习记录以mysql为对象来学习
一.数据库是什么?
数据库就是存放数据的仓库,数据保存起来,计算机加工成的大集合,叫做数据库(Database,DB),进一步,管理这些数据库的叫做数据库管理系统,DBMS.
二.有哪些常见的数据库管理系统?
首先数据的保存格式决定了数据库的分类,目前有5种类型DBMS:
1.关系型数据库(RDB,relation database)最常见。
关系型数据库(行列二维表)有以下分类:
- oracle-经典,金融银行用的多,国内去O行为
- sql server
- db2
- postgresql,开源目前很火的数据库
- mysql,开源亲民学习用,一般企业也用
2.键值存储系统(kv),mongoDB,不熟,听过
3.层次数据库(HDB),不了解
4.面向对象数据库(OODB),不了解
5.XML数据库(XMLDB),不了,应该是属于非关系型数据库吧。
三.数据库是怎么做事的?
安装好了数据库之后,客户端写sql,点击执行,sql语句给到RDBMS服务器,然后解析sql,去查数据库的表里的数据,然后返回给客户端,我们就能看到表数据了。
sql的结果展示
四.SQL是什么?
这个问题其实挺复杂的,我们从最简单的理解,就是像select * from table 这样的语句。是一种指令可以操作数据库对数据进行增删改查等操作。
1.sql语句分类?(DDL,DML,DCL,DQL)
本人知之甚少,给连接自己去看吧。sql语句分类
简单说一下吧
-
1.数据查询语言DQL(用的最多)
基本结构 select from where等组成的语句
select<字段名> from<表名>where<查询条件> -
2.数据定义语言DDL(用的第二多)
-
注意针对库和表的结构增删改
CREATE table/view/index 创建库,表,视图,索引等对象
DROP:删除数据库和表等对象
ALTER: 修改数据库和表等对象的结构 -
3.数据操纵语言DML(其次)
-
用来变更表中的记录
INSERT 向表中插入数据
UPDATE 更新表中的数据
DELETE 删除表中的数据 -
4.数据控制语言DCL(很少用)
-
用来授权和回收访问数据库权限的语句,对数据库进行监视等操作
GRANT :授权
REVOKE:回收权限
ROLLBACK:回滚。取消对数据库的数据进行变更
COMMIT:确认对数据库中数据进行变更 -
这里涉及到mysql等设计的底层原理,操作之后如果误操作,没有执行commit确认操作,可以使用 rollback回滚回初始状态。但是能回滚的只有select ,delete,update,insert 这几个,即DML可回滚。DDL,DCL不能回滚,因为会触发隐士commit提交,已经确认更改了。另外想要实现回滚需要设置参数:set autocommit=0 不开启自动提交
2.怎么写sql?
- 写完一段用分号结尾(;)英文的
- 不区分关键字大小写(select 这些),但是插入到表中的数据区分大小写(因为是字符串是数据)
- win不区分表名和字段大小写,linux 和mac默认严格区分表名和字段的大小写
- 基本结构就是 select 列名 from 表名 where条件
- 每个单词有空格隔开(全英文的就是了)
五.手把手写sql
首先有库,库下有表,表里有字段,字段下有数据(值)。
1.库
- show databases; --看看有哪些库
- create database shop;–建立一个shop库
- use shop --使用这个shop库,用了库才能在库里建表
- drop database shop;删库,跑路
2.表
- create table if not exists(
<字段名> <字段类型> <字段长度> <列的约束> comment <字段注释>
)
简单点这样
show databases;
create database shop;
use shop;
show tables;
#创建本课程需要的商品表
create table product(
product_id varchar(4) not null,
product_name varchar(100) not null,
product_type varchar(32) not null,
sale_price integer,
purchase_price integer,
regist_date date,
primary key (product_id)
);
3.表的命名规则
- 半角英文,字母,数字,下划线作为库,表,列的名称
- 以英文字母开头,全小写
4.表的数据类型
创建表都必须要给列指定类型,只能存储与该列类型相符合的数据
四种基本数据类型
- INTEGER型:指定存储整数的列数据类型(数字型,不能是小数)
- CHAR型:存储固定长度的字符串,当列中存储的字符串长度达不到这个设定值,使用半角空格不足,会浪费空间,现在很少用
- VARCHAR型:存储可变长度的字符串,实际多少空间用多少空间,不会去补足
- DATE型:存储日期(年、月、日)的数据类型
5.表的删除和更新
- 删除的表无法恢复,只能重新插入,所以删除特别注意。
- 删库跑路,被抓坐牢了解下。
- alter table 和drop table语句一样,执行后不能恢复,误加的列可以 alter table 来删掉
#删除表
DROP TABLE table_name;
#删除produce表
drop table product;
#更新表
#用 ALTER TABLE table_name ADD COLUMN <列的定义>
#添加一列可以存储100位可变字符串的 product_name_pinyin列
alter table product add column product_name_pinyin varchar(100);
#删除表的某一列
ALTER TABLE table_name DROP COLUMN <列名>;
#删除product_name_pinyin列
alter table product drop column product_name_pinyin;
#删除表中特定的行(删除部分数据)
#一定要加where 条件,否则整个表数据都被删
DELETE FROM product where product_id=1 #删除产品id为1的数据
# 清空表内容
TRUNCATE TABLE TABLE_NAME;
原理:删掉表,再重建表。索引等等复位
优点:比drop/delete 速度更快
#数据更新
update table_name
set 列名1=<表达式>,列名2=<表达式>...
where <条件> --可选
order by <子句> --可选
limit <子句> --可选;
#修改所有注册时间
update product
set regist_date='2009-10-10';
#修改部分商品单价
update product
set sale_price=sale_price*10
where product_type='厨房用具';
#用update把列初始化NULL(null清空),没有设置 非空约束和主键约束的才可以清空(主键约束默认非空,非空约束设置非空)
#把商品0008的数据登记日期更新为NULL
update product
set regist_date=NULL
where product_id='0008';
- 插入数据
- 基本语法: INSERT INTO 表名(列1,列2,列3,…) VALUES(值1,值2,值3…);
- 这里有几个列,对应几个值,严格数量一致
#新建一个表用来插入数据
create table productins(
product_id varchar(4) not null,
product_name varchar(100) not null,
product_type varchar(32) not null,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER,
regist_date DATE,
primary key (product_id)
);
#对表的全部字段插入数据,不用写每个列名,默认值从左到右挨个赋给每个列
--插入方式1,执行一次插入一条数据
INSRT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
--插入方式2,执行一次插入一条数据
INSERT INTO productins VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
--插入多行数据,执行一次插入多行数据
--方式1,写多条
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO productins VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO productins VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
--方式2,一条插入多值 values(),(),();
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
--默认值(初始值),可以建表时候设置DEFAULT 约束
CREATE TABLE productins
(product_id CHAR(4) NOT NULL,
(略)
sale_price INTEGER
(略) DEFAULT 0, -- 销售单价的默认值设定为0;
PRIMARY KEY (product_id));
--常用的从其他表复制数据导入
-- 将商品表中的数据复制到商品复制表中
INSERT INTO productcopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product;
本次需要插入的数据有
- DML :插入数据
STARTTRANSACTION;
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;
6.索引
作用:提高查询效率,可以根据索引快速的找到数据,从而不用盲目的整个表扫描
#创建索引
#语法格式
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
#另一张方式添加索引
-- 方法1
CREATE INDEX indexName ON table_name (column_name)
-- 方法2
ALTER table tableName ADD INDEX indexName(columnName)
- 索引分类
- 主键索引:一张数据表只能有一个主键索引,不允许空值,建表时候一起建
- 唯一索引:这个列的值,都是唯一的不能重复,一个表可以多个列都是唯一属性,但是多个空值不会重复冲突,其他值会重复冲突。
- 普通索引:普通字段上的索引(这个好像没怎么用过)
- 前缀索引:对字符类型字段的前几个字符或者二进制类型字段的前几个byte建立的索引,不是在整个字段上建。前缀索引可以建在char,varchar,binary,varbinary 类型的列上,能够减少索引占的存储空间,也能提高索引的查询效率
- 全文索引:分词技术实现的搜索关键字的一种索引,mysql5.6以后的版本支持,字段类型为char,varchar,text及其系列才能建全文索引。如果可以的话,先创建表,插入数据之后再创建全文索引,这样会更快,效率更高
语法:SELECT * FROM article WHERE MATCH (col1,col2,...) AGAINST (expr [ search _ modifier ])
-
- 单列索引:建立在单个列上的索引
- 联合索引:复合索引,多列索引