mysql操作实例

Linux/UNIX 上安装 MySQL

安装前，我们可以检测系统是否自带安装 MySQL:

rpm -qa | grep mysql

如果你系统有安装，那可以选择进行卸载:

rpm -e mysql
rpm -e --nodeps mysql    //强力删除

安装 MySQL：

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivch mysql-community-release-el7-5.noarch.rpm
yum update
yum install mysql-server 

权限设置：

chown -R mysql:mysql /var/lib/mysql

初始化 MySQL：

mysqld --initialize

启动 MySQL：

systemctl start mysqld

查看 MySQL 运行状态：

systemctl status mysqld

验证 MySQL 安装

[root@host]# mysqladmin --version

root密码设置：

[root@host]#  mysqladmin -u root password "root"

//连接数据库
[root@host]# mysql -u root -p

基础操作

[root@host]# mysql -u root -p

[root@host]# mysqladmin -u root -p create RUNOOB
mysql> create DATABASE mall;
     
       drop datebase  mall
       use mall
     
     create table 'customer'(
      'c_id' int auto_increment,
       'c_name' vaechar(50) not null,
       primary key('c_id')
               )

         drop table customer
          
          insert into customer(c_id,c_name) values("1","小王");
          delete from customer where c_id=1;
          update customer set c_name='xiaowang'
where c_id=1;
          select * from customer where c_id =5；

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

在 where like 的条件查询中，SQL 提供了四种匹配方式。

1. %：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

2.  _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
   

3. []：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

4.   [^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
   

查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

MySQL UNION

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

##排序
SELECT * from runoob_tbl ORDER BY submission_date ASC;
##分组
SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
##连接
 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;
 NULL值
 SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
## 正则表达式
 查找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$';

##使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：
mysql> ALTER TABLE testalter_tbl  DROP i;

##表 testalter_tbl 中添加 i 字段，并定义数据类型:
mysql> ALTER TABLE testalter_tbl ADD i INT;

事务测试⭐

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

索引

在表中添加索引：
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

在 ALTER 命令中使用 DROP 子句来删除索引：
mysql> ALTER TABLE testalter_tbl DROP INDEX c;

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
mysql> SHOW INDEX FROM table_name\G；

临时表

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

复制表

第一、只复制表结构到新表
第二、复制表结构及数据到新表

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

CREATE TABLE newadmin AS
(
    SELECT username, password FROM admin
)

元数据

MySQL导出数据⭐

数据表 1_tbl 数据导出到 /tmp/1.txt 文件中:
mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';

#导出整个数据库的数据，可以使用以下命令：
$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******

#如果需要备份所有数据库，可以使用以下命令：
$ mysqldump -u root -p --all-databases > database_dump.txt
password ******


#导出整个数据库的数据，可以使用以下命令：
$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******

#如果需要备份所有数据库，可以使用以下命令：
$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

将数据表及数据库拷贝至其他主机

在源主机上执行以下命令，将数据备份到 dump.txt 文件中：

mysqldump -h other-host.com -P port -u root -p database_name > dump.txt
password ****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

MySQL导入数据⭐

1. MySQL命令导入
   将备份的整个数据库 runoob.sql 导入：

mysql -uroot -p123456 < runoob.sql

2. source 命令导入
   source 命令导入数据库需要先登录到数库终端：

mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

3. 使用 LOAD DATA 导入数据
   将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中：

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

4. 使用 mysqlimport 导入数据

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

$ mysqlimport -u root -p --local mytbl dump.txt
password *****