MySQL必知必会笔记-Mysql基本操作

时间:2021-09-24 14:59:17

Mysql基本操作

mysql的基本操作包括增、删、改、查,本书中前三章简单的介绍MySQL为何物,查是mysql中非常重要的功能,4-6章展示了mysql的查(查询——select)的简单实现,mysql的增(插入——insert)是第19章,改(更新——update)和删(删除——delete)是第20章,基本操作还包括21章表操作。

所以这篇文章归纳的是1-6,19-21章的笔记。

第一章 了解SQL

  • 主键
    • 含义:一列(或一组列),其值能够唯一区分表中每个行。
    • 习惯:
      • 不更新主键列中的值;
      • 不重用主键列的值;
      • 不在主键列中使用可能会更改的值。(例如,如果使用一个 名字作为主键以标识某个供应商,当该供应商合并和更改其 名字时,必须更改这个主键。)

第二章 MySQL简介

第三章 使用MySQL

连接

mysql -u root -p
  • -u: 用户名
  • -p: 输入密码

补充:

  • -h: 主机地址
  • -P: 端口
  • mysql --help: 查看MySQL命令行选项

选择数据库

use 数据库名

了解数据库和表

show databases;

SHOW DATABASES;返回可用数据库的一个列表。

show tables;

SHOW TABLES;返回当前选择的数据库内可用表的列表。

SHOW COLUMNS FROM tablename;

示例:

mysql> show columns from blog;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| handle | varchar(255) | NO | | NULL | |
| user_id | bigint(20) | YES | MUL | NULL | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

SHOW也可以用来显示表列。

SHOW COLUMNS 要求给出一个表名。

DESCRIBE语句

MySQL支持用DESCRIBE作为SHOW COLUMNS FROM的一种快捷方式。换句话说,DESCRIBE customers;是 SHOW COLUMNS FROM customers;的一种快捷方式。

推荐使用DESCRIBE customers这种方式!

mysql> describe blog;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| handle | varchar(255) | NO | | NULL | |
| user_id | bigint(20) | YES | MUL | NULL | |
+---------+--------------+------+-----+---------+----------------+

其他show语句:

  • SHOW STATUS,用于显示广泛的服务器状态信息

  • SHOW CREATE DATABASE和SHOW CREATE TABLE,分别用来显示创 建特定数据库或表的MySQL语句

  • mysql> show create table blog;
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | blog | CREATE TABLE `blog` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `handle` varchar(255) NOT NULL,
    `user_id` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fk_blog_user_id` (`user_id`),
    CONSTRAINT `fk_blog_user_id` FOREIGN KEY (`user_id`) REFERENCES `jhi_user` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)
  • SHOW GRANTS,用来显示授予用户(所有用户或特定用户)的安全权限

  • SHOW ERRORS和SHOW WARNINGS,用来显示服务器错误或警告消息。

  • 执行命令 HELP SHOW;显示允许的SHOW语句

第四章 检索数据

检索单个列

-- 检索products表中的prod_name列
SELECT prod_name FROM products;

检索多个列

-- 检索products表中的prod_id,prod_name和prod_price列
SELECT prod_id, prod_name, prod_price FROM products;

检索所有列(使用*通配符)

--检索products表中的所有列
SELECT * FROM products;

检索不同的行(使用DISTINCT关键字)

SELECT返回所有匹配的行。但是,如果你不想要每个值每次都出现,怎么办?

解决办法是使用DISTINCT关键字,顾名思义,此关键字指示MySQL只返回不同的值。

-- DISTINCT关键字必须直接放在列名的前面,不能部分使用DISTINCT,DISTINCT关键字应用于所有列而不仅是前置它的列。
SELECT DISTINCT vend_id FROM products;

示例:

mysql> select * from blog;
+----+-------------------------+------------------------------------+---------+
| id | name | handle | user_id |
+----+-------------------------+------------------------------------+---------+
| 1 | Integration | syndicate Sleek | NULL |
| 2 | Chief Analyst | Savings Account disintermediate | NULL |
| 3 | panel payment Group | Producer | NULL |
| 4 | Executive open-source | generate Saint Martin | NULL |
| 5 | Tuna modular systems | Canyon transmit | NULL |
| 6 | Intranet | quantifying | NULL |
| 7 | Accounts | deposit repurpose | NULL |
| 8 | Multi-layered | Home Loan Account Balboa US Dollar | NULL |
| 9 | Plastic Diverse Dynamic | JBOD New Zealand Buckinghamshire | NULL |
| 10 | payment | website | NULL |
| 11 | 罗翔说刑法 | admin | 3 |
| 12 | 法外狂徒张三 | user | 4 |
| 13 | 法外狂徒张三 | user | 4 |
+----+-------------------------+------------------------------------+---------+

如上,最后是有两行是重复的,如果我不想得到重复的数据:

mysql> select distinct name from blog;
+-------------------------+
| name |
+-------------------------+
| Integration |
| Chief Analyst |
| panel payment Group |
| Executive open-source |
| Tuna modular systems |
| Intranet |
| Accounts |
| Multi-layered |
| Plastic Diverse Dynamic |
| payment |
| 罗翔说刑法 |
| 法外狂徒张三 |
+-------------------------+
12 rows in set (0.00 sec)

限制显示结果(使用limit关键字)

-- 返回不多于5行
SELECT prod_name FROM products LIMIT 5;
-- 返回从第5行开始的5行
SELECT prod_name FROM products LIMIT 5,5;

示例:

mysql> select name from blog limit 10;
+-------------------------+
| name |
+-------------------------+
| Integration |
| Chief Analyst |
| panel payment Group |
| Executive open-source |
| Tuna modular systems |
| Intranet |
| Accounts |
| Multi-layered |
| Plastic Diverse Dynamic |
| payment |
+-------------------------+
10 rows in set (0.00 sec) mysql> select name from blog limit 5,5;
+-------------------------+
| name |
+-------------------------+
| Intranet |
| Accounts |
| Multi-layered |
| Plastic Diverse Dynamic |
| payment |
+-------------------------+
5 rows in set (0.00 sec)

检索出来的第一行为行0,因此LIMIT 1,1检索出来的是第二行而不是第一行 MySQL 5 支持LIMIT的另一种替代语法 LIMIT 4 OFFSET 3为从行3开始取4行,同LIMIT 3,4

使用完全限定的表名

SELECT products.prod_name FROM products;
SELECT products.prod_name FROM book.products;

第五章 排序数据

order by

ELECT prod_name
FROM products
ORDER BY prod_name;

按多个列排序

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;

对于上述例子中的输出,仅在多个行具有相同的prod_price 值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的,则不会按prod_name排序。

指定排序方向

默认升序排序,降序使用DESC关键字

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;

升序关键字ASC,可省略

如果想在多个列上进行降序排序,必须对每个列指定DESC关键字。

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name DESC;

找出一列中最高或最低的值

ORDET BY 与 LIMIT 的组合,能够找出一个列中最高或最低的值

SELECT prod_proce FROM products
ORDER BY prod_price DESC LIMIT 1;

给出ORDER BY句子时,应保证位于FROM句子之后,如果使用LIMIT,应位于ORDER BY之后。

第六章 过滤数据

使用WHERE子句

-- 返回prod_price为2.50的行
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50

在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误

WHERE子句操作符

符号 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间

使用示例

检查单个值

返回prod_name为Fuses的一行(匹配时默认不区分大小写)

SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';

列出小于10美元的所有产品

SELECT prod_name, prod_price FROM products WHERE prod_price < 10;

列出小于等于10美元的所有产品

SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;

不匹配检查

列出不是1003的所有产品

SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;

-- 或者

SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;

范围值检查

检索价格在5-10美元之间的所有产品

SELECT prod_name, prod_price FROM products
WHERE prod_price BETWEEN 5 AND 10;

空值检查

返回价格为空的所有产品

SELECT prod_name FROM products WHERE prod_price IS NULL;

第十九章 插入数据

增删改查中的增功能就是insert,你可以使用insert完成如下操作:

  • 插入完整的行

  • 插入行的一部分

  • 插入多行

  • 插入某些查询的结果

插入完整的行

INSERT INTO Customers
VALUES(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA',
NULL,
NULL);

语法简单但不安全。更安全的方法为:

INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046'
'USA'
NULL,
NULL);

安全体现在写了列名,插入的数据会根据列名填充。

如下操作填充的结果与上面代码相同,但它的填充次序可以不一样,这就是安全的体现:

INSERT INTO customers(cust_name,
cust_contact,
cust_email,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('Pep E. LaPew',
NULL,
NULL,
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA');

补充: INSERT语句一般不会产生输出。

插入多个行

INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('Pep E. LaPew',
'100 Main Street'
'Los Angeles',
'CA',
'90046',
'USA');
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('M. Martian',
'42 Galaxy Way'
'New York',
'NY',
'11213',
'USA');

太麻烦了,可以使用组合句,一般也是这样写

INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('Pep E. LaPew',
'100 Main Street'
'Los Angeles',
'CA',
'90046',
'USA'), ('M. Martian',
'42 Galaxy Way'
'New York',
'NY',
'11213',
'USA');

每组值用一对圆括号括起来,用逗号分隔。

插入检索出的数据

使用insert和select组合,可以将select出来的数据插入到表中。

INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;

为简单起见,这个例子在INSERT和 SELECT语句中使用了相同的列名。但是,不一定要求列名匹配。 事实上,MySQL甚至不关心SELECT返回的列名。它使用的是列的位置,因此SELECT中的第一列(不管其列名)将用来填充 表列中指定的第一个列,第二列将用来填充表列中指定的第二个列,如此等等。这对于从使用不同列名的表中导入数据是非常有用的。

第二十章 更新和删除数据

更新数据

可以使用update完成:

  • 更新表中特定行
  • 更新表中所有行
    • 尽量不要省略where子句,稍不注意就会更新表中所有的行。

update预计由3部分组成:

  • 要更新的表
  • 列名和它们的新值
  • 确定要更新行的过滤条件

如:客户10005现在有了电子邮件地址,因此他的记录需要更新:

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

更新多个列的语法稍有不同:

UPDARTE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

在更新多个列时,只需要使用单个SET命令,每个“列=值”对之间 用逗号分隔(最后一列之后不用逗号)。

IGNORE关键字:如果用UPDATE语句更新多行,并且在更新这些 行中的一行或多行时出一个现错误,则整个UPDATE操作被取消 (错误发生前更新的所有行被恢复到它们原来的值)。为即使是发生错误,也继续进行更新,可使用IGNORE关键字,如下所示:UPDATE IGNORE customers…

删除数据

使用DELETE语句

  • 从表中删除特定的行
  • 从表中删除所有的行
    • 尽量不要省略where子句,稍不注意就会删除表中所有的行。

示例:

DELETE FROM customers
WHERE cust_id = 10006;

更新和删除的指导原则

  • 除非确实打算更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
  • 保证每个表都有主键,尽可能像WHERE子句那样使用它(可以指定各主键、多个值或值的范围)。
  • 在对UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防编写的WHERE子句不正确。
  • 使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联的数据的行。

第二十一章 创建和操纵表

  • 使用具有交互式创建和管理表的工具;
  • 表也可以直接用MySQL语句操纵。

表创建基础

使用CREATE TABLE创建表,注意事项:

  • 新表的名字,在关键字CREATE TABLE之后给出;
    • 指定的表名必须不存在,否则报错
    • 如果要创建一个新的同名表,必须手工删除原有表,然后在用创表语句重建它,CREATE TABLE是无法覆盖原有表的。
    • 可以在表名后面使用IF NOT EXISTS检查表是否已经存在。
  • 表列的名字和定义,用逗号分隔。
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
cust_city char(50) NULL,
cust_state char(5) NULL,
cust_zip char(10) NULL,
cust_country char(50) NULL,
cust_contact char(50) NULL,
cust_email char(255) NULL,
PRIMARY KEY (cust_id)
) ENGINE = InnoDB;

上面的建表语句要注意几个细节:

  • NULL/NOT NULL
  • AUTO_INCREMENT
  • PRIMARY KEY
  • ENGINE = InnoDB

使用NULL值

每个表列或者是NULL列或者是NOT NULL列,这种状态在创建时由表的定义规定

CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL,
cust_id int NOT NULL,
PRIMARY KEY (order_num)
) ENGINE = InnoDB;

建表是可以混合NULL和NOT NULL列的,如上一个例子中就混有。

理解NULL

不要把NULL值与空串相混淆。NULL值是没有值,它不是空串。如果指定''(两个单引号,其间没有字符),这在NOT NULL列中是允许的。空串是一个有效的值,它不是无值。NULL值用关键字NULL而不是空串指定。

解释一下什么是空串:

空串(empty string):长度为零(即不含任何字符)的字符串。

主键

主键值必须唯一。如果主键使用单个列,则它的值必须唯一。如果使用多个列,则这些列的组合值必须唯一。

示例:创建多个列组成的主键

CREATE TABLE orderitems
(
order_num int NOT NULL,
order_item int NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (order_num, order_item)
)ENGINE = InnoDB;

主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的 列不能作为唯一标识。

AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL,本列每当增加一行时自动增量。每次 执行一个INSERT操作时,MySQL自动对该列增量(从而才有这个关键字AUTO_INCREMENT),给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id,从而可以用作主键值。

本文AUTO_INCREMENT讲得比较晦涩,我补充几点个人理解。

理解1:MySQL的中AUTO_INCREMENT类型的属性用于为一个表中记录自动生成ID功能.

理解2:使用AUTO_INCREMENT的注意事项:

  • AUTO_INCREMENT是数据列的一种属性,只适用于整数类型数据列
  • 设置AUTO_INCREMENT属性的数据列应该是一个正数序列
  • AUTO_INCREMENT数据列必须有唯一索引(索引是MySQL高级部分的知识,这里不展开),以避免序号重复(即是主键或者主键的一部分)。
  • AUTO_INCREMENT数据列必须具备NOT NULL属性。

理解3:当进行全表删除时,MySQL AUTO_INCREMENT会从1重新开始编号。

  • MySQL先把数据表里的所有数据和索引删除,然后重建数据表。
  • 如果想删除所有的数据行又想保留序列编号信息,可以用last_insert_id()获取刚刚自增过的值。

理解4:

auto_increment是用于主键自动增长的,从1开始增长,当你把第一条记录删除时,再插入第二跳数据时,主键值是2,不是1。

我们使用上面创建表的一个例子:

CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL,
cust_id int NOT NULL,
PRIMARY KEY (order_num)
) ENGINE = InnoDB;

新增一条语句,不手动插入order_num

mysql> insert into orders (order_date,cust_id) values ("2021-01-01",10086);
Query OK, 1 row affected (0.01 sec)

看看结果:

mysql> select * from orders;
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 1 | 2021-01-01 00:00:00 | 10086 |
+-----------+---------------------+---------+
1 row in set (0.00 sec)

再删除它

mysql> delete from orders where cust_id =10086;
Query OK, 1 row affected (0.00 sec) mysql> select * from orders;
Empty set (0.00 sec)

再次插入,不手动插入order_num

mysql> insert into orders (order_date,cust_id) values ("2021-01-02",10010);
Query OK, 1 row affected (0.00 sec)

看看结果:

mysql> select * from orders;
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 2 | 2021-01-02 00:00:00 | 10010 |
+-----------+---------------------+---------+
1 row in set (0.00 sec)

order_num变为2,说明每新增一条记录,字段值自动加 1。

我们可以使用last_insert_id()获取当前的值

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)

指定默认值

DEFAULT

CREATE TABLE orderitems
(
order_num int NOT NUL,
order_item int NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL DEFAULT 1,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (order_num,order_item)
) ENGINE = InnoDB;

MySQL不允许使用函数作为默认值,只支持常量

引擎类型(属于mysql高级的知识,先了解即可)

  • InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索;
  • MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘) 中,速度很快(特别适合于临时表);
  • MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。 引擎类型可以混用。 外键不能跨引擎 混用引擎类型有一个大缺陷。外键(用于强制实施引用完整性)不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表

使用ALTER TABLE更改表的结构,必须给出以下信息:

  • 在ALTER TABLE之后给出要更改的表名(该表必须存在,否则将出错);
  • 所做更改的列表。

例:为表vendors新增一列vend_phone

ALTER TABLE vendors
ADD vend_phone CHAR(20);

例:删除刚增加的列

ALTER TABLE vendors
DROP COLUMN vend_phone;

为了对单个表进行多个更改,可以使用单条ALTER TABLE语句,每个更改用逗号分隔

复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:

  • 用新的列布局创建一个新表;
  • 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要,可使用转换函数和计算字段;
  • 检验包含所需数据的新表;
  • 重命名旧表(如果确定,可以删除它);
  • 用旧表原来的名字重命名新表;
  • 根据需要,重新创建触发器、存储过程、索引和外键。

使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们。类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据。

删除表

DROP TABLE customers2;

重命名表

RENAME TABLE customers2 TO customers;

#对多个表重命名
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;

补充

  • 推荐使用navicat来学习mysql,就不用面对cmd的黑框框没有提示或补全,像本文一样一个一个字打。而且看表数据表结构也更加美观和直观。

  • MySQL的注释

    • #单行注释可以使用"#"
      -- 单行注释也可以使用"--",注意与注释之间有空格
      /*
      用于多行注释
      */