（韩顺平讲解）jdbc学习（三）---mysql的CRUD操作

创建一张表：

create table emp(

id int,

name varchar(64),

sex char(2),

birthday date,

Entry_date date,

job varchar(32),

salary float,

resume text)

 

修改表结构

--添加新的列

alter table 表名 add 列名 数据类型

--修改列(列的类型和大小)

alter table 表名 modify 列名 新的数据类型

--删除某列

alter table 表名 drop  列名

rename table 原表名 to 新表名

alter table 表名 character set 字符集名;

alter table user change column name username varchar(20);

 

案例:

•在上面员工表的基本上增加一个image列。

alter table emp add image blob;

•修改job列，使其长度为60。

alter table emp modify job varchar(60);

•删除sex列。

alter table emp drop sex;

•表名改为user。

rename table emp to user;

•修改表的字符集为utf-8

alter table user character set utf8;

•列名name修改为username

alter table user change column name username varchar(30);

如何显示创建表的指令:

show create table 表名;

 

insert语句

插入的数据应与字段的数据类型相同。

比如:

create table test15 (name varchar(64));

insert into test15 (name) values(‘aaa’);

insert into test15 (name) values(34);

 

create table test16 (age int);

insert into test16 (age) values(34);

insert into test16 (age) values(‘aaa’);(错)

insert into test16 (age) values(‘111’);(虽然ok,但是不是好的写法.)

数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

在values中列出的数据位置必须与被加入的列的排列位置相对应。

create table test17 (id int ,name varchar(64));

insert into test17 (id,name) values(3,’aaa’);

insert into test17 (name,id) values(’aaa’,3);

 

update 语法

基本语法:

update 表名 set 列名=表达式 ... where 条件

说明: 如果 where后面没有条件，则相当于对整个表进行操作。

UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给予哪些值。

WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

 

将所有员工薪水修改为5000元。

update employee set sal=5000;

将姓名为’zs’的员工薪水修改为3000元;

update employee set sal=3000 where name=’zs’;

将wu的薪水在原有基础上增加1000元

update employee set sal=sa+1000 where name=’wu’;

 

delete语句

基本语法:

delele from 表名 where 条件;

注意:

如果不使用where子句，将删除表中所有数据。要小心使用.

Delete语句不能删除某一列的值（可使用update）

使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。

删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。

truncate table 表名，可以删除表的记录，速度快，但不能回滚...

 

在mysql中事务的特殊说明:

(1)mysql 控制台是默认自动提交事务(dml)

(2)如果我们要在控制台使用事务，应该这样

set autocommit=false;

savepoint 保存点

//操作...

rollback to 保存点.

 

select 语句

基本语法：

select 列名...., 列（可以运行） from 表名 where 条件;

注意事项:

Select 指定查询哪些列的数据。

column指定列名。

*号代表查询所有列。

select * from 表名;

From指定查询哪张表。

DISTINCT可选，指显示结果时，是否剔除重复数据

select distinct * from 表名

 

练习：

查询表中所有学生的信息。

select * from student;

查询表中所有学生的姓名和对应的英语成绩。

select name,english from student;

过滤表中重复数据。

select distinct * from 表名

 

where子句如何使用

案例:

l查询英语分数在 80－90之间的同学。

select * from student where english>=80 and english<=90;

l查询数学分数为89,90,91的同学

select * from student where math in (89,90,91);

l查询所有姓李的学生成绩。

select * from student where name lik ‘李%’;

l查询数学分>80，语文分>80的同学。

select * from student where matn>80 and chinese>80;

 

order by 子句

练习：

l对数学成绩排序后输出。

select name,math from student order by math;

l对总分排序后输出，然后再按从高到低的顺序输出

select math+english+chinese as allfen , name from student order by allfen;

l对姓李的学生成绩排序输出

select (math+english+chinese) as allfen,name from student where name like ‘李%’ order by allfen;

 

count

练习：

l统计一个班级共有多少学生？

select count(*) from student;

l统计数学成绩大于90的学生有多少个？

select count(*) from student where math>90;

l统计总分大于250的人数有多少？

select count(*) from student where (math+english+chinese)>250;

 

sum的用法

练习：

n统计一个班级数学总成绩？

select sum(math) from student;

n统计一个班级语文、英语、数学各科的总成绩

select sum(math),sum(english),sum(chinese) from student;

n统计一个班级语文、英语、数学的成绩总和

select sum(math+english+chinese) from student;

n统计一个班级语文成绩平均分

select sum(chinese)/count(*) from student;

 

avg的用法

练习：

n求一个班级数学平均分？

select avg(math) from student;

n求一个班级总分平均分

select avg(math+english+chinese) from student;

 

 

group by 用法

练习：对订单表中商品归类后，显示每一类商品的总价

select product , sum(price) from orders group by product;

 

having用法

练习：查询购买了几类商品，并且每类总价大于100的商品

select product , sum(price) from orders group by product having sum(price)>100

 

日期和时间函数

CURRENT_DATE (  )当前日期

CURRENT_TIME (  )当前时间

CURRENT_TIMESTAMP (  )当前时间戳

DATE (datetime )返回datetime的日期部分

DATE_ADD (date2 , INTERVAL d_value d_type )

在date2中加上日期或时间

DATE_SUB (date2 , INTERVAL d_value d_type )

在date2上减去一个时间

DATEDIFF (date1 ,date2 )两个日期差(结果是天)

TIMEDIFF(date1,date2)两个时间差(多少小时多少分钟多少秒)

NOW (  )当前时间

YEAR|Month|DATE (datetime )年月日

案例:

select current_date() from dual ； 得到当前日期

select current_time() from dual ;  得到请求时间;

 

mysql 事务控制

Mysql的事务是自动提交的

set autocommit=false;

savepoint 保存点

进行各个dml操作

rollback to aa;

 

mysql的常见约束

①primary key (主键)

特点: 主键是用于唯一标识一条记录的约束，一张表，最多只能有一个主键，主键不能为null,也不能重复

create table user1 (id int primary key, name varchar(32));

 

②auto_increment

可以自增长.

create table user2 (id int primary key auto_increment , name varchar(32));

 

③unique（唯一）

特点: 表的某列的值，不能重复, 可以为null (可以有多个null), 一张表中可以有多个unique.

create table user4(id int unique,name varchar(32));

 

④not null (非空)

mysql 的表的列，默认情况下可以为null, 如果不允许某列为空 ，则可使用 not null说明

create table user5(id int primary key, name varchar(32) not null);

 

⑤外键 foreign key

constraint ordersid_FK foreign key(ordersid) references orders(id),

ordersid:从表的外键列

orders: 主表名

id : 从表指向的列(主键/unique)

从理论上说明，我们先建立主表，再建从表

 

--部门表

create table dept(id int primary key,

name varchar(64));

insert into dept values(1,’财务部’);

--雇员表

create table emp(id int primary key,

name varchar(32),

deptid int references dept(id));

上面的建立外键的写法是错误的。

应该这样.(表级定义)

create table emp(id int primary key,

name varchar(32),

deptid int ,

constraint emp_fk foreign key (deptid) references dept(id)

);

 

小结外键:

（1）外键只能指向 主表的主键列，或者  unique

（2）外键的数据类型和它指向的列的数据类型一样.

（3）外键的值，要么为空，要么是指向的那列中存在值.

 (4)外键可以指向本表的主键列，或者unique

 

补充讲解mysql 分页查询:

select * from 表名 where 条件 ... limit 从第几条取，取出几条

limit str,len

str：表示从第几条开始取，编号从0开始

len：表示取出的长度

 

返回第 4条 ----第 7条记录

select * from student limit 3,4;

按语文成绩排序，查询出第3名到第5名

select * from student order by chinese desc limit 2,3

 

扩展分页: pageNow, pageSize

select * from 表名 where 条件 [group by .. having .. order by ..] limit (pageNow-1)*pagesize, pageSize;