Mysql

※查询乱码问题
set character_set_results=gb2312;

MySql 综合

一。MySQL 参数

-D。--databases {name} 打开指定数据库

-H。 --host {name} 服务器名称

-p. 密码

-u. 用户名

-v. 输出版本信息并退出

-exit 退出

二。修改MySQl提示符

链接客户端参数指定

mysql>mysql -uroot -proot -prmpt --{提示符}

完整日期

d 当前数据库

h 服务器名称

u 当前用户

三。常用命令

SELECT VERSION(); 显示当前用户

SELECT NOW(); 显示当前时间

SELECT USER(); 显示当前用户

SHOW DATABASES; 显示数据库

USER TABLENAME; 使用数据库

SHOW TABLES; 数据库信息

SHOW WARNINGS; 显示错误信息

SHOW CREATE DATABASES TABLENAME; 显示创建表明信息

四。创建数据库

CREATE {DATABASES | SCHEMA} [IF NOT EXISTS]
db_name [DEFAULT] CHARACTER SET [=] charset_name

列：CREATE DATABASES TABLENAME;

列：[DEFAULT] CHARACTER SET [=] charset_name 使用编码方式

五。修改数据库

ALTER {DATABASES | SCHEMA} [db_name] [DEFAULT]
CHARACTER SET [=] CHARSET_NAME;

六。删除数据库

DROP {DATABASES | SCHEMA} [IF EXISTS] db_name

列：DROP TABLE db_name;

七.数据表

USER TABLENAME; 使用数据库

SELECT DATABASES(); 显示当前数据库

1.创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
)
列:
CREATE TABLE tb1(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(20),
age TINYINT UNSIGNED, //UNSIGNED 无符号位
salary FLOAT(8,2) UNSIGNED
PRIMARY KEY(id);
)

创建表添加声明主键 AUTO_INCREMENT 必须要 PRIMARY KEY(主键名)

2.查看数据表

SHOW TABLES [FROM db_name] [LIKE 'PATTERN' | WHERE EXPR]

3.查看表结构

SHOW COLUMNS FROM TABLENAME
columns

4.插入记录

INSERT [INTO] TABLENAME [(COL_NAME,...)] VALUES(val,...)

VALUES 插入所有列记录

INSERT TABLENAME(COL_NAME) VALUES (val); 单列记录

列： INSERT INTO runoob_tbl
->(runoob_title, runoob_author, submission_date)
->VALUES
->("JAVA Tutorial", "Sanjay", '2007-05-06');

DATE类型：2007-05-06 声明当前时间为 NOW();

5.查询记录

SELECT column_name,column_name FROM table_name [WHERE Clause]
[OFFSET M ][LIMIT N]

可以使用WHERE 语句包含条件。OFFSET指定SELECT语句开始查询数据偏移量。 LIMIT属性设置返回记录数
※查询乱码问题
set character_set_results=gb2312;
在查询

6.空值与非空

NULL 字段值可以为空

NOTNULL 字段禁止为空

CREATE TABLE tb1(
username VARCHAR(20) NOTNULL, //不许为空
age TINYINT UNSIGNED, //UNSIGNED 无符号位
salary FLOAT(8,2) UNSIGNED
)

八。数据类型

1.整型

TINYINT<SMALLINT<MEDIUMINT<INT<BIGINT

2.浮点型

FLOAT[(M,D)]<DOUBLE[(M,D)]

M是数字总位数，D是小数点后面的位数

3.日期时间型

YEAR 1 < TIME 3 < DATE 3 < DATETIME 8 < TIMESTAMP 4

4.字符型

略

九。MySQL WHERE语句

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

使用WHERE 可以使用AND 或者 OR 指定一个或多个条件
列：SELECT * from runoob_tbl WHERE runoob_author='Sanjay';

※：WHERE默认子句字符串是不区分大小写的，可以使用BINARY 关键字来设定WHERE子句的字符串比较是区分大小写的
列： SELECT * from runoob_tbl WHERE BINARY runoob_author='sanjay';

WHERE 子句也可以运行SQL DELETE 或者 UPDATE命令

WHERE 子句也可以类似于if条件

十。MySQL UPDATE 查询

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

可在WHERE指定条件：

列：mysql> UPDATE runoob_tbl
-> SET runoob_title='Learning JAVA'
-> WHERE runoob_id=3;

十一。MySQL DELETE 语句

DELETE FROM TABLENAME WHERE ..;

如果没有指定WHERE MySQL表中所有记录被删除

列：mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;

十一。MySQL INSERT 语句
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );

十二。MySQL SELECT查询数据..

SELECT column_name,column_name FROM table_name
[WHERE Clause] [OFFSET M ][LIMIT N]

列名：column_name

※：如果数据是字符型必须使用单引号或者双引号 如：'张三'.

列：mysql> SELECT * FROM tcount_tbl
-> WHERE runoob_count IS NULL;
+-----------------+----------------+
| runoob_author | runoob_count |
+-----------------+----------------+
| mahnaz | NULL |
| Jen | NULL |
+-----------------+----------------+

mysql> SELECT * from tcount_tbl
-> WHERE runoob_count IS NOT NULL;
+-----------------+----------------+
| runoob_author | runoob_count |
+-----------------+----------------+
| mahran | 20 |
| Gill | 20 |
+-----------------+----------------+

列名：field1

十四。MySQL LIKE 子句

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue';

列：mysql> SELECT * from runoob_tbl WHERE runoob_author LIKE '%jay';

十五。MYSQL排序

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

※：ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。
默认情况下，它是按升序排列。

列：SELECT * from runoob_tbl ORDER BY runoob_author ASC;
d

十六。分组

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

※：在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

列：SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+

列： SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | singin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+

列：SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+

十七.Mysql 连接的使用

JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
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;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
| 1 | John Poul | 1 |
| 3 | Sanjay | 1 |
+-----------+---------------+--------------+

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;
+-------------+-----------------+----------------+
| runoob_id | runoob_author | runoob_count |
+-------------+-----------------+----------------+
| 1 | John Poul | 1 |
| 2 | Abdul S | NULL |
| 3 | Sanjay | 1 |
+-------------+-----------------+----------------+

※：该语句会读取左边的数据表runoob_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的runoob_author字段值。

RIGHT JOIN列：
SELECT b.runoob_id, b.runoob_author, a.runoob_count FROM tcount_tbl a RIGHT JOIN runoob_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| runoob_id | runoob_author | runoob_count |
+-------------+-----------------+----------------+
| 1 | John Poul | 1 |
| 2 | Abdul S | NULL |
| 3 | Sanjay | 1 |
+-------------+-----------------+----------------+

※：该语句会读取右边的数据表 runoob_tbl 的所有选取的字段数据，即便在左侧表tcount_tbl中没有对应的runoob_author字段值。

十八。MYSQL 正则表达式

模式 描述
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ' ' 或 ' ' 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ' ' 或 ' ' 之前的位置。
. 匹配除 " " 之外的任何单个字符。要匹配包括 ' ' 在内的任何字符，请使用象 '[. ]' 的模式。
[...] 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...] 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
* 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+ 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

列：

查找name字段中以'st'为开头的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头且以'ok'字符串结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

REGEXP正则表达式对象

十九。MYSQL事务

事务处理可以维护数据库完整性，保证成批SQL语句要么全部执行，要不全部不执行

数据库事务的四大特性(简称ACID)是：
(1) 原子性(Atomicity)
事务的原子性指的是，事务中包含的程序作为数据库的逻辑工作单位，它所做的对数据修改操作要么全部执行，要么完全不执行。这种特性称为原子性。
例如银行取款事务分为2个步骤(1)存折减款(2)提取现金。不可能存折减款，却没有提取现金。2个步骤必须同时完成或者都不完成。
(2)一致性(Consistency)
事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。这种特性称为事务的一致性。假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。
例如完整性约束a+b=10，一个事务改变了a，那么b也应随之改变。
(3)分离性(亦称独立性Isolation)
分离性指并发的事务是相互隔离的。即一个事务内部的操作及正在操作的数据必须封锁起来，不被其它企图进行修改的事务看到。假如并发交叉执行的事务没有任何控制，操纵相同的共享对象的多个并发事务的执行可能引起异常情况。
(4)持久性(Durability)
持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。即一旦一个事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，即对已提交事务的更新能恢复。持久性通过数据库备份和恢复来保证。

开启一个事务
start transaction

做保存点
savapoint 保存点名称

二十。MYSQL ALTER命令

删除字段名
mysql> ALTER TABLE testalter_tbl DROP i;

添加字段名
mysql> ALTER TABLE testalter_tbl ADD i INT;

修改字段类型及名称：使用 MODIFY 或 CHANGE 子句 。
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

mysql> ALTER TABLE testalter_tbl
-> MODIFY j BIGINT NOT NULL DEFAULT 100;

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

修改字段默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

ALTER 命令及 DROP子句来删除字段的默认值
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

通过ALTER 命令以及TYPE子句修改表的类型
ALTER TABLE testalter_tbl TYPE = MYISAM；

通过ALTER 命令修改表名
ALTER TABLE testalter_tbl RNAME TO alt_name;

修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length))

二十一。MySQL 索引

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

1.创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));

※：如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

2.修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

3.创建表的时候直接指定索引

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length))

);

4.删除索引的语法

DROP INDEX [indexName] ON mytable;

5.唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length))

);

6.使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list):
添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

8.使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。

实例如下：
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

9.ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息，可以通过添加 G 来格式化输出信息。
mysql> SHOW INDEX FROM table_nameG

二十二。MySQL （temporary ：临时的）临时表

创建临时表：

CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);

插入临时表信息：
INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);

删除临时表
mysql> SELECT * FROM SalesSummary;