MySQL（表设置、详细说明查询操作）、JDBC基础

墨蓝 2022-05-08 09:46 168阅读 0赞

登陆数据库  
mysql -u(名字)root -p(密码)\*\*\*\*\*\*

查看所有数据库  
show databases

创建数据库  
create database (名称)ztest

选择数据库  
use （名称)ztest

删除数据库  
drop database (名称)ztest

创建表  
create table (表名)student(字段名 数据类型，字段名 数据类型，...)（id int,name varchar(30),age int,sex varchar(3)）;  
数据类型 数字类型int 字符串类型 varchar(长度)

添加数据  
insert into (表名)student(字段名,字段名,...)(id,name,age,sex) values(1,'zhang',22,'m');

查询数据  
select (字段名，字段名，...)id,age (查询全部字段用：\*) from (表名)student where（条件）id<2;

删除数据  删除数据只能按行删  
delete from (表名)student where （条件）id=2;

更新数据  
update （表名）student set (字段名=新数据，字段名=新数据，...)age=20,sex='w' where (条件)id=2;  
    
增加一列  
alter table (表名)book add column (字段名)publishclub (数据类型)varchar(50)

改变字段的名称、数据类型  
alter table (表名)book change column （字段名)price (新字段名)price\_rmb (新数据类型)float;

localhost/127.0.0.1 本机

实现实体完整性，设置：主键约束，唯一约束

主键列必须满足的条件：  
1、值不能为空  
2、值必须唯一  
3、不能有业务含义  
4、值不能发生变动  
一个表只能有一个主键约束  
设置主键约束(primary key)  
创建表时：  
create table student(int id primary key,...)或create table student(int id ,...，primary key（id）)  
建表后：  
alter table student add primary key(id);

唯一约束：  
值必须唯一，允许有null(空值)  
一个表可以有多个约束  
设置唯一约束(unique)  
创建表时：  
create table student(id int unique,...);  
alter table student modify id int unique;

实现域的完整性，设置：数据类型，非空约束，默认约束，检查约束（mysql不支持）

非空约束  
创建表时：  
create table student(id int not null,...);  
建表后:  
alter table student modify id int not null;

默认约束：  
创建表时：  
create table student(sex int default '男',...);  
建表后:  
alter table student modify sex varchar(3) default '男';

检查约束：  
创建表时：  
create table student(age int check(age>18 and age<40),...);  
建表后:  
alter table student modify age int varchar(3) check(age>18 and age<40);

实现引用完整性  
外键约束  
创建表时：  
create table student(...,class\_id int referencse class(id));  
建表后：  
alter table student add constraint (外键名称，如果不写系统会默认生成)ccc foreign key(class\_id) references class(id);

数据查询：首先明确数据库表是从1开始计数

select语句的完整语法，可以有6个子句,完整的语法如下：  
select 目标表的列名或列表达式集合  
 from 基本表或(和)视图集合  
〔where  条件表达式〕  
〔group by   列名集合〕  
〔having     组条件表达式〕  
〔order by  列名〔集合〕〕

投影操作  
投影操作是查询语句里必须有的子句，关键字则为select  
select 列1,列2,列3,列N from 表名  
查询所有列用"\*"代替列名，选择某个表中的多个列，那么列名之间用逗号分隔开

按照cat\_id（字段名）升序排序：  
select \* from goods order by cat\_id  
按照goods\_price降序排序:  
select \* from goods order by goods\_price desc  
asc （ascending 的简写,上升的意思,默认为升序排序所以可不写）  
desc （descending 的简写 下降的意思）

列别名  
显示所有学生的姓名和年龄（使用列别名）  
select  name  as ‘姓名’, age as ‘年龄’ from student  
as 可以忽略，用空格代替即可

排除重复的数据  
关键字：distinct 必须放在第一个列名的开头  
select distinct 列名 from 表名  
select distinct name , address from  student

返回限定行数的查询  
常用于分页  
关键字：limit 开始序列号, 返回的行数  
limit开始的序号是从0开始的  
select \* from  student  limit 2,2//显示第三个和第四个学生的信息  
开始序列号 = （当前页数 - 1）\* 每页显示条数

多列排序：  
select 列1,列2,..from 表名 order by 列1 \[asc,desc\], 列2 \[asc,desc\],…  
查询表goods所有信息，将其按照cat\_id（字段名）升序排序，相同cat\_id下的按照goods\_price降序排序  
select \* from goods  order by cat\_id, goods\_price desc;   
多列排序结果是根据ORDER BY子句后面列名的顺序确定优先级的。 即查询结果首先以 列1 的顺序进行排序，而只有当列1 出现相同的信息时， 这些相同的信息再按列2的进行排序，依此类推。

查询语句SQL的执行顺序  
第一步：执行from，查询表的所有信息  
第二步：执行where,根据条件过滤信息  
第三步：执行select，根据需要查询的字段名，投影列  
第四步：执行order by,排序

查询条件操作符  
=  
    等于  
!=    不等于  
<>    不等于  
>    大于  
<    小于  
>=    大于等于  
<=    小于等于  
!>    不大于  
!<    不小于

多条件选择操作  
and 并且  
or 或者  
select \*  from student  where age=19 or age=25

范围查询  
select 列1,列2,...from 表名 where 列名  between  下限  and  上限  
between   and  表示一个范围搜索，大于等于下限，并且小于等于上限  
select \*  from student  where age between 19 and 22

定义集合关系  
in  
not in  
elect 列1,列2,...from 表名 where 列名 in（值集合）  
select \*  from student  where age in (19,22)

模糊查询  
关键词 like  
例如：  
select \* from sudent where name like '%张%'; //查询名字中有张的所有学生信息  
通配符：  
 \_ :表示任何单个字符  
 % :表示包含零到多个任意字符

处理空值数据  
查询条件某个字段名为空：....where 字段名 is null//不能用 =null   
不为空：where 字段名 is not null  
使用其他任何比较运算符来匹配null得到的都是false的结果， 比如null=null也是返回false。

行转列  
case...when...then...else...end  
employee表：  
e\_id    e\_name  e\_cultur  
1    张三1    大专  
2    张三2    博士  
3    张三3    大专  
4    张三4    本科  
5    张三5    大专  
6    张三6    研究生  
7    张三7    大专  
9    吕琪    本科

select sum(case e\_cultur when '大专' then 1 else 0 end) '大专' ,  
sum(case e\_cultur when '本科' then 1 else 0 end) '本科' ,  
sum(case e\_cultur when '博士' then 1 else 0 end) '博士' ,  
sum(case e\_cultur when '研究生' then 1 else 0 end) '研究生'   
from employee   
效果如下：  
大专   本科    博士    研究生  
4    2    1    1

简单JDBC知识

执行数据库语句  
执行完更新操作之后,会返回几行受影响  
n = ps.executeUpdate();

一定记得关闭  
if(stmt != null) \{  
    stmt.close();  
\}  
if(conn != null)\{  
    conn.close();  
\}

事务处理  
Connection.setAutoCommit()，总的来说是为了保护数据库的一致性的，一般用在事务处理中。  
举例：  
A向B转账，就有两个操作，A的账户减少、B的账户增加  
如果在进行B账户增加操作时，系统出现故障，如果不采用事件处理，就会发生A账户减少，而B账户并没有增加的问题，也就是产生脏数据，那么就必须采取事件处理。  
   Connection con=DriverManager.getConnection();  
   try \{  
        //设置为手动提交  
        con.setAutoCommit(false);  
        update1(con);//执行更新操作，A账户减少  
        update2(con);//执行更新操作，B账户增加  
        //手动提交  
        con.commit();

\} catch (Exception e) \{  
        try \{

//如果出错回滚所有数据      
        con.rollback();

\} catch (Exception e2) \{          
        \}  
    \}  
    finally \{  
        if(con!=null)\{  
            try\{  
            con.close();  
            \}catch(Exception e)\{

\}  
        \}  
    //最后设置为自动提交  
    con.setAutoCommit(true);  
    \}