1.1 数据库操作
- 创建数据库
创建数据库,数据库名为gregs_list
CREATE DATABASE gregs_list;
操作结果:
mysql> CREATE DATABASE gregs_list;
Query OK, 1 row affected (0.00 sec)
- 查看所有的数据库
SHOW DATABASES;
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gregs_list |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
- 使用数据库
USE gregs_list;
mysql> USE gregs_list;
Database changed
1.2 数据表操作
- 创建数据表
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)
- 列出所有表
SHOW TABLES;
mysql> SHOW TABLES;
+----------------------+
| Tables_in_gregs_list |
+----------------------+
| doughnut_list |
| my_contacts |
+----------------------+
2 rows in set (0.01 sec)
- 查看表信息
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)
- 删除表
DROP TABLE my_contacts;
mysql> DROP TABLE my_contacts;
Query OK, 0 rows affected (0.01 sec)
向表中插入数据
- 向指定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’);
- 改变顺序
- 省略列名
省略部分列
- 简单查询表
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)
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
);