登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 业界 ›数据库的应用-第三天

数据库的应用-第三天

芮梦月 2 小时前
程序园永久vip申请，500美金$，无限下载程序园所有程序/软件/数据/等
数据库服务基础应用操作

数据库语句应用实践

1）DCL和数据库安全有关的操作
  1. grant/revoke                                                #        和授权有关操作
  2. create use/alter user/drop user                #        和用户相关操作
  4. commit                可以确保DML语句操作后，产生的数据信息合理存储到磁盘中
  5. rollback        可以实现DML语句操作后，将改动调整的数据做回退操作
  6. DML- insert update delete        可以保证存储语句的执行成功
复制代码
2）DQL可以实现查询数据库数据信息

查询情况1
  1. DQL        可以实现查询数据库数据信息
  2. show select
  3. 查询情况1：数据库中变量信息查询（变量--配置项信息/监控信息）######################################################
  4. show variables like 'XXX';                查看配置项变量信息
  5. show global status like 'xxx';        查看监控项变量信息
  7. select @@max_connections;   -- 查看具体某个变量信息
  9. 扩展说明：数据库配置项变量如何改动
  10. 两种方式可以改动配置项
  11. 方式一：永久改动
  12. max_connections                设置数据库最大并发连接数
  13.         在vim /etc/my.cnf中更改
  14.         [mysqld]
  15.         max_connections                永久修改配置需要重启数据库服务
  16. 方式二：临时改动
  17. set global max_connextions=3;                配置会影响全部用户功能/当会话断开重新连接配置不会失效（全局改动）
  18. set session sql_log_bin='off';                配置只会影响当前用户/当会话断开重新连接配置会失效（会话改动）
复制代码
查询情况2
  1. 查询情况2：可以查看具体数据或对象信息########################################################################
  2. show   查看表或库信息或用户或权限或索引...
  3. select 查看具体数据信息
  5. 单表数据信息查看
  6. -- 创建单表查看测试数据：
  7. https://dev.mysql.com/doc/index-other.html
  9. 加载测试数据-导入数据表到数据库中进行操作
  10. mysql -uroot -p66666 -S /tmp/mysql.sock <world.sql
  12. mysql> show databases;
  13. +--------------------+
  14. | Database           |
  15. +--------------------+
  16. | world              |
  18. mysql> desc city;
  19. +-------------+----------+------+-----+---------+----------------+
  20. | Field       | Type     | Null | Key | Default | Extra          |
  21. +-------------+----------+------+-----+---------+----------------+
  22. | ID          | int      | NO   | PRI | NULL    | auto_increment |
  23. | Name        | char(35) | NO   |     |         |                |
  24. | CountryCode | char(3)  | NO   | MUL |         |                |
  25. | District    | char(20) | NO   |     |         |                |
  26. | Population  | int      | NO   |     | 0       |                |
  27. +-------------+----------+------+-----+---------+----------------+
复制代码
附加：如何导入数据表到mysql中
  1. 1.把world.sql移动到数据库安装目录下
  2. [root@mysql local]#ll                        #        当前在/usr/local
  3. total 603964
  4. ............
  5. -rw-r--r-- 1 root root    398629 Mar  1 07:05 world.sql⭐
  6. ............
  8. 2.使用命令导入world.sql数据表
  9. [root@mysql local]#mysql -uroot -p666666 -S /tmp/mysql.sock <world.sql
  10. mysql: [Warning] Using a password on the command line interface can be insecure.
  11. #        用管理员身份导入world.sql
  13. 3.进入数据库查看
  14. [root@mysql local]#mysql -p666666
  15. mysql: [Warning] Using a password on the command line interface can be insecure.
  16. Welcome to the MySQL monitor.  Commands end with ; or \g.
  17. Your MySQL connection id is 30
  18. Server version: 8.0.36 MySQL Community Server - GPL
  20. Copyright (c) 2000, 2024, Oracle and/or its affiliates.
  22. Oracle is a registered trademark of Oracle Corporation and/or its
  23. affiliates. Other names may be trademarks of their respective
  24. owners.
  26. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  28. mysql> show databases;                #        展示所有数据库，发现world数据库
  29. +--------------------+
  30. | Database           |
  31. +--------------------+
  32. | information_schema |
  33. | mysql              |
  34. | oldboy             |
  35. | performance_schema |
  36. | sys                |
  37. | world              |
  38. +--------------------+
  39. 6 rows in set (0.00 sec)
  41. mysql> use world;                        #        使用world数据库
  42. Reading table information for completion of table and column names
  43. You can turn off this feature to get a quicker startup with -A
  45. Database changed
  46. mysql> show tables;                        #        查看所有数据表
  47. +-----------------+
  48. | Tables_in_world |
  49. +-----------------+
  50. | city            |
  51. | country         |
  52. | countrylanguage |
  53. +-----------------+
  54. 3 rows in set (0.00 sec)
  57. world.sql导入成功
  61. 可以进行操作来查看具体内容，比如数据表结构、数据表内容
  62. mysql> desc course;                        #        查看数据表结构
  63. +-------+-------------+------+-----+---------+-------+
  64. | Field | Type        | Null | Key | Default | Extra |
  65. +-------+-------------+------+-----+---------+-------+
  66. | cno   | int         | NO   | PRI | NULL    |       |
  67. | cname | varchar(20) | NO   |     | NULL    |       |
  68. | tno   | int         | NO   |     | NULL    |       |
  69. +-------+-------------+------+-----+---------+-------+
  70. 3 rows in set (0.00 sec)
  72. mysql> select * from course;                #        查看数据表内容
  73. +------+--------+-----+
  74. | cno  | cname  | tno |
  75. +------+--------+-----+
  76. | 1001 | linux  | 101 |
  77. | 1002 | python | 102 |
  78. | 1003 | mysql  | 103 |
  79. | 1004 | go     | 105 |
  80. +------+--------+-----+
  81. 4 rows in set (0.00 sec)
复制代码
单表数据查看练习（基础查看）

1.单表所有数据查看
  1. 1.单表所有数据查看
  2.         select * from 库名.表名;
  3.         select 列名01，列表02.. from 库名.表名;
  4. 备注：以上表中全部数据查看操作，尽量少用
复制代码
2.单表数据过滤查看 （where）⭐
  1. 2.单表数据过滤查看 （where）
  2. 1）等值条件查看数据信息####################################################################################
  3. 查看北京城市人口情况；
  4. mysql> show databases;                #        查看所有数据库
  5. +--------------------+
  6. | Database           |
  7. +--------------------+
  8. | information_schema |
  9. | mysql              |
  10. | oldboy             |
  11. | performance_schema |
  12. | school             |
  13. | sys                |
  14. | world              |
  15. +--------------------+
  16. 7 rows in set (0.02 sec)
  18. mysql> use world;                        #        使用我们需要的数据库
  19. Reading table information for completion of table and column names
  20. You can turn off this feature to get a quicker startup with -A
  22. Database changed
  23. mysql> show tables;                        #        展示所有数据表
  24. +-----------------+
  25. | Tables_in_world |
  26. +-----------------+
  27. | city            |
  28. | country         |
  29. | countrylanguage |
  30. +-----------------+
  31. 3 rows in set (0.00 sec)
  33. mysql> desc city;                        #        查看city数据表的结构
  34. +-------------+----------+------+-----+---------+----------------+
  35. | Field       | Type     | Null | Key | Default | Extra          |
  36. +-------------+----------+------+-----+---------+----------------+
  37. | ID          | int      | NO   | PRI | NULL    | auto_increment |
  38. | Name        | char(35) | NO   |     |         |                |
  39. | CountryCode | char(3)  | NO   | MUL |         |                |
  40. | District    | char(20) | NO   |     |         |                |
  41. | Population  | int      | NO   |     | 0       |                |
  42. +-------------+----------+------+-----+---------+----------------+
  43. 5 rows in set (0.00 sec)
  45. mysql> select * from city;                #        查看city数据表的内容
  46. +------+------------------------------------+-------------+------------------------+------------+
  47. | ID   | Name                               | CountryCode | District               | Population |
  48. +------+------------------------------------+-------------+------------------------+------------+
  49. |    1 | Kabul                              | AFG         | Kabol                  |    1780000 |
  50. |    2 | Qandahar                           | AFG         | Qandahar               |     237500 |
  51. |    3 | Herat                              | AFG         | Herat                  |     186800 |
  52. |    4 | Mazar-e-Sharif                     | AFG         | Balkh                  |     127800 |
  53. |    5 | Amsterdam                          | NLD         | Noord-Holland          |     731200 |
  54. |    6 | Rotterdam                          | NLD         | Zuid-Holland           |     593321 |
  55. |    7 | Haag                               | NLD         | Zuid-Holland           |     440900 |
  56. |    8 | Utrecht                            | NLD         | Utrecht                |     234323 |
  57. ............
  60. #可以得出name是城市名，countrycode是国家，district是省份，population是人口
  63. mysql> select name,population from city where name='peking';
  64. +--------+------------+
  65. | name   | population |
  66. +--------+------------+
  67. | Peking |    7472000 |
  68. +--------+------------+
  69. 1 row in set (0.01 sec)
  72. 2）范围条件查看数据信息####################################################################################
  73. ①查看人口数量大于1000w城市和国家信息
  74. mysql> select name,countrycode,population from city where population>10000000;
  75. +-----------------+-------------+------------+
  76. | name            | countrycode | population |
  77. +-----------------+-------------+------------+
  78. | Mumbai (Bombay) | IND         |   10500000 |
  79. +-----------------+-------------+------------+
  80. #                数值做范围查看 > < >= <= <>/!=                        ⭐⭐⭐
  82. ②查看北京上海深圳重庆等一线城市人口数量
  83. mysql> select name,population from city where  name in ('peking','shanghai','shenzhen','chongqing');
  84. +-----------+------------+
  85. | name      | population |
  86. +-----------+------------+
  87. | Shanghai  |    9696300 |
  88. | Peking    |    7472000 |
  89. | Chongqing |    6351600 |
  90. | Shenzhen  |     950500 |
  91. +-----------+------------+
  92. #                字符信息做范围查看 in ()  / not in ()        ⭐⭐⭐
  95. 3) 多个条件列的逻辑关联查询################################################################################
  96. 多个条件逻辑关联方式
  97. and --- 将多个条件做交集处理（将过滤条件做逐步处理 得到需要的查询结果）   
  98. or  --- 将多个条件做并集处理（将过滤条件做分别处理 交结果进行合并显示）
  99. ①查看中国和美国所有城市以及人口数量情况
  100. mysql> select name,population from city where countrycode='chn' or countrycode='usa';
  102. ②查看城市人口数量大于100w并小于300w城市信息
  103. mysql> select name,population from city where population>1000000 and population<3000000;
  105. #        或者另一种表达方式-->  and=between and
  107. mysql> select name,population from city where population between 1000000 and 3000000;
  110. 4) 条件列做模糊过滤查询####################################################################################
  111. 查询广州城市人口信息
  112. mysql> select * from city where countrycode='chn' and district like '%guang%' and name like '%guang%';
  113. +------+--------------------+-------------+-----------+------------+
  114. | ID   | Name               | CountryCode | District  | Population |
  115. +------+--------------------+-------------+-----------+------------+
  116. | 1897 | Kanton [Guangzhou] | CHN         | Guangdong |    4256300 |
  117. +------+--------------------+-------------+-----------+------------+
  120. 5) 取重查询数据信息#######################################################################################
  121. 获取中国有多少个省份
  122. select distinct District from city where countrycode='chn';
  123. select count(district) from city where countrycode='chn';
  124. #        统计省份一共有多少
  126. 6) 查看空值列信息#########################################################################################
  127. mysql> select * from city where population is null;
  128.        
  129. 查看非空列信息
  130. mysql> select * from city where population is not null;
复制代码
单表数据查看练习（进阶查看）

1.分组聚合查看数据信息

①聚合查看数据
  1. ①聚合查看数据                #        查询数据需要做统计分析  实现聚合查看数据需要应用聚合函数
  3. 1.查看中国人口总数
  4. select countrycode,sum(population) from city where countrycode='chn';
  5. +-------------+-----------------+
  6. | countrycode | sum(population) |
  7. +-------------+-----------------+
  8. | CHN         |       175953614 |
  9. +-------------+-----------------+
  11. 2.查看中国城市数量  
  12. select countrycode,count(name) from city where countrycode='chn';
  13. +-------------+-------------+
  14. | countrycode | count(name) |
  15. +-------------+-------------+
  16. | CHN         |         364 |
  17. +-------------+-------------+
  19. 3.查看中国人口最多的城市信息/查看中国人口最少的城市信息
  20. mysql> select countrycode,max(population) from city where countrycode='chn';
  21. +-------------+-----------------+
  22. | countrycode | max(population) |
  23. +-------------+-----------------+
  24. | CHN         |         9696300 |
  25. +-------------+-----------------+
  26. 1 row in set (0.05 sec)
  28. mysql> select countrycode,min(population) from city where countrycode='chn';
  29. +-------------+-----------------+
  30. | countrycode | min(population) |
  31. +-------------+-----------------+
  32. | CHN         |           89288 |
  33. +-------------+-----------------+
  36. avg()  -- 取某个数值列的平均数
  37. max()  -- 取某个数值列的最大值
  38. min()  -- 取某个数值列的最小值
  39. -- 直接查看列的最大值或最小值
  44. 额外：显示查看中国人口最多的城市信息，显示人口信息与城市名字
  45. SELECT name,countrycode,population
  46. FROM city
  47. WHERE countrycode = 'chn'
  48. ORDER BY population DESC
  49. LIMIT 1;
  51. +----------+-------------+------------+
  52. | name     | countrycode | population |
  53. +----------+-------------+------------+
  54. | Shanghai | CHN         |    9696300 |
  55. +----------+-------------+------------+
  58. 执行过程（精讲版）：
  59. 我们可以把它拆解成五个简单的步骤来理解：
  60. 1. FROM city
  61. 第一步：去哪个表里找？
  62. 告诉数据库：“请打开名为 city 的那张表（里面存着所有城市的数据）。”
  64. 2. WHERE countrycode = 'chn'
  65. 第二步：筛选哪些行？
  66. 告诉数据库：“在这张表里，我只关心 countrycode（国家代码） 等于 'chn' （中国）的那些行。其他的国家（如 'usa', 'jpn'）全部忽略。”
  67. 此时，手里剩下的全是中国的城市数据。
  69. 3. ORDER BY population DESC
  70. 第三步：怎么排队？
  71. 告诉数据库：“把刚才筛选出来的中国城市，按照 population（人口） 这一列进行排序。”
  72. DESC 是 Descend（下降）的缩写，意思是从大到小排。                               
  73. 此时，人口最多的城市排在了第 1 位，人口最少的排在了最后。
  74. #        升序用ORDER BY population，降序用ORDER BY population DESC
  76. 4. LIMIT 1
  77. 第四步：只要几个？
  78. 告诉数据库：“排好队后，我只要第 1 个（也就是排在最前面、人口最多的那一个），后面的都不要了。”
  79. 如果没有这句话，数据库会把所有中国城市按人口从大到小全部列出来。
  81. 5. SELECT name, countrycode, population
  82. 第五步：给我看什么？
  83. 告诉数据库：“对于最后留下的这唯一一行数据，我只需要看这三列信息：name（城市名）、countrycode（国家代码） 和 population（人口数）。其他的信息（比如所属省份 District、城市 ID 等）不用显示给我。”
复制代码
②分组查看数据⭐
  1. ②分组查看数据                #        表示将某个列相同信息分为一组，然后将分组后数据分别做分析处理
  3. 1.查看全球范围内，每个国家的人口总数
  4. select countrycode,sum(population) from city group by countrycode;
  6. 2.查看中国境内，每个省份的人口总数
  7. select district,sum(population) from city where countrycode='CHN' group by district;
  12. 知识扩展说明：分组聚合查看数据常见问题
  14. 3.查看中国境内，每个省份的城市数量，以及显示省份的城市信息
  15. select district,count(name),name from city where countrycode='CHN' group by district;
  16. ERROR 1140 (42000):
  17. In aggregated query without GROUP BY,
  18. expression #2 of SELECT list contains nonaggregated column 'world.city.Name';
  19. this is incompatible with sql_mode=only_full_group_by
  21. 在聚合查看数据时，需要使用group by做分组操作
  22. 在分组聚合处理输出信息时，某些非聚合处理列，无法正常显示内容
  23. sql_mode=only_full_group_by  无法实现数据表中一行信息对多行信息关联显示
  25. 分组聚合查询原理：
  26. 1）会先根据分组列信息，做排序处理
  27. 2）会将分组后的相同数据列信息合并成一行
  28. 3）会将需要分析统计列做对应聚合处理
  30. 解决以上分组聚合输出问题，可以将多行信息转换成一行
  31. select district,count(name),group_concat(name) from city where countrycode='CHN' group by district;
  33. 命令的执行过程：
  34. 1. GROUP BY district —— 先分类
  35. 动作：把全中国的城市，按照 district（省份/地区）分成不同的堆。
  36. 结果：比如“广东”一堆，“江苏”一堆，“北京”一堆……
  37. 2. COUNT(name) —— 数个数
  38. 动作：在每一堆里，数一数有多少个城市名字。
  39. 结果：得到该地区的城市数量。
  40. 例如：广东 -> 50个，江苏 -> 40个。
  41. 3. GROUP_CONCAT(name) —— 连名字
  42. 动作：把每一堆里的所有城市名字，用逗号 , 连接成一个长长的字符串。
  43. 结果：得到该地区的城市名单列表。
  44. 例如：广东 -> "广州,深圳,珠海,佛山..."
  47. #        分行显示
  48. select district,count(name),group_concat(name) from city where countrycode='CHN' group by district\G;
复制代码
2.分组聚合过滤查看数据信息 -- having⭐
  1. 2）分组聚合过滤查看数据信息 -- having
  2. 查看全球范围内，每个国家的人口总数，将人口总数大于5000w国家信息显示出来
  3. mysql> select countrycode,sum(population)
  4.     -> from city
  5.     -> group by countrycode
  6.     -> having sum(population)>50000000
  7.     -> ;
  8. +-------------+-----------------+
  9. | countrycode | sum(population) |
  10. +-------------+-----------------+
  11. | BRA         |        85876862 |
  12. | CHN         |       175953614 |
  13. | IND         |       123298526 |
  14. | JPN         |        77965107 |
  15. | MEX         |        59752521 |
  16. | RUS         |        69150700 |
  17. | USA         |        78625774 |
  18. +-------------+-----------------+
  22. having -- 会将聚合列作为条件过滤                （统计分析的数据做过滤）  
  23. where  -- 会将现有数据列作为条件过滤           （表中有的数据做过滤）
复制代码
3.数据信息排序查询
  1. 3）数据信息排序查询  
  2. 查看全球范围内，每个国家的人口总数，将人口总数大于5000w国家信息显示出来，并排序显示输出
  3. mysql> select countrycode,sum(population)
  4.     -> from city
  5.     -> group by countrycode
  6.     -> having sum(population)>50000000
  7.     -> order by sum(population)
  8.     -> ;
  9. +-------------+-----------------+
  10. | countrycode | sum(population) |
  11. +-------------+-----------------+
  12. | MEX         |        59752521 |
  13. | RUS         |        69150700 |
  14. | JPN         |        77965107 |
  15. | USA         |        78625774 |
  16. | BRA         |        85876862 |
  17. | IND         |       123298526 |
  18. | CHN         |       175953614 |
  19. +-------------+-----------------+
  21. -- order by默认排序为升序排序⭐
  23. 查看全球范围内，每个国家的人口总数，将人口总数大于5000w国家信息显示出来，并按降序显示输出
  24. mysql> select countrycode,sum(population)
  25.     -> from city
  26.     -> group by countrycode
  27.     -> having sum(population)>50000000
  28.     -> order by sum(population) DESC
  29.     -> ;
  30. +-------------+-----------------+
  31. | countrycode | sum(population) |
  32. +-------------+-----------------+
  33. | CHN         |       175953614 |
  34. | IND         |       123298526 |
  35. | BRA         |        85876862 |
  36. | USA         |        78625774 |
  37. | JPN         |        77965107 |
  38. | RUS         |        69150700 |
  39. | MEX         |        59752521 |
  40. +-------------+-----------------+
复制代码
4.截取部分输出信息显示
  1. 4) 截取部分输出信息显示
  2. 显示全球人口总数大于5000w的前3名
  3. mysql> select countrycode,sum(population)
  4.     -> from city
  5.     -> group by countrycode
  6.     -> having sum(population)>50000000
  7.     -> order by sum(population) DESC
  8.     -> limit 3
  9.     -> ;
  10. +-------------+-----------------+
  11. | countrycode | sum(population) |
  12. +-------------+-----------------+
  13. | CHN         |       175953614 |
  14. | IND         |       123298526 |
  15. | BRA         |        85876862 |
  16. +-------------+-----------------+
  19. 单表查询数据常用语句：
  20. select 输出列信息 from 库.表 where+group by+having+order by[desc]+limit;
复制代码
小知识点总结❗️

[code]1.order by排序的升序和降序升序用ORDER BY population降序用ORDER BY population DESC 即order by默认就是升序，降序只需要加一个DESC####################################################################2.sum()和count()的区别简单一句话总结：COUNT() 是数个数（有多少行？有多少个非空值？）。SUM() 是加总值（这些数字加起来是多少？）。问 “多少个？” (How many?) -> 用 COUNT。问 “总共多少？” (How much total?) -> 用 SUM。####################################################################3.where 和 havinghaving -- 会将聚合列作为条件过滤                （统计分析的数据做过滤）  where  -- 会将现有数据列作为条件过滤           （表中有的数据做过滤）使用where的时候要后group by分组，使用having的时候要先group by分组❗️where不能使用聚合函数，having可以使用聚合函数，如HAVING COUNT(*) > 5的表达方式####################################################################4.SQL的逻辑执行顺序①FROM (找表)②WHERE (
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
数据库应用第三天

使用道具 举报

相关推荐
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
芮梦月
2 小时前

0

粉丝关注

27

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫