MySQL入门

最新推荐文章于 2022-08-02 02:07:47 发布

kikpin

最新推荐文章于 2022-08-02 02:07:47 发布

阅读量185

点赞数

分类专栏： MySQL 文章标签： MySQL

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL入门

使用空间运营商数据库，跳过安装与设置、初始化；分支MariaDB数据库管理系统；innoDB引擎

RDBMS术语

关系型数据库管理系统：

数据库 => 数据表 => 列、行
冗余：存储双倍数据，提高安全性
主键：唯一的，一个表只有一个，用于查询数据
外键：关联两个表
复合键：将多个列作为一个索引键，用于复合查询
索引：一列或多列的值进行排序的一种结构，快速访问特定数据
参照完整性，实体完整性

MySQL指令

启动/关闭服务器：Windows：cmd进入安装目录下bin目录后mysqld --console启动，mysqladmin -uroot shutdown关闭；Linux：同理ps -ef | grep mysqld检查是否已启动，./mysqld_safe &启动，./mysqladmin -u root -p shutdown输入密码，关闭；
登录：mysql -h 主机名 -u 用户名 -p 密码，如本机登录mysql -u root -p后输入密码
退出：exit或quit或\q
用户管理：

//方法一：
//添加用户，并制定权限
mysql> INSERT INTO user
		(host,user,password,
		select_priv,insert_priv,update_priv)
	VALUES('localhost','guest',PASSWORD('guest123'),'Y','Y','Y');
//重载授权表，新用户生效
mysql> FLUSH PRIVILEGES;
//显示特定的表信息
mysql> SELECT host,user,password FROM user WHERE user = "guest";

//方法二：grant指令,语法以分号结束；
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
	-> ON TUTORIALS.*
	-> TO 'test'@'localhost'
	-> IDENTIFIED BY 'test123';

授权表：select_priv，insert_priv，update_priv，delete_priv，create_priv，drop_priv，reload_priv，shutdown_priv，process_priv，file_priv，grant_priv，references_priv，index_priv，alter_priv

列出数据库：show databases
进入数据库：use <数据库名>
列出库中所有的数据表：show tables
显示表中的信息：show columns from <数据表名>表的属性、主键、默认值，show index from <数据表名>表的详细索引信息、包括主键，
数据库的性能及统计信息：show table status like [from db_name] [like 'pattern'] \G

\G以列展示信息

操作MySQL指令

创建数据库：create database <数据库名>，或使用MySQLadmin命令mysqladmin -u root -p create <数据库名>；
删除数据库：drop database <数据库名>，或使用MySQLadmin命令mysqladmin -u root -p drop <数据库名>；

MySQL数据类型：数值，日期/时间，字符串；日期/时间类型的格式

创建数据表：create table <table_name> (column_name column_type)，定义数据表、字段以及数据类型、默认值是否为空；

CREATE TABLE IF NOT EXISTS test_tbl(
	test_id INT UNSIGNED AUTO_INCREMENT,//整数，自增属性
	test_title VARCHAR(100) NOT NULL,//不为空
	test_author VARCHAR(40) NOT NULL,
	submission_date DATE,//日期
	PRIMARY KEY (test_id)//主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;//存储引擎，编码

删除数据表：drop table <数据表名>；
插入数据：insert into <数据表名>(field1,field2,...)values(value1,value2,...);；
查询数据：select * from <数据表名> [where 条件语句] [limit 条数] [offset 数据偏移量];
更新数据：update <数据表名> set field1=newvalue1,field2=newvalue2 [where 指定行]；不指定会更新所有；
删除数据：delete from <数据表名> where <primary_key>；不指定会删除所有；

where子句：类似if的条件句，指定，筛选查询的结果[where [binary] <条件1> [and[or]] <条件2> ]，binary关键字区分条件中字符串大小写；
like子句：加%号模糊匹配，和*效果相同，没有%即使where子句“=”一样效果，如LIKE '%com'包含com的模糊筛选；
union操作符：连接多个SELECT语句并整合结果，union [all/distinct]，参数distinct删除重复项；
order by子句：order by <field> [ASC/DESC]，以指定域升序/降序排序，默认升序ASC；
group by语句：select field1,[COUNT(*)/SUM(*)/SVG(*)/...] from <table> group by <field1>对筛选的结果以某域按某规则统计，select coalesce(<field1>,"总计"),SUM(field2) as <new_field_name> from <table> group by <field1> with rollup统计结果并“总计”；

数据表间查询：
select <a.field1>,<a.field2>,<b.field3>,... from <table1> a [inner join/left join/right join] <table2> b on <a.field2> = <b.field2>，
其中[inner join]等价的where语句：select <a.field1>,<a.field2>,<b.field3>,... from <table1> a,<table2> b where <a.field2>=<b.field2>
- (inner) join：内连接/等值连接，交叉部分
- left join：左连接，
- right join：右连接

php操作MySQL

连接MySQL：mysqli_connect(host,user,password,dbname,port,socket)；
关闭MySQL：mysqli_close(<mysqli $link>)，传参为定义的“连接”名；
创建/删除数据库/表/行：mysqli_query(<$conn>,<$sql>);
进入数据库：mysqli_select_db(<$conn>,<$dbname>)；

// mysqli_query(connection,query,resultmode) 
<?php
//连接MySQL
$dbhost = "localhost:3306";
$dbuser = "root";
$dbpass = "123456";
$conn = mysqli_connect($dbhost,$dbuser,$dbpass);
if(!$conn){
	die("连接错误：" . mysql_error($conn));
}
echo "连接成功<br/>"
mysql_query($conn,"set names utf8");//增改查数据，防止中文乱码
//创建数据库；create database <数据库名>
//删除数据库：drop database <数据库名> 
$sql = "create database <数据库名>";

$retval = mysqli_query($conn,$sql);
if(!$retval){
	die("创建数据库失败：".mysqli_error($conn));
}
echo "创建数据库成功\n";
//进入一个数据库
$enterdb = mysqli_select_db($conn,<数据库名>);
if(!$enterdb){
	die("选取数据库失败：". mysqli_error($conn));
}
echo "选取数据库成功\n";
//创建数据表 create table <数据表名>(<表头/主键>)ENGINE=InnoDB DEFAULT CHARSET=utf8
//删除数据表：drop table <数据表名>
$cret = "CREATE TABLE IF NOT EXISTS test_tbl(".
	"test_id INT UNSIGNED AUTO_INCREMENT,".
	"test_title VARCHAR(100) NOT NULL,".
	"test_author VARCHAR(40) NOT NULL,".
	"submission_date DATE,".
	"PRIMARY KEY (test_id)".
	")ENGINE=InnoDB DEFAULT CHARSET=utf8;";
$cretable = mysqli_query($conn,$cret);
if(!$cretable){
	die("创建数据表失败：".mysqli_error($conn));
}
echo "创建数据表成功\n";
//插入数据（增）
$crefield = "INSERT INTO <数据表名>".
		  "(field1,field2,field3)".
		  "values".
		  "(value1,value2,value3);"

$createdata = mysqli_query($conn,$crefeild);
if(!$createdata){
	die("创建数据失败".mysqli_error($conn));	
}
echo "创建数据成功";
//查询数据（查）
$selectfeild = “select * from <数据表名>”;
$selectdata = mysqli_query($conn,$selectfeild);
if(!$selectdata){
	die("查询数据失败".mysqli_error($conn));
}
echo "查询数据成功"；
// 渲染，查询得到的数据
// mysqli_fetch_array($selectdata,[MYSQLI_ASSOC/MYSQLI_NUM]);
// mysqli_fetch_assoc($selectdata);同上，以某一项为索引
echo "<table><tr><th>title1</th><th>title2</th><th>title3</th>"
while($row = mysqli_fetch_array($selectdata,MYSQLI_ASSOC)){
	echo "<tr><td>{$row[id]}</td>".
	"<tr><td>{$row[feild1]}</td>".
	"<tr><td>{$row[feild2]}</td>".
	"<tr><td>{$row[feild3]}</td>".
	"</tr>";
}
echo "</table>";
mysqli_free_result($selectdata);// 执行完SELECT后，释放游标内存
//更新数据（改）
$updatefeild = “update <数据表名> set field1=newvalue1,field2=newvalue2 where <primary_key>”;
$updatedata = mysqli_query($conn,$updatefeild);
if(!$updatedata){
	die("更新数据失败".mysqli_error($conn));
}
echo "更新数据成功";

//删除数据（删）
$deletefeild = “delete from <数据表名> where <primary_key>”;
$deletedata = mysqli_query($conn,$deletefeild);
if(!$deletedata){
	die("删除数据失败".mysqli_error($conn));
}
echo "删除数据成功";

//关闭MySQL
mysqli_close($conn);
?>

node.js操作MySQL

连接MySQL，进入数据库

//test.js
var mysql = require("mysql");
var connection = mysql.createConnection({
	//连接参数
	host:"localhost",
	user:"root",
	password:"123456",
	database:"test"//数据库名
})
connection.connect();//连接MySQL，进入数据库

connection.query('SELECT 1+1 AS solution',function(error,results,fields){
	if(error) throw error; 
	console.log('this solution is:',results[0].solution);
})

参数 =>
host主机地址(默认localhost)、
user、
password、
port端口(默认3306)、
database数据库名、
charset字符集大写(默认UTF8-GENERAL-CI)、
localAddress用于TCP连接的IP、
socketPath连接到unix域路径(使用host、port时忽略)、
timezone时区、
connectTimeout连接超时(默认不限制)、
stringifyObjects是否序列化对象、
typeCast是否转化列值为本地JS类型值(默认true)、
queryFormat自定义query语句格式化方法、
supportBigNumbers是否支持bigint或decimal类型列(默认false)、
bigNumberStrings强制bigint或decimal列以JS字符串返回(默认false)、
dataStrings强制timestamp/datetime/data以JS字符串返回(默认false)、
debug开启调试(默认false)、
multipleStatements是否一个query中多个MySQL语句(默认false)、
flags用于修改连接标志、
ssl使用SSL参数

查询数据（查）

//test.js 连接数据库后
connection.query("select * from <数据表名>",function(error,results){
	if(error){
		console.log(error.message)
		return;
	}
	console.log(results);
})

connection.end();//断开连接

插入数据（增）

//test.js  连接数据库后
var addSql = "insert into <数据表名>(Id,<属性名>) values(0,?)";//对应“属性名”的动态取值“？”
var addSqlParams = ["?"];//对应values的取值
connection.query(addSql,addSqlParams,function(error,results){
	if(error){
		return;
	}
	console.log(results);
})

更新数据（改）

//test.js 连接数据库后
var modSql = "update <数据表名> set <属性名>=？,<属性名>=？ where id=?";//改多个属性，ID定位，动态取值“？”
var modSqlParams = ["?","?","?"];//对应“？”，最后一位是ID的取值
connection.query(modSql,modSqlParams,function(error,results){
	if(error){return;}
	console.log(results);
})

删除数据（删）

//test.js  连接数据库后
var delSql = "delete from <数据表名> where id=<num>";//通过ID定位
connection.query(delSql,function(error,results){
	if(error){return;}
	console.log(results);
})

NULL值的处理

select语句中where field1 = null或!= null运算符命令无效；使用：

is null 列值为NULL 时为true
is not null 列值不为NULL时为true
<=> 比较两值都是NULL时为true

即where field1 is null或is not null；

正则表达式

模式	描述	说明
`^`	开始	设置multiline属性后，可匹配“\n”“\r”之后的位置
`$`	结束	设置multiline属性后，可匹配“\n”“\r”之前的位置
`.`	除“\n”的任意单个字符	包括“\n”的任意字符，需“[.\n]”模式
`[...]`	字符集合	包含的任意一个字符，如[ace] => “plain”=>“a”
`[^...]`	负值字符集合	未包含的任意字符,如[^ace] => “plain” => “p”
p1`\|` p2	“或”匹配	如gay\|gey => gay或gey，g(a\|e)y => gay或gey
`*`	零或多次	如`pig*` => 后面任意个`g`包括没有“pi”，即`{0,}`匹配无限
`+`	一或多次	如`pig+` => 后面任意个`g`包括至少一个"pig"，即`{1,}`匹配无限
`{n}`	指定非零整数	如`pig{2}` => 后面两个`g`，“pigg”
`{n,m}`	指定n<m范围	如`pig{2,5}` => 后面两至五个`g`

事务

只有innoDB引擎的MySQL才支持事务；
ACID：原子性，一致性，隔离性，持久性；
语句：

begin或start transaction显式开启一个事务
commit或commit work提交，永久修改
rollback或rollback work回滚，撤销未提交的修改
savepoint <identifier>创建保存点
release savepoint <identifier>删除保存点
rollback to <identifier>回滚至保存点
set transaction设置事件隔离级别，四种：read uncommitted、read committed、 repeatable read、serializable

两种方法：

begin,rollback,commit 手动提交；
set autocommit = 0/1禁止/开启自动提交；

参照git理解；用途？！

ALTER命令

修改数据表名、引擎，修改数据表字段、数据类型，修改索引、主键等信息。

增删改表名/表字段

创建/删除字段
alter table <数据表名> drop <field>，如果只有一个字段则无法DROP来删除字段；
alter table <数据表名> add <feild> [typeof]，末尾创建字段；
alter table <数据表名> add <feild> [typeof] [first/after <feild>]，第一列/某域之后创建字段；
修改某域的数据类型
alter table <数据表名> modify <field> [typeof]；
修改某域的名称和数据类型
alter table <数据表名> change <feild_old_name> <field_new_name> [typeof]

first，after关键字可用于add和modify子句；
alter可以指定是否是NULL值或默认值：mysql>alter table <table_name> modify i bigint not null default 100；不设置默认值会自动默认为NULL；

修改/删除字段默认值
alter table <数据表名> alter <field> set default <value>;
alter table <数据表名> alter <field> drop default；
修改数据表引擎、名称
alter table <数据表名> engine = myisam；
alter table <数据表名> rename to <new_name>；
alter table status查看数据表类型；

增删索引

单列索引、组合索引；
索引会提高查询速度，同时降低更新表速度(保存索引文件,占用磁盘空间)；

普通索引

创建
create index <indexname> on <table>(<field>([length]));，char和varchar类型length可小于实际长度，blob和text类型必须指定length；
创建数据表时直接创建索引：

mysql> create table mytable(
	id INT not null,
	username VARCHAR(16) NOT NULL,
	index myindex (username(16))
);

修改表结构（添加索引）
alter table <数据表名> add index <indexname>(feild_name);
删除索引
drop index [index_name] on <table_name>；

唯一索引：列的值必须唯一，但允许为空；如果是组合索引的值也必须唯一；

创建索引
create unique index [index_name] on <table_name>(<field>(length))；
修改索引
alter table [table_name] add unique [index_name] (username(length))；
创建表直接指定

mysql> create table mytable(
	id int not null ,
	username varchar(16) not null ,
	unique myindex (username(16))
);

alter指令

添加和删除索引
- show index from <table_name>;\G：显示数据表的索引信息；
- alter table <table_name> add primary key (feild_name)：添加主键；
- alter table <table_name> drop primary key：删除主键；
- alter table <table_name> add unique <index_name> (feild_name)：添加唯一索引；
- alter table <table_name> add index <index_name>(feild_name)：添加普通索引；
- alter table <table_name> add fulltext <index_name>(feild_name)：添加全文索引；
- alter table <table_name> drop index <index_name>(feild_name)：删除索引；

删除主键只需要指定primary key ；删除索引需要知道索引名；

临时表

手动创建临时表，SHOW TABLES列表不会显示，退出当前MySQL会话后或客户端后自动销毁；

mysql> create temporary table myTempTable(
	-> product_name varchar(50) not null ,
	-> total_sales decimal(12,2) not null defalut 0.00 ,
	-> avg_unit_price decimal(7,2) not null defalut 0.00 ,
	-> total_units_sold int unsigned not null defalut 0
);

手动销毁：drop table <tempTableName>；

复制表

三步：查询原始创建SQL语句=>创建新表=>克隆内容

show create table <表名>；
修改创建的表名，执行查询到的语句；
克隆：

mysql> insert into newtable(
	-> new_id,new_title,new_author
	-> )
	-> select old_id,old_title,old_author
	-> from oldtable;

元信息

命名提示符	说明
`select version`	服务器版本
`select database`	当前数据库名/空
`select user`	当前用户名
`show status`	服务器状态
`show variables`	服务器配置变量
`show databases`	数据库列表
`show tables`	当前数据库的表

序列(auto_increment)

自增序列auto_increment关键字；
获取自增值last_insert_id()，php使用mysql_insert_id(PK)获取；
重置序列：删除PK列后新建PK列；alter table <t_name> drop <pk_name>;alter table <t_name> add <pk_name> int unsigned not null auto_increment first,add primary key(<pk_name>);
设置序列开始值：alter table <t_name> auto_increment = 100；mysql> create table mytable(...)engine=innodb auto_increment=100 charset=utf8;

重复数据

设置主键或唯一索引，防止重复
双主键模式：数据唯一性，不重复，不为NULL；
唯一索引插入重复数据：insert into失败，抛出错误；insert ignore into 成功，抛出警告，可以插入重复数据；replace into删除后插入新数据
统计重复：select count(*) as results, field1, field2 from table1 group by field1, field2 having results > 1;重复数大于1的count(*)统计数据
过滤重复：select distinct field1, field2 from table1;使用distinct关键字；或select field1, field2 from table1 group by (field1,field2);使用group by子句；
删除重复：create table new_table select field1,field2 from old_table group by (field1,field2);复制表后筛选数据以得到新表；或alter ignore table old_table add primary key (field1, field2)添加(双)主键或唯一索引使数据不为重复；

SQL注入安全

防止恶意SQL命令语句：

输入内容过滤，PHP的mysqli_real_escape_string()转义，addcslashes()处理%和_特殊字符：addcslashes(mysqli_real_escape_string("%abc_")，"%_")
不要动态拼接SQL命令，直接或参数化SQL数据操作函数
不要使用管理员权限，数据库账号权限
不要直接存放，机密信息加密或hash存放
尽可能少的错误提示，或自定义错误包装
sql注入检测工具jsky，或平台：亿思网站安全平台检查工具，MDCSOFT SCAN；MDCSOFT-IPS防御SQL注入，XSS攻击

导入导出

导出：select <field> from <table> into outfile <uri/file>；已存在的文件名，会覆盖；需创建文件的服务器权限；自带的mysqldump转存工具；

//特定格式，如下csv格式
mysql> select * from table_name
	-> into outfile '/tmp/file.txt'
	-> fields terminated by ',' enclosed by '"'
	-> lines terminated by '\r\n';
//选取数据，生成文件
mysql> select a, b, a+b into outfile '/tmp/file.txt'
	-> fields terminated by ',' optionally enclosed by '"'
	-> lines terminated by '\n'
	-> from table_name;

导入：
- mysql命令：mysql -u user_name -p < <file_uri>
- source命令：use <database_name>进入库，source <file_uri>导入
- load data语句导入： load data local infile '/test.txt' into table mytable;；指定local关键字客户机上路径，否则服务器上路径；与select into outfile同类，相同子句；
- mysqlimport工具

常用SQL函数、运算符：RUNOOB官网–函数,运算符

kikpin

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL入门

MySQL入门RDBMS术语MySQL指令操作MySQL指令php操作MySQLnode.js操作MySQL虚拟空间使用MySQL，暂时跳过安装与设置、初始化；MariaDB数据库管理系统是此开源分支；以innoDB引擎为主流RDBMS术语关系型数据库管理系统：数据库 => 数据表 => 列、行冗余：存储双倍数据，提高安全性主键：唯一的，一个表只有一个，用于查询数据...
复制链接

扫一扫

专栏目录