Mysql基础

一、数据库的基本概念：

1、数据库发展史

Sybase-->Microsoft(SQL Server)

Informix-->被IBM收购(DB2)

Oracle-->

MySql-->MariaDB

2、DBA工作内容：

(1)、开发：

A、数据库设计

B、代码设计：存储过程、存储函数、触发器

(2)、管理：

A、连接管理以及优化

B、备份和还原

C、数据库设计

D、基本语句优化

E、用户以及权限管理

F、安全管理

G、数据库软件安装及升级

H、配置优化

2、事务（ACID）：事务是多个操作组合的一件事情，保证数据要么都完成，要么都失败。

（1）、A：原子性-->不可分割，要么都执行，要么都不执行。

（2）、C：一致性-->从一个一致性状态，到另一个一致性状态。

      比如： A账户有5000元，B账户有5000元，一共10000元。

          如果A转账2000给给B账户，那么A账户为3000元，B账户为7000元，

         那么一共为10000元。总之数据是不会损失，总数是不会变的。

（3）、I：隔离性-->一个事务在提交前所有的修改对其他人来讲是不可见的。

        还是上面的转账例子:A账户-2000,B账户+2000,在事务提交前，其他人是看不到A、B

      两账户变化的即其他人在查询A、B账户时还都是5000元。

（4）、D：持久性-->一旦事务得到提交，其所做的修改会永久有效。

3、事务的隔离级别：

（1）、读未提交：read-uncommited，能读到别人事务尚未提交的修改，叫读未提交。

    如果事务级别为“读未提交”，那么第一个用户执行

   mysql>start transaction;

   mysql>delete from students where stuID=1;

   此时第一个用户事务不提交，那么第二个用户执行

    mysql>start transaction;

   mysql>select * from students，则查询结果已看不到stuID=1的数据。

   此种情形就是“读未提交”的“脏读”问题。

   此时第一个用户事务回滚，那么第二个用户执行               

   mysql>select * from students，则查询结果又能看到stuID=1的数据。            此种情形就是“读未提交”的“不可重复读（两次读取的结果不一样）”问题。 

   另外还有一个“幻读问题（就是两次结果不一样，和产生幻觉是的）”。    

（2）、读提交：read-commited，解决了“脏读”问题。

    如果事务级别为“读提交”，那么第一个用户执行

   mysql>start transaction;

   mysql>delete from students where stuID=1;

    此时第一个用户事务不提交，那么第二个用户执行

    mysql>start transaction;

   mysql>select * from students，则查询结果能看到stuID=1的数据。

   此时第一个用户事务提交，那么第二个用户执行               

   mysql>select * from students，则查询结果不能看到stuID=1数据。            

     此种情形就是“读提交”的“不可重复读（两次读取的结果不一样）”问题。

   另外还有一个“幻读问题（就是两次结果不一样，和产生幻觉是的）”。   

（3）、可重读：repeatable-read，解决了“脏读、不可重复读”问题，但还有幻读问题。

    如果事务级别为“读提交”，那么第一个用户执行

   mysql>start transaction;

   mysql>delete from students where stuID=1;

    此时第一个用户事务不提交，那么第二个用户执行

    mysql>start transaction;

   mysql>select * from students，则查询结果能看到stuID=1的数据。

   此时第一个用户事务提交，那么第二个用户执行

   mysql>select * from students，则查询结果还能看到stuID=1的数据。

   只有第二个用户执行commit或者rollback后，在执行如下查询：

    mysql>select * from students，则查询结果不能看到stuID=1的数据。

    可见还有幻读问题。

（4）、串行化：serializable，强制事务的串行执行解决了“幻读问题”，但效率极差，因只有一个事务完成，另一个事务才开始执行，否则就一直等待，极少用，甚至可以说不用。

注意:

A、自上而下，级别越来越高。MySql的默认隔离级别为“可重读”。

B、MySql常用的存储引擎MyISAM和InnoDB；注意MyISAM不支持事务，InnoDB支持事务。

C、查看MySQL的默认事务隔离级别方法：

mysql>show global variables like 'tx_isolation';

+---------------+-----------------+

| Variable_name | Value       |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

D、查看MySQL事务自动提交是否开启，MySql默认是自动提交事务，即默认情况下如果没有显示启动事务，每个语句都当作一个独立的事务，其执行完成后会被自动提交。

mysql> show global variables like 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit   | ON   |

+---------------+-------+

E、MySQL是通过多版本并发控制实现事务的，即每个事务启动时，InnoDB会为每个启动的事务提供一个当下时刻的快照。

F、推荐使用“读提交”和“可重读”两种中的一种，MySQL默认隔离级别是“可重读”。

4、SQL(Struct Query Language)：

（1）、DCL（Data Control Language）：

Grant/Revoke

（2）、DDL（Data Defination Language）：

Create/Alter/Drop

（3）、DML（Data Manipulation Language）：

Insert/Delete/Select/Update

5、数据库结构示意图：

6、学习MySQL的内容概述：

（1）、MySql基本知识

（2）、Mysql事务、隔离、并发控制、锁

（3）、用户和权限

（4）、监控

（5）、索引：优化sql

（6）、备份和恢复

（7）、复制功能

（8）、集群

7、MySQL 版本（Community/Enterprise）:

 （1）、Community社区版

 （2）、Enterprise企业版

二、Linux上安装MySql：

1、安装mysql的方式：

（1）、操作系统自带的rpm包

（2）、Mysql官方提供的rpm包

（3）、通用的二进制格式(类似于绿色软件，解压即可使用)

（4）、源码编译安装

2、通用的二进制格式安装Mysql：

（1）、tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local

（2）、ln -sv mysql-5.5.33-linux2.6-x86_64 mysql 

（3）、groupadd -r mysql

       useradd -g mysql -r -s /sbin/nologin mysql

       cd /usr/local/mysql

       chown -R mysql.mysql ./*

（4）、mkdir /data/mydata 

       chown -R mysql.mysql /data/mydata

       

（5）、MySql初始化：

      /usr/local/mysql/scripts/mysql_install_db --datadir=/data/mydata --user=mysql

（6）、初始化完成后，要将/usr/local/mysql/下的组改为root

    cd /usr/local/mysql/

    chown -R root ./*

（7）、mysql配置文件：

cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf

因在安装时，datadir进行了调整，所以/etc/my.cnf的[mysqld]段要加上=/data/mydata

说明：/usr/local/mysql/support-files/下可用的mysql配置文件有很多，

如my-large.cnf 、my-innodb-heavy-4G.cnf、my-medium.cnf、my-small.cnf

（8）、配置mysql服务：

       cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld

       chkconfig --add mysqld：添加到服务中

       chkconfig --list mysqld：查看服务

（9）、启动mysql：service mysqld start

三、linux上mySql的客户端：

1、安装：

（1）、vim /etc/profile.d/mysql.sh

   export PATH=/usr/local/mysql/bin:$PATH

   . /etc/profile.d/mysql.sh

（2）、命令行执行mysql即可。

（3）、处理root用户和匿名用户：

A、删除匿名用户：

mysql> drop user ''@'localhost'或者 delete from  user where user='' and host='localhost';

B、给所有的root用户设置密码：

   a、第一种方式:

mysql> SET PASSWORD FOR username@host =PASSWORD('your_password');

mysql> FLUSH PRIVILEGES;

   b、第二种方式:

    mysql> update user set password =PASSWORD('your_password') where user='root';

mysql> FLUSH PRIVILEGES;

2、mysql客户端的使用：

(1)、-u UserName或者--user=UserName，用户省略表示管理员

(2)、-p 或者--password=，密码省略表示空密码

(3)、-h hostName或者--host=hostName，主机省略表示本机。

四、MySql的存储引擎：

1、mysql的存储引擎是插件化的存储引擎。

2、MySql常用的存储引擎MyISAM和InnoDB：

MyISAM：不支持事务，非聚集索引（索引文件和数据文件不在一起，这样查找相对速度较慢）；

InnoDB：支持事务，聚集索引（索引文件和数据文件在一起，这样查找速度快）。

五、MySql管理：

1、Mysql的基本命令：

（1)、显示所有数据库命令：

   show databases;

（2）、设置默认数据库：

   use 数据库名，如use mysql;

（3）、显示某库下的所有表：

   show tables;

（4）、查看某表的表结构：desc 表名，如desc user;

（5）、为了安全，一般要删除表user中的匿名用户，删除的命令为：

   drop user 用户账号，如drop user ''@'localhsot'或者delete from user where username= ''

（6）、查看当前打开的数据库：select database();

（7）、查看当前登录的用户：select user();

（8）、查询语句中几个重要的参数：

    A、\G：是结果集不在一行展示。 

    B、\c：取消当前sql的语句的执行。

（9）、执行sql脚本即批处理：

     mysql -uroot -p密码<a.sql，其中a.sql的内容如下：

     show databases;

     select user();

（10）、显示创建表的语句：show create table 表名;

如show create table students;

（11）、显示mysql支持的存储引擎(注：存储引擎是表类型) show engines;

结果如下：Transactions：事务， XA ：分布式事务。

+--------------------+---------+------------------------------------------

| Engine         | Support |  Transactions | XA   | Savepoints|

+--------------------+---------+------------------------------------------

| MyISAM         | YES   |  NO           | NO   | NO     |

| CSV           | YES   |  NO           | NO   | NO     |

| MRG_MYISAM      | YES   |  NO           | NO   | NO     |

| BLACKHOLE       | YES   |  NO           | NO   | NO     |

| MEMORY         | YES   |  NO           | NO   | NO     |

| FEDERATED       | NO   |    NULL         | NULL | NULL    |

| ARCHIVE        | YES   |  NO           | NO   | NO     |

| PERFORMANCE_SCHEMA | YES   |  NO           | NO   | NO     |

| InnoDB         | DEFAULT|  YES          | YES  | YES    |

+--------------------+-----------+-----------------------------------------

（12）、显示mysql表状态数据：

A、查看所有表：show  table status;

B、查看某个表：show  table status like '表名';

如show  table status like 'students'\G;

*************************** 1. row ***************************

           Name: students

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 6

 Avg_row_length: 2730

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 10485760

 Auto_increment: 7

    Create_time: 2015-08-24 01:28:58

    Update_time: NULL

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options: 

        Comment: 

2、MySql账户管理：

（1）、用户账号=用户名@主机名

（2）、创建用户账号

（3）、修改用户账号密码：

   set password for 'root'@'localhost.localdomain'=password('123456');

六、约束、键、字段类型、字段类型的修饰符、创建/删除表、增删改查、sql条件：

1、约束constraint：

（1）、主键约束：

惟一，不能为NULL，对于一张表来说主键有且只能有一个。

（2）、外键约束：

       

（3）、惟一键约束：

惟一，可以为NULL，对于某个惟一键来说值不能重复出现，对于一张表来说惟一键可以有多个。

（4）、检查式约束：

用户自定义有效取值范围，如：人的年龄不能大于200

（5）、非空约束：

not null

2、键key：

（1）、主键：能够惟一标识表中每一个记录的字段或者字段的组合。

（2）、外键：

（3）、惟一键

3、字段类型：

（1）、字符型:

char(#)

varchar(#)

text

blob

（2）、数值型

精确数值型：

int

  tinyint

  smallint

  mediumint

  int

  biginit

decimal（常用于金融系统中）

近似数值型

float

double

（3）、日期型

date

time

datetime

timestamp:自1970年到现在的秒数。

（4）、布尔型(mysql无布尔型):

（5）、NULL

（6）、内置类型：

A、ENUM，枚举，如定义性别 ENUM('F','M')，只能是其中一个。

B、SET，集合，只能是集合内一个或几个值的组合。

4、字段或字段类型的修饰符：

（1）、not null:不能为空

（2）、null:可以为空

（3）、unsigned:无符号类型，即>1

（4）、default:默认值

（5）、auto_increment:自动增长，自动增长类型的字段必须为主键或者惟一键。

（6）、primary key：标记某字段为主键

（7）、unique key：惟一键

5、创建表：

create table tbl_name (col1_name col1_type,col2_name col2_type...);

如：create table students (sid bigint,sname varchar(30),sex enum('F','M'));

如：create table students (sid bigint auto_increment primary key,sname varchar(30) not null,age tinyint unsigned not null,sex enum('F','M') default 'M');

6、删除表：

drop table table_name;

如：drop table students;

7、增删改查：

（1）、增：insert into tbl_name (col1,col2...) values (val1,val2...)

      insert into tbl_name select 语句。

（2）、删：delete from tbl_name where col1=条件1 and col2=条件2

（3）、改：update tbl_name set col1=val1，col2=val2 where col1=条件1 and col2=条件2

（4）、查：select col1,col2... from tbl_name where col1=条件1 and col2=条件2

8、sql条件：

(1)、比较条件：=、>=、<=、>、< 、<>

(2)、组合条件：and 、or、not

(3)、模糊条件:like ,其中%匹配任意长度任意字符，_:匹配任意单个字符。