数据库及MYSQL基础(2)

数据库及MySQL基础（1）

SQL进阶及查询练习

1，单表的查询练习

SELECT * FROM emp WHERE deptno=30;

SELECT ename,empno,deptno FROM emp WHERE job='销售员';

SELECT * FROM emp WHERE comm>sal;

SELECT * FROM emp WHERE comm>sal*0.6;

SELECT * FROM emp WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='经理');

SELECT * FROM emp WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='经理') OR (job NOT IN('经理' , '销售员') AND sal>=20000);

SELECT * FROM emp WHERE comm IS NULL OR comm<1000;

SELECT * FROM emp WHERE ename LIKE '___';//模糊查询

SELECT * FROM emp WHERE hiredate LIKE '2000-%';//类似正则表达式

SELECT * FROM emp ORDER BY empno;

SELECT * FROM emp ORDER BY sal DESC,hiredate ASC;

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

SELECT job,MAX(sal),MIN(sal),COUNT(*) FROM GROUP BY job;

2，mysql编码问题

SHOW VARIABLES LIKE 'char%';

character_set_client=utf8;无论客户端发送的是什么编码的数据，mysql都当成是utf8的数据

>若服务器发送的是GBK，服务器会当成utf8对待，总结：必然会乱码

处理问题的手段有两种：

①让客户端发送utf8的数据（行不通）

②把character_set_client修改为gbk,set character_set_client=gbk;只在当前窗口有效！

character_set_results=utf8把数据用什么编码发给客户端

①让服务器发送gbk的数据：set character_set_results=gbk;

②让小黑屏使用utf8来解读（行不通）

my.ini【windows】my.cnf【linux】

在总配置文件中进行配置，一劳永逸

#[mysqld]标记非常重要

[mysqld]

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 一般配置选项

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

port = 3306

socket = /var/run/mysqld/mysqld.sock

character-set-server=utf8

[mysql]

no-auto-rehash

default-character-set=utf8

safe-updates

socket = /var/run/mysqld/mysqld.sock

3，mysql备份与恢复数据

数据库-->sql语句--------->sql语句-->数据库。

①数据库导出SQL脚本(备份数据库内容)

>mysqldump -uroot -p密码数据库名>生成的sql脚本的路径

>例如：mysqldump -uroot -p123 mydb1>C:\mydb1.sql (与mysql.exe和mysqld.exe一样，都在bin目录之下)

>注意，不要打分号，不要登录mysql，直接在cmd下运行

>注意，生成的脚本文件中不包含CREATE DATABASE语句【只复制内容不复制数据库】

②【1】执行SQL脚本

>先删除数据库，再重新创建CREATE DATABSE mydb1;退出

>cmd下执行mysql -uroot -p密码数据库<脚本文件路径

>例如：mysqldump -uroot -p123 mydb1<C:\mydb1.sql;

②【2】

>先登录mysql，删除DROP DATABSE mydb1;后重新创建数据库

>切换到要复制的数据库

>SOURCE SQL脚本路径;

4，约束之主键约束

主键特点：非空、唯一、被引用(学习外键时)

①设置数据表主键

CREATE TABLE stu(

sid CHAR(6) PRIMARY KEY,

sname VARCHAR(20),

age INT,

gender VARCHAR(10),

);

CREATE TABLE stu(

sid CHAR(6),

sname VARCHAR(20),

age INT,

gender VARCHAR(10),

PRIMARY KEY(sid)

);

ALTER TABLE emp{

ADD-->添加列

MODIFY-->修改列名和列类型

CHANGE-->修改列名

DROP-->删除列

RENAME TO-->修改表名;

②修改数据表主键

ALTER TABLE emp DROP PRIMARY KEY;

ALTER TABLE emp ADD PRIMARY KEY(ename);

5，主键自增长

由于主键的三大特点【非空、唯一、引用】，所以我们通常会指定主键类为整型，然后设置其自动增长，这样可以保证在插入数据的时候主键列的唯一和非空性。

CREATE TABLE stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20),

age INT,

gender VARCHAR(10)

);//修改主键自增长

INSERT INTO stu VALUES(1,'万雨',26,'男');

INSERT INTO stu VALUES(NULL,'冯冯',26,'女');

备注：不建议使用有意义的号码来当主键，应该单独使用一个独立的自然数来当主键；并且主键自增长在群集系统中由于网络延迟的问题，导致使用同一个主键会出现问题；建议使用UUID来当主键。

6，非空和唯一约束

CREATE TABLE stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL,//重复可以只是NULL不行

age INT,

gender VARCHAR(10)

);

CREATE TABLE stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL UNIQUE,//①非空②唯一，但是不等同于主键【差引用】

age INT,

gender VARCHAR(10)

);

7，概述模型、对象模型、关系模型

对象模型：可以双向关联，而且引用的是对象，而不是一个主键！

关系模型：只能多方引用一方，而且引用的只能是主键，而不是一整行记录。

在Java中domain！例如：User、Student就等同于数据库中的数据表

Java中的类的关系【①is a继承(1对1、1对多、多对多；少是主，多是从主从),②has a成员的形式,③use a方法对象】

class Employee{//多方关联一方

...

private Department department;

}

class Department{//一方关联多方

...

private List<Employee> employees;

}

class Husband{//一方对应一方

...

private Wife wife;

}

class Wife{//一方对应一方

...

private Husband husband;

}

class Student{//多对多

...

private List<Teacher> teachers;

}

class Teacher{//多对多

...

private List<Student> students;

}

8，外键约束

外键必须是①另一个表的主键的值（外键要引用主键！）；②可以重复；③可以为空；

用户、主体帖、回复帖

1，作者：用户

2，主贴：主题

外键约束初始化

CREATE TABLE emp(

empno INT PRIMARY KEY AUTO_INCREMENT,

ename VARCHAR(50),

dno INT,

CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno)//外键

);

设置修改【添加】外键约束

ALTER TABLE emp

ADD CONTRIAINT fk_emp_dept FOREIGN KEY(dno) dept(deptno);

从表的主键即是外键。

9，一对一关系

10，多对多关系

多对多需要关联表【中间表】

CREATE TABLE stu_tea(

sid INT,

tid INT,

CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(sid),

CONSTRAINT fk_teacher FOREIGN KEY(tid) REFERENCES teacher(tid)

);

INSERT INTO stu_tea VALUES(1,1);

INSERT INTO stu_tea VALUES(2,1);

INSERT INTO stu_tea VALUES(3,1);

INSERT INTO stu_tea VALUES(4,1);

INSERT INTO stu_tea VALUES(5,1);

INSERT INTO stu_tea VALUES(2,2);

INSERT INTO stu_tea VALUES(3,2);

INSERT INTO stu_tea VALUES(4,2);

INSERT INTO stu_tea VALUES(3,3);

INSERT INTO stu_tea VALUES(4,3);

INSERT INTO stu_tea VALUES(5,3);

11，合并结果集

多表查询：①合并结果集；②连接查询；③子查询。

CREATE TABLE ab(a INT,b VARCHAR(50));

INSERT INTO ab VALUES(1,'1');

INSERT INTO ab VALUES(2,'2');

INSERT INTO ab VALUES(3,'3');

CREATE TABLE cd(c INT,d VARCHAR(50));

INSERT INTO cd VALUES(1,'1');

INSERT INTO cd VALUES(2,'2');

INSERT INTO cd VALUES(3,'3');

SELECT * FROM ab UNION SELECT * FROM cd;//无视重复行列全部显示

SELECT * FROM ab UNION ALL SELECT * FROM cd;//将重复行列只显示单次

12，连接查询之内连接【方言】

分类：①内连接；②外连接【左、右、全（mysql不支持）】

方言【只是MYSQL有效】

SELECT * FROM husband,wife;

+-----+--------+-----+------------------+

| hid | hname | wid | wname |

+-----+--------+-----+------------------+

| 1 | 刘备 | 1 | Arya Stark |

| 2 | 关羽 | 1 | Arya Stark |

| 3 | 张飞 | 1 | Arya Stark |

| 1 | 刘备 | 2 | Sansa Stark |

| 2 | 关羽 | 2 | Sansa Stark |

| 3 | 张飞 | 2 | Sansa Stark |

| 1 | 刘备 | 3 | Cersei Lannister |

| 2 | 关羽 | 3 | Cersei Lannister |

| 3 | 张飞 | 3 | Cersei Lannister |

+-----+--------+-----+------------------+

笛卡尔积{a,b,c}{1,2}={a1,a2,b1,b2,c1,c2}

mysql> SELECT husband.hname [AS '丈夫姓名' ],wife.wname [AS '妻子名字']

-> FROM husband,wife

-> WHERE husband.hid=wid;

+--------+------------------+

| hname | wname |

+--------+------------------+

| 刘备 | Arya Stark |

| 关羽 | Sansa Stark |

| 张飞 | Cersei Lannister |

+--------+------------------+

标准语句：

mysql> SELECT h.hname [AS '丈夫姓名' ],w.wname [AS '妻子名字']

-> FROM husband h INNER JOIN wife w

-> ON h.hid=w.wid;

自然语句：

mysql> SELECT h.hname [AS '丈夫姓名' ],w.wname [AS '妻子名字']

-> FROM husband h NATUAL JOIN wife w

13，连接查询之外连接【标签与自然】

/*外连接有一主一次，左外即左表为主，右表为次；左表中所有记录无论满足与否全部都打印，不满足的使用默认值补位。*/

①左外