【SQL学习笔记】

Pycharm社区版的页面中无database选项?

1、进入Setting-Pluggins窗口,输入database navigator

2、安装后,重启即可

MySQL 的架构共分为两层:Server 层和存储引擎层

1、Server 层负责建⽴连接、分析和执⾏ SQL

2、存储引擎层负责数据的存储和提取, ⽀持 InnoDBMyISAMMemory 等多个存储引擎, 现在最常⽤的存储引擎是 InnoDB,其⽀持索引类型是 B+ 树索引。


数据库

创建数据库

CREATE DATABASE [ IF NOT EXISTS] 库名;

此处添加 [ IF NOT EXISTS] 时,若存在同名数据库,不会创建库且不会报错;

不添加会报错提示,下同。

查看数据库

SHOW DATABASES;

必须是databases,复数形式。

删除数据库

DROP DATABASE  库名;

连接到数据库(需要进入特定数据库)

USE 库名;

修改数据库编码集

ALTER DATABASE 库名 CHARACTER SET = charset_name;

如:ALTER DATABASE XXX CHARACTER SET=utf8;关闭再打开数据库后,数据库编码改变。


表格

创建表

CREATE TABLE [ IF NOT EXISTS] 表名(

        列名 数据类型 约束,

        列名 数据类型 约束

[ENGINE=engine_name]|[DEFAULT] CHARACTER SET[= charset_name];

如:CREATE TABLE XXX(

        id INT,

        name VARCHAR(50),

         age INT

) DEFAULT CHARACTER SET utf-8;

查看数据库所有的表

SHOW TABLES;

查看具体表

SHOW CREATE TABLE 表名;

删除表

通过外键约束连接在一起的表不能被删除。删除表之前,需先删除约束。

DROP TABLE 表名;

复制表

CREATE TABLE 新表名 AS (SELECT * FROM 旧表名);

只复制表结构,不需要数据

CREATE TABLE 新表名 LIKE 旧表名;

修改表名

ALTER TABLE 旧表名 RENAME [TO|AS] 新表名;

添加列

ALTER TABLE 表名 ADD [COLUMN] 列定义;

如:ALTER TABLE XXX ADD COLUMN ip VARCHAR(50);

修改列名

ALTER TABLE 表名 CHANGE [COLUMN] 原列名 新列名 列定义;

如:ALTER TABLE XXX ADD COLUMN ip ip_person VARCHAR(50);

修改列类型

ALTER TABLE 表名 MODIFY [COLUMN] 列名 列定义;

ALTER TABLE XXX MODIFY COLUMN ip_person VARCHAR(20);

删除列

ALTER TABLE 表名 DROP [COLUMN] 列名;


插入数据行

INSERT INTO 表名 [(列名)] VALUES (值列表);

如:INSERT INTO XXX (id,name) VALUES (1,'aki');

注意:

1、每次插入一行,不能只插入半行或几列。

2、数据值的数据类型、精度与对应列匹配,否则MySQL可能作自动处理。

3、若列是自增列,一般不手动指定该列指定值。

4、设计表的时候指定了某列NOT NULL,但插入数据时未提供值,MySQL自动处理为一个默认值。

一次插入多行

INSERT INTO 表名 [(列名)] VALUES (值列表1), (值列表1),(值列表1);

更新数据行

UPDATE 表名 SET 列名=新值 [WHERE 条件];

如:

更新列单个元素

UPDATE XXX SET name='googole' WHERE name='aki';

更新列部分元素

UPDATE XXX SET id= id + 5 WHERE id <= 10;

更新列全部元素

UPDATE XXX SET name='googole';

删除数据行

DELETE FROM 表名 [WHERE 条件];

如:

删除所有数据

DELETE FROM;

删除符合条件数据

DELETE FROM XXX WHERE name='aki';

删除表所有数据

TRUNCATE TABLE 表名

功能类似语句DELETE FROM;

注意:

1、不能用于有外键约束的表。

2、表结构、列、约束不被改动。

3、标识列重新开始编号。

4、DELETE 按条删除数据行,TRUNCATE一次性删除数据表。

5、DELETE手动提交事务、可在事务中回滚,TRUNCATE自动提交事务、无法回滚。

查询语句

SELECT 列名|* FROM 表名 [WHERE 查询条件] [ORDER BY 排序的列名 [ASC|DESC]];

如:SELECT name FROM XXX;

注意:

1、大小写不敏感。

2、可写为一行或多行;但关键字不能跨行。

3、子语句独立行编写

计算

如:SELECT id, id+5 FROM XXX

定义列的别名

若别名中存在空格,需要使用引号

如:

SELECT name AS 姓名, FROM XXX;

SELECT name 姓名, FROM XXX;

SELECT name AS '姓 名', FROM XXX;

使用DISTINCT关键字删除重行

如:SELECT DISTINCT name FROM XXX;

不能使用DISTINCT多列排除重复

如:SELECT DISTINCT name, idcard FROM XXX;

以上语句表名name,idcard组合无重复,不能保证单列无重复

限制查询行数

SELECT 列名1,列名2,…… FROM LIMIT OFFSET,COUNT;

OFFSET指定要返回的第一行的偏移量。默认第一行偏移量为0。

COUNT指定返回最大行数。

如:

SELECT * FROM XXX LIMIT 5,6; 从偏移量为5的行开始取6行。

SELECT * FROM XXX LIMIT 6 = SELECT * FROM XXX LIMIT 0,6; 默认第一行偏移量为0。

查询条件

预算符 含义
BETWEEN …… AND…… 两值之间(包含)
IN(list) 匹配列出的值
LIKE 匹配一个字符模式
IS NULL 空值
= 相等
AND 组合条件and
OR 组合条件or
NOT 条件为假返回真值

如:

SELECT idnumber FROM XXX WHERE idnumber BETWEEN 10023 AND 50023;

SELECT idnumber FROM XXX WHERE idnumber IN(10023,20023,30023,40023,50023);

SELECT idnumber FROM XXX WHERE idnumber IS NULL;

LIKE执行通配查询

查询条件可包含文字字符或数字。

% 表示0或多个字符。

_ 表示1个字符

如:

SELECT idnumber FROM XXX WHERE idnumber LIKE ‘50023%’;

以上返回以50023开头的idnumber

SELECT idnumber FROM XXX WHERE idnumber LIKE ‘_0023%’;

以上返回第2-4位字符为0023的idnumber

排序

ASC:升序,默认升序

DESC:降序

ORDER BY语句必须放在SELECT语句整体最后

如:SELECT name, idnumber FROM XXX ORDER BY idnumber DESC;

可使用别名排序

SELECT name, id+5 id_re FROM XXX ORDER BY id_re;

可使用多列排序

先排序第一个条件,再在基础上排序第二个条件,由此进行。

SELECT name, id, idnumber, idcard FROM XXX ORDER BY idnumber ASC, idcard DESC;

聚合分组

聚合函数针对进行运算的所有记录返回唯一一个结果。

分组函数group by对记录进行分组。分组函数会省略列中的空值

COUNT:返回结果集中行的数目

COUNT(*)返回表中记录数

COUNT(列名)返 回非空记录数

SELECT COUNT(计数范围) FROM 表名;

SUM:返回结果集中所有值的总和

AVG:返回结果集中所有值的平均值

MAX:返回结果集中所有值的最大值,可用于日期类型

MIN:返回结果集中所有值的最小值,可用于日期类型

IFNULL函数强制分组函数包括空值

如:SELECT AVG(IFNULL(id, 0))        使用0代替空值进行计算

SELECT后出现聚合函数,该位置只能出现其他聚合函数分组依据列,普通列仅显示第一行值。

如:SELECT idcard,name, AVG(id) FROM XXX GROUP BY idcard

此处普通列name仅显示第一行值,无具体意义

HAVING子句对分组进行过滤

        放在GROUP BY后面,ORDER BY 之前

SELECT idcard, AVG(id) FROM XXX GROUP BY idcard HAVING SUM(idcard) > 1000;

语句执行顺序

SELECT AVG(列名) FROM XXX

WHERE 条件

GROUP BY 列名

HAVING 条件

ORDER BY 列名 ASC/DESC

LIMIT OFFSET,COUNT

①先执行FROM子句,从表中加载数据,在内存形成一张虚拟表

②若有WHERE子句,根据条件,在虚拟表中去掉不满足条件行

③根据GROUP BY子句指定的分组列,进行分组

④计算分组中SELECT子句中聚合函数值,并为每组生成查询结果中的一行

⑤若有HAVING子句,根据条件,分组计算、聚合计算的结果再次过滤

⑥若有ORDER BY子句,根据列,对结果排序

多行子查询

运算符 解释
IN 在子查询中
ANY 其中一个值满足条件返回True
ALL 其中任意值满足条件返回True

多表连接查询

类型

内连接INNER JOIN

外连接LEFT JOIN 和 RIGHT JOIN

内连接

在WHERE子句或者JOIN ON中写连接条件。利用主键与外键相等的特性。

SELECT table1.column, table2.column

FROM table1, table2

WHERE  table1.column = table2.column;

SELECT table1.column, table2.column

FROM table1 INNER JOIN table2

ON  table1.column = table2.column;

连接查询必须指定连接条件,否则会形成笛卡尔积结果。

多表连接必须限定列名,即table.column。

外连接

LEFT JOIN中,会返回左表的所有行,即使坐标中有不符合连接条件的记录,也会在查询结果中显示。RIGHT JOIN相似。


约束

数据类型

类型 精度范围与格式 用途
INT(n) (-2147483648, 2147483647) (0,4294967295) 整数
CHAR(n)

0 - 255

字符型 定长字符
VARCHAR(n) 0 - 65535 字符型 不定长字符
FLOAT (-3.40E-38,3.40E+38) 7位小数 单精度浮点数
TEXT 0 - 65535 文本型 大容量文本
BLOB 65K 二进制文件 二进制大对象

定长字符串:长度固定;实际字符不足时,空格补齐;

变长字符串:最长长度固定;实际长度根据实际存储值确定;

日期和时间类型

类型 格式 范围
YEAR YYYY 1901-2155
DATE YYYY-MM-DD 1000-01-01 - 9999-12-31
TIME HH:MM:SS -835:59:59 - 838:59:59
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 - 2038
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 - 9999-12-31 23:59:59

日期和时间类型插入示例

CREAT TABLE XXX(

        id INT AUTO_INCREMENT PRIMARY_KEY,

        reg_datetime DATETIME,

        reg_time TIME,

        reg_date DATE,

        reg_year YEAR

);

INSERT INTO XXX VALUES(1,'2000-01-01 23:23:23','09:09:09','2000-01-02','2000');

创建主键

1、直接在字段定义后面声明主键

CREATE TABLE XXX(

        id INT PRIMARY KEY,

        ……

);

2、用constraint声明主键

CREATE TABLE XXX(

        id  INT,

        name VARCHAR(10) NOT NULL,

        ……

        CONSTRAINT XXX_namep PRIMARY KEY (id,name)

        或者简写成 PRIMARY KEY (id,name)

);

3、用alter语句补充声明主键

ALTER TABLE XXX ADD CONSTRAINT XXX_namep PRIMARY KEY(id,name); 

删除主键

ALTER TABLE XXX DROP PRIMARY KEY

创建外键

用于强制引用完整性。

CREATE TABLE XXX_DEMO(

        cid  INT,

        sid  INT,

        ……

        FOREIGN KEY (sid) REFERENCES XXX (sid)

); 

指定外键删除或更新的行为

1、若子表试图创建一个父表中不存在的外键值,InnoDB会拒绝任何INSERT或UPDATE操作

2、若父表试图UPDATE或DELETE任何子表中存在的外键值,最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。

InnoDB支持四种不同的动作(MyISAM),若没有指定ON UPDATE和ON DELETE,默认动作RESTRICT。

CASCADE:父表删除或更新对应行,同时自动删除或更新子表匹配的行

SET NULL:父表删除或更新对应行,同时子表外键列设为空,仅外键列未设置为NOT NULL生效;否则报错

NO ACTION:InnoDB拒绝删除或者更新父表

RESTRICT:拒绝删除或者更新父表

CREATE TABLE XXX_DEMO(

        cid  INT,

        sid  INT,

        ……

        FOREIGN KEY (sid) REFERENCES XXX (sid) ON UPDATE CASCADE [ON DELETE RESTRICT]

); 

通过alter + constraint补充添加外键

ALTER TABLE XXX_DEMO ADD CONSTRAINT xxx_namef FOREIGN KEY(sid) REFERENCES XXX(sid) ON UPDATE CASCADE ON DELETE CASCADE;

删除外键

ALTER TABLE XXX_DEMO DROP FOREIGN KEY xxx_namef;

创建自增长

设定自增的列必须为主键,且一张表只能有一个自增列

CREAT TABLE XXX(

        id INT AUTO_INCREMENT PRIMARY_KEY,

        name VARCHAR(10)

);

INSERT INTO XXX VALUES(DEFAULT,'123');

创建唯一约束

CREAT TABLE XXX(

        dicard  VARCHAR(10) UNIQUE,

        ……

);

创建非空约束

CREAT TABLE XXX(

        name  VARCHAR(10) NOT NULL,

        ……

);

创建检查约束

Mysql原生并不支持检查越苏,对于离散值可使用setenum方式解决;对于连续值只能通过触发器解决。

CREAT TABLE XXX(

        sex SET('man','female'),

        arrangement ENUM('now','later')

        ……

);

创建默认值

CREAT TABLE XXX(

        tel VARCHAR(60) DEFAULT '13313313313',

        ……

);

高级特性、存储过程

变量

设置用户自定义变量

1、以@开始,形式为“@变量名”

SET @demo="hello";

查询变量

SELECT @demo;

2、通过SELECT语句定义

SELECT @demo:="hello"

SELECT ''hello" INTO @demo

使用变量

SELECT name FROM XXX WHERE name = @demo;

存储过程

存储在数据库目录中的一段声明性SQL语句。

优点

·有助于提高应用程序性能。

·有助于减少应用程序和数据库服务器间流量,不必发送冗长的SQL语句到数据库,只需要传递存储过程的名称和参数。

·对任何应用程序都是可重用的和透明的。

·存储的程序是安全的,支持权限控制

定义存储过程

创建成功后,若使用Navicat工具,函数一栏中会出现存储过程名。

DELIMITER //

CREATE PROCEDURE 存储过程名字()

        BEGIN

        ___代码

        END //

DELIMITER;

如:

DROP PROCEDURE IF EXISTS getname;        若存在同名存储过程名,则删除

DELIMITER //

CREATE PROCEDURE getname()

        BEGIN

        SELECT name FROM XXX;

        END //

DELIMITER;

CALL getname;                调用存储过程

声明变量

DECLARE var_name datatype(size) DEFAULT default_value;

变量赋值

SET 变量名 = 变量值        注:此处无@

SELECT 变量值 INTO 变量名 FROM XXX

带参数的存储过程

三种模式

IN——默认模式。调用存储过程时,必须将参数传递给存储过程。IN参数值不能被改变。

OUT——可以更改OUT参数值

INOUT——可以传递参数,并在存储过程中修改INOUT参数

如:

DROP PROCEDURE IF EXISTS getname;

DELIMITER //

CREATE PROCEDURE getname(IN idnum INT(10), OUT demo_name VARCHAR)

        BEGIN

        SELECT name INTO demo_name FROM XXX WHERE id > idnum;

        END //

DELIMITER;

在存储过程中定义的参数,MYSQL中自动生成以下划线命名的参数

CALL getname(10,@_demo);

SELECT @_demo;

流程控制

可以在存储过程中加入流程控制

IF判断

IF 表达式1 THEN

……

ELIF 表达式2 THEN

……

ELSE

……

END IF;

CASE条件

CASE 表达式

WHEN 表达式1 THEN ……

……

ELSE ……

END CASE;

循环

REPEAT

循环体

UNTIL 条件

END REPEAT;

WHILE 条件 DO

        循环体

END WHILE;

循环跳出

ITERATE:类似continue

LEAVE:类似break

flag: WHILE 条件 DO

        ITERATE flag;

        LEAVE flag;

END WHILE flag;

触发器

一种特殊的存储过程,不由用户直接调用。主要用于强制复杂的业务规则或要求。

特点
·与表紧密相连,可以看作表定义的一部分。

·不能通过名称直接调用,不允许带参数;当用户对表中数据进行修改时,自动执行。

·可以用于MySQL约束、默认值和规则完整性检查,实施更为复杂的数据完整性约束。

创建

CREATE TRIGGER 名

trigger_time trigger_event ON 表名 FOR EACH ROW

BEGIN

__代码

END

trigger_time:{BEFORE|AFTER}

trigger_event:{INSERT|UPDATE|DELETE}

注意:

不能有返回值和返回结果集

MYSQL的触发器不能对本表进行INSERT\UPDATE\DELETE操作,以免递归循环触发

old和new的使用

使用INSERT时,原表无数据,插入数据后,新插入数据是new。

使用UPDATE时,修改原表数据的时候,原表数据是old;修改后的数据是new。

使用DELETE时,删除的那一条数据是old。

删除

DROP TRIGGER [IF EXISTS] 名;

查看触发器

SHOW TRIGGERS;

示例:

向emp表插入新数据时,若com大于sal,则将com设置为和sal一样的值。

CREATE TRIGGER trigger_demo BEFORE INSERT  ON emp FOR EACH ROW

BEGIN

IF new.com > new.sal THEN

        SET new.com = new.sal

END IF;

END

删除dept表中还有员工的部门,提示错误提示信息

CREATE TRIGGER delete_demo BEFORE DELETE ON dept FOR EACH ROW

BEGIN

IF old.deptno<>40 THEN

        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = "不能删除还有员工的部门"

END IF;

END

数据库事务

事务是作为单个逻辑单元执行的一系列操作。

多个操作 作为一个整体向系统提交,要么执行、要么都不执行,事务是一个不可分割的工作逻辑单元。

特别适用多用户同时操作的数据通信系统。

InnoDB:不能结构化编程,只能通过标记开启、提交或回滚事务。

MyISAM:不支持事务,用于只读程序提高性能。

事务的特性

原子性:

事务是不可分割的最小单元。针对操作本身

一致性:

事务处理执行前后,数据库一致的。针对操作结果

隔离性:

一个事务处理对另一个事务处理没有影响。

持久性:

事务处理的效果能被永久存储。

反过来说,事务能承受所有的失败,包括服务器、进程、通信以及媒体失败等。

事务控制语句

开始事务

START TRANSACTION;

或者

BEGIN;

提交事务

COMMIT;

对于数据库的修改是永久性的,无法回滚。

事务回滚

ROLLBACK;

上一篇:linux udev详解