Mysql常见操作语句-蒲公英云

一、数据完整性
1、实体完整性
2、域完整性（列完整性约束）：
指数据库表的列（即字段）必须符合某种特定的数据类型或约束。

    约束：  
        类型约束：  id int   
        长度约束：    id int(3)        
        非空约束：    username varchar(10) NOT NULL 必须有值  
        唯一约束：    idcardnum varchar(18) UNIQUE 可以为null，有的话必须唯一  
        用户名：一般网站用户名必须有，且唯一（不让他作为主键）  
            username varchar(100) NOT NULL UNIQUE  
    示例：  
        CREATE TABLE user(  
            id int PRIMARY KEY,  
            username varchar(20) NOT NULL UNIQUE,  
            idcardnum varchar(18) UNIQUE,  
            gender varchar(10) NOT NULL  
        );  
    专题：整数类型的主键自动增长。由数据库自己自动插入主键的值  
        CREATE TABLE t1(  
            id int PRIMARY KEY auto\_increment,\#主键自动增长  
            name varchar(100)  
        );  
        INSERT INTO t1 (id,name) VALUES(1,‘A‘);  
        INSERT INTO t1 (name) VALUES(‘B‘);  (推荐)  
        INSERT INTO t1 VALUES (null,‘C‘);  
    建议：尽量不要让数据库维护主键。（不是所有的数据库都有自动增长这个功能）  
    由应用来维护主键  
3、参照完整性（重点：多表.外键）  
    3.1多表的设计  
        a、一对多（出现频率最高）  
            CREATE TABLE department(  
                id int PRIMARY KEY,  
                name varchar(100),  
                addr varchar(100)  
            );  
            CREATE TABLE employee(  
                id int PRIMARY KEY,  
                name varchar(100),  
                gender varchar(10),  
                salary float(8,2),  
                depart\_id int,  
                CONSTRAINT depart\_id FOREIGN KEY(depart\_id) REFERENCES department(id)  
            );  
            外键定义语法：  
            CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名称(主键字段);  
            外键名称：随便定义，当前库中必须唯一  
            外键字段：当前表中那个字段是外键

　　技术分享

        b、多对多（出现频率其次）  
            CREATE TABLE teacher(  
                id int PRIMARY KEY,  
                name varchar(100),  
                salary float(8,2)  
            );  
            CREATE TABLE student(  
                id int PRIMARY KEY,  
                name varchar(100),  
                grade varchar(10)  
            );  
            \#定义关系表  
            CREATE TABLE teacher\_student(  
                t\_id int,  
                s\_id int,  
                PRIMARY KEY(t\_id,s\_id),  
                CONSTRAINT t\_id\_fk FOREIGN KEY(t\_id) REFERENCES teacher(id),  
                CONSTRAINT s\_id\_fk FOREIGN KEY(s\_id) REFERENCES student(id)  
            );  
            INSERT INTO teacher VALUES(1,‘WYJ‘,10000);  
            INSERT INTO teacher VALUES(2,‘DH‘,10001);  
            INSERT INTO student VALUES(1,‘WF‘,‘A‘);  
            INSERT INTO student VALUES(2,‘QHS‘,‘A‘);  
            INSERT INTO teacher\_student VALUES(1,1);  
            INSERT INTO teacher\_student VALUES(1,2);  
            INSERT INTO teacher\_student VALUES(2,1);  
            INSERT INTO teacher\_student VALUES(2,2);

技术分享

        c、一对一（出现频率几乎没有）  
            CREATE TABLE person(  
                id int PRIMARY KEY,  
                name varchar(100)  
            );  
            CREATE TABLE id\_card(  
                id int PRIMARY KEY,  
                num varchar(18),  
                CONSTRAINT id\_fk FOREIGN KEY(id) REFERENCES person(id)  
            );

技术分享

二、多表的查询

1、连接查询  
    基本语法：select XXX from t1 连接类型 t2 \[on 连接条件\]\[where 筛选条件\]  
        约定：t1在连接类型的左边，称之为左表 t2就是右表  
              连接类型：  
                cross join:交叉连接  
                inner join:显式内连接  
                left outer join:左外连接  
                right outer join:右外连接  
    1.1交叉连接(了解)：cross join  
        SELECT \* FROM customer CROSS JOIN orders;  
        返回的结果是：返回的是两张表结果的笛卡尔积。即表1有5条，表2有7条，返回的5\*7=35条  
    1.2内连接：inner join（自然连接）  
        a、隐式内连接：不使用on关键字（即不明确指定连接条件），使用的是where  
            查询有订单的客户的信息和订单信息  
            SELECT \* FROM customer c,orders AS o WHERE c.id=o.customer\_id;  
        b、显式内连接：使用on关键字  
            查询有订单的客户的信息和订单信息  
            SELECT \* FROM customer c INNER JOIN orders o ON c.id=o.customer\_id;  
            查询订单金额在200元以上的客户的信息和订单信息  
            SELECT \* FROM customer c INNER JOIN orders o ON c.id=o.customer\_id WHERE o.price>=200;  
    1.3外连接：outer join  
        a、左外连接:返回满足连接条件的结果，同时返回左表中剩余的其他记录  
            查询客户信息，同时显示他的订单  
            SELECT \* FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer\_id;  
            查询所有的员工，打印所在的部门名称  
            SELECT \* FROM employee e LEFT OUTER JOIN department d ON e.depart\_id=d.id;  
        b、右外连接:返回满足连接条件的结果，同时返回右表中剩余的其他记录  
            查询客户信息，同时显示他的订单  
            SELECT \* FROM orders o RIGHT OUTER JOIN customer c ON c.id=o.customer\_id;  
            查询所有订单，同时显示他的客户信息  
            SELECT \* FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer\_id;  
2、子查询（简单）：  
    子查询：嵌套查询（内部语句）。一个查询语句是另外一个查询语句的条件。子查询的语句必须放在小括号之内  
        select \* from t1 where id=(select id from t2);  
        查询id为2的老师教过的学生姓名  
        方式一：多条语句  
            select s\_id from teacher\_student where t\_id=2;  
            select \* from student where id in (1,2);  
        方式二：连接查询  
            select s.\* from teacher\_student ts,student s where ts.s\_id=s.id and ts.t\_id=2;  
        方式三：子查询（一个单列结果）  
            select \* from student where id in (select s\_id from teacher\_student where t\_id=2);  
        查询名字为‘陈冠希‘的所有订单信息（子查询）  
            select \* from orders where customer\_id=(select id from customer where name=‘陈冠希‘);  
3、联合查询：UNION  
    联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。  
    取多条语句的并集（没有重复记录）  
    查询客户id=1并且订单金额>=200的订单信息  
    SELECT \* from orders where price>=200 and customer\_id=1;  
    查询客户id=1或者订单金额>=200的订单信息  
    SELECT \* from orders where price>=200 or customer\_id=1;  
    使用联合查询：查询客户id=1或者订单金额>=200的订单信息  
    SELECT \* FROM orders WHERE price>=200 UNION SELECT \* FROM orders WHERE customer\_id=1;  
4、报表查询（数据库的内置函数）  
    报表查询对数据行进行分组统计  
    \[select …\] from … \[where…\] \[ group by … \[having… \]\] \[ order by … \]  
    group by:按照那些字段进行分组  
    having:对分组后的内容进行过滤（不能使用where）  
    统计一个班级共有多少学生？  
    mysql>SELECT count(\*) FROM student;  
    统计数学成绩大于90的学生有多少个？  
    mysql>SELECT COUNT(\*) FROM student where math>90;  
    统计总分大于250的人数有多少？  
    mysql>SELECT COUNT(\*) FROM student where (math+chinese+english)>250;  
    统计一个班级数学总成绩？  
    mysql>SELECT SUM(math) FROM student;  
    统计一个班级语文、英语、数学各科的总成绩  
    mysql>SELECT SUM(math),SUM(chinese),SUM(english) FROM student;  
    统计一个班级语文、英语、数学的成绩总和  
    mysql>SELECT SUM(math+chinese+english) FROM student;  
    统计一个班级语文成绩平均分  
    mysql>SELECT SUM(chinese)/COUNT(\*) FROM student;  
    求一个班级数学平均分？  
    mysql>SELECT AVG(math) FROM student;  
    求一个班级总分平均分  
    msyql>SELECT AVG(math+chinese+english) FROM student;  
    求班级语文最高分和最低分  
    mysql>SELECT MAX(chinese) FROM student;  
    mysql>SELECT MIN(chinese) FROM student;  
    注意：不能使用关键字作为表名或列名，如果必须使用，请使用反引号\`（ESC按键下面）引起来  
    对订单表中商品归类后，显示每一类商品的总价  
    mysql>SELECT product,sum(price) FROM orders GROUP BY product;  
    查询购买了几类商品，并且每类总价大于100的商品  
    mysql>SELECT product,sum(price) FROM orders GROUP BY product HAVING sum(price)>100;

三、数据库的备份与恢复（MySQL）
1、备份数据库（表结构和表中数据）
c:/>mysqldump -h localhost -u root -p mydb1>d:/mydb1.sql

2、恢复数据库：数据库名必须手工创建，并选择  
    方式一：在MySQL内部导入数据  
        mysql>create database mydb1;  
        mysql>use mydb1;  
        mysql>source d:/mydb1.sql;  
    方式二：不进入mysql进行数据的恢复  
        c:/>mysql -u root -p mydb1<d:/mydb1.sql