java mysql语句_java学习(一)mysql操作语句(javaweb开发)

1.mysql基本操作

1.1 启动数据库

启动时可以在cmd命令中可以直接采用：

net start mysql

1.2 连接数据库

命令行输入：

mysql -uroot -p******

其中，root为用户名，******为密码；

1.3 注释

mysql中的注释采用在语句后标注"--*"或者"#**"的方式执行，例如：

alter table user change password pwd varchar(20);--改变user表格中的password列名为pwd，字符允许长度不得超过20；

2. 数据定义语言(DDL)

数据定义语言操作对象是：数据库和表，关键词是：create,alter, drop

2.1 数据库常用命令格式

创建：

create database 数据库名称;

删除：

drop database 数据库名称;

查看所有数据库：

show databases;

切换或者进入数据库：

use 数据库名称;

2.2 常用表格操作命令

创建表格:

create table 表名(字段描述,字段描述);

字段描述：

字段名称 字段类型 [约束]

例如：

create table user(

id int primary key auto_increment,

username varchar(20)

);

查看当前数据库下的所有表:

show tables;

查看表结构：

desc 表名;

查看建表语句:

show create table 表名;

修改表格：

alter table 表名......

修改表名:

alter table 旧表名 rename to 新表名;

添加字段:

alter table 表名 add [column] 字段描述；

如：alter table 表名 add password varchar(20);

修改字段名：

alter table 表名 change 字段名称 新字段描述;

例如：

alter table user change password pwd varchar(20);

修改字段描述：

alter table 表名 modify 字段名称 字段类型 [约束];

例如：

alter table user modify pwd int;

删除字段

alter table 表名 drop 字段名;

例如：

alter table user drop pwd;

删除表格:

drop table 表名;

3. DML(数据操作语言)

操作对象：记录(行);

关键词:insert update delete

3.1 插入全部数据

格式：

insert into 表名 values(字段值1,字段值2,...,字段值n);

注意：

默认插入全部字段，必须保证values后面的内容的类型和顺序与表格结构中的完全一致，不允许存在漏项，错项等；

若字段类型为数字，可以省略引号，但是，建议还是全部用单引号进行引用;

例如：

insert into user values(1,'tom');

3.2插入指定列的数据

格式：

insert into 表名(字段名,字段名,...,字段名) values(字段值,字段值,..,字段值);

注意：

插入的字段必须保证与values前面的内容的类型和顺序与表格结构中的完全一致，不允许存在漏项，错项等；

3.3 修改

格式：

update 表名 set 字段名=字段值，字段名1=字段值1，...[where 条件]；

例如：

update user set username = 'jerry' whre username = 'jack';

3.4 删除

格式:

delete from 表名 [where 条件];

例如:

ddelete from user where id='2';

3.5 查询语句及案例分析

CREATE TABLE products(

pid INT PRIMARY KEY AUTO_INCREMENT,

pname VARCHAR(20),

price DOUBLE,

pnum INT,

cno INT,

pdate TIMESTAMP

);

INSERT INTO products VALUES(NULL,'泰国榴莲',98,12,1,NULL);

INSERT INTO products VALUES(NULL,'新疆大枣',38,123,1,NULL);

INSERT INTO products VALUES(NULL,'新疆切糕',68,50,2,NULL);

INSERT INTO products VALUES(NULL,'十三香',10,200,3,NULL);

INSERT INTO products VALUES(NULL,'老干妈',38,123,1,NULL);

INSERT INTO products VALUES(NULL,'豌豆黄',38,123,1,NULL);

3.5.1查询所有商品

SELECT * FROM products;

3.5.2查询商品名和商品价格

SELECT pname,price FROM products;

3.5.3查询商品都有哪些价格

采用distinct可以去除重复的数据，格式为：

SELECT price FROM products;

SELECT DISTINCT price FROM products;

3.5.4.将所有价格+10显示

如果直接采用第一条查询语句，那么price列的列名就会变成price+10，因此需要对列起别名的方式；

格式为：

select price+10 from products;

给列起别名 格式：字段名 [AS] 别名

SELECT price+10 '新价格' FROM products;

3.5.5.查询商品名曾为“十三香”的商品所有信息；

SELECT * FROM products WHERE pname = '十三香';

3.5.6.查询商品价格>60的所有商品信息；

SELECT * FROM products WHERE price>60;

3.5.7.查询商品名称中包含"新"的商品

该种查询采用模糊匹配方式，格式为：字段名 like "匹配原则"，其中匹配内容格式如下：

“*” 值为*

“%*” 值以*结尾

“*%” 值以*开头

“%*%”值中包含*

--匹配个数 “————”表示占有两个位置

SELECT * FROM products WHERE pname LIKE "%新%";

3.5.8.查询价格为38,68,98的商品

SELECT * FROM products WHERE price IN(38,68,98);

3.5.9.查询价格在38到98之间的商品,并且包含该数据

SELECT * FROM products WHERE price BETWEEN 38 AND 98;

3.5.10 排序查询

查询名称带有“新”的商品的信息并且按照价格降序排序(asc:升序，desc降序)：

select * from products where pname like '%新%' order by price desc;

3.5.11 聚合函数

对一列进行计算，返回值只有一个，计算时会忽略null值；

获得所有商品的价格总和：sum()

select sum(price) from products;

获得商品表中价格的平均数avg()

select avg(price) from products;

当需要制定保留多少位小数时，需要使用round命令：

select round(avg(price),2) from products;

获取商品表中有多少条记录：

select count(*) from products;

3.5.12 分组

使用group by实现分组功能。

根据cno字段分组，分组后，统计商品的个数：

select cno,count(*) from products group by cno;

根据cno分组，分组统计每组商品的总数量，并且总数量>200：

select cno,sum(pnum) from products group by cno;

select cno,sum(pnum) from products group by cno having sum(pnum)>200;

注意：where和having之间的区别：

1)where是对分组前的数据进行过滤，having是对分组后的数据进行过滤；

2)where后面不能使用聚合函数，但是having可以。

4.多表

4.1 外键(一对多)

在开发中，我们将一对多的关系，一方称之为主表或者一表，多方称之为多表或者从表，为了表示一对多的关系，一般会在多表添加一个字段，字段名称自定义(建议：主表名称_id)。字段类型一般和主表的主键的类型保持一致，我们称这个字段为外键。

为了保证数据的有效性和完整性，在多表的一方添加外键约束，格式如下：

alter table 多表名称 add foreign key(外键名称) references 一表名称(主键)；

如用户和订单的关系中，描述如下：

alter table orders add foreign key(user_id) references user(id);

添加了外键约束之后有如下的特点：

1)主表中不能删除从表中已经引用的数据；

2)从表中不能添加主表中不存的数据；

4.2 多对多数据库

多对多，在开发中一般引入一张中间表，在中间表中村昂两张表的主键，一般还会降这俩呢主键设置层中间表哥的联合主键。

为了保证数据的有效性和完整性，在中间表上添加两个外键即可。

例如商品与订单的关系：

商品表：

create table product(

id int primary key auto_increment,

name varchar(20),

price double

);

订单表：

create table orders(

id int primary key auto_increment,

price double

);

中间表：

create table orderitem(

oid int,

pid int

);

添加外键约束：

alter table orderitem add foreign key(oid) references orders(id);

alter table orderitem add foreign key(pid) references product(id);

5. 多表查询

通过下面的案例对多表查询进行描述：

--创建数据库

create database multitable;

use multitable;

--用户表(user)

create table user(

id int auto_increment primary key,

username varchar(50)

);

--订单表(orders)

create table orders(

id int auto_increment primary key,

price double,

user_id int

);

--给订单添加外键约束

alter table orders add constraint user_fk foreign key (user_id) references user(id);

--向user表中添加数据

insert into user values(3,'张三');

insert into user values(4,'李四');

insert into user values(5,王五'');

insert into user values(6,赵六'');

--向orders表中插入数据

insert into orders values(1,1314,3);

insert into orders values(2,1314,3);

insert into orders values(3,15,4);

insert into orders values(4,315,5);

insert into orders values(5,1014,null);

练习题目如下：

1.查询用户订单，没有订单的用户不显示；

2.查询所有用户的订单详情；

3.查询所有订单的用户详情。

5.1 笛卡尔积

多张表无条件的联合查询，没有任何特殊含义。格式为：

select a.*, b.* from a,b;

5.2 内连接

内连接共有两种形式，其中显示的内连接最为常用。

显示的内连接：

select a.*,b.* from a [inner] join b on ab的连接条件;

隐藏的内连接：

select a.*,b.* from a,b where ab的连接条件;

因此，对于查询用户订单，没有订单的用户不显示，语句为：

--隐藏的内连接

select user.*, orders.* from user, orders where user.id=orders.user_id;

--显示的内连接

select user.*,orders.* from user join orders on user.id=orders.user_id;

5.3 外连接

外连接分为左外连接和右外连接，其中左外连接：

select a.*,b.* from a left [outer] join b on 连接条件;

--含义为：

--先展示join左侧的(a)表的所有数据，根据条件关联查询join右侧的表(b)，符合条件则展示出来，不符合则以null展示。

右外连接：

select a.*,b.* from b right [outer] join a on 连接条件;

--含义为：

--先展示join右侧的(a)表的所有数据，根据条件关联查询join左侧的表(b)，符合条件则展示出来，不符合则以null展示。

因此，"查询所有用户的订单详情"采用左外连接的查询语句为:

select user.*,orders.* from user left join orders on user.id=orders.user_id;

因此，“查询所有订单的用户详情”采用右外连接的查询语句为：

select orders.*,user.* from user right join orders on user.id=orders.user_id;

5.4 子查询

一个查询语句依赖于另一个查询语句的结果。以案例的形式进行说明。

问题一：查询用户为张三的订单详情

不采用子查询，方式为：

1.先查询张三的id

select id from user where username='张三';

2.select * from orders where user_id = 3;

采用子查询方式为：

select * from orders where user_id=(select id from user where username='张三');

问题二：查询出订单的价格大于300的所有用户信息

不采用子查询：

1.先查询订单价格>300的用户的id

select user_id from orders where price>300;//(3,3,5,null)

2.select * from user where id in (3,3,5,null);

采用子查询：

select * from user where id in(select user_id from orders where price>300);

问题三：查询订单价格大于300的订单信息及相关用户的信息；

内连接方式：

select orders.*,user.* from orders,user where user.id=orders.user_id and orders.price>300;

子查询方式:

select user.*,tmp.* from user,(select * from orders where price>300) as tmp where user.id=tmp.user_id;