MySQL小结

一、概述
    1.数据库服务器简介
        *Miscrosoft公司
            Sql Server（后台用微软的语言开发，只适应Windows平台，与其适配性也是最好的）
        *Orcacle公司
            Orcacle（并发性，最贵）
            Mysql（sun公司，免费，小）
        *IBM公司
            DB2（海量数据，企事级应用）
    2.数据库服务器、数据库、表的关系
        *安装数据库服务器，只是在机器上装了一个数据库管理程序，它可以管理多个数据库.
            一般开发时会针对每一个应用创建一个数据库.
        *为保存应用中实体的数据，一般会在数据库创建多个表，用来保存程序中实体的数据.
    3.常用的概念
        *SQL（structed query language）：结构化的查询语言.
        *DDL（Data Definition Language）：数据定义语言.
                命令：create、drop、alter等.
        *DML（Data Manipulation Language）：数据操纵语言.
                命令：insert、update、delete.
        *DQL（Data Query Language）：数据查询语言.
                命令：select
        *CRUD操作
                指在做计算处理时的增加（Create）、查询（Retrieve）、更新（Update）和删除（Delete）
        注意：DDL是针对数据库和表结构的.
                     其余的是针对表中的数据的.
    4.mysql中的数据类型
        bit：1位，但可以指定位数.如：bit<3>
        int：2字节，可以指定最大位数，如：int<4>
        float 2个字节　可以指定最大的位数和最大的小数位数，
                 如：float<5,2> 最大为一个５位的数，小数位最多2位
        double　4个字节　也可以指定最大的位数和最大的小数位数.
        char　必须指定字符数,如char(5) 为不可变字符　
                即使存储的内容为'ab',也是用5个字符的空间存储这个数据.
        varchar　必须指定字符数,如varchar(5) 为可变字符　
                   如果存储的内容为'ab',占用2个字符的空间；如果为'abc',则占用3个字符的空间.
        text: 大文本(大字符串)
        blob：二进制大数据　如图片，音频文件，视频文件
        date: 日期　如：'1921-01-02'
        datetime: 日期时间　如：'1921-01-02 12:23:43'
        timeStamp: 时间戳，自动赋值为当前日期时间
        其他查文档.
二、数据库和表的操作
    1.连接mysql服务器
        *命令行下：mysql -uroot -proot  (root是用户名和密码，自己的是什么写什么，密码可以先不写)
        *指定默认字符集连接：mysql -uroot -proot --default_character_set=gbk   以gbk为默认编码进行连接
        *show variables like 'character%'; 可以查看mysql所用的字符集.
        *数据的备份与恢复：
                备份：mysqldump -uroot -p mydb1>d:\test.sql; 备份数据库里的数据.
                恢复：mysql -uroot -p mydb1<d:\test.sql  恢复数据库里的数据.
                            source d:\test.sql; (在mysql客户端上执行).
    2.数据库的操作（DDL）
        *创建
            create database mydb1;
                   创建一个名称为mydb1的数据库.
            create database mydb2 character set utf8;
                   创建一个使用utf-8字符集的mydb2的数据库.
            create database mydb3 character set utf8 collate utf8_general_ci;
                   创建一个使用utf-8字符集，并带校对规则的mydb3的数据库.
        *查看
              show databases; 显示所有数据库.
              show create database mydb2; 显示创建的数据库的语句信息.
        *修改
            alter database mydb1 character set utf8;
                  修改mydb1的字符集为utf-8.(数据库名不能修改)
        *删除
              drop database mydb1; 删除数据库mydb2.
    3.表的操作（DDL）
        *创建
            创建一个员工表：
            create table employee(
                id int,
                name varchar(20),
                sex bit,
                birthday date,
                salary double,
                resume test
            );
        *查看
            show tables; 查看所有的表.
            show create table employee; 查看创建的表的语句信息.
            desc employee; 显示指定表的结构.
        *修改
            alter table employee add column job varchar(20); 增加一个字段.
            alter table employee modify column job char(10); 修改一个字段.
            alter table employee drop column job; 删除一个字段.
            rename table employee to worker; 更改表名.
            alter table employee character set gbk; 修改表的字符集.
        *删除
            drop table employee; 删除employee表.
三、表中数据的操作    
    1.表数据的CRUD（DML、DQL）
        *C: insert语句
            根据上边创建的employee来增加数据.
    insert into employee(name,id,sex,birthday,salary,resume) values('张三',1,1,'1983-09-21',15000,'一个大牛');
    insert into employee(id,name,sex,birthday,salary,resume) values(2,'李四',1,'1984-09-21',10000,'一个中牛');
    insert into employee(id,name,sex,birthday,salary,resume) values(3,'王五',0,'1985-09-21',7000,'一个小牛');
        *U：update语句
            update employee set salary=salary+1000; 将所有员工薪水增加1000元.
            update employee set salary=10000,resume='也是一个中牛' where name='王五';
                将王五的薪水修改为10000元，resume改为也是一个中牛.
        *D：delete语句
            delete from employee where where name='王五'; 删除表中姓名为王五的记录.
            delete from employee;删除表中所有记录.可以有条件，效率稍低.
            truncate employee; 删除表中所有记录.无条件，效率高.
        *R:select语句
            准备环境：
                create table student(
                id int,
                name varchar(20),
                chinese int,
                english int,
                math int
            );
            insert into student(id,name,chinese,english,math) values(1,'何东',80,85,90);
            insert into student(id,name,chinese,english,math) values(2,'权筝',90,95,95);
            insert into student(id,name,chinese,english,math) values(3,'何南',80,96,96);
            insert into student(id,name,chinese,english,math) values(4,'叶坦',81,97,85);
            insert into student(id,name,chinese,english,math) values(5,'何西',85,84,90);
            insert into student(id,name,chinese,english,math) values(6,'丁香',92,85,87);
            insert into student(id,name,chinese,english,math) values(7,'何北',75,81,80);
            insert into student(id,name,chinese,english,math) values(8,'唐娇',77,80,79);
            insert into student(id,name,chinese,english,math) values(9,'任知了',95,85,85);
            insert into student(id,name,chinese,english,math) values(10,'王越',94,85,84);
            --------------------------------
            select * from student; 查询表中所有学生的信息.
            select name,english from student; 查询所有学生的姓名和对应的英语成绩.
            select destinct english from student; 过滤英语成绩重复的数据.
            select name,english+math+chinese as 总分 from student;统计每个学生的总分，并取别名总分显示.
            select name,english+10 from student; 在所有学生英语分数上加10分特长分.
            select * from student where name='何东'; 查询姓名为何东的学生成绩.
            select * from student where english+math+chinese>250;查询总分大于250分的所有同学.
            select * from student where english between 85 and 95; 查询英语分数在85-95之间的同学.（包含85与95）
            select * from student where math in(84,90,91);查询数学分数为84，90，91的同学.
            select * from student where name like '何%';查询所有姓何的同学成绩.
            select * from student where math>85 and chinese>90;查询数学>85,语文>90的同学.
            select * from student order by math;按数学成绩升序.
            select * from student order by math desc;按数学成绩降序.
            select count(*) from student; 统计一个班级学生人数.
            select count(*) from student where math>90; 统计数学>90的学生人数.
            select sum(math),sum(chinese),sum(english) from student;统计一个班级语文，数学，英语各科总成绩.
            select avg(math) from student; 求一个班级数学平均分.
            select man(math+chinese+english),min(math+chinese+english) from student; 求班级最高分与最低分.
            *查出各个班的总分，最高分.
            准备环境:
                给表添加一个字段：alter table student add column class_id int;
                更新表(分班级)：
                    update student set class_id=1 where id<=5;
                    update student set class_id=2 where id>5;
                select sum(math+chinese+english),max(math+chinese+english) from student group by class+id;
            *查询出班级总分大于1300分的班级ID
                select class_id from student group by class_id having sum(math+chinese+english)>1300;
            *类似的字符串相关函数与时间日期相关函数，还有数学相关函数还有很多，具体可以查看文档.
    2.表的约束
        定义主键约束
            primary key:不允许为空，不允许重复.
        定义主键自动增长
            auto increment
        定义唯一约束
            unique
        定义非空约束
            not null
        定义外键约束（保证数据的完整性）
            constraint student_classid_FK foreign key(classid) references class(id);
            约束    哪个表_哪个字段_FK    外键(哪个字段)    引用    哪个表(哪个字段)
        示例：
            #班级表：
            create table class(
                id int primary key auto_increment,
                name varchar(15)
            );
            #学生表：
            create table student(
                id int primary key auto_increment,
                name varchar(20),
                classid int not null
            );
            #建立两个班级:
            insert into class(name) values('a');
            insert into class(name) values('b');
            此时如果加一个学生，而classid填写c，是不是不合法？因为没有c班级.
            但没有添加外键约束之前，不会报错.这样数据就不能保证完整性.因此需要添加外键约束.
            #添加外键：
            alter table student add constraint student_classid_FK foreign key(classid) references class(id);
            这样就有了约束.
    3.多表查询与子查询
        *准备环境：
            部门表
            CREATE TABLE DEPARTMENT(
               department_id int primary key auto_increment,       
               name varchar
            );
            职位表
                CREATE TABLE LOCATION (
                   location_id int PRIMARY KEY auto_increment,       
                   name varchar
                );
            员工表
                CREATE TABLE EMPLOYEES(
                   employee_id int PRIMARY KEY auto_increment,       
                   department_id int,       
                   location_id int,       
                   first_name varchar,       
                   last_name varchar,       
                   salary double,
                   hire_date date     
                );
            向department表中添加数据            
                insert into department(name) values ('人事');
                insert into department(name) values ('财务');
                insert into department(name) values ('后勤');
                insert into department(name) values ('公关');
                insert into department(name) values ('研发');
            向location表中添加数据
                insert into location(name) values ('总经理');
                insert into location(name) values ('经理');
                insert into location(name) values ('主管');
                insert into location(name) values ('组长');
                insert into location(name) values ('职员');

            向employees表中添加数据

	insert into employees(department_id,location_id , first_name, last_name, salary,hire_date)
					   values (1,1, 'A', 'z', 50000, '2005-02-21');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (1,2, 'B', 'x', 20000, '2009-03-21');       
				insert into employees(department_id,location_id , first_name, last_name, salary,hire_date)
					   values (2,3, 'C', 'v', 10000, '2009-08-23');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (4,2, 'D', 'n', 30000, '2004-09-28');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (3,5, 'E', 'm', 3000, '2009-04-11');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (5,5, 'F', 'l', 5000, '2008-03-11');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (5,3, 'G', 'p', 20000, '2005-05-09');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (5,4, 'H', 'o', 8000, '2006-07-21');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (5,5, 'I', 'u', 6000, '2006-09-21');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (5,5, 'J', 'y', 5500, '2007-08-21');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (5,5, 'K', 't', 6500, '2006-12-21');       
				insert into employees(department_id,location_id, first_name, last_name, salary,hire_date)
					   values (5,1, 'L', 'r', 100000, '2001-05-21'); 

*多表查询：查出姓z的员工的所有信息.
                select * from employees e,department d,location l
                    where e.department_id=d.department_id and e.location_id=l.location_id and e.[last_name]='z';
                这里需要联表，否则会出现笛卡尔集.
        *单行子查询：查出所有工资大于平均工资的员工信息.
                select * from employees where salary >(select avg(salary) from employees);
        *多行子查询：查出所有姓z的部门的名称.
            分析：可以先查出所有姓z的部门的id，然后在根据id查名称.
            select name from department where department_id in (select department_id from employees where last_name='z');