[MySQL 之一] SQL 基础

（1）SQL 分类

主要分为三个类别：

  • DDL（Data Definition Language）语句：数据定义语言，定义了不同的数据段、数据库、表、列、索引等数据库对象，常用的语句关键字主要包括 create、drop、alter 等。
  • DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字包括 insert、delete、update 和 select 等。
  • DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。常用的语句关键字主要包括 grant、revoke 等。


（2）DDL

  连接 MySQL 服务器

mysql -h 主机地址 -P 端口号 -u 用户名 -p 密码

  主机地址参数不指定时默认为 localhost，端口号默认为 3306。

① 创建数据库

CREATE DATABASE dbname;

创建数据库

如果数据库已存在，重复创建会报错

通过以下命令查看系统中存在的所有数据库

show databases

可以看到前面创建的数据库 test1

有几个 mysql 系统数据库：

  • information_schema：主要存储系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息等。每个用户都可以查看这个数据库，但根据权限的不同看到的内容不同。
  • performance_schema：MySQL 5.5 引入的数据库，用于存储系统性能相关的动态参数表。
  • sys：MySQL 5.7 引入的系统库，本身不记录系统数据，基于 information_schema 和 performance_schema 之上，封装了一层更加易于调优和诊断的系统视图。
  • mysql：存储系统的用户权限信息。

选择要操作的数据库，使用以下命令：

use dbname

选择刚创建的数据库 test1

查看数据库中的数据表，使用以下命令：

show tables;

查看数据库 test1 中存在哪些数据表

查看系统数据库 mysql 中存在哪些数据表

② 删除数据库

通过以下命令删除数据库：

drop database dbname;

删除数据库 test1

PS. 删除数据库的操作会把库中所有的表和数据都删除，所以要谨慎操作，必要做好数据备份。

③ 创建表

创建一张数据表的语法如下：

CREATE TABLE tablename (
    column_name_1 column_type_1 constraints,
    column_name_2 column_type_2 constraints,
    ...
    column_name_n column_type_n constraints
)

column_name 是列的名字，column_type 是列的数据类型，constraints 是列的约束条件（包括是否允许为空、是否自增、默认值、断言检查等）。

【实践】

  创建一个雇员表 emp，表中包括 ename（姓名）、hiredate（雇佣日期）、sal（薪水）3个字段，字段类型分别为 varchar(10)、date、int(2)。

通过以下命令可以查看表的结构：

DESC tablename;

还可以通过以下命令查看更加全面的表定义信息：

show create table tablename \G;   
// \G 选项的含义是使得记录能够按照字段竖向排列，以便更好地显示内容较长的记录。

如图所示，输出的是可以直接执行的建表的SQL：

④ 删除表

通过以下命令删除表：

DROP TABLE tablename

删除数据表 emp

⑤ 修改表

a. 修改表字段类型

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

修改 emp 表的 ename 字段类型为 varchar(20)

b. 增加表字段

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFRER col_name]

为 emp 表增加一个 age 字段，类型为 int(3)

c. 删除表字段

ALTER TABLE tablename drop [COLUMN] col_name

删除 emp 表的 age 字段

d. 字段改名

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name]

修改 emp 表的 age 字段名为 age1，同时将字段类型改为 int(4)

PS. change 和 modify 都可以修改表的定义，不同的是 change 后面需要写两次列名，不方便。但是 change 的优点是可以修改列名称，modify 则不能。

e. 修改字段排列顺序

使用 first|after column_name 选项。

为 emp 表新增 birth date 字段并加在 ename 之后

f. 更改表名

ALTER TABLE tablename RENAME [TO] new_tablename

将表 emp 改名为 emp1


（3）DML

① 增

使用 insert 命令进行新增，语法如下：

INSERT INTO tablename (field1,field2,...,fieldn) values (value1,value2,...,valuen);

往 emp 表中插入一条记录（指定要插入的列和值）

如果不指定字段名，则默认按照表定义顺序来，值应该跟字段排列顺序保持一致，每个字段都必须有对应的值即使是 null。

显示指定字段时，含可空的字段、非空但是含有默认值的字段以及自增字段，可以不再 insert 后的字段列表里面出现，values 后面只写对应字段名称的值。

一次性插入多条数据，可以提高插入数据的性能，语法如下：

INSERT INTO tablename (field1, field2, ..., fieldn)
VALUES
(record1_value1, record1_value2, ..., record1_valuen),
(record2_value1, record2_value2, ..., record2_valuen),
...
(recordn_value1, recordn_value2, ..., recordn_valuen)
;

往 dept 表中插入两条记录

② 改

使用 update 命令进行更改，语法如下：

UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen [WHERE CONDITION]

更新 emp 表中 ename 为 "james" 的薪水为 4000

update 命令可用于更新多个表中数据，语法如下：

UPDATE t1,t2,...,tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]

将计算机系的雇员的工资提高 10%（联表更新，利用一个表的字段来更新另一个表的字段）

③ 删

使用 delete 命令进行删除，语法如下：

DELETE FROM tablename [WHERE CONDITION]

删除 emp 表名字为 "tony" 的雇员

一次性删除多个表的数据，语法如下：

DELETE t1,t2,...,tn FROM t1,t2,...,tn [WHERE CONDITION]

删除没有所属部门的雇员

④ 查

使用 select 命令，语法如下：

SELECT * FROM tablename [WHERE CONDITION]

  • 简单查询

  • 条件查询：使用 where 关键字

  • 查询字段更名：使用 as 关键字，或者直接加别名即可

  • 去除重复的记录：使用 distinct 关键字

  • 排序：使用 order by 关键字，默认为升序，通过 desc/asc 显式指定升降序

  • 限制：使用 limit 关键字，可直接指定最多查询多少数据，或者某一页数据

  • 聚合查询

    • 指定分组：使用 group by 关键字
    • 对每个分组进行条件限定：使用 having 关键字
    • 分组聚合函数：avg()、sum()、min()、max()、count()
    • 分组统计：使用 with rollup 关键字

  • 多关系查询

    • 自然连接（内连接）：使用 natural join 关键字
    • 连接（笛卡儿积）：table1, table2，产生的结果集行数为两个表的数据行数的积
    • 外连接：使用 left join/right join 关键字，mysql 中不存在全外连接
  • 集合运算
    • 并：使用 union/union all 关键字
    • 交/差：无法像其他 DBMS 一样使用 intersect/except 来计算交集和差集，需要借助 in/not in 等方式来计算。


（4）DCL

① 授权

grant [insert,delete,select,update(col_name)] on dbname.[*|tablename] to user@host identified by password;

创建一个用户 z1，具有对 sakila 数据库中所有表的 SELECT/INSERT 权限：

grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';

② 回收权限

revoke [insert,delete,select,update(col_name)] on dbname.[*|tablename] from user@host;

回收用户 Amit 对学校数据库的 department 表的查询权限

revoke select on school.department from Amit@www.scau.edu.cn;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,919评论 6赞 502
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,567评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 163,316评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,294评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,318评论 6赞 390
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,245评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,120评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,964评论 0赞 275
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,376评论 1赞 313
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,592评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,764评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,460评论 5赞 344
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,070评论 3赞 327
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,697评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,846评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,819评论 2赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,665评论 2赞 354