[学习交流] 数据宝典

-- 创建数据库
CREATE DATABASE cad;
-- 查询数据库
SHOW DATABASES;
-- 创建数据库，判断不存在，再创建
CREATE DATABASE IF NOT EXISTS cad2;
-- 查询某个数据库的字符集:查询某个数据库的创建语句
SHOW CREATE DATABASE cad2
-- 创建数据库，并指定字符集
CREATE DATABASE cad3 CHARACTER SET gbk;
-- 创建cad4数据库，判断是否存在，并制定字符集为utf8
CREATE DATABASE IF NOT EXISTS cad4 CHARACTER SET utf8;
-- 修改数据库的字符集
ALTER DATABASE cad3 CHARACTER SET utf8;
-- 删除数据库
DROP DATABASE cad3;
-- 判断数据库存在，存在再删除
DROP DATABASE IF EXISTS cad2;
DROP DATABASE IF EXISTS cad4;
-- 查询当前正在使用的数据库名称
SELECT DATABASE();
-- 使用数据库
USE cad;

-- 创建学生成绩表
CREATE TABLE student_score(
   id INT,
   NAME VARCHAR(10),
   chinese DOUBLE(4,1),
   math DOUBLE(4,1),
   english DOUBLE(4,1)
);
/*
查询某个数据库中所有的表名称: show tables;
查询表结构: desc 表名;

1. 修改表名
alter table 表名 rename to 新的表名;
2. 修改表的字符集
alter table 表名 character set 字符集名称;
3. 添加一列
alter table 表名 add 列名数据类型;
4. 修改列名称类型
alter table 表名 change 列名新列名新数据类型;--->change可以改名,改类型
alter table 表名 modify 列名新数据类型;---> modify只能修改类型
5. 删除列
alter table 表名 drop 列名;
(Delete):删除
* drop table 表名;
* drop table  if exists 表名 ;
*/

-- 修改表名
ALTER TABLE student_score RENAME TO student;
-- 修改name的长度为15
ALTER TABLE student MODIFY NAME VARCHAR(15);
-- 将name改为sname,长度为10
ALTER TABLE student CHANGE NAME sname VARCHAR(10);
-- 添加一列"所属班级","aaa"
ALTER TABLE student ADD class VARCHAR(20);
ALTER TABLE student ADD aaa VARCHAR(20);
-- 删除aaa这一列
ALTER TABLE student DROP aaa;

/*
添加数据：
   语法：
* insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
   注意：
1. 列名和值要一一对应。
2. 如果表名后，不定义列名，则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);
3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
*/

-- 标准格式插入
INSERT INTO student (id,sname,chinese,math,english,class) VALUES (1,'食发鬼',99,65,98,'一班');
-- 省略格式插入
INSERT INTO student  VALUES (2,'小非农',74,85,98,'一班');
-- 批量格式插入
INSERT INTO student (id,sname,chinese,math,english,class) VALUES
(3,'进仓单',85,85,98,'一班'),
(4,'郭大侠',99,85,NULL,'二班'),
(5,'马云',74,85,95,'一班'),
(6,'马化腾',85,88,94,'二班'),
(7,'马景涛',97,87,98,'一班'),
(8,'柳岩',84,85,NULL,'一班'),
(9,'柳青',88,85,94,'二班'),
(10,'刘德华',86,85,79,'二班'),
(11,'马德',85,75,66,'一班'),
(12,'德玛西亚',65,58,78,'二班')
;

/*
删除数据：
* 语法：
* delete from 表名 [where 条件]
* 注意：
1. 如果不加条件，则删除表中所有记录。
2. 如果要删除所有记录
1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
2. TRUNCATE TABLE 表名; -- 推荐使用，效率更高先删除表，然后再创建一张一样的表。
3. 修改数据：
* 语法：
* update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

* 注意：
1. 如果不加任何条件，则会将表中所有记录全部修改。
*/
-- 删除数据
DELETE FROM student WHERE id=3;
-- 修改数据
UPDATE student SET class="无法地带" WHERE id = 2;
UPDATE student SET class="泰波尔斯" WHERE id = 1 || id = 4;
-- 查询表中的记录 select * from 表名;
SELECT * FROM student;

                     /*-------------DQL:查询表中记录-------------*/
/** select * from 表名;

1. 语法：
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定

2. 基础查询
1. 多个字段的查询
select 字段名1，字段名2... from 表名；
* 注意：
* 如果查询所有字段，则可以使用*来替代字段列表。
2. 去除重复：
* SELECT DISTINCT 字段名 FROM 表名;
3. 计算列
* 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
* ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
* 表达式1：哪个字段需要判断是否为null
* 如果该字段为null后的替换值。
4. 起别名：
* as：as也可以省略
*/


-- 查询表中的记录 select * from 表名;
SELECT * FROM student;
-- 查询 name
SELECT NAME FROM student;

-- 查询班级,去除重复
SELECT DISTINCT class FROM student;

-- 查询math和english成绩,并计算两个的总分
SELECT math,english,math+IFNULL(english,0) AS 总分 FROM student;

/*
条件查询
1. where子句后跟条件
2. 运算符
* > 、< 、<= 、>= 、= 、<>
* BETWEEN...AND  在A与B之间
* IN( 集合)
* LIKE：模糊查询
* 占位符：
* _:单个任意字符
* %：多个任意字符
* IS NULL
* and  或 &&
* or  或 ||
* not  或 !
*/

-- 查询math大于80分
SELECT * FROM student WHERE math > 80;
SELECT * FROM student WHERE math >= 80;
-- 查询math等于80分
SELECT * FROM student WHERE math = 80;
-- 查询math不等于80分
SELECT * FROM student WHERE math != 80;
SELECT * FROM student WHERE math <> 80;
-- 查询chinese大于等于95 小于等于75
SELECT * FROM student WHERE chinese >= 95 &&  age <=75;
SELECT * FROM student WHERE chinese >= 95 AND  age <= 75;
SELECT * FROM student WHERE chinese BETWEEN 95 AND 75;
-- 查询chinese99分，85分，86分的信息
SELECT * FROM student WHERE chinese = 99 OR chinese = 85 OR chinese = 86;
SELECT * FROM student WHERE chinese IN (99,85,86);
-- 查询英语成绩为null
SELECT * FROM student WHERE english = NULL; -- 不对的。null值不能使用 = （!=）判断
SELECT * FROM student WHERE english IS NULL;
-- 查询英语成绩不为null
SELECT * FROM student WHERE english  IS NOT NULL;
-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询姓名第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';
-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';

/*
排序查询
* 语法：order by 子句
* order by 排序字段1 排序方式1 ，  排序字段2 排序方式2...

* 排序方式：
* ASC：升序，默认的。
* DESC：降序。

* 注意：
* 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
*/
-- math按升序,english降序排序
SELECT * FROM student ORDER BY math ASC,english DESC;

/*
聚合函数：将一列数据作为一个整体，进行纵向的计算。
1. count：计算个数
1. 一般选择非空的列：主键
2. count(*)
2. max：计算最大值
3. min：计算最小值
4. sum：计算和
5. avg：计算平均值

* 注意：聚合函数的计算，排除null值。
解决方案：
1. 选择不包含非空的列进行计算
2. IFNULL函数
*/

-- 显示name的个数
SELECT COUNT(NAME) FROM student;
-- 计算math成绩最大值
SELECT MAX(math) FROM student;
-- 计算math成绩最小值
SELECT MIN(math) FROM student;
-- 计算math,english的和
SELECT SUM(math),SUM(IFNULL(english,0)) AS 英语总分 FROM student;
-- 计算math大于80分的平均分
SELECT AVG(math) FROM student WHERE math > 80;
/*
分组查询:
1. 语法：group by 分组字段；
2. 注意：
1. 分组之后查询的字段：分组字段、聚合函数
2. where 和 having 的区别？
1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。
*/

-- 按照性年级分组。分别查询学生的数学平均分
SELECT class , AVG(math) FROM student GROUP BY class;
-- 按照性年级分组。分别查询学生的数学平均分,人数
SELECT class , AVG(math),COUNT(id) FROM student GROUP BY class;
--  按照性年级分组。分别查询学生的数学平均分,人数要求：分数低于80分的人，不参与分组
SELECT class , AVG(math),COUNT(id) FROM student WHERE math > 80 GROUP BY class;
--  按照性年级分组。分别查询学生的数学平均分,人数要求：分数低于80分的人，不参与分组,分组之后。人数要大于2个人
SELECT class , AVG(math),COUNT(id) FROM student WHERE math > 80 GROUP BY class HAVING COUNT(id) > 2;
SELECT class , AVG(math),COUNT(id) 人数 FROM student WHERE math > 80 GROUP BY class HAVING 人数 > 2;

/*
分页查询
1. 语法：limit 开始的索引,每页查询的条数;
2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
-- 每页显示3条记录

SELECT * FROM student LIMIT 0,3; -- 第1页

SELECT * FROM student LIMIT 3,3; -- 第2页

SELECT * FROM student LIMIT 6,3; -- 第3页

3. limit 是一个MySQL"方言"
*/
-- 第1页
SELECT * FROM student LIMIT 0,3;

/*
非空约束：not null，某一列的值不能为null
1. 创建表时添加约束
CREATE TABLE stu(
字段名字段类型,
字段名字段类型 NOT NULL -- name为非空
);
2. 创建表完后，添加非空约束
ALTER TABLE 表名 MODIFY 字段名字段类型 NOT NULL;

3. 删除name的非空约束
ALTER TABLE 表名 MODIFY 字段名字段类型;
*/

-- 修改表将sname和class改为非空约束
ALTER TABLE student MODIFY sname VARCHAR(10) NOT NULL;
ALTER TABLE student MODIFY class VARCHAR(10) NOT NULL;
-- 修改表将class非空约束去掉
ALTER TABLE student MODIFY class VARCHAR(10);

/*
唯一约束：unique，某一列的值不能重复
1. 注意：
* 唯一约束可以有NULL值，但是只能有一条记录为null
2. 在创建表时，添加唯一约束
CREATE TABLE stu(
字段名字段类型,
字段名字段类型 UNIQUE
);
3. 删除唯一约束
ALTER TABLE 表名 DROP INDEX 字段名;
4. 在表创建完后，添加唯一约束
ALTER TABLE 表名 MODIFY 字段名字段类型 UNIQUE;
*/

-- 修改id,添加唯一约束
ALTER TABLE student MODIFY id INT UNIQUE;
-- 移除id唯一约束
ALTER TABLE student DROP INDEX id;

/*
主键约束：primary key。
1. 注意：
1. 含义：非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识

2. 在创建表时，添加主键约束
create table stu(
字段名字段类型 primary key,-- 给id添加主键约束
字段名字段类型
);

3. 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

4. 创建完表后，添加主键
ALTER TABLE 表名 MODIFY 字段名字段类型 PRIMARY KEY;
自动增长：
1.  概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

2. 在创建表时，添加主键约束，并且完成主键自增长
create table stu(
字段字段类型 primary key auto_increment,-- 给字段添加主键约束
字段字段类型
);

3. 删除自动增长
ALTER TABLE 表名 MODIFY 字段字段类型;
4. 添加自动增长
ALTER TABLE 表名 MODIFY 字段字段类型 AUTO_INCREMENT;
*/

-- 添加id为主键
ALTER TABLE student MODIFY id INT PRIMARY KEY;

-- 删除主键
ALTER TABLE student DROP PRIMARY KEY;

-- 给主键添加自动增长
ALTER TABLE student MODIFY id INT PRIMARY KEY AUTO_INCREMENT;

-- 删除自动增长
ALTER TABLE student MODIFY id INT;

SELECT * FROM student;

/*
外键约束：foreign key,让表于表产生关系，从而保证数据的正确性。
1. 在创建表时，可以添加外键
* 语法：
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3. 创建表之后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

4. 级联操作
1. 添加级联操作
语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
2. 分类：
1. 级联更新：ON UPDATE CASCADE
2. 级联删除：ON DELETE CASCADE
*/

CREATE TABLE classroom(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20)
);

CREATE TABLE student2(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
cid INT, -- 希望cid作为外键关联classroom的cid,用到外键约束
CONSTRAINT stu_class_fk FOREIGN KEY(cid) REFERENCES classroom(cid)
);
-- 插入数据,先插主表,后插从表
INSERT INTO classroom VALUES(NULL,'801');
INSERT INTO classroom VALUES(NULL,'802');

INSERT INTO student2 VALUES(NULL,'杜傲德',1);
INSERT INTO student2 VALUES(NULL,'贾倩稳',1);
INSERT INTO student2 VALUES(NULL,'刘子豪',2);
INSERT INTO student2 VALUES(NULL,'赵放',2);

-- 删除外键约束
ALTER TABLE student2 DROP FOREIGN KEY stu_class_fk;
-- 添加外键约束
ALTER TABLE student2 ADD FOREIGN KEY(cid) REFERENCES classroom(cid);
SELECT * FROM classroom;
SELECT * FROM student2;

帐号		自动登录	找回密码
密码			加入黑马

[学习交流] 数据宝典

0 个回复