SQL数据库基本操作语句（张扬课堂笔记）

一、数据库概述
    1、什么是数据库
        存储数据的仓库，并且可以管理和维护。
    2、数据库的分类
        层次结构
        网状结构
        关系型结构
    3、常用关系型数据库
        Oracle（后面有课程学习，在企业中用的较多）
        DB2
        Informix
        Sybase
        SQL Server
        ProstgreSQL面向对象数据库
        MySQL（我们主要学习它，在企业中用的较多）
        Access
        SQLite等

二、SQL简介
    1、什么是SQL
        结构化查询语言  Structured  Query Language
        是一种定义和操作数据库的语法，绝大多数关系型数据库都支持。
    2、SQL的作用
        与数据库建立联系，进行沟通
    3、SQL的标准
        ANSI制定的一个：SQL-2003标准
    4、SQL的组成
        数据定义语言  DDL    ***
        数据操作语言  DML    *****
        数据查询语言  DQL    *****
        事务控制语言  TPL
        数据控制语言  DCL
        指针控制语言  CCL
三、MySQL的入门
    1、mysql的安装与配置
    2、数据库服务器，数据库和表的关系
    3、数据和数据库中表的关系
        javabean：由一些属性和get/set方法组成的原始java类。
           分为实体javabean和表单javabean。
           实体javabean：与数据库中表的字段一一对应。
            该javabean的一个对象表明是表的一条记录。
            这种javabean也称为：
                  do  data object
                  vo  value object
                  pojo  plain old java object
        javabean的5大特征：
            类都是公有的
            都有默认的构造函数
            属性都是私有的
            都提供公有get和set方法
            一般都实现序列化接口。（带着这个问题，听第9天的课程）


四、DDL数据定义语言                (练一遍就行）
    1、库操作（创建库，编辑库，删除库）
        练习：
            创建一个名称为mydb1的数据库。
            create database MYDB1;
            查看有多少数据库：
            show databases;
            查看数据库创建细节：
            show create database MYDB1;
            创建一个使用gbk字符集的mydb2数据库。
            create database MYDB2 character set gbk;
            创建一个使用gbk字符集，并带校对规则的mydb3数据库
                查看gbk字符集下有什么校对规则
                show collation like 'gbk%';
            create database MYDB3 character set gbk collate gbk_chinese_ci;
            删除前面创建的mydb3数据库
            drop database MYDB3;
            查看服务器中的数据库，并把mydb2的字符集修改为utf8;
            show databases;
            alter database MYDB2 character set utf8;

    2、表结构操作（创建表结构，编辑表结构和删除表结构）
        练习：
            创建一个员工表：
                字段        类型
                Id        整型
                name        字符型
                gender        字符型
                birthday    日期型
                Entry_date    日期型
                job        字符型
                Salary        浮点数型
                resume        大文本型
            create table EMPLOYEE(
                ID  int,
                NAME varchar(50),
                GENDER varchar(10),
                BIRTHDAY date,
                ENTRY_DATE  date,
                JOB    varchar(100),
                SALARY  float(7,2),
                RESUME  longtext
            );
            查看表详情：desc table_name;-----------//table_name指的是表的名称。
            desc EMPLOYEE;
            在上面员工表的基本上增加一个image列。
            alter table EMPLOYEE add IMAGE blob;
            修改job列，使其长度为60。
            alter table EMPLOYEE modify JOB varchar(60);
            删除image列。
            alter table EMPLOYEE drop IMAGE;
            表名改为user。
            alter table EMPLOYEE rename to USER;
            查看库中所有表。
            show tables;
            修改表的字符集为utf8。
            alter table USER character set utf8;
            列名name修改为username。
            alter table USER change NAME USERNAME varchar(50);
五、DML数据操作语言                （重要）
    1、插入数据操作    insert
        练习：
            使用insert语句向表中插入三个员工的信息。
            insert into USER values(1,'zs','nan','1999-10-01','2011-10-10','ceo',10000,'hello');--//插入的字段与表的字段必须一一对应，必须所有字段都有值。

            insert into USER(id,name,gender,birthday,entry_date,job,salary) values(4,'aaa','nan','1999-10-01','2011-10-10','ceo',10000);//--可以选择性插入
           
            insert into USER(id,username,gender,birthday,entry_date,job,salary,resume)values(3,'张三','nan','1999-10-01','2011-10-10','ceo',10000,'你好');
            在MySQL中一共有6个地方使用了字符集。可以通过命令查看：
                show variables like 'character%';
            修改客户端的字符集：
                set character_set_client=gbk;
            设置结果集用gbk编码：
                set character_set_results=gbk;

    2、更新数据操作  update
        练习：
            将所有员工薪水修改为5000元。
            update USER set SALARY=5000;
            将姓名为’zs’的员工薪水修改为3000元。
            update USER set SALARY=3000 where USERNAME = 'zs';
            将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。
            update USER set SALARY=4000,JOB='ccc' where USERNAME='aaa';
            将'张三'的薪水在原有基础上增加1000元。
            update USER set SALARY=SALARY+1000 where USERNAME='张三';
    3、删除数据操作  delete
        练习：
            删除表中名称为’zs’的记录。
            delete from USER where USERNAME='zs';
            删除表中所有记录。
            delete from USER;
            使用truncate删除表中记录。
            truncate table USER;
六、DQL数据查询语言                （重要）
    1、查询数据操作    select
        练习：
            查询表中所有学生的信息。
            select * from STUDENT;
            查询表中所有学生的姓名和对应的英语成绩。
            select NAME,ENGLISH from STUDENT;
            过滤表中重复数据。
            select distinct ENGLISH from STUDENT;
        练习：（使用表达式和列（表）别名）
            在所有学生数学分数上加10分特长分。
            select NAME as '学生姓名',MATH as '数学成绩',MATH+10 as '特长成绩' from STUDENT;
            统计每个学生的总分。
            select NAME as 学生姓名,CHINESE+MATH+ENGLISH 总分 from STUDENT;
            使用别名表示学生分数。
            select NAME 学生姓名,CHINESE '语文成绩',MATH as 数学成绩,ENGLISH as '英语成绩' from STUDENT;
        练习：（使用条件进行过滤查询）
            查询姓名为王五的学生成绩
            select * from STUDENT where NAME = '王五';
            查询英语成绩大于90分的同学
            select * from STUDENT where ENGLISH > 90;
            查询总分大于200分的所有同学
            select * from STUDENT where CHINESE+MATH+ENGLISH>200;
            查询英语分数在 80－90之间的同学。
            select * from STUDENT where ENGLISH between 80 and 90;
            查询数学分数为89,90,91的同学。
            select * from STUDENT where MATH in(89,90,91);
            查询所有姓李的学生成绩。
            select * from STUDENT where NAME like '李%';
            查询数学分>80，语文分>80的同学。
            select * from STUDENT where MATH>80 and CHINESE>80;
        练习：（将查询结果排序）
            对数学成绩排序后输出。
            select NAME,MATH from STUDENT order by MATH;
            对总分排序后输出，然后再按从高到低的顺序输出。
            select NAME,CHINESE+MATH+ENGLISH as TOTALSCORE from STUDENT order by TOTALSCORE desc;
            对姓李的学生成绩排序输出。
            select NAME,CHINESE,MATH,ENGLISH,CHINESE+MATH+ENGLISH as TOTALSCORE from STUDENT where NAME like '李%' order by TOTALSCORE;
七、数据完整性和多表设计                （重要）
    1、数据完整性的概述
    2、数据完整性的主要分类
        2.1、实体完整性（主键）
            练习：
                创建带主键的表（四种方式）
                第一种：写在字段后
                create table T1(
                    id int primary key,
                    name varchar(20)
                );
                第二种：写在所有字段后
                create table T2(
                    id int,
                    name varchar(20),
                    primary key(id,name)
                );
                第三种：使用alter添加
                create table T3(
                    id int,
                    name varchar(20)
                );
                alter table T3 add primary key(id);
                第四种：使用自增长    不建议使用
                create table T4(
                    id int primary key auto_increment,//是mysql独有的
                    name varchar(20)
                );
        2.2、域完整性（数据类型，非空和唯一）
            练习：
                创建带主键，非空约束和唯一约束的表
                create table T5(
                    id int primary key,
                    name varchar(50) not null,
                    gender varchar(10) not null,
                    phone varchar(15) not null unique,
                    idcard varchar(18) unique
                );
        2.3、参照完整性（多表设计）
            练习：
                创建一对多关系的表，使用外键
                创建多对多关系的表，使用外键
八、DQL的复杂查询                （重要）
    1、连接查询（显示和隐式）
        练习：
            a、交叉连接：cross join   
            select * from customer,orders;//隐式交叉连接
            select * from customer cross join orders;//显式交叉连接
            b、内连接：inner join   
            select * from customer c,orders o where c.id=o.customer_id;//隐式
            select * from customer c inner join orders o on c.id = o.customer_id;//显式
            c、外连接：
                 左外连接：
                 select * from customer c left outer join orders o on c.id=o.customer_id;
                 右外连接：
                 select * from customer c right outer join orders o on c.id=o.customer_id;
    2、子查询
        练习：
            查询“马化腾”的所有订单信息
                形式一：多条查询语句
                select id from CUSTOMER where name='马化腾';
                select * from orders where customer_id = 1;
                形式二：subselect（子查询）
                select * from orders where customer_id=(select id from CUSTOMER where name='马化腾');
            查询订单价格大于100的有哪些客户
                select * from customer where id in (select customer_id from orders where price>100);
    3、常用聚合函数
        练习：
            统计一个班级共有多少学生？
            select count(*) from STUDENT;
            统计数学成绩大于90的学生有多少个？
            select count(*) from STUDENT where MATH>90;
            统计总分大于250的人数有多少？where可以使用表达式
            select count(*) from STUDENT where CHINESE+MATH+ENGLISH>250;
            统计一个班级数学总成绩？
            select sum(MATH) from STUDENT;
            统计一个班级语文、英语、数学各科的总成绩
            select sum(CHINESE),sum(ENGLISH),sum(MATH) from STUDENT;
            统计一个班级语文、英语、数学的成绩总和
            select sum(CHINESE+MATH+ENGLISH) from STUDENT;
            统计一个班级语文成绩平均分
            select avg(CHINESE) from STUDENT;
            select sum(CHINESE)/count(*) from STUDENT;
        练习：（分组（报表）查询）
            对订单表中商品归类后，显示每一类商品的总价
            select product_name,sum(price) from PRODUCT group by product_name;
            查询购买了几类商品，并且每类总价大于100的商品
            select product_name from PRODUCT group by product_name having sum(price)>100;
    4、其他函数
九、数据库的备份与恢复            （练一遍就行）
    1、数据库的备份
    2、数据库的恢复