MYSQL的初步理解-CSDN博客

本文链接：https://blog.csdn.net/sherlocklcy/article/details/127058668

MYSQL

语法

# connect
mysql -u root -p
password
 
# exit
exit
 
# create database 
CREATE DATABASE databaseName;

mysqladmin -u root -p create databaseName
Enter password:******

# user database
use database;

# drop database
drop database databaseName;

mysqladmin -u root -p drop databaseName
Enter password:******

# create table
CREATE TABLE table_name (column_name column_type);

CREATE TABLE IF NOT EXISTS `table_name`(
   `column_name1` INT UNSIGNED AUTO_INCREMENT,
   `column_name2` VARCHAR(100) NOT NULL,
   `column_name3` VARCHAR(40) NOT NULL,
   `column_name4` DATE,
   PRIMARY KEY ( `column_name1` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

# drop table
DROP TABLE table_name ;

# insert data into current table
INSERT INTO table_name ( column_name1, column_name2,...column_nameN )
                       VALUES
                       ( value1, value2,...valueN );
                       
# query data
SELECT column_name1,column_name2
FROM table_name
[WHERE Clause]	
[LIMIT N][ OFFSET M] # OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

# where 子句
    使用 WHERE 指定条件。
    使用 关系操作符、AND 或者 OR 指定一个或多个条件。

# updata data
UPDATE table_name SET column_name1=new_value1, column_name2=new_value2
[WHERE Clause]

# detele data
DELETE FROM table_name [WHERE Clause]

# LIKE 子句
    使用 LIKE 进行模糊匹配。
    LIKE 通常与 % 一同使用， % 字符表示任意字符串

SELECT column_name1, column_name2,...column_nameN 
FROM table_name
WHERE column_name1 LIKE condition1 [AND [OR]] column_name2 = 'somevalue'

# UNION 操作符
    MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。
    多个 SELECT 语句会删除重复的数据。
    
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
    "Websites" 和 "apps" 表中选取所有不同的country
    使用 UNION ALL 来选取重复的值！

# ORDER BY 子句
	设定字段的排序方式
SELECT column_name1, column_name2,...column_nameN FROM table_name1, table_name2...
ORDER BY column_name1 [ASC [DESC][默认 ASC]], [column_name2...] [ASC [DESC][默认 ASC]]    
    	
# GROUP BY 语句
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

SELECT name, COUNT(*) FROM  employee_tbl GROUP BY name;
	GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：
	function() includes COUNT, SUM, AVG
	
# 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;
    INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
    LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
    RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
    
#  NULL 值处理
	使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
	IS NULL: 当列的值是 NULL,此运算符返回 true。
    IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
    <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;    

# ALTER命令
修改数据表名或者修改数据表字段时
ALTER TABLE table_name  DROP 字段;
ALTER TABLE table_name ADD new_字段 new_type;
ALTER TABLE table_name MODIFY 字段 new_type;
ALTER TABLE table_name CHANGE 字段 new_字段 new_type
ALTER TABLE table_name 
    -> MODIFY 字段 new_type NOT NULL DEFAULT new_DEFAULT;;
ALTER TABLE table_name ALTER 字段 SET  DEFAULT new_DEFAULT;    
	ALTER DEFAULT
	
#  索引	
CREATE INDEX indexName ON table_name (column_name)
	如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
ALTER table tableName ADD INDEX indexName(column_name)

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

DROP INDEX [indexName] ON mytable; 

唯一索引
	索引列的值必须唯一，但允许有空值
CREATE UNIQUE INDEX indexName ON tabel_name(column_name(length))
ALTER table mytable ADD UNIQUE indexName(column_name(length))

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

正则表达式

背景
- MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

^			匹配输入字符串的开始位置。如果设置了 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 次。	

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

事务

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

语法

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。

使用
	1、用 BEGIN, ROLLBACK, COMMIT来实现
        BEGIN 开始一个事务
        ROLLBACK 事务回滚
        COMMIT 事务确认
    2、直接用 SET 来改变 MySQL 的自动提交模式
        SET AUTOCOMMIT=0 禁止自动提交
        SET AUTOCOMMIT=1 开启自动提交

临时表

定义
- MySQL 临时表在我们需要保存一些临时数据时是非常有用的。

语法

# CREATE
CREATE TEMPORARY TABLE table_name (
    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
INSERT INTO SalesSummary
    (product_name, total_sales, avg_unit_price, total_units_sold)
   	VALUES('cucumber', 100.25, 90, 2);

# QUERY
SELECT * FROM SalesSummary;

# DROP
DROP TABLE SalesSummary;

复制表

operate
- Select the database in Navicat and select SQL Dump.
- Modify the generated file as needed.

元数据

查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息： 包含了数据库及数据表的结构信息。
MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。

table 使用序列

作用
- 由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现

使用

CREATE TABLE table_name(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id),
    name VARCHAR(30) NOT NULL, # type of insect
    date DATE NOT NULL, # date collected
    origin VARCHAR(30) NOT NULL # where collected
);

table 重复数据

防止表中出现重复数据
- 在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。

统计重复数据

# 统计表中 first_name 和 last_name的重复记录数
SELECT COUNT(*) as repetitions, last_name, first_name
FROM table_name
GROUP BY last_name, first_name
HAVING repetitions > 1;

过滤重复数据

# 过滤重复的last_name和first_name
SELECT DISTINCT last_name, first_name
FROM table_name

# 读取数据表中不重复的数据
SELECT last_name, first_name
FROM table_name
GROUP BY (last_name, first_name);

删除重复数据

# 删除重复数据
CREATE TABLE tmp SELECT last_name, first_name, sex FROM table_name  GROUP BY (last_name, first_name, sex);
DROP TABLE table_name;
mysql> ALTER TABLE tmp RENAME TO table_name;

# 删除重复数据
ALTER IGNORE TABLE table_name
ADD PRIMARY KEY (last_name, first_name);

Q&A

ENGINE=InnoDB

定义
- 存储引擎是innodb。
作用
- innoDB 是 MySQL 上第一个提供外键约束的数据存储引擎，除了提供事务处理外，
- InnoDB 还支持行锁，
  - 提供和 Oracle 一样的一致性的不加锁读取，
  - 能增加并发读的用户数量并提高性能，不会增加锁的数量。
- InnoDB 的设计目标是处理大容量数据时最大化性能，
  - 它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。
背景
- InnoDB 是一套放在 MySQL 后台的完整数据库系统，
- InnoDB 有它自己的缓冲池，能缓冲数据和索引，
- InnoDB 还把数据和索引存放在表空间里面，可能包含好几个文件，
  - 这和 MyISAM 表完全不同，在 MyISAM 中，表被存放在单独的文件中，
  - InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。