這一篇屬于加強(qiáng)版，問題和sql語句如下。
創(chuàng)建users表，設(shè)置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的身份號(hào)是多少
| 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表示內(nèi)連接】
| select u.name "姓名",c.num "身份證號(hào)" from users u inner join cards c on u.id = c.uid where u.name = 'AA'; -- select u.name "姓名",c.num "身份證號(hào)" from users u inner join cards c on u.id = c.uid where name = 'AA'; |
---------------------------------------------
一對多：查詢"開發(fā)部"有哪些員工
創(chuàng)建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('開發(fā)部'); insert into groups(name) values('銷售部'); |
創(chuàng)建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); |
查詢"開發(fā)部"有哪些員工
| select g.name "部門",e.name "員工" from groups g inner join emps e on g.id = e.gid where g.name = '開發(fā)部'; -- select g.name "部門",e.name "員工" from groups g inner join emps e on g.id = e.gid where g.name = '開發(fā)部'; |
------------------------------------------------------
多對多：查詢"趙"教過哪些學(xué)生
新聞熱點(diǎn)
疑難解答
圖片精選
