mysql常用操作(个人总结方便日后使用)

-- 查询表中所有数据

SELECT * FROM `user`

-- 查询id=

SELECT * FROM `user` WHERE id=13436636207

-- 查询若干列信息

SELECT id,pwd FROM `user` WHERE id=134567

-- 插入一条数据

INSERT INTO `user`(id,pwd) VALUES(13436637777,"654321")

-- 删除一条数据

DELETE FROM `user` WHERE id=12

-- 更新一条数据

UPDATE `user` set pwd="111111" WHERE id=134567

-- 查找指定区间的数据

SELECT * FROM `user` WHERE id BETWEEN 13436636207 AND 13436636209;

SELECT * FROM `user` WHERE id>=13436636209 OR id<=13436636207

-- 排序 asc(默认正序) desc（倒叙）

SELECT * FROM `user` WHERE id BETWEEN 13436636207 AND 13436636209 ORDER BY id;

-- 查找null数据

SELECT * FROM `user` WHERE createTime IS NULL

-- 模糊查询 以6开头的 包含6的 以6结尾的

SELECT * FROM `user` WHERE pwd LIKE '6%'

SELECT * FROM `user` WHERE pwd LIKE '%6%'

SELECT * FROM `user` WHERE pwd LIKE '%_6'

-- 查询 5-8行

SELECT * FROM `user` LIMIT 5,3

-- 查询并去重

SELECT DISTINCT * from `user`;

-- 查询时间日期等

select curdate(),curtime(),now() from dual;

-- 查看数据库及用户信息

select database(),version(),user() from dual;

-- 交叉连接

SELECT * FROM a CROSS JOIN b

-- 内链接(会丢数据,无法匹配的数据不会出现)

SELECT * FROM `user` INNER JOIN manage on `user`.id=manage.id

-- 左外连接(左边的数据全部都会出现)

SELECT * FROM `user` LEFT JOIN manage on `user`.id=manage.id

-- 右外连接(右边的数据全部都会出现)

SELECT * FROM `user` RIGHT JOIN manage on `user`.id=manage.id

-- 自然连接

SELECT * FROM `user` NATURAL JOIN manage

-- 全外连接

SELECT * FROM `user` FULL JOIN manage

-- 自连接(自己和自己连接--为了分清表user起了两个别名u1,u2)

SELECT * FROM `user` u1,`user` u2

SELECT * FROM `user` u1,`user` u2 WHERE u1.id=u2.id and u1.pwd<>u2.pwd