SQL语言总结

一、SQL简介
    1、SQL：Structured Query Language的缩写
    中文名称：结构化查询语言
    作用：是一种定义、操作、管理关系数据库的句法。大多数关系型数据库都支持。
    2、结构化查询语言的工业标准由ANSI(美国国家标准学会，ISO的成员之一)维护。
    数据厂商都对该标准进行了扩展。叫做方言。
    
    SQL标准：普通话。
    3、SQL组成：
        DQL：数据查询语言
        DML：数据操作语言
        DDL：数据定义语言
        
        
        DCL：数据控制语言
        TPL：事务处理语言
        CCL：指针控制语言
二、卸载：
    1、计算机--管理--找到服务---MySQL服务停止
    2、控制面板--卸载MySQL
    3、找到MySQL安装目录，把目录删除
三、安装配置
四、验证是否成功
    mysql -u root -p
    
    特别注意：MySQL在非window系统下，是严格区分大小写的。
    
五、特点：Hibernate MyBatis
        表结构和JavaBean类对应
        表中的记录和对象对应
六、DDL：数据定义语言（可视化工具）
    作用：用于描述数据库中要存储的现实世界实体的语言。即创建数据库和表的结构。
    常用关键字：
    CREATE ALTER(奥特) DROP TRUNCATE
    --------------------------------------------------------------
    
    查看当前有哪些数据库
    SHOW DATABASES;
    创建一个名称为mydb1的数据库。
    CREATE DATABASE mydb1; //数据库使用的是什么字符集。默认之前配置的字符集
    查看数据库创建细节
    SHOW CREATE DATABASE mydb1;
    创建一个使用gbk字符集的mydb2数据库。
    CREATE DATABASE mydb2 CHARACTER SET gbk;
    创建一个使用gbk字符集，并带校对规则的mydb3数据库。
    CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci;
    删除前面创建的mydb3数据库
    DROP DATABASE mydb3;
    查看服务器中的数据库，并把mydb2的字符集修改为utf8;
    ALTER DATABASE mydb2 CHARACTER SET utf8;
    --------------------------------------------------------------
    表属于某个库的，创建表之前，必须先选择库。
    查看当前选择的库
    SELECT DATABASE();
    选择一个库
    USE mydb1;
    创建一个员工表
    CREATE TABLE employees(
        id INT,
        name VARCHAR(250),
        gender VARCHAR(100),
        birthday DATE,
        entry_date DATE,
        job VARCHAR(100),
        salary FLOAT(8,2),
        resume TEXT
    );
    
    查看表结构
    DESC employees;
    在上面员工表的基本上增加一个image列。
    ALTER TABLE employees ADD image blob;
    修改job列，使其长度为60。
    ALTER TABLE employees MODIFY job VARCHAR(60);
    删除image列。
    ALTER TABLE employees DROP image;
    查看当前库中的所有表
    SHOW TABLES;
    表名改为users。
    RENAME TABLE employees TO users;
    
    查看表的创建细节
    SHOW CREATE TABLE users;
    
    修改表的字符集为gbk
    ALTER TABLE users CHARACTER SET gbk;
    
    列名name修改为username
    ALTER TABLE users CHANGE name username VARCHAR(250);
    
七、DML语句：数据操作语言
    作用：用于向数据库表中插入、删除、修改数据。
    常用关键字：
    INSERT UPDATE DELETE
    
    MySQL数据输入：字符、字符串、日期(yyyy-MM-dd)  使用单引号引起来。
    特殊值：NULL 空值
    
    查看数据库使用编码的地方
    SHOW VARIABLES LIKE 'character%';
    
    
    插入3条记录
    INSERT INTO users (id,username,gender,birthday,entry_date,job,salary,resume) VALUES (1,'ybb','male','1992-11-05','2014-11-01','CEO',20000,'abc');
    INSERT INTO users VALUES (2,'db','female','1932-11-05','2014-11-01','CTO',20000,'abc');
    告知服务器编码是什么：
    SET character_set_client=gbk;
    
    INSERT INTO users (id,username,gender,birthday,entry_date,job,salary,resume) VALUES (3,'徐琼','female','1990-11-05','2014-11-01','CMO',20000,'abc');
    查看表中的所有记录
    SELECT * FROM users;
    告知服务器返回的结果集使用的码表
    SET character_set_results=gbk;
    
    将所有员工薪水修改为5000元。
    UPDATE  users SET salary=5000;
    将姓名为’ybb’的员工薪水修改为3000元。
    UPDATE users SET salary=3000 WHERE username='ybb';
    将姓名为’db’的员工薪水修改为4000元,job改为ccc。
    UPDATE users SET salary=4000,job='CCC' WHERE username='db';
    将wu的薪水在原有基础上增加1000元。
    UPDATE users SET salary=salary+1000 WHERE username='徐琼';
    
    删除表中名称为’ybb’的记录。
    DELETE FROM users WHERE username='ybb';
    删除表中所有记录。
    DELETE FROM users; //一条一条的删除 (删除磁盘所有文件)
    使用truncate（DDL）删除表中记录。
    TRUNCATE TABLE users;// 摧毁表，重建表结构  （磁盘格式化）
    
八、DQL数据查询语言：针对单表查询
    作用：查询数据，返回结果集。
    常用关键字：
    SELECT
    
    查询表中所有学生的信息。
    SELECT * FROM student;
    查询表中所有学生的姓名和对应的英语成绩。
    SELECT name,english FROM student;
    过滤表中英语重复数据。
    SELECT DISTINCT english FROM student;
    在所有学生数学分数上加10分特长分。
    SELECT math+10 FROM student;
    统计每个学生的总分。
    SELECT math+english+chinese FROM student;
    使用别名表示学生分数。
    SELECT math+english+chinese AS 总分 FROM student;
    查询姓名为wu的学生成绩
    SELECT * FROM student WHERE name='王五';
    查询英语成绩大于90分的同学
    SELECT * FROM student WHERE english>90;
    查询总分大于200分的所有同学
    SELECT name,math+english+chinese 总分 FROM student WHERE (math+english+chinese)>200;
    查询英语分数在 80－90之间的同学。
    SELECT name,english FROM student WHERE english BETWEEN 80 AND 90;
    查询数学分数为89,90,91的同学。
    SELECT name,math 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,math+english+chinese 总分 FROM student ORDER BY 总分 DESC;
    对姓李的学生数学成绩排序输出
    SELECT name,math FROM student WHERE name LIKE '李%' ORDER BY math;
    
九、数据完整性
    数据完整性是为了保证插入到数据中的数据是正确的，它防止了用户可能的输入错误。
    1、域(列)完整性
        指数据库表的列（即字段）必须符合某种特定的数据类型或约束
        类型约束
        长度约束
        非空约束：NOT NULL
        唯一约束：UNIQUE
        
    2、实体(记录)完整性
        规定表的一行（即每一条记录）在表中是唯一的实体。
        通过设置主键实现实体完整性：PRIMARY KEY
        主键的特点：非空+唯一。
        CREATE TABLE t2(
            id int PRIMARY KEY,
            name varchar(100),
            gender varchar(100)
        );
        CREATE TABLE t3(
            id int PRIMARY KEY AUTO_INCREMENT,#自动增长 并不是所有数据库都支持
            name varchar(100),
            gender varchar(100)
        );
        CREATE TABLE t4(
            id int,
            name varchar(100),
            gender varchar(100),
            PRIMARY KEY(id,name)
        );
        
        业务主键：作为主键的字段代表一定的业务含义。比如身份证号。
        逻辑主键：（推荐）作为主键的字段不代表业务含义，只是为了区分不同记录而存在。
            
        
    3、参照完整性（多表）
        保证一个表的外键和另一个表的主键对应。通过外键实现的
        3.1一对多
        CREATE TABLE customers(
            id int PRIMARY KEY,
            name varchar(100)
        );
        CREATE TABLE orders(
            ordernum int(100) PRIMARY KEY,
            meony float(8,2),
            customer_id int,
            CONSTRAINT customer_id_fk FOREIGN KEY(customer_id) REFERENCES customers(id)
        );
        3.2多对多
        CREATE TABLE teachers(
            id int PRIMARY KEY,
            name varchar(100),
            salary float(8,2)
        );
        CREATE TABLE students(
            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 teachers(id),
            CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES students(id)
        );
        3.3一对一
十、多表查询（面试）
    1、链接查询
        a、交叉链接：CROSS JOIN
            SELECT * FROM customer,orders;或者SELECT * FROM customer CROSS JOIN orders;
            
            返回的是两张表的笛卡尔积。（5*7）
        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、外连接查询：LEFT|RIGHT OUTER JOIN
            查询客户信息，同时把客户的订单也查询出来
            SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id;(返回的是满足链接条件的记录和左表其他剩余的记录)
            右外连接：
            SELECT * FROM orders o RIGHT OUTER JOIN customer c  ON c.id=o.customer_id;
十一、子查询
    又名嵌套查询。
    
    举例：查询“小明”的所有订单信息
    多条语句：
        SELECT id FROM customer WHERE name='小明';
        SELECT * FROM orders WHERE customer_id=1;
    子查询：SELECT * FROM orders WHERE customer_id=(SELECT id FROM customer WHERE name='小明');
    
    举例：查询"小明"和"小红"的订单信息
    多条语句：
        SELECT id FROM customer WHERE name='小明' OR name='小红';
        SELECT * FROM orders WHERE customer_id IN (1,2);
    子查询：SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customer WHERE name='小明' OR name='小红');

    联合查询：
十二、报表查询
    统计一个班级共有多少学生？
    SELECT COUNT(*) FROM student;
    统计数学成绩大于90的学生有多少个？
    SELECT COUNT(*) FROM student WHERE math>90;
    统计总分大于250的人数有多少？
    SELECT COUNT(*) FROM student WHERE (chinese+math+english)>250;
    统计一个班级数学总成绩？
    SELECT SUM(math) FROM student;
    统计一个班级语文、英语、数学各科的总成绩
    SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
    统计一个班级语文、英语、数学的成绩总和
    SELECT SUM(math+chinese+english) FROM student;
    统计一个班级语文成绩平均分
    SELECT SUM(chinese)/COUNT(*) FROM student;
    求一个班级数学平均分？(AVG不把NULL的记录算在内)
    SELECT AVG(math) FROM student;
    求一个班级总分平均分
    SELECT AVG(math+chinese+english) FROM student;

    对订单表中商品归类后，显示每一类商品的总价
    SELECT product,SUM(price) FROM orders GROUP BY product;
    查询购买了几类商品，并且每类总价大于100的商品
    SELECT product,SUM(price) FROM orders GROUP BY product HAVING SUM(price)>100;
    
十三、MySQL的数据导出和导入
    导出：
        c:/>mysqldump -u root -p mydb1>d:/mydb1.sql
    导入：
        CREATE DATABASE mydb1;
        方式一：进入MySQL    
        use mydb1;
        SOURCE d:/mydb1.sql
        
        方式二：不进入MySQL
        c:/>mysql -u root -p mydb1<d:/mydb1.sql