【SQL学习笔记】

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

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

2、安装后，重启即可

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

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

2、存储引擎层负责数据的存储和提取, ⽀持 InnoDB、MyISAM、Memory 等多个存储引擎, 现在最常⽤的存储引擎是 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。

查询条件

如：

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子句，根据列，对结果排序

多行子查询

多表连接查询

类型

内连接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相似。

约束

数据类型

定长字符串：长度固定；实际字符不足时，空格补齐；

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

日期和时间类型

日期和时间类型插入示例

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原生并不支持检查越苏，对于离散值可使用set和enum方式解决；对于连续值只能通过触发器解决。

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;