MYSQL数据库指令及各种查询语句合集

无论使用哪一种编程语言…多多少少都离不开与数据库的操作，有甲骨文的oracle，微软的sql server，而今天我们使用的是MYSQL数据库…小编在学习Hibernate之余，重温了数据库的一些操作…陈列出来，互励共勉！！！

一.DDL：数据定义语言：
查看当前选择的数据库

mysql>SELECT DATABASE();

选择数据库

mysql>USE test;

创建一个员工表

mysql>CREATE TABLE employee(
        id int(11),
        name varchar(100),
        gender varchar(100),
        birthday date,
        entry_date date,
        job varchar(100),
        salary float(8,2),
        resume text
        );

查看数据库中的所有表格

mysql>SHOW TABLES;

查看表结构

mysql>DESC employee;

在上面员工表的基本上增加一个image列。

mysql>ALTER TABLE employee ADD image blob;

修改job列，使其长度为60。

mysql>ALTER TABLE employee MODIFY job varchar(60);

删除image列。

mysql>ALTER TABLE employee DROP image;

表名改为user。

mysql>RENAME TABLE employee TO user;

查看表的创建细节

mysql>SHOW CREATE TABLE user;

修改表的字符集为gbk

mysql>ALTER TABLE user CHARACTER SET gbk;

列名name修改为username

mysql>ALTER TABLE user CHANGE name username varchar(100);

二、DML：数据操作语言
DML：Data Manipulation Language
作用：用于向数据库表中插入、删除、修改数据
常用关键字：
INSERT UPDATE DELETE
MySQL中：字符串值用单引号引起来；日期时间也是用单引号引起来； ‘2014-07-09’

举例：
使用insert语句向表中插入三个员工的信息

    mysql>INSERT INTO user (id,username,gender,birthday,entry_date,job,salary,resume) VALUES (1,'LXC','female','2000-08-12','2014-06-20','CEO',20000,'BEAUTY');
    mysql>INSERT INTO user VALUES (3,'WJC','female','1990-08-12','2014-06-20','CTO',20000,'GUO');
    mysql>INSERT INTO user (id,username,gender,birthday,entry_date,job,salary,resume) VALUES (4,'小齐','female','1995-08-12','2014-06-20','董事长',20000,'小美女');

查询表中的所有记录和字段

mysql>SELECT * FROM user;

查看数据库使用的编码细节：

mysql>SHOW VARIABLES LIKE 'character%';

告知服务器客户端使用的编码

mysql>SET character_set_client=gbk;

告知服务器会送的查询结果的编码

mysql>SET character_set_results=gbk;

将所有员工薪水修改为5000元。

mysql>UPDATE user SET salary=5000;

将姓名为’FXF’的员工薪水修改为4999元。

mysql>UPDATE user SET salary=4999 WHERE username='FXF';

将姓名为’WJC’的员工薪水修改为4999.99元,job改为ccc。

mysql>UPDATE user SET salary=4999.99,job='技术全监' WHERE username='WJC';

将LXC的薪水在原有基础上增加1000元。

mysql>UPDATE user SET salary=salary+1000 WHERE username='LXC';

删除表中名称为’FXF’的记录。

mysql>DELETE FROM user WHERE username='FXF';

删除表中所有记录。（一条条的删除）

msyql>DELETE FROM user;

利用DDL中的TRUNCATE删除整张表(摧毁的表结构，然后重新创建该表)

mysql>TRUNCATE TABLE user;

三、DQL：单表查询
查询表中所有学生的信息。

mysql>SELECT * FROM student;
mysql>SELECT id,name,chinese,english,math FROM student;

查询表中所有学生的姓名和对应的英语成绩。

mysql>SELECT name,english FROM student;

过滤表中重复数据。

mysql>SELECT DISTINCT english FROM student;

在所有学生数学分数上加10分特长分。

mysql>SELECT name,math+10 FROM student;

统计每个学生的总分。

mysql>SELECT name,chinese+english+math FROM student;

使用别名表示学生分数。

mysql>SELECT name AS 姓名,chinese+english+math 总分 FROM student;

查询姓名为wu的学生成绩

mysql>SELECT * FROM student WHERE name='王五';

查询英语成绩大于90分的同学

mysql>SELECT * FROM student WHERE english>90;

查询总分大于200分的所有同学

mysql>SELECT * FROM student WHERE (chinese+english+math)>200;

查询英语分数在 80－90之间的同学。

mysql>SELECT * FROM student WHERE english BETWEEN 80 AND 90;

查询数学分数为89,90,91的同学。

mysql>SELECT * FROM student WHERE math IN (89,90,91);

查询所有姓李的学生成绩。

mysql>SELECT * FROM student WHERE name LIKE '李%';

查询数学分>80，语文分>80的同学。

mysql>SELECT * FROM student WHERE math>80 AND chinese>80;

对数学成绩排序后输出。

mysql>SELECT name,math FROM student ORDER BY math;

对总分排序后输出，然后再按从高到低的顺序输出（排序字段可以使用别名）

mysql>SELECT name,chinese+english+math 总分 FROM student ORDER BY chinese+english+math DESC;

对姓李的学生成绩排序输出

mysql>SELECT name,chinese+english+math 总分 FROM student WHERE name LIKE '李%' ORDER BY 总分;

分页查询:查询姓李的学生总成绩最高的是谁

mysql>SELECT name,chinese+english+math 总分 FROM student WHERE name LIKE '李%' ORDER BY 总分 LIMIT 0,1;

**LIMIT M,N;  M开始记录的索引，从0开始。N是一次取几条。**

四、完整性约束
1、实体完整性（行完整性约束）
作用：唯一定位一条记录
实现：PRIMARY KEY 定义主键。主键的特点是唯一并且不能为空。
写法一：

CREATE TABLE t1(
            id int PRIMARY KEY,
            name varchar(100),
            resume varchar(100)

    );
    写法一：

CREATE TABLE t2(
            id int,
            name varchar(100),
            resume varchar(100),
            PRIMARY KEY(id)
        );

让整数类型的主键自动增长（Oracle并不支持）

CREATE TABLE t3(
            id int AUTO_INCREMENT,
            name varchar(100),
            resume varchar(100),
            PRIMARY KEY(id)
        );

 **主键：**
             逻辑主键：并没有实际含义，只是用于唯一确定一条记录用的。（推荐）
                业务主键：有一定的实际含义。身份证号码作为主键。

2、域完整性（列完整性）
其他约束：
NOT NULL 非空约束
UNIQUE 唯一约束

CREATE TABLE customer(
            id int PRIMARY KEY,
            username varchar(100) UNIQUE NOT NULL,
            password varchar(100) NOT NULL
        );

3、参照完整性：多表（重点）
外键
多表的设计
一对多：

CREATE TABLE department(
                    id int primary key,
                    name varchar(100),
                    floor int
                );

CREATE TABLE employee(
                    id int primary key,
                    name varchar(100),
                    salary float(8,2),
                    dept_id int,
                    CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(id)
                );

多对多：

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(100)
                );
                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)
                );

一对一：

CREATE TABLE person(
                    id int primary key,
                    name varchar(100)
                );
                CREATE TABLE idcard(
                    id int primary key,
                    num varchar(18),
                    CONSTRAINT p_id_fk FOREIGN KEY(id) REFERENCES person(id)
                );

五、DQL：多表查询
语法结构：
SELECT * FROM 表1 [连接类型] 表2 [ON 连接条件] [WHERE 进一步的筛选]

1、交叉连接：（CROSS JOIN)
返回多张表中的记录的笛卡尔积（数据有不对的）
2、内连接：
隐式内连接：(不适用连接关键字)

SELECT * FROM customer c,orders o WHERE c.id=o.customer_id;

显式内连接：（使用连接关键字）

SELECT  c.name,c.city,o.order_number,o.price FROM customer c INNER JOIN orders o ON c.id=o.customer_id;

3、外连接：（OUTER JOIN）
    左外连接：（LEFT OUTER JOIN）  返回满足连接条件的所有记录，同时还返回左表中没有满足连接条件的其余记录。
        查询所有客户信息，如果有订单还要显示订单信息

SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id;

   **右外连接：（RIGHT OUTER JOIN）**

4、子查询（简单）
又叫做嵌套查询。子查询的语句必须放在小括号之内

查询“陈某某”的订单详细内容：

    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='李某某');

六、MySQL中的常用函数
统计一个班级共有多少学生？

    mysql>SELECT COUNT(*) FROM student;
    mysql>SELECT COUNT(id) FROM student;

统计数学成绩大于90的学生有多少个？

    mysql>SELECT COUNT(id) FROM student WHERE math>=90;

统计总分大于250的人数有多少？

    mysql>SELECT COUNT(id) FROM student WHERE (math+english+chinese)>=250;

统计一个班级数学总成绩？

mysql>SELECT SUM(math) FROM student;

统计一个班级语文、英语、数学各科的总成绩

mysql>SELECT SUM(math),SUM(chinese),SUM(english) FROM student;

统计一个班级语文、英语、数学的成绩总和

mysql>SELECT SUM(math+english+chinese) FROM student;

统计一个班级语文成绩平均分

mysql>SELECT SUM(chinese)/COUNT(*) FROM student;

求一个班级数学平均分？

mysql>SELECT AVG(math) FROM student;

求一个班级总分平均分

mysql>SELECT AVG(math+english+chinese) FROM student;

特别注意：使用关键字做为表或字段名时，要使用反引号引起来（`）和~在一个按键上
对订单表中商品归类后，显示每一类商品的总价

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、数据备份：
MySQL安装目录\mysqldump

c:/>mysqldump -h localhost -u root -p test>d:/test.sql

2、恢复数据库：
前提：数据库名成必须存在

方式一：进入MySQL

        mysql>use test;
        mysql>source d:/test.sql

方式二：不用进入mysql

        c:/>mysql -u root -p test<d:/test.sql

互励共勉！！！