基础
SQL 结构化查询语句
SQL语句不区分大小写
SQL支持的注释:
# 注释
SELECT * FROM STUDECT; --注释
/*
注释
*/
结构化查询语句(SQL)分类:
- DDL 数据库定义语言:定义和管理数据库对象,eg:数据库、数据表等
- 命令:CREATE、DROP、ALTER
- DML 数据库操作语言:用于操作数据库对象中所包含的数据
- 命令:INSERT、UPDATE、DELETE
- DQL 数据库查询语言:用于查询数据库数据
- 命令:SELETE
- DCL 数据库控制语言:用于管理数据库的语言,包括管理权限及数据更改
- 命令:GRANT、COMMIT、ROLLBACK
数据库的基本使用语句:
--创建数据库
create database name;
--删除数据库
drop database name;
--查询全部的数据库
show databases;
--选择需要的数据库
use databaseName;
--查询当前数据库中的所有表
show tables;
--查询表中的所有信息
describe tableName;
selece * from tabeleName;
创建表
-- AUTO_INCREMENT 自增
-- COMMENT 备注
-- DEFAULT 默认
-- ENGINE-INNODB 引擎
-- CHARSET=UTF8 默认字符集
CREATE TABLE `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(16) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY(`id`)
) ENGINE-INNODB DUFAULT CHARSET=UTF8
修改表
添加列
# 新增列
alter table 表名 add 需要添加的字段
ALTER TABLE mytable ADD col CHAR(20);
删除列
ALTER TABLE mytable DROP COLUMN col;
删除表
DROP TABLE tableName;
插入
INSERT INTO mytable(col1, col2)
VALUES(val1, val2);
插入检测出来的数据:
INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;
将一个表中的内容插入到一个新表
CREATE TABLE newtable AS
SELECT * FROM mytable;
删除
drop table mytable;
更新
UPDATE mytable
SET col = val
WHERE id = 1;
查询
# 查询表中的所有列
SELECT * FROM mytable;
#查询表中指定列
#DISTINCT 相同值只会出现一次
SELECT DISTINCT col1, col2
FROM mytable;
LIMIT(分页查询)
限制返回的行数。可以有两个参数,第一个参数为起始行,默认从0开始,第二个参数为返回的总行数。
#返回前6行
SELECT * FROM mytable
LIMIT 6;
# 或者用两个参数表示返回前6行
SELECT *
FROM mytable
LIMIT 0, 6;
返回指定行
# 返回第2 ~ 6行
SELECT *
FROM mytable
LIMIT 1, 5;
排序
- 升序:ASC(默认)
- 降序:DESC
# 按多个列进行排序
SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;
过滤
(WHERE 判断)
SELECT *
FROM mytable
WHERE col IS NULL; #条件
WHERE 子句可用的操作符:
操作符 | 说明 |
---|---|
= | 等于 |
< | 小于 |
> | 大于 |
<> != | 不等于 |
<= !> | 小于等于 |
>= !< | 大于等于 |
BETWEEN | 在两值之间 |
IS NULL | 为NULL值(空字符串不等于NULL) |
AND 和 OR 用于链接多个过滤条件。
通配符
通配符也用在过滤语句中,只用于文本字段
%
:匹配大于等于0个任意字符;_
:匹配一个任意字符;[ ]
:可以匹配集合内的字符,例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定,也就是不匹配集合内的字符。
使用 Like 来进行通配符匹配。
SELECT * FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本
模糊查询
LIKE
-- LIKE结合使用的通配符 %(代表0到任意个字符),_(表示一个字符)
-- 查询姓张的同学的学号及姓名
SELECT sno,sname FROM student
WHERE sname
LIKE '张%';
#查询姓张的同学后面只有一个字的
SELECT sno,sname FROM student
WHERE sname
LIKE '张_';
# 查询姓张的同学后面只有两个字的
SELECT sno,sname FROM student
WHERE sname
LIKE '张__';
# 查询姓名中含有敏字的
SELECT sno,sname FROM student
WHERE sname
LIKE '%敏%';
IN
#查询学号为1000、1001、1002的学生姓名
SELECT sno,sname FROM student
WHERE sno IN (1000,1001,1002);
#查询地址在北京、上海、深圳的学生
SELECT sno,sname,address FROM student
WHERE address IN ('北京','上海','深圳');
NULL
#查询出生日期没有填写的同学
#不能直接写=NULL 是错误的,要用 IS NULL
SELECT sname FROM student
WHERE birthday IS NULL;
#查询出生日期填写的同学
SELECT sname FROM student
WHERE birthday IS NOT NULL;
子查询
在查询语句中的WHERE 条件子句中,又嵌套了另一个查询语句。
嵌套查询可由多个子查询组成,求解的方式是由里及外。
可以将子查询的结果作为WHERE 语句的过滤条件:
SELECT * FROM mytable1
WHERE col1 IN (SELECT col2
FROM mytable2);
#查询 数据库 的多有考试结果(学号,科目编号,成绩),并且成绩降序排列
SELECT studentno,subjectno,studentResult FROM result
WHERE subjectno=(
SELECT subjectno FROM 'subject'
WHERE subjectname = '数据库'
)
ORDER BY studentresult DESC;
链接
链接用于连接多个表,使用JOIN
关键字,并且条件语句使用ON 而不是WHERE。
链接可以替换子查询,并且比子查询的效率一般要高。
可以用AS
给列名、计算字段和表名取别名(当然也可以省略),给表名取别名是为了简化SQL语句以及连接相同表。
内连接
查询两个表中的结果集中的交集,使用INNER JOIN
关键字。
SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;
/*查询参加了考试的学生信息(学号,姓名,科目编号,分数)
分析之后会发现,确定查询的列来源于两个类:student 和 result*/
SELECT s.studentno,studentname,subjectno,studentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno;
#也可以用等值链接
SELECTT s.studentno,studentname,subjectno,studentResult
FROM student s,result r
WHERE s.studentno = r.studentno;
外连接
外链接保留了没有关联的那些行
(相对于内连接理解:比如说查询参加考试的学生信息,内连接查询的结果只有参见了考试同学的信息,而外连接,不仅会查询出来参加了考试的学生信息,没有参加考试的学生信息同样也能查询出来。)
# 使用LEFT JOIN 关键字
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno;
当然,查询参加了考试同学的信息 这种场景不用外连接,
外连接的适用场景比如说 查询缺考的同学信息。
自链接
数据表与自身进行链接
#场景:一张员工表,包含员工姓名和员工所属部门,要找出与 Jim 处在同一部门的所有员工姓名。
#子查询
SELECT name
FROM employee
WHERE department = (
SELECT department
FROM employee
WHERE name = 'Jim';
)
# 自链接
SELECT e1.name
FROM employee e1
INNER JOIN employee e2
ON e1.epartment = e2.department AND e2.name = 'Jim';
事务管理
- 事务:指一组SQL语句(将一组SQL语句放在同一批次内去执行)
- 如果一个SQL语句出错,则该批次内得所有SQL都将被取消执行
基本语法
Mysql 的事务提交默认是开启的,使用事务时应先关闭自动提交。
使用set语句来改变自动提交模式
SET autocommit = 0; -关闭
SET autocommit = 1; -开启
#开始一个事务,标记事务的起始点:
START TRANSACTION
#提交一个事务给数据库
COMMIT
#将事务回滚,事务回到本次事务的初始状态
ROLLBACK
#还原Mysql数据库的自动提交
SET autocommit = 1;
#保存点
SAVEPOINT 保存点名称 --设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名称 --回滚到保存点
RELEASE SAVEPOINT 保存点名称 --删除保存点