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,

※查询乱码问题
set character_set_results=gb2312;

二: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安装

       #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连接

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

二: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管理数据库

  创建数据库

  # 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
“新密码”; 

三:Mysql连接

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

-D。–databases {name} 打开指定数据库

五:数据类型

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

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

    数值

   图片 1

   日期/时间

          data、time、year、datetime、timestamp

    图片 2

   字符串

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

 
 图片 3

  最大整数:bigint

  最大字符串:longtext

四: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 “新密码”; 

-H。 –host {name} 服务器名称

六:表的增、删、查、改

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;

五:数据类型

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

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

    数值

   图片 1

   日期/时间

          data、time、year、datetime、timestamp

    图片 2

   字符串

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

 
 图片 3

  最大整数:bigint

  最大字符串:longtext

-p. 密码

 七: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);

六:表的增、删、查、改

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;

-u. 用户名

八:union

    图片 7

    图片 8 

    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;

 七: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);

-v. 输出版本信息并退出

九:排序-order by

      SELECT * from runoob_tbl ORDER BY submission_date ASC;           

      SELECT * from runoob_tbl ORDER BY submission_date DESC;

八:union

    图片 7

    图片 8 

    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;

-exit 退出

十:分组-group by

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

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

      图片 11     

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

九:排序-order by

      SELECT * from runoob_tbl ORDER BY submission_date ASC;           

      SELECT * from runoob_tbl ORDER BY submission_date DESC;

二。修改MySQl提示符

十一: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;

十:分组-group by

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

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

      图片 11     

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

链接客户端参数指定

十二:Null空值

       mysql>
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;

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

十一: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;

mysql>mysql -uroot -proot -prmpt –{提示符}

十三:正则表达式

      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’**;

十二:Null空值

       mysql>
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;

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

\n 完整日期

十四:事务

       一般说来,事务满足四个条件(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 开启自动提交

十三:正则表达式

      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’**;

\d 当前数据库

十五: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;

十四:事务

       一般说来,事务满足四个条件(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 开启自动提交

\h 服务器名称

十六:索引

 

十五: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;

\u 当前用户

十七:临时表

        创建临时表:      

            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复制过来

十七:临时表

        创建临时表:      

            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;

SELECT VERSION(); 显示当前用户

十九:元数据

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

          

十八:复制表

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

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

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

SELECT NOW(); 显示当前时间

二十:序列使用

         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;

 

十九:元数据

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

          

SELECT USER(); 显示当前用户

二十一:处理重复数据

        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);

二十:序列使用

         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;

 

SHOW DATABASES; 显示数据库

二十二: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 ‘\n’ 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

 

二十一:处理重复数据

        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);

USER TABLENAME; 使用数据库

二十三: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:\mysql\bin\>mysql -h localhost -u root
//赋予任何主机访问数据的权限
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
//使修改生效
mysql>FLUSH PRIVILEGES
//退出MySQL服务器
mysql>EXIT

  

 

二十二: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 ‘\n’ 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安装 # yum install mysql #yum
install mysql – server #yum install mysql – devel 启动mysql # service
mysqld start 验证安装 #mysql –version 设置数据库…

SHOW TABLES; 数据库信息

SHOW WARNINGS; 显示错误信息

SHOW CREATE DATABASES TABLENAME; 显示创建表明信息

四。创建数据库

CREATE {DATABASES | SCHEMA} [IF NOT EXISTS]
db_name [DEFAULT] CHARACTER SET [=] charset_name

列:CREATE DATABASES TABLENAME;

列:[DEFAULT] CHARACTER SET [=] charset_name 使用编码方式

五。修改数据库

ALTER {DATABASES | SCHEMA} [db_name] [DEFAULT]
CHARACTER SET [=] CHARSET_NAME;

六。删除数据库

DROP {DATABASES | SCHEMA} [IF EXISTS] db_name

列:DROP TABLE db_name;

七.数据表

USER TABLENAME; 使用数据库

SELECT DATABASES(); 显示当前数据库

1.创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
)
列:
CREATE TABLE tb1(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(20),
age TINYINT UNSIGNED, //UNSIGNED 无符号位
salary FLOAT(8,2) UNSIGNED
PRIMARY KEY(id);
)

创建表添加声明主键 AUTO_INCREMENT 必须要 PRIMARY KEY(主键名)

2.查看数据表

SHOW TABLES [FROM db_name] [LIKE ‘PATTERN’ | WHERE EXPR]

3.查看表结构

SHOW COLUMNS FROM TABLENAME
columns

4.插入记录

INSERT [INTO] TABLENAME [(COL_NAME,…)] VALUES(val,…)

VALUES 插入所有列记录

INSERT TABLENAME(COL_NAME) VALUES (val); 单列记录

列: INSERT INTO runoob_tbl
->(runoob_title, runoob_author, submission_date)
->VALUES
->(“JAVA Tutorial”, “Sanjay”, ‘2007-05-06’);

DATE类型:2007-05-06 声明当前时间为 NOW();

5.查询记录

SELECT column_name,column_name FROM table_name [WHERE Clause]
[OFFSET M ][LIMIT N]

可以使用WHERE 语句包含条件。OFFSET指定SELECT语句开始查询数据偏移量。
LIMIT属性设置返回记录数
※查询乱码问题
set character_set_results=gb2312;
在查询

6.空值与非空

NULL 字段值可以为空

NOTNULL 字段禁止为空

CREATE TABLE tb1(
username VARCHAR(20) NOTNULL, //不许为空
age TINYINT UNSIGNED, //UNSIGNED 无符号位
salary FLOAT(8,2) UNSIGNED
)

八。数据类型

1.整型

TINYINT<SMALLINT<MEDIUMINT<INT<BIGINT

2.浮点型

FLOAT[(M,D)]<DOUBLE[(M,D)]

M是数字总位数,D是小数点后面的位数

3.日期时间型

YEAR 1 < TIME 3 < DATE 3 < DATETIME 8 < TIMESTAMP 4

4.字符型

九。MySQL WHERE语句

SELECT field1, field2,…fieldN FROM table_name1, table_name2…
[WHERE condition1 [AND [OR]] condition2…..

使用WHERE 可以使用AND 或者 OR 指定一个或多个条件
列:SELECT * from runoob_tbl WHERE runoob_author=’Sanjay’;

※:WHERE默认子句字符串是不区分大小写的,可以使用BINARY
关键字来设定WHERE子句的字符串比较是区分大小写的
列: SELECT * from runoob_tbl WHERE BINARY runoob_author=’sanjay’;

WHERE 子句也可以运行SQL DELETE 或者 UPDATE命令

WHERE 子句也可以类似于if条件

十。MySQL UPDATE 查询

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE
Clause]

可在WHERE指定条件:

列:mysql> UPDATE runoob_tbl
-> SET runoob_title=’Learning JAVA’
-> WHERE runoob_id=3;

十一。MySQL DELETE 语句

DELETE FROM TABLENAME WHERE ..;

如果没有指定WHERE MySQL表中所有记录被删除

列:mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;

十一。MySQL INSERT 语句
INSERT INTO table_name ( field1, field2,…fieldN )
VALUES ( value1, value2,…valueN );

十二。MySQL SELECT查询数据..

SELECT column_name,column_name FROM table_name
[WHERE Clause] [OFFSET M ][LIMIT N]

列名:column_name

※:如果数据是字符型必须使用单引号或者双引号 如:’张三’.

列:mysql> SELECT * FROM tcount_tbl
-> WHERE runoob_count IS NULL;
+—————–+—————-+
| runoob_author | runoob_count |
+—————–+—————-+
| mahnaz | NULL |
| Jen | NULL |
+—————–+—————-+

mysql> SELECT * from tcount_tbl
-> WHERE runoob_count IS NOT NULL;
+—————–+—————-+
| runoob_author | runoob_count |
+—————–+—————-+
| mahran | 20 |
| Gill | 20 |
+—————–+—————-+

列名:field1

十四。MySQL LIKE 子句

SELECT field1, field2,…fieldN table_name1, table_name2…
WHERE field1 LIKE condition1 [AND [OR]] filed2 = ‘somevalue’;

列:mysql> SELECT * from runoob_tbl WHERE runoob_author LIKE
‘%jay’;

十五。MYSQL排序

SELECT field1, field2,…fieldN table_name1, table_name2…
ORDER BY field1, [field2…] [ASC [DESC]]

※:ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。
默认情况下,它是按升序排列。

列:SELECT * from runoob_tbl ORDER BY runoob_author ASC;
d

十六。分组

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

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

列:SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+——–+———-+
| name | COUNT(*) |
+——–+———-+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+——–+———-+

列: SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP
BY name WITH ROLLUP;
+——–+————–+
| name | singin_count |
+——–+————–+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+——–+————–+

列:SELECT coalesce(name, ‘总数’), SUM(singin) as singin_count FROM
employee_tbl GROUP BY name WITH ROLLUP;
+————————–+————–+
| coalesce(name, ‘总数’) | singin_count |
+————————–+————–+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+————————–+————–+

十七.Mysql 连接的使用

JOIN 按照功能大致分为如下三类:
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
RIGHT JOIN(右连接): 与 LEFT JOIN
相反,用于获取右表所有记录,即使左表没有对应匹配的记录。

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;
+———–+—————+————–+
| runoob_id | runoob_author | runoob_count |
+———–+—————+————–+
| 1 | John Poul | 1 |
| 3 | Sanjay | 1 |
+———–+—————+————–+

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;
+————-+—————–+—————-+
| runoob_id | runoob_author | runoob_count |
+————-+—————–+—————-+
| 1 | John Poul | 1 |
| 2 | Abdul S | NULL |
| 3 | Sanjay | 1 |
+————-+—————–+—————-+

※:该语句会读取左边的数据表runoob_tbl的所有选取的字段数据,即便在右侧表tcount_tbl中没有对应的runoob_author字段值。

RIGHT JOIN列:
SELECT b.runoob_id, b.runoob_author, a.runoob_count FROM tcount_tbl
a RIGHT JOIN runoob_tbl b ON a.runoob_author = b.runoob_author;
+————-+—————–+—————-+
| runoob_id | runoob_author | runoob_count |
+————-+—————–+—————-+
| 1 | John Poul | 1 |
| 2 | Abdul S | NULL |
| 3 | Sanjay | 1 |
+————-+—————–+—————-+

※:该语句会读取右边的数据表 runoob_tbl
的所有选取的字段数据,即便在左侧表tcount_tbl中没有对应的runoob_author字段值。

十八。MYSQL 正则表达式

模式 描述
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性,^
也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性,$
也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
. 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’
在内的任何字符,请使用象 ‘[.\n]’ 的模式。
[…] 字符集合。匹配所包含的任意一个字符。例如, ‘[abc]’ 可以匹配
“plain” 中的 ‘a’。
[^…] 负值字符集合。匹配未包含的任意字符。例如, ‘[^abc]’ 可以匹配
“plain” 中的’p’。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如,’z|food’ 能匹配 “z” 或
“food”。'(z|f)ood’ 则匹配 “zood” 或 “food”。
* 匹配前面的子表达式零次或多次。例如,zo* 能匹配 “z” 以及 “zoo”。*
等价于{0,}。

  • 匹配前面的子表达式一次或多次。例如,’zo+’ 能匹配 “zo” 以及
    “zoo”,但不能匹配 “z”。+ 等价于 {1,}。
    {n} n 是一个非负整数。匹配确定的 n 次。例如,’o{2}’ 不能匹配 “Bob” 中的
    ‘o’,但是能匹配 “food” 中的两个 o。
    {n,m} m 和 n 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。

列:

查找name字段中以’st’为开头的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘^st’;

查找name字段中以’ok’为结尾的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘ok$’;

查找name字段中包含’mar’字符串的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘mar’;

查找name字段中以元音字符开头且以’ok’字符串结尾的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP
‘^[aeiou]|ok$’;

REGEXP正则表达式对象

十九。MYSQL事务

事务处理可以维护数据库完整性,保证成批SQL语句要么全部执行,要不全部不执行

数据库事务的四大特性(简称ACID)是:
(1) 原子性(Atomicity)
事务的原子性指的是,事务中包含的程序作为数据库的逻辑工作单位,它所做的对数据修改操作要么全部执行,要么完全不执行。这种特性称为原子性。
例如银行取款事务分为2个步骤(1)存折减款(2)提取现金。不可能存折减款,却没有提取现金。2个步骤必须同时完成或者都不完成。
(2)一致性(Consistency)
事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。这种特性称为事务的一致性。假如数据库的状态满足所有的完整性约束,就说该数据库是一致的。
例如完整性约束a+b=10,一个事务改变了a,那么b也应随之改变。
(3)分离性(亦称独立性Isolation)
分离性指并发的事务是相互隔离的。即一个事务内部的操作及正在操作的数据必须封锁起来,不被其它企图进行修改的事务看到。假如并发交叉执行的事务没有任何控制,操纵相同的共享对象的多个并发事务的执行可能引起异常情况。
(4)持久性(Durability)
持久性意味着当系统或介质发生故障时,确保已提交事务的更新不能丢失。即一旦一个事务提交,DBMS保证它对数据库中数据的改变应该是永久性的,即对已提交事务的更新能恢复。持久性通过数据库备份和恢复来保证。

开启一个事务
start transaction

做保存点
savapoint 保存点名称

二十。MYSQL ALTER命令

删除字段名
mysql> ALTER TABLE testalter_tbl DROP i;

添加字段名
mysql> ALTER TABLE testalter_tbl ADD i INT;

修改字段类型及名称:使用 MODIFY 或 CHANGE 子句 。
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

mysql> ALTER TABLE testalter_tbl
-> MODIFY j BIGINT NOT NULL DEFAULT 100;

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE
关键字之后,紧跟着的是你要修改的字段名,然后指定新字段名及类型。尝试如下实例:
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

修改字段默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

ALTER 命令及 DROP子句来删除字段的默认值
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

通过ALTER 命令以及TYPE子句修改表的类型
ALTER TABLE testalter_tbl TYPE = MYISAM;

通过ALTER 命令修改表名
ALTER TABLE testalter_tbl RNAME TO alt_name;

修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length))

二十一。MySQL 索引

虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。
因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

1.创建索引

这是最基本的索引,它没有任何限制。它有以下几种创建方式:

CREATE INDEX indexName ON mytable(username(length));

※:如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定
length。

2.修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

3.创建表的时候直接指定索引

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length))

);

4.删除索引的语法

DROP INDEX [indexName] ON mytable;

5.唯一索引

它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length))

);

6.使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引:

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。

ALTER TABLE tbl_name ADD INDEX index_name (column_list):
添加普通索引,索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
该语句指定了索引为 FULLTEXT ,用于全文索引。

以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

8.使用 ALTER 命令添加和删除主键

主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空(NOT
NULL)。

实例如下:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

9.ALTER 命令删除主键:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除指定时只需指定PRIMARY KEY,但在删除索引时,你必须知道索引名。

显示索引信息,可以通过添加 \G 来格式化输出信息。
mysql> SHOW INDEX FROM table_name\G

二十二。MySQL (temporary :临时的)临时表

创建临时表:

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);

删除临时表
mysql> SELECT * FROM SalesSummary;