实战mysql_MySQL实战

第1章 初识MySQL

启动MySQL服务

net start mysql(服务名)

关闭MySQL服务

net stop mysql(服务名)

MySQL登录

mysql -u root -p -P3306 -h127.0.0.1

MySQL退出

mysql > exit;

mysql > quit;

mysql > \q;

修改MySQL提示符

(1)连接客户端时通过参数指定

shell>mysql -u root -p ****** --prompt 提示符

(2)连接上客户端后,通过prompt命令修改

mysql>prompt 提示符

MySQL提示符

MySQL常用命令

显示当前服务器版本:SELECT VERSION();

显示当前时间日期:SELECT NOW();

显示当前用户:SELECT USER();

MySQL语句的规范

1.关键字与函数名称全部大写

2.数据库名称,表名称,字段名称全部小写

3.SQL语句必须以;结尾

创建数据库

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;

查看当前服务器下的数据库列表

SHOW {DATABASES|SCHEMA} [LIKE 'pattern' | WHERE expr];

修改数据库

ALTER {DATABASES|SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name

删除数据库

DROP {DATABASES|SCHEMA} [IF EXISTS] db_name

第2章 数据类型与操作数据表

数据类型

整型

浮点型

日期时间型

字符型

打开数据库

USE db_name

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name (

column_name data_type ,......

);

查看数据表列表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr] ;

查看数据表详细内容

SHOW CREATE TABLE tbl_name ;

查看数据表的结构

SHOW COLUMNS FROM tbl_name;

插入记录

INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...) ;

查找表记录

SELECT * ,FROM tbl_name ;

记录查找

SELECT expr,.... FROM tbl_name ;

空值与非空

NULL ,字段值可以为空

NOT NULL ,字段值禁止为空

自动编号 AUTO_INCREMENT

1.自动编号必须与主键组合使用

2.默认情况下,起始值为1,每次的增量为1.

主键约束 PRIMARY KEY

1.每张数据表只能存在一个主键

2.主键保证记录的唯一性

3.主键自动为NOT NULL

唯一约束 UNIQUE KEY

1.唯一约束可以保证记录的唯一性

2.唯一约束的字段可以为空值

3.每张数据表可以存在多个唯一约束

默认值

当插入记录时,如果没有明确为字段赋值,则自动赋予默认值.

显示索引

SHOW INDEXES FROM tbl_name;

显示索引(网格形式)

SHOW INDEXES FROM tbl_name\G;

第3章 约束以及修改数据表

约束

1.约束保证数据的完整性和一致性

2.约束分为表级约束(针对两个及以上字段)和列级约束(只针对一个字段)

3.约束类型包括:

NOT NULL 非空约束

PRIMARY KEY 主键约束

UNIQUE KEY 唯一约束

DEFAULT 默认约束

FOREIGN KEY 外键约束(保持数据一致性,完整性.实现一对一或一对多关系)

外键约束的要求

1.父表(字表所参照的表)和子表(具有外键列的表)必须使用相同的存储引擎,而且禁止使用临时表

2.数据表的存储引擎只能为InnoDB

3.外键列和参照列必须具有相似的数据类型.其中数字的长度或是否有符号位必须相同;而字符的长度可以不同.

4.外键列和参照列必须创建索引.如果外键列不存在索引的话,MySQL将自动创建索引.

外键约束的参照操作

1.CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行.

2.SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL.如果使用此选项,必须保证子表列没有指定NOT NULL.

3.RESTRICT:拒绝对父表的删除或更新操作.

4.NO ACTION:标准SQL关键字,在MySQL中与RESTRICT相同.

表级约束与列级约束

对一个数据列建立的约束,称为列级约束.

对多个数据列建立的约束,称为表级约束.

列级约束可以在列定义的时候声明,也可以在列定义后声明.表级约束只能在列定义之后声明.

修改数据表-添加/删除列

添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];

添加多列(不能指定位置关系)

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition ,...);

删除列(一列或多列)

ALTER TABLE tbl_name DROP [COLUMN] col_name,DROP [COLUMN] col_name,...;

修改数据表-添加约束

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name,...);

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] UNIQUE[INDEX|KEY] [index_name] [index_type] (index_col_name,...);

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition;

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};

修改数据表-删除约束

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY;

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

修改数据表-修改列定义和更名数据表

修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];

修改列名称,定义

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name col_definition [FIRST | AFTER col_name];

数据表更名

方法1:

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;

方法2:

RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new tbl_name2]...

第4章 操作数据表中的记录

插入记录INSERT

插入记录1(可插入多条数据)

INSERT [INTO] tbl_name [(col_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...

插入记录2(与第一种的区别在于,此方法可以使用子查询(SubQuery),且只能一次插入一条数据)

INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},...

插入记录3(此方法可以将查询结果插入到指定数据表)

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

单表更新记录UPDATE

更新记录(单表更新)

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1 = {expr1|DEFAULT} [,col_name2={expr2|DEFAULT}] ... [WHERE where_condition]

单表删除记录DELETE

删除记录(单表删除)

DELETE FROM tbl_name [WHERE where_codition]

查询表达式解析

查询记录

SELECT select_expr [,select_expr ...]

[

FROM table_references

[WHERE where_condition]

[GROUP BY {col_name|position}] [ASC|DESC],...]

[HAVING where_codition]

[ORDER BY {col_name|expr|position} [ASC|DESC],...]

[LIMIT {[offset,] row_count|row_count OFFSET offset}]

]

查询表达式 select_expr

每一个表达式表示想要的一列,必须有至少一个.

多个列之间以英文逗号分隔.

星号( * )表示所有列.tbl_name.*可以表示命名表的所有列.

查询表达式可以使用[AS] alias_name为其赋予别名.

别名可用于GROUP BY,ORDER BY,HAVING BY或HAVING子句.

WHERE语句进行条件查询

WHERE条件表达式:

对记录进行过滤,如果没有指定WHERE子句,则显示所有记录.在WHERE表达式中,可以使用MySQL支持的函数或运算符.

GROUP BY语句对查询结果进行分组

查询结果分组:

[GROUP BY{col_name | position} [ASC|DESC],...]

HAVING:

分组条件:

[HAVING where_condition]

ORDER BY:

对查询结果进行排序:

[ORDER BY {col_name|expr|position} [ASC|DESC],...]

LIMIT:

限制查询结果返回的数量:

[LIMIT{[offset,] row_count|row_count OFFSET offset}]

第5章 子查询与连接

子查询

子查询是指出现在其他SQL语句内的SELECT语句.

例如:

SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

其中SELECT * FROM t1,称为 Outer Query/Outer Statement,SELECT col2 FROM t2,称为SubQuery.

子查询指嵌套在查询内部,且必须始终出现在圆括号内.

子查询可以包含多个关键字和条件,如DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等.子查询的外层查询可以是:SELECT,INSERT,UPDATE,SET或DO.

子查询返回值

子查询可以返回标量,一行,一列或子查询.

使用比较运算符的子查询

使用比较运算符的子查询 =,>,=,<=,<>,!=,<=>

语法结构 operand comparition_operator subquery

使用ANY,SOME或ALL修饰的比较运算符

perand comparition_operator ANY (subquery)

perand comparition_operator SOME (subquery)

perand comparition_operator ALL (subquery)

ANY,SOME,ALL关键字:

使用[NOT] IN的子查询

语法结构

perand comparition_operator [NOT] IN (subquery) =ANY运算符与IN等效.

!=ALL或<>ALL运算符与NOT IN等效.

使用[NOT] EXISTS的子查询

如果子查询返回任何行,EXISTS将返回TRUE;否则为FALSE.

将查询结果写入数据表

INSERT [INTO] tb_name [(col_name,...)] SELECT ...

多表更新

UPDATE table_references

SET col_name1={expr1|DEFAULT}

[,col_name2={expr2|DEFAULT}] ...

[WHERE where_condition]

语法结构:

table_references

{[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}

table_references

ON conditional_expr

创建数据表的同时将查询的结果写入到数据表(CREATE...SELECT)

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement

连接

MYSQL在SELECT语句,多表更新,多表删除语句中支持JOIN操作.

数据表参照

table_references

tbl_name [[AS] alias] | tbl_subquery [AS] alias]

数据表可以使用tbl_name AS alias_name 或 tbl_name alis_name 赋予别名.

tbl_subquery 可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名.

连接类型

INNER JOIN,内连接,在MYSQL中,JOIN,CROSS JOIN和INNER JOIN是等价的.LEFT [OUTER] JOIN是左外链接,RIGHT [OUTER] JOIN是右外连接.

连接条件

使用ON关键字来设定连接条件,也可以使用WHERE来代替.通常使用ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤.

内连接

显示左表及右表符合连接条件的记录.

左外连接

显示左表的全部记录及右表符合连接条件的记录.

左外连接

显示左表全部记录及右表符合条件的记录

右外连接

显示右表的全部记录及左表符合连接条件的记录

外连接

A LEFT JOIN B join_condition

数据表B的结果集依赖数据表A.

数据表A的结果集根据左连接条件依赖所有数据表(B表除外).

左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下).

如果数据表A的某条记录符合WHERE条件,但是在数据表B不存在符合连接条件的记录,将生成一个所有列为空的额外B行.

如果使用内连接查找的记录在连接数据表中不存在,并且在WHERE子句中尝试以下操作:col_name IS NULL时,如果col_name被定义为NOT NULL,MySQL将在找到符合连接条件的记录后停止搜索更多行.

自身连接

同一个数据表对其自身进行连接.

多表删除

DELETE tbl_name [ .* ] [,tbl_name[ .* ]] ...

FROM table_references

[WHERE where_codition]

第6章 运算符和函数

字符函数

数值运算符与函数

比较运算符与函数

日期时间函数

信息函数

聚合函数(只有一个返回值)

加密函数

第7章 自定义函数

自定义函数

用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同.

自定义函数的两个必要条件:

(1) 参数

(2) 返回值

函数可以返回任意类型的值,同样也可以接受这些类型的参数.

创建自定义函数

CREATE FUNCTION finction_name

RETURNS {STRING|INTEGER|REAL|DECIMAL}

routine_body

函数体(routine_body):

(1)函数体由合法的SQL语句构成;

(2)函数体可以是简单的SELECT或INSERT语句;

(3)函数体如果为复合结构则使用BEGIN...END语句;

(4)复合结构可以包含声明,循环,控制结构;

删除函数

DROP FUNCTION [IF EXISTS] function_name;

第8章 MySQL存储过程

存储过程

存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理

存储过程的优点

(1)增强SQL语句的功能和灵活性

(2)实现较快的执行速度

(3)减少网络流量

创建存储过程

CREATE

[DEFINER = {user|CURRENT_USER}]

PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

proc_parameter:

[IN|OUT|INOUT] param_name_type

IN:表示该参数的值必须在调用存储过程时指定.

OUT:表示该参数的值可以被存储过程改变,并且可以返回

INOUT:表示该参数在调用时指定,并且可以被改变和返回.

过程体:

(1)过程体由合法的SQL语句构成;

(2)过程体可以是任意的SQL语句;

(3)过程体如果为复合结构则使用BEGIN...END语句;

(4)复合结构可以包含声明,循环,控制结构;

调用存储过程

CALL sp_name ([proc_parameter[,...]])

CALL sp_name[()]

修改存储过程

ALTER PROCEDURE

sp_name [characteristic ...]

COMMENT 'string'

|{CONTAINS SQL|NO SQL|READS SQL DATA | MODIFIES SQL DATA}

| SQL SECURITY {DEFINER | INVOKER}

删除存储过程

DROP PROCEDURE {IF EXISTS} sp_name

存储过程与自定义函数的区别

(1)存储过程实现的功能要复杂一些,而函数的针对性更强

(2)存储过程可以返回多个值,函数只能有一个返回值.

(3)存储过程一般独立的来执行,而函数可以作为其他SQL语句的组成部分来出现.

注意事项

创建存储过程或自定义函数时需要通过delimiter语句修改定界符.

第9章 MySQL存储引擎简介

存储引擎

MySQL可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎.

每一种存储引擎使用不同的存储机制,索引技巧,锁定水平,最终提供广泛且不同的功能.

MySQL支持的存储引擎

--MyISAM

--InnoDB

--Memory

--CSV

--Archive

并发控制

当多个连接对记录进行修改时保证数据的一致性和完整性.

锁

--共享锁(读锁):在同一个时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何的变化.

--排他锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或写锁操作.

锁颗粒

--表锁,是一种开销最小的锁策略.

--行锁,是一种开销最大的所策略.

事务

事务用于保证数据库的完整性.

事务的特性(ACID)

--原子性(Atomicity)

--一致性(Consistency)

--隔离性(Isolation)

--持久性(Durability)

外键

是保证数据一致性的策略.

索引

是对数据表中一列或多列的值进行排序的一种结构.包括普通索引,唯一索引,全文索引,btree索引,hash索引...

各种存储引擎的特点

修改存储引擎的方法

通过修改MySQL配置文件实现

default-storage-engine = engine

通过创建数据表命令实现

CREATE TABLE table_name(

...

) ENGINE = engine;

通过修改数据表命令实现

ALTER TABLE table_name ENGINE [=] engine_name;

第10章 MySQL图形化管理工具

管理工具

---PHPMyAdmin

---Navicat

---MySQL Workbench