对数据库表进行操作
SQL功能 | 实现动词 |
---|---|
数据查询 | SELECT |
数据定义 | CREATE、DROP、ALTER |
数据操纵 | INSERT、UPDATE、DELETE |
数据控制 | GRANT、REVOKE |
1、SQL创建表
语法:
create table 表名称{
字符名称 字符类型(长度) 约束,
字符名称 字符类型(长度) 约束,
···
字符名称 字符类型(长度) 约束
};
字符类型:
一个实体对应一个表,一个实体属性对应表的一个字段。
c或c++中的类型 mysql中的类型
byte/short/int/long tinying/smallint/int/bigint
float float
double double
boolean bit
char/string char/varchar
char和varchar的区别:
char**代表的是固定长度的字符或字符串**
定义类型char(8),向这个字段存入字符串hello,那么数据库使用三个空格将其补全。
varchar**代表的是可变长度的字符串**
定义类型varchar(8),向这个字段存入字符串hello,那么存入到数据库就是hello。
Date date/time/datatime/timestamp
datetime**和**timestamp**区别**
datetime**就是既有日期又有时间的日期类型,如果没有向这个字段中存值,数据库使用null存入到数据库中。**
timestamp**也是既有日期又又时间的日期类型,如果没有向这个字段中存值,数据库使用当前的系统时间存入到数据库。**
约束:
约束作用:保证数据的完整性
单表约束分类:
主键约束(primary key):一个表中,存在一个属性,是这个记录的唯一标识(主键约束就是唯一,非空的)
唯一约束(unique):字段的值必须是唯一的,不能重复
非空约束(not null):不能是空的
建表语句:
create database 数据库名称; 先建数据库
use 数据库名称; 后使用数据库
create table 表名( 才能创建表
id int primary key,
username varchar(20) unique,
password varchar(20) not null,
age int,
birthday date
);
2、查看某个数据库下的所有的表
前提:
use 数据库名称;
语法:
show tables;
3、查看某个表的结构信息
语法:
desc 表名;
4、SQL删除表
语法:
drop table 表名;
5、SQL修改表
-
修改表:添加列
alter table 表名 add 列名 类型(长度) 约束; alter table student add image varchar(20) ;
-
修改表:修改列类型,长度和约束
alter table 表名 modify 类名 类型(长度) 约束; alter table student modify image varchar(10);
-
修改表:删除列
alter table 表名 drop 列名; alter table student drop age;
-
修改表:修改列名称
alter table 表名 change 旧列名 新列名 类型(长度) 约束; alter table student change image pic varchar(10);
-
修改表:修改表名
rename table 旧表名 to 新表名; rename table student to people;
-
修改表:修改表的字符集
alter table 表名 character set 字符集; alter table people character set gbk;
6、对数据库表的记录进行操作(重点)
6.1、SQL 添加表的记录
-
语法
-
向表中插入某些列:
insert into student (列名1,列名2,列名3...) values (值1,值2,值3...);
-
向表中插入所有列:
insert into student values (值1,值2,值3...);
-
-
注意事项
- 1.值的类型与数据库中表列的类型一致。
- 2.值的顺序与数据库中表列的顺序一致。
- 3.值的最大长度不能超过列设置最大长度。
- 4.值的类型是字符串或者是日期类型,使用单引号引起来。
-
添加记录
-
添加某几列
insert into student (num,name) values ('3','rose');
-
添加所有列
insert into student values('4','kobe','1980-8-24');
-
6.2、SQL 修改表的记录
-
语法
update 表名 set 列名=值,列名=值 [where 条件];
-
注意事项
- 值的类型与列的类型一致。
- 值的最大长度不能超过列设置的最大长度。
- 字符串类型和日期类型添加单引号
-
修改某一列的所有值
update student set birthday='2001-03-02';
-
按条件修改多个列
update student set name='kobe',birthday='2006-6-6' where num='2';
6.3、SQL 删除表的记录
-
语法
delete from 表名 [where 条件]; -
注意事项
- 删除表的记录,指的是删除表中的一行记录。
- 删除如果没有条件,默认是删除表中的所有记录。
-
删除某一条记录
delete from student where num='1';
-
删除表中的所有记录
delete from student;
-
删除标中的记录有两种做法:
- delete from student;
- 删除所有的记录,属于DML语句,一条记录一条记录删除。事务可以作用在DML语句上的(可以恢复)
- truncate table student;
- 删除所有记录,属于DDL语句,将表删除床建一个结构一样的表。事务不能控制DDL的(不可以恢复)
- 删除所有记录,属于DDL语句,将表删除床建一个结构一样的表。事务不能控制DDL的(不可以恢复)
- delete from student;
6.4、SQL 查看表的记录(重点)
6.4.1 基本查询(selete)
-
语法
select [distinct] * |列名 from 表 [条件]; select distinct 列名 from 表名;(查询时去掉重复的行)
-
环境的准备
create table exam( id int primary key auto_increment, name varchar(20), english int, chinese int, math int ); insert into exam values (null,'张三',85,74,91); insert into exam values(null,'李四',95,90,83); insert into exam values(null,'王五',85,84,59); insert into exam values(null,'赵六',75,79,76); insert into exam values(null,'田七',69,63,98); insert into exam values(null,'李老八',89,90,83);
-
查询所有学生考试成绩信息
select * from exam;
-
查询所有学生的姓名和英语成绩
select name,english from exam;
-
查询英语成绩信息(不显示重复的值)
select distinct english from exam;
-
查看学生姓名和学生的总成绩
select name ,english+chinese+math from exam;
-
别名查询
select name,english+chinese+math as sum from exam;
-
6.4.2 条件查询(where)
查询条件 | 谓词 |
---|---|
比较(比较运算符) | =、>、<、>=、<=、!=、<>、!>、!<;not+上述比较运算符 |
确定范围 | BETWEEN、AND、NOT BETWEEN AND |
确定集合 | IN 、 NOT IN |
字符匹配 | LIKE 、 NOT LIKE |
空值 | IS NULL、IS NOT NULL |
多重条件(逻辑谓词) | AND、OR |
-
使用where子句
- 关系>,<,>=,<=,<>,=
- like:模糊查询
- in:范围查询
- 条件关联:and,or,not-
查询李四学生的成绩:
select * from exam where name = '李四';
-
查询名称叫李四学生并且英文大于90分
select * from exam where name='李四' and english > 90;
-
查询姓李的学生的信息
like可以进行模糊查询,在like子句可以使用_或者%作为占位符。_可以代表一个字符,而%可以代表任意个字符。 * like ’李_’ :名字中必须是两个字,而且是姓李的。 * like '李%’ :名字中姓李的学生,李字后可以是1个或任意个字符。 * like '%四' :名字中以四结尾的。 * like '%王%’ :只要名称中包含这个字就可以。
-
查询英语成绩是69,75,89学生的信息
select * from exam where english in (69,75,89);
-
按范围查询数学成绩在50—90范围内的学生信息
select * from exam where math between 50 and 90;
-
6.4.3 排序查询(order by)
-
使用order by不用字段名称 asc/desc;
select * from exam order by chinese;(默认是asc正序)
-
查询学生信息,并且按照语文成绩进行排序;
select * from exam order by chinese desc;
-
查询学生信息,并且按照语文成绩倒叙排序:
-
查询学生信息,先按照语文成绩进行降序排序,如果成绩相同再按照英语成绩升序排序
select * from exam order by chinese desc,english asc;
-
查询姓李的学生的信息,按照英语成绩降序排序
select * from exam where name like '李%' order by english desc;
-
6.4.4 分组统计查询(COUNT、SUM、AVG…)
聚集函数(distinct:去除重复值,ALL为默认值:不取消重复值) | 功能 |
---|---|
COUNT ( [DISTINCT | ALL] ) | 统计表中元组的个数 |
COUNT( [DISTINCT | ALL] <列名>) | 统计一列中值的个数 |
SUM( [DISTINCT | ALL] <列名>) | 统计一列值的总和(此列必须是数值型) |
AVG( [DISTINCT | ALL] <列名>) | 计算一列的平均值(此列必须是数值型) |
MAX( [DISTINCT | ALL] <列名>) | 求一列值中的最大值 |
MIN( [DISTINCT | ALL] <列名>) | 求一列值中的最小值 |
- 聚合函数使用
-
sum();
-
获取所有学生的英语成绩的总和:
select sum(english) from exam;
-
获得所有学生的英语成绩和数学成绩总和:
select max(english),max(math) from exam;
-
查询所有学生各科的总成绩
select sum(english)+sum(chinese)+sum(math) from exam;
select sum(english+chinese+math) from exam;
与上面的语句有什么不同? * 上面的语句是按照列的方式统计,英语成绩总和+语文成绩总和+数学成绩总和。 * 下面的语句先计算英语+语文+数学然后再求和。 * 如果数据中出现一个null数值,第一种可以正常统计数值,而第二种却不能,因为第二种方法是先计算行,但是任何数和NULL相加都为NULL所以得不到正确值。 * 解决方法:使用ifnull的函数
-
获得学生的总数
select count(*) from exam;
-
获得姓李的学生的个数
select count(*) from exam where name like '李%';
-
-
max();
-
获取数学成绩的最高分:
select max(math) from exam;
-
-
min();
-
获取数学最小值:
select min(math) from exam;
-
-
avg();
-
获取语文成绩的平均值
select avg(chinese) from exam;
-
-
6.4.5 分组查询(group by)
-
语法:使用group by 字段名称;
-
环境准备
create table orderitem( id int primary key auto_increment, product varchar(20), price double ); insert into orderitem values (null,'电视机',2999); insert into orderitem values (null,'电视机',2999); insert into orderitem values (null,'洗衣机',1000); insert into orderitem values (null,'洗衣机',1000); insert into orderitem values (null,'洗衣机',1000); insert into orderitem values (null,'冰箱',3999); insert into orderitem values (null,'冰箱',3999); insert into orderitem values (null,'冰箱',1999);
-
按商品名称统计,每类商品所购买的个数:
select product,count(*) from orderitem group by product;
-
按商品名称统计,每类商品所花费的总金额:
select product,sum(price) from orderitem group by product;
**** where的子句后面不能跟着聚合函数 * 如果在使用带有聚合函数的条件过滤(分组后条件过滤)需要使用一个关键字having
where是从列中选出列组成一个组,having是从组好的组中选出组。 select product,sum(price) from orderitem group by product having sum(price) > 5000;
-
按商品名称统计,统计每类商品花费的总金额在5000元以上的商品,并且按照总金额升序排序
select product,sum(price) from orderitem group by product having sum(price) > 5000 order by sum(price) asc;
-
总结:(使用顺序)
S(select)…F(from)…W(where)…G(group by)…H(having)…O(order by);
-