MySql
一、常见指令
-
select version() 查看版本
-
select current_timestamp(); 查看系统当前时间
-
select unix_timestamp(); 查看时间戳
常见运算符
= + -
+= -= is (当值为空时用is) not is != <> in(1,3)表示值为1和3 between 1 and 3 同上
二、常用操作
-
show database; 展示当前所有数据库
-
create database 数据库名称; 新建数据库
-
use 数据库名称; 切换数据库
-
select database(); 查看当前使用的数据库
-
drop database 数据库名称; 删除数据库
-
desc 表名; 删除表
-
show tables from 其他数据库名称; 查看其它库的表
-
insert into 表名(字段) values (值);向表中插入数据
-
select * from 表名; 查询表中所有的数据
-
create table 表名(字段 字段类型,字段 字段类型);
-
字符串拼接: concat('str', 拼接的新字符串)
三、对列进行操作
-
添加列:alter table 表名 add 列名 列类型;
-
修改列的类型与大小: alter table 表名 modify 列名 类型;
-
删除列: alter table 表名 drop column 列名;
-
更改列名: alter table 表名 change 原列名 新列名 类型;
四、约束
建立约束
1. 主键约束Primary KEY:表中可以没有主键。主键具有唯一性。
主键绝对不能为空。例:create table 表名(classid int not null primary key);必须有非空主键不能为空
2. 非空约束NULL KEY
3.唯一约束:Unique Key(唯一键可以为空)
4. 默认键:DEFAULT (也可以保证唯一)
5. 检查键:Check
6.主外键 foreign Key:主要是建立表与表之间的关系
create table 表名 (表字段 字段类型 , foreign key(外键) reference 主表 (主键));
删除时只能先删除外键再删除主键
如果需要在表外加约束:alter table 表名 add constraint PK_classinfo_classid primary key (classid);
删除约束
alter table 表名 drop primary key;
alter table 表名 drop index UQ _ 表名_字段
增加约束
alter table 表名 add constrain PK_ classinfo(表名)_classid(字段名) primary key(字段);
五、数据操作(增删改查)
增:
insert into 表名 (字段名) values (值);
改:
update 表名 set 列名 = 新值[where 条件];
例: update bookinfo set bookname = '书籍名称' where bookid = 1 ;
将bookid=1的书籍列名称改为'书籍名称';
删:
-
delete from 表名 where 条件
delete from bookinfo where bookid = 6;
删除bookid = 6 的那一数据
-
删除全部数据: drop删除全部库、表
delete 删除数据
-
清空数据表中的全部数据:
truncate table bookinfo;(同时也可以删除自动增长列)
查:
-
全查:select * from 表名;(但尽量不用)
-
查询分列:select 列名 from 表名;
-
带条件查询:select * from 表名 where 条件
-
空值查询:例: select * from bookinfo where bookid = ' ' or bookid is NULL;
补充:and:当前后的条件都成立时才成立 or:当前后表达式有一个为真即为 真
-
判断是否存在:select 1 from 表名 where 条件; 若存在则返回1
-
为列取别名:select 列名 as '新列名' ;
-
模糊查询 用like % _
-
select * from 表名 where 条件 like'c%'; 表示以c开头的信息
-
select * from 表名 where 条件 like ' '; 下划线表示有三个字
-
一个下划线表示一个字符 一个百分号表示模糊查询
-
-
查询排序 order by desc (降序) order by 为升序
select * from 表名 order by 列名
-
限制行数:LIMIT
select * from 表名 where 条件 limit 行数;
-
去除重复行数:
selece distinct 列名 from 表名;
-
聚合函数(max min sum avg)
select count(表名) 统计行数
-
分组查询 group by
select typeid,avg(列名) from 表名 group by 分组依据(typeid);
-
分组后筛选:
where 在group by前面 having在最后面
select bookstatus , count(*) from 表名 where 条件 group by 分组依据 having...
例如:统计编号为1,3的图书类别的平均价格最后查询的是>200的:
select typeid , avg(bookprice) from bookinfo where typeid in (1,3) group by typeid having avg(bookprice) > 200;
-
多表查询:
-
内连接查询:inner join(主要连接) left join(左连接) right join(右连接)
select 表名.列名, 表名.列名 from 表名 inner join 表名 on bookinfo.typeid = booktype.typeid where 条件;
例如:查询出每个类别的平均价格:
select booktype.typename, avg(bookprice) from booktype inner join bookinfo on booktype.typeid = bookinfo.typeid group by booktype.typename;
-
合并结果集
select * from bookinfo where bookstatus = '正' union 另一个查询;
-
子查询
在查询中又包含一个查询 select * from bookinfo where typeid = (select typeid from booktype where typename in ('小说类' , '编程类'));
exist:select * from bookinfo where exist (select * from booktype where 条件);
六、函数
字符串处理函数
-
Lower(str) select Lower(str); 转小写
-
Upper(str) select Upper(str); 转大写
-
concat(str) select Concat(str); 连接 concat('Hello', 'World')
-
截取字符串: select substr('hello', 1, 2)第一个参数为截取位置 第二个参数为截取长度
-
求字符串长度:select length(str)
-
查找字符串位置:select instr('hello', '要查找的字符');
-
左填充:LPAD(1000, 10, '*')
-
右填充:RPAD(1000,10,'*)
-
去除左边的空格:LTRIM(str)
-
去除右边的空格RTRIM(str)
-
替换 replace('zifuchuan', '要替换的', '新字符串')
数学函数
-
四舍五入:round
-
截断:truncate(数字,截断位数)
-
求余:mod(10,3)
-
向上取整:ceil(56.2) 57
-
向下取整:floor(56.9) 56
日期函数
-
获取当前日期:now();
-
取年份:select year(now());
-
str to date:
select str_to_date('9-13-1995', '%m-%d-%y');
-
日期转字符串:
select date_format('2018/6/16','%Y年%m月%d日');
-
select date_add(now(),interval 8 year);向上增加八年
-
时间差
select DATEDIFF(now(), '2022-12-4')
-
时间转为时间戳:
select now() , unix_timestamp(now());
-
时间戳转为时间:
select from unix_time(unix_timestamp(now()), '%Y-%m-%d %H:%i:%s');
七、变量
变量名:一块内存空间的别名
变量:系统变量 局部变量 用户自定义变量
-
系统变量:select @@version
-
局部变量:通常声明在函数体或存储过程
-
用户自定义变量:
-
如果在存储过程中:declare @num int default 0
set @name = 'zhangsan'; 一定要赋值
select @name;(结果为zhangsan)
使用if表达式:if 条件表达式 then 执行 endif; 前面一定要加select
case语句:select * , case when 条件表达式 then 执行 else 执行 end(表示多分支语句结束) as "新字段名字" from 表名;(多用于范围判断与等值判断)
-
if单独语句:select if (条件表达式, '成立返回','不成功返回');
-
八、视图
-
视图可以理解为命了名的查询
-
查询结果是虚拟的 视图中存放的是查询语句,所以视图也是虚拟的
-
优点:提高安全性
-
语句:create view 视图名称 as MySql语句
例如:查询图书类型名称 图书名称 价格
create view view_test as select booktype.typename, bookinfo.bookname, bookinfo.bookprice from booktype inner join bookinfo on booktype.typeid = bookinfo.typeid;
注意:修改视图时,原表中的数据会改变 但修改表时,视图中的数据不变,表中的数据变 这也是为什么视图中不存储真正的数据的原因
例:update 视图名称 set 字段名称 = 修改结果 修改条件 修改了视图,表中的数据以及视图的数据一起变
九、存储过程
一、存储过程介绍
-
定义:存储过程是预编译的并存储在数据库中的一段Sql语句的集合
-
优点:可减少数据在数据库中的应用和在服务器之间的传输,模块化编程、重用、减少网络传输量、提高安全性
-
存储过程的参数分类:输入参数 输出参数 输入输出参数
二、存储过程的使用
-
无参存储过程
create procedure 存储名称() begin Sql 语句 这里的语句只能增删改查 判断循环 不能建表删表 end
-
输入参数存储过程 用关键字in create procedue 存储名称(in id int, in bname varchar(20)) begin
select * from bookinfo where bookid = id and bookname = bname; end call 存储名称(1,'输入参数') call关键字
-
输出参数存储过程 用关键字out create procedue 存储名称(in id int , out bname varchar(20), out price float) begin select bookname , bookprice into bname price from bookinfo where bookid = id; into只能使用一次,前后顺序必须对应 end call 存储名称(1, @n1,@n2); 后面的参数不可以单独定义,直接输出即可
-
输入输出参数存储过程(inout) create procedue 存储名称(inout num int) begin select sun = sum + 10 end set @n = 10; call 存储名称(@n); select @n; 在这里输入输出参数,必须传递变量,不能直接传入直接参数
三、局部变量
访问之前必须先声明 declare,主要在存储过程中使用
create procedue 存储名称(in id int) begin declare price float; 申请局部变量 select bookprice into price from bookinfo where bookid = id; select case when 范围 end call 存储名称(6);
循环语句
while 表达式 Do end while; if语句 if 表达式 then end if; 例如:把学生不及格的成绩加分到60 create table test (id int, score float) insert into test values (1,52),(2,66),(3,89),(4,59) create procedue add_score(); begin declare count1 int default 0; select count( * ) into count1 from test where score < 60; while count1 > 0 DO select count(*) into count1 from test where score < 60; if count1 > 0 then update test set score = score + 1 where score < 60; end if; end while; end call add_score(); select * from test;