J2EE进阶之MySQL数据库 十五

MySQL数据库

一、SQL简介

• SQL：Structured Query Language的缩写
• 中文名称：结构化查询语言
• 作用：是一种定义、操作、管理关系数据库的句法。大多数关系型数据库都支持。
• 结构化查询语言的工业标准由ANSI(美国国家标准学会，ISO的成员之一)维护。
• SQL标准和SQL方言：普通话和地方方言

• 组成：
  　　- DQL：数据查询语言
  　　- DML：数据操作语言
  　　- DDL：数据定义语言
  　　- DCL：数据控制语言
  　　- TPL：事务处理语言
  　　- CCL：指针控制语言

二、安装配置

MySQL安装步骤

三、验证是否成功

mysql -u root -p

特别注意：MySQL在非window系统下，是严格区分大小写的。

四、DDL：数据定义语言（可视化工具）

作用：用于描述数据库中要存储的现实世界实体的语言。即创建数据库和表的结构。
常用关键字：
CREATE ALTER DROP TRUNCATE

练习demo1

创建数据库

查看当前有哪些数据库
SHOW DATABASES;

创建一个名称为mydb11的数据库.
　　CREATE DATABASE mydb11; //数据库使用的是什么字符集。默认之前配置的字符集

查看数据库创建细节
SHOW CREATE DATABASE mydb11;

创建一个使用gbk字符集的mydb22数据库。

创建一个使用gbk字符集，并带校对规则的mydb33数据库。(gbk默认校验gbk_chinese_ci，查询API获取，效果同上)

CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci;

练习demo2

常见数据类型：

表属于某个库的，创建表之前，必须先选择库。
查看当前选择的库
　　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%’;

练习demo

插入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

先创建一个学生表：

create table student(
    id int,
    name varchar(20),
    chinese float,
    english float,
    math float
);

insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'李进',67,98,56);
insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'李一',88,98,90);
insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);

查询表中所有学生的信息。
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一对一

八、多表查询

链接查询
    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='李宗瑞');

联合查询：
联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。联合查询使用union关键字
SELECT * FROM orders WHERE price>200 UNION 
SELECT * FROM orders WHERE customer_id=1;

十、报表查询

统计一个班级共有多少学生？
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的数据导出和导入

导出：
    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

直接新打开一个DOS窗口直接输入命令