MySql基础知识总结

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

时间类型

类型格式
DATEYYYY-MM-DD
TIMEHH:MM:SS
YEARYYYY
DATETIMEYYYY-MM-DD HH:MM:SS
TIMESTAMPYYYYMMDD 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;

【注】:

  1. SHOW COLUMNS FROM 表名;                  //查看表结构

    SHOW COLUMNS FROM usertable

  2. 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);
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值