登录mysql
mysql -uroot -p
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_chat |
| mysql |
| performance_schema |
| sys |
| test_db |
+--------------------+
6 rows in set (0.00 sec)
使用数据库
mysql> use db_chat;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
查看数据库建立语句
mysql> show create database db_chat;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| db_chat | CREATE DATABASE `db_chat` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
查看表
mysql> show tables;
+-------------------+
| Tables_in_db_chat |
+-------------------+
| cluster |
| clusteruser |
| friends |
| groupuser |
| mygroup |
| user |
+-------------------+
6 rows in set (0.00 sec)
查看表结构
mysql> desc user;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| account | varchar(15) | NO | PRI | NULL | |
| nickname | varchar(15) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| password | varchar(18) | NO | | NULL | |
| registerTime | datetime | YES | | NULL | |
| signature | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
查看建表语句
mysql> show create table user;
| Table | Create Table |
| user | CREATE TABLE `user` (
`account` varchar(15) NOT NULL,
`nickname` varchar(15) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`gender` char(1) DEFAULT NULL,
`password` varchar(18) NOT NULL,
`registerTime` datetime DEFAULT NULL,
`signature` varchar(50) DEFAULT NULL,
PRIMARY KEY (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
查看表的索引
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | account | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
查看数据库编码
mysql> show create database db_chat;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| db_chat | CREATE DATABASE `db_chat` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
创建表
create table if not exists test(
account varchar(15) not null primary key,
nickname varchar(15),
age int(3),
gender char(1),
password varchar(18) not null,
registerTime datetime,
signature varchar(50))ENGINE=InnoDB DEFAULT CHARSET=UTF8;
mysql> create table if not exists test(
-> account varchar(15) not null primary key,
-> nickname varchar(15),
-> age int(3),
-> gender char(1),
-> password varchar(18) not null,
-> registerTime datetime,
-> signature varchar(50))ENGINE=InnoDB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+-------------------+
| Tables_in_db_chat |
+-------------------+
| cluster |
| clusteruser |
| friends |
| groupuser |
| mygroup |
| test |
| user |
+-------------------+
7 rows in set (0.00 sec)
插入数据
mysql> insert into test(account,nickname,age,gender,password,registerTime,signature) values('123','人西风',20,'m','666',now(),'love you forever');
Query OK, 1 row affected (0.01 sec)
批量插入
insert into test(account,nickname,age,gender,password,registerTime,signature) values('124','昨夜闲潭梦落花',20,'m','666',now(),'可怜春半不还家');
insert into test(account,nickname,age,gender,password,registerTime,signature) values('125','若雪',19,'f','999',now(),'日日思君不见君');
insert into test(account,nickname,age,gender,password,registerTime,signature) values('126','寒秋',19,'f','996',now(),'衣带渐宽终不悔');
查询全部
mysql> select * from test;
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| 123 | 人与西风 | 20 | m | 666 | 2020-02-14 22:48:13 | love you forever |
| 124 | 昨夜闲潭梦落花 | 20 | m | 666 | 2020-02-14 22:48:20 | 可怜春半不还家 |
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
4 rows in set (0.00 sec)
按账号查
mysql> select * from test where account ='123';
+---------+--------------+------+--------+----------+---------------------+------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+--------------+------+--------+----------+---------------------+------------------+
| 123 | 人与西风 | 20 | m | 666 | 2020-02-14 22:48:13 | love you forever |
+---------+--------------+------+--------+----------+---------------------+------------------+
1 row in set (0.00 sec)
按年龄查
mysql> select * from test where age = 19;
+---------+----------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+----------+------+--------+----------+---------------------+-----------------------+
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+----------+------+--------+----------+---------------------+-----------------------+
2 rows in set (0.00 sec)
多条件查询
mysql> select * from test where account ='125' and age = 19;
+---------+----------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+----------+------+--------+----------+---------------------+-----------------------+
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
+---------+----------+------+--------+----------+---------------------+-----------------------+
1 row in set (0.00 sec)
范围查询
mysql> select * from test where registerTime between '2020-02-14 22:48:15' and '2020-02-14 22:48:30';
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| 124 | 昨夜闲潭梦落花 | 20 | m | 666 | 2020-02-14 22:48:20 | 可怜春半不还家 |
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
3 rows in set (0.00 sec)
mysql> select * from test where age in(19,25);
+---------+----------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+----------+------+--------+----------+---------------------+-----------------------+
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+----------+------+--------+----------+---------------------+-----------------------+
2 rows in set (0.00 sec)
大于小于逻辑运算查询
mysql> select * from test where age >18 and age<20;
+---------+----------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+----------+------+--------+----------+---------------------+-----------------------+
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+----------+------+--------+----------+---------------------+-----------------------+
2 rows in set (0.00 sec)
空与非空查询
mysql> select * from test where nickname is null;
Empty set (0.00 sec)
mysql> select * from test where age is not null;
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| 123 | 人与西风 | 20 | m | 666 | 2020-02-14 22:48:13 | love you forever |
| 124 | 昨夜闲潭梦落花 | 20 | m | 666 | 2020-02-14 22:48:20 | 可怜春半不还家 |
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
4 rows in set (0.00 sec)
模糊查询
mysql> select * from test;
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| 123 | 人与西风 | 20 | m | 666 | 2020-02-14 22:48:13 | love you forever |
| 124 | 昨夜闲潭梦落花 | 20 | m | 666 | 2020-02-14 22:48:20 | 可怜春半不还家 |
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
4 rows in set (0.01 sec)
mysql> select * from test where password like '%6';
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| 123 | 人与西风 | 20 | m | 666 | 2020-02-14 22:48:13 | love you forever |
| 124 | 昨夜闲潭梦落花 | 20 | m | 666 | 2020-02-14 22:48:20 | 可怜春半不还家 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
3 rows in set (0.00 sec)
mysql> select * from test where password like '9%6';
+---------+----------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+----------+------+--------+----------+---------------------+-----------------------+
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+----------+------+--------+----------+---------------------+-----------------------+
1 row in set (0.00 sec)
排序查询
mysql> select * from test order by account asc;
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| 123 | 人与西风 | 20 | m | 666 | 2020-02-14 22:48:13 | love you forever |
| 124 | 昨夜闲潭梦落花 | 20 | m | 666 | 2020-02-14 22:48:20 | 可怜春半不还家 |
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
4 rows in set (0.00 sec)
mysql> select * from test order by account desc;
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 124 | 昨夜闲潭梦落花 | 20 | m | 666 | 2020-02-14 22:48:20 | 可怜春半不还家 |
| 123 | 人与西风 | 20 | m | 666 | 2020-02-14 22:48:13 | love you forever |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
4 rows in set (0.00 sec)
mysql> select * from test order by age asc;
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| account | nickname | age | gender | password | registerTime | signature |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
| 125 | 若雪 | 19 | f | 999 | 2020-02-14 22:48:20 | 日日思君不见君 |
| 126 | 寒秋 | 19 | f | 996 | 2020-02-14 22:48:21 | 衣带渐宽终不悔 |
| 123 | 人与西风 | 20 | m | 666 | 2020-02-14 22:48:13 | love you forever |
| 124 | 昨夜闲潭梦落花 | 20 | m | 666 | 2020-02-14 22:48:20 | 可怜春半不还家 |
+---------+-----------------------+------+--------+----------+---------------------+-----------------------+
分组查询
mysql> select if(gender='f','男','女') as '性别',count(*) as '人数' from test group by gender;
+--------+--------+
| 性别 | 人数 |
+--------+--------+
| 男 | 2 |
| 女 | 2 |
+--------+--------+
2 rows in set (0.00 sec)
退出数据库
mysql> exit;
Bye