MySQL基础(二之SQL语句)

MySQL基础(二)SQL语句

MySQL数据库使用SQL语言,与其他语言类似SQL语言也是由许多关键字组成代码篇章。**但是与其他语言不同的是SQL语言不区分大小写。**SQL语言根据用途不同可分为四类。

  • DDL:数据库定义语言。用于数据库级别的相关操作,例如操作数据库与操作表(注意:这是操作的是数据库的信息与表的信息,并不是具体数据。例如可以操作表中字段类型、数量,但是并不能操作表中行信息。

  • DML:数据库管理语言。用于操作表中数据行(例如修改信息、删除信息)。

  • DQL:数据库查询语言。用于查询数据库中数据行信息(可以对查询结果进行适当地修改,但是修改的只是查询出来的信息并不能改变数据库。你可以类比成传值参数)。

  • DCL:数据库控制语言。

DDL

对于库、表、字段名来说,在SQL语言中可以使用反引号来强调这不是关键字而是名。

例如:

CREATE DATABASE `school`;

操作数据库

  1. 创建数据库
CREATE DATABASE dbName;

使用上述命令后,就创建了一个库名为dbName。但是数据库中如果有叫做dbName的库,程序会报错并且终止(error),为了增强程序的健壮性可以增加一个判断语句:若存在则返回一个异常,若不存在则创建库。这样就不会终止程序了。

CREATE DATABASE IF NOT EXISTS dbName;

因为创建数据库错误会报一个exists(存在),所以上述代码意思为:如果没有exists则创建dbName库;如果存在dbName则报一个warning。

  1. 删除数据库
DROP DATABASE dbName;

删除数据库,与创建数据库相同也会有error、warning。

DROP DATABASE IF EXISTS dbName;

如果存在删除数据库。要注意的是:在删除数据库时使用的是DROP而不是DELETE!

  1. 使用数据库
USE dbName;
  1. 查看所有数据库
SHOW DATABASES;

操作表

表字段属性

字段是什么,字段就是表中的列。在表中列代表数据含义,行表示具体的某个数据。而对于字段也是有很多属性的(在上一篇中使用SQLyog创建表时就可以看出来)。

  1. 数据类型:

数值:(容量从小到大)

  • tinyint:十分小的数据,1字节。
  • smallint:较小的数据,2字节。
  • int:标准整数,4字节。(常用)
  • bigint:较大的数据,8字节。
  • float:单精度浮点数,4字节。(常用)
  • double:双精度浮点数,8字节。(常用)
  • decimal:字符串形式的浮点数(大数),(常用)

字符:

  • char:字符,固定长度(就一个字符),0—255bit。

  • varchar:可变长字符串,**0—65535bit。(常用) **

  • tinytext:微型文本,0—28-1字符。

  • text:大型文本,0—216-1字符。(常用)

正常字符串变量使用varchar,对于超大文本使用text。

时间日期类型(对应Java中的Data类):

  • date:日期,YY–MM–DD。
  • time:时间,HH: MM: SS。
  • datetime:日期时间,YY–MM–DD HH: MM: SS。(常用)
  • timestamp:时间戳。(常用)

null:

  • null就是没值、空值。对于设置了非空的字段数据不可以是null。
  1. Unsigned

无符号,与c++中关键字含义(非负数)。如果要给数据在设置了unsigned的字段赋负值则会报错。

  1. zerofill

0填充,适用于数据类型选择数值的字段,默认将此字段的数据填充为0。

  1. 非空

将字段设置为非空后,数据在次字段不能有空值。

  1. 默认

数据不初始化就创建时,选择此默认。(若无默认则选择(NULL))

  1. 长度

设置数据的最大长度。注意:这里的长度是显示长度而不是比特长度!(比如长度为3的int字段可以存储999999,显示的时候也是999999。但是你如果要存储2,那么显示就是002。但是存储精度还是int的四字节)。当然这是int的,对于varchar而言显示长度就是存储长度。所以varchar的显示长度就限制了存储长度。

  1. 自增

拥有该字段的表,数据没增加一个该字段的值就会+1。(一般与主键相搭配,并且一个表中只能有一个字段设置自增,所以一般为主键)

  1. 主键

主键用于索引字段,例如ID等这些唯一标识的字段。主键字段中数据不能重复,是唯一标识的。

  1. 注释

注释听名字就知道了。用来解释说明。

一个表中的必要字段:

  • id
  • version
  • is_delete:伪删除,是一个标志位并不是真正的将数据删除,而是用标志位查看数据是否被删除了。
  • gmt_create:创建时间
  • gmt_update:修改时间

表引擎

还记得上一篇中用SQLyog创建表吗?在选择引擎时有很多引擎,最常用的就是InnoDB、MyISAM。这是数据库的两种引擎,**两种引擎的最大区别就是:文件存储方式,操作规范不同。**在早些年使用MyISAM多一些,出了InnoDB后使用InnoDB多(后面说InnoDB的优点),但是MyISAM也并不是完全被淘汰了。

引擎在操作规范上的区别:

MyISAMInnoDB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大
  • 事务:两个SQL同时执行时,要么同时成功要么同时失败。这就是事务。
  • 数据行锁定:在MySQL中与Java多线程相似,在访问表中数据时会有一个锁锁定数据使得其他SQL不能访问该数据。MyISAM支持锁定表,InnoDB支持锁定行。MyISAM锁的范围比InnoDB大,所以在使用时MyISAM会比InnoDB慢一些。
  • 外键约束:一张表在数据库级别关联另一张表就是外键约束。也就是一张表中存在外键关联着另一张表。
  • 全文索引与表空间大小就顾名思义了。InnoDB表空间大小大约是MyISAM的两倍。

引擎在文件存储上的区别:

  • InnoDB:
    • 表文件夹中的*.frm文件
    • 上级目录的ibdata1文件
  • MyISAM:
    • 表文件中的*.frm文件
    • 表文件中的*.MYD文件
    • 表文件中的*.MYI文件

文件上的一些区别:

*.frm是表结构文件。(可以在SQLyog中询问DESCRIBE tableName来可视化一下表结构)表结构文件定义的是表的一些结构例如字段等,并没有表中的数据。InnoDB与MyISAM都有表结构文件。

ibdata1是共享表空间文件。在默认情况下所有的表数据都存在这个文件中,所以会导致这个文件越来越大。为解决这个问题就要为个表申请独立的表空间。方法是:在创建表时使用参数InnoDB_file_per_table。使用后就会为每个表中创建独立的*.ibd文件存储表数据。

.ibd是独立表空间文件。在MySQL8.0以后使用InnoDB引擎会直接默认创建每个表的.ibd文件不用再显示声明,并且将frm以及更多信息移动到序列化字典信息SDI中,SDI被写在ibd文件内部。

*.MYD是MyISAM的数据存储文件。

*.MYI是MyISAM的索引文件。

引擎各自的优点:

  • MyISAM:节约空间,查询速度快。
  • InnoDB:安全性高,有事务的处理,多表多用户操作。

表字符集

MySQL的默认字符集是Latin1,是不支持中文的。

设置utf8的两种方式:

  • 在创建表是选择utf8作为字符集。
  • 在my.ini文件中设置默认字符集为utf8,character-set-server = utf8。

操作表

--创建表
CREATE TABLE `tableName`(
    `ID` INT(10) NOT NULL COMMENT '用户id',
    `NAME` VARCHAR(12) NOT NULL DEFAULT '匿名' COMMENT '用户名',
    `age` INT(5) NOT NULL DEFAULT 0 COMMENT '用户年龄',
    PRIMARY KEY(`ID`)
)ENGINE = InnoDB DEFAULT CHARSET = UTF8;
--删除表
DROP TABLE IF EXISTS `tableName`;

修改表

对于操作数据库、操作表都是在宏观层次操作的,是DDL。而对于操作数据就是DML中的内容了。所以对于操作表而言也是操作表的字段属性、创建删除表、修改表名、增加字段等等操作。

--1修改表名
ALTER TABLE `tableName` RENAME AS `newTableName`;

--2增加字段(也就是列)
ALTER TABLE `tableName` ADD `age` INT(3);
--              表名         字段名 数据类型(长度)
--后面还可以跟其他属性,加空格就可以。例如:
ALTER TABLE `tableName` ADD `age` INT(3) NOT NULL DEFAULT 0 COMMENT '年龄';

--3删除字段
ALTER TABLE `tableName` DROP `fieldName`;

--4字段重命名
ALTER TABLE `tableName` change `fieldName` `newFieldName` INT(4);
--在重命名字段时必要字段属性要加上,比如数据类型。否则会报error
--重命名字段时,也可以修改字段属性。例如:
ALTER TABLE `tableName` change `fieldName` `newFieldName` VARCHAR(12) DEFAULT '3';

--5修改字段属性
ALTER TABLE `tableName` MODIFY `age` VARCHAR(12) DEFAULT '3';

外键

如果一个表中的字段要与另一个表中的字段相关联(例如学生表要与学生获奖情况表相关联,那么学生表的ID与学生获奖情况表的ID是相同的,那么可以将这两个ID关联起来增强健壮性。不至于两者分散开来后导致一个表更改后另一个表并不知道就尴尬了。)这时就要使用外键。

注意:使用外键时,相关联的两个字段,外键中数据必须含于被引用字段中数据(既然用了含于也就是数据组成了集合:不重复)。也就是说被引用字段可以有外键中没有的数据,但是外键中不能有被引用字段中没有的数据。

例如学生表与年级表相关联:

student:

idnamegradeid
0小明0
1小华2
2小李2
3小白3
4小江2

grade:

gradeidgradename
0一年级
1二年级
2一年级
3三年级
4四年级

关联起来:

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级',
    PRIMARY KET(`gradeid`)
)ENGINE = InnoDB DEFAULT CHARSET = UTF8;

CREATE TABLE `student`(
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
    `name` VARCHAR(50) NOT NULL DEFAULT '匿名' ,
    `gradeid` INT(10) NOT NULL COMMENT '年级ID'PRIMARY KEY(`id`),
    KEY `FK_gradeid`(`gradeid`),
    --使用FK_gradeid约束名约束外键gradeid <- 此gradeid是student中的字段
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
    --CONSTRAINT 约束名 FOREIGN KET (外键) REGERENCES 另一表(另一表中字段)
)ENGINE = InnoDB DEFAULT CHARSET = UTF8;

定义外键步骤:

  • 定义约束名 :KEY FK_gradeid (gradeid)
  • 约束外键并建立引用关系:CONSTRAINT FK_gradeid FOREIGN KEY (gradeid) REFERENCES grade(gradeid)

增加拥有外键表的数据时,外键字段数据必须在被引用字段中有; 增加被引用表的数据时随意增加; 删除拥有外键的表中的记录时随便删; 删除被引用表中记录时不能删除外键数据正在使用的记录(但是被引用字段中有多条一样的数据时,可以删除。因为外键数据仍含于被引用字段数据)。

上面是在创建时就定义了外键了,但是对于大多数情况而言我们是在创建好表之后再进行外键约束的。所以可以使用修改表操作中修改表的外键。

ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

注意:以上两种都是物理外键。不建议使用。(因为在删除一个表时会不让你删除产生误操)最佳实现方法是使用逻辑外键(在代码中将两个表的字段相关联)。

DML(重中之重)

​ DML数据库管理语言,数据库中数据的增删改查都是通过这类型语言完成的。

INSERT INTO `表名` (`字段1`, `字段2`) VALUES ('值1',2), ('值1',2);

在表中插入数据,列为字段1、字段2,插入数据为值1、值2。插入两条数据(数据间用括号括起来并且中间用逗号隔开)。

例如:

--在一个字段中插入一条数据
INSERT INTO `grade` (`gradename`) VALUES ('大四');

--在两个字段中插入两条数据
INSERT INTO `grade` (`gradeid`, `gradename`) VALUES (1, '大一');

--在两个字段中插入两个数据
INSERT INTO `grade` (`gradeid`, `gradename`) VALUES (1, '大一'), (2, '大二');

--不写字段时:后面值必须与所有字段按顺序一一对应
INSERT INTO `grade` VALUES (1, '大一');

注意:

  • 设置为非空的字段不可以不赋值。
  • 主键若自增可以不赋值。
  • 字段与字段之间、值之间、数据行之间要用逗号隔开;每一条数据要用括号括起来。
  • 可以不写字段,但是后面的值必须一一对应。

UPDATE `表名` SET `前字段名` =1 WHERE `后字段名` =2;

在表中,在后字段名=值2的数据中修改前字段名的值为值1。

例如:

--修改一个字段
UPDATE `grade` SET `gradename` = '大二' WHERE `gradeid` = 1;

--修改多个字段
UPDATE `grade` SET `gradename` = '大三', `gradeid` = 2 WHERE `gradeid` = 1;

--不写判断条件时:字段的所有数据行都被修改
UPDATE `grade` SET `gradename` = '大一';

DELETE FROM `tableName` WHERE `字段名` =;

在表中,删除字段名=值的数据。

例如:

DELETE FROM `student` WHERE `ID` = 2;

不能不加where直接删除表,这样会删除所有的数据。

但是如果你就是要删除所有数据,使用TRUNCATE会更好一些。

TRUNCATE的好处:

  • TRUNCATE会重新设置自增索引,也就是索引会从0开始,计数器清零。
  • TRUNCATE不会影响事务。

DQL

DQL数据库查询语言。

  • 所有数据查询都用它。
  • 简单复杂查询他都可以做。
  • 数据库最核心语言。

查询语句框架

SELECT [DISTINCT]
{table.field1, table.field2...}
FROM tablex as tx
[LEFT|RIGHT|INNER JOIN tabley as ty]
[WHERE]
[GROUP BY field]
[HAVING 聚合函数]
[ORDER BY ASC|DESC]
[LIMIT start, length]

指定查询字段

1、查询表中的所有信息

SELECT * FROM `tableName`;       

2、指定字段查询

SELECT `字段1`, `字段2` FROM `tableName`;

--查询一个字段
SELECT `studentid` FROM `student`;

--查询两个或多个字段:使用逗号隔开
SELECT `studentid`, `studentname` FROM `student`;

3、查询出的信息进行易理解处理

有时候表中的原字段名称复杂时,不好理解,但是使用小名就易理解了。

--使用AS进行起小名
SELECT `studentid` AS 学号 FROM `student`;

4、也可以指定数据进行查询

SELECT `studentid` AS 小红 WHERE `studnet` WHERE `studentid`=10;

交叉连接

值得注意的是:修改查询出的结果并不会改变数据库中的数据,只是修改的显示数据。

要注意这种查询两张表的使用方法(就是直接使用查询两张表)可以是可以但是结果并不是两张表中的字段进行两列输出,结果是两张表中的字段们进行笛卡尔积

例如:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

去重

关键字:DISTINCT

在所查询的数据中可能会有一些数据是重复的所以要对数据进行去重。去重是按照上面查询语句框架在SELECT之后直接跟的关键字。

例如:

SELECT DISTINCT filedname FROM tablename; 

where子句

在SQL中使用where子句进行条件判断。例如:

SELECT gradename FROM grade WHERE gradeid = 4;
--从grade表中查询gradeid=4的gradename字段。

where子句中的逻辑运算符:

AND;&&逻辑与
OR;||逻辑或
NOT;!逻辑非
IS NULL是空
IS NOT NULL非空
BETUEEN a AND b在a与b之间

where子句不一定非要判断查询字段中的字段,但是where判断的字段一定在语句中的表中。

模糊查询

IN关键字

x IN y(x字段的数据集合含于y字段数据集合的部分进行交叉选择)。

联表查询

联表查询是从两张或多张表中查询不同表的数据。既然要查询多张表那么多张表之间一定有联系,否则查询一张表就行了(这是在数据库设计阶段就需要思考设计的)。

联表查询步骤:

  • 确定要查询的字段
  • 确定连接的关键字
  • 确定交叉点:一般用等于,用其他会查询出笛卡尔积(后面说)

在联表查询中可以没有外键关系,字段名可以不同,但是两张表中交叉字段中数据一定要有交叉处。否则是查不到的(所有查询字段数据都为空)

联表查询关键字:LEFT | RIGHT | INNER JOIN

以下例子都使用student与result表来做示例。
在这里插入图片描述
在这里插入图片描述

lNNER JOIN(内查询)

作用:查询结果是两张表的相交处的所有数据。也就是公共部分。

内查询也是可以用交叉查询得到的。交叉查询几乎可以查询所有的东西(因为笛卡尔积可以得到所有组合)。所以内查询也可以吧on替换成where。

例如:

SELECT id, name, s.gradeid, gradename
FROM student AS s
INNER JOIN grade AS g
ON s.gradeid = g.gradeid;

//替换
SELECT id, name, s.gradeid, gradename
FROM student AS s, grade AS g
WHERE s.gradeid = g.gradeid;

在这里插入图片描述

LEFT JOIN(外查询)

作用:查询结果是左表中所有数据都有,右表中判断为真的部分存在(其他部分为NULL)

例如:

SELECT id, `name`, s.gradeid, gradename
FROM student AS s
LEFT JOIN grade AS g
ON s.id = g.`gradeid`;

在这里插入图片描述

RIGHT JOIN(外查询)

作用:查询结果是右表中所有数据都有,左表中判断为真的部分存在(其他部分为NULL)。与LEFT JOIN刚好相反。

SELECT id, `name`, s.gradeid, gradename
FROM student AS s
RIGHT JOIN grade AS g
ON s.id = g.`gradeid`;

也可以在on后面加where进行进一步的筛选。

多表查询

联表查询时不一定非要使用一次join,也可以使用多次join进行多表查询。

例如:

SELECT S.id, s.name, grade.name, c.name
FROM student AS s
INNER JOIN grade AS g
ON s.gradeid = g.gradeid
INNER JOIN class AS c
ON g.gradeid = c.gradeid;

子查询

子查询也可以实现多表查询(并且查询效率更高,减少了查询中执行的次数)。子查询就是在where中嵌套where。

--1
SELECT studentid, subjectid, studentresult
FROM result
WHERE subjectid = (
    --2
	SELECT studentid FROM student
    WHERE subjectname = "DB-1"
);

先进行2的查询再进行1的查询,查出2的结果后再与1进行查询。这样2查询是O(n)得到x条数据,然后与1进行查询是O(xy)最终是O(xy)+O(n)。使用直接多表查询时进行O(ny)。显而易见效率提高。

分页

将一张表分成好几页,数据库级别的分页。

分页的作用:

  • 缓解数据库的压力
  • 看着表好看
SELECT studentid, studentname
FROM student
LIMIT 0, 5;
--LIMIT 起点,长度; 公式为:LIMIT (n-1)*l, l;

排序

SELECT studentid, studentname
FROM student
ORDER BY studentid DESC;
--按照studentid进行排序,DESC是降序、ASC是升序

分组

分组是按照字段的数据值进行分类,同一数据值的为一类。

SELECT studentgrade, gradename
FROM student, grade
WHERE studnet.studentgrade = grade.gradeid
GROUP BY studentgrade;

函数

concat

concat用于拼接字符串。

例如:

SELECT CONCAT('姓名:', studnetname) AS 学生姓名 FROM `student`;

这只是一个函数,用于连接字符串,连接完之后还是一个字段对象。所以还可以用SELECT显示。并且这仅仅是一个函数,所以它不仅仅使用于SELECT也可以用于UPDATE。

UPDATE `student` SET `studentname`=CONCAT('名字:', `studentname`);

将名字字符与studentname字段中的字符串生成新的字段对象,然后赋值给字段对象studentname。

count

  • count(字段),得到字段中的记录的总数,但会忽略NULL的值。
  • count(*),得到表中的记录的总数,因为不关心具体值所以不会忽略某个记录中的某个NULL值。

MD5

MD5函数可以进行MD5加密。

UPDATE studnet SET studentPwd = MD5(studnetPwd);

sun

求和

avg

求平均值

max

求最大值

min

求最小值

事务

事务的概念:两条SQL要么同时成功,要么同时失败。

为什么会有事务?

在账务系统中,如转账,一条记录中数据的减少量必须等于另一条记录中数据的增加量。并且是必须两条都发生不能只发生一条。

事务的原则:ACID原则

  • 原子性:对于两个动作,要么同时成功要么同时失败,不能只发生其中一个。
  • 一致性:最终一致性(事务的两个对象总和不变,也就是前者减小量=后者增加量)
  • 隔离性:多个用户同时操作时,排除其他事务对本次事务的影响(影响包括读与写)。
  • 持久性:事务结束后数据不随外界原因导致数据丢失(事务没提交:恢复原状,事务提交了:持久化到数据库(写入数据库))。

隔离失败后会发生的错误:

  • 脏读:一个事务读取了另一个事务的数据,修改了本事务中的数据,使得本事务中的数据不正确(数据量没变)。
  • 幻读:一个事务读取到了另一个事务中的一条记录(数量增加了)。

事务的使用:(INNODB是默认事务的,也就是每一条SQL都会持久化到数据库)

  • 开启关闭自动事务
--关闭自动事务
set autocommit=0
--开启自动事务
set autocommit=1
  • 开启人为手动事务
START TRANSACTION

手动事务与自动事务的区别是:自动事务会将sql自动持久化到数据库,而手动事务只有在提交事务后才能持久化到数据库。

  • 提交
COMMIT
--将事务执行结果持久化到数据库
  • 回滚
ROLLBACK
--事务提交不成功时,将恢复原数据

示例:转账

SET autocommit = 0
START TRANSACTION
UPDATE account set money = money - 50 WHERE name='zs'
UPDATE account set money = money + 50 WHERE namw='li'
COMMIT
ROLLBACK
SET autocommit = 1;

的增加量。并且是必须两条都发生不能只发生一条。

事务的原则:ACID原则

  • 原子性:对于两个动作,要么同时成功要么同时失败,不能只发生其中一个。
  • 一致性:最终一致性(事务的两个对象总和不变,也就是前者减小量=后者增加量)
  • 隔离性:多个用户同时操作时,排除其他事务对本次事务的影响(影响包括读与写)。
  • 持久性:事务结束后数据不随外界原因导致数据丢失(事务没提交:恢复原状,事务提交了:持久化到数据库(写入数据库))。

隔离失败后会发生的错误:

  • 脏读:一个事务读取了另一个事务的数据,修改了本事务中的数据,使得本事务中的数据不正确(数据量没变)。
  • 幻读:一个事务读取到了另一个事务中的一条记录(数量增加了)。

事务的使用:(INNODB是默认事务的,也就是每一条SQL都会持久化到数据库)

  • 开启关闭自动事务
--关闭自动事务
set autocommit=0
--开启自动事务
set autocommit=1
  • 开启人为手动事务
START TRANSACTION

手动事务与自动事务的区别是:自动事务会将sql自动持久化到数据库,而手动事务只有在提交事务后才能持久化到数据库。

  • 提交
COMMIT
--将事务执行结果持久化到数据库
  • 回滚
ROLLBACK
--事务提交不成功时,将恢复原数据

示例:转账

SET autocommit = 0
START TRANSACTION
UPDATE account set money = money - 50 WHERE name='zs'
UPDATE account set money = money + 50 WHERE namw='li'
COMMIT
ROLLBACK
SET autocommit = 1;
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页