一.MySQL 程序安装
二. MySQL的使用
以管理员运行cmd命令行启动 mysql 服务
net start mysql
用户登录
mysql -u(用户名) -p(密码)
prompt提示符修改语法：
修改用户密码：
SET PASSWORD = PASSWORD('新密码');修改客户端显示字符集
SET names gbk;//将客户端字符集改为gbk,服务器端不变修改分界符
DELIMITER end_symbol; //end_symbol为自己指定的分界符MySQL语句规范：
关键字和函数名称全部大写
数据库名称、表名称、字段名称全部小写
SQL语句必须以“;”结尾，也可以通过’ DELIMITER 符号‘语句来自已定义结尾符号
SQL标准语法“{ }表示必须要有的选项，“|”表示从中选择其一 “[ ]”表示可以有也可以没有的选项
三.数据库的操作
- 数据库的增、删、改、查
创建数据库
CREATE { DATABASE | SCHEMA } [IF NOT EXISTS ] db_name [DEFAULT] CHARACTER SET [=] charset_name ;
//可以不写字符集 使用默认的字符集 字符集格式 utf8 gbk 等删除数据库
DROP { DATABASE | SCHEMA } [IF EXISTS] db_name修改数据库
ALTER {DATABASE |SCHEMA } [db_name] [DEFAULT] CHARACTER SET [=] charset_name ;
//修改数据库的字符集查看数据库列表
SHOW {DATABASES |SCHEMAS } [ LIKE 'Pattern' | WHERE expr] ;打开数据库
USE db_name ;
- 数据表的增、删、改、查
在数据库里创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,...);
CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,...) SELECT select_statement;
//创建表并将查询结果写入表中，一步到位删除数据表
DROP TABLE table_name;修改数据表
修改表名称
ALTER TABLE old_table_name RENAME [TO|AS] new_table_name;
//或者 RENAME TABLE table_name TO new_table_name;新增列
ALTER TABLE table_name ADD [COLUMN] column_name [FIRST|AFTER column_name];删除列
ALTER TABLE table_name DROP [COLUMN] column_name;修改列的位置及定义（数据类型及约束等）
ALTER TABLE table_name MODIFY [COLUMN] column_name column_definition [FIRST|AFTER column_name];修改列的名称及定义
ALTER TABLE table_name CHANGE [COLUMN] old_column_name new_column_name column_definition [FIRST|AFTER column_name];添加列的primary key|unique key（索引）|not null
ALTER TABLE table_name ADD [CONSTRAINT[symbol]] {PRIMARY KEY|UNIQUE KEY|NOT NULL} [index_type](column_name);//CONSTRAINT[symbol]为给外键起的名字删除列的primary key
ALTER TABLE table_name DROP PRIMARY KEY;//一张表中只能有一个primary key删除列的索引(unique key)
ALTER TABLE table_name DROP {INDEX|KEY} column_name;//一张表格中索引可以有多个，所以需要指出列名删除外键约束（foreign key）
ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;//fk_symbol为系统给外键取得别名，可以通过show columns from table_name 查看contraint后面的名字添加列的foreign key
ALTER TABLE table_name ADD [CONSTRAINT[symbol]] FOREIGN KEY (column_name) REFERENCES table_name (column_name);添加或删除默认值约束
ALTER TABLE table_name ALTER [COLUMN] column_name {SET DEFAULT value|DROP DEFAULT};查看数据表
查看数据表列表
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr] //省略from表示查看当前数据库中的数据表查看数据表的结构
SHOW COLUMNS FROM table_name;//或者是 DESC table_name;
- 数据表中记录的增、删、改、查
插入记录
方法一：
INSERT [INTO] table_name [(column_name,...)] {VALUES|VALUE} (value1,value2,...),(...),...;
//若省略列名称，则需为所有的列字段都赋值否则会出错，赋值顺序必须与列名称顺序一致，一次可以插入一条或者多条记录
方法二：
INSERT [INTO] table_name SET column1_name ={expr|DEFAULT},column2_name ={expr|DEFUALT},...;
//此方法可以使用子查询
方法三：
INSERT [INTO] table_name[(column_name,...)] SELECT...
//将select 后面的子查询结果 插入到指定的数据表中删除记录
DELETE FROM table_name [WHERE where_condition]
//若不写where 条件语句将删除该表中的所有记录修改记录
UPDATE [LOW_PRIORITY] [IGNORE] table_name SET column1_name ={expr|DEFAULT} [,column2_name={expr|DEFAULT}]...[WHERE where_condition];
//可以更新一列，也可以更新多列，如果不写where表达式，所有的记录都将被修改查找记录
SELECT expr[AS alias_name],...FROM table_name [
[WHRER where_condition]
[GROUP BY {column_name|position} [ASC|DESC],...]
[HAVING where_condition]
[ORDER BY{column_name|expr|position}[ASC|DESC],...]
[LIMIT {[offset,] row_count|row_count OFFSET offset}]]];
//expr为表达式表示查询的列名，也可以是*表示查询该表格中的所有列 ，表达式的顺序影响这显示结果列的顺序
as Alias_name 为该列起的别名（当列的名称较长时可以起别名来代替）
where为查询条件表达式可以是函数表达式也可以时运算表达式，若省略这显示所有的记录，
group by 表示分组position为select中列的位置，ASC为升序 DESC为降序，
having 对部分记录做分组，后面必须是聚合函数（返回一个具体的值），在group by 后面起到补充条件的作用，
order by对查询结果进行排序
limit 限制查询结果返回的数量,offset是查询结果的起始记录的index值（第一个从0开始） row_count为返回的记录个数查看索引(Index/key)
SHOW INDEXES FROM table_name \G; // \G表示以网格的形式显示
- 子查询：嵌套在MySQL语句内部的，且必须始终出现在圆括号内的查询 外层查询可以是 SELECT、INSERT、UPDAT、SET、DO等
使用比较运算符的子查询：> = < >= <= <> != <=>
SELECT expr From table_name WHERE column_name >(SELECT expr FROM table_name WHERE expr...);
当子查询的返回结果有多个时，可以使用结果 ANY、SOME、ALL来修饰比较运算符
使用IN、NOT IN 的子查询
IN 与 = ANY的用法和返回结果相同
NOT IN 与 ！=ALL 或<>ALL的用法和返回结果相同
- 多表连接查询、更新、删除（参照另一张表来更新表格的记录，可以是两个或两个以上的表连接，也可以是一张表与其自身连接）
表的参照关系（table_reference）
table1_name {[INNER | CROSS] JOIN | {LEFT |RIGHT} [OUTER] JOIN } table2_name ON conditional_expr
多表连接更新语法：
UPDATE table_reference SET column1_name = {expr1|DEFUALT} [,column2_name = {expr2|DEFAULT}]...[WHERE where_condition];
多表连接查询语法：
SELECT column_name[,column_name]... FROM table_reference [WHERE where_condition];
多表连接删除语法：
DELETE table_name [. * ][,table_name.*]...FROM table_reference [WHERE where_condition];
- 字段约束（在创建数据表时 写在 data_type后面）
UNSIGNED(无符号标识，没有负数)
NULL(字段值可以为空)
NOT NULL(字段值禁止为空)
PRIMARY KEY(或者直接写 KEY ，主键约束，每张表只能存在一个字段主键且该字段自动为NOT NULL，主键约束的字段不能有两个相同的值)
AUTO_INCREMENT(自动编号，必须与primary key 组合使用，默认情况下起始值为1，每次的增量为1)
UNIQUE KEY(唯一约束又叫做添加索引，保证字段不能有相同的值，可以为NULL，每张表格可以存在多个唯一约束)
DEFAULT(默认值，当插入记录是，如没有明确为字段赋值，则自动赋予默认值)
FOREIGN KEY(外键约束，实现一对一或一对多的关系，保持多个数据表中数据的一致性，完整性)
//外键约束的要求：
1.父表和子表必须都使用InnoDB存储引擎，且不能使用临时表
2.外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号为必须相同，字符的长度可以不同
3.外键列和参照列必须创建索引。如果外键列不存在索引则会自动创建索引
外键约束的语法：FOREIGN KEY (column_name) REFERENCES table_name(column_name)
外键约束的参照操作：
CASCADE(父表删除或者更新时，子表对应的外键值也自动删除或者更新)
SET NULL(父表删除或者更新时，子表对应的外键值设置为null(外键列必须设置为可以为null))
RESTRICT(拒绝父表的删除或者更新操作)
NO ACTION(与RESTRICT相同)
外键约束参照操作使用语法：
FOREIGN KEY(column_name) REFERENCES table_name（column_name） ON DELETE SET NULL ON UPDATE CASCADE
//父表删除时，子表对应外键值自动设置为null,父表更新时，子表对应外键值自动更新关系型数据库——外键约束的例子
创建table_provinces表格的语法
创建table_users表格的语法
- 数据库支持的数据格式
整数型
数据类型 存储空间 范围 TINYINT 1 byte 有符号的[-128,127] ; 无符号[0,255] SMALLINT 2 byte 有符号[-2^15,2^15-1] ; 无符号[0,2^16-1] MEDIUMINT 3 byte 有符号[-2^23,2^231-1] ; 无符号[0,2^24-1] INT 4 byte :有符号[-2^31,2^31-1] ; 无符号[0,2^32-1] BIGINT 8 byte 有符号[-2^63,2^63-1] ; 无符号[0,2^64-1]
布尔型
mysql中布尔型用0和1来表示，0 表示，1表示true;
浮点型
数据类型 存储范围 FLOAT[(M,D)] M是数字总位数，D是小数点后面的位数，如省略M和D,根据硬件允许的限制来保存,单精度浮点约小数点后7位 DOUBLE[(M,D)] M是数字总位数，D是小数点后面的位数，如省略M和D,根据硬件允许的限制来保存,双精度浮点
日期时间类型
数据类型 存储空间 日期格式 存储范围 YEAR 1 byte YYYY 1901~2155 TIME] 3 byte HH:MM:SS -838:59:59~838:59:59 DATE] 3 byte YYYY-MM-DD 1000-01-01~9999-12-31 DATETIME 8 byte YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~9999-12-31 23:59:59 TIMESTAMP 4 byte YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00~2038-12-31 23:59:59
字符型 (需要用单引号括起来)
数据类型 存储空间 范围 CHAR(M) 定长 M byte (0<=M<=255) VARCHAR(M)不定长 L+1 byte(L为实际长度) L<=M,0<=M<=65535 TINYTEXT L+1 byte L<2^8 TEXT L+2 byte L<2^16 MEDIUMTEXT L+3 byte L<2^24 LONGTEXT L=4 byte L<2^32 ENUM(‘value2’,’value2’,…) 取决于枚举数 最多65535个枚举值 SET(‘value1’,’value2’…) 取决于成员数 最多64个成员
- mysql常用函数命令：
- 信息函数
表达式 参数描述 效果描述 使用效果 CONNECTION_ID() 无参数 连接线程的ID号 VERSION(); 无参数 显示当前服务器版本 NOW(); 无参数 显示当前日期时间 USER(); 无参数 显示当前用户 DATABASE(); 无参数 显示当前打开的数据库 LAST_INSERT _ID() 无参数，表中必须有id字段且为自增 上一次插入记录的ID号 ROW_COUNT() 无参数 上一条SQL命令影响的记录有多少行 EXISTS(x); x表示SQL命令查询结果,判断是否为null
- 字符函数
表达式 参数描述 效果描述 使用效果 CONCAT(text1,text2,…) text1,text2..为需要链接的文本，可以有多个 字符链接 CONCAT_WS(symbol,text1,text2,…) symbol为分隔字符的符号，text1,text2..为需要链接的文本，可以有多个 使用指定的分隔符进行字符链接 FORMAT(M,N) M为需要格式化的数字，N为保留小数点后面的位数 格式化数字 LOWER(text) text为需要转换为小写的文本 转换为小写 UPPER(text) text为需要转大写的文本 转换为大写 LEFT(str,len) str为提供的文本，len为取左侧字符的长度 获取左侧字符 RIGHT(str,len) str为提供的文本，len为取右侧字符的长度 获取右侧字符 LENGTH(str) str为需获取长度的文本 获取字符串长度 TRIM(str);TRIM(LEADING chars,str);TRIM(TRAILING chars,str);TRIM(BOTH chars,str) str为需要从其中删除字符串的原文本，chars为需要删除的字符串，LEADING为删除文本前面的字符串，TRAILING为删除文本后面的字符串，BOTH为前后字符串都删除，不能删除文本中间的字符串 删除前后的空格或指定的字符串 LTRIM(str) str为需要删除前面空格的文本 删除前面的空格 RTRIM(str) str为需要删除后面空格的文本 删除后面的空格 SUBSTRING(str,pos,len) str为原文本，pos为起始index值（第一位index为1,若是负值则为倒数第几位），len为截取长度 字符串截取 str [NOT] LIKE pat [ESCAPE char] str表示原文本，pat表示含有通配符的匹配文本,“%”表示任意位数的通配符，”_”表示只有1位的通配符,escape char表示匹配字符串中可以忽略掉的一位字符 文本中是否包含某个字符串 REPLACE(str,from_str,to_str) str为原文本，from_str为需要替换的字符，to_str 用于代替的字符 字符串替换
- 数值运算函数
表达式 参数描述 效果描述 使用效果 CEIL(x) x为需要进一取整的数字 进一取整 x DIV y x为被除数，y为除数 除法取整数部分 FLOOR(x) x为需要舍弃小数位的数值 舍一取整 MOD(x,y); x % y; x MOD y x为被除数，y为除数 取余数（取模） POWER(x,y) x为底数，y为幂数 幂运算 TRUNCATE(x,y) x为原数值，y为需保留的小数点后位数，y为负数则整数位保留取零 数字截取 ROUND(x,y) x为原数，y为需保留的小数点后面的位数可以省略,如y为负数则 四舍五入 -比较运算函数
表达式 参数描述 效果描述 使用效果 x [NOT] BETWEEN min AND max x在不在（min,max）之间 [不]在范围之内 str [NOT] IN(value1,value2,…) str可以是数值，也可以是字符串，value1,value2…为列举值 [不]在列出值范围内 str IS [NOT] NULL str为需要判断的引用 [不]为空 - 日期时间函数
表达式 参数描述 效果描述 使用效果 NOW() 无参数 当前日期和时间 CURDATE() 无参数 当前日期 CURTIME() 无参数 当前时间 DATE_ADD(date,INTERVAL x unit ) date为原时间，x为增加（负值为减少）的数值，unit为变化的单位（DAY,SECOND等） 日期变化,增加或减少时间 DATEDIFF(date1,date2) date1,date2相差的天数 日期相差的天数 DATE_FORMAT(date,format) date需要格式化的时期，format为格式(%Y表示年，%m月，%d日，%H时，%i分，%s秒） 日期格式化 - 聚合函数（只有一个返回值）
表达式 参数描述 效果描述 使用效果 AVG(column_name) 求平均值用于表格中的计算 平均值 COUNT(column_name) 用于表格中的计算该字段记录求数量 数量 MAX(column_name) 用于表格中的计算求该字段记录最大值 最大值 MIN(column_name) 用于表格中的计算求该字段记录最小值 最小值 SUM(column_name) 用于表格中的计算求该字段记录值的和 求和 - 加密函数
表达式 参数描述 效果描述 使用效果 MD5(str) str 需要加密的内容 信息摘要算法 PASSWORD(str) str 需要加密的内容，可用于修改用户密码 密码算法
- 自定义函数
自定义函数的语法：
CREATE FUNCTION function_name(arg1 data_type [,arg2 data_type] RETURNS {STRING|INTEGER|REAL|DECIMAL} [BEGIN [statement_list]] RETURN routine_body [END];
//函数体(routine_body) 可以是 SELECT、INSERT、DELETE等SQL语句,必须有且只有一个返回值
具有复合结构的函数体自定义函数：使用BEGIN …END语句来包围函数体
函数体流控制语句
IF语句：
IF search_condition THEN statement_list [ELSE IF search_condition THEN statement_list].... [ELSE statement_list] END IF
CASE语句：
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list] END CASE
LOOP语句
[begin_label:] LOOP statement_list END LOOP [end_label]
REPEAT语句
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label:]
WHILE语句
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label:]
离开循环
LEAVE label //打破循环
再次循环
ITERATE label //用在while、repeate、loop 语句中
删除自定义函数：
DROP FUNCTION [IF EXISTS] function_name;
查看定义的函数：
SHOW CREATE FUNCTION function_name;
- 存储过程
存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程能够加快数据库的执行效率,减少网络流量。
创建存储过程的语法：
CREATE [DEFINER = {user|CURRENT_USER}] PROCEDURE sp_name ([ [IN |OUT|INOUT] param_name,data_type [,...]]) routine_body;
//DEFINER为创建的用户名，IN为传入参数，OUT 为传出结果 ,procedure可以有传出结果也可以没有，routune_body为SQL命令，当routine_body为多条SQL命令时需要用begin,end来包裹
调用存储过程的语法
CALL procedure_name([param [,...]]);
删除存储过程：
DROP PROCEDURE [IF EXISTS] procedure_name;
存储过程与函数的区别：
- 存储引擎
1.什么是存储引擎：
就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法，通过这些实现方法将数据保存到内存上，每一种引擎使用不同的存储机制，索引技巧、锁定水平，最终提供广泛且不同的功能,也可以称作表类型（即存储和操作此表的类型）。
2.查看数据库可以支持的引擎
SHOW ENGINES;
并发处理
并发控制：当多个连接对记录进行修改时保证数据的一致性和完整性。
锁机制：封锁下被一个事务处理的数据，防止其他用户访问到不一致的数据
锁的类型
共享销（读锁）:在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
排他锁（写锁）:在任何时候只能有一个用户写入资源，当进行写销时会阻塞其他的读锁或者写锁操作。
锁颗粒：（锁的作用范围）
表锁：针对于整张表格的锁定
行锁：针对某个记录的锁定
事务处理
事务：保持数据的一致性它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位（如银行从一表账户向另一个账户转账，一个账户金额减少，另一个账户的金额必须同等增加）
事务的特性：
原子性（ATOMICITY）:在任何操作出现一个错误的情况下，构成事务的所有操作的效果必须被撤消，数据应被回滚到以前的状态.
一致性（CONSISTENCY):一个事务应该保护所有定义在数据上的不变的属性
隔离性(ISOLATION）:当有多个事务同时执行时，每个事务都应表现为独立执行
持久性(DURABILITY)：一个被完成的事务的效果应该是持久的
几种常见的存储引擎
修改默认的存储引擎：
修改MySQL的配置文件，配置文件为MySQL目录下的my.ini文件
default-storage-engine=INNODB //默认的存储引擎为Innodb,
在创建数据表时指定表的存储引擎：
CREATE TABLE table_name(......) ENGINE =engine_name;
修改表的存储引擎 ：
ALTER TABLE table_name ENGINE =engine_name;
- MySQ管理工具
- PHPMyadmin
- Navicat
- MYSQLWorkBeanch
- Mysql用户管理
系统将用户相关的信息都保存在mysql数据库的中
mysql.user表格(保存用user(用户名)，_priv(全局权限不受数据库限制）password(密码) host(主机名,%表示任意主机，localhost表示服务器说在的主机)等信息）
mysql.db表（保存了user,_priv(数据库权限），Db(权限的受限范围）
- 查看所有用户
select user from mysql.user;//从mysql数据库的user表中显示user字段- 添加普通用户
CREATE USER 'username'[@hostname] [IDENTIFIED BY 'password']//创建普通用户 @hostname为用户登录的主机名- 删除普通用户
DROP USER 'username'[@hostname]- 为普通用户分配权限
GRANT priv ON database.table TO 'username'@'userhost' [IDENTIFIED BY 'password']
//priv为授予的操作权限 ：all表示全部权限，列举多个权限时用”，”隔开,database.table 可以用通配符’*”来匹配说有的表，如*.*表示所有数据库中的所有表
可以配授予的权限有 SELECT,INSERT,DELETE,UPDATE,DROP
ALTER等,如果该用户不存在，则会被创建- 列表内容查看某个用户被授予的权限
SHOW GRANTS FOR username[@userhost]
扫一扫
1845
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
