Fork me on GitHub

mysql学习（上部分）

Posted on | In | Views:

基础构架：database –> table –> row 和 col（数据块）

RDBMS 术语

  1. 数据库: 数据库是一些关联表的集合。.
  2. 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  3. 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
  4. 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
  5. 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
  6. 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  7. 外键：外键用于关联两个表。
  8. 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
  9. 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
  10. 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL 创建数据库

使用 mysqladmin 创建数据库

以下实例会创建一个 RUNOOB 数据库

1
2
[root@host]# mysqladmin -u root -p create RUNOOB
Enter password:******

SQL语句创建数据库 RUNOOB

1
'CREATE DATABASE RUNOOB'

MySQL 删除数据库

使用 mysqladmin 删除数据库

1
2
[root@host]# mysqladmin -u root -p drop RUNOOB
Enter password:******

SQL语句删除数据库 RUNOOB

1
'DROP DATABASE RUNOOB'

MySQL 选择数据库

从命令提示窗口中选择MySQL数据库

使用 RUNOOB 数据库

1
2
3
[root@host]# mysql -u root -p
Enter password:******
mysql> use RUNOOB;

使用 NODE.js 选择数据库

1
2
3
4
5
6
7
8
9
10
11
var mysql = require('mysql');
var connection = mysql.createConnetion({
	host: 'localhost',
	prot: 3306,
	user: 'root',
	password: '****',
	database: 'RUNOOB'
});
connection.connect(); // 连接数据库
connection.query('SQL命令', function (err, result) { // 数据库相应的 CURD })	
connection.end(); // 断开连接

MySQL 数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

mysql_int

日期和时间类型

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

mysql_time

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

mysql_string

MySQL 创建数据表

创建数据表需要：表名、表字段名、定义每个表字段

SQL命令

语法：
    CREATE TABLE table_name (column_name column_type)
    CREATE TABLE IF NOT EXISTS table_name (column_name column_type) // 判断是否存在
实例：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL
2.AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
3.PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

在命令提示窗口中创建数据表

1
2
3
4
5
6
7
8
9
10
root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
mysql> CREATE TABLE runoob_tbl(
   -> runoob_id INT NOT NULL AUTO_INCREMENT,
   -> runoob_title VARCHAR(100) NOT NULL,
   -> runoob_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( runoob_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

node.js中创建表

1
2
3
4
connection.query("CREATE TABLE person(id int,user varchar(255),password varchar(255))", function(err,result){
	if(err){throw err}
	console.log("创建表成功")
})

MySQL 删除数据表

语法：
    DROP TABLE table_name

在命令提示窗口中删除数据表

1
2
3
4
root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
mysql> DROP TABLE runoob_tbl

node.js中删除表

1
2
3
4
connection.query('DROP TABLE runoob_tbl', function (err, result) {
	if (err) throw err;
	console.log(result);
})

MySQL 插入数据

语法：    
    INSERT INTO table_name (field1, field2, ...filedN) VALUES (value1, value2, value3);

通过命令提示窗口插入数据

1
2
3
4
5
6
7
root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
mysql> INSERT INTO runoob_tbl 
	-> (runoob_title, runoob_author, submission_date)
	-> VALUES
	-> ("学习 PHP", "菜鸟教程", NOW());


读取某张表 -- select * from runoob_tbl;

node.js插入数据

1
2
3
4
5
6
7
connection.query('INSERT INTO websites(id, name, url, alexa, country) VALUES (0, "fanerge", "23213", "234", "成都")', function (err, result) {
	console.log(result);
});
// 占位符的形式
connection.query('INSERT INTO websites(id, name, url, alexa, country) VALUES (0, ?, ?, ?)', ["fanerge12", "23213", "234", "成都"], function (err, result) {
	console.log('DELETE affectedRows:', result);
});

MySQL 查询数据

语法：
    SELECT column_name, column_name 
    FROM table_name // 可以查询多张表以,分隔
    [WHERE clause] // 查询条件
    [OFFSET M][LIMIT N] // 偏移量 和 限制条数

通过命令提示符获取数据

1
select * from runoob_tbl

node.js查询数据

1
2
3
connection.query('SELECT name, url FROM websites', function (err, result) {
	console.log(result);
});

MySQL WHERE 子句

可以用在：SELECT 和 DELETE 和 UPDATE 命令中。
默认where语句不区分大小写，使用BINARY开启区分大小写。
语法：
    SELECT field1, field2, ...filedN
    FROM table_name1, table_name2
    [WHERE confition1 [AND [OR]]] condition2

mysql_where

从命令提示符中读取数据

1
2
3
	SELECT name, url FROM user WHERE name="fanerge" AND url="fanerge"
	```	
####	node.js读取数据


connection.query('SELECT name, url FROM user WHERE name="fanerge" AND url="fanerge"', function (err, result) {
    console.log(result);
});

1
2
3
4
5
###		MySQL UPDATE 查询	
	语法：	
		UPDATE table_name SET field1=new-value1, field1=new-value2
		[WHERE clause]
####	通过命令提示符更新数据


UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

1
####	node.js更新数据


connection.query('UPDATE user SET url="zhongguo" WHERE name="1name"', function (err, result) {
    console.log(result);
});

1
2
3
4
5
		
###		MySQL DELETE 语句	
	语法：	
		DELETE FROM table_name [WHERE clause]
####	从命令行中删除数据


use RUNOOB;
mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;

1
####	node.js删除数据


connection.query('DELETE FROM user WHERE name="1name"', function (err, result) {
    console.log(result);
});

1
2
3
4
5
6
7
8
9
10
		
###	MySQL LIKE 子句
	我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。	
	如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。	
	语法：
		SELECT field1, field2, ...filedN
		FROM table_name
		WHERE field1 LIKE confition1 [AND [OR]] field2 = 'somevalue'
####	在命令提示符中使用 LIKE 子句
	以下是我们将 runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录：


use RUNOOB
SELECT * from runoob_tl WHERE runoob_author LIKE '%COM'

1
####	node.js中使用 LIKE 子句


connection.query('SELECT * FROM websites WHERE BINARY name LIKE "f%"', function (err, result) {
    console.log(result);
});

1
2
3
4
5
6
7
8
9
10
11
12
13
14
		
###	MySQL UNION 操作符
	MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。
	多个 SELECT 语句会删除重复的数据。
	UNION [ALL | DISTINCT]	// DISTINCT: 可选，删除结果集中重复的数据。ALL: 可选，返回所有结果集，包含重复数据。
	语法：
		SELECT column1, column2, ... columnN
		FROM tables
		[WHERE conditions]
		UNION [ALL | DISTINCT]
		SELECT column1, column2, ... columnN
		FROM tables
		[WHERE conditions];
####	node.js 中使用 UNION


connection.query('SELECT country FROM websites UNION ALL SELECT country FROM apps ORDER BY country', function (err, result) {
    console.log(result);
});

1
####	带有 WHERE 的 SQL UNION ALL


connection.query('SELECT country, name FROM websites WHERE country LIKE "C%" UNION ALL SELECT country, app_name FROM apps WHERE country="CN" ORDER BY country', function (err, result) {
    console.log(result);
});

1
2
3
4
5
6
		
###	MySQL 排序	
	语法：
		SELECT field1, field2, ...filedN table_name1, table_name2
		ORDER BY field1, [field2] [ASC [DESC]] 
####	在命令提示符中使用 ORDER BY 子句


use RUNOOB;
mysql> SELECT * from runoob_tbl ORDER BY submission_date ASC;

1
####	node.js 中 ORDER BY [ASC DESC]


connection.query('SELECT * FROM websites ORDER BY id DESC', function (err, result) {
    console.log(result);
});

1
2
3
4
5
6
7
8
		
###	MySQL GROUP BY 语句
	语法：	
		SELECT column_name, function(column_name)
		FROM table_name
		WHERE column_name operator value
		GROUP BY column_name;
####	我们使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录


SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

1
2
3
####	使用 WITH ROLLUP
	WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
	coalesce函数说明：name !== null ? name : '总数'。


SELECT coalesce(name, "总数"), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP

1
2
3
4
5
6
7
8
9
###	Mysql 连接的使用
	从多个数据表中读取数据。
	你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
	JOIN 按照功能大致分为如下三类：
	1.	INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。(类似于交集)
	2.	LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
	3.	RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。	
####	在命令提示符中使用 INNER JOIN(相当于交集)


SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

1
2
####	MySQL LEFT JOIN
	MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。


SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

1
2
####	MySQL RIGHT JOIN
	MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。


SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

1
2
3
4
5
6
7
8
9
10
11
###	MySQL NULL 值处理
	IS NULL: 当列的值是 NULL,此运算符返回 true。
	IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
	<=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。
####	在命令提示符中使用 NULL 值
	SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
	SELECT * FROM runoob_test_tbl WHERE runoob_count IS NOT NULL;
###	MySQL 正则表达式
![mysql_regexp](/images/mysql_regexp1.png)


SELECT name FROM websites WHERE BINARY name REGEXP '^F'
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
```

参考文档
MYSQL

-------------本文结束感谢您的阅读，如果本文对你有帮助就记得给个star-------------
Donate comment here
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多