Mysql_基本操作

Mysql

存在闭源风险，是最流行的关系型数据库管理系统。（RDBMS，Relational Dtabase Manager System），将数据保存在不同的表中。增加了访问速度及灵活性。中小型网站喜欢采用。

1.1 Mysql服务器安装及相关配置

1.1.1 LAMP架构

Linux apach mysql php

Centos7.0及以上使用MariaDB替代了MySQL

1.安装

yum -y install httpd mariadb-server mariadb php php-mysql
//httpd ----apache
//maridb----数据库客户端，mariadb-server
//php
//php-mysql---php与mysql的连接模块

2.启动

systemctl start mariadb && systemctl enable mariadb
systemctl start httpd && systemctl enable httpd
//查看是否启动3306及80端口
//-n            以数字形式显示地址和端口号。
//-a            显示所有连接和监听端口。
//-u/t          udp/tcp
//-p proto      显示 proto 指定的协议的连接
netstat -anput  //显示出来挺好看

3.配置mysql安全向导

mysql_secure_installation

除此之外，设置root密码可以用mysql管理工具mysqladmin

mysqladmin -u root password "123456"

远程登录到mysql

mysql -u root -p 123456 -h 192.168.1.63

4.检查apache运行

网页会存在于/var/www/html中

写一个index.php

开启80端口的防火墙方便访问

firewall-cmd --permanent --zone=public --add-port=80/tcp		//永久开启80端口
systemctl restart firewalld //重启防火墙生效

//最简便的方式就是防火墙全关了
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0#临时关闭

搭建完后可以iptables -F //清空防火墙

5.解压zip包,放于html目录下

unzip -d 要存放的地址 XXX.zip  //其中upload是相关的php文件
cp -r XX/upload/* /var/www/html

6.访问网站都是apache账户，所以给予apache权限

尤其是data必须要apache权限才可。

chown apache:apache /var/www/html/data

1.1.2 升级Mysql5.7版本

1.备份，停掉apache先

systemctl stop httpd   //停apache

mysqldump -uroot -p123456 -B ucenter > /tmp/ucenter_bak.sql

//-B会进行建库的操作

|.psc和.sql的区别

2.卸载mariadb

//rpm -qa mariadb* 查看mariadb安装的包
yum -y remove mariadb*

3.直接用包安装mysql5.7

正常情况应做第一步

下载mysql的repo源：

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

rpm -ivh XXXX.prm   //安装mysql5.7的repo源
yum clean all//更新yum源
yum list

//根据repo源开始安装mysql5.7
yum -y install mysql-community-server

//rpm -qa mysql* 查看安装的MySQL包

46min28s

可以直接进行文件传送

scp ucenter_back.sql 192.168.20.64:/root

4.启动mysql5.7版本

systemctl start mysqld //启动mysql，生成临时密码
grep "password" /var/log/mysqld.log //查看密码
mysql -u root -p'XXXXX'//临时密码需要加单引号

//登陆后需设置一个复杂的密码。或者改参数设置简单密码
set globalvalidate password policy=0
set global validate_password length=1;

5.导入的两种方式

//方法一使用sql
mysql -u root -p123456 < ucenter_back.sql
//方法二，进入mysql
mysql>source /root/ucenter.sql

1.2 Mysql语句

用于数据库查询和程序设计语言，用于存取数据一级查询、更新和管理关系数据库。同时也是数据库脚本文件的扩展名。

sql语句结构(6种)

DQL：data query language

DML:data Manipulation(操作) language:INSERT\UPDATE\DELETE

事物处理语言（TPL）：写存储过程和函数BEGIN\TRANSACTION\COMMIT\ROLLBACK

数据控制语言（DCL）：grant、revoke

数据定义语言（DDL）：create、drop

指针控制语言（CCL）：declare、cursor、fetch into、update where current

四个系统库的作用

information_schema:保存mysql服务器所有信息

performance_schema:Mysql5.5以上，记录服务器性能

mysql：保存账户信息，权限信息

sys:5.7以上保存元数据信息，数据库名表名，列的信息等

//无需进入sql数据库执行mysql命令
mysql -u root -p123456 -e "show databases"
//常用于shell脚本
mysql -u root -p123456 -e "select user,host from mysql.user"

设置字符格式为utf-8

//在 /etc/my.cnf中，增加
character_set_server=utf8

条件判断

//if exists
drop database if exists test1;
//if not exists
create database if not exists test;

1.2.1 创建

//创建数据库 
create database test;

//创建表
create table t1(id int(20),name varchar(40),age int);
//查看表的属性
desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

//删除
drop table/database 名

//更改表名，不可更改库名rename
alter table student rename teacher;

****************change改的更多，要给旧的名称***************
//更改字段名 change
alter table student change id ID int(20);

//更改表中属性长度modify
alter table student modify age int(10);

1.2.2 更改

*********************对字段操作***********************
//增加字段 add 在第一行first
alter table student add sex enum('M','F') first;
//增加地址字段在XX之后 after
alter table student add sex enum('M','F') after age;
//更改字段位置
alter table student modify sex enum('F','M') after age;
//删除表中字段
alter table student drop address;


*********************对数据操作***********************

//多行插入
mysql> insert into student values (1,1,'zs',21,'M'),(2,1,'zs',21,'M');

mysql> select * from student;
+------+------+----------+------+------+
| uid  | ID   | name     | age  | sex  |
+------+------+----------+------+------+
|    1 |    1 | zhangsan |   21 | M    |
+------+------+----------+------+------+


//删除数据
delete from student where age=21 and name='zs';
delete from student where age is null; //删除空的数据


//更新数据
update student set name='gg' where id =1

//去除重复后按条件查询  distinct
select distinct stname,age from students where name=‘zs’ and (age=25 or age=27);
//区分大小写查询（将字段的值转为二进制） binary
select * from student where binary name='Anu';
//升降序查询
select * from students order by id desc/asc

1.2.3 相关命令

help show 通过help来查看帮助

1.3 Mysql语句的进一步操作

1.3.1 数据类型

（3种）

数值类型、日期/时间和字符串类型。

INT、FLOAT

时间日期类型：

• DATA YYYY-MM-DD 日期值
• DATATIME YYYY-MM-DD HH:MM:SS 混合日期和时间值
• TIME HH:MM:SS
• YEAR YYYY
• TIMESTAMP YYYYMMDD HHMMSS

字符串类型

• CHAR 0-255 定长字符串
• VARCHAR 0-65535 可变长度
• TEXT 存文章 长文本数据

1.3.2 查询进阶

逻辑查询

//进行范围的查询
mysql> select * from books where price in (50,60,70);

mysql> select * from books where price between 30 and 60 order by price asc,bname desc ;

mysql> select * from books where price not between 30 and 60 order by price asc,bname desc ;

//此处&&替换为and也可以，不包含手尾 30 与 60
mysql> select * from books where price>=30 && price<=60  order by price asc,bname desc ;

打印当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-08-27 11:15:57 |
+---------------------+


mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 11:16:05  |
+-----------+

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.31    |
+-----------+


mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

show命令

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| ucenter            |
| ucenter_h          |
+--------------------+

//查询全局变量
show global variables；
//关于模糊查询%可以代表0个或多个匹配
//模糊查询包含有version的
show global variables like ‘%version%’；
//默认存储引擎
mysql> show global variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+

mysql> show global status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+

模糊查询

mysql> select * from student；
+------+--------+
| id   | stname |
+------+--------+
|    1 | a1     |
|    1 | b2     |
|    3 | c3     |
|    4 | b4     |
+------+--------+
mysql> select * from student where stname like '_1';
+------+--------+
| id   | stname |
+------+--------+
|    1 | a1     |
+------+--------+
mysql> select * from student where stname not like '%1';
+------+--------+
| id   | stname |
+------+--------+
|    1 | b2     |
|    3 | c3     |
|    4 | b4     |
+------+--------+

排序

mysql> select * from books where price  not in (50,60,70) order by price asc,bname desc ;

1.3.3 子查询

mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='网络技术');
+----------------------+---------+
| bName                | bTypeId |
+----------------------+---------+
| Internet操作技术     | 7       |
+----------------------+---------+

//多行子查询
select bName,price from books where price<all(select price from books where publishing="电子工业出版社" order by price asc limit 0,1);

1.3.4 limit 限定显示的条目

比如select * from table limit m,n语句
表示其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。

mysql> select * from category;
+---------+---------------+
| bTypeId | bTypeName     |
+---------+---------------+
|       1 | windows应用   |
|       2 | 网站          |
|       3 | 3D动画        |
|       4 | linux学习     |
|       5 | Delphi学习    |
|       6 | 黑客          |
|       7 | 网络技术      |
|       8 | 安全          |
|       9 | 平面          |
|      10 | AutoCAD技术   |
+---------+---------------+
10 rows in set (0.00 sec)

mysql> select * from category limit 2,3;
+---------+--------------+
| bTypeId | bTypeName    |
+---------+--------------+
|       3 | 3D动画       |
|       4 | linux学习    |
|       5 | Delphi学习   |
+---------+--------------+
3 rows in set (0.00 sec)


//常用于统计前三的数据
mysql> select bName,price from books order by price desc limit 0,3;
+----------------------------------------+-------+
| bName                                  | price |
+----------------------------------------+-------+
| Javascript与Jscript从入门到精通        |  7500 |
| XML 完全探索                           |   104 |
| ASP 3初级教程                          |   104 |
+----------------------------------------+-------+

1.3.5 连接查询

求两张表中符合条件的并集。

内连接：select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段

左连接： select 字段 from a表 left join b表 on 连接条件

右连接：select 字段 from a表 right join b表 on 条件

//内连接  查询a，b两表共三个字段，将books表给别名叫做a表，将category表给别名叫做b表。 连接条件于 类型id相等。
mysql> select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;

//外连接
1.左外连接   
解释：优先显示左表全部记录,此时左表主表，右表为从表
主表内容全都有，从表内没有的显示null。

select * from a_table a left join b_table b on a.a_id=b.b_id;//左表全部显示，右表中没有的就显示null


2. 右外连接
解释：优先显示右表全部记录,此时右表主表，左表为从表
主表内容全都有，从表内没有的显示null。

1.3.6 聚合函数

执行特定功能的代码块

//求和
mysql> select sum(price) from books;
+------------+
| sum(price) |
+------------+
|      10048 |
+------------+
1 row in set (0.05 sec)
//求平均值
mysql> select avg(price) from books;
+------------+
| avg(price) |
+------------+
|   228.3636 |
+------------+
1 row in set (0.00 sec)
//求数量
mysql> select count(*) from books where price<=60;
+----------+
| count(*) |
+----------+
|       28 |
+----------+
1 row in set (0.00 sec)


mysql> select count(distinct price) from books where price<=60;
+-----------------------+
| count(distinct price) |
+-----------------------+
|                    15 |
+-----------------------+
1 row in set (0.00 sec)

//字符串函数
//字符串的截取
mysql> select substr(btypename,1,7) from category where btypeid=10;
+-----------------------+
| substr(btypename,1,7) |
+-----------------------+
| AutoCAD               |
+-----------------------+
//字符串的拼接
mysql> select concat(bname,'--',bId) from books where bId=10;
+-----------------------------------+
| concat(bname,'--',bId)            |
+-----------------------------------+
| 3D MAX 3.0 创作效果百例--10       |

//大小写转换输出
mysql> select upper(bname) from books where bid=9;
+---------------------------+
| upper(bname)              |
+---------------------------+
| DREAMWEAVER 4网页制作     |
+---------------------------+
1 row in set (0.00 sec)

mysql> select lower(bname) from books where bid=9;
+---------------------------+
| lower(bname)              |
+---------------------------+
| dreamweaver 4网页制作     |
+---------------------------+

1.3.7 数据库的导出和导入

导入：

Linux中，对xuegod库执行book.sql脚本

mysql -u root -p xuegod < book.sql

Mysql中，执行book.sql脚本

use 库名
source ~/book.sql //绝对路径导入

导出：(不加-B的话就没有建库语句，仅有表和数据)

//最好一个一个库的导出
mysqldump -uroot -p123456 -B xuegod > /tmp/book.sql

//导出查询的数据，导出报错见遇到的问题
select * from books into outfile "/tmp/123.txt";

//将查询导出至外部文件中
select * from books where price in(60,70) into outfile '/tmp/book.txt';

1.3.X 改变某个字段的值

小于40的价格提高5元
mysql> update books set price=price+5 where price<40;

//一张表中两个字段互换，并且是在498号之后的
update books a, books b set a.f_name= b.f_num, a.f_num= b.f_name where a.id = b.id AND a.id>=498；
//两行中两个字段的值互换
update books a, books b set a.price=b.price,b.price=a.price where a.bId=1 and b.bId=2;

0x00解决数据库中遇到的一些问题：

凡是更改了my.cnf都需要重启mysql，然后再进行操作

systemctl restart mysqld

设置字符格式为utf-8

//在 /etc/my.cnf中，增加
character_set_server=utf8

show variable 无法执行

//出现 Table'performance_schema.session_variables' doesn't exist

//解决方法：登录MySQL数据库，然后执行命令： 
set global show_compatibility_56=on;

waring明文密码

//遇到mysqldump: [Warning] Using a password on the command line interface can be insecure.

export MYSQL_PWD=123456 //如此这般就不用明文输入密码了

导出外部文件需设置安全文件专用

//ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

需要在/etc/my.cnf中加入
secure-file-priv="/"