Mysql database study entry (3)

Three. Mysql common sense (1) Field type 1.int [(m)] Normal size integer type 2. Double [(M, D)] [Zerofill] Normal size (Double Precision) Floating Point Digital Type 3.Date Date Type. Supported range is '1000-01-01' to '9999-12-31'. MySQL displays the DATE value in 'YYYY-MM-DD' format, but allows you to use strings or numbers to assume the value to the Date column 4.char (m) fixed length string type, when storage, always use spaces Fill the right to the specified length 5.blob text blob or text type, the maximum length is 65535 (2 ^ 16-1) characters. 6. Varchar becomes long string type, the most common type. (2) Basic operation 1: Display database mysql> show data; 2: currently selected database, mysql> select database (); ------------ | Database () | - ---------- | TEST | ------------ 3. Current database contains table information: mysql> show tables; ------- -------------- | Tables in test | -------------------- | MyTable1 | | MyTable2 | -------------------- 4. Get the table structure mysql> desc mytable1; -------- ------- ------ ------ ---- --------- ------- | Field | Type | NULL | Key | Default | Extra | --------- ----------- ---- ----- ------- - ------ | S1 | VARCHAR (20) | YES | | NULL | | -------- ------------ ---- - ----- --------- ----- 5. Creating a table is one of the most basic elements of the database, and the tables can be independent of each other. It can also be associated with each other. The basic syntax of the creation table is as follows: create Table Table_name (Column_name Datattype {Identity | Null | NOTATYPE}, ...) Where the parameter table_name and column_name must meet the Identifier requirements in the user database, the parameter datatype is a standard SQL Type or type provided by the user database. Users should use the Non-Null clause to enter data. Create Table also has some other options, such as creating a temporary table and using the SELECT clause from another table to form a new table. Also, the creation table is the primary key or index of the identifier that can be set with Primary Key, Key, Index. Pay attention to writing: List of full field list in a pair of parentheses. The field name is spaced apart with a comma. Add a space after the comma between fields. There is no comma after the last field name. All SQL statements are ended in semicolons;

Example: MySQL> Create Table Guest (Name Varchar (10), SEX VARCHAR (2), AGE INT (3), Career VARCHAR (10)); 6. Creating an index index is used to query the database. The general database has a variety of indexing schemes, each of which is fine at a particular query class. Index can accelerate the query process of the database. The basic syntax for creating an index is as follows: 10. Data update (1) of the table modifies a field again, pay attention to the syntax again. Text needs an additional number but numbers don't. MySQL> Update Table01 Set Field03 = 'New Info'SwhereSfield01 = 1; Query OK, 1 ROW Affected (0.00 sec) (2) Change multiple fields at a time, remember to separate with a comma between each updated field. mysql> update table01 set field04 = 19991022, field05 = 062218swheresfield01 = 1; Query OK, 1 row affected (0.00 sec) (3) update the plurality of data mysql> update table01 set field05 = 152901swheresfield04> 19990101; Query OK, 3 rows affected (0.00 sec) 11. Delete Data Mysql> Delete from Table01SwhereSfield01 = 3; Query OK, 1 ROW Affected (0.00 sec) 12. Import Database Table (1) Create. SQL File (2) Generate a library such as Auction.c: / mysql / bin> mysqladmin -u root -p Creat Auction, you will prompt your password and then successfully created. (3) Import an Auction.sql file C: mysql / bin> mysql -u root -p Auction Grant SELECT, INSERT, DELETE, CREATE, DROP ON *. * (Or Test. * / User. * / ..) to username @localhost identified by 'password'; such as: New one User accounts to access the database, need to do this: mysql> grant usage -> on test. * -> to testuser @ localhost; query ok, 0 rows affected (0.15 sec) then created a new user called: Testuser, This user can only connect to the database from localhost and connect to the TEST database. Next, we must specify this user to do what operations can perform: mysql> grant select, insert, delete, update -> on test. * -> to testuser @ localhost; query ok, 0 rows affected (0.00 sec) This action makes Testuser can perform SELECT, INSERT, and DELETE and UPDATE query operations in each Test database.