MySQL基础语法

最新推荐文章于 2024-08-14 22:03:11 发布

大神，起风了

最新推荐文章于 2024-08-14 22:03:11 发布

阅读量559

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql 基础语法

本文链接：https://blog.youkuaiyun.com/Light__1024/article/details/86618663

数据库专栏收录该内容

12 篇文章

订阅专栏

这篇博客详细介绍了MySQL的基础语法，包括库操作和表操作。在库操作中，涵盖了增、删、查、改的规则和实践；在表操作部分，讲解了创建、查看、删除和修改表的方法，以及如何插入、查询、修改和删除数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

库操作（文件夹）

增

 create database 数据库名 charset utf8;

命名规则

数据库命名规则：

可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位

查

### 查看所有文件夹：show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+


### 查看当前所在文件夹： select database();

+------------+
| database() |
+------------+
| db1        |
+------------+


###查看某一文件夹： show create database db1; 

+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
 

### 切换文件夹：use db1;

改

改字符编码： alter database db1 charset utf8;

删除

drop database 数据库名;

表操作（文件）

创建表

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

create table t3(
    -> id int,
    -> name varchar(50),
    -> sex enum('male','female'),
    -> age int(3)
    -> );
    
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
4. 最后一个字段不加逗号，其他都要加

查看


### 所有表 ：show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
+---------------+

### 查看表结构：describe t3; 
可简写为：desc 表名

 desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+

### 查看创建时的表属性：show create table t3;

show create table t3;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `age` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------

### 查看表详细结构，按行显示，可加\G：
show create table t3\G; 


show create table t3\G;
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `age` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

删除表

drop table t1;

复制表

### 复制表结构＋记录 
 create table 新表名 select * from 旧表名;
   
mysql> create table t4 select * from t3;

mysql> desc t4;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+

### 复制表结构，没有内容，只有字段名
create table 新表名 select * from 旧表名 where 1=3; 
create table 新表名 like 旧表名;

mysql> create table t5 select * from t3 where 1=3;


mysql> desc t5;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+

修改

语法：
1. 修改表名
     alter table 表名 
    	 rename 新表名;

mysql> alter table t5
    -> rename t6;
Query OK, 0 rows affected (0.03 sec)

mysql> desc t5;
ERROR 1146 (42S02): Table 'db1.t5' doesn't exist
mysql> desc t6;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+


2. 增加字段
      alter table 表名 
                          add 字段名  数据类型 [完整性约束条件…],
                          add 字段名  数据类型 [完整性约束条件…];
      alter table 表名 
                          add 字段名  数据类型 [完整性约束条件…]  first;
      alter table 表名 
                          add 字段名  数据类型 [完整性约束条件…]  after 字段名;


mysql> alter table t6
    -> add score int;


mysql> desc t6
    -> ;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| score | int(11)               | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+


3. 删除字段
      alter table 表名
                          drop 字段名;
      
 mysql> alter table t6
	-> drop score;


mysql> desc t6;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+



4. 修改字段，modify改字段类型和条件，change可以全部更改

      alter table 表名 
                          modify  字段名 数据类型 [完整性约束条件…];
      alter table 表名 
                         change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      alter table 表名
                          change 旧字段名 新字段名 新数据类型 [完整性约束条件…];

mysql> alter table t6
    -> change age Age int;


mysql> desc t6;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| Age   | int(11)               | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+

示例：
1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;

mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;

操作文件内容

插入数据

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);

4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

mysql> insert into t6 values
    -> (1,'xiaoming','male',17);

mysql> insert into t6(name) values
    -> ('xiaohong') ;

查询数据

mysql> select * from t6;  
+------+----------+------+------+
| id   | name     | sex  | Age  |
+------+----------+------+------+
|    1 | xiaoming | male |   17 |
+------+----------+------+------+


mysql> insert into t6(name) values
   -> ('xiaohong') ;

mysql> select * from t6;
+------+----------+------+------+
| id   | name     | sex  | Age  |
+------+----------+------+------+
|    1 | xiaoming | male |   17 |
| NULL | xiaohong | NULL | NULL |
+------+----------+------+------+

mysql> select name from t6;
+----------+
| name     |
+----------+
| xiaoming |
| xiaohong |
+----------+

修改数据

mysql> update db1.t6 set name='people';

mysql> select * from t6;
+------+--------+------+------+
| id   | name   | sex  | Age  |
+------+--------+------+------+
|    1 | people | male |   17 |
| NULL | people | NULL | NULL |
+------+--------+------+------+

mysql> update db1.t6 set name='xiaoming' where id=1;

mysql> select * from t6;
+------+----------+------+------+
| id   | name     | sex  | Age  |
+------+----------+------+------+
|    1 | xiaoming | male |   17 |
| NULL | people   | NULL | NULL |
+------+----------+------+------+

删除数据

 delete from t6 where id=1;

mysql> select * from t6;
+------+--------+------+------+
| id   | name   | sex  | Age  |
+------+--------+------+------+
| NULL | people | NULL | NULL |
+------+--------+------+------+



mysql> insert into t6 values
    -> (1,'xiaoming','female',18),
    -> (2,'xiaohong','male',17),
    -> (3,'xiaoqiang','female',19);


mysql> select * from t6;

+------+-----------+--------+------+
| id   | name      | sex    | Age  |
+------+-----------+--------+------+
| NULL | people    | NULL   | NULL |
|    1 | xiaoming  | female |   18 |
|    2 | xiaohong  | male   |   17 |
|    3 | xiaoqiang | female |   19 |
+------+-----------+--------+------+


mysql> delete from t6;


mysql> select * from t6;
Empty set