1.DDL操作表
-
添加表
CREATE TABLE 表名( 列名 数据类型 约束, 列名 数据类型 约束, ... );
CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) UNIQUE, sex CHAR(1) NOT NULL DEFAULT '男' );
-
修改表,
alter table 表名 操作(add、modify、change、drop)
--添加列,add 新列名 类型 约束 【位置(可以是first或after另一个列名)】 ALTER TABLE student2 ADD grade DOUBLE(5,2) NOT NULL AFTER sex; --修改列的类型和约束,modify 目标列名 类型 约束 ALTER TABLE student2 MODIFY NAME VARCHAR(20) NOT NULL DEFAULT'张三'; --修改某列的全部信息(替换某列),change 目标列名 新列名 类型 约束 ALTER TABLE student2 CHANGE sex gender VARCHAR(2) NOT NULL DEFAULT'男'; --删除某列,drop 目标列名 ALTER TABLE student2 DROP grade;
-
修改表名
RENAME TABLE name1 TO name2;
-
删除表
DROP TABLE teacher;
2.DML操作表中的记录(增删改)
-
插入记录,
insert into 表名 【(指定列)】 values (值,值,值…)
-- 方式一,指定列插入,其他赋为null INSERT INTO product (NAME,price,store) VALUES ('华为手机',12000.50,10); -- 方式二,对所有列进行插入,不可以空过任何一列(可以写null) INSERT INTO product VALUES (NULL,'小米手机',8000.00,5);
-
更新记录,
update 表名 set 列 = 值【, 列 = 值 …】【where 条件】
-- 若不写where,则对本列所有数据进行更改 UPDATE product SET price = 9000, store = 5; -- 用where后则只更改指定的记录 UPDATE product SET price = 7000, store = 5 WHERE NAME = '小米手机'; --所有商品涨价10元 UPDATE product SET price = price + 10;
-
删除记录
- delete from 表名 【where 条件】
- truncate table 表名
-- delete用于按条件删除数据 DELETE FROM product WHERE NAME = '小米手机'; DELETE FROM product WHERE price > 1000;
-- truncate用于删除表中全部数据,效率比delete快 TRUNCATE TABLE product;
3.DQL操作表中的记录(查询)
基本查询语法,select 列名 form 表名【where 条件】
1.查询语句整理
SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... LIMIT ...
2.查询语句拆解
- 查询表中的所有列
select * from 表名;
- 查询表中的特定列
select 列名,列名,列名... from 表名;
-
去重查询 distinct(在查询结果中去掉重复的内容,只有所有的属性都相同才会被判定为重复)
select distinct 列名,列名... from 表名;
-
别名查询 as (在查询结果中,列的名字将替换为as后的名称)
SELECT NAME, price AS '商品价格' ,store AS '库存' FROM product;
-
运算查询 as
-- 把商品名,商品价格+10查询出来 SELECT NAME,price + 10 AS new_price FROM product; -- 把语数英加起来得到总分 SELECT Chinese + math + English AS total FROM student;
-
条件查询
以下为查询语句 select 列名 from 表名 【where 条件】 中可以使用的条件
运算 符号表示 1.基本运算符 <、>、>=、<=、= 、!= 2.安全等于(判断null) <=> 3.逻辑运算符 &&(and)、||(or)、!(not)、^(xor) 4.范围 区间:
between a and b;
not between a and b;
集合:
in (x,x.x);
not in(x,x,x);5.模糊查询 like ‘xxx’;
%表示0~n个字符,表示一个字符,
如:
name like ‘张%’ 将查询到姓张的所有人,
neme like '张’ 将查询到姓张且姓名只有两个字的人6.null处理 xx is null;
xx <=> null;
xx is not null;-- 查询价格在1000~9000或者小于10的商品 SELECT * FROM product WHERE price BETWEEN 1000 AND 9000 OR price < 10;
-
排序查询
排序操作写在查询操作之后,先查询再排序。
排序语句 :order by 列名 【asc|desc】,列名 【asc|desc】…
asc为升序,desc为降序,不进行设置的话默认升序。
SELECT * FROM product ORDER BY price DESC;
如果对多个列进行排序,则先按照列1进行排序,若列1相等再依次按照后面的列排序。
SELECT * FROM product ORDER BY price DESC, name ASC;
-
聚合函数
聚合函数 作用 max(列名) 求列的最大值 min(列名) 求列的最小值 avg(列名) 求列的平均值 count(列名) 求列中记录的个数 sum(列名) 求列的和 语法:
SELECT 聚合函数(列名) FROM 表名 【where 条件】
聚合函数会忽略null
如果不想忽略null,可以使用IFNULL语句,将null暂时作为其他值
SELECT AVG(IFNULL(score,0)) FROM student;
-
分组查询,基本查询语句后跟 group by 列 【having 条件】
-- 前面是别名查询和运算查询,省略了as,运算查询使用了聚合函数 SELECT sex '性别',AVG(score) '平均分' FROM student GROUP BY sex;
若使用了having,则分组查修结果将根据having进一步被筛选。
-
分页查询:
SELECT 列名 FROM 表名 LIMIT a,b;
a表示跳过几条数据,b表示显示几条数据
4.外键约束
主表从表的概念:
以订单为例,订单这张表通过客户编号来引用客户信息,订单称为从表,客户信息称为主表。
-
建表时添加外键约束
CREATE TABLE 表名( 列名 数据类型 约束, 列名 数据类型 约束, ... 【 CONSTRAINT 外键约束名】 FOREIGN KEY (从表列名) REFERENCES 主表名(主表被参照的列名)【 ON UPDATE 外键约束等级】【 ON DELETE 外键约束等级】 );
-
在建表后添加外键约束
ALTER TABLE 从表 ADD 【 CONSTRAINT 外键约束名】 FOREIGN KEY (从表列名) REFERENCES 主表名 (主表被参照的列名) 【 ON UPDATE 外键约束等级】【 ON DELETE 外键约束等级】;
-
删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
-
外键约束等级
外键约束等级 效果 Cascade 在主表上更新或删除数据时,从表与其匹配的数据会同步更新或删除 Set null 在主表上更新或删除数据时,从表与其匹配的数据会被设为null No action 从表中有与主表匹配的数据,则不允许对主表相应数据进行更改或删除 Restrict(默认) 同no action Set default 主表更改时,从表的外键列会被设置成默认值
5.多表查询
-
笛卡尔查询(多张表的数据没有条件地连接在一起)
SELECT 表名1.列名,表名2.列名... FROM 表名1,表名2...;
-
内连接查询
只显示满足条件的主表和从表数据的公共部分
- 隐式内连接
SELECT 表名1.列名,表名2.列名... FROM 表名1,表名2... WHERE (表1的外键 = 表2的主键);
- 显式内连接
SELECT 表名1.列名,表名2.列名... FROM 表名1 INNER JOIN 表名2 ON 条件 【WHERE 其他条件】;
-
外连接查询
-
左外连接查询
在内连接基础上保证左边表的数据全部显示
SELECT 表名1.列名,表名2.列名... FROM 表名1 LEFT OUTER JOIN 表名2 ON 条件 【WHERE 其他条件】;
-
右外连接查询sql
在内连接基础上保证右边表的数据全部显示
SELECT 表名1.列名,表名2.列名... FROM 表名1 RIGHT OUTER JOIN 表名2 ON 条件 【WHERE 其他条件】;
示例
SELECT * FROM t_product tp LEFT OUTER JOIN t_category tc ON tp.cno = tc.cid;
其中 tp 与 tc 为别名查询
-
-
union 联合查询
查修语句1 union 查询语句2 union ...;
6.子查询
子查询即查询语句的嵌套,分为 where型、from型、exists型
-
where型
where后跟随了另一个select语句
SELECT * FROM t_product WHERE price = (SELECT MAX(price) FROM t_product);
SELECT * FROM t_product WHERE price >= ALL(SELECT price FROM t_product);
SELECT * FROM t_product WHERE price >= ANY(SELECT price FROM t_product);
-
from型
类似where,在from处使用另一个select语句
-
exist型
在where后跟随exist条件(exist语句用于判断是否有记录)
SELECT ... FROM ... WHERE EXIST(SELECT ... FROM ... WHERE...);