MySQL installation and use and Programming

1. Download the installation file from the Internet (here I download a MySQL binary distribution)
Http://dev.mysql.com/downloads/index.html

2. Upload the file to the host through securecrt
Copy mysql-3.23.58-pc-linux-i686.tar to C:/program files/securecrt/upload and execute rz in the command line to transfer the file to the server.
(Download and execute SZ filename, and transfer the file to the download folder)
Move to/usr/local/
Music mysql-3.23.58-pc-linux-i686.tar.gz/usr/local/

3. Check whether the software has been installed.
Rpm -- Query-A | grep 'my' (it has not been installed yet. If you find any, use rpm-E * to delete the installed package)

3. Extract
Gunzip | mysql-3.23.58-pc-linux-i686.tar.gz | tar xvf-
Create a symbolic connection for easy operation
Ln-s mysql-3.23.58-pc-linux-i686 MySQL
CD MySQL

"Bin"
This directory contains the client program and server. You should put the complete path of this directory
Add the PATH environment variable so that your shell can find the MySQL program correctly.
"Scripts"
This directory contains the mysql_install_db script, which is used to initialize server access permissions.

If you want to install it in another directory, edit the "bin/mysqlaccess" script and specify the installation path.
$ Mysql = "/usr/local/bin/MySQL"; # path to MySQL executable

4. Create a MySQL authorization table (only required if you have not installed MySQL before ):
Scripts/mysql_install_db

5. We plan to enable automatic MySQL startup
Add the following line to/etc/rc. Local:
/Bin/sh-C 'CD/usr/local/MySQL;./bin/safe_mysqld &'

6. Run the following command to start the MySQL server:
Bin/safe_mysqld &

Chown: 'mysql': invalid user
Starting mysqld daemon with databases from/usr/local/MySQL/Data
060427 06:54:59 mysqld ended

Dizzy, it should be a user permission Error

View users
Tail/etc/passwd

No MySQL user has been created
Useradd-M-o-r-D/var/lib/MySQL-S/bin/bash-c "MySQL Server"-U 27 MySQL

7. Execute again
Starting mysqld daemon with databases from/usr/local/MySQL/Data
Bin/safe_mysqld: Row 284: 2067 segmentation fault $ rule $ ledir/$ mysqld $ defaults -- basedir = $ my_basedir_version -- datadir = $ datadir $ user_option -- PID-file = $ pid_file -- skip-locking> $ err_log 2> & amp; 1
060427 07:09:26 mysqld ended

It seems like an environment variable problem. Looking at another tutorial, the steps seem a little different. The directory owner is not changed. Try again.
[Root @ Linux MySQL] # chown-r root.
[Root @ Linux MySQL] # chown-r Mysql Data
[Root @ Linux MySQL] # chgrp-r MySQL.

Then run bin/safe_mysqld &
Haha, finally started,

Close the database with support-files/MySQL. server stop or mysqladmin Shutdown

8. Modify the root MySQL password
It can be executed only when the database is enabled.
Bin/mysqladmin-u Root Password 'redhat'
Bin/mysqladmin-u root-H Linux Password 'redhat'

9. To make it easier to use mysqladmin, set the environment variable.
Edit/etc/profile so that all users have this setting and add
# Add MySQL path
Path = $ path:/usr/local/MySQL/bin
After logging out, you can log on again to apply the environment variable.

10. Testing after installation
View version
Mysqladmin-u root-p version
View Database
Mysqlshow-u root-P
+ ----------- +
| Databases |
+ ----------- +
| MySQL |
| Test |
+ ----------- +
Mysqlshow mysql-u root-P

Mysql-e "select host, DB, user from DB" mysql-u root-P

11. Log On with the user name and password
MySQL [-H host_name] [-u user_name] [-pyour_pass]
Example: mysql-u root-predhat
Mysql> select 1 + 1;
Mysql> exit
Exit. Try other users.
Su lhj
Mysql-u lhj
You can connect to the MySQL database by using the test database. You are not authorized to operate the MySQL database.
Mysql-u root-P
You can also log on to the database as a root user.

12. Start Using
Mysql> select version (), current_date;
Get version and date
Now () Current Time User () current user/C cancel command

Select * From tb_name order by price DESC limit 1
Limit 1 indicates that only the first row of the result is taken, which is equivalent to rownum of Oracle <= 1

Mysql> show databases;
Check several databases
Mysql> use test
Switch to the test database, so you do not need to enter the database name before the table name.
Mysql> show tables
View tables in the database

13. Create a user
First, create the database used by the user
Mysql> Create Database lhj;
Create an lhj account that can be logged on to any host and grant the lhj Library General Permissions
Mysql> grant select, insert, delete, drop, create, update on lhj. * To lhj @ '%' identified by 'lhj123 ';
At this time, the attempt to log on failed. Every time I thought it was an anonymous user, I was depressed. Find the cause of this situation. It is recommended that you specify the Host field in the User table instead of % (why do you say that in the tutorial ?).
Mysql> Update user set host = 'localhost' where user = 'lhj ';
Mysql> flush privileges;
Login With lhj again. Successful.
An error occurred while trying to use the alter and create indxe commands.
Mysql> Create Table t_lhj (ID int, name varchar (20), password varchar (20 ));
Mysql> alter table t_lhj add (timestamp date );
Mysql> Create index idx_lhj on t_lhj (ID );
Use root to log on and grant permissions
Mysql> grant index, alter on lhj. * To lhj;
Now, you can.
The table structure is the same as that in Oracle.
Mysql> DESC t_lhj;

14. General knowledge points required for database development
MySQL is a relatively simple database system, and some large databases do not support functions provided by MySQL (at least not provided before version 3.23 ). Here are the following considerations:
(1) The SELECT statement does not support the in (select * From tab) and not in (select * From tab) syntax. however, the syntax of in (, 3) and not in (, 3) is supported.
(2) Transaction processing is not supported. However, you can use the lock tables and unlock tables commands to prevent interference from other threads.
(3) stored procedures and triggers are not supported, but the new version may support stored procedures.
(4) Foreign keys and views are not supported, but the new version may support views.

MySQL supports the following column types:
M indicates the maximum display size. The max valid display size is 255.
D is applicable to the floating point type and specifies the number of digits following the decimal point. The maximum possible value is 30, but should not be greater than the M-2.
Number: [unsigned] or [zerofill] can be taken after the type. if zerofill is included, it is automatically declared as unsigned and 0 is automatically filled in the vacant space. For example, tinyint type 1 will change to 001. in addition, if the value exceeds the supported range of the type, the value greater than the maximum value is used, and the value smaller than the minimum value is used.
The signed range of tinyint [(m)] is-128 to 127, and the unsigned range is 0 to 255.
Smallint [(m)] The signed range is-32768 to 32767, And the unsigned range is 0 to 65535.
Mediumint [(m)] has a signed range of-8388608 to 8388607, And the unsigned range is 0 to 16777215.
Int [(m)] The signed range is-2147483648 to 2147483647, And the unsigned range is 0 to 4294967295.
Integer [(m)] is a synonym for int.
The signed range of bigint [(m)] is-9223372036854775808 to 9223372036854775807, And the unsigned range is 0 to 18446744073709551615.
Note that all arithmetic operations are completed with signed bigint or double values. Therefore, if the number of operations or results exceeds the range, unmeasurable results are obtained.
Float [(m, d)] cannot be unsigned. The allowed values are-3.402823466e + 38 to-1.175494351e-38,0 and 1.175494351e-38 to 3.402823466e + 38. M indicates the display width, and D indicates the decimal digits. Float without parameters or a parameter with <24 represents a single precision floating point number.
Double [(m, d)] cannot be unsigned. The allowed values are-1.7976931348623157e + 308 to-2.225074255072014e-308, 0, and 2.225074255072014e-308 to 1.7976931348623157e + 308. M indicates the display width, and D indicates the number of decimal places. A double or float (x) (25 <= x <= 53) without a parameter represents a double-precision floating point number.
Double Precision [(m, d)] and real [(m, d)] are synonyms of double.
Decimal [(M [, d])] a uncompressed floating point number. It cannot be unsigned. The behavior is like a char column: "uncompress" means that the number is stored as a string, and each bit of the value uses one character. Decimal point. For negative numbers, the "-" symbol is not calculated in M. If D is 0, there will be no decimal point or decimal part. The maximum range of a decimal value is the same as that of a double value. However, for a given decimal column, the actual range can be limited through the selection of M and D. If D is omitted, it is set to 0. If M is saved, it is set to 10.
Numeric (M, d) is a synonym for decimal.

Time and date:
Date is a date. The supported range is '2017-01-01 'to '2017-12-31 '. MySQL displays the date value in 'yyyy-MM-DD 'format, but allows you to assign the value to the date column using strings or numbers. You can assign values to current_date or now.
Datetime is a combination of date and time. The supported range is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '. MySQL displays datetime values in 'yyyy-MM-DD hh: mm: ss' format, but allows you to assign values to datetime columns using strings or numbers. Now () can be used to assign the current time value. If current_date is used, the time part is all 0.
Time is a time. The value range is '-838: 59: 59' to '2014: 59: 59 '. MySQL displays the time value in 'hh: mm: ss' format, but allows you to assign the value to the Time column using a string or number.
Year [(2 | 4)] year in the format of a 2 or 4-digit number (4 digits by default ). The allowed values are 1901 to 2155, and 0000 (4-digit year format), if you use 2 bits, 1970-2069 (70-69 ).
Timestamp [(m)] is a timestamp. The value range is '2017-01-01 00:00:00 'to a certain time on January 1, 1970. MySQL uses yyyymmddhhmmss, yymmddhhmmss, yyyymmdd, or yymmdd to display the timestamp value, depending on whether M is 14 (or omitted), 12, 8, or 6, however, you can use strings or numbers to assign values to the timestamp column. A timestamp column is useful for recording the date and time of an insert or update operation, because if you do not assign a value to it yourself, it is automatically set to the date and time of the most recent operation. You can assign it a null value to set it to the current date and time. This field is automatically updated when this row is updated.

Character Type:
Char (m) [binary] is a fixed-length string. When stored, it always fills the Right to the specified length with spaces. The range of M is 1 ~ It can contain 255 characters. When the value is retrieved, the tail of the space is deleted. Char values are sorted and compared in a case that is not distinguished by the default Character Set, unless binary keywords are given. National char (short form nchar) is an ansi SQL method to define char columns should use the default character set. This is the default value of MySQL. Char is an abbreviation of character.
[National] varchar (m) [binary] a variable-length string. Note: When the value is stored, spaces at the end are deleted (different from the ansi SQL Specification ). The range of M is 1 ~ It can contain 255 characters. Varchar values are sorted and compared based on the default character set in uppercase/lowercase, unless binary keyword values are given.
Tinyblob or tinytext is a blob or text column. The maximum length is 255 (2 ^ 8-1) characters.
Blob or text: A blob or text column with a maximum length of 65535 (2 ^ 16-1) characters.
Mediumblob or mediumtext is a blob or text column with a maximum length of 16777215 (2 ^ 24-1) characters.
Longblob or longtext is a blob or text column with a maximum length of 4294967295 (2 ^ 32-1) characters.
Note: The difference between text and blob is that text is case-insensitive while blob is case-insensitive.
Enum ('value1 ', 'value2',...) enumeration. A String object with only one value. This value type is set to 'value1 ', 'value2',..., or null. An Enum can have a maximum of 65535 different values.
Set ('value1 ', 'value2',...) is a set. A String object with zero or multiple values, each of which must be selected from the Value List 'value1 ', 'value2. A set can have up to 64 members.

15. Use heap table for advanced applications
A heap table is a MySQL database table that uses a hashed index and is stored in memory. This makes them faster, but if MySQL crashes, you will lose all the stored data. Heap is available as a temporary table!
Create Table Test type = heap select IP, sum (downloads) as down
From log_table group by IP;
Select count (IP), AVG (down) from test;
Drop table test;

Note:
You should always specify max_rows in the create statement to ensure that you intentionally do not use all the memory.
The index can only be used with = and <=> (but soon ).
The heap table uses a fixed record length format.
Heap does not support blob/Text columns.
Heap does not support the auto_increment column.
Heap does not support indexes on a null column.
You can have a non-unique key in a heap table (this is generally not the case for a hash table ).
Heap tables are shared among all customers (just like any other table ).
Data in the heap table is allocated in small blocks. The table is 100% dynamic (during insertion), with no overflow zone or extra key space required. The deleted row is placed in a chain table and used again when you insert new data into the table.
To release the memory, you should execute Delete from heap_table or drop table heap_table.
To ensure that you do not accidentally do stupid things, you cannot create a heap table larger than max_heap_table_size.

16. use MySQL C API
Start to enter the topic and use C to connect to the database. You should ensure the compilation is successful before performing specific operations. Therefore, use a simple program for verification.
File mysqlc. c
# I nclude "mysql. H"
# I nclude <stdio. h>
Int main ()
{
MySQL * my_connection;
Int IRES;

My_connection = mysql_init (null );
Return 1;
}

[Root @ Linux testc] # GCC mysqlc. C-o mysqlc
Mysqlc. C: 1: 19: mysql. h: no such file or directory
The header file cannot be found. Add the header file path.
[Root @ Linux testc] # GCC mysqlc. C-I/usr/local/MySQL/include/-O mysqlc
: Undefined reference to 'mysql _ init'
The function definition is not found. It should be not added to the library file.
[Root @ Linux testc] # GCC mysqlc. C-I/usr/local/MySQL/include/-L/usr/local/MySQL/lib/-lmysqlclient-O mysqlc
: Undefined reference to 'compus'
Dizzy or wrong. It seems that there is no decompressed function definition. See some examples on the Internet with the-LZ parameter. Try it.
Root @ Linux testc] # GCC mysqlc. C-I/usr/local/MySQL/include/-L/usr/local/MySQL/lib-lmysqlclient-LZ-O mysqlc
Oh, this compilation is successful.

The basic operation has been completed. Below is an example of using C to operate mysql. See the file
Appendix 1-basic operation of mysqlc.txt