information about redwork designs

Given clause{article} is not a full management{manual} on administration of the mysql-server, the primary, most necessary data for newbies here are more likely submitted. And the server for OS windows though the majority of commands are fair and for *nix-systems will be considered{examined} therefore. In job the console since the commands entered into the consoles, it is possible to transfer easily is used basically, we admit{allow}, in the web-interface (for example, in php for this purpose exist special functions).

For what the database is necessary? Relational databases represent not that other as some tables with recordings are simple. SUBD creations of new tables, removals{distances} unnecessary, inserts in tables of new recordings, changes of recordings and, certainly, transaction processings, and also some others, less often used functions are intended for management of these tables, i.e. Actually, in fact and databases are necessary operatively to give out the information, and in the certain order. SUBD there is a great variety (even all known microsoft access). But on principles of job we shall not stop. Perhaps, the most widespread SUBD in the Internet is mysql which because of the speed and convenience in use has received a wide circulation. Yes, and the large value has that she completely free-of-charge, i.e. It is distributed on gnu gpl (in general it means, that you can download initial codes and otkompilirovat` them at yourselves or in general make SUBD " under yourself " if, certainly, be competent enough programmer. To tell the truth, if it so, given clause{article} not for vas:-)).

So, in present it is required to " laboratory job ": a computer, actually @ SUBD mysql server, a shortcut of the console near at hand (actually, he always should be near at hand: though convenience of job under cmd and considerably concedes to job in konsol`ke in linux, but many things are possible to make of the console faster, than to climb through 3-5 menus behind the necessary option). Many things will be done{made} through the console. Certainly, there is a set of the "juzero-guided" programs intended for management by the server, but to consider{examine} any one it is not meaningful. If you will know the basic commands, to use mysql in web-applications begins not simply easily, and is very simple!

Installation mysql. we shall establish and use 3.23.38 mysql-server. What for in general it is necessary sql? At first sight, for creation of more - less decent site it is not required special navorotov (what the some people count php and sql), but even simply the broad gully of the counter after pair - other thousand "revolutions" to you to look it will not be interesting. Ezheli to a broad gully of the counter to write only time - date, ip and a name of a computer of the visitor a broad gully in some thousand lines not only to extend each time from nadoest server, but also operatively to see, who was, we admit{allow}, in the night from Thursday to Friday on yours, certainly, a remarkable site, it will be very difficult. And a forum to write only, we admit{allow}, on php not simply enough it is difficult, but also "it is "unpleasant".

So, installation. As such installation does not demand especially big dances with bubnom. We press ehnnoe quantity{amount} of times on a button next - and we enjoy. A pair of words about how the data in mysql are stored{kept}. By default each database is stored{kept} in separate papochke which name coincides with the name of this database which, in turn, are stored{kept} (besides, by default) in a folder data directories in which the server is established. To change defoltovoe a site of the data it is possible as follows. For example, you want to create a database which will be stored{kept}, say, in e:/data/database_name where database_name - a name of a database. Then you create a file database_name.sym in a directory data and write down in him only one line which contains a way to a site of your database: " e:/data/database_name ", also save. If to have a look in folders in which the data you will see there 3 files are stored{kept}. The data are stored{kept} in the "optimized" kind, i.e. not as a plain text - so simply them to see{overlook} it will not be possible. So, three files: with expansions .frm - in him the information on the tables contained in a DB.myd - actually the data and .myi - the index data is stored{kept}. Now we shall engage in safety. The data on users mysql stores{keeps} in a special database which is called mysql (originally, nothing skazhesh`:-)). In her of 5 tables in which logins and passwords are stored{kept} in the ciphered kind, and also attributes of access to the table. These tables are called:

In tables columns_priv, tables_priv privileges of users for access are stored{kept}, accordingly, to columns and tables, in db - privileges for access to databases, in host - the information on computers from which are connected juzveri to mysql, and, at last, in the table user is stored{kept} the information on users.

More particularies can be received from a manual which is in an adjusting package (after installation he is in mysql/docs). It is almost "two-meter" document in which everything is written practically that it is necessary to know at the beginning, and not only on pervykh:-). Us interests how to replace juzverja with a login root (those who is familiar with *nux, know, what is it such, and for the others I explain: the superuser with the unlimited rights) and also to make juzverja from under which name we shall work with the server. In an ideal such user should be only one. Other users should be with the limited rights (a major principle of administration - that is not authorized it is forbidden). The matter is that by default the superuser has no password!! And if you, for example, will bring any classified information in the database, see, remove, change them can kazhdyj:-). So, we have established the server, we begin administration. We find a directory in which we have established our server (by default it c:mysql). We find a directory c:mysqlbin and we see set "ehkzeshnikov". For the beginning it is required to us mysql.exe. We start. Yes, options of start from the console can be seen, having written something like c:mysqlbinmysql.exe-h then the information{inquiry} will be deduced{removed}. But while they will not be necessary for us. All sql-commands are entered in the command line and should come to the end ";" or "g". To use a database, it is necessary to choose her all over again. For this purpose there is use command. To us it is necessary for a DB "mysql": " mysql> use mysql; ". To see, what databases are available on the server, there is a command " mysql> show databases; ". Viewing of available tables as is simple, as well as viewing of available DB: " mysql> show tables from databasename; ", where databasename - a name of a database. We shall see, who at us is registered in juzverjakh our server. We write " mysql> select user, host, password from user; ", Then we shall receive the list juzverej with names of computers from which they can be connected to the server. Before you will start to experiment access rights to databases, I advise you to copy a folder mysql (with files of a corresponding database) somewhere in the remote place that if something will fail, it would be possible "to be rolled away".

Select command - she from most chastoispol`zuemykh commands. With its{her} help we can show, what data of us interest, and to ask to display her . Syntax of her is those: " select interesujuhhie_stolbcy_v_tablice from nazvanie_tablicy <options>; " Instead of "interesujuhhie_stolbcy_v_tablice" it is possible to put "*" then values of all columns which are present at the table will be given to us. More often instead of "<parameters>" write one more keyword - where. Syntax is those: " where nazvanie_kolonki_tablicy ='znachenie ' ".

So, now we write: " mysql> upda-te user set password=password (' newpasword ') where user ='root '; ", where newpassword - the new password for the superuser. To a word, thus it is possible to change the password for any juzverja. I shall a little explain, that these commands mean. Function password ('string') ciphers a line on the certain algorithm, and update command changes value of recording where (where) in the table user. To a word, all passwords in mysql are stored{kept} in the ciphered kind. Now we need to forbid for the superuser to be connected from the removed computers. For this purpose we delete lines with it is familiar "%". We write " de-lete from user where host = '%'; ". Delete command has similar syntax with select command: " select from nazvanie_tablicy where nazvanie_kolonki_tablicy ='znachenie '; " . I hope, understandably, that commands are necessary for typing{collecting} without inverted commas. Yes, last command we also have removed also "empty" juzverja. Now at us only two users with the unlimited rights: root and mentalzavr which password, to a word, you should set at the first loading winmysqladmin (now he should hang in tree a such traffic light, green light on which means normal job of the server, yellow - a transitive status, and red - ostanov the server). That our changes have come into force, it is necessary either to restart the server, or to give the command " flush privileges; " . The matter is that the majority of servers of the most various purpose read the config where the majority of their adjustments is stored{kept}, only once at loading. And mysql server reads a database with parameters of access to databases at loading or compulsorily after the special command. To restart the server it is possible from the control panel if you work in windows nt-series to which concern windows xp, 2000 and itself nt:-), or from the same notorious command line. First we shall see, as our service is called. For this purpose we shall use the so-called expanded console wmic (wmi command-line). If you before did not use her , the system to you will establish her (xp). It is very powerful tool. But not a subject given stat`i:-). So, we write " wmic:rootcli> service list ". We see the name of service "mysql". We leave from wmic: " wmic:rootcli> exit ". Now we stop service " c:> net stop mysql " also we shall start again: " c:> net stop mysql ". You ask, what for I tell all this? One of the reasons - in the same way it is possible to stop and start any other services. And the second... Well, present, that you have mysql server somewhere in a local area network, and your computer is in the other building, and it is necessary for you urgently perekonfigurirovat` it . To run to the server and alive it to adjust? Well, you as want, and me personally laziness. Konnektimsja to the server and far off adminim:-). By the way, programmka mysql.exe which we used for connection to the server (namely it we and did{made}), can work and with the removed server. It is easy for seeing, having started mysql.exe as follows: " c:> mysql-h ip_adresss ", where instead of "ip_address" - or a name of a computer to which we are connected, or it ip-adres:-). Well, we have restarted the server (or have simply written " flush privileges; "). Now, ezheli all is correct, you cannot be connected to the server, simply having started mysql.exe. It is necessary to enter the password. It is done{made} thus: " c:> mysql-u root-p ". You will ask to enter the password on root which we with you set above. If it's OK, the invitation " mysql> ", as well as will appear earlier. Now we shall try to create still juzverej. It is done{made} so. We shall create three juzverej with the different rights:

mysql> grant all privileges on *.* to ferst@localhost

identified by ' some_pass ' with grant option;

mysql> grant all privileges on *.* to ferst "%"

identified by ' some_pass ' with grant option;

mysql> grant reload, process on *.* to admin@localhost;

mysql> grant usage on *.* to doom@localhost;

By the way, if you will give the command " mysql> status ", the server will give out you a quantity of the helpful information. So, we have created three juzverej. The first - ferst - receives all rights and can be connected to the server as from a local computer, and far off, the password for access to him is appointed sam_pass. The second juzver` - admin - receives rights on reload and refresh, but can be connected only from a local computer (that means a badge of "%"), the password is not appointed to an input{entrance}. The third juzver` with a name doom can be connected from a local computer, and he is created, so to say, "deprived of civil rights", i.e. all global privileges are not given to him. The same could be made in another way. For example, so:

mysql> insert into user values (' localhost ',' ferst ', password (' some_pass'),

' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ');

mysql> insert into user values (' % ',' ferst ', password (' some_pass'),

' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ',' y ');

mysql> insert into user set host ='localhost ', user ='admin ',

reload_priv ='y ', process_priv = 'y';

mysql> insert into user (host, user, password)

values (' localhost ',' doom ', ");

mysql> flush privileges;

Probably, it is necessary to explain, what does it mean. Insert command inserts a line into the table which name follows after a word into, in brackets names stolbcov, available in these tables are given. After values values which will be worn out in corresponding columns follow. Values are necessary for quoting. Last command updates access rights. The letter ' y ' means inclusion of the corresponding privilege, ' n ' - accordingly deenergizing. The table user has the following stolbcy (are given in the same order, as in a reality):

I think, that the given table does not require explanatories. Names of columns speak for themselves (if you, certainly, know English as I - at a level of 5-th class though by:-)). It is natural, that, ezheli in the previous commands of the letter ' y ' to replace on ' n ', the corresponding user of the corresponding right and not poluchit:-). Now we shall understand, how it is possible to change rights juzverej at connection to a database. For this purpose there is grant command.

mysql> grant select, insert, update, delete, create, drop

on bankaccount.*

to custom@localhost

identified by ' stupid ';

mysql> grant select, insert, update, delete, create, drop

on expenses.*

to custom@whitehouse.gov

identified by ' stupid ';

mysql> grant select, insert, update, delete, create, drop

on customer.*

to custom ' % '

identified by ' stupid ';

I shall explain, that do{make} these commands. After grant command the list of privileges which it is necessary to give juzverju custom follows. After a word on there is a name of a database, after a point it is possible to specify the concrete table in this database, and the asterisk means "any". And after a word to goes imja_juzverja@imja_ a computer from which this juzver` can be connected. The sign on percent{interests} means "ljuboj_komp`juter". After words identified by it is necessary to specify so-called the password which will be required at juzverja at an input{entrance}. In this case it ' stupid '. Certainly, the similar result can be achieved and direct updating of the table user. For example, so:

mysql> insert into user (host, user, password)

values (' localhost ',' custom ', password (' stupid '));

I think, understandably, that the given command does{makes}. She inserts into the table user recording with values in stolbcakh which are written down after a word values. Corresponding updating of this command it is possible to reach{achieve} the same results, as use of the commands stated above. Also do not forget after will make changes, to give flush privileges command;. Now, after you have successfully created users, we pass to more interesting part - actually to creation of a database. For creation of a database it is necessary from the same directory, as the program mysql.exe, to start the program mysqladmin.exe. If you start this program simply without options, she will give out to you the list of parameters of start. Actually, and all use of this program is reduced to its{her} start with corresponding options. So, us creation and removal{distance} of databases, and also connection through a login and the password to the removed server (laziness - truly the engine of progress first of all interests!) . We are connected to removed hostname server with use of a login root the password which us the ambassador will ask.

" c:mysqlbin> mysqladmin-h hostname-u root-p options ". Instead of options it is necessary to write actually that we want to make. Creation of a database with a name database_name " c:mysqlbin> mysqladmin-h mentalzavr-u mentalzavr-p create database_name ". Removal{distance} of a database database_name after acknowledgement{confirmation} and input of the password: " c:mysqlbin> mysqladmin-h mentalzavr-u mentalzavr-p drop database_name ". Exist as well other parameters of start on which we shall especially not stop though they are not less important. You receive data on them, as I to you have already said, having started mysqladmin without options. So, we have created a database. Now it is necessary to create tables in which, actually, and the data will be stored{kept}. Any table should concern to any database. Therefore we start mysql.exe and we choose just the created database " mysql> use database_name " For creation of the table there is create command. Syntax is those: " mysql> create table table_name (name int (3), name char (15)); ", where table_name - a name of the table after which the name of a column and his type are specified in brackets through a point consistently. Certainly, stolbcov can be and more, than two. Figures in stolbcakh show the size of value in stolbce. Well, and to remove the table, accordingly, it is necessary to write: " mysql> drop table table_name " Tables in a database can be a little. Now in brief we shall stop on types of the data in the table. It is a lot of them, and consequently I think at the beginning (and given clause{article} is intended for granting only INITIAL data on administration SUBD mysql, and in koem time is not a full management{manual}) to be limited underwritten. Perhaps, most often a meeting field in tables is the serial number. It is rather convenient. So, in mysql there is an opportunity to create the table with stolbcom which value will change on unit with each new recording. Now how it is done{made}. As it is usual, write to consoles:

mysql> use test;

mysql> create table table_name (no-mer int auto_increment, text char (10), key (nomer));

The given commands choose as current a database test and create the table with a name table_name with two columns. The first column has attribute extra "auto_increment". It means, that at addition of recording in this table value (number) in it stolbce will automatically increase for unit. To see properties of columns of the table it is possible as follows (having given the corresponding command): " mysql> desc table_name; ".

So, now we shall touch other types of the data. Numerical. Them is a plenty, the basic are (in my opinion) int and float. Those who is familiar even a little with the programming language With ++, will understand at once, that first of them is an integer type of the data. float - the type of the data used for storage of numbers with the floating point. Types of the data of date and time. Some other concern to them data, time, year, and also. A format of a data storage in type data - "yyyy-mm-dd", in type time - "hh:mm:ss", in type year - "yyyy". And practically the basic type of the data is char. To text types also concern text (65535 symbols maximum) and some other.

And in summary I would like to give some useful commands which will help you to use the console more effectively. All advice{councils} are valid for cmd.exe - a command processor windows. For the beginning it is necessary for you to switch on use of the mouse in consoles. For this purpose click the right button of the mouse on heading of an open window of the console and choose Properties. And on the first bookmark put ticks opposite to items{points} Allocation by the mouse and the Fast insert, and also opposite To reject recurrences. Now you can use in the console all of charm of job with a clipboard. Allocation by the mouse occurs as usually, copying in a clipboard - by pressing the right button. The insert is made in the location of the cursor also by the right button. Pair words about automation of process of primary entering of the data in tables. If you create a database from zero any problems practically do not arise (well, short of a plenty of organizational job and is direct kodinga which process includes creation even web-interfejsa:-). Certainly, pair - other of lines can be brought and in the console. Well, and ezheli you have any text file with recordings, the data from which need to be translated in a DB mysql? Here to be at war only a clipboard with infinite recurrence of actions "copying - insert" the workaholic can only well very big …. Well, and we as clever people can act{arrive} easier (the truth, all in this world be relative). The matter is that it is possible as argument in the command " mysql>. file_name " to pass a name of a file "file_name", and this file will be executed. What he itself(himself) represents? It is simply consecutive list of sql-commands. Now we need to generate only such file, that in each separately taken case it is done{made} differently - here it is necessary to look on conditions. Certainly, it is possible to go further away and to write the program for automatic entering the data to a DB. But for simple problems{tasks} it is possible simply even a text file with the commands written to him to copy in the buffer and will insert into the invitation mysql. "Inputs" to be perceived as a sign on end of the command. Only do not overlook to put at the end of each line ";", In fact it is possible to enter into mysql.exe commands and in a multilower case mode, therefore there can be overlays. To tell the truth, there is also an alternative way. If you have text file with recordings as the table, and they in him are divided{shared} by means of points and signs on the termination{ending} of a line or any other symbols (then it is necessary to make changes to parameters of the following command, having replaced a point and "n" on corresponding signs), it is possible to use the command " mysql> load data infile "data.txt" into table my_table fields terminated by ', ' lines terminated by ' n '; " . Result of the given command the table should be. In a file of a field should be divided{shared} by points, and each recording - to begin with a new line. It is necessary to remember also such thing, as the coding. If all given to bring through the console they will be stored{kept} in the dos-coding, and at a conclusion of such data - for example if in html-page use php - you will see "abrakadabru". That it did not occur, wear out the data only in one coding or recode them with the help of special functions (for php it is function $text_after=convert_cyr_string ($text, "w", "d") where last two parameters specify accordingly that the initial coding - windows, and final after code conversion - dos). And at last. It is much more pleasant to work with the console so all unloved, ezheli to make something like it: " c:> title linux ". On ignorant people operates enough svoeobrazno:-).