DDL
(操作数据库,表等)
数据定义语言,用来定义数据库对象:数据库,表,列等。
操作数据库(database)
1.查询(show)
SHOW DATABASES;#show(显示) database(数据库)
show databases;
2.创建(create)
1,创建数据库
CREATE DATABASE 数据库名称;#create(创建)database(数据库)
create database 数据库名称
2,创建数据库(判断,如果不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;#exist(cun'zai)
create database if not exists 数据库名称;
3.删除
1,删除数据库(drop)
DROP DATABASE 数据库名称;
drop database 数据库名称;
2,删除数据库(判断,如果存在则删除)
DROP DATABASE IF EXISTS 数据库名称;
drop database if exists 数据库名称;
4.使用数据库
1,使用数据库(use)use
USE 数据库名称;
use 数据库名称;
2,查看当前使用的数据库(select)
SELECT DATABASE();#select(选择)
select database();
数据类型
MySQL 支持多种类型,可以分为三类:
数值
tinyint : 小整数型,占一个字节
int : 大整数类型,占四个字节(例如 : age int)
double : 浮点类型
使用格式 : 字段名 double(总长度,小数点后保留的位数)
例如 : score double(5,2) #0~100的学生成绩
日期
date#日期值。只包含年月日
例如 :birthday date(生日)
datetime#混合日期和时间值。包含年月日时分秒
字符串
char : 定长字符串。
优点:存储性能高
缺点:浪费空间
例如 : name char(10) 如果存储的数据字符个数不足10个,也会占10个的空间
varchar : 变长字符串。
优点:节约空间
缺点:存储性能底
li'r: name varchar(10) 如果存储的数据字符个数不足10个,那就数据字符个数是几就占几个的空间
案例:
需求:设计一张学生表,请注重数据类型、长度的合理性
1. 编号
2. 姓名,姓名最长不超过10个汉字
3. 性别,因为取值只有两种可能,因此最多一个汉字
4. 生日,取值为年月日
5. 入学成绩,小数点后保留两位
6. 邮件地址,最大长度不超过 64
7. 家庭联系电话,不一定是手机号码,可能会出现 - 等字符
8. 学生状态(用数字表示,正常、休学、毕业...)
语句设计如下:
create table student (
id int,
name varchar(10),
gender char(1),
birthday date,
score double(5,2),
email varchar(15),
tel varchar(15),
status tinyint
);
操作表(table)
- 创建(Create)
- 查询(Retrieve)
- 修改(Update)
- 删除(Delete)
创建(Create)
CREATE TABLE 表名 (#create(创建) table(表)
字段名1 数据类型1,
字段名2 数据类型2,
...
字段名n 数据类型n
);#注意,最后一行末尾,不能加逗号。
create table tb_user(
id int,#先写名称,后写类型。
username varchar(20),#在SQL中,字符串是varchar类型,后面需要跟一个最大长度,比如说我们不允许用户名超过20位。
password varchar(32)#最后不能带逗号。
);
查询(Retrieve)
1,查询当前数据库下所有表的名称(show)
SHOW TABLES;#show(显示) tables (
show tables;
2,查询表结构(desc)
DESC 表名称;
desc 表名称;
修改(Update)(alter table 表名)
alter table (修改表;修改语句;修改数据库表结构)
1,修改表名(rename to)
ALTER TABLE 表名 RENAME TO 新的表名;#rename(重新命名)
alter table 表名 rename to 新的表名;
alter table student rename to stu;
2,添加一列(add)
ALTER TABLE 表名 ADD 列名 数据类型;#add(添加)
alter table 表名 add 列名 数据类型;
alter table stu add address varchar(50);
3,修改数据类型(modify)
ALTER TABLE 表名 MODIFY 列名 新数据类型;#modify(修改)
alter table 表名 modify 列名 新数据类型;
alter table stu modify address char(50);
4,修改列名和数据类型(change)
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;#change(改变)
alter table 表名 change 列名 新列名 新数据类型;
alter table stu change address addr varchar(30);
5,删除列(drop)
ALTER TABLE 表名 DROP 列名;#drop(下降)
alter table 表名 drop 列名;
alter table stu drop addr;
删除(Delete)
1,删除表(drop)
DROP TABLE 表名;
drop table 表名;
2,删除表时判断表是否存在(drop)
DROP TABLE IF EXISTS 表名;
drop table if exists 表名;
DML(对表中的数据进行增删改)
数据操作语言,用来对数据库中表的数据进行增删改。
DML主要是对数据进行增(insert)删(delete)改(update)操作。
添加数据
1,给指定列添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
inster into 表名(列名1,列名2,…) values(值1,值2,…);
-- 给指定列添加数据
INSERT INTO stu (id, name) VALUES (1, '张三');
2,给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,…);
insert into 表名 values(值1,值2,…);
-- 给所有列添加数据,列名的列表可以省略的
INSERT INTO stu VALUES (2,'李四','男','1999-06-10',88.88,'lisi@itcast.cn','13888888888',1);
3,批量添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
inster into 表名(列名1,列名2,…) values(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
inster into 表名 values(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
-- 批量添加数据
INSERT INTO stu VALUES
(2,'李四','男','1999-06-10',89.54,'lisi@qq.com','13876454637',1),
(2,'李四','男','1999-06-10',89.54,'lisi@qq.com','13876454637',1),
(2,'李四','男','1999-06-10',89.54,'lisi@qq.com','13876454637',1);
练习
查询所有数据的语句:
select * from stu;
//创建表
create table stu (
id int,
name varchar(10),
sex char(1),
birthday date,
score double(5,2),
email varchar(15),
tel varchar(15),
status tinyint
);
//查询所有数据
select * from stu;
//添加数据
-- 给指定列添加数据
INSERT INTO stu (id, name) VALUES (1, '张三');
-- 给所有列添加数据,列名的列表可以省略的
INSERT INTO stu (id,name,sex,birthday,score,email,tel,status) VALUES (2,'李四','男','1999-11-11',89.54,'lisi@qq.com','13876454637',1);
INSERT INTO stu VALUES (2,'李四','男','1999-06-10',88.88,'lisi@itcast.cn','13888888888',1);
-- 批量添加数据
INSERT INTO stu VALUES
(2,'李四','男','1999-06-10',89.54,'lisi@qq.com','13876454637',1),
(2,'李四','男','1999-06-10',89.54,'lisi@qq.com','13876454637',1),
(2,'李四','男','1999-06-10',89.54,'lisi@qq.com','13876454637',1);
修改数据
1,修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;
update 表名 set 列名1=值1,列名2=值2,… [where 条件] ;
update stu set birthday = '1999-12-12', score = 99.99 where name = '张三';
注意:
- 修改语句中如果不加条件,则将所有数据都修改!
- 像上面的语句中的中括号,表示在写sql语句中可以省略这部分
练习
-
将张三的性别改为女
update stu set sex = '女' where name = '张三';
-
将张三的生日改为 1999-12-12 分数改为99.99
update stu set birthday = '1999-12-12', score = 99.99 where name = '张三';
-
注意:如果update语句没有加where条件,则会将表中所有数据全部修改!
update stu set sex = '女';
上面语句的执行完后查询到的结果是:
select * from stu;
-- 修改数据
-- UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;
-- 将张三的性别改为女。
UPDATE stu SET sex = '女' where name = '张三';
-- 将张三的生日改为1999-12-12,分数改为99.99分。
UPDATE stu SET birthday = '1999-12-12',score = 99.99 where name = '张三';
-- 注意:如果update语句没有加where条件,则会将表中所有数据全部修改!
-- UPDATE stu SET sex = '女'; 不加where条件这会把数据表中的性别全部修改成女!
删除数据
1,删除数据
DELETE FROM 表名 [WHERE 条件] ;
delete from 表名 [wher条件] ;
delete from stu where name = '张三';
练习
-- 删除张三记录
delete from stu where name = '张三';
-- 删除stu表中所有的数据
delete from stu;
-- 删除数据
-- DELETE FROM 表名 [WHERE 条件] ;
-- 删除张三记录
DELETE FROM stu where name = '张三';
-- 删除stu表中所有的数据
DELETE FROM stu;
DQL(数据查询语言,用来查询数据库中表的记录)
基础查询
1,查询多个字段
SELECT 字段列表 FROM 表名;
select 字段列表 from 表名;
SELECT * FROM 表名;-- 查询所有数据
select * from 表名;-- 查询所有数据
2,去除重复记录
SELECT DISTINCT 字段列表 EROM 表名;
select distinct 字段列表 from 表名;
3,起别名
AS: AS也可以省略
select name,math as 数学成绩, english as 英语成绩 from stu;
条件查询(WHERE)
1,条件查询语法
SELECT 字段列表 FROM 表名 WHERE 条件列表
2,条件
SELECT * FROM stu;
-- 条件查询 ============
-- 1,查询年龄大于20岁的学员信息
select * from stu where age > 20;
-- 2,查询年龄大于等于20岁的学员信息
select * from stu where age >= 20;
-- 3,查询年龄大于等于20岁,并且年龄小于等于30岁学员信息
select * from stu where age >= 20 && age <= 30;
select * from stu where age >= 20 and age <= 30;
select * from stu where age BETWEEN 20 and 30;
-- 4,查询入学日期在“1998-09-01”到“1999-09-01”之间的学员信息
select * from stu where hire_date BETWEEN '1991-09-01' and '1999-09-01';
-- 5,查询年龄等于18岁的学员信息
select * from stu where age = 18;
-- 6,查询年龄不等于18岁的学员信息
select * from stu where age != 18;
select * from stu where age <> 18;
-- 7,查询年龄等于18岁或者年龄等于20岁或者年龄等于22岁的学员信息
select * from stu where age = 18 || age = 20 || age = 22;
select * from stu where age = 18 or age = 20 or age = 22;
select * from stu where age in(18,20,22);
-- 8,查询英语成绩为null的学员信息
-- 注意:null值的比较不能使用 = !=。需要使用is is not
select * from stu where english is null;
select * from stu where english is not null;
-- 模糊查询 like =========
/*
通配符:
(1) _:代表单个任意字符
(2) %:代表任意个数字符串
*/
-- 1.查询姓“马”的学员信息
select * from stu where name like '马%';
-- 2,查询第二个字是“花”的学员信息
select * from stu where name like '_花%';
-- 3,查询名字中包含“德”的学员信息
select * from stu where name like '%德%';
排序查询(ORDER BY)
1,排序查询语法
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1[排序方式1],[排序方式2]...;
排序方式:
- ASC:升序排序(默认值)
- DESC :降序排序
注意:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序。
SELECT * FROM stu;
/*
排序查询:
* 语法:SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1[排序方式1],[排序方式2]...;
* 排序方式:
* ASC:升序排列(默认值)
* DESC:降序排列
*/
-- 1,查询学生信息,按照年龄升序排列
select * from stu ORDER BY age ASC;
select * from stu ORDER BY age;
-- 2,查询学生信息,按照数学成绩降序排列
select * from stu ORDER BY math DESC;
-- 3,查询学生信息,按照数学成绩降序排列,如果数学成绩一样,再按照英语成绩升序排序。
select * from stu ORDER BY math DESC , english ASC;
聚合函数
1,概念:
将一列数据作为一个整体,进行纵向计算。
现有一需求让我们求表中所有数据的数学成绩的总和。这就是对math字段进行纵向求和。
2,聚合函数分类:
函数名 | 功能 |
---|---|
count(列名) | 统计数量(一般选用不为null的列) |
max(列名) | 最大值 |
min(列名) | 最小值 |
sum(列名) | 求和 |
avg(列名) | 平均值 |
3,聚合函数语法
SELECT 聚合函数名(列名) FROM 表;
注意:null 值不参与所有聚合函数运算
SELECT * FROM stu;
-- 语法:SELECT 聚合函数名(列名) FROM 表;
/*
聚合函数
count:统计数量
取值:
* 1 主键(非空且唯一)
* 2 *(*代表所有)
max:求最大值
min:求最小值
sum:求和
avg:求平均值
*/
-- 1,统计班级一共有多少个学生
select count(name) from stu; -- count 统计的列名不能为null。
select count(english) from stu;
select count(*) from stu;
-- 2,查询数学成绩的最高分
select max(math) from stu;
-- 3,查询数学成绩的最低分
select min(math) from stu;
-- 4,查询数学成绩的总分
select sum(math) from stu;
-- 5,查询数学成绩的平均分
select avg(math) from stu;
-- 6,查询英语成绩的最低分
select min(english) from stu;
分组查询(GROUP BY)
1,分组查询语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义。
2,where 和 having 区别:
-
执行时机不一样:where 是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
-
可判断的条件不一样:where 不能对聚合函数进行判断,having 可以。
3,执行顺序
where > 聚合函数 > having
SELECT * FROM stu;
/*
分组函数:
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤]...;
*/
-- 1,查询男同学和女同学各自的数学平均分
select sex,avg(math) from stu GROUP BY sex;
-- 注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义。
select name,sex,avg(math) from stu GROUP BY sex;
-- 2,查询男同学和女同学各自的数学平均分,以及各自人数。
select sex,avg(math),count(*) from stu GROUP BY sex;
-- 3,查询男同学和女同学各自的数学平均分,以及各自人数。要求:分数低于70分的不参与分组。
select sex,avg(math),count(*) from stu where math > 70 GROUP BY sex;
-- 4,查询男同学和女同学各自的数学平均分,以及各自人数。要求:分数低于70分的不参与分组,分组之后人数大于2个的。
select sex,avg(math),count(*) from stu where math > 70 GROUP BY sex having count(*) > 2;
分页查询(LIMIT)
分页查询语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
- 起始索引:从0开始
公式:
起始索引 = (当前页码 - 1) * 每页显示的条数
select * from stu;
/*
分页查询:
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
起始索引:从0开始
*/
-- 1,从0开始查询,查询三条数据
select * from stu limit 0,3;
-- 2,每页显示三条数据,查询第1页数据
select * from stu limit 0,3;
-- 3,每页显示三条数据,查询第2页数据
select * from stu limit 3,3;
-- 4,每页显示三条数据,查询第3页数据
select * from stu limit 6,3;
-- 起始索引 = (当前页码 - 1) * 每页显示的条数
注意:
- 分页查询limit是MySQL数据库的方言。
- Oracle分页查询使用rownumber
- SQL Server分页查询使用top