确定MySQL服务端是否开启的方法：[牢记:服务端关闭,是没法进入MySQL数据库的]
-点击'我的电脑'(右键)->'管理'->'服务应用程序'->'服务'(双击)->查看MySQL状态
-在控制台CMD中输入:net start mysql57(回车)->'服务启动成功'
net stop mysql57(回车)->'服务成功停止'

打开MySQL数据库的方法：
-进入控制台CMD;输入mysql -u root -p(回车);输入密码(回车)[进入数据库]
-前提是把[C:\Program Files\MySQL\bin]写入环境变量中
-打开MySQL客户端:MySQL5.7Command Line Client-Unicode;输入密码(回车)[进入数据库]
-打开MySQL Workbench 6.3 CE[连接进入数据库]

\s:查看MySQL相关配置信息
-Current user:root@localhost
-Using delimiter:;[命令分隔符]
-Server version:5.7.20-log[==SELECT VERSION();]
-Client characterset:gbk
-Conn. characterset:gbk
-TCP port:3306
注意的问题:
-CMD进入MySQL数据库客户端和连接端字符编码为UTF8(如果为GBK需要修改为UTF8)
-方法:在配置文件my.ini中写入default-character-set=utf8/character-set-server=utf8
-MySQL客户端进入MySQL数据库则客户端和连接端字符编码为UTF8(无需修改)

\c:删除敲入的字符
退出MySQL数据库的方法:
-exit;
-quit;

\T 文本绝对路径\file.txt:开启MySQL日志
\t:关闭日志

注意:
在CMD输入用户账号:mysql -u qinlan -p ->输入密码:*****[可进入MySQL:数据库管理员DBadmin]


--创建数据库
CREATE DATABASE IF NOT EXISTS db_train;
USE db_train;
--创建数据表tb_train_user
CREATE TABLE IF NOT EXISTS tb_train_user(
userid TINYINT KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
sex ENUM('男','女','保密') DEFAULT '保密',
age INT UNSIGNED DEFAULT 20,
birth YEAR,
proid TINYINT UNSIGNED,
score FLOAT NOT NULL
)AUTO_INCREMENT=100 ENGINE=INNODB CHARSET=UTF8;
SHOW TABLES;
--查看表结构
DESC tb_train_user;
--查看数据表创建信息
SHOW CREATE TABLE tb_train_user;
--插入数据
INSERT tb_train_user(username,sex,age,birth,proid,score) VALUES
('曹操','男',45,'1992',2,98.4),
('吕布',DEFAULT,36,'2012',1,70.1),
('甄姬','女',28,'2018',5,82.8),
('赵子龙','男',DEFAULT,'2020',4,96.5),
('关凤','女',22,'2016',3,83.7),
('黄月英','女',30,'2011',1,89.6),
('James','保密',35,'2014',5,75.3),
('Nike','男',28,'2018',4,87.8);
SLECT * FROM tb_train_user;

--创建数据表tb_train_province
CREATE TABLE IF NOT EXISTS tb_train_province(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
proname VARCHAR(5) NOT NULL UNIQUE
);
--插入数据
INSERT tb_train_province(proname) VALUES
('北京'),
('深圳'),
('上海'),
('成都'),
('苏州'),
('西安');

------创建数据表tb_train_cpp1:修改表结构
CREATE TABLE IF NOT EXISTS tb_train_cpp1(
id TINYINT UNSIGNED,
name VARCHAR(10),
age INT UNSIGNED
);
--添加字段:
ALTER TABLE tb_train_cpp1 ADD sex ENUM('男','女') DEFAULT '男';
ALTER TABLE tb_train_cpp1 ADD card CHAR(3) NOT NULL UNIQUE AFTER name;

ALTER TABLE tb_train_cpp1
ADD sex ENUM('男','女') DEFAULT '男',
ADD card CHAR(3) NOT NULL UNIQUE AFTER name;[等价于以上2条命令]
--删除字段:
ALTER TABLE tb_train_cpp1 DROP sex;
ALTER TABLE tb_train_cpp1 DROP card;

ALTER TABLE tb_train_cpp1
DROP sex,
DROP card;[等价于以上2条命令]
--修改字段(属性):
ALTER TABLE tb_train_cpp1 MODIFY name VARCHAR(15) NOT NULL;
--修改表名:
ALTER TABLE tb_train_cpp1 RENAME tb_train_cpp;
ALTER TABLE tb_train_cpp RENAME tb_train_cpp1;

--设置默认值:
ALTER TABLE tb_train_cpp1 ALTER age SET DEFAULT 18;
ALTER TABLE tb_train_cpp1 ALTER age DROP DEFAULT;[删除默认值]
--设置主键:
ALTER TABLE tb_train_cpp1 ADD PRIMARY KEY(id);
ALTER TABLE tb_train_cpp1 DROP PRIMARY KEY;[删除主键]
--设置唯一:主键只能有一个+唯一可以有多个
ALTER TABLE tb_train_cpp1 ADD PRIMARY KEY(id);
ALTER TABLE tb_train_cpp1 ADD UNIQUE(name);
ALTER TABLE tb_train_cpp1 DROP INDEX name;[删除唯一]
--插入数据/删除数据:
INSERT tb_train_cpp1(id,name,age) VALUES
(1,'M1',12),
(3,'M2',6),
(8,'M3',25),
(12,'M4',20);
DELETE FROM tb_train_cpp1 WHERE id=8;[删除1条记录]
TRUNCATE TABLE tb_train_cpp1;[删除所有记录]