1.数据库
- 创建库
create database 库名;
- 删除库
drop databases 库名;
-查看所有库
SHOW DATABASES;
-使用数据库
USE 数据库名
-查看当前所在数据库
SELECT DATABASE();
-查看当前时间、用户名、数据库版本
SELECT now(), user(), version();
-查看库默认字符集
SHOW CREATE DATABASE 数据库名
-创建库
CREATE DATABASE 数据库名
-创建库指定字符集
CREATE DATABASE 数据库名 DEFAULT CHARSET 字符集名
-修改库的选项信息
ALTER DATABASE 库名 选项信息
-删除库,同时删除该数据库相关的目录及其目录内容
DROP DATABASE 数据库名
2.数据表
//创建
create table 表名(
字段名 数据类型 约束条件,
字段名 数据类型 约束条件
);
//删除表
DROP TABLE 表名;
//插入数据
INSERT INTO 表名 (字段1,字段2,字段3)
values
(值1,值2,值3),
(值1,值2,值3),
(值1,值2,值3);
-查看所有表
SHOW TABLES;
-查看表结构
DESC 表名;
-查看创建表的语句
SHOW CREATE TABLE 表名\G
-修改表名称
方式一:RENAME TABLE 旧表名 TO 新表名;
方式二:ALTER TABLE 旧表名 RENAME 新表名;
- 删除表数据
DELETE FROM 表名 WHERE 条件;
- 模糊查询 LIKE
// '%cm' 以cm结尾
// 'cm%' 以cm开头
// '%cm%' 中间带有cm
SELECT * FROM 表名 WHERE 字段 LIKE '% %';
3.数据类型
数值类型
类型 | 范围(有符号) |
---|---|
TINYINT | (-128,127) |
SMALLINT | (-32 768,32 767) |
MEDIUMINT | (-8 388 608,8 388 607) |
INT 或 INTEGER | (-2 147 483 648,2 147 483 647) |
BIGINT | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) |
FLOAT | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) |
DOUBLE | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) |
DECIMAL |
时间类型
类型 | 格式 |
---|---|
DATE | YYYY-MM-DD |
TIME | HH:MM:SS |
YEAR | YYYY |
DATETIME | YYYY-MM-DD HH:MM:SS |
TIMESTAMP | YYYYMMDD HHMMSS(时间戳) |
字符串类型
类型 | 用途 |
---|---|
CHAR | 定长字符串 |
VARCHAR | 变长字符串 |
TINYBLOB | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 短文本字符串 |
BLOB | 二进制形式的长文本数据 |
TEXT | 长文本数据 |
MEDIUMBLOB | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 中等长度文本数据 |
LONGBLOB | 二进制形式的极大文本数据 |
LONGTEXT | 极大文本数据 |
4.UNION
UNION 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)
UNION ALL 语句:用于将不同表中相同列中查询的数据展示出来;(包括重复数据)
使用形式如下:
SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称;
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称;
eg:
mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name | url | country |
+----+------------+-------------------------+---------+
| 1 | QQ APP | http://im.qq.com/ | CN |
| 2 | 微博 APP | http://weibo.com/ | CN |
| 3 | 淘宝 APP | https://www.taobao.com/ | CN |
+----+------------+-------------------------+---------+
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
带有 WHERE 的 SQL UNION ALL
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
5.排序 ORDER BY
ASC ---- 升序
DESC ---- 降序
默认为升序
SELECT * from 表名 ORDER BY 排序字段 ASC;
6.分组 GROUP BY
SELECT * FROM 表名 GROUP BY 分组字段
eg:
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | singin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:
mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | singin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+
其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:
select coalesce(a,b,c);
参数说明:如果a == null,则选择b;如果b == null,则选择c;如果a != null,则选择a;如果a b c 都为null ,则返回为null(没意义)。
以下实例中如果名字为空我们使用总数代替:
mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+
7.连接 JOIN
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。可省略 INNER ,只用 JOIN
----- SELECT a.id, a.author, bcount FROM tbl a INNER JOIN tbl b ON a.runoob_author = b.runoob_author;
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
----- SELECT a.id, a.author, b.count FROM tbl a LEFT JOIN tbl b ON a.author = b.author;
RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
【注】
SELECT a.id, a.author, b.count FROM t_al a INNER JOIN t_bl b ON a.author = author;
-- 等价于 --
SELECT a.id, a.author, b.count FROM t_al a , t_bl b WHERE a.author = author;
8. 空值处理 NULL
不能用 = 、!=
空值
------- SELECT * FROM 表名 WHERE runoob_count IS NULL
非空
------- SELECT * FROM 表名 WHERE runoob_count IS NOT NULL
9.正则表达式
模式 | 描述 |
---|---|
^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性,^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。 |
$ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性,$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。 |
. | 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符,请使用象 ‘[.\n]’ 的模式。 |
[…] | 字符集合。匹配所包含的任意一个字符。例如, ‘[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 次。 |
eg:
---- 查找name字段中以’st’为开头的所有数据:
SELECT name FROM 表名 WHERE name REGEXP '^st';
---- 查找name字段中以’ok’为结尾的所有数据:
SELECT name FROM 表名 WHERE name REGEXP 'ok$';
---- 查找name字段中包含’mar’字符串的所有数据:
SELECT name FROM 表名 WHERE name REGEXP 'mar';
---- 查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据:
SELECT name FROM 表名 WHERE name REGEXP '^[aeiou]|ok$';
10. 事务
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
事务控制语句:
- BEGIN 或 START TRANSACTION 显式地开启一个事务;
- COMMIT 等价于 COMMIT WORK,提交事务,并使已对数据库进行的所有修改成为永久性的;
- ROLLBACK 等价于 ROLLBACK WORK。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
- SAVEPOINT identifier,SAVEPOINT 允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT;
- RELEASE SAVEPOINT identifier 删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
- ROLLBACK TO identifier 把事务回滚到标记点;
- SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
MYSQL 事务处理主要有两种方法:
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
eg:
BEGIN;
INSERT INTO usertable (user_id) VALUES (666);
ROLLBACK; --COMMIT;
保留点 SAVEPOINT
savepoint 是在数据库事务处理中实现“子事务”(subtransaction),也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。
ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT,执行多条操作时,回滚到想要的那条语句之前。
SAVEPOINT savepoint_name; // 声明一个 savepoint
ROLLBACK TO savepoint_name; // 回滚到savepoint
删除 SAVEPOINT
保留点再事务处理完成(执行一条 ROLLBACK 或 COMMIT)后自动释放。
MySQL5 以来,可以用:
RELEASE SAVEPOINT savepoint_name; // 删除指定保留点
11.ALTER命令
修改数据表名或表字段时使用
表字段操作
---- DROP 删除字段 i 【只剩一个字段无法使用 DROP 删除】
ALTER TABLE 表名 DROP i;
---- ADD 添加字段 i 并定义数据类型【新增字段会添加到表字段末尾】
ALTER TABLE 表名 ADD i INT;
---- FIRST (在第一位新增字段)
ALTER TABLE 表名 ADD i INT FIRST;
---- AFTER 字段名(某个字段后新增)
ALTER TABLE 表名 ADD i INT AFTER c; //在 c 后增加 i 字段
修改字段类型及名称 【MODIFY、 CHANGE】
---- 把字段 c 的类型从 CHAR(1) 改为 CHAR(10)
ALTER TABLE 表名 MODIFY c CHAR(10);
---- 使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后,紧跟着的是你要修改的字段名,然后指定新字段名及类型。
ALTER TABLE 表名 CHANGE i j BIGINT;
ALTER TABLE 表名 CHANGE j j INT;
NULL值和默认值
---- 指定字段 j 为 NOT NULL 且默认值为100
ALTER TABLE 表名 MODIFY j BIGINT NOT NULL DEFAULT 100;
【注】:如果你不设置默认值,MySQL会自动设置该字段默认为 NULL。
---- 修改字段的默认值
ALTER TABLE 表名 ALTER i SET DEFAULT 1000;
---- 删除字段的默认值
ALTER TABLE 表名 ALTER i DROP DEFAULT;
修改数据表类型
---- 我们将表 testalter_tbl 的类型修改为 MYISAM
ALTER TABLE 表名 ENGINE = MYISAM;
修改表名
ALTER TABLE 原表名 RENAME TO 新表名;
删除外键约束:keyName是外键别名
ALTER TABLE 表名 DROP FOREIGN KEY keyName;
【注】:
- SHOW COLUMNS FROM 表名; //查看表结构
SHOW COLUMNS FROM usertable- SHOW TABLE STATUS //查看数据表类型
SHOW TABLE STATUS LIKE ‘usertable’
12.索引
【注】虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
创建索引
---- 普通索引【INDEX】 最基本的索引,它没有任何限制
1. CREATE 添加
CREATE INDEX 索引名 ON 表名(字段名(length)); //如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
eg: CREATE INDEX jobTitle ON yunyingzhidao(yyzd_admin_name);
2. ALTER TABLE 添加
ALTER table 表名 ADD INDEX 索引名(字段名);
eg: ALTER table yunyingzhidao ADD INDEX jobTitle(yyzd_admin_name);
==================================================================================================================
---- 唯一索引【UNIQUE INDEX】 值不能相同,但可为NULL;如果是组合索引,则列值的组合必须唯一
1. CREATE 添加
CREATE UNIQUE INDEX 索引名 ON 表名(字段名(length)) ;
eg: CREATE UNIQUE INDEX jobTitle ON yunyingzhidao(yyzd_application);
2.ALTER TABLE 添加
ALTER table 表名 ADD UNIQUE 索引名 (字段名(length));
eg: ALTER table yunyingzhidao ADD UNIQUE jobTitle (yyzd_application);
==================================================================================================================
---- 主键索引【PROMARY KEY】 索引值必须是唯一的,且不能为NULL
ALTER TABLE 表名 ADD PROMARY KEY (字段名) ;
==================================================================================================================
---- 全局索引 【FULLTEXT INDEX】 可以针对值中的某个单词,但效率很低
ALTER TABLE 表名 ADD FULLTEXT 索引名(字段名):
删除索引
DROP INDEX 索引名 ON 表名;
DROP INDEX jobTitle ON yunyingzhidao;
13. 临时表
临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
创建临时表
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
);
删除临时表
---- 默认情况下,当你断开与数据库的连接后,临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
DROP TABLE SalesSummary;
用查询直接创建临时表的方式:
CREATE TEMPORARY TABLE 临时表名 AS
(
SELECT * FROM 旧的表名
LIMIT 0,10000
);
14. 复制表
如果我们需要完全的复制MySQL的数据表,包括表的结构,索引,默认值等。 如果仅仅使用CREATE TABLE … SELECT 命令,是无法实现的。
完整的复制MySQL数据表,步骤如下:
- 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句,该语句包含了原数据表的结构,索引等。
- 复制以下命令显示的SQL语句,修改数据表名,并执行SQL语句,通过以上命令 将完全的复制数据表结构。
- 如果你想复制表的内容,你就可以使用 INSERT INTO … SELECT 语句来实现。
步骤一:
获取数据表的完整结构。
mysql> SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
ERROR:
No query specified
步骤二:
修改SQL语句的数据表名,并执行SQL语句。
mysql> CREATE TABLE `clone_tbl` (
-> `runoob_id` int(11) NOT NULL auto_increment,
-> `runoob_title` varchar(100) NOT NULL default '',
-> `runoob_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`runoob_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)
步骤三:
执行完第二步骤后,你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO… SELECT 语句来实现。
mysql> INSERT INTO clone_tbl (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
15. 处理重复数据
防止出现重复数据
设置指定的字段为 PRIMARY KEY(主键) 或者 UNIQUE(唯一) 索引来保证数据的唯一性
插入时使用 INSERT IGNORE INTO
INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。
INSERT IGNORE INTO 当插入数据时,在设置了记录的唯一性后,如果插入重复数据,将不返回错误,只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录,则先删除掉。再插入新记录。
统计重复数据
SELECT COUNT(*) as repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;
以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下,查询重复的值,请执行以下操作:
- 确定哪一列包含的值可能会重复。
- 在列选择列表使用COUNT(*)列出的那些列。
- 在GROUP BY子句中列出的列。
- HAVING子句设置重复数大于1。
过滤重复数据
如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
SELECT DISTINCT last_name, first_name FROM person_tbl;
也可以使用 GROUP BY 来读取数据表中不重复的数据
SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);
删除重复数据
//创建新表放入过滤后的数据
CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);
//删除原表
DROP TABLE person_tbl;
//更名
ALTER TABLE tmp RENAME TO person_tbl;
可以在数据表中添加 INDEX(索引) 和 PRIMAY KEY(主键)这种简单的方法来删除表中的重复记录。方法如下:
ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);