Shulou(Shulou.com)06/02 Report--

LINUX database-about data cleaning 1, modify database code 1. Log in to the database mysql-uroot-proot first

two。 View database coding

It is found that the corresponding code of database and database service is latin1, and the code of database needs to be modified to UTF8.

Show VARIABLES like 'character%'

3. Exit the database and edit the / etc/my.cnf file exitvi / etc/my.cnf

4. Modify the configuration file

Add to the configuration file, after writing: wq save exit

[client] default-character-set=utf8 [mysqld] character-set-server=utf8collation-server=utf8_unicode_ci

5. Restart the database service systemctl restart mysql.service

6. Enter the database again and check the database code mysql-uroot-prootshow VARIABLES like 'character%'

Second, create database and table creation database in LINUX. Create a database create database kb02

two。 Create table use kb02create table userinfos (userid int (20) primary key not null auto_increment,username varchar (20), birthday date)

3. View the newly created database and table showdatabases;show tables

4. Hot backup export

Exit the database and export the database using mysqldump

Exitmysqldump-uroot-proot kb02 > / opt/mysql/mysql.sql

5. Import Fil

Import the previously created sql file. Here, delete and then import the tables that have already been built.

Operate database 1. Import the locally built data into the database 2. Cleaning data

Filter those that do not meet the requirements, and write the results of the query to a new table: both username and birthday are filtered if they are empty, and those with the same name retain the information of the largest userid.

Create table cp_user as select max (userid) uid, username, max (birthday) birth from (SELECT * from userinfos WHERE username! =''and birthday! ='') newuserinfosGROUP BY usernameORDER BY uid

Exist ()

3. Inquire name (Zhang, Li)

Fuzzy query:

3.1 query the name that begins with l (case-insensitive)

3.2 query names that begin with L (case-sensitive)

3.3 query the name at the beginning of LMagol W (regardless of case)

4. Enquire about the number of births (in a certain year) 4.1 Enquiry about people born in 1995

There are three ways:

4.1.1 Fuzzy query

4.1.1 Fuzzy query

5. Query name, number of births (number of Zhang and Li born in a certain year) 5.1 query people with the surname l or w and were born between 1901 and 1903

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.