MySQl数据库必知必会sql语句(加强版)

2024-07-24 12:52:01

字体：大中小

来源：转载

供稿：网友

这一篇属于加强版，问题和sql语句如下。

创建users表，设置id,name,gender,sal字段，其中id为主键

--------------------------------------------------------------------------------------

一对一：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 c on u.id = c.uid where u.name = 'AA'; -- select u.name "姓名",c.num "身份证号" from users u inner join cards c on u.id = c.uid where 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 e on g.id = e.gid where g.name = '开发部'; -- select g.name "部门",e.name "员工" from groups g inner join emps e on g.id = e.gid where g.name = '开发部';

------------------------------------------------------

多对多：查询"赵"教过哪些学生

上一篇：mysql导出查询结果到csv的实现方法

下一篇：Windows平台下MySQL安装与配置方法与注意事项