Jiachen's blog

数据定义语言DDL

Posted on by Jiachen

Data Definition Language 是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learning           |
| learning3          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.02 sec)

mysql> drop database learning3;
Query OK, 1 row affected (0.31 sec)

mysql> create database learning3;
Query OK, 1 row affected (0.07 sec)

mysql> use learning3;
Database changed
mysql> create table houses (name varchar(100) not null,
    -> house_location varchar(100) not null,
    -> purchasing_year varchar(100) not null);
Query OK, 0 rows affected (0.25 sec)

mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| houses              |
+---------------------+
1 row in set (0.00 sec)

mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(100) | NO   |     | NULL    |       |
| house_location  | varchar(100) | NO   |     | NULL    |       |
| purchasing_year | varchar(100) | NO   |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table houses drop purchasing_year;
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc houses;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| name           | varchar(100) | NO   |     | NULL    |       |
| house_location | varchar(100) | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table houses add purchasing_year varchar(100) after house_location;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(100) | NO   |     | NULL    |       |
| house_location  | varchar(100) | NO   |     | NULL    |       |
| purchasing_year | varchar(100) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table houses modify purchasing_year char(100);
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(100) | NO   |     | NULL    |       |
| house_location  | varchar(100) | NO   |     | NULL    |       |
| purchasing_year | char(100)    | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table houses change purchasing_year year  varchar(100);
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc houses;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| name           | varchar(100) | NO   |     | NULL    |       |
| house_location | varchar(100) | NO   |     | NULL    |       |
| year           | varchar(100) | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table houses rename to bighouses;
Query OK, 0 rows affected (0.24 sec)

mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| bighouses           |
+---------------------+
1 row in set (0.00 sec)

mysql> create table test(
    -> id int(10) unsigned not null auto_increment,
    -> name varchar(10) not null,
    -> primary key (id)
    -> );
Query OK, 0 rows affected (0.27 sec)

mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into test (name) values ('abc');
Query OK, 1 row affected (0.06 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
+----+------+
1 row in set (0.00 sec)

mysql> truncate test;
Query OK, 0 rows affected (0.49 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.11 sec)

mysql> insert into test (name) values('abc');
Query OK, 1 row affected (0.05 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
+----+------+
1 row in set (0.00 sec)

mysql> delete from test;
Query OK, 1 row affected (0.04 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into test (name) values ('abc');
Query OK, 1 row affected (0.07 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  2 | abc  |
+----+------+
1 row in set (0.00 sec)

mysql> set global autocommit='off';
Query OK, 0 rows affected (0.03 sec)

mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.03 sec)

mysql> delete from test;
Query OK, 1 row affected (0.07 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> insert into test (name) values ('abc');
Query OK, 1 row affected (0.07 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  3 | abc  |
+----+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test;
Query OK, 1 row affected (0.06 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> set session autocommit='off';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> insert into test (name) values ('abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  4 | abc  |
+----+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.07 sec)

mysql> delete from test;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  4 | abc  |
+----+------+
1 row in set (0.00 sec)

mysql> truncate test;
Query OK, 0 rows affected (0.38 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.17 sec)

mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| bighouses           |
+---------------------+
1 row in set (0.00 sec)

mysql> drop table bighouses;
Query OK, 0 rows affected (0.18 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learning           |
| learning3          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database learning3;
Query OK, 0 rows affected (0.08 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learning           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

数据操纵语言DML

Posted on by Jiachen

Data Manipulation Language 由插入insert into、修改update、删除delete from三个命令组成。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learning           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database learning3;
Query OK, 1 row affected (0.01 sec)

mysql> use learning3;
Database changed
mysql> create table houses (name varchar(100) not null,house_location varchar(10
0) not null,purchasing_year varchar(100) not null);
Query OK, 0 rows affected (0.04 sec)

mysql> select name,house_location,purchasing_year from houses;
Empty set (0.00 sec)

mysql> insert into houses(name,house_location,purchasing_year) values ('甲','天
河',1997);
Query OK, 1 row affected (0.00 sec)

mysql> select name,house_location,purchasing_year from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> create table houses2 (name varchar(100)not null,house_location varchar(10
0) not null, purchasing_year varchar(100) not null);
Query OK, 0 rows affected (0.04 sec)

mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(100) | NO   |     | NULL    |       |
| house_location  | varchar(100) | NO   |     | NULL    |       |
| purchasing_year | varchar(100) | NO   |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc houses2;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(100) | NO   |     | NULL    |       |
| house_location  | varchar(100) | NO   |     | NULL    |       |
| purchasing_year | varchar(100) | NO   |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from houses2;
Empty set (0.00 sec)

mysql> insert into houses2 select name,house_location,purchasing_year from house
s;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from houses2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> update houses2 set name='乙';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from houses2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 乙   | 天河           | 1997            |
+------+----------------+-----------------+
1 row in set (0.00 sec)


mysql> delete from houses2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from houses2;
Empty set (0.00 sec)

mysql> insert into houses values('乙','番禺',1998);
Query OK, 1 row affected (0.00 sec)


mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 乙   | 番禺           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> insert into houses2 select * from houses where name='乙';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from houses2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 乙   | 番禺           | 1998            |
+------+----------------+-----------------+
1 row in set (0.00 sec)

mysql> insert into houses2 select name,house_location,purchasing_year from house
s where house_location='天河';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from houses2;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 乙   | 番禺           | 1998            |
| 甲   | 天河           | 1997            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> delete from houses2;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from houses2;
Empty set (0.00 sec)

mysql> desc houses2;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(100) | NO   |     | NULL    |       |
| house_location  | varchar(100) | NO   |     | NULL    |       |
| purchasing_year | varchar(100) | NO   |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> drop table houses2;
Query OK, 0 rows affected (0.02 sec)

mysql> desc houses2;
ERROR 1146 (42S02): Table 'learning3.houses2' doesn't exist
mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| houses              |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 乙   | 番禺           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> update houses set name='yi',house_location='panyu',purchasing_year=1996 w
here name='乙';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| yi   | panyu          | 1996            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

删除操作基本语法：

DELETE FROM table_name (WHERE Clause);

修改基本语法：

UPDATE table_name SET field1=new_value1, field2=new_value2 (WHERE Clause);

数据查询语言DQL

Posted on by Jiachen

Data Query Language 基本结构是由 select<字段>from<表或视图>where<查询条件>组成。

mysql> use learning;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_learning |
+--------------------+
| houses             |
+--------------------+
1 row in set (0.02 sec)

mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(100) | NO   |     | NULL    |       |
| house_location  | varchar(100) | NO   |     | NULL    |       |
| purchasing_year | varchar(100) | NO   |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into houses (name,house_location,purchasing_year) values ('甲','天河',1997);/*向表格中插入新的行*/
Query OK, 1 row affected (0.12 sec)

mysql> insert into houses (name,house_location,purchasing_year) values ('甲','天河',1998);
Query OK, 1 row affected (0.07 sec)

mysql> select name,house_location,purchasing_year from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select name,house_location,purchasing_year from houses where name='甲';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select name from houses;
+------+
| name |
+------+
| 甲   |
| 甲   |
+------+
2 rows in set (0.00 sec)

mysql> select * from houses where name='ab';
Empty set (0.00 sec)

mysql> create view housesview as select * from houses;/*创建视图*/
Query OK, 0 rows affected (0.14 sec)

mysql> select * from housesview;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲   | 天河           | 1997            |
| 甲   | 天河           | 1998            |
+------+----------------+-----------------+
2 rows in set (0.00 sec)

mysql> select concat(name,"|",house_location) from houses;/*合并字段输出*/
+---------------------------------+
| concat(name,"|",house_location) |
+---------------------------------+
| 甲|天河                         |
| 甲|天河                         |
+---------------------------------+
2 rows in set (0.00 sec)

INSERT INTO语法：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

CREATE VIEW语法：

CREATE VIEW <视图名> AS <SELECT语句>

建库与建表

Posted on by Jiachen 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.23 sec)
mysql> create database learning;
Query OK, 1 row affected (0.23 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learning           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use learning;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| learning   |
+------------+
1 row in set (0.00 sec)

mysql> create table houses (name varchar(100) not null,house_location varchar(100) not null,purchasing_year varchar(100)not null);
Query OK, 0 rows affected (5.06 sec)

mysql> show tables;
+--------------------+
| Tables_in_learning |
+--------------------+
| houses             |
+--------------------+
1 row in set (0.04 sec)

mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name            | varchar(100) | NO   |     | NULL    |       |
| house_location  | varchar(100) | NO   |     | NULL    |       |
| purchasing_year | varchar(100) | NO   |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

建表语法：

create table table_name (column_name column_type);

MySQL安装记录

Posted on by Jiachen

在MySQL官网下载安装文件，本次安装使用MySQL Community Server版本，版本号8.0.16。下载后得到mysql-8.0.16-winx64.zip压缩包，解压。

我将压缩包解压到D:\Install\mysql-8.0.16-winx64目录，接下来配置系统环境变量。由于最开始安装时没有操作这步，导致安装不成功。

将解压目录下的bin文件夹路径添加到系统环境变量中。

然后配置my.ini文件，它是数据库的配置文件。下载来的压缩包内并不包含该文件，可以在安装目录内自己建立此文件。以下配置文档是我在网上找到的，注意basedir和datadir要修改成自己的安装路径。其中data文件夹不需要手动创建，下一步初始化时系统会自动创建。

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\Install\mysql-8.0.16-winx64   # 切记此处一定要用双斜杠\\，单斜杠我这里会出错，不过看别人的教程，有的是单斜杠。自己尝试吧
# 设置mysql数据库的数据的存放目录
datadir=D:\Install\mysql-8.0.16-winx64\data   # 此处同上
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

下面进入到安装步骤。

以管理员身份运行CMD命令提示符应用。在MySQL安装目录下的bin目录下执行命令：

mysqld --initialize --console

该命令为初始化安装，其中--console命令为在初始化后显示系统登陆密码。由于该密码为系统随机生成，请记住该密码，以便后续登录会用到。

D:\Install\mysql-8.0.16-winx64\bin>mysqld --initialize --console
2023-05-19T14:18:03.345459Z 0 [System] [MY-013169] [Server] D:\Install\mysql-8.0.16-winx64\bin\mysqld.exe (mysqld 8.0.16) initializing of server in progress as process 9924
2023-05-19T14:18:03.499328Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2023-05-19T14:18:26.420196Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: M-KNS95h8Csp
2023-05-19T14:18:42.560837Z 0 [System] [MY-013170] [Server] D:\Install\mysql-8.0.16-winx64\bin\mysqld.exe (mysqld 8.0.16) initializing of server has completed

D:\Install\mysql-8.0.16-winx64\bin>

以上安装过程中，root@localhost: 后面的M-KNS95h8Csp就是登陆密码，root为用户名。如果不小心没记住该密码，可以将安装目录中系统生成的data文件夹删除后，重新执行初始化命令。

安装服务。初始化命令执行完毕后，在MySQL安装目录下的bin目录执行命令：

mysqld --install

该命令为安装服务命令。

至此，MySQL数据库就安装成功了。接下来通过net start mysql命令来启动服务。

接下来是更改密码。在MySQL安装目录的bin目录下执行以下命令登录数据库：

mysql -u root -p

执行完毕以上命令后会提示输入密码，使用之前系统生成的密码登录。

登录后执行以下命令更改密码：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

注意命令结尾的;符号一定要有，这是MySQL的语法。以后，就可以使用新密码登录MySQL服务了。

以上就是我的MySQL安装记录。