Mysql 基础
一、数据库基础介绍
目录:
数据库概述
数据的存储方式
- 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
isgenerated
forroot
@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二进制包下载地址
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
@sqlsrc]
# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root
@sqlsrc]
# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /soft
[root
@sqlsrc]
# 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
isgenerated fo
r'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
2659mysql
16u IPv6
284310
t0 TCP *:mysql (LISTEN)
[root
@sql~]
# ss -lntup|grep 3306
tcp LISTEN
080
:::
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
thisstatement.
//修改系统默认密码
mysql> alter user root@
'localhost'identified by
' Bgx123.com ';
Query OK,
0rows 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
inplain text, use ssl connection to ensure password safety.
[root@sql ~]
# mysql -uroot -pBgx123.com
mysql>
//第二种方式, 登录MySQL, 修改相应表
mysql> update mysql.user
setauthentication_string=password(
'Bgx123.com')
whereuser=
'root';
mysql> flush privileges;
2.忘记mysql root
密码
[root@sql ~]
# vim /etc/my.cnf
[mysqld]
skip-grant-tables
# 新增跳过授权表
//重启数据库生效
[root@sql ~]
# systemctl restart mysqld
//查看表字段
mysql>
selectuser,host,authentication_string
frommysql.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')
whereuser=
'root';
mysql> exit
//5.7.6版本前更新密码方式
mysql> update mysql.user
setpassword=password(
'Bgx123.com')
whereuser=
"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|
+
-----------+
1row
inset
(
0.01sec)
2.创建数据库DDL
mysql> create database Bgx_edu;
Query OK,
1row affected (
0.00sec)
//以分号结尾
注意
数据库名称严格区分大小写
数据库名称必须是唯一
数据库名称不允许使用数字
数据库名称不能使用关键字命名create
select
3.查看当前的库内容
mysql> show databases;
+
--------------------+
| Database |
+
--------------------+
| information_schema |
| Bgx_edu |
| mysql |
| performance_schema |
| sys |
+
--------------------+
5rows
inset (
0.00sec)
//
执行命令不区分大小写
mysql> SHOW DATABASES;
+
--------------------+
| Database |
+
--------------------+
| information_schema |
| Bgx_edu |
| mysql |
| performance_schema |
| sys |
+
--------------------+
5rows
inset (
0.00sec)
4.删除数据库
mysql> drop database Bgx_edu;
Query OK,
0rows affected (
0.07sec)
mysql> show databases;
+
--------------------+
| Database |
+
--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+
--------------------+
4rows
inset (
0.00sec)
//
删除库下的表
mysql> drop
tableBgx_edu.t1;
5.查询某个库的表
//use进入对应库
mysql>
useBgx_edu
;
Database changed
//列出当前库下面的表
mysql> show tables;
Emptyset (
0.00sec)
//查询某个库下的表结构
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| |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
12rows in set (
0.00sec)
//查看某张表的建表语句
mysql> show create table mysql.slow_log\G
***************************
1.row ***************************
Table: slow_log
Create Table: CREATE TABLE `slow_log` (
`start_time` timestamp(
6) NOT
NULLDEFAULT
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
DEFAULTCHARSET=utf8 COMMENT=
'Slow log'
1row in set (
0.00sec)
3.数据库增删查改
在MySQL
管理软件中, 可以通过SQL
语句中的DML
语言来实现数据的操作, 包括如下:
INSERT
数据插入
UPDATE
数据更新
DELETE
数据删除
1.准备操作环境数据表
#创建数据文件
mysql> create database bgx;
mysql>
usebgx
;
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| |
+-------+--------------------+------+-----+---------+-------+
4rows in set (
0.00sec)
2.插入数据INSERT
语句
#1.插入完整数据, 顺序插入: INSERT INTO 表名(字段1,字段2,字段n) VALUES (值1,值2,值 n);
mysql> insert
intot1(
id,
name,sex,age) values (
"1",
"bgx",
"man",
"18");
Query OK,
1row affected (
0.01sec)
#1.插入完整数据, 推荐方式 INSERT INTO 表名 VALUES (值1,值2,值n);
mysql> insert
intot1 values(
"2",
"bgx2",
"gril",
"10");
Query OK,
1row affected (
0.01sec)
#2.指定字段插入, INSERT INTO 表名(字段2,字段3…) VALUES (值 2,值3…);
mysql> insert
intot1(
name,sex,age) values (
"bgx",
"man",
"20");
Query OK,
1row affected (
0.00sec)
#3.插入多条记录, INSERT INTO 表名 VALUES (值1,值2,值n),(值1,值2,值n);
mysql> insert
intot1 values
(
"3",
"bgx3",
"man",
"18"),
(
"4",
"bgx4",
"man",
"18"),
(
"5",
"bgx5",
"man",
"18");
Query OK,
3rows affected (
0.00sec)
Records:
3Duplicates:
0Warnings:
0
mysql> select *
fromt1;
+
------+------+------+------+
|
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|
+
------+------+------+------+
6rows
inset
(
0.00sec)
3.更新数据UPDATE
语句
//语法: 更新 表 设置 字段1=值1, 字段2=值2, WHERE 条件;
1.查看需要修改的表的字段 desc
2.查询对应的字段
select
3.更新对应的表字段
update
4.添加对应的where条件,精准修改
//示例1: 将t1表中, name字段等于bgx1的改为update_bgx
mysql> update t1
setname=
"update_bgx"where
name=
"bgx1";
Query OK,
2rows 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")
whereuser=
'root'and host=
'localhost';
Query OK,
0rows affected,
1warning (
0.00sec)
Rows matched:
1Changed:
0Warnings:
1
mysql> flush privileges;
Query OK,
0rows affected (0.00 sec)
4.删除数据DELETE
语法: DELETE FROM 表名 WHERE CONITION;
//删除字段包含update_bgx
mysql> delete
fromt1
wherename=
"update_bgx";
Query OK,
2rows 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,
0rows 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
notnull
,
name varchar(
30)
notnull
,
sex
enum(
'man',
'gril')
default'man'
not
null
,
time date
notnull
,
post varchar(
50)
notnull
,
job varchar(
100),
salary double(
15,
2)
notnull
,
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 | |
+
--------+--------------------+------+-----+---------+----------------+
9rows
inset (
0.00sec)
#
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|
+
----+-------+------+------------+------------+--------+----------+--------+--------+
10rows
inset (
0.00sec)
#
2.指定字段查询
mysql>
selectname,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|
+
-------+----------+--------+
10rows
inset (
0.00sec)
#
3.避免重复查询字段
distinct
mysql>
selectpost from t2;
+
------------+
| post |
+
------------+
| instructor |
| instructor |
| instructor |
| instructor |
| hr |
| hr |
| sale |
| sale |
| sale |
| sale |
+
------------+
10rows
inset (
0.00sec)
mysql>
selectdistinct post from t2;
+
------------+
| post |
+
------------+
| instructor |
| hr |
| sale |
+
------------+
3rows
inset (
0.00sec)
#
4.通过四则运算查询
,
计算每个人的年薪
mysql>
selectname,salary,salary*
14from 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|
+
-------+----------+-----------+
10rows
inset (
0.00sec)
#
计算年薪并定义输出字段信息别名
, AS
可去掉
mysql>
selectname,salary,salary*
14AS 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|
+
-------+----------+---------------+
10rows
inset (
0.01sec)
#
5.定义显示格式
CONCAT()
函数用于连接字符串
mysql>
selectconcat(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|
+
-----------------------------------------+
10rows
inset (
0.00sec)
2.单条件查询
单条件查询
多条件查询
关键字
BETWEEN AND
关键字
IS NULL
关键字
IN 集合查询
关键字
LIKE 模糊查询
#1.单条件查询
mysql> select
name,post
fromt2
wherepost='hr';
+
-------+------+
|
name| post |
+
-------+------+
| bgx | hr |
| harry | hr |
+
-------+------+
2rows
inset
(
0.00sec)
#2.多条件查询
mysql> select
name,post,salary
fromt2
wherepost='hr'
andsalary >
5000;
+
-------+------+---------+
|
name| post | salary |
+
-------+------+---------+
| harry | hr |
6000.00|
+
-------+------+---------+
1row
inset
(
0.00sec)
#3.查找薪资范围在8000-2000,使用BETWEEN区间
mysql> select
name,salary
fromt2
wheresalary
between8000
and
20000
;
+
-------+----------+
|
name| salary |
+
-------+----------+
| robin |
8000.00|
| trf |
20000.00|
+
-------+----------+
2rows
inset
(
0.00sec)
#4.查找部门为Null, 没有部门的员工
mysql> select
name,job
fromt2
wherejob
isnull;
+
-------+------+
|
name| job |
+
-------+------+
| harry | NULL |
| dog | NULL |
+
-------+------+
2rows
inset
(
0.00sec)
#查找有部门的员工
mysql> select
name,job
fromt2
wherejob
isnot
null;
+
-------+--------+
|
name| job |
+
-------+--------+
| jack | teach |
| tom | teach |
| robin | teach |
| alice | teach |
| bgx | hrcc |
| trf | salecc |
| test | salecc |
| alex | |
+
-------+--------+
8rows
inset
(
0.00sec)
#查看部门为空的员工
mysql> select
name,job
fromt2
wherejob='';
+
------+------+
|
name| job |
+
------+------+
| alex | |
+
------+------+
1row
inset
(
0.00sec)
#5.集合查询
mysql> select
name,salary
fromt2
wheresalary=
4000OR salary=
5000OR salary=
8000;
mysql> select
name,salary
fromt2
wheresalary
in(
4000,
5000,
8000);
+
-------+---------+
|
name| salary |
+
-------+---------+
| jack |
5000.00|
| robin |
8000.00|
+
-------+---------+
2rows
inset
(
0.01sec)
#6.模糊查询like, 通配符%
mysql> select *
fromt2
wherename
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|
+
----+-------+------+------------+------------+-------+---------+--------+--------+
2rows
inset
(
0.00sec)
#通配符__
mysql> select *
fromt2
wherename
like 'al__
[31]';
+
----+------+-----+------------+------+------+---------+--------+--------+
|
id|
name| sex |
time| post | job | salary | office | dep_id |
+
----+------+-----+------------+------+------+---------+--------+--------+
|
10| alex | man |
2018-
02-
05| sale | |
2200.00|
503|
102|
+
----+------+-----+------------+------+------+---------+--------+--------+
1row
inset
(
0.00sec)
3.查询排序
单列排序
多列排序
#1.按单列排序, 按薪水从低到高排序, 默认ASC
mysql> select *
fromt2 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|
+
----+-------+------+------------+------------+--------+----------+--------+--------+
10rows
inset
(
0.00sec)
#1.按单列排序, 薪水从低往高排序, DESC倒序
mysql> select *
fromt2 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|
+
----+-------+------+------------+------------+--------+----------+--------+--------+
10rows
inset
(
0.00sec)
#2.多列排序, 先按入职时间,再按薪水排序
mysql> select *
fromt2 ORDER BY
timeDESC, 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|
+
----+-------+------+------------+------------+--------+----------+--------+--------+
10rows
inset
(
0.00sec)
#2.多列排序, 先按职位,再按薪水排序
mysql> select *
fromt2 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|
+
----+-------+------+------------+------------+--------+----------+--------+--------+
10rows
inset
(
0.00sec)
4.限制查询的记录数
#查询薪资最高前5名同事, 默认初始位置为0
mysql> select *
fromt2 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|
+
----+-------+------+------------+------------+--------+----------+--------+--------+
5rows
inset
(
0.00sec)
#从第4条开始, 并显示5条数据[32]
mysql> select *
fromt2 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>
selectcount(*)
fromt2;
+----------+
| count(*) |
+----------+
|
10|
+----------+
1row
inset
(
0.00sec)
#统计dep_id为101有多少条数据
mysql>
selectcount(*)
fromt2
wheredep_id=
101;
+----------+
| count(*) |
+----------+
|
2|
+----------+
1row
inset
(
0.00sec)
#薪水最高
mysql>
selectMAX(salary)
fromt2;
+-------------+
| MAX(salary) |
+-------------+
|
20000.00|
+-------------+
1row
inset
(
0.00sec)
#薪水最低
mysql>
selectmin(salary)
fromt2;
+-------------+
| min(salary) |
+-------------+
|
600.00|
+-------------+
1row
inset
(
0.00sec)
#平均薪水
mysql>
selectavg(salary)
fromt2;
+-------------+
| avg(salary) |
+-------------+
|
5890.000000|
+-------------+
1row
inset
(
0.00sec)
#总共发放多少薪水
mysql>
selectsum(salary)
fromt2;
+-------------+
| sum(salary) |
+-------------+
|
58900.00|
+-------------+
1row
inset
(
0.00sec)
#hr部门发放多少薪水
mysql>
selectsum(salary)
fromt2
wherepost=
'hr';
+-------------+
| sum(salary) |
+-------------+
|
6600.00|
+-------------+
1row
inset
(
0.00sec)
#哪个部门哪个人薪水最高
mysql>
select*
fromt2
wheresalary=(
selectmax(salary)
fromt2);
+----+------+------+------------+------+--------+----------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+------+------+------------+------+--------+----------+--------+--------+
|
7| trf | gril |
2018-
02-
06| sale | salecc |
20000.00|
503|
102|
+----+------+------+------------+------+--------+----------+--------+--------+
1row
inset
(
0.01sec)
6.分组查询
# GROUP BY 和 GROUP_CONCAT()函数一起使用
mysql> select post,GROUP_CONCAT(
name)
fromt2 GROUP BY post;
+
------------+----------------------+
| post | GROUP_CONCAT(
name) |
+
------------+----------------------+
| hr | bgx,harry |
| instructor | jack,tom,robin,alice |
| sale | trf,test,dog,alex |
+
------------+----------------------+
3rows
inset
(
0.00sec)
mysql> select post,GROUP_CONCAT(
name) AS Group_Post
fromt2 GROUP BY post;
+
------------+----------------------+
| post | Group_Post |
+
------------+----------------------+
| hr | bgx,harry |
| instructor | jack,tom,robin,alice |
| sale | trf,test,dog,alex |
+
------------+----------------------+
3rows
inset
(
0.00sec)
#GROUP BY 和集合函数一起使用
mysql> select post,sum(salary)
fromt2 GROUP BY post;
+
------------+-------------+
| post | sum(salary) |
+
------------+-------------+
| hr |
6600.00|
| instructor |
25700.00|
| sale |
26600.00|
+
------------+-------------+
3rows
inset
(
0.00sec)
7.使用正则表达式查询
mysql> select *
fromt2
wherename
REGEXP '^ali';
+
----+-------+------+------------+------------+-------+---------+--------+--------+
|
id|
name| sex |
time| post | job | salary | office | dep_id |
+
----+-------+------+------------+------------+-------+---------+--------+--------+
|
4| alice | gril |
2018-
02-
02| instructor | teach |
7200.00|
501|
100|
+
----+-------+------+------------+------------+-------+---------+--------+--------+
1row
inset
(
0.00sec)
mysql> select *
fromt2
wherename
REGEXP 'gx$';
+
----+------+-----+------------+------+------+--------+--------+--------+
|
id|
name| sex |
time| post | job | salary | office | dep_id |
+
----+------+-----+------------+------+------+--------+--------+--------+
|
5| bgx | man |
2018-
02-
02| hr | hrcc |
600.00|
502|
101|
+
----+------+-----+------------+------+------+--------+--------+--------+
1row
inset
(
0.00sec)
对字符串匹配方式
WHERE name = 'trf';
WHERE name LIKE 'ha%';
WHERE name REGEXP 'gx$';
4.2多表查询
多表连接查询
复合条件连接查询
子查询
准备2张数据表
#准备表1
mysql> create table bgx.t3(
id
intauto_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*
fromt3;
+----+---------+------+--------+
| 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*
fromt4;
+--------+-----------+
| 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
fromt3,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 |
+
---------+------+--------+-----------+
24rows
inset
(
0.00sec)
2.内连接, 只连接匹配的行
# 只找出有部门的员工, (部门表中没有natasha所在的部门)
mysql> select t3.
id,t3.
name,t3.age,t4.dep_id,t4.dept_name
fromt3,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 |
+
----+-------+------+--------+-----------+
5rows
inset
(
0.00sec)
3.外连接
SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
#左连接
mysql> select
id,
name,t4.dept_name
fromt3 left join t4
ont3.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 |
+
----+---------+-----------+
6rows
inset
(
0.00sec)
#右连接
mysql> select
id,
name,t4.dept_name
fromt3 right join t4
ont3.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 |
+
------+-------+-----------+
6rows
inset
(
0.00sec)
4.符合条件连接查询
#1.以内连接的方式查询 t3和t4表, 找出公司所有部门中年龄大于25岁的员工
mysql>
selectt3.id,t3.name,t3.age,t4.dept_name
from t3,t4
where t3.dep_id = t4.dep_id
andage >
25;
+----+-------+------+-----------+
| id | name | age | dept_name |
+----+-------+------+-----------+
|
5| robin |
40| hr |
|
2| tom |
26| it |
|
3| jack |
30| it |
+----+-------+------+-----------+
3rows
inset
(
0.01sec)
#以内连接的方式查询 t3和t4表,并且以age字段降序显示
mysql>
selectt3.id,t3.name,t3.age,t4.dept_name
->
fromt3,t4
->
wheret3.dep_id = t4.dep_id
->
ORDERBY
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 |
+----+-------+------+-----------+
5rows
inset
(
0.00sec)
4.子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果,可以为外层查询语句提供查询条件。
子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字 还可以包含比较运算符:= 、 !=、> 、<等
#带 IN 关键字的子查询查询t3表,但dept_id必须在t4表中出现过
mysql> select *
fromt3
wheredep_id IN (select dep_id
fromt4);
+
----+-------+------+--------+
|
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|
+
----+-------+------+--------+
5rows
inset
(
0.00sec)
#代表运算符子查询, 查询年龄大于等于 25 岁员工所在部门(查询老龄化的部门)
mysql> select dep_id,dept_name
fromt4
where dep_id IN
(select DISTINCT dep_id
fromt3
whereage >=
25);
+
--------+-----------+
| dep_id | dept_name |
+
--------+-----------+
|
201|
it|
|
200| hr |
+
--------+-----------+
2rows
inset
(
0.01sec)
#子查询 EXISTS 关字键字表示存在。在使用 EXISTS 关键字时,内层查询语句不返回查询的记录,而是返回一个真假值。
#Ture 或 False,当返回 Ture 时,外层查询语句将进行查询;当返回值为 False 时,外层查询语句不进行查询
#t4 表中存在 dep_id=203,Ture
mysql> select *
fromt3
whereEXISTS (select *
fromt4
wheredep_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|
+
----+---------+------+--------+
6rows
inset
(
0.00sec)
mysql> select *
fromt3
whereEXISTS (select *
fromt4
wheredep_id=
300);
Empty
set(
0.00sec)
四、数据库视图操作
目录:
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
frommysql.user;
Query OK,
0rows affected (
0.01sec)
mysql> select *
fromu_grant;
+
---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+
---------------+-----------+-------------------------------------------+
| root | localhost | *
4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+
---------------+-----------+-------------------------------------------+
3rows
inset
(
0.00sec)
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>
selectproduct.name,product.price,
purchase.quantity,
product.price * purchase.quantity
astotal_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
wherepurchase.
name= product.
name;
Query OK,
0rows affected (
0.00sec)
mysql> select *
fromtotol_product;
+
-------+-------+----------+-------+
|
name| price | quantity | Total |
+
-------+-------+----------+-------+
| apple |
5|
7|
35|
| pear |
7|
10|
70|
+
-------+-------+----------+-------+
2rows
inset
(
0.00sec)
#再次卖出产品后
mysql> insert
intopurchase(
name,quantity,gen_time) values
('balane',
20,now());
Query OK,
1row affected (
0.00sec)
mysql> select *
fromtotol_product;
+
--------+-------+----------+-------+
|
name| price | quantity | Total |
+
--------+-------+----------+-------+
| apple |
5|
7|
35|
| balane |
6|
20|
120|
| pear |
7|
10|
70|
+
--------+-------+----------+-------+
3rows
inset
(
0.00sec)
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
frommysql.user;
mysql> select *
frombgx.u_grant;
+
---------------+-----------+
| user | host |
+
---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+
---------------+-----------+
3rows
inset
(
0.01sec)
2.使用alter
修改视图
mysql> alter view bgx.u_grant AS
select user,host,authentication_string
frommysql.user;
mysql> select *
frombgx.u_grant;
+
---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+
---------------+-----------+-------------------------------------------+
| root | localhost | *
4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+
---------------+-----------+-------------------------------------------+
3rows
inset
(
0.00sec)
4.删除视图
语法DROP VIEW view_name [,view_name]…;
mysql>
usebgx
;
mysql> drop view u_grant;
Query OK,
0rows affected (
0.00sec)
五、数据库索引操作
目录:
1.索引概述
索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书1000页,有500也是目录,它当然效率低,目录是要占纸张的,而索引是要占磁盘空间的。
2.索引分类
普通索引INDEX: 最基本的索引,没有任何限制
唯一索引UNIQUE:
与"普通索引"类似,不同的是索引列的值必须唯一,但允许有空值。
全文索引FULLTEXT:仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时好空间。
主键索引PRIMARY
KEY:它
是一种特殊的唯一索引,不允许有空值。
3.索引环境
1.准备表
mysql> create table t5 (
idint,
namevarchar(
30));
Query OK,
0rows affected (
0.02sec)
mysql> desc t5;
+
-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+
-------+-------------+------+-----+---------+-------+
|
id| int(
11) | YES | | NULL | |
|
name| varchar(
30) | YES | | NULL | |
+
-------+-------------+------+-----+---------+-------+
2rows
inset
(
0.00sec)
2.使用存储过程(函数), 批量插入数据
1.创建存储过程
mysql> delimiter $$
mysql>
create procedure autoinsert()
BEGIN
declare i int default 1;
while(i<
200000)
do
insert
intobgx.
t5 values(i,'bgx');
seti = 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
intdefault
1
;
while(i<
200000)
do
insert
intobgx.
t5 values(i,'bgx');
seti = 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*
fromt5
whereid=
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
1row
inset
,
1warning (
0.00sec)
2.建立索引
//对id字段进行索引创建
mysql> create index index_t5_id on bgx.t5(id);
Query OK,
0rows 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
1row
inset
,
1warning (
0.00sec)
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