当前位置：Gxlcms > 数据库问题 > 数据库--mysql

数据库--mysql

时间：2021-07-01 10:21:17 帮助过：7人阅读

1.数据库(database,DB)
是指长期存储在计算机内的，有组织，可共享的数据的集合，存储数据的仓库。
2.数据库管理系统软件
(1)数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。
(2)数据库管理系统对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。大部分DBMS提供数据定义语言DDL（Data Definition Language）和数据操作语言DML（Data Manipulation Language），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。
(3)数据库管理系统是数据库系统的核心，是管理数据库的软件。数据库管理系统就是实现把用户意义下抽象的逻辑数据处理，转换成为计算机中具体的物理数据处理的软件。有了数据库管理系统，用户就可以在抽象意义下处理数据，而不必顾及这些数据在计算机中的布局和物理位置。
(4)常见的数据库管理软件：甲骨文的oracle,IBM的db2，sql server, Access,Mysql(开源,免费,跨平台)
3.数据库系统DBS(Data Base System,简称DBS)
通常由软件、数据库和数据管理员组成。其软件主要包括操作系统、各种宿主语言、实用程序以及数据库管理系统。数据库由数据库管理系统统一管理，数据的插入、修改和检索均要通过数据库管理系统进行。数据管理员负责创建、监控和维护整个数据库，使数据能被任何有权使用的人有效使用。
二.mysql数据库管理软件
1.sql及其规范
(1)sql：是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。
<1>在数据库系统中，SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写，表名库名小写；
<2>SQL语句可单行或多行书写，以“;”结尾。关键词不能跨多行或简写。
<3>用空格和缩进来提高语句的可读性。子句通常位于独立行，便于编辑，提高可读性。
<4>注释：单行注释：-- 多行注释：/*......*/
<5>sql语句可以折行操作
<6>sql语句包含：DDL定义语言,DML操作语言和DCL控制语言
2.数据库操作(DDL)
(1)创建数据库(在磁盘上创建一个对应的文件夹)
语法：create database 数据库名字;
语句：

create database db_name;

(2)查看数据库
<1>查看所有数据库

show databases;

<2>查看数据库的创建方式

show create database db_name;

+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| boss_ui | CREATE DATABASE `db_name` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set
(3)修改数据库编码
语法：alter database 库名 character set utf8;
(4)删除数据库
语法：drop database 库名;
(5)切换数据库
语法：use 库名;
注意：进入到某个数据库后没办法再退回之前状态，但可以通过use进行切换查看当前使用的数据库 select database();
3.mysql数据类型,MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
(1)数值类型
<1>TINYINT(及小型)，年龄，包含在0-255之间
<2>SMALLINT(小型)，端口号，包含在0-65535之间
<3>MEDIUMINT(中型)，中小型网站注册会员，1600万够用
<4>INT(普通型)，身份证编号，42亿可以用很久
<5>BIGINT(大型)，微博量，几百亿
注意：由于mysql中不存在布尔型，所以一般来说我们可以使用TINYINT(1)来创建字段，用0表示false,1表示true
(2)日期/时间类型分别为：DATE,DATETIME,TIME,YEAR,TIMESTAMP。
<1>YEAR类型：包含1个字节，最小值1901，最大值2155
YEAR占用1个字节，包含年份，长度默认为4位，无法设置
<2>TIME类型：包含3个字节，最小值-838:59:89，最大值838:59:59
TIME占用3个字节，包含时分秒，长度0到6之间，用于设置微秒。对于TIME的范围是-838到838的原因，是印伟TIME类型不但可以保存一天的时，还可以包含时间之间的间隔。
<3>DATE类型：包含4个字节，最小值1000-01-01，最大值9999-12-31
DATE占用3个字节，包含年月日，范围和DATETIME一样。DATE长度是0，无法设置。
<4>TIMESTAMP类型：包含4个字节，最小值1970-01-01 00:00:00，最大值2038-01-19 03:14:07
TIMESTAMP占4个字节，可以包含完整的年月日时分秒，无法指定长度，不能存储微秒。范围只能存储1970年至2038年，对于想存储前后久远的时间就无法满足了。比较合适存储经常插入或者更新日期为当前系统时间的环境，比如注册时间或者登录时间的存储，由于TIMESTAMP固定为19个字符，且范围比DATETIME小，所以所占空间就小。
TIMESTAMP有几个特点：
①当更新一条数据的时候，设置此类型根据当前系统更新可以自动更新时间
②如果插入一条NULL，也会自动插入当前系统时间。
③创建字段时，系统会给一个默认值
④会根据当前时区来存储和查询时间，存储时对当前时区进行转换，查询时再转换为当前的时区。
查看当前时区：默认是东八区
语句：

SHOW VARIABLES LIKE ‘%time_zone%‘;

SET time_zone=‘+9:00‘;

<5>DATETIME类型：包含8个字节，最小值1000-01-01 00:00:00，最大值9999-12-31 23:59:59
DATETIME占用8个字节，可以包含完整的年月日时分秒，范围很大不必考虑。当创建类型的时候，可以设置它的长度0到6位之间，如果是0，只包含年月日时分秒；如果是1-6之间，就包含M为的微秒。DATETIME类型直接保存日期时间格式，取值赋值也比较方便，并且没有时区的问题，问题是占用空间最大。
注意：对于日期格式要求相对宽松，主要有以下几种：
①2017-10-10 10:10:10
②2017/10/10 10+10+10
③20171010101010
④17/10/10 10@10@10
(3)字符串(字符)类型
(1)CHAR字节M(0-255)字节，设置M为固定长度的字符
(2)VARCHAR字节M(0-N)字符，设置M为最大长度的字符，最大的字节为65535，在UTF8下，N最大是21844
CHAR是保存定长字符串，而VARCHAR则是保存变长字符串。CHAR(5)表示必须保存5个字符，而VARCHAR(5)则表示最大保存字符为5。
创建CHAR(5)和VARCHAR(5)，分别输入一个字符：“a”。通过查询语句来查看他们的长度
查看CHAR的长度
SELECT LENGTH(char1) FROM think_test;
+---------------+
| LENGTH(char1) |
+---------------+
|             1 |
+---------------+
1 row in set
查看varchar的长度
SELECT LENGTH(varchar1) FROM think_test;
+------------------+
| LENGTH(varchar1) |
+------------------+
|                1 |
+------------------+
1 row in set
这里的char和varchar的长度俩个结果都为1，单位是字节，也就是说"a"字符插入到CHAR和VARCHAR中所占的空间为1个字节，而实际上CHAR占了5个字节，VARCHAR为1+1=2个字节
虽然在SELECT语句自动删除了空白，但CHAR类型内部还是存储了5个字符。可以通过SQL_MODE设置为PAD_CHAR_TO_FULL_LENGTH来查看完整的长度。
SET SQL_MODE=‘PAD_CHAR_TO_FULL_LENGTH‘;
Query OK, 0 rows affected
设置完整的长度后查看CHAR的长度
SELECT LENGTH(char1) FROM think_test;
+---------------+
| LENGTH(char1) |
+---------------+
|             5 |
+---------------+
1 row in set
如果是UTF8编码下，长度为5的CHAR类型，最多插入5个字符，最多可以存储15字节，也就是5个汉子的内容。因为一个汉子占3个字节。
由于CHAR类型是定长，MYSQL会根据定义的长度进行分配空间，在处理速度上比VARCHAR快的多，所以适合存储如手机，身份证这种定长的字符，否则就会造成浪费。、
那么CHAR类型最大可以插入255个字符，最多可以存储765个字节。
对于VARCHAR变长类型，适合存储不定长度的字符串。不管设置了多长的字符，它会按需求分配字节存储空间，不会浪费，当然，在插入0-255字节时，会占用额外1个字节用于计算长度，大于255的则需要2个字节，那么其实真正有效存储的字节数为65533。而对于UTF8编码下，一个字符最大占用3个字节，那么其实VARCHAR(M)中的M最大21844个字符。
注意：虽然VARCHAR理论上最大长度是21844字符，当这个表还包含其他字段时，还要减去其他字段所占的字符数。
在变长VARCHAR(5)和VARCHAR(1000)中，保存童颜的一条数据"a",所占用的空间是一样的，但问题是，长度设置越大，在分配内存处理的时候会消耗更多。所以，我们最好的策略是设置一个最恰当的长度。
(3)BINARY字节M(0-255)字节，存储固定长度的二进制字符串
(4)VARBINARY字节M(0-65535)字节，存储最大长度的字节，最大是65533
BINARY和VARBINARY是采用二进制存储的，没有字符集概念，意义在于防止字符集的问题导致数据丢失，存储中文会占用俩个字符，会乱码，半截会问号。因为是蚕蛹二进制存储，在比较字符和排序的时候，都是二进制进行的，所以只有需要操作二进制时才需要使用。
上面四个了解定长和变长的字符类型，这四种类型都适合短字符存储。那么还有八种适合文本内容的大数据类型：TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT,TINYBLOG,BLOB,MEDIUMTEXT,LONGTEXT。这里比较主要的就是TEXT类型和BLOB类型，一个是普通文本类型，一个是二进制存储的文本类型。
(5)TINYTEXT范围：0-255所占空间是本身长度+2
(6)TEXT范围：0-65535所占空间是本身长度+2
(7)MEDIUMTEXTf范围：0-167772150所占空间是本身长度+3
(8)LONGTEXT范围：0-4294967295所占空间是本身长度+4
一般我们把TEXT等类型理解为数据超大的VARCHAR类型，当我们输入的字符长度超过VARCHAR本身最大长度，就会自动换成MEDIUMTEXT或LONGTEXT俩种类型。
(9)TINYBLOG范围：0-255所占空间是本身长度+2
(10)BLOB范围：0-65535所占空间是本身长度+2
(11)MEDIUMTEXT范围：0-16777215所占空间是本身长度+3
(12)LONGTEXT范围：0-4294967295所占空间是本身长度+4
注意：我们把短小定长的字符存储在CHAR类型，把短小变长的字符存VARCHAR，把内容较多的文本或各种内容存TEXT以及更大的类型中，另外，CHAR和VARCHAR在创建时可以设置默认值，而TEXT没有默认值，且TEXT不需要设置长度。
TEXT一组文本类型可以保存文章文字内容，而BLOB是二进制存储，可以保存比如图片之类的信息。
TEXT的范围和存储空间，我们可以用TINYEXT做测试。它的范围是(0-255)字节，那么我们通过村满发现，可以存85个汉子，即255字节，最终占用空间还要+2，其他三种以此类推。
(13)ENUM最大支持65535个成员
(14)SET最大支持64个成员
ENUM类型成为枚举类型，创建此类型一般用于单选操作，比如性别，最大支持65535个成员。1-255占1个字节，大于255占2个字节，用于单选。
SET和ENUM相对，创建此类型一般用于多选操作，比如兴趣爱好。1-8占1个字节，9-16占2个字节，17-24占3个字节，25-32占4个字节，33-64占8个字节，用于多选。
虽然显示的字符串，但存储的是int整型
<1>CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
<2>BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。
<3>BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
<4>有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。
4.数据表操作
(1)完整性约束条件之主键约束
<1>单字段主键特点：非空且唯一
<2>多字段联合主键:
1)一张表只能有一个主键
2)主键类型不一定非是整型
(2)创建表：
<1>语法：
create table 表名(
           id 类型 primary key(非空且唯一,能够唯一区分出当前记录的字段称为主键) auto_increment(自增,主键字段必须是数字类型)
            字段名类型[完整性约束条件],
            字段名类型,
            ...
            字段名类型
        );
<2>创建一个员工表xixi语句：

create table xixi(
            id int primary key auto_increment,  
            name varchar(20)
          );

alter table xixi add entry_date date not null;

alter table xixi
            add addr varchar(20),
            add age  int first,
            add birth varchar(20) after name;

alter table xixi DROP resume;

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| age        | int(11)     | YES |     | NULL    |                |
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20) | YES |     | NULL    |                |
| birth      | varchar(20) | YES |     | NULL    |                |
| entry_date | date        | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
2)删除多列语句：

alter table xixi DROP birth,DROP entry_date;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| age   | int(11)     | YES |     | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name | varchar(20) | YES |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
<3>修改一列类型
语法：alter table tab_name modify 列名类型 [完整性约束条件]［first｜after 字段名］;
修改xixi表里的age列的Type改成smallint，Null不能为空，Default默认值18移动到id的后面

alter table xixi modify age smallint not null default 18 after id;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| age   | smallint(6) | NO   |     | 18      |                |
| name | varchar(20) | YES |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
<4>修改列名
语法：alter table 表名 change [column] 列名新列名类型 [完整性约束条件]［first｜after 字段名］;
修改xixi表里的age改为Age，类型还是smallint放到name后面
语句：

alter table xixi change age Age smallint after name;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name | varchar(20) | YES |     | NULL    |                |
| Age   | smallint(6) | YES |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
<5>修改表名
语法：rename table 旧表名 to 新表名;

rename table xixi to wangxixi;

insert into xixi (id,name,birthday,salary) values (1,‘xixi‘,‘2000-20-20‘,2000);

+----+------+------------+---------+
| id | name | birthday | salary |
+----+------+------------+---------+
| 1 | xixi | 2000-20-20 | 2000.00 |
+----+------+------------+---------+
方式二：
语句：

insert into xixi (name,salary) values (‘wang‘,1000);

+----+------+------------+---------+
| id | name | birthday | salary |
+----+------+------------+---------+
| 1 | xixi | 2000-20-20 | 2000.00 |
| 2 | wang | NULL | 1000.00 |
+----+------+------------+---------+
<2>插入多条数据
方式一：

insert into xixi values
       (4,‘shi‘,‘2001-20-21‘,3000),
       (5,‘yao‘,‘2002-20-22‘,5000);

INSERT INTO xixi (name,salary) VALUES (‘chi‘,6000),
    (‘he‘,9000);

insert into xixi set id=8,name="wan";

+----+------+------------+---------+
| id | name | birthday   | salary |
+----+------+------------+---------+
| 1 | xixi | 2000-20-20 | 2000.00 |
| 2 | wang | NULL       | 1000.00 |
| 4 | shi | 2001-20-21 | 3000.00 |
| 5 | yao | 2002-20-22 | 5000.00 |
| 6 | chi | NULL       | 6000.00 |
| 7 | he   | NULL       | 9000.00 |
| 8 | wan | NULL       |    NULL |
+----+------+------------+---------+
(2)修改数据：
语法：update 表名 set 字段=修改的值,字段2=修改的值,......[where 语句]
UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行。

update xixi set birthday="2018-12-24" WHERE id=1;

修改id=1的birthday改为2018-12-24
+----+------+------------+---------+
| id | name | birthday   | salary |
+----+------+------------+---------+
| 1 | xixi | 2018-12-24 | 2000.00 |
| 2 | wang | NULL       | 1000.00 |
| 4 | shi | 2001-20-21 | 3000.00 |
| 5 | yao | 2002-20-22 | 5000.00 |
| 6 | chi | NULL       | 6000.00 |
| 7 | he   | NULL       | 9000.00 |
| 8 | wan | NULL       |    NULL |
+----+------+------------+---------+
(3)删除表纪录
语法：delete from 表名 [where ....]
如果不跟where语句则删除整张表中的数据，delete只能用来删除一行记录，delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在事务中恢复
<1>删除一条语句：删除表中名称为’chi’的记录。

delete from xixi where name=‘chi‘;

+----+------+------------+---------+
| id | name | birthday   | salary |
+----+------+------------+---------+
| 1 | xixi | 2018-12-24 | 2000.00 |
| 2 | wang | NULL       | 1000.00 |
| 4 | shi | 2001-20-21 | 3000.00 |
| 5 | yao | 2002-20-22 | 5000.00 |
| 7 | he   | NULL       | 9000.00 |
| 8 | wan | NULL       |    NULL |
+----+------+------------+---------+
<2>删除多条语句：删除表中名称为’he’和‘wan‘的记录。

delete from xixi where name=‘he‘ OR name=‘wan‘;

+----+------+------------+---------+
| id | name | birthday | salary |
+----+------+------------+---------+
| 1 | xixi | 2018-12-24 | 2000.00 |
| 2 | wang | NULL | 1000.00 |
| 4 | shi | 2001-20-21 | 3000.00 |
| 5 | yao | 2002-20-22 | 5000.00 |
+----+------+------------+---------+
<3>删除所有记录
方式一：删除方式是一条一条删
语法：delete from 表名; --注意auto_increment没有被重置:alter table employee auto_increment=1;

DELETE FROM xixi;

方式二：是把表删掉在创建一个一样的表
truncate table 表名;

truncate table xixi;

6.表纪录操作之查(单表查询)
<1>查找表内容
1)查询表里所有内容
语法：select * from 表名 --其中from指定从哪张表筛选，*表示查找所有列

select * from ExamResult;

+----+------+------+--------+-----------+
| id | name | JS   | Django | OpenStack |
+----+------+------+--------+-----------+
| 1 | xixi |   98 |     98 |        98 |
| 2 | wang |   35 |     98 |        67 |
| 3 | shi |   59 |     59 |        62 |
| 4 | yao |   88 |     89 |        82 |
| 5 | chi |   88 |     98 |        67 |
| 6 | wan |   86 |    100 |        55 |
+----+------+------+--------+-----------+
2)查询表中单独俩列：所有学生的name和对应的JS成绩
语法：select 字段1,字段2，...... from 表名   --可以指定一个列。表明确指定要查找的列

select name,JS from ExamResult;

+------+------+
| name | JS   |
+------+------+
| xixi |   98 |
| wang |   35 |
| shi |   59 |
| yao |   88 |
| chi |   88 |
| wan |   86 |
+------+------+
3)过滤表中重复数据
语法：select [distinct] 字段1,字段2，..... from 表名 --distinct用来剔除重复行。

select distinct JS from ExamResult;

+------+
| JS   |
+------+
|   98 |
|   35 |
|   59 |
|   88 |
|   86 |
+------+
<2>使用表达式查询
1)在所有学生分数上加10分特长分显示。

select name,JS+10,Django+10,OpenStack+10 from ExamResult;

+------+-------+-----------+--------------+
| name | JS+10 | Django+10 | OpenStack+10 |
+------+-------+-----------+--------------+
| xixi |   108 |       108 |          108 |
| wang |    45 |       108 |           77 |
| shi |    69 |        69 |           72 |
| yao |    98 |        99 |           92 |
| chi |    98 |       108 |           77 |
| wan |    96 |       110 |           65 |
+------+-------+-----------+--------------+
2)统计每个学生的总分显示

select name,JS+Django+OpenStack from ExamResult;

+------+---------------------+
| name | JS+Django+OpenStack |
+------+---------------------+
| xixi |                 294 |
| wang |                 200 |
| shi |                 180 |
| yao |                 259 |
| chi |                 253 |
| wan |                 241 |
+------+---------------------+
3)使用别名显示学生总分
语法：select 字段1 as 别名,字段2 as 别名 from 表名;

select name as 姓名,JS+Django+OpenStack as 总成绩 from ExamResult;

+--------+-----------+
| 姓名   | 总成绩    |
+--------+-----------+
| xixi   |       294 |
| wang   |       200 |
| shi    |       180 |
| yao    |       259 |
| chi    |       253 |
| wan    |       241 |
+--------+-----------+
<3>使用where子句，进行过滤查询
1)比较运算符：> < >= <= <> !=
查询姓名为xixi的学生成绩

select * from ExamResult where name=‘xixi‘;

+----+------+------+--------+-----------+
| id | name | JS | Django | OpenStack |
+----+------+------+--------+-----------+
| 1 | xixi | 98 | 98 | 98 |
+----+------+------+--------+-----------+
查询JS成绩大于80分的同学

select id,name,JS from ExamResult where JS>80;

+----+------+------+
| id | name | JS   |
+----+------+------+
| 1 | xixi |   98 |
| 4 | yao |   88 |
| 5 | chi |   88 |
| 6 | wan |   86 |
+----+------+------+
查询总分大于250分的所有同学

select name,JS+Django+OpenStack as 总成绩 from ExamResult where JS+Django+OpenStack>250 ;

+------+-----------+
| name | 总成绩    |
+------+-----------+
| xixi |       294 |
| yao |       259 |
| chi |       253 |
+------+-----------+
2)between 80 and 100：值在80到100之间
查询JS分数在80－100之间的同学

select name ,JS from ExamResult where JS between 80 and 100;

+------+------+
| name | JS   |
+------+------+
| xixi |   98 |
| yao |   88 |
| chi |   88 |
| wan |   86 |
+------+------+
3)in(80,90,100)：值是80或90或100
查询Django分数为59,89,100的同学。

select name ,Django from ExamResult where Django in (59,89,100);

+------+--------+
| name | Django |
+------+--------+
| shi |     59 |
| yao |     89 |
| wan |    100 |
+------+--------+
4)like ‘xixi%‘：pattern可以是%或者_，如果是%则表示任意多字符，此例如唐僧,唐国强，如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
查询所有名字xi开头的学生成绩。

select * from ExamResult where name like ‘xi%‘;

select name from ExamResult where Django is null;

<4>Order by指定排序的列，排序的列即可是表中的列名，也可以是select语句后指定的别名。
语法：select *|field1,field2... from tab_name order by field [Asc|Desc] ---Asc升序、Desc 降序，其中asc为默认值ORDER BY子句应位于SELECT语句的结尾。
1)对JS成绩排序后输出(默认从小到大)。

select name,JS from ExamResult order by JS;

+------+------+
| name | JS   |
+------+------+
| wang |   35 |
| shi |   59 |
| wan |   86 |
| yao |   88 |
| chi |   88 |
| xixi |   98 |
+------+------+
2)对总分排序按从高到低的顺序输出
语法：select name,总成绩 from 表名 order by 总成绩 desc;

select name,(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)) 总成绩 from ExamResult order by 总成绩 desc;

+------+-----------+
| name | 总成绩    |
+------+-----------+
| xixi |       294 |
| yao |       259 |
| chi |       253 |
| wan |       241 |
| wang |       200 |
| shi |       180 |
+------+-----------+
3)对姓w开头的学生成绩从高到底排序输出
语法：select name,总成绩 from 表名 where like ‘w开头‘ order by 总成绩 desc;

select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))总成绩 from ExamResult where name like ‘w%‘ order by 总成绩 desc;

+------+-----------+
| name | 总成绩    |
+------+-----------+
| wan |       241 |
| wang |       200 |
+------+-----------+
<5>group by分组查询：按分组条件分组后每一组只会显示第一条记录
+----+------+------+--------+-----------+
| id | name | JS   | Django | OpenStack |
+----+------+------+--------+-----------+
| 1 | xixi |   98 |     98 |        98 |
| 2 | wang |   35 |     98 |        67 |
| 3 | shi |   59 |     59 |        62 |
| 4 | yao |   88 |     89 |        82 |
| 5 | chi |   88 |     98 |        67 |
| 6 | wan |   86 |    100 |        55 |
| 7 | xixi |   88 |     68 |        99 |
| 8 | xixi |   55 |     64 |        78 |
+----+------+------+--------+-----------+
1)按照字段名字筛选
按照name分类

select * from ExamResult group by name;

+----+------+------+--------+-----------+
| id | name | JS   | Django | OpenStack |
+----+------+------+--------+-----------+
| 5 | chi |   88 |     98 |        67 |
| 3 | shi |   59 |     59 |        62 |
| 6 | wan |   86 |    100 |        55 |
| 2 | wang |   35 |     98 |        67 |
| 1 | xixi |   98 |     98 |        98 |
| 4 | yao |   88 |     89 |        82 |
+----+------+------+--------+-----------+
2)按照位置字段筛选by 1等于第一列
按照name分类

select * from ExamResult group by 1;

+----+------+------+--------+-----------+
| id | name | JS   | Django | OpenStack |
+----+------+------+--------+-----------+
| 1 | xixi |   98 |     98 |        98 |
| 2 | wang |   35 |     98 |        67 |
| 3 | shi |   59 |     59 |        62 |
| 4 | yao |   88 |     89 |        82 |
| 5 | chi |   88 |     98 |        67 |
| 6 | wan |   86 |    100 |        55 |
| 7 | xixi |   88 |     68 |        99 |
| 8 | xixi |   55 |     64 |        78 |
+----+------+------+--------+-----------+
3)对成绩表按name类名分组后显示JS类成绩的总和

select name, sum(JS) from ExamResult group by name;

+------+---------+
| name | sum(JS) |
+------+---------+
| chi |      88 |
| shi |      59 |
| wan |      86 |
| wang |      35 |
| xixi |     241 |
| yao |      88 |
+------+---------+
4)对成绩表按name类名分组后，显示每一类名字的Django的分数总和>150的类的名字和django总分

select name, SUM(Django) from ExamResult group by name HAVING SUM(Django)>150;

+------+-------------+
| name | SUM(Django) |
+------+-------------+
| xixi | 230 |
+------+-------------+
having和where两者都可以对查询结果进行进一步的过滤，差别有：
-where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
-使用where语句的地方都可以用having进行替换
-having中可以用聚合函数，where中就不行。
<5>聚合函数：把要求的内容查出来再包上聚合函数即可。(一般和分组查询配合使用)
1)COUNT(列名)：count(*)统计所有行，count(字段)不统计null值
统计一个班级共有多少学生？先查出所有的学生，再用count包上

select count(*) from ExamResult;

+----------+
| count(*) |
+----------+
| 8 |
+----------+
统计JS成绩大于70的学生有多少个

select count(JS) from ExamResult where JS>70;

+-----------+
| count(JS) |
+-----------+
| 5 |
+-----------+
统计一个班级共有多少学生？先查出所有的学生，再用count包上

select count(name) from ExamResult where (ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))>280;

+-------------+
| count(name) |
+-------------+
| 1 |
+-------------+
2)AVG(列名)：
求一个班级JS平均分？先查出所有的JS分，然后用avg包上。

select avg(ifnull(JS,0)) from ExamResult;

+-------------------+
| avg(ifnull(JS,0)) |
+-------------------+
| 74.625 |
+-------------------+
求一个班级总分平均分:null和所有的数计算都是null，所以需要用ifnull将null转换为0

select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))) from ExamResult;

+----------------------------------------------------------+
| avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))) |
+----------------------------------------------------------+
| 234.875 |
+----------------------------------------------------------+
3) Max、Min
求班级所有成绩总和的最高分(数值范围在统计中特别有用)

select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))) 最高分 from ExamResult;

+-----------+
| 最高分 |
+-----------+
| 294 |
+-----------+
求班级所有成绩总和的最低分

select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))) 最低分 from ExamResult;

+-----------+
| 最低分 |
+-----------+
| 180 |
+-----------+
<6>limit
显示第一条

SELECT * from ExamResult limit 1;

SELECT * from ExamResult limit 2,4;

+----+------+------+--------+-----------+
| id | name | JS   | Django | OpenStack |
+----+------+------+--------+-----------+
| 3 | shi |   59 |     59 |        62 |
| 4 | yao |   88 |     89 |        82 |
| 5 | chi |   88 |     98 |        67 |
| 6 | wan |   86 |    100 |        55 |
+----+------+------+--------+-----------+
<7>使用正则表达式查询
ExamResult表里name字段xi开头的所有名字

SELECT 
                        
                            
                                < 上一篇
mysql 遍历所有的库并根据表和sql语句备份
                            
                            
                                 下一篇 >
mysqldump完全备份 和 二进制即时点还原数据库

数据库--mysql

人气教程排行