MySQL语句

MySQL基本操作

连接数据库
mysql -hlocalhost -uroot -p123456
#-hlocalhost -h host端口，当前数据库所处的服务器ip地址，域名或者主机名
#localhost 表示本地，如果是本地连接可以省略
#-uroot -u user用户 root用户表示用户
#-p -password 密码
推荐方式：
mysql -uroot -p
Enter password: ******
– 展示当前数据库中所有的数据表
show databases;
– 创建数据库
create database lqq2001;
– 创建数据库过程修改编码集
create database lqq2001 character set latin1;
create database lqq2001 character set gbk;
– 查看创建数据库的详细内容
show create database lqq2001;
– 修改数据库的编码集
alter database lqq2001 charaset set gbk;
– 删除对应数据库
drop database lqq2001;
– 选择使用数据库
user lqq2001;
– 让MySQL告知当前使用的数据库是哪一个
select database();

查询语句【重点】DQL

1.查询语句

select 字段名 from 表名
1.1基本查询
select * from user;
1.2查询结果字段进行计算
select ID,NAME,SALARY * 12 from user;
1.3去重查询
select distinct NAME ,ID from user;
1.4别名
select ID as’学号’，NAME as ‘姓名’ from user;

2.排序

2.1升序或者降序
select ID,NAME from user order by id dese/asc;
2.2多条件排序
select ID,NAME ,SALARY from user order by id dese/asc,SALARY asc/desc;

3.条件查询where

select fieldName from tbName where condition;

3.1等值判断
select ID,NAME,SALARY from user where SALARY =10000 ;
3.2不等职判断
select ID,NAME,SALARY from user where SALARY <10000 ;(<,>,<>,!=)
3.3逻辑判断(and, or, not)
select ID,NAME,SALARY from user where SALARY <10000 and ID >10 ;
3.4 区间 between and
select ID,NAME,SALARY from user where SALARY between 5000 and 10000 ;
3.5NULL值判断
select ID,NAME,SALARY from user where SALARY is null;
3.6枚举查询in
select ID,NAME,SALARY from user where SALARY in (5000,3000,5050);
3.7模糊查询
select ID,NAME,SALARY from user where NAME like ‘%’；
3.8分支结构查询

case
when condition1 then ret1
when condition2 then ret2
when condition3 then ret3
end
– 从case开始，到end结束。满足条件对应一个结果，类似于Java中的switch case
select ID,NAME,SALARY ,
case
when SALARY >10000 then ‘高薪’
else ‘普通’
end as ‘收入水平’ from user;

4.内置方法

4.1sum()
select sum(SALARY) from user;
4.2avg()
select avg(SALARY) from user;
4.3min()
select min(SALARY) from user;
4.4max()
select max(SALARY) from user;
4.5count
select count(*) from user;