MySQL之三---MySQL数据库的二进制安装、源码编译和基础入门操作

一、MySQL安装

（1）安装方式：

1 、程序包yum安装

　　优点：安装快，简单

　　缺点：定死了各个文件的地方，需要修改里边的相关配置文件，很麻烦

2 、二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用（推荐）

3 、源代码：编译安装，太麻烦

（2）mysql 的程序组成：

Client:

　　mysql ：CLI 交互式客户端程序

　　mysqldump, mysqladmin...

Server:

　　mysqld_safe mysql的备份

　　mysqld

　　mysqld_multi ：多实例

服务器监听的两种socket 地址：

　　ip socket: 监听在tcp 的3306 端口，支持远程通信

　　unix sock: 监听在sock 文件上(/tmp/mysql.sock,/var/lib/mysql/mysql.sock) ，仅支持本机通信

实验一：yum源安装MySQL和开启设置服务

1、打开官方网站，上边有各种版本的yum源，找到自己想要的版本设置yum源

https://downloads.mariadb.org/mariadb/repositories/

若上不了网，也可以使用自己光盘里的老版本

 

2、若不用自己的光盘老版本，要配置yum源，这里我选了10.2的版本

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

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

yum源安装mariadb： yum install MariaDB-server

若有其他yum源，加一个enabled=0 先临时关闭，yum clean all 清除缓存，注意：官方给的yum源安装的Maria没有安装相依赖的包，还需自己安装相依赖的包

 

3、yum安装和开启服务

这里，就直接安装光盘里的老版本了

yum -y install mariadb-server

systemctl start mariadb 开启服务

　　ss -nutl 打开了3306的tcp端口

　　查询端口对应的进程信息 lsof -i :3306 或 netstat -tnlp | grep 3306

注意：开启服务后，会多一个mysql的用户，它的家目录：存放数据库，相当于数据库中的每个表

mysql用户是安装包时，执行了个脚本，创建了mysql用户，提示我们要是二进制安装需创建用户

rpm -q --scripts mariadb-server 可以查看这个脚本

 

4、mysql 运行

发现是root，能查能删，很不安全

还能mysql xxx 匿名登录

5、运行安全脚本

/usr/bin/mysql_secure_installation

实验二：二进制安装mariadb和开启设置服务

注意：安装之前要确保自己系统上没有mariadb服务，有老版本可以先卸掉，删除mysql用户

（1）去官网下载自己想要的版本 http://mariadb.org

      rpm -qi mariadb 可以查询官网

     上传，解包解压缩

     rz，tar xvf mariadb-10.2.8-linux-x86_64.tar.gz -C /usr/local/

[root@centos77 local]# tar xvf mariadb-10.2.8-linux-x86_64.tar.gz -C /usr/local/

  （不像编译安装一样，解包可以放在任何目录，这个二进制安装必须指定在这个目录）

 （2）cd /usr/local/ 发现mariadb的目录名字不符合要求

ln -s mariadb-10.2.8-linux-x86_64   /usr/local/mysql 创建软链接

[root@centos77 local]# ln -s mariadb-10.2.29-linux-x86_64/ /usr/local/mysql  创建软链接

 （3）修改当前的数据库文件所属组和所有者：

[root@centos77 local]# chown -R root.root /usr/local/mysql

（4）创建mysql用户：

[root@centos77 local]# useradd -r -s /sbin/nologin  -d /data/mysql   mysql  自己指定创建数据库文件，加上-r是系统创建，不加-r，就会在家目录自行创建隐藏目录，在数据库内查询show databases; 就会有其他乱码的数据库名称

（5）执行脚本，创建数据库文件：

mkdir /data/mysql  创建一个数据库目录
cd /usr/local/mysql 一定要在这个目录下执行脚本，不能进入scripts目录下，不然无法找到脚本路径
scripts/mysql_install_db  --user=mysql（以mysql身份运行）  --datadir=/data/mysql  （指定数据库路径）

[root@centos77 mysql]# scripts/mysql_install_db  --user=mysql  --datadir=/data/mysql  指定当前的数据库路径

（6）修改mysql配置文件

[root@centos77 mysql]#mkdir /etc/mysql  新建mysql配置文件路径
[root@centos77 local]# cd /usr/local/mysql
[root@centos77 mysql]# ls
bin data INSTALL-BINARY mariadb-10.2.29-linux-x86_64 README-wsrep sql-bench
COPYING EXCEPTIONS-CLIENT lib mysql-test scripts support-files
CREDITS include man README.md share THIRDPARTY
[root@centos77 mysql]# ls support-files/
binary-configure my-innodb-heavy-4G.cnf my-small.cnf（小配置文件） mysql.server wsrep_notify  
magic my-large.cnf mysqld_multi.server policy
my-huge.cnf（大配置文件） my-medium.cnf（中等配置文件） mysql-log-rotate wsrep.cnf
[root@centos77 mysql]#cp /usr/local/mysql/support-files/my-huge.cnf  /etc/mysql/my.cnf 将本地存在的mysql配置文件复制到自己创建的目录下，并起名为my.cnf
[root@centos77 mysql]#vim /etc/mysql/my.cnf  配置文件内容
[client]
#password       = your_password
socket          = /tmp/mysql.sock 
[mysqld]
datadir=/data/mysql  修改数据库路径
port            = 3306
socket          = /tmp/mysql.sock 

（7）复制centos自带的服务脚本，并将此服务脚本加入到运行服务列表内，启动该服务：

[root@centos77 mysql]# cp support-files/mysql.server  /etc/init.d/将本地的mysql服务复制到配置文件中
[root@centos77 mysql]# cd /etc/init.d
[root@centos77 init.d]# ls
functions  mysql.server  netconsole  network  README
[root@centos77 init.d]# mv mysql.server  mysqld  将mysql.server 改名为mysqld
[root@centos77 init.d]# chkconfig --list  查看mysqld 服务脚本是否在服务列表中

Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.

If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.

netconsole 0:off	1:off	2:off	3:off	4:off	5:off	6:off
network 0:off	1:off	2:on	3:on	4:on	5:on	6:off
[root@centos77 init.d]# chkconfig --add mysqld  添加mysqld 服务
[root@centos77 init.d]# chkconfig --list

Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.

If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.

mysqld 0:off	1:off	2:on	3:on	4:on	5:on	6:off
netconsole 0:off	1:off	2:off	3:off	4:off	5:off	6:off
network 0:off	1:off	2:on	3:on	4:on	5:on	6:off

（8）启动mysql服务，并制定PATH变量路径：

[root@ansibleinit.d]#service mysqld start
[root@ansibleinit.d]#echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@ansibleinit.d]#. /etc/profile.d/mysql.sh 生效PATH变量路径

（9）查看当前的数据库目录：

[root@ansibleinit.d]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.29-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'datadir'; 查询当前的数据库目录
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+
1 row in set (0.01 sec)

MariaDB [(none)]> select @@datadir;  查询当前的数据库目录
+--------------+
| @@datadir    |
+--------------+
| /data/mysql/ |
+--------------+
1 row in set (0.00 sec)

（8）对数据库进行安全初始化

[root@ansibleinit.d]#cd /usr/local/mysql/bin/ 切换到当前目录可以看到安全初始化脚本

[root@ansiblebin]#mysql_secure_installation  进行mysql数据库密码设置

实验三：源码编译安装mariadb　

（1）安装依赖包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-
devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-
devel libevent-devel libaio-devel

（2）创建账号

[root@centos77 ~]# useradd -r -s /sbin/nologin -d /data/mysql/ mysql

（3）创建mysql目录

[root@centos77 ~]# mkdir /data/mysql
[root@centos77 ~]# chown mysql.mysql /data/mysql

（4）将网上下载的源码包传到linux系统上

mariadb源码包下载地址：https://mariadb.com/kb/en/mariadb-server-10-2-29/

rz mariadb-10.2.29.tar.gz   将源码传到电脑上
[root@centos77 ~]# tar xvf mariadb-10.2.29.tar.gz  解压源码
[root@centos77 ~]# cd mariadb-10.2.29 切换到此目录下
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \  指定程序路径
-DMYSQL_DATADIR=/data/mysql/ \   数据库存放路径
-DSYSCONFDIR=/etc/mysql \    配置文件路径
-DMYSQL_USER=mysql \  mysql账号
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \      socket文件路径
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci

[root@centos77 ~]# make -j 4 && make install  进行多线程编译安装

（5）生成数据库

cd /app/mysql/
[root@centos77 mysql]# scripts/mysql_install_db  --user=mysql  --datadir=/data/mysql

（6）指定PATH变量路径：

[root@centos77 mysql]# echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh  指定PATH变量路径
[root@centos77 mysql]# . /etc/profile.d/mysql.sh  让PATH变量路径生效

（7）准备配置文件

cd /app/mysql
[root@centos77mysql]# cp support-files/my-huge.cnf  /etc/mysql/my.cnf

（8）准备启动脚本

cd  /app/mysql/
[root@centos77 mysql]# cp support-files/mysql.server  /etc/init.d/mysql

（9）添加mysqld，并启动服务

[root@centos77  mysql]# chkconfig --add mysqld ,service mysqld start

（10）安全初始

cd  /user/local/mysql/bin/
mysql_secure_installation  设置mysql密码

实验四：基于源码编译多实例

1 规划相关目录

mkdir /mysql/{3306,3307,3308}/{data,etc,socket,log,pid} -pv
chown -R mysql.mysql /mysql

2 准备数据库数据文件

/app/mysql/scripts/mysql_install_db --user=mysql --datadir=/mysql/3306/data
/app/mysql/scripts/mysql_install_db --user=mysql --datadir=/mysql/3307/data
/app/mysql/scripts/mysql_install_db --user=mysql --datadir=/mysql/3308/data

3 准备配置文件

配置3308配置文件：

vim /mysql/3308/etc/my.cnf 
[mysqld]
port=3308
datadir=/mysql/3308/data
socket=/mysql/3308/socket/mysql.sock
[mysqld_safe]
log-error=/mysql/3308/log/mariadb.log
pid-file=/mysql/3308/pid/mariadb.pid

配置3307配置文件：

vim /mysql/3307/etc/my.cnf 
[mysqld]
port=3308
datadir=/mysql/3307/data
socket=/mysql/3307/socket/mysql.sock
[mysqld_safe]
log-error=/mysql/3307/log/mariadb.log
pid-file=/mysql/3307/pid/mariadb.pid

配置3306配置文件：　

vim /mysql/3306/etc/my.cnf 
[mysqld]
port=3308
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid

编mysql启动脚本：vim mysqld

#!/bin/bash
#chkconfig: 345 80 2
port=3308  #对应不同的监听端口进行修改，如3306,3307
mysql_user="root"
mysql_pwd="centos"  # 初始账号没有口令可以写成空“”
cmd_path="/app/mysql/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}


function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}


function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

4 准备启动脚本

chmod +x /mysql/3308/mysqld
chmod +x /mysql/3307/mysqld
chmod +x /mysql/3306/mysqld

5 启动mysql

以3308为例，进行启动

/mysql/3308/mysqld start
mysql -uroot -h127.0.0.1 -P 3308 
mysql -uroot -S /mysql/3308/socket/mysql.sock

6 安全加固

/app/mysql/bin/mysql_secure_installation -S /mysql/3308/socket/mysql.sock

mysql -uroot -pcentos -h127.0.0.1 -P 3308 
mysql -uroot -pcentos -S /mysql/3308/socket/mysql.sock

7 将数据库文件进行配置，添加到开机启动程序

cp /mysql/3308/mysqld /etc/init.d/mysql3308
chkconfig --list
chkconfig --add mysqld3308

二、mysql基础入门操作

1、命令行交互式命令：mysql

mysql 命令的选项：

　　-uUSERNAME: 用户名；默认为root

　　-hHOST: 服务器主机; 默认为localhost

　　-pPASSWORD ：用户的密码; 建议使用-p, 默认为空密码

关系型数据库的常见组件

 数据库：database
表：table
 行：row
 列：column
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler，任务计划

2、SQL语言规范

1、在数据库系统中，SQL语句不区分大小写(建议用大写)
2、SQL语句可单行或多行书写，以“;”结尾
3、关键词不能跨多行或简写
4、用空格和缩进来提高语句的可读性
5、子句通常位于独立行，便于编辑，提高可读性
6、注释：
SQL标准：
   /*注释内容*/ 多行注释
   -- 注释内容 单行注释，注意有空格
MySQL注释：
#

3、数据库对象

1、数据库的组件(对象)：
2、数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则：
  必须以字母开头
  可包括数字和三个特殊字符（# _ $）
  不要使用MySQL的保留字
  同一database(Schema)下的对象不能同名

4、SQL语句分类

DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER

DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE

DCL：Data Control Language 数据控制语言
GRANT，REVOKE，COMMIT，ROLLBACK

DQL：Data Query Language 数据查询语言
SELECT

　　

5、SQL语句构成

1、Keyword组成clause
2、多条clause组成语句
示例：

SELECT * SELECT子句
FROM products FROM子句
WHERE price>400 WHERE子句

说明：一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字

三、数据库操作

1、查看数据库列表：show databases;

1）查看某个数据库中的表：show tables [from database_name] 如果已经在这个库下，不用加from

2）数据库有information_schema 库，这个库是只读库，只有root特殊权限的用户登录才能看到，不能drop删除

3）数据库有#mysql50#.mozilla 类似的库的处理方法，因为mysql家目录下有个隐藏文件导致，不能删除，将/usr/local/mysql/目录下的隐藏文件删除即可。

show create  databases  test; 可以查看当前数据库使用的是什么字符集：

2、创建数据库：

CREATE DATABASE（数据库） [IF NOT EXISTS]（如果不存在，创建）'DB_NAME'; 创建成功一个，会生成一个表数据库文件

CHARACTER SET 'character set name' 设置字符集，不推荐设置，不建议调

COLLATE 'collate name' 设置排序规则，不推荐设置

字符集一般默认的utf8

注意：数据库对象的命名规则

必须以字母开头

可包括数字和三个特殊字符（# _ $）

不要使用MySQL 的保留字

同一Schema（数据库） 下的对象不能同名

以下是创建一个utf8mb4的字符集数据库：

MariaDB [(none)]> help create database;可以查询帮助，可以看出怎么去创建指定的字符集数据库
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html


MariaDB [(none)]> create database utf8mb4 CHARACTER SET=utf8mb4;
Query OK, 1 row affected (0.00 sec)

查看当前数据库的字符集：show create database utf8mb4;

3、删除数据库

DROP DATABASE [IF EXISTS] 'DB_NAME'; 删除成功，会删除对应的表数据库文件

查看支持所有字符集：SHOW CHARACTER SET; 不需要改

查看支持所有排序规则：SHOW COLLATION; 不需要改

获取命令使用帮助：mysql>help create database;

例：create database utf8mb4;  创建utf8mn4数据库

drop database utf8mn4;  删除utf8mn4数据库

四、创建表及操作表

表含义：

• 表：二维关系
• 设计表：遵循规范
• 定义：字段，索引
• 字段：字段名，字段数据类型，修饰符
• 约束，索引：应该创建在经常用作查询条件的字段上

1、查看数据库中所有的表：show tables from base_name;

　 查看表结构：desc tbl_name;

2、创建表：CREATE TABLE

（1） 直接创建

（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 （引擎设置，默认就好）

　　SHOW ENGINES; 查看支持的engine 引擎类型

• ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT |COMPACT} 行的格式，默认就好

（3）create table students3 select * [id,name] from students; 也可以创建一个表，完全复制另一个表的结构[或自己选择的结构]；复制的表没有约束，如主键

insert into students3 select * from students; 有students3这个表，完全复制另一个表的内容

获取创建表的帮助：mysql> HELP CREATE TABLE;

注意：
 Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同表可以使用不同的存储引擎
 同一个库中表建议要使用同一种存储引擎类型

MariaDB [(none)]> create database studentdb;创建一个studentdb数据库
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use studentdb;  切换到studentdb数据库内
Database changed

MariaDB [studentdb]> create table student (id int unsigned auto_increment primary key,name varchar(10) not null,sex enum('f','m') default 'm',age tinyint unsigned,mobile char(11),address varchar(50)); 创建表的详细信息

解释：创建studentdb库中名为student表；其中id：数据类型int为正、不为空、设为主键；name：数据类型VARCHAR (10)、不为空；age：数据类型tinyint UNSIGNED) sex：性别，分为f,m，不写，默认为m,mobile：固定的字符型11，address:地址为变长的50字符型。

查看自己创建的student表结果： desc  student; 或者用show columns  from student;

 show columns  from student;命令也可以查询当前自己创建的表格结构：

3、操作表

查看所有的引擎：SHOW ENGINES
查看表：SHOW TABLES [FROM db_name]
查看表结构：DESC [db_name.]tb_name
                            SHOW COLUMNS FROM [db_name.]tb_name
删除表：DROP TABLE [IF EXISTS] tb_name
查看表创建命令：SHOW CREATE TABLE tbl_name
查看表状态：SHOW TABLE STATUS LIKE 'tbl_name’
查看库中所有表状态：SHOW TABLE STATUS FROM db_name

4、数据类型：

1、数据类型：  
    数据长什么样
  数据需要多少空间来存放
2、系统内置数据类型和用户定义数据类型
3、MySql支持多种列类型：
  数值类型
  日期/时间类型
  字符串(字符)类型
 https://dev.mysql.com/doc/refman/5.5/en/data-types.html
3、选择正确的数据类型对于获得高性能至关重要，三大原则：
  更小的通常更好，尽量使用可正确存储数据的最小数据类型
  简单就好，简单数据类型的操作通常需要更少的CPU周期
  尽量避免NULL，包含为NULL的列，对MySQL更难优化

 

整型

• tinyint(m)：1个字节 范围(-128~127)
• smallint(m)：2个字节 范围(-32768~32767)
• mediumint(m)： 3个字节 范围(-8388608~8388607)
• int(m)： 4个字节 范围(-2147483648~2147483647)
• bigint(m)： 8个字节 范围(+-9.22*10的18次方)

加了unsigned，则最大值翻倍，如：tinyint unsigned的取值范围为(0~255)
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

• BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

5、修饰符
1、所有类型：

• NULL 数据列可包含NULL值
• NOT NULL 数据列不允许包含NULL值
• DEFAULT 默认值
• PRIMARY KEY 主键
• UNIQUE KEY 唯一键
• CHARACTER SET name 指定一个字符集

2、数值型

• AUTO_INCREMENT 自动递增，适用于整数类型
• UNSIGNED 无符号

五、修改表结构，尽量不要改表结构，很少用

1、查看表结构：DESC [db_name.]tb_name;

2、修改表结构 alter

ALTER TABLE 'tbl_name'

　　字段：

　　　　添加字段：add

　　　　　　ADD col1 data_type [FIRST加到第一段的后边|AFTER col_name某一段的后边]

　　　　删除字段：drop

　　　　修改字段：change （字段名）, modify （字段属性）

　　　　索引:

　　　　　　添加索引：add

　　　　　　删除索引: drop

　　　　表选项：

　　　　　　修改:change

查看帮助：Help ALTER TABLE

3、例子：

Help ALTER TABLE 查看帮助

注：如果表或者数据库结构字符集出现问题，可用此方法进行解决：

alter  database studentdb  character set utf8mb4;将数据库的字符集改为utf8mb4

alter  table student  character  set utf8mb4;   将新加的表格字符集改为utf8mb4,老的字符集不能修改

alter table student change  name name varcharacter(20)  character  set  utf8mb4; 将老的字符集名称改为新的字符集utf8mb4名称。

ALTER TABLE students3 RENAME s3; 改表名

ALTER TABLE s3 ADD phone varchar(11) AFTER name; 在s3表的name字段后加一个phone

ALTER TABLE s1 MODIFY phone int; 把phone的数据类型改为int

ALTER TABLE s1 CHANGE COLUMN phone mobile char(11); 把字段phone改名为字段mobile，数据类型为char(11)

ALTER TABLE s1 DROP COLUMN mobile; 删除字段mobile

ALTER TABLE students ADD gender ENUM('m','f') 增加gender字段，为枚举类型，只能是m或f

ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY; 把修改id字段为sid，数据类型为正int、非空、主键

ALTER TABLE students ADD UNIQUE KEY(name); 在name字段加唯一键

ALTER TABLE students ADD INDEX(age); 在age字段加索引

DESC students; 查看这个表

SHOW INDEXES FROM students; 查看索引信息

ALTER TABLE students DROP age;更改表学生年龄

六、DML语句：插入、删除、更新

1、查看

select * from tab_name [WHERE clause [LIMIT [m,]n];] 查看表中的所有内容，Limit m,n 跳过m行，要n行

select id,name,... from tab_name; 查出指定的表中内容

select count(*) from tab_name; 查看表中的记录数量，count()是自带的函数

2、修改表内容：

• INSERT 添加，插入：

　　INSERT [INTO] tbl_name[(col_name,...)] VALUES(val1,...),(...),... 

　　分析：① tbl_name后不加(),默认按表结构的列；若加()，前后()内容要对应，顺序可以不按表结构，也可以设null值，但最好不要，

　　　　　② 选项不是数字，都要加''，例：name='along'

insert into tab_name1 select * from tab_name2; 批量导数据，

插入表的格式：

（1）INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
（2）INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]

示例：

MariaDB [studentdb]> insert into student (name,age,mobile,address)values('wang',30,10086,'beijing'),  
-> ('zhang',28,10010,'zhengzhou');

select  *  from  student; 展开此表格的详细内容：

解释：insert  into添加一个表，表名为student，括号内注释：指定名字，年龄，手机号，地址，values对应相应的值。

create  table  employee(员工的含义)  select  *  from  student; 可以复制克隆此表格，克隆后的表格，起名为employee（员工的含义）

此时表格结构和内容都被复制过去：

 

 

use studentdb;  切换到studentdb;数据库下

create  table custom like  student; 复制原有的像的表格结构，但是不能复制表格内容：

 批量添加到一个新表中： insert custom select * from student; 含义：将student表格内容复制到custom表格内。注意：两张表格结构要一致才行，不然就会有问题。

 status 可以查询数据库的字符集类型，此时客户端字符集和服务器端的字符集不一致：

MariaDB [studentdb]> status
--------------
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		3
Current database:	studentdb
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		5.5.60-MariaDB MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket
Insert id:		3
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			2 hours 31 min 34 sec

Threads: 1  Questions: 76  Slow queries: 0  Opens: 8  Flush tables: 2  Open tables: 34  Queries per second avg: 0.008

修改当前的服务器端的字符集类型：

vim  /etc/my.cnf

character-set-server=utf8mb4

修改客户端的字符集类型：

vim /etc/my.cnf.d/mysql-clients.cnf

default-character-set=utf8mb4

此时，我们再查看当前的数据库字符集类型，已经全部统一为utf8mb4:

查询字符集类型： status;

2、DELETE:删除表内容，下有例子

　　DELETE FROM tbl_name [WHERE clause] [ORDERBY 'col_name' [DESC]];可先排序再指定删除的行数

　　分析：若不加where，直接把所有列都删除了

• TRUNCATE TABLE tbl_name; 清空表，快速清空，删除的时候，不计日志，谨慎使用

示例：

delete from student  where  id >= 3; 删除id号大于等于3的表格：

3、我们来对比一下5.5版本的数据库和10.2的数据库区别：

（1）10.2版本的数据库：当前创建一个db1数据库，我们来看创建的db1数据库中，会有对应的frm和idb后缀的表生成。

（2）10.2版本的功能是打开的，所以会将数据库的表分类存放

show  variables like '%per_table';

innodb_file_per_table    ON

（3）5.5版本的数据库表：可以看到frm和idb后缀的数据库文件全部在一个文件中存放，不太合理，管理起来比较混乱。

 

（4）我们可以看到5.5版本的这个功能是关闭的，需要我们将此功能打开，才会分别存放数据。

show  variables like '%per_table';

innodb_file_per_table     改为ON

 

（5）修改5.5版本的配置文件，将此功能打开，不写ON，默认是打开的意思：

vim  /etc/my.cnf

innodb_file_per_table

 

 （6）重启mysql 服务

[root@centos7studentdb]#systemctl restart mariadb

（7）我们在studentdb数据库中创建一个新表tt2,然后在数据库中/var/lib/mysql/studentdb/目录下查看当前新建的表是否有两个表存在（idb,frm后缀的文件），

可以看到，此时已经重新有期望的两个后缀（idb,frm）的表生成，设置完毕。

4、UPDATE: 更新，修改表内容，下有例子

　 UPDATE tbl_name SET col1=val1, col2=val2, ... [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n]; 

 　分析：若不加where，直接把所有列都修改了，Limit m,n 跳过m行，要n行

注意：一定要有限制条件，否则将修改所有行的指定字段
    限制条件：
    WHERE
    LIMIT
Mysql 选项：-U|--safe-updates| --i-am-a-dummy 

避免人间惨案发生：可以将mysql -U 定义为别名，alias mysql='mysql -U'，当删除时就会报错，提示你输入where语句，与delete用法一致，都要加where。

也可以将此功能放到配置文件中,也会低概率保证出错问题：

vim /etc/my.cnf.d/mysql-clients.cnf
safe-updates

示例：

操作步骤如下：

mysql -uroot -pcentos  -u指定用户名，  -p指定密码
use  studentdb;  切换至studentdb数据库下
select * from student;  查找当前的表中内容
update student set name='haha' id=1;  将表中第一行name的wang修改为haha

 七、DQL语句：select （单表查询）

DQL ：SELECT，用法很多

SELECT col1,col2,... FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n]; 查询表内容信息，

1、字段表示法：

　　*: 所有字段

　　as ：字段别名，若事先做好了表，想要把列的英语改成中文，不用修改，可以直接用别名，

2、排序：order by col_name [desc]

　　解释：by后指定列，desc反向排序，反向也可以-col_name

　　注意：若其中有空值，正向排序空值在第一行，反向排序空值在最后一行，可以order by -col_name desc 即正向排序，有把空值放在最后一行

3、WHERE clause：where 后的选项

操作符：下例3

　　>, <, >=, <=, ==, !=

　　BETWEEN ... AND ...

        DISTINCT ： 去重，

　　LIKE：模糊匹配，

　　　　% ：任意长度的任意字符

　　　　_ ：任意单个字符；

　　RLIKE ：正则表达式模式匹配

　　IS NULL ，IS NOT NULL 寻找空值，不能用=，只能用is，所以最好不要有null，不好管理

　　IN （val1,val2,…） 离散值显示

条件逻辑操作：

　　and ，or ，not ，xor

4、GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算

    avg(), max(), min(), count(), sum()

 5、HAVING: 对分组聚合运算后的结果指定过滤条件

 6、ORDER BY: 根据指定的字段对查询结果进行排序

       升序：ASC

      降序：DESC

7、LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制

8、对查询结果中的数据请求施加“锁”

FOR UPDATE: 写锁，独占或排它锁，只有一个读和写

LOCK IN SHARE MODE: 读锁，共享锁，同时多个读

示例：

（1）*：代表所有字段： select * from student;  在所有字段中显示表格内容。

（2）AS用法：select  id as 编号,name 姓名,age 年龄,sex 性别,mobile 手机号,address 地址 from student;  定义好英文的字段，想改为中文，直接用as即可。

 （3）where用法：  select  * from  student  where age > 20;  筛选年龄大于20以上的表。

 （4） select  * from student  where age >=20 and  age <=30;  筛选年龄在20到30之间

           select *  from student where age between 20 and 30; 筛选年龄在20到30之间。

 （5）select * from student  where  age =10 or  age = 30;筛选年龄10和30的行

        select   *  from student  where age  in (10,30); 推荐使用此方法，简洁，好用

 （6） select * from  student  where   mobile  is  null;帅选手机号为空。   两个比较特殊，用is语句，表格中尽量不要为空。

           select *  from student  where  mobile  is not null; 筛选手机号不为空。

（7）select * from student  where  name  like %a%;  姓名 包含a的行。

        select *  from student where  name  like 'a%'; 显示姓名以a开头的行。

       select * from student where name rlike  '^a';以a开头的行，支持正则表达式，影响服务器性能，尽量不要用，最好精确匹配

       select * from student where name rlike 'a$'; 以a结尾的行。

 （8）select distinct sex  from student;  将性别去重

 （9）select count(*)  from student; 统计全部的个数

select  sex as 性别,count(*) as 人数  from  student group by sex;  按性别统计人数

 （10）select sex as 性别,avg(age) as 年龄 from student group by sex; 以性别统计平均年龄

 （11）select name,sex,max(age) from  student group by  name,sex;  按姓名和性别统计年龄的最大值。  分组统计

 （12）1、select sex,avg(age) from student  group by sex having sex = 'm'; 先按性别筛选年龄，最后再过滤性别

           2、select sex,avg(age) from student  where sex='m' group by sex;   先过滤性别，再按性别筛选年龄

           3、select classid,count(*) from student where  age >=30 group by classid  having  classid >=3; 筛选年纪大于等于30岁人数,班级大于等于3的人数

           4、 select classid,count(*) from student  where age >= 30 group by classid; 筛选出年龄大于等于30岁以上的班级

 （13）select * from student order by age asc;  按年龄从小到大排序

           select * from student order by age desc;  按年龄从大到小排序

 （14）select * from student order by age asc limit 1;  从小到大排序选第一个

            select * from student order  by  age asc limit 3,4; 从小到大排序，跳过第三个选后面四个。

            select * from student order by age asc,mobile desc; 将年龄先进行从小到大排序，再将mobile从大到小排序。

            select * from student order by -clessid asc;  小技巧，classid 前面加一个-就会将null排序放到最后。

 

 （15）以ClassID为分组依据，显示每组的平均年龄，再显示平均年龄大于30的分组及平均年龄

      select classid,avg(age) from students group by classid having avg(age) > 30;

 （16）查询年龄大于等于20岁，小于等于25岁的同学的信息

   1、select * from  student  where age >= 20 and age <=25;

   2、select *  from student where age  between  20  and  25;

 

小练习：

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

  第一种写法：select gender,age from students where gender = 'f' group by gender having age > 25;
  第二种写法：select gender,age from students where age > 25 group by gender having gender ='f';

(2) 以ClassID为分组依据，显示每组的平均年龄

select classid,avg(age) from students  group by classid;

 

(3) 显示第2题中平均年龄大于30的分组及平均年龄

select classid,avg(age) from students  group by classid having avg(age) > 30;

(4) 显示以L开头的名字的同学的信息

select * from students where name like 'l%';

(5) 显示TeacherID非空的同学的相关信息

 select *  from students  where teacherid is not  null;

(6) 以年龄排序后，显示年龄最大的前10位同学的信息

select * from students  order by age  desc limit 10;

(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息

select * from students where age >=20 and age <=25;
select * from students where age  between 20 and 25;

八、 DQL 语句 ：SQL JOINS （SQL连接）

多表查询

（1）交叉连接：笛卡尔乘积
（2）内连接：
    等值连接：让表之间的字段以“等值”建立连接关系；
    不等值连接
    自然连接:去掉重复列的等值连接
    自连接
（3）外连接：
      左外连接：
      FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
      右外连接
      FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

子查询：在查询语句嵌套着查询语句，性能较差

（1）基于某语句的查询结果再次进行的查询
    用在WHERE子句中的子查询
    用于比较表达式中的子查询；子查询仅能返回单个值
（2）SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age)FROM students);
（3）用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
     SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
（4）用于EXISTS
（5）用于FROM子句中的子查询
    使用格式：SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
    示例：
    SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID
   FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s
   WHERE s.aage>30;
（6）联合查询：UNION
    SELECT Name,Age FROM students UNION SELECT Name,Age FROM
    teachers;

（1）将筛选的表头（stuid,name,age,gender）再将一个teacher表合并到一起，teacher字段要和student字段一致，注：要筛选同一类型的表进行合并。

  MariaDB [testdb]> select stuid as id,name,age,gender  from student

     -> union  联合在一起（有去重的功能）

     -> select * from teacher;

select * from student union select * from haha;将student和haha两张表纵向合并。

（2）select  * from haha union  select  *  from haha; 将haha表中重复的行去除。

          select  distinct * from haha;等价上面的写法

 （3）select  * from  student  cross  join haha; 将student 和haha竖向交叉合并，不常用。

 

  内连接：

（4）select * from student,haha where student.id=haha.id;

   select * from student inner join haha on student.id=haha.id;推荐使用此方法：将两张表ID相同的行进行合并

 

 （5）select  stuid,s.name as student_name,tid,t.name   teacher_name  from students  as  s  inner join  teachers   t on s.teacherid=t.tid;筛选出特定的几列进行显示。

 

右外连接： 右部分全部显示，左部分只显示有内容部分

（6）select * from students as s right outer join teachers t on s.teacherid=t.tid;

右外连接特例：右部分非交集全部显示，左部分只显示空值。

select  * from  students as s right outer  join  teachers  t on s.teacherid=t.tid  where s.teacherid is null;

左外连接： 左部分全部显示，右部分显示有内容的部分

（7）select *  from students  as s  left outer  join  teachers  t on  s.teacherid=t.tid;

 

   左外连接特例：左部分非交集部分显示，右部分只显示空值

   select  *  from students as s  left  outer  join  teachers   t on  s.teacherid=t.tid  where   t.tid  is null;

 子查询： select  avg(age)  from student;意思是学生的平均年龄

 select  *  from  teachers  where age > (select  avg(age)  from student); 老师大于学生的平均年龄

 

 （8）左右外连接：

1、select *  from students  full outer  join   teachers  on students.teacherid=teachers.tid;       MySQL暂时不支持此功能，其他数据库可能会支持。

2、select * from  students as s left outer  join teachers  t on  s.teacherid=t.tid   union   select * from  students as s right outer  join teachers  t on  s.teacherid=t.tid;

（9）左右外连接：将左右表的交集取反，后面慢慢理解。

select * from (select stuid,s.name as student_name,s.age as student_age,s.gender as student_gender ,classid,teacherid,tid,t.name as teacher_name,t.age as teacher_age,t.gender as teacher_gedner
from students as s left outer join teachers t on s.teacherid=t.tid
union
select stuid,s.name,s.age,s.gender,classid,teacherid,tid,t.name,t.age,t.gender
from students as s right outer join teachers t on s.teacherid=t.tid)
as f where f.teacherid is null or f.tid is null;

（10）自连接（自查询）：在左边表筛选出右边相同的ID表的内容：

　　select  * from a.name,b.name from emp as a  left  outer join  emp  as b on a.leaderid=b.id;

 

小练习：

 三张表：处理交集，筛选三张表中学生姓名，并报了什么科目，得了多少分。

 

 

select st.name,co.Course,sc.score  from  students  as st  inner  join  scores  as sc  on st.stuid=sc.stuid  inner  join  courses  as co on  sc.courseid=co.CourseID;

 

1、以ClassID分组，显示每班的同学的人数

select classid,count(*)  from students  group by classid having classid is not null;

2、以Gender分组，显示其年龄之和

select gender,sum(age)  from students group by gender;

3、以ClassID分组，显示其平均年龄大于25的班级

select  classid,count(*)  from students  where classid is not null group by classid  having avg(age)  > 25;

4、以Gender分组，显示各组中年龄大于25的学员的年龄之和

select classid,sum(age) from students  where age > 25 group by classid;

5、显示前5位同学的姓名、课程及成绩

select st.name,co.course,sc.score from students as st  inner join scores as sc  on   st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid order by score desc limit 5; 

6、显示其成绩高于80的同学的名称及课程

select st.name,co.course,sc.score  from students   as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid  having score > 80;  

7、取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩

select co.course,avg(sc.score)  from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid group by co.course;

8、显示每门课程课程名称及学习了这门课的同学的个数

select co.course,sum(st.stuid) from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid  group by co.course;

9、显示其年龄大于平均年龄的同学的名字

select name,age from students group by name having age > (select avg(age) from students);

10、显示其学习的课程为第1、2，4或第7门课的同学的名字

select st.name,co.course,co.courseid from students  as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid where co.courseid  in(1,2,4,7);

11、显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学

第一种写法：select * from students as a inner join (select classid,count(stuid),avg(age) as aage from students where classid >0 group by classid having count(stuid)>=3 ) as b on a.classid=b.classid where a.age>b.aage;

第二种写法： 拆分写：select classid,count(classid),avg(age) from students group by classid having count(classid)>=3; 
+---------+----------------+----------+
| classid | count(classid) | avg(age) |
+---------+----------------+----------+
| 1 | 4 | 20.5000 |
| 2 | 3 | 36.0000 |
| 3 | 5 | 32.2000 |
| 4 | 4 | 24.7500 |
| 6 | 4 | 20.7500 |
| 7 | 3 | 19.6667 |
+---------+----------------+----------+
select * from students where classid=1 and age>20.5;
select * from students where classid=2 and age>36.0;

12、统计各班级中年龄大于全校同学平均年龄的同学

select *  from students where age >(select avg(age) from students);