mysql

create table hqh (
    id int not null primary key  auto_increment,
    username varchar(100) not null ,
    age int not null
);
 alter table hqh rename to hqh1;
alter table hqh1 add sex char(1);
alter table hqh1 add math double ;
alter table hqh1 add english double ;
desc  hqh1;
drop  table hqh1;

# DML
insert  into hqh1 values(1,'hqh','21','0');
insert  into hqh1(username,age,status) values('hjj','18','0');
insert  into hqh1(id,username,age,status) values(3,'lxs','17','1');
insert  into hqh1 values(4,'wj','22','2');


# DQL
# show table hqh1; 不行的
select * from hqh1;

select username ,id,age from hqh1 where username like '%h%';
-- 查询年龄大于20岁SELECT * FROM student WHERE age > 20;
SELECT * FROM hqh1 WHERE age >= 20;
-- 查询年龄等于20岁
SELECT * FROM hqh1 WHERE age = 20;
-- 查询年龄不等于20岁
SELECT * FROM hqh1 WHERE age != 20;
SELECT * FROM hqh1 WHERE age <> 20;
-- 查询年龄大于等于20 小于等于30
SELECT * FROM hqh1 WHERE age >= 20 && age <=30;
SELECT * FROM hqh1 WHERE age >= 20 AND age <=30;
SELECT * FROM hqh1 WHERE age BETWEEN 20 AND 30;
-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM hqh1 WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM hqh1 WHERE age IN (22,18,25);
-- 查询英语成绩为null
SELECT * FROM hqh1 WHERE english = NULL;
-- 不对的。null值不能使用 = （!=） 判断
SELECT * FROM hqh1 WHERE english IS NULL;
-- 查询英语成绩不为null
SELECT * FROM hqh1 WHERE english IS NOT NULL;
-- 查询姓马的有哪些？like
SELECT * FROM hqh1 WHERE username  LIKE '马%';
-- 查询姓名第二个字是化的人
SELECT * FROM hqh1 WHERE username  LIKE "_化%";
-- 查询姓名是3个字的人
SELECT * FROM hqh1 WHERE username  LIKE '___';
-- 查询姓名中包含德的人
SELECT * FROM hqh1 WHERE username  LIKE '%德%';

-- 分别查询男、女同学的平均分
SELECT sex , AVG(math) FROM hqh1 GROUP BY sex;
-- 按照性别分组。分别查询男、女同学的平均分,人数
SELECT sex , AVG(math),COUNT(id) FROM hqh1 GROUP BY sex  limit 1,1;
-- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
SELECT sex , AVG(math),COUNT(id) FROM hqh1 WHERE math > 70 GROUP BY sex;
-- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM hqh1 WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id)   人数 FROM hqh1 WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

JDBC

import com.ydlclass.entity.User;

import java.sql.*;

public class jdbc {
    public static void main(String[] args) throws Exception {

        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://localhost:3306/ssm?characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "111111";
        //8.0之后名字改了  com.mysql.cj.jdbc.Driver
//        String driverName = "com.mysql.cj.jdbc.Driver";

        //2.实例化Driver
//        Class clazz = Class.forName(driverName);
//        Driver driver = (Driver) clazz.newInstance();
//        //3.注册驱动
//        DriverManager.registerDriver(driver);
        //4.获取连接
        Connection conn = DriverManager.getConnection(url, username, password);

        PreparedStatement preparedStatement = conn.prepareStatement("select * from user where id = ?");
        preparedStatement.setInt(1, 1);
        ResultSet resultSet = preparedStatement.executeQuery();

        // 处理结果集
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            System.out.println(user);
        }
    }
}