1查看当前的用户
select user();
2 查询所有用户
SELECT DISTINCT User FROM mysql.user;
3 删除用户
delete from mysql.user where user = 'bb' and host = 'localhost';
4 把aa数据库中的user表及其数据复制到数据库bb中的user表中,表字段一样
前提,在高权限用户下操作,比如root
第一步 CREATE TABLE user LIKE aa.user;
第二部 INSERT user SELECT * FROM aa.user ;
5 创建数据库,例如名字为aa
create database aa;
6 创建一个新的用户,并只能操作某一个特定的数据库
例如用户为bb,密码为123456,数据库为bb,
前提是root登录后
第一步创建用户和密码
create user 'bb'@'localhost' identified by '123456';
第二部刷新权限
flush privileges;
第三步创建数据库
create database bb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
第四步赋予权限
grant all privileges on bb.* to 'bb'@'localhost' identified by '123456' with grant option;
第五步刷新权限
flush privileges;
7 添加字段
例如为user表添加字段
alter table user add column small_image varchar(100) not null default '/video/image/ee.jpg';
8 删除字段
例如表名称为user,要删除的字段名称为small_image
alter table user drop small_image;
9 更新字段的值
例如表名称为user,要更改的字段名称为small_image_url
update user set small_image_url = '/video/image/ee.jpg' where name = '11';
10 删除字段 比如表名称为id_name ,要删除的字段为age alter table id_name drop column age
11修改字段默认值,类型,长度
比如表名称为user,要修改的字段为new1
alter table user MODIFY new1 VARCHAR(10);
12 从两个表里面内连接查询数据,
第一张表为history,第二张表为video,history表的video_idl来源于video表的id,需要histrory表的全部字段和video表的cover_url字段
注意了,这个条件 h.username ='11' and h.video_id = 1 ;,查出的结果是唯一的
select h.video_id, h.url, h.video_name , v.cover_url from history as h inner join video as v on h.video_id = v.id where h.username ='11' and h.video_id = 1 ;
13 删除表中数据
truncate table table_name;
delete * from table_name;
14 例如,user表中插入默认值
insert into user values(null, '11', '11', default);
15 在某一个表的某个字段后面添加字段,并且做字段约束、注释
alter table user add column open_id int(11) default null comment '你好' after password;
16 打开与关闭安全模式
当sql语句正确,但是无法更新操作的时候
14:37:21 update red_money set remain_money = remain_money -10 and remain_amount = remain_amount -1 where red_money_id = 1 Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect. 0.000 sec
SET SQL_SAFE_UPDATES = 1;
SET SQL_SAFE_UPDATES = 0;
17 mysql减法
针对一个字段进行减法
update red_money set remain_money = remain_money -10 where id = 1;
针对两个字段进行减法,注意,中间是逗号
update red_money set remain_money = remain_money -10 , remain_amount = remain_amount-1 where id = 1;
18 最简单的去重统计
注意: parking_name字段有重复
select count( distinct parking_name) from parking;
19 先去重,在统计数量,返回一个数值
select count(1) from ( select * from car_code_manage group by car_code order by id ) as old;
20 普通去重,再根据id排序
select * from car_code_manage group by car_code ORDER BY id
21 分页查询
select * from car_code_manage order by id limit 10,10 ;
22 查询,在从另外一张表中获得统计数量,组成新的实体类,返回
select m.* , (select count(1) from line where belong = '0001' and status ='0') as count01 from machine as m ,
line as l where m.code ='0001' and l.belong ='0001' order by id limit 1;
23 全部合并
select m.*, l.* from machine m ,line l;
24 根据条件统计数量
select count(1) from line where belong = '0001';
25 两张表,根据相同字段,内连,统计,并给统计结果起别名字段,去重
select m.* ,(select count(1) from line where belong = m.code and status ='0') as count01 from machine as m inner join line as l on m.code = l.belong group by id;
26 两张表,根据相同字段,统计,并给统计结果起别名,去重,
select m.* , (select count(1) from line where belong = m.code and status ='0') as count01 from machine as m ,
line as l group by id;
27 mysql枚举类型
不建议使用,使用mybatis更新的时候,会造成长度截取,发生错误
28 显示字段的comment内容,显示表字段的注释
show full columns from orders;
29 查询统计
select m.* , (select count(1) from line where belong = '0001' and status ='0') as count01 from machine as m ,
line as l where m.code ='0001' and l.belong ='0001' order by id limit 1;
30 根据日期查询,表为messages,后面是日期格式,参数值
根据年月日
select * from message where date_format(date,'%y%m%d') ='20220427';
根据年月
SELECT * FROM messages where DATE_FORMAT(date,'%Y%m') = '202204'
SELECT * FROM messages where DATE_FORMAT(date,'%Y-%m') = '2022-04'
注意,Y代表年份,只能是大写,这里的月份的0,不能省略
根据年
SELECT * FROM messages where DATE_FORMAT(date,'%Y') = '2022'
根据年月日区间查询
SELECT * FROM messages where DATE_FORMAT(date,'%Y%m%d') BETWEEN '20170601' and '20170625'
根据年月区间查询
SELECT * FROM messages where DATE_FORMAT(date,'%Y%m') BETWEEN '201706' and '201708'
根据年区间查询
SELECT * FROM `dateTest` where DATE_FORMAT(date,'%Y') BETWEEN '2016' and '2018'
根据年月日查询,字段为create_time;
SELECT * FROM `admin` where date(create_time) = '2021-01-07' ;
按照小时查询
SELECT * FROM `mqtt_test_log` where DATE_FORMAT(create_time, '%Y-%m-%d %H') = '2022-12-22 17';
31
mysql 根据日期时间查询数据 - anobscureretreat - 博客园
MySql数据库出现 1396错误
1、安装MySql数据库后。创建新的用户。有可能会出现 1396这个错误,
2、解决的办法如下:假装有你需要创建的这个用户、先删了。再创建。
3、这样就可以解决用户创建不成功的问题了。
32 查询某个表 的重复数据, 比如a标的 ,字段1,字段2 ,分别相同的数据。
SELECT p1.id,p2.id FROM personmenu p1,personmenu p2
where p1.id!=p2.id and p1.openid = p2.openid and p1.types_id = p2.types_id;
33 根据两个字段分别相同,的查询排序
select count(1) c from personmenu
group by types_id,openid
order by c desc;
34 同时成立
SELECT * FROM `order` where (state=3 and actual_receiver =5 ) or (state=4 and actual_receiver =5 ) ;
35 查询时间最大的那条数据
select * from mqtt_test_log where create_time = ( SELECT max( create_time ) FROM mqtt_test_log ) ;
36 查出今天的数据
select * from mqtt_test_log where to_days(create_time) = to_days(now());
37查询某个字段值为最大或者最小的 结果。该字段是 数字类型
SELECT max(heart_rate) heart_rate from health
where date(create_time ) = '2022-12-26';
SELECT min(blood_oxygen) blood_oxygen from health
where date(create_time ) = '2022-12-26';
38 查询某几天的 某个字段的每天的最大值,最小值
select
max(heart_rate) maxHeartRate ,
min(heart_rate) minHeartRate ,
max(blood_oxygen) maxBloodOxygen,
min(blood_oxygen ) minBloodOxygen,
DATE_FORMAT(create_time,'%Y-%m-%d') ab
from health
where device_user_id = 0
and equipment_no = '123456789asd'
and find_in_set( date(create_time), '2022-12-24,2022-12-25,2022-12-26')
GROUP BY ab;
注意日期格式 Y只能是大写
39 两个时间字段相减
SELECT timediff(end_time,start_time) result
FROM `equipment_opening_duration`
where id = '595efc312a6e4e0ab893a3364433ee9c';
40 order by 和group by共同使用,非常简单版本
select * from (
SELECT id, create_time
FROM `equipment_opening_duration`
where to_days(create_time) = to_days(now()) group by id )
f1 order by create_time asc;
41 查询某个时间段的数据
select * from equipment_opening_duration
where
equipment_no = '213121ada3123'
and port_id = '2'
and '2023-1-3 09:58' > start_time
and ( end_time < '2023-1-3 09:58' or end_time is null )
and (end_time > '2023-1-3 09:00' or end_time is null)
order by start_time
42 查出某
select * from oxygen_port where user_id is not null and user_id !='';
个字段 不是 null也不是空字符串的数据
43 查询最大的时间
select max(create_time ) from user
44 查询最大的id
select max(id) from user
查询id最大的那条数据
select * from user where id = (select max(id) from user )
45 给时间字段 添加索引 ,注意, 里面不是单引号,而是键盘左上角的那个esc下面的那个按键。
alter table equipment_opening_duration03 add index
`start_time_index` (`start_time`);
46 mysql中的 大于,小于,等于
使用data_format() 函数
比如 > '2023-01-30', 会查出来 2023-01-30 23:59:59之后的数据,
如果是 >= '2023-01-30' 会查出来 2023-01-30 :00:00:00之后的数据
使用str_to_date()函数
比如 > '2023-01-30', 会查出来 2023-01-30 00:00:00之后的数据,
比如 > = '2023-01-30', 会查出来 2023-01-30 00:00:00之后的数据,
啥函数都不用
比如 > '2023-01-30', 会查出来 2023-01-30 00:00:00之后的数据,
比如 >= '2023-01-30', 会查出来 2023-01-30 00:00:00之后的数据,
比如 = '2023-01-30' ,查出来的结果为空,这个用法不正确
47 删除符合某个条件的许多 数据, 注意,多了一层封装
delete from equipment_opening_duration
where id in
( select id from (
select id from equipment_opening_duration
where 60 > TIMEDIFF(end_time,start_time)
) a ) ;
48 按照 某个字段 进行分组,查看该字段出现的次数
SELECT count(*) FROM `personmenu` group by openid
49 按照某个字段进行分组,统计该字段的 不重复的 总个数
select count(*) from ( SELECT count(*) FROM `personmenu` group by openid) f1;
50 按照某个统计结果作为条件进行 查询
select openid,count(1) from personmenu GROUP BY openid HAVING count(1)=60;
51 in和find_in_set的区别之一
in走索引,find_in_set全表扫描,in超快。
52 使用list<T>来接收 查询结果,
如果是单表查询, 比如根据id查用户, 如果查不到, 那么返回值list集合。size() 为0,
如果是查该表的统计类信息, 比如 max(id) 等, 如果差不到,那么list集合 。size() 为1, 里面的元素 为null
53
假如用户表,没有id为1的用户, name
select * from user where id = 1; 返回值为null
但是 如果
select count(*) from user where id = 1; 返回值为0