【关于mysql指令】

创建数据集
CREATE XXX
展示数据集
SHOW DATABASES
使用数据集
use XXX
退出
exit

进入数据集并修改
mysql -uroot -p --default-character-set=gbk
接下来这句（不知道需不需要，反正我加了）
use XXX（数据集名字）

样例
添加table
 create table t_user(
    -> uid int unsigned NOT NULL auto_increment,
    -> ucode varchar(12) NOT NULL, uname varchar(20) NOT NULL,
    -> upass varchar(40) NOT NULL, ugender tinyint, ubirthday date,
    -> utype tinyint(2) NOT NULL, uaddress text, uzip varchar(8),
    -> umoney int NOT NULL default 1000, utel varchar(20), uemail varchar(30),
    -> ulogo varchar(50), udate  date,
    -> PRIMARY KEY (`uid`)
    -> );

添加数据
insert into t_user(ucode, uname, upass, ugender, ubirthday, utype, umoney) values('170104100101', '蔡怡帅', '123456', 1, '2001-2-14', 3, 9521),
('170104100102', '柴奕', '123456', 1, '1999-0-11', 5, 8601),
('170104100103', '陈凡', '123456', 1, '2000-5-25', 5, 4965),
('170104100104', '陈悦', '123456', 1, '2000-11-23', 3, 6586),
('170104100106', '方兴安', '123456', 0, '2001-8-7', 4, 1900),
('170104100108', '郭晓萱', '123456', 1, '2001-6-24', 6, 8069),
('170104100109', '侯卓伶', '123456', 1, '2000-2-2', 2, 7642),
('170104100110', '黄张欢', '123456', 1, '1999-6-20', 2, 8716),
('170104100112', '梁曙 轩', '123456', 0, '1999-10-10', 5, 7470),
('170104100113', '刘晗', '123456', 1, '2000-4-25', 4, 3173),
('170104100116', ' 毛媛媛', '123456', 1, '2000-5-16', 6, 6150),
('170104100117', '梅吕珂', '123456', 1, '2000-3-6', 2, 8374),
('170104100118', '齐钊瑜', '123456', 1, '2000-8-8', 3, 1408),
('170104100119', '任逸', '123456', 0, '1999-7-8', 1, 8522),
('170104100121', '苏江武', '123456', 0, '2000-8-3', 4, 7964),
('170104100122', '孙晶怡', '123456', 1, '2000-11-19', 2, 4114),
('170104100123', '孙玉洁', '123456', 1, '2000-0-4', 2, 2326),
('170104100124', '谭灏', '123456', 0, '2001-4-0', 3, 4835),
('170104100125', '王雪杰', '123456', 1, '2001-10-15', 6, 5274),
('170104100126', '王宇航', '123456', 0, '2001-0-18', 3, 8998),
('170104100128', '吴晓波', '123456', 0, '2000-10-17', 5, 7481),
('170104100129', '吴心协', '123456', 1, '1999-10-17', 1, 4023),
('170104100130', '谢城燕', '123456', 1, '2001-0-0', 1, 6800),
('170104100132', '杨坤尤', '123456', 0, '2001-8-11', 2, 2133),
('170104100133', '杨梦', '123456', 1, '2000-5-26', 6, 5064),
('170104100134', '杨梦舒', '123456', 1, '1999-6-14', 2, 4553),
('170104100136', '叶泽恩', '123456', 0, '1999-10-5', 2, 2711),
('170104100138', '张育晖', '123456', 0, '2001-9-10', 6, 9376),
('170104100139', '赵薇', '123456', 1, '2001-3-21', 4, 1222),
('170104100141', '朱聪', '123456', 0, '2000-4-20', 3, 7596),
('170104100142', '朱俊杰', '123456', 0, '1999-9-4', 6, 2635),
('170104100143', '朱乾源', '123456', 0, '1999-4-5', 3, 6188),
('170104100144', '庄晓瑜', '123456', 1, '1999-11-8', 1, 3158),
('170104100201', '鲍玉霞', '123456', 1, '1999-5-1', 1, 5668),
('170104100202', '边佳慧', '123456', 1, '1999-10-7', 4, 4080),
('170104100203', '陈明勇', '123456', 0, '1999-11-8', 1, 9471),
('170104100204', '陈云婷', '123456', 1, '2001-1-9', 6, 6363),
('170104100205', '陈卓', '123456', 1, '1999-8-12', 1, 5833),
('170104100206', '费馨雯', '123456', 1, '2001-8-21', 6, 4099),
('170104100207', '奉耘旭', '123456', 0, '2000-10-16', 4, 6823),
('170104100208', '韩珂', '123456', 1, '2000-7-6', 6, 3500),
('170104100209', '胡雨晴', '123456', 1, '2000-7-4', 6, 8034),
('170104100210', '黄雅丽', '123456', 1, '2000-5-2', 2, 6827),
('170104100213', '康杰', '123456', 0, '2001-6-2', 6, 7227),
('170104100215', '刘美君', '123456', 1, '1999-0-25', 2, 8344),
('170104100216', '刘小琴', '123456', 1, '1999-11-27', 5, 3134),
('170104100217', '娄凌豪', '123456', 0, '1999-11-13', 1, 6900),
('170104100218', '楼世昊', '123456', 0, '2000-5-6', 3, 8675),
('170104100219', '卢蕾羽', '123456', 1, '2001-7-18', 5, 5513),
('170104100220', '罗银巍', '123456', 0, '2001-0-22', 2, 3686),
('170104100221', '宋昱', '123456', 0, '2000-2-10', 3, 9528),
('170104100222', '陶洋 铮', '123456', 0, '2001-7-27', 6, 9519),
('170104100223', '田舒琪', '123456', 1, '2000-6-6', 5, 7919),
('170104100224', ' 王芃萱', '123456', 1, '1999-6-20', 2, 2316),
('170104100225', '王潇灵', '123456', 1, '2000-3-17', 2, 3230),
('170104100226', '吴楚楚', '123456', 1, '2000-7-12', 5, 5507),
('170104100227', '项紫薇', '123456', 1, '2001-9-0', 1, 8439),
('170104100228', '徐荔薇', '123456', 1, '2000-3-0', 3, 2164),
('170104100232', '杨璐', '123456', 1, '2000-0-22', 2, 5650),
('170104100233', '杨权', '123456', 0, '2001-5-12', 2, 3949),
('170104100234', '尹祥', '123456', 0, '1999-9-25', 3, 7436),
('170104100235', '应羽燕', '123456', 1, '2000-1-10', 3, 7968),
('170104100236', '雍雪婷', '123456', 1, '2000-4-16', 2, 8835),
('170104100238', '袁鑫泽', '123456', 0, '2001-8-19', 5, 6061),
('170104100239', '张铭舟', '123456', 1, '1999-6-11', 3, 7951),
('170104100240', '张榕', '123456', 1, '1999-6-1', 6, 4641),
('170104100241', '郑浩杰', '123456', 0, '2001-11-6', 2, 8932),
('170104100242', '朱镕锋', '123456', 0, '2000-7-2', 3, 9341),
('170104400142', '钟善男', '123456', 0, '2000-2-0', 2, 7579);

一些查询显示语句
显示该表单中的全部数据
select * from t_user;
只查找名字为XXX的数据
中文需加‘’
select * from t_user where uname='柴奕';
查找性别为男的全部数据
select * from t_user where ugender=0;
查找该表单中的某几项数据
select uid, ucode, uname, ugender, umoney from t_user;
查找男性，金钱>=8000的几项数据
select uid, ucode, uname, ugender, umoney from t_user where ugender=1 and umoney>=8000;
按金钱从小到大
select uid, ucode, uname, ugender, umoney from t_user where ugender=1 order by umoney;
按金钱从大到小
select uid, ucode, uname, ugender, umoney from t_user where ugender=1 order by -umoney;
select uid, ucode, uname, ugender, umoney from t_user where ugender=1 order by umoney desc;  
查找陈姓开头的人
select uid, ucode, uname, ugender, umoney from t_user where uname like '陈%';
查找女性金钱前20名，从大到小排
select uid, ucode, uname, ugender, umoney from t_user where ugender=1 order by umoney desc limit 0,20;
查找男性女性的人数，平均金钱数（用性别分组）
select ugender,count(umoney),avg(umoney) from t_user group by ugender;
查找1999年5月9日及之后出生的女生
select * from t_user where ubirthday >='1999-5-9' and ugender =1;
查找学号第5位开始的三个数是041的人
substr(什么值，第几位开始（从1开始算），连续几位)
select * from t_user where substr(ucode,5,3)='041';

select concat(left(ucode,2),right(ucode,8)) from t_user;