-- 连接数据库-- mysql -u root -p-- 查看有哪些数据库show databases;-- 创建数据库create database demo;-- 选中指定数据库use demo;-- 查看选中的数据库有哪些表show tables;/* ---- Table ---- */-- 创建表CREATE TABLE user (id INT NOT NULL PRIMARY KEY auto_increment COMMENT‘ID‘,username CHAR(20) NOT NULL COMMENT ‘用户名‘,password CHAR(36) NOT NULL COMMENT ‘密码‘,nickname VARCHAR(20) COMMENT ‘昵称‘,sex ENUM(‘MALE‘,‘FEMALE‘,‘UNKNOWN‘) NOT NULL DEFAULT ‘UNKNOWN‘ COMMENT ‘性别‘,birthday DATE COMMENT ‘生日‘,deleted ENUM (‘YES‘,‘NO‘) DEFAULT ‘NO‘ COMMENT ‘是否删除‘,update_time DATETIME NOT NULL COMMENT ‘更新时间‘,create_time DATETIME NOT NULL COMMENT ‘创建时间‘);-- 查看表结构show columns from user;-- 添加约束alter table user add unique(`username`);-- 给表添加一个字段ALTER TABLE user ADD remark VARCHAR(50) COMMENT ‘备注‘;-- 插入一条数据INSERT INTO user(username,password,nickname, update_time,create_time) VALUES (‘harlan‘,md5(‘123456‘),‘Harlan‘,NOW(),NOW());-- 插入多条数据INSERT INTO user(username,password,nickname, update_time,create_time) VALUES (‘harlan‘,md5(‘123456‘),‘Harlan‘,NOW(),NOW()),(‘amy‘,md5(‘123456‘),‘Amy‘,NOW(),NOW());/* ---- Query ---- */-- 查看指定表所有数据SELECT * FROM user;-- 查询前2条数据SELECT * FROM user limit 2;-- 查看第2页的1条数据 limit ((2-1)*1),1SELECT * FROM user limit 1,1;-- 加查询条件SELECT * FROM user WHERE username = ‘harlan‘;-- 分组SELECT sex FROM user GROUP BY sex;-- 排序 ASC / DESCSELECT * FROM user ORDER BY create_time ASC;-- 删除表中所有数据DELETE FROM user;-- 删除表 DROP TABLE user;-- 显示数据库编码格式SHOW VARIABLES LIKE ‘character%‘;-- 更改数据库编码格式ALTER DATABASE `ip` DEFAULT CHARACTER SET utf8 ;-- 导入txt文件LOAD DATA LOCAL  INFILE ‘E://ip.txt‘INTO TABLE ip  CHARACTER SET gbk FIELDS TERMINATED BY ‘|‘LINES TERMINATED BY ‘\n‘(ip_start, ip_end, ip_start_num,ip_end_num,continent,country,province,city,district,isp,area_code,country_english,country_code,longitude,latitude) ;