前 言
mysql
mysql 语法 -- 本篇学习都是通过使用 Navicat Premium(数据库管理工具),连接 mysql 数据.
本篇学习主要有两个部分:
【SQL 语句的组成】DML 数据操作语言 (插入、删除和修改数据库中的数据)INSERT UPDATE DELETEDQL 数据查询语言 (用来查询数据库中的数据)SELECTDCL 数据控制语言 (用来控制存取许可、存取权限等)CREATEDDL 数据定义语言 (用来建立数据库、数据库对象和定义表的列)CREATE DROP【常用的函数】数学函数字符串函数常用日期函数系统信息函数加密函数多行函数【综合操作】分组操作限制行数内连接自连接以下所有代码全部在新建查询表里面使用 mysql 语法编辑。
1、DML 数据操作语言(INSERT UPDATE DELETE) |
- -- 选中mydb数据库
- USE mydb;
- -- 在mydb中建立一个名字为tb1表单
- -- 内有三个属性,分别为id,username,age,sex
- CREATE TABLE IF NOT EXISTS tb1(
- id INTUNSIGNEDPRIMARY KEY AUTO_INCREMENT,
- username VARCHAR(255)NOT NULL,
- age INT DEFAULT 0,
- sex enum("男","女") DEFAULT "男"
- );
- -- ↓给表tb1添加一行数据
- INSERT INTOtb1 (username,age,sex)VALUES("张三","122","女");
- -- ↓给表tb1添加一行数据 age和sex 没有数据
- INSERT INTOtb1 (username)VALUES ("李四");
- -- ↓给表tb1添加一行数据id为7的行中添加数据,如果有id7,依然在表后部添加
- inserttb1VALUES(7,"王二",12,"女");
- -- 添加多行数据
- INSERT INTOtb1 (username,age,sex)VALUES("张四",12,"女"),("张五",12,"女"),("张六",12,"女");
- -- 更新 where 后面的条件的 行内数据
- UPDATEtb1SETusername="帅哥",age=18,sex="男"whereid=8;
- UPDATEtb1SETusername="王五",age=18,sex="男"whereid=7;
- -- 删除ID为10 的那行数据
- delete fromtb1WHEREid=10;
2、DQL 数据查询语言 select |
- -- 显示表格的全部内容
- SELECT * FROM tb1;
- -- 显示表格的age列 不为null的内容
- SELECT * fromtb1where not ISNULL(age);
- -- 查询tb表中的所有数据 按id 降序排流
- select * fromtb1order byiddesc ;
- -- 查询tb表中的所有数据 按id 升序排流
- select * fromtb1order byidasc ;
- -- 列表达式
- -- 根据已有的列查询出来的结果,我们使用case结构虚拟出来的列,称为列表达式
- -- as 关键字
- -- 用于给列名其别名,as关键字还可以省略。
- selectusernameas"姓名",sex "性别",age,(-- 给username列起别名"名字",sex起别名"性别"
- CASE -- case 表示判断的开始
- whensex="男"then 1 -- 如果查询出sex 是男,则虚拟的列表达式的值就是1
- whensex="女"then 2
- else 3 -- 上面所有的wen都不成立时,列表达式的值为3
- END -- END 表示判断的结束
- ) assexno-- 给细腻出的这一列,起别名叫 sexno
- from tb1;
- -- DISTINCT 对查询之后的结果去重。
- select distinctagefromtb1;
3、数据控制语言 / 数据定义语言(CREATE DROP) |
- CREATE TABLE IF NOT EXISTS tb1(
- -- IF NOT EXISTS 可以省略,省略后重复创建报错.如果不省略,则创建时会检测表是否已存在,如果表存在则不再执行创建语句idINT(3),
- `name` VARCHAR(255)NOT null,-- name是系统关键字,所以使用反引号``包裹ageSMALLINT(3) AUTO_INCREMENTPRIMARY KEY,
- lalala INT UNIQUE,
- height DOUBLE(3,2)DEFAULT 1.2 -- 设置默认值约束:默认值为1.2
- -- PRIMARY KEY(age)
- );
- create table if not exists classes(
- id INTUNSIGNED AUTO_INCREMENTPRIMARY KEY,
- classname VARCHAR(255)NOT NULL
- );
- CREATE table if not EXISTS`user`(
- id INTUNSIGNED AUTO_INCREMENTPRIMARY KEY,
- clsId INT UNSIGNED,
- `name` VARCHAR(255)NOT NULL,
- CONSTRAINTuser_fk_classesFOREIGN KEY(clsid)REFERENCESclasses(id)ON DELETE SET NULL on update CASCADE
- )
- -- auto_increment
- -- 显示表结构
- SHOW TABLES;
- -- 显示表内容结构SHOW COLUMNSFROM TB1;
- -- 现实表的建表语句showcreate 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 tabletb1 change height widthVARCHAR(200)not NULL FIRST;
- -- 删除表中某一列
- alter tabletb1drop name;
- -- 新增一列:必选部分:alter table tb1 add haha DOUBLE(8,2)
- alter tabletb1addhahaDOUBLE(8,2)DEFAULT 1.2 after age;
- -- 新增多列:不能调整列的位置,只能插在最后。
- alter tabletb1add (
- ha1 DOUBLE(3,2) UNSIGNED,
- ha2 VARCHAR(255)
- );
- -- 同时修改多表明 rename table tb3 to tb1[,`USER`to user1];renametabletb2totb1,`USER`to user1;
- -- 增加主键约束
- alter tabletb1add PRIMARY KEY(id);
- -- 删除主键约束
- alter tabletb1drop PRIMARY KEY;
- -- 新增唯一性约束
- ALTER tabletb1add unique key(ha1);
- -- 删除唯一性约束:由于创建唯一性约束会默认创建索引,所以删除时,需删除索引
- ALTER tabletb1drop index ha1;
- -- 设置默认值约束:前提必须设置default 属性
- ALTER tabletb1alterha1set default 20;
- -- 删除默认值约束
- ALTER tabletb1alterhahadrop default;
- -- 设置外键约束 必选部分 alter table tb1 add foreign key (clsid)REFERENCES classes(id)
- alter tabletb1add constraintwaijianmingforeign key(clsid)REFERENCESclasses(id)ON DELETE SET NULL on update CASCADE;
- -- 删除外键约束,由于常见外键时会生成索引,所以删除外键后,需要删索引。
- alter tabletb1drop foreign key waijianming;
- alter tabletb1drop INDEX waijianming;
- SHOW COLUMNS FROMTB1;
4、常用的函数 |
③ 常用日期函数 CURDATE() 返回当前日期。 2017-06-20CURTIME() 返回当前时间。 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 LENGTH("哈哈哈哈");
- SELECT CHAR_LENGTH("哈哈哈哈");
- SELECTconcat("123","456","789");
- SELECT UPPER("hahahaha");
- SELECT LEFT("abcdefg",5);
- SELECT trim (" hahahaha ");
- SELECTsubstr("abcdefghigklmn",3,5);
- SELECT substring("abcdefghigklmn",3,5);
④ 系统信息函数 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 CURDATE();
- SELECT CURTIME();
- SELECT NOW();
- SELECT DATEDIFF(NOW(),"1996-06-03");
- SELECTADDDATE(NOW(),100);
- SELECT YEAR("1996-06-03");
- SELECTWEEKDAY("2017-06-18");
- SELECT HOUR(CURTIME());
- SELECT MINUTE(CURTIME());
- SELECT SECOND(CURTIME());
- SELECT DAY(NOW());
⑥ 多行函数 sum(): 求和 avg(): 求均值 min(): 求最小值 max(): 求最大值 count(): 求总个数 select count(username) from tb2
- 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");
- SELECT * FROM cusinfo;
- -- 要求统计客户信用度为3的客户数量
- SELECT COUNT(*)FROMcusinfoWHEREcuscredit=3;
- -- 要求统计本月度客户订单的总和以及最高最高的客户订单额度
- SELECT SUM(ordmoney)"总和",MAX(ordmoney)"最大值"fromcusorder;
5、综合操作 |
- use mydb;
- SELECT * FROM cusinfo;
- -- 需要对客户构成进行分析,要求按照客户等级显示客户数量
- set @count = 0;
- SELECT(@count:= @count +1)"序号",cuslevel"客户等级",COUNT(*)"客户数量"
- FROMcusinfoGROUP BY cuslevel
- ORDER BY @count;
- -- 需要对客户构成进行分析,要求计算不同区域客户的数量在5个以上的统计报表
- SELECTcusregion"客户地区",COUNT(1)"客户数量"FROM cusinfo
- GROUP BYcusregionHAVING COUNT(1)>5
- -- 需要对客户贡献进行分析,要求统计本年度每个客户订单的总额
- set @count = 0;
- SELECT(@count:= @count +1)"序号",i.cusname"客户编号",o.ordmoney"订单金额"-- 多表关联,字段前必须有表名
- FROMcusorderaso,cusinfoasi-- 为了方便书写,给表起别名
- WHEREo.cusno=i.cusno;-- 两张表通过哪个键连接
- -- (内连接)需要对客户贡献进行分析,要求统计本年度每个客户订单的总额
- set @count = 0;
- SELECT(@count:= @count +1)"序号",i.cusname"客户编号",o.ordmoney"订单金额"-- 多表关联,字段前必须有表名
- FROMcusorderasoINNER JOINcusinfoasi-- 为了方便书写,给表起别名
- ono.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
- */
- -- 订单明细(产品编号、产品数量、产品单价)及订单(订单编号,订购日期)信息
- SELECTod.*,o.orddate
- FROM cusorderdetail od,cusorder o
- WHEREo.ordno=od.ordno;
- /*[限制行数]
- LIMIT n 前n条数据 相当于LIMIT 0,n
- LIMTT n,m 第n+1 到 第M条
- */
- SELECT * fromtb1 LIMIT5;
- SET @page = 1;-- 第几页
- SET @rows = 5;-- 每页几条
- -- 分页语句
- SELECT * FROMcusorder LIMIT@rows*(@page-1),@rows*@page;
- /*
- 各种限制语句的顺序
- */
- select *,sum(ordmoney)FROM cusorder
- WHERE NOT ISNULL(ordno)
- GROUP BY ordno
- having SUM(ordmoney)>0
- ORDER BYordmoneyDESC
- LIMIT 0,10
学习时候的笔记,可能会有一些错误的地方,欢迎各位的批评指点。
反思,复盘,每天收获一点 --------------------- 期待更好的自己
来源: http://www.cnblogs.com/zhuanzhibukaixin/p/7074786.html