学习mysql语法--基础篇（二）
前 言
mysql
mysql语法--本篇学习都是通过使用Navicat Premium（数据库管理工具），连接mysql数据.
本篇学习主要有两个部分：
【SQL语句的组成】
DML 数据操作语言(插入、删除和修改数据库中的数据)INSERT UPDATE DELETE
DQL 数据查询语言(用来查询数据库中的数据)SELECT
DCL 数据控制语言(用来控制存取许可、存取权限等)CREATE
DDL 数据定义语言(用来建立数据库、数据库对象和定义表的列)CREATE DROP
【常用的函数】
数学函数
字符串函数
常用日期函数
系统信息函数
加密函数
多行函数
【综合操作】
分组操作
限制行数
内连接
自连接
以下所有代码全部在新建查询表里面使用mysql语法编辑。
| 1、DML 数据操作语言（INSERT UPDATE DELETE） |
① 数据插入语句 insert
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、综合操作 |
select …… FROM <表名>
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
学习时候的笔记，可能会有一些错误的地方，欢迎各位的批评指点。
反思，复盘，每天收获一点---------------------期待更好的自己
