数据库应用-mysql语句拾遗

发布时间：2020-05-24 21:01:07 来源：网络阅读：1469 作者：zyx1990zm 栏目：数据库

MySQL

（关系型数据库管理系统）

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，

每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database

RDBMS 术语

在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：

数据库: 数据库是一些关联表的集合。.
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余可以使系统速度更快。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

Mysql数据库

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

Mysql是开源的，所以你不需要支付额外的费用。
Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

MariaDB

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。

MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX存储引擎。

发展

成立于2009年，MySQL之父Michael “Monty” Widenius用他的新项目MariaDB完成了对MySQL的“反戈一击”。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。过去一年中，大型互联网用户以及Linux发行商纷纷抛弃MySQL，转投MariaDB阵营。MariaDB是目前最受关注的MySQL数据库衍生版，也被视为开源数据库MySQL的替代品。

MariaDB虽然被视为MySQL数据库的替代品，但它在扩展功能、存储引擎以及一些新的功能改进方面都强过MySQL。而且从MySQL迁移到MariaDB也是非常简单的：

1、数据和表定义文件（.frm）是二进制兼容的

2、所有客户端API、协议和结构都是完全一致的

3、所有文件名、二进制、路径、端口等都是一致的

4、所有的MySQL连接器，比如PHP、Perl、Python、Java、.NET、MyODBC、Ruby以及MySQL C connector等在MariaDB中都保持不变

5、mysql-client包在MariaDB服务器中也能够正常运行

6、共享的客户端库与MySQL也是二进制兼容的

也就是说，在大多数情况下，你完全可以卸载MySQL然后安装MariaDB，然后就可以像之前一样正常的运行。

起源

为何改了个名字呢，这其中是有些典故的。

MySQL之父Widenius先生离开了Sun之后，觉得依靠Sun/Oracle来发展MySQL，实在很不靠谱，于是决定另开分支，这个分支的名字叫做MariaDB。

MariaDB跟MySQL在绝大多数方面是兼容的，对于开发者来说，几乎感觉不到任何不同。目前MariaDB是发展最快的MySQL分支版本，新版本发布速度已经超过了Oracle官方的MySQL版本。

在Oracle控制下的MySQL开发，有两个主要问题：1. MySQL核心开发团队是封闭的，完全没有Oracle之外的成员参加。很多高手即使有心做贡献，也没办法做到。2. MySQL新版本的发布速度，在Oracle收购Sun之后大为减缓。Widenius有一个ppt，用数据比较了收购之前和之后新版本的发布速度。有很多bugfix和新的feature，都没有及时加入到发布版本之中。

以上这两个问题，导致了各个大公司，都开发了自己定制的MySQL版本，包括Yahoo!/Facebook/Google/阿里巴巴+淘宝网等等。

MySQL是开源社区的资产，任何个人/组织都无权据为己有。为了依靠广大MySQL社区的力量来更快速的发展MySQL，另外开分支是必须的。

MariaDB默认的存储引擎是Maria，不是MyISAM。Maria可以支持事务，但是默认情况下没有打开事务支持，因为事务支持对性能会有影响。可以通过以下语句，转换为支持事务的Maria引擎。ALTER TABLE `tablename` ENGINE=MARIA TRANSACTIONAL=1;

区别

这两个数据库究竟有什么本质的区别，我看mariadb文件夹BIN中还是mysql*.exe，除了MySQL会被ORACLE闭源外，而mariadb则开源，他俩之间到底还有什么本质区别没有？

区别一：

MariaDB不仅仅是Mysql的一个替代品，它的主要目的是创新和提高Mysql的技术。

区别二：

MySQL之父Widenius先生离开了Sun之后，觉得依靠Sun/Oracle来发展MySQL，实在很不靠谱，于是决定另开分支，这个分支的名字叫做MariaDB。

MariaDB 是一个采用Aria存储引擎的MySQL分支版本，是由原来 MySQL 的作者Michael Widenius创办的公司所开发的免费开源的数据库服务器。

这个项目的更多的代码都改编于 MySQL 6.0，例如 “pool of threads”功能提供解决多数据连接问题。MariaDB 5.1.41 RC可以到这里下载，32位和64位已编译Linux版本，还包括源代码包。MariaDB基于GPL 2.0发布。

所以对于大部分的MySQL用户来说，从现在主流的MySQL转到MariaDB应该是没有什么难度的

区别三：

LAMP架构盛极一时，这离不开MySQL的免费与易用，但是在Oracle收购了Sun之后，很多公司开始担忧MySQL的开源前景，而最近Oracle进一步闭源的举措更是让人难以安心，众多互联网公司纷纷开始寻求MySQL的替代方案。

不得不提的是Apple的远见，在Oracle收购Sun之初就宣布迁移到PostgreSQL。但PostgreSQL的设计初衷就不同于MySQL，并不是使用MySQL的大部分互联网公司合适的解决方案。除了Apple，Google、Facebook、Twitter也大量使用了MySQL，纷纷发布了自己的MySQL分支/补丁集，并为不少公司所采用。同时，MariaDB、Percona等MySQL分支也渐渐步入大众的视野。

根据Wikipedia介绍，MariaDB是一个社区驱动的、采用XtraDb存储引擎的MySQL分支版本，由MySQL创始人Michael Widenius带领开发，遵循GPL v2.0协议开源。因为MySQL创始人的介入，MariaDB备受关注，Drupal、MediaWiki、phpMyAdmin、WordPress等众多应用都宣布支持MariaDB。

从MySQL迁移到MariaDB Wikipedia力求开放

mysql和mariaDB开发团队一致，区别在于mysql已经被oracle闭源了而mariaDB是开源的但是当然不排除若干年后oracle再花几十亿美金收买mysql之父。

下载MySql

官网：http://dev.mysql.com/downloads/

关闭防火墙

实例：

一、概念：
   数据： data
   数据库： DB
   数据库管理系统：DBMS
   数据库系统：DBS
   MySQL：数据库  
   mysql：客户端命令（用来连接服务或发送sql指令）
   SQL：结构化查询语言 ，其中MySQL支持这个。
   SQL语言分为4个部分：DDL、DML、DQL、DCL
   
二、连接数据库：
   mysql -h 主机名 -u 用户名  -p密码  库名
   
   C:\>mysql  --采用匿名账号和密码登陆本机服务
   C:\>mysql -h localhost -u root -proot   --采用root账号和root密码登陆本机服务
   C:\>mysql -u root -p   --推荐方式默认登陆本机
     Enter password: ****

   C:\>mysql -u root -p lnmp61  --直接进入lnmp61数据库的方式登陆
   
三、授权：
    格式：grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';
    
    --实例：创建zhangsan账号，密码123，授权lnmp库下所有表的增/删/改/查数据,来源地不限
    mysql> grant select,insert,update,delete on lnmp61.* to zhangsan@'%' identified by '123';
    Query OK, 0 rows affected (0.00 sec)
    

四、SQL的基本操作
    mysql>show databases;     --查看当前用户下的所有数据库
    mysql>create database 数据库名; --创建数据库
    mysql> use test;    --选择进入test数据库
    mysql> drop database 数据库名;  --删除一个数据库 
    
    mysql> show tables; --查看当前库下的所有表格
    mysql> select database();  --查看当前所在的数据库
    mysql> desc tb1;  --查看tb1的表结构。
    mysql> create table demo(    --创建demo表格
        -> name varchar(16) not null,
        -> age int,
        -> sex enum('w','m') not null default 'm');
    Query OK, 0 rows affected (0.05 sec)

    mysql> desc demo;  --查看表结构
    +-------+---------------+------+-----+---------+-------+
    | Field | Type          | Null | Key | Default | Extra |
    +-------+---------------+------+-----+---------+-------+
    | name  | varchar(16)   | NO   |     | NULL    |       |
    | age   | int(11)       | YES  |     | NULL    |       |
    | sex   | enum('w','m') | NO   |     | m       |       |
    +-------+---------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
    mysql>drop table if exists mytab;  -- 尝试删除mytab表格
    
    
    --添加一条数据
    mysql> insert into demo(name,age,sex) values('zhangsan',20,'w');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into demo values('lisi',22,'m'); --不指定字段名来添加数据
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into demo(name,age) values('wangwu',23); --指定部分字段名来添加数据
    Query OK, 1 row affected (0.00 sec)
    
    --批量添加数据
    mysql> insert into demo(name,age,sex) values('aaa',21,'w'),("bbb",22,'m');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from demo; --查询数据
    
    mysql> update demo set age=24 where name='aaa';  --修改
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
        
    mysql> delete from demo where name='bbb';  --删除
    Query OK, 1 row affected (0.00 sec)
    
    
    mysql>\h   -- 快捷帮助
    mysql>\c   -- 取消命令输入
    mysql>\s   -- 查看当前数据库的状态
    mysql>\q   -- 退出mysql命令行
    
五、 MySQL数据库的数据类型：

    MySQL的数据类型分为四大类：数值类型、字串类型、日期类型、NULL。
    
    5.1 数值类型：
        *tinyint(1字节)
        smallint(2字节)
        mediumint(3字节)
        *int(4字节)     
        bigint(8字节)
        *float(4字节)   float(6,2)
        *double(8字节)  
        decimal(自定义)字串形数值
        
     5.2 字串类型
        普通字串
        *char  定长字串        char(8)  
        *varchar 可变字串 varchar(8)
        
        二进制类型
        tinyblob
        blob
        mediumblob
        longblob
        
        文本类型
        tinytext
        *text      常用于<textarea></textarea>
        mediumtext
        longtext
        
        *enum枚举
        set集合
        
    5.3 时间和日期类型：
        date  年月日
        time  时分秒
        datatime 年月日时分秒
        timestamp 时间戳
        year 年
    
    5.4 NULL值
        NULL意味着“没有值”或“未知值”
        可以测试某个值是否为NULL
        不能对NULL值进行算术计算
        对NULL值进行算术运算，其结果还是NULL
        0或NULL都意味着假，其余值都意味着真

    MySQL的运算符：
        算术运算符：+ - * / % 
        比较运算符：= > < >= <= <> != 
        数据库特有的比较：in，not in, is null,is not null,like, between and 
        逻辑运算符：and or not
    
 六、 表的字段约束：
        unsigned 无符号(正数)
        zerofill 前导零填充
        auto_increment  自增
        default    默认值
        not null  非空
        PRIMARY KEY 主键 （非null并不重复）
        unique 唯一性   （可以为null但不重复）
        index 常规索引
        
七: 建表语句格式：
     create table 表名(
       字段名 类型 [字段约束],
       字段名 类型 [字段约束],
       字段名 类型 [字段约束],
       ...
      );

    mysql> create table stu(
        -> id int unsigned not null auto_increment primary key,
        -> name varchar(8) not null unique,
        -> age tinyint unsigned,
        -> sex enum('m','w') not null default 'm',
        -> classid char(6)
        -> );
    Query OK, 0 rows affected (0.05 sec)

    
    mysql> desc stu;
    +---------+---------------------+------+-----+---------+----------------+
    | Field   | Type                | Null | Key | Default | Extra          |
    +---------+---------------------+------+-----+---------+----------------+
    | id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
    | name    | varchar(8)          | NO   | UNI | NULL    |                |
    | age     | tinyint(3) unsigned | YES  |     | NULL    |                |
    | sex     | enum('m','w')       | NO   |     | m       |                |
    | classid | char(6)             | YES  |     | NULL    |                |
    +---------+---------------------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)

    mysql> show create table stu\G  --查看建表的语句
    *************************** 1. row ***************************
           Table: stu
    Create Table: CREATE TABLE `stu` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `name` varchar(8) NOT NULL,
      `age` tinyint(3) unsigned default NULL,
      `sex` enum('m','w') NOT NULL default 'm',
      `classid` char(6) default NULL,
      PRIMARY KEY  (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    mysql>
    mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lnmp
    61');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into stu(name,age,sex,classid) values('lisi',22,'w','lnmp61');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into stu(name,age,classid) values('wangwu',21,'lnmp61');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into stu values(null,'qq',24,'w','lnmp62');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into stu values(null,'aa',20,'m','lamp62'),(null,'bb',25,'m','lnmp
    63');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0

    mysql> select * from stu;
    +----+----------+------+-----+---------+
    | id | name     | age  | sex | classid |
    +----+----------+------+-----+---------+
    |  1 | zhangsan |   20 | m   | lnmp61  |
    |  2 | lisi     |   22 | w   | lnmp61  |
    |  3 | wangwu   |   21 | m   | lnmp61  |
    |  4 | qq       |   24 | w   | lnmp62  |
    |  5 | aa       |   20 | m   | lnmp62  |
    |  6 | bb       |   25 | m   | lnmp63  |
    +----+----------+------+-----+---------+
    6 rows in set (0.00 sec)
    
    
八、修改表结构

 //需求分析->功能（模块）->数据库设计->找实体、找属性、找关系

深入部分：

mysql> desc stu;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name    | varchar(16)         | NO   | UNI | NULL    |                |
| sex     | enum('m','w')       | NO   |     | m       |                |
| age     | tinyint(3) unsigned | NO   |     | NULL    |                |
| classid | char(6)             | NO   |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

-- 标准格式添加，指定所有的字段，给定所有的值
mysql> insert into stu(id,name,sex,age,classid) values(null,'zhangsan','m',20,'lnmp80');
Query OK, 1 row affected (0.09 sec)

-- 给定部分字段添加值（值和字段要对应上）
mysql> insert into stu(name,age,classid) values('lisi',22,'lnmp80');
Query OK, 1 row affected (0.03 sec)

-- 不给字段信息，来添加值。（值和表结构对应上）
mysql> insert into stu values(null,'wangwu','w',25,'lnmp80');
Query OK, 1 row affected (0.08 sec)

-- 批量添加数据
mysql> insert into stu(name,sex,age,classid)
    -> values('qq','w',21,'lamp81'),
    -> ('aa','m',26,'lnmp81'),
    -> ('bb','w',20,'lnmp80');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from stu;
+----+----------+-----+-----+---------+
| id | name     | sex | age | classid |
+----+----------+-----+-----+---------+
|  1 | zhangsan | m   |  20 | lnmp80  |
|  2 | lisi     | m   |  22 | lnmp80  |
|  3 | wangwu   | w   |  25 | lnmp80  |
|  4 | qq       | w   |  21 | lnmp81  |
|  5 | aa       | m   |  26 | lnmp81  |
|  6 | bb       | w   |  20 | lnmp80  |
+----+----------+-----+-----+---------+
6 rows in set (0.00 sec)

mysql>

mysql> insert into stu values
    -> (null,'zhaoliu','w',19,'lnmp81'),
    -> (null,'tianqi','m',27,'lnmp82'),
    -> (null,'uu','w',26,'lnmp80'),
    -> (null,'yy','m',24,'lnmp82'),
    -> (null,'pp','w',29,'lnmp81');
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from stu;
+----+----------+-----+-----+---------+
| id | name     | sex | age | classid |
+----+----------+-----+-----+---------+
|  1 | zhangsan | m   |  20 | lnmp80  |
|  2 | lisi     | m   |  22 | lnmp80  |
|  3 | wangwu   | w   |  25 | lnmp80  |
|  4 | qq       | w   |  21 | lnmp81  |
|  5 | aa       | m   |  26 | lnmp81  |
|  6 | bb       | w   |  20 | lnmp80  |
|  7 | zhaoliu  | w   |  19 | lnmp81  |
|  8 | tianqi   | m   |  27 | lnmp82  |
|  9 | uu       | w   |  26 | lnmp80  |
| 10 | yy       | m   |  24 | lnmp82  |
| 11 | pp       | w   |  29 | lnmp81  |
+----+----------+-----+-----+---------+
11 rows in set (0.00 sec)

mysql>

--2. 数据的修改：
--===================================================
--格式： update 表名 set 字段名=修改值[,字段名=修改值[,....]] [where 条件] [order by 排序] [limit 部分数据]

--实例
--将学号id值为5的信息，年龄改为28，班级设为lamp82
mysql> update stu set age=28,classid='lamp82' where id=5
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu;
+----+----------+-----+-----+---------+
| id | name     | sex | age | classid |
+----+----------+-----+-----+---------+
|  1 | zhangsan | m   |  20 | lnmp80  |
|  2 | lisi     | m   |  22 | lnmp80  |
|  3 | wangwu   | w   |  25 | lnmp80  |
|  4 | qq       | w   |  21 | lnmp81  |
|  5 | aa       | m   |  28 | lnmp82  |
|  6 | bb       | w   |  20 | lnmp80  |
|  7 | zhaoliu  | w   |  19 | lnmp81  |
|  8 | tianqi   | m   |  27 | lnmp82  |
|  9 | uu       | w   |  26 | lnmp80  |
| 10 | yy       | m   |  24 | lnmp82  |
| 11 | pp       | w   |  29 | lnmp81  |
+----+----------+-----+-----+---------+
11 rows in set (0.00 sec)

mysql>

--3. 数据的删除
---===========================================================================
--格式： delete from 表名 [where 条件] [order by 排序] [limit 部分数据]

--删除实例：
  --1.删除id号为5的信息
 mysql> delete from stu where id=5;

  --2.删除班级为lnmp80的男生信息
 mysql> delete from stu where classid='lnmp80' and sex='m';
 
  --3.删除年龄在21到25之间的信息
 mysql> delete from stu where age>=21 and age<=25;
 mysql> delete from stu where age between 21 and 25;
 
  --4.删除年龄在21到25之外的信息
 mysql> delete from stu where age<21 or age>25;
 mysql> delete from stu where age not between 21 and 25;
 
 
 -- 4. 数据的查询
 --=======================================================
 --格式： select 字段名|* from 表名 
 --                   [ where 搜索条件]
 --                   [ group by 分组列名[having 分组后的子条件]]
 --                   [ order by 排序列名 [desc降序|asc升序(默认)]]
 --                   [ limit m[,n] 获取部分数据（分页） ]
 
 实例：
 mysql>
mysql> select * from stu;  --查看所有字段的所有信息
+----+----------+-----+-----+---------+
| id | name     | sex | age | classid |
+----+----------+-----+-----+---------+
|  1 | zhangsan | m   |  20 | lnmp80  |
|  2 | lisi     | m   |  22 | lnmp80  |
|  3 | wangwu   | w   |  25 | lnmp80  |
|  4 | qq       | w   |  21 | lnmp81  |
|  5 | aa       | m   |  28 | lnmp82  |
|  6 | bb       | w   |  20 | lnmp80  |
|  7 | zhaoliu  | w   |  19 | lnmp81  |
|  8 | tianqi   | m   |  27 | lnmp82  |
|  9 | uu       | w   |  26 | lnmp80  |
| 10 | yy       | m   |  24 | lnmp82  |
| 11 | pp       | w   |  29 | lnmp81  |
+----+----------+-----+-----+---------+
11 rows in set (0.00 sec)

-- 查看name/sex/age这几个字段的所有信息
mysql> select name,sex,age from stu;
+----------+-----+-----+
| name     | sex | age |
+----------+-----+-----+
| zhangsan | m   |  20 |
| lisi     | m   |  22 |
| wangwu   | w   |  25 |
| qq       | w   |  21 |
| aa       | m   |  28 |
| bb       | w   |  20 |
| zhaoliu  | w   |  19 |
| tianqi   | m   |  27 |
| uu       | w   |  26 |
| yy       | m   |  24 |
| pp       | w   |  29 |
+----------+-----+-----+
11 rows in set (0.00 sec)

-- 查看数据，将name字段名换成username  （其中as关键字可以省略不写） 
mysql> select name as username,sex,age from stu;

-- 查看所有学生信息，并追加一列（年龄都加5的值），起个别名age2
mysql> select *,age+5  age2 from stu;
+----+----------+-----+-----+---------+------+
| id | name     | sex | age | classid | age2 |
+----+----------+-----+-----+---------+------+
|  1 | zhangsan | m   |  20 | lnmp80  |   25 |
|  2 | lisi     | m   |  22 | lnmp80  |   27 |
.....
| 11 | pp       | w   |  29 | lnmp81  |   34 |
+----+----------+-----+-----+---------+------+
11 rows in set (0.00 sec)

-- 在查看学生信息时，追加一列。值为beijing，字段名为city
mysql> select *,"beijing" city from stu;
+----+----------+-----+-----+---------+---------+
| id | name     | sex | age | classid | city    |
+----+----------+-----+-----+---------+---------+
|  1 | zhangsan | m   |  20 | lnmp80  | beijing |
|  2 | lisi     | m   |  22 | lnmp80  | beijing |
|  3 | wangwu   | w   |  25 | lnmp80  | beijing |
....
| 10 | yy       | m   |  24 | lnmp82  | beijing |
| 11 | pp       | w   |  29 | lnmp81  | beijing |
+----+----------+-----+-----+---------+---------+
11 rows in set (0.00 sec)

-- 将字串aa和bb合并到一列中输出
mysql> select concat("aa","bb");
+-------------------+
| concat("aa","bb") |
+-------------------+
| aabb              |
+-------------------+
1 row in set (0.02 sec)

-- 将stu表中班级和姓名字段合并到一列输出，起字段名为uname
mysql> select concat(classid,":",name) as uname from stu;
+-----------------+
| uname           |
+-----------------+
| lnmp80:zhangsan |
| lnmp80:lisi     |
。。。
| lnmp82:tianqi   |
| lnmp80:uu       |
| lnmp82:yy       |
| lnmp81:pp       |
+-----------------+
11 rows in set (0.00 sec)

mysql>

-- where条件查询语句
    --1. 年龄在20至25岁的学生信息（包含20和25）
    mysql> select * from stu where age>=20 and age<=25;
    mysql> select * from stu where age between 20 and 25;
    
    --2. id号为3,5,8,9的学生信息。
    mysql> select * from stu where id=3 or id=5 or id=8 or id=9
    mysql> select * from stu where id in(3,5,8,9);
    
    --3. 获取lamp80期的女生信息
    mysql> select * from  stu where classid='lnmp80' and sex="w"
    
    --4. 获取性别为m的lamp81和lamp82的学生信息
    mysql> select * from  stu where classid in('lnmp81','lamp82') and sex="w"
    
    --5. 查询数据时去除重复的数据  
    mysql> select distinct classid from stu;
    +---------+
    | classid |
    +---------+
    | lnmp80  |
    | lnmp81  |
    | lnmp82  |
    +---------+
    3 rows in set (0.00 sec)
    
    --6. 查询学生信息id不是1,3,5,8,10的信息
    mysql> select * from stu where id not in(1,3,5,8,10);
    
    --7. like模糊查询只支持两个通配符: '%'表示任意长度的任意值, '_'表示1位的任意值。
    -- 获取name是由z字符开头的所有信息
    mysql> select * from stu where name like 'z%';
    +----+----------+-----+-----+---------+
    | id | name     | sex | age | classid |
    +----+----------+-----+-----+---------+
    |  1 | zhangsan | m   |  20 | lnmp80  |
    |  7 | zhaoliu  | w   |  19 | lnmp81  |
    +----+----------+-----+-----+---------+
    2 rows in set (0.00 sec)

    -- 查看name值中包含a字符的所有信息
    mysql> select * from stu where name like '%a%';
    +----+----------+-----+-----+---------+
    | id | name     | sex | age | classid |
    +----+----------+-----+-----+---------+
    |  1 | zhangsan | m   |  20 | lnmp80  |
    |  3 | wangwu   | w   |  25 | lnmp80  |
    |  5 | aa       | m   |  28 | lnmp82  |
    |  7 | zhaoliu  | w   |  19 | lnmp81  |
    |  8 | tianqi   | m   |  27 | lnmp82  |
    +----+----------+-----+-----+---------+
    5 rows in set (0.00 sec)
    
    -- 查看name是由两个字符构成的信息。
    mysql> select * from stu where name like '__';
    +----+------+-----+-----+---------+
    | id | name | sex | age | classid |
    +----+------+-----+-----+---------+
    |  4 | qq   | w   |  21 | lnmp81  |
    |  5 | aa   | m   |  28 | lnmp82  |
    |  6 | bb   | w   |  20 | lnmp80  |
    |  9 | uu   | w   |  26 | lnmp80  |
    | 10 | yy   | m   |  24 | lnmp82  |
    | 11 | pp   | w   |  29 | lnmp81  |
    +----+------+-----+-----+---------+
    6 rows in set (0.00 sec)
    
    
  -- 统计查询（mysql的聚合函数：count() /sum() /max() /min() /avg()
    -- 统计stu表的数据条数11，年龄最大29，最小19，平均年龄23.7273，年龄总和261    
    mysql> select count(*),max(age),min(age),avg(age),sum(age) from stu;
    +----------+----------+----------+----------+----------+
    | count(*) | max(age) | min(age) | avg(age) | sum(age) |
    +----------+----------+----------+----------+----------+
    |       11 |       29 |       19 |  23.7273 |      261 |
    +----------+----------+----------+----------+----------+
    1 row in set (0.00 sec)

   -- 分组查询： group by 字段名 
    -- 按班级号分组查询
    mysql> select classid from stu group by classid;
    +---------+
    | classid |
    +---------+
    | lnmp80  |
    | lnmp81  |
    | lnmp82  |
    +---------+
    3 rows in set (0.00 sec)
    -- 按班级分组并统计，统计每个班的人数，最大年龄，最小年龄 
    mysql> select classid,count(*),max(age),min(age) from stu group by classid;
    +---------+----------+----------+----------+
    | classid | count(*) | max(age) | min(age) |
    +---------+----------+----------+----------+
    | lnmp80  |        5 |       26 |       20 |
    | lnmp81  |        3 |       29 |       19 |
    | lnmp82  |        3 |       28 |       24 |
    +---------+----------+----------+----------+
    3 rows in set (0.00 sec)
    
    -- 统计每个班男生的平均年龄
    mysql> select classid,avg(age) from stu where sex='m' group by classid;
    +---------+----------+
    | classid | avg(age) |
    +---------+----------+
    | lnmp80  |  21.0000 |
    | lnmp82  |  26.3333 |
    +---------+----------+
    2 rows in set (0.00 sec)
 
    -- 按班级分组，统计每个班的平均年龄，并获取平均年龄在23及以上信息。
    mysql> select classid,avg(age) from stu group by classid having avg(age)>=23;
    +---------+----------+
    | classid | avg(age) |
    +---------+----------+
    | lnmp81  |  23.0000 |
    | lnmp82  |  26.3333 |
    +---------+----------+
    2 rows in set (0.02 sec)
    
   --排序：order by 字段名 [asc升（默认）|desc降]
    mysql> select  * from stu; --没有排序
    +----+----------+-----+-----+---------+
    | id | name     | sex | age | classid |
    +----+----------+-----+-----+---------+
    |  1 | zhangsan | m   |  20 | lnmp80  |
    |  2 | lisi     | m   |  22 | lnmp80  |
    |  3 | wangwu   | w   |  25 | lnmp80  |
    。。。
    |  9 | uu       | w   |  26 | lnmp80  |
    | 10 | yy       | m   |  24 | lnmp82  |
    | 11 | pp       | w   |  29 | lnmp81  |
    +----+----------+-----+-----+---------+
    11 rows in set (0.00 sec)

    --按照年龄升序排序
    mysql> select  * from stu order by age;
    mysql> select  * from stu order by age asc; --等价于上面语句
    +----+----------+-----+-----+---------+
    | id | name     | sex | age | classid |
    +----+----------+-----+-----+---------+
    |  7 | zhaoliu  | w   |  19 | lnmp81  |
    |  1 | zhangsan | m   |  20 | lnmp80  |
    |  6 | bb       | w   |  20 | lnmp80  |
    |  4 | qq       | w   |  21 | lnmp81  |
    |  2 | lisi     | m   |  22 | lnmp80  |
    | 10 | yy       | m   |  24 | lnmp82  |
    |  3 | wangwu   | w   |  25 | lnmp80  |
    |  9 | uu       | w   |  26 | lnmp80  |
    |  8 | tianqi   | m   |  27 | lnmp82  |
    |  5 | aa       | m   |  28 | lnmp82  |
    | 11 | pp       | w   |  29 | lnmp81  |
    +----+----------+-----+-----+---------+
    11 rows in set (0.00 sec)
    
    --多列排序：首先按班级升序，相同班级再按年龄降序排序
    mysql> select  * from stu order by classid,age desc;
    +----+----------+-----+-----+---------+
    | id | name     | sex | age | classid |
    +----+----------+-----+-----+---------+
    |  9 | uu       | w   |  26 | lnmp80  |
    |  3 | wangwu   | w   |  25 | lnmp80  |
    |  2 | lisi     | m   |  22 | lnmp80  |
    |  1 | zhangsan | m   |  20 | lnmp80  |
    |  6 | bb       | w   |  20 | lnmp80  |
    | 11 | pp       | w   |  29 | lnmp81  |
    |  4 | qq       | w   |  21 | lnmp81  |
    |  7 | zhaoliu  | w   |  19 | lnmp81  |
    |  5 | aa       | m   |  28 | lnmp82  |
    |  8 | tianqi   | m   |  27 | lnmp82  |
    | 10 | yy       | m   |  24 | lnmp82  |
    +----+----------+-----+-----+---------+
    11 rows in set (0.00 sec)
    
    --limit 分页： 
    --分页公式： limit （当前页-1）*页大小，页大小;
    mysql> select * from stu limit 5; --获取前5条信息
    +----+----------+-----+-----+---------+
    | id | name     | sex | age | classid |
    +----+----------+-----+-----+---------+
    |  1 | zhangsan | m   |  20 | lnmp80  |
    |  2 | lisi     | m   |  22 | lnmp80  |
    |  3 | wangwu   | w   |  25 | lnmp80  |
    |  4 | qq       | w   |  21 | lnmp81  |
    |  5 | aa       | m   |  28 | lnmp82  |
    +----+----------+-----+-----+---------+
    5 rows in set (0.00 sec)

    --获取年龄最大的3条数据
    mysql> select * from stu order by age desc limit 3;
    +----+--------+-----+-----+---------+
    | id | name   | sex | age | classid |
    +----+--------+-----+-----+---------+
    | 11 | pp     | w   |  29 | lnmp81  |
    |  5 | aa     | m   |  28 | lnmp82  |
    |  8 | tianqi | m   |  27 | lnmp82  |
    +----+--------+-----+-----+---------+
    3 rows in set (0.00 sec)

    --以4条数据为一页，取第一页
    mysql> select * from stu limit 0,4;
    +----+----------+-----+-----+---------+
    | id | name     | sex | age | classid |
    +----+----------+-----+-----+---------+
    |  1 | zhangsan | m   |  20 | lnmp80  |
    |  2 | lisi     | m   |  22 | lnmp80  |
    |  3 | wangwu   | w   |  25 | lnmp80  |
    |  4 | qq       | w   |  21 | lnmp81  |
    +----+----------+-----+-----+---------+
    4 rows in set (0.00 sec)
    
    --以4条数据为1页，取第二页
    mysql> select * from stu limit 4,4;
    +----+---------+-----+-----+---------+
    | id | name    | sex | age | classid |
    +----+---------+-----+-----+---------+
    |  5 | aa      | m   |  28 | lnmp82  |
    |  6 | bb      | w   |  20 | lnmp80  |
    |  7 | zhaoliu | w   |  19 | lnmp81  |
    |  8 | tianqi  | m   |  27 | lnmp82  |
    +----+---------+-----+-----+---------+
    4 rows in set (0.00 sec)

    mysql> select * from stu limit 8,4;
    +----+------+-----+-----+---------+
    | id | name | sex | age | classid |
    +----+------+-----+-----+---------+
    |  9 | uu   | w   |  26 | lnmp80  |
    | 10 | yy   | m   |  24 | lnmp82  |
    | 11 | pp   | w   |  29 | lnmp81  |
    +----+------+-----+-----+---------+
    3 rows in set (0.00 sec)

 
 -- 数据的导入和导出
 --================================================
 -- 导出lnmp80数据的所有信息
D:\xampp\htdocs\lamp80>mysqldump -u root -p lnmp80 >lnmp80_20170106.sql
Enter password:
 -- 只导出lamp80库下的stu表信息
D:\xampp\htdocs\lnmp80>mysqldump -u root -p lnmp80 stu>stu.sql
Enter password:

D:\xampp\htdocs\lnmp80\python_mysql03>

 --数据库lnmp80的导入（要求数据库必须存在）
D:\xampp\htdocs\lnmp80>mysql -u root -p lnmp80<lnmp80_20170106.sql

Enter password:


 --数据库lnmp80下stu表信息的导入
D:\xampp\htdocs\lnmp80>mysql -u root -p lnmp80 < stu.sql

多表查询：

-- 多表查询：
--      1. 嵌套方式的多表查询
--      2. where关联查询
--      3. 左联和右联，内联的查询。

=================================================
-- 已知条件：
mysql> select * from stu;
+----+------------+-----+------+---------+
| id | name       | sex | age  | classid |
+----+------------+-----+------+---------+
|  1 | zhangsan   | m   |   20 | lnmp01  |
|  2 | lisi       | w   |   21 | lnmp02  |
|  3 | wangwu     | m   |   25 | lnmp01  |
|  4 | zhaoliu    | w   |   29 | lnmp02  |
|  5 | qq01       | w   |   28 | lnmp01  |
|  6 | qq02       | m   |   30 | lnmp02  |
|  7 | qq03       | w   |   31 | lnmp03  |
|  8 | xiaowang   | m   |   18 | lnmp04  |
|  9 | xiaoli     | w   |   19 | lnmp03  |
| 10 | xiaobai    | m   |   22 | lnmp02  |
| 11 | xiaosun    | w   |   24 | lnmp01  |
| 12 | xiaozhang2 | w   |   36 | lnmp05  |
+----+------------+-----+------+---------+
12 rows in set (0.05 sec)

mysql> select * from grade;
+----+-----+------+-------+
| id | sid | php  | mysql |
+----+-----+------+-------+
|  1 |   4 |   80 |    92 |
|  2 |   2 |   78 |    80 |
|  3 |   1 |   99 |    95 |
|  4 |   6 |   58 |    62 |
|  5 |   7 |   89 |    98 |
|  6 |   3 |   68 |    54 |
+----+-----+------+-------+
6 rows in set (0.00 sec)

mysql>

-- 一、嵌套查询：一个查询结果是另外一个查询的条件。

-- 例如在学生表中获取年龄最大的信息
mysql> select max(age) from stu;
+----------+
| max(age) |
+----------+
|       36 |
+----------+
1 row in set (0.00 sec)

mysql> select * from stu where age=(select max(age) from stu);
+----+------------+-----+------+---------+
| id | name       | sex | age  | classid |
+----+------------+-----+------+---------+
| 12 | xiaozhang2 | w   |   36 | lnmp05  |
+----+------------+-----+------+---------+
1 row in set (0.00 sec)

mysql>

-- 获取php考试成绩最好的是谁？

mysql> select max(php) from grade; --获取php最高成绩
+----------+
| max(php) |
+----------+
|       99 |
+----------+
1 row in set (0.11 sec)

mysql> select sid from grade where php=99; --获取php成绩在99分的sid学号信息
+-----+
| sid |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select * from stu where id=1;  --获取学号为1的学生信息
+----+----------+-----+------+---------+
| id | name     | sex | age  | classid |
+----+----------+-----+------+---------+
|  1 | zhangsan | m   |   20 | lnmp01  |
+----+----------+-----+------+---------+
1 row in set (0.00 sec)

mysql> select * from stu where id=(select sid from grade where php=99);
mysql> select * from stu where id in(select sid from grade where php=99);
+----+----------+-----+------+---------+
| id | name     | sex | age  | classid |
+----+----------+-----+------+---------+
|  1 | zhangsan | m   |   20 | lnmp01  |
+----+----------+-----+------+---------+
1 row in set (0.19 sec)

-- 三层嵌套查询，解决考试php成绩最好的信息
mysql> select * from stu where id in(select sid from grade where php=(select max(p
hp) from grade));
+----+----------+-----+------+---------+
| id | name     | sex | age  | classid |
+----+----------+-----+------+---------+
|  1 | zhangsan | m   |   20 | lnmp01  |
+----+----------+-----+------+---------+
1 row in set (0.00 sec)


-- 二、  where关联查询

-- 查询stu表与grade表的关联查询
mysql> select * from stu,grade where stu.id=grade.sid;
+----+----------+-----+------+---------+----+-----+------+-------+
| id | name     | sex | age  | classid | id | sid | php  | mysql |
+----+----------+-----+------+---------+----+-----+------+-------+
|  4 | zhaoliu  | w   |   29 | lnmp02  |  1 |   4 |   80 |    92 |
|  2 | lisi     | w   |   21 | lnmp02  |  2 |   2 |   78 |    80 |
|  1 | zhangsan | m   |   20 | lnmp01  |  3 |   1 |   99 |    95 |
|  6 | qq02     | m   |   30 | lnmp02  |  4 |   6 |   58 |    62 |
|  7 | qq03     | w   |   31 | lnmp03  |  5 |   7 |   89 |    98 |
|  3 | wangwu   | m   |   25 | lnmp01  |  6 |   3 |   68 |    54 |
+----+----------+-----+------+---------+----+-----+------+-------+
6 rows in set (0.13 sec)
-- 查询stu表与grade表的关联查询，只显示部分字段
mysql> select s.id,s.name,g.php,g.mysql  from stu s,grade g
    -> where s.id=g.sid;
+----+----------+------+-------+
| id | name     | php  | mysql |
+----+----------+------+-------+
|  4 | zhaoliu  |   80 |    92 |
|  2 | lisi     |   78 |    80 |
|  1 | zhangsan |   99 |    95 |
|  6 | qq02     |   58 |    62 |
|  7 | qq03     |   89 |    98 |
|  3 | wangwu   |   68 |    54 |
+----+----------+------+-------+
6 rows in set (0.03 sec)

-- 查询lnmp01班的学生考试信息，显示：学号、姓名、班级、php和MySQL字段。
mysql> select s.id,s.name,s.classid,g.php,g.mysql from stu s,grade g
    -> where s.id=g.sid and s.classid='lamp01';
+----+----------+---------+------+-------+
| id | name     | classid | php  | mysql |
+----+----------+---------+------+-------+
|  1 | zhangsan | lnmp01  |   99 |    95 |
|  3 | wangwu   | lnmp01  |   68 |    54 |
+----+----------+---------+------+-------+
2 rows in set (0.00 sec)

-- 统计每个班的考试人数和平均成绩：显示：班级，人数和平均成绩
mysql> select s.classid,count(*) num,avg(g.php) php ,avg(g.mysql) mysql
    -> from stu s,grade g
    -> where s.id=g.sid group by s.classid;
+---------+-----+---------+---------+
| classid | num | php     | mysql   |
+---------+-----+---------+---------+
| lnmp01  |   2 | 83.5000 | 74.5000 |
| lnmp02  |   3 | 72.0000 | 78.0000 |
| lnmp03  |   1 | 89.0000 | 98.0000 |
+---------+-----+---------+---------+
3 rows in set (0.00 sec)

-- 查询姓名中含有ang的考试信息
mysql> select s.id,s.name,g.php,g.mysql from stu s,grade g
    -> where s.id=g.sid and s.name like '%ang%';
+----+----------+------+-------+
| id | name     | php  | mysql |
+----+----------+------+-------+
|  1 | zhangsan |   99 |    95 |
|  3 | wangwu   |   68 |    54 |
+----+----------+------+-------+
2 rows in set (0.00 sec)


--三、 左联和右联、内联（等价于where） 查询
------------------------------------------------------

--查询所有学生的考试信息（包含没有考试的）
-- 采用左联查询（以左边为主，右边没有的对应数据补null）
mysql> select s.id,s.name,g.php,g.mysql from stu s left join  grade g
    -> on s.id=g.sid;
+----+------------+------+-------+
| id | name       | php  | mysql |
+----+------------+------+-------+
|  2 | lisi       |   78 |    80 |
|  5 | qq01       | NULL |  NULL |
|  6 | qq02       |   58 |    62 |
|  7 | qq03       |   89 |    98 |
|  3 | wangwu     |   68 |    54 |
| 10 | xiaobai    | NULL |  NULL |
|  9 | xiaoli     | NULL |  NULL |
| 11 | xiaosun    | NULL |  NULL |
|  8 | xiaowang   | NULL |  NULL |
| 12 | xiaozhang2 | NULL |  NULL |
|  1 | zhangsan   |   99 |    95 |
|  4 | zhaoliu    |   80 |    92 |
+----+------------+------+-------+
12 rows in set (0.00 sec)

-- 采用右联查询（以右边为主，左边没有的对应数据补null）
mysql> select s.id,s.name,g.php,g.mysql from grade g right join stu s
    -> on s.id=g.sid;
+----+------------+------+-------+
| id | name       | php  | mysql |
+----+------------+------+-------+
|  2 | lisi       |   78 |    80 |
|  5 | qq01       | NULL |  NULL |
|  6 | qq02       |   58 |    62 |
|  7 | qq03       |   89 |    98 |
|  3 | wangwu     |   68 |    54 |
| 10 | xiaobai    | NULL |  NULL |
|  9 | xiaoli     | NULL |  NULL |
| 11 | xiaosun    | NULL |  NULL |
|  8 | xiaowang   | NULL |  NULL |
| 12 | xiaozhang2 | NULL |  NULL |
|  1 | zhangsan   |   99 |    95 |
|  4 | zhaoliu    |   80 |    92 |
+----+------------+------+-------+
12 rows in set (0.00 sec)
-- 内联查询，就是两边表都存在的。
mysql> select s.id,s.name,g.php,g.mysql from grade g inner join stu s
    -> on s.id=g.sid;
+----+----------+------+-------+
| id | name     | php  | mysql |
+----+----------+------+-------+
|  4 | zhaoliu  |   80 |    92 |
|  2 | lisi     |   78 |    80 |
|  1 | zhangsan |   99 |    95 |
|  6 | qq02     |   58 |    62 |
|  7 | qq03     |   89 |    98 |
|  3 | wangwu   |   68 |    54 |
+----+----------+------+-------+
6 rows in set (0.00 sec)

mysql>
-- 查询lnmp01班的学生考试信息
mysql> select s.id,s.name,s.classid,g.php,g.mysql 
    -> from stu s left join grade g
    -> on s.id=g.sid 
    -> where s.classid='lnmp01';
+----+----------+---------+------+-------+
| id | name     | classid | php  | mysql |
+----+----------+---------+------+-------+
|  1 | zhangsan | lnmp01  |   99 |    95 |
|  3 | wangwu   | lnmp01  |   68 |    54 |
|  5 | qq01     | lnmp01  | NULL |  NULL |
| 11 | xiaosun  | lnmp01  | NULL |  NULL |
+----+----------+---------+------+-------+
4 rows in set (0.00 sec)

mysql>

向AI问一下细节

数据库应用-mysql语句拾遗

MySQL

（关系型数据库管理系统）

什么是数据库？

RDBMS 术语

Mysql数据库

MariaDB

发展

起源

区别

猜你喜欢

最新资讯

相关推荐

相关标签