MySQL数据库基础篇

目录

SQL的分类

数据定义语言（DDL）---Data Definition Language

数据操作语言(DML) ---Data Manipulation Language

 数据查询语言(DQL) ---Data Query Language

数据控制语言(DCL) ---Data Control Language

事务控制语言(TCL) --- Transaction Control Language

索引管理

筛选和排序数据

数据库操作

表操作

查询表信息

小练习

---

SQL的分类

数据定义语言（DDL）---Data Definition Language

DDL用于定义和操作数据库结构，常见操作有：

• Create：创建数据库、表、视图、索引等：

create database database_name;-- 创建数据库
create table table_name(....);-- 创建表
create index index_name on table_name(....)；-- 创建索引

• Alter：修改数据库对象的结构：

alter table table_name ADD column_name column_definition;-- 添加新列
alter table table_name modify column_name new_column_definition;-- 修改列属性

• Drop：删除数据库对象：

drop database database_name;-- 删除数据库
drop table table_name;-- 删除表

• truncate：删除表数据，不删除表本身(没有记录，速度快，无法回滚)：

truncate table table_name;-- 删除表数据

数据操作语言(DML) ---Data Manipulation Language

DML用于对数据库中的数据进行插入、更新，删除等：

• insert ：向表中插入数据：

insert into table_name(column_name1,column_name2) vlaues (....);-- 向表中插入数据

• update：更新表中的数据：

uodate table_name set column_name1 = value1,column_name2 = value2 where condition; --更新表数据

• delete：删除表中数据：

delete from table_name where condition;-- 删除符合条件的数据

• replace：用新的数据替换已有数据(效果类似updata，实际执行删除和添加两步操作)：

replace into table_name (column_name1,column_name2) values(value1，value2);替换数据

 数据查询语言(DQL) ---Data Query Language

DQL用于从数据库查询数据：

• select：查询数据

select column_name1,column_name2 from table_name where condition;-- 查询字段
select * from table_name;-- 查询表中所有数据
select distinct column_name from table_name;查询字段并去重

数据控制语言(DCL) ---Data Control Language

DCL用于数据库权限管理：

• crant：给用户添加权限

grant all privileges on data_base to 'user_name'@'host'-- 给用户添加权限

• revoke：删除用户权限

revoke all privileges on database_name from 'user_name'@'host'-- 撤销用户权限

事务控制语言(TCL) --- Transaction Control Language

TCL用于管理数据库事务，确保数据库操作的一致性和完整性：

• commit：提交事务，保存对于数据库的更改。

commit;-- 提交事务，保存更改

• rollback：回滚事务，撤销上次commit提交的操作。

rollback;-- 撤销commit操作

• savepoint：设置保存点，用于事务中的部分回滚。

savepoint savepoint_name;设置保存点

• set truncation：设置事务的隔离级别。

set truncation isolation level serializable;-- 设置事务的隔离等级

索引管理

索引是为了加速数据查询而创建的数据结构。常见的数据结构操作包括：

• create index：创建索引以优化查询

create index index_name on table_name(column_name1,column_name2);-- 创建索引

• drop index：删除索引

drop index index_name on table_name;-- 删除索引

筛选和排序数据

• where：用于过滤查询结果

select * from table_name where cloumn1 = value;

• order by：排序查询结果

select * from table_name order by column_name desc;-- desc降序

• group by：按某些列分组

select column1 ，count(column_name2) from table_name group by column;

• having:对分组数据进行筛选

select column1,count(colum_name1) from table_name group by column_name1 having count(*) > 2;

• join :连接多个表的数据

select * from table_name1 inner join table_name2 on table1.id = table2.id;

数据库操作

• 创建数据库

create database database_name;-- 创建数据库

• 删除数据库

drop database database_name;-- 删除数据库

• 查看所有数据库

show databases；-- 查看所有数据库

• 查看当前数据库

select database();-- 查看当前数据库

• 修改数据库名

alter database database_name modify name = new_database_name;-- 修改数据库名

• 修改数据库使用的编码数据集

alter database database_name character set utf8;-- 修改编码格式

• 使用数据库

use database_name;-- 使用数据库

表操作

• 创建表

create table table_name (id int primary key auto_increment，-- 主键约束+自动递增s_id int not null unique,-- 唯一约束+不允许为空age int check(age > 18),-- 检查约束，数据必须满足条件password varchar(20) not null ，sex char(2) check(sex in ('f','m')),#sex enum('m','f')number int default 18-- 默认约束
);

• 删除表

drop table table_name;-- 删除表及表中数据
truncate table table_name;-- 删除表数据，保留表结构 

• 添加字段

alter table table_name add column_name column_type;-- 添加列

• 删除字段

alter table table_name drop column column_name;-- 删除字段

• 修改字段数据类型

alter table tanle_name modify column_name new_column_type;-- 有数据必须能转换，否则会失败

• 添加数据

insert into table_name(column_name1,column_name2) values(....);-- 向表中对应字段添加值

• 删除列数据

update table_name set column_name = null;-- 该列数据全部为null

• 替换表中部分数据

update table_name set column_name new_column_value where condition;-- 没有约束条件更新所有行

• 修改字段名

alter table table_name change column_name new_column_name column_type;-- 修改字段名，不修改字段类型和约束也必须指定
alter table table_name rename column column_name to new_column_name;-- mysql-8++以上版本 

• 添加约束

alter table table_name add constraint 约束名 约束类型(列名);-- 没有约束才能添加
alter table table_name add primary key(id);-- 添加主键约束
alter table table_name add constraint 约束名 unique (column_name);-- 添加唯一约束
alter table table_name add constraint fk_department foreign key (deportment_id) references departments(id);-- 添加外键约束
alter table table_name add constraint chk_age check (age >= 18);-- 添加检查约束

• 删除约束

alter table table_name drop primary key;-- 删除主键约束
alter table table_name drop index 约束名;-- 删除唯一约束
alter table table_name drop foreign key 约束名; -- 删除外键约束
alter table table_name drop check 约束名;-- 删除检查约束

• 查看表结构

desc table_name;-- 查看表结构

• 查看创表语句

show create table table_name;-- 查看创表语句

查询表信息

•  查询所有信息

select * from table_name;-- 查询表中所有信息

• 查询指定字段信息

select column_name1,column_name2 from table_name;-- 查询指定字段信息

• 查询符合条件的信息

select column_name from table_name where condition;-- 查询符合条件的信息
select column_name from table_name where condition1  and condition ;-- 范围查询
select column_name from table_name where clumn_name between x and y;-- 闭区间[x,y]
select column_name from table_name where clumn_name in ('x','y'); -- 枚举类型
select column_name from table_name where condition1 or condition2;-- 或操作
select column_name from table_name where not condition;-- 取反操作 
select column_name from table_name where column_name is null;-- 等号不能判断null
select column_name from table_name where column_name regexp '正则表达式';-- 使用正则表达式匹配

• 模糊查询

select column_name from table_name where column_name like '%_';-- %代表所有，_代表任意单字符

• 对查询结果分组

select column_name1 from table_name group by column_name;-- 分组显示 
select column_name1 from table_name group by column_name having count(column_name) >x;-- 筛选分组结果，该操作在分组后

• 对查询结果排序

select column_name from table_name order by column_name asc;-- 以该字段升序排序，默认升序
select column_name from table_name order by column_name desc;-- 降序排序

• 对查询结果分页显示

select column_name from table_name limit x;-- 显示x条结果
select column_name from table_name limit x，y;--  从第x+1条显示，显示y条结果

• 别名的使用

select column_name,TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age from table_name where age > 18 and age < 30;-- 这个函数会返回当前时间与第二个参数日期之差结果类型由第一个参数决定

• 多表查询

select table_name1.column_name1,table_name2.column_name2 from table_name1 inner join table_name2 on table_name1.column_name = table_name2.column_name;-- 返回符合条件的查询字段，会返回虚拟表(结果集)
select table_name1.column_name1,table_name2.column_name2 from table_name1 left join table_name2 on table_name1.column_name = table_name2.column_name;-- 上面的连接不会返回不符合条件的结果，如果想要显示左表全部信息，就得使用左右连接，(会返回左表全部行，右表符合条件行)，右表不匹配的行，列将填充null
#返回两张表全部行，不符合匹配条件的列填充null，full join 全连接会对两张表执行该操作，MySQL不支持全连接
select table_name.column_name from table_name as x left join table_name as y on x.领导号 = y.id号; -- 通过别名来引用同一张表，我的领导id就是你的主键id

小练习

/*
一、单表查询
素材： 表名：worker-- 表中字段均为中文，比如 部门号 工资 职工号 参加工作 等
CREATE TABLE `worker` (`部门号` int(11) NOT NULL,`职工号` int(11) NOT NULL,`工作时间` date NOT NULL,`工资` float(8,2) NOT NULL,`政治面貌` varchar(10) NOT NULL DEFAULT '群众',`姓名` varchar(20) NOT NULL,`出生日期` date NOT NULL,PRIMARY KEY (`职工号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');
*/
#1、显示所有职工的基本信息。
SELECT 部门号,职工号,工作时间,工资,政治面貌,姓名,出生日期 FROM db_ck.worker;
#2、查询所有职工所属部门的部门号，不显示重复的部门号。 
SELECT DISTINCT 部门号 FROM db_ck.worker; 
#3、求出所有职工的人数。
SELECT count(职工号) FROM db_ck.worker;  
#4、列出最高工和最低工资。 
SELECT max(工资),min(工资) FROM db_ck.worker;  
#5、列出职工的平均工资和总工资。   
SELECT avg(工资),sum(工资) from db_ck.worker;
#6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
CREATE TABLE '工作日期表'(`职工号` int(11) NOT NULL,`姓名` varchar(20) NOT NULL,`参加工作` DATA NOT NULL
); 
#7、显示所有女职工的年龄。
SELECT TIMESTAMPDIFF(YEAR,db_ck.worker.出生日期，CURDATE()) as age FROM db_ck.worker;-- 这个函数计算差值，参数1是结果的显示方式，参数二是被减数，参数三是获取当前日期 
#8、列出所有姓刘的职工的职工号、姓名和出生日期。
SELECT 职工号,姓名,出生日期 FROM db_ck.worker WHERE 姓名 LIKE '刘%';
#9、列出1960年以前出生的职工的姓名、参加工作日期。
SELECT 姓名, 工作时间 AS start_day FROM db_ck.worker WHERE 出生日期 < '1960-01-01';
#10、列出工资在1000－2000之间的所有职工姓名。
SELECT 姓名 FROM db_ck.worker WHERE 工资 > 1000 AND 工资 < 2000;
#11、列出所有陈姓和李姓的职工姓名。
SELECT 姓名 FROM db_ck.worker WHERE 姓名 LIKE '陈%' OR 姓名 LIKE '李%';
#12、列出所有部门号为2和3的职工号、姓名、党员否。
SELECT 职工号,姓名,政治面貌 FROM db_ck.worker WHERE 部门号 = 2 OR 部门号 = 3;  
#13、将职工表worker中的职工按出生的先后顺序排序。
SELECT 部门号,职工号,工作时间,工资,政治面貌,姓名,出生日期 FROM db_ck.worker ORDER BY 出生日期 DESC;
#14、显示工资最高的前3名职工的职工号和姓名。
SELECT 职工号,姓名,工资 FROM db_ck.worker ORDER BY 工资 DESC LIMIT 0,3;
#15、求出各部门党员的人数。
SELECT 部门号, COUNT(政治面貌) AS 党员人数 FROM db_ck.worker WHERE 政治面貌 = '党员' GROUP BY 部门号;  
#16、统计各部门的工资和平均工资
SELECT 部门号,SUM(工资) AS 总工资,AVG(工资) AS 人均工资 FROM db_ck.worker GROUP BY 部门号;
#17、列出总人数大于4的部门号和总人数。
SELECT 部门号,COUNT(职工号) AS 总人数 from db_ck.worker GROUP BY 部门号 HAVING COUNT(职工号) > 4;

/*
二、多表查询
1.创建student和score表
CREATE  TABLE student (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY ,
name  VARCHAR(20)  NOT NULL ,
sex  VARCHAR(4) ,
birth  YEAR,
department  VARCHAR(20) ,
address  VARCHAR(50)
);
创建score表。SQL代码如下：
CREATE  TABLE score (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
stu_id  INT(10)  NOT NULL ,
c_name  VARCHAR(20) ,
grade  INT(10)
);
2.为student表和score表增加记录
向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);
*/-- 3.查询student表的所有记录
SELECT id,`name`,sex,birth,department,address FROM db_ck.student;
-- 4.查询student表的第2条到4条记录
SELECT id,`name`,sex,birth,department,address FROM db_ck.student LIMIT 2,2;
-- 5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
SELECT id,`name`,department FROM db_ck.student;
-- 6.从student表中查询计算机系和英语系的学生的信息
SELECT id,`name`,sex,birth,department,address FROM db_ck.student WHERE department = '计算机系' OR department = '英语系';
-- 7.从student表中查询年龄18~22岁的学生信息
SELECT id,`name`,sex,birth,department,address,TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM db_ck.student WHERE age > 18 AND age < 22 ;
-- 8.从student表中查询每个院系有多少人
SELECT department,COUNT(department) as number FROM db_ck.student GROUP BY department;
-- 9.从score表中查询每个科目的最高分
SELECT c_name,MAX(grade) FROM db_ck.score GROUP BY c_name;
-- 10.查询李四的考试科目（c_name）和考试成绩（grade）
SELECT c_name,grade FROM db_ck.student JOIN db_ck.score ON db_ck.student.id = db_ck.score.stu_id WHERE db_ck.student.`name` = '李四'; 
-- 11.用连接的方式查询所有学生的信息和考试信息
SELECT id,`name`,sex,birth,department,address,db_ck.score.grade FROM db_ck.student LEFT JOIN db_ck.score ON db_ck.student.id = db_ck.score.stu_id;
-- 12.计算每个学生的总成绩
SELECT id,`name`,sex,birth,department,address,sum(db_ck.score.grade) AS s_grade FROM db_ck.student LEFT JOIN db_ck.score ON db_ck.student.id = db_ck.score.stu_id GROUP BY db_ck.student.id;
-- 13.计算每个考试科目的平均成绩
SELECT c_name,avg(grade) FROM db_ck.score GROUP BY c_name;
-- 14.查询计算机成绩低于95的学生信息
SELECT id,`name`,sex,birth,department,address,db_ck.score.c_name,db_ck.score.grade FROM db_ck.student,db_ck.score WHERE db_ck.score.c_name = '计算机' and db_ck.score.grade < 95;
-- 15.查询同时参加计算机和英语考试的学生的信息
SELECT id,`name`,sex,birth,department,address,db_ck.score.c_name,db_ck.score.grade FROM db_ck.student JOIN db_ck.score ON db_ck.score.stu_id = db_ck.student.id WHERE db_ck.score.c_name IN ('计算机','英语') GROUP BY db_ck.student.id HAVING count(DISTINCT db_ck.score.c_name) = 2; -- 好巧妙，聚合后判断字段次数，等于2就是命中目标
-- 16.将计算机考试成绩按从高到低进行排序
SELECT c_name,grade FROM db_ck.score WHERE c_name = '计算机' ORDER BY grade desc;
-- 17.从student表和score表中查询出学生的学号，然后合并查询结果
SELECT db_ck.student.id FROM db_ck.student,db_ck.score WHERE db_ck.student.id = db_ck.score.stu_id;
-- 18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SELECT `name`,department,c_name,grade FROM db_ck.student,db_ck.score WHERE db_ck.student.`name` LIKE '张%' OR db_ck.student.`name` LIKE '王%';
-- 19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
SELECT `name`,TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age,deportment,c_name,grade FROM db_ck.student JOIN db_ck.score ON db_ck.score.stu_id = db_ck.student.id WHERE db_ck.student.address LIKE '湖南%';