MySQL基础操作的学习笔记,记录了安装、创建数据库、创建表和数据增删改查等操作,介绍了MySQL引擎和KEY等基本知识。
文章目录
数据库简介
什么是数据库?
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。
常见数据库
- Oracle
- DB2
- SQL Server
- Postgre SQL
- MySQL(关系数据库)
数据库系统
数据库系统(Database System,简称DBS),是由数据库及其管理软件组成的系统。能更全面的进行数据处理。包含如下部分:
- 数据库(Database)
- 数据库管理系统(Database Management System):DBMS
- 应用开发工具
- 管理员及用户
SQL语言
结构化查询语言(Structured Query Language)简称SQL,是关系数据库的操作语言,主要用于
存取数据以及查询、更新和管理关系数据库系统。包含如下部分:
- DDL(数据定义语言)
- DML(数据操作语言)
- DQL(数据查询语言)
- DCL(数据控制语言)
数据库的下载和安装
下载安装步骤
msi文件安装
基本全程自动化安装。
- 进入官网下载MYSQL
官网地址:https://dev.mysql.com/downloads/installer/
(32位的MSI包同样适用64位) - 全部使用默认值,下一步安装即可。
(有Execute的地方则选择Execute。此外,若打开时提示.NET相关内容,则需要访问提示框中的地址,下载安装.NET对应版本即可)
zip文件安装
- 解压安装包,根据自己的喜好选择路径,我选择的路径是G:\,因此MySQL的完整路径为:G:\mysql-5.7.25-win32。
- 配置环境变量
- 新增系统环境变量:
- 键名:MYSQL_HOME
- 值为:G:\mysql-5.7.25-win32
- 编辑环境变量:
- 在Path的末尾添加:%MYSQL_HOME%\bin,注意Path中不同值之间的“;”符号不能省略。
-
准备my.ini文件。可以先新建一个my.txt文件,然后通过重命名修改文件后缀为.ini,以前的版本解压后或许会存在my-default.ini文件,但是5.7.21版本没有,因此要自己手动创建该文件,编辑文件的内容如下:
[mysqld] port = 3306 basedir=G:\mysql-5.7.25-win32 datadir=G:\mysql-5.7.25-win32/data max_connections=200 character-set-server=utf8 default-storage-engine=INNODB sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysql] default-character-set=utf8
-
以管理员身份打开cmd命令窗口,然后将目录切换到MySQL的安装目录的bin目录下。
-
执行以下语句进行MySQL的安装。
执行命令后提示:Service successfully installed. 表示安装成功。mysqld -install
-
执行以下语句进行MySQL的初始化。
执行命令后会在MySQL的安装目录下生成data目录并创建root用户。mysqld --initialize-insecure --user=mysql
-
执行以下命令以启动mysql服务。
执行后会有如下提示:
MySQL服务正在启动…
MySQL服务已经启动成功。net start mysql
-
启动MySQL之后,root用户的密码为空,设置密码,命令如下:
mysqladmin -u root -p password 新密码 (输入上一行后自动弹出)Enter password: 旧密码
-
安装完毕,可以使用MYSQL了。
使用步骤
-
开始-输入cmd-回车。进入命令行模式。
-
输入"mysql -uroot -proot"(用户名root,密码root)
-
若提示找不到命令,则需要配置环境变量。\
- (WIN7)开始-对着计算机右键-属性-高级系统设置\
- 高级标签-环境变量\
- 在用户变量和系统变量中,添加变量名"Path",值为MYSQL安装路径下的bin目录地址(一般在"C:\Program Files\MySQL\MySQL Server 8.0\bin")
-
账号密码正确则登录成功
-
退出MYSQL则输入"quit"。
MYSQL基础说明
基本配置&命令
名称 | 说明/语句 | 详细说明 |
---|---|---|
MYSQL配置文件 | Windows:my.ini文件 | |
登录MYSQL | mysql -uroot -proot | -u代表用户名,-p代表密码 |
退出MYSQL | exit或quit或\q | 三种方法均可 |
命令行结尾符 | ;或\g | 两个都可以表示语句的结尾 |
帮助命令 | help或\h | 在命令前面加help或者命令后面加\h |
取消命令 | /c | 加在结尾可以表示取消当前命令的执行 |
注释 | #或– | mysql中的注释 |
查看错误信息 | SHOW WARNINGS; |
SQL语句语法规范
- 常用MySQL的关键字需要大写,库名、表名、字段名称等使用小写
- SQL语句支持折行操作,拆分的时候不能把完整单词拆开
- 数据库名称、表名称、字段名称不要使用MySQL的保留字,如果必须要使用,需要用反引号将其括起来
MYSQL常用命令
数据库相关
语句 | 说明 | |
---|---|---|
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name; | 创建数据库 | |
USE db_name; | 打开指定数据库 | |
SELECT DATABASE()|SCHEMA(); | 得到当前打开的数据库 | |
DROP DATABASE db_name; | 删除指定的数据库 | |
CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset; | 创建数据库同时指定编码 | |
SELECT USER(); | 得到登陆的用户 | |
SELECT VERSION(); | 得到MySQL的版本信息 | |
SELECT NOW(); | 得到当前的日期时间 | |
SHOW DATABASES|SCHEMAS; | 查看当前服务器下全部数据库 | |
SHOW CREATE DATABASE db_name; | 查看指定数据库的详细信息 | |
ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset; | 修改指定数据库的编码方式 | |
DROP DATABASE db_name; | 删除指定的数据库 |
数据表相关
创建表的语法:
CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [完整性约束条件],
字段名称 字段类型 [完整性约束条件],
...
)ENGINE=存储引擎 CHARSET=编码方式;
例子:
CREATE TABLE IF NOT EXISTS `test_user`(
`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
`username` VARCHAR(20) NOT NULL COMMENT '用户名',
`password` CHAR(32) NOT NULL COMMENT '密码',
`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
`sex` ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
`tel` CHAR(11) NOT NULL UNIQUE COMMENT '电话',
`married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚,1代表已结婚',
`salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
语句 | 说明 | |
---|---|---|
SHOW TABLES; | 查看当前数据库下已有数据表 | |
SHOW CREATE TABLE tbl_name; | 查看指定数据表的详细信息 | |
DESC tbl_name; | 查看表结构 | |
DROP TABLE [IF EXISTS] tbl_name; | 删除指定的数据表 | |
DESCRIBE tbl_name; | 查看表结构 | |
SHOW COLUMNS FROM tbl_name; | 查看表结构 | |
SHOW TABLE STATUS LIKE ‘tbl_name’ \G | 查看表详细信息 | |
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name | 修改表名 | |
RENAME TABLE tbl_name TO new_tbl_name; | 修改表名 | |
ALTER TABLE user AUTO_INCREMENT=1; | 设置自动增长值(重置) |
字段相关
语法 | 说明 | |
---|---|---|
ALTER TABLE tbl_name ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]; | 添加字段 | |
ALTER TABLE tbl_name DROP 字段名称; | 删除字段 | |
ALTER TABLE tal_name ALTER 字段名称 SET DEFAULT 默认值; | 修改默认值 | |
ALTER TABLE tal_name ALTER 字段名称 DROP DEFAULT; | 删除默认值 | |
ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称] | 修改字段类型、字段属性 | |
ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称] | 修改字段名称、字段类型和字段属性 | |
ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称) | 增加主键 | |
ALTER TABLE tbl_name DROP PRIMARY KEY; | 删除主键 | |
ALTER TABLE tbl_name ADD UNIQUE KEY|INDEX [index_name] (字段名称) | 添加唯一 | |
ALTER TABLE tbl_name DROP [INDEX] index_name; | 删除唯一 |
数据相关(增删改查)
增加数据
INSERT [INTO] tbl_name[(col_name,...)] {VALUE|VALUES}(VALUES...);
语法 | 说明 |
---|---|
INSERT tbl_name VALUE(value…) | 不指定字段名称,需要按照建表时的字段顺序给每一个字段赋值 |
INSERT tbl_name(字段名称,…) VALUES(值,…) | 按照列出字段添加数据 |
INSERT tbl_name SET 字段名称=值,…; | INSERT … SET的形式,字段对应值逐个添加 |
INSERT tbl_name[(字段名称…)] SELECT 字段名称,… FROM tbl_name [WHERE 条件] | INSERT … SELECT的形式,从其他表新增数据 |
INSERT tbl_name[(字段名称,…)] VALUES(值,…),(值,…),(值,…) | 一次添加多条记录 |
删除数据
语法 | 说明 |
---|---|
DELETE FROM tbl_name [WHERE 条件] | 如果不添加条件,表中所有记录都会被删除 |
TRUNCATE [TABLE] tbl_name; | 清除表中所有记录,同时重置AUTO_INCREMENT值 |
修改数据
语法 | 说明 |
---|---|
UPDATE tbl_name SET 字段名称=值,字段名称=值 [WHERE 条件] | 如果不添加条件,整个表中的记录都会被更新 |
查找数据
SELECT select_expr,... FROM tbl_name
[WHERE 条件]
[GROUP BY {col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr} [ASC|DESC]]
[LIMIT 限制结果集的显示条数]
语法 | 说明 |
---|---|
SELECT * FROM tbl_name; | 查询表中所有记录,*代表所有字段 |
SELECT 字段名称,… FROM tbl_name | 查询指定字段的信息 |
SELECT 字段名称,… FROM db_name.tbl_name; | 查找来自“库名.表名”的信息 |
SELECT 字段名称 [AS] 别名名称,… FROM db_name.tbl_name; | 给字段起别名 |
SELECT 字段名称 ,… FROM tbl_name [AS] 别名; | 给数据表起别名 |
SELECT tbl_name.col_name,… FROM tbl_name; | 多表查询中可以显示该字段来自哪个表。表名.字段名 |
WHERE条件
查询需要搭配WHERE条件进行使用。使用通配符时需要用LIKE关键字,例如:
SELECT * FROM goods WHERE name LIKE '%机' AND price<5000;
语法 | 说明 |
---|---|
> >= < <= != <> | 比较运算符 |
<=> | 可以检测NULL值 |
IS [NOT] NULL | 检测值是否为NULL或者NOT NULL |
[NOT] BETWEEN begin AND end | 查找指定范围的数据 |
[NOT] IN(值,…) | 查找指定值的数据 |
AND | 逻辑运算符:逻辑与 |
OR | 逻辑运算符:逻辑或 |
[NOT] LIKE | 匹配字符,需要与通配符配合使用 |
% | 通配符:代表任意长度的字符串 |
_ | 通配符:代表任意一个字符 |
GROUP BY 分组
可以指定按照某个字段进行分组。
语法 | 说明 | 截图 |
---|---|---|
GROUP BY tbl_name; | 按照tbl_name进行分组,最终查询出的结果只会显示组中一条记录 | |
GROUP_CONCAT() | 显示指定字段的详细数据 | |
WITH ROLLUP | 会在记录末尾添加一条记录,是上面所有记录的总和 | |
HAVING | 二次筛选,和WHERE使用类似,后面加条件。会在一次筛选结果后再次筛选。条件必须为聚合函数或出现在SELECT中的字段 |
聚合函数通常可以配合GROUP BY分组使用,类似于EXCEL中的数据透视功能。
聚合函数 | 说明 | 截图 |
---|---|---|
COUNT() | 统计记录总数 COUNT(字段名称)不统计NULL COUNT(*)会统计NULL | |
SUM() | 求和 | |
MAX() | 求最大值 | |
MIN() | 求最小值 | |
AVG() | 求平均值 |
ORDER BY 排序
可以通过ORDER BY指定按照某个字段升序或降序排列。
语法 | 说明 |
---|---|
ORDER BY 字段名称 ASC|DESC | ASC为升序,DESC为降序 |
LIMIT 限制结果集显示条数
使用LIMIT可以控制显示数量,方便实现分页效果。
语法 | 说明 |
---|---|
LIMIT 值 | 显示结果集的前几条记录 |
LIMIT offset,row_count | 从offset开始,显示几条记录,offset从0开始 |
多表查询
可以理解为把多个表合成一个表进行处理,有两种形式,内连接和外链接(去重和不去重的区别)。
语法 | 说明 |
---|---|
SELECT 字段名称,... FROM tbl_name1 INNER JOIN tbl_name2 ON 连接条件 | 内连接的形式: 查询两个表中符合连接条件的记录,提取重合部分 |
SELECT 字段名称,... FROM tbl_name1 LEFT [OUTER] JOIN tbl_name2 ON 条件; | 左外连接:先显示左表中的全部记录,再去右表中查询复合条件的记录,不符合的以NULL代替 |
SELECT 字段名称,... FROM tbl_name1 RIGHT [OUTER] JOIN tbl_name2 ON 条件; | 右外连接:先显示右表中的全部记录,再去左表中查询复合条件的记录,不符合的以NULL代替 |
内连接查询 |
---|
左外连接查询 |
三表联查-内连接查询 |
MYSQL使用案例
创建数据库
CREATE DATABASE IF NOT EXISTS QQFH DEFAULT CHARACTER SET 'UTF8';
查看所有数据库
SHOW DATABASES;
进入数据库
USE QQFH;
查看当前打开的数据库
SELECT DATABASE();
创建表和字段
CREATE TABLE IF NOT EXISTS `qqfh_class`(
id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '教室编号',
c_name CHAR(32) NOT NULL COMMENT '教室名称'
)ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
CREATE TABLE IF NOT EXISTS `qqfh_user`(
id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
username VARCHAR(20) NOT NULL COMMENT '用户名 ',
sex ENUM('a','b','c') NOT NULL DEFAULT 'a' COMMENT '性别',
class_id INT UNSIGNED COMMENT '教室编号',
FOREIGN KEY(class_id) REFERENCES qqfh_class(id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
查看所有表
SHOW TABLES;
查看表结构
DESC qqfh_user;
DESC qqfh_class;
查看详细的键表信息
SHOW CREATE TABLE qqfh_user;
添加数据
INSERT qqfh_class(c_name) VALUES('图书馆'),('实验室'),('行政楼');
INSERT qqfh_user(username,sex,class_id) VALUES('tom','a',2),
('jack','b',3),
('hasen','a',1),
('sam','a',1),
('zhangsan','a',1),
('lisi','c',3),
('wangwu','c',2),
('zhaoliu','a',1);
查询数据
--通过内连接查询两个表中的字段,按照用户编号字段升序排序
SELECT u.id AS '用户编号',u.username AS '用户名',u.sex AS '性别',c.c_name AS '教室名称' FROM qqfh_user AS u
INNER JOIN qqfh_class AS c
ON u.class_id=c.id
WHERE sex='a'
ORDER BY 用户编号 ASC;
--把表格按照“教室名称”分组,统计每个教室的用户数量
SELECT GROUP_CONCAT(u.username) AS '用户名称',count(u.username) AS '用户数量',c.c_name AS '教室名称' FROM qqfh_user AS u
INNER JOIN qqfh_class AS c
ON u.class_id=c.id
GROUP BY 教室名称;
删除表
DROP TABLE IF EXISTS qqfh_class;
DROP TABLE IF EXISTS qqfh_user;
删除数据库
DROP DATABASE qqfh;
MySQL参数详解
约束条件
关键字 | 说明 |
---|---|
UNSIGNED | 设置为无符号,没有负数,从0开始 |
ZEROFILL | 零填充,当数据显示长度不够时,会使用前补0填充至指定长度,字段会自动添加UNSIGNED |
NOT NULL | 不能为NULL值,并且不能不赋值 |
DEFAULT | 默认值,如果增加信息时没有赋值则赋予默认值 |
PRIMARY KEY | 主键,标识记录的唯一性,值不能重复,自动禁止为空,一个表只能有一个主键 |
AUTO_INCREMENT | 自动增长,只能用于数值列,必须和主键配合使用 |
UNIQUE KEY | 唯一性索引,值不能重复,可以为NULL(不算重复),KEY可以省略不写 |
数据类型
整数型
数据类型 | 存储范围 | 字节 |
---|---|---|
TINYINT | 有符号值:-128到127(-2^7^到2^7^-1);无符号值:0到255(0到2^8^-1) | 1 |
SMALLINT | 有符号值:-32768到32767(-2^15^到2^15^-1);无符号值:0到65535(0到2^16^-1) | 2 |
MEDIUMINT | 有符号值:-8388608到8388607(-2^23^到2^23^-1);无符号值:0到16777215(0到2^24-1) | 3 |
INT | 有符号值:-2147683648到2147683647(-2^31^到2^31^-1);无符号值:0到4294967295(0到2^32^-1) | 4 |
BIGINT | 有符号值:-9223372036854775808到9223373036854775807(-2^63^到2^63^-1);无符号值:0到18446744073709551615(0到2^64^-1) | 8 |
BOOL,BOOLEAN | 等价于TINYINT(1),0为false,其余为true | 1 |
浮点型
数据类型 | 存储范围 | 字节 |
---|---|---|
FLOAT[(M,D)] | 负数取值范围为-3.40E+38到-1.17E-38、0和1.175E-38到3.40E+38。M是数字总位数,D是小数点后面的位数,如果M和D被省略,根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。 | 4 |
DOUBLE[(M,D)] | -1.79E+308到-2.22E-308、0和2.22E-308到1.79E+308 | 8 |
DECIMAL[(M,D)] | 和DOUBLE一样,内部以字符串形式存储数值 | M+2 |
字符串类型
- CHAR效率高于VARCHAR,CHAR相当于拿空间换时间,VARCHAR拿时间换空间
- CHAR默认存储数据的时候,后面会用空格填充到指定长度;而在检索的时候会去掉后面空格;VARCHAR在保存的时候不进行填充,尾部的空格会留下
- TEXT列不能有默认值,检索的时候不存在大小写转换
数据类型 | 存储需求 |
---|---|
CHAR(M) | M个字节,0<=M<=255 |
VARCHAR(M) | L+1个字节,其中L<=M且0<=M<=65535 |
TINYTEXT | L+1个字节,其中L<2^8 |
TEXT | L+2个字节,其中L<2^16 |
MEDIUMTEXT | L+3个字节,其中L<2^24 |
LONGTEXT | L+4个字节,其中L<2^32 |
ENUM(‘value1’,‘value2’,…) | 1或2个字节,取决于枚举值的个数(最多65535个值) |
SET(‘value1’,‘value2’,…) | 1、2、3、4或者8个字节,取决于set成员的数目(最多64个成员) |
日期时间类型
数据类型 | 存储范围 | 存储需求 |
---|---|---|
TIME | -838:59:59~838:59:59 | 3 |
DATE | 1000-01-01~9999-12-31 | 3 |
DATETIME | 1000-01-01 00:00:00~9999-12-31 23:59:59 | 8 |
TIMESTAMP | 1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 | 4 |
YEAR | 1901-2155 | 1 |
存储引擎
Mysql有两种数据引擎,MyISAM和InnoDB。
-
MyISAM是MySQL旧版默认引擎类型(5.5版之前),由早期的ISAM(Indexed Sequential Access Method:有索引的顺序访问方法)所改良,它是存储记录和文件的标准方法。MyISAM具有检查和修复表格的大多数工具。MyISAM表格可以被压缩,而且它们支持全文搜索。它们不是事务安全的,而且也不支持外键。如果事务回滚将造成不完全回滚,不具有原子性。如果执行大量的SELECT,MyISAM是更好的选择。
-
InnoDB是MySQL新版默认引擎类型,这种类型是事务安全的,它支持外键。如果需要一个事务安全的存储引擎,建议使用它。如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。
MyISAM存储引擎特点
- 默认MyISAM的表会在磁盘中产生3个文件
- .frm(表结构文件)
- .MYD(数据文件)
- .MYI(索引文件)
- 可以在创建的时候指定数据文件和索引文件的存储位置,只有MyISAM表支持
- DATA DIRECORY [=] 数据保存的绝对路径
- INDEX DIRECTORY [=] 索引文件保存的绝对路径
- MyISAM单表最大支持的数据量2的64次方条记录
- 每个表最多可以建立64个索引
- 如果是复合索引,每个复合索引最多包含16个列,索引值最大长度是1000B
- MyISAM引擎的存储格式
- 定长(FIXED 静态):是指字段中不包含VARCHAR/TEXT/BLOB
- 动态(DYNAMIC):只要字段中包含了VARCHAR/TEXT/BLOB
- 压缩(COMPRESSED):myisampack创建
InnoDB存储引擎特点
- 创建InnoDB表之后会产生两个文件
- .frm表结构文件
- .ibd,数据和索引存储表空间中
- 支持行级锁,可以提升多用户并发时的读写性能
- 支持外键,保证数据的一致性和完整性
- InnoDB拥有自己独立的缓冲池,常用的数据和索引都在缓存中
- 对于INSERT、UPDATE、DELETE操作,InnoDB会使用一种change buffering的机制来自动优化,还可以提供一致性的读,并且还能够缓存变更的数据,减少磁盘I/O,提高性能
- 设计遵循ACID模型,支持事务,具有从服务崩溃中恢复的能力,能够最大限度保护用户的数据
- 所有的表都需要创建主键,最好是配合上AUTO_INCREMENT,也可以放到经常查询的列作为主键
外键约束
外键约束是指把两个表中的某一个字段进行关联,两个表分为主表和子表,子表被选中的字段的值必须和主表被选中的字段的值相同。外键约束有如下特点:
- 子表的外键字段和主表的主键字段类型要相似;如果是数值型要求一致,并且无符号也要一致;如果是字符型,要求类型一致,长度可以不同
- 如果外键字段没有创建索引,MySQL会自动帮我们添加索引
- 子表的外键关联的必须是父表的主键
语法 | 说明 | 截图 |
---|---|---|
[CONSTRAINT 外键名称 ]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称) | 创建表时指定外键 | |
ALTER TABLE 表名称 ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段); | 动态添加外键,不能有脏记录 | |
ALTER TABLE 表名称 DROP FOREIGN KEY 外键名称; | 动态删除外键 |
外键约束的参照
可以设置外键约束包含CASCADE参数,设置后父表删除或者更新,子表也跟着删除或者更新。例子:
ALTER TABLE user_exam
ADD FOREIGN KEY(user_id) REFERENCES user_class(id)
ON DELETE CASCADE ON UPDATE CASCADE;
语法 | 说明 | 截图 |
---|---|---|
CASCADE | 从父表删除或更新,子表也跟着删除或者更新,级联的操作 | |
SET NULL | 从附表删除或者更新记录,并设置子表的外键列为NULL。 | |
NO ACTION | RESTRICT | 拒绝对父表做更新或者删除操作 |
子查询
子查询表示在一个查询语句中,还嵌套了一个查询语句。内层语句查询的结果会做为外层语句查询的条件。
语法:
SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
例子1:由IN引发的子查询
内层查询执行完后,再执行外层查询。
SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);
例子2:由EXISTS引发的子查询
查询id为1的score,得到一个数值后,执行外层查询。
SELECT id,username,score FROM stud
WHERE score>=(SELECT score FROM level WHERE id=1);
例子3:由EXISTS引发的子查询
如果内层执行结果为真,则执行外层查询语句。
SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=1);
例子4:使用ANY SOME ALL查询
>=ANY()表示查询结果的最小值,得到内层查询结果的最小值,然后执行外层查询。
SELECT * FROM stu
WHERE score>= ANY(SELECT score FROM level);
运算符 | ANY | SOME | ALL |
---|---|---|---|
>、>= | 最小值 | 最小值 | 最大值 |
<、<= | 最大值 | 最大值 | |
= | |||
<>、!= |
例子5:去掉重复字段
DISTINCT关键字可以实现去重功能。
SELECT DISTINCT(username) FROM user2;
例子6:通过SELECT,在创建表时添加数据
CREATE TABLE user1(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20)
)SELECT id,username FROM emp;
例子7:通过LIKE复制user1表,生成新的user2表
CREATE TABLE user2 LIKE user1;
例子8:通过INSERT把user1的数据添加到user2中
INSERT user2 SELECT id,username FROM user1;
联合查询
语法 | 说明 |
---|---|
SELECT 字段名称,… FROM tbl_name1 UNION SELECT 字段名称… FROM tbl_name2; | 把两个表合并,去掉重复项 |
SELECT 字段名称,… FROM tbl_name1 UNION ALL SELECT 字段名称… FROM tbl_name2; | 把两个表合并,不去掉重复项 |
MySQL常用函数
数学函数
语法 | 说明 |
---|---|
CEIL() | 进一取整,返回的最小整数值 |
FLOOR() | 舍掉小数部分 |
ROUND() | 四舍五入 |
TRUNCATE() | 截取小数点后几位 |
MOD() | 取余数 |
ABS() | 取绝对值 |
POWER() | 幂运算 |
PI() | 圆周率 |
RAND()或者RAND(X) | 0~1之间的随机数 |
SIGN(X) | 得到数字符号 |
EXP(X) | 计算e的x次方 |
字符串函数
语法 | 说明 |
---|---|
CHAR_LENGTH() | 得到字符串的字符数 |
LENGTH() | 返回字符串的长度 |
CONCAT(s1,s2,…) | 将字符串合并成一个字符串,如果包含NULL,拼接后的结果为NULL |
CONCAT_WS(x,s1,s2,s3,…) | 以指定分隔符拼接字符串 |
UPPER()/UCASE() | 将字符串转换成大写 |
LOWER()/LCASE() | 将字符串转换成小写 |
REVERSE() | 反转函数 |
LEFT()/RIGHT() | 得到前几字符或者后几个字符 |
LPAD()/RPAD() | 用字符串填充左端或右端到指定长度 |
TRIM()/LTRIM()/RTRIM() | 去掉字符串两端的空格 |
REPEAT() | 重复指定字符串的次数 |
REPLACE() | 字符串替换 |
SUBSTRING() | 截取字符串 |
STRCMP() | 比较字符串 |
日期时间函数
语法 | 说明 |
---|---|
CURDATE()/CURRENT_DATE() | 返回当前日期 |
CURTIME()/CURRENT_TIME() | 返回当前时间 |
NOW()/CURRENT_TIMESTAMP()/SYSDATE() | 返回当前日期时间 |
MONTH() | 返回日期中的月份 |
MONTHNAME() | 返回日期中的月份名称 |
DAYNAME() | 返回星期几 |
DAYOFWEEK() | 返回一周内的第几天 |
WEEK() | 返回一年中的第几个星期 |
YEAR() | 返回年份 |
DAY() | 返回当月第几天 |
HOUR() | 返回小时 |
MINUTE() | 返回分钟 |
SECOND() | 返回秒 |
DATEDIFF() | 计算两个日期相差的天数 |
其他常用函数
语法 | 说明 |
---|---|
VERSION() | 获得版本 |
CONNECTION_ID | 服务器连接数 |
USER()/CURRENT_USER/SYSTEM_USER()/SESSION_USER() | 获取当前登录的用户 |
LAST_INSERT_ID() | 得到上一步插入操作产生的AUTO_INCREMENT的值 |
MD5() | 使用MD5加密 |
PASSWORD() | MySQL默认的密码加密算法 |