切换模式
《深入浅出MySQL》个人笔记（基础篇）
我是K
困难永远能解决,没解决是因为惧怕而没有前行
SQL基础
SQL分类
DDL（Data Definition Languages）语句：
数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引、等数据库对象。常用语句关键字包括create、drop、alter等。
DML（Data Manipulation Languages）语句：
数据操控语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字包括insert、delete、update和setect等。
DCL（Data Control languages）语句：
数据控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要语句关键字包括grant、revoke等。
DDL语句
1. 创建数据库
CREATE DATABASE dbname
选择要操作的数据库
USE dbname
查看数据库中创建的所有数据表
SHOW tables
2. 删除数据库
drop database dbname
3. 创建表
CREATE TABLE tablename(
column_name1 column_type_1 constraints,
column_name2 column_type_2 constraints,
···
column_namen column_type_n constraints)
查看表定义
DESC tablename; or\G
4. 删除表
DROP TABLE tablename
5. 修改表
修改表类型
ALTER TABLE tablename MODIFY[COLUMN]column_defintion[FIRST|AFTER col_name]
例：alter table emp modify ename varchar(20)
增加表字段
ALTER TABLE tablename ADD[COLUMN] column_definition [FIRST|AFTER col_name]
例：alter table emp add column age int(3);
删除表字段
ALTER TABLE tablename DROP [COLUMN] col_name
字段改名
ALTER TABLE tablename CHANGE [COLUMN] old_col_name coumn_definition [FIRST|AFTER col_name]
例：alter table emp change age age1 int(4);
change和modify都可以修改表的定义，不同的是change后面需要写两次列名，不方便。但是change的优点是可以修改列名称，modify则不能。
修改字段排列顺序
例：将新增字段birth date加载ename之后
alter table emp add birth date after ename;
修改字段age，将它放在最前面
alter table emp modify age int(3) first;
修改表名
ALTER TABLE tablename RENAME [TO] new_tablename
例：alter table emp rename emp1DML语句
1. 插入记录
INSERT INTO tablename(field1，field2，···fieldn)VALUES(value1,value2,···,valuen);
例：insert into emp (ename,hiredate,sal,deptno)values('zzx1','2000-01-01','2000','1');
也可以不指定字段名称，但是后面value要对应好字段。如果只插入其中几个字段，没写的字段自动加NULL、默认值、自增下一个数字等。
也可以一次性插入多条记录
INSERT INTO tablename(field1,field2,····fieldn)
VALUES(record1_value1,record1_value2,···record1_valuen,
record2_value1,record2_value2,···record2_valuen,
···
recordn_value1,recordn_value2,···recordn_valuen)
;
2. 更新记录
UPDATE tablename SET field1=value1,field2=value2,···fieldn=valuen[WHERE CONDITION]
例：将表emp中ename为'lisa'的薪水（sal）从3000涨到4000
update emp set sal=4000 where ename='lisa';
使用update命令同时更新多个表中数据
UPDATE t1,t2,···，tn set t1.field1=expr1,tn.field=exprn [WHERE CONDITION]例：同时更新表emp中的字段sal和表dept中的字段deptname：
update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno; 
多表更新多用在根据一个表的字段来动态地更新另外一个表地字段。
3. 删除记录
DELETE FROM tablename [WHERE CONDITION]
例：delete from emp where ename='dony';
一次删除多个表的数据
DELETE t1,t2....,tn FROM t1,t2....,tn[WHERE CONDITION]
例：detele a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3; #p36
4. 查询记录
SELECT*FROM tablename [WHERE CONDITION]
*代表所有记录，可以用逗号分隔所有字段来代替，例：
select ename,hiredate,sal,deptno from emp;
查询不重复记录
去重显示用 distinct关键字，例：
select distinct deptno from emp;
条件查询
使用 WHERE关键字实现，查询条件记录，使用例如=，或> < >= <= != 等比较运算符，或or、and等逻辑运算联合查询
例：select* from emp where deptno=1 and sal<3000;
排序和限制
排序使用 ORDER BY 来实现
SELECT*FROM tablename [WHERE CONDITION] ORDER BY field1[DESC|ASC],...,fieldn[DESC|ASC]
DESC降序 ASC升序 默认升序
例： select * from emp order by sql
多字段： select * from emp order by deptno,sal desc; #多字段排序会优先排一个字段，字段相同排序另外一个。
排序后显示部分记录
使用 LIMIT 关键字
SELECT ...[LIMIT offset_start,row_count] #offset_start表示记录的其实偏移量，row_count表示显示的行数。
例： select * from emp order by sal limit 3;
select * from emp order by sal limit 1,3; #从第二条显示，三条数据。
#limit语法其他数据库不通用
聚合
汇总操作，语法：
SELECT [field1,field2,...,fieldn] fun_name
FROM tablename
[WHERE where_conditition]
[GROUP BY field1,field2,...,fieldn
[WITH ROLLUP] ]
[HAVING where_cotition]
#fun_name 表示要做的聚合操作，也就是聚合函数，常用的有sum、count(*)（记录数）、max、min
#GROUP BY 关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在group by 后面。
#WITH ROLLUP是可选语法，表名是否对分类聚合后的结果进行再汇总。
#HAVING关键字表示对分类后的结果再进行条件过滤例：在emp表中统计公司总人数：
select count(1) from emp;
在此基础上，统计各个部门人数：
select deptno,count(1) from emo group by deptno;
既统计各部门人数，又统计总人数：
select deptno,count(1) from emp group by deptno with rollup;
统计人数大于1人的部门：
select deptno,count(1) from emp group by deptno having count(1)>1;
统计公司所有员工的薪水总额、最高和最低薪水：
select sum(sal),max(sal),min(sal) from emp;
表连接
当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。大致分为内连接和外连接.内连接金选出两张表中相互匹配的记录,而外连接会选出其他不匹配的记录.
例:查询所有雇员的名字和所在部门名称,雇员名称和部门分别放在两个表中,因此,需要使用表连接来进行查询:
select ename,deptname from emp,dept where emp.deptno=dept.deptno;
外连接分为左连接和右连接，意为左边所有记录和右边的交集部分呈现，右边所有记录和左边交集部分呈现。
例： select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
或
select ename,deptname from dept left join emp on dept.deptno=emp.deptno;
子查询
有时，进行查询时需要嵌套查询另外一个select语句的结果。用于子查询的关键字包括in、not in、=、！=、 exists、not exists等。
例： select* from emp where deptno in(select deptno from dept);
如果查询近路数唯一，还可以用=代替in：
select* from emp where deptno = (select deptno from dept);
记录联合
将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示。需要用到 union 和 union all关键字来实现这样的功能：
SELECT* FROM t1
UNION | UNION ALL
SELECT* FROM t2
...
UNION | UNION ALL
SELECT* FROM tn；
UNION ALL 区别是把结果集合并在一起，而 UNION是将 UNION ALL后的结果进行一次 DISTINCT,去重记录后的结果。
例结果见P47
常用函数
1.字符串函数
MySQL常用字符串函数
CONCAT(S1,S1,...Sn) 连接S1....为一个字符串
INSERT(str,x,y,instr) 将字符串str从x位置开始，y个字符长的子串替换为字符串instr
LOWER(str) 将字符串str中的所有字符变为小写
UPPER(str) ...大写...
LEFT(str，x) 返回字符串str最左边的x个字符
RIGHT(str，x) ...右边...
LPAD(str，n，pad) 用字符串pad对str最左边进行填充，知道长度为n个字符长度。
RPAD(str，n，pad) ...右边...
LTRIM(str) 去掉字符串str左侧的空格
RTRIM(str) ...右侧...
TRIM(str) 去掉字符串行尾和行头的空格
REPEAT(str，x) 返回str重复x次的结果
REPLACE(str，a，b) 用字符串b替换字符串str中所出现的字符串a
STRCMP(s1，s2) 比较字符串s1和s2
SUBSTRING(str，x，y) 返回从字符串str x位置起y个字符串长度的字串
详细案例见P80
2.数值函数
MySQL中常用数值函数
ABS(x) 返回x的绝对值
CEIL(x) 返回大于x的最小整数值
FLOOR(x) 返回小于X的最大整数值
MOD(x，y) 返回x/y的模 #求余数
RAND() 返回0～1的随机值
ROUND(x，y) 返回参数x的四舍五入的有y位小数的值
TRUNCATE(x，y) 返回数字x截断为y位小数的结果
详细案例见P83
3.日期和时间函数
MySQL中常用日期和时间函数
CURDATE() 返回当期日期
CURTIME() 返回当前时间
NOW() 返回当前的日期和时间
UNIX_TIMESTAME(date) 返回日期date的UNIX时间戳
FROM_UNIXTIME 返回UNIX时间戳的日期值
WEEK(date) 返回日期date为一年中的第几周
YEAR(date) 返回日期date的年份
HOUR(time) 返回time的小时值
MINUTE(time) 返回time的分钟值
MONTHNAME(date) 返回date的月份
DATE_FORMAT(date，fmt) 返回按字符串fmt格式化日期date值
DATE_ADD(date，INTERVAL expr type) 返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr，expr2) 返回起始时间expr和结束时间expe2之间的天数
详细案例见P85
4.流程函数
MySQL的流程函数
IF(value，t f) 如果value是真，返回t；否则返回f
IFNULL(value1，value2) 如果value1不为空，返回value1，飞则返回value2
CASE WHEN[value1] THEN[result1]...ELSE[default]END 如果value1是真，返回resul1，否为返回default
CASE [expr] WHEN [value1] THEN[result1]... ELSE[default]END 如果expr等于value1，返回result1，否则发挥default
详细案例见P90
5.其他常用函数
MySQl其他常用函数
DATABASE() 返回当前数据库名
VERSION() 返回当前数据库版本
UESR() 返回当前登录用户名
INET_ATON(IP) 返回IP地址的数字表示
INET_NTOA(num) 返回数字代表的IP地址
PASSSWORD(str) 返回字符串str的加密版本
MD5() 返回字符串str的MD5值编辑于 2019-10-10 16:53
SQL
MySQL
MySQL 入门