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)

修改字段字符编码 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)