Common SQL statement query and sharing

Common SQL statement query and sharing

-- Create a database (File: main data file mdf = 1, secondary data file ndf> = 0, log file ldf> = 1)
-- File group: When 1mdf, 5 ndf (, 2), 10 ldf (, 4), they are divided into multiple groups for storage.

CREATE database studb;

-- Create Table teacher and student

create table teacher(tid int(10) primary key auto_increment,tname varchar(20),tage int(10));use studb;

create table student(sid int(10) primary key auto_increment,sname varchar(20),sage int(10),tid int(10) REFERENCES teacher(tid) );

-- Foreign key constraint: Who are the teachers of Michael Zhang ??

-- Select teacher. tname from teacher, student where student. sname = 'zhang san' select t. tname from teacher t, student s where s. sname = 'zhang san' and t. tid = s. tid

-- Create a course schedule

create table course(cid int(10) primary key,cname varchar(20),tid int(10) REFERENCES teacher(tid));

-- Create a score table

create table sc(scid int(10) primary key,sid int(10) REFERENCES student(sid),cid int(10) REFERENCES course(cid),score int(10));

-- Joint query: equivalent Query
-- 1 ..

Select c. cname from course c, student s, SC where s. sname = 'zhang' and s. sid = SC. sid and c. cid = SC. cid;

-- 2 ..

select sname from student s,course c,sc where c.cname='android' and sc.score>=60and s.sid = sc.sid and c.cid = sc.cid;

-- 3 ..
-- Subquery: when the condition is to be queried, I only know the student ID. I don't know the field "Xiao Zhang". Do you know the student ID of Xiao Zhang?

Delete from SC where sid = (select sid from student where sname = 'zhangzhang ');

-- The symbol in the middle of the subquery must be the fields associated with the two tables in the parent query and the subquery (generally the primary foreign key)

-- 4 ..

Update SC set score = score + 5 where cid = ????; Select tid from teacher where tname = 'instructor Lee '= 1 select cname from course where tid = 1 = course name, select cid from course where cname = 'android' = course IDupdate SC set score = score + 5 where cid = (select cid from course where cname = (select cname from course where tid = (select tid from teacher where tname = 'instructor li ')));

Articles you may be interested in:

• Solutions for SQL query statement wildcard and ACCESS fuzzy query like
• SQL Server SQL advanced query statement Summary
• SQL Server queries all database names, table names, and field names
• Mysql like query string Example Statement
• MySql date query statement details
• Mysql paging principle and efficient mysql paging query statements
• How to use SQL statements to query records with the same value under a certain field in the database
• Only two fields use one SQL statement to query the name, score, and ranking of a student in the table.