AI 助理
备案控制台
开发者社区 数据库 文章 正文

【MySQL】—— 数据库操作基础 和 常用语法（DDL,DML,DQL,DCL）(下)

2022-05-11 128
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 【MySQL】—— 数据库操作基础 和 常用语法（DDL,DML,DQL,DCL）(下)

三、数据库基本命令

【MySQL】—— 数据库操作基础 和 常用语法（DDL,DML,DQL,DCL）_MySQL

常规命令：

show databases;      #查看数据库中都有哪些库
create database mydb charset=utf8;   #创建mydb库
use mydb;        #选择mydb库
create table students(
    id int primary key aotu,
    name varchar(20) Not Noll,
    score float,
    birth date,
);     #创建学生表
show tables;     #查看当前库下的所有表
desc students;    #查看学生表都有哪些属性
select *  from students;    #查看学生表都有哪些成员
select database();          #查看当前所在的库
select show table 表名;     # 查看该表的属性
drop table students;        #删除学生表，指没有关联的表
drop database mydb;         #删除mydb库
delete from 表名;           #删除该表
delete from user where name='张三';    #删除user表中‘张三’这个字段

alter：针对表的字段

alter table 旧表名 rename to 新表名;    #修改表名
rename 旧表名 to 新表名;
eg:alter table scores rename grades;

alter table 表名 modify 字段名  数据类型;   #修改字段的数据类型
eg:alter table scores modify score int;

alter table 表名 change 旧字段  新字段  数据类型;   #修改字段名
eg:alter table scores change score grade int;

alter table 表名 add 新字段 数据类型;    #添加字段
eg:alter table scores add name varchar(10);

alter table 表名 drop 字段名；   #删除字段
eg:alter table scores drop address;

alter table 表名 drop foreign key 外键约束名；    #删除表的外键约束
eg:alter table scores drop foreign key name;

注意：删除关联表时，先解除关联，再进行删除。

insert插入：针对数据

insert into scores(name,school,grade,teacher) values(jack,beijing,75,wangqiang);   #单行插入
insert into scores(name,school,grade,teacher) values(jack,beijing,75,wangqiang),
                                                    (tom,xian,86,liuhan),
                                                    (alice,shanghai,69,noll）；   #多行插入

update更新

update scores set name=tom;    #将scores表中name全部更新为tom】
update school set num=num+20 where address='China';  #给school表中地址为China的加20

一、DDL–数据定义语言

作用：数据定义语言主要用来定义数据库中的各类对象，包括用户、库、表、视图、索引、触发器、事件、存储过程和函数等。

常见的DDL操作的基本用法如下：

   CREATE USER           #创建用户
   CREATE DATABASE       #创建数据库
   CREATE TABLE          #创建表
   CREATE VIEW           #创建视图
   CREATE INDEX          #创建索引
   CREATE TRIGGER        #创建触发器
   CREATE EVENT          #创建事件
   CREATE PROCEDURE      #创建存储过程
   CREATE FUNCTION       #创建自定义函数

1、创建用户：

CREATE USER 'username'@'[ip/domain/netmask]'

参数解释：
username:表示登陆MySQL实例的用户名

示例：创建一个名称为bingwang，登陆ip为192.168.0.10的用户：

mysql> CREATE USER 'bingwang'@'192.168.0.10';

2、创建数据库

详细用法：

CREATE DATABASE db_name;

示例如下：

#创建一个名称为test_db，字符集为utf8的数据库
mysql> CREATE DATABASE test_db DEFAULT CHARSET UTF8;

3、创建表：

详细用法：

CREATE TABLE table_name;

示例如下：

#创建一个名称为t_test，字符集为utf8，存储引擎为InnoDB，字符校验集为utf8_general_ci的表：
mysql> CREATE TABLE t_test (
           id INT NOT NULL AUTO_INCREMENT,
           name VARCHAR(50),
           PRIMARY KEY(id)
       ) ENGINE = InnoDB DEFAUL CHARSET = UTF8 COLLATE = utf8_general_ci;

4、创建视图：

详细用法：

CREATE VIEW view_name as <SELECT phrase>;

示例如下：

#创建一个视图t_view，用来查询t_test中的ID为1或者2的数据：
mysql> CREATE VIEW test_view AS SELECT * FROM t_test WHERE id IN (1,2);

查看创建视图的过程：

mysql> SHOW CREATE VIEW test_view;

5、创建索引

有两种方法，CREATE 和 ALTER

下面先介绍一下CREATE：

详细用法：

CREATE [UNIQUE] INDEX index_name ON table_name(field[num]) <OPTIONS>;

参数解释：

UNIQUE:表示创建的索引类型为唯一索引，如果创建的为一般索引可以忽略该选项
table_name:表名称

field:表中的某个字段。num为可选参数，如果field为字符创类型，表示给该字段的前num个字符创建索引

OPTIONS:表示可选选项，可以指定索引使用的算法，比如：USING BTREE。不指定默认为BTREE;

示例如下：
（1）给t_test表中的name字段添加一个唯一索引，使用BTREE作为其索引算法：

mysql> CREATE UNIQUE INDEX name_ind ON t_test(name) USING BTREE;
mysql> SHOW [INDEX/INDEXES] FROM t_test;  #查看t_test表中的索引，[INDEX/INDEXES]两个关键字都可以

（2）给t_test表中的name字段的前5个字符创建一般索引，使用BTREE作为其索引算法：

mysql> CREATE INDEX name_index ON t_test(name(5));
关于索引的更多用法及优化在后面的文章中会详细讲解。

6、创建触发器：
详细用法：

CREATE TRIGGER trigger_name trigger_time trigger_event FOR EACH ROW 
BEGIN 
  trigger_stmt 
END;

示例：创建触发器内容稍多，此处先稍微提一下，后面专门章节介绍；

7、创建存储过程：
详细用法：

CREATE PROCEDURE procedure_name([proc_parameter[,...]])
BEGIN
…存储过程体
END

8、创建自定义函数：
详细用法：

CREATE FUNCTION function_name([func_parameter[,...]])
RETURNS type
BEGIN
...函数体
END

至此，简单的DDL操作介绍完成。

二、DML–数据操纵语言：

作用：用来操作数据库中的表对象，主要包括的操作有：INSERT，UPDATE，DELETE

常见的DML的基本操作方法如下：

#给表中添加数据
INSERT INTO ...
#修改表中的数据
UPDATE table_name SET ...
#删除表中的数据
DELETE FROM table_name WHERE <condition>;

注：:表示DML操作时的条件

1、向表中插入数据：
详细用法：

mysql> INSERT INTO table_name(field1,field2,[,...]) values(value1,value2),(value3,value4),...;

示例：向学生表中插入一条数据，name:‘xiaohong’, age:24, gender:‘M’ ，如下：

(1)创建表：

 mysql> CREATE TABLE student(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL DEFAULT '',
        age TINYINT,
        gender ENUM('F','M')
     ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;

(2)插入数据：

mysql> INSERT INTO student(name,age,gender) VALUES('xiaohong',24,'M');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
|  1 | xiaohong |   24 | M      |
+----+----------+------+--------+
1 row in set (0.37 sec)

注：主键如果自动递增，插入时可不用指定；

2、修改表中的数据：

详细用法：

UPDATE table_name SET field1 = value1, field2 = value2, … , WHERE ;

示例：将student表中id为1的记录中的name值修改为:“xiaohua”，如下：

mysql> UPDATE STUDENT SET name = 'xiaohua' WHERE id = 1;
Query OK, 1 row affected (0.67 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM student;
+----+---------+------+--------+
| id | name    | age  | gender |
+----+---------+------+--------+
|  1 | xiaohua |   24 | M      |
+----+---------+------+--------+
1 row in set (0.00 sec)

3、删除表中的数据：

详细用法：

mysql> DELETE FROM table_name WHERE <condition>;

示例：删除student表中id为1的记录，如下：

mysql> DELETE FROM student WHERE id = 1;
Query OK, 1 row affected (0.37 sec)
mysql> SELECT * FROM student;
Empty set (0.00 sec)

注意：注意！注意！！再注意！！！，

该操作非常危险，命令行中操作时，需要万分注意。可以使用登录时加-U参数的方式，防止忘加条件而删除所有数据，加了-U参数之后，如果不加条件，会被阻止，执行结果如下：

mysql> DELETE FROM student;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KE

至此，DML操作介绍完毕。

三、DQL–数据查询语言

作用：主要用来查看表中的数据，也是平时使用最多的操作，主要命令为：SELECT

基本用法如下：

mysql> SELECT fields FROM table_name WHERE <condition>;

注意事项：

fields:表示要查询的字段列表，可以使用代替，但是在程序中最好别写，因为使用*一方面会降低SQL的查询效率，查询到一些用不到的字段；另一方面，使用一些ORM框架时，如果数据库中字段有变动，可能会立刻导致程序报错。

1、简单不加条件的单表查询：

用法：

mysql> SELECT * FROM table;

2、单表中的条件查询：
常见的条件：>，>=，<，<= ，=，<>，!=，IN，NOT IN，LIKE，NOT LIKE，REGEXP

示例：

#查询年龄大于23的记录
mysql> SELECT * FROM student WHERE age > 23;
#查询年龄大于等于24的记录，和上面age>23结果相同
mysql> SELECT * FROM student WHERE age >= 24;
#查询年龄小于24的记录
mysql> SELECT * FROM student WHERE age < 24;
#查询年龄小于等于24的记录
mysql> SELECT * FROM student WHERE age <= 24;
#查询姓名等于xiaohong的记录
mysql> SELECT * FROM student WHERE name = 'xiaohong';
#查询姓名不等于xiaohong的记录
mysql> SELECT * FROM student WHERE name <> 'xiaohong'; 
#查询姓名不等于xiaohong的记录
mysql> SELECT * FROM student WHERE name != 'xiaohong';
#查询姓名为xiaohong或者xiaohui的记录
mysql> SELECT * FROM student WHERE name in ('xiaohong','xiaohui');
#查询姓名不是xiaohong和xiaohui的记录等价于：where name != xiaohong and name != xiaohui
mysql> SELECT * FROM student WHERE name not in ('xiaohong','xiaohui');
#查询姓名以xiao开头的记录
mysql> SELECT * FROM student WHERE name like 'xiao%';
#查询姓名以xiaohon开头的记录，后面模糊匹配一位，如：xiaohong，xiaohoni
mysql> SELECT * FROM student WHERE name like 'xiaohon_';
#查询姓名中包含ao字符创的记录
mysql> SELECT * FROM student WHERE name like '%ao%';
#查询以hong结尾的记录
mysql> SELECT * FROM student WHERE name not like '%hong';
#使用正则表达式查询姓名以xiao开头的记录
mysql> SELECT * FROM student WHERE name REGEXP('^xiao');
#使用正则表达式查询姓名以hong结尾的记录
mysql> SELECT * FROM student WHERE name REGEXP('hong$');

注意：

（1）当某个字段上有索引时，使用上述的反向查询或者前模糊查询，如：<>，!=，NOT LIKE，NOT IN，LIKE “%test”，将会不走索引；
（2）查询中的潜在问题：如果某个字段在创建表结构的时候未设置非空，则使用WHERE name!="BING"的时候，将不会包含name为NULL的记录；

示例：查询student表中年龄大于"xiaohong"年龄的记录的数量：

mysql> SELECT COUNT(*) FROM student WHERE age > (SELECT age FROM student WHERE name = 'xiaohong');
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.46 sec)

3、分页查询：

用法：

mysql> SELECT * FROM table_name LIMIT start,num;

参数解释：
start:开始位置，默认从0开始；
num:偏移量，即：从开始位置向后查询的数据条数；
示例：查询test表中，第二页的数据，每页显示10条，如下：

mysql> SELECT * FROM student LIMIT 1,10;

4、使用ORDER BY对查询结果进行排序：

用法：

SELECT * FROM table_name <where condition> ORDER BY <field> ASC/DESC;

示例：从student表中查询出所有年龄大于20的学生记录，并且按照年龄age倒序排列，如下：

SELECT * FROM student WHERE age > 20 ORDER BY age DESC;

注意：如果在排序时ORDER BY 之后没有添加DESC和ASC关键字，默认按照ASC升序排列；

5、使用GROUP BY对查询结果集进行分组

基本用法：

mysql> SELECT res FROM table_name <where condition> GROUP BY <field>;

示例：查询student表中男生和女生的数量：

mysql> SELECT gender,COUNT(*) FROM student GROUP BY gender;

6、使用GROUP BY之后，在使用HAVING完成分组之后的条件查询

基本用法：

SELECT res FROM table_name <where condition> GROUP BY <field> <having condition>;

示例：查询student_course表中有3门成绩大于等于80的学生学号
（1）创建测试表结构：

mysql> CREATE TABLE student_course(
    sno INT(11) NOT NULL,
    cno INT(11) NOT NULL,
    grade SMALLINT NOT NULL DEFAULT 0
)ENGINE = InnoDB DEFAULT CHARSET = UTF8;

2）插入测试数据：

INSERT INTO student_course(sno,cno,grade) VALUES(1,100,79);
INSERT INTO student_course(sno,cno,grade) VALUES(1,101,89);
INSERT INTO student_course(sno,cno,grade) VALUES(1,102,87);
INSERT INTO student_course(sno,cno,grade) VALUES(1,103,99);
INSERT INTO student_course(sno,cno,grade) VALUES(2,100,90);
INSERT INTO student_course(sno,cno,grade) VALUES(2,101,80);
INSERT INTO student_course(sno,cno,grade) VALUES(2,102,77);
INSERT INTO student_course(sno,cno,grade) VALUES(2,103,79);
INSERT INTO student_course(sno,cno,grade) VALUES(3,100,89);
INSERT INTO student_course(sno,cno,grade) VALUES(3,101,90);
INSERT INTO student_course(sno,cno,grade) VALUES(3,102,83);
INSERT INTO student_course(sno,cno,grade) VALUES(3,103,91);

（3）查询：

mysql> SELECT sno,SUM(CASE WHEN grade > 80 THEN 1 ELSE 0 END) num FROM student_course GROUP BY sno HAVING num >= 3;     
+-----+------+
| sno | num  |
+-----+------+
|   1 |    3 |
|   3 |    4 |
+-----+------+
2 rows in set (0.45 sec)

四、DCL–数据控制语言

作用：用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果。

1、GRANT授予用户权限：

基本用法：

mysql> GRANT priv_type ON <object_type> TO user <WITH {GRANT OPTION | resource_option} ...>;

示例：给用户jerry授予对test_db数据库的增删改查权限，允许该用户从IP为’192.168.0.10’的网络登录

（1）方法一：

mysql> GRANT INSERT,SELECT,UPDATE,DELETE ON test_db.* TO 'jerry'@'192.168.0.10' IDENTIFIED BY 'password' WITH GRANT

（2）方法二：

mysql> CREATE USER 'jerry'@'192.168.0.10' IDENTIFIED BY 'password';
mysql> GRANT INSERT,SELECT,UPDATE,DELETE ON test_db.* TO 'jerry'@'192.168.0.10';

2、REVOKE收回用户权限：

基本用法：

mysql> REVOKE priv_type ON <object_type> FROM 'jerry'@'192.168.0.10';

示例：收回用户对test_db库的删除权限：

mysql> REVOKE DELETE ON test_db.* FROM 'jerry'@'192.168.0.10';

3、查看给某个用户所授予的权限：
基本用法：

mysql> SHOW GRANTS FOR user;

示例：查询给’jerry’@'192.168.0.10’所授予的所有权限：

mysql> SHOW GRANTS FOR 'jerry'@'192.168.0.10';

4、查询可授予的所有权限，使用技巧：

（1）首先将某个库(如：test_db)的所有权限授予给用户’jerry’@‘localhost’

mysql> GRANT ALL ON test_db.* TO 'jerry'@'localhost' IDENTIFIED BY 'jerry';

（2）收回某个权限，如：查询权限

mysql> REVOKE SELECT ON test_db.* FROM 'jerry'@'localhost';

（3）查看剩余权限，就可以查到除了查询权限之外的权限，再加上查询权限即可授予的所有权限

 mysql> SHOW GRANTS FOR 'jerry'@'localhost';
文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
算法
数据库
存储
关键词：
云数据库 RDS MySQL 版数据库
mysql数据库
云数据库 RDS MySQL 版dql
云数据库 RDS MySQL 版ddl dml
云数据库 RDS MySQL 版ddl
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
Perceus
目录
相关文章
肾透侧视攻城狮
|
20天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列（5）作者——LJS[含MySQL DQL基本查询:select；简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
肾透侧视攻城狮
62 5
肾透侧视攻城狮
|
20天前
|
SQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列（2）作者——LJS[含MySQL登录；DDL；DML；举例说明；编码等详解步骤及常见报错问题对应的解决方法]
MySQL登录；DDL；DML;举例说明；编码等详解步骤及常见报错问题对应的解决方法
肾透侧视攻城狮
22 2
vohelon
|
1月前
|
关系型数据库 MySQL 数据库
MySQL的语法涵盖了数据定义、数据操作、数据查询和数据控制等多个方面
MySQL的语法涵盖了数据定义、数据操作、数据查询和数据控制等多个方面
vohelon
59 5
vohelon
|
1月前
|
关系型数据库 MySQL 数据库
MySQL的语法知识
MySQL的语法知识
vohelon
18 4
小Tomkk
|
1月前
|
SQL 存储 关系型数据库
mysql SQL必知语法
本文详细介绍了MySQLSQL的基本语法，包括SELECT、FROM、WHERE、GROUPBY、HAVING、ORDERBY等关键字的使用，以及数据库操作如创建、删除表，数据类型，插入、查询、过滤、排序、连接和汇总数据的方法。通过学习这些内容，读者将能更好地管理和操
小Tomkk
14 0
m1a6
|
1月前
|
SQL 关系型数据库 MySQL
Mysql(2)—SQL语法详解
SQL（Structured Query Language，结构化查询语言）是一种用于管理关系型数据库的标准编程语言。它主要用于数据的查询、插入、更新和删除等操作。SQL最初在1970年代由IBM的研究人员开发，旨在处理关系数据模型。
m1a6
29 0
yuanzhengme
|
1月前
|
自然语言处理 关系型数据库 MySQL
MySQL数据库使用Match语法需要安装什么插件吗？
【10月更文挑战第1天】MySQL数据库使用Match语法需要安装什么插件吗？
yuanzhengme
64 0
椰椰椰耶
|
1月前
|
关系型数据库 MySQL Java
【MySQL】基础语法大全
【MySQL】基础语法大全
椰椰椰耶
53 0
糜终
|
1月前
|
存储 关系型数据库 MySQL
MySQL 字符字段长度设置详解：语法、注意事项和示例
MySQL 字符字段长度设置详解：语法、注意事项和示例
糜终
194 0
路卿老师
|
10天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法，包括安装并连接MySQL服务器、选择数据库、执行SQL语句（如插入、更新、删除和查询），以及将结果集返回到数组。通过具体示例代码，详细介绍了每一步的操作流程，帮助读者快速入门PHP与MySQL的交互。
路卿老师
25 1

热门文章

最新文章

  • 1
    SQLServer数据库日志清理 清除sqlserver2005日志 - sk
  • 2
    DbVisualizer 数据库连接工具：添加数据库驱动方法。dbvis搜索不到驱动文件夹下的驱动解决方法
  • 3
    数据库被注入daxia123原因及解决办法
  • 4
    ChatGPT 引爆向量数据库赛道
  • 5
    sql语句查询数据库表结构信息
  • 6
    SQLite数据库版本升级的管理实现
  • 7
    创建逻辑stand by数据库
  • 8
    备份,还原,压缩数据库
  • 9
    《你不可不知的关系数据库理论》——第1章 数据库基本概念
  • 10
    Android 数据库ORM框架GreenDao学习心得及使用总结<二>
  • 1
    【JAVA进阶篇教学】第八篇：Java链接MySql数据库异常
    239
  • 2
    【JAVA基础篇教学】第十六篇：Java连接和操作MySQL数据库
    311
  • 3
    java mysql druid mybatis-plus里使用多表删除出错的一种处理方式
    94
  • 4
    MySQL免编译安装和用户管理（转）
    32
  • 5
    MySQL 计算时间差分钟
    109
  • 6
    MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀
    127
  • 7
    MySQL是怎样存储数据的？
    68
  • 8
    MySQL高可用架构探秘：主从复制剖析、切换策略、延迟优化与架构选型
    101
  • 9
    “时光机”与“多维视界”⭐️MySQL中原子性与隔离性的科幻大片
    69
  • 10
    MySQL持久化不为人知的一面⭐️卡顿现象的根源与对策
    70

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云企业级自治数据库 RDS 详解
  • 阿里云MySQL云数据库产品体系介绍

    • 相关实验场景

    更多
  • 自建MySQL 5.7数据库备份上云
  • RDS MySQL高并发访问下的系统稳定性
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    阿里云OSS设置跨域访问