mysql语句

创建表

create table  user(userId 字符类型,username 字符类型,password 字符类型)

1、向user表插入数据

inset into user(username,password) values("xx","12345")

2、修改user表的内容

update password('3232323') from user

3、查询user表的所有内容

select * from user

4、查询在user表中username为gg的所有信息

select * from user where username='gg'

5、查询user中所有的username

select username from user

6、查询user表中的多个列

select username,password from user

7、查询user表的某列并修改别名

select username as 用户名,password as 密码 from user

8、查询user的某列并删除重复字段

select distinct password as 密码 from user

9、查询并筛选

select userId,username from user where userId>3

10、模糊查询

select * from user where password like '%2'         like语句不加百分号和下划相当于等号
select * from user where password like '__2%'          一个_表示1个字符，%表示0到多个字符
select * from user where username like '%\%%'     查询%
select * from user where username like '%\e%%' escape 'e'   查询\%
select * from user where password not like '%2%'
select * from user where password like '__2%' or '3%'

select * from user where password like '__2%' and password like '5%'

11、查询并排列

select * from user where userId in(2,3,4 ) order by flag desc,userId asc        desc降序，asc升序

12、将查询的字段转换为小写

select userId,lower(username) from user    

12、将查询的字段转换为大写
select userId,upper(username) from user   

13、连接字段或字符串
select concat(userId,username) from user  

14、查询某字段或字符串的长度
select length(username) from user

15、截取字段，第一个2（从第二个开始截），第二个2（截取两个字符））
select userId,substr(username,2,2) from user    

16、查询显示求平均数
select avg(sal) from emp 

17、查询某列的最大值
select max(sal) from emp

18分组,过滤
select deptno,avg(sal) from emp group by deptno having avg(sal)  > 2000 
select job,deptno,avg(sal) from emp group by job

19、子查询
select * from emp where sal > (select avg(sal) from emp) order by sal         子查询查出来的结果是一张临时表（表实际上不存在）
select * from (select * from emp) e
select * from emp where empno in (select empno from emp where empno=7369 or empno=7499 or empno=7521)

20、表连接
select * from emp e,dept d where e.deptno=d.deptno
select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno
select * from emp e inner join dept d on e.deptno=d.deptno
select e.*,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno        left join左边有字符就会显示，right join表示右边有字符显示，inner join两边都有字符显示
select e.*,d.dname,d.loc from emp e right join dept d on e.deptno=d.deptno 

21、表的分页
select * from emp order by empno limit 0,3