MySQL底子操作,关周到据库SQL之宗旨数据查询

MySQL基本总结操作

先进入Mysql容器。

[root@promote ~]# docker exec -it mysql /bin/bash
root@30d60b852cf5:/# mysql -uroot -p000000
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

新建一个数据库。

mysql> create database gubeiqing;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
 -------------------- 
| Database           |
 -------------------- 
| gubeiqing          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
 -------------------- 
5 rows in set (0.01 sec)

进入数据库,新建数据表。

mysql> use gubeiqing;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table gubeiqing_table(name varchar(20) not null , age varchar(20) not null);
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
 --------------------- 
| Tables_in_gubeiqing |
 --------------------- 
| gubeiqing_table     |
 --------------------- 
1 row in set (0.01 sec)

随后给数据库新扩大列,基本总结语法是:ALTER TABLE 表名 add column 列名 列类型 是否为空;

mysql> desc gubeiqing_table;
 ------- ------------- ------ ----- --------- ------- 
| Field | Type        | Null | Key | Default | Extra |
 ------- ------------- ------ ----- --------- ------- 
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | varchar(20) | NO   |     | NULL    |       |
 ------- ------------- ------ ----- --------- ------- 
2 rows in set (0.01 sec)

mysql> alter table gubeiqing_table add column job varchar(20) not null;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc gubeiqing_table;
 ------- ------------- ------ ----- --------- ------- 
| Field | Type        | Null | Key | Default | Extra |
 ------- ------------- ------ ----- --------- ------- 
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | varchar(20) | NO   |     | NULL    |       |
| job   | varchar(20) | NO   |     | NULL    |       |
 ------- ------------- ------ ----- --------- ------- 
3 rows in set (0.00 sec)

匡正列名,基本回顾语法是:ALTER TABLE 表名 change column 原列名 修改后的列名 列类型 是否为空;

mysql> desc gubeiqing_table;
 ------- ------------- ------ ----- --------- ------- 
| Field | Type        | Null | Key | Default | Extra |
 ------- ------------- ------ ----- --------- ------- 
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | varchar(20) | NO   |     | NULL    |       |
| job   | varchar(20) | NO   |     | NULL    |       |
 ------- ------------- ------ ----- --------- ------- 
3 rows in set (0.00 sec)

mysql> alter table gubeiqing_table change column job gbq_job varchar(20) not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc gubeiqing_table;
 --------- ------------- ------ ----- --------- ------- 
| Field   | Type        | Null | Key | Default | Extra |
 --------- ------------- ------ ----- --------- ------- 
| name    | varchar(20) | NO   |     | NULL    |       |
| age     | varchar(20) | NO   |     | NULL    |       |
| gbq_job | varchar(20) | NO   |     | NULL    |       |
 --------- ------------- ------ ----- --------- ------- 
3 rows in set (0.00 sec)

去除刚刚新扩大的列,基本总结语法是:ALTER TABLE 表名 drop column 列名;

mysql> desc gubeiqing_table;
 --------- ------------- ------ ----- --------- ------- 
| Field   | Type        | Null | Key | Default | Extra |
 --------- ------------- ------ ----- --------- ------- 
| name    | varchar(20) | NO   |     | NULL    |       |
| age     | varchar(20) | NO   |     | NULL    |       |
| gbq_job | varchar(20) | NO   |     | NULL    |       |
 --------- ------------- ------ ----- --------- ------- 
3 rows in set (0.00 sec)

mysql> alter table gubeiqing_table drop column gbq_job;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc gubeiqing_table;
 ------- ------------- ------ ----- --------- ------- 
| Field | Type        | Null | Key | Default | Extra |
 ------- ------------- ------ ----- --------- ------- 
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | varchar(20) | NO   |     | NULL    |       |
 ------- ------------- ------ ----- --------- ------- 
2 rows in set (0.00 sec)

然后说一下模糊寻觅,就比近来后要在数据Curry探求zhangsan的音讯,可是只记得zhang余下的部分都不记得了,那么就足以应用模糊搜索,基本总结语法是:SELECT * from 表名 WHERE 字段 LIKE '模糊字段';

mysql> select * from gubeiqing_table where name like 'zhang%';
 ---------- ----- 
| name     | age |
 ---------- ----- 
| zhangsan | 20  |
 ---------- ----- 
1 row in set (0.01 sec)

%用来代表不记得的风流倜傥部分,是通配符。
除了%之外还会有 _意味着别的单个字符,[ ]钦点范围或会集中的任何单个字符,[^]不归属钦定范围或集合的任何单个字符。

前言


上一篇关周到据库常用SQL语句语法大全最首若是关系型数据库大要结构,本文细说一下关系型数据库查询的SQL语法。

SQL数据查询

数据库底子知识

  1. 数据库顾客端
    MySQL作为数据库服务器来运作,任何知足mysql通讯专门的学问的软件都能够视作客商带来一而再再而三服务器。常用的图形化顾客端:mysql_front,navcat和依靠web的phpmyadmin,MySQL自带命令行顾客端。

  2. 命令行客商端
    拉开windows的命令行,运转mysql服务端进程,大家利用mysql顾客带给连接服务器。要是现身命令提醒如下所示,则表明未有布署境变量,招致系统找不到mysql.exe。

    C:User> mysql -uroot -p
    'mysql'不是内部或外部命令,也不是可运行的程序或批处理文件
    

    缓和方案

    1. 布置景况变量$PATH,钦点mysqlbin目录。

    2. 历次步入mysqlbin目录后,再运行mysql。

  3. 连日来服务器

    服务器地址(IP或域名):端口(3306) -u客户名 -p密码

    C:User> mysql -h localhost -uUsername -pPassword
    

    -h 假诺不写则默许连接localhost

    跻身刚安装好的MySQL数据库中,我们会发觉内置了多少个数据库information_schema(数据库基本讯息),mysql(顾客主旨音讯),performance_schema(数据库优化音讯)那八个放置数据库很主要,不能不理订正或删除。

语法回顾


SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>]…
  FROM <表名或视图名>[,<表名或视图名>]…
  [WHERE <条件表达式>]
  [GROUP BY <列名> [HAVING <条件表达式>]]
  [ORDER BY <列名> [ASC|DESC]…]

SQL查询语句的相继:SELECT、FROM、WHERE、GROUP BY、HAVING、O宝马X3DER BY。SELECT、FROM是必得的,HAVING子句只可以与GROUP BY搭配使用。

数据库入门语句

  1. 查看当前服务器上面有哪些库(database)

    show databases;
    
  2. 始建数据库

    create database DBName[字符集声明,整理集声明];
    
  3. 剔除数据库

    drop database test2;
    
  4. 改革数据库

    只能修改数据库的字符集和整理集,不能修改数据库的名字
    
  5. 筛选数据库

    use DBName;
    
  6. 翻开库下的表

    show tables; 
    
  7. 去除一张表

    drop table tableName;
    
  8. 修正表名

    rename table oldName to newName;
    
  9. 查看表结构

    desc tableName;
    
  10. 最简易的建表语句

    create table 表名(
    列1名称 列1类型,
    列2名称 列2类型 
    );
    
  11. 为留言本建一张表

    create table msg(
    id int,
    title varchar(60),
    name varchar(10),
    content varchar(1000)
    );
    
  12. 清除字符集的题材

    私下认可的建表日常用utf8,大家在windows下窗口是gbk,由此需求评释字符集。

所谓建表正是声称列的长河,数据是以文件的样式放在硬盘或内部存款和储蓄器中的,不相同的列类型占的空间不平等,选列的原则是够用又不浪费。

未雨积谷防饥筹划专门的职业


1.创办数据库和数据表
2.插入基本数据
3.本文以SQL Server为例介绍

-- 创建学生表
CREATE TABLE Students(
    Id int NOT NULL PRIMARY KEY,
    Name varchar(20) NOT NULL,
    Class varchar(15) NOT NULL,
    Gender varchar(10) NULL,
    Age int NULL,
    Phone varchar(15) NULL,
    [Address] varchar(100) NULL
)

-- 创建课程表
CREATE TABLE Courses(
    Id int NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name varchar(50) NOT NULL
);

-- 创建成绩表
CREATE TABLE Scores(
    Id int PRIMARY KEY IDENTITY(1,1),
    SId int NOT NULL,
    CId int NOT NULL,
    Grades decimal(5,2) NOT NULL,
    IsPassed bit NOT NULL
);

-- 插入学生表基础数据
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016001,'小明','一班','男',20,'18817716611','北京');
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016002,'小龙','一班','男',19,'18817716622','天津');
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016003,'小王','二班','男',20,'18817716633','北京');
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016004,'婷婷','一班','女',17,'18817716644','济南');
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016005,'张三','一班','男',19,'18817716655','北京');
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016006,'小赵','一班','男',20,'18817716666','北京');
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016007,'丽丽','二班','女',18,'18817716677','北京');
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016008,'花花','一班','女',19,'18817716688','沈阳');
INSERT INTO Students (Id,Name,Class,Gender,Age,Phone,[Address]) VALUES(2016009,'静静','二班','女',20,'18817716699','北京');

-- 插入课程表基础数据
INSERT INTO Courses(Name) VALUES('语文');
INSERT INTO Courses(Name) VALUES('数学');
INSERT INTO Courses(Name) VALUES('英语');

-- 插入成绩表基础数据
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016001,1,120,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016001,2,70,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016001,3,89,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016002,1,90,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016002,2,88,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016002,3,96,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016003,1,112,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016003,3,102,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016004,1,80,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016004,2,86,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016004,3,47,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016005,1,87,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016005,2,96,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016005,3,68,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016006,2,95,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016006,3,100,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016007,1,87,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016007,2,57,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016007,3,130,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016008,1,89,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016008,3,66,0);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016009,1,97,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016009,2,104,1);
INSERT INTO Scores(SId,CId,Grades,IsPassed) VALUES(2016009,3,68,1);

详整列类型

  1. 数值类型

    a. 整数型:tinyint smallint mediumint int bigint

    类型 字节 占位 无符号 有符号
    tinyint 1 8 0 ~ 2^8-1 -2^7 ~2^7-1
    smallint 2 16 0 ~ 2^16-1 -2^15 ~ 2^15 -1
    mediumint 3 24 0 ~ 2^24-1 -2^23 ~ 2^23 -1
    int 4 32 0 ~ 2^32-1 -2^31 ~ 2^31 -1

    暗中同意的莫西干发型类型是有标志的,假诺存储无符号的数据应彰显注脚

    tinyint(M) unsigned zerofill的含义

    M : 代表宽度,对存储范围无影响,在zerofill时才有含义。

    unsigned:无符号类型,对存款和储蓄范围有影响,即数值非负。

    zerofill:零填充(假若某列是zerofill默许便是unsigned)

    M和zerofill标志的字段是仅意气风发种显示效果,和数码的储存范围非亲非故。

    列能够表明私下认可值,而且推荐注解暗许值

    not null default 0
    

    b. 小数型

    1. 浮点型:float(M,D)(M代表总位数,D代表小数位)

    2. 定点型:decimal(M,D)

    以float(4,2)为例,有标识时表示范围-99.99 ~ 99.99,无符号时表示范围0 ~ 99.99
    decimal和float类型据有一定的字节数,为4字节或8字节。M<=23时占4个字节,M>23时占8个字节

    decimal类型比float类型特别正确。

  2. 字符类型

    定长类型:char(M),M代表宽度,0<=M<=255,就可以容纳的字符数。

    变长类型:varchar(M),M代表宽度,0<=M<=65535,就可以容纳的字节数。(utf-8字符可容纳2二〇〇四个左右)

    文件类型:text,能够存相当的大的文本段,寻觅速度稍慢,由此生机勃勃旦不是非常大的剧情,建议使用char和varchar来顶替,text不用加暗许值,加了也没用。

    char类型和varchar类型的可比

    char定长:M个字符,假使存的小于M个字符,实占M个字符

    varchar变长:M个字符,尽管存的小于M个字符,设为N,N<=M 实占N 1-2个字节

    类型 宽度 可存字符 实存字符 利用率
    char(M) M M i i/M <= 100%
    varchar(M) M M i字符 (1-2)字节 i/(i 1~2) < 100%

    char类型利用率或者完成百分之百,可是varchar类型一定达不到百分百。

    char类型的进程比较varchar类型会稍快一些,如若是存姓名等定长的字段,固然空间有浪费,不过思考速度使用char类型会越来越好有的。

    char类型若是远远不够M个字符,内部用空格补齐,抽出来的时候再把前边的空格去掉,所以纵然剧情最终有空格再一次抽出时会被破除。

    杜撰空间利用率和存款和储蓄速度,像顾客名,四字成语等长短相比牢固的字段应该使用char类型存款和储蓄,而个人简单介绍等不是专门长的文书内容应当利用varchar类型来存款和储蓄。

  3. 日期时间等级次序

    a. 年类型:year,多少个字节表示,范围 一九零零 ~ 2155 [ 0000标记错误选取 ]

    大器晚成经输入两位,"00 ~ 69"表示2000 ~ 2069,"70 ~ 99"表示1970 ~ 1999

    假若记得麻烦,输入的时候输入三位

    b. 日子类型:date,规范格式"Y-m-d" 范围 1000-01-01 ~ 9999-12-31

    c. 日子项目:time,标准格式"H:i:s" 范围 -838:59:59 ~ 838:59:59

    d. 日猪时间档案的次序:datetime,规范格式"Y-m-d H:i:s" 范围 "1000-01-01 ~ 9999-12-31 23:59:59"

    注意事项:在付出中,相当少使用时间档期的顺序来代表三个内需标准到秒的列,原因是即使日马时间项目能精确到秒并且有益于查看,不过不便利总计。消除办法是用时间戳来代表,即利用int类型来累积时间戳。

    时间戳:是1969-01-01 00:00:00到近日的秒数,日常存注册时间,商品宣布时间等,并非应用datetime而是使用时间戳,因为datetime固然直观,但总结相当不够方便,而用int类型存储时间戳,方便计算,对于展现来讲,也足以渔人之利格式化。

    对此相符于性别那样的字段,能够申明为tinyint,相同的时候约定0和1独家代表怎样,也足以应用枚举类型enum('男',‘女’),不过这种安插不适合关系型数据库的列的原子性设计基准,假诺某一列的源委仅具备二种值,那么相应单独设计一张表来保存那三种值。

    插入数据时除了数值类型其他项目数据应该加上引号

简短的建表演习

create table php313(
id int unsigned primary key auto_increment,
name char(3) not null default '',
age tinyint unsigned not null default 0,
email varchar(30) not null default '',
tel char(11) not null default '',
salary decimal(7,2) not null default 0.0,
riqi date not null default "2012-03-13"
)charset utf8;

粗略询问


大约询问只须求SELECTFROMWHERE3个荦荦大者字就可以兑现。

SELECT * FROM Students;
SELECT * FROM Students WHERE Class='一班';
SELECT * FROM Students WHERE Class='一班' AND Age = 20;

简言之询问结果

增加和删除改查操作

  1. insert语法

    insert into 表名(列1,...列n) values(值1,...值n);
    
    insert into 表名 values(值1,...值n);
    
    insert into 表名(列1,...列n) values(值1,...值n),(值1,...值n),(值1,...,值n);
    
  2. update语法

    update 表名 set 列1=新值1,列2=新值2 where expr;
    
  3. delete语法

    delete from 表名 where expr;
    

对where条件的明亮:对满意expr的行试行相应动作,假如不加where条件,则暗中认可对负有行实施动作。

  1. select语法

    查询的多种子句:where(条件查询),group by(分组),having(筛选),order by(排序),limit(节制结果条数)

    1. where expression

      用法:若是expression为真就将该行抽出。

    应用途合:各类口径查询场面,如按学号查询学子,按价格查询商品,按发表时间查询信息等。

    正如运算符:<(小于) >(大于) =(等于) <=(小于等于) >=(大于等于) !=或<>(不等于) in 在某集结内 between and 在某范围内。

    逻辑运算符:NOT或!(逻辑非) OR或||(逻辑或) AND或&&(逻辑与)。

    模糊查询

    %:通配任意字符 _ :通配单个字符

    事例:查找"三星"早先的有所商品

    select goods_id,cat_id,goods_name from goods where goods_name like '诺基亚%';
    
    select goods_id,cat_id,goods_name from goods where goods_name like '诺基亚__'; 
    
    1. group by col1,col2,...colN

    用法:把行按字段分组

    行使场所:常见于计算地方,如按栏目总括帖子数,总计种种人的平分成绩等。

    多个总括函数(也叫聚合函数):max()求最大,min()求最小,sum()求总和,avg()求平均,count()求总行数。

    select count(*) from goods;
    
    select cat_id,max(shop_price) from goods group by cat_id;      
    

    七个聚合函数必得功用于select和from之间的字段上,独有单独选用在某一列也许同盟group by使用时才有意义,不然查询出的列是不相相称的。
    要把列名当成变量来看待,可以给列,表和总括结果取小名。

    3.having expr

    用法:对查询结果的筛选

    where:针对原始表起效果,功能的年华在结果早先。

    having:对查询结果其职能,成效的小时在结果现在。

    事例:一张学子战绩信息表如下:

    姓名 科目 成绩
    张三 数学 90
    张三 语文 50
    张三 地理 40
    李四 语文 55
    李四 政治 45
    王五 政治 30

    询问挂科两门及两门以上的同室的平分成绩

    思路:先总计有所同学的平分成绩,然后新建三个字段作为是不是挂科标识,总结这几个符号的和,使用标准过滤。

    select name,avg(grade) as average,sum(grade < 60) as notpass from grade group by name having notpass > 2;
    

    4.order by

    用法:按贰个或多个字段对查询结果实行排序。

    知识点在本项目案例的使用:对栏目标物品按价格由高到低或由低到高排序

    知识点的接收地方描述:各类排序地方,如抢手消息,发帖状元等。

    据说字段能够升序排列也足以降序排列,暗中认可是升序排列。

    "字段名 desc"来声称按降序排列,"字段名 asc"来声称按升序排列。

    接纳order by举行排序时,只经过二个字段有异常的大概率排不出结果,此时能够多字段排序。

    order by 列1 [desc/asc],列2 [desc/asc]...
    

    5.limit [offset, ] n

    用法:limit在讲话的最后,起到限定条目的效果与利益。

    offset:偏移量 n:抽取的条数

    limit 2,3                 //取出第三条到第五条
    

    offset要是不写,相当于从头开端取

    本着sql语句优异的接头模型:

    where表明式:将表明式放在行中,看表明式是不是为真。

    列:通晓为变量,能够运算。

    抽出结果:掌握为一张有时表。

    子查询

    where型子查询:是指把内层查询的结果作为外层查询的比较标准

    #查询最新的商品
    select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods );
    
    #查询每个栏目下的最新商品
    select goods_id,goods_name from goods where goods_id in (select max(godos_id) from goods group by cat_id);
    

    from型子查询:是指把内层查询结果真是临时表,供外sql再一次询问。

    #查询每个栏目下最新的商品
    select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as tmp group by cat_id;
    
    #查询挂科两门以上的平均分
    select name,avg(grade) from grade where name in (select name from(select name,count(*) as notpass from grade where grade < 60 group by name having notpass >=2) as tmp) group by name;
    

    where型子查询和from型子查询的界别:当境遇两条相仿记录时,from型子查询大概会只询问到里面一条记下,而where型子查询会查询出富有记录。

    介意from型子查询的"as 有的时候表"不可能放任。

    exists型子查询:是指把外围的查询结果获得内层,看内层的询问结果是还是不是建立

    #查询有商品的栏目
    select cat_id,cat_name from category where exists(select * from goods where goods.cat_id=category.cat_id);
    

    一齐查询

    union的用法:合併查询结果。

    效果与利益:把两回或频仍的询问结果合併起来。

    必要:四回查询结果的列数大器晚成致,能够来自于一张或多张表。

    引入:查询的每一列对应的列类型意气风发致

    #查询大于5000或者小于2000元的商品
    select goods_id,goods_name,shop_price from goods where shop_price > 5000
    union 
    select goods_id,goods_name,shop_price from goods where shop_price < 20;
    
    #查询反馈表和评论表的内容并合并起来
    select user_name,user_email,user_content from feedback where status=1
    union 
    select user_name,email,content from comment;
    

    频仍sql语句抽出的列名能够不等同,当时以第三个sql抽取的列名字为准。如若不一致的言辞中收取的行有完全相像的(各个列的值都风流罗曼蒂克致),那么雷同的将要会联合(去重新)。固然不去重新,能够应用union all来钦定。

    #查询所有的合并记录,而不去除重复数据
    select id,sum(num) from (select * from ta union all select * from tb) as tmp group by id;
    

    要是子句中有order by或limit,应该用括号将子句包起来,推荐放到全部子句之后,即对最终合併的结果发挥效能。

    (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc)
    union
    (select goods_id,goods_name,shop_price from goods where cat_id=5 order by shop_price desc) 
    order by shop_price desc;  
    

    在子句中order by同盟limit使用时才有意义,即便order by不相配limit使用,会被语法解析器剖判时去除。

    (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by shop_price desc limit 3) 
    union 
    (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc limit 2);
    

    连接查询

    连年查询步骤:先将两张表连接起来,再接收口径过滤一些数据,最终像查询一张表那样查询联合表,注意字段要是有双重应该显得申明是哪一张表的字段。

    select goods_id,goods.cat_id,cat_name,goods_name,shop_price from goods left join category on goods.cat_id=category.cat_id;
    

    左连接 (left join)

    select 列1,列2,...列n from tableA left join tableB on tableA.列=tableB.列
    
    [此处表连接成一张大表,完全当成普通表来看待]
    
    where group by having等查询子句照常写
    

    右连接 (right join)

    select 列1,列2,...列n from tableA right join tableB on tableA.列=tableB.列
    
    [此处表连接成一张大表,完全当成普通表来看待]
    
    where group by having等查询子句照常写
    

    内连接 (inner join)

    select 列1,列2,...列n from tableA inner join tableB on tableA.列=tableB.列
    
    [此处表连接成一张大表,完全当成普通表来看待]
    
    where group by having等查询子句照常写
    

    左右连接以左表为准,去右表找相称数据,找不到相称用NULL补齐。

    左连接与右连接的涉及:左右连连能够并行转变,A left join B <=> B right join A。推荐把右连接转产生左连接来使用,并援用应用左连接代替右连接,那样做宽容性越来越好一点。

    内连接:查询左右表都有个别数据,即不用左右延续中的NULL那有个别,是左右一而再一而再的因陋就简。

    万风姿洒脱想要查出左右接连的并集,能够用union来到达目标,前段时间mysql不协助外连接outer join。

练习题

1.抽出goods表,category表,brand表连接查询得到以下字段

goods_id,goods_name,cat_id,cat_name,brand_id,brand_name

select goods_id,goods_name,category.cat_id,category.cat_name,brand.brand_id,brand.brand_name 
from goods left join category on goods.cat_id=category.cat_id 
left join brand on goods.brand_id=brand.brand_id;

2.面试题

Match赛程表

字段名称 字段类型 描述
matchID int 主键
hostTeamID int 主队的ID
guestTeamID int 客队的ID
matchResult varchar(20) 比赛结果,如(2,0)
matchTime date 比赛开始时间

Team参Gaby赛队伍容貌表

字段名称 字段类型 描述
teamID int 主键
teamName int 队伍名称

Match的hostTeamID与guestTeamID都与Team中的teamID关联,查出二零零七-6-1到二零零七-7-1以内进行的兼具竞赛,并且用以下格局列出:拜仁罗马2:0 不来梅

select t1.tname as hname,mres,t2.tname as gname,matime from m 
left join t as t1 on m.hid = t1.tid 
left join t as t2
on m.gid = t2.tid
where matime between '2006-06-01' and '2006-07-01';

别名/更名


语法

SELECT 字段名1 [AS] 别名 [,字段名1 AS 别名]… FROM <表名>

AS可以差不离
当别称中隐含非字母和下划线时要么是根本字时,要求丰裕单/双引号

示例

SELECT Id AS '学号',Name '姓名',Class ClassName FROM Students;

别名/更名

列的增加和删除改

  1. 增加列

    alter table 表名 add 列声明;
    

    日增的列暗中认可放在表的最后一列,能够用after来声称新扩张的列放在哪一列的末端

    alter table 表名 add 列声明 after 列名;
    

    比方新增加的列放在最前边,则使用first关键字

    alter table 表名 add 列声明 first;
    
  2. 修改列

    alter table 表名 change 列名 列声明;
    
  3. 删除列

    alter table 表名 drop 列名;
    

子查询(嵌套查询卡塔尔国


子查询也称嵌套查询,是指一个SELECT查询语句能够放置另一个SELECT查询语句之中。SQL中允好些个级嵌套,子查询在实质上运用中相当多。
三翻陆遍查询:涉及多个及以上的表查询为连接查询。

--查询二班学生成绩
SELECT * FROM Scores WHERE SId IN(SELECT Id FROM Students WHERE Class='二班')

子查询

视图 view

  1. 视图的概念

    视图是由询问结果造成的一张虚构表,未有全神关注的数目存在,只是大器晚成种查询产生的涉及。

  2. 视图的创办

    create view 视图名 select 语句;
    
  3. 视图的改换

    alter table 视图名 as select 语句;

  4. 视图的去除

    drop view 视图名;
    
  5. 视图的意义

    a.可以完毕简化查询的目标,对于一个多步复杂查询,能够将中等结果保存为视图,再指向视图举办查询。

    b.可以拓宽权力调节,把表的权力密封,不过开放相应的视图权限,视图里只支付一些数码。

    c.大数据分表时得以用到,譬喻表的行数超越200万时,就能变慢,能够把一张表的数码拆成四张表来存放,查询时能够用视图将四张表合并为一张来查询。

  6. 视图与表的关联

    视图是表的查询结果,表的数据修正会潜濡默化视图的结果,视图的增加和删除改也足以影响到表。可是视图并不总是能够增删改的,独有视图数据与表的数码生机勃勃黄金年代对应时,视图的数额是足以改良的。对于视图的insert操作还应注意视图必须带有全数表中未有暗许值的列。

  7. 视图的algorithm

    merge:当援引视图时,引用视图的话语与概念视图的话语合并。

    temptable:当引用视图时,依据视图的创始语句建立一张有的时候表。

    undefined:未定义,系统扶助于merge情势。

    merge方式与temptable形式的差异

    merge:意味着视图只是一个讲话准绳,当查问视图时,把询问视图的口舌(比方where子句)与创建时的口舌(譬喻where子句)等统后生可畏,剖判产生一条select语句。

    #创建视图的语句
    create view g2 as select goods_id,cat_id,goods_name,shop_price 
    from goods order by 
    cat_id asc,shop_price desc;
    
    #查询视图的语句
    select * from g2 group by cat_id;
    
    #最终执行的语句
    select goods_id,cat_id,goods_name,shop_price from goods 
    group by cat_id order by 
    cat_id asc,shop_price desc;
    

    temptable:依据创立语句瞬间开创一张不时表,然后查询视图的口舌从该有的时候表查询数据。通过呈现钦点temptable情势能够达到子查询的职能。

    create algorithm=temptable view g2 as 
    select goods_id,cat_id,goods_name,shop_price 
    from goods order by cat_id asc,shop_price desc;
    
    #最终执行两句话,首先取数据放在临时表,然后查询临时表。
    

聚合函数查询


聚合函数:是叁个值的成团为输入,再次来到单个值的函数。
SQL预订义了5个聚众函数:AVG(平均值卡塔 尔(阿拉伯语:قطر‎、MIN(最小值卡塔尔、MAX(最大值卡塔 尔(英语:State of Qatar)、SUM(求和卡塔尔国、COUNT(计数卡塔 尔(英语:State of Qatar)。
切实的数据库还有恐怕会预定义一些别的常用的函数,举个例子字符串相聚合函数、时间聚合函数……。

SELECT AVG(Age),MAX(Age),MIN(Age),SUM(Age),COUNT(Id) FROM Students;

聚合函数查询

字符集与查对集

1.字符集:MySQL的字符集设置特别灵活,能够安装以下多少个层面:服务器暗中认可字符集,数据库默许字符集,表私下认可字符集,列暗中认可字符集。借使某一流别未有一点点名字符集,则一而再再而三上一级。

以表注解为utf8为例,存款和储蓄在表中的数量是utf8字符集。

[图解MySQL字符设置]

1.告知服务器客商端的编码 character_set_client

2.告诉转换器调换的编码 character_set_connection

3.询问结果运用什么编码 character_set_results

设若上述三者为同一字符集N能够利用 set names N;

发出乱码的景况:

client申明与事实不符或result证明与客商端页面不符。

不见数据的状态:

character_set_connection和character_set_server的字符集比character_set_client小

2.校对集:核查集正是排序准则,黄金时代种字符集能够有二个或三个排序准则,utf8日常接收暗中同意的utf8_general_ci准绳,也足以接纳二进制法规utf8_bin

show character set;             //查看字符集

show collation;                 //查看校对集

show colation like 'utf8%';     //查看utf8字符集对应的校对集 

分组查询


使用GROUP BY子句可进行分组查询
在乎:分组查询的时候要在GROUP BY子句前面跟上有所查询字段的列表

--根据班级分组查询各班平均年龄、最大 年龄、最小年龄、年龄总和、班级人数
SELECT Class,AVG(Age),MAX(Age),MIN(Age),SUM(Age),COUNT(Id) FROM Students GROUP BY Class;

分组查询

HAVING子句

要是要求在分组前的数目开展限制,能够应用HAVING子句
HAVING子句只好与GROUP BY搭配使用

--根据班级分组查询各班平均年龄并且班级人数大于3人
SELECT Class,AVG(Age) FROM Students GROUP BY Class HAVING COUNT(Id)>3;

HAVING子句

HAVING子句和WHERE的区别

WHERE语句在GROUP BY语句在此之前,SQL会在分组此前总结WHERE语句;
HAVING语句在GROUP BY语句之后,SQL会在分组之后总计HAVING语句。

触发器 trigger

  1. 触发器的效益

    蹲点某种动作并触及某种动作,触发器能监视增删改三种操作并动身增加和删除改二种操作。

  2. 触发器的接收场景

    a. 当向一张表中加多或删除记录时,需求在相关表中进行同步操作。比方,当叁个订单位面积生产数量生时,订单的所购的物品的库存量应改相应回降。

    b. 当表上某列数据的值与任何表中的多少有挂钩时。例如当某顾客实行负债花费,可以在扭转订单时通过两全触发器推断顾客的累积欠钱是或不是超越了最大限度。

    c. 当需求对某张表举办追踪时。比方,当有新订单位面积产量生时,须要及时通报相关职员实行拍卖,那时能够在订单表上兼备增加触发器加以落到实处。

  3. 触发器的多少个成分

    a. 监视地点:table

    b. 监视事件:insert/update/delete

    c. 触发时间:after/before

    d. 触发事件:insert/update/delete

  4. 触发器的应用语法

    创设触发器的语法

    create trigger triggerName
    after/before insert/update/delete
    on tableName
    for each row        //这句话在MySQL数据库中是固定的
    begin
      sql语句;        //一句或多句insert/update/delete范围内
    end$
    

    去除触发器的语法

    drop trigger triggerName;
    
  5. 触发器援用行变量的值

    a. 对于insert来讲,新添的行用new来代表,行中每一列用new.列名来表示。

    b. 对于delete来讲,删除的行用old来表示,行中每一列用old.列名来表示。

    c. 对于update来讲,原本的行用old来代表,行中每一列的值用old.列名来代表;新增添的行用new来表示,行中每一列的值用new.列名来代表。

模糊查询


语法

SELECT 字段列表 FROM 表名 WHERE 字段 LIKE '<通配符>'

混淆查询是透过重大字LIKE和通配符达成的
_:任何单个字符(三个'_'只相配一个字符,八个字符就动用多少个_)
%:包罗零个或更加多字符的自便字符串(相称猖狂内容卡塔尔
[]:内定范围的字符(只优越[]内的字符卡塔尔国
[ ^]:不在钦点范围的字符(只相配除[]内的字符卡塔 尔(阿拉伯语:قطر‎
SQL中通配符可以勾兑使用

  • 单个字符相称
SELECT * FROM Students WHERE Name LIKE '小_';
SELECT * FROM Students WHERE Phone LIKE '188177166__';

模糊查询-单个字符相称

  • 大肆字符相称
SELECT * FROM Students WHERE Phone LIKE '188177166__';
SELECT * FROM Students WHERE Phone LIKE '%';
SELECT * FROM Students;

那三条SQL语句询问结果都后生可畏律只针对此表,原因是瞎猫碰上死耗子。(数据少,恰恰查询的结果是一切的内容卡塔尔国

混淆查询-大肆相称

  • 界定内查询
-- 查询所有手机号码结尾两位包含1和2的信息
SELECT * FROM Students WHERE Phone LIKE '188177166[12][12]';

模糊查询-范围内查询

  • 不在范围内查询
-- 查询所有手机号码结尾两位不包含1、3、5、7的信息
SELECT * FROM Students WHERE Phone LIKE '188177166[^1357][^1357]';

张冠李戴查询-不在范围内查询

至于SQL查询就到这里告生龙活虎段落,后边会更新相关内容。
生机勃勃旦你感觉有标题,款待和您协作探究。

正文采取文化分享签字-相符格局分享 4.0 国际许可合同开展许可。
基于简书上的著述创作。 可转发、引用,但需经本人同意后具名小编且注脚随笔出处,并以相符方式分享。

图片 1

知识共享许可商榷

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:MySQL底子操作,关周到据库SQL之宗旨数据查询

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。