• 【01】MySQL使用入门 (全)


    一、数据库简介

    1.数据库概述

      ①数据库:是按照某种数据结构对数据进行组织,存储和管理的容器,简单的说就是用来存储和管理数据的容器。
      ②数据库系统:是指在计算机中引入数据库后的系统,一般由数据库,数据库管理系统,应用程序和数据库管理员组成。
      ③数据库管理系统:是一个管理,控制数据库容器中各种数据库对象的系统软件。

    2.结构化查询语言SQL

      ①结构化查询语言:是一种用来与数据库通信的语言,其利用一些简单的句子构成基本的语法来存取数据库的内容,便于用户从数据库中获得和操作所需数据。
      ②SQL语言特点:非过程化语言,统一的语言,关系数据库的公共语言。
      ③SQL语言的组成:数据库定义语言(DDL),数据库操纵语言(DML),数据控制语言(DCL)。

    3.数据库设计基本步骤

      ①需求分析阶段。
      ②概念结构设计阶段。
      ③逻辑结构设计阶段。
      ④数据库的物理结构设计阶段。
      ⑤数据库的实施阶段。
      ⑥数据库运行与维护阶段。

    4.MySQL数据库

      它是一种关系型数据库管理系统,关系型数据库将数据保存在不同的表中,而不是将所有数据存放在一个大仓库,提高了速度和灵活性。

    5.MySQL特点

      ①体积小,运行速度快,成本低,开放源码。
      ②单进程,多线程架构,属于轻量级的数据库。

    6.MySQL体系结构

        包括连接池组件,管理服务和工具组件,SQL接口组件,查询分析组件,优化器组件,缓存组件,插件式存储引擎以及物理组件。

    7.数据库系统解决的问题

      持久化存储,优化读写,保证数据的有效性。

    8.数据库的分类

      ①文档型,如sqlite,就是一个文件,通过对文件的复制完成数据库的复制。
      ②服务型,如mysql、postgresql,数据存储在一个物理文件中,但是需要使用终端以tcp/ip协议连接,进行数据库的读写操作。

    9.数据库的操作

      ①数据库的操作,包括创建、删除。
      ②表的操作,包括创建、修改、删除。
      ③数据的操作,包括增加、修改、删除、查询,简称crud。

    10.常用的可视化操作工具

      ①MySQL-Front。
      ②MySQL Workbench。
      ③Navicat for MySQL。

    11. SQL的基本书写规则

      ①关键字大写。
      ②表名的首字大写。
      ③其余(列名)小写。

    二、E-R模型

    1.E-R

      E表示entry-实体,R表示relationship-关系。

    2.实体之间的对应规则

      ①一对一。
      ②一对多。
      ③多对多。

    3.三范式

      ①第一范式(1NF):列不可拆分。
      ②第二范式(2NF):唯一标识。
      ③第三范式(3NF):引用主键。
      说明:后一个范式,都是在前一个范式的基础上建立的。

    4.数据完整性

      一个数据库就是一个完整的业务单元,可以包含多张表,数据被存储在表中,在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括数据字段的类型、约束。

    5.字段类型

      ①数字:int,decimal:如decimal(5,2),表示数字长度不超过5位,小数不超过2位。
      ②字符串:char,varchar,text:其中char和varchar都是有限字符的,而text是不确定的。char和varchar都是8个字符,但char如果不够会补充空格,varchar是不会补充空格的。
      ③日期:datetime。
      ④布尔:bit。

    6.约束  

      ①主键primary key。
      ②非空not null。
      ③惟一unique。
      ④默认default。
      ⑤外键foreign key 。
      ⑥auto_increment表示自动增长。

    7. 数据库的选型

      ①开发人员的熟练程度,费用,数据规模,性能要求,集群能力等,也可参考数据库管理员的建议。
      ②对于有1:1关系的两个表,为两个表设置相同的主键列。
      ③对于有1:N关系的两个表,在N表中添加一个外键列,该列与1表的主键列向关联。
      ④对于M:N关系,生成一个单独的表表示该关系,该关系的列由两个表的主键组成。

    三、命令脚本命令

    1.远程连接

      一般在公司开发中,可能会将数据库统一搭建在一台服务器上,所有开发人员共用一个数据库,而不是在自己的电脑中配置一个数据库。

    mysql -hip地址 -uroot -p

    2.数据库操作

      ①创建数据库。

    create database 数据库名 charset=utf8;

      ②删除数据库。

    drop database 数据库名;

      ③切换数据库。

    use 数据库名;

      ④查看当前选择的数据库。

    select database();

    3.表操作

      ①查看当前数据库中所有表。

    show tables;

      ②创建表。

    create table 表名(列及类型);

      ③修改表。

    增,改,删:alter table 表名 add|change|drop 列名 类型;
    增加约束条件:alter table 表名 add constraint 约束名,类型(字段名);
    删除约束条件:alter table  表名 drop primary key;
    添加外键约束:alter table 表名 add 约束名 constraint foreign key(字段名)references 父表名(字段名);
    修改存储引擎:alter table 表名 engine = 新的存储引擎;(MyISAM,InooDB)
    修改默认字符集:alter table 表名 default charset=新的字符集;

      ④删除表。

    drop table 表名;

      ⑤查看表结构。

    desc 表名;

      ⑥更改表名称。

    rename table 原表名 to 新表名;

      ⑦查看表的创建语句。

    show create table 表名;

    4.数据操作

      ①查询。

    select distinct 列名 from students;消除重复
    select * from 表名 where 条件;

      ②增加。

    全列插入:insert into 表名 values(...)
    缺省插入:insert into 表名(列1,...) values(值1,...)
    同时插入多条数据:insert into 表名 values(...),(...)...;
    或insert into 表名(列1,...) values(值1,...),(值1,...)...;
    主键列是自动增长,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准

      ③修改。

    update 表名 set 列1=值1,... where 条件

      ④删除。

    delete from 表名 where 条件

      ⑤逻辑删除,本质就是修改操作update。

    alter table students add isdelete bit default 0;

      ⑥如果需要删除则。

    update students isdelete=1 where ...;

    5.备份与恢复

      1) 数据备份

        ①进入超级管理员。

    sudo -s

        ②进入mysql库目录。

    cd /var/lib/mysql

        ③运行mysqldump命令。

    mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;

        ④按提示输入mysql的密码  。

      2) 数据恢复

        ①连接mysql,创建数据库。
        ②退出连接,执行如下命令。

    mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql

        ③根据提示输入mysql密码。

    四、查询(条件)

    1.比较运算符

    select * from students where id>3;

    2.逻辑运算符

    select * from students where id>3 and gender=0;

    3.模糊查询

    Like:select * from students where sname like '黄_';

      ①%表示任意多个任意字符。
      ②_表示一个任意字符。

    4.范围查询

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

    select * from students where id in(1,3,8);

      ②between ... and ...表示在一个连续的范围内。

    select * from students where id between 3 and 8;

    5.空判断

      ①判空is null。

    select * from students where hometown is null;

      ②判非空is not null。

    select * from students where hometown is null;

    6.优先级

      ①小括号,not,比较运算符,逻辑运算符。
      ②and比or先运算,如果同时出现并希望先算or,需要结合()使用。

    五、查询(聚合)

    1.count(*)

      表示计算总行数,括号中写星与列名,结果是相同的。

    select count(*) from students;

    2.max(列)

      表示求此列的最大值。

    select max(id) from students where gender=0;

    3.min(列)

      表示求此列的最小值。

    select min(id) from students where isdelete=0;

    4.sum(列)

      表示求此列的和。

    select sum(id) from students where gender=1;

    5.avg(列)

      表示求此列的平均值。

    select avg(id) from students where isdelete=0 and gender=0;

    六、查询(分组)

    1.语法1

      
    select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
    分组后的数据筛选

    2.语法2

    select 列1,列2,聚合... from 表名
    group by 列1,列2,列3...
    having 列1,...聚合...
    having后面的条件运算符与where的相同,不同的是having对分组的结果集进行筛选,where对原始集筛选

    3.与聚合函数和Group by子句有关的常见错误

      ①在select子句中书写了多余的列。

    select product_id,purchase_price,count(*) from Product Group by purchase_price;

      ②在group by子句中写了列的别名。

    select product_type as pt,count(*) From Product Group by pt;

      ③Group by的子句是随机的,不能人为排序。
      ④不能在where子句中使用聚合函数。
      ⑤只有select子句和having子句以及(order by)中才能够使用count等聚合函数。

    七、查询(排序)

    1.语法1

    select * from 表名
    order by 列1 asc|desc,列2 asc|desc,...

      将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推默认按照列值从小到大排列,asc从小到大排列,即升序,desc从大到小排序,即降序查询(分页)。

    2.语法2

    select * from 表名 limit start,count;

    八、查询(总结)

    1.完整的select语句

    select distinct *
    from 表名
    where ....
    group by ... having ...
    order by ...
    limit star,count

    2.From子句是否有必要?

      并不是,只使用select子句也是可以的,如select(100+300)*3 as calculation,但在oracle中不允许。

    3.子查询

      ①子查询就是一次性视图,与视图不同,子查询在select语句执行完毕之后就会消失。
      ②标量子查询:标量就是单一的意思,标量子查询有一个特殊的限制,必须而且只能返回1行1列的结果。
      ③关联子查询:在子查询中添加where子句条件,where p1.product_type=p2.product_type。

    九、关系

    1.外键

      为stuid添加外键约束。

    alter table scores add constraint stu_sco foreign key(stuid) references students(id);

    2.外键的级联操作

      ①在删除students表的数据时,如果这个id值在scores中已经存在,则会抛异常。
      ②推荐使用逻辑删除,还可以解决这个问题。
      ③可以创建表时指定级联操作,也可以在创建表后再修改外键的级联操作。

    alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;

    3.级联操作的类型

      ①restrict(限制):默认值,抛异常。
      ②cascade(级联):如果主表的记录删掉,则从表中相关联的记录都将被删除。
      ③set null:将外键设置为空。
      ④no action:什么都不做。

    十、连接查询

    1.连接查询分类

      ①表A inner join 表B:表A与表B匹配的行会出现在结果中。
      ②表A left join 表B:表A与表B匹配的行会出现在结果中,外加表A中独有的数据,未对应的数据使用null填充。
      ③表A right join 表B:表A与表B匹配的行会出现在结果中,外加表B中独有的数据,未对应的数据使用null填充。
      ④查询学生的姓名、平均分。

    select students.sname,avg(scores.score)
    from scores
    inner join students on scores.stuid=students.id
    group by students.sname;

    2.自关联

      创建areas表的语句如下:

    create table areas(
        id int primary key,
        atitle varchar(20),
        pid int,
        foreign key(pid) references areas(id)
    );

    十一、视图

    1.视图

      从SQL的角度来看,视图和表是相同的两者的区别在于表中保存的是实际数据,而视图中保存的是select语句(视图本身并不存储数据)。

    create view stuscore as 
    select students.*,scores.score from scores
    inner join students on scores.stuid=students.id;

    2.视图的用途

      就是查询。

    select * from stuscore;

    3.视图的优点

      ①视图无需保存数据,因此可以节省存储设备的容量。
      ②可以将频繁使用的select语句保存成视图,这样就不用每次都重写了。

    4.视图的限制

      ①定义视图时不能使用order by子句,这是因为视图和表一样数据行都是没有顺序的。
      ②对视图进行更新(未被汇总得到的视图)  。
      ③限制条件
        select子句未使用Dintinct。
        From子句中只有一张表。
        未使用group by子句。
        未使用having子句。

      ④删除视图(多重视图)。

    drop view 视图名 cascade

    十二、事务

    1.事务

      是恢复和并发控制的基本单位,什么是事务,就是需要在一个处理单元中执行的一系列更新处理的集合,通过使用事务,可以对数据库中的数据更新处理提交和取消进行<beigin,commit,rollback>视图本质就是对查询的一个封装。

    2.使用事务的请况

      当数据被更改时,包括insert,update,delete,使用事务可以完成退回的功能,保证业务逻辑的正确性。

    3.事务四大特性(简称ACID)

      ①原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行。
      ②一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致。
      ③隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明。
      ④持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障。
      ⑤要求:表的类型必须是innodb或bdb类型,才可以对此表使用事务。
      ⑥事务语句:

    begin;  // 开启
    commit; // 提交
    rollback; // 回滚

    十三、索引

    1.单列索引和组合索引

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

    2.操作

      ①查看索引:

    show index from table_name;

      ②创建索引:

    create index indexName on mytable(usrname(length));

      ③删除索引:

    drop index [indexName] on mytable;

    3.缺点

      ①虽然索引提高了查询速度,同时却会降低更新表的速度,如对表进行insert,update和delete。
      ②因为更新表时,mysql不仅要保存数据,还要保存一下索引文件。
      ③建立索引会占用磁盘空间的索引文件。

    4.检测运行时间

      ①开启运行时间检测:

    set profiling=1;

      ②执行查询语句:
      ③查看执行的时间:

    show profiles;

    十四、谓词

    1.返回值为真值的函数

      like,in,not in,between,is null,is not null。

    十五、函数

    1.根据用途划分

      ①算术函数(用来进行数值计算的函数)。
      ②字符串函数(用来进行字符串操作的函数)。
      ③日期函数(用来进行日期操作的函数)。
      ④转换函数(用来转换数值类型和值的函数)。
      ⑤聚合函数(用来进行数据聚合的函数)。

    2.case表达式

      ①case表达式分为简单case表达式和搜索case表达式两种。
      ②搜索case表达式包含简单case表达式的全部功能。

    case搜索表达式;
       case when<求值表达式>then<表达式>
          when<求值表达式>then<表达式>
          ....
          else<表达式>
       end
    case简单表达式:
       case<表达式>
           when<表达式>then<表达式>
           when<表达式>then<表达式>
           when<表达式>then<表达式>
           .....
           else<表达式>
       end

    十六、集合运算

    1.集合运算

      集合在数据库领域表示记录的集合,具体来说表,视图和查询执行的结果都是记录的集合。

    2.集合运算注意事项

      ①作为运算对象的记录的列数必须相同。
      ②作为运算对象的记录中列的类型必须一致。
      ③可以使用任何select语句,但order by子句只能在最后使用一次。

    3.表的加法-union并集

    select product-id,product_name from Product union(all) select product_id,product_name from product2;

    4.选取表中的公共部分-intersect交集

    select product_id,product_name from Product intersect select product_id,product_name from Product2 order by product_id;

    5.记录的减法-except差集

    select product_id,product_name from Product except select product_id,product_name from Product2 order by product_id;

    6.表的联结

      联结join就是将其他表的列添加过来,进行添加列的集合运算,union是以行(纵向)为单位进行操作,而联结则是以列(横向)为单位进行的。

    十七、与python交互

    1.安装引入模块

      ①安装mysql模块。

    sudo apt-get install python-mysqldb,pymysql

      ②在文件中引入模块。

    import Mysqldb

    2.Connection对象

      用于建立与数据库的连接。

      
    创建对象:调用connect()方法
    conn=connect(参数列表)
    参数host:连接的mysql主机,如果本机是'localhost'
    参数port:连接的mysql主机的端口,默认是3306
    参数db:数据库的名称
    参数user:连接的用户名
    参数password:连接的密码
    参数charset:通信采用的编码方式,默认是'gb2312',要求与数据库创建时指定的编码一致

    3.对象的方法

    close():关闭连接
    commit()事务:所以需要提交才会生效
    rollback()事务:放弃之前的操作
    cursor()返回Cursor对象:用于执行sql语句并获得结果

    4.Cursor对象

    创建对象:调用Connection对象的cursor()方法
    cursor1 = conn.cursor()

    5.对象的方法

    close()  关闭
    execute(operation [, parameters ])  执行语句,返回受影响的行数
    fetchone() 执行查询语句时,获取查询结果集的第一个行数据,返回一个元组
    next()  执行查询语句时,获取当前行的下一行
    fetchall()  执行查询时,获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回
    scroll(value[,mode])  将行指针移动到某个位置
    mode  表示移动的方式
    mode的默认值为relative  表示基于当前行移动到value,value为正则向下移动,value为负则向上移动
    mode的值为absolute  表示基于第一条数据的位置,第一条数据的位置为0

    6.对象的属性

      ①rowcount只读属性,表示最近一次execute()执行后受影响的行数。
      ②connection获得当前连接对象。

    7.sql语句参数化

    sname = raw_input("请输入学生姓名:")
    params = [sname]
    count = cs1.execute('insert into students(sname) values(%s)',params)

    十八、SQL语句法则

     

    1.数据库和SQL

      法则1-1:关系数据库以行为单位读写数据。 
      法则1-2:一个单元格只能插入一个数据。
      法则1-3:学会标准SQL就可以在各种RDBMS中书写SQL语句了。
      法则1-4:SQL根据功能不他可以分为三类,其中使用最多的是DML。
      法则1-5:SQL语句以分号(;)结尾。
      法则1-6:关键字不区分大小写。
      法则1-7:字符串和日期常数需要使用单引号('')括起来,数字常数无需加注单引号。
      法则1-8:单词之间需要使用半角空格或者换行符进行分隔。
      法则1-9:数据库名,表名和列名等可以使用半角英文字母,半角数字和下划线。
      法则1-10:名称必须以半角英文字母作为开头。
      法则1-11:名称不能重复。
      法则1-12:删除了的表是无法恢复的,在执行drop table语句之前需仔细确认。
      法则1-13:表定义变更之后无法恢复,在执行alter table语句之前需仔细确认。

    2.查询基础

      法则2-1:星号(*)代表全部列的意思。
      法则2-2:设定汉语别名时需要使用双引号("")括起来。
      法则2-3:在select语句中使用distinct可以删除重复行。
      法则2-4:where子句要紧跟在from子句之后。
      法则2-5:注释是SQL语句中用来标识说明或者注意事项的部分,分为一行注释和多行注释两种。
      法则2-6:select子句中可以使用常数或者表达式。
      法则2-7:使用比较运算符一定要注意不等号和等号的位置。
      法则2-8:字符串类型的数据原则上按照字典顺序进行排序,不能与数字的大小顺序混淆。
      法则2-9:希望选取null记录时,需要在条件表达式中使用is null运算符,希望选取不是null的记录时,需要在条件表达式中使用is null的运算符。
      法则2-10:not运算符用来否定某一条件,但是不能滥用。
      法则2-11:多个条件进行组合时,需要使用and运算符或者or运算符。
      法则2-12:and运算符的优先级高于or运算符,想要优先执行or运算符可以使用括号。

    3.聚合与排序

      法则3-1:count函数的结果根据参数的不同而不同,count(*)会得到包含null的数据行数,而count(列名)会得到null之外的数据行数。
      法则3-2:聚合函数会将null排除在外,但count(*)例外,并不会排除null。
      法则3-3:max/min函数几乎适用于所有数据类型的列,sum/avg函数只适用于数值类型的列。
      法则3-4:想要计算值的种类时,可以在count函数的参数中使用distinct。
      法则3-5:在聚合函数中使用distinct,可以删除重复数据。
      法则3-6:group by就像是切分表的一把刀。
      法则3-7:SQL子句的顺序不能改变,也不能互相替换。
      法则3-8:聚合键中包含null时,在结果中会以"不确定"行(空行)的形式表现出来。
      法则3-9:使用group by子句时,select子句中不能出现聚合键之外的列名。
      法则3-10:在group by子句中不能使用select子句中定义的别名。
      法则3-11:group by子句结果的显示是无序的。
      法则3-12:只有select子句和having子句以及order by子句中才能够使用聚合函数。
      法则3-13:having子句要写在group by子句之后。
      法则3-14:聚合键所对于的条件不应该书写在having子句中,而应该书写在where子句当中。
      法则3-15:order by子句通常写在select语句的末尾。
      法则3-16:未指定order by子句中排列顺序时会默认使用升序进行排列。
      法则3-17:排序键中包含null时,会在开头或结尾进行汇总。
      法则3-18:在order by子句中可以使用select子句中定义的别名。
      法则3-19:在order by子句中可以使用select子句中未使用的列和聚合函数。
      法则3-20:在order by子句中不要使用列编号。

    4.数据更新

      法则4-1:原则上,执行一次insert语句会插入一行数据。
      法则4-2:省略insert语句中的列名,就会设置为该列的默认值(没有默认值会设置为null)。
      法则4-3:insert语句的select语句中,可以使用where子句或者group by子句等任何SQL语法(但使用order by子句并不会产生任何效果)(常用在数据表的复制)。
      法则4-4:delete语句的删除对象并不是表或者列,而是记录(行)。
      法则4-5:可以通过where子句指定对象条件来删除部分数据。
      法则4-6:使用update语句可以将值清空为null(但只限于未设置not null约束的列)。
      法则4-7:事务是需要在同一个处理单元中执行的一些列更新处理的集合。
      法则4-8:虽然我们可以不清楚事务开始的时间点,但是在事务结束时一定要仔细进行确认。

    5.复杂查询

      法则5-1:表中存储的是实际数据,而视图中保存的是从列表中取出数据属于所使用的select语句。
      法则5-2:应该将经常使用的select语句做成视图。
      法则5-3:多重视图会降低SQL的性能,应该避免在视图的基础上创建视图。
      法则5-4:定义视图时不要使用order by子句。
      法则5-5:视图和表需要同时进行更新,因此通过汇总得到的视图无法进行更新。
      法则5-6:子查询作为内层查询会首先执行。
      法则5-7:标量子查询就是返回单一值的子查询。
      法则5-8:在细分的组内进行比较时,需要使用关联子查询。

    6.Case表达式

      法则6-1:通常指定关联子查询作为exist的参数。
      法则6-2:作为exist参数的子查询中通常使用select*。
      法则6-3:虽然case表达式中的else子句可以忽略,但最好不要忽略。
      法则6-4:case表达式中的end不能忽略。

    7.集合运算

      法则7-1:集合运算符会除去重复的记录。
      法则7-2:在集合运算符中使用all选项,可以保留重复行。
      法则7-3:进行联结时需要在from子句中使用多张表。
      法则7-4:进行内联结时,必须使用on子句,并且要书写from和where之间。
      法则7-5:使用联结时select子句中的列需要按照<表的别名>.<列名>的格式书写。

  • 相关阅读:
    VS2010引用App_Code下的类文件问题解决方法
    Sql server 2008 sa用户开启教程
    ASP.NET中App_Code,App_Data等文件夹的作用
    错误提示: (provider: Named Pipes Provider, error: 40 Could not open a connection to SQL Server)
    treeview展开一个节点就关闭其他节点
    ASP.NET AJAX Toolkit的安装过程
    c++推荐读物
    for()的多参数
    模板函数I n p u t
    这次真的是下定决心了
  • 原文地址:https://www.cnblogs.com/djcoder/p/12258807.html
Copyright © 2020-2023  润新知