数据库系统概论之SQL笔记

@JAMMLY 2019年10月11日 数据库

摘要数据库相关操作

数据库操作:

SHOW DATABASES;  //查看数据库

CREATE DATABASE test;    //创建数据库test

USE test;    //启用test数据库

SHOW TABLES;  //查看数据表

CREATE TABLE student(         //创建student表,有id , name , birthday ,gender , isDelete五个字段

    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,  //字段id 自动递增 主键 值不空

    name VARCHAR(8) NOT NULL,  //字段name 字符型 不空

    birthday DATETIME,  //字段birthday 日期时间型

    gender BIT DEFAULT 1,  //字段gender bit位 默认1

    isDelete BIT DEFAULT 0);    

DROP TABLE student;    //删除student表

ALTER TABLE student RENAME TO stu;

RENAME TABLE student TO stu;    //重命名表student 为 stu 的两种方法

ALTER TABLE stu ADD age INTEGER;    //增加stu表字段age

ALTER TABLE stu CHANGE password pwd VARCHAR(8);    //把stu表中的password字段名改成pwd

ALTER TABLE stu DROP pwd;    //删除stu表中的pwd字段

DESC student;  //查看student表结构

SHOW CREATE TABLE student;  //查看表的创建语句

数据操作:

查询:

SELECT * FROM 表名;  //查询

增加:

INSERT INTO 表名 VALUES( ... );   //全列插入

INSERT INTO 表名(列1, ... ) VALUES(值1, ... );   //缺省插入

INSERT INTO 表名 VALUES( ... ),( ... ), ... ;  

INSERT INTO 表名(列1, ...) VALUES(值1, ... ),(值2, ... ), ... ; //同时插入多条数据

注:主键列是自动增长,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准

修改:

UPDATE 表名 SET 列1=值1, ... WHERE 条件;

删除:

DELETE FROM 表名 WHERE 条件;

逻辑删除:

ALTER TABLE students ADD isdelete BIT DEFAULT 0;   //增加一个字段isdelete 默认0(1表示删除)

如果需要删除列

UPDATE students isdelete=1 WHERE 条件;   //更新字段isdelete=1 逻辑表示数据删除



查询

基本语法:select * from 表名;

    from 关键字后面写表名,表示数据来源于这张表

    select 后面写表中的列名,如果是 * 表示在结果中显示表中所有列

    在 select 后面的列名部分,可以使用 as 为列起别名, 这个别名出现在结果集中

    如果查询多个列,之间使用逗号分隔

消除重复的行

    在 select 后面列前使用 distinct 可以消除重复的行

条件

    使用 where 子句对表中的数据筛选,结果为 true 的行会出现在结果集中

语法: select * from 表名 where 条件;

比较运算符

    等于 =

    大于 >

    大于等于 >=

    小于 <

    小于等于 <=

    不等于 != 或 <>

逻辑运算符

    and

    or

    not

模糊查询

    like

    % 表示任意多个任意字符

    _ 表示一个任意字符

    查询黄姓的学生 select * from students where sname like '黄%';

范围查询

    in 表示在一个非连续的范围内

    查询编号是1或3或8的学生:  select * from students where id in(1,3,8);

    between … and … 表示在 一个连续的范围内

    查询学生是3至8的学生:  select * from students where id between 3 and 8;

空判断

    注意:null 与 '' 是不同的

    判空 is null

    判非空 is not null

优先级

    (高到低)小括号,not,比较运算符,逻辑运算符

    and 比 or 先运算,如果同时出现并希望先算or ,需要结合()使用



聚合

    为了快速得到统计数据,提供了5个聚合函数

    count(*) 表示计算总行数,括号中写星2或列名,结果是相同的

    max() 表示求此列的最大值

    min(列) 表示求此列的最小值

    sum(列) 表示求此列的和

    avg(列) 表示求此列的平均值

分组

    按照字段分组,表示此字段相同的数据会被放到一个组中

    分组后,只能查询出相同的数据列,对于有差异的数据列无法出现在结果集中

    可以对分组后的数据进行统计,做聚合运算

    语法: select 列1,列2,聚合… from 表名 group by 列1,列2,列3…

分组后的数据筛选

    语法: select 列1,列2,聚合… from 表名 group by 列1,列2,列3…having  列1,…聚合…

    having 后面的条件运算符与where的相同

对比 where 与 having

    where 是对 from 后面指定的表进行数据筛选,属于对原始数据的筛选

    having 是对 group by 的结果进行筛选



排序

    为了方便查看数据,可以对数据进行排序

    语法: select * from 表名 order by 列1 asc|desc, 列2 asc|desc, …

    将数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推

    默认按照列值从小到大排序

    asc 从小到大排序,即升序

    desc 从大到小排序,即降序

获取部分行

    当数据行过大时,在一页中查看数据是一件非常麻烦的事情

    语法: select * from 表名 limit start,count 

    从 start 开始,获取count条数据

    start 索引从0开始

示例:分页

    已知:每页显示m条数据,当前显示第n页

    求总页数:

        查询总条数p1

        使用p1除以m得到p2

        如果整除则p2为总数页

        如果不整除则p2+1为总页数

    求第n页的数据:

        select * from students where isdelete=0 limit (n-1)*m,m




连接查询

    连接查询的分类:

        表A inner join 表B:表A 与 表B 匹配的行会出现在结果中

        表A left join 表B:表A 与 表B 匹配的行会出现在结果中,外加 表A 独有的数据,未对应的数据使用 null 填充

        表A right join B:表A 与 表B 匹配的行会出现在结果中, 外加 表B 独有的数据,为对应的数据使用 null 填充

    在查询或条件中推荐使用 “表名.列名”的语法

    如果多个表中列名不重复可以省略 “表名.”部分

    如果表的名字实在太长,可以在表名的后面使用 “as 简写名” ,为表起个临时的简写名称



视图

    对于复杂的查询,在多次使用后,维护是一件非常麻烦的事情

    解决:定义视图

    视图本质就是对查询的一个封装

    定义视图:

        create view 视图名 as 查询语句



事务

    当一个业务逻辑需要多个sql完成时,如果其中某条sql语句出错,则希望整个操作都退回

    使用事务可以完成退回的功能,保证业务逻辑的正确性

    事务四大特性(简称ACID)

        原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行

        一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致

        隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的

        持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障

    要求:表的类型必须是innodb或bdb类型,才可以对此表使用事务

    查看表的创建语句

        show create table students;

    修改表的类型

        alter table '表名' engine=innodb;

    事务语句

        开启 begin; 

        提交 commit; 

        回滚 rollback;



索引

    当数据库中数据量很大时,查找数据会变的很慢

    索引能提高数据访问性能

    主键和唯一索引,都是索引,可以提高查询速度

    选择列的数据类型

    越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快

    简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂

    尽量避免NULL:应该指定列为 NOT NULL,除非你想存储NULL。含有空值的列很难进行查询优化,因为他们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值

    操作

    索引分单列索引和组合索引

        单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,单这不是组合索引

        组合索引,即一个索引包含多个列

    查看索引:

        show INDEX FROM table_name;

    创建索引:

        CREATE INDEX indexName ON mytable(username(length));

    删除索引:

        DROP INDEX [indexName] ON mytable;

    缺点

    虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERT、UPDATE 和DELETE。因为更新表时,不仅要保存数据,还要保存一下索引文件

    建立索引会占用磁盘空间的索引文件

    语句执行时间

    开启运行时间监测:    set profiling=1;

    执行查询语句后查看执行时间:  show profiles;

    


郑重声明:本站部分内容来源网络,如有侵权请联系本站删除处理!