本文给大家分享了一篇关于mysql数据库必会sql语句加强版内容，非常不错，具有参考借鉴价值，需要的朋友参考下吧。
这一篇属于加强版，问题和sql语句如下。
创建users表，设置id,name,gender,sal字段，其中id为主键
drop table if exists users;create table if not exists users(id int(5) primary key auto_increment,name varchar(10) unique not null,gender varchar(1) not null,sal int(5) not null);insert into users(name,gender,sal) values('AA','男',1000);insert into users(name,gender,sal) values('BB','女',1200);
一对一：AA的身份号是多少
drop table if exists users;create table if not exists users(id int(5) primary key auto_increment,name varchar(10) unique not null,gender varchar(1) not null,sal int(5) not null);insert into users(name,gender,sal) values('AA','男',1000);insert into users(name,gender,sal) values('BB','女',1200);drop table if exists cards;create table if not exists cards(id int(5) primary key auto_increment,num int(3) not null unique,loc varchar(10) not null,uid int(5) not null unique,constraint uid_fk foreign key(uid) references users(id));insert into cards(num,loc,uid) values(111,'北京',1);insert into cards(num,loc,uid) values(222,'上海',2);
【注：inner join表示内连接】
select u.name "姓名",c.num "身份证号"from users u inner join cards con u.id = c.uidwhere u.name = 'AA';--select u.name "姓名",c.num "身份证号"from users u inner join cards con u.id = c.uidwhere name = 'AA';
一对多：查询"开发部"有哪些员工
创建groups表
drop table if exists groups;create table if not exists groups(id int(5) primary key auto_increment,name varchar(10) not null);insert into groups(name) values('开发部');insert into groups(name) values('销售部');
创建emps表
drop table if exists emps;create table if not exists emps(id int(5) primary key auto_increment,name varchar(10) not null,gid int(5) not null,constraint gid_fk foreign key(gid) references groups(id));insert into emps(name,gid) values('哈哈',1);insert into emps(name,gid) values('呵呵',1);insert into emps(name,gid) values('嘻嘻',2);insert into emps(name,gid) values('笨笨',2);
查询"开发部"有哪些员工
select g.name "部门",e.name "员工"from groups g inner join emps eon g.id = e.gidwhere g.name = '开发部';--select g.name "部门",e.name "员工"from groups g inner join emps eon g.id = e.gidwhere g.name = '开发部';
多对多：查询"赵"教过哪些学生
创建students表
drop table if exists students;create table if not exists students(id int(5) primary key auto_increment,name varchar(10) not null);insert into students(name) values('哈哈');insert into students(name) values('嘻嘻');
创建teachers表
drop table if exists teachers;create table if not exists teachers(id int(5) primary key auto_increment,name varchar(10) not null);insert into teachers(name) values('赵');insert into teachers(name) values('刘');
创建middles表 primary key(sid,tid) 表示联合主键，这两个字段的整体要唯一
drop table if exists middles;create table if not exists middles(sid int(5),constraint sid_fk foreign key(sid) references students(id),tid int(5),constraint tid_fk foreign key(tid) references teachers(id),primary key(sid,tid));insert into middles(sid,tid) values(1,1);insert into middles(sid,tid) values(1,2);insert into middles(sid,tid) values(2,1);insert into middles(sid,tid) values(2,2);
查询"赵"教过哪些学生
select t.name "老师",s.name "学生"from students s inner join middles m inner join teachers ton (s.id=m.sid) and (m.tid=t.id)where t.name = '赵';--select t.name "老师",s.name "学生"from students s inner join middles m inner join teachers ton (s.id=m.sid) and (t.id=m.tid)where t.name = "赵";
------------------------------------------------------------------------------
将5000元（含）以上的员工标识为"高薪"，否则标识为"起薪"
将薪水为NULL的员工标识为"无薪"
将5000元（含）以上的员工标识为"高薪"，否则标识为"起薪"
将7000元的员工标识为"高薪",6000元的员工标识为"中薪",5000元则标识为"起薪",否则标识为"试用薪"
------------------------------------------------------------------------------
内连接（等值连接）：查询客户姓名，订单编号，订单价格
【注：customers c inner join orders o使用了别名，以后o就代表orders】
select c.name "客户姓名",o.isbn "订单编号",o.price "订单价格"from customers c inner join orders oon c.id = o.customers_id;--select c.name "客户姓名",o.isbn "订单编号",o.price "订单价格"from customers c inner join orsers oon c.id = o.customers_id;
on+两张表连接的条件.一张表的主键，一张表的外键
内连接：只能查询出二张表中根据连接条件都存在的记录，有点类似于数学中交集
----------------------------------------------------
外连接：按客户分组，查询每个客户的姓名和订单数
外连接：既可以根据连接条件查询出二张表中都存在的记录，也能根据一方，强行将另一方就算不满兄条件的记录也能查询出来
外连接可以细分为：
<左外连接 : 以左侧为参照，left outer join表示select c.name,count(o.isbn)from customers c left outer join orders oon c.id = o.customers_idgroup by c.name;-->右外连接 : 以右侧为参照，right outer join表示select c.name,count(o.isbn)from orders o right outer join customers con c.id = o.customers_idgroup by c.name;
left outer join表示左边的内容都会显现出来，例如customers c left out join 表示会把customers中的某列所有内容都找出来
------------------------------------------------------
自连接：求出AA的老板是EE。把自己想象成两张表。左右各一张
select users.ename,bosss.enamefrom emps users inner join emps bossson users.mgr = bosss.empno;select users.ename,bosss.enamefrom emps users left outer join emps bossson users.mgr = bosss.empno;
------------------------------------------------------------------------
演示MySQL中的函数（查询手册）
日期时间函数：
select addtime('2016-8-7 23:23:23','1:1:1'); 时间相加select current_date();select current_time();select now();select year( now() );select month( now() );select day( now() );select datediff('2016-12-31',now());
字符串函数：
select charset('哈哈');select concat('你好','哈哈','吗');select instr('www.baidu.com','baidu');select substring('www.baidu.com',5,3);
数学函数：
select bin(10);select floor(3.14);//比3.14小的最大整数---正3select floor(-3.14);//比-3.14小的最大整数---负4select ceiling(3.14);//比3.14大的最小整数---正4select ceiling(-3.14);//比-3.14大的最小整数---负3，一定是整数值select format(3.1415926,3);保留小数点后3位，四舍五入select mod(10,3);//取余数select rand();//
加密函数：
select md5('123456');
返回32位16进制数 e10adc3949ba59abbe56e057f20f883e
演示MySQL中流程控制语句
use json;drop table if exists users;create table if not exists users(id int(5) primary key auto_increment,name varchar(10) not null unique,sal int(5));insert into users(name,sal) values('哈哈',3000);insert into users(name,sal) values('呵呵',4000);insert into users(name,sal) values('嘻嘻',5000);insert into users(name,sal) values('笨笨',6000);insert into users(name,sal) values('明明',7000);insert into users(name,sal) values('丝丝',8000);insert into users(name,sal) values('君君',9000);insert into users(name,sal) values('赵赵',10000);insert into users(name,sal) values('无名',NULL);
将5000元（含）以上的员工标识为"高薪"，否则标识为"起薪"
select name "姓名",sal "薪水",if(sal>=5000,"高薪","起薪") "描述"from users;
将薪水为NULL的员工标识为"无薪"
select name "姓名",ifnull(sal,"无薪") "薪水"from users;
将5000元（含）以上的员工标识为"高薪"，否则标识为"起薪"
select name "姓名",sal "薪水",case when sal>=5000 then "高薪"else "起薪" end "描述"from users;
将7000元的员工标识为"高薪"，6000元的员工标识为"中薪",5000元则标识为"起薪"，否则标识为"试用薪"
select name "姓名",sal "薪水",case salwhen 3000 then "低薪"when 4000 then "起薪"when 5000 then "试用薪"when 6000 then "中薪"when 7000 then "较好薪"when 8000 then "不错薪"when 9000 then "高薪"else "重薪"end "描述"from users;
更多数据科学知识，请扫码关注：全栈数据
