1-深入浅出SQL-实践-第一章

1.1 数据库操作

1. 创建数据库

创建数据库，数据库名为gregs_list

CREATE DATABASE gregs_list;

操作结果：

mysql> CREATE DATABASE gregs_list;
Query OK, 1 row affected (0.00 sec)

1. 查看所有的数据库

SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gregs_list         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

1. 使用数据库

USE gregs_list;

mysql> USE gregs_list;
Database changed

1.2 数据表操作

1. 创建数据表

CREATE TABLE doughnut_list
(
donut_name VARCHAR(10),
donut_type VARCHAR(6)
);

CREATE TABLE my_contacts
(
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
);

CREATE TABLE my_contacts
(
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
gender CHAR(1),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
);

mysql> CREATE TABLE doughnut_list
    -> (
    -> doughnut_name VARCHAR(10),
    -> doughnut_type VARCHAR(6)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE my_contacts
    -> (
    -> last_name VARCHAR(30),
    -> first_name VARCHAR(20),
    -> email VARCHAR(50),
    -> birthday DATE,
    -> profession VARCHAR(50),
    -> location VARCHAR(50),
    -> status VARCHAR(100),
    -> seeking VARCHAR(100)
    -> );
Query OK, 0 rows affected (0.02 sec)

1. 列出所有表

SHOW TABLES;

mysql> SHOW TABLES;
+----------------------+
| Tables_in_gregs_list |
+----------------------+
| doughnut_list        |
| my_contacts          |
+----------------------+
2 rows in set (0.01 sec)

1. 查看表信息

DESC my_contacts;

mysql> DESC  my_contacts;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| last_name  | varchar(30)  | YES  |     | NULL    |       |
| first_name | varchar(20)  | YES  |     | NULL    |       |
| email      | varchar(50)  | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| profession | varchar(50)  | YES  |     | NULL    |       |
| location   | varchar(50)  | YES  |     | NULL    |       |
| status     | varchar(100) | YES  |     | NULL    |       |
| seeking    | varchar(100) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

1. 删除表

DROP TABLE my_contacts;

mysql> DROP TABLE my_contacts;
Query OK, 0 rows affected (0.01 sec)

1. 向表中插入数据

   • 向指定columns插入数据

INSERT INTO my_contacts
(last_name, first_name, email, gender, birthday, profession, location, status, interests, seeking)
VALUES
(‘Lv’, ‘Lu’, ‘lauralv@foxmail.com’, ‘F’, ‘2017-12-04’, ‘Engineer’, ‘Beijing’, ‘engagement’, ‘volleyball’, ‘Job’);

• 改变顺序
• 省略列名

• 省略部分列

  1. 简单查询表

SELECT * FROM my_contacts;

mysql> SELECT * FROM  my_contacts;
+-----------+------------+---------------------+--------+------------+------------+----------+------------+------------+---------+
| last_name | first_name | email               | gender | birthday   | profession | location | status     | interests  | seeking |
+-----------+------------+---------------------+--------+------------+------------+----------+------------+------------+---------+
| Lv        | Lu         | lauralv@foxmail.com | F      | 2017-12-04 | Engineer   | Beijing  | engagement | volleyball | Job     |
+-----------+------------+---------------------+--------+------------+------------+----------+------------+------------+---------+
1 row in set (0.00 sec)

1. NULL设置

   • 创建表时设置某一些字段不允许为空

CREATE TABLE my_contacts
(
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL,
gender CHAR(1),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20) NOT NULL,
interests VARCHAR(100),
seeking VARCHAR(100)
);

mysql> DESC my_contacts;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| last_name  | varchar(30)  | NO   |     | NULL    |       |
| first_name | varchar(20)  | NO   |     | NULL    |       |
| email      | varchar(50)  | NO   |     | NULL    |       |
| gender     | char(1)      | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| profession | varchar(50)  | YES  |     | NULL    |       |
| location   | varchar(50)  | YES  |     | NULL    |       |
| status     | varchar(20)  | NO   |     | NULL    |       |
| interests  | varchar(100) | YES  |     | NULL    |       |
| seeking    | varchar(100) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
10 rows in set (0.01 sec)

• 设置NULL的缺省填充值

CREATE TABLE doughnut_list
(
doughnut_name VARCHAR(10) NOT NULL,
doughnut_type VARCHAR(6) NOT NULL,
doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.00
);