MySQL

最新推荐文章于 2022-06-14 19:22:07 发布

userwqk

最新推荐文章于 2022-06-14 19:22:07 发布

阅读量351

点赞数 2

本文链接：https://blog.csdn.net/userwqk/article/details/78543881

版权

SQL基本语句：{MySQL}

1. 创建表：create table user（userID,username，password）

2. 增加：insert into user (username,password) values ("dsa","54q")

3. 修改密码：update user set password="312" {修改了所有用户密码}

修改多个字段用逗号隔开（update user set password="312",flag=2）

加条件用where连接（update user set password="312",flag=2,sex=0 WHERE flag =2）

4.删除：delete from user WHERE userId = 3

5.查询：select * from user 【查询表】

select * from user WHERE userId=1;【查询表中Id=1】

select userName from user WHERE userId=1;【查询表中Id=1的userName，如果要查询多个字段，在userName后加{用逗号隔开}】

select userName AS 用户名,userId AS 用户Id from user【对字段取别名,AS可加可不加】

SELECT * from `user` WHERE `password` = ""
SELECT * from `user` WHERE `password` IS NULL [查询密码为空]
SELECT * from `user` WHERE `password` IS NOT NULL [查询密码不为空]
SELECT * from `user` WHERE userId >=3 AND userId <=6 [Id范围的查找]
SELECT * from `user` WHERE userId BETWEEN 3 AND 6 [现在一般用的]

SELECT * from `user` WHERE userId in (3,5,6) [选定查询]
SELECT * from `user` WHERE userId =3 or userId =5 OR userId=6
SELECT * from `user` WHERE userId NOT in (3,5,6) [不查询3,5,6]

SELECT * from `user` ORDER BY flag ASC [按照flag从小到大排序，，order by：对查询结果排序;ASC:升序排列（默认）；DESC:降序排列 ]
SELECT * from `user` ORDER BY flag DESC

SELECT LOWER(ename) from emp [LOWER()函数把大写换为小写]
SELECT UPPER(LOWER(ename)) from emp [UPPER()函数把小写换为大写]
SELECT LENGTH(ename),ename from emp [LENGTH()函数查询字符个数]

SELECT COUNT(*) FROM emp [COUNT()函数计算总数]
SELECT COUNT(*) 总员工数, AVG(sal)平均工资,MAX(sal)最高工资,MIN(sal)最低工资,SUM(sal)工资总和 from emp

SELECT deptno,COUNT(*) from emp GROUP BY deptno [按部门编号分组；group by:分组函数 ; deptno:部门编号]
SELECT deptno,COUNT(*) from emp GROUP BY deptno HAVING deptno>10 [HAVING：对分组条件过滤]
SELECT * from emp WHERE sal >(SELECT AVG( sal) from emp) [查询工资大于平均工资的]-------用到子查询
列如：
SELECT * from user WHERE userId in (SELECT userId from user WHERE userId>5)

SELECT e.ename ,e.deptno,d.dname from emp e , dept d WHERE e.deptno=d.deptno [连表查询]
SELECT e.*,d.dname from emp e , dept d WHERE e.deptno=d.deptno
SELECT e.*,d.dname from emp e INNER JOIN dept d ON e.deptno=d.deptno
SELECT e.*,d.dname from emp e LEFT JOIN dept d ON e.deptno=d.deptno[左连接]
SELECT e.*,d.dname from emp e , dept d WHERE e.deptno=d.deptno AND sal>1000

分页查询：
SELECT * from user LIMIT 0,5
注释：
{SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
//如果只给定一个参数，它表示返回最大的记录行数目：
mysql> SELECT * FROM table LIMIT 5; //检索前5个记录行
//换句话说，LIMIT n 等价于 LIMIT 0,n}