Java学习笔记：数据库操作

1、数据定义语言/Data Definition Language（DDL）

用于定义数据库对象（库、表、列等）。

1.1、DDL对库操作

1.1.1、创建数据库（create database）

格式：    create database 数据库名;
举例：    create database mydb1;

格式：    create database 数据库名 character set 编码方式;
举例：    create database mydb2 character set gbk;

格式：    create database 数据库名 set 编码方式 collate 排序规则;
举例：    create database mydb3 set gbk collate gbk_chinese_ci;

1.1.2、查看数据库（show database）

（1）查看所有数据库
格式：    show databases;

（2）查看某个数据库
格式：    show create database 数据库名;
举例：    show create database mydb1;

1.1.3、修改数据库（alter database）

格式：    alter database 数据库名 character set 编码方式
举例：    alter database mydb2 character set utf8;

1.1.4、删除数据库（drop database）

格式：    drop database 数据库名;
举例：    drop database mydb3;

1.1.5、其他语句

（1）查看当前使用的数据库
格式：    select database();

（2）切换数据库
格式：    use 数据库名;
举例：    use mydb1;

1.2、DDL对表操作

1.2.1、创建新表（create table）
格式：    create table 表名(列名1 数据类型 [约束], 列名2 数据类型 [约束], ……, 列名n 数据类型 [约束]);
举例：    create table student(id int, name varchar(50), sex char(1));

1.2.2、数据类型
    int        整型
    double        浮点型
    char        定长字符串型
    varchar        可变长字符串型
    text        超长字符串型
    blob        字节型
    date        日期型（yyyy-MM-dd）
    time        时间型（hh:mm:ss）
    timestamp    时间戳型（ yyyy-MM-dd hh:mm:ss，自动赋值）
    datetime    日期时间型（yyyy-MM-dd hh:mm:ss）

1.2.3、删除表（drop table）
格式：    drop table 表名;
举例：    drop table student;

1.2.4、查看表（show/desc）
（1）查看所有表
格式：    show tables;

（2）查看某个表的字段
格式：    desc 表名
举例：    desc student

（3）查看表创建细节
格式：    show create table 表名;
举例：    show create table student;

1.2.5、修改表（alter table）
（1）增加列
格式：    alter table 表名 add 新列名 数据类型;
举例：    alter table student add age int;

（2）删除列
格式：    alter table 表名 drop 列名;
举例：    alter table student drop sex;

（3）修改列属性（长度）
格式：    alter table 表名 modify 列名 数据类型 [约束];
    alter table 表名 change 列名 列名 数据类型 [约束];
举例：    alter table student modify name varchar(80);
    alter table student change name newname varchar(80);

（4）修改列名
格式：    alter table 表名 change 旧列名 新列名 数据类型 [约束];
举例：    alter table student change name myname varchar(100);

（5）修改表名
格式：    alter table 旧表名 rename 新表名;
举例：    alter table student rename student2;

（6）修改表属性（字符集）
格式：    alter table 表名 character set 编码方式
举例：    alter table student character set gbk

 

2、数据操作语言/Data Manipulation Language（DML）

用于对表中数据记录的增删改操作。

2.1、增加记录（insert）
格式：    insert into 表名(列名) values(数据值);
举例：    insert into student(name,sex,age) values('张三','m',18);
    insert into student values('张三','m',18);
    insert into student(name,sex,age) values('张三','m',18),('李四','f',19);
    insert into student(name,sex,age) values('王五',null,null);

 

2.2、修改记录（update）
格式：    update 表名 set 列名1=列值1,列名2=列值2 ... where 列名=值;
举例：    

2.3、删除记录（delete）
（1）删除表中符合条件的记录（删除的记录可恢复）
格式1：    delete from 表名 [where 列名=值];
（2）删除表并重建（记录不可恢复）
格式2：    truncate table 表名;

2.4、运算符
（1）算术运算符
符号：    +（加），-（减），*（乘），/（除）,%（求余）
举例：    5/2、5%2

（2）赋值运算符
符号：    =
举例：    name='张三'

（3）逻辑运算符
符号：    and(并且)、or(或者)、not（取非）

（4）关系运算符
符号：    >（大于）、<（小于）、>=（大于等于）、<=（小于等于）、!=（不等于）、<>（不等于）、=（等于）

 

 

3、数据控制语言/Data Control Language（DCL）

用于定义访问权限和安全级别。

3.1、创建用户

（1）指定的ip登录
格式1：    create user 用户名@指定ip identified by 密码;
举例：    create user abc@localhost identified by '123456';

（2）任意ip都可登录
格式2：    create user 用户名@'%' identified by 密码;
举例：    create user abc@'%' identified by '123456';

3.2、用户授权
格式：    grant 权限1,权限2,……,权限n on 数据库名.* to 用户名@指定ip;
举例：    grant select,insert,update,delete,create on student.* to 'abc'@'127.0.0.1';
    grant all on *.* to 'abc'@'127.0.0.1';

3.3、查询权限
格式：    show grants for 用户名@指定ip;
举例：    show grants for 'abc'@'%';

3.4、撤销权限
格式：    revoke 权限1,权限2,……,权限n on 数据库名.* from 用户名@指定ip;
举例：    revoke select on *.* from 'abc'@'%';

3.5、删除用户
格式：    drop user 用户名@指定ip
举例：    drop user 'abc'@localhost;

 

 

4、数据查询语言/Data Query Language（DQL）

用于数据记录的查询。

4.1、简单查询
（1）查询所有列
格式：    select * from 表名;

（2）查询指定列
格式：    select 列名1,列名2,……,列名n from 表名;

4.2、条件查询
在简单查询的基础上，加上where子句以限定条件。

（1）where子句中可使用以下运算符：
=、!=、<>、<、<=、>、>=

（2）where子句中可使用以下关键字：
between 条件1 and 条件2、in(集合)、is null、and、or、not

4.3、模糊查询
（1）模糊查询是使用关键字like的条件查询。
（2）模糊查询中，可使用通配符_表示任意1个字符，或通配符%表示任意0到n个字符。

举例：    select * from student where name like '___';    （name长度为3个字符）
    select * from student where name like '____c';    （name长度为5个字符且第5位是c）
    select * from student where name like 'a%';    （name第1位是a，长度不限）
    select * from student where name like '%c%';    （name含有c，长度不限）
    select * from student where name like '_b%';    （name第2位是b，长度不限）

4.4、字段控制查询

（1）去除查询结果中的重复记录
格式：    select distinct 列名 from 表名 [where语句];

（2）字段运算
格式：    列名1+列名2、列名1-列名2、列名1+ifnull(列名2,0)、……
其中“ifnull(列名,数值)”表示指定列中如果是null则以指定数值参与运算。

（3）列的别名
格式1：    列名 as 别名
格式2：    列名 别名
举例：    select name,score+ifnull(bonus,0) as total from student;
    select name,score+ifnull(bonus,0) total from student;

4.5、查询结果排序
格式：    order by 列名 [asc/desc]

4.6、聚合函数
用于对指定列作纵向运算的函数：
count(列名)：统计指定列不为NULL的记录行数；
max(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
sum(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
avg(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0。

4.7、分组查询
（1）group by 子句
格式：    group by 子句
举例：    select level, sum(score) from student group by level;    （查询各level的总分）

（2）having 子句
用于对分组结果进行过滤，其中可使用分组函数。（where子句用于分组前，不可使用分组函数）
举例：    select level, sum(score) from student group by level having sum(score)>1000;

（3）limit 子句
用于限定查询结果显示的起止行。
格式1：    limit 开始下标,显示行数;
格式2：    limit 显示行数;            （默认从0开始）

（4）各子句总结
书写顺序：select – from - where - groupby - having - order by - limit