SQL language,

SQL language,
1. Create a Mode

1 create schema "S-T" authorization wang;

 

 

2. Set the default value

1 create table student1 (2     sno char(8),3     sname char(8),4     sbirth date default '0000-00-00',5     ssex char(2) default '?',6     sdept char(30)7 );

 

 

3. Basic table Revocation

1 drop table sc;2 drop table course;3 drop table student;

 

　　Note: When you cancel a table, objects such as tables, views, indexes, and stored procedures that depend on the deleted table should not exist in the database.

4. Modify the basic table structure

1 -- add the "admission time" column 2 alter table student1 add stime date; 3 -- delete the "admission time" column 4 alter table student1 drop stime;

 

5. Query (remove duplicate values)

1 -- Query all different birthdays 2 select distinct sbirth from student1;

 

7. String Matching (LIKE)

1 -- query the information of all birthdates in 1996. 2 select * 3 from student14 where sbirth like '123 ';

 

 

8. Three-value Logic (and or not)

1 -- note that is null or is not null 2 select * 3 from sc4 where sno = '000000' and5 (grade> = 60 or grade <60 or grade is null );

 

 

9. Output sorting

1/* 2 query all student information. 3. The results are sorted in reverse order of the student's department. 4. The students in the same department are sorted by student ID. 5 */6 select * 7 from student18 order by sdept desc, sno asc;

 

10. Rename the output Column

1 select sno student ID, sname name, sbirth Date of birth 2 from student13 where sbirth like '000000' 4 order by sno desc;

 

 

11. Aggregate functions

1/* 2 query the number of students enrolled in course 3. The highest score and average score are 3 */4 select count (*), max (grade), and avg (grade) 5 from sc6 where cno = '3 ';

 

12. Aggregate functions and groups (group by, HAVING)

1/* 2 query the student ID and average score of the student who has selected more than three courses 3 */4 select sno, avg (grade) 5 from sc6 group by sno7 having count (*)> 3;

 

13. Complete syntax of the select statement

14. Connection query (Multi-table)

1/* 2 Calculate the names and scores of students whose data structure score is greater than 85. 3 results are sorted in descending order of scores. 4 */5 select student. sname, grade 6 from student, SC, course 7 where student. sno = SC. sno and 8 SC. sno = course. cno and 9 course. cname = 'data struct' and10 SC. grade> 8511 order by grade desc;

 

　　Note: When different tables have attributes of the same name, the attribute name must be specified before the table name.

 

15. Self-connection

1/* 2 Calculate the course number and name of the first course in Discrete Mathematics 3 */4 select z. cno, z. cname5 from course x, course y, course z6 where x. pcno = y. cno and7 y. pcno = z. cno and8 x. cname = 'discrete mates ';

 

 

16. External Connection

You need to find that one table exists in two tables. If the other table does not exist, use an external join.

1/* 2 query the student ID, name, course number of the selected course, and score of the course. 3 left join returns the data rows that meet the connection conditions and the data rows that do not meet the conditions in the left table. data row 4 right join, full join is similar to 5 */6 select student. sno, sname, cno, grade7 from8 student left outer join sc9 on student. sno = sn. sno;

 

 

17. nested Query

1/* 2 query the student ID and name of the same department as the student whose student ID is 95003 */4 select sno, sname5 from student6 where sdept = (select sdept7 from student where sno = '2013 ');