数据库操作
点击查看代码
创建数据库: create database 库名;
create database if not exists 库名;(不存在就创建,不报错)
create dtabase if not exists 库名 character set 字符集;(指定数据的字符集)
删除数据库: drop database 库名;
切换数据库: use 库名;
查数据库: show databases; (查看所有数据库)
show create database 库名; (查看数据库的创建过程)
select database(); (查看正在使用的数据库)
数据表操作
点击查看代码
创建表: create table [if not existe] (同数据库创建用法) 表名(
字段名 类型 约束
字段名 类型 约束
......
字段名 类型 约束);
数据类型: 整数 int 字符串 char,varchar(动态) 小数 double,float 日期 date,timestamp ....
约束: 主键约束 primary key (一张表只能有一个主键,一个主键可以有多个字段)
唯一约束 unique
非空约束 not null
删除表: drop table 表名;
改表名: rename table 旧表名 to 新表名;
修改表的字段: 增 alter table 表名 add 新字段 类型 约束;
删 alter table 表名 drop 字段;
改 alter table 表名 change 旧字段名 新字段名 类型(长度) 约束;
查数据表: show tables; (查看所有数据库)
desc 表名; (查看表的结构)
show create table 表名; (查看表的创建过程)
表记录操作
点击查看代码
插入数据: insert into 表名 values(值1,值2,...)
insert into 表名 (字段1,字段2,...) values (值1,值2,...)
insert into 表名 values(值1,值2,...),(值1,值2,...),... (多条数据插入)
删除数据: delete from 表名 [where 条件];
delete 1:DML语句;2:按照条件删除;3:逐条删除,效率低
truncate table 表名; 1:DDL语句;2:只能全部删除;3:直接删除表,创建一个一摸一样的新表,效率高
改数据: update 表名 set 字段=值,字段=值 ,... [where 条件];(不加where全部修改)
update 表名 set 字段=replace ('字段','旧值','新值')
查询数据:
select [distinct] 字段 from 表名;[distinct 去重] (简单查询)
select 字段 from 表名 where 条件; 条件查询(关系运算符 > >= < <= = != <>
逻辑运算符 and 同时成立; or 满足任意一个 ; not 取反
空置判断 is null 判断为空; is not null 判断不为空
范围判断 between 值1 and 值2 ; in(值1,值2,...)
模糊匹配 like '内容' %任意个任意内容 _ 一个内容)
select 字段 from 表名 limit offset,count ;(分页查询;offset 偏移量,count,每页查询的数量)
select 字段 from 表名 order by 字段1 asc|desc , 字段2 asc|desc,.....(如果是多个字段 优先按照前面的字段进行排序 前面字段相同的情况下 按照后面的字段排序
asc 升序(默认),desc 降序)
select 字段,聚合函数 from 表名 group by 字段; (分组查询 查询的字段与分组的字段保持一致)
多表查询
内连接查询: select 字段 from 表1,表2 where 条件; (隐式内连接)
select 字段 form 表1 inner join 表2 on 条件;(显示内连接)
外连接查询: select 字段 from 表名1 left join 表名2 on 条件; (左外连接查询;两张表的交集 + 左表的特有数据)
select 字段 from 表名1 right join 表名2 on 条件;(右外连接查询;两张表的交集 + 右表的特有数据)
子查询:将上一条的查询结果作为下一条sql的查询条件
select 字段 from 表名 where 字段 关系运算符 (sql);
as用法: 1.字段取别名; 2.给表名取别名; 3.将sql查询结果看作为临时表使用
索引
点击查看代码
索引分类:
主键索引: primary key
普通索引: 索引的创建 create index 索引名称 on 表名(字段(长度));
alter table 表名 add index 索引名称(字段);
索引的查看 show index from 表名;
select * from mysql.innodb_index_stats
where database_name = '库名' and table_name = '表名';
索引的删除 drop index 索引名 on 表名;
唯一索引: 索引创建 create unique index 索引名称 on 表名(字段(长度));
alter table 表名 add unique 索引名称(字段);
索引的查看 show index from 表名;
select * from mysql.innodb_index_stats
where database_name = '库名' and table_name = '表名';
强制使用索引: select * from 表名 force index(索引字段名);
视图
概述
视图是从一个或者多个表中导出的,视图的行为与表非常类似,但视图是一个虚拟表,在视图中用户可以使用select语句查询数据以及使用insert,update,delete修改记录,视图可以使用户操作方便,而且可以保障数据库系统的安全
创建视图
点击查看代码
show tables;
select * from student;
#单表创建视图
create view stu_view as select Sname from student;
show tables;
#查看视图内容
select * from stu_view;
#多表创建视图
create view stu_01_score as
select student.Sname,student.SId,s.score
from student
join stucou s on student.SId = s.SId and CId=01;
select * from stu_01_score;
查看视图
点击查看代码
使用describe语句查看视图基本信息: desc 视图名;
使用show table status 语句查看视图基本信息: show table status like '视图名';
使用show create view 语句查看视图详细信息: show create view 视图名;
在views表中查看视图详细信息: select * from information_schema.views;
实例代码:
#查看视图基本信息
desc stu_01_score; #方法1
show table status like 'stu_01_score'; #方法2
#查看视图详细信息
show create view stu_01_score; #方法1
select * from information_schema.VIEWS; #方法2 在视图表中查看视图详细信息
修改视图
点击查看代码
使用create or replace view 语句修改视图:修改视图的语句和创建视图的语句的完全一样的,当视图已经存在时,修改语句对视图进行修改;当视图不存在时,创建视图
使用alter语句修改视图
实例代码:
#修改视图
create or replace view stu_01_score as select * from student;
alter view stu_01_score as select * from student;
更新视图
更新视图是指通过视图来插入,更新,删除表中的数据,因为视图是一个虚拟表,其中没有数据.通过视图更新的时候转到基本表上进行更新的,如果对视图增加或者删除记录,实际上是对其基本表增加或者删除记录
updaete 视图名 set 字段 = 修改的值 where条件语句
insert into 表名 values(数据)
delete from 视图名 where语句
当视图含有如下内容时,视图的更新操作不能被执行:
(1)视图中不包含基表中被定义为非空的列
(2)在定义视图的select语句后的字段列表中使用了数学表达式
(3)在定义视图的select语句后的字段列表中使用聚合函数
(4)在定义视图的select 语句中使用了distinct,union,top,group by或having子句
实例代码:
#查看视图基本信息
desc stu_01_score; #方法1
show table status like 'stu_01_score'; #方法2
删除视图
点击查看代码
drop view[if exists]
view_name(,view_name)...
[restrict | cascade]
实例代码:
删除视图
drop view if exists stu_01_score;
视图和表的区别
点击查看代码
1.两者的区别
(1)视图是已经编译好的SQL语句,是基于SQL语句结果集的可视化表,而表不是
(2)视图没有实际的物理记录,而表有
(3)表是内容,视图是窗口
(4)表占用物理空间,而视图不占用物理空间,视图只是逻辑概念的存在,表可以及时修改,但视图只能用创建的语句来修改
(5)视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合.从安全的角度来说,视图可以防止用户接触数据表,因而用户不知道表结构
(6)表数据全局模式中的表,是实表;视图属于局部模式的表,是虚表
(7)视图的建立和删除只影响视图本身,不影响对应的基本表.
2.两者的联系
视图是在基本表之上建立的表,它的结构(所定义的列)和内容(所有记录都来自基本表,它依据基本表存在而存在.一个视图可以对应一个基本表,也可以对应对个基本表,视图是基本表的抽象和在逻辑意义上建立的新关系
事务
函数
聚合函数
点击查看代码
（1）count 统计总个数
（2）sum 总和
（3）avg 求平均值
（4）max 最大值
（5）min 最小值
--聚合函数一般会配合分组使用,null 是不参与聚合函数运算的
开窗函数
点击查看代码
使用: select 窗口函数 over (partition by 分区字段 order by 字段 asc|desc)
分区: 可以使用多个字段分区;按照某个字段,将数据分为一区域,使用窗口函数对着一个区域的数据进行操作
排序: 按照某个字段排序
窗口函数: 聚合函数
row_number 连续不间断排名
rank 相同排名一直,排名不连续
dense_rank 相同则排名一致,排名是连续的
数学函数
点击查看代码
（1）ABS(x) 返回x的绝对值
（2）PI() 返回圆周率π，默认显示6位小数
（3）MOD(x,y) 返回x被y除后的余数
（4）FLOOR(x) 返回不大于x的最大整数
（5）ROUND(x)、ROUND(x,y) 前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位
（6）SIGN(x) 返回参数x的符号，-1表示负数，0表示0，1表示正数
（7）POW(x,y)和、POWER(x,y) 返回x的y次乘方的值
（8）EXP(x) 返回e的x乘方后的值
字符串函数
点击查看代码
（1）CHAR_LENGTH(str) 计算字符串字符个数
（2）CONCAT(s1,s2，...) 返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL
（3）CONCAT_WS(x,s1,s2,...) 返回多个字符串拼接之后的字符串，每个字符串之间有一个x
（4）INSERT(s1,x,len,s2) 返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符
（5）LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str) 前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写
（6）LEFT(s,n)、RIGHT(s,n) 前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符
（7）REPLACE(s,s1,s2) 返回一个字符串，用字符串s2替代字符串s中所有的字符串s1
（8）STRCMP(s1,s2) 若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1
（9）SUBSTRING(s,n,len)、MID(s,n,len) 两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串
（10）LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1) 三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）
（11）REVERSE(s)将字符串s反转
（12）ELT(N,str1,str2,str3,str4,...) 返回第N个字符串
日期和时间函数
点击查看代码
（1）CURDATE()、CURRENT_DATE() 将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定
（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE() 这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定
（3）MONTH(date)和MONTHNAME(date) 前者返回指定日期中的月份，后者返回指定日期中的月份的名称
（4）DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d) DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二
（5）WEEK(d)、WEEKOFYEAD(d) 前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周
（6）DAYOFYEAR(d)、DAYOFMONTH(d) 前者返回d是一年中的第几天，后者返回d是一月中的第几天
（7）EXTRACE(type FROM date) 从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
（8）DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type) 返回将起始时间减去expr type之后的时间
（9）ADDTIME(date,expr)、SUBTIME(date,expr) 前者进行date的时间加操作，后者进行date的时间减操作
条件判断函数
点击查看代码
（1）CURDATE()、CURRENT_DATE() 将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定
（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE() 这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定
（3）MONTH(date)和MONTHNAME(date) 前者返回指定日期中的月份，后者返回指定日期中的月份的名称
（4）DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d) DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二
（5）WEEK(d)、WEEKOFYEAD(d) 前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周
（6）DAYOFYEAR(d)、DAYOFMONTH(d) 前者返回d是一年中的第几天，后者返回d是一月中的第几天
（7）EXTRACE(type FROM date) 从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
（8）DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type) 返回将起始时间减去expr type之后的时间
（9）ADDTIME(date,expr)、SUBTIME(date,expr) 前者进行date的时间加操作，后者进行date的时间减操作
————————————————
版权声明：本文为博主「～Chen～」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
