终端操作数据库
2017-03-14 本文已影响14人
GA_
http://www.cnblogs.com/fly1988happy/archive/2011/12/15/2288554.html
// 终端登录mysql
./mysql -h localhost -p -u jianan
// root登录
houjianandeMacBook-Pro:bin houjianan$ ./mysql -h localhost -p -u root
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 10.1.13-MariaDB Source distribution
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
// 创建数据库 db001
MariaDB [(none)]> create database db001;
Query OK, 1 row affected (0.00 sec)
// 展示所有数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db001 |
| db11 |
| db131 |
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| testData |
+--------------------+
// 使用数据库db11
MariaDB [(none)]> use db11;
Database changed
// 使用数据库db001
MariaDB [(none)]> use db001;
Database changed
// 展示数据库db001中的所欲表
MariaDB [db001]> show tables;
+-----------------+
| Tables_in_db001 |
+-----------------+
| person |
+-----------------+
1 row in set (0.00 sec)
//展示标的详情
MariaDB [db001]> desc person;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| age | int(11) | YES | | NULL | |
| name | text | YES | | NULL | |
| des | text | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
-增-
// 往表person中插入字段id和name对应值分别为5和jianan
MariaDB [db001]> insert into person (id, name) values (5, 'jianan');
Query OK, 1 row affected (0.01 sec)
// 往表person中插入字段id, name, age, des对应的值为6, 'jianan', 27, '腿很长'
MariaDB [db001]> insert into person (id, name, age, des) values (6, 'jianan', 27, '腿很长');
Query OK, 1 row affected, 1 warning (0.00 sec)
// 查看表里的内容
MariaDB [db001]> select * from person;
+----+------+--------+------+
| id | age | name | des |
+----+------+--------+------+
| 2 | NULL | NULL | NULL |
| 3 | 27 | NULL | NULL |
| 5 | NULL | jianan | NULL |
| 6 | 27 | jianan | ??? |
+----+------+--------+------+
4 rows in set (0.04 sec)
// 往表person中插入字段id, name, age, des对应的值为6, 'jianan', 27, '腿很长'
MariaDB [db001]> insert into person values (7, 'jianan', 27, '腿很长');
Query OK, 1 row affected, 2 warnings (0.01 sec)
// 往表person中插入 两组 字段name, age对应的值为'jianan001', 28和'jianan002', 29
MariaDB [db001]> insert into person (name, age) values ('jianan001', 28), ('jianan002', 29);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
// 查看表里的内容
MariaDB [db001]> select * from person;
+----+------+-----------+------+
| id | age | name | des |
+----+------+-----------+------+
| 2 | NULL | NULL | NULL |
| 3 | 27 | NULL | NULL |
| 5 | NULL | jianan | NULL |
| 6 | 27 | jianan | ??? |
| 7 | 0 | 27 | ??? |
| 8 | 28 | jianan001 | NULL |
| 9 | 29 | jianan002 | NULL |
+----+------+-----------+------+
7 rows in set (0.00 sec)
MariaDB [db001]>
-改-
MariaDB [db001]> select * from person;
+----+------+-----------+------+
| id | age | name | des |
+----+------+-----------+------+
| 2 | NULL | NULL | NULL |
| 3 | 27 | NULL | NULL |
| 5 | NULL | jianan | NULL |
| 6 | 27 | jianan | ??? |
| 7 | 0 | 27 | ??? |
| 8 | 28 | jianan001 | NULL |
| 9 | 29 | jianan002 | NULL |
+----+------+-----------+------+
7 rows in set (0.00 sec)
// 修改表中id=2的用户name和age
MariaDB [db001]> update person set
-> name = 'jianan',
-> age = 30
-> where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
// 查看表里的内容
MariaDB [db001]> select * from person
-> ;
+----+------+-----------+------+
| id | age | name | des |
+----+------+-----------+------+
| 2 | 30 | jianan | NULL |
| 3 | 27 | NULL | NULL |
| 5 | NULL | jianan | NULL |
| 6 | 27 | jianan | ??? |
| 7 | 0 | 27 | ??? |
| 8 | 28 | jianan001 | NULL |
| 9 | 29 | jianan002 | NULL |
+----+------+-----------+------+
7 rows in set (0.01 sec)
-删-
// 删除id=6在表中的内容
MariaDB [db001]> delete from person
-> where id = 6;
Query OK, 1 row affected (0.00 sec)
-选-
// 查看表里的内容
MariaDB [db001]> select * from person;
+----+------+--------+------+
| id | age | name | des |
+----+------+--------+------+
| 2 | 30 | jianan | NULL |
| 3 | 27 | jianan | NULL |
| 5 | NULL | jianan | NULL |
| 7 | 0 | jianan | ??? |
| 8 | 28 | jianan | NULL |
| 9 | 29 | jianan | NULL |
+----+------+--------+------+
6 rows in set (0.00 sec)
// 查看表里的字段age和name
MariaDB [db001]> select age, name from person;
+------+--------+
| age | name |
+------+--------+
| 30 | jianan |
| 27 | jianan |
| NULL | jianan |
| 0 | jianan |
| 28 | jianan |
| 29 | jianan |
+------+--------+
6 rows in set (0.00 sec)
// 查看表里id=3中id和age的字段
MariaDB [db001]> select id, age from person where id = 3;
+----+------+
| id | age |
+----+------+
| 3 | 27 |
+----+------+
1 row in set (0.01 sec)
// 查看表里id大于等于4中age字段
MariaDB [db001]> select age from person
-> where id >= 4;
+-----+
| age |
+-----+
| 5 |
| 7 |
| 8 |
| 9 |
+-----+
4 rows in set (0.01 sec)
-表创建-
MariaDB [db001]> show tables;
+-----------------+
| Tables_in_db001 |
+-----------------+
| person |
+-----------------+
1 row in set (0.01 sec)
// 创建表person01，id是自动增长的主键，长度20的title字段，长度100的content字段
MariaDB [db001]> create table person01 (
-> id int primary key auto_increment,
-> title varchar(20),
-> content varchar (100)
-> )charset utf8;
Query OK, 0 rows affected (0.24 sec)
MariaDB [db001]> show tables;
+-----------------+
| Tables_in_db001 |
+-----------------+
| person |
| person01 |
+-----------------+
2 rows in set (0.00 sec)
终端创建完数据库 下图红框处刷新
6FB189E0-DED0-4EFA-936F-C1B660A6D784.png
