query the student numbers and names of students who have studied "001" and also learned the course number "002";
select student.sid,student.sname from student,sc
where student.sid=sc.sid and sc.cid=1 and exists
(select * from sc as sc_2 where sc.sid=sc_2.sid and sc_2.cid=2)
Why does the last part of the subquery section have to be sc_2.cid=2, and empty with sc.cid=2?
thanks for your anser.
sc.cid has been limited to 1 . sc_2 you can think of it as exactly the same table as sc , but not sc . The subquery select * from table1 where [exist] subquery traverses the table table1 to see if it can pass the subquery, and if so, the current row meets the condition.
assume that the sc table saves the selection of students who have taken odd-numbered courses (001003), and the sc_2 table saves the selections of students who have taken even-numbered courses (002004.)
select student.sid,student.sname from student,sc
where student.sid=sc.sid and sc.cid=1 and exists (select * from sc_2 where sc.sid=sc_2.sid and sc_2.cid=2)this makes it clearer.
take a look at the usage of the exists subquery , Chinese translation .
Previous: How cPP files delete data and insert data
Next: Python grammar problems
how does sql write from the database table to the display of the page? requirement: the first-level classification needs sorting, the first-level classification shows its subcategories, and the subcategories also need sorting. ...
there are two insert statements in the mysql trigger. How to implement the second inert with the result of the first insert DELIMITER $$ USE `nightclub`$$ DROP TRIGGER *!50032 IF EXISTS * `create_histroy_room_statuses`$$ CREATE *!50017 DEFIN...
ask how to write the sql statement ...
has always thought that index optimization is effective in where is useless in select but it seems that there is a performance difference between select nonclustered index columns, clustered index columns, unindexed columns and select *? ...
Table T_USER T_ROLE : the following is the table creation and data script drop table if exists T_USER; CREATE TABLE T_USER( USER_ID INT, USER_NAME VARCHAR(25) ); INSERT INTO T_USER VALUES(1, USER_A ); INSERT INTO T_USER VALUES(2, USER_B )...
in mysql, set a regular expression to filter strings ending in number select number from test where number REGEXP xxxxxxxxx . ...
A a_id a_title a_key a_desc a_pid 1 A A A 1 B b_id b_title b_key b_desc b_pid 1 b 2 C c_id c_title c_key c_desc c_pid 1 $title= Ctitile || ...
for stand-alone service, the primary key ID is a long integer, which needs to be non-continuous. For example, self-increment cannot be implemented because it is continuous. I thought of using System.nanoTime () to do ID, because I haven t practiced it...
query the COUNT of a sentence (1), JOIN IN has 4 tables. The total number of records is 3800, and it takes 43 seconds this is SQL: select count(1) from ( SELECT a.id AS "id", a.batch_id AS "batchId", a.product_type AS "product...
my mysql database has two pieces of data when I print_r, it looks like this: [{"errno":"1","errstr":"2","errfile":"3","errline":"4"},{"errno":"12",...
is currently self-learning mysql,. When querying, sometimes you need to use as aliases, but after adding as aliases to the following two query statements, why is it incorrect? 1, select cat_id,goods_name,shop_price from goods where shop_price in (sele...
as shown in the figure: the fields that reply_article is " 69 " and reply_type is " 0 " and reply_aims_user is null are required. then! Here s the point. Use the reply_id in the previous query field as the primary key to associate the ...
as shown in the figure, this association query is different in that it is associated with the same table indicates that table_test requirement: first query the result set of the field whose reply_type is 0, and then use the reply_id of the resul...
Which method of HIbernate is more appropriate to use in the SQL statement select name,sex from db, for example: public T findByID (ID id, I dbName) throws Exception; public List<T> findAll(I dbName) throws Exception; public List<T> findBy...
how to realize the if judgment of mysql. There is now a table with two key fields SELECT * FROM house WHERE price= 8500 OR shoufu= 30 I think, when the $price passed in by php is empty, the sql search statement is SELECT * FROM house WHERE s...
problem description: a blog system is being developed that needs to achieve the top of an article, and then the foreground will display the article (or more than one) at the top. What I want is to add a new field to determine whether it is a top article...
Table 1 fields are as follows 2 3 1event_description_description_id2id1cel_order_item_order_item_id3id 123idnull id ask the boss for advice ...
I have found many articles on utf8_unicode_ci, utf8_general_ci and utf8_bin collation on the Internet, and I generally know that ci is case ignore, etc., but I didn t mention what other collations mean . what does the utf8mb4_polish_ci collation her...
uses union all to merge query results when doing a comprehensive statistical function, but the result set obtained by the query is found to be overwritten sql: SELECT studyCenterName, grade, weiji, done, zong FROM ( ...
for example, I like % R% answer, the query results include not only R, but also r, which is not what I want, I want to be case-sensitive. I guess it has something to do with the proofreading rules of the table. My table uses utf8mb4 encoding, and the pr...