Mysql入门

RDBMS-Relational database ase management system 数据库服务器 关系型数据库管理系统

RDBMS = 管理员（manager）+仓库（database）

database = N个table

table：

表结构：定义表的列名和列类型

表记录：一行一行的记录

SQL

1 什么是SQL：结构化查询语言（Structured  Query Language）

2 SQL作用：客户端使用sql来操作服务器

启动mysql.exe，连接服务器，就可以使用sql来操作服务器了

将来使用java程序连接服务器。然后使用sql来操作服务

3  SQL标准（例如SQL99，即1999制定的标准）

由国际标准化组织制定，对DBMS的统一操作方式（例如相同的语句可以操作：mysql oracle）

4 SQL方言

某种DBMS不只不会支持SQL标准，而且还会有一些自己独有的语法，这个称之为方言，例如limit语句只有Mysql可以用

SQL语法

1 SQL语句可以在单行或多行书写，以分号结尾

2 可使用空格和缩进来增强语句的可读性

3 MySql不区别大小写建议使用大写

SQL语句分类

1DDL 数据定义语言，用来定义数据库对象：库，表，列等

创建 删除 修改：库 表结构

2 DML 数据操作语言，用来定义数据库记录

增 删 改：表记录

3 DCL 数据控制语言，用来定义访问权限和安全级别

4 DQL 数据查询语言，用来查询记录（数据）

DDL

1 数据库

查看所有数据库：SHOW DATABASES

切换（选择要操作的）数据库：USE 数据库名

创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1 [CHARSET=utf8] (方括号不加)

删除数据库：DROP DATABASE [IF EXISTS] mydb1

修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

2 数据类型

int  整型

double 浮点型 例如double(5,2) 表示最多五位，其中必须有两位小数，即最大值为999.99；

decimal:浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题

char：固定长度字符串类型：char(255),最大255，数据长度不足指定长度，补足到指定长度

varchar:可变长度字符串类型：varchar(65535),zhangsan

text(clob):字符串类型

很小 tinytext

小 text

中 mediumtext

大 longtext

blob：字节类型

很小

小

中

da

date：日期类型，格式为 yyyy-mm-dd

time:时间类型，格式为:hh:mm:ss

timestamp：时间戳类型

3 表

创建表

CREATE TABLE [IF NOT EXISTS] 表名 （

列名 列类型，

列名 列类型，

....

）;

查看当前数据库所有表名称：SHOW TABLES;

查看指定表的创建语句：SHOW CREATE TABLE 表名（了解）

查看表结构：DESC 表名

删除表：DROP TABLE 表名

修改表：前缀：ALTER TABLE 表名

修改之添加列：

ALTER TABLES 表名 ADD(

列名 列类型，

.....

);

修改之修改类型（如果被修改的列已存在数据，那么新的类型可能会影响到已存在的数据）ALTER TABLE 表名 MODIEY 列名 列类型

修改之修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列类型

修改之删除列：ALTER TABLE 表名 DROP 列名

修改表名称：ALTER TABLE 原表名 RENAME TO 新表名

DQL: SELECT * FROM emp 查询表记录

DML

1 插入数据

INTERT INTO 表名（列名，列名2.....） VALUES(列值1，列值2.....); 在数据库中所有的字符串类型，必须使用单引，不能使用双引

在表名后给出要插入的列名，其他没有指定的列等同于插入null值，所以插入记录总是插入一行，不可能是半行

在VALUES后给出列值，值得顺序和个数必须与前面指定的列对应

INTERT INTO 表名 VALUES(列值1，列值2)

没有给出要插入的列，那么表示插入所有列

值得个数必须是该表列的个数

值得顺序，必须与表创建时给出的列的顺序相同

2 修改数据

UPDATE 表名 SET 列名1=列值1，列名2=列值2，...[WHERE 条件]  通常都会带where

条件（条件可选的）：

条件必须是一个boolean类型的值或表达式， UPDATE t_person SET gender='男'，age=age+1 WHERE sid='1'

运算符：=  != <> > < >= <= BETWEEN...AND IN(....) IS NULL(使用时只能写IS NULL不能写=NULL) NOT OR AND

3 删除数据  

DELETE FROM 表名 [WHERE 条件]

TRUNCATE TABLE 表名：TRUNCATE是DDL语句，它是先删除drop元素，再create该表。而且无法回滚

DCL 

一个项目创建一个用户！一个项目对应的数据库只有一个

这个用户只能对这个数据库有权限，其他数据库操作不了

 1 创建用户

CREATE USER 用户名@ip地址 IDENTIFIED BY '密码'

用户只能在指定的ip地址上登录

CREATE USER 用户名@‘%’ IDENTIFIED BUY '密码'

用户可以在任意ip地址上登录

2 给用户授权

GRANT 权限1..... ON 数据库.* TO  用户名@IP地址

例如：GRENT CREATE,ALTER ON mydb1,* TO user1@localhost

给user1用户分派mydb1数据库上的CREATE ALTER  权限

GRANT ALL ON 数据库.* TO 用户名@ip地址

给用户分派指定数据库上的所有权限

3 撤销用户

Remove 权限1，.... ON 数据库.* FORM 用户名@ip地址

撤销指定用户在指定数据库上的指定权限

4 查看权限

SHOW GRANTS FOR 用户名@ip地址

查看指定用户的权限

5 删除用户

DROP USER 用户名@ip地址

DQL  数据查询语言

一 字段(列)控制

1 查询所有列

SELECT * FROM 表名  其中*表示查询所有列

2 查询指定列

SELECT 列1 [,列2，....] FORM 表名

3 完全重复的记录只一次

当查询结果中的多行记录一模一样时，只显示一行

SELECT DISTINCT 列名 FROM 表名

4 列运算

1 数据量类型的列可以做加，减，乘，除运算

SELECT sal*1.5 FORM emp

SELECT sal+comm FORM emp

2 字符串类型可以做连续运算

SELECT CONCAT('$',sal) FORM emp

3 转换NULL值

有时需要把NULL转换成其他值，例如com+1000时，如果com列存在NULL值，那么NULL+1000还是NULL

而我们这时希望把NULL当成0来运算

SELECT IFNULL(COMM,0)+1000 FORM emp     

IFNULL(comm,0):如果comm中存在NULL值，那么当成0来运算

4 给列起别名

使用列运算后，查询的结果不好看，这时我们需要给列名起个别名，这样在结果集中列名就显示别名了

SELECT IFNULL(comm，0)+1000 AS 奖金 FROM emp

其中AS可以省略

二  条件控制

1 条件查询

与前面介绍的UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE句子来控制记录

SELECT empno,ename,sal,comm FROM emp WHERE sal >10000 AND comm IS NOT NULL

2 模糊查询

当你想查询姓张，并且姓名一共两个字的员工时，这时就可以使用模糊查询

SELECT * FORM emp WHERE ename LIKE '张_'

模糊查询使用使用运算符，LIKE 其中匹配一个任意字符，注意，只匹配一个字符而不是多个

上面语句查询的是姓张，名字由两个字组成的员工

SELECT * FROM emp WHERE ename LIKE '___'   姓名由三个字足蒸的员工 (几个字几个下划线)

如果我们想查询姓张，名字几个字都可以的员工就要使用"%"了

SELECT * FORM emp WHERE ename LIKE '张%'

其中%匹配0-N个字符，所以上面语句查询的是姓张的所有员工

SELECT * FORM emp WHERE ename LIKE '%阿%

查询带有阿或者开头结尾为阿的

SELECT * FORM emp WHERE ename LIKE '%'

这个条件等同于不存在，但如果姓名为NULL的查询不出来

二 排序

1 升序

SELECT * FROM  表名 ORDER BY 列名 ASC

2 降序

SELECT * FROM 表名 ORDER BY 列名 DESC

3 使用多列作为排序条件

SELECT * FROM  表名 ORDER BY 列名 ASC,列名 DESC....  (比较结果相同时，使用后面的进行比较)

三 聚合函数 

用来做某列的纵向运算

SELECT 函数(列名) FORM 表名

1 COUNT 行数 列名为* 表示计算表中所有列不全为NULL的记录的行数

2 MAX 最大值

3 MIN 最小值

4 SUM 和

5 AVG 平均

四 分组查询

分组查询是把记录使用某一队列进行分组，然后查询信息

例如：查看所有部门的记录数

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

使用deptno分组，查询部门编号和每个部门的记录数

SELECT job, MAX(SAL) FORM emp GROUP BY job

使用job分组，查询每种工作的最高工资

组条件：以部门分组，查询每组记录数。条件为记录数大于3

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3

select from where group by having order by(执行顺序)

五 limit字句（方言）

LIMIT用来限定查询结果的起始行，以及总行数

例如：查询起始行为第5行，一共查询三行记录

SELECT * FROM emp LIMIT 4,3

其中4表示从第5行开始，其中3表示一共查询3行。即第5 6 7行记录

mysql编码问题

character set_client=utf-8,无论客户端发送的什么编码的数据，mysql都当成是utf8的数据

若客户端发送的是GBK

服务器会当成utf8对待

必然乱码

处理问题的手段有两种：

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

把character_set_client修改为gbk   set character set_clien=gbk 只在当前窗口内有效

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

若服务器发送给客户端的是utf8的数据

客户端会把它当成gbk，因为小黑屏，只能显示gbk

必然乱码

处理问题的手段有两种：

让服务器发送gbk的数据，set character_set_results=gbk

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

my/ini中配置

default-character-set=gbk

备份与恢复

数据库-->sql语句

sql语句-->数据库

1 数据库导出sql脚本(备份数据库内容并不是数据库)

mysqldump -uroot -p密码 数据库名>路径

注意没有分号，不登录，在cmd下运行

注意生成的脚本文件中不包含create database语句

2 执行SQL脚本（创建要恢复的库）

1 mysql -u -p密码 数据库名<脚本文件路径（不登录操作）

2 登录mysql

source SQL脚本路径

约束：约束是添加在列上的，用来约束列的

1 主键的约束（唯一标识）

特点：非空 唯一 被引用

当表的某一列被指定为主键后，该列就不能为空，不能有重复值出现

创建表时指定主键的两种方式

1 

CREATE TABLE stu(

sid        CHAR(6)  PRIMARY KEY,

......

)

指定sid列为主键列，即为sid列添加主键约束

2

CREATE TABLE stu(

sid          CHAR(6),

......

 PRIMARY KEY(sid)

)

  指定sid列为主键列，即为sid列添加主键约束

修改表时指定主键：ALTER TABLE stu ADD PRIMARYKEY(sid)

删除主键：ALTER TABLE stu DROP PRIMARYKEY

2 主键自增长

因为主键的特性是唯一，不能为空，所以我们通常会指定主键类型为整型，然后设置其自动增长

创建表时设置自增长

CREATE TABLE stu(

sid        CHAR(6)  PRIMARY KEY AUTO_INCREMENT,

......

)

3 非空约 因为某些列不能设置NULL值，所以可以对列添加非空约束NOT NULL

4 唯一约束

某些列不能设置重复的值，所以可以对列添加唯一约束 UNIQUE

5 概念模型

当我们要完成一个软件系统时，需要把系统中的实体抽取出来，形成概念模型

实体之间还存在着关系：

一对多；

一对一；

多对多；

概念模型在java中成为实体类（javaBean）类就使用成员变量来完成关系，一般都是双向关联

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

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

6 外键约束

外键必须是另一表的主键的值（外键要引用主键）

外键可以重复

外键可以为空

一张表中可以有多个外键

语法：

CONSTRAINT 约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表（关联表的主键）

创建表时指定外键约束

修改表时添加外键约束

ALERT TABLE emp

ADD CONSTRAINT fk_emp_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)

修改表时删除外键约束

ALTER TABLES emp

DROP FOREIGN KEY fk_emp_deptno(外键名称)

7 数据库中建立一对一关系

在表中建立一对一关系，比较特殊，需要让其中一张表的主键，既是主键又是外键

8 数据库多对多关系

在表中建立多对多关系需要使用中间表，即需要三张表，在中间表中使用两个外键，分别引用其他两个表的主键

多表查询：

1 分类：

1 合并结果集

2 连接查询

3 子查询

合并结果集

要求被合并的表中，列的类型和列数相同

UNION,去除重复行

UNION ALL 不去除重复行

连接查询

1 分类

内连接

外连接

左外连接

右外连接

全外连接（不支持）

自然连接

2 内连接

方言：SELECT * FROM 表1 别名1，表2 别名2 WHERE 别名1.xx=别名2.xx

标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2

内连接查询出的所有记录都满足条件

3 外连接

左外：SELECT * FORM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

左边为主表，右边为次表，主表所有的记录无论满足不满足条件，都打印出来。当不满足条件时，次表使用NULL补位

左外自然：SELECT * FORM表1 别名1NATURALLEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

右外：SELECT * FORM表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

右边为主表，左边为词表，主表所有的记录无论满足不满足条件，都打印出来。当不满足条件时，次表使用NULL补位

右外自然：SELECT * FORM表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

全连接：SELECT * FORM表1 别名1 FULL OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx(不支持) 可以使用UNION来完成连接

子查询

查询中有查询（查看select关键字的个数）

1 出现的位置：

where后作为条件存在

from后作为表存在（多行多列）

2 条件

单行单列：SELECT * FROM 表1 别名1 WHERE 列1 运算符 （SELECT 列 FROM 表2 别名2 WHERE 条件）

多行单列 ：SELECT * FROM 表1 别名1 WHERE 列1 [IN,ALL,ANY]（可与运算符结合使用） (SELECT 列 FROM 表2 别名2 WHERE 条件)

单行多列：SELECT * FROM 表1 别名1 WHERE (列1，列2) IN (SELECT 列1，列2 FROM表2 别名2 WHERE 条件)

多行多列：SELECT * FROM 表1 别名1,(SELECT......) 别名2 WHERE 条件