Linux-015-Centos mysql 创建用户、数据库、授权、建表、数据插入

# 使用管理员账户，登录 mysql 数据库后执行操作
# 创建用户：创建用户 fanfengping，可在任意终端登录，密码 mysql
create user 'fanfengping'@'%' identified by 'mysql';

# 创建数据库：创建数据库 test_a，指定编码格式 utf8，若已存在则删除后创建
CREATE DATABASE IF NOT EXISTS test_a DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

# 用户授权：赋给用户 fanfengping 在任意可连接的客户操作终端对数据库 test_a 的所有操作权限
grant all on test_a.* to 'fanfengping'@'%' identified by 'mysql';

 

# 刷新，使配置更新生效
flush privileges;

 

# 创建表
DROP TABLE `user_basic_info`;
CREATE TABLE `user_basic_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '登录账号',
`password` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '登录密码',
`name` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '用户姓名',
`mobile` varchar(11) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '手机号码',
`email` varchar(50) COLLATE utf8_unicode_ci DEFAULT '' COMMENT '邮箱账号',
`address` varchar(100) COLLATE utf8_unicode_ci DEFAULT '' COMMENT '地址信息',
`level` varchar(10) COLLATE utf8_unicode_ci DEFAULT '' COMMENT '会员等级',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`utime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `index_username` (`username`) USING BTREE COMMENT '登录账号唯一索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='用户基本信息';

 

# 插入数据
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('zsbimucwgu', 'TakBN9Ckm0', 'zsbimucwgu', '13541079697', 'raxiigjv@163.com', '上海市黄浦区人民广场', '4', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('lvydyzkiqc', '75aKMxv4_m', 'lvydyzkiqc', '13567743567', 'eoyxwbou@163.com', '上海市黄浦区人民广场', '3', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('znfmhjlceo', 'O8clpJnfoa', 'znfmhjlceo', '13549349979', 'ercrgqxs@163.com', '上海市黄浦区人民广场', '5', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('pivwnwtfyn', 'KDCc4nOGu4', 'pivwnwtfyn', '13570142421', 'awzuqtcq@163.com', '上海市黄浦区人民广场', '7', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('hpqydzwzba', 'Kc0hWjxmgd', 'hpqydzwzba', '13576340515', 'frqzwjoc@163.com', '上海市黄浦区人民广场', '1', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('jnshjosiid', 'Fxjmd7legd', 'jnshjosiid', '13584712719', 'wovqjsoi@163.com', '上海市黄浦区人民广场', '7', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('wosvzjipvl', 'ZLNKXryRS8', 'wosvzjipvl', '13508554317', 'xgxuowwo@163.com', '上海市黄浦区人民广场', '1', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('yvwjthzquw', 'ITKfYbhZ8Y', 'yvwjthzquw', '13538229383', 'bswsbhvz@163.com', '上海市黄浦区人民广场', '8', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('yqxgupvxdz', 'wpE9UrMFkz', 'yqxgupvxdz', '13593354661', 'hruhyuxt@163.com', '上海市黄浦区人民广场', '6', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('pycnsapvzu', 'K_ERonxm6C', 'pycnsapvzu', '13564937362', 'xizzrijz@163.com', '上海市黄浦区人民广场', '4', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('mdkmihudux', 'gconpPWdV7', 'mdkmihudux', '13594380210', 'yovcyqae@163.com', '上海市黄浦区人民广场', '1', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('fzlshkseio', '8QcG2mvijt', 'fzlshkseio', '13586894225', 'xqzocwzo@163.com', '上海市黄浦区人民广场', '4', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('mcqfhxmyvv', 'rlIeXfGx7x', 'mcqfhxmyvv', '13508631031', 'goesyjtz@163.com', '上海市黄浦区人民广场', '8', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('zonncziqpi', '3ZmwTw32PC', 'zonncziqpi', '13550433938', 'htqbzowo@163.com', '上海市黄浦区人民广场', '9', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('xkrloonomn', 'GHQQoe_6rc', 'xkrloonomn', '13580237097', 'bbdtsuzo@163.com', '上海市黄浦区人民广场', '8', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('yazkkydcsa', 'gsr1IwFR9_', 'yazkkydcsa', '13534042780', 'orvwfuoi@163.com', '上海市黄浦区人民广场', '9', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('stijpjforw', 'r1z4Pq8olY', 'stijpjforw', '13538965875', 'rgrjhxex@163.com', '上海市黄浦区人民广场', '0', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('fnimsjfaav', 'sJn_I_d63M', 'fnimsjfaav', '13563966994', 'bzqzrgvf@163.com', '上海市黄浦区人民广场', '1', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('egiyyyjodh', '7Jv538WI2w', 'egiyyyjodh', '13572689813', 'hbajtzwt@163.com', '上海市黄浦区人民广场', '2', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('neuxtuedyf', 'YU0Af6sH3n', 'neuxtuedyf', '13533002008', 'tisysbaz@163.com', '上海市黄浦区人民广场', '1', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('yelasyovnx', 'Bz79oZj12v', 'yelasyovnx', '13591620544', 'qaqavhgd@163.com', '上海市黄浦区人民广场', '2', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('ruiampjarr', 'AuPE6di3t6', 'ruiampjarr', '13539298329', 'sxbatdto@163.com', '上海市黄浦区人民广场', '5', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('ezshdddaut', 'xuaeXeeLCI', 'ezshdddaut', '13525030311', 'tsfysrjc@163.com', '上海市黄浦区人民广场', '5', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('zxdfoklkph', '8qF0BHUKiI', 'zxdfoklkph', '13525518733', 'zxzjtjrs@163.com', '上海市黄浦区人民广场', '9', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('lrnkwkuuxs', '5WbYIv2mjr', 'lrnkwkuuxs', '13508186563', 'oyzfshdv@163.com', '上海市黄浦区人民广场', '3', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('teelfahmiq', 'p7YoKSrcF7', 'teelfahmiq', '13588012368', 'jfvxaojt@163.com', '上海市黄浦区人民广场', '6', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('sxlhahucti', '7PQRgnyvKW', 'sxlhahucti', '13589411053', 'yvrucobi@163.com', '上海市黄浦区人民广场', '6', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('rhvnlxyosg', 'aaOEGRwMeu', 'rhvnlxyosg', '13506171856', 'iaayijig@163.com', '上海市黄浦区人民广场', '6', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('wxyawlklnq', 'UwbLYxPr3u', 'wxyawlklnq', '13568211096', 'yuueihjc@163.com', '上海市黄浦区人民广场', '7', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('wvrktbckaw', 'V_bX313So1', 'wvrktbckaw', '13573901378', 'ubjdbsev@163.com', '上海市黄浦区人民广场', '6', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('onhexuczve', 'lMBJc4IJT1', 'onhexuczve', '13529655823', 'rubhgrfy@163.com', '上海市黄浦区人民广场', '9', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('jvxuqymonp', 'Gm58wtWRq_', 'jvxuqymonp', '13597559182', 'hidziqur@163.com', '上海市黄浦区人民广场', '5', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('xkiynwhgxn', 'byGwxq41de', 'xkiynwhgxn', '13547210610', 'htyaufou@163.com', '上海市黄浦区人民广场', '7', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('mbgtykfuez', 'mkfwGHjn04', 'mbgtykfuez', '13579891530', 'ajgxicrj@163.com', '上海市黄浦区人民广场', '5', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('fwtblfulbv', 'wKZIvclOCV', 'fwtblfulbv', '13527069895', 'eegsoogf@163.com', '上海市黄浦区人民广场', '3', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('cyezguedkh', 'qQtkS0Wsth', 'cyezguedkh', '13599334032', 'aeeicqzt@163.com', '上海市黄浦区人民广场', '8', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('pzmfzxqtfb', 'UFbtn76Abf', 'pzmfzxqtfb', '13526542753', 'fsudjrxb@163.com', '上海市黄浦区人民广场', '0', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('lmixdpvjrt', 'W8cvv4K6XD', 'lmixdpvjrt', '13536336988', 'qiouubdb@163.com', '上海市黄浦区人民广场', '2', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('jfnaswbvdj', 'ktzcXNcN6e', 'jfnaswbvdj', '13568624218', 'duwdwobz@163.com', '上海市黄浦区人民广场', '6', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('ebbqvjxeqi', 's0PQ6cLXhw', 'ebbqvjxeqi', '13581163530', 'jhxwgtub@163.com', '上海市黄浦区人民广场', '6', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('ymjphpkhxo', 'tyQgGSnbZ4', 'ymjphpkhxo', '13527515452', 'ihsueszq@163.com', '上海市黄浦区人民广场', '1', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('dmkxtdgfvq', 'CNpMaAguij', 'dmkxtdgfvq', '13589162479', 'aubavzib@163.com', '上海市黄浦区人民广场', '9', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('kshorhgmko', 'ADWu5jdTFu', 'kshorhgmko', '13579347784', 'ruccztax@163.com', '上海市黄浦区人民广场', '8', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('fxydiyeoyr', 'L9A9uerK7C', 'fxydiyeoyr', '13552558816', 'otjczffv@163.com', '上海市黄浦区人民广场', '3', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('lzfwqprtuh', 'uJGsUWaYhU', 'lzfwqprtuh', '13509954101', 'abtcfvig@163.com', '上海市黄浦区人民广场', '6', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('qikzofvqth', 'YHhVkOOf1U', 'qikzofvqth', '13592275569', 'fdfggcsa@163.com', '上海市黄浦区人民广场', '8', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('gkexgorgxp', '86QwnhlNWQ', 'gkexgorgxp', '13577252836', 'jdbofths@163.com', '上海市黄浦区人民广场', '7', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('mxvappqplb', 'T1nM4OBomn', 'mxvappqplb', '13578128253', 'hotztrwg@163.com', '上海市黄浦区人民广场', '8', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('rgdhlqhmcd', '8m7qlcOcDg', 'rgdhlqhmcd', '13563709182', 'ziwyuxci@163.com', '上海市黄浦区人民广场', '6', now(), now());
insert into user_basic_info(username, `password`, `name`, mobile, email, address, `level`, ctime, utime) VALUE ('vjabekoexu', 'dy9ru2AhPJ', 'vjabekoexu', '13580692822', 'zusywsuz@163.com', '上海市黄浦区人民广场', '0', now(), now());