黑马程序员技术交流社区

标题: 数据宝典 [打印本页]

---

作者: 退换货的通话    时间: 2019-7-14 14:51
标题: 数据宝典
-- 创建数据库
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;

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2