mysql实践一SQL基础

33次阅读

共计 11457 个字符，预计需要花费 29 分钟才能阅读完成。

mysql 介绍和安装

mysql 简介

mysql 是有名的开放源代码关系型数据库。它最早是 AB 公司开源的，后来到 Sun 公司手中。之后 Sun 公司被 Oracle 公司收购，mysql 就归 Oracle 所有。从此 mysql 走向商业化，又有名为 mariadb 数据库作为 mysql 的分支被开源，之后 mysql 又出了社区版和企业版。mysql 大致的历史就这样，更详细的可以百度，这里不多展开。

对于开发者来说，数据库是绕不开的一门技术，所有 mysql 这个有名，使用又频繁的开源数据库当然又学习的必要。

mysql 的安装

mysql 的安装可以是源代码编译安装，或者是在 mysql 的官网直接下载二进制文件，然后安装。当然，在容器技术这么火的 2019，直接在 docker 上安装并使用 mysql 对开发者来说更合适。

先准备一台 CentOS7 虚拟机，安装 docker。直接下载 mysql 镜像：

docker pull mysql:5.7

如果因为是在国内使用安装慢，可以使用阿里云的镜像加速。

然后启动 mysql 镜像

docker run -itd --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

默认 mysql 数据库 root 用户的登陆密码为 123456,。本机使用 mysql 命令登陆，未安装可以使用 yum 命令安装：

yum install -y mysql

使用 mysql 命令登陆：

mysql -uroot -p123456 -h 127.0.0.1

SQL

SQL 简介

SQL 全称为 Structure Query Language（结构化查询语言），它是使用关系型模型的数据库应用语言。SQL 语句主要分为三类：

  • DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括 create、drop、alter 等。
  • DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括 insert、delete、update 和 select 等。
  • DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

注：SQL 语句中的 ; 或者 \g 都是语句的结束符。\G 表示行显示输出。

DDL 语句

DDL 是针对数据库内部的对象进行创建、删除、修改等操作，一般是数据库管理员（DBA）来使用。我们看列举常用的 DDL 语句。

创建数据库

创建数据库使用的命令为：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

例如创建一个名为 test 的数据库：

MySQL [(none)]> CREATE DATABASE test DEFAULT CHARSET utf8;
Query OK, 1 row affected (0.00 sec)

注：mysql 中 sql 是不区别大小写的，所以上面的命令等同于 create database test default charset utf8;

选择数据库

创建数据库之后，就可以针对对应的数据库进行操作了，使用命令为:

USE dbname

例如使用数据库 test:

MySQL [(none)]> use test;
Database changed
MySQL [test]>

可以看到 [] 内的内容变成 test 数据库的名称了。

删除数据库

创建数据库对应的就是删除数据库了，命令为：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

例如查看 test 数据库：

MySQL [test]> drop database if exists test;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]>

注：数据库删除后，下面的所有表数据都会全部删除，所以在删除前需要检查并备份。

查看所有数据库

命令为：

SHOW DATABASES
MySQL [(none)]> show databases;     
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

创建表

在数据库中创建一张表的语法如下：

CREATE TABLE [IF NOT EXISTS] tablename (
column_name_1 column_type_1 constraints,
column_name_2 column_type_2 constraints,
...
column_name_n column_type_n constraints
);

例如创建一张 user 表，包含字段 id，name，age。

MySQL [test]> create table if not exists user (-> id int(11) primary key,
    -> name varchar(32) not null,
    -> age int(11)
    -> );

注：创建表之前需要先选择数据库。

查看所有表

同样也是使用 show

SHOW tables

例如：查看 test 数据库下的所有表

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

查看表结构

查看表结构命令为：

DESC tablename

例如查看之前创建的 user 的结构。

MySQL [test]> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

和这个命令相似功能的命令是show create table tbname:

MySQL [test]> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (`id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: No query specified

删除表

表的删除命令如下:

DROP TABLE [IF EXISTS] tablename

例如：删除之前创建的 user 表:

MySQL [test]> drop table if exists user;
Query OK, 0 rows affected (0.00 sec)

修改表

修改表使用关键字alter。它修改的内容可以是修改表的字段类型，还可以添加删除表字段。

1. 修改表类型：

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST|ALTER col_name]

例如：修改 user 表的 name 字段，将 varchar(32)修改为 varchar(64)

MySQL [test]> alter table user modify name varchar(64);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(64) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2. 添加表字段

ALTER TABLE tbname ADD [COLUMN] column_definition [FIRST|ALTER col_name]

例如：user 中添加一个字段，名为 email，类型是 varchar(64)

MySQL [test]> alter table `user` add `email` varchar(64) not null;
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(64) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| email | varchar(64) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3. 删除表字段

语法如下：

ALTER TABLE tablename DROP [COLUMN] col_name

例如：删除 user 中的 email 字段

MySQL [test]> alter table `user` drop `email`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(64) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4. 字段改名

语法如下：

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|ALTER col_name]

例如：将 name 改名为 username，类型为 varchar(32):

MySQL [test]> alter table `user` change `name` `username` varchar(32);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| username | varchar(32) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

注：change 和 modify 都可以修改表的定义，不同的是 change 后面需要写两次列名，不方便。但 change 可以改名，modify 则不能。

5. 修改字段排列顺序。

修改字段的关键字 ADD、CHNAGE、MODIFY 中都支持可选项 first|after，用于修改字段的位置。

例如：新增一个字段 email，并置于 username 字段之前:

MySQL [test]> alter table `user` add `email` varchar(64) after `id`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| email    | varchar(64) | YES  |     | NULL    |       |
| username | varchar(32) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------

例如：修改字段 email 在 username 字段之后：

MySQL [test]> alter table `user` modify `email` varchar(64)  after `username`;     
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [test]> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| username | varchar(32) | YES  |     | NULL    |       |
| email    | varchar(64) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

注：CHNAGE|FIRST|ALTER 这些关键字都属于 MySQL 在标准 SQL 上的扩展，在其他数据库上不一定适用。

6. 修改表名

语法如下：

ALTER TABLE tablename RENAME [TO] new_name

例如：将表 user 修改为 tb_user:

MySQL [test]> alter table `user` rename `tb_user`;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> desc tb_user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| username | varchar(32) | YES  |     | NULL    |       |
| email    | varchar(64) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

DML 语句

DML 语句为数据库表的操作语句，可以对数据库表的内容做增（insert）、删（delete）、改（update）、查（select）操作。

插入记录

表创建完毕后就往表中插入记录了，语法如下：

INSERT INTO tablename (field1,field2,field3,...,fieldn) VALUES (value1,value2,value3,...,valuen)

例如：在 user 表中插入一条记录，id 为 1，name 为 xiaoming，age 为 20：

MySQL [test]> insert into `user` (`id`,`name`,`age`) values (1,'xiaoming',20);
Query OK, 1 row affected (0.01 sec)

MySQL [test]> select id, name, age from `user`;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   20 |
+----+----------+------+
1 row in set (0.00 sec)

注：如果表中的字段类型设置了初始值，那插入时可以不指定该字段的值，这样它就以默认的值插入到表中。

select 是 SQL 查询操作的关键字，之后再说明。insert 语句支持同时插入多条记录：

INSERT INTO tablename (field1,field2,field3,...,fieldn)
VALUES
 (value1,value2,value3,...,valuen),
 (value1,value2,value3,...,valuen)
 ...
 (value1,value2,value3,...,valuen);

例如：同时在 user 表中插入多条记录：

MySQL [test]> insert into `user` (`id`,`name`,`age`)
    -> values
    ->  (2,'John',40),
    ->  (3,'Lisa',18);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [test]> select id, name, age from `user`;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   20 |
|  2 | John     |   40 |
|  3 | Lisa     |   18 |
+----+----------+------+
3 rows in set (0.00 sec)

注：同时插入可以节省很多的网络开销，提高插入效率。

更新记录

更新表的记录语法如下：

UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen [WHERE CONDITION]

例如：修改 John 的年龄为 30：

MySQL [test]> update `user` set age=30 where name='John';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [test]> select id, name, age from `user` where name='John';
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | John |   30 |
+----+------+------+
1 row in set (0.00 sec)

注：修改操作时需要使用关键字 where 指定条件，否则表中的所有记录的指定字段都会被修改。

UPDATE支持同时修改多个表的记录，语法如下：

UPDATE t1,t2,...tn SET t1.field1=value1,...,tn.fieldn=valuen [WHERE CONDITION]

删除记录

删除数据库表记录使用命令delete，语法如下：

DELETE FROM tablename [WHERE CONDITION]

例如：删除 name 为 ’xiaoming’ 的记录：

MySQL [test]> delete from `user` where name='xiaoming';
Query OK, 1 row affected (0.00 sec)

MySQL [test]> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | John |   30 |
|  3 | Lisa |   18 |
+----+------+------+
2 rows in set (0.00 sec)

同样的，删除操作也支持多表删除，语法如下：

DELETE t1,t2,...,tn FROM t1,t2,...,tn [WHERE CONDITION]

在进行多表操作时，为了简化 SQL 命令，可以给表添加别名。如果使用了别名，则在字段前也需要用别名表示该表。

注：删除时也一定要指定条件，不加 where 条件会删除表的所有记录。

查询记录

查询记录使用命令 select，语法如下：

SELECT * FROM tablename [WHERE CONDITION]

例如：查询 user 表的所有记录：

MySQL [test]> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | John |   30 |
|  3 | Lisa |   18 |
+----+------+------+
2 rows in set (0.00 sec)

*表示输出所有的字段，所以上面的 select 命令等同于 select id, name, age from user。并且 select 命令也是使用最频繁的命令，它支持各种方式的查询。

1. 查询去重。

使用关键字 distinct 对某一个字段的值去重。例如：字段 name 去重查询。

MySQL [test]> select * from user;                          
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | John |   40 |
|  2 | John |   30 |
|  3 | Lisa |   18 |
|  4 | Lisa |   18 |
+----+------+------+
4 rows in set (0.00 sec)

MySQL [test]> select distinct name from user;              
+------+
| name |
+------+
| John |
| Lisa |
+------+
2 rows in set (0.00 sec)

2. 条件查询。

用 where 关键字指定查询条件。例如：查询 name 为 John 且 age 为 30 的记录：

MySQL [test]> select * from user where name='John' and age=30;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | John |   30 |
+----+------+------+
1 row in set (0.01 sec)

使用 and 连接多个查询条件，也可以使用 or 表示查询满足多个条件中某个条件的记录。例如：查询 name 为 John 或 id= 3 的记录：

MySQL [test]> select * from user where name='John' or id=3;   
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | John |   40 |
|  2 | John |   30 |
|  3 | Lisa |   18 |
+----+------+------+
3 rows in set (0.00 sec)

where 后面的条件时一个表达式，支持各种运算符：>、<、>=、<=、!=。

3. 排序

使用关键字 ORDER BY 可以对查询的记录进行排序，语法如下：

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC],...,fieldn [DESC|ASC]

DESC 和 ASC 是排序顺序的关键字，DESC 表是按照字段进行降序排列，ASC 则表示升序排列，默认为升序排列。如果有多个字段，则先对第一个字段排序，字段相同的情况下再根据第二个排序，依次类推。例如：按照 age 字段对 user 表的记录排序：

MySQL [test]> select * from user order by age desc;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | John |   40 |
|  2 | John |   30 |
|  3 | Lisa |   18 |
|  4 | Lisa |   18 |
+----+------+------+
4 rows in set (0.00 sec)

MySQL [test]> select * from user order by age asc; 
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | Lisa |   18 |
|  4 | Lisa |   18 |
|  2 | John |   30 |
|  1 | John |   40 |
+----+------+------+
4 rows in set (0.01 sec)

4. 限制和偏移

使用关键字 OFFSET 和LIMIT限制查询记录的数量，已经查询的范围。语法如下:

SELECT * FROM LIMIT N OFFSET M

例如：查询 user 表的第一条记录:

MySQL [test]> select * from user limit 2;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | John |   40 |
|  2 | John |   30 |
+----+------+------+
2 rows in set (0.00 sec)

例如：查询 user 表的第二条记录:

MySQL [test]> select * from user limit 1 offset 1; 
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | John |   30 |
+----+------+------+
1 row in set (0.00 sec)

而且 OFFSET 和LIMIT配合使用还能实现查询的分页效果：如查询 user 表的第 2 页，每页 5 条记录：

MySQL [test]> select * from user limit 5 offset 5;

注：limit 属于 MySQL 扩展 SQL92 后的语法，在其他数据库上不一定通用。

正文完
mysql
发表至：无分类
2019-10-21
 0