一、数据库基础
1. 连接数据库
- mysql --host=localhost --user=root --password test
- mysql -h localhost -u root -p test;
- mysql -u root -p
2. 断开MySQL服务器
quit
3. 创建数据库
create database 数据库名;
4. 查看数据库
- select version():查看MySQL版本
- select current_date / curdate()、select current_time / curtime():查看当前日期、时间 = select now();
- show databases:查看数据库
- show tables:查看数据表
- show columns from 数据表名[from 数据库名]:查看表结构
- describe 数据表名 列名;describe简写desc:查看表结构
5. 选择数据库
- use 数据库名;
- mysql -d 所选择的数据库名 -h 主机名 -u 用户名 -p;
6. 删除数据库
drop database 数据库名;
7. 创建数据表
create [temporary] table [if not exists] 数据表名 [(create_definition,...)][table_options][select_statement];
参数 | 说明 |
temporary | 如果使用该关键字,表示创建一个临时表 |
if not exists | 该关键字用于避免表不存在时MySQL报告的错误 |
create_definition | 表的列属性部分,MySQL要求在创建表时,表至少包含一列 |
table_options | 表的一些特性参数 |
select_statement | select语句描述部分,用它可以快速地创建表 |
参数 | 说明 |
col_name | 字段名 |
type | 字段类型 |
NOT NULL | NULL | 指出该列是否允许是空值,系统一般默认允许为空值,当不允许为空值设置NOT NULL; |
DEFAULT default_values | 表示默认值 |
AUTO_INCREMENT | 表示是否是自动编号,每个表只能有一个AUTO_INCREMENT列,并且必需被索引 |
PRIMARY KEY | 表示是否为主键,一个表只能有一个PRIMARY KEY。 |
reference_definition | 为字段添加注释 |
8. 插入数据
insert [into] 表明 [(列名1,列名2,列名3,...)] values (值1,值2,值3,...);
9. 查询表中的数据
select 列名称 from 表名称 [where 条件];
10. 更新表中的数据
update 表名称 set 列名称 = 新值 where 更新条件;
11. 删除表中的数据
delete from 表名称 where 删除条件;
12. 创建后表的修改
- 添加列:alter table 表名 add 列名 列数据类型 [after 插入位置];
- 修改列:alter table 表名 change 列名称 列新名称 新数据类型。
- 删除列:alter table 表名 drop 列名称;
- 重命名表:alter table 表名 rename 新表名;
- 删除整张表:drop table 表名;
- 删除整个数据库:drop database 数据库名;
- 修改子句定义字段:modify 列名 列数据类型;
修改root 用户密码
mysql admin -u root -p password 新密码;
二、MySQL数据库备份和恢复
1. 数据的备份
mysqldump -u root -p 需要备份的数据库 >“c:\users\simple\desktop\test.txt”(备份的路径以及文件名,注意命令末尾不能加分号";",否则报错)
2. 数据的恢复
mysql -u root -p test < "c:\users\simple\desktop\test.txt"
三、其他
1. MySQL所处状态的含义
提示 | 含义 |
mysql> | 准备好进行新查询 |
-> | 等待多行查询的下一行 |
'> | 等待下一行,等待以单引号开头的字符串的完成( ' ) |
"> | 等待下一行,等待以双引号开头的字符串的完成( " ) |
`> | 等待下一行,等待以反引号( ` )开头的标识符的完成 |
/*> | 等待下一行,等待以 # 开头的评论完成 /* |
在执行某语句时,键入 \c 可以切换回来,mysql>
2. 信息功能
名称 | 描述 |
---|---|
BENCHMARK() | 反复执行表达式 |
CHARSET() | 返回参数的字符集 |
COERCIBILITY() | 返回字符串参数的归类强制性值 |
COLLATION() | 返回字符串参数的排序规则 |
CONNECTION_ID() | 返回连接的连接ID(线程ID) |
CURRENT_USER() , CURRENT_USER | 经过身份验证的用户名和主机名 |
DATABASE() | 返回默认(当前)数据库名称 |
FOUND_ROWS() | 对于带有LIMIT子句的SELECT,返回的行数没有LIMIT子句 |
LAST_INSERT_ID() | 最后一次INSERT的AUTOINCREMENT列的值 |
ROW_COUNT() | 行数已更新 |
SCHEMA() | DATABASE()的同义词 |
SESSION_USER() | USER()的同义词 |
SYSTEM_USER() | USER()的同义词 |
USER() | 客户端提供的用户名和主机名 |
VERSION() | 返回表示MySQL服务器版本的字符串 |
3. 将文本加载数据表中
mysql> load data local infile "文件路径及名称" into table 数据表;
4. 将 .sql 文件导入到MySQL的数据库中
mysql> source 文件路径及文件名称.sql;
5. 使用 distinct 去除重复的数据。
mysql> select distinct name from users;
6. 排序行
mysql> select * from users order by name [asc];(默认为升序)
mysql> select * from users order by name desc;(按降序排序)
对多个列进行排序,并且可以按不同方向对不同列进行排序:mysql> select name,gender,email from users order by gender ,email desc; (DESC
关键字仅适用于紧邻其前面的列名,它不会影响对gender列排序)
7. where 运算符
操作符 | 描述 |
---|---|
= | 等于 |
<> | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
BETWEEN | 在某个范围内 |
LIKE | 搜索某种模式 |
查询使用death IS NOT NULL
而不是death <> NULL
因为 NULL
是一个特殊值
8. 规定要返回的数据记录数目
mysql> select * from users limit 需要返回数据的数目(数字)
9. 通配符
通配符 | 描述 |
---|---|
% | 替代一个或多个字符 |
_ | 仅替代一个字符 |
10. Alias(别名):返回的数据是别名的字段
- 表的Alias语法:select column_name(s) from table_name as alias_name
- 列的Alias语法:select column_name as alias_name from table_name;
11. join用于根据两个或多个表中的列之间的关系,从这些表中查询数据
- 内连接查询:使用关键字join、cross join或inner join
语法:select column_name(s) from table_name1 inner join table_name2 on table_name1.uid=table_name2.id
- 外连接查询:左外查询left join、右外查询right join
语法:select column_name(s) from table_name1 left join table_name2 on table_name1.uid=table_name2.id
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行
语法:select column_name(s) from table_name1 right join table_name2 on table_name1.uid=table_name2.id
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
- 全连接 full join
语法:select column_name(s) from table_name1 full join table_name2 on table_name1.column_name=table_name2.column_name;
只要其中某个表存在匹配,FULL JOIN 关键字就会返回行
12. union 操作符
union操作符用于合并两个或多个select语句的结果集
语法:select column_name(s) from table_name1 union select column_name(s) from table_name2;
默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL,另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
语法:select column_name(s) from table_name1 union all select column_name(s) from table_name2;
13. 日期和时间函数
14. 正则表达式
- ^:匹配字符串的开头
- $:匹配字符串的结尾
- . :匹配任何字符(包括回车和换行符)
- a*:匹配零个或多个
a
字符的任何序列 - a+:匹配一个或多个
a
字符的任何序列(至少有一个) - a?:匹配零个或一个
a
字符(至多一个) - de|abc:匹配任何序列de或abc(轮换)
- (abc)*:匹配序列的零个或多个实例abc
- {n},{m,n}:匹配完全匹配的实例(
m
并且n
必须在范围从0
到RE_DUP_MAX
(默认255),包括端值。) - [a-dX],[^a-dX]:匹配任何字符,该字符(或不是,如果
^
被使用)任一a
,b
,c
,d
或X
。
15. 聚合(Group By)功能
AVG()
返回参数的平均值BIT_AND()
按位返回ANDBIT_OR()
按位返回ORBIT_XOR()
按位返回异或COUNT()
返回返回的行数COUNT(DISTINCT)
返回许多不同值的计数GROUP_CONCAT()
返回一个连接的字符串MAX()
返回最大值MIN()
返回最小值STD()
返回人口标准差STDDEV()
返回人口标准差STDDEV_POP()
返回人口标准差STDDEV_SAMP()
返回样本标准差SUM()
归还总和VAR_POP()
返回人口标准差异VAR_SAMP()
返回样本方差VARIANCE()
返回人口标准差异
16. 使用用户定义的变量
mysql>select @min_id:=min(id),@max_id:=max(id) from users;
mysql>select * from users where id=@min_id or id=@max_id;
17. 计算数据总数并去除重复的数据
- select bit_count(bit_or(column_name)) as alias_name from table_name;
- select count(distinct column_name) from table_name;
五、mysql 数据库优化
优化查询的注意考虑因素:
1. 要select...where加快查询速度,首先要检查的是是否可以添加索引。在where子句中使用的列上设置索引,以加快评估,过滤和结果的最终检索。为避免浪费磁盘空间,请构建一小组索引,以加快应用程序中使用的许多相关查询。
2. 隔离并调整查询的任何部分,例如函数调用,这回占用过多时间。根据查询的结构,可以为结果集中的每一行调用一次函数,甚至可以为表中的每一行调用一次函数,从而大大减轻任何低效率。
3. 最大限度地减少查询中的全表扫描次数,尤其是对大型表。
4. 通过analyze table定期使用语句使表统计信息保持最新,因此优化程序具有构建有效执行计划所需的信息。
5. 了解特定于每个表的存储引擎的调优技术,索引技术和配置参数,双方InnoDB并MyISAM有两套准则的实现和维持查询高性能。
6. 如果其中一个基本准则无法轻松解决性能问题,通过阅读查询调用特定查询的内部详细信息explain计划并调整索引、where子句、连接子句。
7. 调整MySQL用于缓存的内存区域的大小和属性,通过有效使用InnoDB缓冲池,MyISAM密钥缓存和MySQL查询缓存,重复查询运行的更快,用为第二次以及以后的时间从内存中检索结果