数据库基础(sql/mysql)增删改查操作语句

基本常用操作

创建

CREATE DATABASE database-name

show databases 显示数据库

插入

insert into

查询

查询操作使用频率最高 ，且不影响数据库本身（其他增删改 数据库皆发生变化）

select * from `users`  查询数据库下名为users的表
增加查询条件
select * from `users` where id=6    查询users表中字段id的值等于6的记录
比较运算符（大于 >  等于 =  小于 <  大于等于 >=   小于等于 <=  不等于 !=  或 <> ）判断 
逻辑运算符( 与 and ， &&   或  or     非 not ， !    )
模糊查询 筛选
content为文章的表，title为文章的标题字段
select * from `contents` where `title` like '测试%'   筛选查询标题以“测试”开头的文章
select * from `contents` where `title` like '%测试'   筛选查询标题以“测试”结尾的文章
select * from `contents` where `title` like '%测试%'   查询标题中含有“测试”的文章
select * from `contents` order by id      文章以id排序
select * from `contents` order by id ASC      文章以id升序排序  
扩展（ASC升序 DESC降序） 
select * from `users` where `id` in ('11','26')     查询id为11和id为26的用户
select * from `users` where `id` between 1 and 58    查询id编号为1到57之间的用户
select * from link where uid = 1 limit 2   查询link表中uid = 1的信息 并显示两条

备注：语句中 表名和字段名 不加引号 也行

查询行数(条数)： SELECT count(*) FROM link WHERE category_id=112
示例1：SELECT * FROM link WHERE category_id=35

模糊查询

like 匹配/模糊匹配，会与 % 和 _ 结合使用
示例2：SELECT * FROM link WHERE category_id=35 and title like'%101%'
addsleshes()函数 加入转义 防注入
SELECT * FROM link WHERE title like '%".addslashes($key)."%' or url like '%".addslashes($key)."%'
批量修改 示例3：UPDATE link SET category_id=3 WHERE category_id=2

示例样式

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

mysql多条件组合查询
and 还有 or 和 in语句 使用()括号
示例：
SELECT * FROM user WHERE uid IN (2,3,5)
示例2： SELECT * FROM link WHERE uid IN(SELECT uid FROM user WHERE status=0)
示例3：SELECT * FROM user WHERE uid IN(1,2) OR username IN('admin','manong')

注意事项
查询条件 判断 不等于时 <> 和!= 是等价的
其它 is null ，!= null ，is not null

修改更新

针对category表中sort的值大于等于166的增加1
update category set sort= sort+1 where sort>=166

1.修改日期时间字段的值
示例：change_time = '2018-03-24 13:59:09'
针对日期时间的一些mysql函数
time_to_sec() 将格式化的日期时间转化秒
SELECT time_to_sec(change_time) FROM state WHERE uid = 1
to_days()将格式化的日期时间转化天数
示例：查询记录 距 当前过去多少天了
SELECT to_days(now())-to_days(change_time) FROM state WHERE uid = 1

时间修改为当前时间
将时间类型的值设为 CURRENT_TIMESTAMP 或者 直接以时间格式化形式 date("Y-m-d H:i:s")

删除

删除操作请再三确认，操作前请备份好数据
delete from 表名 删除表中全部内容
drop table 表名 删除整个表

批量修改操作

将searchs表中 字段type为video的值 批量修改为 media

     表名          字段     修改的新内容               原始值

UPDATE searchs SET type = 'media' WHERE 1 and type = 'video'

条件里 嵌套子语句
UPDATE link SET sss = 2 where icon in (SELECT url FROM lastpass)

注意事项：
在知道 需要使用多少字段的情况下，尽量不用用 * 。 可以具体到 哪几个字段 减少后台查询读取
同时更新多个字段时，格式：update 表名 set 字段1=值 ,字段2=值 where 条件 字段之间 用逗号分隔

AS的用法

SELECT *,FROM_UNIXTIME(ctime,'%Y-%m-%d') as create_time FROM `link`

在查询的结果后面 再创建一个名为 create_time 的虚拟字段 ，实际不存在 只能当作查询的别名用
查询'%Y-%m-%d'格式的ctime时 就可以 用 create_time

手写语句备忘

if($cid==1  || $cid==2) $sql = "select count(*) as `count` from link";

    if($type == "hot") $sql = "select *,FROM_UNIXTIME(ctime,'%Y-%m-%d') as create_time from link $where order by hits desc limit $offset,$each";
$type_hot = mysql::query($sql);
$link_list_hot = $type_hot;

if($type == "newly") $sql = "select *,FROM_UNIXTIME(ctime,'%Y-%m-%d') as create_time from link $where order by ctime desc limit $offset,$each";
$type_newly = mysql::query($sql);
$link_list_newly = $type_newly;

来自links站点index.php页面

limit用法详解

用于限制结果集中的行数，
limit后面跟一个参数值
如 limit 1 代表只显示 一条(一行) 数据
limit 5 代表只显示 五条(五行) 数据 即前5条数据
limit后面跟二个参数值
如 limit 5,30 代表 从第六行开始(包括第六行) 显示30条(行) 数据
如 LIMIT 1,2 表示 检索第2-3行(包括)
所以 要想结果 从第一行开始显示 第一个参数是0 而不是1
备注：参数的值必须为零或正整数

排序 ORDER BY

使用 ORDER BY link.id ASC 按照id升序
ORDER BY link.id DESC 按照id降序

遇到的问题

---经常手动操作修改设置数据库字段id后，自增就从最大的那次数字开始了 怎样恢复？？？

修改数据库单张表自增id 起始数字
以数据库名links 下category表为例：
步骤：导出表category数据文件category.sql
文本编辑器软件打开category.sql文件，可以用 notepad++ 或是 sublime 亦或是 VScode
修改 AUTO_INCREMENT=115;就行了

MySQL 实现一个字段赋值给另一个字段
UPDATE 表名 SET 字段1 = 字段2

mysql触发器 使用

以phpmyadmin数据库管理工具为例
打开触发器
设置：触发器名称 随意，选择对应 表
时机（after或before）before在表中数据发生改变前的状态，after在表中数据发生改变后的状态
事件（insert、delete、upodate）
定义 行所在输入框里 填入需要执行sql语句
如：update state set is_login=0 where to_days(now())-to_days(change_time)>=1 and is_login=1
最后 用户 root@127.0.0.1

mysql事件 定时器 （时间任务）

事件 相对于 触发器，不需要 人为操作条件，可以根据时间来控制执行
在操作之前 一定要 统一 本地时间 和 服务器时区，确保时间一致
支持event事件的mysql版本必须5.1.6以上
---使用前 先 执行语句 SET GLOBAL event_scheduler="ON" 开启 事件 或者
SET GLOBAL event_scheduler = 1
相关sql语句-查看状态是否开启： SHOW VARIABLES LIKE 'event_scheduler'
关闭 SET GLOBAL event_scheduler = OFF
特别注意事项：在服务器重启或者mysql重启后 开启状态将会恢复到off状态
所以一劳永逸的方法是：修改 服务器上MySQL的my.cnf配置文件 (linux系统在etc目录下)
在文件中[mysqld]段落里 添加上 event_scheduler = on

设置：事件名称 随意，状态（开启enabled），事件类型（循环recurring，一次one time）
运行周期 后面有单位（年year，季度quarter，月month，天day，小时hour，分钟minute，周week，秒second 等）
起始时间（执行后会自动生成当前时间）和终止时间 这两项可以不用填
定义 行所在输入框 输入 以下 sql语句
update state set is_login=0 where to_days(now())-to_days(change_time)>=1 and uid = 1 and is_login=1
最后 用户 root@127.0.0.1
注释 解释说明一下这个事件的用途什么的

实现功能： 间隔24小时（1天）修改一次登陆状态。正好 模拟cookie缓存
另外：to_seconds查询秒数
示例： select to_seconds(now())-to_seconds(change_time)

新的sql语句（实现4小时更新一次）
update state set is_login=0 where to_seconds(now())-to_seconds(change_time)>14400 and uid = 1 and is_login=1

时间字段 设置 为表发生变化 自动更新
字段后面 额外增加 ON UPDATE CURRENT_TIMESTAMP()

时间字段类型
DATETIME和TIMESTAMP
默认值为CURRENT_TIMESTAMP时 表示 自动生成当前时间

mysql的一些内置 函数

如 当前日期和时间now()，当前日期curdate()，当前时间curtime() ，时间戳unix_timestamp()
示例：SELECT now() 查询当前时间
select unix_timestamp() 当前时间 转为时间戳
select unix_timestamp('2019-03-24 20:03:44') 常规时间格式转换为时间戳
select from_unixtime(1553429024); 时间戳还原为常规时间格式
SELECT CURRENT_TIMESTAMP(); 当前时间 结果显示 2019-03-24 20:06:21

mysql数据库 命令
登陆 mysql -u用户名 -p

切换数据库 mysql>use test; （其中test为要导入的数据库名）

导入sql文件 mysql> source 文件路径 （source后边为sql文件存放位置）