轻松掌握SQL编程开发

之前写过MySQL的安装部署，接着就是使用MySQL，MySQL需要使用SQL语言进行操作，下面就来一起看下SQL编程。

SQL概述

SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言， 与数据直接打交道，由 IBM 上世纪70年代开发出来。后由美国国家标准局（ANSI）开始着手制定SQL标准，先后有 SQL-86 ， SQL-89 ， SQL-92 ， SQL-99 等标准。

SQL 有两个重要的标准，分别是 SQL92 和 SQL99，它们分别代表了 92 年和 99 年颁布的 SQL 标准，今天使用的 SQL 语言依然遵循这些标准。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

SQL不是某个特定数据库供应商专有的语言，是几乎所有DBMS都支持SQL，它是一种强有力的语言，灵活使用其语言元素可以进行非常复杂和高级的数据库操作。

SQL分类

SQL语言在功能上主要分为如下3大类：

DML数据操纵语言 用于添加、删除、更新和查询数据库记录，并检查数据完整性。包括：insert、update、delete、select等。

DDL数据定义语言 这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。包括：create table、alter table、drop table、create index、drop index等。

DCL数据控制语言 用于定义数据库、表、字段、用户的访问权限和安全级别。包括：grant(授予访问权限)、revoke(撤销访问权限)、commit(提交事务处理)、rollback(事务处理回退)、savepoint(设置保存点)、lock(对数据库的特定部分进行锁定)等。

SQL语言规范

SQL语言的基本规则：

  • SQL可以写在一行或多行，为了提高可读性，各子句分行写，必要时使用缩进
  • 每条命令以;或\g或\G结束
  • 关键字不能被缩写也不能分行
  • 标点符号：必须保证所有的()、单引号、双引号是成对结束的；必须使用英文状态下的半角输入方式；字符串型和日期时间类型的数据可以使用单引号表示；列的别名尽量使用双引号，而且不建议省略as

SQL的大小写规范(建议遵循)：

  • MySQL在Windows环境下是大小写不敏感的
  • MySQL在Linux环境下是大小写敏感的，数据库名、表名、表的别名、变量名是严格区分大小写的，关键字、函数名、列名(或字段名)、列的别名是忽略大小写的
  • 推荐采用统一的书写规范，数据库名、表名、表别名、字段名、字段别名等都小写，SQL关键字、函数名、绑定变量等都大写

注释：可以使用如下格式的注释结构。

# 这是单行注释，MySQL特有的方式
-- 这是单行注释，后面必须有个空格
/*这是多行注释*/

基本查询语句

select是SQL语言的基础，最为重要。

查询全部列

语法：

SELECT *
FROM 标识从哪个表中选择;

其他的一些语句操作：

-- 查看所有的库
show databases;
-- 用某库
use "databasesName";
-- 查看库中所有的表
show tables;
-- 查某表全部列
select * 
from table;
-- dual是一张伪表，可以省略
select 1+1
from dual;
-- 任何数据和null做运算，结果都为null
select 1+null;
-- 结果都为null，在mysql中单引号和双引号大部分情况下都可以相互交换使用
select 1+null,'aab'+null;

查询特定列

语法：

SELECT 标识选择哪些列
FROM 标识从哪个表中选择;

示例：

/*
列的别名：
紧跟列名或者用关键字as，别名使用双引号，便于在别名中包含空格或特殊的字符并区分大小写
*/
select id as "ID",name "Name"
from table;
-- 显示表结构
describe table;
-- 用distinct去重
select distinct department_id
from employees;
-- `字段名`，加飘号用来区分关键字和字段名
select `select`
form table;

注：SQL大小写不敏感(数据大小写是敏感的)；SQL可以写在一行或多行；关键字不能被缩写也不能分行；各子句一般要分行写；使用缩进可提高语句的可读性。

过滤操作

语法：

SELECT 字段1,字段  
FROM 表名  
WHERE 过滤条件;

示例：

/*
等于(不是==) =
大于 >
大于等于 >=
小于 <
小于等于 <=
不等于(也可以是!=) <>
*/
-- where子句紧跟from语句
select *
from table
where age=18;
/*
其他比较运算
在两个值之间(包含边界) between ... and ...
等于值列表中的一个 in(...)
模糊查询 like
空值 is null
*/
-- 或者用where age=13 or age=15;或者用where age=13 || age=15
select *
from table
where age in(13,15);
-- 或者用where age>=13 and age<=15,注意between包含边界;或者用where age>=13 && age<=15
select *
from table
where age between 13 and 15;
-- not表示不在13到15范围的所有
select *
from table
where age between 13 and 15;
-- 查询为null的
select *
from table
where age is null; --where age is not null;不为null的

模糊查询

使用关键字like可实现模糊查询，如下：

-- 查询姓名中第二个字符为a的有哪些
select *
from table
where name like '_a%'; -- %表示任意个数的任意字符,_表示一个任意的字符
-- 包含a又包含e
select *
from table
where name like '%a%' and name like '%e%';
-- 查第二个字符为_的
select *
from table
where name like '_\_%'; -- \_转义字符后表示_
-- 指定转义字符
select *
from table
where name like '_$_%' escape '$'; -- 指定$为转义字符

排序操作

在过滤条件之后使用order by可实现排序功能，如下：

/*
select 字段1，字段2
from 表名
where 过滤条件
order by 字段名 asc/desc, 字段名2 asc/desc ......; 默认升序
*/
-- 二级排序
-- 所有员工按照部门排序(升序)，如果部门相同再按照薪水排序(降序)
select department_id,salary
from employees
order by department_id asc,salary desc;
-- 查询job_id为FI_ACCOUNT的所有员工，薪水升3倍，并按照最新的薪水排序（降序）
SELECT job_id,salary,salary*3 "ns"
FROM employees
WHERE job_id='FI_ACCOUNT'
ORDER BY ns DESC; -- 可以按照别名进行排序

多表查询

多表查询也称关联查询，指两个或更多个表一起完成的查询操作。但前提是这些一起查询的表之间是有关系的，它们之间一定是有关联字段，这个关联字段可能建立了外键也可能没有建立外键。

多表连接分类理解

现有两个表分别如下。

男生表：

bidbnamegid
1小张a
2小李c
3小明b
4小王

女生表：

gidgnamebid
a小芳1
b小红3
c小橙2
d小青

内连接：查找所有的情侣。

bidbnamegidgidgnamebid
1小张aa小芳1
2小李cc小橙2
3小明bb小红3

左外连接：查找所有的男生及他们的女朋友。

bidbnamegidgidgnamebid
1小张aa小芳1
2小李cc小橙2
3小明bb小红3
4小王

右外连接：查找所有的女生及她们的男朋友。

bidbnamegidgidgnamebid
1小张aa小芳1
2小李cc小橙2
3小明bb小红3
d小青

满外连接：所有的男生和所有的女生。

bidbnamegidgidgnamebid
1小张aa小芳1
2小李cc小橙2
3小明bb小红3
4小王
d小青

示例：

/*
多表连接（关联）的分类：
内连接 vs 外连接
自连接 vs 非自连接
等值连接 vs 非等值连接
*/
-- sql92语法，不用
-- 查询员工的姓名及所在部门的名称
SELECT first_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;
-- sql99语法，主要用
/*
select .....
from 表名1 join 表名2
on 连接条件
join 表名3
on 连接条件
.......
where 过滤条件
order by .......
*/
-- 查询员工的姓名及所在部门的名称
-- 内连接：
-- 也是非自连接：自己这张表和其它表做连接
-- 也是等值连接
SELECT e.`first_name`,d.`department_name`
FROM employees e JOIN departments d
ON e.department_id=d.department_id;
-- 外连接：左外连接 和 右外连接
-- 左外连接
-- 获取所有的员工及员工的部门名称
SELECT e.`first_name`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`;
-- 右外连接
-- 获取所有的部门及部门中的员工
SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
-- 满外连接(mysql不支持)
SELECT e.`first_name`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
UNION -- union将两张表的数据进行合并（去重） union all不去重
SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
-- 自连接：自己这张表和自己做连接
-- 查询所有的员工姓名及管理者的姓名。
SELECT e.`first_name` 员工姓名,e2.`first_name` 管理者姓名
FROM employees e JOIN employees e2 #e当成员工表 e2管理者表
ON e.`manager_id`= e2.`employee_id`;
-- 非等值连接
-- 查询所有员工薪水的等级
SELECT e.`first_name`,e.`salary`,j.`GRADE`
FROM employees e JOIN job_grades j
ON e.`salary` BETWEEN j.`LOWEST_SAL` AND j.`HIGHEST_SAL`;

单行函数

单行函数的特点：

  • 操作数据对象
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以是一列或一个值

下面是部分单行函数示例：

/*
lower('SQL Course')：将所有的内容转成小写
upper('SQL Course')：将所有的内容转成大写
*/
SELECT LOWER('aAbCdE');
SELECT UPPER('aAbCdE');
SELECT LOWER(first_name),UPPER(last_name)
FROM employees;
/*
concat('Hello', 'World'): 字符串拼接
SUBSTR('HelloWorld',1,5): 截取子串
1：表示起始位置(注意：索引位置从1开始不是从0开始)
5：表示长度
length('HelloWorld') ：字符串长度
instr('HelloWorld', 'W') : w在字符串中的首次出现的位置
lpad(salary,10,'*') : 向右对齐
10：表示字符串的长度
*：如果长度不够用*补全
rpad(salary, 10, '*') :向左对齐
10：表示字符串的长度
*：如果长度不够用*补全
trim('H' FROM 'HelloWorld') : 去除字符串两端指定的字符
replace('abcd','b','m') : 将字符串中的b字符替换成m
*/
SELECT CONCAT('hello','longge');
SELECT CONCAT(first_name,last_name)
FROM employees;
SELECT SUBSTR('HelloWorld',2,2);
SELECT first_name,LENGTH(first_name)
FROM employees;
SELECT INSTR('HelloWorldWWWW', 'W');
SELECT LPAD(salary,10,' '),RPAD(salary,10,'*')
FROM employees;
SELECT TRIM('H' FROM 'HHHHHelloHWorldHHHHHHH');
SELECT TRIM(' ' FROM '    ello  World   ');
SELECT REPLACE('abcdefgaaaaaaaaaa','a','A');
/*
round:四舍五入
round(45.926, 2) 45.93
truncate:截断
truncate(45.926) 45
mod:求余
mod(1600, 300) 100
*/
SELECT ROUND(45.926, 2),ROUND(45.926, 0),ROUND(45.926, 1),ROUND(45.926, -1); -- 45.93,46,45.9,50
SELECT TRUNCATE(45.926, 2),TRUNCATE(45.926, 0),
TRUNCATE(45.926, 1),TRUNCATE(45.926, -1); -- 45.92,45,45.9,40
-- 结果的正页和第一个参数正负有关	
SELECT MOD(3,2),MOD(-3,2),MOD(3,-2),MOD(-3,-2); -- 1,-1,1,-1
/*
now() : 当前的日期和时间
*/
SELECT NOW();
/*
ifnull(字段名，默认值)：如果字段的内容为null那么就用默认值进行替换
*/
SELECT commission_pct,IFNULL(commission_pct,100)
FROM employees;
-- 求所有员工的工资
SELECT salary+ IFNULL(commission_pct,0) * salary
FROM employees;
/*
case表达式：
case 字段名
	when 值1 then 返回值1
	when 值2 then 返回值2
	when 值3 then 返回值3
	.......
	else 返回值
end
*/
-- 查询部门号为10,20,30的员工信息,若部门号为10则打印其工资的1.1倍,20号部门,则打印其工资的1.2倍,30号部门打印其工资的1.3倍数
SELECT department_id,salary,CASE department_id
				WHEN 10 THEN salary*1.1
				WHEN 20 THEN salary*1.2
				WHEN 30 THEN salary*1.3
			     END AS new_salary
FROM employees
WHERE department_id IN(10,20,30);
-- 在上一道题的基础上加一个其它部门为原来的2倍
SELECT department_id,salary,CASE department_id
				WHEN 10 THEN salary*1.1
				WHEN 20 THEN salary*1.2
				WHEN 30 THEN salary*1.3
				ELSE salary*2
			     END AS new_salary
FROM employees;

组函数

组函数也叫聚合函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

下面是部分组函数示例：

/*
avg()：求平均值
sum()：求和
注意：上面这两个函数运算的数据只能数值
max()：求最大值
min()：求最小值
count()：求数据的个数
*/
SELECT AVG(salary),SUM(salary)
FROM employees;
SELECT MAX(salary),MIN(salary),MAX(first_name)
FROM employees;
/*
count()：求数据的个数
count(字段名)：统计该字段不为null的数据的个数
count(*)：整张表中所有的数据有多少条
count(数值)：和count(*)效果一样,count(数值)比count(*)的效率高一些
*/
SELECT COUNT(*),COUNT(commission_pct),COUNT(1),COUNT(2)
FROM employees;
SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
-- 注意：在求平均值时并没有把为null的数据算在内
SELECT AVG(commission_pct),SUM(commission_pct)/107,SUM(commission_pct)/35
FROM employees;
-- 注意：select后面一旦出现组函数那么就不能再写其它的字段,除非该字段出现在group by的后面
SELECT first_name,AVG(salary) -- 这种写法不对
FROM employees;

group by和having

语法：

SELECT column, group_function(column)  
FROM table  
[WHERE condition]  
[GROUP BY group_by_expression]  
[ORDER BY column];

示例：

/*
分组：
	select .....
	from 表名
	where 过滤条件
	group by 字段名1，字段名2 ......
	order by .......
*/
-- 求各部门平均薪水
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;
-- 求各部门平均薪水，不包括没有部门的,结果并按照薪水降序排序
SELECT department_id,AVG(salary) a_s
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
order by a_s desc; -- 使用的是别名
-- 求不同部门中不同工种的最高薪水
SELECT department_id,job_id,MAX(salary)
FROM employees
GROUP BY department_id,job_id; -- 先按照部门分组再按照各部门中不同的工种再分组
/*
having : 用来过滤
select .....
from 表名
where 过滤条件
group by 字段名1，字段名2 ......
having 过滤条件
order by .......
where和having的区别？
1.位置：where是在group by的前面，having是在group by的后面
2.使用：where后面不能跟组函数，having后面可以跟组函数
*/
-- 求各部门中平均薪水大于5000的部门有哪些
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000
-- 求各部门中平均薪水大于5000的部门有哪些，部门不能为null
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000 AND department_id IS NOT NULL;
-- 下面的写法和上面的效果一样，下面的写法的效率更高
-- 注意：没有组函数的过滤可以放在where的后面
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > 5000;

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

示例：

/*
子查询：在一个查询语句a再嵌套另一个查询语句b，那么b查询语句叫作子查询,a查询语句叫作主查询（外查询）
子查询分类：单行子查询 vs 多行子查询
单行子查询：子查询的结果只有一行
多行子查询: 子查询的结果有多行
单行子查询使用的运算符：> >= < <= <>
多行子查询使用的运算符：in any(=any和in作用一样) all
注意：子查询中先执行子查询再执行主查询
*/
-- 谁的工资比 Abel 高?
-- 方式一：
-- 1.先查出Abel工资是多少
SELECT salary
FROM employees
WHERE last_name='Abel'; -- 11000
-- 2.再查询有哪些员工的工资比查出的工资高
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
-- 方式二：自连接
SELECT e.`last_name`,e.`salary`
FROM employees e JOIN employees e2
ON e.`salary` > e2.`salary` AND e2.`last_name`='Abel';
-- 方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name='Abel'
);
-- 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
-- 方式一：
-- 1.先查询出141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141; --ST_CLERK
-- 2.查询出143号员工的薪水
SELECT salary
FROM employees
WHERE employee_id=143; -- 2600
-- 3.查询出薪水大于2600，job_id为ST_CLERK
SELECT job_id,salary
FROM employees
WHERE job_id='ST_CLERK' AND salary>2600;
-- 方式二
SELECT job_id,salary
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id=141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
);
-- 返回公司工资最少的员工的last_name,job_id和salary
-- 方式一：
-- 1.先求出最少工资
SELECT MIN(salary)
FROM employees; -- 2100
-- 2.查询薪水为2100的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=2100;
-- 方式二：
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);
-- 多行子查询：
-- 返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
	-- 查询job_id为‘IT_PROG’部门的所有薪水
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
-- 返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
	-- 查询job_id为‘IT_PROG’部门的所有薪水
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';

库和表的操作

库的操作

相关操作示例：

-- 查看所有的库
SHOW DATABASES;
-- 选库
USE 库名;
-- 创建库
-- create database [IF NOT EXISTS] 库名 [CHARACTER SET '编码集']
CREATE DATABASE demo1;
CREATE DATABASE demo2 CHARACTER SET 'utf8'; -- 可以在mysql的安装文件my.ini中修改默认的编码集，但一般不建议修改
-- IF NOT EXISTS :如果库不存在则创建，存在则不创建
-- 如果没有该字段库存在则报错
CREATE DATABASE IF NOT EXISTS demo1;
-- 查看库的信息
SHOW CREATE DATABASE demo2;
-- 删除库
-- DROP DATABASE [if exists] 库名;
-- if exists : 如果库存在则删除，不存在则不删除
-- 如果没有该字段库不存在则报错
DROP DATABASE IF EXISTS demo6;
-- 修改库的编码集
ALTER DATABASE demo2 CHARACTER SET 'gbk';

表的操作

相关操作示例：

-- 创建表
-- 方式一（白手起家）
-- 注意：默认表的编码集和库的编码集相同
CREATE TABLE student(
sid INT,
sname VARCHAR(20)
);
-- 指定表的编码集
CREATE TABLE student2(
sid INT,
sname VARCHAR(20)
)CHARACTER SET 'utf8';
-- IF NOT EXISTS: 如果表不存在则创建，存在则不创建，如果没有该字段表存在则报错
CREATE TABLE IF NOT EXISTS student2(
sid INT,
sname VARCHAR(20)
)CHARACTER SET 'utf8';
-- 创建表
-- 方式二（基于现有的表创建新表）
-- 创建表：基于现有的表创建新表(没有数据)
CREATE TABLE st LIKE myemployees.`employees`;
-- 创建表：基于查询的结果创建成一张表
CREATE TABLE st2
SELECT employee_id,salary
FROM myemployees.employees;
-- 查看表信息
SHOW CREATE TABLE student2;
-- 查看表结构
DESCRIBE student;
DESC student;
-- 删除表
DROP TABLE st;

数据类型

MySQL中的数据类型：

类型类型举例
整数类型TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型FLOAT、DOUBLE
定点数类型DECIMAL
位类型BIT
日期时间类型YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型ENUM
集合类型SET
二进制字符串类型BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
json类型JSON对象、JSON数组
空间数据类型单值类型：GEOMETRY、POINT、LINESTRING、POLYGON;集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

在定义数据类型时，如果确定是整数，就用 INT ； 如果是小数，一定用定点数类型DECIMAL(M,D) ； 如果是日期与时间，就用 DATETIME 。这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

列的操作

相关操作示例：

CREATE TABLE person(
id INT,
NAME VARCHAR(20)
);
/*
对列进行操作：
alter table 表名 add/drop/change/modify column .......
*/
-- 添加一列
ALTER TABLE person ADD COLUMN age INT;
-- 删除一列
ALTER TABLE person DROP COLUMN age;
-- 修改列的名字
ALTER TABLE person CHANGE COLUMN age age2 INT;
-- 修改列的类型
ALTER TABLE person MODIFY COLUMN age2 VARCHAR(20);
DESC person;
-- 修改表的名字
ALTER TABLE person RENAME TO person2;

数据处理之增删改

增删改查是最基本的操作，下面看下增删改。

/*
向表中插入数据：
insert into 表名(字段名1，字段名2....) 
	values(数据1，数据2.....),(数据1，数据2.....) ......
*/
-- 插入一条数据
INSERT INTO person2(NAME,id,age2) VALUES('aaa',1,'12');
-- 插入多条数据
INSERT INTO person2(id,NAME,age2) VALUES(2,'ccc','12'),(3,'eee','12');
-- 如果插入的是全数据那么表名后面的字段可以省略,但是要注意顺序
INSERT INTO person2 VALUES(4,'fff','19');
-- 将查询的结果插入到当前表中
/*
注意：
	1.查询的字段和插入的字段的类型要匹配
	2.查询的字段的个数要和插入字段的个数要匹配
*/
INSERT INTO person2(id,NAME)
SELECT employee_id,last_name
FROM myemployees.`employees`;
-- 下面的写法不对
INSERT INTO person2(id,NAME)
SELECT *
FROM myemployees.`employees`;
/*
删除数据：
delete from 表名 [where 过滤条件]
delete from 表名：将表中所有的内容删除
*/
DELETE FROM person2 WHERE id<100;
DELETE FROM person2;
/*
修改数据
update 表名 set 字段名1=值1，字段名2=值2 .... [where 过滤条件]
*/
UPDATE person2 SET NAME='longge' WHERE id=1;
UPDATE person2 SET NAME='cangjie',age2='18' WHERE id=2;
UPDATE person2 SET NAME='canglaoshi';

事务、约束和分页

事务

事务的基本要素（ACID）：

  • 原子性：事务是一个不可分割的整体。
  • 一致性：事务开始前和结束后，数据库的完整性约束没有被破坏。
  • 隔离性：同一时间只允许一个事务请求同一数据。
  • 持久性：对数据库的所有更新将被保存到数据库。

事务的并发问题：

  • 脏读：事务a读取了事务b更新的数据，然后b回滚，那么a读到的数据是脏数据。
  • 不可重复读：事务a多次读取同一数据，事务b在事务a多次读过程中对数据做了更新并提交，导致事务a多次读同一数据时结果不一致。
  • 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样。

小结：不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务操作示例：

/*
AA给CC转账1000
AA 2000
CC 2000
代码实现：
    try{
	   开启事务
	   AA -= 1000
	   System.out.println(1/0); //遇到异常
	   CC += 1000
	   提交(一旦提交数据不能再回滚（撤销）)
    }catch(Exception e){
	事务回滚（撤销）
    }
*/
-- 禁止自动提交（开启事务）
SET autocommit=FALSE;
DELETE FROM person2;
-- 提交-一旦提交不能回滚
COMMIT;
-- 回滚
ROLLBACK;
-- 允许自动提交
SET autocommit=TRUE;
-- 清空表，不是删除表
/*
delete from 表名： 可以事务回滚
truncate TABLE 表名：不可以事务回滚
注意：如果确定数据要被清空那么可以使用truncate table因为效率高
*/
TRUNCATE TABLE person2;

约束

约束是表级的强制规定，可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

约束操作示例：

/*
有以下六种约束:
not null 非空约束，规定某个字段不能为空
unique  唯一约束，规定某个字段在整个表中是唯一的
primary key  主键(非空且唯一)
foreign key  外键
check  检查约束 （mysql不支持）
default  默认值
约束分类：列级约束 vs 表级约束
列级约束：对单独的某一列进行约束
注意：not null和default只有列级约束
表级约束：可以同时对多个列进行组合约束
*/
-- 添加约束：创建表时添加约束（掌握）
-- 添加列级约束(了解)
CREATE TABLE stu(
id INT PRIMARY KEY, -- 主键：唯一 + 非空
sname VARCHAR(20) NOT NULL, -- 非空
sid INT UNIQUE, -- 唯一
sage INT DEFAULT 10 -- 默认值
);
DESC stu;
INSERT INTO stu(id,sname,sid,sage) VALUES(1,'aa',10,18);
INSERT INTO stu(id,sname,sid,sage) VALUES(2,'bb',11,18);
INSERT INTO stu(id,sname,sid) VALUES(3,'cc',12);
INSERT INTO stu(id,sname,sid) VALUES(NULL,'ee',14);
-- 主键自增
CREATE TABLE stu2(
id INT PRIMARY KEY AUTO_INCREMENT, -- auto_increment:自增
sname VARCHAR(20)
);
INSERT INTO stu2(sname) VALUES('aa');
-- 添加表级约束
CREATE TABLE stu3(
username VARCHAR(20),
pwd VARCHAR(20),
-- constraint 索引名 UNIQUE/primary key (NAME,PASSWORD)
CONSTRAINT stu3_a_b UNIQUE (username,pwd)
);
DESC stu3;
INSERT INTO stu3(username,pwd) VALUES('111','ccc');
INSERT INTO stu3(username,pwd) VALUES('111','aaa');
/*
表创建好以后添加约束（了解）
primary key
添加约束：alter table 表名 add primary key(字段名)
修改约束：alter table 表名 modify 字段名 类型 primary key
删除约束：alter table 表名 drop primary key
*/
create table stu4(
sid int,
sname varchar(20),
sage int
);
-- 添加主键约束
alter table stu4 add primary key(sid);
-- 删除主键约束
alter table stu4 drop primary key;
desc stu4;
-- 修改主键约束
alter table stu4 modify sid int primary key;
/*
unique
添加约束：alter table 表名 add unique(字段名)
添加约束：alter table 表名 add constraint 索引名 unique(字段名)
修改约束：alter table 表名 modify 字段名 类型 unique
删除约束：alter table 表名 drop index 索引名
*/
desc stu4;
-- 添加唯一约束
alter table stu4 add unique(sage); -- 索引名默认就是字段名
alter table stu4 add constraint sut4_sname unique(sname); -- 自定义索引名
-- 删除唯一约束
alter table stu4 drop index sage;
-- 修改唯一约束
alter table stu4 modify sage int unique;
/*
外键约束：foreign key
创建表时需要先创建主表再创建从表
插入数据时先插入主表再插入从表
*/
-- 主表
CREATE TABLE dept(
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);
-- 从表
CREATE TABLE emp(
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id)  -- constraint 索引名 foreign key(当前表中的字段名) 
REFERENCES dept(dept_id) -- references 主表的表名(主表的字段名)
);
-- 插入数据
insert into emp(last_name,dept_id) values('aaa',10); -- 报错，从表中插入的部门号主表中必须存在否则报错
-- 删除数据
delete from dept where dept_id =10; -- 先删除从表再删除主表，从主表中删除部门信息必须先把从表中使用该信息的数据进行修改或删除
-- 级联删除，很危险，不要用
CREATE TABLE dept2( -- 主表
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);
CREATE TABLE emp2( -- 从表
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT,
CONSTRAINT emp_dept_id_fk_aaa FOREIGN KEY(dept_id)
REFERENCES dept2(dept_id) on delete cascade -- 加了级联删除，删除部门号时该部门的员工也会被删
);

分页

所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。

分页操作示例：

/*
select ...
from ...
where ...
group by ...
having ...
order by ...
limit ...;
limit 位置，数据的条数
*/
select *
from table
limit 0,10; -- 从0位置开始取10条
-- 分页公式：limit (当前页数-1)*每页条数，每页条数

最后，今天的内容就到这里，喜欢的话点个关注吧，下篇见！

全部评论

相关推荐

11-26 13:37
已编辑
贵阳人文科技学院 Web前端
点赞 评论 收藏
分享
不愿透露姓名的神秘牛友
11-27 23:32
点赞 评论 收藏
分享
10-18 00:17
第一拖拉机制造厂拖拉机学院 Java
又来了哈
hr天天给我发消息😅
牛客279957775号：铁暗恋
点赞 评论 收藏
分享
11-27 16:15
北京理工大学 Java
点赞 评论 收藏
分享
评论
点赞
收藏
分享

全站热榜

正在热议
# 拼多多求职进展汇总 #
236043次浏览 2038人参与
# ai智能作图 #
26343次浏览 311人参与
# 北方华创开奖 #
66693次浏览 551人参与
# 25届秋招总结 #
405751次浏览 4068人参与
# 25届机械人为了秋招做了哪些准备？ #
25966次浏览 363人参与
# 地方国企笔面经互助 #
6800次浏览 16人参与
# 阿里云管培生offer #
61413次浏览 1756人参与
# 机械求职避坑tips #
23228次浏览 248人参与
# 实习，投递多份简历没人回复怎么办 #
2438804次浏览 34731人参与
# 软件开发投递记录 #
1480495次浏览 23940人参与
# 虾皮求职进展汇总 #
88255次浏览 710人参与
# 我的实习求职记录 #
6128855次浏览 84002人参与
# 我在牛爱网找对象 #
74695次浏览 554人参与
# 机械人怎么评价今年的华为 #
157643次浏览 1350人参与
# 你觉得通信/硬件有必要实习吗？ #
54698次浏览 695人参与
# 歌尔求职进展汇总 #
42861次浏览 294人参与
# 如果可以，你希望哪个公司来捞你 #
33191次浏览 193人参与
# 如果再来一次，你还会选择这个工作吗？ #
114093次浏览 1131人参与
# 如何写一份好简历 #
618553次浏览 8723人参与
# 在职场上，你最讨厌什么样的同事 #
5940次浏览 90人参与
# 硬件兄弟们 甩出你的华为奖状 #
78385次浏览 628人参与
# 你觉得第一学历对求职有影响吗？ #
17679次浏览 155人参与
牛客网
牛客企业服务