MySQL基础篇

				 执行顺序
SELECT 查询列表		7
FROM 表				1
【
JOIN TYPE JOIN 表b	2
ON 连接条件			3
WHERE 筛选条件		4
GROUP BY 分组字段	5
HAVING 分组后筛选	    6
ORDER BY 排序的字段	8
】
LIMIT OFFSET,SIZE;	9

常用函数 命令

show databases; 查看数据库
use 库名; 进入库
show tables; 查看有哪些表
show tables from 库名; 查看某库的所有表
select database(); 查看在哪个库
desc 表名; 查看表结构
select version(); 查看版本
show variables like ‘%char%’ 查看使用字符集
SHOW VARIABLES LIKE 'time_zone'; 查看时区
SET time_zone=''; 修改时区
SHOW VARIABLES LIKE 'AUTO_INCREMENT'; 查看自增长步长
SELECT USER();

SELECT DISTINCT id FROM a; 去重
SELECT CONCAT(NAME,id) cid FROM a; mysql中专用拼接关键字 CONCAT
LENGTH() 获取参数值的字节个数
UPPER() LOWER() 变大写小写
substring() substr()截取字符
substring(start,end) 从start截取到end包括start不包括end
substr(start,length) 截取从指定索引处指定字符长度的字符
INSTR() 返回子串第一次出现的索引 找不到返回0
TRIM() 去前后空格
LPAD() 用指定的字符和长度实现左填充
RPAD() 用指定的字符和长度实现右填充
REPLACE() 替换 SELECT REPLACE('替换替换替换','替换','不');
ROUND() 四舍五入
CEIL()	向上取整 返回>=该参数的最小整数
FLOOP()	向下取整 返回<=该参数的最大整数
TRUNCATE() 截断 SELECT TRUNCATE(1.111111,2);
MOD() 取余 SELECT MOD(10,3);
日期函数
	1.NOW 返回当前系统日期加时间
		SELECT NOW();
	2.CURDATE 返回当前系统日期 不包括时间
		SELECT CURDATE();
	3.CURTIME 返回当前系统时间 不包括日期
		SELECT CURTIME();
	4.可以获取指定的部分 年月日小时分钟秒
		SELECT YEAR(NOW());
		SELECT YEAR('1998-1-1');
		SELECT YEAR(gmt_create) FROM edu_teacher;
		SELECT MONTH(gmt_create) FROM edu_teacher;
		SELECT MONTHNAME(gmt_create) FROM edu_teacher;
	5.STR_TO_DATE 将字符通过指定的格式转换成日期
		SELECT STR_TO_DATE('3-2-1998','%m-%d-%y');
		SELECT *FROM edu_teacher WHERE gmt_create = STR_TO_DATE('10-30-2019','%m-%d-%y');
	6.DATE_FORMAT 将日期转成字符
		SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');
		
	流程控制函数
	1.IF （IF ELSE 效果）
		SELECT is_deleted,IF(sort=0,'you0','you1') FROM edu_teacher;
	2.CASE
		SELECT LEVEL ,
			CASE 
			WHEN LEVEL=0 THEN 'C'
			WHEN LEVEL=1 THEN 'B'
			ELSE 'A'
			END
		FROM edu_teacher;
分组函数
SUM() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值
COUNT() 计算个数 只计算非空的
效率
	MYISAM下 COUNT(*)效率高
	INNODB下 COUNT(*) 和 COUNT(1) 差不多 比COUNT(字段)高
	一般用COUNT(*) 统计函数

条件查询

条件运算符: > < = != <> >= <= （<>和!=一样）(安全等于 <=>)
逻辑运算符: and or not
模糊查询: LIKE BETWEEN AND IN IS NULL IS NOT NULL

LIKE
一般和通配符配合使用
% 任意多个字符 包含0个字符
_ 任意单个字符

IN
列表值类型必须一致或兼容

排序查询

1.排序列表：根据那个列表来排序
2.asc升序 desc降序
3.order by子句中可以支持单个字段 多个字段 表达式 函数 别名

按别名排序
SELECT id ,IFNULL(NAME,‘kong’) “all” FROM a ORDER BY “all” DESC;
按函数排序
SELECT LENGTH(id) 字节长度 FROM a ORDER BY LENGTH(id) DESC;
按多个字段排序 以前面的为主 一样的话在根据后面的条件排
SELECT id FROM a ORDER BY id DESC,LEVEL ASC;

分组查询

分组查询特点
查询表必须特殊 要求是分组函数和GROUP BY后出现的字段
和分组函数一同查询的字段要求是GROUP BY后的字段
分组函数做条件肯定放在HAVING子句中 能用分组前筛选 优先考虑分组前筛选

SELECT MAX(salary),job_id
			from employees
			where commission IS NOT NOT
			GROUP BY job_id
			HAVING MAX(salary)>12000;

连接查询

内连:INNER JOIN ON 共有的

外连
1.外链接的查询结果为主表中的全部内容
如果从表中有匹配的 显示匹配的值
如果没有 显示NULL
外连接查询 = 内连接结果 + 主表中有而从表中没有的内容
2.左外 LEFT JOIN 左边为主
右外 RIGHT JOIN 右边为主
ON 后的连接条件最好选从表中的主键列

全外连接=内连接的结果+表1有表2没有的+表1没有表2有的
SELECT title,NAME FROM edu_chapter c
FULL JOIN edu_teacher t ON c.id=t.id
WHERE c.title IS NULL;

MYSQL 不支持 需要用UNION
SELECT …
UNION
SELECT …

交叉连接 两表的笛卡尔乘积
SELECT c.*,t.* FROM edu_chapter c CROSS JOIN edu_teacher t;

子查询

	SELECT后面 				支持 	标量子查询
	FROM后面   				支持 	表子查询
	★WHERE或HAVING后面 	    支持 	标量子查询 列子查询 行子查询
	EXISTS后面(相关子查询)	支持 	表子查询
	
按结果集行列数不同
	标量子查询(结果集只有一行一列)
	列子查询  (结果集只有一列多行)
	行子查询  (结果集只有一行多列)
	表子查询  (结果集一般多行多列)
	
1.子查询放在小括号内
2.一般放在条件的右侧
3.标量子查询一般搭配单行操作符使用 > < >= <= = <>
4.列子查询一般搭配多行操作符使用 
	IN  		等于列表中的任意一个
	ANY/SOME  	和子查询返回的，某一个值比较
	ALL			和子查询返回的所有值比较

分页查询

LIMIT OFFSET,SIZE; OFFSET 要显示条目的起始索引 SIZE 要显示的条目个数 SELECT * FROM edu_chapter LIMIT 2,5;//从第二条开始 往后显示五条
公式
要显示的页数page 每页条目数size SELECT 查询列表
FROM 表 LIMIT (page-1)*size,size;

联合查询

UNION 将多条查询语句的结果合并成一个结果

INSERT

方式一
insert into 表名 (列名，列名2…) values(‘数据1’,’,数据2’);

1.插入的值的类型要与列的类型一致或兼容
INSERT INTO edu_course_description (id,description) VALUES(1,‘s’);

2.不可以为NULL的必须插入值 可以为NULL的可以省略 例(description 可以为NULL)
INSERT INTO edu_course_description (id) VALUES(1);

3.可以省略列名 默认所有列 而且列的顺序和表中列的顺序一样
INSERT INTO edu_course_description VALUES(3,‘2’,‘0000-00-00 00:00:00’,‘0000-00-00 00:00:00’);

方式二
insert into 表名 set 列名=值,列名=值;
INSERT INTO edu_course_description SET id=5;

方式一支持插入多行 支持子查询
INSERT INTO edu_course_description (id,description) VALUES(1,'s')
(77,'7');
	
INSERT INTO edu_course_description (id,description)
SELECT 555,'s';
	
INSERT INTO edu_course_description (id,description)
SELECT id,title FROM edu_chapter c WHERE c.id>30;

UPDATE

修改单表
update 表名 ①
set 列=新值,列=新值… ③
where 筛选条件; ②

DELETE

DELETE FROM 表名 WHERE 筛选条件;

TRUNCATE 清空表 不能加WHERE
TRUNCATE TABLE 表名;

区别
假如要删除的表中有自增长列
DELETE删除后在加数据 自增长列的值从断点开始
TRUNCATE删除后在加数据 自增长列的值从1开始
DELETE删除有返回值 TRUNCATE没有
DELETE删除可以回滚 TRUNCATE不行

DDL

							=====库=====
创建库
CREATE DATABASE 【IF NOT EXISTS】库名;

修改 
RENAME DATABASE 旧库名 TO 新库名; 不让用

更改库的字符集
ALTER DATABASE 库名 CHARACTER SET GBK;

删除库
DROP DATABASE IF EXISTS 库名;
							=====表=====
创建表
CREATE TABLE 表名(
			 列名 列的类型【长度 约束】,
			 列名 列的类型【长度 约束】.....
);

修改表
①修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型(长度);
ALTER TABLE tt CHANGE COLUMN nn NAME INT(20);

②修改列的类型或约束
ALTER TABLE 表名 MODIFY COLUMN 列名 类型(长度);

③添加新列
ALTER TABLE 表名 ADD COLUMN 列名 类型(长度);

④删除列
ALTER TABLE 表名 DROP COLUMN 列名;

⑤修改表名
ALTER TABLE 表名 RENAME TO 新表名;

删除表
DROP TABLE  IF EXISTS 表名;

建库建表通用写法
DROP DATABASE IF EXISTS 库名;
CREATE DATABASE 库名;

DROP TABLE IF EXISTS 表名;
CREATE TABLE 表名();

**表的复制**

只复制表结构
CREATE TABLE copyt LIKE 要复制的表名;

复制表的结构+数据
CREATE TABLE copyt2 SELECT * FROM 要复制的表名;

只复制部分数据
CREATE TABLE copyt2 SELECT 字段名1,字段名2 FROM 要复制的表名 WHERE 筛选条件;

只复制某些字段
CREATE TABLE copyt2 SELECT 字段名1,字段名2 FROM 要复制的表名 WHERE 1=2;
CREATE TABLE copyt2 SELECT 字段名1,字段名2 FROM 要复制的表名 WHERE 0;

约束

含义：一种限制 用于限制表中的数据 为了保证表中的数据的准确性和可靠性

分类：六大约束
NOT NULL:非空 保证该字段值不能为空
DEFAULT:保证有默认值
PRIMARY KEY:主键 保证该字段值有唯一性且不能为空
UNIQUE:唯一 保证该字段值有唯一性 可以为空
CHECK:检查约束【mysql不支持】
FOREIGN KEY:外键 限制两个表的关系
保证该字段值必须来自主表的关联查询的值
在从表添加外键约束 引用主表中某列的值

添加时机 创建表时 修改表时
列级约束 六大约束语法上都支持 但外键约束没有效果
表级约束 除了NOT NULL非空 DEFAULT默认 其他都支持

CREATE TABLE hh (
	id INT PRIMARY KEY,#主键
	card INT(8) NOT NULL,#非空
	checka CHAR(1) CHECK(checka='男'),#检查
	seat INT UNIQUE,#唯一
	age INT DEFAULT 18,#默认
	mmid INT,
	CONSTRAINT fk_hh_tt FOREIGN KEY(mmid) REFERENCES copyt(id)#外键
);

====修改表时添加约束====
①非空
ALTER TABLE xx MODIFY COLUMN id int(11) NOT NULL;
②默认
ALTER TABLE xx MODIFY COLUMN id int(11) DEFAULT 1;
③主键
1.列级约束 ALTER TABLE xx MODIFY COLUMN id int(11) PRIMARY KEY;
2.表级约束 ALTER TABLE xx ADD PRIMARY KEY(id);
④唯一
1.列级约束 ALTER TABLE xx MODIFY COLUMN id int(11) UNIQUE;
2.表级约束 ALTER TABLE xx ADD UNIQUE(id);
⑤外键 ALTER TABLE xx ADD CONSTRAINT fk_hh_tt FOREIGN KEY(mmid) REFERENCES copyt(id);

====修改表时删除约束====
①删除非空
ALTER TABLE xx MODIFY COLUMN id int(11) NULL;
②删除默认
ALTER TABLE xx MODIFY COLUMN id int(11);
③删除主键
ALTER TABLE xx DROP PRIMARY KEY;
④删除唯一
ALTER TABLE hh DROP INDEX id;
⑤删除外键 
ALTER TABLE xx DROP FOREIGN KEY fk_hh_tt;

自增长列

标识列又称自增长列
可以不用手动插入值 系统提供默认序列值

1.标识列不一定和主键搭配 但要求是个key
2.一个表只能有一个标识列
3.标识列类型只能是数值型
4.标识列可以通过 SET AUTO_INCREMENT_名字=3; 设置步长;
查看 SHOW VARIABLES LIKE ‘AUTO_INCREMENT’;

创建表时设置
CREATE TABLE hh (
id INT PRIMARY KEY AUTO_INCREMENT
);
修改表时设置
ALTER TABLE xx MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;