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}
6.消除重复(DISTINCT):SELECT DISTINCT userName from `user`
7.模糊查询:like
SELECT * from emp WHERE ename LIKE '_M%'[查询表中用户名第二个字母是M的名字,下划线表示占位]
%表示零个或多个字符【‘%s’--表示以S结尾;‘s%’--表示以S开头】。
‘__M_%’:名字中第三个字母为M,后面至少有一个字符。
MySQL安装(MySQL官网)
有些情况缺少一个vcre.dll文件,百度搜索:vcredist2013_x64.exe下载.dll链接
我这里下载的是5.5版本,在MySQL官网进行下载,5.5;5.6;5.7均有。