MySql基本学习知识点：

1．Mysql的简介：

（1）：常识：

  • MySQL是一种关系数据库管理系统，是一种开源软件
  • 由瑞典MySQL AB公司开发，2008年1月16号被Sun公司收购。2009年，SUN又被Oracle收购
  • MySQL软件采用双授权政策，分为社区版和商业版。由于体积小、速度快、总体拥有成本低，尤其是开放源码特点，一般中小型网站的开发都选择MySQL作为网站数据库。
  • 由于其社区版的性能卓越，搭配PHP和Apache可组成良好的开发环境。
  • MySQL能够工作在众多不同的平台上

 

 

（2）：基本安装信息：

  • 端口：3306
  • 默认用户：root
  • 字符集：默认字符集latin1，应设置为gbk或utf-8
  • 安装时可以启动远程登录、创建匿名账户
  • 可安装图形化操作界面，方便操作

 

 

（3）：安装mysql错误操作：

·手动删除mysql安装目录

·重新运行配置向导MySQLInstanceConfig.exe

·删除C:\ProgramData\MySQL目录

 

 

2.Mysql命令：

（1）．连接数据库：

  • 格式： mysql -h主机地址 -u用户名 －p用户密码

    1、连接到本机上的MYSQL。
  • 进入目录mysql\bin，再键入命令mysql -u root -p，

    2、连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：
        mysql -h110.110.110.110 -u root -p 123;（注:u与root之间可以不用加空格，其它也一样）

    3、退出MYSQL命令： exit （回车）
  • 格式：mysqladmin -u用户名 -p旧密码 password 新密码
    1、给root加个密码ab12。
    mysqladmin -u root -password ab12
    注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
    2、再将root的密码改为djg345。
    mysqladmin -u root -p ab12 password djg345
  • 注意：和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符
    格式：grant select,insert,update,delete on 数据库.* to 用户名@登录主机 identified by “密码”

    1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入MYSQL，然后键入以下命令：
        grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”;
    但增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见2。
    2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。
    grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;

    如果你不想test2有密码，可以再打一个命令将密码消掉。
    grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”;

（2）．修改密码：

 

（3）.添加新用户：

 

 

（4.1）.创建数据库：

  • 注意：创建数据库之前要先连接Mysql服务器
    命令：create database <数据库名>
    例1：建立一个名为xhkdb的数据库
    mysql> create database xhkdb;
    例2：创建数据库并分配用户
    ①CREATE DATABASE 数据库名;
    ②GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.* TO 数据库名@localhost IDENTIFIED BY '密码';
    ③SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码');
    依次执行3个命令完成数据库创建。注意：中文 “密码”和“数据库”是户自己需要设置的。

 

（4.2）.显示数据库：

  • 命令：show databases （注意：最后有个s）
    mysql> show databases;

    注意：为了不再显示的时候乱码，要修改数据库默认编码。以下以GBK编码页面为例进行说明：
    1、修改MYSQL的配置文件：my.ini里面修改default-character-set=gbk
    2、代码运行时修改：
       ①Java代码：jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk
       ②PHP代码：header("Content-Type:text/html;charset=gb2312");
       ③C语言代码：int mysql_set_character_set( MYSQL * mysql, char * csname)；
    该函数用于为当前连接设置默认的字符集。字符串csname指定了1个有效的字符集名称。连接校对成为字符集的默认校对。该函数的工作方式与SET NAMES语句类似，但它还能设置mysql- > charset的值，从而影响了由mysql_real_escape_string() 设置的字符集。

 

（4.3）.删除数据库：

  • 命令：drop database <数据库名>
    例如：删除名为 xhkdb的数据库
    mysql> drop database xhkdb;
    例子1：删除一个已经确定存在的数据库
       mysql> drop database drop_database;
       Query OK, 0 rows affected (0.00 sec)
    例子2：删除一个不确定存在的数据库
       mysql> drop database drop_database;
       ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
          //发生错误，不能删除'drop_database'数据库，该数据库不存在。
       mysql> drop database if exists drop_database;
       Query OK, 0 rows affected, 1 warning (0.00 sec)//产生一个警告说明此数据库不存在
       mysql> create database drop_database;
       Query OK, 1 row affected (0.00 sec)
       mysql> drop database if exists drop_database;//if exists 判断数据库是否存在，不存在也不产生错误
       Query OK, 0 rows affected (0.00 sec)

 

（4.4）.连接数据库：

  • 命令： use <数据库名>
    例如：如果xhkdb数据库存在，尝试存取它：
       mysql> use xhkdb;
    屏幕提示：Database changed
    use 语句可以通告MySQL把db_name数据库作为默认（当前）数据库使用，用于后续语句。该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的USE语句：
       mysql> USE db1;
       mysql> SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
       mysql> USE db2;
       mysql> SELECT COUNT(*) FROM mytable;   # selects from db2.mytable
    使用USE语句为一个特定的当前的数据库做标记，不会阻碍您访问其它数据库中的表。下面的例子可以从db1数据库访问作者表，并从db2数据库访问编辑表：
       mysql> USE db1;
       mysql> SELECT author_name,editor_name FROM author,db2.editor
        >  WHERE author.editor_id = db2.editor.editor_id;
    USE语句被设立出来，用于与Sybase相兼容。
    有些网友问到，连接以后怎么退出。其实，不用退出来，use 数据库后，使用show databases就能查询所有数据库，如果想跳到其他数据库，用
       use 其他数据库名字
    就可以了。

 

（4.5）.当前选择的数据库：

  • 命令：mysql> select database();
    MySQL中SELECT命令类似于其他编程语言里的print或者write，你可以用它来显示一个字符串、数字、数学表达式的结果等等。如何使用MySQL中SELECT命令的特殊功能？
    1.显示MYSQL的版本
    mysql> select version(); 
    +-----------------------+ 
    | version()| 
    +-----------------------+ 
    | 6.0.4-alpha-community | 
    +-----------------------+ 
    1 row in set (0.02 sec) 
  • SELECT DATABASE( ) 当前数据库名 (或者返回空)
  • SELECT USER( ) 当前用户名
  • SHOW STATUS服务器状态
  • SHOW VARIABLES服务器配置变量
    2. 显示当前时间
    mysql> select now(); 
    +---------------------+ 
    | now()| 
    +---------------------+ 
    | 2009-09-15 22:35:32 | 
    +---------------------+ 
    1 row in set (0.04 sec) 

    3. 显示年月日
    SELECT DAYOFMONTH(CURRENT_DATE); 
    +--------------------------+ 
    | DAYOFMONTH(CURRENT_DATE) | 
    +--------------------------+ 
    |15 | 
    +--------------------------+ 
    1 row in set (0.01 sec) 
      
    SELECT MONTH(CURRENT_DATE); 
    +---------------------+ 
    | MONTH(CURRENT_DATE) | 
    +---------------------+ 
    | 9 | 
    +---------------------+ 
    1 row in set (0.00 sec) 
      
    SELECT YEAR(CURRENT_DATE); 
    +--------------------+ 
    | YEAR(CURRENT_DATE) | 
    +--------------------+ 
    | 2009 | 
    +--------------------+ 
    1 row in set (0.00 sec) 

    4. 显示字符串
    mysql> SELECT "welecome to my blog!"; 
    +----------------------+ 
    | welecome to my blog! | 
    +----------------------+ 
    | welecome to my blog! | 
    +----------------------+ 
    1 row in set (0.00 sec) 

    5. 当计算器用
    select ((4 * 4) / 10 ) + 25; 
    +----------------------+ 
    | ((4 * 4) / 10 ) + 25 | 
    +----------------------+ 
    |                26.60 | 
    +----------------------+ 
    1 row in set (0.00 sec) 

    6. 串接字符串
    select CONCAT(f_name, " ", l_name) 
    AS Name 
    from employee_data 
    where title = 'Marketing Executive'; 
    +---------------+ 
    | Name          | 
    +---------------+ 
    | Monica Sehgal | 
    | Hal Simlai    | 
    | Joseph Irvine | 
    +---------------+ 
    3 rows in set (0.00 sec) 
    注意：这里用到CONCAT()函数，用来把字符串串接起来。另外，我们还用到以前学到的AS给结果列'CONCAT(f_name, " ", l_name)'起了个假名。

 

（5.1）.创建数据表：

命令：create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);

例如，建立一个名为MyClass的表，

字段名

数字类型

数据宽度

是否为空

是否主键

自动增加

默认值

id

int

4

否

primary key

auto_increment

 

name

char

20

否

 

 

 

sex

int

4

否

 

 

0

degree

double

16

是

 

 

 

  • mysql> create table MyClass(
    > id int(4) not null primary key auto_increment,【主键自增】
    > name char(20) not null,
    > sex int(4) not null default '0',
    > degree double(16,2));

 

（5.2）.删除数据表：

  • 命令：drop table <表名>
    例如：删除表名为 MyClass 的表
       mysql> drop table MyClass;
    DROP TABLE用于取消一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消，所以使用本语句要小心！
    注意：对于一个带分区的表，DROP TABLE会永久性地取消表定义，取消各分区，并取消储存在这些分区中的所有数据。DROP TABLE还会取消与被取消的表有关联的分区定义（.par）文件。
    对与不存在的表，使用IF EXISTS用于防止错误发生。当使用IF EXISTS时，对于每个不存在的表，会生成一个NOTE。
    RESTRICT和CASCADE可以使分区更容易。目前，RESTRICT和CASCADE不起作用。

 

（5.3）.表插入数据：

  • 命令：insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]
    例如：往表 MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99， 编号为3 的名为Wang 的成绩为96.5。
       mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);
    注意：insert into每次只能向表中插入一条记录。

 

（5.4）.查询表中的数据：

  • 1)、查询所有行
    命令： select <字段1，字段2，...> from < 表名 > where < 表达式 >
    例如：查看表 MyClass 中所有数据
       mysql> select * from MyClass;
    2）、查询前几行数据
    例如：查看表 MyClass 中前2行数据
    mysql> select * from MyClass order by id limit 0,2;

  • select一般配合where使用，以查询更精确更复杂的数据。

 

（5.5）.删除表中的数据：

命令：delete from 表名 where 表达式
例如：删除表 MyClass中编号为1 的记录
mysql> delete from MyClass where id=1;

 

（5.6）.修改表中数据：

  • 语法：update 表名 set 字段=新值,… where 条件
       mysql> update MyClass set name='Mary' where id=1;
    例子1：单表的MySQL UPDATE语句：
       UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
    例子2：多表的UPDATE语句：
    UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
    UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。

 

（5.7）.增加字段：

  • 命令：alter table 表名 add字段 类型 其他;
    例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0
       mysql> alter table MyClass add passtest int(4) default '0'
    加索引
       mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);
    例子： mysql> alter table employee add index emp_name (name);
    加主关键字的索引
      mysql> alter table 表名 add primary key (字段名);
    例子： mysql> alter table employee add primary key(id);
    加唯一限制条件的索引
       mysql> alter table 表名 add unique 索引名 (字段名);
    例子： mysql> alter table employee add unique emp_name2(cardnumber);
    删除某个索引
       mysql> alter table 表名 drop index 索引名;
    例子： mysql>alter table employee drop index emp_name;
    增加字段：
    mysql> ALTER TABLE table_name ADD field_name field_type;

    修改原字段名称及类型：
    mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
    删除字段：
    MySQL> ALTER TABLE table_name DROP field_name;

 

（5.8）.修改表名：

  • 命令：rename table 原表名 to 新表名;
    例如：在表MyClass名字更改为YouClass
       mysql> rename table MyClass to YouClass;
    当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。
    如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。
    RENAME TABLE 在 MySQL 3.23.23 中被加入。

 

（6）.备份数据库：

  • 命令在DOS的[url=file://\\mysql\\bin]\\mysql\\bin[/url]目录下执行
    1.导出整个数据库
    导出文件默认是存在mysql\bin目录下
        mysqldump -u 用户名 -p 数据库名 > 导出的文件名
        mysqldump -u user_name -p123456 database_name > outfile_name.sql
    2.导出一个表
        mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
        mysqldump -u user_name -p database_name table_name > outfile_name.sql
    3.导出一个数据库结构
        mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
        -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table
    4.带语言参数导出
        mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql
    例如，将aaa库备份到文件back_aaa中：
    [root@test1 root]# cd　/home/data/mysql
    [root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

 

（7）.一个建库和建表的实例：

  • drop database if exists school; //如果存在SCHOOL则删除
    create database school; //建立库SCHOOL
    use school; //打开库SCHOOL
    create table teacher //建立表TEACHER
    (
    id int(3) auto_increment not null primary key,
    name char(10) not null,
    address varchar(50) default ‘深圳’,
    year date
    ); //建表结束
    //以下为插入字段
    insert into teacher values(”,’allen’,'大连一中’,'1976-10-10′);
    insert into teacher values(”,’jack’,'大连二中’,'1975-12-23′);
    如果你在mysql提示符键入上面的命令也可以，但不方便调试。
    1、你可以将以上命令原样写入一个文本文件中，假设为school.sql，然后复制到c:\\下，并在DOS状态进入目录[url=file://\\mysql\\bin]\\mysql\\bin[/url]，然后键入以下命令：
        mysql -uroot -p密码 < c:\\school.sql
    如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。
    2、或者进入命令行后使用 mysql> source c:\\school.sql; 也可以将school.sql文件导入数据库中。
  • 注：在建表中
    1、将ID设为长度为3的数字字段:int(3)；并让它每个记录自动加一:auto_increment；并不能为空:not null；而且让他成为主字段primary key。
    2、将NAME设为长度为10的字符字段
    3、将ADDRESS设为长度50的字符字段，而且缺省值为深圳。
    4、将YEAR设为日期字段。

 

3.数据类型

·  数值类型：

  • SMALLINT: 2个字节
  • INT: 4个字节      // age int(10)
  • INTEGER:INT的同义词
  • BIGINT : 8个字节
  • FLOAT : 4个字节
  • DOUBLE : 8个字节   //score float(10,2)
  • 字符串(字符)类型
    • CHAR：固定长度字符串  sex char(2)
    • VARCHAR:可变长度字符串 name varchar(20)
    • VARCHAR使用起来较为灵活，CHAR处理速度更快
    • TEXT:非二进制大对象（字符）
    • BLOB:二进制大对象（非字符）
  • 日期/时间类型：
    • DATE: YYYY-MM-DD
    • DATETIME: YYYY-MM-DD HH:MM:SS
    • TIMESTAMP: YYYY-MM-DD HH:MM:SS
    • TIME:HH:MM:SS
    • YEAR:YYYY
  • 主键自增
    • 不使用序列，通过auto_increment

 

 

4.SQL语句语法：

  • SQL语言包含4个部分：
    • 数据定义语言（如create,drop,alter等语句）
    • 数据查询语言（select语句）
    • 数据操纵语言（insert,delete,update语句）
    • 数据控制语言（如grant,revoke,commit,rollback等语句）

 

数据操纵语言针对表中的数据，而数据定义语言针对数据库或表

 

 

  • 数据定义语言

create database school;  /*创建数据库*/

show databases;          /*显示所有数据库select database()*/

use school;                     /*指定默认数据库*/

create table student(       /*创建表*/

id int(10) primary key  auto_increment,  /*主键，自增*/

name varchar(8),

sex char(1),

score float(6,2)

);

 

show tables;                     /*显示当前库中表清单*/

/* 显示指定表结构show columns from student;*/

describe student;

show create table student; /*显示建表sql语句*/

drop table student;           /*删除表*/

drop database school;       /*删除数据库*/

 

  • 数据操纵语言

insert into student values("张三", "t",87.5);

insert into student values(null,"张三","t",87.5);

//insert into student (name,sex,score) values("张三","t",87.5);

 select * from student;

 insert into student values(null,"李四","男",89);

alter table student modify sex char(2);

insert into student values(null,"李四","男",89);

select * from student;

update student set sex ="女";

select * from student;

 update student set sex="男" where id=1;

select * from student;

 update  student set name="王五" ,score=100 where id=2;

select * from student;

delete from student where name="王五";

select * from student;

delete from student;

select * from student;

 

 

  • 数据查询语言（select语句）
    • select * from student;
    • select  id,name,score from student;
    • select * from student where id<5 and sex=“男”;
    • select count(*) from student ;
    • select  max(score) from student;
    • select id ,name,score from student  order by score desc;
    • select * from stu where name like "张%";

 

 

  • 更改表结构语句
    • alter table student add birth date;
    • insert into student values(null,"赵六","男",100, now() );
    • insert into student values(null,"赵六","男",100, “1980-12-23");
    • alter table student change birth birthday date ;
    • alter table student modify sex char(4) not null ;
    • alter table student modify sex  char(2) after birthday;
    • alter table student rename as stu;

alter table stu drop birthday;

 

  • 多表查询和外键关联

create table person(  id int primary key,   name varchar(6), sex char(2), age int(10)  );

insert into person values(1,"张三","男",45);

insert into person values(2,"李四","男",32);

insert into person values(3,"王五","女",32);

 

create table pet(

 id int  auto_increment ,

 name varchar(6),

 masterid int,

 primary key(id),

 constraint fk foreign key (masterid) references person(id)

 );

 

 

  • 分页语句
    • select * from table limit (start-1)*limit,limit;
    • 其中start是页码

limit是每页显示的条数.

 

5.导入导出：

  • 命令行操作
    • 导入
      • 方法1：mysql>source d:\dbname.sql
      • 方法2：mysql -u root -p 数据库名 < dbname.sql
      • 导出
        • 导出整个数据库
          • mysqldump -u root -p 数据库名 > dbname.sql
          • 只导出一个表
            • mysqldump -u root -p 数据库名 表名> dbname.sql
  • 图形界面操作
    • Navicat
    • 导入：运行SQL文件
    • 导出：转储SQL文件

 

 

6.mysql数据库的内连接与外连接的区别：

内连接指的是把表连接时表与表之间匹配的数据行查询出来，就是两张表之间数据行匹配时，要同时满足ON语句后面的条件才行。

左连接的意思是，无论是否符合ON语句后面的表连接条件都会把左边那张表的记录全部查询出来，右边的那张表只匹配符合条件的数据行。右连接则与之相反（这里同样OUTER 可以省略

 

7.MySql的事务处理：

MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；
  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
  • ROLLBACK TO identifier 把事务回滚到标记点；
  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ U NCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

 

 

数据库事务隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读 、不可重复读 、幻读 这几类问题。

 

√: 可能出现    ×: 不会出现

 

脏读

不可重复读

幻读

Read uncommitted

√

√

√

Read committed

×

√

√

Repeatable read

×

×

√

Serializable

×

×

×

 

注意：我们讨论隔离级别的场景，主要是在多个事务并发 的情况下，因此，接下来的讲解都围绕事务并发。

Read uncommitted 读未提交

公司发工资了，领导把5000元打到singo的账号上，但是该事务并未提交，而singo正好去查看账户，发现工资已经到账，是5000元整，非常高 兴。可是不幸的是，领导发现发给singo的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后singo实际的工资只有 2000元，singo空欢喜一场。

 

出现上述情况，即我们所说的脏读 ，两个并发的事务，“事务A：领导给singo发工资”、“事务B：singo查询工资账户”，事务B读取了事务A尚未提交的数据。

当隔离级别设置为Read uncommitted 时，就可能出现脏读，如何避免脏读，请看下一个隔离级别。

Read committed 读提交

singo拿着工资卡去消费，系统读取到卡里确实有2000元，而此时她的老婆也正好在网上转账，把singo工资卡的2000元转到另一账户，并在 singo之前提交了事务，当singo扣款时，系统检查到singo的工资卡已经没有钱，扣款失败，singo十分纳闷，明明卡里有钱，为 何......

出现上述情况，即我们所说的不可重复读 ，两个并发的事务，“事务A：singo消费”、“事务B：singo的老婆网上转账”，事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

当隔离级别设置为Read committed 时，避免了脏读，但是可能会造成不可重复读。

大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。如何解决不可重复读这一问题，请看下一个隔离级别。

Repeatable read 重复读

当隔离级别设置为Repeatable read 时，可以避免不可重复读。当singo拿着工资卡去消费时，一旦系统开始读取工资卡信息（即事务开始），singo的老婆就不可能对该记录进行修改，也就是singo的老婆不能在此时转账。

虽然Repeatable read避免了不可重复读，但还有可能出现幻读 。

singo的老婆工作在银行部门，她时常通过银行内部系统查看singo的信用卡消费记录。有一天，她正在查询到singo当月信用卡的总消费金额 （select sum(amount) from transaction where month = 本月）为80元，而singo此时正好在外面胡吃海塞后在收银台买单，消费1000元，即新增了一条1000元的消费记录（insert transaction ... ），并提交了事务，随后singo的老婆将singo当月信用卡消费的明细打印到A4纸上，却发现消费总额为1080元，singo的老婆很诧异，以为出 现了幻觉，幻读就这样产生了。

注：MySQL的默认隔离级别就是Repeatable read。

Serializable 序列化

Serializable 是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。



二、脏读、幻读、不可重复读

1.脏读：
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。


2.不可重复读：
是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）
例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。


3.幻读:
是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象
发生了幻觉一样。
例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

 

 

8.MySql数据库索引：

  • 一、简介
  • MySQL目前主要有以下几种索引类型：
    1.普通索引
    2.唯一索引
    3.主键索引
    4.组合索引
    5.全文索引
  • 二、语句
  • CREATE TABLE table_name[col_name data type]
  • [unique|fulltext][index|key][index_name](col_name[length])[asc|desc]
  • 1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
    2.index和key为同义词，两者作用相同，用来指定创建索引
    3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
    4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
    5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
    6.asc或desc指定升序或降序的索引值存储
  • (三)使用索引的优点
  • 1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
    2.建立索引可以大大提高检索的数据,以及减少表的检索行数
    3.在表连接的连接条件 可以加速表与表直接的相连
    4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
    5.建立索引,在查询中使用索引 可以提高性能 
  • (四)使用索引的缺点
  • 1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
    2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
    3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。
  • (五)使用索引需要注意的地方
  • 在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的.
    一般来说,
    1.在经常需要搜索的列上,可以加快索引的速度
    2.主键列上可以确保列的唯一性
    3.在表与表的而连接条件上加上索引,可以加快连接查询的速度
    4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间,  (单独order by 用不了索引，索引考虑加where 或加limit)
    5.在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
  • 6.like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick '%ABC%' 那么这个索引讲不会起到作用.而nickname lick 'ABC%' 那么将可以用到索引
  • 7.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ' '字符串
  • 8.使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.
  • 9.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描
  • 10.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快
  • (六)什么情况下不创建索引
  • 1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
    2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
    3.定义为text和image和bit数据类型的列不应该增加索引,
    4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

 

 

9.MySql注入：

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

 

 

防止SQL注入，我们需要注意以下几个要点：

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。

2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。

5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装

6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

 

 

应对方法：

1.mysql_escape_string() 转义特殊字符（(PHP 4 >= 4.3.0, PHP 5)）(mysql_real_escape_string必须先链接上数据库，否则会报错)

下列字符受影响：

\x00 //对应于ascii字符的NULL

\n  //换行符且回到下一行的最前端

\r //换行符

\ //转义符

'

"

\x1a  //16进制数

如果成功，则该函数返回被转义的字符串。如果失败，则返回 false。

2.addslashes(): 函数返回在预定义字符之前添加反斜杠的字符串 (stripslashes()实现字符串还原)

预定义的字符有：

    单引号（'）

    双引号（"）

    反斜杠（\）

    NULL

3.prepared  statements(预处理机制)

 

 

 

posted @ 2019-03-20 15:17  久伴兔兔宝宝  阅读(252)  评论(0)  编辑  收藏  举报