数据库基础

数据库分类

  1. 关系型数据库
    数据库建立在数据的紧密关系基础之上(如:父子关系、师生关系),称其为关系型数据库。
    常见关系型数据库:Oracle、MySQL、SQLServer、Access
  2. 非关系型数据库
    数据库建立在数据的松散关系基础之上(如:中国人和美国人、中国人和印度人、视频、音频),称其为非关系型数据库nosql(not only sql)
    常见非关系型数据库:MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

数据库结构

数据库 —>表 —>记录(行和列/记录)

SQL语句

定义

结构化查询语言(Structured Query Language)简称SQL是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。

分类

  1. DML:数据库操作语言,对数据库进行增删改查
  2. DDL:数据库定义语言,创建库、创建表
  3. DCL:数据库控制语言,细粒度的管理操作数据库的权限
  4. DQL:数据库查询语言,SQL不区分大小写

数据库常用操作

  1. 创建

    1. create database 库名;
  2. 删除

    1. drop database 库名;
  3. 查看

    1. show databases;

  1. 创建

    1. create table 表名(
    2. 字段名 字段类型(长度),
    3. 字段名 字段类型(长度),
    4. 字段名 字段类型(长度),
    5. ··········
    6. );
    7. create table goods(
    8. name varchar(10),
    9. id int(10),
    10. goods_title varchar(22)
    11. );
  2. 修改
    插入列

    1. alter table 表名 add column 字段名 字段类型(长度);
    2. alter table user add column id int(10);
  3. 删除

    1. drop tabel 表名;
  4. 查看
    查看表结构
    查看数据库中所有表

    1. desc 表名;
    1. use 库名;//选中数据库
    2. show tables;

记录

  1. 插入
    值的个数必须与表中字段个数一致
    示例

    1. insert into 表名 values(字段1,字段2,字段3,····);
    1. INSERT INTO `user` VALUES('jack', 22,'nan',1);
    2. -- 只给指定字段设置值
    3. INSERT INTO dept(deptno) VALUES(7);
    4. INSERT INTO dept(deptno,dname) VALUES(15,'达内');
  2. 查询
    示例

    1. select 字段名,字段名,···· from 表名;
    2. select * form 表名;
    1. SELECT * FROM `dept`;
    2. SELECT loc FROM dept;
    3. SELECT deptno,dname FROM dept;
  3. 修改
    示例

    1. update 表名 set 字段值 = 新值;
    1. UPDATE `user` SET `name`='马钊' WHERE `name` = 'mazhao';
    2. UPDATE `user` SET `age`=25;
  4. 删除
    示例

    1. delete form 表名;
    1. delete form user;

命名规则

  1. 字段名必须以字母开头,尽量不要使用拼音
  2. 长度不能超过30个字符(不同数据库,不同版本会有不同)
  3. 不能使用SQL的保留字,如where,order,group
  4. 只能使用如下字符az、AZ、0~9、$ 等
  5. Oracle习惯全大写:USER_NAME,mysql习惯全小写:user_name
  6. 多个单词用下划线隔开,而非java语言的驼峰规则
  7. 单引号是一个SQL语句的特殊字符 ,数据中有单引号时,用一个\转义变成普通字符

数据类型

字符

  • char:长度固定,不足使用空格填充,最多容纳2000个字符,char(11)存储abc,占11位,查询速度快但是浪费空间。
  • varchar:长度可变,最多容纳4000个字符,char(11)存储abc,占3位,查询速度慢且节省空间,Oracle为varchar2。
  • Text:大文本,不推荐使用,一般使用varchar。

注意:utf-8编码中一个汉字占3个字节。

数字

  • tinyint,int整数类型
  • float,double小数类型
  • numberic(5,2) ,decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
  • decimalnumeric表示精确的整数数字

日期

  • date 包含年月日
  • time时分秒
  • datetime包含年月日和时分秒
  • timestamp时间戳,不是日期,而是从1970年1月1日到指定日期的毫秒数

图片

blob 二进制数据,可以存放图片、声音,容量4g。早期有这样的设计。但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。同时数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,而只存储其访问路径,文件则存放在磁盘上。

字段约束

主键约束

主键约束:如果为一个列添加了主键约束,那么这个列就是主键,主键的特点是唯一且不能为空。通常情况下,每张表都会有主键。

主键自增策略:设置了主键自增策略后,数据库会在表中保存一个AUTO_INCREMENT变量值,初始值为1,当需要id值,不需要我们指定值,由数据库负责从AUTO_INCREMENT获取一个id值,作为主键值插入到表中。而且每次用完AUTO_INCREMENT值,都会自增1。

示例代码

  1. create table user(
  2. id int primary key auto_increment--设为主键且自增
  3. );

非空约束

一个字段设置了非空约束,那么这个字段不能为空,但可重复。

示例代码

  1. create table user(
  2. id int primary key auto_increment,
  3. password varchar(50) not null--非空约束
  4. );

唯一约束

当一个字段设置唯一约束后,这个字段值不可以重复,但可以为空。

示例代码

  1. create table test(
  2. id int primary key auto_increment,
  3. username varchar(50) unique--唯一约束
  4. );

基本函数

  1. **lower**:数据转小写

    1. SELECT 字段名称,LOWER(字段名称) FROM 表名
    2. SELECT ename,LOWER(ename) FROM emp
  2. **upper**:数据转大写

    1. SELECT 字段名称,UPPER(字段名称) FROM 表名
    2. SELECT ename,UPPER(ename) FROM emp
  3. **length**:获取数据长度

    1. SELECT LENGTH(字段名) FROM 表名
    2. SELECT LENGTH(ename),ename,job,LENGTH(job) FROM emp
  4. **substr**:截取字符串

    1. SELECT 字段名,SUBSTR(str FROM pos FOR len) FROM 表名
    2. SELECT dname,SUBSTR(dname,1,4) FROM deptSELECT dname,SUBSTR(dname,1,4),SUBSTR(dname,2) FROM dept
  5. **concat**:拼接字符串

    1. SELECT 字段名,CONCAT(字段名,字符串1,字符串2,·····) FROM 表名
    2. SELECT ename,CONCAT(ename,123,'hello') FROM emp
  6. **replace**:替换字符串

    1. SELECT 字段名,SUBSTR(字段名,'b替换的字符','要替换的字符') FROM 表名
    2. SELECT dname,SUBSTR(dname,'a','6') FROM dept
  7. **ifnull**:判断一个字段是否为null

    1. SELECT 字段名,IFNULL(字段名,要替换的值) FROM 表名
    2. SELECT comm,IFNULL(comm,10) FROM emp--判断一个字段是否为null,若为null10替代
  8. **round/ceil/floor**:四舍五入并保留一位小数/向上取整/向下取整

    1. SELECT comm,ROUND(comm),FLOOR(comm),ceil(comm) FROM emp
    2. SELECT 字段名,ROUND(字段名),FLOOR(字段名),ceil(字段名) FROM emp
  9. +:将字段值相加

    1. SELECT *,comm+sal FROM emp
  10. **now**

    1. SELECT now()-年与日 时分秒
    2. SELECT CURDATE()--年与日
    3. SELECT curtime()--时分秒
  11. **year/month/day/hour/minute/second**:获取年份/获取月份/获取天数/获取分钟/获取秒

    1. select now(),hour(now()),minute(now()),second(now()) from emp ;
    2. select now(),year(now()),month(now()),day(now()) from emp ;