linux中数据库的配置与学习

书接上文

Windows 安装 wsl Linux子系统和数据库-CSDN博客
https://blog.csdn.net/2301_76475469/article/details/138199341?spm=1001.2014.3001.5502

打开ubuntu终端进行如下操作

sudo mysql -u root

 show databases;

use mechanical_design;

show tables;

create table photos(id smallint,pictures_description varchar(30));

show tables;

查看表

desc photos;

select * from photos;

####增删改查

‘’’

CRUD

Create  insert

Read  select from

Update  update set where

Delete  delete from

‘’’

####

C增

insert into photos(id,pictures_description) Values(1,'机构运动图');

#这里如果报错的话可以尝试修改字符集以支持中文字符

ALTER TABLE photos MODIFY pictures_description VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

读

select * from photos;

 select pictures_description from photos;

改

update photos set pictures_description='机构运动图1' where id=1;

删

delete from photos where id=1;

select * from photos;

CREATE TABLE person (  

        person_id SMALLINT UNSIGNED,  

        fname VARCHAR(30),  

        lname VARCHAR(30),

        gender ENUM('M', 'F'),  

        birth_date DATE,  

        street VARCHAR(30),  

        city VARCHAR(30),  

        country VARCHAR(30),  

        postal_code VARCHAR(20),  

        time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  

        PRIMARY KEY (person_id)   

);

• CREATE TABLE 关键字用于创建新表。
• 表名 person 和列名（如 person_id, fname, lname 等）用括号 () 包围，并且列定义之间用逗号 , 分隔。
• 列的定义包括数据类型（如 SMALLINT UNSIGNED, VARCHAR(30), ENUM('M', 'F') 等）和可能的默认值或约束（如 PRIMARY KEY）。
• 在表名 person 和列名之间不应有空格或特殊字符。

##Stored procedure

DELIMITER //  

CREATE PROCEDURE FINDME(IN con CHAR(20))  

BEGIN  

    SELECT country, fname, lname FROM person  

    WHERE country = con;  

END //  

DELIMITER ;

执行

call FINDME(‘canada’);

Select * from person;

Show create procedure FINDME;

或者

先退出数据库

Exit;

Sudo mysql -u root mechanical_design --silent --raw

Show create procedure FINDME;

还有一种：在上上种基础上

select * from person \G;

删除procedure：

drop procedure FINDME;