mysql日常笔记

叼着奶瓶敲键盘

已于 2022-07-01 14:27:44 修改

阅读量815

点赞数 1

分类专栏： Linux 文章标签： mysql 数据库 database

于 2022-01-18 11:33:50 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_45646026/article/details/122454974

版权

Linux 专栏收录该内容

13 篇文章

订阅专栏

1、连接数据库

mysql -u root -p -h 192.168.1.16 -P 3309

mysql -u 用户名 -p -h 数据库所在主机地址 -P 端口号

2、查看有哪些数据库并使用

show databases; 查看都有哪些数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| config             |
| face_source        |
| facedb             |
| facedb_model1      |
| facedb_model2      |
| mysql              |
| performance_schema |
| sys                |
| taskapi            |
| web_system_gdrs    |
| zl802              |
+--------------------+

use 数据库名称; 使用数据库

mysql> use facedb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

3、一些数据库操作

1、查看数据库中有哪些表

show tables;

mysql> show tables;
+---------------------------+
| Tables_in_facedb          |
+---------------------------+
| dictionary_dict           |
| dictionary_option         |
| face_channel              |
| face_channel_group        |
| face_channel_video        |
| face_daily_list           |
| face_daily_report         |
| face_daily_report_website |
| face_program              |
| face_website              |
+---------------------------+

2、查看某个表里字段的设置

show columns from 表名;

mysql> show columns from dictionary_dict;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | NO   |     | NULL    |                |
| memo        | varchar(255) | YES  |     | NULL    |                |
| parent_id   | int(11)      | YES  |     | NULL    |                |
| is_active   | tinyint(1)   | NO   |     | NULL    |                |
| is_readonly | tinyint(1)   | YES  |     | NULL    |                |
| option_type | varchar(20)  | NO   |     | text    |                |
| create_time | timestamp    | YES  |     | NULL    |                |
| update_time | timestamp    | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

3、查询表里的所有数据信息

select * from 表名; (如果想要查询表里某个字段的信息，把'*'换成字段名就可以了，多个字段名以逗号隔开)

mysql> select * from dictionary_dict;
+----+--------------------------+---------------------+-----------+-----------+-------------+-------------+---------------------+---------------------+
| id | name                     | memo                | parent_id | is_active | is_readonly | option_type | create_time         | update_time         |
+----+--------------------------+---------------------+-----------+-----------+-------------+-------------+---------------------+---------------------+
|  1 | resource_type            | ????                |      NULL |         1 |           1 | number      | 2021-12-22 18:16:14 | 2022-01-05 17:39:14 |
|  2 | person_class             | ????                |      NULL |         1 |           1 | number      | 2021-12-22 19:17:33 | 2022-01-06 15:38:29 |
|  3 | person_level             | ????                |         2 |         1 |           1 | number      | 2021-12-22 19:17:46 | 2021-12-22 19:17:46 |
|  4 | channel_level            | ????                |      NULL |         1 |           1 | number      | 2021-12-22 19:18:04 | 2021-12-22 19:18:04 |
|  5 | channel_category         | ????                |      NULL |         1 |           1 | number      | 2021-12-22 19:18:13 | 2022-01-10 16:25:36 |
|  6 | channel_classify         | ????                |      NULL |         1 |           1 | text        | 2021-12-22 19:18:22 | 2021-12-22 19:18:22 |
|  7 | channel_type             | ????                |      NULL |         1 |           1 | text        | 2021-12-22 19:18:31 | 2021-12-22 19:18:31 |
|  8 | report_program_type      | ?? ---?? --- ????   |      NULL |         1 |           1 | text        | 2021-12-22 19:18:43 | 2021-12-22 19:18:43 |
|  9 | report_way               | ??--????            |      NULL |         1 |           1 | text        | 2021-12-22 19:18:52 | 2021-12-22 19:18:52 |
| 17 | task_video_status        | taskapi,??????????? |      NULL |         1 |           1 | text        | 2022-01-05 13:06:03 | 2022-01-05 13:06:03 |
| 27 | strategy_resolving_power | ????-???            |      NULL |         1 |           1 | text        | 2022-01-06 15:13:40 | 2022-01-06 15:13:40 |
| 33 | task_type                | ????                |      NULL |         1 |           1 | number      | 2022-01-10 15:01:21 | 2022-01-10 15:01:21 |
| 35 | channel_location         | ??????              |      NULL |         1 |           1 | number      | 2022-01-10 16:27:21 | 2022-01-10 16:27:21 |
| 37 | report_program_origin    | test                |      NULL |         1 |           1 | text        | 2022-01-11 11:38:55 | 2022-01-11 11:38:55 |
| 39 | website_level            | ????(??)            |      NULL |         1 |           1 | text        | 2022-01-11 18:48:29 | 2022-01-11 18:48:29 |
+----+--------------------------+---------------------+-----------+-----------+-------------+-------------+---------------------+---------------------+

4、如果想要查询某个表里的某个字段，但是这个字段的数据重复值太多，我们需要去重，就拿dictionary_dict中的option_type举例

select option_type from dictionary_dict 不去重

mysql> select option_type from dictionary_dict;
+-------------+
| option_type |
+-------------+
| number      |
| number      |
| number      |
| number      |
| number      |
| text        |
| text        |
| text        |
| text        |
| text        |
| text        |
| number      |
| number      |
| text        |
| text        |
+-------------+
15 rows in set (0.00 sec)

select distinct option_type from dictionary_dict; 去重

mysql> select distinct option_type from dictionary_dict;
+-------------+
| option_type |
+-------------+
| number      |
| text        |
+-------------+
2 rows in set (0.00 sec)

5、查询表里指定行数据

select * from 表名 limit 5; 这里指获取表里前五行的数据

select * from 表名 limit 5,6; 这里指从第5行开始，获取之后的6行数据（不包括第五行数据）

6、添加

alter table 表名 add 字段名字段类型 comment 字段备注给某个表添加字段

7、删除

alter table 表名 DROP COLUMN 字段删除字段

del from 表名 where 条件删除数据

8、修改（更新）

update 表名 set 字段名=concat('字符串',想要和哪个字段拼接) where 条件给某个字段内容添加前缀

update 表名 set 字段名=replace(字段名,'原字符串','新字符串') where 条件给某个字段内容的某个字符串进行修改，如果是对多个字段的某个字符串进行修改，可以再加条件 update 表名 set 字段名=replace(字段名,'原字符串','新字符串'),字段名=replace(字段名,'原字符串','新字符串') where 条件