重拾SQL——从无到有
2024-08-25 19:58:24
2016.10.22
因为工作需要，在这里提前重拾sql。
0.创建并选择数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| menagerie |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
+--------------------+
6 rows in set (0.00 sec)
1.创建并选择数据库
mysql> CREATE DATABASE tianyuan;
Query OK, 1 row affected (0.00 sec)
2.查看创建后的数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| menagerie |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| tianyuan |
+--------------------+
7 rows in set (0.00 sec)
3.访问它
mysql> USE tianyuan
Database changed
4.指定数据库中表的布局
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.14 sec)
5.显示表格
mysql> SHOW TABLES;
+--------------------+
| Tables_in_tianyuan |
+--------------------+
| pet |
+--------------------+
1 row in set (0.00 sec)
6.验证表是按期望的方式创建（如果你忘记表中的列的名称或类型时）
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
7.选择表格所有列的内容（无内容时）
mysql> SELECT * FROM pet;
Empty set (0.00 sec)
8.将数据装入表中
LOAD DATA
mysql> LOAD DATA LOCAL INFILE '/Users/v/Desktop/1.txt' INTO TABLE pet;
'/Users/v/Desktop/1.txt':
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13加载后
Query OK, 3 rows affected, 3 warnings (0.07 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 3查看
mysql> SELECT * FROM pet;
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
3 rows in set (0.00 sec)INSERT
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.05 sec)
查看
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+-------+
4 rows in set (0.00 sec)
9.删除数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| menagerie |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| tianyuan |
+--------------------+
7 rows in set (0.00 sec)
删除
mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.02 sec)
查看删除效果
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| menagerie |
| mysql |
| performance_schema |
| phpmyadmin |
| tianyuan |
+--------------------+
6 rows in set (0.00 sec)
最新文章
- BrnShop mvc3升级mvc4
- JAVA字符串05之课程问题解决
- Pomelo：网易开源基于 Node.js 的游戏服务端框架
- php数组遍历
- eclipse中文乱码
- postgresql大批量数据导入方法
- opencv开发的程序分发给客户时所需要的dll文件
- Win7 64bit 安装VisualSVN出现报错：Servic 'VisualSVN Server' failed to start.解决办法
- C# 读写INI 文件
- 【模拟】Codeforces 707A Brain's Photos
- centos7 部署ssserver
- Leetcode easy
- Apache shiro集群实现 （二） shiro 的INI配置
- 认证模式之Form模式
- Android官方技术文档翻译——Gradle 插件用户指南（4）
- PAT1065: A+B and C (64bit)
- VRS的GPS/BDS双系统网元固定存在的问题
- cors 详解
- 21Oracle数据库和实例
- adb常用命令教程