当前位置：首页 > news >正文

初识SQL语句

news 2026/6/11 16:33:09

操作文件夹（数据库）

增

create database db1 charset utf8;mysql> create database db1 charset utf8;
Query OK, 1 row affected (0.00 sec)

查

show create database db1;
# 查看db1数据库
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

show databases;
# 查看所有的数据库
mysql> show database;
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 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

改

alter database db1 charset gbk;mysql> alter database db1 charset gbk;
Query OK, 1 row affected (0.00 sec)

删

drop database db1;mysql> drop database db1;
Query OK, 0 rows affected (0.01 sec)

操作文件（表）

切换文件夹

use db1;
# 切换文件夹
mysql> use db1;
Database changed# 查看当前所在文件夹
mysql> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

增

create table t1(id int,name char);
# 新增文件 t1
mysql> create table t1(id int,name char);
Query OK, 0 rows affected (0.04 sec)# 会新增两个文件t1.frm，t1.ibd
t1.frm  # 存放表结构，标题、id、name等
t1.ibd  # 存放数据

查

show create table t1;mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (`id` int(11) DEFAULT NULL,`name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)# 查看当前文件夹中所有表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)desc t1;
# 另一种表结构查询的方式
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

改

alter table t1 modify name char(6);
# 更改name字段的数据类型
mysql> alter table t1 modify name char(6);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 更改后内容，更改前见-查
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (`id` int(11) DEFAULT NULL,`name` char(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)# 更改name字段名
alter table t1 change name NAME char(7);
mysql> alter table t1 change name NAME char(7);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| NAME  | char(7) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

删

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

操作文件内容（记录）

增

insert t1(id,name) value(1,'cy1'),(2,'cy2'),(3,'cy3');
# 新增数据
mysql> insert t1(id,name) value(1,'cy1'),(2,'cy2'),(3,'cy3');
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

查

select id,name from db1.t1;
# select 列名，列名（*所有列）from 数据库.表名
mysql> select id,name from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | c    |
|    2 | c    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

改

update db1.t1 set name='zy';
# 把name列所有行改成'zy'
mysql>update db1.t1 set name='zy';
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 3mysql> select id,name from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | z    |
|    2 | z    |
|    3 | z    |
+------+------+
3 rows in set (0.00 sec)update db1.t1 set name='cy' where id=2;
# 把id=2的行，name列的内容改成'cy'
mysql> update db1.t1 set name='cy' where id=2;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1mysql> select id,name from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | z    |
|    2 | c    |
|    3 | z    |
+------+------+
3 rows in set (0.00 sec)

删

delete from t1 where id=2;
# 删除id=2的行
mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.00 sec)mysql> select id,name from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | z    |
|    3 | z    |
+------+------+
2 rows in set (0.00 sec)delete from t1;
# 删除所有行
mysql> delete from t1;
Query OK, 2 rows affected (0.01 sec)mysql> select id,name from db1.t1;
Empty set (0.00 sec)