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

简介: 【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、创建索引

有两种方法,CREATEALTER

下面先介绍一下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';

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
7月前
|
SQL 关系型数据库 MySQL
菜鸟之路Day30一一MySQL之DML&DQL
本文介绍了MySQL中DML(数据操作语言)和DQL(数据查询语言)的核心用法。DML主要包括插入(insert)、更新(update)和删除(delete)语句,通过具体示例演示了如何对表数据进行增删改操作。DQL则聚焦于数据查询,涵盖基本查询、条件查询、聚合函数、分组查询、排序查询和分页查询等内容。文章通过丰富的SQL语句实例,帮助读者掌握如何高效查询和操作数据库中的数据,适合初学者学习和实践。
259 12
|
3月前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
储存过程(Stored Procedures) 和 函数(Functions) : 储存过程和函数允许用户编写 SQL 脚本执行复杂任务.
246 14
|
3月前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
以上概述了MySQL 中常见且重要 的几种 SQL 查询及其相关概念 这些知识点对任何希望有效利用 MySQL 进行数据库管理工作者都至关重要
126 15
|
5月前
|
Oracle 关系型数据库 MySQL
比较Oracle和MySQL的语法差异。
在使用Oracle和MySQL时,数据库设计、查询优化、以及日常管理的方式会因为这些差异而有不同的考虑和应用策略。因此,开发人员和数据库管理员必须了解各自数据库的特性和语法差异,以便更有效地利用数据库资源。适应这些语法和功能上的差异对于维护跨数据库平台应用至关重要。
320 0
|
7月前
|
SQL 关系型数据库 MySQL
MySQL探索:详解WITH AS语法的使用。
总的来说,MySQL的 `WITH AS`语法就如同我们路途中的导航设备,能帮助我们更好地组织和简化查询, 增强了我们和数据沟通的能力,使得复杂问题变得可控且更有趣。不论是在森林深处,还是在数据的海洋中,都能找到自己想要的路途和方向。
1218 12
|
12月前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
1064 56
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
|
12月前
|
存储 SQL 关系型数据库
【MySQL基础篇】MySQL约束语法
文章介绍了MySQL中表的约束概念,包括非空、唯一、主键、默认和外键约束,以及如何在创建和修改表时指定这些约束。外键约束用于保持数据的一致性和完整性,文章通过示例展示了添加、删除外键的语法,并讨论了不同的删除/更新行为,如CASCADE和SETNULL。
【MySQL基础篇】MySQL约束语法
|
3月前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
165 3
|
3月前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。

推荐镜像

更多