Mysql 使用过程问题总结

岳来

已于 2023-10-07 10:30:02 修改

阅读量452

点赞数 2

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

于 2022-05-10 11:14:19 首次发布

本文链接：https://blog.csdn.net/yuelai_217/article/details/124684000

版权

mysql 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

1、创建数据库

mysql> create database daily_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| daily_test         |
| information_schema |
| mysql              |
| performance_schema |
| pipeline           |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

create table if not exists internet_company (id INT UNSIGNED AUTO_INCREMENT,
                                             name VARCHAR(100) NOT NULL,
                                             person VARCHAR(100) NOT NULL,
                                             country VARCHAR(100) NOT NULL,
                                             PRIMARY KEY (id)
                                            )ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、创建表

mysql> create table if not exists internet_company (id INT UNSIGNED AUTO_INCREMENT,
    ->                                              name VARCHAR(100) NOT NULL,
    ->                                              person VARCHAR(100) NOT NULL,
    ->                                              country VARCHAR(100) NOT NULL,
    ->                                              PRIMARY KEY (id)
    ->                                             )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show tables;
+----------------------+
| Tables_in_daily_test |
+----------------------+
| internet_company     |
+----------------------+
1 row in set (0.00 sec)

3、表中插入数据

mysql> insert into internet_company (id,name,person,country) values(1,"alibaba","mayun","china");
Query OK, 1 row affected (0.01 sec)

mysql> select * from internet_company;
+----+---------+--------+---------+
| id | name    | person | country |
+----+---------+--------+---------+
|  1 | alibaba | mayun  | china   |
+----+---------+--------+---------+
1 row in set (0.00 sec)


# id values可以加引号
mysql> insert into internet_company (id,name,person,country) values("2","jingdong","liuqiangdong","china");
Query OK, 1 row affected (0.01 sec)

mysql> select * from internet_company;
+----+----------+--------------+---------+
| id | name     | person       | country |
+----+----------+--------------+---------+
|  1 | alibaba  | mayun        | china   |
|  2 | jingdong | liuqiangdong | china   |
+----+----------+--------------+---------+
2 rows in set (0.00 sec)

# AUTO_INCREMENT id可以不设置，默认递增
mysql> insert into internet_company (name,person,country) values("wangyi","dinglei","china");
Query OK, 1 row affected (0.01 sec)

mysql> select * from internet_company;
+----+----------+--------------+---------+
| id | name     | person       | country |
+----+----------+--------------+---------+
|  1 | alibaba  | mayun        | china   |
|  2 | jingdong | liuqiangdong | china   |
|  3 | wangyi   | dinglei      | china   |
+----+----------+--------------+---------+
3 rows in set (0.00 sec)


# insert into 必须给所有的字段赋值
mysql> insert into internet_company (name,country) values("拼多多","china");
ERROR 1364 (HY000): Field 'person' doesn't have a default value


mysql> insert into internet_company (name,person,country) values("拼多多","","china");
Query OK, 1 row affected (0.00 sec)

mysql> select * from internet_company;
+----+-----------+--------------+---------+
| id | name      | person       | country |
+----+-----------+--------------+---------+
|  1 | alibaba   | mayun        | china   |
|  2 | jingdong  | liuqiangdong | china   |
|  3 | wangyi    | dinglei      | china   |
|  4 | 拼多多    |              | china   |
+----+-----------+--------------+---------+
4 rows in set (0.00 sec)


# 键值不对应，值中缺少person对应的
mysql> insert into internet_company (name,person,country) values("taobao","china");
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into internet_company (name,person,country) values("taobao","戴""珊","china");
Query OK, 1 row affected (0.00 sec)

mysql> select * from internet_company;
+----+-----------+--------------+---------+
| id | name      | person       | country |
+----+-----------+--------------+---------+
|  1 | alibaba   | mayun        | china   |
|  2 | jingdong  | liuqiangdong | china   |
|  3 | wangyi    | dinglei      | china   |
|  4 | 拼多多    |              | china   |
|  5 | taobao    | 戴"珊        | china   |
+----+-----------+--------------+---------+
5 rows in set (0.00 sec)

4、更新表数据

mysql> update internet_company set person='黄铮，”帅哥“，‘哈哈’，说你呢' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from internet_company;
+----+-----------+--------------------------------------------------+---------+
| id | name      | person                                           | country |
+----+-----------+--------------------------------------------------+---------+
|  1 | alibaba   | mayun                                            | china   |
|  2 | jingdong  | liuqiangdong                                     | china   |
|  3 | wangyi    | dinglei                                          | china   |
|  4 | 拼多多    | 黄铮，”帅哥“，‘哈哈’，说你呢                     | china   |
|  5 | taobao    | 戴"珊                                            | china   |
+----+-----------+--------------------------------------------------+---------+
5 rows in set (0.00 sec)


# 引号需要转义
mysql> update internet_company set person='戴珊 [[ $(time) == 'now' ]]' where id=5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'now' ]]' where id=5' at line 1


mysql> update internet_company set person='戴珊 [[ $(time) == \'now\' ]]' where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from internet_company;
+----+-----------+--------------------------------------------------+---------+
| id | name      | person                                           | country |
+----+-----------+--------------------------------------------------+---------+
|  1 | alibaba   | mayun                                            | china   |
|  2 | jingdong  | liuqiangdong                                     | china   |
|  3 | wangyi    | dinglei                                          | china   |
|  4 | 拼多多    | 黄铮，”帅哥“，‘哈哈’，说你呢                     | china   |
|  5 | taobao    | 戴珊 [[ $(time) == 'now' ]]                      | china   |
+----+-----------+--------------------------------------------------+---------+
5 rows in set (0.00 sec)

5、删除表数据

5.1、清空表

mysql> truncate table base_data;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from base_data;
Empty set (0.00 sec)

5.2、删除表中指定数据

mysql> select id from base_data;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)
mysql> delete from base_data where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select id from base_data;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> delete from base_data where id between 373 and 431;
Query OK, 59 rows affected (0.01 sec)

6、删除表字段

删除一个字段
alter table build_task drop column aarch64_mirror_sha256;


删除多个字段
alter table build_task drop column aarch64_mirror_tag, drop column build_path, drop column is_recommend;

7、拷贝表中一条数据插入表中

#从不同的表复制
insert into 表1 select * from 表2 where id =** ;

#同一张表中复制（无主键）
insert into 表1 select * from 表2 where id =** ;

#同一张表中复制（有主键）
insert into 表1(字段1,字段2,字段3) select 字段1,字段2,字段3 from 表1 where id= ** ;

8、同时查看多个条件

select * from 表 where 字段1='xxx' and 字段2='yyy'and 字段3='zzzz';

9、查看mysql 数据存储目录

mysql> SHOW VARIABLES LIKE '%datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

10、查看 mysql `max_allowed_packet`

max_allowed_packet 指mysql服务器端和客户端在一次传送数据包的过程当中最大允许的数据包大小

show global variables like 'max_allowed_packet';

11、查看 mysql `max_heap_table_size`

show variables like 'max_heap_table_size';

12、字符集相关

12.1、查看数据库字符集

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

12.2、查看每个字段详情

mysql> show create table build_task;

| build_task | CREATE TABLE `build_task` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(6) NOT NULL,
  )
mysql> show full columns from build_task;
+-----------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field                 | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-----------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id                    | int(11)      | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| create_time           | datetime(6)  | NULL            | NO   |     | NULL    |                | select,insert,update,references |         |

12.3、修改表字符集

mysql> ALTER TABLE build_task CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

13、No module named MySQLdb

解决办法：

pip install MySQL-python

安装失败，报错：EnvironmentError: mysql_config not found
需要安装依赖：
yum install gcc mysql-devel

14、Mysql连接报错：1130-host … is not allowed to connect to this MySql server

解决办法：添加host 到 user表中,具体如下：

# 1、连接服务器: 
mysql -u root -p

# 2、看当前所有数据库：
show databases;

#3、进入mysql数据库：
use mysql;

# 4、查看mysql数据库中所有的表：
show tables;

# 5、查看user表中的数据：
select Host, User,Password from user;

#6、修改user表中的Host(% 指主机名称): 
update user set Host='%' where User='root';

# 7、最后刷新一下：
flush privileges;