1)进入 MySQL 控制台;
[root@localhost ~]# mysql -u root -p
Enter password:
2)输入密码后可以进入,创建数据库;
MariaDB [(none)]> create database muke;
3)使用SHOW语句找出在服务器上当前存在什么数据库;
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jmt_b2b_design |
| muke |
| mysql |
| performance_schema |
| shop |
+--------------------+
4)选择你所创建的数据库;
MariaDB [(none)]> use muke;
Database changed
5)创建一个数据表;
MariaDB [muke]> create table mk_user(
-> id int(11) not null auto_increment,
-> `user_name` varchar(20) not null default '',
-> `nick_name` varchar(20) not null default '',
-> `password` char(32) not null default '',
-> `create_date` int(10) not null default '0',
-> `update_date` int(10) not null default '0',
-> primary key(`id`)
-> )ENGINE=InnoDB CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
6)查看当前数据库中存在什么表;
MariaDB [muke]> show tables;
+----------------+
| Tables_in_muke |
+----------------+
| mk_user |
+----------------+
1 row in set (0.00 sec)
7)查看当前数据表结构;
MariaDB [muke]> describe mk_user;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_name | varchar(20) | NO | | | |
| nick_name | varchar(20) | NO | | | |
| password | char(32) | NO | | | |
| create_date | int(10) | NO | | 0 | |
| update_date | int(10) | NO | | 0 | |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
8)往表里插入数据;
MariaDB [muke]> insert into mk_user values(1, 'zjc', 'vision', '3f799a3608509e9ed753b02bc9c6c7ed', '1443628800', '1443628800'),(2, 'zjc', 'vision', '3f799a3608509e9ed753b02bc9c6c7ed', '1443628800', '1443628800');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
9)查询表;
MariaDB [muke]> select * from mk_user;
+----+-----------+-----------+----------------------------------+-------------+-------------+
| id | user_name | nick_name | password | create_date | update_date |
+----+-----------+-----------+----------------------------------+-------------+-------------+
| 1 | zjc | vision | 3f799a3608509e9ed753b02bc9c6c7ed | 1443628800 | 1443628800 |
| 2 | zjc | vision | 3f799a3608509e9ed753b02bc9c6c7ed | 1443628800 | 1443628800 |
+----+-----------+-----------+----------------------------------+-------------+-------------+
3 rows in set (0.00 sec)
10)修改表里的数据;
MariaDB [muke]> update mk_user set nick_name = 'visionz' where id = 1;
Query OK, 1 row affected (0.26 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [muke]> select * from mk_user;
+----+-----------+-----------+----------------------------------+-------------+-------------+
| id | user_name | nick_name | password | create_date | update_date |
+----+-----------+-----------+----------------------------------+-------------+-------------+
| 1 | zjc | visionz | 3f799a3608509e9ed753b02bc9c6c7ed | 1443628800 | 1443628800 |
| 2 | zjc | vision | 3f799a3608509e9ed753b02bc9c6c7ed | 1443628800 | 1443628800 |
+----+-----------+-----------+----------------------------------+-------------+-------------+
3 rows in set (0.00 sec)
11)删除表里的数据;
MariaDB [muke]> delete from mk_user where id=2;
Query OK, 1 row affected (0.01 sec)
MariaDB [muke]> select * from mk_user;
+----+-----------+-----------+----------------------------------+-------------+-------------+
| id | user_name | nick_name | password | create_date | update_date |
+----+-----------+-----------+----------------------------------+-------------+-------------+
| 1 | zjc | visionz | 3f799a3608509e9ed753b02bc9c6c7ed | 1443628800 | 1443628800 |
+----+-----------+-----------+----------------------------------+-------------+-------------+
2 rows in set (0.00 sec)
12)清空表数据(自增字段变为1,如果不想可用delete);
MariaDB [muke]> truncate table mk_user;
Query OK, 0 rows affected (0.11 sec)
13)修改表结构;
MariaDB [muke]> alter table mk_user add column score int(11) not null default 0;
Query OK, 0 rows affected (0.05 sec)
14)删除表;
MariaDB [muke]> drop table mk_user;
Query OK, 0 rows affected (0.03 sec)
参考了博文MySQL查询语句大全集锦。