mysql 常用的查询命令

ゝ一纸荒年。 2024-04-18 20:47 32阅读 0赞

从Key1中获取rand1

SELECT rand1 FROM key_first WHERE key_first.id='1'

数据如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmJvdW5jb25zdHJhaW50_size_16_color_FFFFFF_t_70]

--------------------

查询表名

show tables;

![在这里插入图片描述][2019091116502072.png]

--------------------

查询字段

select COLUMN_NAME from information_schema.COLUMNS where table_name = 'sdv';

![在这里插入图片描述][20190911164857161.png]

--------------------

查询字段  
//从指定数据库

select COLUMN_NAME from information_schema.COLUMNS where table_name = 'sdv' and table_schema = 'linbx';

![在这里插入图片描述][20190911164946661.png]

--------------------

## 查询表的行数 ##

SELECT table_rows FROM information_schema.tables where table_name = 'sdv';
    //SELECT table_name,table_rows FROM information_schema.tables;

![在这里插入图片描述][20190911170954458.png]

**建议使用这个**，上面的information\_schema，有时候会不准（在行数很多的时候，我测试是六千多就会不准）

SELECT COUNT(*) FROM key_second;

![!\[在这里插入图片描述\](https://img-blog.csdnimg.cn/20191120163558836.png][https_img-blog.csdnimg.cn_20191120163558836.png]

--------------------

查询表的列数

SELECT COUNT(*) FROM information_schema.COLUMNS where table_name = 'sdv';

![在这里插入图片描述][20190911171753118.png]

--------------------

根据id插入update数据

UPDATE u_bdj SET xu_lie_hao='zzz1zz'  WHERE u_bdj.pid='123'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmJvdW5jb25zdHJhaW50_size_16_color_FFFFFF_t_70 1]

SELECT table_rows FROM information_schema.tables where table_name = 'u_bdj'
    
    UPDATE u_bdj SET xu_lie_hao='33333', pid='xxx', uid='www' , name='wx1111111xxww' WHERE id='0'

--------------------

## 根据字段A获取其它字段内容 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmJvdW5jb25zdHJhaW50_size_16_color_FFFFFF_t_70 2]

SELECT id FROM correlation WHERE name='aaa999'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmJvdW5jb25zdHJhaW50_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmJvdW5jb25zdHJhaW50_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/12ddbd7d6b374af794b6063662f59093.png
[2019091116502072.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/660e8335794a40bb9a8a4c3f0debad2b.png
[20190911164857161.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/ac8095dff0c940248db721224ba5fb64.png
[20190911164946661.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/2a4c24eb9caf4d7dad0e71aae78954de.png
[20190911170954458.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/593d0b0ca53344f1881b25078b3169d9.png
[https_img-blog.csdnimg.cn_20191120163558836.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/065072c23d6c4a369170a37259b701b1.png
[20190911171753118.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/928bb20822714af2a3a10465e0fb9a4c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmJvdW5jb25zdHJhaW50_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7d680e225ff842dab224302fc3b6fe0d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmJvdW5jb25zdHJhaW50_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/84da013b4ed34c558aa860d9d1ffaca4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbmJvdW5jb25zdHJhaW50_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/4489dd9500ff489381ec577fd7823d14.png