Mysql 数据库基本操作

select distinct math from 表名

select ... from  表名 where... order by 列名 [asc|desc];

--查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示
select name , math , english, chinese from table_name order by math desc,english,chinese;

--查询数学成绩是58 或者 59 或者 98 或者 99 的同学及数学成绩
select name,math from table_name where math in(58,59,98,99);
 
--使用 OR 也可以实现
select name , math from table_name where math=58 or math=59 ro math=98 or math=99;

-- % 匹配任意多个( 包括 0 个字符)
select name from table_name where name like '孙%';--匹配到孙悟空，孙权
 
-- _匹配严格的一个字符
select name from table_name where name like '孙_';--匹配到孙权

--查询 qq_mail 已知的同学的姓名
select name,qq_mail from student where qq_email is not null;
 
--查询 qq_mail 未知的同学姓名
select name,qq_mail from student where qq_mail is null;

--起始下标为0
 
-- 从0开始，筛选 n 条结果
select ... from table_name [where ...] [order by ...] limit n;
 
--从s 开始，筛选 n 条结果
select ... from table_neme [where ...] [order by ...] limint s,n;
 
--从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
select ... from table_name [where ...] [order by ...] limit n offset s

update table_name set column =...[where ...] [order by ...][limit ...]

delete from table_name [where ...] [order by ...] [limit ...]

 create table student(id int not null,sn int,name varchar(20),qq_mail varchar(20));
 
 insert into student(sn,name,qq_mail) values(23,'zhangsan','353323@qq.com');
 Field 'id' doesn't have a default value

 create table  student(id int not null,sn int unique,name varchar(20),qq_mail varchar(20));
 
 insert into student values (1,2,'zhangsan','235@qq.com'),(2,2,'lisi','352@qq.com');
ERROR 1062 (23000): Duplicate entry '2' for key 'sn'

create table student (id int not null,sn int unique,name varchar(20) default 'unknow',qq_mail varchar(20));
 
 insert into student (id,sn,qq_mail)values (1,3,'35452@qq.com');

 create table test_user(id int primary key auto_increment,
                        name varchar(20),
                        age int,
                        email varchar(20),
                        sex varchar(5),
                        mobile varchar(20));
 
--将学生表中的所有数据赋值到用户表
 
insert into test_user(name,email) select name,qq_mail from student;

--统计班级共有多少同学
select count(*) from student;
 
--统计班级收集的qq_mail有多少个，qq_mail为null的数据不会计入结果
select count(qq_mail) from student;

-- 统计数学成绩总分
select sum(math) from exam_result;
 
-- 不及格 <60 的总分，没有结果，返回null
select sum(math) from exam_result where math <60;

1. -- 统计平均总分
2. select AVG(chinese + math + english) 平均总分 from exam_result;

1. -- 返回英语最高分
2. select max(english) from exam_result;

1. -- 返回>70 分以上的数学的最低分
2. select min(math) from exam_result where math >70;

select column1,sum(column2),... from table group by column1,coulmn3;

create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
 
select role ,max(salary),min(salary),avg(salary) from emp group by role;

select role ,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

 select score.score from student join score on student.id=score.student_id and student.name='许仙

select stu.sn,stu.name,stu.qq_mail,sum(sco.score) from student stu join score sco on stu.id =sco.student_id group by sco.student_id;
 
+-------+-----------------+-----------------+----------------+
| sn    | name            | qq_mail         | sum(sco.score) |
+-------+-----------------+-----------------+----------------+
| 09982 | 黑旋风李逵      | xuanfeng@qq.com |          300.0 |
| 00835 | 菩提老祖        | NULL            |          119.5 |
| 00391 | 白素贞          | NULL            |          200.0 |
| 00031 | 许仙            | xuxian@qq.com   |          218.0 |
| 00054 | 不想毕业        | NULL            |          118.0 |
| 51234 | 好好说话        | say@qq.com      |          178.0 |
| 83223 | tellme          | NULL            |          172.0 |
+-------+-----------------+-----------------+----------------+

select stu.id ,stu.sn,stu.name,stu.qq_mail,sco.score,sco.course_id,cou.name from student stu join score sco on stu.id=sco.student_id join course cou on sco.course_id =cou.id order by stu.id;
+----+-------+-----------------+-----------------+-------+-----------+--------------------+
| id | sn    | name            | qq_mail         | score | course_id | name               |
+----+-------+-----------------+-----------------+-------+-----------+--------------------+
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |  70.5 |         1 | Java               |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |  98.5 |         3 | 计算机原理         |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |  33.0 |         5 | 高阶数学           |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |  98.0 |         6 | 英文               |
|  2 | 00835 | 菩提老祖        | NULL            |  60.0 |         1 | Java               |
|  2 | 00835 | 菩提老祖        | NULL            |  59.5 |         4 | 语文               |
|  3 | 00391 | 白素贞          | NULL            |  33.0 |         1 | Java               |
|  3 | 00391 | 白素贞          | NULL            |  68.0 |         3 | 计算机原理         |
|  3 | 00391 | 白素贞          | NULL            |  99.0 |         5 | 高阶数学           |
|  4 | 00031 | 许仙            | xuxian@qq.com   |  67.0 |         1 | Java               |
|  4 | 00031 | 许仙            | xuxian@qq.com   |  23.0 |         3 | 计算机原理         |
|  4 | 00031 | 许仙            | xuxian@qq.com   |  56.0 |         5 | 高阶数学           |
|  4 | 00031 | 许仙            | xuxian@qq.com   |  72.0 |         6 | 英文               |
|  5 | 00054 | 不想毕业        | NULL            |  81.0 |         1 | Java               |
|  5 | 00054 | 不想毕业        | NULL            |  37.0 |         5 | 高阶数学           |
|  6 | 51234 | 好好说话        | say@qq.com      |  56.0 |         2 | 中国传统文化       |
|  6 | 51234 | 好好说话        | say@qq.com      |  43.0 |         4 | 语文               |
|  6 | 51234 | 好好说话        | say@qq.com      |  79.0 |         6 | 英文               |
|  7 | 83223 | tellme          | NULL            |  80.0 |         2 | 中国传统文化       |
|  7 | 83223 | tellme          | NULL            |  92.0 |         6 | 英文               |
+----+-------+-----------------+-----------------+-------+-----------+--------------------+

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

--"老外学中文"同学，没有考试成绩，也显示出来了
select * from student left join score on student.id=score.student_id;
 
--对应的右外连接为
 select * from score right join student on student.id=score.student_id;

select s1.* from score s1 ,score s2 where s1.student_id= s2.student_id and s1.score<s2.score and s1.course_id=1 and s2.course_id=3;

mysql> select * from student where classes_id =(select classes_id from student where name='不想毕业');
+----+-------+-----------------+-----------------+------------+
| id | sn    | name            | qq_mail         | classes_id |
+----+-------+-----------------+-----------------+------------+
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |          1 |
|  2 | 00835 | 菩提老祖        | NULL            |          1 |
|  3 | 00391 | 白素贞          | NULL            |          1 |
|  4 | 00031 | 许仙            | xuxian@qq.com   |          1 |
|  5 | 00054 | 不想毕业        | NULL            |          1 |
+----+-------+-----------------+-----------------+------------+

使用 IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');
 
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文')

 select * from score sco where exists (select cou.id from course cou where (name='语文' or name='英文') and cou.id=sco.course_id);
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.0 |          1 |         6 |
|  59.5 |          2 |         4 |
|  72.0 |          4 |         6 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
 
 select * from score sco where not exists (select cou.id from course cou where (name='语文' or name='英文') and cou.id=sco.course_id);
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  70.5 |          1 |         1 |
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  60.0 |          2 |         1 |
|  33.0 |          3 |         1 |
|  68.0 |          3 |         3 |
|  99.0 |          3 |         5 |
|  67.0 |          4 |         1 |
|  23.0 |          4 |         3 |
|  56.0 |          4 |         5 |
|  81.0 |          5 |         1 |
|  37.0 |          5 |         5 |
|  56.0 |          6 |         2 |
|  80.0 |          7 |         2 |
+-------+------------+-----------+

-- 获取“中文系2019级3班”的平均分，将其看作临时表
SELECT
avg( sco.score ) score
FROM
score sco
JOIN student stu ON sco.student_id = stu.id
JOIN classes cls ON stu.classes_id = cls.id
WHERE
cls.NAME = '中文系2019级3班';

SELECT
*
FROM
score sco,
(
SELECT
avg( sco.score ) score
FROM
score sco
JOIN student stu ON sco.student_id = stu.id
JOIN classes cls ON stu.classes_id = cls.id
WHERE
cls.NAME = '中文系2019级3班'
) tmp
WHERE
sco.score > tmp.score;

-- 可以看到结果集中出现重复数据Java
 select * from course where id<3 union all select * from course where name ='Java';
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | Java               |
|  2 | 中国传统文化       |
|  1 | Java               |
+----+--------------------+

--查看索引
show index from 表名;
 
--创建索引
create index 索引名 on 表名(字段名);
 
--删除索引
drop index 索引名 on 表名;

start transaction；
 
--阿里巴巴账户减少2000
update account set money=money -2000 where name='阿里巴巴';
 
--四十大盗账户增加2000
update account set money=money+2000 where name ='四十大盗';
 
commit;

//加载JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");
 
//创建数据库连接
Connection connection =DriverManager.getConnection(url);

         DataSource dataSource =new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");//固定为“root”
        ((MysqlDataSource)dataSource).setPassword("123456");//安装数据库时设置的密码

public class JDBCInsert {
    public static void main(String[] args) throws SQLException {
        //JDBC 需要通过以下步骤来完成开发
        //1.创建并初始化一个数据源
        Scanner scanner=new Scanner(System.in);
        DataSource dataSource =new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");//固定为“root”
        ((MysqlDataSource)dataSource).setPassword("123456");//安装数据库时设置的密码
        //可以直接创建MysqlDataSource对象
        //MysqlDataSource dataSource=new MysqlDataSource();
 
        //2.和数据库服务器建连接
        Connection connection =dataSource.getConnection();//此时需要使用java.sql中的Connection
 
        //3.构造 SQL 语句
        /*//此时代码是写死的
        String sql="insert into student values(1,'张三')";
        PreparedStatement statement=connection.prepareStatement(sql);*/
        System.out.println("请输入学生姓名：");
        String name=scanner.next();
        System.out.println("请输入学号：");
        int id=scanner.nextInt();
 
        String sql="insert into student value(?,?)";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.setString(2,name);
        System.out.println(statement);
 
        //4.执行 SQL 语句
        int ret=statement.executeUpdate();
        System.out.println("ret="+ret);
 
        //5.释放必要的资源
        statement.close();
        connection.close();
    }
}

public class JDBCSelect {
    public static void main(String[] args) throws SQLException {
        MysqlDataSource mysqlDataSource=new MysqlDataSource();
        mysqlDataSource.setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("123456");
 
        Connection connection=mysqlDataSource.getConnection();
 
        String sql="select * from student";
        PreparedStatement statement =connection.prepareStatement(sql);
 
        ResultSet resultSet=statement.executeQuery();
 
        while (resultSet.next()){
            //
            int id=resultSet.getInt("id");
            String name=resultSet.getString("name");
            System.out.println("id="+id+"name="+name);
        }
 
    }
}