sql语言笔记

操作库相关
create database db1 character set utf8;
create database student character set gbk;
show create database db1;
drop database db1;
use db1;
操作表相关
use db1;
show tables;
show create table db1;
desc student;
drop table person;
alter table 表名 add 字段名 字段类型;
alter table person add id int first;
alter table person add salary int after name;
rename table person to persons;
alter table person change 属性 新属性 新类型;
alter table emp drop age;
create table emp(name varchar(10)) charset utf8;
insert into 表名 values(值1，值2，值3);
insert into 表名(字段1，字段2) values(值1，值2)；
select 字段信息 from 表名 where 条件;
修改属性用alter修改字段用update
展示属性用show展示内容用select
insert into hero values(1,'诸葛亮','法师',18888),(2,'周瑜','法师',13888),(3,'孙悟空','打野',18888),(4,'小乔','法师',13888),(5,'黄忠','射手',8888),(6,'刘备','战士',6888);
update hero set money=28888  where money=18888;
alter table hero add gender varchar(10) after name;
update hero set gender='男';
delete *from hero;
 drop table hero;
create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t_date values("2019-12-12",null,null,null);
主键约束
创建时给表字段添加限制条件
主键：表示数据唯一性的字段称为主键
主键约束：唯一且非空
create table t1(id int primary key,name varchar(10));
create table t2(id int primary key auto_increment,name varchar(10));
source /home/soft01/桌面/emp.sql;
去重
select distinct job from emp;
select ENAME,JOB from emp where JOB!='程序员';
select ENAME,JOB from emp where JOB<>'程序员';
 select ename,sal from emp where sal>2000 and sal<3000;
 select ename,sal from emp where sal between 2000 and 3000;
select * from emp where sal=800 or sal=1500 or sal=3000;
select * from emp where sal in(800,1500,3000);
select *from emp where sal between 1000 and 2000;
select distinct job from emp where DEPTNO=1;
select ename,sal,DEPTNO from emp where DEPTNO=1 and sal in(800,1600,5000);
select *from emp where mgr is not null and DEPTNO=1;
模糊查询like
%：代表0或多个未知字符
_:代表1个未知字符
select ename,sal from emp order by sal desc;
select ename,sal from emp order by sal asc;
分页查询limit
 select ename as '姓名' from emp;
select ename '姓名' from emp;
select ename 姓名 from emp;
select max(sal) 最高工资,min(sal) 最低工资 from emp where deptno=2;
group by 字段名
where后面只写普通字段条件
having后面写聚合函数条件
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
select deptno,avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)>2000;
自增
auto_increment
limit 跳过条数，请求条数
select sum(sal) from emp where mgr is not null  group by deptno having sum(sal)>5400 order by sum(sal) desc limit 0,1;
select deptno,max(sal) from emp where mgr is not null group by deptno ;
select sal from emp where mgr is not null order by sal asc limit 4,2;
 select deptno,max(sal) from emp where mgr is not null group by deptno ;
select *from emp  order by deptno asc ,sal desc;
select *from emp group by deptno  order by deptno asc ,sal desc;
select count(*),job from emp where mgr is not null group by job order by
    -> count(*) desc;
 select sum(sal) from emp where deptno=3 group by deptno;
select *from emp where sal>(select avg(sal) from emp where deptno=1);
select * from emp where job=(select job from emp where ename='孙悟空') and ename!='孙悟空';
 select *from dept where deptno=(select deptno from emp where ename='白骨精');
 select * from dept where deptno in (select deptno from emp ) and deptno!=4;
关联关系：
1对1
1对多
多对多

关联查询：
同时查询多张

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
select e.ename,e.sal,d.dname,d.loc from emp e,dept d where  e.deptno=d.deptno and e.deptno=1;
select 字段信息 from A join B on 关联关系 where 条件
外连接
查询一张表的全部数据和另外一张表的交集数据
right join
select e.ename,d.* from emp e left join dept d on d.deptno=e.deptno;
select       from    表1 left/right join 表2  on d.deptno=e.deptno where 条件;

execute() 此方法可以用于任意sql语句，建议执行DDL包括create,drop,alter
executeUpdate(sql)此方法用于执行增删改的sql语句
executeQuery(); 此方法用于查询的sql语句

select a.oId,a.title,a.abstract,a.commentCount,a.viewCount,a.content,a.putTop,a.created,a.imgName,u.userName
 from article a join user u on a.authorId=u.oId order by a.putTop desc,a.created desc limit 0,8;

select t.oId,t.referenceCount,t.title from tag t join tag_article ta on t.oId=ta.tag_oId
where ta.article_oId=10;

 

create table t_user(id int primary key auto_increment comment '用户id',username varchar(20) not null unique comment '用户名',password varchar(20) not null comment '密码',age int comment '年龄',phone int comment '电话号码',email varchar(30) comment '邮 箱')charset=utf8mb4;