数据库基础——基本操作

基本操作

Schema： Schema中文是模式的意思，在MySQL中与数据库同义

创建Schema

创建Schema的语法很简单：

CREATE DATABASE [SchemaName] DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

需要说明的是：DEFAULT后面的是制定utf8的编码格式，推荐大家用这种编码格式。

例如：创建一个教育管理系统数据库。

CREATE DATABASE edu_managerment_sys DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

获取Schema信息

删除Schema

如果一个数据库废弃了，删除它是最好的选择：

DROP DATABASE [SchemaName];

例如：

DROP DATABASE edu_managerment_sys;

创建表Table

在Schema中，表(Table)是存储数据的集合，任何数据记录都存储在表里; 列(Column)就是表的一个字段，代指的是某类信息; 行(Row) 表示的是表中的一条记录，是一个实体数据。

比如下面的学生表(student)：

学号	姓名	年龄	性别
001	张三	18	男
002	李四	20	女

那么学号、姓名等就是列，而001，张三这一行就是表的一条记录，称为行。

语法：

CREATE TABLE table_name (column_name column_type [constraint]) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：

• ENGINE=InnoDB： 表示用InnoDB引擎来创建表。
• DEFAULT CHARSET=utf8：表示设置表的默认编码。
• constraint: 表示的是一种约束，可省略，后面会详细介绍。

例如，创建一个学生表：student

CREATE TABLE student (
    `id` INT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `age` INT NOT NULL,
    `sex` VARCHAR(100) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

修改表

修改表主要有关于表的信息修改和表的列信息修改：

修改表名称

语法：

RENAME TABLE [old_table] TO [new_table];

例如修改student表为student_info:

RENAME TABLE student TO student_info;

修改列

• 增加列：

  ALTER TABLE table_name ADD COLUMN column_name column_type;
  

  修改列类型

  ALTER TABLE table_name MODIFY column_name column_type;
  

  删除列

  ALTER TABLE table_name DROP COLUMN column_name;
  

  例如：

ALTER TABLE student_info ADD COLUMN class_name VARCHAR(100);
ALTER TABLE student_info MODIFY class_name INT;
ALTER TABLE student_info DROP COLUMN class_name;

可以用下面的语句来显示列的详细信息：

SHOW COLUMNS FROM table_name;

例如：

SHOW COLUMNS FROM student_info;

删除表

DROP TABLE table_name;

新增数据

• 插入一条完整的数据：

  INSERT INTO table_name VALUES(data1, data2, data3,...);
  

  如果想插入部分数据，在表名后面的括号里写入要插入的列名：

  INSERT INTO table_name(column1, column2, column3,...) VALUES(data1, data2, data3,...);
  

  如果想一次性插入多条数据，既写多个SQL，也可以用下面这个：

  INSERT INTO table_name VALUES(data1, data2, data3,...),(data1, data2, data3,...);
  

  修改数据

UPDATE table_name SET column_name = 'column_value',... [WHERE column_name = 'column_value'];

说明:

• SET后面可以接多个列名来更新多个数据。
• WHERE条件表示更新符合条件的具体数据，如果省略，表示更新所有数据。

举个例子： 修改学生表里面id为1的学生，将他的名字改成小明：

UPDATE student_info SET name = '小明' WHERE id = 1;

删除数据

DELETE FROM table_name [WHERE column_name = 'column_value'];

说明：

• DELETE后面有FROM关键字，这个比较容易忘记。
• WHERE条件表示删除符合条件的记录，如果省略，表示删除表里面的所有记录。
• 使用DELETE删除时应该十分小心，最好带有WHERE条件，否则就很容易删除表里面的所有记录。
• DELETE是删除表里面的内容，而不是删除表，删除表应该用DROP。
• 如果想清空表里的所有内容，不推荐使用DELETE，最好使用TRUNCATE TABLE;语句，它完成相同的工作，但是速度更快，这条语句其实是删除了原来的表，然后重新创建一个表。

举个例子： 删除学生表里面id为1的数据：

DELETE FROM student_info WHERE id = 1;

检索数据

• 检索表里的所有列：

  SELECT * FROM table_name;
  

  检索表里的多个列：

• SELECT column1, column2, ... FROM table_name; 
  

  检索结果消除重复：

  SELECT DISTINCT column1, column2,... FROM table_name;
  

  检索指定开始行(m)后和返回的行数(n)：

  SELECT column1, column2, ... FROM table_name LIMIT m, n; 
  

  说明：

• 用*来查询所有列的性能比查询指定列的性能低，推荐用查询指定列的方式查询。

• 用LIMIT可以实现分页。

过滤数据

语法是：

SELECT [*|columns] FROM table_name WHERE condition expression；

条件表达式有以下几种：

1. 条件操作符：

条件操作符是WHERE子句根据条件过滤数据的根本，常见的条件操作符有下面几种：

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间
IS NOT NULL	为空/不为空

说明：

• 是否为空判断用IS NOT NULL，与!=''是不同的含义，前者是数值本身是空，后者表示字符串为''，不要混淆。

• 他们之间的优先级和JAVA等语言里面的优先级一致

  1. 逻辑操作符

  如果想查找学生表中年龄大于18岁的男性学生，怎么办？这里就要用到逻辑操作符了，常见的有下面几种：

  • AND：与操作符，用在WHERE子句中的关键字，用来指示检索满足所有给定的条件的行。
  • OR：或操作符，用来指示检索匹配任一给定的行。
  • NOT：非操作符，该操作符有且只有一个功能，那就是否定它之后所跟任何条件。

  上面的例子： 查找学生表中年龄大于18岁的男性学生

  mysql> SELECT * FROM student_info WHERE age > 18 AND sex = '男';
  +----+--------+-----+-----+
  | id | name   | age | sex |
  +----+--------+-----+-----+
  |  3 | 王五   |  30 | 男  |
  +----+--------+-----+-----+
  1 row in set (0.01 sec)
  

  3. 其他操作符：

  除了上面的两种操作符之外，还有几种操作符比较常用：

  • IN操作符：

    IN操作符用来指定条件范围，范围中的每个条件都可以匹配，具体语法就是取值清单包括在括号里面：

    SELECT * FROM table_name WHERE column_name IN (value1, value2, value3,....);
    

    例如： 查找年龄为20岁和30岁的学生：

    mysql> SELECT * FROM student_info WHERE age IN (20, 30);
    +----+--------+-----+-----+
    | id | name   | age | sex |
    +----+--------+-----+-----+
    |  2 | 李四   |  20 | 女  |
    |  3 | 王五   |  30 | 男  |
    +----+--------+-----+-----+
    2 rows in set (0.00 sec)
    

    LIKE操作符：

  • 假如想获取学生表中名字里面带有‘王’字的学生，怎么办？这就要用到LIKE操作符了：

    SELECT * FROM table_name WHERE column_name LIKE 'value';
    

    LIKE关键字后面经常有两种通配符：

    • 百分号(%): 表示任何字符出现任意次数，可以匹配一个或者多个。
    • 下划线(_): 匹配任意字符，只能匹配单个字符。

    例如： 学生表里面有两个姓王的学生：王五和王明刚，如果用百分号和下划线匹配，则为：

    mysql> SELECT * FROM student_info WHERE name LIKE '王%';
    +----+-----------+-----+-----+
    | id | name      | age | sex |
    +----+-----------+-----+-----+
    |  3 | 王五      |  30 | 男  |
    |  4 | 王明刚    |  40 | 男  |
    +----+-----------+-----+-----+
    2 rows in set (0.00 sec)
    
    mysql> SELECT * FROM student_info WHERE name LIKE '王_';
    +----+--------+-----+-----+
    | id | name   | age | sex |
    +----+--------+-----+-----+
    |  3 | 王五   |  30 | 男  |
    +----+--------+-----+-----+
    1 row in set (0.00 sec)
    

  备注：

  正如所见，MySQL的通配符很有用，但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索花更长时间。这里给出一些使用通配符的要领：

  • 不要过度使用通配符，如果其他操作符能够达到同样的目的，应该使用其他操作符。
  • 在确实需要使用通配符时，除非有绝对必要，否则不要把他们用在搜索模式的开始处。
  • 仔细注意通配符的位置，如果放错地方，可能不会返回想要的数据。

排序检索

排序是我们经常需要用到的功能，比如学生成绩的排名，年龄的排名等，语法如下：

SELECT * FROM table_name ORDER BY column [ASC|DESC];

说明：

• ASC表示升序，可以省略，DESC表示降序，不可省略。
• 如果要对多个字段进行排序，可以在ORDER BY后面依次增加多个列，会依次按照多个列进行排序。
• 排序可以和LIMIT关键字进行组合来找到一个列中最高或者最低的值。
• 排序通常位于WHERE子句的后面。