黑马程序员技术交流社区

标题: 【成都校区】MySQL [打印本页]

作者: Boooom    时间: 2018-12-7 13:48
标题: 【成都校区】MySQL
MySQL
        数据库的基本概念
                1. 英文:DataBase    简称:DB
                2. 什么是数据库?
        用于存储和管理数据的仓库
                3. 数据库的特点:
        1. 持久化存储数据的。其实数据库就是一个文件系统
        2. 方便存储和管理数据
        3. 使用了统一的方式操作数据库-->SQL
                4. 常用的数据库软件:
        1. Oracle:收费的大型数据库
        2. MySQL:开源免费的数据库,小型的数据库,已经被Oracle收购
        3. Microsoft SQLServer:MicroSoft公司收费的中型的数据库。C#、net等语言常使用。
        4. DB2:IBM公司的数据库产品,收费的,常应用在银行系统中
                5. MySQL目录结构
        1. MySQL安装目录:basedir="D:/develop/MySQL/"
                * 配置文件:my.ini
        2.  MySQL数据目录:datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
                * 数据库:文件夹
                * 表:文件
                * 数据:数据
        MySQL使用
                1. MySQL启动
        1. 手动:管理-->服务-->MySQL
        2. cmd-->services.msc 打开服务的窗口
        3. 使用管理员打开cmd
                net start mysql:启动mysql的服务
                net stop mysql:关闭mysql服务
                2. MySQL登陆(cmd)
        1. mysql -uroot -p密码
        2. mysql -hip -uroot -p连接目标的密码
                (登陆本机的MySQL:mysql -h127.0.0.1 -uroot -proot)
        3. mysql --host=ip --user=root --password=连接目标的密码
                3. MySQL退出(cmd)
        1. exit
        2. quit
        SQL
                1. 什么是SQL?
        Structured Query Language:结构化查询语言
        其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。
                2. SQL通用语法
        1. SQL 语句可以单行或多行书写,以分号结尾。
                (show databases;显示数据库列表)
        2. 可以使用空格和缩进来增强语句的可读性
        3. MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写。
        4. 3 种注释:
                * 单行注释:-- 注释内容 (或 # 注释内容[MySQL特有])
                * 多行注释:/* 注释内容 */
                3. SQL 分类
        1. DDL(Data Definition Language)数据定义语言
                用来定义(创建)数据库对象:数据库、表、列等。关键字:create、drop、alter 等
        2. DML(Data Manipulation Language)数据操作语言
                用来对数据库中的数据进行增删改。关键字:insert、delete、update 等
        3. DQL(Data Query Language)数据查询语言
                用来查询数据库中表的记录(数据)。关键字:select、where 等
        4. DCL(Data Control Language)数据控制语言(了解)
                用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT、REVOKE 等
                        1.  DDL(操作数据库、表)
                                1. 操作数据库:CRUD
                                        1. C(Create):创建
        1. 创建数据库:
                * CREATE database 数据库名称;
        2. 创建数据库,判断不存在,再创建:
                * create database if not exists 数据库名称;
        3. 创建数据库,并指定字符集
                * create database 数据库名称 character set 字符集名;
                                        2. R(Retrieve):查询
        1.  查询所有的数据库的名称:
                * show databases;
        2. 查询某个数据库的字符集:查询某个数据库的创建语句
                * show create database 数据库名称;
                                        3. U(Update):修改
        1. 修改数据库的字符集
                * alter database 数据库名称 character set 字符集名称;
                                        4. D(delete):删除
        1. 删除数据库
                * drop database 数据库名称;
        2. 判断数据库存在,存在再删除
                * drop database if exists 数据库名称;
                                        5. 使用数据库
        1. 查询当前正在使用的数据库名称
                * select database();
        2. 使用数据库
                * use 数据库名称;
                                2. 操作表
                                        1. C(Create):创建
        1. 语法:
                * create table 表名 (
                        列名1 数据类型1,
                        列名2 数据类型2,
                        ...
                        列名n 数据类型n );
        2. 复制表:
                * create table 表名 like 被复制的表名;
                                                #注意:最后一列,不需要加逗号(,)
#数据类型:
        1. int:整数类型
                * age int,
        2. double:小数类型
                * score double(5,2)
        3. data:日期,只包含年月日,yyyy-MM-dd
        4. datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss
        5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
                #如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间 来自动赋值
        6. varchar:字符串
                * name varchar(20):姓名最大20个字符
                * zhangsan --> 8个字符 / 张三 --> 2个字符
                                                * 创建表:
        create table student (
                id int,
                name varchar(32),
                age int,
                score double(4,1),
                birthday date,
                insert_time timestamp );
                                        2. R(Retrieve):查询
        1. 查询某个数据库中所有的表名称
                * show tables;
        2. 查询表结构
                * desc 表名;
                                        3. U(Update):修改
        1. 修改表名
                * alter table 表名 rename to 新的表名;
        2. 修改表的字符集
                * alter table 表名 character set 字符集名称;
        3. 表中添加一列
                * alter table 表名 add 列名 数据类型;
        4. 修改列名称、类型
                * alter table 表名 change 列名 新列名 新数据类型;
                * alter table 表名 change 列名 新数据类型;
        5. 删除列
                * alter table 表名 drop 列名;
                                        4. D(Delete):删除
        1. 删除表
                * drop table 表名;
        2. 先判断表是否存在,存在再删除
                * drop table if exists 表名;
                        2. DML :增删改表中数据
                                1. 添加数据:
        1. 语法:
                * insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
        # 注意:
                1. 列名和值要一一对应。
                2. 如果表名后,不定义列名,则默认给所有列添加值
                        * insert into 表名 values(值1,值2,...值n);
                3. 除了数字类型,其他类型需要使用引号(单双都可以)引起来
                                2. 删除数据:
        * 语法:
                * delete from 表名 [where 条件]
        # 注意:
                1. 如果不加条件,则删除表中所有记录。
                2. 如果要删除所有记录
                        1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
                        2. TRUNCATE TABLE 表名; -- 推荐使用,效率更高 先删除表,然后再创建一张一样的表。
                                3. 修改数据:
        1. 语法:
                * update 表名 set 列名1 = 值1,列名2 = 值2,...[where 条件];
        # 注意:
                如果不加任何条件,则会将表中所有记录全部修改。
                        3. DQL :查询表中的记录
                                * select * from 表名;
* select 列名1,列名2 from 表名;
                                1. 语法:
        select
                字段列表
        from
                表名列表
        where
                条件列表
        group by
                分组字段
        having
                分组之后的条件
        order by
                排序
        limit
                分页限定
                                2. 基础查询
        1. 多个字段的查询
                *select 字段名1,字段名2... from 表名;
                # 注意:
                        * 如果查询所有字段,则可以使用*来替代字段列表。
        2. 去除重复:
                * distinct
        3. 计算列
                * 一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
                * ifnull(表达式1,表达式2):null参与的运算,计算结果都为null
                        * 表达式1:哪个字段需要判断是否为null
                        * 如果该字段为null后的替换值。
        4. 起别名:
                * as:as也可以省略
                                3. 条件查询
        1. where子句后跟条件
        2. 运算符
                * > 、< 、<= 、>= 、= 、<>
                * BETWEEN...AND  
                * IN( 集合)
                * LIKE:模糊查询
                        * 占位符:
                                * _  :单个任意字符
                                * % :多个任意字符
                * IS NULL  
                * and  或  &&
                *  or    或   ||
                * not   或   !
                                        -- 查询年龄大于20岁
        SELECT * FROM student WHERE age > 20;               
        SELECT * FROM student WHERE age >= 20;               
-- 查询年龄等于20岁
        SELECT * FROM student WHERE age = 20;               
-- 查询年龄不等于20岁
        SELECT * FROM student WHERE age != 20;
        SELECT * FROM student WHERE age <> 20;               
-- 查询年龄大于等于20 小于等于30                       
        SELECT * FROM student WHERE age >= 20 &&  age <=30;
        SELECT * FROM student WHERE age BETWEEN 20 AND 30;       
-- 查询年龄22岁,18岁,25岁的信息
        SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
        SELECT * FROM student WHERE age IN (22,18,25);               
-- 查询英语成绩为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 '%德%';
                                1. 排序查询
        1. 语法:order by 子句
                * order by 排序字段1 排序方式1,排序字段2 排序方式2 ...;
        2. 排序方式:
                * ASD   :升序,默认的。
                * DESC :降序。
# 注意:
        如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
                                2. 聚合函数:将一列数据作为一个整体,进行纵向的计算。
        1. count:计算个数
                1. 一般选择非空的列:主键
                2. count(*)
        2. max:计算最大值
        3. min:计算最小值
        4. sum:计算和
        5. avg :计算平均值
# 注意:聚合函数的计算,不排除null值。
        解决方案:
                1. 选择不包含null值的列进行计算
                2. IFNULL函数
                                3. 分组查询:
        1. 语法:group by 分组字段;
# 注意:
        1. 分组之后查询的字段:分组的字段,聚合函数的字段
        2. where 和 having 的区别?
                1. where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足条件,则不会被查询出来。
                2. where后不可以跟聚合函数,having可以进行聚合函数的判断。
                                        -- 按照性别分组。分别查询男、女同学的平均分
        SELECT sex , AVG(math) FROM student GROUP BY sex;
                       
-- 按照性别分组。分别查询男、女同学的平均分,人数
        SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
                       
--  按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组
        SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
                       
--  按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组,分组之后。人数要大于2个人
        SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
        SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
                                4. 分页查询
        1. 语法:limit 开始的索引,每页查询的条数;
        2. 公式:开始的索引 =(当前的页码-1)* 每页显示的条数
-- 每页显示3条记录
        SELECT * FROM 表名 LIMIT 0,3;--> 第1页
        SELECT * FROM 表名 LIMIT 3,3;--> 第2页
# 注意: limit 是一个 MySQL“方言”
                                4. 约束
        1.概念:对表中的数据进行限定,保证数据的正确性、有效性 和 完整性
        2. 分类:
                * 主键约束:primary key
                * 非空约束:not null
                * 唯一约束:unique
                * 外键约束:foreign key
                                        1. 非空约束:not null,某一列的值不能为null
        1. 创建表时添加约束
                * CREATE TABLE 表名(
                列名1 数据类型1,
                列名2 数据类型2 NOT NULL ,
                ...
                );
        2. 表创建完成后,添加非空约束
                * ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
        3. 删除某一列的非空约束
                * ALTER TABLE 表名 MODIFY 列名 数据类型;
                                        2. 唯一约束:unique,某一列的值不能重复
        1. 在创建表时,添加唯一约束
                * CREATE TABLE 表名(
                列名1 数据类型1,
                列名2 数据类型2 UNIQUE,
                ...
                );
        2. 删除唯一约束
                * ALTER TABLE 表名 DROP INDEX 列名;
        3. 在表创建完成后,添加唯一约束
                * ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
# 注意:唯一约束可以有NULL值,但是只能有一条记录为NULL
                                        3. 主键约束:primary key。
        # 注意:
                1. 含义:非空且唯一
                2. 一张表只能有一个字段为主键
                3. 主键就是表中记录的唯一标识

        2. 在创建表时,添加主键约束
                create table stu(
                        id int primary key,-- 给id添加主键约束
                        name varchar(20)
                );
        3. 删除主键
                # 错误 alter table stu modify id int ;
                * ALTER TABLE stu DROP PRIMARY KEY;

        4. 创建完表后,添加主键
                * ALTER TABLE stu MODIFY id INT PRIMARY KEY;

        5. 自动增长:
                1.  概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
                2. 在创建表时,添加主键约束,并且完成主键自增长
                        create table stu(
                                id int primary key auto_increment,-- 给id添加主键约束
                                name varchar(20)
                        );
                3. 删除自动增长
                        * ALTER TABLE stu MODIFY id INT;
                4. 添加自动增长
                        * ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
                                        4. 外键约束: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
                                5. 多表查询
        1. 多表查询的作用:
                比如:我们想查询孙悟空的名字和他所在的部门的名字,则需要使用多表查询。
                 如果一条 SQL 语句查询多张表,因为查询结果在多张不同的表中。每张表取 1 列或多列。
        2. 查询语法:
                select
                        列名列表
                from
                        表名列表
                where....
        3. 笛卡儿积A,B
        --> SELECT * FROM A表,B表;

                                        1. 内连接查询:
        1. 隐式内连接:使用where条件消除无用数据
                * 例子:
                -- 查询所有员工信息和对应的部门信息
                SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
                               
                -- 查询员工表的名称,性别。部门表的名称
                        SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;       
                        SELECT
                                t1.name, -- 员工表的姓名
                                t1.gender,-- 员工表的性别
                                t2.name -- 部门表的名称
                        FROM
                                emp t1,
                                dept t2
                        WHERE
                                        t1.`dept_id` = t2.`id`;

        2. 显式内连接:
                * 语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件
                * 例如:
                        * SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;       
                        * SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;       

        3. 内连接查询:
                1. 从哪些表中查询数据
                2. 条件是什么
                3. 查询哪些字段
                                        2. 外链接查询:
        1. 左外连接:
                * 语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;
                * 查询的是左表所有数据以及其交集部分。
                * 例子:
                -- 查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称
                        SELECT         t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
        2. 右外连接:
                * 语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;
                * 查询的是右表所有数据以及其交集部分。
                * 例子:
                        SELECT         * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
                                        3. 子查询:
        * 概念:查询中嵌套查询,称嵌套查询为子查询。
        -- 查询工资最高的员工信息
        -- 1 查询最高的工资是多少 9000
                SELECT MAX(salary) FROM emp;
                               
        -- 2 查询员工信息,并且工资等于9000的
                SELECT * FROM emp WHERE emp.`salary` = 9000;
                               
        -- 一条sql就完成这个操作。子查询
                SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
                                                1. 子查询的结果是单行单列的:
        * 子查询可以作为条件,使用运算符去判断。 运算符: > >= < <= =
        -- 查询员工工资小于平均工资的人
                SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
                                                2. 子查询的结果是多行单列的:
        * 子查询可以作为条件,使用运算符in来判断
        -- 查询'财务部'和'市场部'所有的员工信息
                SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
                SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
        -- 子查询
                SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
                                                3. 子查询的结果是多行多列的:
        * 子查询可以作为一张虚拟表参与查询
        -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
        -- 子查询
                SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
                WHERE t1.id = t2.dept_id;
                                       
        -- 普通内连接
                SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'






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