首页 > 数据库 >MySQL必会必知笔记

MySQL必会必知笔记

时间：2023-04-08 11:24:45浏览次数：47  
标签：ClassID 必知 Age mysql students 必会 MySQL where select

1.简单使用

[root@mysql ~]# mysql -uroot -p123 < hellodb_innodb.sql 所有命令都是在hellodb库中执行，此库可以在MySQL官网下载
进入库
[root@mysql ~]# mysql -u root -p123
查询所有的库
mysql> show databases;
进入库 
mysql> use hellodb;
查询库里的所有表
mysql> show tables;

2.检索数据

2.1检索classes表的所有列
mysql> select * from classes;

mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)
View Code 
2.2检索单个列
mysql> select ClassID from classes;
mysql> select ClassID from classes;
+---------+
| ClassID |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
|       7 |
|       8 |
+---------+
8 rows in set (0.00 sec)
View Code 


  2.3 检索多个列   ,检索不同行，使用distinct关键字


mysql> select distinct ClassID from students;




mysql> select distinct ClassID from students;
+---------+
| ClassID |
+---------+
|       2 |
|       1 |
|       4 |
|       3 |
|       5 |
|       7 |
|       6 |
|    NULL |
+---------+
8 rows in set (0.00 sec)


View Code


3.限制结果，使用limit子句



3.1检索students表ClassID列的前五行；
mysql> select ClassID from students limit 5;




mysql>  select ClassID from students limit 5;
+---------+
| ClassID |
+---------+
|       2 |
|       1 |
|       2 |
|       4 |
|       3 |
+---------+
5 rows in set (0.00 sec)


View Code

3.2检索students表ClassID列，从第五行开始的10行；
mysql> select ClassID from students limit 5,10;




 1 mysql> select ClassID from students limit 5,10;
 2 +---------+
 3 | ClassID |
 4 +---------+
 5 |       5 |
 6 |       3 |
 7 |       7 |
 8 |       6 |
 9 |       3 |
10 |       6 |
11 |       1 |
12 |       2 |
13 |       3 |
14 |       4 |
15 +---------+
16 10 rows in set (0.00 sec)


View Code



 3.3使用完全限定的表名


 mysql> select students.ClassID from students;




mysql> select students.ClassID from students;
+---------+
| ClassID |
+---------+
|       2 |
|       1 |
|       2 |
|       4 |
|       3 |
|       5 |
|       3 |
|       7 |
|       6 |
|       3 |
|       6 |
|       1 |
|       2 |
|       3 |
|       4 |
|       1 |
|       4 |
|       7 |
|       6 |
|       7 |
|       6 |
|       1 |
|       4 |
|    NULL |
|    NULL |
+---------+
25 rows in set (0.00 sec)


View Code

 3.3.1表名完全限定


 mysql> select students.ClassID from hellodb.students;




mysql>  select students.ClassID from hellodb.students;
+---------+
| ClassID |
+---------+
|       2 |
|       1 |
|       2 |
|       4 |
|       3 |
|       5 |
|       3 |
|       7 |
|       6 |
|       3 |
|       6 |
|       1 |
|       2 |
|       3 |
|       4 |
|       1 |
|       4 |
|       7 |
|       6 |
|       7 |
|       6 |
|       1 |
|       4 |
|    NULL |
|    NULL |
+---------+
25 rows in set (0.00 sec)


View Code


4.排序检索数据



order by子句,取一个列或多个列，据此对输出进行排序
4.1单列排序
mysql> select ClassID from students order by ClassID;




mysql> select ClassID from students order by ClassID;
+---------+
| ClassID |
+---------+
|    NULL |
|    NULL |
|       1 |
|       1 |
|       1 |
|       1 |
|       2 |
|       2 |
|       2 |
|       3 |
|       3 |
|       3 |
|       3 |
|       4 |
|       4 |
|       4 |
|       4 |
|       5 |
|       6 |
|       6 |
|       6 |
|       6 |
|       7 |
|       7 |
|       7 |
+---------+
25 rows in set (0.00 sec)


View Code

4.2多列排序
mysql>  select Name,ClassID from students order by Name,ClassID;




mysql>  select Name,ClassID from students order by Name,ClassID;
+---------------+---------+
| Name          | ClassID |
+---------------+---------+
| Diao Chan     |       7 |
| Ding Dian     |       4 |
| Duan Yu       |       4 |
| Hua Rong      |       7 |
| Huang Yueying |       6 |
| Lin Chong     |       4 |
| Lin Daiyu     |       7 |
| Lu Wushuang   |       3 |
| Ma Chao       |       4 |
| Ren Yingying  |       6 |
| Shi Potian    |       1 |
| Shi Qing      |       5 |
| Shi Zhongyu   |       2 |
| Sun Dasheng   |    NULL |
| Tian Boguang  |       2 |
| Wen Qingqing  |       1 |
| Xi Ren        |       3 |
| Xiao Qiao     |       1 |
| Xie Yanke     |       2 |
| Xu Xian       |    NULL |
| Xu Zhu        |       1 |
| Xue Baochai   |       6 |
| Yu Yutong     |       3 |
| Yuan Chengzhi |       6 |
| Yue Lingshan  |       3 |
+---------------+---------+
25 rows in set (0.00 sec)


View Code

4.3指定排序方向  
降序 desc
mysql> select ClassID from students order by ClassID desc;




mysql> select ClassID from students order by ClassID desc;
+---------+
| ClassID |
+---------+
|       7 |
|       7 |
|       7 |
|       6 |
|       6 |
|       6 |
|       6 |
|       5 |
|       4 |
|       4 |
|       4 |
|       4 |
|       3 |
|       3 |
|       3 |
|       3 |
|       2 |
|       2 |
|       2 |
|       1 |
|       1 |
|       1 |
|       1 |
|    NULL |
|    NULL |
+---------+
25 rows in set (0.00 sec)


View Code

升序asc
mysql> select ClassID from students order by ClassID asc;




mysql> select ClassID from students order by ClassID asc;
+---------+
| ClassID |
+---------+
|    NULL |
|    NULL |
|       1 |
|       1 |
|       1 |
|       1 |
|       2 |
|       2 |
|       2 |
|       3 |
|       3 |
|       3 |
|       3 |
|       4 |
|       4 |
|       4 |
|       4 |
|       5 |
|       6 |
|       6 |
|       6 |
|       6 |
|       7 |
|       7 |
|       7 |
+---------+
25 rows in set (0.00 sec)


View Code

4.4列出classid最大的值


mysql> select Age,ClassID from students order by ClassID desc limit 1;




mysql> select Age,ClassID from students order by ClassID desc limit 1;
+-----+---------+
| Age | ClassID |
+-----+---------+
|  17 |       7 |
+-----+---------+
1 row in set (0.00 sec)


View Code


5.过滤数据 where



where
5.1列出classid的值为7的行
mysql>  select Name,ClassID from students where ClassID = 7;




mysql> select Name,ClassID from students where ClassID = 7;
+-----------+---------+
| Name      | ClassID |
+-----------+---------+
| Lin Daiyu |       7 |
| Hua Rong  |       7 |
| Diao Chan |       7 |
+-----------+---------+
3 rows in set (0.00 sec)


View Code

where子句操作符
=    等于
<>  不等于
<  小于
>= 大于等于
between  在指定两个值之间

5.2检查单个值
mysql>  select Name,ClassID from students where Name = 'lin daiyu'; 不区分大小写




mysql>  select Name,ClassID from students where Name = 'lin daiyu';
+-----------+---------+
| Name      | ClassID |
+-----------+---------+
| Lin Daiyu |       7 |
+-----------+---------+
1 row in set (0.00 sec)


View Code

mysql>  select Name,ClassID from students where ClassID >= 3;




mysql> select Name,ClassID from students where ClassID >= 3;
+---------------+---------+
| Name          | ClassID |
+---------------+---------+
| Ding Dian     |       4 |
| Yu Yutong     |       3 |
| Shi Qing      |       5 |
| Xi Ren        |       3 |
| Lin Daiyu     |       7 |
| Ren Yingying  |       6 |
| Yue Lingshan  |       3 |
| Yuan Chengzhi |       6 |
| Lu Wushuang   |       3 |
| Duan Yu       |       4 |
| Lin Chong     |       4 |
| Hua Rong      |       7 |
| Xue Baochai   |       6 |
| Diao Chan     |       7 |
| Huang Yueying |       6 |
| Ma Chao       |       4 |
+---------------+---------+
16 rows in set (0.00 sec)


View Code

5.3不匹配检查
mysql>  select Name,ClassID from students where ClassID <> 7;




mysql> select Name,ClassID from students where ClassID <> 7;
+---------------+---------+
| Name          | ClassID |
+---------------+---------+
| Shi Zhongyu   |       2 |
| Shi Potian    |       1 |
| Xie Yanke     |       2 |
| Ding Dian     |       4 |
| Yu Yutong     |       3 |
| Shi Qing      |       5 |
| Xi Ren        |       3 |
| Ren Yingying  |       6 |
| Yue Lingshan  |       3 |
| Yuan Chengzhi |       6 |
| Wen Qingqing  |       1 |
| Tian Boguang  |       2 |
| Lu Wushuang   |       3 |
| Duan Yu       |       4 |
| Xu Zhu        |       1 |
| Lin Chong     |       4 |
| Xue Baochai   |       6 |
| Huang Yueying |       6 |
| Xiao Qiao     |       1 |
| Ma Chao       |       4 |
+---------------+---------+
20 rows in set (0.00 sec)


View Code

5.4范围检查
mysql>  select Name,ClassID from students where ClassID between 3 and 7;




mysql> select Name,ClassID from students where ClassID between 3 and 7;
+---------------+---------+
| Name          | ClassID |
+---------------+---------+
| Ding Dian     |       4 |
| Yu Yutong     |       3 |
| Shi Qing      |       5 |
| Xi Ren        |       3 |
| Lin Daiyu     |       7 |
| Ren Yingying  |       6 |
| Yue Lingshan  |       3 |
| Yuan Chengzhi |       6 |
| Lu Wushuang   |       3 |
| Duan Yu       |       4 |
| Lin Chong     |       4 |
| Hua Rong      |       7 |
| Xue Baochai   |       6 |
| Diao Chan     |       7 |
| Huang Yueying |       6 |
| Ma Chao       |       4 |
+---------------+---------+
16 rows in set (0.00 sec)


View Code

5.5空值检查
mysql>  select ClassID from students where ClassID  is null;




mysql>  select ClassID from students where ClassID  is null;
+---------+
| ClassID |
+---------+
|    NULL |
|    NULL |
+---------+
2 rows in set (0.01 sec)


View Code


6.数据过滤  组合where子句



6.1and操作符  显示满足所有条件的行
mysql>  select ClassID,Age from students where ClassID = 7 and  Age >= 19;




mysql>   select ClassID,Age from students where ClassID = 7 and  Age >= 19;
+---------+-----+
| ClassID | Age |
+---------+-----+
|       7 |  23 |
|       7 |  19 |
+---------+-----+
2 rows in set (0.01 sec)


View Code

6.2or操作符   显示任一条件的行
mysql>  select ClassID,Age from students where ClassID = 7 or  Age >= 25;




mysql> select ClassID,Age from students where ClassID = 7 or  Age >= 25;
+---------+-----+
| ClassID | Age |
+---------+-----+
|       2 |  53 |
|       4 |  32 |
|       3 |  26 |
|       5 |  46 |
|       7 |  17 |
|       2 |  33 |
|       4 |  25 |
|       7 |  23 |
|       7 |  19 |
|    NULL |  27 |
|    NULL | 100 |
+---------+-----+
11 rows in set (0.00 sec)


View Code

6.3计算次序
mysql>  select ClassID,Age from students where ClassID = 7 or  Age >= 25 and Gender = 'F';




mysql> select ClassID,Age from students where ClassID = 7 or  Age >= 25 and Gender = 'F';
+---------+-----+
| ClassID | Age |
+---------+-----+
|       7 |  17 |
|       7 |  23 |
|       7 |  19 |
+---------+-----+
3 rows in set (0.00 sec)


View Code

mysql>  select ClassID,Age from students where (ClassID = 7 or  Age >= 25) and Gender = 'F';




mysql> select ClassID,Age from students where (ClassID = 7 or  Age >= 25) and Gender = 'F';
+---------+-----+
| ClassID | Age |
+---------+-----+
|       7 |  17 |
|       7 |  19 |
+---------+-----+
2 rows in set (0.00 sec)


View Code

6.4in操作符 指定条件范围
mysql>  select ClassID,Age from students where ClassID in (1,7) order by Age;




mysql>  select ClassID,Age from students where ClassID in (1,7) order by Age;
+---------+-----+
| ClassID | Age |
+---------+-----+
|       7 |  17 |
|       1 |  19 |
|       7 |  19 |
|       1 |  20 |
|       1 |  21 |
|       1 |  22 |
|       7 |  23 |
+---------+-----+
7 rows in set (0.00 sec)


View Code

mysql>  select ClassID,Age from students where ClassID in (1,7) and Gender = 'F';




mysql> select ClassID,Age from students where ClassID in (1,7) and Gender = 'F';
+---------+-----+
| ClassID | Age |
+---------+-----+
|       7 |  17 |
|       1 |  19 |
|       7 |  19 |
|       1 |  20 |
+---------+-----+
4 rows in set (0.00 sec)


View Code

mysql>  select ClassID,Age from students where ClassID = 1 OR ClassID = 7 order by Age;




mysql> select ClassID,Age from students where ClassID = 1 OR ClassID = 7 order by Age;
+---------+-----+
| ClassID | Age |
+---------+-----+
|       7 |  17 |
|       1 |  19 |
|       7 |  19 |
|       1 |  20 |
|       1 |  21 |
|       1 |  22 |
|       7 |  23 |
+---------+-----+
7 rows in set (0.00 sec)


View Code

6.5NOT操作符 否定后跟条件
mysql>  select ClassID,Age from students where  ClassID not in (1,7) order by Age;




mysql> select ClassID,Age from students where  ClassID not in (1,7) order by Age;
+---------+-----+
| ClassID | Age |
+---------+-----+
|       3 |  17 |
|       6 |  18 |
|       3 |  19 |
|       3 |  19 |
|       4 |  19 |
|       6 |  20 |
|       2 |  22 |
|       6 |  22 |
|       6 |  23 |
|       4 |  23 |
|       4 |  25 |
|       3 |  26 |
|       4 |  32 |
|       2 |  33 |
|       5 |  46 |
|       2 |  53 |
+---------+-----+
16 rows in set (0.01 sec)


View Code


7.用通配符进行过滤  like操作符



like操作符 区分大小写
7.1%通配符 %表示任意 匹配多个字符
以S开头
mysql>  select ClassID,Age,Name from students where Name like 'S%';




mysql> select ClassID,Age,Name from students where Name like 'S%';
+---------+-----+-------------+
| ClassID | Age | Name        |
+---------+-----+-------------+
|       2 |  22 | Shi Zhongyu |
|       1 |  22 | Shi Potian  |
|       5 |  46 | Shi Qing    |
|    NULL | 100 | Sun Dasheng |
+---------+-----+-------------+
4 rows in set (0.00 sec)


View Code

包含ong
mysql>  select ClassID,Age,Name from students where Name like '%ong%';




mysql> select ClassID,Age,Name from students where Name like '%ong%';
+---------+-----+-------------+
| ClassID | Age | Name        |
+---------+-----+-------------+
|       2 |  22 | Shi Zhongyu |
|       3 |  26 | Yu Yutong   |
|       4 |  25 | Lin Chong   |
|       7 |  23 | Hua Rong    |
+---------+-----+-------------+
4 rows in set (0.00 sec)


View Code

mysql>  select ClassID,Age,Name from students where Name like 'S%g';




mysql> select ClassID,Age,Name from students where Name like 'S%g';
+---------+-----+-------------+
| ClassID | Age | Name        |
+---------+-----+-------------+
|       5 |  46 | Shi Qing    |
|    NULL | 100 | Sun Dasheng |
+---------+-----+-------------+
2 rows in set (0.00 sec)


View Code

7.2_下划线通配符 只匹配单个字符  需要1 anc zzz 查找时为like '_ anc zzz' 格式



8.用正则表达式搜索 



不区分大小写
8.1基本字符匹配
regexp 后的作为正则表达式
mysql> select Age  from  students where Age regexp '100' order by Age;




mysql>  select Age  from  students where Age regexp '100' order by Age;
+-----+
| Age |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)


View Code

mysql> select Age  from  students where Age regexp '.2' order by Age;




mysql> select Age  from  students where Age regexp '.2' order by Age;
+-----+
| Age |
+-----+
|  22 |
|  22 |
|  22 |
|  32 |
+-----+
4 rows in set (0.00 sec)


View Code

. 匹配任意一个字符   

8.2 进行or匹配 使用 | 
mysql> select Age  from  students where Age regexp '19|22' order by Age;




mysql> select Age  from  students where Age regexp '19|22' order by Age;
+-----+
| Age |
+-----+
|  19 |
|  19 |
|  19 |
|  19 |
|  19 |
|  22 |
|  22 |
|  22 |
+-----+
8 rows in set (0.00 sec)


View Code

8.3匹配几个字符之一
mysql> select Age  from  students where Age regexp '[123]' order by Age;




mysql> select Age  from  students where Age regexp '[123]' order by Age;
+-----+
| Age |
+-----+
|  17 |
|  17 |
|  18 |
|  19 |
|  19 |
|  19 |
|  19 |
|  19 |
|  20 |
|  20 |
|  21 |
|  22 |
|  22 |
|  22 |
|  23 |
|  23 |
|  23 |
|  25 |
|  26 |
|  27 |
|  32 |
|  33 |
|  53 |
| 100 |
+-----+
24 rows in set (0.00 sec)


View Code

[123]定义一组字符，匹配1或2或3


8.4匹配范围




 



标签：ClassID,必知,Age,mysql,students,必会,MySQL,where,select	

From： https://www.cnblogs.com/Xkf-IE/p/17298104.html
      

      




      

      

        
相关文章

        

          
    
 
  • MYSQL进阶之索引
    1、什么是索引官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数......
     
  • 力扣1070(MySQL)-产品销售分析Ⅲ（中等）
    题目：销售表 Sales： 产品表 Product：编写一个SQL查询，选出每个销售产品 第一年销售的产品id、年份、数量 和价格。结果表中的条目可以按任意顺序排列。查询结果格式如下例所示：示例1： 来源：力扣（LeetCode）链接：https://leetcode.cn/problems/product-sales-analysis......
     
  • Mysql_无法删除数据库的处理方法
    问题现象使用Navicat删除数据库一直转圈定位问题执行 SHOWPROCESSLIST;  提示元数据无法被锁定执行  select*frominformation_schema.innodb_trx\G; 发现有进程一直处于运行中解决方法kill进程id 重新查看没有 Waiting进程，showdatabases; 发现库已被......
     
  • 0-MySQL常见错误代码及代码说明
    先转载一下优秀作者的文章，后期会逐渐完善！ 作者：理想三旬出处：https://www.cnblogs.com/operationhome/p/9095522.html......
     
  • 力扣1069(MySQL)-产品分析Ⅱ（简单）
    题目：编写一个SQL查询，按产品idproduct_id来统计每个产品的销售总量。查询结果格式如下面例子所示: 解题思路：没有用到product表，直接在sales表中使用聚合函数：1selectproduct_id,sum(quantity)astotal_quantity2fromsales3groupbyproduct_id;......
     
  • yum 安装Mysql
    1.yum源下载安装https://dev.mysql.com/downloads/repo/yum/yumlocalinstall-ymysql80-community-release-el7-7.noarch.rpmyumrepolistenabled|grep"mysql.*-community.*"修改/etc/yum.repos.d/mysql-community.repo[mysql57-community]enabled=12.安装mysqlyumin......
     
  • 力扣1068(MySQL)-产品销售分析Ⅰ（简单）
    题目：销售表 Sales： 产品表 Product：写一条SQL 查询语句获取Sales 表中所有产品对应的产品名称product_name以及该产品的所有售卖年份year 和价格price。查询结果中的顺序无特定要求。查询结果格式示例如下： 来源：力扣（LeetCode）链接：https://leetcode.cn/proble......
     
  • Mysql索引原理
    什么是索引索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,为了优化数据库查询效率,引入的数据结构,类似于书的目录索引的分类普通索引--创建索引的基本语法CREATEINDEXindexNameONtable(column(length));主键索引联合索引--......
     
  • MYSQL进阶之存储引擎
    1、概念存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方法。其基于表的，而不是基于库的，所以存储引擎也称为表类型。在建表的时候，不指定存储引擎，默认的存储引擎是InnoDB。查看建表语句：showcreatetable表名; 可看到默认的存储引擎就是InnoDB 在建表的时候，......
     
  • django中使用orm连接mysql，setting.py的设置
    默认使用的时sqllite数据库，我们需要改成mysql，只要需要填写相关信息即可。比如mysql的数据库名，用户名，密码，主机地址，端口等信息#Database#https://docs.djangoproject.com/en/4.1/ref/settings/#databases#DATABASES={#'default':{#'ENGINE':'django.db.b......
     
    •            
          

        

      

    

    

      
赞助商

      




      

      
阅读排行

      

        
      

    

  





  
 
    
网站主页关于我们联系我们网站地图 

    
本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。 

    
Copyright © 2020-2023  IPS99  版权所有 IPS99