前 言
mysql
mysql语法--本篇学习都是通过使用Navicat Premium（数据库管理工具），连接mysql数据.
本篇学习主要有两个部分：
【SQL语句的组成】
DML 数据操作语言(插入、删除和修改数据库中的数据)INSERT UPDATE DELETE
DQL 数据查询语言(用来查询数据库中的数据)SELECT
DCL 数据控制语言(用来控制存取许可、存取权限等)CREATE
DDL 数据定义语言(用来建立数据库、数据库对象和定义表的列)CREATE DROP
【常用的函数】
数学函数
字符串函数
常用日期函数
系统信息函数
加密函数
多行函数
【综合操作】
分组操作
限制行数
内连接
自连接以下所有代码全部在新建查询表里面使用mysql语法编辑。
| 1、DML 数据操作语言（INSERT UPDATE DELETE） |
INSERT [INTO] 表明 [列名] VALUES (列值)[,(列值),……];
① 如果省略列名，则后面的列值必须为所有的列复制，包括自增列和默认列；
② 如果不省略列名，则后面的列值需要与前面的列名一一对应；并且需要给虽有非null列复制
② 数据更新语句 update
update 表明 set 列名=列值[,列名=列值,……] [where 条件]
where 条件可以省略，但是表示修改表中所有的行
③ 删除表中数据 delete
delete [FROM] 表名 [where 条件]
-- 选中mydb数据库
USE mydb; -- 在mydb中建立一个名字为tb1表单
-- 内有三个属性，分别为id,username,age,sex
CREATE TABLE IF NOT EXISTS tb1(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) NOT NULL,
age INT DEFAULT 0,
sex enum("男","女") DEFAULT "男"
); -- ↓给表tb1添加一行数据
INSERT INTO tb1 (username,age,sex) VALUES ("张三","122","女"); -- ↓给表tb1添加一行数据 age和sex 没有数据
INSERT INTO tb1 (username) VALUES ("李四"); -- ↓给表tb1添加一行数据id为7的行中添加数据，如果有id7,依然在表后部添加
insert tb1 VALUES (7,"王二",12,"女"); -- 添加多行数据
INSERT INTO tb1 (username,age,sex) VALUES ("张四",12,"女"),("张五",12,"女"),("张六",12,"女"); -- 更新 where 后面的条件的 行内数据
UPDATE tb1 SET username="帅哥",age=18,sex="男" where id=8;
UPDATE tb1 SET username="王五",age=18,sex="男" where id=7;
-- 删除ID为10 的那行数据
delete from tb1 WHERE id=10;
| 2、DQL 数据查询语言 select |
数据查询语言 select：
-- select 列名[,列名,……] from 表名 where 条件 order by 排序的列名 asc/desc
-- ① select * 表示查询所有字段；
-- ② order by 表示对查出的数据进行排序，必须在where后面；
-- asc 表示升序排列 desc 表示降序排列
-- 列表达式
-- 根据已有的列查询出来的结果，我们使用case结构虚拟出来的列，称为列表达式
-- as 关键字
-- 用于给列名其别名，as关键字还可以省略。
-- 显示表格的全部内容
SELECT * FROM tb1; -- 显示表格的age列 不为null的内容
SELECT * from tb1 where not ISNULL(age); -- 查询tb表中的所有数据 按id 降序排流
select * from tb1 order by id desc ; -- 查询tb表中的所有数据 按id 升序排流
select * from tb1 order by id asc ; -- 列表达式
-- 根据已有的列查询出来的结果，我们使用case结构虚拟出来的列，称为列表达式 -- as 关键字
-- 用于给列名其别名，as关键字还可以省略。 select username as "姓名",sex "性别",age,( -- 给username列起别名"名字",sex起别名"性别" CASE -- case 表示判断的开始
when sex="男" then 1 -- 如果查询出sex 是男，则虚拟的列表达式的值就是1
when sex="女" then 2
else 3 -- 上面所有的wen都不成立时，列表达式的值为3
END -- END 表示判断的结束 ) as sexno -- 给细腻出的这一列，起别名叫 sexno
from tb1; -- DISTINCT 对查询之后的结果去重。
select distinct age from tb1;
| 3、数据控制语言/数据定义语言（CREATE DROP） |
【CREATE】：
-- 创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 主机名可以为空，为空默认为%权限，表示所有主机可连接。
-- 给用户分配权限： GRANT 权限名 ON 数据库名.表明 TO 用户名@主机名
-- 给用户分配权限： GRANT 权限名 ON 数据库名.表明 TO 用户名@主机名
-- 创建数据库： CREATE DATABASE [IF NOT EXISTS] 数据库名[CHARACTER SET[=] 'UTF8'];
-- 如果省略 [IF NOT EXISTS] 在重复创建数据库时，会报错！
【DROP】：
-- 关闭数据库 DROP DATABASE [IF EXISTS] 数据库
-- 如果省略 [IF EXISTS] 在不存在关闭数据库时，会报错！
CREATE TABLE IF NOT EXISTS tb1(
-- IF NOT EXISTS 可以省略，省略后重复创建报错.如果不省略，则创建时会检测表是否已存在，如果表存在则不再执行创建语句
id INT(3),
`name` VARCHAR(255) NOT null, -- name是系统关键字，所以使用反引号``包裹
age SMALLINT(3) AUTO_INCREMENT PRIMARY KEY,
lalala INT UNIQUE,
height DOUBLE(3,2) DEFAULT 1.2 -- 设置默认值约束：默认值为1.2
-- PRIMARY KEY(age)
); create table if not exists classes(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
classname VARCHAR(255) NOT NULL ); CREATE table if not EXISTS `user`(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
clsId INT UNSIGNED,
`name` VARCHAR(255) NOT NULL,
CONSTRAINT user_fk_classes FOREIGN KEY (clsid) REFERENCES classes(id) ON DELETE SET NULL on update CASCADE
)
-- auto_increment -- 显示表结构
SHOW TABLES; -- 显示表内容结构
SHOW COLUMNS FROM TB1; -- 现实表的建表语句
show create TABLE tb1; -- 删除表
DROP TABLE IF EXISTS TB1;
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS `user`; -- 修改表名
ALTER table tb1 rename tb2; -- 修改字段 列
-- alter table 表名 change 旧列名 新列名 列定义 [first|after某一列]
-- first 将这个字段调整为表格第一列； after某一列： 将这个字段放到某一列后面
alter table tb1 change height width VARCHAR(200) not NULL FIRST; -- 删除表中某一列
alter table tb1 drop name; -- 新增一列：必选部分：alter table tb1 add haha DOUBLE(8,2)
alter table tb1 add haha DOUBLE(8,2) DEFAULT 1.2 after age; -- 新增多列：不能调整列的位置，只能插在最后。
alter table tb1 add (
ha1 DOUBLE(3,2) UNSIGNED,
ha2 VARCHAR(255)
); -- 同时修改多表明 rename table tb3 to tb1[,`USER`to user1];
rename table tb2 to tb1,`USER`to user1; -- 增加主键约束
alter table tb1 add PRIMARY KEY(id); -- 删除主键约束
alter table tb1 drop PRIMARY KEY; -- 新增唯一性约束
ALTER table tb1 add unique key(ha1); -- 删除唯一性约束：由于创建唯一性约束会默认创建索引，所以删除时，需删除索引
ALTER table tb1 drop index ha1; -- 设置默认值约束：前提必须设置default 属性
ALTER table tb1 alter ha1 set default 20; -- 删除默认值约束
ALTER table tb1 alter haha drop default; -- 设置外键约束 必选部分 alter table tb1 add foreign key (clsid)REFERENCES classes(id)
alter table tb1 add constraint waijianming foreign key (clsid)REFERENCES classes(id) ON DELETE SET NULL on update CASCADE; -- 删除外键约束，由于常见外键时会生成索引，所以删除外键后，需要删索引。
alter table tb1 drop foreign key waijianming; alter table tb1 drop INDEX waijianming; SHOW COLUMNS FROM TB1;
| 4、常用的函数 |
① 数学函数
ABS(X) 返回 绝对值
CELL(X) 向上取整 1.5-->2 -1.5-->-1
FLOOR(X) 向下取整 1.5-->1 -1.5-->-2
ROUND(X) 四舍五入取整 1.4-->1 1.5-->2
ROUND(X,D) 将X保留D位小数，进行四舍五入
` TRUNCATE`(X,D) 将X保留D位小数，不进行四舍五入
sign(X) X为负数返回-1 0返回0 正数返回1
pow(X,D) 返回X的D次方
② 字符串函数
LENGTH(str) 返回字符串的长度。其中一个中文占三个字符；
CHAR_LENGTH(str) 返回字符串的字符数。其中一个中文只占一个字符；
CONCAT(str1,str2,...) 拼接多个字符串；
UPPER(str) 将字符串全部转为大写；
LOWER(str) 将字符串全部转为小写；
left(str,n) 返回字符串的前N个字符；
right(str,n) 返回字符串的后N个字符；
TRIM(str) 去掉str两端的空格；
REPLACE(str,s1,s2) 将str中所有的s1换为s2；
substring(str,pos[,Len]) 截取字符串str，从第pos个字符开始，截取len个。如果省略len，则从第pos个字符截取到最后；
INSTR(str,s1) 返回s1在str的下标 INSTR("12-23","-")-->3 下标从1开始。
SELECT LENGTH("哈哈哈哈");
SELECT CHAR_LENGTH("哈哈哈哈");
SELECT concat("123","456","789");
SELECT UPPER("hahahaha");
SELECT LEFT ("abcdefg",5);
SELECT trim (" hahahaha ");
SELECT substr("abcdefghigklmn",3,5);
SELECT substring("abcdefghigklmn",3,5);
③ 常用日期函数
CURDATE() 返回当前日期。 2017-06-20
CURTIME() 返回当前时间。 09:57:51
返回当前的日期和时间。
DATEDIFF(expr1,expr2) 返回从expr1天到expr2天的天数。
ADDDATE(expr1,len) 返回从expr1当天到len天数。
YEAR(date) 返回日期中的年份
MONTH(date) 返回日期中的月份
DAY(date) 返回日期中一个的中的第几天
WEEKDAY(date) 返回一周中的第几天 0->周一
HOUR(time) 返回小时数
MINUTE(time) 返回分钟数
SECOND() 返回秒数
DATE_FORMAT(date1,format): 将date1使用format要求的格式显示。
format中可用的占位符格式：
%Y年份, 数字形式,4位数
%y年份, 数字形式 (2位数)
%M月份名称 (January..December)
%m月份, 数字形式 (00..12)
%b月份的缩写名称 (Jan..Dec)
%c月份，数字形式(0..12)
%D带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, ...)
%d该月日期, 数字形式 (00..31)
%e该月日期, 数字形式(0..31)
%H小时(00..23)
%h小时(01..12)
%k小时(0..23)
%l小时(1..12)
%i分钟,数字形式 (00..59)
%S秒 (00..59)
%s秒 (00..59)
%W工作日名称 (周日..周六)
%w一周中的每日 (0=周日..6=周六)
SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT DATEDIFF(NOW(),"1996-06-03");
SELECT ADDDATE(NOW(),100);
SELECT YEAR("1996-06-03");
SELECT WEEKDAY("2017-06-18");
SELECT HOUR(CURTIME());
SELECT MINUTE(CURTIME());
SELECT SECOND(CURTIME());
SELECT DAY(NOW());
④ 系统信息函数
VERSION(); 取到当前数据库的版本号
DATABASE(); 返回当前数据库的名字
USER(); 返回当前数据库的用户
CHARSET(str); 返回字符串的字符集
LAST_INSERT_ID(); 返回最后一个auto_increment 的值
1、PASSWORD(str) 对字符串进行加密，常用于加密密码
2、MD5(str) 对字符串进行MD5散列加密，常用语不需要解密数据
3、ENCODE(str,pwd_str) 使用加密密码pwd对字符串str进行加密;
加密之后的结果是二进制，该字段需要使用BLOB类型保存
DECODE(mistr,pwd_str) 将ENCODE加密的二进制进行解密，解密密码需与ENCODE的加密密码一致。
SELECT PASSWORD("哈哈哈");
SELECT MD5("哈哈哈");
SELECT ENCODE("neirong","mima");
SELECT DECODE(ENCODE("neirong","mima"),"mima");
UPDATE `Card` SET `PASSWORD` = REPLACE(REPLACE(`PASSWORD`,"O","0"),"i","1");
sum():求和
avg():求均值
min():求最小值
max():求最大值
count():求总个数 select count(username) from tb2
SELECT * FROM cusinfo;
-- 要求统计客户信用度为3的客户数量
SELECT COUNT(*) FROM cusinfo WHERE cuscredit=3;
-- 要求统计本月度客户订单的总和以及最高最高的客户订单额度
SELECT SUM(ordmoney)"总和",MAX(ordmoney)"最大值" from cusorder;
| 5、综合操作 |
where ……
GROUP BY ……
HAVING ……
上述代码执行流程：
① 先select……from…… 查询所有数据
② 使用where 将数据进行筛选
③ 对where筛选后的数据，使用group by 进行分组；
-- 分为N组后，使用聚合函数时，将会产生N条数据。
④ HAVING 字句对产生的多条聚合数据，进行处理操作。
注意事项：
① 代码顺序必须是： where -> GROUP BY -> HAVING
② 有having字句，必须有group by;有group by并不一定有HAVING
③ where和having区别？
where是在分组之前先筛选数据；having是对分组之后的数据，进行筛选；
where语句中一定不能包含聚合函数。
having是专门用来过滤聚合函数的，
>>> 求出，平均薪资大于1万的部门的平均薪资？
注：各部门出勤记录大于15天的员工的平均薪资。
SELECT avg(薪资) from 公司表 where 出勤率>15 group by 部门 having avg(薪资)>10000
use mydb; SELECT * FROM cusinfo; -- 需要对客户构成进行分析，要求按照客户等级显示客户数量
set @count = 0;
SELECT (@count := @count +1)"序号",cuslevel"客户等级",COUNT(*)"客户数量"
FROM cusinfo GROUP BY cuslevel
ORDER BY @count; -- 需要对客户构成进行分析，要求计算不同区域客户的数量在5个以上的统计报表
SELECT cusregion"客户地区",COUNT(1)"客户数量" FROM cusinfo
GROUP BY cusregion HAVING COUNT(1)>5 -- 需要对客户贡献进行分析，要求统计本年度每个客户订单的总额
set @count = 0;
SELECT (@count := @count +1)"序号",i.cusname"客户编号",o.ordmoney"订单金额" -- 多表关联，字段前必须有表名
FROM cusorder as o,cusinfo as i -- 为了方便书写，给表起别名
WHERE o.cusno=i.cusno; -- 两张表通过哪个键连接 -- (内连接)需要对客户贡献进行分析，要求统计本年度每个客户订单的总额
set @count = 0;
SELECT (@count := @count +1)"序号",i.cusname"客户编号",o.ordmoney"订单金额" -- 多表关联，字段前必须有表名
FROM cusorder as o INNER JOIN cusinfo as i -- 为了方便书写，给表起别名
on o.cusno=i.cusno; -- 两张表通过哪个键连接 -- 内连接 INNER JOIN
-- 特点：相关联的两张表，总是以数据少的为准。
-- 另一张表中，没有被匹配的表，会被删除不显示。 -- 左外连接 left JOIN
-- 特点： 以左边的表为准
-- 把左表的全部数据显示，右表多的删除不显示，右表少的补null。 -- 右外连接 left JOIN
-- 特点： 以右边的表为准
-- 把右表的全部数据显示，左表多的删除不显示，左表少的补null。 /*[自连接]同一张表与自己链接
SELECT e1.ename"雇员",e2.ename"领导"
FROM emp e1 LEFT JOIN emp e2
ON e1.mgr=e2.empno
*/ -- 订单明细（产品编号、产品数量、产品单价）及订单(订单编号，订购日期)信息
SELECT od.*,o.orddate
FROM cusorderdetail od,cusorder o
WHERE o.ordno=od.ordno; /*[限制行数]
LIMIT n 前n条数据 相当于LIMIT 0,n
LIMTT n,m 第n+1 到 第M条 */ SELECT * from tb1 LIMIT 5; SET @page = 1; -- 第几页
SET @rows = 5; -- 每页几条
-- 分页语句
SELECT * FROM cusorder LIMIT @rows*(@page-1),@rows*@page; /*
各种限制语句的顺序
*/ select *,sum(ordmoney) FROM cusorder
WHERE NOT ISNULL(ordno)
GROUP BY ordno
having SUM(ordmoney)>0
ORDER BY ordmoney DESC
LIMIT 0,10
学习时候的笔记，可能会有一些错误的地方，欢迎各位的批评指点。
反思，复盘，每天收获一点---------------------期待更好的自己