一、数据库基础介绍
目录:
数据库概述
数据的存储方式
数据库技术构成
数据库类型划分
SQL语言(结构化查询语言)
数据访问方式
数据库运维
目录
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)在服务器初始启动时,如果服务器的数据目录为空,则会发生以下情况:
超级用户的密码被设置并存储在错误日志文件中。要显示它,请使用以下命令:
[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:验证长度、数字、大小写、特殊字符、字典文件;
采用二进制免编译方式安装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
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
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>
目录:
系统数据库:
使用mysql -u root -p
可以连接数据库, 但这只是本地连接数据库的方式, 在生产很多情况下都是连接网络中某一个主机上的数据库
-P //指定连接远程数据库端口
-h //指定连接远程数据库地址
-u //指定连接远程数据库账户
-p //指定连接远程数据库密码
[root
@sql~]
# mysql -uroot -p
Enter
password:
Bgx123.com
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)
在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)
在学习查询前, 需要定义好对应数据进行查询
编号 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$‘;
多表连接查询
复合条件连接查询
子查询
准备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
视图的作用类似于筛选。
SQL
查询语句,它主要出于两种原因: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)
SHOW TABLES
视图名;SHOW TABLE STATUS FROM
视图名\GSHOW CREATE VIEW
视图名\GDESC
视图名;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)
语法DROP VIEW view_name [,view_name]…;
mysql>
usebgx
;
mysql> drop view u_grant;
Query OK,
0rows affected (
0.00sec)
目录:
索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书1000页,有500也是目录,它当然效率低,目录是要占纸张的,而索引是要占磁盘空间的。
普通索引INDEX: 最基本的索引,没有任何限制
唯一索引UNIQUE:
与"普通索引"类似,不同的是索引列的值必须唯一,但允许有空值。
全文索引FULLTEXT:仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时好空间。
主键索引PRIMARY
KEY:它
是一种特殊的唯一索引,不允许有空值。
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();
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);
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)
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
原文:https://www.cnblogs.com/flytor/p/11415097.html