Mysql相关

1、创建数据库

create database lixx;
create schema [数据库名称] default character set utf8 collate utf8_general_ci;--创建数据库

2、删除数据库

drop database lixx;

3、创建用户并授权

create user '[用户名称]'@'%' identified by '[用户密码]';--创建用户
密码8位以上，包括：大写字母、小写字母、数字、特殊字符
%：匹配所有主机，该地方还可以设置成'localhost'，代表只能本地访问，例如root账户默认'localhost'
grant select,insert,update,delete,create on [数据库名称].* to [用户名称];--用户授权数据库

4、选择数据库

use lixx;

5、Mysql数据类型

http://www.runoob.com/mysql/mysql-data-types.html

6、创建表

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;

7、删除表

DROP TABLE table_name ;

8、插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
                        VALUES
                       ( value1, value2,...valueN );

9、查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]   --LIMIT返回数据条数  OFFSET偏移量设置

10、WHERE语句

11、UPDATE语句

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

12、DELETE语句

DELETE FROM table_name [WHERE Clause]

13、LIKE子句

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

SELECT * from runoob_tbl where runoob_title like '%JAVA%'

14、UNION操作

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据

15、MYSQL排序

SELECT * from runoob_tbl ORDER BY submission_date ASC;  --升序asc降序desc

16、GROUP BY语句

GROUP BY 语句根据一个或多个列对结果集进行分组。

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

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

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

SELECT name,SUM(singin) as singin_count from employee_tbl GROUP BY name WITH ROLLUP

17、连接

你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT 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 
LEFT  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 
RIGHT  JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

18、Mysql NULL值处理

select * , columnName1+ifnull(columnName2,0) from tableName;
columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。

为了处理这种情况，MySQL提供了三大运算符:
IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true

19、Mysql正则表达式

查找name字段中以'st'为开头的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含'mar'字符串的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

20、Mysql事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

BEGIN或START TRANSACTION；显式地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
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 开启自动提交

21、ALTER命令

（1）使用 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：

ALTER TABLE testalter_tbl  DROP i;

（2）使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型

ALTER TABLE testalter_tbl ADD i INT;

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：

ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

（3）修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

 ALTER TABLE testalter_tbl MODIFY c CHAR(10);

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

 ALTER TABLE testalter_tbl CHANGE i j BIGINT;

（4）ALTER TABLE 对 Null 值和默认值的影响

指定字段 j 为 NOT NULL 且默认值为100

 ALTER TABLE testalter_tbl 
 MODIFY j BIGINT NOT NULL DEFAULT 100;

你可以使用 ALTER 来修改字段的默认值，尝试以下实例：

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

（5）修改表名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

（6）修改存储引擎

修改为myisam

alter table tableName engine=myisam;

（7）删除外键约束

keyName是外键别名

alter table tableName drop foreign key keyName;

（8）修改字段的相对位置

这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面

alter table tableName modify name1 type1 first|after name2;

22、索引

普通索引

（1）创建索引

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

（2）修改表结构（添加索引）

ALTER table tableName ADD INDEX indexName(columnName)

（3）创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);

（4）删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

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

（1）创建索引

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

（2）修改表结构

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

（3）创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
 
);

使用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 ，用于全文索引。

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

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
 ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
 ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键：
ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

SHOW INDEX FROM table_name; \G

23、复制表

（1）完全复制表

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

（2）拷贝一个表中其中的一些字段

CREATE TABLE newadmin AS
(
    SELECT username, password FROM admin
)

（3）创建表的同时定义表中的字段信息

CREATE TABLE newadmin
(
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
    SELECT * FROM admin
)

24、序列使用

由于mysql和oracle不太一样，不支持直接的sequence，所以需要创建一张table来模拟sequence的功能。

（1）创建sequence表

CREATE TABLE `sequence` (
  `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '序列的名字',
  `current_value` int(11) NOT NULL COMMENT '序列的当前值',
  `increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

（2）创建–取当前值的函数

BEGIN 
     DECLARE value INTEGER; 
     SET value = 0; 
     SELECT current_value INTO value 
          FROM sequence 
          WHERE name = seq_name; 
     RETURN value; 
END

（3）创建–取下一个值的函数

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '' 
BEGIN 
     UPDATE sequence 
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

（4）创建–更新当前值的函数

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '' 
BEGIN 
     UPDATE sequence 
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

（5）测试

INSERT INTO sequence VALUES ('testSeq', 0, 1);--添加一个sequence名称和初始值，以及自增幅度

SELECT SETVAL('testSeq', 10);--设置指定sequence的初始值

SELECT CURRVAL('testSeq');--查询指定sequence的当前值

SELECT NEXTVAL('testSeq');--查询指定sequence的下一个值

25、处理重复数据

（1）统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

 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

(2)过滤重复数据

如果你需要读取不重复的数据可以在 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);

（3）删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然你也可以在数据表中添加 INDEX（索引）和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

26、导出数据

使用 SELECT ... INTO OUTFILE 语句导出数据

SELECT * FROM runoob_tbl 
INTO OUTFILE '/tmp/runoob.txt';

连接:http://www.runoob.com/mysql/mysql-database-export.html

27、导入数据

链接：http://www.runoob.com/mysql/mysql-database-import.html

28、函数

链接：http://www.runoob.com/mysql/mysql-functions.html

29、运算符

链接：http://www.runoob.com/mysql/mysql-operator.html

posted @ 2019-02-15 20:29 李清欣阅读(311) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

qingxin