20170708――20170807
注:cmd需要以管理员身份运行
1,启动与关闭
net start mysql ,
net stop mysql
2,登录与退出
mysql -u(username) -p(password) -P(端口号) -h(服务器地址),
exit; quit; \q;
默认端口号:3306,本机服务器地址:127.0.0.1
3,修改MySQL提示符
①连接客户端时 :mysql -u(username) -p(password) --prompt 提示符
②连接客户端后 :mysql>prompt 提示符
![](https://img-blog.csdn.net/20170807175857827?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvUm9va2llX09yX1ZldGVyYW4=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
4,常用命令
SELECT VERSION(); 显示当前服务器版本
SELECT NOW(); 显示当前日期时间
SELECT USER(); 显示当前用户
SELECT DATABASE(); 显示当前打开的数据库
5,语句规范
① 关键字与函数名称全部大写
② 数据库名称,表名称,字段名称全部小写
③ SQL语句必须以分号结尾
6,创建数据库
CREATE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
7,查看当前服务器下的数据库列表
SHOW DATABASES;
8,修改数据库
ALTER {DATABASE | SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name;
9,删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
10,数据类型
整形 :
浮点型
日期
字符型
定长:用空格补齐
L+1或L+2里多出来的字节是用来保存数据值的长度的。在对长度可变的数据进行处理时,MySQL要把数据内容和数据长度都保存起来。
11,创建数据表:先打开数据库 USE db_name
CREATE TABLE [IF NOT EXISTS] tb_name(column_name data_type,...)
column_name:列名称,data_type:数据类型
12,查看数据表
SHOW TABLES [FROM db_name] [LIKE 'pattern'|WHERE expr]
13,查看数据表结构
SHOW COLUMNS FROM tb_name;
14,记录插入(为行内容赋值)
INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...);
(col_name,...)若省略则需要写入所有要插入的字段
15,记录的查找(查看赋值结果如何)
SELECT expr,...FROM tb_name;
16,自动编号
AUTO_INCREMENT
必须与主键组合使用,起始值为1,每次增量为1;类型可以为整数或者浮点数,浮点数的小数部分必须为0
17,主键约束 PRIMARY KEY
① 一张数据表中只能有一个主键
② 主键保证记录的唯一性
③ 主键自动为NOT NULL
自动编号不需要赋值,主键约束可以赋值,但是不允许重复
18,唯一约束 UNIQUE KEY
① 唯一约束可以保证记录的唯一性
② 唯一约束的字段可以为空值
③ 每张数据表可以存在多个唯一的约束
唯一约束不允许有多个空值
19,默认约束 DEFAULT
当插入记录时,如果没有明确为字段赋值,则自动赋予默认值
eg:
CREATE TABLE tb1(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1','2','3') DEFAULT '3'
);
20,根据约束所针对的字段的数目可以分为 表级约束(>=2字段)和列级约束(1字段)
21,根据功能将约束分为
NOT NULL (非空约束)
PRIMARY KEY (主键约束)
UNIQUE KEY (唯一约束)
DEFAULT (默认约束)
FOREIGN KEY (外键约束)
22,外键约束 FOREIGN KEY(chl_col_name)REFERENCES fath_tb_name(fath_col_name)
23,表级约束与列级约束
(1)对一个数据列建立的约束,称为列级约束
(2)对多个数据列建立的约束,称为表级约束
(3)列级约束既可以在列定义时声明,也可以在列定以后声明
(4)表级约束只能在列定义后声明
24,修改数据表(改)
添加单列 ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
添加多列 ALTER TABLE tb_name ADD [COLUMN] (col_name column_definition,...);
删除列 ALTER TABLE tb_name DROP [COLUMN] col_name;
添加主键约束 ALTER TABLE tb1_name add [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
添加唯一约束 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUWE [INDEX | KEY] [index_name] [index_type] (index_col_name,...)
添加外键约束 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [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 column_definition [FIRST | AFTER col_name]
数据表更名
① ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
② RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...
插入记录(增)
① INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
② INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},... 与第一种方式的区别在于,法②可以使用子查询(SubQuery),且一次只能插入一条记录
③ INSERT [INTO] tbl_name [(col_name,...)] SELECT ... 从一个数据表导入
更新记录(单表更新)
UPDATE [LOW_PRIORITY][IGNORE] tbale_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}] ... [WHERE where_condition]
删除记录(单表删除)(删)
DELETE FROM tbl_name [WHERE where_condition]
25, SELECT:
SELECT select_expr[,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name|position} [ASC|DESC],...]
[HAVING where_condition]
[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 , ORDRE BY 或 HAVING 字句
26,查询结果的操作(查)
where的条件查询
GROUP BY的查询结果分组
[GROUP BY {col_name | position} [ASC | DESC],...]
ASC表示升序,DESC表示降序
HAVING 的分组条件 [HAVING where_condition]
eg:
SELECT id,age FROM tb_name GROUP BY id HAVING expr;
expr中出现的字段必须为聚合函数或者在前面的SELECT语句中出现的字段,该例中只能为id,age或者其他聚合函数。
聚合函数为max()min()avg()sum()count()等,只有一个返回值。
ORDER BY 对查询结果进行排序
[ORDER BU {col_name | expr | position} [ASC | DESC],...]
eg: SELECT * FROM tb_name ORDER BY id DESC; //按id的降序排列
LIMIT 限制查询结果返回的数量
[LIMIT {[offset,]row_count | row_count OFFSET offset}]
eg: SELECT * FROM tb_name LIMIT 1; //只显示第一条
SELECT * FROM tb_name LIMIT 3,2; //显示第四条和第五条,排序时从0开始的
27, 修改客户端显示的内容编码(不影响数据表的内容)
SET NAMES gbk/utf8...
28 , 子查询 指嵌套在查询内部,且必须始终出现在圆括号内,子查询可以包含多个关键字或条件,如 DISTINCT,GROUP BY,ORDER BY,LIMIT 函数等。子查询的外层查询可以是:SELECT,INSERT,UPDATE,SET或DO。子查询可以返回标量,一行,一列,或者一个子查询
分类:
① 使用比较运算符的子查询 = ,>=, < ,<= ,<> ,!= ,<=>
语法结构 operand comparison_operator subquery
eg:
SELECT ROUND(AVG(goods_price),2) FROM tbd_goods; //对价格字段求平均值,四舍五入保留两位,不写2,默认保留整数。
SELECT goods_id,goods_price,goods_name FROM tbd_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tbd_goods) //用子查询查找数据表中价格大于价格平均值的项
用ANY,SOME,ALL修饰的比较运算符
operand comparsion_operator ANY (subquery)
operand comparsion_operator SOME (subquery)
operand comparsion_operator ALL (subquery)
② 使用[NOT] IN
语法结构 operand comparison_operator [NOT] IN (subquery)
ANY 运算符与IN等效,!=ALL或<>ALL 与NOT IN等效
③ 使用[NOT] EXISTS 的子查询,如果子查询返回任何行,EXISTS将返回TRUE,否则为FALSE。(不常用)
29 ,INSERT ... SELECT 将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
30 , 多表更新
UPDATE table_references SET col_name1 = {expr | DEFAULT} [,col_name2 = {expr2 | DEFAULT} ... [WHERE where_condition]
table_references : {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
31 , CREATE ... SELECT 创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name [create_definition,...]select_statement
32 , 连接
①{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
②table_reference
③ON conditional_expr
JOIN
INNER JOIN:内连接和交叉连接等价,都是笛卡尔积运算
CROSS JOIN:交叉连接
LEFT [OUTER] JION 左连接:是根据最左表的记录,在被连接右表中找出符合条件的记录与之匹配,找不到与左表匹配的,用null填充。
RIGHT [OUTER] JOIN 右连接:是根据最右表的记录,在被连接左表中找出符合条件的记录与之匹配,找不到与右表匹配的,用null填充。
连接类型:
INNER JOIN,内连接。在MySQL中,JOIN,CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN ,左外连接
RIGHT [OUTER] JOIN,右外连接
连接条件:使用On关键字来设定连接条件,也可以用WHERE来代替。通常使用ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤
内连接:显示左表及右表符合连接条件的记录
左外连接:显示左表的全部及右表中符合条件的记录
右外连接:显示右表的全部及左表中符合条件的记录
多表连接
SELECT col_name1,col_name2,col_name3,col_name4,col_name5 FROM tbl_name1 AS t1
INNER JOIN tbl_name2 AS t2 ON join_condition
INNER JOIN tbl_name3 AS t3 ON join_condition\G;
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
-> INNER JOIN tdb_goods_cate AS c ON g.cate_id=c.cate_id
-> INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;
连接三个表联合显示查询结果,注意不同的INNER JOIN中间没有逗号!!没有逗号!!没有逗号!!
显示的结果和商品表tdb_goods的记录一样,不过之前是通过单表的查询来显示,这次是通过三张表的连接来显示。
多表的连接实际上是外键的逆向约束。外键把数据分开存储,多表连接又把数据联系在一起。
多表删除
DELETE tbl_name[.*] [,tbl_name[.*]] ... FROM table_references [WHERE where_condition]
子查询与连接总结:
子查询:出现在mysql语句的select子句,而子查询必须要出现在小括号()内,它的外层可以是select语句,insert语句,update语句,delete语句,而且在子查询中可以包含多个关键词:group by,order by,limit以及相关函数。。。引发子查询的情况有比较运算符,(Not)in运算符,exist(Not exists)引发的子查询
连接:连接分为外连接和内连接,内连接主要显示的是左表和右表当中都符合连接条件的记录。外链接有分为左外连接和右外连接,左外连接显示的是左表中的全部记录和右表符合条件的记录,右外连接和左外连接相反
33,字符函数
CONCAT(); 连接多个字段
eg(面试题):SELECT CONCAT(first_name,'_',last_name) AS full_name FROM test;
CONCAT_WS(); 用某个分隔符连接多个字段
eg:SELECT CONCAT_WS('_',first_name,last_name,...);
FORMAT() 数字格式化(返回类型为字符型)
eg: SELECT FORMAT(12530.75,2) //12,530.75
SELECT FORMAT(12530.75,1) //12,530.8
SELECT FORMAT(12530.75,0) //12,531
UPPER() 转换成大写字母
LOWER() 转换成小写字母
eg:SELECT UPPER('MySQL') //MYSQL
SELECT LOWER(;MySQL') //mysql
LEFT() 获取左侧字符串
RIGHT() 获取右侧字符串
eg:SELECT LEFT('mysql',2) //my
SELECT RIGHT('mysql',3) //sql
SELECT UPPER(LEFT('mysql',2)) //MY 函数嵌套
SELECT CONCAT(UPPER(LEFT('mysql',2)),LOWER(RIGHT('mysql',3))) //MYsql 函数嵌套
LENGTH() 获取字符串长度
eg: SELECT LENGTH('MY SQL') //6
LTRIM() 删除字符串的前导空格
RTRIM() 删除字符串的后续空格
TRIM() 删除字符串的前导和后续空格
eg: SELECT LENGTH(LTRIM(' mysql ')) //9
SELECT LENGTH(RTRIM(' mysql ')) //7
SELECT LENGTH(TRIM(' MYSQL ')) //5
SELECT TRIM(LEADING '?' FROM '???MYSQL??') //MYSQL?? 删除前导指定字符
SELECT TRIM(TRAILING '?' FROM '???MYSQL??')//???MYSQL 删除后续指定字符
SELECT TRIM(BOTH '?' FROM '???MYSQL??') //MYSQL 删除前导和后续的指定字符
REPLACE 替换指定字符
eg: SELECT REPLACE('??MY??SQL???','?','') //MYSQL
SUBSTRING 从某字符串的某个位置开始截取指定长度的字符
eg: SELECT SUBSTRING('MYSQL',1,2); //MY 从1开始计数
SELECT SUBSTRING('MYSQL',2); //YSQL
SELECT SUBSTRING('MYSQL',-2); //QL从右往左数
SELECT SUBSTRING('MYSQL',-1,-2); //输出空
[NOT]LIKE 模式匹配
eg: SELECT 'MYSQL' LIKE 'M%' //1 即true,MySQL中的%为通配符,类似Windows下的*,%代表任意字符,_代表任意一个字符
SELECT 'MYSQL' LIKE 'm%' //1 不区分大小写
34,数值运算函数
CEIL() 向上取整
FLOOR() 向下取整
DIV 整除运算
MOD 取模
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取
35 ,比较运算符与函数
[NOT] BETWEEN AND(); //(不)在某个范围内
eg:SELECT 15 BETWEEN 1 AND 22; //1(true),包括两边 [1,22]
SELECT 15 BETWEEN 15 AND 15; //1(true)
[NOT] IN(); //(不)在列出值范围内
eg:SELECT 15 IN(10,15,20); //1(true)
SELECT 15 IN(10,12,20); //0(true)
IS [NOT] NULL; //(不)为空
eg:SELECT NULL IS NULL; //1(true)
SELECT '' IS NULL; //0(false)
36 , 日期时间函数
NOW(); //当前日期和时间
CURDATE(); //当前日期
CURTIME(); //当前时间
DATE_ADD(); //日期的变化
eg:SELECT DATE_ADD('2017-7-25',INTERVAL 365 DAY); //2018-07-25
SELECT DATE_ADD('2017-7-25',INTERVAL -365 DAY); //2016-07-25
SELECT DATE_ADD('2017-7-25',INTERVAL 48 HOUR); //2017-07-27 00:00:00
SELECT DATE_ADD('2017-7-25',INTERVAL 48*60 MINUTE); //2017-07-27 00:00:00
DATE_DIFF() //日期差值
eg:SELECT DATEDIFF('2019-6-10','2015-6-8'); //1463
DATE_FORMAT() //日期格式化
eg:SELECT DATE_FORMAT('2017-7-25','%m/%d/%Y'); //07/25/2017
37 , 信息函数
CONNECTION_ID() //连接ID
eg:SELECT CONNECTION_ID(); //8(服务器不同输出不同)
DATABASE(); //当前数据库
eg:SELECT DATABASE();
LAST_INSERT_ID() //最后插入记录的ID号。 表中必须存在一个自动编号的字段,设置为主键,名字不一定叫ID。而且如果最后一条记录一次插入了多个数据,显示的是插入的多个数据的第一个数据的ID号
USER(); //当前用户
38 , 聚合函数
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
39 ,加密函数
MD5():信息摘要算法,为以后的Web页面做准备,尽量使用MD5()
eg: SELECT MD5('admin');
PASSWORD():密码算法,通过PASSWORD()修改(重要用于MYSQL数据库)当前用户和其他用户的密码,修改客户端自己的密码
eg: SET PASSWORD=PASSWORD(‘dimitar’); 把密码修改成dimitar。
40 , 自定义函数(udf user-define-function):
两个必要条件:a,参数,b,返回值
创建自定义函数:
CREATE FUNCTION function_name RETURNS{STRING|INTEGER|REAL|DECIMAL} routine_body
eg:
CREATE FUNCTION F1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
SELECT F1(); //2017年07月25日 16点:33分:01秒 输出当前时间并格式化
创建带参数的自定义函数:
eg:
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN(num1+num2)
SELECT f2(10,15) //12.5
删除函数:
DROP [IF EXISTS]FUNCTION fun_name;
修改分隔符: DELIMITER 分隔符
41,存储过程:存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理
优点:
a)增强SQL语句的功能和灵活性
b)实现较快的执行速度
c)减少网络流量
创建存储过程 CREATE [DEFINER = {user| CURRENT_USER}] PROCEDURE sp_name([proc_parameter[,...]]) [characteristic ...]routine_body
proc_parameter: [IN|OUT|INOUT] param_name type
IN:表示该参数的值必须在调用存储过程时指定
OUT:表示该参数的值可以被存储过程改变,并且可以返回
INOUT:表示该参数的调用时指定,并且可以被改变和返回
eg: CREATE PROCEDURE sp() SELECT VERSION();
调用存储过程:
CALL sp_name([parameter[,...]])
CALL sp_name[()]
eg:CALL sp();
修改存储过程:只可以修改参数类型和注释,过程体不可以修改,只能删除再重建。
ALTER ORICEDURE sp_name [characteristic ...] COMMENT 'string' | {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}
删除存储过程: DROP PRICEDURE[IF EXISTS] sp_name;
eg: CREATE PROCEDURE removeUsersAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;
END
CALL removeUsersAndReturnUserNums(10,@nums); //变量
SELECT @nums; //输出剩余条目数
SET @nums = 7; //定义用户变量,只对当前客户端有效
SELECT ROW_COUNT(); //系统函数;得到插入,删除,更新 影响的记录总数
eg:INSERT test(username)VALUES('A'),('B'),('C');
SELECT ROW_COUNT(); //3
UPDATE test SET username = CONCAT(username,'--CHINA') WHERE id<=2;
SELECT ROW_COUNT(); //2
![](https://img-blog.csdn.net/20170807180852150?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvUm9va2llX09yX1ZldGVyYW4=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
42,存储引擎:可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎。每一种存储引擎使用不同的存储机制,索引技巧,锁定水平,最终提供广泛且不同的功能
查看数据表的存储引擎: SHOW CREATE TABLE tb_name;
mysql支持的存储引擎:
MySAM(常用):存储限制可达256TB,支持索引,表级锁定,数据压缩
InnoDB(常用): 限制为64TB,支持事务和索引,锁颗粒为行锁
Memory
CSV
Archive
相关知识点:
①并发处理:
并发控制:当多个连接对记录进行修改时保证数据的一致性和完整性。对于并发读或者并发写,系统采用“锁”系统解决
a)锁:
共享锁(读锁):在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化。
排他锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作。
b)锁颗粒:
表锁:是一种开销最小的锁策略。
行锁:是一种开销最大的锁策略。
②事务处理:
事务:用于保证数据库的完整性。
事务的特性:
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
③外键:是保证数据一致性的策略
④索引:是对数据表中一列或多列的值进行排序的一种结构。是快速定位的一种方法
普通索引
唯一索引
全文索引
btree索引
hash索引
...
![](https://img-blog.csdn.net/20170807180915683?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvUm9va2llX09yX1ZldGVyYW4=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
修改存储引擎的方法
①通过修改MySQL配置文件实现 ———— default-storage-engine = engine
②通过创建数据表命令实现 ———— CREATE TABLE table_name( ... )ENGINE = engine;
③通过修改数据表命令实现 ———— ALTER TABLE table_name ENGINE [=] engine_name;
43,数据库管理工具:PHPMyAdmin,Navicat,MySQL Workbench
注:cmd需要以管理员身份运行
1,启动与关闭
net start mysql ,
net stop mysql
2,登录与退出
mysql -u(username) -p(password) -P(端口号) -h(服务器地址),
exit; quit; \q;
默认端口号:3306,本机服务器地址:127.0.0.1
3,修改MySQL提示符
①连接客户端时 :mysql -u(username) -p(password) --prompt 提示符
②连接客户端后 :mysql>prompt 提示符
4,常用命令
SELECT VERSION(); 显示当前服务器版本
SELECT NOW(); 显示当前日期时间
SELECT USER(); 显示当前用户
SELECT DATABASE(); 显示当前打开的数据库
5,语句规范
① 关键字与函数名称全部大写
② 数据库名称,表名称,字段名称全部小写
③ SQL语句必须以分号结尾
6,创建数据库
CREATE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
7,查看当前服务器下的数据库列表
SHOW DATABASES;
8,修改数据库
ALTER {DATABASE | SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name;
9,删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
10,数据类型
整形 :
浮点型
日期
字符型
定长:用空格补齐
L+1或L+2里多出来的字节是用来保存数据值的长度的。在对长度可变的数据进行处理时,MySQL要把数据内容和数据长度都保存起来。
11,创建数据表:先打开数据库 USE db_name
CREATE TABLE [IF NOT EXISTS] tb_name(column_name data_type,...)
column_name:列名称,data_type:数据类型
12,查看数据表
SHOW TABLES [FROM db_name] [LIKE 'pattern'|WHERE expr]
13,查看数据表结构
SHOW COLUMNS FROM tb_name;
14,记录插入(为行内容赋值)
INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...);
(col_name,...)若省略则需要写入所有要插入的字段
15,记录的查找(查看赋值结果如何)
SELECT expr,...FROM tb_name;
16,自动编号
AUTO_INCREMENT
必须与主键组合使用,起始值为1,每次增量为1;类型可以为整数或者浮点数,浮点数的小数部分必须为0
17,主键约束 PRIMARY KEY
① 一张数据表中只能有一个主键
② 主键保证记录的唯一性
③ 主键自动为NOT NULL
自动编号不需要赋值,主键约束可以赋值,但是不允许重复
18,唯一约束 UNIQUE KEY
① 唯一约束可以保证记录的唯一性
② 唯一约束的字段可以为空值
③ 每张数据表可以存在多个唯一的约束
唯一约束不允许有多个空值
19,默认约束 DEFAULT
当插入记录时,如果没有明确为字段赋值,则自动赋予默认值
eg:
CREATE TABLE tb1(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1','2','3') DEFAULT '3'
);
20,根据约束所针对的字段的数目可以分为 表级约束(>=2字段)和列级约束(1字段)
21,根据功能将约束分为
NOT NULL (非空约束)
PRIMARY KEY (主键约束)
UNIQUE KEY (唯一约束)
DEFAULT (默认约束)
FOREIGN KEY (外键约束)
22,外键约束 FOREIGN KEY(chl_col_name)REFERENCES fath_tb_name(fath_col_name)
23,表级约束与列级约束
(1)对一个数据列建立的约束,称为列级约束
(2)对多个数据列建立的约束,称为表级约束
(3)列级约束既可以在列定义时声明,也可以在列定以后声明
(4)表级约束只能在列定义后声明
24,修改数据表(改)
添加单列 ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
添加多列 ALTER TABLE tb_name ADD [COLUMN] (col_name column_definition,...);
删除列 ALTER TABLE tb_name DROP [COLUMN] col_name;
添加主键约束 ALTER TABLE tb1_name add [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
添加唯一约束 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUWE [INDEX | KEY] [index_name] [index_type] (index_col_name,...)
添加外键约束 ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [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 column_definition [FIRST | AFTER col_name]
数据表更名
① ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
② RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...
插入记录(增)
① INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
② INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},... 与第一种方式的区别在于,法②可以使用子查询(SubQuery),且一次只能插入一条记录
③ INSERT [INTO] tbl_name [(col_name,...)] SELECT ... 从一个数据表导入
更新记录(单表更新)
UPDATE [LOW_PRIORITY][IGNORE] tbale_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}] ... [WHERE where_condition]
删除记录(单表删除)(删)
DELETE FROM tbl_name [WHERE where_condition]
25, SELECT:
SELECT select_expr[,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name|position} [ASC|DESC],...]
[HAVING where_condition]
[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 , ORDRE BY 或 HAVING 字句
26,查询结果的操作(查)
where的条件查询
GROUP BY的查询结果分组
[GROUP BY {col_name | position} [ASC | DESC],...]
ASC表示升序,DESC表示降序
HAVING 的分组条件 [HAVING where_condition]
eg:
SELECT id,age FROM tb_name GROUP BY id HAVING expr;
expr中出现的字段必须为聚合函数或者在前面的SELECT语句中出现的字段,该例中只能为id,age或者其他聚合函数。
聚合函数为max()min()avg()sum()count()等,只有一个返回值。
ORDER BY 对查询结果进行排序
[ORDER BU {col_name | expr | position} [ASC | DESC],...]
eg: SELECT * FROM tb_name ORDER BY id DESC; //按id的降序排列
LIMIT 限制查询结果返回的数量
[LIMIT {[offset,]row_count | row_count OFFSET offset}]
eg: SELECT * FROM tb_name LIMIT 1; //只显示第一条
SELECT * FROM tb_name LIMIT 3,2; //显示第四条和第五条,排序时从0开始的
27, 修改客户端显示的内容编码(不影响数据表的内容)
SET NAMES gbk/utf8...
28 , 子查询 指嵌套在查询内部,且必须始终出现在圆括号内,子查询可以包含多个关键字或条件,如 DISTINCT,GROUP BY,ORDER BY,LIMIT 函数等。子查询的外层查询可以是:SELECT,INSERT,UPDATE,SET或DO。子查询可以返回标量,一行,一列,或者一个子查询
分类:
① 使用比较运算符的子查询 = ,>=, < ,<= ,<> ,!= ,<=>
语法结构 operand comparison_operator subquery
eg:
SELECT ROUND(AVG(goods_price),2) FROM tbd_goods; //对价格字段求平均值,四舍五入保留两位,不写2,默认保留整数。
SELECT goods_id,goods_price,goods_name FROM tbd_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tbd_goods) //用子查询查找数据表中价格大于价格平均值的项
用ANY,SOME,ALL修饰的比较运算符
operand comparsion_operator ANY (subquery)
operand comparsion_operator SOME (subquery)
operand comparsion_operator ALL (subquery)
② 使用[NOT] IN
语法结构 operand comparison_operator [NOT] IN (subquery)
ANY 运算符与IN等效,!=ALL或<>ALL 与NOT IN等效
③ 使用[NOT] EXISTS 的子查询,如果子查询返回任何行,EXISTS将返回TRUE,否则为FALSE。(不常用)
29 ,INSERT ... SELECT 将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
30 , 多表更新
UPDATE table_references SET col_name1 = {expr | DEFAULT} [,col_name2 = {expr2 | DEFAULT} ... [WHERE where_condition]
table_references : {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
31 , CREATE ... SELECT 创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name [create_definition,...]select_statement
32 , 连接
①{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
②table_reference
③ON conditional_expr
JOIN
INNER JOIN:内连接和交叉连接等价,都是笛卡尔积运算
CROSS JOIN:交叉连接
LEFT [OUTER] JION 左连接:是根据最左表的记录,在被连接右表中找出符合条件的记录与之匹配,找不到与左表匹配的,用null填充。
RIGHT [OUTER] JOIN 右连接:是根据最右表的记录,在被连接左表中找出符合条件的记录与之匹配,找不到与右表匹配的,用null填充。
连接类型:
INNER JOIN,内连接。在MySQL中,JOIN,CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN ,左外连接
RIGHT [OUTER] JOIN,右外连接
连接条件:使用On关键字来设定连接条件,也可以用WHERE来代替。通常使用ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤
内连接:显示左表及右表符合连接条件的记录
左外连接:显示左表的全部及右表中符合条件的记录
右外连接:显示右表的全部及左表中符合条件的记录
多表连接
SELECT col_name1,col_name2,col_name3,col_name4,col_name5 FROM tbl_name1 AS t1
INNER JOIN tbl_name2 AS t2 ON join_condition
INNER JOIN tbl_name3 AS t3 ON join_condition\G;
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
-> INNER JOIN tdb_goods_cate AS c ON g.cate_id=c.cate_id
-> INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;
连接三个表联合显示查询结果,注意不同的INNER JOIN中间没有逗号!!没有逗号!!没有逗号!!
显示的结果和商品表tdb_goods的记录一样,不过之前是通过单表的查询来显示,这次是通过三张表的连接来显示。
多表的连接实际上是外键的逆向约束。外键把数据分开存储,多表连接又把数据联系在一起。
多表删除
DELETE tbl_name[.*] [,tbl_name[.*]] ... FROM table_references [WHERE where_condition]
子查询与连接总结:
子查询:出现在mysql语句的select子句,而子查询必须要出现在小括号()内,它的外层可以是select语句,insert语句,update语句,delete语句,而且在子查询中可以包含多个关键词:group by,order by,limit以及相关函数。。。引发子查询的情况有比较运算符,(Not)in运算符,exist(Not exists)引发的子查询
连接:连接分为外连接和内连接,内连接主要显示的是左表和右表当中都符合连接条件的记录。外链接有分为左外连接和右外连接,左外连接显示的是左表中的全部记录和右表符合条件的记录,右外连接和左外连接相反
33,字符函数
CONCAT(); 连接多个字段
eg(面试题):SELECT CONCAT(first_name,'_',last_name) AS full_name FROM test;
CONCAT_WS(); 用某个分隔符连接多个字段
eg:SELECT CONCAT_WS('_',first_name,last_name,...);
FORMAT() 数字格式化(返回类型为字符型)
eg: SELECT FORMAT(12530.75,2) //12,530.75
SELECT FORMAT(12530.75,1) //12,530.8
SELECT FORMAT(12530.75,0) //12,531
UPPER() 转换成大写字母
LOWER() 转换成小写字母
eg:SELECT UPPER('MySQL') //MYSQL
SELECT LOWER(;MySQL') //mysql
LEFT() 获取左侧字符串
RIGHT() 获取右侧字符串
eg:SELECT LEFT('mysql',2) //my
SELECT RIGHT('mysql',3) //sql
SELECT UPPER(LEFT('mysql',2)) //MY 函数嵌套
SELECT CONCAT(UPPER(LEFT('mysql',2)),LOWER(RIGHT('mysql',3))) //MYsql 函数嵌套
LENGTH() 获取字符串长度
eg: SELECT LENGTH('MY SQL') //6
LTRIM() 删除字符串的前导空格
RTRIM() 删除字符串的后续空格
TRIM() 删除字符串的前导和后续空格
eg: SELECT LENGTH(LTRIM(' mysql ')) //9
SELECT LENGTH(RTRIM(' mysql ')) //7
SELECT LENGTH(TRIM(' MYSQL ')) //5
SELECT TRIM(LEADING '?' FROM '???MYSQL??') //MYSQL?? 删除前导指定字符
SELECT TRIM(TRAILING '?' FROM '???MYSQL??')//???MYSQL 删除后续指定字符
SELECT TRIM(BOTH '?' FROM '???MYSQL??') //MYSQL 删除前导和后续的指定字符
REPLACE 替换指定字符
eg: SELECT REPLACE('??MY??SQL???','?','') //MYSQL
SUBSTRING 从某字符串的某个位置开始截取指定长度的字符
eg: SELECT SUBSTRING('MYSQL',1,2); //MY 从1开始计数
SELECT SUBSTRING('MYSQL',2); //YSQL
SELECT SUBSTRING('MYSQL',-2); //QL从右往左数
SELECT SUBSTRING('MYSQL',-1,-2); //输出空
[NOT]LIKE 模式匹配
eg: SELECT 'MYSQL' LIKE 'M%' //1 即true,MySQL中的%为通配符,类似Windows下的*,%代表任意字符,_代表任意一个字符
SELECT 'MYSQL' LIKE 'm%' //1 不区分大小写
34,数值运算函数
CEIL() 向上取整
FLOOR() 向下取整
DIV 整除运算
MOD 取模
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取
35 ,比较运算符与函数
[NOT] BETWEEN AND(); //(不)在某个范围内
eg:SELECT 15 BETWEEN 1 AND 22; //1(true),包括两边 [1,22]
SELECT 15 BETWEEN 15 AND 15; //1(true)
[NOT] IN(); //(不)在列出值范围内
eg:SELECT 15 IN(10,15,20); //1(true)
SELECT 15 IN(10,12,20); //0(true)
IS [NOT] NULL; //(不)为空
eg:SELECT NULL IS NULL; //1(true)
SELECT '' IS NULL; //0(false)
36 , 日期时间函数
NOW(); //当前日期和时间
CURDATE(); //当前日期
CURTIME(); //当前时间
DATE_ADD(); //日期的变化
eg:SELECT DATE_ADD('2017-7-25',INTERVAL 365 DAY); //2018-07-25
SELECT DATE_ADD('2017-7-25',INTERVAL -365 DAY); //2016-07-25
SELECT DATE_ADD('2017-7-25',INTERVAL 48 HOUR); //2017-07-27 00:00:00
SELECT DATE_ADD('2017-7-25',INTERVAL 48*60 MINUTE); //2017-07-27 00:00:00
DATE_DIFF() //日期差值
eg:SELECT DATEDIFF('2019-6-10','2015-6-8'); //1463
DATE_FORMAT() //日期格式化
eg:SELECT DATE_FORMAT('2017-7-25','%m/%d/%Y'); //07/25/2017
37 , 信息函数
CONNECTION_ID() //连接ID
eg:SELECT CONNECTION_ID(); //8(服务器不同输出不同)
DATABASE(); //当前数据库
eg:SELECT DATABASE();
LAST_INSERT_ID() //最后插入记录的ID号。 表中必须存在一个自动编号的字段,设置为主键,名字不一定叫ID。而且如果最后一条记录一次插入了多个数据,显示的是插入的多个数据的第一个数据的ID号
USER(); //当前用户
38 , 聚合函数
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
39 ,加密函数
MD5():信息摘要算法,为以后的Web页面做准备,尽量使用MD5()
eg: SELECT MD5('admin');
PASSWORD():密码算法,通过PASSWORD()修改(重要用于MYSQL数据库)当前用户和其他用户的密码,修改客户端自己的密码
eg: SET PASSWORD=PASSWORD(‘dimitar’); 把密码修改成dimitar。
40 , 自定义函数(udf user-define-function):
两个必要条件:a,参数,b,返回值
创建自定义函数:
CREATE FUNCTION function_name RETURNS{STRING|INTEGER|REAL|DECIMAL} routine_body
eg:
CREATE FUNCTION F1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
SELECT F1(); //2017年07月25日 16点:33分:01秒 输出当前时间并格式化
创建带参数的自定义函数:
eg:
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN(num1+num2)
SELECT f2(10,15) //12.5
删除函数:
DROP [IF EXISTS]FUNCTION fun_name;
修改分隔符: DELIMITER 分隔符
41,存储过程:存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理
优点:
a)增强SQL语句的功能和灵活性
b)实现较快的执行速度
c)减少网络流量
创建存储过程 CREATE [DEFINER = {user| CURRENT_USER}] PROCEDURE sp_name([proc_parameter[,...]]) [characteristic ...]routine_body
proc_parameter: [IN|OUT|INOUT] param_name type
IN:表示该参数的值必须在调用存储过程时指定
OUT:表示该参数的值可以被存储过程改变,并且可以返回
INOUT:表示该参数的调用时指定,并且可以被改变和返回
eg: CREATE PROCEDURE sp() SELECT VERSION();
调用存储过程:
CALL sp_name([parameter[,...]])
CALL sp_name[()]
eg:CALL sp();
修改存储过程:只可以修改参数类型和注释,过程体不可以修改,只能删除再重建。
ALTER ORICEDURE sp_name [characteristic ...] COMMENT 'string' | {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}
删除存储过程: DROP PRICEDURE[IF EXISTS] sp_name;
eg: CREATE PROCEDURE removeUsersAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;
END
CALL removeUsersAndReturnUserNums(10,@nums); //变量
SELECT @nums; //输出剩余条目数
SET @nums = 7; //定义用户变量,只对当前客户端有效
SELECT ROW_COUNT(); //系统函数;得到插入,删除,更新 影响的记录总数
eg:INSERT test(username)VALUES('A'),('B'),('C');
SELECT ROW_COUNT(); //3
UPDATE test SET username = CONCAT(username,'--CHINA') WHERE id<=2;
SELECT ROW_COUNT(); //2
42,存储引擎:可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎。每一种存储引擎使用不同的存储机制,索引技巧,锁定水平,最终提供广泛且不同的功能
查看数据表的存储引擎: SHOW CREATE TABLE tb_name;
mysql支持的存储引擎:
MySAM(常用):存储限制可达256TB,支持索引,表级锁定,数据压缩
InnoDB(常用): 限制为64TB,支持事务和索引,锁颗粒为行锁
Memory
CSV
Archive
相关知识点:
①并发处理:
并发控制:当多个连接对记录进行修改时保证数据的一致性和完整性。对于并发读或者并发写,系统采用“锁”系统解决
a)锁:
共享锁(读锁):在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化。
排他锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作。
b)锁颗粒:
表锁:是一种开销最小的锁策略。
行锁:是一种开销最大的锁策略。
②事务处理:
事务:用于保证数据库的完整性。
事务的特性:
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
③外键:是保证数据一致性的策略
④索引:是对数据表中一列或多列的值进行排序的一种结构。是快速定位的一种方法
普通索引
唯一索引
全文索引
btree索引
hash索引
...
修改存储引擎的方法
①通过修改MySQL配置文件实现 ———— default-storage-engine = engine
②通过创建数据表命令实现 ———— CREATE TABLE table_name( ... )ENGINE = engine;
③通过修改数据表命令实现 ———— ALTER TABLE table_name ENGINE [=] engine_name;
43,数据库管理工具:PHPMyAdmin,Navicat,MySQL Workbench