mysql基础增删改查等命令

yourenk

已于 2023-03-31 14:26:13 修改

阅读量489

点赞数

于 2022-07-01 16:00:12 首次发布

本文链接：https://blog.csdn.net/m0_51777056/article/details/125189394

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

一分钟安装数据库

https://blog.csdn.net/m0_51777056/article/details/125556438?spm=1001.2014.3001.5502

1. 登陆相关

1.1 数据库登陆

mysql -u {$db_name} -p {$db_passwd} -h {$host_ip} -D {$db_port}

参数	用途
-u	数据库帐号
-p	数据库密码
-h	主机地址
-P	指定端口

1.2 修改密码

mysqladmin -uroot -p123456 password 12345678;

1.3 忘记密码

1. 关闭正在运行的MySQL服务。
2. 跳转到mysql\bin目录。
3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
4. 再开一个窗口，输入mysql回车，如果成功，将出现MySQL提示符 >。
5. 连接权限数据库： use mysql; 。
6. 改密码：update user set password=password("{$new_passwd}") where user="{$db_name}"; 。
7. 刷新权限：flush privileges;　。
8. 退出mysql。

2. 增删改查

2.1 创建

//创建数据库
create database {$db_name};

//创建表
create table {$tb_name} ({列名称1} varchar(20), {列名称2} char(1), {列名称3} date, {列名称4} varchar(20), primary key($name));  

varchar 和 cahr 都是设置长度的参数；
primary key($name) 指定主键字符串。

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

mysql> create database newdb;					#创建数据库
Query OK, 1 row affected (0.01 sec)

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

mysql> use newdb;
Database changed

//创建表
mysql> create table testtable (name varchar(20), sex char(1), birth date, birthaddr varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-----------------+
| Tables_in_newdb |
+-----------------+
| testtable       |
+-----------------+
1 row in set (0.00 sec)
mysql> desc testtable;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(20) | YES  |     | NULL    |       |
| sex       | char(1)     | YES  |     | NULL    |       |
| birth     | date        | YES  |     | NULL    |       |
| birthaddr | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

//在创建好的表上设置主键
alter table ois add primary key(uid)

2.2 删除

drop database {$db_name};  				#删除库
drop table {$tb_name};	   				#删除表
delete from {$tb_name} where {$列名称} =‘{$要删除的数据}’;	#删除表数据（删一行）

//举例
mysql> delete from testtable where name='ZhangSan';				#删除一条数据
Query OK, 1 row affected (0.01 sec)

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

mysql> drop table testtable;									#删除表
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> drop database newdb;										#删除库
Query OK, 0 rows affected (0.00 sec)

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

2.3 插入数据&&修改数据

2.3.1 插入数据

//第一种形式无需指定要插入数据的列名，只需提供被插入的值即可
mysql> insert into {$tb_name} values (value1,value2,value3,...);
//第二种形式需要指定列名及被插入的值
mysql> insert into {$tb_name} (column1,column2,column3,...) values (value1,value2,value3,...);

//举例
mysql> insert into testtable (name,sex,birth,birthaddr) values ("ZhangSan","m","2000-01-01","china");
Query OK, 1 row affected (0.01 sec)
mysql> select * from testtable;
+----------+------+------------+-----------+
| name     | sex  | birth      | birthaddr |
+----------+------+------------+-----------+
| ZhangSan | m    | 2000-01-01 | china     |
+----------+------+------------+-----------+
1 row in set (0.00 sec)

2.3.2 修改数据

update {$tb_name} set {$被修改值的列名称} = {$想修改的值} where {$另一个具有唯一性列的名称} = {$与被修改值同行，且在后者列的值};

//举例
mysql> update testtable set sex='w' where name='ZhangSan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from testtable;
+----------+------+------------+-----------+
| name     | sex  | birth      | birthaddr |
+----------+------+------------+-----------+
| ZhangSan | w    | 2000-01-01 | china     |
+----------+------+------------+-----------+
1 row in set (0.00 sec)

2.4 查看表数据

select * from {$tb_name};						#查看表内所有数据
select {$被查看表中的列名称} from {$db_name};		#只查看对应表中想看的一列

//举例
mysql> select * from testtable;
+----------+------+------------+-----------+
| name     | sex  | birth      | birthaddr |
+----------+------+------------+-----------+
| ZhangSan | w    | 2000-01-01 | china     |
+----------+------+------------+-----------+
1 row in set (0.00 sec)

mysql> select name from testtable;
+----------+
| name     |
+----------+
| ZhangSan |
+----------+
1 row in set (0.00 sec)

3. 数据库备份

//格式
mysqldump -uroot -p[密码] [数据库名] > /导出目录/备份文件名.sql
//全库备份
mysqldump -uroot -p123456 --all-databases > /tmp/test.sql

4. 表结构

desc {$表名}																#查看表的字段信息
alter table {$表名} add column {$列名} varchar(30);						#添加一列
alter table {$表名} drop column {$列名};									#删除一列
alter table {$表名} rename to {$新名字};									#修改表名
alter table {$表名} change  {$老列名} {$新列名} {$列属性};					#修改列名
alter table {$表名} modify {$列名} {$列属性};								#修改列属性
show create table {$表名}; 												#查看表的所有信息
alter table {$表名} add constraint {$主键} primary key {$表名($主键字段)};	#添加主键
alter table {$表名} drop primary key;									#删除主键
alter table {$从表名} add constraint{$外键} foreign key {$从表名($外键字段)} references {$主表名($主键字段)};	#添加外键
alter table {$表名} drop foreign key {$外键};							#删除外键

//举例
mysql> desc testtable;												#查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(10)    | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
| age   | varchar(2)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table testtable add column 籍贯 varchar(30);			#新增列
Query OK, 0 rows affected (0.01 sec)
mysql> desc testtable;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | YES  |     | NULL    |       |
| sex    | char(10)    | YES  |     | NULL    |       |
| birth  | date        | YES  |     | NULL    |       |
| age    | varchar(2)  | YES  |     | NULL    |       |
| 籍贯   | varchar(30) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table testtable drop column 籍贯;						#删除列
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc testtable;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(10)    | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
| age   | varchar(2)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table testtable rename to testtable_new ;				#修改表名
Query OK, 0 rows affected (0.00 sec)

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

mysql> alter table testtable_new change birth time int;				#修改列名
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc testtable_new;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(10)    | YES  |     | NULL    |       |
| time  | int         | YES  |     | NULL    |       |
| age   | varchar(2)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table testtable_new modify name varchar(30);			#修改表属性
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc testtable_new;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | char(10)    | YES  |     | NULL    |       |
| time  | date        | YES  |     | NULL    |       |
| age   | varchar(2)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table testtable_new\G							#查看表详细信息
*************************** 1. row ***************************
       Table: testtable_new
Create Table: CREATE TABLE `testtable_new` (
  `name` varchar(30) DEFAULT NULL,
  `sex` char(10) DEFAULT NULL,
  `time` date DEFAULT NULL,
  `age` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

#设置表主键
mysql> alter table testtable_new add constraint name primary key testtable_new(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc testtable_new;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | NO   | PRI | NULL    |       |
| sex   | char(10)    | YES  |     | NULL    |       |
| time  | date        | YES  |     | NULL    |       |
| age   | varchar(2)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table testtable_new drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc testtable_new;					#删除主键
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | NO   |     | NULL    |       |
| sex   | char(10)    | YES  |     | NULL    |       |
| time  | date        | YES  |     | NULL    |       |
| age   | varchar(2)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)