MySQL基础2---数据库的基本操作

一、sql语句
    
1.操作数据库
    (1)创建数据库
        CREATE  DATABASE  [IF NOT EXISTS] db_name  [create_specification [, create_specification] ...]
            create_specification:   
                [DEFAULT] CHARACTER SET charset_name |   [DEFAULT] COLLATE collation_name
        ~创建一个名称为mydb1的数据库。
            create database mydb1;
        ~创建一个使用gbk字符集的mydb2数据库。
            create database mydb2 character set gbk;
        ~创建一个使用utf8字符集，并带校对规则的mydb3数据库。
            create database  mydb3 character set utf8 collate utf8_bin;
    (2)查看数据库
        SHOW DATABASES; -- 显示所有数据库
        SHOW CREATE DATABASE db_name; -- 显示指定的数据库的建库语句
        
        ~查看当前数据库服务器中的所有数据库
            show databases;
        ~查看前面创建的mydb2数据库的定义信息
            show create database mydb2;
    (3)删除数据库
        DROP DATABASE  [IF EXISTS]  db_name

        ~删除前面创建的mydb1数据库
            drop database mydb1;
    (4)修改数据库
        ALTER  DATABASE  [IF NOT EXISTS] db_name   [alter_specification [, alter_specification] ...]
            alter_specification:   
                [DEFAULT] CHARACTER SET charset_name   |   [DEFAULT] COLLATE collation_name
        ~查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;
            alter database mydb2 character set utf8;
    (5)选择数据库
        use db_name;
        select database();

2.操作表
    (1)创建表
        CREATE TABLE table_name
        (
            field1  datatype,
            field2  datatype,
            field3  datatype,
            ....
        )character set 字符集 collate 校对规则
        field：指定列名　datatype：指定列类型
    
        ~创建employee表
            create table employee(
                id int primary key auto_increment,
                name varchar(20) unique ,
                gender bit not null,
                birthday date,
                entry_date date,
                job varchar(50),
                salary double,
                resume text
            );
    (2)查看表
        desc tabName -- 查看表结构
        show tables -- 查看数据库中所有表
        show create table tab_name -- 查看指定表的建表语句
    
    (3)修改表
        ALTER TABLE table ADD/MODIFY/DROP/character set/change (column datatype [DEFAULT expr]  [, column datatype]...);
        
        rename table 表名 to 新表名;


        ~在上面员工表的基本上增加一个image列。
            alter table employee add image blob;
        ~修改job列，使其长度为60。
            alter table employee modify job varchar(60);
        ~删除gender列。
            alter table employee drop gender;
        ~表名改为user。
        rename table employee to user;
        ~修改表的字符集为utf8
            alter table user character set utf8;
        ~列名name修改为username
            alter table user change name username varchar(20);
    (4)删除表
        DROP TABLE tab_name;

        ~删除user
            drop table user;
3.操作表记录(CRUD)
    (1)INSERT
        INSERT INTO table [(column [, column...])] VALUES (value [, value...]);
        
        insert into employee (id,name,gender,birthday,entry_date,job,salary,resume)
        values (null,'张飞',1,'1999-09-09','1999-10-01','打手',998.0,'真的很能打..');
        
        insert into employee values (null,'关羽',1,'1998-09-09','1998-09-10','财神',9999999.0,'公司挣钱就指着他了');
        
        insert into employee values (null,'刘备',0,'1990-01-01','1990-01-01','ceo',10.0,'公司老总,女强人'),(null,'赵云',0,'2000-02-02','2003-01-01','保安队队长',109.0,'老总贴身的保镖..');

        *mysql中的乱码问题
            set names gbk;--- 通知服务器当前客户端是什么编码格式,从而使服务器以指定的编码处理和当前客户端的通信,从而解决了乱码
                ---仅仅对当前客户端起作用

            修改mysql安装目录下的my.ini,设置default-character-set=gbk,修改了服务器默认认为的客户端编码

    (2)UPDATE
        UPDATE     tbl_name SET col_name1=expr1 [, col_name2=expr2 ...]  [WHERE where_definition]
        
        ~将所有员工薪水修改为5000元。
            update employee set salary=5000;
        ~将姓名为’张飞’的员工薪水修改为3000元。
            update employee set salary=3000 where name='张飞';
        ~将姓名为’关羽’的员工薪水修改为4000元,job改为ccc。
            update employee set salary=4000 ,job='ccc' where name='关羽';
        ~将刘备的薪水在原有基础上增加1000元。    
            update employee set salary=salary+1000 where name='刘备';
 
    (3)DELETE
        delete from tbl_name [WHERE where_definition]
        
        ~删除表中名称为’张飞’的记录。
            delete from employee where name='张飞';
        ~删除表中所有记录。
            delete from employee;
        ~使用truncate删除表中记录。
            truncate employee;
    (4)SELECT
        
        ~1.SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;

            ~查询表中所有学生的信息。
                select * from exam;
            ~查询表中所有学生的姓名和对应的英语成绩。
                select name,english from exam;
            ~过滤表中重复数据
                select distinct english from exam;

            ~在所有学生分数上加10分特长分显示。
                select name,math+10,english+10,chinese+10 from exam;
            ~统计每个学生的总分。
                select name ,math+english+chinese from exam;
            ~使用别名表示学生总分
                select name 姓名 ,math+english+chinese 总成绩 from exam;
            
        2.使用WHERE子句进行过滤查询
            ~查询姓名为张飞的学生成绩
                select * from exam where name='张飞';
            ~查询英语成绩大于90分的同学
                select name ,english from exam where english>90;
            ~查询总分大于230分的所有同学
                select name 姓名,english+math+chinese 总成绩 from exam where english+math+chinese>230;
            ~查询英语分数在 80－100之间的同学。
                select name ,english from exam where english between 80 and 100;
            ~查询数学分数为75,76,77的同学。
                select name ,math from exam where math in (75,76,77);
            ~查询所有姓张的学生成绩。
                select * from exam where name like '张%';
                select * from exam where name like '张__';
            ~查询数学分>70，语文分>80的同学。
                select * from exam where math>70 and chinese>80;
                
        3.使用Order by子句进行排序查询
            SELECT column1, column2. column3.. FROM    table; order by column asc|desc
            
            ~对英语成绩排序后输出。
                select name,english from exam order by english desc;
            ~对总分排序按从高到低的顺序输出
                select name 姓名,ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0) 总成绩 from exam order by 总成绩 desc;
            ~对姓张的学生成绩排序输出
                select name 姓名,ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0) 总成绩
                from exam
                where name like '张%'
                order by 总成绩 desc;
        4.使用聚合函数
            ~1.count -- 求符合条件的行的总数
                Select count(*)|count(列名) from tablename [WHERE where_definition]
                
                ~统计一个班级共有多少学生？
                    select count(*) from exam;
                ~统计数学成绩大于70的学生有多少个？
                    select count(*) from exam where math>70;
                ~统计总分大于230的人数有多少？
                    select count(name) from exam where ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)>230;
            ~2.sum --求符合条件的列的和
                ~统计一个班级数学总成绩？
                    select sum(math) from exam;
                ~统计一个班级语文、英语、数学各科的总成绩
                    select sum(math),sum(chinese),sum(english) from exam;
                ~统计一个班级语文、英语、数学的成绩总和
                    select sum(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
                ~统计一个班级语文成绩平均分
                    select sum(chinese)/count(chinese) from exam;
            ~3.AVG 求符合条件的列的平均数
                ~求一个班级数学平均分？
                    select avg(math) from exam;
                ~求一个班级总分平均分？
                    select avg(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;

            ~4.MAX/MIN 求符合条件的列中的最大/最小值
                ~求班级最高分和最低分
                    select max(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
                    select min(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;


        5.分组查询
            ~对订单表中商品归类后，显示每一类商品的总价
                select product,sum(price) from orders group by product;
            
            ~查询购买了几类商品，并且每类总价大于100的商品
                select product ,sum(price) from orders group by product having sum(price)>100;

            ~查询单价小于100总价大于150的商品
                  select product from orders where price < 100 group by product having sum(price)>150;
    
            *使用having 子句 对分组结果进行过滤
                having和where都可以进行过滤,但是where是在分组之前进行过滤,having在分组之后进行过滤.having子句中可以使用聚合函数而where子句中不能使用聚合函数
            
            !!sql语句的书写顺序,执行顺序
                书写顺序:select from where group by having order by
                执行顺序:from where select group by having order by

二、数据库的备份和恢复
    备份--cmd窗口：mysqldump -u root -p mydb2>c:/1.sql
    恢复1--先创建数据库 回到cmd窗口 mysql -u root -p mydb2<c:/1.sql
    恢复2 -- 创建数据库,进入数据库,在mysql命令符下source c:/1.sql,所谓的source命令就是执行一边sql文件


三、多表设计多表查询
    1.外键约束
        数据库中的表和表之间常常具有一定的关系,这些关系往往是通过表中引入参照表的id来进行声明的,此时可以将这种参照关系利用外键明确的通知数据库,这样一来数据库会帮助我们管理这种参照关系,当对数据进行增删改操作时,数据库会帮我们检查并确保不会违反这些参照关系.

         foreign key(ordersid) references orders(id)
        
        create table dept (
            id int primary key auto_increment,
            name varchar(20)
        );
        insert into dept values(null,'人事部'),(null,'财务部'),(null,'技术部'),(null,'销售部');

        create table emp(
            id int primary key auto_increment,
            name varchar(20),
            dept_id int
        );
        insert into emp values(null,'奥巴马',1),(null,'普京',2),(null,'安倍',2),(null,'朴乾',3);


    2.多表设计
        1--*:在多的一方保存一的一方的主键作为外键
        1--1:在任意一方保存另一方的主键作为外键
        *--*:设计第三方表,保存两张表的主键作为外键,从而保存两张表中记录的对应关系

    3.多表查询
        笛卡尔积查询:如果左边表有m条记录,右边表有n条记录,则笛卡尔积查相当于做乘法查处m*n条记录.这样的查询,查出来的数据往往包含了大量错误的数据
            select * from dept,emp;
            
        内连接查询:查询的结果里只有左边有且右边表也有的记录
            select * from dept,emp where dept.id=emp.dept_id;
            select * from dept inner join emp on dept.id=emp.dept_id;
        
        外连接查询:
            左外连接查询(左连接查询):在内连接的基础上,增加上左边表有而右边表没有的记录
                select * from dept left join emp on dept.id=emp.dept_id;
            右外连接查询(右连接查询):在内连接的基础上,增加上右边表有而左边表没有的记录
                select * from dept right join emp on dept.id=emp.dept_id;
            全外连接查询(全连接查询):在内连接的基础上增加上左边表有而右边表没有的记录和右边表有而左边表没有的记录
                select * from dept full join emp on dept.id=emp.dept_id;--mysql不支持全外连接

                select * from dept left join emp on dept.id=emp.dept_id
                union
                select * from dept right join emp on dept.id=emp.dept_id;