1、DDL语句
DDL语句(DataBase Defined Language 数据定义语句)
1.1数据库的定义
(1)数据库的创建 CREATE CREATE DATABASE db_name; 创建一个默认的数据库,如果存在报错,不存在则创建。CREATE DATABASE IF NOT EXISTS db_name; 创建一个默认的数据库,[IF NOT EXISTS]表示,但数据库存在时什么也不做,不存在时创建。CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET ‘utf8’; 创建编码为utf-8的数据库(2)数据库的删除 DROP DROP DATABASE db_name; 删除数据库,如果不存在报错,存在则删除。DROP DATABASE IF NOT EXISTS db_name; 删除数据库,存在删除,不存在什么也不做。(3)数据库的修改 ALTER ALTER DATABASE db_name CHARACTER SET ‘gbk’; 一般就修改数据库的编码。
1.2、数据表的定义
(1)数据表的创建 CREATE CREATE TABLE tab_name(col_name datatype [COMMENT ‘注释’],…) tab_name:表的名称,可以自定义。 col_name:列的名称,可以自定义。 datatype:列的数据类型,常见的有(int整型、float浮点型、char定长字符型、varchar变长字符型、date日期、time时间、bool布尔…) comment:给列添加注释 (2)数据表的修改 ALTER ALTER TABLE tab_name RENAME TO new_tab_name;修改表的名称 ALTER TABLE tab_name CHANGE COLUMN old_col_name new_col_name datatype;修改列的名称,old_col_name:旧的列名称,new_col_name:新的列名称,datatype:数据类型 (3)数据表的删除 DROP ALTER TABLE tab_name DROP COLUMN col_name;删除一列,col_name:列名称 ALTER TABLE tab_name ADD COLUMN col_name datatype;给表增加一列。 DROP TABLE tab_name;删除数据表,存在删除,不存在报错。 DROP TABLE IF NOT EXISTS tab_name;删除数据表,存在删除,不存在什么也不做。
2、DML语句
DML语句(Data Manipulation Language 数据操纵语言)
2.1、数据的增加 INSERT语句
INSERT 语法: INSERT [INTO] tab_name [(col_name,…)]VALUES(val,…)描述: 数据表添加数据,[(col_name,…)]可加可不加,加上是给指定的列添加数据,不加默认给所有列添加数据,VALUES后面的是具体的数据,数据个数和类型要和列的个数和类型要想对应。案例1: 案例2:
2.2、数据的删除 DELETE语句
DELETE 语法: DELETE FROM tab_name [WHERE 条件][ORDER BY …][LIMIT row_count]描述: 删除符合条件的信息,[ORDER BY …]:按照什么排序asc升序 desc倒序,[LIMIT row_count]:删除几行案例1: 案例2:
2.3、数据的修改 UPDATE语句
UPDATA 语法: UPDATE tab_name SET col_name1=expr1 [, col_name2=expr2 …][WHERE 条件][ORDER BY …] [LIMIT row_count]描述: 指定修改符合条件的数据案例:
2.4、数据的查询 SELECT语句
2.4.1、全表查询
语法: SELECT * FROM tab_name描述: 查询指定表的所有数据。案例:
2.4.2、单条件查询
语法: SELECT * FROM tab_name WHERE 条件描述: 查询该表中所有符合条件的数据案例:
2.4.3、多条件查询:并且|或者
语法: SELECT * FROM tab_name WHERE 条件1 {AND\OR} 条件2..{AND\OR}条件n描述: AND:查询同时符合多个条件的数据,OR:查询符合其中一个条件的数据案例1: 案例2:
2.4.4、比较查询
语法:SELECT * FROM tab_name WHERE 字段比较条件 描述: 查询满足比较条件的数据案例:
2.4.5、模糊查询:like搜索
语法: SELECT FROM tab_name WHERE col_name LIKE ‘%关键字%’ 或者 ‘关键字 ’描述: 查询包含关键字的数据,%:匹配任意0-n个字符,_:匹配任意一个字符案例1: 案例2: 注意: 如果查询关键字中包含 % 或 _ ,可以用转义字符 \ 将字符转移成普通字符
2.4.6、空值查询
语法: SELECT * FROM tab_name WHERE col_name IS NULL 或 IS NOT NULL描述: 查询字段是否为空的所有数据案例1: 案例2:
2.4.7、范围查询 IN
语法: SELECT * FROM tab_name WHERE col_name IN (val1,val2,val3…)描述: 查询字段满足数值中的所有数据案例:
2.4.8、范围查询 区间查询
语法: SELECT * FROM tab_name WHERE col_name BETWEEN … AND…描述: 查询字段在区间内的数据案例:
2.4.9、排序查询
语法: SELECT * FROM tab_name ORDER BY col_name [ASC\DESC]描述: 排序查询,可以一次指定一列或多列进行升序或降序案例1: 案例2:
2.4.10、分页查询
语法: SELECT * FROM tab_name LIMIT m,n描述: 查询一张表中的数据,起始id位m,查询n条案例: 公式: m = n * (page - 1) 其中page:页数
2.4.11、聚合函数
名称 描述 avg() 求取指定列的平均值 count() 求取指定列的数据总数量 max() 求取指定列中数据的最大值 min() 求取指定列中数据的最小值 sum() 求取指定列中数据的和
2.4.12、指定列查询
语法:SELECT col_name1,col_name,… FROM tab_name; 描述: 查询指定列的数据信息案例:
2.4.13、分组查询
语法: GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]描述: (1)属性名:是指按照该字段的值进行分组。(2)HAVING 条件表达式:用来限制分组后的显示,符合条件表达式的结果将被显示。(3)WITH ROLLUP:将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和。(4)一般分组查询和聚合函数一起使用 (5)分组查询和GROUP_CONCAT(col_name)一起使用,显示改组该字段的数据。案例:
2.4.14、关于查询时,关键字的顺序
SELECT
指定查询列
[FROM 表名称
(1 )[WHERE 条件]
(2 )[GROUP BY
[ASC | DESC ], ... [WITH ROLLUP]]
(3 )[HAVING 分组条件]
(4 )[ORDER BY
[ASC | DESC ] , ...]
(5 )[LIMIT ]
[PROCEDURE procedure_name (argument_list) ]
[FOR UPDATE | LOCK IN SHARE MODE ]]
2.5、多表关联查询
# 创建部门表 bumen
CREATE TABLE bumen(
`id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门编号' ,
`dname` VARCHAR (20 ) NOT NULL COMMENT '部门名称' ,
`darea` VARCHAR (20 ) NOT NULL COMMENT '办公地点' ,
`dreg_time` DATETIME NOT NULL COMMENT '创建时间' ,
`ddesc` TEXT COMMENT '描述' ,
`dremark` VARCHAR (200 ) DEFAULT '待完善备注信息' COMMENT '备注'
)
# 创建员工表 emp
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工编号' ,
NAME VARCHAR (20 ) NOT NULL COMMENT '员工姓名' ,
age INT DEFAULT 0 COMMENT '员工年龄' ,
pid INT COMMENT '上级编号' ,
reg_time DATETIME COMMENT '入职时间' ,
bm_id INT NOT NULL COMMENT '所属部门编号'
)
# 添加外键约束
ALTER TABLE emp # 修改emp表
ADD CONSTRAINT FOREIGN KEY `emp_bumen` # 增加一个名称为emp_bumen的外键约束
(`bm_id` ) REFERENCES bumen(id); # 当前emp表的bm_id关联bumen表的id字段
# 增加数据
INSERT INTO `bumen` (dname, darea, dreg_time, ddesc)
VALUES
('总经室' , '东三街101' , '2018-08-29 00:00:00' , '总经理办公室' ),
('行政' , '东三街102' , '2018-08-29 00:00:00' , '行政办公地点' ),
('人事' , '东三街103' , '2018-08-29 00:00:00' , '公司人事管理部分' ),
('后勤' , '东三街104' , '2018-08-29 00:00:00' , '公司所有后勤资源管理' ),
('财务' , '东三街105' , '2018-08-29 00:00:00' , '财务都是公司的无冕之王' ),
('教学部' , '东三街106' , '2018-08-29 00:00:00' , '教学相关管理部门' );
# 增加数据
INSERT INTO emp(NAME, age, pid, reg_time, bm_id)
VALUES
('关磊涛' , 40 , NULL , '2015-11-11' , 1 ),
('欧战民' , 45 , 1 , '2015-11-11' , 1 ),
('耿景武' , 42 , 1 , '2015-11-11' , 1 ),
('李志涛' , 35 , 3 , '2015-11-11' , 6 ),
('段菲菲' , 25 , 2 , '2015-11-11' , 2 );
2.5.1、关联查询
语法:SELECT * FROM tab_name1,tab_name,… 描述: 多张表一起查询,会重复许多数据,数据条数等于两张表数据条数的乘积。案例:
2.5.2、内连接查询
内连接 【掌握】
内部有联系,使用内连接
格式: [inner] join
结果:
从左表中取出每一条记录,去右表中与所有的记录进行匹配;
匹配必须是某个条件是左表中与右表中相同,才会保留结果,否则不保留;
基本语法:
左表 [inner] join 右表
on 左表.字段 = 右表.字段;
注意事项:
1 、on 表示连接条件;条件字段代表相同的业务含义
(如 stu.cid 和 cla.id )
学生stu表中的班级cid 和 班级cla表中的id
2 、内连接可以没有连接条件,没有on 之后的内容,
这时候系统会保留所有结果(笛卡尔集)
3 、内连接可以使用where 代替 on
通常不用where ,因为where 没有on 效率高
on 指匹配到第一条成功的就结束,其他不匹配;
若没有,不进行匹配
而where 会一直匹配,进行判断
4 、通常使用字段别名 表别名
在查询数据的时候,不同表有同名字段,
这时候需要加上表名才能区分,
而若表名太长,使用表别名
若不想使用默认的字段名 可以通过[as ] 设置字段别名
2.5.3、外链接查询
2 、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:
1 )LEFT JOIN 或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
2 )RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
3 )FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
2.6、子查询
子查询: 子查询是一个查询语句嵌套着另外的查询语句,用来进行一定层次的查询,其中子查询相当于第一步查询过滤,外查询就是最后得到结果的查询。经常会用到关键字:ANY、SOME、ALL、IN、EXISTS
# 首先创建两张表
# 创建studen表
CREATE TABLE student(
sid INT AUTO_INCREMENT PRIMARY KEY ,
sname VARCHAR (20 ),
smajor VARCHAR (20 )
)DEFAULT CHARSET 'utf8' ;
# 创建成绩表
CREATE TABLE grade(
gid INT PRIMARY KEY ,
gcourse VARCHAR (20 ),
gcore INT
)DEFAULT CHARSET 'utf8' ;
# 插入数据
INSERT INTO student (sname,smajor)
VALUES
('tom' ,'CS' ),
('jerry' ,'MS' ),
('shuke' ,'SE' ),
('xiha' ,'CS' );
# 插入数据
INSERT INTO grade (gid,gcourse,gcore)
VALUES
(1 ,"Math" ,90 ),
(2 ,"English" ,89 ),
(4 ,"chinese" ,78 ),
(5 ,"Physical" ,92 ),
(6 ,"Phiosophy" ,87 );
1、关键字ANY、SOME子查询 描述: 关键字ANY、SOME子查询:ANY/SOME都是表示满足其中一个条件即可。案例:
2、ALL关键字查询 描述: 与ANY/SOME相反,是要满足所有的条件才行。案例:
3、IN关键字查询 描述: 若外查询想要查询的结果在子查询中,则返回true,继续查询到结果,否则返回false,查询为NULL。注意: 与ANY类似,但是很不相同,IN后面可是子查询,也可以是给定的集合。ANY后面必须为子查询,且前面必须有运算符(<、>、=)案例1: 案例2:
4、关键字EXISTS查询 描述: 表示在EXISTS后面的查询结果是否有结果,而不在乎子查询返回什么样的结果。若子查询的结果至少有一行,则为True,若子查询结果为NULL,则返回False,外层查询不再进行。案例:
2.7、合并查询
UNION与UNION ALL 描述: 合并查询主要是UNION与UNION ALL 两个,是将查询结果合并,但是必须满足:合并的列的数据与数据类型必须相同。案例1: 案例2: