Master MySQL Basics: Schemas, Data Types, and Full CRUD Guide

1. Basics

Schema defines how data is stored, what data is stored, and how it is partitioned; both databases and tables have schemas.

Primary key values cannot be modified or reused (deleted primary key values cannot be assigned to new rows).

SQL (Structured Query Language) is managed by the ANSI standards committee and is therefore called ANSI SQL. Each DBMS implements its own extensions, such as PL/SQL or Transact‑SQL.

SQL statements are case‑insensitive, but whether identifiers (table names, column names, values) are case‑sensitive depends on the specific DBMS and its configuration.

Starting, Stopping, and Uninstalling MySQL Service

Run the following in a Windows command prompt:

启动: net start MySQL
停止: net stop MySQL
卸载: sc delete MySQL

MySQL Data Types

MySQL has three major data type categories: numeric, date/time, and string, each further divided into many sub‑types.

Numeric Types

整数: tinyint、smallint、mediumint、int、bigint
浮点数: float、double、real、decimal

Date and Time

date、time、datetime、timestamp、year

String Types

字符串: char、varchar
文本: tinytext、text、mediumtext、longtext
二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

Login

mysql -h 主机名 -u 用户名 -p

-h : specifies the MySQL host name; can be omitted for the local machine. -u : the user name to log in with. -p : tells the server a password will be used; can be omitted if the account has no password.

After logging, the prompt shows mysql> and you can type exit or quit to leave.

Comments

SQL supports three comment styles:

# comment SELECT * FROM mytable;
-- comment
/* comment1   comment2 */

2. Creating, Importing, and Deleting Databases

Creating a Database

Use the create database statement:

create database 数据库名 [其他选项];

Example: create a database named samp_db with GBK character set:

create database samp_db character set gbk;

Create a database with UTF‑8 charset:

create database db_name default charset utf8;

Select the database to use:

use samp_db;

MySQL statements end with a semicolon, but the use statement can omit it.

Importing a Database

If you have a complete .sql file (e.g., samp_db.sql), create and select the target database first, then run:

source D:\worksp\samp_db.sql;

This imports the file into the server, which may take some time.

Deleting a Database

drop database samp_db;

3. Creating Tables

Use the following command:

create table 表名称(列声明);

Example:

create table students(
    id int unsigned not null auto_increment primary key,
    name char(8) not null,
    sex char(4) not null,
    age tinyint unsigned not null,
    tel char(13) null default "-"
);

Common queries:

show databases;
show tables;
show columns from customers;
describe customers;

4. Modifying Tables

The alter table statement modifies an existing table.

Adding Columns

Basic form:

alter table 表名 add 列名 列数据类型 [after 插入位置];

Example: add an address column at the end:

alter table students add address char(60);

Insert birthday column after age:

alter table students add birthday date after age;

Modifying Columns

Basic form:

alter table 表名 change 列名称 列新名称 新数据类型;

Rename tel to telphone:

alter table students change tel telphone char(13) default "-";

Change name to char(16) and set NOT NULL:

alter table students change name name char(16) not null;

Dropping Columns

Basic form:

alter table 表名 drop 列名称;

Example: drop the birthday column:

alter table students drop birthday;

Renaming a Table

Basic form:

alter table 表名 rename 新表名;

Example: rename students to workmates:

alter table students rename workmates;

Dropping an Entire Table

Basic form:

drop table 表名;

Example: drop the workmates table:

drop table workmates;

5. CRUD Operations Within Tables

Inserting Data

Basic syntax:

insert [into] 表名 [(列名1, 列名2, ...)] values (值1, 值2, ...);

Insert a full row into students:

insert into students values(NULL, "王刚", "男", 20, "13811371377");

Insert specific columns:

insert into students (name, sex, age) values("孙丽华", "女", 21);

Querying Data

Basic Usage

Basic select syntax:

select 列名称 from 表名称 [查询条件];

Example: select name and age:

select name, age from students;

Select all columns:

select * from students;

Combined with INSERT or CREATE

Use SELECT results as values for INSERT:

insert into table_1 select c1, c2 from table_2;

Create a table with the same structure as another:

create table tasks_bak like tasks;

Create a new table from a query:

create table newtable as select * from mytable;

Conditional Queries with WHERE

WHERE clause syntax:

select 列名称 from 表名称 where 条件;

Supported operators include =, >, <, >=, <=, !=, IS [NOT] NULL, IN, LIKE, as well as AND, OR.

Example: select students older than 21:

select * from students where age > 21;

Example: select rows where name contains "王":

select * from students where name like "%王%";

Example: id < 5 and age > 20:

select * from students where id<5 and age>20;

DISTINCT Keyword

Remove duplicate rows.

Single Column

select distinct [列名] from [表名];

Example:

select distinct username from t_user;

Multiple Columns

select distinct [列名1], [列名2] from [表名];

Example:

select distinct username, password from t_user;

LIMIT Keyword

Limit the number of returned rows. First parameter is offset (starting at 0), second is row count.

select * from [表名] limit 3; # first 3 rows
select * from [表名] limit 1, 2; # start at row 2, return 2 rows

UPDATE Statement

Basic syntax:

update 表名称 set 列名称=新值 where 更新条件;

Example: set default phone for id 5:

update students set tel=default where id=5;

Example: increase all ages by 1:

update students set age=age+1;

Example: change name and age for a specific phone number:

update students set name="张伟鹏", age=19 where tel="13288097888";

DELETE Statement

Basic syntax:

delete from 表名称 where 删除条件;

Example: delete row with id 2:

delete from students where id=2;

Delete all rows where age < 20:

delete from students where age<20;

Delete all rows in the table:

delete from students;