一.连接
1.格式：mysql -h主机名 -u用户名 -p用户密码
2.示例：
(1)连接到本地
//假设root为主机名（默认），123456为密码
mysql -uroot -p123456;
(2)连接到远程主机上的MySQL
//假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：
mysql -h110.110.110.110 -uroot -pabcd123;
3.mysql 结束必须有封号
4.输入\q或者EXIT退出
二. 库
1.查看所有数据库
show databases;
/*(MySQL内有四个自带解析数据库
information_schema,
mysql,
performance_schema,
sys)*/
2.新建数据库
//创建名为bhz_credit的数据库
CREATE DATABASE bhz_credit;
3.使用某数据库
use DatabaseName;
/*use 数据库后,如果想跳到其他数据库，用use 其他数据库名字就可以了。*/
4.select database();
显示当前选择的数据库。返回null表示没有选择
三.表
1.查看所有表
show tables;
2.创建表
//创建 一个user_credit 表
CREATE TABLE `user_credit` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`USER_ID` int(11) unsigned NOT NULL COMMENT '用户id',
`ORDER_ID` int(11) unsigned NOT NULL COMMENT '订单id',
`STG_ID` int(11) unsigned NOT NULL COMMENT '门店id',
`ORDER_START_TIME` datetime NOT NULL COMMENT '订单开始时间',
`UPDATE_TIME` datetime NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
四、插入数据
1.向user_credit表插入一条数据
INSERT INTO
user_credit(USER_ID,ORDER_ID,STG_ID,ORDER_START_TIME,UPDATE_TIME)
VALUES(1001,0001,657,'2018-05-01 00:00:00','2018-05-01 00:00:10');
//注意int类型的不能加单引号，datetime类型的必须加单引号，不要加反的那个单引号
五、检索数据
1.从user_credit中检索数据
SELECT
u.ID as recordId,
u.STG_ID as stgId,
u.ORDER_ID as orderId,
u.USER_ID as userId,
u.ORDER_START_TIME as orderStartTime,
u.UPDATE_TIME as updateTime
FROM
user_credit as u;
2.按ID检索数据
SELECT
u.ID as recordId,
u.STG_ID as stgId,
u.ORDER_ID as orderId,
u.USER_ID as userId,
u.ORDER_START_TIME as orderStartTime,
u.UPDATE_TIME as updateTime
FROM
user_credit as u
WHERE
u.USER_ID= 1001;
3.按ID和某时间段检索数据
SELECT
u.ID as recordId,
u.STG_ID as stgId,
u.ORDER_ID as orderId,
u.USER_ID as userId,
u.ORDER_START_TIME as orderStartTime,
u.UPDATE_TIME as updateTime
FROM
user_credit as u
WHERE
u.USER_ID= 1001
AND ORDER_START_TIME BETWEEN '2018-04-05 01:00:00' AND '2018-04-05 19:00:00';
六、增加字段
1. 向user_credit加一个 'CREATE_ORDER_TIME' 字段
alter table user_credit add CREATE_ORDER_TIME datetime NOT NULL;
