数据库基础

数据库分类

  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位,其中可以有两位小数
  • decimal和numeric表示精确的整数数字

日期

  • 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，若为null用10替代

  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 ;