SQL基础教程-优快云博客

本文链接：https://blog.youkuaiyun.com/xm526489770/article/details/79963235

一,数据表的创建和管理:

1.1.创建表定义非空约束:

CREATE TABLE test(
Tuser VARCHAR(20) NOT NULL ,
Tpasswd INT NOT NULL

)

1.2.创建默认值(关键字DEFAULT):

Tuser VARCHAR(20) NOT NULL ,
Tpasswd INT NOT NULL,
Tage INT DEFAULT 10

)

1.3.定义主键(关键字PRIMARY KEY(Tpasswd)):

CREATE TABLE test(
Tuser VARCHAR(20) NOT NULL ,
Tpasswd INT NOT NULL,
Tage INT DEFAULT 10,
PRIMARY KEY(Tpasswd)

)

1.4.创建联合主键(关键字PRIMARY KEY(Tpasswd,Tage)):

CREATE TABLE test(
Tuser VARCHAR(20) NOT NULL ,
Tpasswd INT NOT NULL,
Tage INT DEFAULT 10,
PRIMARY KEY(Tpasswd,Tage)

)

1.5.关联外键:

PRIMARY KEY(外键字段名称) REFERENCES 目标表明 (目标表主键)

1.6.修改已有数据表:

ALTER TABLE 表名 ADD 字段名 VARCHAR(50)

1.7.删除表字段:

ALTER TABLE 表名 DROP 字段名

1.8.删除数据表:

DROP TABLE 表名

第二数据库的增,删,改:

2.1.添加数据:

INSERT INTO test(Tuser,Tpasswd,Tage) VALUES('1',3,5);

2.2.更新数据:

UPDATE test SET Tuser='ttt' WHERE Tuser='2'

2.3.或条件更新数据:

UPDATE test SET Tuser='ttt' WHERE Tuser='2' OR Tage=5

2.4.删除数据:

DELETE FROM test WHERE Tuser='de' OR Tuser='ttt'

第三数据库的查询:

3.1.检索出需要的列:

SELECT username FROM user

3.2.别名列:

SELECT username AS uname ,PASSWORD AS p FROM user

SELECT username 姓名 ,PASSWORD 密码 FROM user

3.3.按条件过滤:

SELECT * FROM USER WHERE age>30

3.4.聚合函数查询最大值:

SELECT MAX(alaery) AS '最高工资' FROM USER

3.5.聚合函数查询平均值:

SELECT AVG(alaery) AS '平均工资' FROM USER

3.6.聚合函数查询数据和:

SELECT SUM(alaery) AS '工资总计' FROM USER

3.7.聚合函数查询最大和最小值:

SELECT MAX(alaery) AS '最高工资',MIN(alaery) AS '最低工资' FROM USER

3.8.聚合函数统计数量:

SELECT COUNT(*)AS '总数' FROM USER(若换成具体字段,只会统计不为空的字段数量)

3.9.数据排序升序(asc是代表升序,排序默认是升序所以asc可以省略):

SELECT * FROM USER ORDER BY alaery ASC

3.10.数据排序降序(若多个排序规则系统默认按照第一排序规则,若无法排序按照第二个排序规则,以此类推):

SELECT * FROM USER ORDER BY alaery DESC ,age DESC

3.11.模糊查询(单字符匹配):

SELECT * FROM USER WHERE username LIKE '李_'

3.12.模糊查询(多字符匹配):

SELECT * FROM USER WHERE username LIKE '李%'

3.13.模糊查询集合匹配:

SELECT * FROM USER WHERE username LIKE "张%" OR username LIKE "王%"

3.14.空值检索:

SELECT * FROM USER WHERE alaery IS NULL

3.15.非空检索:

SELECT * FROM USER WHERE alaery IS NOT NULL

3.16.反义检索(<>为不等于):

SELECT * FROM USER WHERE alaery<>788 AND age>=56

3.17.反义检索(添加NOT关键字可以实现反义):

SELECT * FROM USER WHERE NOT alaery=788 AND age>=56

3.18.范围值检索(其中的关键字是IN):

SELECT * FROM USER WHERE age IN(10,56,33)

3.19.范围值检索:

SELECT * FROM USER WHERE age>=20 AND age<50

3.20.范围值检索(BETWEEN 和AND关键字可以实现多个不连续范围查询):

SELECT * FROM USER WHERE (age BETWEEN 10 AND 30)OR (age BETWEEN 50 AND 60)

3.21.动态SQL创建(伪代码):

public void doSql(){
    Boolean hasWhere=false;
    StringBuilder sql=new StringBuilder("SELECT * FROM USER ");
    if(选中工号复选框){
        hasWhere=appendWhere(sql,hasWhere);
        sql.append("")
    }
    if(选中姓名复选框){
        hasWhere=appendWhere(sql,hasWhere);
        sql.append("")
    }
    if(选中工资复选框){
        hasWhere=appendWhere(sql,hasWhere);
        sql.append("")
    }
}

public Boolean appendWhere(StringBuilder sql,boolean hasWhere){
    if(!hasWhere){
        sql.append("WHERE");
    }else {
        sql.append("AND");
    }
    return hasWhere=true;
}