mysql基础知识笔记总结

创建数据库

在与数据进行任何其他操作之前，需要创建一个数据库。数据库是数据的容器。它可以用于存储联系人，供应商，客户或任何想存储的数据。 在MySQL中，数据库是用于存储和操作诸如表，数据库视图，触发器，存储过程等数据的对象的集合。

要在MySQL中创建数据库，请使用CREATE DATABASE语句，如下：

CREATE DATABASE [IF NOT EXISTS] database_name;
SQL

我们来更详细地看看CREATE DATABASE语句：

  • CREATE DATABASE语句的后面是要创建的数据库名称。建议数据库名称尽可能是有意义和具有一定的描述性。
  • IF NOT EXISTS是语句的可选子句。 IF NOT EXISTS子句可防止创建数据库服务器中已存在的新数据库的错误。不能在MySQL数据库服务器中具有相同名称的数据库。

例如，要创建一个名称为mytestdb数据库，可以执行CREATE DATABASE语句后接数据库名称：mytestdb，如果当前MySQL服务器中没有数据库：mytestdb，则创建成功，如下所示：

CREATE DATABASE IF NOT EXISTS mytestdb;
SQL

执行此语句后，MySQL返回一条消息，通知新数据库是否已成功创建。

显示数据库

SHOW DATABASES语句显示MySQL数据库服务器中的所有数据库。您可以使用SHOW DATABASES语句来查看您要创建的数据库，或者在创建新数据库之前查看数据库服务器上的所有数据库，例如：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| yiibaidb           |
+--------------------+
5 rows in set

在此MySQL数据库服务器中有6个数据库。 information_schema，performance_schema和mysql是我们安装MySQL时可用的默认数据库，而yiibaidb是创建的新数据库。

选择要使用的数据库

在使用指定数据库之前，必须通过使用USE语句告诉MySQL要使用哪个数据库。

USE database_name;
 

删除数据库

删除数据库意味着数据库中的所有数据和关联对象将被永久删除，并且无法撤消。 因此，用额外的注意事项执行此查询是非常重要的。

要删除数据库，请使用DROP DATABASE语句，如下所示：

DROP DATABASE [IF EXISTS] database_name;
SQL

遵循DROP DATABASE是要删除的数据库名称。 与CREATE DATABASE语句类似，IF EXISTS是该语句的可选部分，以防止您删除数据库服务器中不存在的数据库。

如果要使用DROP DATABASE语句练习，可以创建一个新数据库，然后将其删除。来看下面的查询：

CREATE DATABASE IF NOT EXISTS tempdb;
SHOW DATABASES;
DROP DATABASE IF EXISTS tempdb;
SQL

三个语句的说明如下：

  • 首先，使用CREATE DATABASE语句创建了一个名为tempdb的数据库。
  • 第二，使用SHOW DATABASES语句显示所有数据库。
  • 第三，使用DROP DATABASE语句删除了名为tempdb的数据库。

MySQL 创建表

要在数据库中创建一个新表，可以使用MySQL CREATE TABLE语句。 CREATE TABLE语句是MySQL中最复杂的语句之一。

下面以简单的形式来说明CREATE TABLE语句的语法：

CREATE TABLE [IF NOT EXISTS] table_name(
        column_list
) engine=table_type;
 

我们来更详细地来查看其语法：

  • 首先，指定要在CREATE TABLE子句之后创建的表的名称。表名在数据库中必须是唯一的。 IF NOT EXISTS是语句的可选部分，允许您检查正在创建的表是否已存在于数据库中。 如果是这种情况，MySQL将忽略整个语句，不会创建任何新的表。 强烈建议在每个CREATE TABLE语句中使用IF NOT EXISTS来防止创建已存在的新表而产生错误。

  • 其次，在column_list部分指定表的列表。字段的列用逗号(，)分隔。我们将在下一节中向您展示如何更详细地列(字段)定义。

  • 第三，需要为engine子句中的表指定存储引擎。可以使用任何存储引擎，如：InnoDB，MyISAM，HEAP，EXAMPLE，CSV，ARCHIVE，MERGE， FEDERATED或NDBCLUSTER。如果不明确声明存储引擎，MySQL将默认使用InnoDB。

要在CREATE TABLE语句中为表定义列，请使用以下语法：

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] 
[AUTO_INCREMENT]

以上语法中最重要的组成部分是：

  • column_name指定列的名称。每列具有特定数据类型和大小，例如：VARCHAR(255)。
  • NOT NULL或NULL表示该列是否接受NULL值。
  • DEFAULT值用于指定列的默认值。
  • AUTO_INCREMENT指示每当将新行插入到表中时，列的值会自动增加。每个表都有一个且只有一个AUTO_INCREMENT列。

MySQL 创建表示例

下面让我们练习一个例子，在数据库中创建一个名为tasks的新表，如下所示：

可以使用CREATE TABLE语句创建这个tasks表，如下所示：

CREATE TABLE IF NOT EXISTS tasks (
  task_id INT(11) NOT NULL AUTO_INCREMENT,
  subject VARCHAR(45) DEFAULT NULL,
  start_date DATE DEFAULT NULL,
  end_date DATE DEFAULT NULL,
  description VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (task_id)
) ENGINE=InnoDB;

MySQL 修改表结构

可以使用ALTER TABLE语句来更改现有表的结构。 ALTER TABLE语句可用来添加列，删除列，更改列的数据类型，添加主键，重命名表等等。 以下说明了ALTER TABLE语句语法：

ALTER TABLE table_name action1[,action2,…]

要更改现有表的结构：

  • 首先，在ALTER TABLE子句之后指定要更改的表名称。
  • 其次，列出一组要应用于该表的操作。操作可以是添加新列，添加主键，重命名表等任何操作。ALTER TABLE语句允许在单个ALTER TABLE语句中应用多个操作，每个操作由逗号(，)分隔。

示例：

我们在数据库中创建一个名为tasks的新表。 以下是创建tasks表的脚本。

DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks (
    task_id INT NOT NULL,
    subject VARCHAR(45) NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    description VARCHAR(200) NULL,
    PRIMARY KEY (task_id),
    UNIQUE INDEX task_id_unique (task_id ASC)
);

使用 ALTER语句来设置列的自动递增属性

假设您希望在任务表中插入新行时，task_id列的值会自动增加1。那么可以使用ALTER TABLE语句将task_id列的属性设置为AUTO_INCREMENT，如下所示：

ALTER TABLE tasks
CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;

使用 ALTER从表中删除列

假设您不想将任务的描述存储在tasks表中了，并且必须将其删除。 以下语句允许您删除tasks表的description列：

ALTER TABLE tasks
DROP COLUMN description;

使用ALTER 语句重命名表

可以使用ALTER TABLE语句重命名表。请注意，在重命名表之前，应该认真考虑以了解更改是否影响数据库和应用程序层，不要因为重命名表之后，应用程序因未找到数据库表而出错。

以下语句将tasks表重命名为work_items表：

ALTER TABLE tasks
RENAME TO work_items;

 

MySQL SELECT语句

使用SELECT语句从表或视图获取数据。

SELECT语句的语法：

SELECT 
    column_1, column_2, ...
FROM
    table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
    conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;
 

SELECT语句由以下列表中所述的几个子句组成：

  • SELECT之后是逗号分隔列或星号(*)的列表，表示要返回所有列。
  • FROM指定要查询数据的表或视图。
  • JOIN根据某些连接条件从其他表中获取数据。
  • WHERE过滤结果集中的行。
  • GROUP BY将一组行组合成小分组，并对每个小分组应用聚合函数。
  • HAVING过滤器基于GROUP BY子句定义的小分组。
  • ORDER BY指定用于排序的列的列表。
  • LIMIT限制返回行的数量。

语句中的SELECT和FROM语句是必须的，其他部分是可选的。

SELECT语句允许通过在SELECT子句中指定逗号分隔列的列表来查询表的部分数据。 例如，如果要仅查看员工的名字，姓氏和职位，请使用以下查询：

SELECT 
    lastname, firstname, jobtitle
FROM
    employees;

语句-1

SELECT lastname, firstname, jobtitle FROM employees;

语句-2

SELECT * FROM employees;

不建议使用*号来返回表中所有的列和行，建议显式获取数据的列，原因如下：
  • 使用星号(*)可能会返回不使用的列的数据。 它在MySQL数据库服务器和应用程序之间产生不必要的I/O磁盘和网络流量。
  • 如果明确指定列，则结果集更可预测并且更易于管理。 想象一下，当您使用星号(*)并且有人通过添加更多列来更改表格数据时，将会得到一个与预期不同的结果集。
  • 使用星号(*)可能会将敏感信息暴露给未经授权的用户。

 MySQL WHERE子句简介

如果使用SELECT语句但不使用WHERE子句在表中查询数据，则会获取表中的所有行记录，这些行记录中大部分是不想要的行记录。例如，在一些表中存放商业交易中的数据。 从这些表中获取所有行，尤其是对于诸如员工，销售订单，采购订单，生产订单等的大型表格来说，这是没有意义的，因为我们经常想要的是一些特定的数据，例如本季度的销售额 ，今年销量比去年同期的销量等等。

WHERE子句允许根据指定的过滤表达式或条件来指定要选择的行。

假设只想从employees表中获取销售代表员工，可使用以下查询：

SELECT 
    lastname, firstname, jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep';

可以像上面的查询一样形成一个简单的条件，或者是将多个表达式与逻辑运算符(如AND，OR等)组合在一起的一个非常复杂的例子。例如，要在办公室代码(officeCode)等于1中查找所有销售代表，请使用以下查询：

SELECT 
    lastname, firstname, jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' AND officeCode = 1;

以下查询使用不等于(!=)运算符来获取不是销售代表的其它所有员工：

SELECT 
    lastname, firstname, jobtitle
FROM
    employees
WHERE
    jobtitle <> 'Sales Rep';

还有一些有用的运算符可以在WHERE子句中使用来形成复杂的条件，例如：

  • BETWEEN选择在给定范围值内的值。
  • LIKE匹配基于模式匹配的值。
  • IN指定值是否匹配列表中的任何值。
  • IS NULL检查该值是否为NULL。

MySQL插入语句

MySQL INSERT语句允许您将一行或多行插入到表中。下面说明了INSERT语句的语法：

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...);
 

首先，在INSERT INTO子句之后，在括号内指定表名和逗号分隔列的列表。
然后，将括号内的相应列的逗号分隔值放在VALUES关键字之后。

在执行插入语句前，需要具有执行INSERT语句的INSERT权限。

例如，如果要将任务插入到tasts表中，则使用INSERT语句如下：

INSERT INTO tasks(subject,start_date,end_date,description)
VALUES('Learn MySQL INSERT','2017-07-21','2017-07-22','Start learning..');

想要在表中一次插入多行，可以使用具有以下语法的INSERT语句：

INSERT INTO table(column1,column2...)
VALUES (value1,value2,...),
       (value1,value2,...),
...;

在这种形式中，每行的值列表用逗号分隔。 例如，要将多行插入到tasks表中，请使用以下语句：

INSERT INTO tasks(subject,start_date,end_date,description)
VALUES ('任务-1','2017-01-01','2017-01-02','Description 1'),
       ('任务-2','2017-01-01','2017-01-02','Description 2'),
       ('任务-3','2017-01-01','2017-01-02','Description 3');

具有SELECT子句的MySQL INSERT

在MySQL中，可以使用SELECT语句返回的列和值来填充INSERT语句的值。 此功能非常方便，因为您可以使用INSERT和SELECT子句完全或部分复制表，如下所示：

INSERT INTO table_1
SELECT c1, c2, FROM table_2;
 

假设要将tasks表复制到tasks_bak表。

首先，通过复制tasks表的结构，创建一个名为tasks_bak的新表，如下所示：

CREATE TABLE tasks_bak LIKE tasks;
 

第二步，使用以下INSERT语句将tasks表中的数据插入tasks_bak表：

INSERT INTO tasks_bak
SELECT * FROM tasks;

如果新行违反主键(PRIMARY KEY)或UNIQUE约束，MySQL会发生错误。 例如，如果执行以下语句：

INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority');

MySQL很不高兴，并向你扔来一个错误消息：

Error Code: 1062. Duplicate entry '4' for key 'PRIMARY' 0.016 sec

因为表中的主键task_id列已经有一个值为 4 的行了，所以该语句违反了PRIMARY KEY约束。

但是，如果在INSERT语句中指定ON DUPLICATE KEY UPDATE选项，MySQL将插入新行或使用新值更新原行记录。

例如，以下语句使用新的task_id和subject来更新task_id为4的行。

INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority')
ON DUPLICATE KEY UPDATE 
   task_id = task_id + 1, 
   subject = 'Test ON DUPLICATE KEY UPDATE';

MySQL更新表数据

我们使用UPDATE语句来更新表中的现有数据。也可以使用UPDATE语句来更改表中单个行，一组行或所有行的列值。

下面说明了MySQL UPDATE语句的语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
WHERE
    condition;

在上面UPDATE语句中：

  • 首先，在UPDATE关键字后面指定要更新数据的表名。
  • 其次，SET子句指定要修改的列和新值。要更新多个列，请使用以逗号分隔的列表。以字面值，表达式或子查询的形式在每列的赋值中来提供要设置的值。
  • 第三，使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。 如果省略WHERE子句，则UPDATE语句将更新表中的所有行。

请注意，WHERE子句非常重要，所以不应该忘记指定更新的条件。 有时，您可能只想改变一行; 但是，可能会忘记写上WHERE子句，导致意外更新表中的所有行。

MySQL在UPDATE语句中支持两个修饰符。

  • LOW_PRIORITY修饰符指示UPDATE语句延迟更新，直到没有从表中读取数据的连接。 LOW_PRIORITY对仅使用表级锁定的存储引擎(例如MyISAM，MERGE，MEMORY)生效。
  • 即使发生错误，IGNORE修饰符也可以使UPDATE语句继续更新行。导致错误(如重复键冲突)的行不会更新。
实例：使用UPDATE语句将Mary的电子邮件更新为新的电子邮件：mary.new@yiibai.com，如下查询所示：
UPDATE employees 
SET 
    email = 'mary.new@yiibai.com'
WHERE
    employeeNumber = 1056;
因为上面语句中，只想更新一行，所以使用WHERE子句来指定更新的是员工编号1056的行。SET子句将电子邮件列的值设置为新的电子邮件。

要更新多列中的值，需要在SET子句中指定分配。例如，以下语句更新了员工编号1056的姓氏和电子邮件列：

UPDATE employees 
SET 
    lastname = 'Hill',
    email = 'mary.hill@yiibai.com'
WHERE
    employeeNumber = 1056;

　　

MySQL删除表

要从表中删除数据，请使用MySQL DELETE语句。下面说明了DELETE语句的语法：

DELETE FROM table_name
WHERE condition;

在上面查询语句中 -

  • 首先，指定删除数据的表(table_name)。
  • 其次，使用条件来指定要在WHERE子句中删除的行记录。如果行匹配条件，这些行记录将被删除。

请注意，WHERE子句是可选的。如果省略WHERE子句，DELETE语句将删除表中的所有行。

除了从表中删除数据外，DELETE语句返回删除的行数。

要使用单个DELETE语句从多个表中删除数据，请阅读下一个教程中将介绍的DELETE JOIN语句。

要删除表中的所有行，而不需要知道删除了多少行，那么应该使用TRUNCATE TABLE语句来获得更好的执行性能。

对于具有外键约束的表，当从父表中删除行记录时，子表中的行记录将通过使用ON DELETE CASCADE选项自动删除。

请注意，一旦删除数据，它就会永远消失。 因此，在执行DELETE语句之前，应该先备份数据库，以防万一要找回删除过的数据。

假设要删除officeNumber为4的员工，则使用DELETE语句与WHERE子句作为以下查询：

DELETE FROM employees 
WHERE
    officeCode = 4;
 

要删除employees表中的所有行，请使用不带WHERE子句的DELETE语句，如下所示：

DELETE FROM employees;

在执行上面查询语句后，employees表中的所有行都被删除。

 

如果要限制要删除的行数，则使用LIMIT子句，如下所示：

DELETE FROM table
LIMIT row_count;

请注意，表中的行顺序未指定，因此，当您使用LIMIT子句时，应始终使用ORDER BY子句，不然删除的记录可能不是你所预期的那样。

DELETE FROM table_name
ORDER BY c1, c2, ...
LIMIT row_count;

例如，以下语句按客户名称按字母排序客户，并删除前10个客户：

DELETE FROM customers
ORDER BY customerName
LIMIT 10;
 

类似地，以下DELETE语句选择法国(France)的客户，按升序按信用额度(creditLimit)进行排序，并删除前5个客户：

DELETE FROM customers
WHERE country = 'France'
ORDER BY creditLimit
LIMIT 5;
 

类似地，以下DELETE语句选择法国(France)的客户，按升序按信用额度(creditLimit)进行排序，并删除前5个客户：

DELETE FROM customers
WHERE country = 'France'
ORDER BY creditLimit
LIMIT 5;
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jiansen/p/7357383.html