Some SQL statement considerations for Database, table operations, and additions and deletions in MySQL

(1) How to copy and paste content in cmd?

Reference: http://jingyan.baidu.com/article/93f9803fd3a4dde0e46f55f5.html

(2) How to open Enter and exit database?

--------H is the hostname, can write IP address 127.0.0.1, enter, prompt for password.

Mysql-h Localhost-u Root-p

--Exit the database is exit or quit or CTRL + C.

(3) Change the root password of MySQL in dos and MySQL

-In DOS, you don't need to go to MySQL, with the mysqladmin command, and of course you don't need a semicolon.

--------------and-p can be concatenated with the following characters, and must also be connected by entering the statement below, otherwise an error

mysqladmin-uroot-p123456 Password root

--After entering MySQL, we change the password with the command in the first line below, of course, this is the statement in the database, the semicolon is required. The second line is the result of the return. Of course, simply enter the following command, just modify the password of the user who is logged in.

Mysql> set Password=password (' root '); Query OK, 0 rows affected (0.07 sec)

(4) Create and delete databases and considerations

--You need to specify the character set, if not specified, the default is the GBK type (depending on the individual installation, this default may be different, some latin1). General use Create DATABASE IF not EXISTS newdatabasename CHARSET UTF8;

--Delete Database statement drop DB IF EXISTS newdatabasename;

--If you do not want to specify a character set every time you create a database, you can set up a MySQL configuration file, typically a my.ini under C:\Program files\phpstudy\mysql. With the following statement, the GBK can be modified to UTF8:

Default-character-set=gbkcharacter-set-server=gbk

--of course, modifying the database character set can also be modified with commands in MySQL. Use alert DATABASE newdatabasename DEFAULT CHARACTER SET UTF8;

--Query The database statement is show DATABASES; View the information of a database with show CREATE database newdatabasename; (why add a create does not understand, do not add error).

Use this database (that is, access to the database) using the command, which is used newdatabasename;

(5) Notes on the table

--Show all tables below the database, with show tables from Newdatabasename, and if you have entered the table with the use command, you can omit from, direct show tables;

--and the specific information that displays a table is used describe, which is very common: describe secondtable; Of course, we can also use show create TABLE secondtable, but that display format

--the statement that creates the table is as follows, the main field is enclosed in parentheses, and there is a comma between the fields.

CREATE table if not EXISTS secondtable (ID int. NOT NULL auto_increment primary key,title varchar (a) Not null,content text NULL);

-delete Table is drop secondtable from Newdatabasename;

(6) Database variable type

--Integral type. Our general int is 4 bytes, of course the range is 0~2^32-1. But there are also tinyint (1 bytes, if the symbol is -128~127, unsigned is 0~255), and smallint is 2 bytes, Mediumint is 3 bytes, bigint is 8 bytes, all are signed and unsigned.

-Float is float and double,float can be accurate to 7 bits after decimal, float (6,2) represents total number of digits 6, the number of digits after the decimal point is 2. A double can be accurate to 15 digits after the decimal.

--character type is char and varchar. CHAR (m) inside M is the number of characters to give (a character is a char), such as chars (50), but the length of the char range is 0~255, suitable for heading title. The varchar length is longer and the range is 0~65535. We often use varchar because if you specify a total width of char (10) and varchar (10), but only 5 characters are actually saved, the remaining 5 characters of char (10) are filled with spaces and wasted. The remaining characters of varchar (10) only occupy one character to record the character length information, while the other 4 characters do not occupy and save space.

--Text type. We generally use the text is 2 bytes, that is, 0-65535 characters long, general article enough. There are also tinytext that are 1 bytes long, Mediumtext and Longetext are 3 and 4 byte lengths respectively.

--Time type. Data and the like. But when we store it, we usually use the int type, and then we use the time and date functions in PHP to deal with this type of int data.

(7) Increase and deletion of the search statement

--INSERT statement: INSERT INTO newtable (title) value ("second title");

--DELETE statement: Delete from newtable where id=1;

--Modify statement: Update newtable set title= "new title" where id=2;

--Query statement: SELECT * from newtable; this is too much, not in detail, just remember a few key keywords such as order by DESC/ASC sorting, and the limit, a, and a A is the beginning (excluding this) B is the output of how many bars. It is usually limit 0, 10.

--In the query statement, there is an important keyword is like, such as where the title "%hello%", that the title contains Hello, while the% represents a person more than one character, there is a _ for any 1 characters. So if it's a title like "a%", it means that there are not many characters after the beginning of a. Here is involved in the regular expression of the content, detailed information can be looked at: http://blog.micxp.com/index.php/archives/31/

Some SQL statement considerations for Database, table operations, and additions and deletions in MySQL