常用sql语句汇总

创建订单表
create table  orders
(
ddbh char(4) primarykey,#主键
khbh char(6), #外键
spm varchar(100) not null,
dj  double(5,1) #总位数6位数字，小数1位
foreign  key(khbh)  references  customer(khbh)
)
#显示所有数据库名称
show databases
#定位数据库
use  database
#删除表
drop  table  orders
#查看表结果
desc  orders
#删除姓名叫张三的数据
delete  from  customer where xm='张三'
#插入数据
insert into customer  values('11','赵四','男','12345678')
#修改数据
update  customer  set xm='小红'   where   xm='李明'
#查询语句
计算
select  23*45+35*29
#看当前时间
select  now(),current_timestamp()
#看年月日
select  current_date
#查看会员的姓名和性别
select  name,gender from member
#查询会员性别
select distinct  gender  from member
#显示前五人
select * from  member limit 5
#显示会员从第三行开始的6行
select * from member limit 2,6
#只看会员第三行
select *  from member limit 3,1
107、进mysql -uroot  -p入数据库：mysql -uroot  -p回车后输入密码：abc123456
108、停止服务器：net stop mysql80;
109、mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --init-file="E:/temp.txt" --console；修改root密码
110、temp文档：Alter user 'root'@'localhost'  identified  by '123456';
111、开启服务器net start mysql80；
112、unsigned 没有负数的整数
113、float(10,2)代表算上小数点后面一共10位，2位小数点
114、alter table student  #给student表添加其他项目
115、add address  varchar(200)  not null,
116、add  home_tel  char(11)  not null;
117、alter table  student#修改字段类型和约束
118、modify  home_tel  varchar(20)  not null;
119、#修改字段名称
120、alter  table  student
121、change  address  home_address  varchar(20) not null;
122、 #删除字段
123、 alter table student
124、 drop home_tel,
125、 drop  home_address;
126、DML:添加，修改，删除，查询   DCL:用户，权限，事物，DDL:逻辑库，数据表，视图，索引。
127、主键约束primary key：字段唯一，不能为null,非空约束not null：字段值不能为空
128、唯一约束unique：字段值唯一，可以为null    外键约束foreign key：保持关联数据的逻辑性
129、自动生成主键
create   table  t_teacher(
id  int  primary  key   auto_increment
)
索引：
#索引：index
create  table  t_message(
id int unsigned  primary key,
content  varchar(200)  not null,
type enum("公告","通报","个人通知") not null,
create_time  timestamp   not null,
index idx_type  (type)
);
#删除索引
drop index idx_type  on t_message;
#已存在的表添加与删除索引
create index  idx_type on t_message(type);
show index from t_message;#展示索引
alter table  t_message   add index  idx_type(type);
sql文件导出：mysqldump  -uroot -p  demo > D:/demo.sql
sql导入：use demo;
source  backup.sql
导入表结构：在数据库右击选择运行
导入表文件：在表上右键选择导入文件
导出表结构：选择转存储仅表结构
&lt;  <小于
&gt; > 大于
&amp;  & 和号
&apos; ‘ 单引号
&quot “”双引号
<![cdata[]]>
URL：jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
-- drop table temp;
-- create table temp(
-- id int unsigned  PRIMARY key,
-- num decimal(20,10)
-- );
alter table student  #给student表添加其他项目
add address  varchar(200)  not null,
add  home_tel  char(11)  not null;
alter table  student#修改表结构
modify  home_tel  varchar(20)  not null;
desc student
#修改字段名称
alter  table  student
change  address  home_address  varchar(20) not null;
 #删除字段属于ddl语句
 alter table student
 drop home_tel,
 drop  home_address;
create  table  t_teacher(
id  int  unsigned  primary  key auto_increment,
name  varchar(20)   not  null,
tel  char(11)  not  null  unique,
married  boolean  not null  default  false
);
create  table t_dept1(
deptno  int unsigned primary key,
dname  varchar(20)  not null  unique,
tel  char(4)  unique

);
create table  t_emp1(
empno int unsigned  primary key,
ename  varchar(20)  not null,
sex  enum("男","女") not null,
deptno  int  unsigned  not null,
hiredate  date  not null,
foreign key(deptno)  references  t_dept1(deptno)
);
#索引index
create  table  t_message(
id int unsigned  primary key,
content  varchar(200)  not null,
type enum("公告","通报","个人通知") not null,
create_time  timestamp   not null,
index idx_type  (type)
);
#删除索引
drop index idx_type  on t_message;
#已存在的表添加与删除索引
create index  idx_type on t_message(type);
show index from t_message;#展示索引
alter table  t_message   add index  idx_type(type);
#查询前5条数据,limit:从几条到第几条，10,5意思是11-15条
select empno,ename  from t_emp  limit 5;
#结果集排序:order by  desc:降序  asc升序
select  empno,ename,sal,deptno  from  t_emp order by sal asc,hiredate desc limit 0,5;#查看前面五行
select hiredate, empno ,ename from  t_emp order by  hiredate  asc;
#去除重复记录 distinct:只能使用一次，只能放在第一字段的前面
select distinct job,ename from  t_emp;
select empno,ename,sal,deptno from  t_emp  where  (deptno=10 or deptno=20) and sal>=2000;
select  empno,ename,sal,hiredate   from  t_emp  where  deptno=10  and 
 (sal+ifnull(comm,0))*12>=15000 and datediff(now(),hiredate)/365>=20;
select  10+10*ifnull(null,0);
select   empno, ename, sal, job, deptno,hiredate             from  t_emp where deptno in(10)  and job!="salesman"  and  hiredate<"1985-01-01";
select    ename,comm     from t_emp  where comm is not null;#不为空
select    ename,comm,sal     from t_emp  where comm is null
and sal  between 2000  and 3000 and ename like "%A%";#为空_alak
#%A%名字中有a,a%:名字开头是a,%a:名字结尾是a,_alak:首字母忘记其他字母记得。regexp：正则表达式
select    ename,comm,sal     from t_emp  where comm is not null
and sal  between 1000  and 3000 and ename regexp "^[\\u4e00-\\u9fa5]{2,4}$"
select ename, deptno
from t_emp
where not deptno in(10,20) xor sal>=2000;
#聚合函数:avg,sum,max,min,count：统计非空数值
select  avg(sal+ifnull(comm,0)) as avg from  t_emp;
select     sum(sal),max(sal+ifnull(comm,0))from t_emp  where  deptno  in(10,20);
select max(length(ename))  from  t_emp where deptno in(10,20,30);
select    count(*),count(comm)         from  t_emp;
select  count(*)  from  t_emp  
where  deptno in(10,20) and sal>=2000 and datediff(now(), hiredate )/365>15;
# group by：根据什么分组，select 后边只能跟聚合函数或者group by后面的字段。
select  deptno,round(avg(sal))  from t_emp  group by deptno;
 select deptno,job,count(*),avg(sal)
 from t_emp
 
 group by deptno,job;
 select deptno,count(*)
 select  deptno,avg(sal),sum(sal),min(sal),count(*)
 from  t_emp  group by deptno  with rollup;
 select    deptno,count(*),GROUP_CONCAT(ename)   from t_emp where sal>=2000 group by deptno;
 #having 要和group by 一起用，不能单独使用
 select    deptno  from t_emp 
  group by deptno having avg(sal) >=2000;
 #表连接查询
select  e.empno,e.ename,d.dname
from  t_emp e join t_dept d on e.deptno=d.deptno;
#表连接的分类
select  e.empno,e.ename,d.dname
from  t_emp e join t_dept d where e.deptno=d.deptno;	
select       ename              from   t_emp  where  deptno=
(select   deptno from  t_emp  where  ename="scott") and ename!="scott";
select  e2.ename
from t_emp  e1  join  t_emp e2  on   e1.deptno=e2.deptno
where  e1.ename="scott" and e2.ename!="scott";
#内连接查询信息
SELECT  e.empno,e.ename,e.sal
from  t_emp e  join  (select  avg(sal) avg from t_emp ) t 
on e.sal>=t.avg;
SELECT d.dname, count(*),max(e.sal),min(e.sal),avg(e.sal)
from  t_emp e join t_dept d on e.deptno=d.deptno
where d.dname="research";
select  floor(28.9);#强制退位
select cell(1.1);#强制进位
SELECT  e.job,max(e.sal+ifnull(e.comm,0)), min(e.sal+ifnull(e.comm,0)),avg(e.sal+ifnull(e.comm,0))  
from t_emp e  join t_salgrade s on (e.sal+ifnull(e.comm,0))between 
s.losal and s.hisal
group by  e.job;
#查询每个底薪超过部门平均底薪的员工信息
select  e.ename,e.sal, e.deptno    from  t_emp e join 
(select deptno,avg(sal) as avg from   t_emp  group by deptno) t
on e.deptno=t.deptno and e.sal>=t.avg ;
#外连接
select  e.empno,e.ename
from t_emp e left join t_dept d on e.deptno=d.deptno
select  e.empno,e.ename
from t_dept d right join t_emp e on e.deptno=d.deptno;
#查询每个部门的名称和部门人数
(select count(e.deptno),  d.dname,e.deptno  from  t_emp e right  join 
t_dept d on  e.deptno=d.deptno group by e.deptno)
UNION 
(select count(*),  d.dname,e.deptno  from  t_emp e left  join 
t_dept d on  e.deptno=d.deptno group by e.deptno);
select e.empno,e.ename,e.deptno
from t_emp e left join t_dept d on e.deptno=d.deptno  where e.deptno=10;
#子查询
select empno,ename,sal
from t_emp
where  sal>=(select avg(sal) from t_emp);
select e.empno,e.ename,(select dname from t_dept d where deptno=e.deptno)  from t_emp e;
select deptno,ename 
from t_emp  where deptno in
(select deptno from t_emp  where ename in("ford","martin"))
and ename not in("ford","martin") ;
#:all比那两个人都高,any:比结果集中任何一个高就可以
select ename,sal  from t_emp where sal>=all
(select sal from t_emp where ename  in("ford","martin"))
and ename not in("ford","martin");
#exists 
SELECT  empno,ename,sal
from t_emp 
where exists(
select *  from t_salgrade  where sal between losal and hisal  and 
grade in (3,4)
);
#insert into  表名（字段1，字段2） values(值1，值2，)
insert into t_dept(deptno,dname,loc)
values(50,"技术部","北京");
insert into t_dept(deptno,dname,loc)
values(60,"后勤部","北京"),(70,"保安部","北京");
#向技术部添加一条员工记录
insert into t_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(8001,"刘娜","saleman",8000,"1988-12-20",2000,null,
(select deptno from t_dept where dname="技术部"))
insert into t_emp set empno=8002,ename="jack",job="salesman",
mgr=8000,hiredate="1985-3-14",sal=2500,comm=null,deptno=50;
insert ignore  into t_dept(deptno,dname,loc)
values(70,"A","南京"),(80,"B","上海");#只插入数据库不存在的数据
update t_emp  set  empno=empno+1,mgr=mgr+1
order by empno desc;
update t_emp  set  sal=sal-100
order by sal+ifnull(comm,0) desc limit 3;
update  t_emp  set  sal=sal+200  where datediff(now(),hiredate)/365>20
and deptno=10;
#update 表1 join 表2  on 条件  set  字段=值1，字段等于值2，可以修改多个表记录
update t_emp e join  t_dept d 
set  e.deptno=d.deptno ,e.job="analyst"
where e.ename="Allen" and d.dname="research";
update  t_emp e join
(select avg(sal) avg from t_emp )t
on e.sal<t.avg  set  e.sal=e.sal+150;
update t_emp e left join t_dept d on e.deptno=d.deptno
set e.deptno=20
where  e.deptno is null or (d.dname="sales" and e.sal<2000)
delete  from  t_emp  where  datediff(now(),hiredate)/365>20
and  deptno=10;
delect from t_emp 
where deptno=20 order by sal+ifnull(comm,0) desc limit 1;
delete  e,d#表连接删除两张表
from t_emp e join t_dept d on e.deptno=d.deptno
where d.dname="sales";
delete   from t_emp e join 
(select deptno, avg(sal)  a from t_emp  group by  deptno) t
on e.deptno=t.deptno  and e.sal<t.a  ;
#两个表删除时可以delete 表 from 表
delete e from t_emp e join 
(select deptno from t_emp where ename="king" ) t 
on e.deptno=t.deptno  ;
#外连接
delete e
from  t_emp e left join t_dept d on e.deptno=d.deptno 
where d.dname="sales"  or  e.deptno  is null;
#删除数据全部记录
truncate table t_emp;
select abs(-100);#绝对值
select round(4.638*100)/100;#保留整数
select floor(9.9);强制退位
select ceil(3.2);强制进位
select power(2,3);#2的3次密
select log(7,3);
select ln(10);
#数字函数
select sqrt(9);#给9开平方
select pi();
select sin(radians(30));
select round(3.4);
#lower:小写  upper:大写  length:长度  concat:连接  instr:有几个字母a
select lower(ename),upper(ename),length(ename),
concat(sal,"$"),instr(ename,"A")
from t_emp;
select insert ("你好",1,1,"先生");
select replace("你好先生","先生","女士");
#substr:截图字符串   substring:截取字符串  lpad:左侧填充字符
#rpad:右侧填充字符instr:查询字符出现的位置
select substr("你好世界",3,2),substring("你好世界",3,2);
select lpad(substring("13312345678",8,4),11,"*");
select rpad(substring("李晓娜",1,1),length("李晓娜")/3,"*");
select trim("        Hello World   ");#去掉两边空白
select  e.empno,e.ename,d.dname,
if(d.dname="SALES","礼品A","礼品B")
from  t_emp e join  t_dept d on e.deptno=d.deptno;
select  e.empno,e.ename,d.dname,
case 
 when d.dname="sales"  then  "p1"
 when  d.dname="accounting"  then  "P2"
 when d.dname="research"   then "p3"
 end as place
from t_emp e join t_dept d on e.deptno=d.deptno;
update  t_emp e left  join t_dept d on e.deptno=d.deptno
left join (select deptno,avg(sal) as avg from t_emp group by deptno ) t 
on e.deptno=t.deptno
set e.sal=(
case 
when d.dname="sales"  and  datediff(now(),e.hiredate)/365>=20
then e.sal*1.1
when d.dname="sales"  and  datediff(now(),e.hiredate)/365<20
then e.sal*1.05 
when d.dname="accounting"  then  e.sal+300
when d.dname="research" and e.sal<t.avg then  e.sal+200
when e.deptno is null then e.sal+100
end
);
#rollback:事物不生效
start transaction;
delete from t_emp;
delete from  t_dept;
select * from t_emp;
select * from t_dept;
rollback;#commit;提交后才能生效
start transaction ;
update t_emp set sal=7;
commit;
set session transaction isolation  level  read committed;
start  transaction;
select empno,ename,sal from t_emp;
commit;
set session transaction isolation  level  repeatable read;
set session transaction isolation  level  serializable;
use demo;
select * from t_emp;
create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男');
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男');
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男');
insert into Student values('04' , N'李云' , '1990-08-06' , N'男');
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女');
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女');
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女');
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女');
create table Course(C varchar(10),Cname nvarchar(10),T varchar(10));
insert into Course values('01' , N'语文' , '02');
insert into Course values('02' , N'数学' , '01');
insert into Course values('03' , N'英语' , '03');
create table Teacher(T varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , N'张三');
insert into Teacher values('02' , N'李四');
insert into Teacher values('03' , N'王五');
create table SC(S varchar(10),C varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
select student.s,student.sname,student.sage,student.ssex 
from student  left join(
select student.s from student  join sc on student.s=sc.S 
join course  on sc.c=course.c
join teacher on course.t=teacher.t
where teacher.tname="张三")
as s1 on student.s=s1.s
where s1.s is null;
SELECT
student.S,student.Sname,student.Sage,student.Ssex
FROM
student
LEFT JOIN (
SELECT
student.S
FROM
student
JOIN sc ON student.S = sc.S
JOIN course ON sc.C = course.C
JOIN teacher ON course.T = teacher.T
WHERE
teacher.Tname = "张三"
) AS s1 ON student.S = s1.S
WHERE
s1.S IS NULL;