1

CREATE SCHEMA TEST AUTHORIZATION ZHANG;

1

DROP SCHEMA <模式名> <CASCADE | RESTRICT>;

1
2

DROP TABLE  <表  名> <CASCADE | RESTRICT>;     ---缺省是RESTRICT
                       级联      限制

1
2
3
4
5
6
7

CREATE TABLE SC(
        Sno CHAR(5), 
        Cno CHAR(3), 
        Grade int,
    　  PRIMARY KEY (Sno,Cno),
    　  FOREIGN KEY (Sno) REFERENCES S(Sno),       --- FOREIGN KEY <列名>REFERENCES <被参照表名>(<列名>)
    　  FOREIGN KEY (Cno) REFERENCES C(Cno));

1
2
3
4

ALTER TABLE <表名>
    [ ADD <新列名> <数据类型> [ 完整性约束 ] ]
    [ DROP <完整性约束名>]
　　[ ALTER COLUMN <列名>  <数据类型> ] ;

1
2
3
4
5

ALTER TABLE Student ADD Scome DATE ;
ALTER TABLE Student Drop Scome ;
ALTER TABLE Student ALTER COLUMN Sage SMALLINT ;
ALTER TABLE Student ADD UNIQUE(Sname) ;
ALTER TABLE Student DROP UNIQUE(Sname) ;

1
2

SC表按学号升序和课程号降序建唯一索引
CREATE UNIQUE INDEX SCno ON SC(Sno,Cno DESC);

1
2

在Student表的Sname列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放
CREATE CLUSTER INDEX Stusname ON Student(Sname);

1

DROP INDEX Stusname; ---SQL标准中没有定义对索引的修改功能，而采用删除后重新定义索引的方式实现

1
2

SELECT DISTINCT Sno FROM SC; ---DISTINCT取消重复行,作用范围是所有目标列
投影(π)运算的定义中直接去掉了结果中的重复元组，SQL中必须明确指定DISTINCT,否则默认ALL

1
2
3
4

-查询年龄不在20～23岁之间的学生姓名、系别和年龄。
SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20  AND 23;
-查询不是信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ('IS','MA','CS');

1
2
3
4
5
6
7
8

%:任意长度(可以为0)的字符串    _:任意单个字符
-查询姓名中第二个字为“阳”字的学生姓名和学号。
SELECT Sname,Sno FROM Student WHERE Sname LIKE '__阳％';
-查询以"DB_"开头，倒数第3个字符为i的课程的详细情况。
SELECT * FROM Course WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';
-查询所有有成绩的学生学号和课程号
SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;
!!!   [NOT] BETWEEN … AND … 和 [NOT] IN将不会利用索引提高查询效率，应改为多重条件查询

1
2

-所在系的系名升序排列,年龄降序排列
SELECT * FROM Student ORDER BY Sdept,Sage DESC;

1
2

集函数:
COUNT ([DISTINCT|ALL] <列名>) SUM AVG MAX MIN   ---DISTINCT以避免重复

1
2

查询有3门以上课程是90分以上的学生的学号及(90分以上的)课程数。
SELECT Sno, COUNT(*) FROM SC WHERE Grade>90 GROUP BY Sno HAVING COUNT(*)>3;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

public class Work3
{
    public static void main(String[] args)
    {
        int i=0,j=0;
        double[] b={1,-2,+0,-0,Double.POSITIVE_INFINITY,Double.NEGATIVE_INFINITY,Double.NaN};
        double[][] a=new double[7][7];
        for(i=0;i<=6;i++)
            for(j=0;j<=6;j++)
                {
                    a[i][j]=b[i]/b[j];
                    System.out.print(a[i][j]+"      ");
                    if(j==6)
                    {
                        System.out.print("\n");
                    }
                }
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

public class Work17
{
    public static void main(String[] args)
    {
        int[] a={4,6,23,78,2,345,90098,2,5,1,90098};
        int min=0,min_value=0,i=0;
        for(;i<a.length;i++)
        {
            if(a[i]>min_value)
                {
                    min_value=a[i];
                    min=i;
                }
            if(a[i]==min_value)
                continue;
        }
        System.out.println("The min is NO."+min+" : "+min_value);
        System.out.println("Attention please:The array is began with \"0\"");
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

public class Work18
{
    public static void main(String[] args)
    {
        int[][] array={{99,22,639,15,66,19},{17,85,56,535,89,76},{56,67,71,51,24,873},{821,61,83,17,24,78},{2,232,87,95,68,49},{25,90,869,92,93,25}};
        int[] sum=new int[6];
        int max=0,max_value=0,i=0,j=0;
        for(;i<array[0].length;i++)
            for(;j<array[0].length;j++)
                sum[i]=sum[i]+array[i][j];
        for(i=0;i<array[0].length;i++)
        {
            if(sum[i]>max)
                {
                    max_value=sum[i];
                    max=i;
                }
            if(sum[i]==max_value)
            continue;
        }
        System.out.println("The max sum_value("+max_value+") is in the Line."+max);
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

public class Work19
{
    public static void main(String args[])
    {
        int[] a=new int[4];
        int i=1000,j=0;
        for(;i<10000;i++)
        {
            a[0]=i/1000;
            a[1]=(i-a[0]*1000)/100;
            a[2]=(i-a[0]*1000-a[1]*100)/10;
            a[3]=i-a[0]*1000-a[1]*100-a[2]*10;
            if((a[0]*10+a[1])*(a[2]*10+a[3])==i||(a[1]*10+a[0])*(a[2]*10+a[3])==i||(a[0]*10+a[1])*(a[3]*10+a[2])==i||(a[1]*10+a[0])*(a[3]*10+a[2])==i)
                System.out.println(i);
            if((a[0]*10+a[2])*(a[1]*10+a[3])==i||(a[2]*10+a[0])*(a[1]*10+a[3])==i||(a[0]*10+a[2])*(a[3]*10+a[1])==i||(a[2]*10+a[0])*(a[3]*10+a[1])==i)
                System.out.println(i);
            if((a[0]*10+a[3])*(a[1]*10+a[2])==i||(a[3]*10+a[0])*(a[1]*10+a[2])==i||(a[0]*10+a[3])*(a[2]*10+a[1])==i||(a[3]*10+a[0])*(a[2]*10+a[1])==i)
                System.out.println(i);
        }
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

public class Work2
{
    public static void main(String args[])
    {
        NewRectangle a=new NewRectangle();
        a.Initialize(3,4);
        a.getArea(3,4);
        a.getPerimeter(3,4);
    }
}

class NewRectangle
{
    double width,height;
    public void Initialize(double width,double height)
    {
        this.width=width;
        this.height=height;
        System.out.println("width initialize:"+this.width+'\n'+"height initialize:"+this.height+'\n');
    }
    public void getArea(double width,double height)
    {
        System.out.println("The area is:"+width*height+'\n');
    }
    public void getPerimeter(double width,double height)
    {
        System.out.println("The perimeter is:"+2*(width+height));
    }
}