使用SQL语句对数据库进行CRUD操作
【创建数据库】
create database 数据库名;
create database 数据库名 character set 字符集;
【查看数据库】
查看数据库服务器中的所有的数据库:show databases;
查看某个数据库的定义的信息:show create database 数据库名;
【删除数据库】
drop database 数据库名称;
【修改数据库】
alter database 数据库名 character set 字符集;
【其他的数据库操作命令】
切换数据库:use 数据库名;
查看正在使用的数据库:select database();
使用SQL语句对数据库中表进行CRUD的操作
【创建表】
create table 表名(
字段名 类型(长度) 约束,
字段名 类型(长度) 约束,
...
);
【MySQL的数据类型】
Java MySQL
int int
float float
double double
char/String char/varchar(char固定长度字符串,varchar可变长度的字符串)
Date date,time,datetime,timestamp
文件类型 BLOB、TEXT TEXT指的是文本文件 BLOB二进制文件
【单表约束】
主键约束(一个表中只能有一个主键且值肯定是唯一的):primary key
auto_increment自动增长,从1开始
MySQL的auto_increment属性只能用在主键上
唯一约束(值必须是唯一的,但是一个表中可以有多个唯一的字段):unique
非空约束(值不能为空):not null
【创建一个商品分类表】
分类id int类型主键 自动增长(auto_increment)
分类名称 字符串类型 长度20
create table category(category_id int primary key auto_increment,name varchar(20));
注意:建表之前一定先选择数据库
use 数据库名;
【查看表】
查看数据库中的所有表:show tables;
查看表结构:desc 表名;
查看表的创建信息:show create table 表名;
【删除表】
drop table 表名;
【修改表】
alter table 表名 add 列名 类型(长度) 约束; 添加列
alter table 表名 modify 列名 类型(长度) 约束; 修改列的类型长度及约束
alter table 表名 change 旧列名 新列名 类型(长度) 约束; 修改列名,类型,长度,约束
alter table 表名 drop 列名; 删除列
rename table 表名 to 新表名; 修改表名
alter table 表名 character set 字符集; 修改表的字符集
使用SQL语句对表中的数据进行CRUD操作
创建商品表:
id,名称,价格,分类id
【插入记录】
insert into 表名 (列名1,列名2,列名3..) values (值1,值2,..); 向表中插入某些列
insert into 表名 values (值1,值2,值3..); 向表中插入所有列
注意:
1.列名数与values后面的值的个数相等
2.列的顺序与插入的值得数据类型要保持一致
3.插入值得时候不能超过最大长度.
4.值如果是字符串或者日期需要加’’
【修改记录】
update 表名 set 字段名=值,字段名=值 [where 条件];
注意:
1.列名的类型与修改的值要一致
2.修改值得时候不能超过最大长度
3.值如果是字符串或者日期需要加’’
【where后的条件写法】
> ,<,=,>=,<=
like:使用占位符 _ 和 % _代表一个字符 %代表任意个字符.
in:在某个范围中获得值.
and:可以将多个条件拼接起来
【删除记录】
delete from 表名 [where 条件];
注意:
删除表中所有记录使用delete from 表名; 还是用truncate table 表名;
区别:
删除方式:delete 一条一条删除. 而truncate 直接将表结构记录删除.
事务控制DML(transaction),而delete属于DML.如果在一个事务中,delete数据,这些数据可以找回.truncate删除的数据找不回来.
start transaction;开启事务
commit;提交事务
rollback;回滚事务
【查询记录】
select [distinct]*[列名,列名] from 表 [where 条件].
简单查询
查询所有的商品:select * from product;
查询商品名和商品价格:select name,price from product;
别名查询.使用的关键字是as,as可以省略的.
表别名:select * from product as p;
列别名:select name 姓名,price 价格 from product;
去掉重复值的查询:select distinct name,price from product;
select运算查询
将所有商品的价格+20元进行显示:select name,price+20 价格 from product;
条件查询
查询商品名称为xxx的商品所有信息:select * from product where name='小米8手机';
查询商品价格>xxx元的所有的商品信息:select * from product where price>5000;
查询商品价格>xxx元且名字包含‘手机’的商品信息:select * from product where price>3000 and name like '%手机%';
查看商品id等于1或者3或者5的商品信息:
select * from product where product_id=1 or product_id=3 or product_id=5;
select * from product where product_id in(1,3,5);
(order by)排序查询
查询所有的商品,按价格进行排序:select * from product order by price;
查询名称有xxx的商品的信息并且按价格降序排序:select * from product where name like '%电脑%' order by price desc;
聚合函数
获得所有商品的价格的总和:select sum(price) from product;
获得所有商品的平均价格:select avg(price) from product;
获得所有商品的个数:select count(*) from product;
获取最贵的商品价格:select max(price) from product;
获取最便宜的商品价格:select min(price) from product;
分组查询
根据category_id字段分组,分组后统计商品的个数:select count(*),category_id from product group by category_id;
根据category_id分组,分组统计每组商品的平均价格并且平均价格> 4000的category_id:
select avg(price) p,category_id from product group by category_id having p>4000;
【外键约束】
多个表之间是有关系的,那么关系靠谁来维护?
外键约束写法:
表已存在外键约束:
alter table product add foreign key (category_id) references
category(category_id);
创建表的时候直接外键约束:
create table product(product_id int primary key auto_increment,name varchar(20),price float,category_id int,foreign key (category_id) references category(category_id));
【多表的关系】
一对多关系:客户和订单,分类和商品...
一对多建表原则:在多的一方创建一个字段,字段作为外键指向一的一方的主键.
多对多关系:学生和课程...
多对多关系建表原则:需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指 向各自一方的主键.
一对一关系:在实际的开发中应用不多.因为一对一可以创建成一张表.
唯一外键对应:假设一对一是一个一对多的关系,在多的一方创建一个外键指向一的一方的主 键,将外键设置为unique.
主键对应:让一对一的双方的主键进行建立关系.
多表查询
交叉连接查询(基本不会使用-得到的是两个表的乘积)了解
语法:select * from A,B;
内连接查询(使用的关键字 inner join – inner可以省略)
隐式内连接:select * from A,B where 条件;
显示内连接:select * from A inner join B on 条件;
外连接查询(使用的关键字 outer join – outer可以省略)
左外连接:left outer join
select * from A left outer join B on 条件;
右外连接:right outer join
select * from A right outer join B on 条件;
更新
去重
distinct
ec:select distinct Sname from student inner join sc on student.Sno=Sc.Sno;