确定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;[删除所有记录]