首页 > 数据库技术 > 详细

Mysql 基础

时间：2019-08-26 22:00:28      阅读：252      评论：0      收藏：0      [点我收藏+]

一、数据库基础介绍

目录：

数据库概述

数据的存储方式

  • 1.人工管理阶段
    • Excel表格
  • 2.文件系统阶段

数据库技术构成

  • 1.数据库系统 (DataBase System, DBS)
  • 2.数据库管理系统（DataBase Management System, DBMS）

数据库类型划分

  • 关系型数据库(RDS)
    • Oracle、MySQL、MariaDB、Percona Server for MySQL、DB2
  • 非关系型数据库(NoSQL)
    • Memcache、Redis、MongoDB、

SQL语言(结构化查询语言)

  • 1.DDL(数据库定义语言 //开发人员)
    • 数据库、表、视图、索引、存储过程、函数、CREATE DROP ALTER
  • 2.DML(数据库操作语言 //开发人员)
    • 插入数据 INSERT、删除数据 DELETE、更新数据UPDATE
  • 3.DQL(数据库查询语言 //运维开发人员)
    • 查询数据 SELECT
  • 4.DCL(数据库控制语言 //运维人员)
    • 控制用户的访问权限 GRANT、REVOKE

数据访问方式

  • 1.ODBC (php)
  • 2.JDBC (jsp)

数据库运维

  • 1.安装部署
    • yum安装
    • 二进制安装
    • 源码编译
  • 2.基础操作
    • 数据库操作
      • 增删查改
      • 单表查询
      • 多表查询
      • 视图
      • 触发器
      • 存储过程
  • 3.权限管理
    • 用户授权
    • 安全机制
  • 4.日志管理
    • 访问日志
    • 错误日志
    • 慢查询日志(索引)
    • binlog
  • 5.备份恢复
    • 逻辑备份
    • 物理备份
  • 6.HA 架构
    • 主从复制
    • MHA、Mycat
  • 7.性能优化

 

二、数据库安装管理

目录

1.YUM安装数据库

MySQL Yum仓库提供了用于在Linux平台上安装MySQL服务器，客户端和其他组件的RPM包。mysql-yum安装下载地址



注意
使用MySQL Yum仓库时，默认选择安装最新的MySQL版本。如果需要使用低版本请按如下操作。

1.安装MySQL仓库源

[root@sql ~]# rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

2.选择并启用适合当前平台的发行包

//列出所有MySQL发行版仓库
[root@sql ~]# yum repolist all|grep mysql
//禁用8.0发行版仓库, 启用5.6发行版仓库
[root@sql ~]# yum install yum-utils
[root@sql ~]# yum-config-manager --disable mysql80-community
[root@sql ~]# yum-config-manager --enable mysql57-community

注意
可以手动编辑/etc/yum.repos.d/mysql-community.repo 文件配置仓库

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

3.通过以下命令安装MySQL, 并启动MySQL

[root@sql ~]# yum install -y mysql-community-server
[root@sql ~]# systemctl start mysqld
[root@sql ~]# systemctl enable mysqld

MySQL服务器初始化（仅适用于MySQL 5.7）在服务器初始启动时，如果服务器的数据目录为空，则会发生以下情况：

  • 服务器已初始化。
  • 在数据目录中生成SSL证书和密钥文件。
  • validate_password插件安装并启用。
  • 超级用户帐户‘root‘@‘localhost‘已创建。

超级用户的密码被设置并存储在错误日志文件中。要显示它，请使用以下命令:

[root@vm-70-160 ~]# grep "password" /var/log/mysqld.log 
2018-04-28T07:11:51.589629Z 1 [Note] A temporary password is generated for root@localhost: jHlRHucap3+7

通过使用生成的临时密码登录并尽快更改root密码并为超级用户帐户设置自定义密码

[root@vm-70-160 ~]# mysql -uroot -pjHlRHucap3+7
mysql> ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘Bgx123.com‘;

注意
MySQL的validate_password插件默认安装。将要求密码至少包含大写、小写、数字、特殊字符、并且总密码长度至少为8个字符。

 

如果出现报错如下：ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

 

 

解决：重新设置一下新密码

SET PASSWORD = PASSWORD(‘新密码’)

 

密码策略问题异常信息：

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

如下图：

 

 

 

解决办法：

1、查看 mysql 初始的密码策略，

输入语句 “ SHOW VARIABLES LIKE ‘validate_password%‘; ” 进行查看，

如下图：

 

2、首先需要设置密码的验证强度等级，设置 validate_password_policy 的全局参数为 LOW 即可，

输入设值语句 “ set global validate_password_policy=LOW; ” 进行设值，

如下图：

 

3、当前密码长度为 8 ，如果不介意的话就不用修改了，按照通用的来讲，设置为 6 位的密码，设置 validate_password_length 的全局参数为 6 即可，

输入设值语句 “ set global validate_password_length=6; ” 进行设值，

如下图：

 

4、现在可以为 mysql 设置简单密码了，只要满足六位的长度即可，

输入修改语句 “ ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘123456‘; ” 可以看到修改成功，表示密码策略修改成功了！！！

如下图：

 

关于 mysql 密码策略相关参数；

1）、validate_password_length  固定密码的总长度；

2）、validate_password_dictionary_file 指定密码验证的文件路径；

3）、validate_password_mixed_case_count  整个密码中至少要包含大/小写字母的总个数；

4）、validate_password_number_count  整个密码中至少要包含阿拉伯数字的个数；

5）、validate_password_policy 指定密码的强度验证等级，默认为 MEDIUM；

关于 validate_password_policy 的取值：

0/LOW：只验证长度；

1/MEDIUM：验证长度、数字、大小写、特殊字符；

2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；

2.通用安装数据库

采用二进制免编译方式安装MySQL, 不需要复杂的编译设置和编译时间等待,解压下载的软件包,初始化即可完成MySQL的安装和启动。MySQL二进制包下载地址

 

MySQL二进制包官方安装手册

1.基础环境准备

[root@sql ~]# systemctl stop firewalld
[root@sql ~]# systemctl disable firewalld
[root@sql ~]# setenforce 0
 
//建立用户与相应目录
[root@sql ~]# groupadd mysql
[root@sql ~]# useradd -r -g mysql -s /sbin/nologin mysql
[root@sql ~]# mkdir /soft/src -p && cd /soft/src

2.下载MySQL并安装

[root@sql src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@sql src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /soft
[root@sql src]# ln -s /soft/mysql-5.7.22-linux-glibc2.12-x86_64/ /soft/mysql

3.进行MySQL初始化

//创建初始化目录以及数据库数据目录
[root@sql ~]# mkdir /soft/mysql/{mysql-file,data}
[root@sql ~]# chown mysql.mysql /soft/mysql/
 
//初始化数据库
[root@sql ~]# /soft/mysql/bin/mysqld --initialize \
--user=mysql --basedir=/soft/mysql \
--datadir=/soft/mysql/data
-------
//初始化数据库会告诉默认登陆账户与密码
2018-04-28T02:30:33.954980Z 1 [Note] A temporary password is generated for‘root@localhost: I,isfqnx.0tO‘
-------
 
//使用ssl连接, 初始化后重新授权目录权限[如不需要可忽略]
[root@sql ~]# /soft/mysql/bin/mysql_ssl_rsa_setup \
--datadir=/soft/mysql/data/
[root@sql ~]# chown -R mysql.mysql /soft/mysql/

4.建立MySQL配置文件

//mysql安装目录及mysql数据库目录
[root@sql ~]# cp /etc/my.cnf /etc/my.cnf_bak
[root@sql ~]# vim /etc/my.cnf
[mysqld]
basedir=/soft/mysql
datadir=/soft/mysql/data

5.启动MySQL数据库

//方式1,使用 mysqld_safe 
[root@sql ~]# /soft/mysql/bin/mysqld_safe --user=mysql &
 
//方式2, 使用(systemV)方式管理, [强烈推荐]
[root@sql ~]# cp /soft/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@sql ~]# chkconfig --add mysqld
[root@sql ~]# chkconfig mysqld on
 
//修改安装目录与数据存放目录
[root@sql ~]# sed -i ‘/^basedir=/cbasedir=\/soft\/mysql‘ /etc/init.d/mysqld 
[root@sql ~]# sed -i ‘/^datadir=/cdatadir=\/soft\/mysql\/data‘ /etc/init.d/mysqld
 
//启动数据库
[root@sql ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
 
//检查进程
[root@sql ~]# ps aux|grep mysql
 
//检查端口
[root@sql ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2659 mysql   16u  IPv6  28431      0t0  TCP *:mysql (LISTEN)
 
[root@sql ~]# ss -lntup|grep 3306
tcp    LISTEN     0      80 :::3306  :::*   users:(("mysqld",pid=2659,fd=16))

6.连接数据库测试

//默认情况没有mysql命令, 如果有可能使用过yum安装, 这样容易连错数据库(PATH路径存在命令执行优先级问题)
[root@sql ~]# mysql
-bash: mysql: command not found
//可以选择添加路径至PATH中, 或者直接使用绝对路径执行
[root@sql ~]# echo "export PATH=$PATH:/soft/mysql/bin" >> /etc/profile
[root@sql ~]# source /etc/profile
 
//登陆数据库
[root@sql ~]# mysql -uroot -p"I,isfqnx.0tO"
//默认系统配置数据库密码必须修改, 否则无法使用数据库
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
//修改系统默认密码
mysql> alter user root@‘localhost‘ identified by ‘ Bgx123.com ‘;
Query OK, 0 rows affected (0.01 sec)
mysql> exit;
 
//退出后使用新密码重新登录数据库
[root@sql ~]# mysql -uroot -p" Bgx123.com "
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)
mysql> exit;

注意: 如果需要重新初始化[可选]

//如果重新初始化会导致数据全部丢失
[root@sql ~]# yum install -y psmisc
[root@sql ~]# killall mysqld
[root@sql ~]# rm -rf /soft/mysql/data/*
[root@sql ~]# /soft/mysql/bin/mysqld --initialize --user=mysql \
--basedir=/soft/mysql --datadir=/soft/mysql/data
 
#可不执行
[root@sql ~]# /soft/mysql/bin/mysql_ssl_rsa_setup --datadir=/soft/mysql/data

3.源码安装数据库

1.源码安装mysql需要依赖cmake、boost

[root@sql ~]# yum install libaio-devel gcc gcc-c++ ncurses ncurses-devel cmake -y
[root@sql ~]# useradd -M -s /sbin/nologin mysql
[root@sql ~]# mkdir /soft/mysql
 

2.下载源码包并编译MySQL

[root@sql ~]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.22.tar.gz
[root@sql ~]# tar xf mysql-boost-5.7.22.tar.gz
[root@sql ~]# cd mysql-5.7.22/
[root@sql ~]# cmake -DCMAKE_INSTALL_PREFIX=/soft/mysql-5.7.22 \
-DMYSQL_UNIX_ADDR=/soft/mysql-5.7.22/data/mysql.sock \
-DMYSQL_DATADIR=/soft/mysql-5.7.22/data \
-DSYSCONFDIR=/soft/mysql-5.7.22/conf \
-DWITH_MYISAM_STORAGE_ENGINE=0 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=0 \
-DWITH_READLINE=1 \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_BOOST=/root/mysql-5.7.22/boost/boost_1_59_0
 [root@sql ~]# make
[root@sql ~]# make install

3.完成后基本优化

[root@sql ~]# ln -s /soft/mysql-5.7.22 /soft/mysql
[root@sql ~]# mkdir /soft/mysql/data
[root@sql ~]# chown -R mysql.mysql /soft/mysql

4.准备MySQL基础配置文件

[root@sql ~]# vim /etc/my.cnf
[mysqld]
basedir=/soft/mysql
datadir=/soft/mysql/data

5.拷贝MySQL程序启动文件

//拷贝官方准备的启动脚本
[root@sql ~]# cp /soft/mysql/support-files/mysql.server /etc/init.d/mysqld
 
//添加为系统服务, 并设定开机自启动
[root@sql ~]# chkconfig --add mysqld && chkconfig mysqld on

5.初始化MySQL

[root@sql ~]# /soft/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/soft/mysql --datadir=/soft/mysql/data
//启动MySQL
[root@sql ~]# /etc/init.d/mysqld start
 
//为mysql命令添加环境变量,以便后续简化执行命令
[root@sql ~]# echo "export PATH=$PATH:/soft/mysql/bin" >> /etc/profile
[root@sql ~]# source /etc/profile
 
//源码编译MySQL默认root没有密码
[root@sql ~]# mysql

4.变更数据库密码

1.更改root密码

//第一种方式, 需要知道密码
[root@sql ~]# mysqladmin -uroot -pBgx123.com password ‘Bgx123.com‘
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@sql ~]# mysql -uroot -pBgx123.com
mysql>
//第二种方式, 登录MySQL, 修改相应表
mysql> update mysql.user set authentication_string=password(‘Bgx123.com‘) where user=‘root‘;
mysql> flush privileges;

2.忘记mysql root密码

[root@sql ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables # 新增跳过授权表
//重启数据库生效
[root@sql ~]# systemctl restart mysqld
 
//查看表字段
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *C786BB788F276CD53317C80C1957E5F5696751F0 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
 
//5.7.6版本后更新密码方式
mysql> update mysql.user set authentication_string=password(‘Bgx123.com‘) where user=‘root‘;
mysql> exit
 
//5.7.6版本前更新密码方式
mysql> update mysql.user set password=password(‘Bgx123.com‘) where user="root" and host="localhost";
 
[root@sql ~]# vim /etc/my.cnf
[mysqld]
#skip-grant-tables #注释
//重启数据库生效
[root@sql ~]# systemctl restart mysqld
//使用新密码登录数据库
[root@sql ~]# mysql -uroot -pBgx123.com
mysql>

 

三、数据库基本操作

目录：

 

系统数据库：

  • information_schema(虚拟库)
    • 用户表信息、列信息、权限信息、字符信息等
  • performance_schema
    • 主要存储数据库服务器的性能参数
  • mysql(授权库)
    • 主要存储系统用户的权限信息
  • sys(优化库)
    • 主要存储数据库服务器的性能参数
  • bgx(业务库)
    • 主要存放业务所需要的库和表

1.数据库连接方式

使用mysql -u root -p可以连接数据库, 但这只是本地连接数据库的方式, 在生产很多情况下都是连接网络中某一个主机上的数据库

-P //指定连接远程数据库端口
-h //指定连接远程数据库地址
-u //指定连接远程数据库账户
-p //指定连接远程数据库密码

[root@sql ~]# mysql -uroot -p
Enter password: Bgx123.com

2.数据库基本操作

1.查看数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0.01 sec)

2.创建数据库DDL

mysql> create database Bgx_edu;
Query OK, 1 row affected (0.00 sec)
//以分号结尾

注意

数据库名称严格区分大小写
数据库名称必须是唯一
数据库名称不允许使用数字 
数据库名称不能使用关键字命名create select

3.查看当前的库内容

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Bgx_edu            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
 
//执行命令不区分大小写
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Bgx_edu            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

4.删除数据库

mysql> drop database Bgx_edu;
Query OK, 0 rows affected (0.07 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
//删除库下的表
mysql> drop table Bgx_edu.t1;

5.查询某个库的表

//use进入对应库
mysql> use Bgx_edu;
Database changed
 
//列出当前库下面的表
mysql> show tables;
Empty set (0.00 sec)
 
//查询某个库下的表结构
mysql> desc mysql.slow_log;
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| Field          | Type                | Null | Key | Default              | Extra                          |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| user_host      | mediumtext          | NO   |     | NULL                 |                                |
| query_time     | time(6)             | NO   |     | NULL                 |                                |
| lock_time      | time(6)             | NO   |     | NULL                 |                                |
| rows_sent      | int(11)             | NO   |     | NULL                 |                                |
| rows_examined  | int(11)             | NO   |     | NULL                 |                                |
| db             | varchar(512)        | NO   |     | NULL                 |                                |
| last_insert_id | int(11)             | NO   |     | NULL                 |                                |
| insert_id      | int(11)             | NO   |     | NULL                 |                                |
| server_id      | int(10) unsigned    | NO   |     | NULL                 |                                |
| sql_text       | mediumblob          | NO   |     | NULL                 |                                |
| thread_id      | bigint(21) unsigned | NO   |     | NULL                 |                                |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
12 rows in set (0.00 sec)
 
//查看某张表的建表语句
mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT=‘Slow log‘
1 row in set (0.00 sec)

3.数据库增删查改

在MySQL管理软件中, 可以通过SQL语句中的DML语言来实现数据的操作, 包括如下:

INSERT数据插入
UPDATE数据更新
DELETE数据删除

1.准备操作环境数据表

#创建数据文件
mysql> create database bgx;
mysql> use bgx;
mysql> create table t1(id int, 
name varchar(10),
sex enum(‘man‘,‘gril‘), 
age int);
 
#查看表字段
mysql> desc t1;
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| id    | int(11)            | YES  |     | NULL    |       |
| name  | varchar(10)        | YES  |     | NULL    |       |
| sex   | enum(‘man‘,‘gril‘) | YES  |     | NULL    |       |
| age   | int(11)            | YES  |     | NULL    |       |
+-------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2.插入数据INSERT语句

#1.插入完整数据, 顺序插入: INSERT INTO 表名(字段1,字段2,字段n) VALUES (值1,值2,值 n);
mysql> insert into t1(id,name,sex,age) values ("1","bgx","man","18");
Query OK, 1 row affected (0.01 sec)
 
#1.插入完整数据, 推荐方式 INSERT INTO 表名 VALUES (值1,值2,值n);
mysql> insert into t1 values("2","bgx2","gril","10");
Query OK, 1 row affected (0.01 sec)
 
#2.指定字段插入, INSERT INTO 表名(字段2,字段3…) VALUES (值 2,值3…);
mysql> insert into t1(name,sex,age) values ("bgx","man","20");
Query OK, 1 row affected (0.00 sec)
 
#3.插入多条记录, INSERT INTO 表名 VALUES (值1,值2,值n),(值1,值2,值n);
mysql> insert into t1 values
("3","bgx3","man","18"), 
("4","bgx4","man","18"), 
("5","bgx5","man","18");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from t1;
+------+------+------+------+
| id   | name | sex  | age  |
+------+------+------+------+
|    1 | bgx  | man  |   18 |
|    2 | bgx2 | gril |   10 |
| NULL | bgx  | man  |   20 |
|    3 | bgx3 | man  |   18 |
|    4 | bgx4 | man  |   18 |
|    5 | bgx5 | man  |   18 |
+------+------+------+------+
6 rows in set (0.00 sec)

3.更新数据UPDATE语句

//语法: 更新 表 设置 字段1=值1, 字段2=值2, WHERE 条件;
1.查看需要修改的表的字段 desc
2.查询对应的字段 select
3.更新对应的表字段 update
4.添加对应的where条件,精准修改

//示例1: 将t1表中, name字段等于bgx1的改为update_bgx
mysql> update t1 set name="update_bgx" where name="bgx1";
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
 
mysql> select * from t1;
+------+------------+------+------+
| id   | name       | sex  | age  |
+------+------------+------+------+
|    1 | update_bgx | man  |   18 |
|    2 | bgx2       | gril |   10 |
| NULL | update_bgx | man  |   20 |
|    3 | bgx3       | man  |   18 |
|    4 | bgx4       | man  |   18 |
|    5 | bgx5       | man  |   18 |
+------+------------+------+------+
6 rows in set (0.00 sec)
 
 
//示例2: 修改密码示例, 查看表字段内容
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
//更新字段
mysql> update mysql.user set
authentication_string=password("Bgx123.com") 
where user=‘root‘ and host=‘localhost‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4.删除数据DELETE

语法: DELETE FROM 表名 WHERE CONITION;

//删除字段包含update_bgx
mysql> delete from t1 where name="update_bgx";
Query OK, 2 rows affected (0.01 sec)
 
mysql> select * from t1;
+------+------+------+------+
| id   | name | sex  | age  |
+------+------+------+------+
|    2 | bgx2 | gril |   10 |
|    3 | bgx3 | man  |   18 |
|    4 | bgx4 | man  |   18 |
|    5 | bgx5 | man  |   18 |
+------+------+------+------+
4 rows in set (0.00 sec)
 
//清空表数据
mysql> truncate t1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t1;
Empty set (0.00 sec)

4.数据库查询语句

4.1单表查询

在学习查询前, 需要定义好对应数据进行查询

编号 id int 
姓名 name varchar(30) 
性别 sex enum 
日期 time date 
职位 post varchar(50) 
描述 job varchar(100) 
薪水 salary double(15,2)
部门编号 dep_id int

#创建表
mysql> CREATE TABLE bgx.t2(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null, 
sex enum(‘man‘,‘gril‘) default ‘man‘ not null, 
time date not null, 
post varchar(50) not null, 
job varchar(100), 
salary double(15,2) not null,
office int, 
dep_id int );
 
#插入数据
mysql> insert into bgx.t2(name,sex,time,post,job,salary,office,
dep_id) values 
(‘jack‘,‘man‘,‘20180202‘,‘instructor‘,‘teach‘,5000,501,100), 
(‘tom‘,‘man‘,‘20180203‘,‘instructor‘,‘teach‘,5500,501,100),
(‘robin‘,‘man‘,‘20180202‘,‘instructor‘,‘teach‘,8000,501,100),
(‘alice‘,‘gril‘,‘20180202‘,‘instructor‘,‘teach‘,7200,501,100), 
(‘bgx‘,‘man‘,‘20180202‘,‘hr‘,‘hrcc‘,600,502,101),
(‘harry‘,‘man‘,‘20180202‘,‘hr‘, NULL,6000,502,101),
(‘trf‘,‘gril‘,‘20180206‘,‘sale‘,‘salecc‘,20000,503,102),
(‘test‘,‘gril‘,‘20180205‘,‘sale‘,‘salecc‘,2200,503,102),
(‘dog‘,‘man‘,‘20180205‘,‘sale‘, NULL,2200,503,102),
(‘alex‘,‘man‘,‘20180205‘,‘sale‘,‘‘,2200,503,102);

1.简单查询

//查看表字段与表信息
mysql> desc t2;
+--------+--------------------+------+-----+---------+----------------+
| Field  | Type               | Null | Key | Default | Extra          |
+--------+--------------------+------+-----+---------+----------------+
| id     | int(11)            | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30)        | NO   |     | NULL    |                |
| sex    | enum(‘man‘,‘gril‘) | NO   |     | man     |                |
| time   | date               | NO   |     | NULL    |                |
| post   | varchar(50)        | NO   |     | NULL    |                |
| job    | varchar(100)       | YES  |     | NULL    |                |
| salary | double(15,2)       | NO   |     | NULL    |                |
| office | int(11)            | YES  |     | NULL    |                |
| dep_id | int(11)            | YES  |     | NULL    |                |
+--------+--------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
 
#1.查询所有数据
mysql> select * from t2;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  1 | jack  | man  | 2018-02-02 | instructor | teach  |  5000.00 |    501 |    100 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  5 | bgx   | man  | 2018-02-02 | hr         | hrcc   |   600.00 |    502 |    101 |
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
|  8 | test  | gril | 2018-02-05 | sale       | salecc |  2200.00 |    503 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale       | NULL   |  2200.00 |    503 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale       |        |  2200.00 |    503 |    102 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)
 
#2.指定字段查询
mysql> select name,salary,dep_id from t2;
+-------+----------+--------+
| name  | salary   | dep_id |
+-------+----------+--------+
| jack  |  5000.00 |    100 |
| tom   |  5500.00 |    100 |
| robin |  8000.00 |    100 |
| alice |  7200.00 |    100 |
| bgx   |   600.00 |    101 |
| harry |  6000.00 |    101 |
| trf   | 20000.00 |    102 |
| test  |  2200.00 |    102 |
| dog   |  2200.00 |    102 |
| alex  |  2200.00 |    102 |
+-------+----------+--------+
10 rows in set (0.00 sec)
 
#3.避免重复查询字段distinct
mysql> select post from t2;
+------------+
| post       |
+------------+
| instructor |
| instructor |
| instructor |
| instructor |
| hr         |
| hr         |
| sale       |
| sale       |
| sale       |
| sale       |
+------------+
10 rows in set (0.00 sec)
 
mysql> select distinct post from t2;
+------------+
| post       |
+------------+
| instructor |
| hr         |
| sale       |
+------------+
3 rows in set (0.00 sec)
 
#4.通过四则运算查询, 计算每个人的年薪
mysql> select name,salary,salary*14 from t2;
+-------+----------+-----------+
| name  | salary   | salary*14 |
+-------+----------+-----------+
| jack  |  5000.00 |  70000.00 |
| tom   |  5500.00 |  77000.00 |
| robin |  8000.00 | 112000.00 |
| alice |  7200.00 | 100800.00 |
| bgx   |   600.00 |   8400.00 |
| harry |  6000.00 |  84000.00 |
| trf   | 20000.00 | 280000.00 |
| test  |  2200.00 |  30800.00 |
| dog   |  2200.00 |  30800.00 |
| alex  |  2200.00 |  30800.00 |
+-------+----------+-----------+
10 rows in set (0.00 sec)
 
#计算年薪并定义输出字段信息别名, AS可去掉
mysql> select name,salary,salary*14 AS Annual_salary from t2;
+-------+----------+---------------+
| name  | salary   | Annual_salary |
+-------+----------+---------------+
| jack  |  5000.00 |      70000.00 |
| tom   |  5500.00 |      77000.00 |
| robin |  8000.00 |     112000.00 |
| alice |  7200.00 |     100800.00 |
| bgx   |   600.00 |       8400.00 |
| harry |  6000.00 |      84000.00 |
| trf   | 20000.00 |     280000.00 |
| test  |  2200.00 |      30800.00 |
| dog   |  2200.00 |      30800.00 |
| alex  |  2200.00 |      30800.00 |
+-------+----------+---------------+
10 rows in set (0.01 sec)
 
#5.定义显示格式 CONCAT() 函数用于连接字符串
mysql> select concat(name,‘annual salary:‘,salary*14) from t2;
+-----------------------------------------+
| concat(name,‘annual salary:‘,salary*14) |
+-----------------------------------------+
| jackannual salary:70000.00              |
| tomannual salary:77000.00               |
| robinannual salary:112000.00            |
| aliceannual salary:100800.00            |
| bgxannual salary:8400.00                |
| harryannual salary:84000.00             |
| trfannual salary:280000.00              |
| testannual salary:30800.00              |
| dogannual salary:30800.00               |
| alexannual salary:30800.00              |
+-----------------------------------------+
10 rows in set (0.00 sec)

2.单条件查询

单条件查询
多条件查询
关键字 BETWEEN AND
关键字 IS NULL
关键字 IN 集合查询
关键字 LIKE 模糊查询

#1.单条件查询
mysql> select name,post from t2 where post=‘hr‘;
+-------+------+
| name  | post |
+-------+------+
| bgx   | hr   |
| harry | hr   |
+-------+------+
2 rows in set (0.00 sec)
 
#2.多条件查询
mysql> select name,post,salary from t2 where post=‘hr‘ and salary >5000;
+-------+------+---------+
| name  | post | salary  |
+-------+------+---------+
| harry | hr   | 6000.00 |
+-------+------+---------+
1 row in set (0.00 sec)
 
#3.查找薪资范围在8000-2000,使用BETWEEN区间
mysql> select name,salary from t2 where salary between 8000 and 20000;
+-------+----------+
| name  | salary   |
+-------+----------+
| robin |  8000.00 |
| trf   | 20000.00 |
+-------+----------+
2 rows in set (0.00 sec)
 
#4.查找部门为Null, 没有部门的员工
mysql> select name,job from t2 where job is null;
+-------+------+
| name  | job  |
+-------+------+
| harry | NULL |
| dog   | NULL |
+-------+------+
2 rows in set (0.00 sec)
 
#查找有部门的员工
mysql> select name,job from t2 where job is not null;
+-------+--------+
| name  | job    |
+-------+--------+
| jack  | teach  |
| tom   | teach  |
| robin | teach  |
| alice | teach  |
| bgx   | hrcc   |
| trf   | salecc |
| test  | salecc |
| alex  |        |
+-------+--------+
8 rows in set (0.00 sec)
 
#查看部门为空的员工
mysql> select name,job from t2 where job=‘‘;
+------+------+
| name | job  |
+------+------+
| alex |      |
+------+------+
1 row in set (0.00 sec)
 
#5.集合查询
mysql> select name,salary from t2 where salary=4000 OR salary=5000 OR salary=8000;
mysql> select name,salary from t2 where salary in(4000,5000,8000);
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| robin | 8000.00 |
+-------+---------+
2 rows in set (0.01 sec)
 
#6.模糊查询like, 通配符%
mysql> select * from t2 where name like ‘al%‘;
+----+-------+------+------------+------------+-------+---------+--------+--------+
| id | name  | sex  | time       | post       | job   | salary  | office | dep_id |
+----+-------+------+------------+------------+-------+---------+--------+--------+
|  4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 |    501 |    100 |
| 10 | alex  | man  | 2018-02-05 | sale       |       | 2200.00 |    503 |    102 |
+----+-------+------+------------+------------+-------+---------+--------+--------+
2 rows in set (0.00 sec)
 
#通配符__
mysql> select * from t2 where name like ‘al__[31] ‘;
+----+------+-----+------------+------+------+---------+--------+--------+
| id | name | sex | time       | post | job  | salary  | office | dep_id |
+----+------+-----+------------+------+------+---------+--------+--------+
| 10 | alex | man | 2018-02-05 | sale |      | 2200.00 |    503 |    102 |
+----+------+-----+------------+------+------+---------+--------+--------+
1 row in set (0.00 sec)

3.查询排序

单列排序
多列排序

#1.按单列排序, 按薪水从低到高排序, 默认ASC
mysql> select * from t2 ORDER BY salary ASC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  5 | bgx   | man  | 2018-02-02 | hr         | hrcc   |   600.00 |    502 |    101 |
|  8 | test  | gril | 2018-02-05 | sale       | salecc |  2200.00 |    503 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale       | NULL   |  2200.00 |    503 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale       |        |  2200.00 |    503 |    102 |
|  1 | jack  | man  | 2018-02-02 | instructor | teach  |  5000.00 |    501 |    100 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)
 
#1.按单列排序, 薪水从低往高排序, DESC倒序
mysql> select * from t2 ORDER BY salary DESC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
|  1 | jack  | man  | 2018-02-02 | instructor | teach  |  5000.00 |    501 |    100 |
|  8 | test  | gril | 2018-02-05 | sale       | salecc |  2200.00 |    503 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale       | NULL   |  2200.00 |    503 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale       |        |  2200.00 |    503 |    102 |
|  5 | bgx   | man  | 2018-02-02 | hr         | hrcc   |   600.00 |    502 |    101 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)
 
#2.多列排序, 先按入职时间，再按薪水排序
mysql> select * from t2 ORDER BY time DESC, salary ASC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
|  8 | test  | gril | 2018-02-05 | sale       | salecc |  2200.00 |    503 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale       | NULL   |  2200.00 |    503 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale       |        |  2200.00 |    503 |    102 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
|  5 | bgx   | man  | 2018-02-02 | hr         | hrcc   |   600.00 |    502 |    101 |
|  1 | jack  | man  | 2018-02-02 | instructor | teach  |  5000.00 |    501 |    100 |
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)
 
#2.多列排序, 先按职位，再按薪水排序
mysql> select * from t2 ORDER BY post, salary DESC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  5 | bgx   | man  | 2018-02-02 | hr         | hrcc   |   600.00 |    502 |    101 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
|  1 | jack  | man  | 2018-02-02 | instructor | teach  |  5000.00 |    501 |    100 |
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
|  8 | test  | gril | 2018-02-05 | sale       | salecc |  2200.00 |    503 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale       | NULL   |  2200.00 |    503 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale       |        |  2200.00 |    503 |    102 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)

4.限制查询的记录数

#查询薪资最高前5名同事, 默认初始位置为0
mysql> select * from t2 ORDER BY salary DESC limit 5;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
5 rows in set (0.00 sec)
 
#从第4条开始, 并显示5条数据[32] 
mysql> select * from t2 ORDER BY salary DESC limit 3,5;
+----+-------+------+------------+------------+--------+---------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary  | office | dep_id |
+----+-------+------+------------+------------+--------+---------+--------+--------+
|  6 | harry | man  | 2018-02-02 | hr         | NULL   | 6000.00 |    502 |    101 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  | 5500.00 |    501 |    100 |
|  1 | jack  | man  | 2018-02-02 | instructor | teach  | 5000.00 |    501 |    100 |
| 10 | alex  | man  | 2018-02-05 | sale       |        | 2200.00 |    503 |    102 |
|  8 | test  | gril | 2018-02-05 | sale       | salecc | 2200.00 |    503 |    102 |
+----+-------+------+------------+------------+--------+---------+--------+--------+

5.使用集合函数查询

#统计当前表总共多少条数据
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)
 
#统计dep_id为101有多少条数据
mysql> select count(*) from t2 where dep_id=101;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
 
#薪水最高
mysql> select MAX(salary) from t2;
+-------------+
| MAX(salary) |
+-------------+
|    20000.00 |
+-------------+
1 row in set (0.00 sec)
 
#薪水最低
mysql> select min(salary) from t2;
+-------------+
| min(salary) |
+-------------+
|      600.00 |
+-------------+
1 row in set (0.00 sec)
 
#平均薪水
mysql> select avg(salary) from t2;
+-------------+
| avg(salary) |
+-------------+
| 5890.000000 |
+-------------+
1 row in set (0.00 sec)
 
#总共发放多少薪水
mysql> select sum(salary) from t2;
+-------------+
| sum(salary) |
+-------------+
|    58900.00 |
+-------------+
1 row in set (0.00 sec)
 
#hr部门发放多少薪水
mysql> select sum(salary) from t2 where post=‘hr‘;
+-------------+
| sum(salary) |
+-------------+
|     6600.00 |
+-------------+
1 row in set (0.00 sec)
 
#哪个部门哪个人薪水最高
mysql> select * from t2 where salary=(select max(salary) from t2);
+----+------+------+------------+------+--------+----------+--------+--------+
| id | name | sex  | time       | post | job    | salary   | office | dep_id |
+----+------+------+------------+------+--------+----------+--------+--------+
|  7 | trf  | gril | 2018-02-06 | sale | salecc | 20000.00 |    503 |    102 |
+----+------+------+------------+------+--------+----------+--------+--------+
1 row in set (0.01 sec)

6.分组查询

# GROUP BY 和 GROUP_CONCAT()函数一起使用
mysql> select post,GROUP_CONCAT(name) from t2 GROUP BY post;
+------------+----------------------+
| post       | GROUP_CONCAT(name)   |
+------------+----------------------+
| hr         | bgx,harry            |
| instructor | jack,tom,robin,alice |
| sale       | trf,test,dog,alex    |
+------------+----------------------+
3 rows in set (0.00 sec)
 
mysql> select post,GROUP_CONCAT(name) AS Group_Post from t2 GROUP BY post;
+------------+----------------------+
| post       | Group_Post           |
+------------+----------------------+
| hr         | bgx,harry            |
| instructor | jack,tom,robin,alice |
| sale       | trf,test,dog,alex    |
+------------+----------------------+
3 rows in set (0.00 sec)
 
#GROUP BY 和集合函数一起使用
mysql> select post,sum(salary) from t2 GROUP BY post;
+------------+-------------+
| post       | sum(salary) |
+------------+-------------+
| hr         |     6600.00 |
| instructor |    25700.00 |
| sale       |    26600.00 |
+------------+-------------+
3 rows in set (0.00 sec)

7.使用正则表达式查询

mysql> select * from t2 where name REGEXP ‘^ali‘;
+----+-------+------+------------+------------+-------+---------+--------+--------+
| id | name  | sex  | time       | post       | job   | salary  | office | dep_id |
+----+-------+------+------------+------------+-------+---------+--------+--------+
|  4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 |    501 |    100 |
+----+-------+------+------------+------------+-------+---------+--------+--------+
1 row in set (0.00 sec)
 
mysql> select * from t2 where name REGEXP ‘gx$‘;
+----+------+-----+------------+------+------+--------+--------+--------+
| id | name | sex | time       | post | job  | salary | office | dep_id |
+----+------+-----+------------+------+------+--------+--------+--------+
|  5 | bgx  | man | 2018-02-02 | hr   | hrcc | 600.00 |    502 |    101 |
+----+------+-----+------------+------+------+--------+--------+--------+
1 row in set (0.00 sec)

对字符串匹配方式

WHERE name = ‘trf‘;
WHERE name LIKE ‘ha%‘;
WHERE name REGEXP ‘gx$‘;

4.2多表查询

多表连接查询 
复合条件连接查询
子查询

准备2张数据表

#准备表1
mysql> create  table bgx.t3( 
id int auto_increment primary key not null,
name varchar(50),
age int,
dep_id int
);
#为表1插入数据
mysql> insert into t3(name,age,dep_id) values 
(‘bgx‘,18,200),
(‘tom‘,26,201),
(‘jack‘,30,201),
(‘alice‘,24,202),
(‘robin‘,40,‘200‘),
(‘natasha‘,28,204);
mysql> select * from t3;
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | bgx     |   18 |    200 |
|  2 | tom     |   26 |    201 |
|  3 | jack    |   30 |    201 |
|  4 | alice   |   24 |    202 |
|  5 | robin   |   40 |    200 |
|  6 | natasha |   28 |    204 |
+----+---------+------+--------+
6 rows in set (0.00 sec)
 
#准备表2
mysql> create table t4(
dep_id int, 
dept_name varchar(100)
);
 
#为表2插入数据
mysql> insert into t4 values
(200,‘hr‘),
(201,‘it‘),
(202,‘xs‘),
(203,‘cw‘);
mysql> select * from t4;
+--------+-----------+
| dep_id | dept_name |
+--------+-----------+
|    200 | hr        |
|    201 | it        |
|    202 | xs        |
|    203 | cw        |
+--------+-----------+
4 rows in set (0.00 sec)

1.交叉连接, 不使用任何匹配条件

mysql> select t3.name,t3.age,t3.dep_id,t4.dept_name from t3,t4;
+---------+------+--------+-----------+
| name    | age  | dep_id | dept_name |
+---------+------+--------+-----------+
| bgx     |   18 |    200 | hr        |
| bgx     |   18 |    200 | it        |
| bgx     |   18 |    200 | xs        |
| bgx     |   18 |    200 | cw        |
| tom     |   26 |    201 | hr        |
| tom     |   26 |    201 | it        |
| tom     |   26 |    201 | xs        |
| tom     |   26 |    201 | cw        |
| jack    |   30 |    201 | hr        |
| jack    |   30 |    201 | it        |
| jack    |   30 |    201 | xs        |
| jack    |   30 |    201 | cw        |
| alice   |   24 |    202 | hr        |
| alice   |   24 |    202 | it        |
| alice   |   24 |    202 | xs        |
| alice   |   24 |    202 | cw        |
| robin   |   40 |    200 | hr        |
| robin   |   40 |    200 | it        |
| robin   |   40 |    200 | xs        |
| robin   |   40 |    200 | cw        |
| natasha |   28 |    204 | hr        |
| natasha |   28 |    204 | it        |
| natasha |   28 |    204 | xs        |
| natasha |   28 |    204 | cw        |
+---------+------+--------+-----------+
24 rows in set (0.00 sec)

2.内连接, 只连接匹配的行

# 只找出有部门的员工, (部门表中没有natasha所在的部门)
mysql> select t3.id,t3.name,t3.age,t4.dep_id,t4.dept_name from t3,t4 
where t3.dep_id=t4.dep_id;
+----+-------+------+--------+-----------+
| id | name  | age  | dep_id | dept_name |
+----+-------+------+--------+-----------+
|  1 | bgx   |   18 |    200 | hr        |
|  2 | tom   |   26 |    201 | it        |
|  3 | jack  |   30 |    201 | it        |
|  4 | alice |   24 |    202 | xs        |
|  5 | robin |   40 |    200 | hr        |
+----+-------+------+--------+-----------+
5 rows in set (0.00 sec)

3.外连接

SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

#左连接
mysql> select id,name,t4.dept_name from t3 left join t4 on t3.dep_id = t4.dep_id;
+----+---------+-----------+
| id | name    | dept_name |
+----+---------+-----------+
|  1 | bgx     | hr        |
|  5 | robin   | hr        |
|  2 | tom     | it        |
|  3 | jack    | it        |
|  4 | alice   | xs        |
|  6 | natasha | NULL      |
+----+---------+-----------+
6 rows in set (0.00 sec)
 
#右连接
mysql> select id,name,t4.dept_name from t3 right join t4 on t3.dep_id = t4.dep_id;
+------+-------+-----------+
| id   | name  | dept_name |
+------+-------+-----------+
|    1 | bgx   | hr        |
|    2 | tom   | it        |
|    3 | jack  | it        |
|    4 | alice | xs        |
|    5 | robin | hr        |
| NULL | NULL  | cw        |
+------+-------+-----------+
6 rows in set (0.00 sec)

4.符合条件连接查询

#1.以内连接的方式查询 t3和t4表, 找出公司所有部门中年龄大于25岁的员工
mysql> select t3.id,t3.name,t3.age,t4.dept_name 
from t3,t4 
where t3.dep_id = t4.dep_id 
and age >25;
+----+-------+------+-----------+
| id | name  | age  | dept_name |
+----+-------+------+-----------+
|  5 | robin |   40 | hr        |
|  2 | tom   |   26 | it        |
|  3 | jack  |   30 | it        |
+----+-------+------+-----------+
3 rows in set (0.01 sec)
 
#以内连接的方式查询 t3和t4表，并且以age字段降序显示
mysql> select t3.id,t3.name,t3.age,t4.dept_name 
    -> from t3,t4 
    -> where t3.dep_id = t4.dep_id 
    -> ORDER BY age DESC;
+----+-------+------+-----------+
| id | name  | age  | dept_name |
+----+-------+------+-----------+
|  5 | robin |   40 | hr        |
|  3 | jack  |   30 | it        |
|  2 | tom   |   26 | it        |
|  4 | alice |   24 | xs        |
|  1 | bgx   |   18 | hr        |
+----+-------+------+-----------+
5 rows in set (0.00 sec)

4.子查询

子查询是将一个查询语句嵌套在另一个查询语句中。 
内层查询语句的查询结果，可以为外层查询语句提供查询条件。 
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字 还可以包含比较运算符：= 、 !=、> 、<等

#带 IN 关键字的子查询查询t3表，但dept_id必须在t4表中出现过
mysql> select * from t3 where dep_id IN (select dep_id from t4);
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | bgx   |   18 |    200 |
|  2 | tom   |   26 |    201 |
|  3 | jack  |   30 |    201 |
|  4 | alice |   24 |    202 |
|  5 | robin |   40 |    200 |
+----+-------+------+--------+
5 rows in set (0.00 sec)
 
#代表运算符子查询, 查询年龄大于等于 25 岁员工所在部门（查询老龄化的部门）
mysql> select dep_id,dept_name from t4
where dep_id IN
(select DISTINCT dep_id from t3 where age >=25);
+--------+-----------+
| dep_id | dept_name |
+--------+-----------+
|    201 | it        |
|    200 | hr        |
+--------+-----------+
2 rows in set (0.01 sec)
 
 
#子查询 EXISTS 关字键字表示存在。在使用 EXISTS 关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。
#Ture 或 False，当返回 Ture 时，外层查询语句将进行查询；当返回值为 False 时，外层查询语句不进行查询
 
#t4 表中存在 dep_id=203，Ture
mysql> select * from t3
where EXISTS (select * from t4 where dep_id=203);
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | bgx     |   18 |    200 |
|  2 | tom     |   26 |    201 |
|  3 | jack    |   30 |    201 |
|  4 | alice   |   24 |    202 |
|  5 | robin   |   40 |    200 |
|  6 | natasha |   28 |    204 |
+----+---------+------+--------+
6 rows in set (0.00 sec)
 
mysql> select * from t3 where EXISTS (select * from t4 where dep_id=300);
Empty set (0.00 sec)

 

四、数据库视图操作

目录：

MySQL视图是一个虚拟表，内容由select查询语句定义, 同真实的表数据一致, 但是视图并不在数据库中以存储的数据值形式存在。
视图引用自定义查询表的字段, 并且在引用试图时动态生成, 对其所引用的基础表来说MySQL视图的作用类似于筛选。

  • 1.定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。
  • 2.视图查询没有任何限制，通过它们进行数据修改时的限制也很少。
  • 3.视图是存储在数据库中的SQL查询语句，它主要出于两种原因：
    • 1.安全原因, 视图可以隐藏一些敏感的信息。
    • 2.简化查询, 使复杂的查询易于理解和使用。

1.创建视图

1.创建单表视图

CREATE VIEW 视图名 AS SELECT 语句;

mysql> use bgx;
mysql> create view u_grant AS select user,host,authentication_string from mysql.user;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from u_grant;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

2.创建多表视图环境准备

mysql> create database shop;
mysql> use shop;
 
#创建产品表
mysql> create table product(
id int unsigned auto_increment primary key not null,
name varchar(60) not null,
price double not null
);
#插入产品数据
mysql> insert into product(name,price) values
(‘apple‘,5),
(‘balane‘,6),
(‘pear‘,7);
 
#创建销售表
mysql> create table purchase(
id int unsigned auto_increment primary key not null,
name varchar(60) not null,
quantity int not null default 0,
gen_time datetime not null);
#插入销售数据
mysql> insert into purchase(name,quantity,gen_time) values
(‘apple‘,7,now()),
(‘pear‘,10,now());
 
#查询产品卖出金额
mysql> select product.name,product.price,
purchase.quantity,
product.price * purchase.quantity as total_value
from product,purchase 
where product.name = purchase.name;
+-------+-------+----------+-------------+
| name  | price | quantity | total_value |
+-------+-------+----------+-------------+
| apple |     5 |        7 |          35 |
| pear  |     7 |       10 |          70 |
+-------+-------+----------+-------------+
2 rows in set (0.00 sec)

3.创建多表视图

mysql> create view totol_product AS 
select product.name,product.price,purchase.quantity,
product.price * purchase.quantity AS Total 
from purchase,product  
where purchase.name = product.name;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from totol_product;
+-------+-------+----------+-------+
| name  | price | quantity | Total |
+-------+-------+----------+-------+
| apple |     5 |        7 |    35 |
| pear  |     7 |       10 |    70 |
+-------+-------+----------+-------+
2 rows in set (0.00 sec)
 
#再次卖出产品后
mysql> insert into purchase(name,quantity,gen_time) values
(‘balane‘,20,now());
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from totol_product;
+--------+-------+----------+-------+
| name   | price | quantity | Total |
+--------+-------+----------+-------+
| apple  |     5 |        7 |    35 |
| balane |     6 |       20 |   120 |
| pear   |     7 |       10 |    70 |
+--------+-------+----------+-------+
3 rows in set (0.00 sec)

2.查看视图

  • 1.查看视图名
    • SHOW TABLES 视图名;
  • 2.查看视图详细信息
    • SHOW TABLE STATUS FROM 视图名\G
  • 3.查看视频定义信息
    • SHOW CREATE VIEW 视图名\G
  • 4.查看视图表结构
    • DESC 视图名;

3.修改视图

1.删除后新创建

mysql> drop view bgx.u_grant;
mysql> create view bgx.u_grant AS
select user,host from mysql.user;
 
mysql> select * from bgx.u_grant;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.01 sec)

2.使用alter修改视图

mysql> alter view bgx.u_grant AS
select user,host,authentication_string from mysql.user;
mysql> select * from bgx.u_grant;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

4.删除视图

语法DROP VIEW view_name [,view_name]…;

mysql> use bgx;
mysql> drop view u_grant;
Query OK, 0 rows affected (0.00 sec)

 

五、数据库索引操作

目录：

1.索引概述

索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

2.索引分类

普通索引INDEX: 最基本的索引，没有任何限制
唯一索引UNIQUE: 与"普通索引"类似，不同的是索引列的值必须唯一，但允许有空值。
全文索引FULLTEXT：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
主键索引PRIMARY KEY:它 是一种特殊的唯一索引，不允许有空值。

3.索引环境

1.准备表

mysql> create table t5 (id int, name varchar(30));
Query OK, 0 rows affected (0.02 sec)
 
mysql> desc t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.使用存储过程(函数), 批量插入数据

1.创建存储过程
mysql> delimiter $$
mysql> create procedure autoinsert()
BEGIN
declare i int default 1;
while (i<200000)do
insert into bgx.t5 values(i,‘bgx‘);
set i = i+1;
end while;
END $$
mysql> delimiter ;
 
//2.查看存储过程
mysql> show procedure status\G
mysql> show create procedure autoinsert\G
*************************** 1. row ***************************
           Procedure: autoinsert
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert`()
BEGIN
declare i int default 1;
while (i<200000)do
insert into bgx.t5 values(i,‘bgx‘);
set i = i+1;
end while;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
 
3.调用存储过程,执行
mysql> call autoinsert();

4.索引创建

1.创建表时创建索引

语法：CREATE TABLE 表名 (字段名 数据类型 [完整性约束条件…],[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]));

//1.创建普通索引示例
CREATE TABLE tt ( id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
‘INDEX‘ index_tt_name (name) );
 
//2.创建唯一索引示例
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
‘UNIQUE INDEX‘ index_tt_name (name) );
 
//3.创建全文索引示例myisam
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
log text,
‘FULLTEXT INDEX‘ index_tt_log (log);
 
//3.创建多列索引示例
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
‘INDEX‘ index_tt_name_comment (name, comment));

2.在已存在的表上创建索引

语法:CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]);

//1.创建普通索引示例
CREATE ‘INDEX‘ index_name ON product(name);
 
//2.创建唯一索引示例
CREATE ‘UNIQUE INDEX‘ index_name ON product(name);
 
//3.创建全文索引示例
CREATE ‘FULLTEXT INDEX‘ index_dept_name ON product (name);
 
//4.创建多列索引示例
CREATE ‘INDEX index_dept_name_comment‘ ON product (name, id);

5.索引测试

1.未建立索引

//花费时长
mysql> select * from t5 where id=199999;
+--------+------+
| id     | name |
+--------+------+
| 199999 | bgx  |
+--------+------+
1 row in set (0.08 sec)
 
//explain查看查询优化器如何决定执行查询
mysql> explain select * from t5 where id=199999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 199949
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
 
 

2.建立索引

//对id字段进行索引创建
mysql> create index index_t5_id on bgx.t5(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
//索引后花费时长
mysql> select * from t5 where id=199999;
+--------+------+
| id     | name |
+--------+------+
| 199999 | bgx  |
+--------+------+
1 row in set (0.00 sec)
 
//建立索引后, 再次查看查询优化器如何执行查询
mysql> explain select * from t5 where id=200000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
   partitions: NULL
         type: ref
possible_keys: index_t5_id
          key: index_t5_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

6.索引管理

1.查看索引

SHOW CRETAE TABLE 表名\G
EXPLAIN SELECT * FROM t5 WHERE id=‘19999‘;

2.删除索引

//查看索引名称
mysql> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `index_t5_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
//删除索引语法: DROP INDEX 索引名 ON 表名
mysql> drop index index_t5_id on t5;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

 [31]两个”_”下划线组成

 [32]把薪水从高到底排序，然后查询从第四开始后的5条数据

Mysql 基础

原文：https://www.cnblogs.com/flytor/p/11415097.html
踩
(0)
赞
(0)
   
举报
评论 一句话评论（0）
分享档案
更多>
2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)
最新文章
更多>
教程昨日排行
更多>
友情链接
汇智网   PHP教程   插件网  
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣，分享程序人生！