本章内容

  • SQL语句分类：
  • DDL语句
  • 库操作:
  • 表操作：
  • 字段操作


SQL语句分类：

  1. DDL： CREATE，DROP，ALTER
  2. DML： INSERT，DELETE，UPDATE
  3. DCL： GRANT，REVOKE，COMMIT，ROLLBACK
  4. DQL： SELECT

DDL语句

库操作:

1. 查看数据库列表：

SHOW DATABASES;

2. 查看支持所有字符集：

SHOW CHARACTER SET;
vim /etc/my.cnf.d/my-clients.cnf
mysql --default-character-set=utf8mb4  #临时

3. 查看支持所有排序规则：

SHOW COLLATION;

4. CREATE 创建数据库：

#注意，第一条是子句，因为没有";"结尾。2条子句为1条SQL语句。
#CHARACTER是设置字符集，COLLATE设置排序规则
CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name
CHARACTER SET character_name COLLATE collate_name;
#实例
CREATE DATABASE students CHARACTER SET utf8mb4;
#查看创建的数据库
SHOW CREATE DATABASE students;

5. USE 进入数据库

USE students

6. ALTER 修改数据库：

ALTER DATABASE db_name CHARACTER SET utf8;

7. DROP 删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] db_name;

6. 获取命令使用帮助：

HELP 你要查的命令;

恢复原来数据库

rm -rf /var/lib/mysql/*
systemctl restart mariadb

bash中导入SQL语句脚本

mysql < hellodb_inoodb.sql

设置服务器字符集为：utf8mb4

#查看选项
show variables like "%chara%"
#修改配置文件
vim /etc/mysql/my.cnf
    character_set_server=utf8mb4
    character_set_database=utf8mb4

表操作：

表：二维关系
设计表：遵循规范
定义：字段，索引
字段：字段名，字段数据类型，修饰符
约束，索引：应该创建在经常用作查询条件的字段上

这是我们平时所见的表，如excel

col1

col2

col3

内容1.1

内容1.2

内容1.3

内容2.1

内容2.2

内容3.3

  • col就是字段，每个字段都有各自的数据类型type，还有修饰符。这3个概念在(一)中已经讲到。

一、CREATE TABLE 创建表
获取帮助：HELP CREATE TABLE;

(1) 直接创建
格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   #表名
    (col_name column_definition,...) [table_options] [partition_options]; #字段

示例：

CREATE TABLE students 
(id INT PRIMARY KEY,name VARCHAR(20) NOT NULL,gender ENUM("m","f") DEFAULT "m" );

(2) 通过查询现存表创建；新表会被直接插入查询而来的数据
格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 
    [(create_definition,...)] [table_options]  [partition_options] 
    select_statement

示例：

create table student3 select * from test2.student;

(3) 通过复制现存的表的表结构创建，但不复制数据
格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

示例：

#从其它表拷贝创建，保留原来库的格式
create table student2 like test2.student;
  • 注意：
    Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同表可以使用不同的存储引擎,同一个库中表建议要使用同一种存储引擎类型
  • 一些SHOW命令
    获取帮助：
    HELP CREATE TABLE; 查看所有的引擎：
    SHOW ENGINES 查看表：
    SHOW TABLES [FROM db_name] 查看表结构：
    DESC [db_name.]tb_nameSHOW COLUMNS FROM [db_name.]tb_name 删除表：
    DROP TABLE [IF EXISTS] tb_name 查看表创建命令：
    SHOW CREATE TABLE tbl_name 查看表状态：
    SHOW TABLE STATUS LIKE "tbl_name" 查看库中所有表状态：
    SHOW TABLE STATUS FROM db_name

二、表操作(删改)
查看帮助：Help ALTER TABLE 删：DROP TABLE [IF EXISTS] 'tbl_name'; 改：ALTER TABLE tbl_name

字段操作

ADD、CHANGE、MODIFY…

  1. 查看所有字段：DESC
DESC students;
  1. 添加字段：ADD
#ALTER TABLE tbl_name ADD col1 data_type [FIRST|AFTER col_name]
ALTER TABLE class ADD gender ENUM('m','f') default 'm' AFTER name;
  1. 删除字段：DROP
#ALTER TABLE tbl_name DROP [COLUMN] col_name
ALTER TABLE class DROP gender ;
  1. 修改字段：
    alter（默认值）
#ALTER col_name {SET DEFAULT literal | DROP DEFAULT};

change（字段名）

#CHANGE old_col_name new_col_name column_definition;
ALTER TABLE class CHANGE num mobile char(11);

modify（字段属性）

#MODIFY [COLUMN] col_name column_definition;
ALTER TABLE class MODIFY phone int;   #修改字段属性

索引:
添加索引：ADD

ALTER TABLE class add index

删除索引：DROP

ALTER TABLE class drop index

查看表上的索引：

SHOW INDEXES FROM [db_name.]tbl_name;

三、修改表示例

Help ALTER TABLE #查看帮助
ALTER TABLE students RENAME s1;    #重命名表
ALTER TABLE s1 ADD phone varchar(11) AFTER name;  #添加字段在name后
ALTER TABLE s1 MODIFY phone int;   #修改字段属性
ALTER TABLE s1 CHANGE phone mobile char(11);  #修改字段名
ALTER TABLE s1 DROP mobile;  #删除字段
ALTER TABLE s1 character set utf8; #修改表的字符集
ALTER TABLE s1 change name name varchar(20) character set utf8;  #修改表的字符集和name字段属性
ALTER TABLE students ADD gender ENUM('m','f'); #在结尾添加gender字段
ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY; 
ALTER TABLE students drop primary key ;  #删除主键
ALTER TABLE students ADD UNIQUE KEY(name); #添加唯一键在name上
ALTER TABLE students ADD INDEX(age);  #在age添加索引
DESC students; #查看所有字段
SHOW INDEXES FROM students;  #查看索引