数据库基本
基本的查询语句
基本查询:SELECT 列名 FROM 表名
关键字:
SELECT 指定要查询的列
FROM 指定要查询的行
查询所有列
1、使用*的方式
2、全列名查询
注意:生产环境下优先使用全列表名查询。*需要转换成全列名,效率低,可读性差
运算符
对列中的数据进行运算
运算符 作用
+ 加法
- 减法
* 乘法
/ 或 DIV 除法
% 或 MOD 取余
注意:%是占位符,而非模运算符。
select查出来的是一个虚拟表
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY12 FROM t_employees;这一句中SALARY12,但原表中的数据不会*12
基本操作
别名
列的别名:
列 as ‘列名’:SALARY12 AS ‘年薪’ 将salary12重命名为年薪
去重
查询结果去重:
distinct 列名:SELECT DISTINCT MANAGER_ID FROM t_employees; 将经理id重复的去除
排序
排序查询:
select 列名 from 表名 order by 排序列[排序规则]
排序规则:
ASC 对前面排序列做升序排序
desc 对前面排序列做降序排序
注意:加了[]的内容是可以省略的
单列排序:SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary ASC;
多列排序:SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary DESC,EMPLOYEE_ID ASC;
多列排序时,先按salary降序排序,如果有相同的salary数据,则这些相同salary数据的EMPLOYEE_ID按照升序排序(先写什么先排什么,有相同时
结果时排序后面的)
条件
条件查询:
select 列名 from 表名 where 条件
where关键字:在查询结果中,筛选符合条件的查询结果,条件为布尔表达式
等值判断(=):
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees WHERE salary=11000;
注意:与Java不同(==),mysql中等值判断使用=
EMPLOYEE_ID FIRST_NAME salary
----------- ---------- --------
114 Den 11000
148 Gerald 11000
174 Ellen 11000
逻辑判断(and、or、not):
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees WHERE salary=11000 AND COMMISSION_PCT=0.3 AND EMPLOYEE_ID=‘148’;
EMPLOYEE_ID FIRST_NAME salary
----------- ---------- --------
148 Gerald 11000
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees WHERE salary=11000 OR COMMISSION_PCT=0.3;
EMPLOYEE_ID FIRST_NAME salary
----------- ---------- --------
114 Den 11000
146 Karen 13500
147 Alberto 12000
148 Gerald 11000
150 Peter 10000
159 Lindsey 8000
160 Louise 7500
174 Ellen 11000
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees WHERE NOT salary<20000;
EMPLOYEE_ID FIRST_NAME salary
----------- ---------- --------
100 Steven 24000
不等值判断(>、<、>=、<=、!=、<>):
!=和<>都是不等于,!=是以前sql标准,<>是现在sql标准
区间判断(between and):
注:在区间判断语句中,小值在前,大值在后,反之得不到正确结果,但不等值判断可以大在前小在后
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees WHERE salary BETWEEN 6000 AND 6300;
EMPLOYEE_ID FIRST_NAME salary
----------- ---------- --------
104 Bruce 6000
167 Amit 6200
173 Sundita 6100
179 Charles 6200
202 Pat 6000
null值判断(is null、is not null)
1、列名 is null
2、列名 is not null
SELECT EMPLOYEE_ID,FIRST_NAME,salary,COMMISSION_PCT FROM t_employees WHERE COMMISSION_PCT IS NULL AND salary > 20000;
EMPLOYEE_ID FIRST_NAME salary COMMISSION_PCT
----------- ---------- ------ ----------------
100 Steven 24000 (NULL)
SELECT EMPLOYEE_ID,FIRST_NAME,salary,COMMISSION_PCT FROM t_employees WHERE COMMISSION_PCT IS NOT NULL AND salary > 12000;
EMPLOYEE_ID FIRST_NAME salary COMMISSION_PCT
----------- ---------- ------ ----------------
145 John 14000 0.4
146 Karen 13500 0.3
枚举
枚举查询(in(值1、值2、…)):
注:in的查询效率低,可通过多条件拼接
SELECT EMPLOYEE_ID,FIRST_NAME,salary,DEPARTMENT_ID FROM t_employees WHERE DEPARTMENT_ID IN(70,90);
EMPLOYEE_ID FIRST_NAME salary DEPARTMENT_ID
----------- ---------- ------ ---------------
100 Steven 24000 90
101 Neena 17000 90
102 Lex 17000 90
204 Hermann 10000 70
模糊
模糊查询:
like_ (单个任意字符):
列名 like ‘张_’
% (任意长度的任意字符):
列名 like ‘张%’
注意:模糊查询只能和like关键字结合使用
SELECT EMPLOYEE_ID,FIRST_NAME,salary,DEPARTMENT_ID FROM t_employees WHERE FIRST_NAME LIKE 'l%';
EMPLOYEE_ID FIRST_NAME salary DEPARTMENT_ID
----------- ---------- ------ ---------------
102 Lex 17000 90
113 Luis 6900 100
129 Laura 3300 50
159 Lindsey 8000 80
160 Louise 7500 80
168 Lisa 11500 80
SELECT EMPLOYEE_ID,FIRST_NAME,salary,DEPARTMENT_ID FROM t_employees WHERE FIRST_NAME LIKE 'l___';
(一个_代表一个长度,上面的l后又3个_)
EMPLOYEE_ID FIRST_NAME salary DEPARTMENT_ID
----------- ---------- ------ ---------------
113 Luis 6900 100
168 Lisa 11500 80
分支
分支结构查询:
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
else 结果
end
注意:通过使用case end进行条件判断,每条数据对应产生一个值(类似Java中的switch)
SELECT EMPLOYEE_ID,FIRST_NAME,salary,DEPARTMENT_ID,(注意这里有一个逗号)
CASE
WHEN salary>=10000 THEN 'A'
WHEN salary>=8000 AND salary<10000 THEN 'B'
WHEN salary>=6000 AND salary<8000 THEN 'C'
WHEN salary>=4000 AND salary<6000 THEN 'D'
ELSE 'E'
END AS 'LEVEL'
FROM t_employees;
EMPLOYEE_ID FIRST_NAME salary DEPARTMENT_ID LEVEL (部份表结果)
----------- ----------- ------ ------------- --------
100 Steven 24000 90 A
101 Neena 17000 90 A
102 Lex 17000 90 A
103 Alexander 9000 60 B
104 Bruce 6000 60 C
105 David 4800 60 D
修改数据结构
修改列的数据结构:
alter table 表名 modify column 列名 新的列的类型
例如:
student表中列name的类型是char(20),现在修改为varchar(20),sql语句如下:
alter table student modify column name varchar(20);
时间
时间查询:
select时间参数([参数列表])
经验:执行时间函数查询,会自动生成一张虚表(一行一列)
时间函数 | 描述 |
SYSDATE() | 当前系统时间(日、月、年、时、分、秒) |
CURDARE() | 获取当前日期 |
CURTIME() | 获取当前时间 |
WEEK(DATE) | 获取指定日期为一年中的第几周 |
YEAR(DATE) | 获取指定日期的年份 |
HOUR(TIME) | 获取指定时间的小时值 |
MINUTE(TIME) | 获取时间的分钟值 |
DATEDIFF(DATE1,DATE2) | 获取DATE1和DATE2之间相隔的天数 |
ADDDATE(DATE,N) | 计算DATE加上N天后的日期 |
注意:调用这些函数时要写完年月日,且日期存在(获取分钟值可以不用写秒)
SELECT WEEK(‘2020-5’);#null
SELECT WEEK(‘2020-9-1’);#35
SELECT YEAR('2021-2-2');#2021
SELECT YEAR('2021-2-29');#NULL
SELECT YEAR('2021-2');#NULL
字符串
字符串查询:
SELECT 字符串函数([参数列表])
1、LOWER(str):将字符串参数值转换为全小写字母后返回
2、UPPER(str):将字符串参数值转换为全大写字母后返回
3、CONCAT(str1, str2,…):将多个字符串参数首尾相连后返回
4、INSERT(str,pos,len,newStr):将str中指定pos位置开始len长度的内容替换为newStr
5、SUBSTRING(str,num,len):将str字符串指定num位置开始截取len个内容
CONCAT(str1, str2,...):将多个字符串参数首尾相连后返回
SELECT FIRST_NAME,LAST_NAME FROM t_employees;
FIRST_NAME LAST_NAME
----------- -------------
Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst
David Austin
SELECT CONCAT(FIRST_NAME,LAST_NAME) AS ‘name’ FROM t_employees;
name
------------------
StevenKing
NeenaKochhar
LexDe Haan
AlexanderHunold
BruceErnst
DavidAustin
INSERT(str,pos,len,newStr):将str中指定pos位置开始len长度的内容替换为newStr
注意:insert中下标从1开始,不是0
SELECT INSERT(‘这是一个数据库’,3,2,‘MySQL’);
INSERT('这是一个数据库',3,2,'MySQL')
---------------------------------------------
这是MySQL数据库
LOWER(str):将字符串参数值转换为全小写字母后返回
SELECT LOWER('MYSQL');
lower('MYSQL')
----------------
mysql
UPPER(str):将字符串参数值转换为全大写字母后返回
SELECT UPPER(‘mysql’);
upper('mysql')
----------------
MYSQL
SUBSTRING(str,num,len):将str字符串指定num位置开始截取len个内容
SELECT SUBSTRING('JavaMySQLOracle',5,5);
substring('JavaMySQLOracle',5,5)
----------------------------------
MySQL
聚合函数
聚合函数
SELECT 聚合函数(列名) FROM 表名;
经验:对多条数据的单列进行统计,返回统计后的一行结果
注意:聚合函数会自动忽略null值,不进行统计
聚合函数 | 说明 |
SUM() | 求所有行中单列结果的总和|
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
COUNT() | 求总行数 |
SUM()求所有行中单列结果的总和
SELECT SUM(SALARY) FROM t_employees;
SUM(SALARY)
-------------
691400
AVG()平均值
SELECT AVG(SALARY) FROM t_employees;
avg(SALARY)
-------------------
6461.682242990654
MAX()最大值 MIN()最小值
SELECT MAX(SALARY+0) FROM t_employees; SELECT MIN(SALARY+0) FROM t_employees;
MAX(SALARY+0) Min(SALARY+0)
--------------- ---------------
240000 2100
COUNT() 求总行数
SELECT COUNT(EMPLOYEE_ID) FROM t_employees;
count(EMPLOYEE_ID)
---------------
107
#统计有提成的人数
SELECT COUNT(COMMISSION_PCT) FROM t_employees;
COUNT(COMMISSION_PCT)
-----------------------
35
基本操作
分组查询
分组查询
分组查询:
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列);
关键字:GROUP BY 说明:分组依据,必须在WHERE之后生效
SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID) AS '部门总人数' FROM t_employees GROUP BY DEPARTMENT_ID;
DEPARTMENT_ID 部门总人数
------------- -----------------
90 3
60 5
100 6
30 6
50 45
80 34
(NULL) 1
10 1
20 2
40 1
70 1
110 2
SELECT DEPARTMENT_ID,AVG(SALARY) AS '各部门平均工资' FROM t_employees GROUP BY DEPARTMENT_ID;
DEPARTMENT_ID 各部门平均工资
------------- -----------------------
90 19333.333333333332
60 5760
100 8600
30 4150
50 3475.5555555555557
80 8955.882352941177
(NULL) 7000
10 4400
20 9500
40 6500
70 10000
110 10150
#常见问题
SELECT DEPARTMENT_ID,COUNT(1),FIRST_NAME FROM t_employees GROUP BY DEPARTMENT_ID;
这里的COUNT(1)是统计行数的聚合函数,统计所有id一致的行数,但FIRST_NAME不是,只能记录一个名字
注:分组查询中,select显示的列只能是分组依据列,或者聚合函数列,不能出现其他列
DEPARTMENT_ID COUNT(1) FIRST_NAME
------------- -------- ------------
90 3 Steven
60 5 Alexander
100 6 Nancy
30 6 Den
50 45 Matthew
80 34 John
(NULL) 1 Kimberely
10 1 Jennifer
20 2 Michael
40 1 Susan
70 1 Hermann
110 2 Shelley
分组过滤
分组过滤查询:
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则;
关键字:HAVING 过滤规则 说明:过滤规则定义对分组后的数据进行过滤
#统计60、70、90部门工资最高的
SELECT DEPARTMENT_ID,MAX(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID HAVING DEPARTMENT_ID IN (60,70,90);
DEPARTMENT_ID max(SALARY)
------------- -------------
90 24000
60 9000
70 10000
限定
限定查询
限定查询:
SELECT 列名 FROM 表名 LIMIT 起始行,查询行数
关键字:LIMIT 说明:限定查询结果的起始行和总行数
注意:起始行从0开始,代表了第一行。第二个参数代表查询的行数
SELECT * FROM t_employees LIMIT 5,5;
指从第6行开始往后查5行
经验:在分页应用场景中,起始行是变化的,但是一页显示的条数是不变的
子查询
子查询:
SELECT 列名 FROM 表名 WHERE 条件(子查询结果)
//此种方式需要2行毫无关系的代码查询
#查询工资大于bruce的工资
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';
#查询工资大于6000的
SELECT * FROM t_employees WHERE SALARY>6000;
进行整合:
SELECT * FROM t_employees WHERE SALARY>(SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce');
注意:将子查询“一行一列”的结果作为外部查询的条件,做第二次查询
子查询得到一行一列的结果才能作为外部查询的等值判断条件
子查询(作为枚举查询的条件)
子查询(作为枚举查询的条件):
将子查询“多行一列”的结果作为外部查询的枚举查询条件,做第二次查询
注意:当子查询结果集形式为多行单列时可以使用ANY或ALL关键字
#高于所有all
SELECT * FROM t_employees WHERE salary > ALL(SELECT (SALARY+0) FROM t_employees WHERE DEPARTMENT_ID = 60);
要比60部门最高的高
#高于部分any
SELECT * FROM t_employees WHERE salary > ANY(SELECT (SALARY+0) FROM t_employees WHERE DEPARTMENT_ID = 60);
比60部门最低的高即可
子查询(作为一张表)
子查询(作为一张表):
SELECT 列名 FROM (子查询的结果集) WHERE 条件;
#查询员工表中工资排名前5的员工信息
#思路一
SELECT * FROM t_employees ORDER BY (salary+0) DESC LIMIT 0,5;
#思路二
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM (SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY (SALARY+0) DESC)AS TEMP LIMIT 0,5;
EMPLOYEE_ID FIRST_NAME SALARY
----------- ---------- --------
100 Steven 24000
101 Neena 17000
102 Lex 17000
145 John 14000
146 Karen 13500
将子查询“多行多列”的结果作为外部查询的一张表,做第二次查询
注意:子查询作为临时表,为其赋予一个临时表名
合并
合并查询(了解):
SELECT * FROM 表名1 UNION SELECT * FROM 表名2
SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2
#合并2张表的结果(去除重复记录)
SELECT * FROM t_departments UNION SELECT * FROM t_jobs;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID (部份表)
------------- ------------------------------- ---------- -------------
10 Administration 200 1700
100 Finance 108 1700
110 Accounting 205 1700
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
注意:表的查询结果以第一个查询(t_departments)的结果为主,显示第一个查询的列名
SELECT * FROM t1 UNION SELECT * FROM T2;
ID NAME
------ ----------
1 ZHANGSAN
2 LISI
1 WANGWU
2 ZHAOLIU
像t1和t2表中同时添加3、aa后
SELECT * FROM t1 UNION SELECT * FROM t2;
ID NAME
------ ----------
1 ZHANGSAN
2 LISI
3 aa
1 WANGWU
2 ZHAOLIU
SELECT * FROM t1 UNION ALL SELECT * FROM t2;
ID NAME
------ ----------
1 ZHANGSAN
2 LISI
3 aa
1 WANGWU
2 ZHAOLIU
3 aa
表连接
表连接查询
表连接查询:
SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件
1、内连接查询(INNER JOIN ON):
#查询所有部门的员工信息(不包括没有部门的员工)
#如果不知道拼接条件,则会造成笛卡尔积的结果
#A{a,b} B{1,2,3} -> {a,1}{a,2}{a,3}{b,1}{b,2}{b,3}
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID
= t_jobs.JOB_ID
;
SELECT * FROM t_employees,t_jobs WHERE t_employees.`JOB_ID` = t_jobs.`JOB_ID`;
经验:在mysql中,第二种方式也可以作为内连接查询,但是不符合sql标准
而第一种属于sql标准,与其他关系型数据库通用
三表连接查询
2、三表连接查询:
SELECT EMPLOYEE_ID,t_employees.DEPARTMENT_ID,t_locations.LOCATION_ID FROM t_employees
INNER JOIN t_departments
ON t_employees.DEPARTMENT_ID
= t_departments.DEPARTMENT_ID
INNER JOIN t_locations
ON t_locations.LOCATION_ID
= t_departments.LOCATION_ID
;
在三表连接查询时,注意条件为两表共同的列,查询多个表共有的列时,需要指定时那张表的列,不然会报错
左外连接
3、左外连接(LEFT JOIN ON):
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_ID FROM t_employees
LEFT JOIN t_departments
ON t_departments.DEPARTMENT_ID
= t_employees.DEPARTMENT_ID
;
注意:左外连接,是以左表为主,依次向右匹配,匹配到,返回结果 即若该EMPLOYEE_ID,FIRST_NAME,SALARY下无DEPARTMENT_ID,则写null
匹配不到,则返回null值填充
右外连接
4、右外连接(LEFT JOIN ON):
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_ID FROM t_employees
RIGHT JOIN t_departments
ON t_departments.DEPARTMENT_ID
= t_employees.DEPARTMENT_ID
;
注意:右外连接,是以右表为主,依次向左匹配,匹配到,返回结果 即若该DEPARTMENT_ID下无EMPLOYEE_ID,FIRST_NAME,SALARY,则写null
匹配不到,则返回null值填充
DML操作
DML操作【重点】
1、新增(INSERT)
INSERT INTO 表名(列名1,列名2,列名3…)VALUES(值1,值2,值3…)
INSERT INTO t_jobs(job_id,job_title,MIN_SALARY,MAX_SALARY)
VALUES('Java_Le','Java_Lecturer',2500,9000)
SELECT * FROM t_jobs;
此查询出来后:
Java_Le Java_Lecturer 2500 9000
注意:表名后的列名和values里的值要一一对应(个数,顺序、类型)
2、修改(UPDATE)
UPDATE 表名 SET 列1=新值1,列2=新值2,… WHERE 条件;
UPDATE t_employees SET JOB_ID=‘st_man’,salary=3500 WHERE EMPLOYEE_ID=‘135’;
注意:SET后多个列名=值,绝大多数情况下都要加WHERE条件,指定修改,否则为整表更新
3、删除(DELETE)
DELETE FROM 表名 WHERE 条件;
delete删除整表对表只是清空了数据,不影响表的结构,这种删除是不彻底的
4、清空整表数据(TRUNCATE)
TRUNCATE TABLE 表名;
与DELETE不同,TRUNCATE是把表摧毁,再按照原表的格式创建一张新表
数据表操作
数据表操作
1、数据类型
Mysql支持多种数据类型,大致分为三类:数值、日期/时间和字符串(字符)类型。
2、数值类型
int(m) 4个字节 范围(-2147483648~2147483647)
float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数,d小数位
浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位
3、日期类型
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
year 年份 ‘1901/2155’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间
4、字符串类型
char(n) 固定长度,最多255个字符
varchar(n) 固定长度,最多65535个字符
blob(binary large object) 二进制形式的长文本数据
text 可变长度,最多65535个字符
都给长度为10,char存储a后会在a后面补9个空格占满10个长度,varchar根据实际存储的类型来决定占用空间的实际大小。
char和varchar类型类似,但他们的保存与检索的方式不同。他们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BLOB是一个二进制大对象,可以容纳可变数量的数据。右四种blob类型:tinyblob、blob、mediumblob和longblob。他们只是可容纳值的最大长度不同。
数据表的创建(create)
数据表的创建(create):
CREATE TABLE 表名 (
列名 数据类型 [约束],
列名 数据类型 [约束],
…
列名 数据类型 [约束]//最后一列的末尾不加逗号
)[charset=utf8]//可根据需要指定表的字符编码集
CREATE TABLE `Subject`(
subjectId INT,
subjectName VARCHAR(20),
subjectHours INT
)CHARSET=utf8;
SELECT * FROM `subject`;
INSERT INTO `Subject` VALUES (1,'Java',20);
subjectId subjectName subjectHours
--------- ----------- --------------
1 Java 20
数据表的修改
数据表的修改:
ALTER TABLE 表名 操作;
向现有表中添加一个列:
ALTER TABLE Subject
ADD greadId INT
subjectId subjectName subjectHours greadId
--------- ----------- ------------ ---------
1 Java 20 (NULL)
修改表中的列:
ALTER TABLE Subject
MODIFY subjectName VARCHAR(10);
注意:修改表中的某列时,也要写全列的名字,数据类型,约束
删除表中的列:
ALTER TABLE Subject
DROP greadId;
注意:删除列时,每次只能删除一行
修改列名:
ALTER TABLE Subject
CHANGE subjectHours classHours INT;
注意:修改列名时,在给定列新名称时,要指定列的数据类型和约束
修改表名:
ALTER TABLE Subject
RENAME sub
;
数据表的删除(DROP):
DROP TABLE 表名
约束
约束
问题:在往已创建表中新增数据时,可不可以新增两行相同列值的数据? 答:可以
如果可以,会有什么弊端? 答:数据重复多余
实体完整性约束
实体完整性约束:
表中的一行数据代表一个实体(entity),实体完整性的作用即是标识每一行数据不重复,实体唯一。
主键约束
主键约束(每个表只能定义一个主键):
PRIMARY KEY 唯一,标识表中的一行数据,此列的值不可重复,且不能为null
CREATE TABLE `Subject`(
subjectId INT PRIMARY KEY,
subjectName VARCHAR(20),
subjectHours INT
)CHARSET=UTF8;
SELECT * FROM `Subject`;
INSERT INTO `Subject` VALUES (2,'Java',20);
唯一约束
唯一约束
唯一约束(可以多次使用):
UNIQUE 唯一,标识表中的一行数据,不可重复,可以为null
自动增长列
自动增长列(要求主键列是int类型,主键列可以不用自己写入 ):
AUTO_INCREMENT 自动增长,给主键数列值添加自动增长。从1开始,每次加1.不能单独使用,和主键配合。
CREATE TABLE `Subject`(
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR(20) UNIQUE,
subjectHours INT
)CHARSET=UTF8;
SELECT * FROM `Subject`;
INSERT INTO `Subject`(subjectName,subjectHours) VALUES ('Java',25);
INSERT INTO `Subject`(subjectName,subjectHours) VALUES ('HTML',20);
INSERT INTO `Subject`(subjectName,subjectHours) VALUES ('C++',25);
subjectId subjectName subjectHours
--------- ----------- --------------
1 Java 25
2 HTML 20
3 C++ 25
域完整性约束
域完整性约束
限制列的单元格的数据正确性
非空约束
非空约束:
NOT NULL 非空,此列必须有值
subjectName VARCHAR(20) UNIQUE NOT NULL#约束subjectName列不能有空值
默认值约束
默认值约束:
default 值 为此列赋予默认值,当新增数据不指定值时,书写default,以指定默认值进行填充
subjectHours INT DEFAULT 20
INSERT INTO Subject
(subjectName,subjectHours) VALUES (‘C#’,DEFAULT);
subjectId subjectName subjectHours
--------- ----------- --------------
1 Java 25
2 HTML 20
3 C++ 25
4 C# 20
引用完整性约束
引用完整性约束:
CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)
FOREIGN KEY 引用外部表的某个列的值,新增数据时,约束此列的值必须是引用表中存在的值
#专业表
CREATE TABLE Speciality(
id INT PRIMARY KEY AUTO_INCREMENT,
SpecialName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;
SELECT * FROM Speciality;
INSERT INTO Speciality(SpecialName)VALUES('Java');
INSERT INTO Speciality(SpecialName)VALUES('H5');
#课程表
CREATE TABLE `Subject`(
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR(20) UNIQUE NOT NULL,
subjectHours INT DEFAULT 20,
specialId INT noy NULL,
CONSTRAINT fk_subject_specialId FOREIGN KEY(SpecialId) REFERENCES Speciality(id)
)CHARSET=UTF8;
注意:当两张表存在引用关系,要执行删除操作,一定要先删除从表(引用表),在删除主表(被引用表)
事务
事务(重点)
模拟转账
一、模拟转账
1、数据库模拟转账
CREATE TABLE account(
id INT,
money INT
)CHARSET=utf8;
INSERT INTO account(id,money) VALUES(1,10000);
INSERT INTO account(id,money) VALUES(2,1000);
SELECT * FROM account;
#模拟转账,账户1给账户2转账1000
UPDATE account SET money = money - 1000 WHERE id = 1;
UPDATE account SET money = moneys + 1000 WHERE id = 2;
上述代码完成了两个账户之间转账的操作
2、模拟转账错误
UPDATE account SET money = money - 1000 WHERE id = 1;
UPDATE account SET money = moneys + 1000 WHERE id = 2;//此处写成moneys会导致此句出错,但上一句会执行,1账户扣钱2账户不加钱
注意:每条sql语句都是一个独立的操作,一个操作执行完对数据库是永久性的影响
事务的概念
事务的概念:
事务是一个原子操作。是一个最小的执行单元。可以由一个或多个sql语句组成,在同一个事物当中,所有的sql语句都成功执行时,整个事物成功,有一个sql语句执行失败,整个事务都执行失败。
事务的边界:
开始:连接到数据库,执行一条DML语句。上一个事务结束后,又输入了一条DML语句,即事务的开始。
结束:
(1)提交:
a、显示提交:commit;
b、隐式提交:一条创建、删除的语句,正常退出(客户端退出链接);
(2)回滚:
a、显示回滚:rollback;
b、隐式回滚:非正常退出(断电、宕机),执行了创建、删除的语句,但是失败了,会为这个无效的语句执行回滚。
事务的原理
事务的原理:
数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段),一个事务中所有的增删改语句的执行结果都会缓存在回滚段中,只有当事务中所有
SQL语句均正常结束(commit),才会将回滚段中的数据同步到数据库。否则无论因为那种原因失败,整个事务将回滚(rollback)。
事务的特性:
Atomicity(原子性)
表示一个事务内的所有操作是一个整体,要么全部成功,要么全部失败
Consistency(一致性)
表示一个事务内有一个操作失败时,所有的更改过的数据都必须回滚到修改前状态
Isolation(隔离性)
事务查看数据操作时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看中间状态 的数据
Durability(持久性)
持久性事务完成之后,它对于系统的影响是永久性的
事务应用
事务应用:
应用环境:基于增删改语句的操作结果(均返回操作后受影响的行数),可通过程序逻辑手动控制事务提交或回滚
事务完成转账:
#1、开启事务
START TRANSACTION;
#模拟转账,账户1给账户2转账1000
UPDATE account SET money = money - 1000 WHERE id = 1;
UPDATE account SET money = money + 1000 WHERE id = 2;
SELECT * FROM account;
#如果事务语句执行成功
COMMIT;#提交
#如果事务语句执行失败
ROLLBACK;#回滚
权限管理
权限管理:
1、创建用户
CREATE USER 用户名 IDENTIFIED BY 密码;
2、授权
GRANT ALL ON 数据库.表 TO 用户名;
3、撤销授权
REVOKE ALL ON 数据库.表名 FROM 用户名;
注意:撤销授权后,账户要重新连接客户端才会生效
4、删除用户
DROP USER 用户名;
视图
视图:
概念:
视图,虚拟表,从一个或多个表中查询出来的表,作用和真实表一样,包含一系列带有行和列的数据。视图中,用户可以使用SELECT语句查询数据,也可以使用INSERT、UPDATE、DELETE修改记录,视图可以使用户操作方便,并保障数据库系统安全。
特点:
优点:
简单化,数据所见即所得
安全性,用户只能查询或修改他们所能看见的数据
逻辑独立性:可以屏蔽真是表结构变化带来的影响
缺点:
性能相对较差,简单的查询也会变得稍显复杂
修改不方便,特别是复杂的聚合视图基本无法修改
视图的创建:
CREATE VIEW 视图名 AS 查询数据源表语句;
视图的修改:
方式一:CREATE OR REPLACE NEW 视图名 AS 查询语句
方式二:ALTER VIEW 视图名 AS 查询语句
视图的删除:
DROP VIEW 视图名;
注意:删除视图不会影响原表
视图的注意事项:
视图不会独立存储数据,原表发生改变,试图也会发生改变。没有优化任何查询性能。
如果视图包含以下结构中的一种,则视图不可更新
聚合函数的结果
DISTINCT去重复后的结果
GROUP BY分组后的结果
HAVING帅选过滤后的结果
UNION、UNION ALL联合后的结果
SQL语句分类
SQL语言分类
1、数据查询语言DQL select,where,order by,group by,having
2、数据定义语言DDL create,alter,drop
3、数据操作语言DML insert,uptade,delete
4、事务处理语言TPL commit,rollback
5、数据控制语言DCL grant,revokr