mysql的values中的

转载

数码精灵abc 2024-12-03 11:56:11

文章标签 mysql的values中的 mysql 数据库 ci 文章分类 MySQL 数据库

进入数据库

安装过程跳过，直接进入数据库

[root@zheng ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

数据库基本语句

查询数据库

show

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

进入mysql名字哪个数据库

use

mysql> use mysql;
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

查询mysql数据库的表

刚才已经use这个数据库了，所以直接查询就可以

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |

如果想查询别的数据库的信息要重新use到别的数据库，这样很麻烦
可以在这个数据库直接查询另一个数据库的表，不用重新进入
在mysql数据库下查询sys数据库下的表

mysql> show tables from sys;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |

查询当前数据库

mysql> select database();
+------------+
| database() |
+------------+
| sys        |
+------------+
1 row in set (0.00 sec)

创建表 create

创建一个学生信息表
表名为 stuinfo
学号为 int 类型
名字为字符型，字符个数为20个
性别为单个字符
生日类型为datetime

mysql> create table stuinfo(
    ->  stuid int,
    -> stuname varchar(20),
    -> gender char,
    -> borndate datetime);
Query OK, 0 rows affected (0.05 sec)

查询创建表成功了吗

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

查看表的描述

mysql> desc stuinfo;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stuid    | int(11)     | YES  |     | NULL    |       |
| stuname  | varchar(20) | YES  |     | NULL    |       |
| gender   | char(1)     | YES  |     | NULL    |       |
| borndate | datetime    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

插入表数据 insert

mysql> insert into stuinfo values(1,'史森明','男','2000-6-21');
ERROR 1366 (HY000): Incorrect string value: '\xE5\x8F\xB2\xE6\xA3\xAE...' for column 'stuname' at row 1

检查字段状态

报错了，先检查一下字段的状态

mysql> show full columns from stuinfo;
+----------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field    | Type        | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+----------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| stuid    | int(11)     | NULL              | YES  |     | NULL    |       | select,insert,update,references |         |
| stuname  | varchar(20) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| gender   | char(1)     | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| borndate | datetime    | NULL              | YES  |     | NULL    |       | select,insert,update,references |         |
+----------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.00 sec)

mysql的values中的_ci

修改字段字符编码 alter

这个不支持中文，所以要把这两个改成utf8

mysql> alter table stuinfo change stuname stuname varchar(25) character set utf8 collate utf8_unicode_ci not null default '';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table stuinfo change stuname name varchar(25) character set utf8 collate utf8_unicode_ci not null default '';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

重新检查字段状态

mysql> show full columns from stuinfo;
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field    | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| stuid    | int(11)     | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |
| name     | varchar(25) | utf8_unicode_ci | NO   |     |         |       | select,insert,update,references |         |
| gender   | char(1)     | utf8_unicode_ci | NO   |     |         |       | select,insert,update,references |         |
| borndate | datetime    | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.00 sec)

然后重新插入表数据

mysql> insert into stuinfo values(1,'史森明','男','1999-03-03');
Query OK, 1 row affected (0.00 sec)

mysql> insert into stuinfo values('1','uzi','男','1997-04-05');
Query OK, 1 row affected (0.00 sec)

mysql> insert into stuinfo values('2','史森明','男','1998-12-22');
Query OK, 1 row affected (0.00 sec)

查看表的信息

mysql> select * from stuinfo;
+-------+-----------+--------+---------------------+
| stuid | name      | gender | borndate            |
+-------+-----------+--------+---------------------+
|     1 | 史森明    | 男     | 1999-03-03 00:00:00 |
|     1 | uzi       | 男     | 1997-04-05 00:00:00 |
|     2 | 史森明    | 男     | 1998-12-22 00:00:00 |
+-------+-----------+--------+---------------------+
3 rows in set (0.00 sec)

修改表数据 update

之前插入数据的时候出错，stuid 和名字都重了，需要修改
修改名字的时候要注意，选择字段以后还要选择在哪里
不选择就会把这个字段的所有数据都修改

mysql> update stuinfo set name='小虎'；
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from stuinfo;
+-------+--------+--------+---------------------+
| stuid | name   | gender | borndate            |
+-------+--------+--------+---------------------+
|     1 | 小虎   | 男     | 1999-03-03 00:00:00 |
|     1 | 小虎   | 男     | 1997-04-05 00:00:00 |
|     2 | 小虎   | 男     | 1998-12-22 00:00:00 |
+-------+--------+--------+---------------------+

修改name为史森明，在stuid =2的地方修改

mysql> update stuinfo set name='史森明' where stuid = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stuinfo;
+-------+-----------+--------+---------------------+
| stuid | name      | gender | borndate            |
+-------+-----------+--------+---------------------+
|     1 | 小虎      | 男     | 1999-03-03 00:00:00 |
|     1 | 小虎      | 男     | 1997-04-05 00:00:00 |
|     2 | 史森明    | 男     | 1998-12-22 00:00:00 |
+-------+-----------+--------+---------------------+

修改name为uzi 在 brondate=1997-04-05的地方修改

mysql> update stuinfo set name='uzi'  where borndate = '1997-04-05';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stuinfo;
+-------+-----------+--------+---------------------+
| stuid | name      | gender | borndate            |
+-------+-----------+--------+---------------------+
|     1 | 小虎      | 男     | 1999-03-03 00:00:00 |
|     1 | uzi       | 男     | 1997-04-05 00:00:00 |
|     2 | 史森明    | 男     | 1998-12-22 00:00:00 |
+-------+-----------+--------+---------------------+

接下来就依次修改就好了

mysql> update stuinfo set stuid = '2' where name = 'uzi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update stuinfo set stuid = '3' where name = '史森明';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stuinfo;
+-------+-----------+--------+---------------------+
| stuid | name      | gender | borndate            |
+-------+-----------+--------+---------------------+
|     1 | 小虎      | 男     | 1999-03-03 00:00:00 |
|     2 | uzi       | 男     | 1997-04-05 00:00:00 |
|     3 | 史森明    | 男     | 1998-12-22 00:00:00 |
+-------+-----------+--------+---------------------+

删除表数据 delete

先插入一个没用的数据，然后再删除

mysql> insert into stuinfo values(4,'Loken','女','2021-04-09');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stuinfo;
+-------+-----------+--------+---------------------+
| stuid | name      | gender | borndate            |
+-------+-----------+--------+---------------------+
|     1 | 小虎      | 男     | 1999-03-03 00:00:00 |
|     2 | uzi       | 男     | 1997-04-05 00:00:00 |
|     3 | 史森明    | 男     | 1998-12-22 00:00:00 |
|     4 | Loken     | 女     | 2021-04-09 00:00:00 |
+-------+-----------+--------+---------------------+

delete删除掉插入的数据

mysql> delete from stuinfo where stuid = '4';
Query OK, 1 row affected (0.00 sec)

mysql> select * from stuinfo;
+-------+-----------+--------+---------------------+
| stuid | name      | gender | borndate            |
+-------+-----------+--------+---------------------+
|     1 | 小虎      | 男     | 1999-03-03 00:00:00 |
|     2 | uzi       | 男     | 1997-04-05 00:00:00 |
|     3 | 史森明    | 男     | 1998-12-22 00:00:00 |
+-------+-----------+--------+---------------------+

修改表结构 alter

在表中添加一个email新字段，类型varchar（20）
add column 添加新字段

mysql> alter table stuinfo add column email varchar(20);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stuinfo;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stuid    | int(11)     | YES  |     | NULL    |       |
| name     | varchar(25) | NO   |     |         |       |
| gender   | char(1)     | NO   |     |         |       |
| borndate | datetime    | YES  |     | NULL    |       |
| email    | varchar(20) | YES  |     | NULL    |       |

删除表 drop

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

mysql> drop table stuinfo;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：安卓 Springboot后端部署

下一篇：opencv 替代品

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql的values中的

mysql的values中的

进入数据库

数据库基本语句

查询数据库

进入mysql名字哪个数据库

查询mysql数据库的表

查询当前数据库

创建表 create

查询创建表成功了吗

查看表的描述

插入表数据 insert

检查字段状态

修改字段字符编码 alter

查看表的信息

修改表数据 update

删除表数据 delete

修改表结构 alter

删除表 drop

51CTO博客