1:SQL
结构化查询语言,专门用来操作数据库(增删改查,权限等)
select
from
where
group by
having
order by
limit
1.1分类:
DCL
概念:DCL(Data Control Language) 数据控制语言,用来定义数据库的访问权限和安全级别,及创建用户(权限控制)
权限控制:
# 修改密码
set password=password('root');
DDL
数据库操作
# 查看
SHOW DATABASES;
# 创建
CREATE DATABASE 数据库名称
CREATE DATABASE IF NOT EXISTS 数据库名称;
# 删除
DROP DATABASE 数据库名称;
DROP DATABASE IF EXISTS 数据库名称;
# 进入
USE 数据库名称;
数据库操作
# 查看表
show tables;
# 展示表结构
DESC 表名;
# 创建(掌握)
CREATE TABLE 表名 (
字段名1 数据类型1,
字段名2 数据类型2,
…
字段名n 数据类型n
);
# 删除
DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;
# 修改
修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
-- 将表名student修改为stu
alter table student rename to stu;
添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
-- 给stu表添加一列address,该字段类型是varchar(50)
alter table stu add address varchar(50);
修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);
修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
-- 将stu表中的address字段名改为 addr,类型改为varchar(50)
alter table stu change address addr varchar(50);
删除列
ALTER TABLE 表名 DROP 列名;
-- 将stu表中的addr字段 删除
alter table stu drop addr;
练习
create table stu(
id int);
alter table stu rename to student;--修改表名
alter table student add sname varchar(10);-- 添加一列
alter table student modify sname int;-- 修改数据类型
alter table student change sname name varchar(10);-- 修改列名和数据类型
alter table student drop name; -- 删除列
数据类型
数值
tinyint : 小整数型,占一个字节
int : 大整数类型,占四个字节
eg : age int
bigint : 长整型
double : 浮点类型
使用格式: 字段名 double(总长度,小数点后保留的位数)
eg : score double(5,2)
日期
date : 日期值。只包含年月日 插入时 使用 'yyyy-MM-dd'
eg :birthday date :
datetime : 混合日期和时间值。包含年月日时分秒
字符串
char : 定长字符串。
优点:存储性能高
缺点:浪费空间
eg : name char(10) 如果存储的数据字符个数不足10个,也会占10个的空间
varchar : 变长字符串。
优点:节约空间
缺点:存储性能底
eg : name varchar(10) 如果存储的数据字符个数不足10个,那就数据字符个数是几就占几个的空间
DML
/*
添加语法
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
INSERT INTO 表名 VALUES(值1,值2,…);
批量添加语法
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
*/
-- 给指定列添加数据
INSERT INTO stu (id, NAME) VALUES (1, '张三');
-- 给所有列添加数据,列名的列表可以省略的
INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
-- 批量添加数据
INSERT INTO stu VALUES
(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
-- 修改数据
-- 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 = '女';
-- 删除 DELETE FROM 表名 [WHERE 条件] ;
-- 删除张三记录
delete from stu where name = '张三' ;
delete from stu;
练习 :1. 创建数据库db3
2. 创建商品表, 要求有(商品编号, 商品名称, 商品价格, 商品描述, 商品生产日期(年月日即可))
3. 使用sql语句, 对商品表添加商品库存一列
4. 使用sql语句插入若干条数据
5. 使用sql语句删除编号为3的数据
6. 使用sql语句将编号为1商品价格改成100
create database db3;-- 创建数据库db3
create table list(-- 创建商品表,
id int ,-- 商品编号
name varchar(20),-- 商品名称
price double(10,2),-- 商品价格
described varchar(100),-- 商品描述
l_date date-- 商品生产日期(年月日即可)
);
-- 使用sql语句, 对商品表添加商品库存一列
alter table list add inventory int;
-- 使用sql语句插入若干条数据
insert into list values
(1,'手机',1000,'能敲核桃','2022-9-26',50),
(2,'手机',1000,'能敲核桃','2022-9-26',50),
(3,'手机',1000,'能敲核桃','2022-9-26',50)
-- 使用sql语句删除编号为3的数据
delete from list where id=3;
-- 使用sql语句将编号为1商品价格改成100
update list set price=100 where id=1;
DQL
-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE stu (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math double(5,2), -- 数学成绩
english double(5,2), -- 英语成绩
hire_date date -- 入学时间
);
-- 添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date)
VALUES
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');
基础查询
# 查询 所有列
select * from user
# 查询 指定列返回
select id,username from user
# 去重distinct :
# 注意 : distinct 会对 其后面的所有列合在一起判断去重
select distinct address from stu
select distinct address as 地址, name from stu
# 起别名 as
select address as 地址 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 '1998-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 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 = null; -- 不行的
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 '%德%';
排序
/*
排序查询:
* 语法:SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;
* 排序方式:
* ASC:升序排列(默认值)
* DESC:降序排列
*/
-- 1.查询学生信息,按照年龄升序排列
select * from stu order by age ;
-- 2.查询学生信息,按照数学成绩降序排列
select * from stu order by math desc ;
-- 3.查询学生信息,按照数学成绩降序排列,如果数学成绩一样,再按照英语成绩升序排列
select * from stu order by math desc , english asc ;
聚合
/*
count(列名) 统计数量(一般选用不为null的列)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值
*/
-- 聚合函数语法
SELECT 聚合函数名(列名) FROM 表;
统计班级一共有多少个学生
select count(id) from stu;
select count(english) from stu;
注意:
上面语句根据某个字段进行统计,如果该字段某一行的值为null的话,将不会被统计。所以可以在count(*) 来实现。 表示所有字段数据,一行中也不可能所有的数据都为null,所以建议使用 count(*)
练习:1. 已知员工表中包含如下字段(员工eid,员工姓名,入职日期,工资,奖金,部门 )
要求:
1.请求按照要求将表创建出来,并执行添加员工的操作, 添加员工的数据已经给定:
INSERT INTO emp VALUES
(1001,'孙悟空','2000-12-17','8000.00',NULL,"教研部"),
(1002,'卢俊义','2001-02-20','16000.00','3000.00',"学工部"),
(1003,'林冲','2001-02-22','12500.00','5000.00',"学工部"),
(1004,'唐僧','2001-04-02','29750.00',NULL,"教研部"),
(1005,'李逵','2001-09-28','12500.00','14000.00',"学工部"),
(1006,'宋江','2001-05-01','28500.00',NULL,"学工部"),
(1007,'刘备','2001-09-01','24500.00',NULL,"教研部"),
(1008,'猪八戒','2007-04-19','30000.00',NULL,"咨询部"),
(1009,'罗贯中','2001-11-17','50000.00',NULL,"学工部"),
(1010,'吴用','2001-09-08','15000.00','0.00',"教研部"),
(1011,'沙僧','2007-05-23','11000.00',NULL,"咨询部"),
(1012,'李逵','2001-12-03','9500.00',NULL,"教研部"),
(1013,'小白龙','2001-12-03','30000.00',NULL,"咨询部"),
(1014,'关羽','2002-01-23','13000.00',NULL,"咨询部");
2.删除入职在2005年以后入职的员工
3.给薪水>20000块的员工,降薪2000块
4.请按照薪水升序排序查询所有员工的信息
5.请查询所有员工的平均薪水
6.请将奖金为null的员工信息查询出
create table staff(
eid int,
name varchar(20),
hiredate date,
salary double(7,2),
bonus double(7,2),
branch varchar (5)
);
insert into staff values
(1001,'孙悟空','2000-12-17','8000.00',NULL,"教研部"),
(1002,'卢俊义','2001-02-20','16000.00','3000.00',"学工部"),
(1003,'林冲','2001-02-22','12500.00','5000.00',"学工部"),
(1004,'唐僧','2001-04-02','29750.00',NULL,"教研部"),
(1005,'李逵','2001-09-28','12500.00','14000.00',"学工部"),
(1006,'宋江','2001-05-01','28500.00',NULL,"学工部"),
(1007,'刘备','2001-09-01','24500.00',NULL,"教研部"),
(1008,'猪八戒','2007-04-19','30000.00',NULL,"咨询部"),
(1009,'罗贯中','2001-11-17','50000.00',NULL,"学工部"),
(1010,'吴用','2001-09-08','15000.00','0.00',"教研部"),
(1011,'沙僧','2007-05-23','11000.00',NULL,"咨询部"),
(1012,'李逵','2001-12-03','9500.00',NULL,"教研部"),
(1013,'小白龙','2001-12-03','30000.00',NULL,"咨询部"),
(1014,'关羽','2002-01-23','13000.00',NULL,"咨询部");
-- 删除入职在2005年以后入职的员工
delete from staff where hiredate>'2004-12-31';
-- 给薪水>20000块的员工,降薪2000块
update staff set salary=salary-2000 where salary >20000;
-- 请按照薪水升序排序查询所有员工的信息
select *from staff order by salary;
-- 请查询所有员工的平均薪水
select avg(salary)from staff ;
-- 请将奖金为null的员工信息查询出来
select * from staff where bonus is null