建库建表
建库
CREATE DATABASE IF NOT EXISTS mall;
CREATE DATABASE IF NOT EXISTS mall DEFAULT CHARSET utf8 COLLATE utf8_general_ci;删库
DROP DATABASE mall;创建表
CREATE TABLE IF NOT EXISTS goods(
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
goods_name VARCHAR(100) COMMENT '商品名称',
price DECIMAL(10,2) DEFAULT 0.00 COMMENT '金额' (price>=0),
num int DEFAULT 0 COMMENT '数量',
des VARCHAR(255) COMMENT '说明',
`code` VARCHAR(20) COMMENT '编号',
create_date datetime COMMENT '创建日期',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;(price>=0) 用于添加字段约束
删除表
DROP TABLE IF EXISTS goods;更新表
向表中添加字段
-- 添加一个时间类型的字段
ALTER TABLE goods ADD update_date datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
-- 添加一个普通类型的字段
ALTER TABLE goods ADD COLUMN good_name varchar(20) DEFAULT NULL COMMENT '商品名称';删除列
ALTER TABLE goods DROP COLUMN des;修改字段名；修改字段属性一样的（将名字设为一样的即可）
ALTER TABLE goods CHANGE oldName newName int;更新数据库字段常用SQL语句示例
-- 指定数据库
USE demo;
-- 1.修改字段类型
ALTER TABLE member MODIFY COLUMN money int(11);
ALTER TABLE member MODIFY COLUMN money DECIMAL;
ALTER TABLE member MODIFY COLUMN money DECIMAL(10,2);
ALTER TABLE member MODIFY COLUMN money BIGINT;
ALTER TABLE member MODIFY COLUMN money BIGINT(20);
ALTER TABLE member MODIFY COLUMN money VARCHAR(255);
-- 2.修改字符集合排序方式
ALTER TABLE member MODIFY COLUMN name varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
-- 3.修改字段描述， 不能单独通过COMMENT修改注释
ALTER TABLE member MODIFY COLUMN money int COMMENT '费用';
-- 4.将字段money的名称改为fees, 不能单独修改字段名称
ALTER TABLE member CHANGE money fees int;
ALTER TABLE app_user CHANGE fees money int COMMENT '费用';
-- 5.修改表名
ALTER TABLE member RENAME TO app_user;
ALTER TABLE app_user RENAME TO member;
-- 6.修改表注释
ALTER TABLE app_user COMMENT 'app 用户信息';
-- 7.删除字段
ALTER TABLE app_user DROP vip;
-- 8.添加字段vip
ALTER TABLE app_user add vip VARCHAR(20) NOT NULL COMMENT '会员等级';
-- 9.更新自增id起始值
alter table app_user AUTO_INCREMENT=1000000;清空表
删除表数据；将自增重置，重新从1开始；会将删除日志保存；数据可以回滚；表和索引占用空间会保留
DELETE FROM goods;
ALTER TABLE goods AUTO_INCREMENT=1;清空表数据；将自增重置，重新从1开始；同时表和索引占用空间会重置；不会记录日志，所以执行速度很快；数据不能通过rollback来恢复
TRUNCATE TABLE goods;DROP TABLE 是直接删除表的所有信息。
查询相关
使用CONCAT函数对结果的字段进行拼接
CONCAT(str1, str2, ….)
SELECT goods_name, CONCAT('$', price) price FROM goods在SQL Server中可以使用+号来拼接；Oracle、PostgreSQL、Sybase可以使用 ||来拼接
去掉字段前后空格
去掉结尾的空格
SELECT RTRIM(goods_name) FROM goods;去掉开头的空格
SELECT LTRIM(goods_name) FROM goods;去掉首尾空格
SELECT TRIM(goods_name) FROM goods;字符大小写转换
转换为大写
SELECT UPPER(code) FROM goods;转换为小写
SELECT LOWER(code) FROM goods;使用SOUNDEX函数查询发音相似的数据
SELECT `code` FROM goods WHERE SOUNDEX(code)=SOUNDEX('sg');日期相关处理
提取年份
SELECT goods_name FROM goods WHERE YEAR(create_date)=2020;在PostgreSQL中使用 DATA_PART(‘year’, create_date)
Oracle、Sybase使用 DATA_PART(yy, create_date)
按日期查询
SELECT goods_name, create_date FROM goods WHERE create_date>'2020-01-01'COUNT统计函数说明
SELECT COUNT(des) FROM goods;
SELECT COUNT(*) FROM goods;
SELECT COUNT(DISTINCT des) FROM goods;COUNT(*)是直接计算行数，不会管内容； COUNT(指定具体列名)会将NULL值排除掉
DISTINCT去重
最大最小值查询
MAX和MIN函数会忽略NULL值；当字段类型不是数值或日期类型时，MAX将返回排序后的最后一条记录，MIN将返回排序后最前面的一条记录。
SELECT MAX(goods_name) FROM goods;
SELECT MIN(goods_name) FROM goods;分组GROUP BY
GROUP BY语句必须在WHERE语句之后，ORDER BY语句之前
SELECT class_name, SUM(store) sum_store FROM grades GROUP BY class_name通过group by将某个字段合并为一个
我们在分组后通过GROUP_CONCAT将其某个字段合并。
SELECT id_card, GROUP_CONCAT(city_name) as region FROM student WHERE type=1 GROUP BY id_card使用HAVING过滤分组
统计每科成绩总分，且总分数大于270的科目
SELECT class_name, SUM(store) sum_store FROM grades GROUP BY class_name HAVING sum_store>270;联结
自然联结
SELECT * FROM goods, grades WHERE store>100内联结
SELECT * FROM goods INNER JOIN grades ON(store>100)自联结
即自己和自己连接；比如：查询名字为张三所在部门的信息
SELECT * FROM grades AS g1, grades AS g2 WHERE g2.name='张三' AND g1.id=g2.id;虽然使用子查询也可以实现自联结，但是由于数据库内部处理问题，可能使用自联结的效率性能上会更好，可以根据实际情况调试后再选择用那种。
外联结
SELECT * FROM goods LEFT JOIN grades ON(store>100)
SELECT * FROM goods RIGHT JOIN grades ON(store>100)在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN；RIGHT JOIN 称为 RIGHT OUTER JOIN。
UNION组合查询
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
SELECT order_no, price FROM train_order WHERE train_order_status!='CANCEL'
UNION
SELECT order_no, price FROM hotel_order WHERE hotel_order_status!='CANCEL'注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时每条 SELECT 语句中的列的顺序必须相同。 UNION 默认会自动去重；可以使用 UNION ALL 来关闭；需要对整个结果集排序则在最后一个查询语句后面加 ORDER BY排序规则。
插入语句
每一列必须值
INSERT INTO grades VALUES(7, '语文', 'Jerry', 40)指定插入列名
INSERT INTO grades(class_name, name, store) VALUES('语文', 'Jerry', 40)INSERT SELECT 将查询结果导入
INSERT INTO grades(class_name, name, store) SELECT class_name, name, store FROM grades;SELECT INTO 将一个表的数据导出到另一个表中
SELECT * INTO grades_new FROM grades更新
UPDATE grades SET filed='' WHERE ...删除
DELETE FROM grades WHERE ...存储过程
创建存储过程
delimiter $
CREATE PROCEDURE class_grades(in className VARCHAR(100))
BEGIN
SELECT * FROM grades WHERE class_name=className;
END $执行存储过程
CALL class_grades('数学');delimiter定义结束标记为：$
CREATE PROCEDURE 创建存储过程，后面更函数名称；in表示这是个输入参数
示例：统计总分等级
delimiter $
CREATE PROCEDURE grades_rank(in uname VARCHAR(50))
BEGIN
DECLARE rank VARCHAR(50);
SET @total = (SELECT SUM(store) FROM grades WHERE `name`=uname);
IF @total>500 THEN
SET rank = '优秀';
ELSE
SET rank = '合格';
END IF;
SELECT rank;
END $
CALL grades_rank('张三');游标
DECLARE 游标名称 CURSOR FOR 查询语句
DROP PROCEDURE IF EXISTS grades_point;
delimiter $
create procedure grades_point()
BEGIN
DECLARE id_1 int;
DECLARE name_1 VARCHAR(20);
DECLARE store_1 int;
declare done int default 0;
DECLARE point CURSOR FOR SELECT id,name,store FROM grades;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN point;
posLoop:LOOP
FETCH point into id_1,name_1,store_1;
IF done=1 THEN
LEAVE posLoop;
END IF;
SELECT id_1,name_1,store_1;
END LOOP posLoop;
CLOSE point;
END $FETCH 语句接收游标返回的数据不能和上面查询语句的字段相同，否赋值失败。
