MySQL

一：Mysql安装

       #yum install mysql

　　#yum install mysql-server

　　#yum install mysql-devel

    启动mysql

　　#service mysqld start

　　验证安装

      #mysql --version

　　设置数据库登录密码

　　#mysqladmin -u root password "new_password";

　　密码登录

　　# mysql -u root -p　

    　Enter password:*******

二：Mysql管理

　　添加用户　　 
　　# mysql -u root -p
　　mysql> use mysql;
　　mysql> INSERT INTO user (host, user, password,select_priv, insert_priv, update_priv)VALUES ('localhost', 'guest',PASSWORD('guest123'), 'Y', 'Y', 'Y');
　　mysql> FLUSH PRIVILEGES;
　　注意：PASSWORD() 函数来对密码进行加密。 你可以在以上实例看到用户密码加密后为： 6f8c114b58f2ce9e.
　　注意：在注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。如果你不使用该命令，你就无法使用新创建的用户来连接mysql服务器，除非你重启mysql服务器。
　　
　　配置文件
　　mysql配置文件在/etc/my.cnf 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

　管理mysql命令

mysql> use RUNOOB;

mysql> SHOW DATABASES;

mysql> SHOW TABLES;

      root密码重置

#vim /etc/my.cnf

       在[mysqld]下添加skip-grant-tables，然后保存并退出

　   重启mysql服务：service mysqld restart

三：Mysql连接

连接
# mysql -u root -p
退出
mysql> exit

四：Mysql管理数据库

　　创建数据库

　　# mysqladmin -u root -p create RUNOOB

　　删除数据库

　　#mysqladmin -u root -p drop RUNOOB

　　备份数据库

　　#mysqldump -uroot -p -h127.0.0.1 --opt mysql  > mysql.sql

　　修改数据库密码

　　mysqladmin -u root password "新密码"; 

五：数据类型

　　大致可以分为三类：数值、日期/时间和字符串(字符)类型。

        tinyint、smallint、mediumint、int、bigint、float、double、decimal

　　  数值

 　　

　　　日期/时间

          data、time、year、datetime、timestamp

　　  

　　 字符串

　　 char、varchar、tinyblob、tinytext、blob、text、mediumblob、mediumtext、longblob、longtext

　 　

　　最大整数：bigint

　　最大字符串：longtext

六：表的增、删、查、改

6.1创建

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table yy5(id int,name varchar(20));

  解析：

　　　　　　如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。

　　　　　　AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。

　　　　　　PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

　　　　　　ENGINE 设置存储引擎，CHARSET 设置编码。

6.2删除

　　　　　　DROP TABLE test;

    　　　　   DELETE FROM runoob_tbl WHERE runoob_id=3;

6.3查询

      　　　　select * from runoob_tbl;

6.4修改

　　　　　增加：

                 　INSERT INTO runoob_tbl
　　　　　　(runoob_title, runoob_author, submission_date)
　　　　　　VALUES
　　　　　　("学习mysql","mysql",NOW());

                  修改：

      　　　　 UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

 七：LIKE 子句

　　　　　7.1查询user表中姓名中包含“王”字的：

　　　　　　　　select * from user where name like '%王%';

　　　　　7.2查询user表中姓名中没有“王”字的：

　　　　　　　　select * from user where name not like '%王%';

　　　　　7.3查询user中姓名以“王”开头的：

　　　　　　　　select * from user where name not like '王%';

                  7.4查询user中姓名以“王”结尾的：

　　　　　　　　select * from user where name not like '%王';

　　　　　7.5查询user表中地址在上海姓名中没有“王”字和所有姓名为空的：

　　　　　　　　select * from user where adress =‘上海’ and name not like '%王%' or name is null;

　　　　　7.6查询user表中地址在上海姓名中没有“王”字和地址在上海姓名为空的：

　　　　　　　　select * from user where adress =‘上海’ and (name not like '%王%' or name is null);

八：union

　　　　

　　　　 

　　　　8.1求两个table某列的交集

　　　　　　SELECT country FROM Websites
　　　　　　UNION
　　　　　　SELECT country FROM apps
　　　　　　ORDER BY country;

　　　　8.2求两个table某列的并集

　　　　　　SELECT country FROM Websites
　　　　　　UNION ALL
　　　　　　SELECT country FROM apps
　　　　　　ORDER BY country;

九：排序-order by

　　　　　　SELECT * from runoob_tbl ORDER BY submission_date ASC;　　　　　　　　　　　

　　　　　　SELECT * from runoob_tbl ORDER BY submission_date DESC;

十：分组-group by

　　　　　　GROUP BY 语句根据一个或多个列对结果集进行分组。

　　　　　　在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

　　　　　　　　　　　

　　　　　　mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
　　　　　　+--------+----------+
　　　　　　| name   | COUNT(*) |
　　　　　　+--------+----------+
　　　　　　| 小丽    |        1 |
　　　　　　| 小明    |        3 |
　　　　　　| 小王    |        2 |
　　　　　　+--------+----------+
　　　　　　3 rows in set (0.01 sec)

十一:INNER JOIN

　　　　Mysql和JOIN来进行多连接

　　　　JOIN 按照功能大致分为如下三类：

　　　　　　INNER JION:内连接或者等值连接

　　　　　　LEFT  JION:左连接

　　　　　　RIGHT JION:右连接

　　　　1： INNER JOIN 

                      SELECT

　　　　　　 a.runoob_id, a.runoob_author, b.runoob_count

　　　　　　 FROM

　　　　　　 runoob_tbl a INNER JOIN tcount_tbl b

　　　　　　 ON

　　　　　 　a.runoob_author = b.runoob_author;

　　　　　　 等价于-------------------------------------------------------

　　　　　　 SELECT

　　　　　　  a.runoob_id, a.runoob_author, b.runoob_count

　　　　　  　FROM

　　　　　 　 runoob_tbl a, tcount_tbl b

　　　　　　  WHERE

　　　　　 　 a.runoob_author = b.runoob_author;

　　　　2： LEFT JOIN

　　　　　　SELECT 
　　　　　　a.runoob_id, a.runoob_author, b.runoob_count　　
　　　　　　FROM　　　　　　
　　　　　　runoob_tbl a　LEFT JOIN tcount_tbl b 
　　　　　　ON 
　　　　　　a.runoob_author = b.runoob_author;

十二：Null空值

 　　　　　　mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;

　　　　　　 mysql> SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;

十三：正则表达式

　　　　　　MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

 　　　　　　1：找出字符串中以"st"开头的字符串

　　　　　　　　mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

　　　　　　 2：找出字符串中以“st”结束的字符串

　　　　　　　　mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

　　　　　　 3：找出字符串中包含'mar'的字符串

　　　　　　　　mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

十四：事务

 　　　　　　一般说来，事务满足四个条件（ACID）:Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

　　　　　　　　1：事务的原子性：一组事务要么成功，要么失败。

　　　　　　　　2：稳定性：有非法数据（外键约束），事务撤回。

　　　　　　　　3：隔离性：事务独立运行，独立撤回。

          　　　　　4：可靠性：软硬件崩溃后，InnoDB回依靠日志问价进行重构。

　　　　　　事务控制语句

　　　　　　　　1：开启事务：begin或者start transaction

　　　　　　　　2：提交事务：commit或者commit work，但二者不等价，commit会对数据库进行彻底修改。

　　　　　　　　3：rollback：回滚用户提交的数据，并进行停止正在进行的

　　　　　　　　4：SAVEPOINT identifier：事务保存点，一个事务可以拥有多个保存点，

　　　　　　　　5：RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

　　　　　　　　6：ROLLBACK TO identifier：把事务回滚到标记点；

　　　　　　　　7：SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

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

　　　　　　　　1、用 BEGIN, ROLLBACK, COMMIT来实现

　　　　　　　　　　BEGIN 开始一个事务

　　　　　　　　　　ROLLBACK 事务回滚

　　　　　　　　　　COMMIT 事务确认

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

　　　　　　　　　　SET AUTOCOMMIT=0 禁止自动提交

　　　　　　　　　　SET AUTOCOMMIT=1 开启自动提交

十五：ALTER命令

 　　　　　　1：ALTER TABLE 语句插入删除

　　　　　　　　删除:ALTER TABLE testalter_tbl DROP i;

 　　　　　　　  增加: ALTER TABLE testalter_tbl ADD i INT;

　　　　　　　　修改字段类型：

　　　　　　　　　　 modify：ALTER TABLE testalter_tbl MODIFY c CHAR(10);    修改c的类型为char（10）

　　　　　　　　　　 change：ALTER TABLE testalter_tbl CHANGE i j BIGINT;

　　　　　　　　修改字段默认值：

　　　　　　　　　 　ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

　　　　　　　　删除字段默认值：

　　　　　　　　　　ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

　　　　　　　　修改表名：

　　　　　　　　　　alert table testalert_tbl rename to alter_test;

十六：索引

十七：临时表

　　　　　　　　创建临时表：　　　　　　

　　　　　　　　　　　　CREATE TEMPORARY TABLE SalesSummary (
　　　　　　　　　　　　　　product_name VARCHAR(50) NOT NULL
　　　　　　　　　　　　　　, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
　　　　　　　　　　　　　　, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
　　　　　　　　　　　　　　, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
　　　　　　　　　　　　);

　　　　　　　　修改临时表：　　　　　　　　　　　

　　　　　　　　　　　　INSERT INTO SalesSummary
　　　　　　　　　　　　　　(product_name, total_sales, avg_unit_price, total_units_sold)
　　　　　　　　　　　　　　VALUES
　　　　　　　　　　　　　　('cucumber', 100.25, 90, 2);

　　　　　　　　查询临时表：　　　　　　　

　　　　　　　　　　　　SELECT * FROM SalesSummary;
　　　　　　　　删除临时表：
　　　　　　　　　　　　DROP TABLE SalesSummary;

十八：复制表

 　　　　　　　　1:执行SHOW CREATE TABLE runoob_tbl G;得到创建runoob_tbl 的语句

　　　　　　　 　2:执行创建runoob_tbl 的语句，并修改表名

　　　　　　　　 3：将原表中的数据select复制过来

十九：元数据

　　　　　　　　SELECT VERSION( )           服务器版本信息
　　　　　　　　SELECT DATABASE( )        当前数据库名 (或者返回空)
　　　　　　　　SELECT USER( )                 当前用户名
　　　　　　　　SHOW STATUS 　　　　    服务器状态
　　　　　　　　SHOW VARIABLES             服务器配置变量

　　　　　　　　　　

二十：序列使用

 　　　　　　　 1：auto_increment自增

　　　　　　　　 　 id INT  AUTO_INCREMENT,

　　　　　　　　2:重置序列

　　　　　　　　　　如果删除了自增的id部分值，则剩下的id不是连续的，怎样设置为连续　　　　　　　　　　

　　　　　　　　　　mysql> ALTER TABLE insect DROP id;    
　　　　　　　　　　mysql> ALTER TABLE insect
  　　　　　　　　　　  -> ADD id INT AUTO_INCREMENT FIRST,
　　　　　　　　　　    -> ADD PRIMARY KEY (id);
　　　　　　　3：设置ip的起始值
　　　　　　　　　　ALTER TABLE t AUTO_INCREMENT = 100;

二十一：处理重复数据

 　　　　　　　1：防止重复数据的方法有设置主键和唯一索引

　　　　　　　　   设置双主键　　　　　　　　　

　　　　　　　　 CREATE TABLE person_tbl
　　　　　　　　　 (
 　　　　　　　　  first_name CHAR(20) NOT NULL,
 　　　　　　　　  last_name CHAR(20) NOT NULL,
　　　　　　　　   sex CHAR(10),
　　　　　　　　   PRIMARY KEY (last_name, first_name)
　　　　　　　　);
　　　　　　　   唯一索引

　　　　　　　 　CREATE TABLE person_tbl
　　　　　　　 　(
　　　　　　    first_name CHAR(20) NOT NULL,
　　　　　　    last_name CHAR(20) NOT NULL,
　　　　　　    sex CHAR(10)
　　　　　 　   UNIQUE (last_name, first_name)
　　　　　　 　　);

           2：统计重复数据

　　　　　　　　  SELECT COUNT(*) as repetitions, last_name,fist_name
　　　　　　　  　from person_tbl
　　　　　　　  　GROUP BY lst_name, fist_name
　　　　　　　  　HAVING repetitons >1;

　　　　　　　3：过滤重复数据

　　　　　　　　 方法1;distinct

　　　　　　　　  select distinct last_name, fist_name from person_tbl;

　　　　　　　　方法2：grop by

　　　　　　　　　select last_name, fist_name from person_tbl  GROUP BY (last_name, first_name);

二十二：MySQL导入出数据

 　　　　　　 1：select into outfile语句

　　　　　　　　select * from user_img into outfile "/tmp/mysqluser.txt"；

　　　　　　　　会出现权限报错：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

　　　　　　　　因为导出只能导出到规定文件中

　　　　　　　　mysql> show global variables like '%secure%';

　　　　　　　　进行查看：

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

　　　　　　　　| Variable_name  | Value                       | 
　　　　　　　　+------------------- -+---------------------------+
　　　　　　　　| secure_auth      | ON                           |
　　　　　　　　| secure_file_priv | /var/lib/mysql-files/- |
　　　　　　　　+---------------------+---------------------------+

　　　　　　　　可知我本地的文件只能复制到/var/lib/mysql-files/目录下　　　　　　

　　　　　　　　mysql> select * from user_img into outfile "/var/lib/mysql-files/user_img.txt";
　　　　　　　　　　　Query OK, 0 rows affected (0.11 sec)

　　　　　　　　导出为csv格式

　　　　　　　　mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files/user_1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ' ' FROM user;
　　　　　　　　　　　　Query OK, 2 rows affected (0.00 sec)

　　　　　　　　mysqldump导出数据做备份

　　　　　　　　#mysqldump -uroot -p -h127.0.0.1 --opt xserver > xserver.sql

　　　　　　　　将备份的数据导入到数据库

　　　　　　　　mysql -u用户名 -p -h127.0.0.1 --default-character-set=utf8 数据库 < 需要导入的数据库文件.sql

二十三：Mysql初始化root密码和允许远程访问

　　mysql默认root用户没有密码，输入mysql –u root 进入mysql

　　1、初始化root密码

　　进入mysql数据库

mysql>use mysql;
mysql>update user set password=PASSWORD('123456') where User='root';

　　 

　　2、允许mysql远程访问,可以使用以下三种方式:

　　a、改表。

mysql -u root –p
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;　

　　b、授权。

例如，你想root使用123456从任何主机连接到mysql服务器。

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

　　

如果你想允许用户jack从ip为10.10.50.127的主机连接到mysql服务器，并使用654321作为密码

mysql>GRANT ALL PRIVILEGES ON *.* TO 'jack'@’10.10.50.127’ IDENTIFIED BY '654321' WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;

　　

c:在安装mysql的机器上运行：

//进入MySQL服务器
d:mysqlin>mysql -h localhost -u root
//赋予任何主机访问数据的权限
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
//使修改生效
mysql>FLUSH PRIVILEGES
//退出MySQL服务器
mysql>EXIT