MySQL 基础知识

凝雪探世界 等级 722 1 0

前言

在日常开发中,一些不常用且又比较基础的知识,过了一段时间之后,总是容易忘记或者变得有点模棱两可。本篇主要记录一些关于MySQL数据库比较基础的知识,以便日后快速查看。

SQL命令

SQL命令分可以分为四组:DDL、DML、DCL和TCL。四组中包含的命令分别如下

MySQL 基础知识

DDL

DDL是数据定义语言(Data Definition Language)的简称,它处理数据库schemas和描述数据应如何驻留在数据库中。

  • CREATE:创建数据库及其对象(如表,索引,视图,存储过程,函数和触发器)

  • ALTER:改变现有数据库的结构

  • DROP:从数据库中删除对象

  • TRUNCATE:从表中删除所有记录,包括为记录分配的所有空间都将被删除

  • COMMENT:添加注释

  • RENAME:重命名对象

    常用命令如下:

# 建表
CREATE TABLE sicimike (
  id int(4) primary key auto_increment COMMENT '主键ID',
  name varchar(10) unique,
  age int(3) default 0,
  identity_card varchar(18)
  # PRIMARY KEY (id) // 也可以通过这种方式设置主键
  # UNIQUE KEY (name) // 也可以通过这种方式设置唯一键
  # key/index (identity_card, col1...) // 也可以通过这种方式创建索引
) ENGINE = InnoDB;

# 设置主键
alter table sicimike add primary key(id);

# 删除主键
alter table sicimike drop primary key;

# 设置唯一键
alter table sicimike add unique key(column_name);

# 删除唯一键
alter table sicimike drop index column_name;

# 创建索引
alter table sicimike add [unique/fulltext/spatial] index/key index_name (identity_card[(len)] [asc/desc])[using btree/hash]
create [unique/fulltext/spatial] index index_name on sicimike(identity_card[(len)] [asc/desc])[using btree/hash]
example:alter table sicimike add index idx_na(name, age);

# 删除索引
alter table sicimike drop key/index identity_card;
drop index index_name on sicimike;

# 查看索引
show index from sicimike;

# 查看列
desc sicimike;

# 新增列
alter table sicimike add column column_name varchar(30);

# 删除列
alter table sicimike drop column column_name;

# 修改列名
alter table sicimike change column_name new_name varchar(30);

# 修改列属性
alter table sicimike modify column_name varchar(22);

# 查看建表信息
show create table sicimike;

# 添加表注释
alter table sicimike comment '表注释';

# 添加字段注释
alter table sicimike modify column column_name varchar(10) comment '姓名';

DML

DML是数据操纵语言(Data Manipulation Language)的简称,包括最常见的SQL语句,例如SELECT,INSERT,UPDATE,DELETE等,它用于存储,修改,检索和删除数据库中的数据。


分页

-- 查询从第11条数据开始的连续5条数据
select * from sicimike limit 10, 5
group by
默认情况下,MySQL中的分组(group by)语句,不要求select返回的列,必须是分组的列或者是一个聚合函数。
如果select查询的列不是分组的列,也不是聚合函数,则会返回该分组中第一条记录的数据。对比下面两条SQL语句,第二条SQL语句中,cname既不是分组的列,也不是以聚合函数的形式出现。所以在liming这个分组中,cname取的是第一条数据。
mysql> select * from c;
+-----+-------+----------+
| CNO | CNAME | CTEACHER |
+-----+-------+----------+
| 1 | 数学 | liming |
| 2 | 语文 | liming |
| 3 | 历史 | xueyou |
| 4 | 物理 | guorong |
| 5 | 化学 | liming |
+-----+-------+----------+
5 rows in set (0.00 sec)

mysql> select cteacher, count(cteacher), cname from c group by cteacher;
+----------+-----------------+-------+
| cteacher | count(cteacher) | cname |
+----------+-----------------+-------+
| guorong |               1 | 物理 |
| liming |               3 | 数学 |
| xueyou |               1 | 历史 |
+----------+-----------------+-------+
3 rows in set (0.00 sec)

having

having关键字用于对分组后的数据进行筛选,功能相当于分组之前的where,不过要求更严格。过滤条件要么是一个聚合函数( ... having count(x) > 1),要么是出现在select后面的列(select col1, col2 ... group by x having col1 > 1)

多表更新

update tableA a inner join tableB b on a.xxx = b.xxx set a.col1 = xxx, b.col1 = xxx where ...

多表删除

delete a, b from tableA a inner join tableB b on a.xxx = b.xxx where a.col1 = xxx and b.col1 = xxx

DCL

DCL是数据控制语言(Data Control Language)的简称,它包含诸如GRANT之类的命令,并且主要涉及数据库系统的权限,权限和其他控件。

  • GRANT :允许用户访问数据库的权限
  • REVOKE:撤消用户使用GRANT命令赋予的访问权限

TCL

TCL是事务控制语言(Transaction Control Language)的简称,用于处理数据库中的事务

  • COMMIT:提交事务
  • ROLLBACK:在发生任何错误的情况下回滚事务

范式

数据库规范化,又称正规化、标准化,是数据库设计的一系列原理和技术,以减少数据库中数据冗余,增进数据的一致性。关系模型的发明者埃德加·科德最早提出这一概念,并于1970年代初定义了第一范式、第二范式和第三范式的概念,还与Raymond F. Boyce于1974年共同定义了第三范式的改进范式——BC范式。除外还包括针对多值依赖的第四范式,连接依赖的第五范式、DK范式和第六范式。

现在数据库设计最多满足3NF,普遍认为范式过高,虽然具有对数据关系更好的约束性,但也导致数据关系表增加而令数据库IO更易繁忙,原来交由数据库处理的关系约束现更多在数据库使用程序中完成。

横表纵表


SQL脚本

 横表

CREATE TABLE `table_h2z` (
`name` varchar(32) DEFAULT NULL,
`chinese` int(11) DEFAULT NULL,
`math` int(11) DEFAULT NULL,
`english` int(11) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `table_h2z` */
insert into `table_h2z`(`name`,`chinese`,`math`,`english`) values 
('mike',45,43,87),
('lily',53,64,88),
('lucy',57,75,75);

  纵表

CREATE TABLE `table_z2h` (
  `name` varchar(32) DEFAULT NULL,
  `subject` varchar(8) NOT NULL DEFAULT '',
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `table_z2h` */
insert into `table_z2h`(`name`,`subject`,`score`) values 
('mike','chinese',45),
('lily','chinese',53),
('lucy','chinese',57),
('mike','math',43),
('lily','math',64),
('lucy','math',75),
('mike','english',87),
('lily','english',88),
('lucy','english',75);

横表转纵表

SELECT NAME, 'chinese' AS `subject`, chinese AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'math' AS `subject`, math AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'english' AS `subject`, english AS `score` FROM table_h2z

执行结果
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| mike | chinese | 45 |
| lily | chinese | 53 |
| lucy | chinese | 57 |
| mike | math | 43 |
| lily | math | 64 |
| lucy | math | 75 |
| mike | english | 87 |
| lily | english | 88 |
| lucy | english | 75 |
+------+---------+-------+
9 rows in set (0.00 sec)

纵表转横表

SELECT NAME,
 SUM(CASE `subject` WHEN 'chinese' THEN score ELSE 0 END) AS chinese,
 SUM(CASE `subject` WHEN 'math' THEN score ELSE 0 END) AS math,
 SUM(CASE `subject` WHEN 'english' THEN score ELSE 0 END) AS english
FROM table_z2h
GROUP BY NAME

执行结果
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| lily | 53 |   64 | 88 |
| lucy |      57 | 75 |      75 |
| mike | 45 |   43 | 87 |
+------+---------+------+---------+
3 rows in set (0.00 sec)
收藏
评论区

相关推荐

mysql表和字段的操作
(3)mysql表和字段的操作 (3)mysql表和字段的操作 创建表 create table name( id int, student varchar(20) ); 查看表结构 常用 describe 表名; 修改表名 老表 rename 新表 ALTER TABLE tb\_men
MySQL 8.0 创建 user 及允许远程连接
进入 mysql 命令行模式mysql h127.0.0.1 uroot p 查看当前 mysql 版本select version(); 查看当前 mysql 用户列表信息select host, user, authentication_string, plugin from user; 执行完上面的命令后会显示一个表格 查看表格
MySQL 5.7 基于 GTID 的主从复制实践
MySQL 5.7 基于 GTID 的主从复制实践 ========================= Posted by Mike on 2017-07-03 [运维之美](https://www.oschina.net/action/GoToLink?url=https%3A%2F%2Fwww.hi-linux.com%2F) * [Home]
MySQL 8.0.23新特性
> 作者:lefred > > 译者:孟维克 > 原文链接: > > * https://lefred.be/content/mysql-invisible-column-part-i/ > > * https://lefred.be/content/mysql-invisible-column-part-ii/ >
MySQL主从复制与读写分离
MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践 Mysql作为目前世界上使用最广泛的免费数据库,相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中,由单台Mysql作为独立的数据库是完全不能满足实际需求的,无论是在安全性,高可用性以及高并发等各个方面。 因此,一般来说都是通过 主从复制(Master-S
MySQL学习笔记(一)
一、MySQL产品的介绍和安装 --------------- * **MySQL服务的启动和停止** 1. 方式一:开始(右键)→ 计算机管理 → 服务和应用程序 → 服务 → MySQL(服务名) → 启动、停止、暂停、重新启动等 2. 方式二:以管理员身份运行cmd net stop mysql(服务名)
MySQL数据库优化技巧
MySQL优化三大方向 ① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。 ② 对MySQL配置参数进行优化(my.cnf)此优化需要进行压力测试来进行参数调整。 ③ 对SQL语句以及表优化。 MySQL参数优化 1:MySQL 默认的最大连接数为 100,可以在 mysql 客户端使用以下命令查看 mysql>
MySQL自动化平台那些事
**导读** > 作者:田帅萌(邮箱:tplinux@163.com,欢迎交流) > > 知数堂MySQL DBA班第9期优秀学员,Python运维开发班第5期学员,现任职知数堂助教 承接上文[《构建MySQL自动化平台思路》](https://www.oschina.net/action/GoToLink?url=https%3A%2F%2Fmp.w
MySQL运维之
**1、mysqldump备份一个数据库** mysqldump命令备份一个数据库的基本语法: mysqldump -u user -p pwd dbname > Backup.sql 我们来讲解一下备份的文件都包含了什么?\-- MySQL dump 10.13 Distrib 5.5.20, for Win32 (x86)\--
Mysql5.7 Mac修改密码
1、mysql5.7 安装没有默认密码,安装过程中mysql会临时给你生成一个随机密码,查看方式如下: ![](https://oscimg.oschina.net/oscnet/1be25fd28f6ce3dc3af7460dbd3ad61bdc1.jpg) 2、打开命令行 alias mysql=/usr/local/mysql/bin/mysql
Ubuntu下忘记MySQL密码重设方法
1、结束当前正在运行的mysql进程。 \# /etc/init.d/mysql stop 2、用mysql安全模式运行并跳过权限验证。 \# /usr/bin/mysqld\_safe --skip-grant-tables 3、重开一个终端以root身份登录mysql。 \# mysql -u root 4、修改
mysql数据库的导出与导入
**1、首先linux 下查看mysql相关目录**       \[root@op-workorder bin\]# **whereis mysql**       mysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql **2、导出数据库用mysqldump命令**     cd /
ubuntu安装msql5.7
1.执行以下命令安装,一路按Y即可 ================= sudo apt-get install mysql-server   sudo apt install mysql-client   sudo apt install libmysqlclient-dev 2.检查mysql的
Docker rancher 部署
##Docker-rancher > #环境 centos7.4 , Docker version 17.12.0-ce #下载docker镜像 docker pull mysql:5.7 docker pull rancher/server:latest docker images #### #运行MySQL容器
TiDB 在 58 集团的应用与实践
> **作者介绍**:刘春雷,58 集团高级 DBA,负责 MySQL 和 TiDB 的运维工作,TUG Ambassador。 58 集团业务种类繁多,目前包括的业务有 58 同城、赶集网、安居客、58 金融公司、中华英才网、驾校一点通等,数据库种类包括 MySQL、Redis、MongoDB、ES、TiDB。我们自己构建了“58 云 DB 平台”,整合