文章目錄
- SQL 數據庫創建及使用
- 刪除數據庫
- SQL 查看數據空中有哪些表格
- SQL 創建表格
- SQL 修改表格列數據格式
- SQL 表格插入數據
- SQL 查看表格類型組成
- SQL 查看表格中的內容
- SQL 查詢語句
- SQL 查看指定列
- SQL 選擇指定列
- SQL 按指定列進行升序排序
- 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)
)
