|NO.Z.00088|——————————|LinuxNetwork|——|Linux&MySQL.V03|——|增删改查|
一、MySQL常用操作：创建数据库
### --- 创建数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)### --- 创建数据库
mysql> create database atyanqi;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atyanqi |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)二、创建数据表
### --- 创建数据表
### --- 选择要使用的数据库
mysql> use atyanqi;### --- 创建 a1 表，并添加 id 和 name 字段以及类型
mysql> create table a1 (id int,name char(20),age int);
Query OK, 0 rows affected (0.02 sec)### --- 查看表结构（字段）
mysql> describe a1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)### --- 查看表结构（字段）
mysql> use atyanqi;
mysql> create table a2 (
-> id int unsigned not null auto_increment, // 字段要求为正数、且自增长、主键
-> name char(20) not null default '', // 字符型长度 30 字节，默认值为空格
-> age int not null default 0, // 字段默认值为 0
-> primary key (id)); // 设置 id 为主键
Query OK, 0 rows affected (0.57 sec)mysql> describe a1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> describe a2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | | |
| age | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)一、增加数据：插入数据/查看数据
### --- 增加数据：插入数据
mysql> use atyanqi; // 确认在atyanqi这个库里面
Database changed
mysql> show tables;
+-------------------+
| Tables_in_atyanqi |
+-------------------+
| a1 |
| a2 |
+-------------------+
2 rows in set (0.00 sec)二、查询数据
### --- 查询数据
mysql> select * from a1;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 18 |
+------+----------+------+
1 row in set (0.00 sec)mysql> insert into a1 values (2,'lisi',28),(3,'laow',20); // 指明插入字段和数据
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from a1;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 18 |
| 2 | lisi | 28 |
| 3 | laow | 20 |
+------+----------+------+
3 rows in set (0.00 sec)Mysql>insert into a2 values (2,‘lisi’,20)； // 按顺序插入指定字段
Mysql>insert into a2 values (3,‘wangwu’)； // 未声明年龄
Mysql>insert into a2 values (4,‘zhao’,19)，(5,‘sun’,25)； // 插入多条数据### --- 将表 a2 的数据复制到表 a1
mysql> select * from a1;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 18 |
| 2 | lisi | 28 |
| 3 | laow | 20 |
+------+----------+------+
3 rows in set (0.00 sec)mysql> insert into a2 (id,name,age) select * from a1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0### --- 查询 a1 值，并写入到 a2
mysql> select * from a2;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 18 |
| 2 | lisi | 28 |
| 3 | laow | 20 |
+----+----------+-----+
3 rows in set (0.00 sec)三、删除数据库
### --- 删除数据库
mysql> drop tables a1; // 删除a1数据库
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_atyanqi |
+-------------------+
| a2 |
+-------------------+
1 row in set (0.00 sec)mysql> create database abc;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| atyanqi |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)mysql> drop database abc;
Query OK, 0 rows affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atyanqi |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)### --- 删除数据表
Mysql>drop table a1；
Mysql>show table;### --- 删除表里的数据记录
~~~ 库和表的删除用 drop,记录删除用 delete
mysql> delete from a2 where id=3; // 删除 id=5 的记录
Query OK, 1 row affected (0.00 sec)mysql> select * from a2;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 18 |
| 2 | lisi | 28 |
+----+----------+-----+
2 rows in set (0.00 sec)
mysql> delete from a2 where age between 25 and 30; // 删除年龄在 23-25 之间的
Query OK, 1 row affected (0.00 sec)mysql> select * from a2;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 18 |
+----+----------+-----+
1 row in set (0.00 sec) 四、修改表中的数据
### --- 修改表中的数据
Mysql>update a2 set age=21 where id=3;### --- 修改数据表的名称
mysql> alter table a2 rename a1;
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+-------------------+
| Tables_in_atyanqi |
+-------------------+
| a1 |
+-------------------+
1 row in set (0.00 sec)mysql> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | | |
| age | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)### --- 修改数据表的字段类型
mysql> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | | |
| age | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)mysql> alter table a1 modify name char(30);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
| age | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)### --- 修改数据表的字段类型详情
mysql> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
| age | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)mysql> alter table a1 change name username char(50) not null default '';
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 'defaul''' at line 1
mysql> alter table a1 change name username char(50) not null default '';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)### --- 添加字段
mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)~~~ 添加位置默认在末尾
mysql> alter table a1 add time datetime;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
| time | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)~~~ 添加字段到第一列
mysql> alter table a1 add birthday year first;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| birthday | year(4) | YES | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
| sex | char(1) | YES | | NULL | |
| time | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
| time | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)~~~ 添加到指定字段后
mysql> alter table a1 add sex char(1) after age;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
| sex | char(1) | YES | | NULL | |
| time | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec) ### --- 删除字段
mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| birthday | year(4) | YES | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
| sex | char(1) | YES | | NULL | |
| time | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)mysql> alter table a1 drop birthday;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
| sex | char(1) | YES | | NULL | |
| time | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart
——W.S.Landor
分类:
cdv007-network
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通