必威体育Betway必威体育官网
当前位置：首页 > IT技术

数据库学习的一些基础知识及常用命令

时间:2019-11-08 07:14:30来源:IT技术作者:seo实验室小编阅读:80次「手机版」
　

数据库学习

数据库

“数据库”是以一定方式储存在一起、能够多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。

【基本术语】

  • DB：数据库
  • DBA：数据库管理员
  • DBM：数据库管理系统
  • DBS：数据库系统

【sql分类】

  1. DDL：数据定义语言

用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等.----CREATE

  1. DCL：数据控制语言

用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等

1) GRANT：授权。
2) rollback [WORK] TO [SAVEPOINT]：回退到某一点。
回滚---ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>ROLLBACK;
3) COMMIT [WORK]：提交。
    在数据库的插入、删除和修改操作时，只有当事务在提交到数据
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
别说明这三种类型。
(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；
(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，audit，comment，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；
  1. DML：数据操纵语言

主要有三种形式：插入：INSERT;更新：UPDATE;删除：DELETE

  1. DQL：数据查询语言

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

SELECT <字段名表>

FROM <表或视图名>

WHERE <查询条件>

【数据库索引】

  1. 索引简介

索引是一种有效组合数据的方式，为快速查找到指定记录

  1. 索引作用

    • 提高数据库的检索速度
    • 改善数据库性能

  2. 索引分类

    • B树索引：InnoDB、MyISAM均支持
    • 哈希索引

  3. 常用索引

    • 普通索引：允许在定义索引的列中插入重复值和空值
    ALTER TABLE table_name ADD INDEX index_name (column_name);
    • 唯一索引：索引列数据不重复，允许有空值
    ALTER TABLE table_name ADD unique (column_name);
    • 主键索引：主键将自动创建索引，每个值是非空、唯一的
    ALTER TABLE table_name ADD PRIMARY KEY (column_name);
    • 复合索引/多列索引：将多个列组合作为索引
    ALTER TABLE table_name ADD INDEX index_name(column1,column2,...);
    • 全文索引：支持值的全文查找，允许重复值和空值
    ALTER TABLE table_name ADD FULLTEXT (column_name);
    • 空间索引：对空间数据类型的列建立的索引

  4. 查看和删除索引

    //查看索引
SHOW INDEX FROM table_name;
//查看全部索引
SHOW INDEX FROM table_name\G;
//删除索引
DROP INDEX index_name ON table_name;

  5. 创建索引的指导

    • 频繁搜索的列
    • 经常用作查询选择的列
    • 经常排序、分组的列
    • 经常用作连接的列（主键/外键）
    • 仅包含几个不同值或表内容仅有几行不适合创建索引

  6. 注意事项

    • 查询时减少使用*返回全部列，不要返回不需要的列
    • 索引应该尽量小，在字节数小的列上简历索引
    • WHERE子句中有多个表达式时，包含索引列的表达式应置于其他条件之前
    • 避免在order BY 子句中使用表达式

【数据库事务】

  1. 事务的特性，简称ACID属性

    • 原子性（Atomicity）

    事务是一个完整的操作，事务的各步操作是不可分的，要么全执行，要么都不执行

    当事务完成时，数据必须处于一致状态

    并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务

    事务完成后，它对数据库的修改被永久保存

  2. 事务的操作（需要存储引擎支持）

    • 开始事务（BEGIN或START transaction）
    • 提交事务（COMMIT）
    • 回滚事务（ROLLBACK）
    • 关闭/开启自动提交状态
      • SET autocommit=0|1;(0为关闭，1为开启，mysql默认开启)

关闭自动提交后，从下一条语句开始则开启新事务，需使用commit或rollback语句结束该事务（默认情况下，每条单独的SQL语句视为一个事务）

【数据库视图】

  1. 视图介绍

视图是一张虚拟表，其结构和数据时简历在对表的查询基础上，表示一张表的部分数据或多张表的综合数据；视图中不存放数据；一个原始表可以创建不同的视图。

  1. 视图的用途
    • 筛选表中的行
    • 防止未经许可的用户访问敏感数据
    • 降低操作数据库的复杂程度
    • 将多个物理数据库抽象为一个逻辑数据库
  2. 视图操作
    • 创建视图
    CREATE VIEW view_name AS SELECT (值列表) FROM table_name;
    • 删除视图
    DROP VIEW [IF exists] view_name;
//用if exists 关键字判断视图是否存在
    • 查看视图
    SELECT (值列表) FROM view_name;
//查看所有视图
SHOW TABLE STATUS WHERE COMMENT='view';
  3. 注意事项
    • 视图中可以使用多个表
    • 一个视图可以嵌套另一个视图
    • 对视图数据进行添加、更新和删除操作直接影响所引用表中的数据
    • 当视图数据来自多个表时，不允许添加和删除数据

【数据库定义/控制/操作语法】

  1. 添加数据库
CREATE DATABASE testdatabase;
  1. 删除数据库
DROP DATABASE testdatabase;
  1. 添加数据表
CREATE TABLE test2(uid INT(4) PRIMARY KEY AUTO_INCREMENT,uname VARCHAR(20) NOT NULL);

//修改表名
ALTER TABLE old_table_name RENAME new_table_name;
  1. 删除数据表
DROP TABLE test2;
  1. 添加数据：
INSERT INTO table[(列名列表)] VALUES(值列表)；
//自增列不赋值，其他列一一对应；
    - 批量添加
        - 现有表插入新表,如新表已存在不能重复创建：
        CREATE TABLE new_table_name(查询语句)//一个表只能执行一次
        create table phonelist(select stuno,stuphone from student);
        - 插入多条数据：
        INSERT INTO table[(列名列表)] VALUES(值列表1)，(值列表2)，...，（值列表n）;
  1. 更新数据：
UPDATE table_name SET 列1=值，列2=值，...,列n=值 [WHERE 更新条件];
  1. 删除数据：
DELETE FROM table_name [WHERE 删除条件];//删除表中数据
    批量删除：TRUNCATE TABLE table_name; 
    TRUNCATE TABLE注意：*如删除所有数据，推荐使用此命令
        1.删除数据无法恢复
        2.自增列重新计数
        3.无法删除有外键关联的主表
  1. 表字段操作
//添加字段
ALTER TABLE table_name ADD column1_name column1_type,...;

//删除字段：
ALTER TABLE table_name DROP COLUMN column_name;

//修改字段：
ALTER TABLE table_name ALTER COLUMN column_name column_type；

//修改字段名
ALTER TABLE table_name CHANGE column_name new_name column_type;

//组合主键
ALTER TABLE table_name ADD constRaiNT primary_key_name PRIMARY KEY table_name(column1,column2,...);

//添加外键
ALTER TABLE foreign_table_name ADD constraint foreign_key_name FOREIGN KEY (column_name) references primary_table_name(column_name);

//删除外键
ALTER TABLE foreign_table_name DROP FOREIGN KEY foreign_key_name;

【数据查询语法】

  • 基本语法
    SELECT */列名列表 FROM table [WHERE 查询条件]
    select * from user where id=1;
  • 分组，用在WHERE 之后
    GROUP BY (column1[,column2,...])
    //select后的表达式对每一个分组只能返回一个值
    SELECT * FROM user where id>20 GROUP BY gender;
  • 分组后筛选，用在GROUP BY 之后
    HAVING 筛选条件(只能是聚合函数的形式)
    //将使用聚合函数计算并分组后的数据按条件筛选
    SELECT *,COUNT(score) FROM user where id>20 GROUP BY name HAVING COUNT(score)>60;
  • 排序
    ORDER BY (colunm1 [ASC/DESC],column2 [ASC/DESC],...) 
    //ASC默认升序/DESC降序
    SELECT *,COUNT(score) FROM user where id>20 GROUP BY name HAVING COUNT(score)>60 ORDER BY id DESC;
  • 分页
    mysql分页：
    limit [起始值(下标从0开始)，]页容量;
    
    oracle分页：
    select column1，column2 from
        （select column1，column2，rn from
            (select column1，column2，rownum rn from table) 
        where rn<=最大值）
    where rn>最小值
  • 高级查询
  1. 表连接查询（更适合查看多表数据）
    1.内连接(等值连接/取交集)
    SELECT u.*,r.roleName FROM smbms_user u 
    inner join smbms_role r ON u.`userRole`=r.`id`
    INNER JOIN ...  ON  ...
    [WHERE 查询条件];
    2.外连接(以左/右表为主，未匹配的字段为null) 
    SELECT u.*,r.roleName FROM smbms_user u RIGHT [OUTER] JOIN smbms_role r ON u.`userRole`=r.`id`;
    
    //所有的表连接查询都可以用子查询替换，反之不对。
  1. 模糊查询
    //LIKE ：一般与字符串字段配合使用
    通配符：%（匹配N多字符）
            _（匹配一个字符）
            []（该位置取值在给定范围内）
            [^]（取值不在给定范围内）
    SELECT *
    FROM smbms_user
    WHERE userName LIKE '%张%'
    WHERE userName LIKE '张_'
    WHERE phone LIKE '13[5-9]%'
    
    //between:取值是连续的区间
    SELECT *
    FROM smbms_user
    WHERE birthday BETWEEN '1971-1-1' AND '1999-12-12';
    
    //IN / NOT IN：取值为集合，可以跟随返回多条记录的子查询
    SELECT * FROM smbms_user where id IN(1,2,3);
    SELECT * FROM smbms_user where id NOT IN(SELECT id FROM smbms_user WHERE userRole>1);
  1. 子查询（适合作为增删改查的条件）
    可以出现在任何表达式可以出现的位置
    //例如查询年龄小于赵燕的用户
    SELECT userName FROM smbms_user 
        WHERE age<(SELECT age FROM smbms_user 
            WHERE userCode='zhaoyan');
    //子查询和比较运算符联合使用时，必须保证子查询返回的结果不能多于一个

【MySQL存储引擎】

  1. InnoDB,适用多删除、更新操作，安全性高，事务处理及并发控制
  2. MyISAM,不需要事务，空间小，以查询为主
  3. 其他引擎（需查询）

设置表的存储引擎

CREATE TABLE testtable(
    id int(10) primary key auto_increment
)ENGINE=InnoDB;

数据表的存储位置，可查看my.ini

【MySQL常用函数】

  1. 聚合函数
    • AVG(),返回某字段的平均值
    • COUNT(),返回某字段的行数
    • MAX(),返回某字段的最大值
    • MIN(),返回某字段的最小值
    • SUM(),返回某字段的和
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL  
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL  
count(列名)只包括列名那一列，在统计结果的时候，某个字段值为NULL时，不统计。
列名为主键，count(列名)会比count(1)快  
列名不为主键，count(1)会比count(列名)快  
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  
如果有主键，则 select count（主键）的执行效率是最优的  
如果表只有一个字段，则 select count（*）最优。
  1. 字符串函数
    • CONCAT(str1,str2),字符串链接
    • INSERT(str,index,len,newstr),将字符串str中起始位置为index且长度为len的字符串替换为newstr(index起始值1)
    • LOWER(str),将字符串转为小写
    • UPPER(str),将字符串转为大写
    • substring(str,index,len),从str中截取起始位置index且长度为len的字符串（index起始值1）
  2. 时间日期函数
    • CURDATE(),获取当前日期 2018-11-1
    • CURTIME(),获取当前时间 20:32:30
    • NOW(),获取当前日期和时间 2018-11-1 20:32:50
    • WEEK(date),返回日期date为一年中的第几周
    • YEAR(date),返回日期date中的年份
    • HOUR(date),返回date中的小时值
    • MINUTE(date),返回date中的分钟值
    • DATEDIFF(date1,date2),返回日期之间相隔的天数date1>date2
    • ADDDATE(date,n),返回date加上n天后的日期
  3. 数学函数
    • CEIL(X),取天花板值 select ceil（2.3） 返回3
    • FLOOR(X)，取地板值 select floor（2.3） 返回2
    • RAND(),返回0-1之间的随机数

【MySQL中常用命令】

  1. 连接MySQL
mysql -u[username] -p[password]
  1. 查看所有数据库
show databases;
  1. 转换当前库数据库
use database_name;
  1. 展示当前库所有表
show tables;
  1. 退出数据库
exit

【MySQL的数据类型】

  1. 整数类型
    • smallint， 2字节
    • int， 4字节
    • integer， 同int，默认int（11）
    • bigint， 8字节

      2.浮点数

    • float， 4字节
    • double， 8字节
  2. 字符类型
    • char， 不可变长度
    • varchar， 可变长度
  3. 日期类型
    • data， yyyy-mm-dd
    • datatime， yyyy-MM-dd hh:mm:ss
    • time， hh:mm:ss
    • timestamp， 更精确的时间
    • year， yyyy
  4. 其他类型

【MySQL常见SQL优化策略】

  1. 避免全表扫描
    • 对查询进行优化，应尽量避免全表扫描，首先考虑在where和order by 涉及的列上建立索引。
  2. 避免判断空值
    • 避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。可以设置默认值进行优化，使字段有值。
  3. 避免不等值判断
    • 尽量避免在where子句中使用！=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
  4. 避免使用or逻辑
    • 尽量避免在where子句中使用or来连接条件，否则引擎将放弃索引而进行全表扫描。
    • 可以使用union all,select id from table where num=10 union all select id from table where num=20;等价于select id from table where num=10 or num=20；
  5. 慎用in 和 not in
  6. 注意模糊查询的使用
    • like ‘%aaa%’ 可以更改为 like ‘aa%’ ，前者使用全表扫描。
  7. 避免查询条件中字段计算
    • 例如：select id from table where num/2=100;
    • 可以更改为select id from table where num=100*2；
  8. 避免查询条件中对字段进行函数操作。
  9. where子句“=”左边不要进行函数、算术运算符或其他表达式运算。
  10. 用exists代替in是一个好的选择
    • select num from table where num in（select num from table1）;
    • 可以替换为select num from table where exists(select 1 from table1 where num=table.num);
    • select 1 表示真，select 0 表示假。

文章最后发布于: 2018-11-03 15:33:20

相关阅读

详解数据库的第一范式、第二范式、第三范式、BCNF范式

出于复试的必要性，很努力地复习了数据库的内容，大学没好好学习的东西，后期都是要花费大量的时间去弥补的。很多东西都是逝去了才知道

数据库优化设计（非常实用）

一、树型关系的数据表 不少程序员在进行数据库设计的时候都遇到过树型关系的数据，例如常见的类别表，即一个大类，下面有若干个子类，某

使用PreparedStatement操作mysql数据库出现中文乱码问

背景： 在使用PreparedStatement执行setString(4, "我是中文");后，通过debug发现中文变成了？？？；这个肯定是编码问题，然后通过字符串和字

powerdesign建Oracle数据库设置某个属性值自增

两种方式，一种是通过执行PowerDesign生成的触发器语句，一种是通过SqlDeveloper内创建触发器，但是两种方法使用之前都需要创建序列seq

常见的关系型数据库和非关系型都有哪些？

常见的关系型数据库和非关系型都有哪些？ 关系型数据库：关系模型就是指二维表格模型，因而一个关系型数据库就是由二维表及其之间的联

赞()
分享到:

猜你喜欢

栏目导航

推荐阅读

热门阅读

seo实验室提供seo优化服务，有网站优化、网站建设、运营推广等需求请联系我们!
Copyright © 2001-2017 .seo实验室 版权所有 网站地图