【 computer Undergraduate program 】Mysql learning subtotal (4) published articles have been saved

The text before

I finally sent out my miserable graduation project approval form yesterday. As a result, the production practice of the summer vacation began to check accounts, and I began to work here again, but also to sign, I sometimes think that the whole class to sign again. Otherwise really anxious ah! MMP, school these things broken things!! It’s reasonable, but it’s too busy! Vexed very vexed, no matter, I still set my mind to learn my small xi, occasionally write articles to find fun! Today I wrote a different article (compared to my usual style of writing, or did I not have it before? Anyway, will law take a look at the magistrate? A like is the best?)

Cheng Jia — Study hard, Personal Knowledge Management Improvement Guide

The body of the

MySQL sequence usage

If you add a row, it will automatically add the value to the row, even if you do not specify the value of the corresponding column. Just like a staff record, without inserting an employee record, the employee number can be automatically increased by 1, without you having to look at it and specify the number. Think of our freshman enrollment when the allocation of student number, if we have to add one by one, increase the amount of work, and if the accuracy is not guaranteed, such as if U201710655 followed by a U201710657 in the middle of the individual will not be. How embarrassed? So increment columns are necessary, and this is where Mysql sequences come in.

You can start by specifying that a column is a self-increasing sequence, as follows:

Create table auto_raise(ID int unsigned not null AUTO_increment,primary key(ID),··· name VARCHAR (30) not null,date date Not null) ··· engine= InnoDB auto_increment=201410600 CHARset = UTf8;Copy the code

So I’m going to do this table, and I’m not going to insert the id column, and I’m going to do it myself.

In addition, I don’t know if you have seen it, but my ID has been growing since 201410600. How did I do that? Why don’t you go up and see how you did it when you created the table? Smart as you must have seen it all at once!

INSERT INTO auto_raise (id,name,date) VALUES ···
 (NULL,'housefly'.'2001-09-10'),(NULL,'millipede'.'2001-09-10');Copy the code

In the MySQL client you can use the 1262205 function in SQL to get the value of the increment column in the last insert table.

This is probably for manual insertion, right? Sure enough, manual insertion will cause the increment point to change, that is, you can create the increment point, first insert a desired starting point, and then continue to increment the purpose of the specified increment point, similar to the above mentioned when creating the table.

When you look at me and you insert it again, it’s going to start where I inserted it last time.


Sure enough, I think I know about increased urine. That is to catch the biggest plug, if you specify that it does not increment. The alacrity. There is a self-increase I designated an egg ah!!

If you delete multiple entries from a table and want to rearrange the AUTO_INCREMENT column for the remaining data, you can do this by deleting the AUTO_INCREMENT column and then adding it again. Do this with care, however; if a new record is added at the same time as the deletion, data may be corrupted. The operation is as follows:

As you can see, if you don’t delete the original column, the table already remembers the last increment, so you can’t find the original increment. So delete the current row and column and continue to increment

alter table auto_raise drop id; ··· alter table auto_rasIE add ID INT UNSIGNED NOT NULL ··· · AUTO_INCREMENT FIRST, add PRIMARY KEY (ID);Copy the code

It seems that there is no good way to make all id records start from 201410600. After that, I have to change it slowly, trouble, and then again, don’t panic now.

alter table auto_raise auto_increment=201410600;Copy the code

MySQL handles duplicate data

You can specify the PRIMARY KEY or UNIQUE index in the MySQL table to ensure that the data is UNIQUE. When inserting duplicate data, an error will be reported. If we can set it to ignore, the error will not be reported, but the insert will be invalid, that is, the insert will be ignored.

INSERT IGNORE INTO auto_raise (id,name) VALUES(2017, 'Thomas');Copy the code

Replace, as the name implies, ignores the insert. Replace obviously overwrites the original duplicate data:

replace INTO auto_raise (id,name,date) VALUES(2017, 'Zhang'.'2018-09-11');Copy the code

Select duplicate data by name from group by

select count(*) as repeations,name from auto_raise group by name ;Copy the code

Two filtering methods:

mysql> select distinct name from auto_raise ;
mysql> select name from auto_raise group by name;Copy the code

Select * from group by; select * from group by; select * from group by;

MySQL > select * from ‘MySQL’;

In MySQL you can use SELECT… INTO OUTFILE statement to simply export data to a text file.

select * from auto_raise into outfile '/Users/zhangzhaobo/Desktop/test.txt';Copy the code

Of course, unsurprisingly, you have this question:

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

The Internet to find a lot of ways, and finally this more reliable point:

#[/private/etc/my.cnf]
[mysqld]
secure-file-priv="Your Path"Copy the code

Sure, you can go first

show variables like '%secure%';Copy the code

Check out a wave. As usual, the NULL in the third line of the result indicates that the export is restricted, so you need to change the system configuration. +————————–+——-+ | Variable_name | Value | +————————–+——-+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | NULL | +————————–+——-+

Follow the above strategy and just play it straight. /private/etc/ my.cnf = my.cnf; /private/etc/ my.cnf = my.cnf; /private/etc/ my.cnf = my.cnf;

I’m even kind enough to let you copy it directly:

[mysqld]
# Only allow connections from localhost
bind- the address = 127.0.0.1 secure - file - priv ='/Users/zhangzhaobo/Desktop/'Copy the code

Make sure you change the path. Otherwise you don’t have me this user name, the path reported wrong.

Then restart the mysql server

This is what happens after the execution

SELECT … The INTO OUTFILE statement has the following attributes:

  • LOAD DATA INFILE SELECT… INTO OUTFILE inverse operation, SELECT syntax To write data from a database to a file, use SELECT… INTO OUTFILE, to read the file back to the database, use LOAD DATA INFILE.

  • SELECT… INTO OUTFILE SELECT of the form ‘file_name’ writes the selected rows to a file. The FILE is created on the server host, so you must have FILE permission to use this syntax.

  • The output cannot be an existing file. Prevent file data from being tampered with.

  • You need to have a login account to the server to retrieve files. Otherwise, the SELECT… INTO OUTFILE does nothing.

  • In UNIX, the file is created readable and has permissions owned by the MySQL server. This means that while you can read the file, you may not be able to delete it.

Export data in SQL format, which you can use directly after copying to fully produce the current table:

The commands are also easy to use, and I suspect that changing the extension to SQL could simply regenerate a new database table on another server.

And worse!

$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******
$ mysqldump -u root -p --all-databases > database_dump.txt
password ******Copy the code

Copy the data table and database to other hosts, otherwise you think the exported data is directly for you to see ah, of course, is used for communication between databases!!

$ mysql -u root -p database_name < dump.txt
password *****Copy the code

You can also use the following command to import the exported data directly to a remote server, but make sure the two servers are connected and accessible to each other:

$ mysqldump -u root -p database_name \ | mysql -h other-host.com database_nameCopy the code

MySQL > alter database;

Import DATA using LOAD DATA:

mysql> load data local infile '/Users/zhangzhaobo/Desktop/test1.txt' into table tableclone;Copy the code
ERROR 1148 (42000): The used command is not allowed with this MySQL version

Import data using mysqlimport:

HustWolf:~ zhangzhaobo$ mysqlimport -u root -p --local test '/Users/zhangzhaobo/Desktop/test1.txt'
Enter password:Copy the code

Remember that your database should have a table with the same name as your TXT file, or an error will be reported.

Use the figure below:

After the body

Mysql can go play other games. These knowledge has not been used at present, the back of the start to do the design, estimate I was Mysql play dead name. Happy first!!