【SQL 基础入门 1. -- SQL 基本语法详解及举例】

SQL 数据库创建及使用

创建新的数据库时，如果该数据库已经存在，则会出现错误，所以我们在创建新的数据库时一般会判断该数据库是否存在，如果已存在，则不会创建。

学习SQL 时建议大家自己搭建一个 linux虚拟机环境：可以参考 https://www.cnblogs.com/EthanS/p/18211302

mysql> create database if not exists test;
Query OK, 1 row affected (0.00 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

删除数据库

drop database test;

为了避免出现错误，我们一般会先判断该数据库是否存在，如果不存在，则不会删除。

drop database if exists test;

SQL 查看数据空中有哪些表格

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| employee       |
+----------------+
1 row in set (0.00 sec)

SQL 创建表格

mysql> create table employee( id int, name varchar(40), sex varchar(4), birthday date, entry_date date, salary decimal(8,2), resume text );
Query OK, 0 rows affected (0.01 sec)

SQL 修改表格列数据格式

mysql> alter table employee modify column salary int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改之后如下：

mysql> desc employee;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | YES  |     | NULL    |       |
| name       | varchar(40)  | YES  |     | NULL    |       |
| sex        | varchar(4)   | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| entry_date | date         | YES  |     | NULL    |       |
| salary     | int          | YES  |     | NULL    |       |
| resume     | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

SQL 表格插入数据

mysql> insert into employee(id,name,sex,birthday,entry_date,salary,resume) values(4,'sunliu','female','1998-05-04','2020-08-10','4500','i am a developer');
Query OK, 1 row affected (0.00 sec)

插入一行后如下：

mysql> select * from employee;
+------+----------+------+------------+------------+--------+------------------+
| id   | name     | sex  | birthday   | entry_date | salary | resume           |
+------+----------+------+------------+------------+--------+------------------+
|    1 | zhangsan | male | 1993-03-04 | 2016-11-10 |   1000 | i am a developer |
+------+----------+------+------------+------------+--------+------------------+
1 row in set (0.00 sec)

SQL 查看表格类型组成

mysql> desc employee;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | YES  |     | NULL    |       |
| name       | varchar(40)  | YES  |     | NULL    |       |
| sex        | varchar(8)   | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| entry_date | date         | YES  |     | NULL    |       |
| salary     | int          | YES  |     | NULL    |       |
| resume     | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

SQL 查看表格中的内容

插入多行数据之后，查看表格内容

mysql> select * from employee;
+------+----------+--------+------------+------------+--------+------------------+
| id   | name     | sex    | birthday   | entry_date | salary | resume           |
+------+----------+--------+------------+------------+--------+------------------+
|    1 | zhangsan | male   | 1993-03-04 | 2016-11-10 |   1000 | i am a developer |
|    2 | lisi     | male   | 1992-01-04 | 2017-11-10 |   1500 | i am a waiter    |
|    3 | wangwu   | female | 1990-02-04 | 2019-11-10 |   2500 | i am a developer |
|    4 | sunliu   | female | 1998-05-04 | 2020-08-10 |   4500 | i am a developer |
+------+----------+--------+------------+------------+--------+------------------+
4 rows in set (0.00 sec)

SQL 查询语句

SQL 查看指定列

mysql> select name, sex from employee;
+----------+--------+
| name     | sex    |
+----------+--------+
| zhangsan | male   |
| lisi     | male   |
| wangwu   | female |
| sunliu   | female |
+----------+--------+
4 rows in set (0.00 sec)

SQL 选择指定列

mysql> select birthday from employee where birthday = '1990-02-04';
+------------+
| birthday   |
+------------+
| 1990-02-04 |
+------------+
1 row in set (0.00 sec)

SQL 按指定列进行升序排序

mysql> select salary from employee order by salary ASC;
+--------+
| salary |
+--------+
|   1000 |
|   1500 |
|   2500 |
|   4500 |
+--------+
4 rows in set (0.00 sec)mysql> select salary from employee order by salary DESC;
+--------+
| salary |
+--------+
|   4500 |
|   2500 |
|   1500 |
|   1000 |
+--------+
4 rows in set (0.00 sec)

SQL 平均值/求和/最大值/最小值

mysql> select avg(salary) from employee;
+-------------+
| avg(salary) |
+-------------+
|   2375.0000 |
+-------------+
1 row in set (0.00 sec)mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)mysql> select max(salary) from employee;
+-------------+
| max(salary) |
+-------------+
|        4500 |
+-------------+
1 row in set (0.00 sec)mysql> select min(salary) from employee;
+-------------+
| min(salary) |
+-------------+
|        1000 |
+-------------+
1 row in set (0.00 sec)mysql> select sum(salary) from employee;
+-------------+
| sum(salary) |
+-------------+
|        9500 |
+-------------+
1 row in set (0.00 sec)