mysql
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含33806字,纯文字阅读大概需要49分钟。
内容图文
![mysql](/upload/InfoBanner/zyjiaocheng/870/255a2fcf2cd74d479a8145aa034ff9ca.jpg)
1.关系型数据库
1.1常用的关系型数据库
- Msyql,MariaDB,Percona-Server
- PosrgreSQL
- Oracle
- MSSQL
1.2专业名词
-SQL:Structure Query Language,结构化查询语言
-约束:
主键约束:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行。且必须提供数据,不能为空(NOT NULL)。一个表只能存在一个
惟一键约束:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行。允许为空(NULL)一个表可以存在多个
外键约束:一个表中的某字段可填入数据取决于另一个表的主键已有的数据
检查性约束
- 索引:将表中的一个或多个字段中的数据复制一份另存,并且这些数据需要按特定次序排序存储
1.3关系型数据库常见组件
- database:数据库
- table:表,由行(row)和列(colum)组成
-index: 索引
-view:视图
-user:用户
-privilege:权限
-procedure:存储过程
-function:存储函数
-trigger:触发器
-event scheduler:事件调度器
1.4SQL语句
- DDL:数据定义语言
- DML:数据操纵语言
- DCL:数据控制语言
SQL语句类型 | 对应操作 |
---|---|
DDL | CREATE:创建 DROP:删 ALTER:修改 |
DML | INSERT:向表中插入数据 DELETE:删除表中数据 PDATE:更新表中数据 SELECT:查询表中数据 |
DCL | GRANT:授权 REVOKE:移除授权 |
2. 数据库安装与配置
MariaDB
- 1.通过yum安装
[root@localhost ~]# yum -y install mariadb mariadb-server
- 2.启动MariaDB服务
##3306端口
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since 二 2020-10-20 22:46:36 CST; 6s ago
Process: 27817 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 27727 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 5 *:902 *:*
LISTEN 0 50 *:3306 *:*
- 3.查看版本
##此时说明安装成功
[root@localhost ~]# mysqladmin --version
mysqladmin Ver 9.0 Distrib 5.5.65-MariaDB, for Linux on x86_64
- 4.运行mysql
##刚开始默认root用户登陆,且密码为空
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
##设置root用户密码
[root@localhost ~]# mysqladmin -u root password xxxxxx
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
3.数据库操作
//语法:mysql [OPTIONS] [database]
//常用的OPTIONS:
-uUSERNAME //指定用户名,默认为root
-hHOST //指定服务器主机,默认为localhost,推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口,这里的#需用实际的端口号代替,如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出,常用于脚本
3.1 DDL操作
- CREATE:创建 DROP:删除 ALTER:修改
3.1.1用户操作
用户帐号由两部分组成,如'USERNAME'@'HOST',表示此USERNAME只能从此HOST上远程登录
HOST用于限制此用户可通过哪些主机远程连接mysql程序,其值可为:IP地址或者是通配符(%和_)
- %:匹配任意长度的任意字符,常用于设置允许从任何主机登录
- _:匹配任意单个字符
创建用户
CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
##创建wisan用户
MariaDB [(none)]> create user 'wisan'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> quit
Bye
##使用wisan用户登陆
[root@localhost ~]# mysql -uwisan -h127.0.0.1 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
删除用户
DROP USER 'username'@'host';
MariaDB [(none)]> drop user 'wisan'@'localhost';
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -uwisan -hlocalhost -p
Enter password:
ERROR 1045 (28000): Access denied for user 'wisan'@'localhost' (using password: YES)
3.1.2数据库操作
创建数据库
CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
##root用户登陆
##其他用户没有给权限则操作不了
MariaDB [(none)]> create database if not exists wisan_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases>;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wisan_db |
+--------------------+
删除数据库
DROP DATABASE [IF EXISTS] 'DB_NAME';
MariaDB [(none)]> drop database if exists wisan_db;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
3.1.3表操作
创建表
CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
##先进入数据库
MariaDB [(none)]> use wisan_db;
Database changed
MariaDB [wisan_db]> create table student(id int not null,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.05sec)
MariaDB [wisan_db]> show tables from wisan_db;
+--------------------+
| Tables_in_wisan_db |
+--------------------+
| student |
+--------------------+
1 row in set (0.00 sec)
MariaDB [wisan_db]> desc wisan_db.student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除表
DROP TABLE [ IF EXISTS ] 'table_name';
MariaDB [wisan_db]> drop table if exists student;
Query OK, 0 rows affected (0.02 sec)
MariaDB [wisan_db]> show tables ;
Empty set (0.00 sec)
3.1.4查看show
show character set
MariaDB [wisan_db]> show character set ;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
show engines
MariaDB [wisan_db]> show engines ;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
show databases
MariaDB [wisan_db]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wisan_db |
+--------------------+
5 rows in set (0.00 sec)
show tables from db_name
MariaDB [wisan_db]> show tables from wisan_db;
+--------------------+
| Tables_in_wisan_db |
+--------------------+
| student |
+--------------------+
1 row in set (0.00 sec)
desc db_name.tb_name
MariaDB [wisan_db]> desc wisan_db.student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
show create table db_name.tb_name
MariaDB [wisan_db]> show create table wisan_db.student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
show table status like 'tb_name'\G
MariaDB [wisan_db]> show table status like 'student'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: NULL
Create_time: 2020-10-21 00:03:03
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
查询帮助
- HELP keyword;
MariaDB [wisan_db]> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
........................................................
3.2 DCL操作
- GRANT:授权 REVOKE:移除授权
权限类型
- priv_type
WITH GRANT OPTION:被授权的用户可将自己的权限副本转赠给其他用户
类型 | 意义 |
---|---|
ALL | 所有权限 |
SELECT | 读取内容的权限 |
INSERT | 插入内容的权限 |
UPDATE | 更新内容的权限 |
DELETE | 删除内容的权限 |
操作对象
- 表: db_name.tb_name
- 存储 函数
- 存储 过程
对象 | 意义 |
---|---|
* . * | 所有库的所有表 |
db_name.* | 指定库的所有表 |
db_name.tb_name | 指定库的指定表 |
3.2.1创建权限grant
GRANT priv_type,... ON [object_type] db_name.table_name TO ‘username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
##授权wisan用户本地登陆数据库访问所有库的表
MariaDB [(none)]> grant all on *.* to 'wisan'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
##授权wisan用户在172.16.104.132远程登陆数据库访问wisan_db库的所有表
MariaDB [(none)]> grant all on wisan_db.* to 'wisan'@'172.16.104.132' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
##授权wisan用户在所有位置远程登陆数据库访问所有库的所有表
MariaDB [(none)]> grant all on *.* to 'wisan'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
3.2.2 查看权限
查看当前用户权限
- show grants
MariaDB [(none)]> show grants;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wisan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看指定用户的权限
- show grants for user
MariaDB [(none)]> show grants for 'wisan'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wisan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show grants for 'wisan'@'172.16.104.132';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@172.16.104.132 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wisan'@'172.16.104.132' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `wisan_db`.* TO 'wisan'@'172.16.104.132' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3.2.3 取消授权Revoke
REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
MariaDB [(none)]> show grants for 'wisan'@'172.16.104.132';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@172.16.104.132 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wisan'@'172.16.104.132' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `wisan_db`.* TO 'wisan'@'172.16.104.132' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> revoke all on wisan_db.* from 'wisan'@'172.16.104.132';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for 'wisan'@'172.16.104.132';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@172.16.104.132 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wisan'@'172.16.104.132' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
注意:mysql服务进程启动时会读取mysql库中的所有授权表至内存中:
GRANT或REVOKE等执行权限操作会保存于表中,mysql的服务进程会自动重读授权表,并更新至内存中
对于不能够或不能及时重读授权表的命令,可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;
3.3DML操作
- 增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT),均属针对表的数据操作
3.3.1 INSERT
INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
##插入一条数据
MariaDB [(none)]> use wisan_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [wisan_db]> insert into student(id,name,age) value(1,'tom',20);
Query OK, 1 row affected (0.02 sec)
##插入多条数据
MariaDB [wisan_db]> insert into student(id,name,age) values(2,'tom',20),(3,'null',15),(4,'jerry',null),(5,'cat',NULL);
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | tom | 20 |
| 3 | null | 15 |
| 4 | jerry | NULL |
| 5 | cat | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
3.3.2 UPDATE
UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | tom | 20 |
| 3 | null | 15 |
| 4 | jerry | NULL |
| 5 | cat | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
'
MariaDB [wisan_db]> update student set name = 'mouse' where name = 'null';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [wisan_db]> update student set age = 18 where id = 4;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | tom | 20 |
| 3 | mouse | 15 |
| 4 | jerry | 18 |
| 5 | cat | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
3.3.3 DELETE
DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | tom | 20 |
| 3 | mouse | 15 |
| 4 | jerry | 18 |
| 5 | cat | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
MariaDB [wisan_db]> delete from student where id = 1;
Query OK, 1 row affected (0.02 sec)
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | tom | 20 |
| 3 | mouse | 15 |
| 4 | jerry | 18 |
| 5 | cat | NULL |
+----+-------+------+
4 rows in set (0.00 sec)
MariaDB [wisan_db]> delete from student;
Query OK, 4 rows affected (0.02 sec)
MariaDB [wisan_db]> select * from student;
Empty set (0.00 sec)
MariaDB [wisan_db]> desc wisan_db.student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
3.3.4 TRUNCATE
TRUNCATE table_name;
truncate删除表中所有数据,新添加的行计数值重置为初始值,且无法恢复(通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放)。表结构、约束和索引等保持不变。对于有外键约束引用的表不能使用它删除数据,也不能用于加入了索引视图的表。
delete删除表内容时仅删除内容且每次删除一行,但会保留表结构。并在事务日志中为所删除的每行记录一项,所以可以通过回滚事务日志恢复数据
3.3.5 SELECT
- select * 表示查询所有字段
- select column as alias 表示查询column并用alias替代
- 常用操作符号:< ,>,>= ,<= ,= ,!= ,BETWEEN * AND *
- LIKE(模糊匹配),RLIKE(正则表达式匹配),IS NOT NULL,IS NULL
- 逻辑操作符号: AND OR NOT
- 排序:ORDER BY COLUMN (默认ASC升序,DESC降序),LIMIT [n],m(略过第n个)取第m个
SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
4.连接查询
- 笛卡尔积,表记录的乘积
- 内连接查询时,若没有条件,或者条件为真,则返回笛卡尔积
- 创建两张表student_info,student_score
MariaDB [wisan_db]> desc wisan_db.student_info;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
MariaDB [wisan_db]> select * from student_info;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | xiang | 18 |
| 2 | peng | 19 |
| 3 | fan | 17 |
| 4 | yi | 20 |
| 5 | jing | NULL |
| 6 | wisan | 19 |
| 7 | flora | 18 |
| 8 | wang | NULL |
+----+-------+------+
MariaDB [wisan_db]> desc wisan_db.student_score;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| score | tinyint(4) | YES | | NULL | |
| grade | char(1) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
MariaDB [wisan_db]> select * from student_score;
+----+-----------+-------+-------+
| id | name | score | grade |
+----+-----------+-------+-------+
| 2 | peng | 88 | A |
| 3 | fan | 90 | A |
| 5 | jing | 100 | A |
| 6 | wisan | NULL | C |
| 7 | flora | 85 | B |
| 8 | wang | NULL | C |
| 10 | lisi | 60 | B |
| 11 | biyue | 85 | B |
| 13 | zhangshan | 70 | B |
| 15 | qiuwu | 70 | B |
+----+-----------+-------+-------+
4.1 内连接
- 说明:组合两个表中的记录,返回满足条件的两个表交集部分。
- 关键字:inner join on 连接条件
- 语句:select * from a_table inner join b_table on a_table.id = b_table.id;
##没有条件时
MariaDB [wisan_db]> select * from student_info as a inner join student_score as b;
+----+-------+------+----+-----------+-------+-------+
| id | name | age | id | name | score | grade |
+----+-------+------+----+-----------+-------+-------+
| 1 | xiang | 18 | 2 | peng | 88 | A |
| 2 | peng | 19 | 2 | peng | 88 | A |
| 3 | fan | 17 | 2 | peng | 88 | A |
| 4 | yi | 20 | 2 | peng | 88 | A |
| 5 | jing | NULL | 2 | peng | 88 | A |
| 6 | wisan | 19 | 2 | peng | 88 | A |
| 7 | flora | 18 | 2 | peng | 88 | A |
| 8 | wang | NULL | 2 | peng | 88 | A |
| 1 | xiang | 18 | 3 | fan | 90 | A |
| 2 | peng | 19 | 3 | fan | 90 | A |
| 3 | fan | 17 | 3 | fan | 90 | A |
| 4 | yi | 20 | 3 | fan | 90 | A |
| 5 | jing | NULL | 3 | fan | 90 | A |
| 6 | wisan | 19 | 3 | fan | 90 | A |
| 7 | flora | 18 | 3 | fan | 90 | A |
....................................................
##一共有8*10条记录
##有条件时(a.id=b.id),此时输出表的交集
MariaDB [wisan_db]> select * from student_info as a inner join student_score as b on a.id = b.id;
+----+-------+------+----+-------+-------+-------+
| id | name | age | id | name | score | grade |
+----+-------+------+----+-------+-------+-------+
| 2 | peng | 19 | 2 | peng | 88 | A |
| 3 | fan | 17 | 3 | fan | 90 | A |
| 5 | jing | NULL | 5 | jing | 100 | A |
| 6 | wisan | 19 | 6 | wisan | NULL | C |
| 7 | flora | 18 | 7 | flora | 85 | B |
| 8 | wang | NULL | 8 | wang | NULL | C |
+----+-------+------+----+-------+-------+-------+
4.2 外连接
左连接
- 说明:返回左表(即关键词左边的表)所有记录和右表满足条件的记录(不足的NULL代替)
- 关键字:left join on 连接条件
- 语句:select * from a_table left join b_table on a_table.id = b_table.id;
##
MariaDB [wisan_db]> select * from student_info as a left join student_score as b on a.id = b.id;
+----+-------+------+------+-------+-------+-------+
| id | name | age | id | name | score | grade |
+----+-------+------+------+-------+-------+-------+
| 1 | xiang | 18 | NULL | NULL | NULL | NULL |
| 2 | peng | 19 | 2 | peng | 88 | A |
| 3 | fan | 17 | 3 | fan | 90 | A |
| 4 | yi | 20 | NULL | NULL | NULL | NULL |
| 5 | jing | NULL | 5 | jing | 100 | A |
| 6 | wisan | 19 | 6 | wisan | NULL | C |
| 7 | flora | 18 | 7 | flora | 85 | B |
| 8 | wang | NULL | 8 | wang | NULL | C |
+----+-------+------+------+-------+-------+-------+
8 rows in set (0.001 sec)
右连接
- 说明:返回右表(即关键词右边的表)所有记录和左表满足条件的记录(不足的NULL代替)
- 关键字:right join on 连接条件
- 语句:select * from a_table right join b_table on a_table.id = b_table.id;
MariaDB [wisan_db]> select * from student_info as a right join student_score as b on a.id = b.id;
+------+-------+------+----+-----------+-------+-------+
| id | name | age | id | name | score | grade |
+------+-------+------+----+-----------+-------+-------+
| 2 | peng | 19 | 2 | peng | 88 | A |
| 3 | fan | 17 | 3 | fan | 90 | A |
| 5 | jing | NULL | 5 | jing | 100 | A |
| 6 | wisan | 19 | 6 | wisan | NULL | C |
| 7 | flora | 18 | 7 | flora | 85 | B |
| 8 | wang | NULL | 8 | wang | NULL | C |
| NULL | NULL | NULL | 10 | lisi | 60 | B |
| NULL | NULL | NULL | 11 | biyue | 85 | B |
| NULL | NULL | NULL | 13 | zhangshan | 70 | B |
| NULL | NULL | NULL | 15 | qiuwu | 70 | B |
+------+-------+------+----+-----------+-------+-------+
4.3全连接
- 关键字:union | union all
- 语法: (查询语句) union|union all (查询语句)
- 注意:
1.查询的COLUMN数(列数)必须相等
2.没有要求时,合并的表列名以第一个查询语句为准
3.union会合并完全相同的记录,但是比较耗时,union all则不会,通常使用union all
4.在单个查询的语句中即便排序,也不会有排序效果,但是可以对最终结果排序
##union查询name,age字段,两条记录(wang null)合并为一条
MariaDB [wisan_db]> (select name,age from student_info) union (select name,score from student_score);
+-----------+------+
| name | age |
+-----------+------+
| xiang | 18 |
| peng | 19 |
| fan | 17 |
| yi | 20 |
| jing | NULL |
| wisan | 19 |
| flora | 18 |
| wang | NULL | ####
| peng | 88 |
| fan | 90 |
| jing | 100 |
| wisan | NULL |
| flora | 85 |
| lisi | 60 |
| biyue | 85 |
| zhangshan | 70 |
| qiuwu | 70 |
+-----------+------+
## union all查询name,score字段
MariaDB [wisan_db]> (select name,age from student_info) union all (select name,score from student_score);
+-----------+------+
| name | age |
+-----------+------+
| xiang | 18 |
| peng | 19 |
| fan | 17 |
| yi | 20 |
| jing | NULL |
| wisan | 19 |
| flora | 18 |
| wang | NULL | ######
| peng | 88 |
| fan | 90 |
| jing | 100 |
| wisan | NULL |
| flora | 85 |
| wang | NULL | ######
| lisi | 60 |
| biyue | 85 |
| zhangshan | 70 |
| qiuwu | 70 |
+-----------+------+
18 rows in set (0.001 sec)
##在第二个查询语句排序,结果并没有排序
MariaDB [wisan_db]> (select name,age from student_info) union all (select name,score from student_score order by score);
+-----------+------+
| name | age |
+-----------+------+
| xiang | 18 |
| peng | 19 |
| fan | 17 |
| yi | 20 |
| jing | NULL |
| wisan | 19 |
| flora | 18 |
| wang | NULL |
| peng | 88 |
| fan | 90 |
| jing | 100 |
| wisan | NULL |
| flora | 85 |
| wang | NULL |
| lisi | 60 |
| biyue | 85 |
| zhangshan | 70 |
| qiuwu | 70 |
+-----------+------+
##对整个结果排序
MariaDB [wisan_db]> (select name,age from student_info) union all (select name,score from student_score order by score) order by age;
+-----------+------+
| name | age |
+-----------+------+
| jing | NULL |
| wang | NULL |
| wisan | NULL |
| wang | NULL |
| fan | 17 |
| flora | 18 |
| xiang | 18 |
| peng | 19 |
| wisan | 19 |
| yi | 20 |
| lisi | 60 |
| qiuwu | 70 |
| zhangshan | 70 |
| flora | 85 |
| biyue | 85 |
| peng | 88 |
| fan | 90 |
| jing | 100 |
+-----------+------+
5.分组查询
5.1 语法
SELECT column, group_function,... FROM table [WHERE condition] GROUP BY group_by_expression [HAVING group_condition];
- 说明
group_function:聚合函数。
group_by_expression:分组表达式,多个之间用逗号隔开。
group_condition:分组之后对数据进行过滤。
分组中,select后面只能有两种类型的列:
- group_function:
函数名 | 作用 |
---|---|
max | 查询指定列的最大值 |
min | 查询指定列的最小值 |
count | 统计查询结果的行数 |
sum | 求和,返回指定列的总和 |
avg | 求平均值,返回指定列数据的平均值 |
5.2 查询
- 建表
MariaDB [wisan_db]> select * from student_info;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | xiang | 18 |
| 2 | peng | 19 |
| 3 | fan | 17 |
| 4 | yi | 20 |
| 5 | jing | NULL |
| 6 | wisan | 19 |
| 7 | flora | 18 |
| 8 | wang | NULL |
+----+-------+------+
8 rows in set (0.001 sec)
MariaDB [wisan_db]> select * from student_score;
+----+-----------+-------+-------+
| id | name | score | grade |
+----+-----------+-------+-------+
| 2 | peng | 88 | A |
| 3 | fan | 90 | A |
| 5 | jing | 100 | A |
| 6 | wisan | NULL | C |
| 7 | flora | 85 | B |
| 8 | wang | NULL | C |
| 10 | lisi | 60 | B |
| 11 | biyue | 85 | B |
| 13 | zhangshan | 70 | B |
| 15 | qiuwu | 70 | B |
+----+-----------+-------+-------+
- 单独查询
##每个组只取第一个
MariaDB [wisan_db]> select * from student_score group by grade;
+----+-------+-------+-------+
| id | name | score | grade |
+----+-------+-------+-------+
| 2 | peng | 88 | A |
| 7 | flora | 85 | B |
| 6 | wisan | NULL | C |
+----+-------+-------+-------+
- group_concat()函数输出里面的所有成员
MariaDB [wisan_db]> select group_concat(name),group_concat(score),grade from student_score group by grade;
+----------------------------------+---------------------+-------+
| group_concat(name) | group_concat(score) | grade |
+----------------------------------+---------------------+-------+
| peng,fan,jing | 88,90,100 | A |
| zhangshan,biyue,lisi,qiuwu,flora | 70,85,60,70,85 | B |
| wisan,wang | NULL | C |
+----------------------------------+---------------------+-------+
- count()函数统计里面的成员数量
MariaDB [wisan_db]> select count(name),grade from student_score group by grade;
+-------------+-------+
| count(name) | grade |
+-------------+-------+
| 3 | A |
| 5 | B |
| 2 | C |
+-------------+-------+
- HAVING,对分组后的结果筛选,分组人数>=3
MariaDB [wisan_db]> select count(name),grade from student_score group by grade having count(name)>=3;
+-------------+-------+
| count(name) | grade |
+-------------+-------+
| 3 | A |
| 5 | B |
+-------------+-------+
- with roollup在最后进行统计
MariaDB [wisan_db]> select count(name),grade from student_score group by grade with rollup;
+-------------+-------+
| count(name) | grade |
+-------------+-------+
| 3 | A |
| 5 | B |
| 2 | C |
| 10 | NULL |
+-------------+-------+
内容总结
以上是互联网集市为您收集整理的mysql全部内容,希望文章能够帮你解决mysql所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。