【数据库】-2 mysql基础语句（上）
文章目录
- 1、SQL语句
- 1.1 SQL语句的简要介绍
- 2、SQL语句的四种基本类型
- 2.1 DDL - 数据库定义语言
- 管理对象
- 修改表的结构
- 2.2 DML - 数据库操纵语言
- 2.3 DQL - 数据库查询语言
- 2.4 DCL - 数据库控制语言
1、SQL语句
1.1 SQL语句的简要介绍
SQL（Structured Query Language，结构化查询语言）是用于管理关系型数据库的标准语言，广泛应用于数据存储、查询、更新和管理等场景。它通过简洁的语法实现对数据库的操作，主要功能包括定义数据库结构（如创建、修改、删除表等）、操纵数据（插入、查询、更新、删除记录）、控制数据访问权限（设置用户权限、管理事务等）。SQL 具有标准化程度高、易于学习、跨平台性强等特点，无论小型数据库（如 SQLite）还是大型企业级数据库（如 Oracle、MySQL、SQL Server），均支持 SQL 语言，使其成为数据领域从业者必备的核心技能之一，也是数据查询、分析和开发的基础工具。
注：SQL语言不是很区分大小写，但是我们还是建议使用大写
2、SQL语句的四种基本类型
前置条件：正常登录数据库
2.1 DDL - 数据库定义语言
- DDL :用于管理数据库的对象、数据库、表、索引等
管理对象
- CREATE DATABASE 库名 ;
mysql> create database bbs;
Query OK, 1 row affected (0.00 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bbs |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)mysql> - USE 库名;
mysql> USE mysql;
Database changed
mysql> SHOW tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |- CREATE TABLE 表名 （字段1 数据类型 ，字段2 数据类型 ， primary key (主键字段)）;
mysql> create table ailun (id int, name varchar(10)NOT NULL ,sex char(1),score decimal(5,2) ,PRIMARY KEY (id));
Query OK, 0 rows affected (0.51 sec)mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| ailun |
+---------------+
1 row in set (0.00 sec)mysql>
- show database; 作用：显示数据库，初始只显示系统中的基础数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)mysql> - show tables from 库名；
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 |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
| io_global_by_file_by_bytes |
| io_global_by_file_by_latency |
| io_global_by_wait_by_bytes |
| io_global_by_wait_by_latency |
| latest_file_io |
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_bytes |- desc 表名；
mysql> use mysql;
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |....mysql> desc table user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> #表的结构
第一段 ：字段名
第二段 ：数据类型
第三段 ：表示字段是否可以为空
第四段 ：表示这个字段是否可以有键
第五段 ：表示是否有默认值
第六段 : 表示这个字段是否有其他的属性
- show create table 表名；#以创建表的命令的形式显示命令
mysql> show create table user;...| user | CREATE TABLE `user` (`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',`User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',`Select_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',`Insert_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',`Update_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',`Delete_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',`Create_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',`Drop_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',`Reload_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',`Shutdown_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',`Process_priv` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
- drop table 表名；
mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| ailun |
+---------------+
1 row in set (0.00 sec)mysql> drop table ailun;
Query OK, 0 rows affected (0.02 sec)mysql> show tables;
Empty set (0.00 sec)mysql>
- drop database 库名；
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bbs |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)mysql> drop database bbs;
Query OK, 0 rows affected (0.02 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)mysql>
修改表的结构
- alter table 旧表名 rename 新表名；
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)mysql> alter table coc rename abc;
Query OK, 0 rows affected (0.02 sec)mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| abc |
| classes |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)mysql> - alter table 表名 add 新字段 数据类型 [属性]–（约束属性）；
mysql> alter table abc add length smallint;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show create table abc;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc | CREATE TABLE `abc` (`ID` int unsigned NOT NULL AUTO_INCREMENT,`ClassID` tinyint unsigned NOT NULL,`CourseID` smallint unsigned DEFAULT NULL,`length` smallint DEFAULT NULL,PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)mysql> - alter table 表名 change 旧字段 新字段 数据类型 约束属性；
mysql> alter table abc change ID UID int NOT NULL;
Query OK, 14 rows affected (0.03 sec)
Records: 14 Duplicates: 0 Warnings: 0mysql> show create table abc;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc | CREATE TABLE `abc` (`UID` int NOT NULL,`ClassID` tinyint unsigned NOT NULL,`CourseID` smallint unsigned DEFAULT NULL,`length` smallint DEFAULT NULL,PRIMARY KEY (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> - alter table 表名 drop 字段；
mysql> alter table abc drop length;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show create table abc;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc | CREATE TABLE `abc` (`UID` int NOT NULL,`ClassID` tinyint unsigned NOT NULL,`CourseID` smallint unsigned DEFAULT NULL,PRIMARY KEY (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> - alter table 表名 add primary key (字段)；
mysql> alter table abc add primary key (UID);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc abc;
+----------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| UID | int | NO | PRI | NULL | |
| ClassID | tinyint unsigned | NO | | NULL | |
| CourseID | smallint unsigned | YES | | NULL | |
+----------+-------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> - alter table 表名 drop primary key；
mysql> desc abc;
+----------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| UID | int | NO | PRI | NULL | |
| ClassID | tinyint unsigned | NO | | NULL | |
| CourseID | smallint unsigned | YES | | NULL | |
+----------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> alter table abc drop primary key;
Query OK, 14 rows affected (0.05 sec)
Records: 14 Duplicates: 0 Warnings: 0mysql> desc abc;
+----------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| UID | int | NO | | NULL | |
| ClassID | tinyint unsigned | NO | | NULL | |
| CourseID | smallint unsigned | YES | | NULL | |
+----------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> - alter table 表名 drop key 键名； (除了主键以外，其他无需体现键的类型，直接删除)
mysql> alter table ailun change name username varchar(50) unique key;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc ailun;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| username | varchar(50) | YES | UNI | NULL | |
| sex | char(1) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)#删除操作
mysql> alter table ailun drop key `username`;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> decs ailun;
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 'decs ailun' at line 1
mysql> desc ailun;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| username | varchar(50) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> - show create table; --可以查看键名
mysql> show create table ailun;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ailun | CREATE TABLE `ailun` (`id` int NOT NULL,`username` varchar(50) DEFAULT NULL,`sex` char(1) DEFAULT NULL,`score` decimal(5,2) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)2.2 DML - 数据库操纵语言
现有一张表如下
mysql> show tables;
+---------------+
| Tables_in_bbs |
+---------------+
| ailun |
+---------------+
1 row in set (0.00 sec)mysql> desc ailun-> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| sex | char(1) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> -
DML ： 用于管理表中的数据结构
-
insert into 表名 （字段1，字段2，…）values (字段1的值，字段2的值)；
mysql> insert into ailun (id,name,sex,score) values(1,'gfy','男','60');
Query OK, 1 row affected (0.00 sec)mysql> select * from ailun;
+----+------+------+-------+
| id | name | sex | score |
+----+------+------+-------+
| 1 | gfy | 男 | 60.00 |
+----+------+------+-------+
1 row in set (0.00 sec)- insert into 表名 values (所有字段的值)；
mysql> insert into ailun values(2,'abc','nan',80);
ERROR 1406 (22001): Data too long for column 'sex' at row 1
mysql> insert into ailun values(2,'abc','女',80);
Query OK, 1 row affected (0.01 sec)mysql> select * from ailun;
+----+------+------+-------+
| id | name | sex | score |
+----+------+------+-------+
| 1 | gfy | 男 | 60.00 |
| 2 | abc | 女 | 80.00 |
+----+------+------+-------+
2 rows in set (0.00 sec)mysql>
- update 表名 set 字段1=值1 字段2=值2 where 条件表达式；
mysql> update ailun set score=90 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from ailun;
+----+------+------+-------+
| id | name | sex | score |
+----+------+------+-------+
| 1 | gfy | 男 | 90.00 |
| 2 | abc | 女 | 80.00 |
+----+------+------+-------+
2 rows in set (0.00 sec)mysql> #同时更改多条记录
mysql> update ailun set score=90, name='qda' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from ailun;
+----+------+------+-------+
| id | name | sex | score |
+----+------+------+-------+
| 1 | qda | 男 | 90.00 |
| 2 | abc | 女 | 80.00 |
+----+------+------+-------+
2 rows in set (0.00 sec)mysql>
- delete from 表名 where 条件表达式；
mysql> delete from ailun where id=2;
Query OK, 1 row affected (0.01 sec)mysql> select * from ailun;
+----+------+------+-------+
| id | name | sex | score |
+----+------+------+-------+
| 1 | qda | 男 | 90.00 |
+----+------+------+-------+
1 row in set (0.00 sec)mysql>
2.3 DQL - 数据库查询语言
#示例数据库
mysql> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)mysql>
-
DQL ： 查询数据表中的数据记录
-
select 字段 from 表名 （where 条件表达式）；
mysql> desc table scores\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: scorespartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 15filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)ERROR:
No query specifiedmysql> mysql> select * from scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 11 | 6 | 1 | 96 |
| 12 | 7 | 1 | 86 |
| 13 | 7 | 7 | 83 |
| 14 | 8 | 4 | 57 |
| 15 | 8 | 3 | 93 |
+----+-------+----------+-------+
15 rows in set (0.00 sec)mysql> - select 字段1，字段2 from 表名 （where 条件表达式）；
mysql> select ID,Score from scores;
+----+-------+
| ID | Score |
+----+-------+
| 1 | 77 |
| 2 | 93 |
| 3 | 47 |
| 4 | 97 |
| 5 | 88 |
| 6 | 75 |
| 7 | 71 |
| 8 | 89 |
| 9 | 39 |
| 10 | 63 |
| 11 | 96 |
| 12 | 86 |
| 13 | 83 |
| 14 | 57 |
| 15 | 93 |
+----+-------+
15 rows in set (0.00 sec)mysql>
- select 字段 from 表名 limit N；查看表的前n行记录
mysql> select ID,Score from scores limit 5;
+----+-------+
| ID | Score |
+----+-------+
| 1 | 77 |
| 2 | 93 |
| 3 | 47 |
| 4 | 97 |
| 5 | 88 |
+----+-------+
5 rows in set (0.00 sec)mysql> - select 字段 from 表名 limit N，M；查看表的第N行之后的连续M行的记录（不包含第N行）
mysql> select ID,Score from scores limit 5,8;
+----+-------+
| ID | Score |
+----+-------+
| 6 | 75 |
| 7 | 71 |
| 8 | 89 |
| 9 | 39 |
| 10 | 63 |
| 11 | 96 |
| 12 | 86 |
| 13 | 83 |
+----+-------+
8 rows in set (0.00 sec)mysql>
2.4 DCL - 数据库控制语言
- DCL：用于管理用户和授权