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

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

2022-05-11 140
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 【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 版数据库语法
数据库语法
云数据库 RDS MySQL 版语法
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
Perceus
目录
相关文章
蓝染-惣右介
|
5天前
|
存储 SQL 关系型数据库
【MySQL基础篇】MySQL约束语法
文章介绍了MySQL中表的约束概念，包括非空、唯一、主键、默认和外键约束，以及如何在创建和修改表时指定这些约束。外键约束用于保持数据的一致性和完整性，文章通过示例展示了添加、删除外键的语法，并讨论了不同的删除/更新行为，如CASCADE和SETNULL。
蓝染-惣右介
38 2
【MySQL基础篇】MySQL约束语法
蓝染-惣右介
|
5天前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法，包括数据定义（DDL）、数据操作（DML）、数据查询（DQL）和数据控制（DCL）四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作，以及通过图形化工具DataGrip进行数据库管理和查询。此外，还讲解了数据的增、删、改、查操作，以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
蓝染-惣右介
16 1
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
vohelon
|
2月前
|
关系型数据库 MySQL 数据库
MySQL的语法涵盖了数据定义、数据操作、数据查询和数据控制等多个方面
MySQL的语法涵盖了数据定义、数据操作、数据查询和数据控制等多个方面
vohelon
69 5
vohelon
|
2月前
|
关系型数据库 MySQL 数据库
MySQL的语法知识
MySQL的语法知识
vohelon
20 4
vohelon
|
2月前
|
SQL 关系型数据库 MySQL
MySQL语法
MySQL语法
vohelon
87 3
小Tomkk
|
2月前
|
SQL 存储 关系型数据库
mysql SQL必知语法
本文详细介绍了MySQLSQL的基本语法，包括SELECT、FROM、WHERE、GROUPBY、HAVING、ORDERBY等关键字的使用，以及数据库操作如创建、删除表，数据类型，插入、查询、过滤、排序、连接和汇总数据的方法。通过学习这些内容，读者将能更好地管理和操
小Tomkk
41 0
m1a6
|
2月前
|
SQL 关系型数据库 MySQL
Mysql(2)—SQL语法详解
SQL（Structured Query Language，结构化查询语言）是一种用于管理关系型数据库的标准编程语言。它主要用于数据的查询、插入、更新和删除等操作。SQL最初在1970年代由IBM的研究人员开发，旨在处理关系数据模型。
m1a6
43 0
yuanzhengme
|
2月前
|
自然语言处理 关系型数据库 MySQL
MySQL数据库使用Match语法需要安装什么插件吗？
【10月更文挑战第1天】MySQL数据库使用Match语法需要安装什么插件吗？
yuanzhengme
81 0
椰椰椰耶
|
2月前
|
关系型数据库 MySQL Java
【MySQL】基础语法大全
【MySQL】基础语法大全
椰椰椰耶
98 0
糜终
|
2月前
|
存储 关系型数据库 MySQL
MySQL 字符字段长度设置详解：语法、注意事项和示例
MySQL 字符字段长度设置详解：语法、注意事项和示例
糜终
283 0

热门文章

最新文章

  • 1
    MySQL常用函数：IF、SUM等用法
  • 2
    从新手到高手：彻底掌握MySQL表死锁
  • 3
    MySQL事务隔离级别及默认隔离级别的设置
  • 4
    Liunx部署java项目Tomcat、Redis、Mysql教程
  • 5
    Mysql索引：深入理解InnoDb聚集索引与MyisAm非聚集索引
  • 6
    MySQL的插件式认证到底是干什么的？
  • 7
    docker高级篇（大厂进阶）：安装mysql主从复制
  • 8
    Servlet+MySQL增删改查 原文出自[易百教程] 转载请保留原文链接: https://www.yiibai.com/geek/1391
  • 9
    深入解析MySQL的EXPLAIN：指标详解与索引优化
  • 10
    MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
  • 1
    云原生一体化数据库技术是一个具有潜力的领域
    118
  • 2
    构建高性能数据库的秘诀
    99
  • 3
    新一代数据库技术：融合AI的智能数据管理系统
    336
  • 4
    新一代数据库技术：融合人工智能与分布式系统的未来前景
    360
  • 5
    使用 Python 访问数据库的基本方法
    73
  • 6
    数据库解决方案
    107
  • 7
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    170
  • 8
    服务器Linux系统配置mysql数据库主从自动备份
    221
  • 9
    SQLite数据库介绍以及安装
    110
  • 10
    sprinboot+vue集成neo4j图数据库
    185

    • 相关课程

    更多
  • 数据库的前世今生
  • 数据库核心概念
  • 从传统数据库到云数据库演进
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础
  • 高校精品课-西安交通大学 -数据库理论与技术

    • 相关电子书

    更多
  • DTCC 2022大会集锦《云原生一站式数据库技术与实践》
  • 阿里云瑶池数据库精要2022版
  • 2022 DTCC-阿里云一站式数据库上云最佳实践

    • 相关实验场景

    更多
  • 使用DAS实现数据库SQL优化
  • 自建数据库迁移到云数据库
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • 快速体验PolarDB开源数据库

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    手把手教你白嫖阿里云服务器(免费领服务器)