MySQL的安装&SQL语句操作

一、mysql客户端命令行工具

mysql      mysqladmin

【注】：客户端不接； 服务端需要接；

1.mysql

语法：mysql [-u username] [-h host] [-p password] [-P port] [-S socket_file] [-e 参数]

-e：交互式的CLI（字符交互界面）工具（不用登入mysql的sql界面，直接在字符终端交互输入相关语句进行操作）

-P:

-S: 套接字应用场景，运用在有多个端口（或者端口被隐藏）

-e:

2.mysqladmin

语法：mysqladmin [option] command [command option] command ......

常用选项：ping  status  password  refresh  shutdown  等

例如：mysqladmin -u root -p'Xxhf@2022' ping         

          mysqladmin -u用户名 -p旧密码 password 新密码

3.为用户设置密码三种方式

①SET PASSWORD FOR “USER@’HOST’=PASSWORD(‘XXXXXX’);    %匹配任意长度的任意字符

 ②update mysql.user SET password=PASSWORD(‘password’) WHERE clause; （需要重读授权表 8.0不支持此语句）

 ③mysqladmin -u用户名 -p旧密码 password 新密码（脚本）

4. 查看mysql的用户名和密码

5. MySQL元数据数据库--->mysql

查看mysql元数据库的所有表  use mysql      show tables;

6.MySQL的脚本模式数据恢复  

在mysql的sql接口进行导入脚本，可用于恢复mysql的数据信息，但脚本要以.sql为结尾

mysql > source 路径下的脚本名（相当于\. 路径下的脚本名）前提条件必须指定某个库 

二、MySQL的安装

MySQL的安装有多种方式，在Linux系统内，可以实现yum安装、二进制定制安装和源代码编译安装。

二进制安装

在centos-7上 安装mysql5.7或者mysql8.0需要源码编译，或者重新定制yum仓库。建议使用国内yum源。

vim /etc/yum.repos.d/mysql.repo

[mysql]

name=mysql5.7

baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/

gpgcheck=0

enabled=1

 yum安装 

启动mysql服务器

systemctl start mysqld

mysql5.7要求不能空密码登录，临时密码在/var/log/mysqld.log中

grep password /var/log/mysqld.log     然后使用临时密码登录进

临时密码登录后需要强行修改密码（大小写数字和特殊符号都要满足）

alter user root@'localhost' identified by 'Xxhf@2022';

mysql5.7之前的版本需要在生产环境进行安全加固

执行mysql自带的脚本    mysql_secure_installation

探测数据库是否可以连接使用

mysqladmin -u root -pxxxxxx ping

mycli

人性化的sql命令提示工具（在centos-7，需要安装python3.6以上的版本）

yum -y install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gdbm-devel db4-devel libpcap-devel xz-devel

yum install -y libffi-devel

yum install zlib* -y

yum -y install python3-pip

pip3 install mycli

 MySQL多实例：在生产环境，为节约成本，有可能实现多个数据库基于不同的端口工作在一台主机，此时我们需要多实例同时工作。

三、SQL语句具体操作

DDL：数据定义语言  （非数据层面的组件   库  表  视图  索引  存储过程  存储函数）

CREATE   DROP    ALTER   

DML：数据操作语言

INSERT   DELETE  UPDATE   SELECT

DCL：数据控制语言

GRANT,REVOKE

1.数据类型

字符：

变长字符 VARCHAR(#),VARBINARY(#)    

定长字符 CHAR(#),BINARY(#)      ‘zhangsan’     ZHANGSAN

内建类型 ENUM,SET(集合)   mysql独有的数据类型

 对象     TEXT,  TINYTEXT,  MEDIUMTEXT,  LOGTEXT,     （文本大对象类型）

BLOB，TINYBLOB，MEDIUMBLOB，LONGBLOB    （二进制大对象类型）

【注】：BINARY是区分大小写的，定长字符型最长长度为255，变长为65535；字符型插入或更新数据必须接引号（单引号或双引号都可以）

网址：https://dev.mysql.com/doc/refman/8.0/en/char.html 

数值：

精确数值：

1：整形  INT  (包含正数和负数)

              TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT

               2^8(1字节)       2^16(2字节)     2^24(3字节)     2^32(4字节)     2^64(8字节)

  2：十进制  DECIMAL(mysql独有类型)       decimal(8,3)       20000.535   200000.10

近似数值： 1：单精度：FLOAT     2：双精度：DOUBLE

字符类型修饰符： 

NOT NULL  非空约束

NULL

DEFAULT ‘’指定默认值  (此两试用任何)

CHARACTER SET ‘’ 使用的字符集，或者使用CHARSET ‘’

COLLATION：使用的排序规则

查看所有字符集

mysql > SHOW CHARACTER SET;   show charset;

查看默认排序规则

mysql > SHOW COLLATION;

整形数据修饰符

NOT NULL 非空约束

NULL

DEFAULT  NUMBER

PRIMARY KEY | UNIQUE KEY

UNSIGNED(无符号修饰,仅正数) （mysql中数值不能加引号，字符型要加引号，支持三种引号，’’,””,``.）

AUTO_INCREMENT(自动增长)

此两修饰数值属于mysql独有数据类型

内建类型（枚举和集合）修饰符

NULL

NOT NULL

DEFAULT        gender enum(‘M’,’F’) default ‘M’

日期时间型：（也需要加上引号）

    日期：DATE     年-月-日

    时间：TIME     时-分-秒

    日期和时间：DATETIME

    时间戳：TIMESTAMP

年份：YEAR(2),YEAR(4)    18     2018

2.DDL(数据定义语言)

CREATE  DROP  ALTER

上述三种语句针对的数据组件为：数据库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度器

使用HELP 命令可以查看上述三种语句的使用方法。例如  HELP CREATE；

（1）库级别

① 创建数据库

CREATE DATABASE [IF NOT EXISTS] ‘DB_NAME’[create_specification];

CHARACTER SET [=] charset_name;（指明字符集）

 COLLATE [=] collate_name;（指明字符集排序规则）

查看字符集

SHOW CHARACTER SET;

查看排序规则

SHOW COLLATION;

②修改数据库

ALTER DATABASE DB_NAME ......   一般用于修改字符集或排序规则

③删除数据库

DROP DATABASE [IF NOT EXISTS] ‘db_name’;

（2）表级别 

表的结构，是由二维关系构成（行、列）

定义表，可以在创建表的时候定义字段和索引（字段必须定义）

定义字段包括：字段名、字段数据类型、修饰符

定义约束包括：索引（应该定义在经常查询的字段上）

①创建表

CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1,col2 type2,.......);

col type1

        PRIMARY KEY(COL1,....)

        INDEX(col1,.....)

        UNIQUE KEY(col1,....)

 表选项：

 ENGINE [=] engine_name

ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

 SHOW ENGINES(单独命令，查看存储引擎类型）

查看表的结构（字段结构）：DESC tb_name

查看表的创建命令：SHOW CREATE TABLE tbl_name；

查看表的状态属性信息：

SHOW TABLE STATUS LIKE ‘tbl_name’\G;   注意：like后面的表名必须接引号

删除表（不可逆，谨慎操作）：DROP TABLE [IF EXISTS] ‘tbl_name’;

修改表 （谨慎）

修改表名：alter table tbl_name rename table_new_name

修改字段

包括添加ADD、修改、删除字段

字段：

添加字段：add

ALTER TABLE tbl_name ADD col1 data_type [FIRST|AFTER col_name]

删除字段:drop

ALTER TABLE tbl_name DORP col_name;

修改字段：

alter(删除字段默认值）change(改字段名称),modify(改字段的属性定义)

修改字段名  ALTER TABLE tbl_name change col_name col_name 字段定义   （如果是主键，不要写入主键）

（3）索引

 一种特殊数据结构，定义在查找时作为查找条件的字段。

优点：加速查询操作，如果没有索引，查询表时需要进行全表扫描。

缺点：会一定程度影响性能

定义索引时，须要定义在查找条件的字段上。索引只有添加和删除，没有修改。

创建表的同时可以直接创建索引，如果没有创建可以后续进行添加索引

添加索引：add

ALTER TABLE tbl_name ADD indexTYPE(col_name);

例：在xxhftable表上创建普通索引，索引名称为hehe，定义在zhuanye字段上

alter table xxhftable add index hehe (zhuanye);

删除索引：

ALTER TABLE tbl_name DROP index index_name;

查看表中的索引：

SHOW INDEXES FROM [db_name.] tbl_name;

3.DML语句（数据操作语言）

INSERT ,DELETE ,SELECT, UPDATE

INSERT语句（插入数据语句）

可以单行插入或多行插入。（如多行尽量一次执行插入）

INSERT [INTO] tbl_name [(clo_name,...)] {VALUES | VALUE} (val1,...),(...),....

INSERT [INTO] tbl_name SET clo_name1=val,clo_name2=val......   （仅仅局限于Mysql） 

SELECT语句（查询语句）

SELECT col1,col2,... FROM tbl_name [where clause] [ORDER BY ‘col_name’ [DESC]] [LIMIT [m,]n];

字段表示法：

*：所有字段

as：字段别名,col1 AS alias1

ORDER BY:排序查找（默认升序） 降序接DESC 

例：查询出students表中没有班级并且年龄最大的那个同学的数据信息。

select * from  students where classid is null order by age desc limit 1;

LIMIT：字符限制查找（只显示其中部分内容），可以偏移限制查找。)

扔掉两个，查第三个：

 clause条件表示：

字段后接比较式（除等于，其他仅局限于数值型）

>  =  <   >=   <=   !=

 条件逻辑操作

and

or

not

between ... and ...(相当于>=和<=之间）

 like:%:任意长度任意字符

       _：任意单个字符

rlike：基于正则表达式（用的少！）

SQL注入攻击案例：select * from user where name='admin' and password='' or '1'='1';

 DELETE语句（删除表中的数据）

一定要注意删除需要加条件限制

DELETE FROM tb_name [where clause] [ORDER by ‘col_name’ [DESC]] [LIMIT [m,]n]

UPDATE语句（修改表中的数据）

UPDATE tbl_name SET col1=new_cal1,col2=new_val2, [where clause] [ORDER by ‘col_name’ [DESC]] [LIMIT [m,]n]

例：修改xxhftable中的内容，将第三行数据的phonenum字段的数据更改为13000000000

update xxhftable set phonenum='13000000000' where xid=3;