MySQL暑假学习(一)
目录
MySQL的体系结构术语
MySQL采用的是“客户端/服务端”
- 服务端:
mysqld - 客户端:
mysql
两个主要用于管理客户端程序
- mysqldump
用于把表的内容导出到文件里
- mysqladmin
主要用来检查数据库服务器的工作状态,以及执行一些数据库管理象关的任务
简单的创建数据库用户(以后还会涉及到)
CREATE USER '用户名'@'数据库服务机器IP地址' IDENTIFIED BY '密码';
GRANT ALL ON 数据库名称.* TO '用户名'@'数据库服务端机器IP地址'
back
数据库的连接和断开
连接
连接到mysql程序,需要在cmd窗口下,或者shell下使用命令,命令如mysql option;
来进行连接到数据库。请注意,option是还没有的命令形式,其部分可为空。
最常见的形式是:
mysql -h host_name -p -u user_name
对于上面,其中:
-h host_name指定运行服务端的主机名称(另一种形式:--host=host_name)
-u user_name指定mysql的用户名(另一种形式:--user=user_name)
-p要求输入密码(另一种形式:--password)
back
断开
断开与mysql的连接,我们使用exit;的指令来终止对话(或者q)
back
执行SQL语句
对于sql语句,在每一句的最后的位置,都必须以;分号或者g结束。请注意,g和G不一样。
g是要求mysql立即执行该查询语句,使用横向的方式显示G是要求mysql执行查询语句,并且使用垂直的方式显示
如果sql语句不以;或者g结尾,那么mysql会等待结束命令,一直到你输入语句结束符为止。
如果输入了一条多行的sql语句,如果不需要执行,那么输入c可以清楚这条语句并且不执行。
SQL不分大小写
back
基本表操作
创建表
使用CREATE TEBLE 表名字(表列)去创建表,语句还可以包含各种索引的定义,如果有索引定义,能够加快查询速度
在创建完成表之后,我们可以使用
DESCRIBE 表名字,用来查看表的结构(也可以使用DESC或者EXPLAIN,SHOW语句)
DESCRIBE 表名字DESC 表名字EXPLAIN 表名字SHOW COLUMNS FROM 表名字SHOW FIELDS FROM 表名字
创建表的时候指明数据库引擎,类似于下面的写法
CREATE TABLE mytable
(
id INT UNSIGNED NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(id) REFERENCES newtable(column)
)ENGINE = InnoDB;
上面的写法,创建了一个表,表名为mytable,表中有一个项,为id。该表主键为id,并且该表设置的存储引擎为InnoDB。指名id为外键,与newtable中的column行有对应关系。
创建外键会有一种依赖的关系,可以按照一定的顺序去创建表和删除表。
上面的写法是,必须存在newtable之后,才能存在mytable。否则会报错。而删除则必须先删除mytable之后才能删除newtable,否则也会报错。
back
添加新行
对于创建一个新的表,我们当然要往里面添加点东西。
一：
- 使用INSERT进行插入
这种方式是一种利用SQL语句进行数据插入的方式,可以用它来指定要插入数据行的那个表以及要插入的数据
INSERT 语句有多种格式
INSERT INTO tbl_name VALUES(value1,value2...)
- 一次性插入全部的值。使用这个语法的时候,首先应该知道
tbl_name中的每个列的属性以及顺序。可以使用上面介绍的DESCRIBE tbl_name去查询(tbl_name是表名称)- 可以插入多个值,格式例如
INSERT INTO tbl_name VALUES (value1-1,value1-2...),(values2-1,values2-2...),...。将每个行的值括起来的括号不可少
INSERT INTO tbl_name(col_name1,col_name2...) VALUES(values1,values2)这样的一种方式是命名赋值列。对于这个INSERT语句中没有涉及到的列,将会被设置为初始状态值,也就是
default的状态。
- 使用"列/值"的形式进行赋值,需要用到SET子句的实现,也就是如同
INSERT INTO tbl_name SET col_name1=value1,col_name2...这样的实现请注意,这样的实现中,是无法进行多行插入的,只能单行插入,对于没有被赋值的列项,将会自动初始化为默认值。
二：
- 利用文件的方式添加新行
mysql可以直接从文件中读取数据,然后转换到数据库中。文件可以包含INSERT语句或者原始数据。比如有一个文件(后缀.sql),里面包含着INSERT语句,那么有两种方式可以进行添加
- 如果是在命令行中,而非mysql中,可以使用
mysql db_name < file_name.sql,使用<进行重定向,从而进行文件的输入。 - 如果是在mysql控制台中,使用
source file_name.sql进行读取这个文件。
如果文件中存储的并非INSERT语句,而是数据,并且默认以制表符分开,并且每行末尾都是换行符,并且每一行中,值的顺序与数据库中的顺序相同,就可以进行相关的操作了,默认情况下,使用LOAD DATA语句就可以数据的插入了。
对于数据库,可能会需要权限,如果权限不足,那么必须提升权限或者利用
root账户进行修改选项。其次,还会有文件路径的问题,数据库默认的情况下可能会禁用LOCAL功能。
back
检索信息
表建好了,那我们得进行使用数据了。可以使用SELECT语句进行检索和显示表里面的数据信息,可以使用特定的方式进行检索。
SELECT * FROM tbl_name
检索tbl_name表中的所有信息
当然,SELECT语句很复杂,也可以很简单,对于长句,可以进行简化。
SELECT what to retrieve FROM table or tables WHERE conditions that data must satisfy;
换句话说,对于数据库中的检索信息,那么可以使用SELECT语句进行检索
back
- 特殊的东西
NULL
NULL是一种特殊的东西,它表示无值,或者未知值,故不能使用已知值去比较,否则也会得到一个未知值,也就是NULL。如果使用两个NULL进行比较,那么返回的结果也是NULL以为两个未知值比较还是未知值。
如果需要测试多个NULL的值是否相等,采用IS NULL或者IS NOT NULL进行NULL的比较,而不能用=,!=,<>
但是在运算符中,有一个特殊的运算符,
<=>,这个东西可以和NULL去比较。
back
- 使用
ORDER BY tbl_name进行排序
在查询的时候,可以使用ORDER BY 进行排序,如果不适用,在你查询的时候,不见得都是按照一定的顺序来的。
ORDER BY是按照一定的顺序来的,默认按照升序的方式,如果需要指定序列,可以在后面使用ASC或者DESC来指定排序方式
ASC是升序,DESC是降序
SELECT * FROM tbl_name ORDER BY(column) ASC选择所有的内容,根据column进行升序排序
在使用ORDER BY的时候,后面还可以接IF(a,b,c)函数或者LIMIT等
back
- 使用
LIMIT去限制查询你结果
如果在一个很大的表中,查询通常会返回很多行,这就使得我们看得很繁琐(反正本人看得很繁琐,当然,你也可以看,en),这个时候,我们可以使用LIMIT去限定几行进行输出。
SELECT * FROM tbl_name LIMIT 5;从tbl_name中选出所有的列,但是只显示前面的5行
LIMIT子句还允许从查询结果的中间抽去一部分的行,这个时候需要两个值,第一个是从查询结果的开头跳过的行数目,第二个是需要返回的行数目
SELECT * FROM tbl_name LIMIT 8,6;从tbl_name中选择所有的列显示,跳过先前的8行,从第9行开始选择后面的6行输出显示
back
- 对输出的列进行计算和重命名
使用FORMAT函数进行输出格式化
格式为
FOMRAT(N,D,locale);
| 参数 | 作用 |
|---|---|
参数N |
是要格式化的数字 |
参数D |
是要舍入的小数位数(遵循四舍五入原则) |
参数locale |
是可选项,用于确定千分分隔符和分隔符之间的分组 |
back
- 使用
CONCAT(first,' ',last)进行字符串合并
比如在我们的名字中，通常是由姓和名组成的，姓是前缀，名是后缀。如果我们数据库中的表把我们的名字拆成了姓和名分别记录。如果我们想查看这个人的名字，那么我们就可以使用CONCAT(a,connector,b)这样的方式来进行连接。如下图：
在这个例子中，我使用了AS去进行重命名显示的列，格式是something as b把something这个东西重命名为b。
back
- 处理日期
在MySQL中，可以对日期做多种运算，例如
- 按照日期排序
- 搜索特定的日期或者日期范围
- 从日期中获取年月日
- 计算两个日期的时差
- 计算出另一个日期
我们可以使用YEAR(),MONTH(),DAYOFMONTH()这样的函数检索日期的各个部分
如果知道两个日期，需要知道他们相隔的时间，使用TIMESTAMPDIFF()函数计算出结果。其参数是TIMESTAMPDIFF(unit,begin,end);
| 参数 | 含义 |
|---|---|
| unit | 确定结果的单位 |
| bengin | DATE或者DATETIME表达式 |
| end | 同上 |
其中，unit的有效单位有：
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
如果根据某个日期计算出另一个日期，那么使用函数DATE_ADD()或者DATE_SUB进行计算，前者是日期相加，后者是日期相减，其参数是一个日期值，一个时间间隔。
DATE_ADD(now_date,INTERVAL n DAY);,now_date相加n天，返回一个新的日期CURDATE()这个函数主要是返回当前的日期
back
- 匹配模式
MySQL中有匹配模式，这个时候就要使用如同LIKE或者NOT LIKE这样的形式，然后使用_（下划线）或者%(百分号)去进行匹配字符串
_下划线可以匹配任何的单个字符%百分号可以匹配任何的字符序列，包括空序列
COUNT()函数是显示所有被查询到的行数。只会统计所有非NULL值的数目
COUNT()函数返回匹配指定条件的行数。
GROUP BY 使用这个子句，可以对某个计算列的结果进行分组。其中，COUNT()函数可以和ORDER BY和LIMIT组合在一起使用，当然也可以和GROUP BY
如果不是想用LIMIT子句来先置查询结果中的记录条数，而是想把COUNT()中的某些特定的值找出来，需要用HAVING子句。
HAVING子句与WHERE子句不同之处在于，它可以引用像COUNT()这样的汇总函数输出的结果
| 汇总函数 | 作用 |
|---|---|
COUNT() |
总计数量 |
MIN() |
计算最小值 |
MAX() |
计算最大值 |
SUM() |
计算和 |
AVG() |
计算平均值 |
- 连接多个表查询信息
这部分比较庞大，咱先不做笔记.....
back
删除或更新已有行
- 使用
DELETE语句进行删除
- 基本格式:
DELETE FROM tbl_name WHERE which rows to delete;- 其中的WHERE部分可以省略，如果没有限定，那么就是删除tbl_name中的所有的列。
- 使用
UPDATE语句进行修改行的内容
- 基本格式:
UPDATE tbl_name SET which columns to change WHERE which rows to update;- 在基本格式中，WHERE部分可以省略，省略之后更改的是表中的全部内容。
back
数据类型
| 数据类型 | 定义 |
|---|---|
| varchar(n) | 可以存放长度可变的字符型之,并且最大字符是n个 |
| date | 用于保存日期值,标准格式CCYY-MM-DD |
| int | 存放整型数 |
| enum('a','b') | 枚举类型,指定括号里面的字符 |
back
列属性
| 属性 | 作用 |
|---|---|
| not null | 必须有值 |
| null | 可以没有值 |
| auto_increment | 存放序号,并且自动生成唯一编号 |
| unsigned | 值不能为负 |
| primary key | 约束值必须唯一 |
| primary key(a,b) | 约束a,b两个值,使其为唯一 |
| foreign key(a) references table(b) | 当前表中的列名称为a的列与表名为table中的列名为b的列对应,且每一个a都必须与一个b对应 |
| index(a) | 根据列名为a的列建立索引 |
| IF(a,b,c) | if函数,作用是计算出第一个参数给出的那个表达式的值,然后根据计算结果来决定是返回第二个参数还是第三个参数,第二个参数是真值,第三个参数是假值 |
back
算术运算符
| 运算符 | 含义 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / | 除法 |
| DIV | 整除 |
| % | 模运算 |
back
比较运算符
| 运算符 | 含义 |
|---|---|
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <=> | 等于 |
| <> 或者 != | 不等于 |
| >= | 大于等于 |
| > | 大于 |
back
逻辑运算符
| 运算符 | 含义 |
|---|---|
| AND | 逻辑与 |
| OR | 逻辑或 |
| XOR | 逻辑异或 |
| NOT | 逻辑非 |
back
参考书籍《MySQL技术内幕（第5版）》
如若转载，请注明出处