2-17 Job database and shell synthesis exercises

1. Use the shell to "12306 User name and password library-do not use Notepad to open the card will die-after decompression can be used word or ultraedit open. rar" All records genetic SQL statements, and then the SQL into the database into a upwd_12306 table. The field is defined by itself.
Extension: Please analyze the formation characteristics of the password in the "12306 User name and password Library" and summarize it.
2. Copy the "12306 User name and password library-do not use Notepad to open the card will die-after decompression can be opened with Word or ultraedit. rar" All mailbox and Password fields, use the u_id, mailbox, password fields to create a separate table upwd. Create an index on the Upwd table to help you find your username and password when you post your own late brute force account
3. See if there is a record of your own name in the upwd_12306 table
4, find out all the records in upwd_12306 table and upwd_90 view. (Year of birth >=1990)

1. Copy the file here first

Solve the problem of Chinese garbled:

[Email protected] ~]# Find/-name VIMRC
/etc/vimrc

[Email protected] ~]# VIM/ETC/VIMRC

Set fileencodings=utf-8,gb2312,gbk,gb18030

Set Termencoding=utf-8

Set Fileformats=unix

Set ENCODING=PRC

Writing scripts

[Email protected] ~]# vim make_12306_table.sh

#!/bin/Bash#made by yGY .-Ten#translate Convert a newline symbol,# Write SQL sentences such as INSERT into table_name values ('a','b');file=/home/yy/#convert \ r to"'sed-I.'s/\r//' "$file"12306. Txtmysql-E'CREATE database if not exists 12306_data;'-usystem-p123456Echo 'DROP TABLE IF EXISTS upwd_12306;'>"$file"12306_mid.sqlEcho "CREATE TABLE upwd_12306 (Uidintprimary Key auto_increment,unamevarchar +) notNULLDefault"', Paw varchar ( -) notNULLDEFAULT"', Realname varchar ( -) notNULLDEFAULT"', GMSFHM varchar ( -) notNULLDEFAULT"', other varchar ( -) notNULLDEFAULT"', tel varchar ( One) notNULLDEFAULT"', email varchar ( +) notNULLDEFAULT"'      );">>"$file"12306_mid.sqlsed 's/----/'\', \ ''/g'"$file" 12306.txt |awk'{Print"INSERT INTO upwd_12306 (uname,paw,realname,gmsfhm,other,tel,email) VALUES (' \ '"$0"' \ ');"}'>> "$file" 12306_mid.sqlmysql-usystem-p123456 12306_data <"$file"12306_mid.sql

View Code

Run an error

Surfing the internet for the next reason, because there are too many data. To modify the following configuration, Linux is my.cnf

Restart MySQL

[Email protected] ~]# service mysqld restart

Run again

[Email protected] ~]#./make_12306_table.sh

ERROR 1064 (42000) at line 2279:you has an error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' 210504197710140512 ', ' Lich eng1977 ', ' 13942443523 ', ' [email protected] ';

Error, I'll check the intermediate file.

Found that this data is Chinese is garbled.

Obviously has turned the code, why some words are garbled.

After research, originally uploaded 12306.txt encoding is UTF8, use notepad++ open, Chinese is garbled,

After you convert the format to ANSI, the Chinese can be displayed correctly, so the correct content is clipped, then the encoding format is UTF8, and then the content of the cut is pasted up.

Then upload this transcoded 12306.txt to Linux.

This method works, and other methods that try are not working.

Run again

[Email protected] ~]#./make_12306_table.sh

After waiting, go to MySQL to verify it.

Verify success!

2.

Mysql> CREATE table if not exists upwd (uid Int (ten) primary key, Upwd varchar (+), email varchar (40));

Import data

mysql> INSERT INTO Upwd (UID, upwd, e-mail) select Uid,paw,email from upwd_12306;

Query OK, 131653 rows affected (0.52 sec)

records:131653 duplicates:0 warnings:0

3. mysql> SELECT * from upwd where email like ' 4145965 ';

4. mysql> CREATE VIEW upwd_90 as SELECT * from upwd_12306 where substring (gmsfhm,7,4) >=1990;

Query OK, 0 rows Affected (0.00 sec)

2-17 Job database and shell synthesis exercises