MySQL之SQL语法介绍

结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名

本节索引:

一、SQL简介及数据类型

二、数据定义语言(DDL):CREATE, DROP, ALTER

三、数据操作语言(DML):INSERT,UPDATE,DELETE

四、数据查询语言(DQL):SELECT,LIKE,GROUP,ODER BY

五、多表查询SQJ JOIN

六、视图VIEW

封面

一、SQL语言简介:

结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库

查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件

的扩展名

 

关系型数据库的常见组件

数据库:database

表:table

行:row

列:column

索引:index

视图:view

用户:user

权限:privilege

存储过程:procedure,无返回值

存储函数:function,有返回值

触发器:trigger

事件调度器:event scheduler,任务计划

 

命名规则:

必须以字母开头

可包括数字和三个特殊字符(# _ $)

不要使用MySQL的保留字

同一database(Schema)下的对象不能同名

 

数据类型:

(一)整型

tinyint(m) 1个字节 范围(-128~127)

smallint(m) 2个字节 范围(-32768~32767)

mediumint(m) 3个字节 范围(-8388608~8388607)

int(m) 4个字节 范围(-2147483648~2147483647)

bigint(m) 8个字节 范围(+-9.22*10的18次方)

取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~255)

 

(二)浮点型(float和double),近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位

double(m,d) 双精度浮点型16位精度(8字节) m总个数,d小数位

设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存

的是123.457,但总个数还以实际为准,即6位

 

(三)字符串(char,varchar,_text)

char(n) 固定长度,最多255个字符

varchar(n)可变长度,最多65535个字符

inytext 可变长度,最多255个字符

ext 可变长度,最多65535个字符

mediumtext 可变长度,最多2的24次方-1个字符

ongtext 可变长度,最多2的32次方-1个字符

BINARY(M) 固定长度,可存二进制或字符,长度为0-M字节

VARBINARY(M) 可变长度,可存二进制或字符,允许长度为0-M字节

内建类型:ENUM枚举, SET集合

 

charvarchar

1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。

所以char类型存储的字符串末尾不能有空格,varchar不限于此。

2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar

是存入的实际字符数+1个字节(n< n>255),所以varchar(4),存入3个字符将

占用4个字节。

3.char类型的字符串检索速度要比varchar类型的快

 

varchartext

1.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个

字节(n< n>255),text是实际字符数+2个字节。

2.text类型不能有默认值

3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速

度快于text

 

(四)二进制数据:BLOB

BLOB和text存储方式不同,TEXT以文本方式存储,英文存储区分大小写,

而Blob是以二进制方式存储,不分大小写

BLOB存储的数据只能整体读出

TEXT可以指定字符集,BLOB不用指定字符集

 

(五)日期时间类型

date 日期 ‘2008-12-2’

time 时间 ’12:25:36′

datetime 日期时间 ‘2008-12-2 22:06:44’

timestamp 自动存储记录修改时间

YEAR(2), YEAR(4):年份

 

timestamp字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类

型的字段可以存放这条记录最后被修改的时间

 

 

修饰符:

NULL                              数据列可包含NULL值

NOT NULL                    数据列不允许包含NULL值

DEFAULT                      默认值

PRIMARY KEY             主键

UNIQUE KEY                唯一键

CHARACTER SET name            指定一个字符集

AUTO_INCREMENT                自动递增,适用于整数类型

UNSIGNED              无符号

 

SQL语句分类:

DDL:Data Defination Language         

代表操作:

CREATE    可用来创建数据库,表

DROP       可用来删除数据库,表

ALTER      可用来修改表的属性,字段的结构

 

DML:Data Manipulation Language       

代表操作:

INSERT    可用来添加表中的行

DELETE    可用来删除表中的行

UPDATE   可用来修改标准行的信息

 

DCL:Data Control Language        

代表操作:

GRANT

REVOKE

 

DQL:Data Query Language  

代表操作:

SELECT

 

二、数据定义语言(DDL):CREATE, DROP, ALTER

数据定义语言(DDL):其语句包括动词CREATE和DROP。在数据库中创建新表或删除表(CREAT

TABLE 或 DROP TABLE);为表加入索引等。DDL包括许多与人数据库目录中获得数据

有关的保留字。它也是动作查询的一部分。

 

新建数据库:

CREATE DATABASE|SCHEMA [IF NOT EXISTS] DB_NAME;

CHARACTER SET ‘character set name’

COLLATE ‘collate name’

 

删除数据库:

DROP DATABASE|SCHEMA [IF EXISTS]’DB_NAME’

 

新建表:

CREATE TABLE

(1) 直接创建

(2) 通过查询现存表创建;新表会被直接插入查询而来的数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,…)] [table_options]

[partition_options] select_statement

(3) 通过复制现存的表的表结构创建,但不复制数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE

oldtblname | (LIKE oldtblname) }

 

示例:直接创建表

MariaDB [(db1)]> create table emp

    -> ( id int unsigned primary key  auto_increment,

    -> name varchar(30) not null ,

    -> sex char(1) default ‘m’ ,

    -> address varchar(100) ) engine=innodb charset=utf8;

 

 

示例:通过查询现存表创建

MariaDB [db1]> create table user select user,host,password from mysql.user;

 

MariaDB [db1]> desc user;

+———-+———-+——+—–+———+——-+

| Field    | Type     | Null | Key | Default | Extra |

+———-+———-+——+—–+———+——-+

| user     | char(16) | NO   |     |         |       |

| host     | char(60) | NO   |     |         |       |

| password | char(41) | NO   |     |         |       |

+———-+———-+——+—–+———+——-+

 

MariaDB [db1]> select * from user;

+——+—————+———-+

| user | host          | password |

+——+—————+———-+

| root | localhost     |          |

| root | centos7.wangx |          |

| root | 127.0.0.1     |          |

| root | ::1           |          |

|      | localhost     |          |

|      | centos7.wangx |          |

+——+—————+———-+

 

 

示例:通过复制现存的表的表结构创建,但不复制数据

MariaDB [db1]> create table user3 like mysql.user;

 

示例:创建表包含复合主键

MariaDB [db1]> create table t1 ( name char(30),city char(30),sex char(1) primary key(name,city) );

 

 

表操作:

查看所有的引擎:SHOW ENGINES

查看表:SHOW TABLES [FROM db_name]

查看表结构:DESC [db_name.]tb_name

删除表:DROP TABLE [IF EXISTS] tb_name

查看表创建命令:SHOW CREATE TABLE tbl_name

查看表状态:SHOW TABLE STATUS LIKE ‘tbl_name’

查看库中所有表状态:SHOW TABLE STATUS FROM db_name

 

示例:直接创建TABLE

MariaDB [db1]> CREATE TABLE students ( id tinyint unsigned not null primary key,

name char(10) not null,phone char(11) not null ,sex char(1) );

MariaDB [db1]> show tables; 

+—————+

| Tables_in_db1 |

+—————+

| students      |

+—————+

 

示例:查看表状态信息

MariaDB [db1]> show table status like ‘students’\G

*************************** 1. row ***************************

Name: students

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: 2018-06-06 18:19:05

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

 

示例:查看数据库状态信息

MariaDB [db1]> show table status from db1\G

*************************** 1. row ***************************

Name: students

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: 2018-06-06 18:19:05

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

 

表操作

DROP TABLE [IF EXISTS] ‘tbl_name’;

ALTER TABLE ‘tbl_name’

字段:

添加字段:add

   ADD col1 data_type [FIRST|AFTER col_name]

删除字段:drop

修改字段:

alter(默认值), change(字段名), modify(字段属性)

索引:

添加索引:add index

删除索引: drop index

表选项

修改:

查看表上的索引:SHOW INDEXES FROM [db_name.]tbl_name;

查看帮助:Help ALTER TABLE

 

示例:

修改表名

ALTER TABLE students RENAME s1;

 

添加表s1中phone列到name列后

ALTER TABLE s1 ADD phone varchar(11) AFTER name;

 

修改表s1中的phone字段属性为int

ALTER TABLE s1 MODIFY phone int;

 

修改表s1中的phone字段名称为mobile

ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);

 

删掉表s1中的一个字段mobile

ALTER TABLE s1 DROP COLUMN mobile;

 

在students表中新增加一列gender,只允许填入m,f

ALTER TABLE students ADD gender ENUM(‘m’,’f’)

 

修改students表中的id字符变为sid字段

ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;

 

将students表中name字段设为唯一键

ALTER TABLE students ADD UNIQUE KEY(name);

 

将students表中age字段设为索引

ALTER TABLE students ADD INDEX(age);

DESC students;

SHOW INDEXES FROM students;

 

删掉students表中的age字段

ALTER TABLE students DROP age;

 

三、数据操作语言(DML):INSERT,UPDATE,DELETE

数据操作语言(DML):其语句包括动词INSERT,UPDATE和DELETE。它们分别用于添加,修

改和删除表中的行。也称为动作查询语言。

 

 

INSERT

功能:可用来添加表中的行

示例:添加表中的行第一种语法

MariaDB [db1]> insert students values(1,’bai’,’10086′,’m’);

MariaDB [db1]> select * from students;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  1 | bai  | 10086 | m    |

+—-+——+——-+——+

 

示例:添加表中的行第二种语法

MariaDB [db1]> insert students(name,id) value (‘wangx’,70);

MariaDB [db1]> select * from students;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  1 | bai  | 10086 | m    |

| 70 | wangx|       | NULL |

+—-+——+——-+——+

 

多行添加

MariaDB [db1]> insert students(id,name,sex) values (2,’liu’,’m’),(3,’lin’,’f’);

MariaDB [db1]> select * from students;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  1 | bai  | 10086 | m    |

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

| 70 | wang |       | NULL |

+—-+——+——-+——+

 

示例:添加表中的行第三种语法

MariaDB [db1]> insert students set id=4,name=’zhao’ ;

MariaDB [db1]> select * from students;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  1 | bai  | 10086 | m    |

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

|  4 | zhao |       | NULL |

| 70 | wang |       | NULL |

+—-+——+——-+——+

 

 

UPDATE

功能:可用来修改标准行的信息

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

注意:一定要有限制条件,否则将修改所有行的指定字段

限制条件:

WHERE

LIMIT

Mysql 选项:–safe-updates| –i-am-a-dummy|-U

 

MariaDB [db1]> select * from students;   

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  1 | bai  | 10086 | m    |

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

|  4 | zhao |       | NULL |

| 70 | wang |       | NULL |

+—-+——+——-+——+

 

示例:修改表中行内容

MariaDB [db1]> update students set name=’admin’,sex=’f’ where id=1;  

MariaDB [db1]> select * from students;

+—-+——-+——-+——+

| id | name  | phone | sex  |

+—-+——-+——-+——+

|  1 | admin | 10086 | f    |

|  2 | liu   |       | m    |

|  3 | lin   |       | f    |

|  4 | zhao  |       | NULL |

| 70 | wang  |       | NULL |

+—-+——-+——-+——+

 

 

DELETE

功能:可用来删除表中的行

生产环境一般用伪删除代替DELETE:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

可先排序再指定删除的行数

注意:一定要有限制条件,否则将清空表中的所有数据

限制条件:

WHERE

LIMIT

TRUNCATE TABLE tbl_name; 清空表,慎用!

 

MariaDB [db1]> delete from students where id=1;

MariaDB [db1]> select * from students;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

|  4 | zhao |       | NULL |

| 70 | wang |       | NULL |

+—-+——+——-+——+

 

为避免操作时忘加where可使用安全更新模式

mysql –safe-updates|-U

当执行修该操作未指定WHERE时就会进行报错提醒

MariaDB [db1]> update user set user=’admin’

-> ;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

 

方法一:添加别名

alias mysql=mysql -U    

方法二:修改配置文件

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

[mysql]

safe-updates

 

四、数据查询语言(DQL):SELECT,LIKE,GROUP,ODER BY

数据查询语言(DQL):也称为“数据检索语句”,用以从表中获得数据,确定数据怎样在

应用程序给出。保留字SELECT是DQL(也是所有SQL)用得最多的动词,其他DQL常用的保

留字有WHERE,ORDER BY,GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一

起使用。

select语句执行顺序:

语句逻辑

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[SQL_CACHE | SQL_NO_CACHE]

select_expr [, select_expr …]

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], … [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], …]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[FOR UPDATE | LOCK IN SHARE MODE]

注意:select语句用法不同会造成性能消耗不同,性能差的写法甚至可能造成宕机

 

LIKE:

%: 任意长度的任意字符

_:任意单个字符

RLIKE:正则表达式,索引失效,不建议使用

REGEXP:匹配字符串可用正则表达式书写模式,同上

逻辑操作符:

         NOT

         AND

         OR

         XOR

 

示例:

单表操作

MariaDB [db1]> select * from user;

+——+—————+———-+

| user | host          | password |

+——+—————+———-+

| root | localhost     |          |

| root | centos7.wangx |          |

| root | 127.0.0.1     |          |

| root | ::1           |          |

|      | localhost     |          |

|      | centos7.wangx |          |

+——+—————+———-+

 

select也是一种语句:

MariaDB [db1]> select ‘hello world’;

+————-+

| hello world |

+————-+

| hello world |

+————-+

 

也可以进行运算

MariaDB [db1]> select ‘1+2=’,1+2;

+——+—–+

| 1+2= | 1+2 |

+——+—–+

| 1+2= |   3 |

+——+—–+

 

有类似与sed的特性:

MariaDB [db1]> select ‘1+2=’,1+2 from user;

+——+—–+

| 1+2= | 1+2 |

+——+—–+

| 1+2= |   3 |

| 1+2= |   3 |

| 1+2= |   3 |

| 1+2= |   3 |

| 1+2= |   3 |

| 1+2= |   3 |

+——+—–+

 

MariaDB [db1]> select user,password from user;    

+——+———-+

| user | password |

+——+———-+

| root |          |

| root |          |

| root |          |

| root |          |

|      |          |

|      |          |

+——+———-+

 

可添加自定义字符:

MariaDB [db1]> select ‘number’,user,password from user;

+——–+——+———-+

| number | user | password |

+——–+——+———-+

| number | root |          |

| number | root |          |

| number | root |          |

| number | root |          |

| number |      |          |

| number |      |          |

+——–+——+———-+

 

 

限定条件select

MariaDB [db1]> select ‘number’,user,password from user where user=’root’;

+——–+——+———-+

| number | user | password |

+——–+——+———-+

| number | root |          |

| number | root |          |

| number | root |          |

| number | root |          |

+——–+——+———-+

 

MariaDB [db1]> select * from students;                 

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

|  4 | zhao |       | NULL |

| 70 | wang |       | NULL |

+—-+——+——-+——+

 

 

判断条件,是否为空

搜索性别为空的学生信息

MariaDB [db1]> select * from students where sex is null;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  4 | zhao |       | NULL |

| 70 | wang |       | NULL |

+—-+——+——-+——+

 

搜索性别不为空的学生信息

MariaDB [db1]> select * from students where sex is not null;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

+—-+——+——-+——+

 

搜索id大于2小于5的学生信息

MariaDB [db1]> select * from students where id >=2 and id<=5;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

|  4 | zhao |       | NULL |

+—-+——+——-+——+

 

也可用between写法表示范围

MariaDB [db1]> select * from students where id between 2 and 5;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

|  4 | zhao |       | NULL |

+—-+——+——-+——+

 

字符范围搜索

MariaDB [db1]> select * from students where sex in (‘f’,’m’); 

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

+—-+——+——-+——+

 

逻辑或关系搜索

MariaDB [db1]> select * from students where sex in (‘f’,’m’) or sex is null;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  0 | 70   |       | NULL |

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

|  4 | zhao |       | NULL |

| 70 | wang |       | NULL |

+—-+——+——-+——+

 

可对字段定义别名(as可省略)

MariaDB [db1]> select id as 编号,name 姓名 from students where sex in (‘f’,’m’) or sex is null;   

+——–+——–+

| 编号   | 姓名   |

+——–+——–+

|      0 | 70     |

|      2 | liu    |

|      3 | lin    |

|      4 | zhao   |

|     70 | wang   |

+——–+——–+

 

 

 

LIKE

功能:支持模糊搜索

 

搜索名字以w开头学生信息

MariaDB [db1]> select * from students where name like ‘w%’;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

| 70 | wang |       | NULL |

+—-+——+——-+——+

 

搜索名字包含i的学生信息

MariaDB [db1]> select * from students where name like ‘%i%’;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

+—-+——+——-+——+

 

“_”可用来表示单个字符,搜索名字为三个字符的学生信息

MariaDB [db1]> select * from students where name like ‘___’;

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

+—-+——+——-+——+

 

RLIKE

功能:支持正则表达式,不建议使用,将导致索引生效!!!!!

 

搜索名字以l开头的学员信息

MariaDB [db1]> select * from students where name rlike ‘^l’;  

+—-+——+——-+——+

| id | name | phone | sex  |

+—-+——+——-+——+

|  2 | liu  |       | m    |

|  3 | lin  |       | f    |

+—-+——+——-+——+

 

 

GROUP

功能:根据指定的条件把查询结果进行“分组”以用于做“聚合”运算

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

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

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

升序:ASC

降序:DESC

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

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

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

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

 

以下表为示例

MariaDB [db1]> select * from students;           

+—-+——-+——-+——-+——+

| id | name  | phone | score | sex  |

+—-+——-+——-+——-+——+

|  2 | liu   |       |    88 | m    |

|  3 | lin   |       |    84 | f    |

|  6 | zhang | 10010 |    76 | m    |

| 70 | wang  |       |    93 | f    |

+—-+——-+——-+——-+——+

 

按性别分组统计最好成绩

MariaDB [db1]> select sex,max(score) as 最好成绩 from students group by sex; 

+——+————–+

| sex  | 最好成绩     |

+——+————–+

| f    |           93 |

| m    |           88 |

+——+————–+

 

按性别分组统计平均成绩

MariaDB [db1]> select sex,avg(score) from students group by sex;             

+——+————+

| sex  | avg(score) |

+——+————+

| f    |    88.5000 |

| m    |    82.0000 |

+——+————+

 

多行分组:

MariaDB [db1]> select * from students;                       

+—-+——-+——-+——-+——+——-+

| id | name  | phone | score | sex  | class |

+—-+——-+——-+——-+——+——-+

|  2 | liu   |       |    88 | m    |     1 |

|  3 | lin   |       |    84 | f    |     1 |

|  4 | abc   | 11000 |    86 | f    |     1 |

|  6 | zhang | 10010 |    76 | m    |     2 |

| 70 | wang  |       |    93 | f    |     2 |

+—-+——-+——-+——-+——+——-+

 

统计按班级,性别分组后的各组平均成绩

MariaDB [db1]> select class,sex,avg(score) from students group by class,sex;

+——-+——+————+

| class | sex  | avg(score) |

+——-+——+————+

|     1 | f    |    85.0000 |

|     1 | m    |    88.0000 |

|     2 | f    |    93.0000 |

|     2 | m    |    76.0000 |

+——-+——+————+

 

count()统计数量:

MariaDB [db1]> select class,sex,count(*) from students group by class,sex;

+——-+——+———-+

| class | sex  | count(*) |

+——-+——+———-+

|     1 | f    |        2 |

|     1 | m    |        1 |

|     2 | f    |        1 |

|     2 | m    |        1 |

+——-+——+———-+

 

统计数量时建议统计主键,可提高数率

MariaDB [db1]> select class,sex,count(id) from students group by class,sex;

+——-+——+———–+

| class | sex  | count(id) |

+——-+——+———–+

|     1 | f    |         2 |

|     1 | m    |         1 |

|     2 | f    |         1 |

|     2 | m    |         1 |

+——-+——+———–+

 

统计学生人数

MariaDB [db1]> select count(id) from students;

+———–+

| count(id) |

+———–+

|         5 |

+———–+

 

分组后过滤:

统计按班级,性别分组后的各组平均成绩,只显示平均成绩打印80的组统计信息

MariaDB [db1]> select class,sex,avg(score) from students group by class,sex having avg(score) >80;

+——-+——+————+

| class | sex  | avg(score) |

+——-+——+————+

|     1 | f    |    85.0000 |

|     1 | m    |    88.0000 |

|     2 | f    |    93.0000 |

+——-+——+————+

 

分组前过滤

只显示1班分组后的统计信息

MariaDB [db1]> select class,sex,avg(score) from students where class=1 group by class,sex having avg(score) >80;

+——-+——+————+

| class | sex  | avg(score) |

+——-+——+————+

|     1 | f    |    85.0000 |

|     1 | m    |    88.0000 |

+——-+——+————+

 

 

ORDER BY

正序排列:

MariaDB [db1]> select * from students order by score;

+—-+——-+——-+——-+——+——-+

| id | name  | phone | score | sex  | class |

+—-+——-+——-+——-+——+——-+

|  6 | zhang | 10010 |    76 | m    |     2 |

|  3 | lin   |       |    84 | f    |     1 |

|  4 | abc   | 11000 |    86 | f    |     1 |

|  2 | liu   |       |    88 | m    |     1 |

| 70 | wang  |       |    93 | f    |     2 |

+—-+——-+——-+——-+——+——-+

 

倒序排列:

MariaDB [db1]> select * from students order by score desc;

+—-+——-+——-+——-+——+——-+

| id | name  | phone | score | sex  | class |

+—-+——-+——-+——-+——+——-+

| 70 | wang  |       |    93 | f    |     2 |

|  2 | liu   |       |    88 | m    |     1 |

|  4 | abc   | 11000 |    86 | f    |     1 |

|  3 | lin   |       |    84 | f    |     1 |

|  6 | zhang | 10010 |    76 | m    |     2 |

+—-+——-+——-+——-+——+——-+

 

小技巧:利用-调整空字符NULL的位置

MariaDB [db1]> select * from students order by score desc;

+—-+——-+——-+——-+——+——-+

| id | name  | phone | score | sex  | class |

+—-+——-+——-+——-+——+——-+

| 70 | wang  |       |    93 | f    |     2 |

|  2 | liu   |       |    88 | m    |     1 |

|  4 | abc   | 11000 |    86 | f    |     1 |

|  3 | lin   |       |    84 | f    |     1 |

|  6 | zhang | 10010 |    76 | m    |     2 |

|  7 | zz    | 10086 |    69 | m    |     2 |

|  8 | zhao  | 10000 |  NULL | m    |     1 |

+—-+——-+——-+——-+——+——-+

 

MariaDB [db1]> select * from students order by -score desc;

+—-+——-+——-+——-+——+——-+

| id | name  | phone | score | sex  | class |

+—-+——-+——-+——-+——+——-+

|  7 | zz    | 10086 |    69 | m    |     2 |

|  6 | zhang | 10010 |    76 | m    |     2 |

|  3 | lin   |       |    84 | f    |     1 |

|  4 | abc   | 11000 |    86 | f    |     1 |

|  2 | liu   |       |    88 | m    |     1 |

| 70 | wang  |       |    93 | f    |     2 |

|  8 | zhao  | 10000 |  NULL | m    |     1 |

+—-+——-+——-+——-+——+——-+

 

LIMIT

MariaDB [db1]> select * from students order by score limit 3;

+—-+——-+——-+——-+——+——-+

| id | name  | phone | score | sex  | class |

+—-+——-+——-+——-+——+——-+

|  8 | zhao  | 10000 |  NULL | m    |     1 |

|  7 | zz    | 10086 |    69 | m    |     2 |

|  6 | zhang | 10010 |    76 | m    |     2 |

+—-+——-+——-+——-+——+——-+

 

 

五、多表查询SQJ JOIN

多表查询中主要的 SQL JOIN 类型:

INNER JOIN:如果表中有至少一个匹配,则返回行

LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行

RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行

FULL JOIN:只要其中一个表中存在匹配,则返回行

CROSS JOIN:用于生成两张表的笛卡尔集

 

本次示例,我们将使用 HELLODB 样本数据库。

下面是选自 “teachers” 表的数据:

MariaDB [hellodb]> select * from teachers;

+—–+—————+—–+——–+

| TID | Name          | Age | Gender |

+—–+—————+—–+——–+

|   1 | Song Jiang    |  45 | M      |

|   2 | Zhang Sanfeng |  94 | M      |

|   3 | Miejue Shitai |  77 | F      |

|   4 | Lin Chaoying  |  93 | F      |

+—–+—————+—–+——–+

 

以下是 “students” 表的数据

MariaDB [hellodb]> select * from students;

+——-+—————+—–+——–+———+———–+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+——-+—————+—–+——–+———+———–+

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |

|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |

|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |

|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |

|    17 | Lin Chong     |  25 | M      |       4 |      NULL |

|    18 | Hua Rong      |  23 | M      |       7 |      NULL |

|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |

|    20 | Diao Chan     |  19 | F      |       7 |      NULL |

|    21 | Huang Yueying |  22 | F      |       6 |      NULL |

|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |

|    23 | Ma Chao       |  23 | M      |       4 |      NULL |

|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |

|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |

+——-+—————+—–+——–+———+———–+

 

内连接:INNER JOIN

效果:如果表中有至少一个匹配,则返回行

内连接

写法一:旧写法

MariaDB [hellodb]> select s.name,t.name from students as s,teachers as t where s.teacherid=t.tid;

+————-+—————+

| name        | name          |

+————-+—————+

| Yu Yutong   | Song Jiang    |

| Shi Zhongyu | Miejue Shitai |

| Ding Dian   | Lin Chaoying  |

+————-+—————+

 

MariaDB [hellodb]> select s.name as 学生姓名,t.name as 讲师姓名 from students as s,teachers as t where s.teacherid=t.tid;   

+————–+—————+

| 学生姓名     | 讲师姓名      |

+————–+—————+

| Yu Yutong    | Song Jiang    |

| Shi Zhongyu  | Miejue Shitai |

| Ding Dian    | Lin Chaoying  |

+————–+—————+

 

写法二:SQL标准写法,适合其他类型数据库

MariaDB [hellodb]> select s.name as 学生姓名,t.name as 讲师姓名 from students as s inner join teachers as t on s.teacherid=t.tid;

+————–+—————+

| 学生姓名     | 讲师姓名      |

+————–+—————+

| Yu Yutong    | Song Jiang    |

| Shi Zhongyu  | Miejue Shitai |

| Ding Dian    | Lin Chaoying  |

+————–+—————+

 

交叉连接:CROSS JOIN

效果:用于生成两张表的笛卡尔集,笛卡尔集的列数为每个表的列数之

和,笛卡尔集的行数为每个表的行数相乘。我们经常做的多表查询就是在笛卡

尔集中通过筛选条件得出的数据,所以笛卡尔集是多表查询的基础。

 

MariaDB [hellodb]> select * from students cross join teachers;

+——-+—————+—–+——–+———+———–+—–+—————+—–+——–+

| StuID | Name          | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |

+——-+—————+—–+——–+———+———–+—–+—————+—–+——–+

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   1 | Song Jiang    |  45 | M      |

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  94 | M      |

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   4 | Lin Chaoying  |  93 | F      |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |   1 | Song Jiang    |  45 | M      |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |   2 | Zhang Sanfeng |  94 | M      |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |   3 | Miejue Shitai |  77 | F      |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |   4 | Lin Chaoying  |  93 | F      |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |   1 | Song Jiang    |  45 | M      |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |   2 | Zhang Sanfeng |  94 | M      |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |   3 | Miejue Shitai |  77 | F      |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |   4 | Lin Chaoying  |  93 | F      |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |   1 | Song Jiang    |  45 | M      |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |   2 | Zhang Sanfeng |  94 | M      |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |   3 | Miejue Shitai |  77 | F      |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |

…….

 

左外连接:LEFT JOIN

功能:即使右表中没有匹配,也从左表返回所有的行

注释:在某些数据库中,LEFT JOIN 称为 LEFT OUTER JOIN

左连接

MariaDB [hellodb]> select s.name as studentname,t.name as teachername from students as s left outer join teachers as t on s.teacherid=t.tid;

+—————+—————+

| studentname   | teachername   |

+—————+—————+

| Shi Zhongyu   | Miejue Shitai |

| Shi Potian    | NULL          |

| Xie Yanke     | NULL          |

| Ding Dian     | Lin Chaoying  |

| Yu Yutong     | Song Jiang    |

| Shi Qing      | NULL          |

| Xi Ren        | NULL          |

| Lin Daiyu     | NULL          |

| Ren Yingying  | NULL          |

| Yue Lingshan  | NULL          |

| Yuan Chengzhi | NULL          |

| Wen Qingqing  | NULL          |

| Tian Boguang  | NULL          |

| Lu Wushuang   | NULL          |

| Duan Yu       | NULL          |

| Xu Zhu        | NULL          |

| Lin Chong     | NULL          |

| Hua Rong      | NULL          |

| Xue Baochai   | NULL          |

| Diao Chan     | NULL          |

| Huang Yueying | NULL          |

| Xiao Qiao     | NULL          |

| Ma Chao       | NULL          |

| Xu Xian       | NULL          |

| Sun Dasheng   | NULL          |

+—————+—————+

 

右外连接:RIGHT JOIN

效果:即使左表中没有匹配,也从右表返回所有的行

注释:在某些数据库中,LEFT JOIN 称为 RIGHT OUTER JOIN

右连接

MariaDB [hellodb]> select s.name as studentname,t.name as teachername from students as s right outer join teachers as t on s.teacherid=t.tid;   

+————-+—————+

| studentname | teachername   |

+————-+—————+

| Shi Zhongyu | Miejue Shitai |

| Ding Dian   | Lin Chaoying  |

| Yu Yutong   | Song Jiang    |

| NULL        | Zhang Sanfeng |

+————-+—————+

 

 

联合查询:union

效果:两张表上下连起来,类似cat a b的效果

MariaDB [hellodb]> select name from students union select name from teachers;

+—————+

| name          |

+—————+

| Shi Zhongyu   |

| Shi Potian    |

| Xie Yanke     |

| Ding Dian     |

| Yu Yutong     |

| Shi Qing      |

| Xi Ren        |

| Lin Daiyu     |

| Ren Yingying  |

| Yue Lingshan  |

| Yuan Chengzhi |

| Wen Qingqing  |

| Tian Boguang  |

| Lu Wushuang   |

| Duan Yu       |

| Xu Zhu        |

| Lin Chong     |

| Hua Rong      |

| Xue Baochai   |

| Diao Chan     |

| Huang Yueying |

| Xiao Qiao     |

| Ma Chao       |

| Xu Xian       |

| Sun Dasheng   |

| Song Jiang    |

| Zhang Sanfeng |

| Miejue Shitai |

| Lin Chaoying  |

+—————+

29 rows in set (0.00 sec)

 

自连接

功能:将一张表想象成两张表,自己连接自己

MariaDB [hellodb]> select s1.name as emp,s2.name as leader from students as s1 inner join students as s2 on s1.teacherid=s2.stuid;  

+————-+————-+

| emp         | leader      |

+————-+————-+

| Shi Zhongyu | Xie Yanke   |

| Shi Potian  | Xi Ren      |

| Xie Yanke   | Xu Zhu      |

| Ding Dian   | Ding Dian   |

| Yu Yutong   | Shi Zhongyu |

+————-+————-+

 

左自连接

MariaDB [hellodb]> select s1.name as emp,s2.name as leader from students as s1 left outer join students as s2 on s1.teacherid=s2.stuid;        

+—————+————-+

| emp           | leader      |

+—————+————-+

| Shi Zhongyu   | Xie Yanke   |

| Shi Potian    | Xi Ren      |

| Xie Yanke     | Xu Zhu      |

| Ding Dian     | Ding Dian   |

| Yu Yutong     | Shi Zhongyu |

| Shi Qing      | NULL        |

| Xi Ren        | NULL        |

| Lin Daiyu     | NULL        |

| Ren Yingying  | NULL        |

| Yue Lingshan  | NULL        |

| Yuan Chengzhi | NULL        |

| Wen Qingqing  | NULL        |

| Tian Boguang  | NULL        |

| Lu Wushuang   | NULL        |

| Duan Yu       | NULL        |

| Xu Zhu        | NULL        |

| Lin Chong     | NULL        |

| Hua Rong      | NULL        |

| Xue Baochai   | NULL        |

| Diao Chan     | NULL        |

| Huang Yueying | NULL        |

| Xiao Qiao     | NULL        |

| Ma Chao       | NULL        |

| Xu Xian       | NULL        |

| Sun Dasheng   | NULL        |

+—————+————-+

 

右自连接

MariaDB [hellodb]> select s1.name as emp,s2.name as leader from students as s1 right join students as s2 on s1.stuid=s2.teacherid;   

+————-+—————+

| emp         | leader        |

+————-+—————+

| Xie Yanke   | Shi Zhongyu   |

| Xi Ren      | Shi Potian    |

| Xu Zhu      | Xie Yanke     |

| Ding Dian   | Ding Dian     |

| Shi Zhongyu | Yu Yutong     |

| NULL        | Shi Qing      |

| NULL        | Xi Ren        |

| NULL        | Lin Daiyu     |

| NULL        | Ren Yingying  |

| NULL        | Yue Lingshan  |

| NULL        | Yuan Chengzhi |

| NULL        | Wen Qingqing  |

| NULL        | Tian Boguang  |

| NULL        | Lu Wushuang   |

| NULL        | Duan Yu       |

| NULL        | Xu Zhu        |

| NULL        | Lin Chong     |

| NULL        | Hua Rong      |

| NULL        | Xue Baochai   |

| NULL        | Diao Chan     |

| NULL        | Huang Yueying |

| NULL        | Xiao Qiao     |

| NULL        | Ma Chao       |

| NULL        | Xu Xian       |

| NULL        | Sun Dasheng   |

+————-+—————+

 

子查询

下表是内连接查询姓名和分数信息

MariaDB [hellodb]> select students.name,scores.score from students inner join scores on students.stuid=scores.id;

+—————+——-+

| name          | score |

+—————+——-+

| Shi Zhongyu   |    77 |

| Shi Potian    |    93 |

| Xie Yanke     |    47 |

| Ding Dian     |    97 |

| Yu Yutong     |    88 |

| Shi Qing      |    75 |

| Xi Ren        |    71 |

| Lin Daiyu     |    89 |

| Ren Yingying  |    39 |

| Yue Lingshan  |    63 |

| Yuan Chengzhi |    96 |

| Wen Qingqing  |    86 |

| Tian Boguang  |    83 |

| Lu Wushuang   |    57 |

| Duan Yu       |    93 |

+—————+——-+

 

取大于平均成绩的同学和成绩

MariaDB [hellodb]> select students.name,scores.score from students inner join scores on students.stuid=scores.id and score > (select avg(score) from scores);

+—————+——-+

| name          | score |

+—————+——-+

| Shi Zhongyu   |    77 |

| Shi Potian    |    93 |

| Ding Dian     |    97 |

| Yu Yutong     |    88 |

| Lin Daiyu     |    89 |

| Yuan Chengzhi |    96 |

| Wen Qingqing  |    86 |

| Tian Boguang  |    83 |

| Duan Yu       |    93 |

+—————+——-+

 

六、视图VIEW

视图:VIEW,虚表,保存有实表的查询结果

类似于shell中起个别名

视图不存数据,修改视图实际是修改了背后的表

物化视图:视图在磁盘上也占空间

 

示例:

MariaDB [hellodb]> create view view_students as select stuid,name from students;

MariaDB [hellodb]> select * from view_students;

+——-+—————+

| stuid | name          |

+——-+—————+

|     1 | Shi Zhongyu   |

|     2 | Shi Potian    |

|     3 | Xie Yanke     |

|     4 | Ding Dian     |

|     5 | Yu Yutong     |

|     6 | Shi Qing      |

|     7 | Xi Ren        |

|     8 | Lin Daiyu     |

|     9 | Ren Yingying  |

|    10 | Yue Lingshan  |

|    11 | Yuan Chengzhi |

|    12 | Wen Qingqing  |

|    13 | Tian Boguang  |

|    14 | Lu Wushuang   |

|    15 | Duan Yu       |

|    16 | Xu Zhu        |

|    17 | Lin Chong     |

|    18 | Hua Rong      |

|    19 | Xue Baochai   |

|    20 | Diao Chan     |

|    21 | Huang Yueying |

|    22 | Xiao Qiao     |

|    23 | Ma Chao       |

|    24 | Xu Xian       |

|    25 | Sun Dasheng   |

+——-+—————+

25 rows in set (0.00 sec)

 

 

判断一个表是否为视图:Comment状态

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| toc               |

| view_students     |

+——————-+

 

MariaDB [hellodb]> show table status like ‘view_students’\G; 

*************************** 1. row ***************************

Name: view_students

Engine: NULL

Version: NULL

Row_format: NULL

Rows: NULL

Avg_row_length: NULL

Data_length: NULL

Max_data_length: NULL

Index_length: NULL

Data_free: NULL

Auto_increment: NULL

Create_time: NULL

Update_time: NULL

Check_time: NULL

Collation: NULL

Checksum: NULL

Create_options: NULL

        Comment: VIEW

 

MariaDB [hellodb]> show table status like ‘students’\G;    

*************************** 1. row ***************************

Name: students

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 25

Avg_row_length: 655

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 9437184

Auto_increment: 26

Create_time: 2018-06-06 21:25:56

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

 

删除视图

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| toc               |

| view_goodstudent  |

| view_students     |

+——————-+

 

MariaDB [hellodb]> drop view view_goodstudent;

本文来自投稿,不代表Linux运维部落立场,如若转载,请注明出处:http://www.178linux.com/100561

联系我们

400-080-6560

在线咨询

工作时间:周一至周五,9:30-18:30,节假日同时也值班

QR code