For MySql database, table operations, addition, deletion, modification, query, and other SQL statements, mysqlsql

For MySql database, table operations, addition, deletion, modification, query, and other SQL statements, mysqlsql

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

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

(2) how to open and log out of the database?

-- Where-h is the host name, you can write the IP address 127.0.0.1, press enter, and prompt to enter the password.

mysql -h localhost -u root -p

-- Exit the database using exit, quit, or CTRL + C.

(3) modify the root password of mysql in dos and mysql

-- In dos, mysql is not required and the mysqladmin command is used. Of course, no extra points are required.

-- Where-u and-p can be connected together with the following characters, and the following statements must be connected together; otherwise, an error occurs.

mysqladmin -uroot -p123456 password root

-- After entering mysql, we use the following command to change the password. Of course, this is a statement in the database and requires a semicolon. The second row is the returned result. Of course, directly enter the following command, but modify the password of the user that is logged in.

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

(4) Create and delete databases and precautions

-- You need to specify a character set. If this parameter is not specified, the default value is gbk. (the default value may vary depending on the individual installation, and some may be latin1 ). Generally, create database if not exists newDatabaseName CHARSET utf8 is used;

-- Delete the DATABASE statement drop database if exists newDatabaseName;

-- If you do not want to specify the character set every time you create a database, you can set the mysql configuration file, which is generally a my. ini file under C: \ Program Files \ phpStudy \ MySQL. Modify gbk to utf8 using the following statement:

default-character-set=gbkcharacter-set-server=gbk

-- Of course, you can also modify the database character set by using commands in mysql. Use alert database newDatabaseName default character set utf8;

-- The query DATABASE statement is show databases. To view the information of a DATABASE, use show create database newDatabaseName ).

-- Use the USE command to use this database (that is, to access this database), that is, USE newDatabaseName;

(5) Table considerations

-- Display all tables under the database and use show tables FROM newDatabaseName. If you have used the USE command to enter the table, you can omit FROM and directly SHOW tables;

-- Describe is used to display the specific information of a table, which is very common: describe secondtable; of course, we can also use show create table secondtable; but the display format

-- The statement for creating a table is as follows. The main fields are in brackets and each field can be separated by a comma.

create table if not exists secondtable(id int not null auto_increment primary key,title varchar(50) not null,content text null);

-- DROP secondtable FROM newDatabaseName;

(6) database variable types

-- Integer. Our general integer int Is 4 bytes, of course, the range is 0 ~ 2 ^ 32-1. But there is also tinyint (1 byte, if the symbol is-128 ~ 127, the unsigned value is 0 ~ 255), and smallint is 2 bytes, mediumint is 3 bytes, bigint is 8 bytes, are both signed and unsigned.

-- Float is float and double. float can be precise to the last 7 digits after decimal places. float (6, 2) indicates the total number of digits 6, and the number of digits after the decimal point is 2. Double can be precise to the last 15 digits of a decimal number.

-- Character type: char and varchar. In char (M), M represents the number of characters (a Chinese character is a character), for example, char (50), but the char length range is 0 ~ 255, suitable for title. Varchar is longer in length and ranges from 0 ~ 65535. We usually use varchar, because if the total width of char (10) and varchar (10) are specified, but only five characters are saved, then char (10) the remaining five characters will be filled with spaces, which is a waste of time. The remaining characters of varchar (10) only occupy the length of one character to record, while the remaining four characters do not occupy, saving space.

-- Text. We generally use 2 bytes of text, that is, the length of 0-characters. The general article is sufficient. Tinytext is also a one-byte length, and mediumtext and longetext are three and four bytes respectively.

-- Time type. Data. However, we generally use the int type for storage, and then use the time and date functions in php to process the time data of this int type.

(7) add, delete, modify, and query statements

-- 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; there are too many, not to mention, as long as you remember several major keywords such as order by desc/asc for sorting, there is also limit, which of the following statements does B a start with (excluding this one) B is the number of outputs. It is generally limit.

-- In a query statement, an important keyword is like. For example, where title like "% hello %" indicates that the title contains "hello", while "%" indicates that there are multiple characters, there is also a _ character that represents any 1 character. Therefore, if it is title like "a %", it indicates whether there are many characters after. Here involved in the regular expression content, detailed information can be found to see: http://blog.micxp.com/index.php/archives/31/