MySQL入门基础1

一、数据库的增删查

创建数据库：

CREATE DATABASE 数据库名称;

示例：CREATE DATABASE member;

查看数据库：

SHOW DATABASES;

SHOW DATABASE 数据库名称;

删除数据库：

DROP DATABASE 数据库名称;

二、表的增删改查：

创建表：

CREATE TABLE 表名称(

       字段名1 字段类型 PRIMARY KEY AUTO_INCREMEMT,

       字段名2 字段类型,

       字段名3 字段类型,

       ...    

       )DEFAULT CHARSET=utf8;

示例：

DROP TABLE IF EXISTS boys;
CREATE TABLE boys(
boyNum VARCHAR(20),
matchNum INT(10)
);

修改表：

1、修改表字段

-- 新增表字段

ALTER TABLE member ADD regTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP();

添加字段到指定末尾

ALTER TABLE member ADD type TINYINT DEFAULT 1 AFTER mobilephone;

-- 添加多列

ALTER TABLE member ADD regTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),ADD type TINYINT DEFAULT 1 AFTER mobilephone;

-- 删除多列

ALTER TABLE member DROP type;

ALTER TABLE member DROP type,DROP regTime;

-- 删除并添加

ALTER TABLE member DROP regTime,ADD type TINYINT DEFAULT 1 AFTER mobilephone;

-- 修改表字段

ALTER TABLE member MODIFY type INT UNSIGNED DEFAULT 2;

2、修改列定义和表名

-- 修改列定义

ALTER TABLE member MODIFY type TINYINT UNSIGNED NOT NULL;

ALTER TABLE member MODIFY type SMALLINT UNSIGNED NOT NULL FIRST;

-- 修改默认值

ALTER TABLE member MODIFY type INT UNSIGNED DEFAULT 2;

ALTER TABLE member ALTER type SET DEFAULT NULL;

ALTER TABLE member ALTER type DROP DEFAULT;

-- 修改列名

ALTER TABLE member CHANGE COLUMN regTime crateTime TIMESTAMP;;

-- 修改表名

ALTER TABLE member RENAME student;

RENAME TABLE student TO member;

删除表：

DROP TABLE 表名;

查询表：

SHOW TABLES;