Database--mysql

1, download, install, configure the environment

2. Database operation

3, user rights, authorization management

4. Data table operation

5, data row operation

1, download, installation, configuration environment (slightly)

2. Database operation

2.1. Display Database

SHOW DATABASES;

Default database:
MySQL-User rights-related data
Test-for user testing data
Information_schema-mysql itself schema-related data

2.2. Create a database

# utf-8create database name DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; # gbkcreate database name DEFAULT CHARACTER SET GBK COLLATE gbk_chinese_ci;

2.3. Using the database

Use db_name;

Displays all tables in the currently used database: show TABLES;

3, user management, authorization management

3.1. User Management

Create user ' username '    @ ' IP address ' identified by ' password ', delete user ' username '    @ ' IP address ', modify user    rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';; Modify password    set password for ' username ' @ ' IP address ' = password (' new password ')  PS: User rights related data is stored in the user table in the MySQL database, so it can also be manipulated directly (not recommended)

3.2. Authorization Management

Show grants for ' user ' @ ' IP address '                  --View permissions grant  permissions on database. Table to   ' user ' @ ' IP address '      --Authorize revoke permissions on database. Table from ' user ' @ ' IP ground Address '      --Cancel permissions

Forgot your password?

# Start the mysqld--skip-grant-tables# client mysql-u root-p# Modify the user name Password update mysql.user set Authentication_string=password (' 666 ') where user= ' root '; flush privileges; Forget password

　　

4. Data table operation

1. Create a table

CREATE TABLE table name (    whether the column name  type  can be empty,    whether the column name  type  can be empty) Engine=innodb DEFAULT Charset=utf8

The parameters are as follows:

 Nullable, NULL for NULL, non-string NOT null-non-nullable-nullable----------------------------------------------- -------------default value, you can specify a default value when you create a column, and automatically add a default value when inserting data if you are not actively setting the CREATE TABLE tb1 (nid int not null Defalut 2 , num int not null)---------------------------------------------------------self-increment, if you set the self-increment column for a column, when inserting data                Without setting this column, the default will be self-increment (only one self-increment in the table) CREATE TABLE tb1 (nid int not NULL auto_increment primary key,                 num int null) or CREATE TABLE tb1 (nid int not null auto_increment,                 num int NULL, index (NID)) Note: 1, for self-increment columns, must be an index (with a primary key).                     2, for self-increment can be set step and start value Show session variables like ' auto_inc% ';                     Set session auto_increment_increment=2;                     Set session auto_increment_offset=10;               SHWO Global variables like ' auto_inc% ';      Set global auto_increment_increment=2; Set global auto_increment_offset=10;------------------------------------------------------primary key, a special unique index that does not allow null values,            If the primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique.            CREATE TABLE TB1 (nid int not NULL auto_increment primary key, num int null) or CREATE TABLE tb1 (nid int not null, num int NOT NULL, PR Imary key (Nid,num))---------------------------------------------------------foreign key, a special index, only the specified content Crea            T table color (NID int not null primary key, name char (+) NOT NULL) CREATE table fruit (nid int not null primary key, SMT char (+) NULL, Color_i d int not NULL, constraint FK_CC foreign key (color_id) references color (NID)

2. Delete a table

DROP table Name

3. Clear the Table

Delete from table name TRUNCATE TABLE table name deletes the sorting of the index (that is, a thorough, clean delete)

4, modify the table

Add Column: ALTER TABLE table name add column name type Delete column: ALTER TABLE table name drop Column name Modify column:        ALTER TABLE table name modify column name type;  --type ALTER TABLE name change original column name        new column name type;--column name, type  add primary key:        ALTER TABLE table name add primary key (column name); Delete PRIMARY key:        alter TABL e table Name drop primary key;        ALTER TABLE name  modify  column name int, drop primary key;  Add foreign key: ALTER TABLE from TABLE ADD constraint foreign key name (shape: fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key field); Delete foreign key: ALTER TABLE name drop for Eign key FOREIGN Key name  modifies default value: ALTER TABLE TESTALTER_TBL alter I SET default 1000; Delete defaults: ALTER TABLE TESTALTER_TBL alter I DROP D Efault;

5. Basic data type

MySQL data types are broadly divided into: numeric, time, and string

Bit[(m)] bits (101001), M represents the length of bits (1-64), default M=1 tinyint[(m)] [unsigned] [zerofill] small integer, data type is used to hold a            Range of integers: Signed:-128 ~ 127.        Unsigned: ~ 255 Special: MySQL has no boolean value and is constructed using tinyint (1). int[(M)][unsigned][zerofill] integer, data type is used to hold some range of integer numeric ranges: Signed: 2147483648 ~ 21474 83647 unsigned: ~ 4294967295 Special: M in the integer type is only for display and has no limit on storage range.                For example: Int (5), when inserting data 2 o'clock, the data is displayed as: 00002 bigint[(m)][unsigned][zerofill] Large integer, data type is used to hold some range of integer numeric ranges:        Signed:-9223372036854775808 ~ 9223372036854775807 unsigned: ~ 18446744073709551615 decimal[(M[,d])] [unsigned] [Zerofill] Accurate small value, M is the total number of digits (minus sign), D is the number of decimal points.            The M maximum value is 65,d maximum of 30.        Special: The reason why this type of decaimal is needed for accurate numerical calculations is that it stores the exact value internally as a string.                float[(m,d)] [UNSIGNED] [Zerofill] single-precision floating-point number (not accurate decimal value), M is the sum of numbers, D is the number after the decimal point. Unsigned:-3.402823466E+38 to-1.175494351e-38, 1.175494351E-38 to 3.402823466E+38 signed:            1.175494351E-38 to 3.402823466E+38 * * * * * * * The larger the value, the less accurate * * * double[(M,D)] [UNSIGNED] [Zerofill]                Double-precision floating-point number (non-accurate decimal value), M is the total number of digits, and D is the number after the decimal point.  Unsigned: -1.7976931348623157E+308 to-2.2250738585072014e-308 2.2250738585072014E-308 to            1.7976931348623157E+308 signed: 2.2250738585072014E-308 to 1.7976931348623157E+308 The larger the number, the less accurate the char (m) char data type is used to represent fixed-length strings and can contain up to 255 characters.            where m represents the length of the string. PS: Even if the data is less than m length, the M-length varchar (m) Varchars data type is used for variable-length strings and can contain up to 255 characters.            where m represents the maximum length of a string that is allowed to be saved by the data type, as long as a string that is less than the maximum value can be saved in that data type. Note: Although varchar is more flexible to use, the char data type can be processed faster and sometimes more than 50% of the varchar processing speed from the overall system performance perspective.        Therefore, users in the design of the database should be comprehensive consideration of various factors, in order to achieve the best balance text text data type used to save the large string of variable length, you can group more than 65535 (2**16 1) characters.  Mediumtext          A TEXT column with a maximum length of 16,777,215 (2**24? 1) characters.        Longtext a TEXT column with A maximum length of 4,294,967,295 or 4GB (2**32? 1) characters. Enum enum type, an enum column can has a maximum of 65,535 distinct elements.            (The practical limit is less than 3000.) Example: CREATE TABLE Shirts (name VARCHAR (+), size ENUM (' X-small ', ' SMA                ll ', ' Medium ', ' large ', ' x-large '));        INSERT into shirts (name, size) VALUES (' Dress shirt ', ' large '), (' T-shirt ', ' Medium '), (' Polo shirt ', ' small ');            Set set Type a set column can have a maximum of distinct members.                Example: CREATE TABLE myset (Col SET (' A ', ' B ', ' C ', ' d '));        INSERT into MySet (col) VALUES (' A,d '), (' D,a '), (' A,d,a '), (' A,d,d '), (' d,a,d ');            DATE Yyyy-mm-dd (1000-01-01/9999-12-31) timeHH:MM:SS (' -838:59:59 '/' 838:59:59 ') year YYYY (1901/2155) DATETIME yyyy-mm-dd HH:MM:SS (1 000-01-01 00:00:00/9999-12-31 23:59:59 Y) TIMESTAMP YYYYMMDD hhmmss (1970-01-01 00:00:00/2037 years)

5. Table Content operation

1, increase

Insert into table (column name, column name ...) values (value, value, Value ...) Insert into table (column name, column name ...) values (value, value, Value ...), (value, value, Value ...) Insert into table (column name, column name ...) select (column name, column name ...) from table

2. By deleting

Delete from table delete from table where id=1 and Name= ' Alex '

3. The

Update table Set name = ' Alex ' where id>1

4. Check

SELECT * FROM Table SELECT * from table where ID > 1select nid,name,gender as GG from table where ID > 1

5. Other (conditions, wildcards, restrictions, sorting, grouping, linking tables, combinations)

A, condition select * from table where ID > 1 and name! = ' Alex ' and num = 12;     SELECT * FROM table where ID between 5 and 16; SELECT * FROM table where ID in (11,22,33) select * FROM table where ID not in (11,22,33) select * FROM table where ID in (Selec   T nid from table) b, wildcard select * from table where name like ' ale% '-ale begins with all (multiple strings) select * FROM table where name like ' Ale_ '            -Ale begins with all (one character) c, restricts select * from table limit 5;          -First 5 lines select * FROM table limit 4, 5; -5 rows starting from line 4th select * FROM table Limit 5 offset 4-5 rows starting from line 4th D, sort select * from table ORDER BY column ASC-according to column  "From small to large arrange select * from table ORDER BY column desc-rank from largest to smallest according to" column "SELECT * from Table order BY column 1 desc, column 2 ASC-according to "Column 1" is arranged from large to small, if same column 2 from small to large sort e, group Select Num from table GROUP by NUM Select Num,nid from table GROUP by Num,nid Select Num  , nid from table where nid > Group by num,nid Order Nid desc Select num,nid,count (*), SUM (score), Max (score), Min (score) From table GROUP by Num,nid SelectNum from table GROUP by NUM has max (ID) > 10 Special: Group by must be in where, before order by F, no correspondence of the list does not display select A.num, A . Name, b.name from A, Where A.nid = B.nid No correspondence does not show select A.num, A.name, b.name from A inner JOIN B o     n A.nid = B.nid A table All display, if there is no correspondence in B, then the value is NULL select A.num, A.name, B.name from a left join B on a.nid = B.nid    b table all display, if there is no correspondence in B, then the value is NULL select A.num, A.name, b.name from A right join B on a.nid = B.nid g, combined combination, automatic processing coincident Select nickname from a union select name from B combination, do not process coincident select nickname from a UNION ALL Sele CT name from B

　　

Database--mysql