MySQL 数据库简介

数据库database DB

  概念：长期存放在计算机内，有组织可共享的大量数据数据集合，是一个数据

仓库

  作用：保存，管理数据

优点：有效保持数据一致性 完整性 数据的准确

            降低数据冗余   安全      

   数据仓库：整理      

数据库总览：

     关系数据库：sql   数据存放在表中表之间可以互相建立联系 （不利于数据分散）

         MySQL   Oracle   SQL Sever   SQLite   DB2

     非关系数据库NOSQL  不支持连接查询 数据可以分散在不同数据库里

         Redis   MongoDB

数据库管理系统 DBMS  Database Management system

  数据库管理软件，科学组织和存储数据，高效的获取和维护数据

      数据库 （存储）

数据库管理系统   （管理）   

数据库应用系统1.。。。。。。。。。。数据库应用系统n（应用）    

 

 

MySQL简介   Oracle

  概念：是现流行的开源，免费的关系型数据库

  特点：免费 开源数据库    小巧，功能齐全    使用便捷    可运行于Windows或Linux操作系统      可适用于中小型甚至大型网站应用

 

Cmd  net start mysql  开启服务  net stop 服务停止

    Servers.msc

MySQL -uroot  -u表示用户名 -p代表密码

注册表 regedit HKEY-Local-machine

 

在cmd查看是否有建立的表

Show databases  下一步  按一个 ；

显示版本号  select version()；

SQLyog

   可手动操作，管理MySQL数据库的软件工具

  特点  易用 简介图形化

连接数据库

   打开MySQL命令窗口

      在DOS命令行窗口

       在窗口中进入 安装目录\mysql\bin

          可设置环境变量

连接数据库语句

    MySQL -h 服务器主机地址   -u 用户名  -p用户名密码

 

  服务器主机地址  本机IP（127.0.0.1 本机回环地址  ）  

或者主机名localhost

 

-h 后可以有空格  -u后面也可以有空格    p后面不能有空格

 

 

进入数据库后

    使用show databases查看表的数量

   使用use进行对单个表的操作

       在使用 show tables 查看单个表中详细信息

Select 查询 * 所有  from  user；  从user表中查询所有记录

\G 换行

Update 修改用户名代码  update user set Password= password(’123’)  where  User=’root’

  修改完以后：flush  privileges  刷新权限

 ？寻求帮助

 

结构化查询语句SQL

结构化查询语句分类

（数据定义语言）DDL  data definition language定义和管理数据对象，如数据库，数据表  CREATE（创建）  DROP （删除） ALTER（查找）

（数据操作语言）DML  data manipulation language用于操作数据库对象中所包含的数据  INSERT（增加） UPDATE（修改） DELETE （删除）

（数据查询语言）DQL 用于查询数据库数据  SELECT

（数据库控制语言）DCL  用来管理数据库的语言，包括管理权限及数据更改 GRANT COMMIT  ROLLBACK

  

命令行操作数据库

   创建数据库  CREATE DATABASE [IF NOT EXISTS]  数据库名;

   删除数据库   DROP  DATABASE [IF EXISTS] 数据库名;

   查看数据库   SHOW  DATABASES;

   使用数据库   USE  数据库名 ;

 

 

SQL 语句

  #注释

数据库字符集：字符编码

数据库校对规则：utf-8_general_ci不区分大小写

 

工具建表

  建表

显示表结构

  Desc表明

显示表创建语句

  Show create table表名

 

 

 创建数据表

     属于DDL的一种

       语法：

           CREATE DATABASE [IF NOT EXISTS]`表名`(

       `字段名1` 列类型 [属性] [索引][注释],

       `字段名2` 列类型 [属性] [索引][注释],

       ...

       `字段名n`列类型 [属性] [索引][注释]

)[表类型][表字符集][注释]

 

 

数据值和列类型

  列类型

规定数据库中该列存放的数据类型

分为：

 数值类型

   Tinyint 非常小的数据  有负值 -2^7~2^7-1   无负值 0~2^8-1    1字节

   Smallint 较小的数据         -2^15~2^15-1        0~2^16-1    2字节

   Mediumint 中等大小数据       23    23              24     3

   Int    标准整数               31     31              32     4

   Bigint   较大的整数            63      63              64     8

   Float   单精度浮点数       +-1.1754351e-38                      4   

  Double   双               +-2.2250738585072014e-308              8

  Decimal  字符串形式的浮点数      decimal（m，d）                 m   m代表这个数字一共有m位，其中小数点后有d位   一般用于货币

 

 

 字符串类型

       类型                   说明                            最大长度

     Char[(M)]   固定长字符串，检索快但费空间，0<=M<=255       M字符

   varchar[(M)]   可变字符串 0<=M<=65535                         变长度

      Tinytext   微型文本串                                       2^8-1字节

        Text     文本串                                          2^16-1 字节

 

 日期和时间型数值类型

类型                  说明                               取值范围

 DATE     YYYY-MM-DD，日期格式                1000-01-01~9999-12-31

TIME      Hh:mm:ss,时间格式                   -838:59:59~838:59:59

DATETIME  YYYY-MM-DD  hh:mm:ss      1000-01-01  00:00:00~9999-12-31 23:59:59

TIMESTAMP  YYYYMMDDhhmmss 格式表示的时间戳  197010101000000~2037年的某个时刻

YEAR       YYYY格式的年分值                     1901~2155

 

 NULL值

 理解为“没有值”或“未知值”

 不要用NULL进行算数运算，结果仍为NULL

   如何选择数据类型

     整数和浮点

     日期类型

     Char和varchar

 

数据字段属性

  UNSIGNED   无符号的  声名该数据列不允许负数

 ZEROFILL     0填充的  不足位数的用0来填充，如int(3),5则为005

 AUTO_INCREMENT    自动增长的，每添加一条数据，自动在上一个记录上加1

                    通常用于设置主键，且为整数类型

                    可定义起始值和步长

NULL 和NOT NULL

默认为NULL 即没有插入该列的数值

如果设置为NOT NULL ,则该列必须有值

 

DEFAULT

     默认的

     用于设置默认值

            例如，性别字段，默认为“男”，否则为“女”；若无指定该列的值，则默认为“男”的值

创建表

   Create table if not exists 表名(

            列名 类型  属性,

            列名 类型  属性,

列名 类型  属性,

)

设置主键 primary key

 注释 comment

查看数据库的定义 show create database “”；

 

Desc  student  显示表结构

 

Blob 数据类型

设置严格检查模式

  Set  SQL_mode=‘strict_trans_tables’;

自增列：默认初始值1，步长1

能否改变自增列的初始值？100  能否改变步长

 Create table if not exists 表名(

            列名 类型  属性,

            

)auto_increment=100；

改变自增列初始值只改变当前表

 

改变步长  影响所有使用自增列的表

  Set  @@auto_increment_increment=5；

同一个列可以被多个属性修饰，但有顺序

Unsigned zerofill  not null  default

   

数据表的类型

  ENGINE=MyISAM

  ENGINE=InnoDB

MySQL数据表的类型：

   MyISAM （适用于节约空间及相应速度）   InnoDB（安全性，事务处理及多用户操作数据表）  HEAP    BOB   CSV 等

常见的MyISAM与InnoDB类型

名称                 MyISAM                 InnoDB

事务处理            不支持                  支持                                  

数据行锁定           不支持                  支持                                 

外键约束              不支持                  支持                                  

全文索引              支持                  不支持                                 

表空间大小            较小                   较大，约2倍     

查看mysql 所支持的引擎类型

Show  ENGINES

查看默认引擎show variables like ‘storage_engine’;      

外键

  一个表里的一列引用于另一个表里的一列被引用的那一列就叫外键，约束了引用之后就约束了两个表里数据的一致性，避免出现错误

MySQL数据表以文件方式存放在磁盘中

   包括表文件，数据文件以及数据库的选项文件

   位置：MySQL安装目录\data下存放数据表。目录名对应数据库名，该目录下文件名对应数据表

InnoDB类型数据表只有一个*.frm文件，以及上一级目录的ibdata1文件

MyISAM类型数据表对应三个文件：*.frm--表结构定义文件  *.MYD--数据文件

 *.MYI--索引文件

[表类型][表字符集][注释]

设置库的字符集 Character set utf8    设置表的字符集  charset=utf8

设置列的字符集 Character set utf8

 

修改数据表：

修改表名：ALTER TABLE 旧表名 RENAME AS 新表名

添加字段：ALTER TABLE 表名  ADD 字段名 列类型[属性]

修改字段：ALTER TABLE 表名 MODIFY 字段名 列类型[属性]

          ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型[属性]

删除字段：ALTER TABLE 表名 DROP 字段名

 

一个表里的外键可以有一个也可以有多个，有主键的叫主表，外键的叫从表或子表

外键的作用

创建外键

   建表时指定外键约束  用constraint

   建表后修改

删除外键

 

 

删除外键，删除表

 

添加数据

INSERT命令 语法：insert into 表名[ (字段1，字段2，...)] values（‘值1’,’值2’,’值3’）

MySQL里面的函数 now（）代表当前时间

 

修改数据

UPDATE命令 语法 update 表名 set column_name=value[,column_name=2=value2,....]

[where condition];

Column_name为要更改的数据列

Value为修改后的数据，可以为变量，具体值，表达式或者嵌套的select结果

Condition 为筛选条件，如不指定则修改该表的所有列数据

WHERE中的运算符

      = 等于

     <>或!= 不等于

     Between 在某一范围之间

     And   并且       or 或

 

函数  concat() 追加  concat(“要追加的”, 原先的)

删除命令

   DELETE命令 语法： delete from 表名 [where condition];

Condition为筛选条件，如不指定则删除该标的所有列数据

用这个删除数据时，自增列依然从原来的基础上进行，会一行一行记录日志

 

   Truncate table 表名 ;  用这个删除自增值恢复到初始值重新开始会直接全部删除

  用于完全清空表数据，但表结构、索引、约束等不变

区别于DELETE命令

  相同：都能删除数据，不删除表结构，但TRuncate速度更快

  不同：使用truncate table重新设置auto_increment计数器

         使用truncate table不会对事务有影响

表类型存储引擎默认是InnoDB

同样使用delete from清空表数据，重启数据库服务后，对于InnoDB的表，自增列从初始值重新开始而MyISAM类型的表依然从上一个自增数据的基础上开始

InnoDB 存储在内存中 MyISAM存储在文件里

 

 

 

DQL 数据库查询语言

   查询数据库数据如select语句

   简单的单表查询或多表的复杂查询和嵌套查询

   数据库语言中最核心、最重要的语句

   使用频率最高的语句

    

SELECT *FROM grade1;

SELECT语法

   SELECT [ALL | DISTINCT]

  {*| table.*| [table.field1[as alias1],[,table.fileld2[as,alias2]][,...]}

FROM table_name [as table_alias ]

[left | right| inner join table_name2]联合查询

[WHERE ....]  #指定结果需满足的条件

[GROUP BY...]  指定结果按照哪几个字段来分组

[HAVING ....]   过滤分组的记录必须满足的次要条件

[ORDER BY...]   指定查询记录按一个或者多个条件排序

[LIMIT {[offset,]row_count | row_countOFFSET offset}]; 指定查询的记录从哪条至哪条

Unique 唯一约束

*表示查询所有列

指定查询字段

   查询表结果时，可指定查询结果的数据列

      查询表中所有的数据列结果，采用 * 符号；

            如 select * from student； 效率低不推荐

      查询指定列（学号 姓名）

               Select  studentno,studentname from student;

               取别名  as 给列取别名能够省略

                Select  studentno as 学号,studentname  姓名 from student;

                为一个函数查询后的表达式取新的别名

               Select  concat（studentname） as 姓名 from student;

                去除重复项 distinct

                  Select  distinct  studentno from result；

           查询步长  select @@auto_increment_increment

 

Where 条件语句

       用于检索数据表中 符合条件的记录

        搜索条件可有一个或多个逻辑表达式组成，结果一般为真或假

        搜索条件的组成

            逻辑操作符                比较操作符

     逻辑操作符      

  AND或&&         同时为真结果才为真

              OR     ||        一个为真就为真

             NOT     !         操作符为假则结果为真

 

  比较操作符

     Is null     a  is null      若操作符为空则结果为真

     Is not null               若操作符不为空则结果为真

     Between    a between b and c  若a 在b 与c 之间则结果为真

    Like        a  like  b           SQL模式匹配，若a b 匹配则结果为真（结合使用通配符%表示0到任意个字符  _ 表示一个字符 \转义符）

   In         a  in  （a1 ，a2，、、、） 若a 等于a1,a2,..中某一个，则结果为真

Escape 标注自定的转义符

null 与’’  不同 查询时都应写出来

连接查询

 内连接 inner join 查询两个表中的两个结果集中交集  示例如下：

SELECT s.studentno ,studentname,subjectid,studentresult

FROM student s

INNER JOIN result r

ON r.`StudentNo`=s.`StudentNo`

 外连接 outer  join

      左外连接 left join  以左表作为基准，右边表来一一匹配，匹配不上的返回左表的记录右表以null填充

        右外连接 right  join

  自连接

 

等值连接

SELECT s.studentno ,studentname,subjectid,studentresult

FROM student s,result r

WHERE r.`StudentNo`=s.`StudentNo`  等同于内连接

 

  非等值连接

  

不同join对比

Inner join 如果表中有至少一个匹配则返回行

Left join  即使右表中没有匹配，也从左表中返回所有的行

Right join   即使左表中没有匹配，也从右表中返回所有的行  

 子查询

     在查询语句的where语句中，有嵌套了另外一个查询语句

 

Mysql 函数

     数学函数

         Abs（）绝对值

         Ceiling（）  求最小的大于值的整数

         Floor （） 求最大的小于值的整数

         Rand（） 返回0-1之间的随机数

         Sing（） 判断一个数字的符号负数为-1 正数1  0返回0

 

     字符串函数

          Char_length()  返回字符串长度

          Concat()  合并字符串

          Insert ( ‘’，1,3，‘’)   替换字符串，从某个位置替换某个长度

          Lower()  变小写

          Upper()  变大写

           Left(‘’,3)  从左边节3

           Right()      右

           Replace(‘ 这个 ’, ‘XX’ ，‘XX’ ) 把这个字符串里的什么替换成什么

           Substr(‘ 这个’，1,3) 截取从哪个位置开始截取，节多长

           Reverse（） 字符串反转

     日期和时间函数

           Current_date()  获得当前日期

           Curdate()   获得当前日期

           Now（）   获得当前时间和日期

           Localtime（）获得当前时间和日期

           Sysdate（） 获得当前时间和日期

           Year（now（）） 获得当前日期中的年

           Month（now（））    月

           Day（now（））       日

           Hour（now（））      时

           Minute（now（））     分

           Second（now（））     秒

           DateDIFF（date1，date2,） date1到date2相隔的天数

           

     系统信息函数

            Version（） 查看版本信息

            User（）    查询用户

  聚合函数 MySQL的统计函数

        Count（）返回满足select条件的记录总和数，如select count（*）*不建议使用效率低

        Sum（）返回数字字段或表达式列作统计，返回一列的总和

        Avg()   通常为数值字段或表达列作统计，返回一列的平均值

        Max()   可以作为数值字段，字符字段或表达式列做统计，返回最大值

        Min（）  ——————————————————————    最小值   

 

Having  在分组以后进行筛选 他的作用等同于where   

 

 

 

 

MySQL事务处理

    事务就是将一组SQL语句放在同一批次内去执行

如果一个SQl 语句出错，则该批次内的所有SQL都将被取消执行

MySQL事务处理只支持InnoDB和BDB数据表类型

 

事务的ACID原则

   原子性 atomic  事务处理中包括一组操作语句这一组当一个执行

   一致性 consist  事务同时改变

   隔离性 isolated  

   持久性  durable

MySQL事务的实现方法

   Set autocommit 使用set语句来改变自动提交模式

   Set autocommit =1  开启自动提交模式

                  0  关闭

  MySQL中默认是自动提交

  Start transaction  开始一个事务，标记事务的起始点

  Commit      提交一个事务给数据库

  Rollback   将事务回滚，数据回到本次事务的初始状态

  Set autocommit=1 还原MySQL数据库的自动提交

MySQL事务处理步骤

   Set autocommit=0 关闭MySQL的自动提交

   Start transaction 开始一个事务，标记事务的起始点

   Commit 提交一个事务     rollback  将事务回滚，所有commit中的操作将被取消

     Set commit =1    还原MySQL数据库的自动提交

事务实现方法

SET autocommit=0;

START TRANSACTION;

UPDATE account SET cash=cash-500 WHERE NAME='A';

UPDATE account SET cash=cash+500 WHERE NAME='B';

#commit;

ROLLBACK;

SET autocommit=1;

 

数据库索引

     提高查询速度、确保数据的唯一性、可以加速表和表之间的连接，实现表与表之间的参照完整性、使用分组和排序字句进行数据检索时，可以显著减少分组和排序的时间、全文检索字段进行搜索优化

分类

  主键索引 primary key

       某一个属性能唯一标识一条记录

     特点：

        最常见的索引类型

        确保数据记录的唯一性

        确定特定数据记录在数据库中的位置

 唯一索引 unique

    作用：

       避免同一个表中某数据列中的值重复

   与主键索引的区别

       主键索引只能有一个、唯一索引可有多个，主键索引不能为空，唯一索引可以有

 常规索引 index

     快速定位特定数据

   注意：

     Index和key 关键字都可设置常规索引

     应加在查找条件的字段

     不宜添加太多常规索引，影响数据的插入、删除和修改操作

 全文索引 fulltext

     作用：快速定位特定数据

     注意：只能用与MyISAM类型的数据表

           只能用于char varchar text数据列类型

           适合大型数据集

Select  *  form  student  where   Match（studentname）against（‘love’）

Explain 解析进行步骤

 

删除索引

DROP INDEX c ON test3;         删除索引

ALTER TABLE test3 DROP INDEX e;   删除索引

ALTER TABLE test3 DROP PRIMARY KEY; 删除主键索引

显示索引信息

SHOW INDEX FROM student;

索引准则

  索引不是越多越好

  不要对经常变动的数据加索引

  小数据量的表建议不要加索引

  索引一般应加在查找条件的字段

MySQL 数据备份方法

       MySQLdump 备份  

           MySQLdump和MySQL是同级命令

           作用：转存数据库

                搜集数据库进行备份

                将数据转移到另一个SQL服务器(不一定是MySQL服务器)

           语法： mysqldump -h 主机名  -u 用户名  -p[options] 数据库名 [table1 table2         

 

table3]  > path/filename.sql 预存该文件目录，需有该目录读写权限

             查看帮助文档

              Mysqldump --help      更方便的查看 mysqldump --help| more

        --add-drop-table 导出SQL脚本加上DROP table if exists 语句默认是打开的，可以用--skip-add-drop-table 来取消

        --add-locks 该选项会在insert语句中捆绑一个lock table和unlock table语句，好处：

防止记录被再次导入时，其他用户对表进行的操作，默认是打开的

        -t或--no-create-info 忽略不写重新创建每个转储表的create table语句

        -c或--complete-insert  在每个insert语句的列上加上字段名在数据库导入另一个数据库时非常有用

        -d 或--no-data 不写任何行信息。对于只想转储表的结构很有用

        --where”where-condition”,-w”where-condition” 只转储给定的where条件选择的记录

     --opt  该选项是速记；等同于指定  --add-drop-tables

                                    --add-lockin--create-option  --disable-keys-extend-insert                  --lock-tables                                   --quick   --set-charset

 数据恢复    

        source命令恢复

              方法一：用source语法

                  /path/是一个绝对路径，并且必须是MySQL运行用户有权限读取的文件

                  Source在MySQL命令里执行

             Source   /path/db_name.sql       都要先创建或选择数据库

        用cmd先进入数据库选择要进行恢复的表use ‘table’； 然后开始恢复source 文件备份的路径

         Mysql命令恢复

            Mysql -uroot -p dbname </path/db_name.sql;

       使用SQLyog工具来备份和恢复

        

       使用SQL命令方式来恢复和备份

             

SELECT studentno,studentname INTO OUTFILE '文件名' FROM student;

LOAD DATA INFILE '文件路径' INTO TABLE 表名(id,sname);

   直接拷贝数据库文件和相关配置文件

数据库设计

   规范化数据库设计

      良好的数据库设计

            节省数据的存储空间、能够保证数据的完整性、方便进行数据库应用系统的开发

   需求分析阶段：分析客户的业务和数据处理需求

   概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整

   详细设计阶段：应用三大范式审核数据库结构

   代码编写阶段：物理实现数据库，编码实现应用

设计数据库步骤：

     收集信息

     标识实体（entity）

     标识每个实体需要存储的详细信息

     标识实体之间的关系（relationship）

1. R图

   实体，一般是名词，   长方形

   属性，一般是名词    椭圆形

   联系，一般是动词      菱形  

映射基数

   一对一   

   一对多

多对一

多对多

用二维表的形式表示实体和实体间联系的数据模型即关系模式

1. R图转换为关系模式的步骤

   把每个实体都转化为关系模式R(U)形式    建立实体间关系的转换

   不合规范的表设计

       信息重复

       更新异常

       插入异常

          无法正确表示信息

       删除异常

          丢失有效信息

数据库设计三大范式

        第一范式（1st NF）：第一范式的目标是确保每列的原子性

        如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）

        第二范式（2nd NF）:要求每个表只描述一件事情

        第三范式（3nd NF）：如果一个关系满足2NF，并且除了主键以外的其他列都不出传递依赖与主键列，则满足第三范式