Basic syntax mysql database

sponsored links
Start: net start mySql;
Enter: mysql-u root -p/mysql-h localhost-u root-p databaseName;
Listed in the database: show databases;
Select a database: use databaseName;
List form: show tables;
Display table column attributes: show columns from tableName;
Establish a database: source fileName.txt;
Matching characters: You can use the wildcard _ represents any character% represents any string;
Add a field: alter table tabelName add column fieldName dateType;
Increase the number of fields: alter table tabelName add column fieldName1 dateType, add columns fieldName2 dateType;
Multi-line command input: Note the word can not be broken; when you insert or change data, the string field can not be expanded to multiple lines, otherwise the hard returns will be stored in the data;
Add an administrator account: grant all on *.* to user @ localhost identified by "password";
After each statement, enter a semicolon to the end of the fill ';', or fill plus '\ g' can be;
Query time: select now ();
Queries the current user: select user ();
Query the database version: select version ();
The current use of the database query: select database ();

1, delete student_course students in the database table:
rm-f student_course / students .*

2, back up the database: (test database backup)
mysqldump-u root-p test> c: \ test.txt
Backup tables: (backup table mytable under test database)
mysqldump-u root-p test mytable> c: \ test.txt
The backup data into a database: (back into the test database)
mysql-u root-p test

3, create a temporary table: (create a temporary table zengchao)
create temporary table zengchao (name varchar (10));

4, is to first create a table to determine the existence of the table create table if not exists students (... ...);

5, some tables have been copied from the structure of the table create table table2 select * from table1 where 1 <> 1;

6, copy the table create table table2 select * from table1;

7, rename the table alter table table1 rename as table2;

8, to modify the column type alter table table1 modify id int unsigned; / / modify column id of type int unsigned
alter table table1 change id sid int unsigned; / / change the name of the column id sid, and the property is modified to int unsigned

9, creating an index alter table table1 add index ind_id (id);
create index ind_id on table1 (id);
create unique index ind_id on table1 (id); / / create a unique index of 10, delete the index drop index idx_id on table1;
alter table table1 drop index ind_id;

11, the joint character or more columns (column id with ':' and column name and "=" Connection)
select concat (id ,':', name ,'=') from students;

12, limit (select 10-20) <first record set number 0>
select * from students order by id limit 9,10;

13, MySQL does not support the function of transactions, views, foreign keys and referential integrity, stored procedures and triggers 14, MySQL will use the index operation symbols <,<=,>=,>,=, between, in, without % or _ at the beginning of the like

15, the shortcomings of using the index 1) slow down the speed of data additions and deletions;
2) take up disk space;
3) increase the burden on the query optimizer;
When the query optimizer to generate execution plans, we will consider the index, too many indexes will increase the workload of the query optimizer, the query result can not choose the best solution;

16, analysis of the efficiency index method: the former in the general SQL statement with explain;
The results mean:
1) table: table name;
2) type: connection type, (ALL / Range / Ref). One ref is the best;
3) possible_keys: query can use the index name;
4) key: the actual use of the index;
5) key_len: the index to be used part of the length (bytes);
6) ref: display column name or "const" (do not understand the meaning);
7) rows: The MySQL that results in finding the right number of rows must be scanned before;
8) extra: MySQL's recommendations;

17, with a shorter fixed-length column 1) as a shorter data type;
2) Whenever possible, use fixed-length data types;
a) use char instead of varchar, fixed-length data processing faster than the variable-length;
b) For frequently modified tables, disks can become fragmented, thus affecting the overall performance of the database;
c) collapse in case of a data table, using a fixed length rows of the table easier to reconstruct. Using fixed-length rows, the beginning of each record is a multiple of a fixed record length, can easily be detected, but the use of variable-length data line is not necessarily a;
d) types of data for MyISAM tables, although the data into fixed-length columns can improve performance, but occupies a large space;

18, using not null and enum
Try to define a column as not null, it will give the data out faster and need less space, but also in the query, MySQL do not need to check whether there is a special case, namely, a null value, thus optimizing the query;
If a column contains only a limited number of specific values, such as gender, whether valid or school year, etc., in which case you should consider converting to enum value of a column, MySQL processed more quickly, because all the enum values ​​in the system is to identify the values ​​expressed in;

19, using the optimize table
For the frequent changes to the table, prone to fragmentation, so that queries the database must be read in more disk blocks, to reduce query performance. A long table with variable disk fragmentation problems exist, the problem is more prominent on the blob data types, because of its size vary greatly. You can use the optimize table to defragment the database to ensure that performance does not drop to optimize those affected by the fragmentation of data tables. optimize table can be used for MyISAM and BDB type tables. In fact any defragmentation methods are used mysqldump to dump the data table, then use the dump file after both the new data table;

20, using the procedure analyse ()
You can use the procedure analyse () shows the best type of proposal is very simple to use, in the select statement followed by the procedure analyse () on it; such as:
select * from students procedure analyse ();
select * from students procedure analyse (16,256);
The second statement requires procedure analyse () do not contain more than 16 recommended value, or contains more than 256 byte enum type, without limitation, the output may be very long;

21, use the query cache 1) the query cache works:
First to execute a select statement, the server to remember the query text and query results are stored in the cache, the next encounter this statement, directly from the cache return result; when updating the data table, the table Any query cache becomes invalid, and will be discarded.
2) Configure the cache parameters:
Variables: query_cache _type, the query cache mode of operation. There are 3 modes, 0: do not cache; 1: query cache, unless and select sql_no_cache beginning; 2: those needed to select sql_cache cache only the beginning of the query; query_cache_size: Set maximum query result set cache size, larger than this value large will not be cached.

22, adjust the hardware 1) upload more memory in the machine;
2) increase faster hard drives to reduce I / O wait time;
Seek time is a major factor in determining performance, literally moving the head is the slowest, once head positioning, reading from the track is fast;
3) in a different physical hard disk activity on the device re-distribution;
If possible, should be the busiest database stored in different physical devices, which use the same physical device with a different partition is different, because they will compete for the same physical resources (head).

18:03 | permalink | visit (181) | Comments (4) | Favorites | Technology | Pangu Party published in the circle


Permanent link
http://loveexception.iteye.com/blog/134511


A total of 4 comments Comment


Throw love 2 weeks ago


1. Mysql training
1.1 This document is for training purposes MySQL database basic training, in order to project team members to achieve the purpose of using the MySQL database.
1.2 Target audience Developers
1.3 Explanation of symbols commonly used words and commonly used words:
Mysql: a free cross-platform database system
E: \ mysql: that is the dos command window below
mysql> that is in the mysql command line
1.4 Reference Information
http://dev.mysql.com/doc/refman/5.0/en/index.html

2. MYSQL
2.1 Connection MYSQL
Format: mysql-h host address-u username-p password to connect to remote machine:
E: \ mysql> mysql-h10.4.3.188-uptsdb-p
Equivalent to writing
E: \ mysql> mysql - host = 10.4.3.188 - user = ptsdb - password
Connect the local machine:
E: \ mysql> mysql-uroot-p
Equivalent to writing
E: \ mysql> mysql - user = root-password
(Note: u and the root can not add space, the other is the same)
Note: To set the environment variable path inside the mysql bin path:
C: \ Program Files \ MySQL \ MySQL Server 5.0 \ bin
2.2 Change Password Method 1: Using mysqladmin
Format: mysqladmin-u username-p password old password new password Example 1: E: \ mysql> mysqladmin-uroot password root
Note: Since the beginning of root without a password, so-p old password one can be omitted.
Example 2: then the root password to root123.
E: \ mysql> mysqladmin-uroot-proot password root123
Method Two: Update the user table directly
mysql> UPDATE user SET password = PASSWORD ("test123") WHERE user = 'test';
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR test = PASSWORD ('test123');
mysql> FLUSH PRIVILEGES;
Method 3: Use the grant
Format: grant permissions on the database. Form | other to the user @ host IDENTIFIED BY password Example 1: to test the user all the permissions in the local localhost (except GRANT OPTION), the password for the test
(Equivalent to modify the test user's password)
mysql> grant all on *.* to test @ localhost identified by "test";
Equivalent to
mysql> grant all on *.* to test @ localhost identified by PASSWORD "* 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29";

Example 2, add a user test password to abc, so that he can log on any host, and the test database with the query, insert, update, delete permissions. First, the root user connected to MYSQL, and then type the following command:
mysql> grant select, insert, update, delete on test .* to test @ "%" Identified by "abc";

In the mysql.user table, there are two test users a test user, the local authority with all the other test users, all hosts on the CRUD permissions

Can also cancel some privileges (all)
mysql> revoke insert, update, delete on test .* from test @ "%"
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM test @ "%"
Then mysql> FLUSH PRIVILEGES;

Test with a user no longer used, you can delete
mysql> Delete from user where user = 'test' and host = '%'
mysql> FLUSH PRIVILEGES;

Note: Example 2 to increase the user is more dangerous, and you want to test if a person knows the password, then he can on the internet on any computer to access your mysql database and do whatever they want your test database (which can be By limiting the host)
mysql> grant select, insert, update, delete on test .* to test @ "IP address" Identified by "abc";
2.3 The display command displays the list of databases:
mysql> show databases;
mysql> show schemas; - mysql 5.0.2
Show Table
mysql> show tables from mydb;
Show table status
Mysql> SHOW TABLE STATUS;
Display character set:
mysql> SHOW CHARACTER SET;
Show create table:
mysql> show create table quote;
Show the user permissions:
mysql> SHOW GRANTS FOR 'test' @ 'localhost';
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR CURRENT_USER;
mysql> SHOW GRANTS FOR CURRENT_USER ();
Show index:
mysql> SHOW INDEX FROM mydb.mytable;
Shows the table structure:
mysql> desc mydb.tablename;
mysql> show columns from mydb.tablename;
The MySQL database version:
mysql> select version ();
Display function
mysql> Select * from mysql.func;
Display stored procedure
mysql> Select * from mysql.proc;
Show storage engines
mysql> SHOW ENGINES;
Display variables:
mysql> SHOW VARIABLES;
Display State:
Mysql> SHOW STATUS;
Shows the process
Mysql> SHOW PROCESSLIST
Show INNODB Status
Mysql> SHOW INNODB STATUS
Shows the connection status
Mysql> SHOW STATUS LIKE '% CONNECT%';
Show thread state
Mysql> SHOW STATUS LIKE '% THREAD%';

Etc. ..

2.4 Create Modify Delete
2.4.1 Create a database
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[Create_specification [, create_specification] ...]

create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
For example:
CREATE DATABASE IF NOT EXISTS ddd - if you do not exist, create.
CHARACTER SET 'ujis' - set the character set
COLLATE 'ujis_japanese_ci';
2.4.2 Create Table
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(Create_definition ,...)]
[Table_options] [select_statement]
Example:
CREATE TABLE if not exists `Admin_User` (
`Id` int (11) NOT NULL auto_increment, - PRIMARY KEY,
`LivedoorId` varchar (255) NOT NULL default'',
`Password` varchar (255) NOT NULL default'',
`Auth` int (11) default '0 ',
PRIMARY KEY (`id`) - set the primary health
) ENGINE = MyISAM DEFAULT CHARSET = ujis? Set the character set
ENGINE = MyISAM default storage engine
The binary portable storage engine that is the default storage engine used by MySQL
MyISAM storage engine for each table, there are three files on the hard disk
File Purpose
tbl_name.frm Table format (definition) file
tbl_name.MYD Data file
tbl_name.MYI Index file

ENGINE = InnoDB
Transaction-safe tables with row locking and foreign keys.
ENGINE = BDB
Transaction-safe tables with page locking.
There are other memory engine so MEMORY archive ARCHIVE
ISAM is no longer used

2.4.3 Create an index
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name ,...)
index_col_name:
col_name [(length)] [ASC | DESC]

The name field in the customer table as the index of the first ten characters
CREATE INDEX part_of_name ON customer (name (10));
MYSQL 5.0 features storage engine MyISAM, InnoDB, or BDB table, you can have a null value in the field to create an index storage engine MyISAM, InnoDB, or BDB table, you can create an index on the BLOB TEXT only in MyISAM type form, can be CHAR, VARCHAR, and TEXT field types to create FULLTEXT indexes
Storage Engine Allowable Index Types
MyISAM BTREE
InnoDB BTREE
MEMORY / HEAP HASH, BTREE
You can specify an index type

Example:
CREATE TABLE testtable (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON testtable (id);
2.4.4 Modify the table
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
IGNORE ignore the primary health repeat the error, if repeated, with the first, delete the remaining case: multiple operations simultaneously
mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
Example, rename the INTEGER field, from a to b:
mysql> ALTER TABLE t1 CHANGE ab INTEGER;
Example modify the field type, field names still need the old and new, even if the same field name:
mysql> ALTER TABLE t1 CHANGE bb BIGINT NOT NULL;
You can also use modify
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

In the mysql 5.0 using FIRST or AFTER you can add fields to add the field, the default is in the final
modify change can also be used in mysql 5.0 InnoDB storage engine supports ALTER TABLE to delete foreign key:
mysql> ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

Example:
Create table
mysql> CREATE TABLE t1 (a INTEGER, b CHAR (10));
Rename Table
mysql> ALTER TABLE t1 RENAME t2;
MODIFY a field TINYINT NOT NULL, and change the field b, from CHAR (10) to CHAR (20) and renamed c:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE bc CHAR (20);
Add new field d:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
In the ad on the increase in the index:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);
Remove the field c:
mysql> ALTER TABLE t2 DROP COLUMN c;
Add an automatic increase of the field c, and add the c-based health:
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> ADD PRIMARY KEY (c);
2.4.4.1. To modify the foreign key syntax:
ALTER TABLE tbl_name
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
Example:
create table aa (id1 int not null, name varchar (20), primary key (id1)) type = InnoDB;
create table b (id2 int not null, lessonname varchar (20), primary key (id2)) type = InnoDB;
alter table b add FOREIGN KEY id (id2) references aa (id1);

2.4.5 Delete to delete the database
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
Example: mysql> drop DATABASE IF EXISTS testdb;
Remove Table
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
Example: mysql> drop TABLE IF EXISTS testTable;
Delete an index
DROP INDEX index_name ON tbl_name
Example: mysql> drop index testIndex on testTable;
Rename
RENAME TABLE tbl_name TO new_tbl_name
[, Tbl_name2 TO new_tbl_name2] ...
Example: RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
2.5 database backup to restore the database backup database backup command:
mysqldump - opt - user = username - password = password - default_character-set = charset-B database> output sql file example:
E: \ mysql> Mysqldump - user = ptsdb - password = ptsdb - default_character-set = ujis - opt pts> dump.sql
See batch file

Database import command:
mysql - user = username - password = password - default_character-set = charset [database] <import the sql statement

E: \ mysql> mysql-uptsdb-pptsdb - default-character-set = ujis

Examples: for InnoDB (not set character set)
Following mysqldump import example for InnoDB tables is at least 100x faster than previous examples.
1. Mysqldump - opt - user = username - password database> dumbfile.sql
2. Edit the dump file and put these lines at the beginning:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS = 0;
3. Put these lines at the end:
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
4. Mysql - user = username - password database <dumpfile.sql

Parameters:
- Add-drop-database
Add a DROP DATABASE statement before each CREATE DATABASE statement.

- Add-drop-table
Add a DROP TABLE statement before each CREATE TABLE statement.

- All-databases,-A
Dump all tables in all databases. This is the same as using the - databases option and naming all the databases on the command line.
- Databases,-B
Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name
and following names as table names. With this option, it treats all name arguments as database names.
CREATE DATABASE IF NOT EXISTS db_name and USE db_name statements are included in the output before each new database.

- Host = host_name,-h host_name
Dump data from the MySQL server on the given host. The default host is localhost.
- Opt
This option is shorthand; it is the same as specifying - add-drop-table - add-locks - create-options
- Disable-keys - extended-insert - lock-tables - quick - set-charset.
It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.
In MySQL 5.0, - opt is on by default, but can be disabled with - skip-opt.
To disable only certain of the options enabled by - opt, use their - skip forms;
for example, - skip-add-drop-table or - skip-quick.
There are some other parameters are interested to learn
2.6 Table Data Backup
mysql> use test;
mysql> CREATE TABLE imptest (id INT, n VARCHAR (30));
Query OK, 0 rows affected (0.03 sec)
Method One:
Export using: Mysqldump
E: \ mysql> mysqldump-uptsdb-pptsdb-where "id> = '100 '"
test imptest
E: \ mysql> mysqldump-uptsdb-pptsdb test imptest> e: \ mysql \ imp \ imptest2.txt
Import using mysql
mysql-uptsdb-pptsdb <imptest2.txt
Method Two:
Export using select into OUTFILE
mysql> select * from imptest where'e: \ \ mysql \ \ imp \ \ test3.txt 'FIELDS TERMINATED BY', ';

Import using LOAD DATA INFILE
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(Col_name_or_user_var ,...)]
[SET col_name = expr ,...]]
Example:
mysql> LOAD DATA INFILE 'e: \ \ mysql \ \ imp \ \ test3.txt' INTO TABLE imptest FIELDS TERMINATED BY ',';

Import Using mysqlimport:
E: \ mysql> mysqlimport-uptsdb-pptsdb - local test E: \ mysql \ imp \ imp.txt
mysqlimport: Error: Table 'test.imp' doesn't exist, when using table: imp

E: \ mysql> mysqlimport-uptsdb-pptsdb - local test E: \ mysql \ imp \ imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
Note: File names must follow the same parameters table
-D or - delete the new data into the data table to delete the data before all of the information in the data table
-F or - force regardless of whether an error is encountered, mysqlimport will be forced to insert data
-I or - ignore mysqlimport skip or ignore those who have the same unique
-R or-replace this option with the-i option does the opposite; this option will have the same representatives for the record a unique key
2.7 Data import and export pipelines
E: \ mysql> mysql-h10.5.1.66-uroot-proot - default-character-set = name frontdb_20060415 (databasename)
> E: / mysql / test.txt (output of the sql statement)

1:>; create databases newname (the establishment of a new empty database on the server)
2: # / usr / local / mysql / bin / mysqldump databasename> *. sql (server to export the old database)
3: # / usr / local / mysql / bin / mysql databasename <*. sql (on the new server to import *. sql)
Note the database name to one correspondence.
2.8 The range of field
TINYINT 1 byte
-128--127
TINYINT UNSIGNED 1 byte
0? 255 that is 0 - (28-1)
SMALLINT 2 bytes
-32768--32767 The -215 to (215-1)
SMALLINT UNSIGNED 2 bytes
0 - 65535 or 0 to (216-1)
MEDIUMINT 3 bytes
-8388608 --8,388,607 That? 223 to (223-1)
MEDIUMINT UNSIGNED 3 bytes
0--16777215 that is 0 to (224-1)
INT or INTEGER 4 bytes
-2147483648 - 2147483647 that? 231 to (231-1)
INT UNSIGNED or INTEGER UNSIGNED 4 bytes
0--4294967295 that is 0 to (232-1)
BIGINT 8 bytes
-9223372036854775808 - 9223372036854775807 that? 263 to (263-1)
BIGINT UNSIGNED 8 bytes
0 - 18446744073709551615 ie 0 to (264-1)
FLOAT 4 bytes
-3.402823466E +38 --1.175494351E-38
0
1.175494351E-38 - 3.402823466E +38
DOUBLE or DOUBLE PRECISION or REAL 8 bytes
-1.7976931348623157E +308 --2.2250738585072014E-308
0
2.2250738585072014E-308 - 1.7976931348623157E +308
DECIMAL [(M, [D])] or NUMERIC (M, D) variable from the M (the length of the entire number, including decimal point, left of the decimal digits, decimal digits to the right, but not negative), and
D (right of the decimal digits) to decide, M defaults to 10, D defaults to 0
DATE 3 bytes
1000-01-01 --9999-12-31
DATETIME 8 bytes
1000-01-01 00:00:00 --9999-12-31 23:59:59
TIMESTAMP 4 bytes
1970-01-01 00:00:00 --2037-12-31 23:59:59
TIME 3 bytes
-838:59:59 'To 838:59:59
YEAR [(2 | 4)] 1 byte
The default is 4-bit format, 4-bit format in the range of 1901 - 2155,0000,2 bit format in the range of 70-69 (1970-2069)
CHAR (M) [BINARY] or NCHAR (M) [BINARY] M bytes
M in the range of 1 - 255, if not BINARY item is not case-sensitive, NCHAR to use the default character set. Make up a space in the database, but in the end when you take out the spaces will be removed.
[NATIONAL] VARCHAR (M) [BINARY]
Before 5.0.3 M in the range of 0? 255 L +1 bytes L <= M
5.0.3 and later the range of 0-65535 M
L +1 bytes L <= M 0 <= M <= 256
L +2 bytes L <= M 256 <= 65535
Trailing spaces in the database will be automatically removed.
TINYBLOB or TINYTEXT L +1 bytes
255 (2 ^ 8-1) characters.
BLOB or TEXT L +2 bytes
65535 (2 ^ 16-1) characters
MEDIUMBLOB or MEDIUMTEXT L +3 bytes
16777215 (2 ^ 24-1) characters
LONGBLOB or LONGTEXT L +4 bytes
4294967295 (2 ^ 32-1) characters
ENUM ('value1', 'value2',...) 1 or 2 bytes
Can be a total of 65535 different values
SET ('value1', 'value2',...) 1/2/3/4/8 bytes
A maximum of 64 members
2.9 Query
2.9.1. Limit
The LIMIT clause can be used to limit over by the SELECT statement returns the number of data, it has one or two parameters, if given two arguments,
The first parameter specifies the first line of the location of all data, from 0 (note that not 1), the second parameter specifies the maximum number of rows returned. For example:
select * from table LIMIT 5,10; # return the first 6-15 rows
select * from table LIMIT 5; # return the first five rows
select * from table LIMIT 0,5; # return the first five rows
2.9.2. Join Detailed Create table first, it was

create table emp (
id int not null primary key,
name varchar (10)
);

create table emp_dept (
dept_id varchar (4) not null,
emp_id int not null,
emp_name varchar (10),
primary key (dept_id, emp_id));

insert into emp () values
(1, "Dennis-1"),
(2, "Dennis-2"),
(3, "Dennis-3"),
(4, "Dennis-4"),
(5, "Dennis-5"),
(6, "Dennis-6"),
(7, "Dennis-7"),
(8, "Dennis-8"),
(9, "Dennis-9"),
(10, "Dennis-10");

insert into emp_dept () values
("R & D", 1, "Dennis-1"),
("DEv", 2, "Dennis-2"),
("R & D", 3, "Dennis-3"),
("Test", 4, "Dennis-4"),
("Test", 5, "Dennis-5");
("Dddd", 20, "eeee");
>> Left join
-------------
select a.id, a.name, b.dept_id
from emp a left join emp_dept b on (a.id = b.emp_id);

# Pick out the left side of the table emp in all data, even if there is no information emp_dept also singled out, not to use NULL to show that
# That is displayed on the left of the table emp data is based on the information in

mysql> select a.id, a.name, b.dept_id
-> From emp a left join emp_dept b on (a.id = b.emp_id);
+----+-----------+---------+
| Id | name | dept_id |
+----+-----------+---------+
| 1 | Dennis-1 | R & D |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+
# Pick table emp in the table emp_dept personnel information is not
select a.id, a.name, b.dept_id
from emp a left join emp_dept b on (a.id = b.emp_id)
where b.dept_id IS NULL;

mysql> select a.id, a.name, b.dept_id
-> From emp a left join emp_dept b on (a.id = b.emp_id)
-> Where b.dept_id IS NULL;
+----+-----------+---------+
| Id | name | dept_id |
+----+-----------+---------+
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+

# The table emp_dept on the left side of the case (of course, to emp_dept data to display data based, emp in more than emp_dept in information will not show out):
select a.id, a.name, b.dept_id
from emp_dept b left join emp a on (a.id = b.emp_id);
mysql> select a.id, a.name, b.dept_id
-> From emp_dept b left join emp a on (a.id = b.emp_id);
+------+----------+---------+
| Id | name | dept_id |
+------+----------+---------+
| NULL | NULL | dddd |
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R & D |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+------+----------+---------+
>> Right join
---------------
select a.id, a.name, b.dept_id
from emp a right join emp_dept b on (a.id = b.emp_id);
# Pick the right table emp_dept data to the data to display data based on
mysql> select a.id, a.name, b.dept_id
-> From emp a right join emp_dept b on (a.id = b.emp_id);
+------+----------+---------+
| Id | name | dept_id |
+------+----------+---------+
| NULL | NULL | dddd |
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R & D |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+------+----------+---------+
6 rows in set (0.00 sec)
# Then we look at the exchange table position, and then try to right join

select a.id, a.name, b.dept_id
from emp_dept b right join emp a on (a.id = b.emp_id);

mysql> select a.id, a.name, b.dept_id
-> From emp_dept b right join emp a on (a.id = b.emp_id);
+----+-----------+---------+
| Id | name | dept_id |
+----+-----------+---------+
| 1 | Dennis-1 | R & D |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+
# Left join and the same is not it?
>> Inner join STRAIGHT_JOIN
select a.id, a.name, b.dept_id
from emp a, emp_dept b
where a.id = b.emp_id;

mysql> select a.id, a.name, b.dept_id
-> From emp a, emp_dept b
-> Where a.id = b.emp_id;
+----+----------+---------+
| Id | name | dept_id |
+----+----------+---------+
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R & D |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+----+----------+---------+
2.9.3. Alias ​​alias
You can GROUP BY, ORDER BY or HAVING to use alias to refer to some of the columns. Aliases can also be used to get a better point out the name:
SELECT SQRT (a * b) as rt FROM table_name GROUP BY rt HAVING rt> 0;
SELECT id, COUNT (*) AS cnt FROM table_name GROUP BY id HAVING cnt> 0;
SELECT id AS "Customer identity" FROM table_name;
Note that you ANSI SQL does not allow you in a reference to an alias in the WHERE clause. This is because when the WHERE code is executed the column value may not end. For example, the following query is illegal:
SELECT id, COUNT (*) AS cnt FROM table_name WHERE cnt> 0 GROUP BY id;
WHERE statement is executed to determine which rows should be included in the GROUP BY part while HAVING is used to determine the result set should only be used in which the line.
2.9.4. Regular regular expression (regex) is to define a complex query is a powerful tool.
Here is a simple data, it ignores some of the details.
Regular expression defines a string of rules. The simplest regular expression does not contain any reserved words. For example,

And only the regular expression hello string "hello" match.
General regular expression to use some special structure, so it can match more strings. For example, the regular expression hello | word matches both the string "hello" can match the string "word".
For a more complex example, the regular expression B [an] * s matches the string "Bananas", "Baaaaa
s "
, "Bs" and any other B-terminated string that begins with s, the middle can include any number a and any n-combination.
A regular expression can use the following reserved words
^
After the matched string to the string at the beginning
mysql> select "fonfo" REGEXP "^ fo $"; -> 0 (that do not match)
mysql> select "fofo" REGEXP "^ fo"; -> 1 (that match)
$
The string to match the front end of the string
mysql> select "fono" REGEXP "^ fono $"; -> 1 (that match)
mysql> select "fono" REGEXP "^ fo $"; -> 0 (that do not match)
..
Match any character (including newline)
mysql> select "fofo" REGEXP "^ f. *"; -> 1 (that match)
mysql> select "fonfo" REGEXP "^ f. *"; -> 1 (that match)
a *
0-n match any more than a (including the empty string)
mysql> select "Ban" REGEXP "^ Ba * n"; -> 1 (that match)
mysql> select "Baaan" REGEXP "^ Ba * n"; -> 1 (that match)
mysql> select "Bn" REGEXP "^ Ba * n"; -> 1 (that match)
a +
1-n match any number of a (not including the empty string)
mysql> select "Ban" REGEXP "^ Ba + n"; -> 1 (that match)
mysql> select "Bn" REGEXP "^ Ba + n"; -> 0 (that do not match)
a?
0-1 a match
mysql> select "Bn" REGEXP "^ Ba? n"; -> 1 (that match)
mysql> select "Ban" REGEXP "^ Ba? n"; -> 1 (that match)
mysql> select "Baan" REGEXP "^ Ba? n"; -> 0 (that do not match)
de | abc
Match de or abc
mysql> select "pi" REGEXP "pi | apa"; -> 1 (that match)
mysql> select "axe" REGEXP "pi | apa"; -> 0 (that do not match)
mysql> select "apa" REGEXP "pi | apa"; -> 1 (that match)
mysql> select "apa" REGEXP "^ (pi | apa) $"; -> 1 (that match)
mysql> select "pi" REGEXP "^ (pi | apa) $"; -> 1 (that match)
mysql> select "pix" REGEXP "^ (pi | apa) $"; -> 0 (that do not match)
(Abc) *
Match any number (0-n a) abc (including the empty string)
mysql> select "pi" REGEXP "^ (pi )*$"; -> 1 (that match)
mysql> select "pip" REGEXP "^ (pi )*$"; -> 0 (that do not match)
mysql> select "pipi" REGEXP "^ (pi )*$"; -> 1 (that match)
{1}
{2,3}
This is a more comprehensive approach, it can achieve several reserved words in front of the function
a *
Can be written as a {0,}
a +
Can be written as a {1,}
a?
Can be written as a {0,1}
In {} is only one integer parameter i, that character can only appear i times; in the {} parameter within an integer i,
Followed by a "" means the character can appear or i times i times; in {} is only one integer parameter i,
Followed by a "," talk to an integer argument j, that characters can appear in more than i, j times the following (including the i-th and j times). One of the integer parameter must be greater than or equal to 0, less than or equal RE_DUP_MAX (default is 25
5).
If there are two parameters, the second must be greater than equal to the first
[A-dX]
Match "a", "b", "c", "d" or "X"
[^ A-dX]
Matches "a", "b", "c", "d", "X" any character.
"[","]" Must be doubled
mysql> select "aXbc" REGEXP "[a-dXYZ]"; -> 1 (that match)
mysql> select "aXbc" REGEXP "^ [a-dXYZ] $"; -> 0 (that do not match)
mysql> select "aXbc" REGEXP "^ [a-dXYZ ]+$"; -> 1 (that match)
mysql> select "aXbc" REGEXP "^ [^ a-dXYZ ]+$"; -> 0 (that do not match)
mysql> select "gheis" REGEXP "^ [^ a-dXYZ ]+$"; -> 1 (that match)
mysql> select "gheisa" REGEXP "^ [^ a-dXYZ ]+$"; -> 0 (that do not match)
-------------------------------------------------- ----------
[[. Characters.]]
That more elements of the sequence. The order of the characters in the brackets is unique. However, parentheses can contain wildcards,
So he can match more characters. For example: the regular expression [[. Ch.]] * C matches the first five characters chchcc.
[= Character_class =]
That same class, the class can replace the other equivalent elements, including its own. For example, if o and (+) is an equal member of a class, then [[= o =]],[[=(+)=]] and [o (+)] are completely equivalent.
[: Character_class:]
In parentheses, in [: and:] character class is the middle name, can represent all the characters belong to this class.
Character class names are: alnum, digit, punct, alpha, graph, space, blank, lower, uppe
r, cntrl, print and xdigit
mysql> select "justalnums" REGEXP "[[: alnum :]]+"; -> 1 (that match)
mysql> select "!!" REGEXP "[[: alnum :]]+"; -> 0 (that do not match)
[[:<:>
[[:>:]]
Respectively, the beginning and end of a word matches the empty string, the beginning and end of this word is not included in the alnum character and can not be underlined.
mysql> select "a word a" REGEXP "[[:<:>:]]"; -> 1 (that match)
mysql> select "a xword a" REGEXP "[[:<:>:]]"; -> 0 (that do not match)
mysql> select "weeknights" REGEXP "^ (wee | week) (knights | nights) $"; -> 1 (that match)
2.9.5. Select to use IF Statement
mysql> select * from test;
+------+------+------+-------+
| Dept | id | sex | name |
+------+------+------+-------+
| 1 | 1 | 0 | wang |
| 2 | 2 | 1 | zhang |
| 3 | 3 | 0 | li |
+------+------+------+-------+
3 rows in set (0.00 sec)
mysql> select dept, id, if (sex = 0, 'female', 'M') sex, name from test;
+------+------+-----+-------+
| Dept | id | sex | name |
+------+------+-----+-------+
| 1 | 1 | F | wang |
| 2 | 2 | M | zhang |
| 3 | 3 | women | li |
+------+------+-----+-------+
3 rows in set (0.00 sec)
2.9.6. Select the Use CASE Statement
mysql> select dept, id, (case sex when '0 'then' Female 'else' male 'end) as sex, name from test;
+------+------+------+-------+
| Dept | id | sex | name |
+------+------+------+-------+
| 1 | 1 | F | wang |
| 2 | 2 | M | zhang |
| 3 | 3 | women | li |
+------+------+------+-------+
mysql> select (case dept when '1 'then' no1 'when '2' then 'no2' else 'other' end) as dept from test;
+-------+
| Dept |
+-------+
| No1 |
| No2 |
| Other |
+-------+
3 rows in set (0.00 sec)
Note: the equivalent in Oracle decode and case when
Useful in the statistical report

2.10 Stored Procedures and Functions
CREATE PROCEDURE sp_name ([proc_parameter [,...]])
[Characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter [,...]])
RETURNS type
[Characteristic ...] routine_body

proc_parameter:
[IN | OUT | INOUT] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'string'

routine_body:
Valid SQL procedure statement or statements

Example: Creating a process hello, greeting display
mysql> delimiter;
mysql> drop PROCEDURE if exists hello;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter / /
mysql>
mysql> CREATE PROCEDURE hello (IN s varchar (20))
-> BEGIN
-> SELECT CONCAT ('Hello,', s ,'!') as hello;
-> END;
-> / /
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter;
mysql> call hello ('wangyl');
+---------------+
| Hello |
+---------------+
| Hello, wangyl! |
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Example 2: Query total number of records in table t
mysql> delimiter / /

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT (*) INTO param1 FROM t;
-> END
-> / /
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter;

mysql> CALL simpleproc (@ a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @ a;
+------+
| @ A |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Example: Creating a function
mysql> delimiter;
mysql> drop FUNCTION if exists dateFunction;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter / /
mysql> CREATE FUNCTION dateFunction (iCase int)
-> RETURNS varchar (50)
-> Begin
-> DECLARE iType int;
-> DECLARE sReturn varchar (50);
-> Set iType = icase +1;
-> Case iType
-> When 1 then select DATE_FORMAT (NOW (),'% Y-% m-% d ') into sReturn;
-> When 2 then select DATE_FORMAT (NOW (),'% W% M% Y ') into sReturn;
-> Else
-> Select NOW () into sReturn;
-> End case;
-> Return sReturn;
-> End;
-> / /
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter;
mysql> select dateFunction (0);
+-----------------+
| DateFunction (0) |
+-----------------+
| 2005-11-14 |
+-----------------+
1 row in set (0.00 sec)

mysql> select dateFunction (1);
+----------------------+
| DateFunction (1) |
+----------------------+
| Monday November 2005 |
+----------------------+
1 row in set (0.00 sec)

mysql> select dateFunction (2);
+---------------------+
| DateFunction (2) |
+---------------------+
| 2005-11-14 15:05:43 |
+---------------------+
1 row in set (0.00 sec)

2.11 added: trigger
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
CREATE TABLE test1 (a1 INT);
CREATE TABLE test2 (a2 INT);
CREATE TABLE test3 (a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4 (
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END |

DELIMITER;

INSERT INTO test3 (a3) ​​VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

If you insert the following values ​​into table test1 as shown here:

mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

Then the data in the four tables will be as follows:

mysql> SELECT * FROM test1;
+------+
| A1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| A2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| A3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| A4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)

2.12 Delete DELETE Syntax
Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name [.*] [, tbl_name [.*]] ...
FROM table_references
[WHERE where_condition]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name [.*] [, tbl_name [.*]] ...
USING table_references
[WHERE where_condition]
For delete multiple tables, you can not use order by and limit
Multi-table delete syntax one: just remove from the table in front of the matching records
DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;
From t1, t2, t3 select the table you want to delete the record, just delete t1, t2 in the table matched those records.
Multi-table delete syntax II: listed in the table to delete from the selected records.
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;
From t1, t2, t3 select the table you want to delete the record (using using), just delete t1, t2 in the table matched those records.

Note: If you use an alias, you must use an alias.

DELETE t1 FROM test AS t1, test2 WHERE ...

Support multiple data between multi-table delete, but in this case, you must specify the table, but can not use an alias:

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...
Currently, you can not sub-queries from the same table, delete data in the same table



Throw love 2 weeks ago


1016 Error: File not open, use the background or use phpmyadmin to repair the repair.
1044 Error: Insufficient user rights database, please contact the space business solutions
1045 Error: database server / database username / database name / database password error, please contact the space business checking account.
1054 Error: file conflict with the database, please use the correct file upload up coverage.
1146 Error: Data table missing, please restore the backup data.
1005: create table failed
1006: Failed to create database
1007: The database already exists, create a database failure
1008: The database does not exist, delete the database failed
1009: Can not delete database delete database file that is causing the failure
1010: Can not delete delete database data directory led to failure
1011: Failed to delete database file
1012: can not read record in system table
1020: Record has been modified by other users
1021: free hard disk space is low, increase available hard disk space
1022: keyword repetition, changes in the failure
1023: An error occurred close
1024: Error reading file
1025: An error occurred when changing name
1026: Write file error
1032: Record does not exist
1036: data table is read-only, can not be modified
1037: system memory, restart the database or restart the server
1038: for the sort of memory, increase the sort buffers
1040: Reach the maximum number of connections the database, please increase the number of database connections available
1041: System memory
1042: Invalid host name
1043: Invalid Connection
1044: The current user does not have access to the database
1045: Can not connect to the database, user name or password error
1048: Field can not be empty
1049: The database does not exist
1050: data table already exists
1051: data table does not exist
1054: Field does not exist
1065: Invalid SQL, SQL statement is empty
1081: Socket connection can not be established
1114: table is full, can not hold any records
1116: Too many open tables
1129: database exception occurs, restart the database
1130: connect to the database failed, there is no permission to connect to the database
1133: The database user does not exist
1141: The current user does not have access to the database
1142: The current user does not have access to data tables
1143: The current user does not have access to data fields in the table
1146: data table does not exist
1147: Undefined user access to data tables
1149: SQL statement syntax error
1158: Network error, read error occurs, check the network connection status
1159: A network error, reading out, check the network connection status
1160: Network error, write error occurs, check the network connection status
1161: Network error, write out, please check the network connection status
1062: field values ​​repeat, storage failure
1169: duplicate field value, updating records failed
1177: Failed to open data sheet
1180: Failed to commit the transaction
1181: rollback the transaction failed
1203: The current user and database connection to the database to establish the maximum number of connections, increase the number of available database connections or restart the database
1205: Lock Timeout
1211: The current user does not have permission to create user
1216: foreign key constraint check fails, the failure to update the child table records
1217: foreign key constraint check fails, delete or modify the failure of the main table records
1226: The current users of the resource has exceeded the allowed resources, restart the database or restart the server
1227: insufficient privileges, you have no right to do this
1235: MySQL version is too low, do not have this feature
2002: Can't connect to ... usually means not a MySQL server running on the system or when trying to connect to the mysqld server, you are using a wrong socket file or TCP / IP port.

Description: These are just a common error number, and certainly not necessarily effective solution! Refer to MYSQL manual (download stations have large download)

Wrong name for more detailed error code / error code SQL state
ER_HASHCHK 1000 HY000
ER_NISAMCHK 1001 HY000
ER_NO 1002 HY000
ER_YES 1003 HY000
ER_CANT_CREATE_FILE 1004 HY000
ER_CANT_CREATE_TABLE 1005 HY000
ER_CANT_CREATE_DB 1006 HY000
ER_DB_CREATE_EXISTS 1007 HY000
ER_DB_DROP_EXISTS 1008 HY000
ER_DB_DROP_DELETE 1009 HY000
ER_DB_DROP_RMDIR 1010 HY000
ER_CANT_DELETE_FILE 1011 HY000
ER_CANT_FIND_SYSTEM_REC 1012 HY000
ER_CANT_GET_STAT 1013 HY000
ER_CANT_GET_WD 1014 HY000
ER_CANT_LOCK 1015 HY000
ER_CANT_OPEN_FILE 1016 HY000
ER_FILE_NOT_FOUND 1017 HY000
ER_CANT_READ_DIR 1018 HY000
ER_CANT_SET_WD 1019 HY000
ER_CHECKREAD 1020 HY000
ER_DISK_FULL 1021 HY000
ER_DUP_KEY 1022 23000
ER_ERROR_ON_CLOSE 1023 HY000
ER_ERROR_ON_READ 1024 HY000
ER_ERROR_ON_RENAME 1025 HY000
ER_ERROR_ON_WRITE 1026 HY000
ER_FILE_USED 1027 HY000
ER_FILSORT_ABORT 1028 HY000
ER_FORM_NOT_FOUND 1029 HY000
ER_GET_ERRNO 1030 HY000
ER_ILLEGAL_HA 1031 HY000
ER_KEY_NOT_FOUND 1032 HY000
ER_NOT_FORM_FILE 1033 HY000
ER_NOT_KEYFILE 1034 HY000
ER_OLD_KEYFILE 1035 HY000
ER_OPEN_AS_READONLY 1036 HY000
ER_OUTOFMEMORY 1037 HY001
ER_OUT_OF_SORTMEMORY 1038 HY001
ER_UNEXPECTED_EOF 1039 HY000
ER_CON_COUNT_ERROR 1040 08004
ER_OUT_OF_RESOURCES 1041 08004
ER_BAD_HOST_ERROR 1042 08S01
ER_HANDSHAKE_ERROR 1043 08S01
ER_DBACCESS_DENIED_ERROR 1044 42000
ER_ACCESS_DENIED_ERROR 1045 42000
ER_NO_DB_ERROR 1046 42000
ER_UNKNOWN_COM_ERROR 1047 08S01
ER_BAD_NULL_ERROR 1048 23000
ER_BAD_DB_ERROR 1049 42000
ER_TABLE_EXISTS_ERROR 1050 42S01
ER_BAD_TABLE_ERROR 1051 42S02
ER_NON_UNIQ_ERROR 1052 23000
ER_SERVER_SHUTDOWN 1053 08S01
ER_BAD_FIELD_ERROR 1054 42S22
ER_WRONG_FIELD_WITH_GROUP 1055 42000
ER_WRONG_GROUP_FIELD 1056 42000
ER_WRONG_SUM_SELECT 1057 42000
ER_WRONG_VALUE_COUNT 1058 21S01
ER_TOO_LONG_IDENT 1059 42000
ER_DUP_FIELDNAME 1060 42S21
ER_DUP_KEYNAME 1061 42000
ER_DUP_ENTRY 1062 23000
ER_WRONG_FIELD_SPEC 1063 42000
ER_PARSE_ERROR 1064 42000
ER_EMPTY_QUERY 1065 42000
ER_NONUNIQ_TABLE 1066 42000
ER_INVALID_DEFAULT 1067 42000
ER_MULTIPLE_PRI_KEY 1068 42000
ER_TOO_MANY_KEYS 1069 42000
ER_TOO_MANY_KEY_PARTS 1070 42000
ER_TOO_LONG_KEY 1071 42000
ER_KEY_COLUMN_DOES_NOT_EXITS 1072 42000
ER_BLOB_USED_AS_KEY 1073 42000
ER_TOO_BIG_FIELDLENGTH 1074 42000
ER_WRONG_AUTO_KEY 1075 42000
ER_READY 1076 00000
ER_NORMAL_SHUTDOWN 1077 00000
ER_GOT_SIGNAL 1078 00000
ER_SHUTDOWN_COMPLETE 1079 00000
ER_FORCING_CLOSE 1080 08S01
ER_IPSOCK_ERROR 1081 08S01
ER_NO_SUCH_INDEX 1082 42S12
ER_WRONG_FIELD_TERMINATORS 1083 42000
ER_BLOBS_AND_NO_TERMINATED 1084 42000
ER_TEXTFILE_NOT_READABLE 1085 HY000
ER_FILE_EXISTS_ERROR 1086 HY000
ER_LOAD_INFO 1087 HY000
ER_ALTER_INFO 1088 HY000
ER_WRONG_SUB_KEY 1089 HY000
ER_CANT_REMOVE_ALL_FIELDS 1090 42000
ER_CANT_DROP_FIELD_OR_KEY 1091 42000
ER_INSERT_INFO 1092 HY000
ER_UPDATE_TABLE_USED 1093 HY000
ER_NO_SUCH_THREAD 1094 HY000
ER_KILL_DENIED_ERROR 1095 HY000
ER_NO_TABLES_USED 1096 HY000
ER_TOO_BIG_SET 1097 HY000
ER_NO_UNIQUE_LOGFILE 1098 HY000
ER_TABLE_NOT_LOCKED_FOR_WRITE 1099 HY000
ER_TABLE_NOT_LOCKED 1100 HY000
ER_BLOB_CANT_HAVE_DEFAULT 1101 42000
ER_WRONG_DB_NAME 1102 42000
ER_WRONG_TABLE_NAME 1103 42000
ER_TOO_BIG_SELECT 1104 42000
ER_UNKNOWN_ERROR 1105 HY000
ER_UNKNOWN_PROCEDURE 1106 42000
ER_WRONG_PARAMCOUNT_TO_PROCEDURE 1107 42000
ER_WRONG_PARAMETERS_TO_PROCEDURE 1108 HY000
ER_UNKNOWN_TABLE 1109 42S02
ER_FIELD_SPECIFIED_TWICE 1110 42000
ER_INVALID_GROUP_FUNC_USE 1111 42000
ER_UNSUPPORTED_EXTENSION 1112 42000
ER_TABLE_MUST_HAVE_COLUMNS 1113 42000
ER_RECORD_FILE_FULL 1114 HY000
ER_UNKNOWN_CHARACTER_SET 1115 42000
ER_TOO_MANY_TABLES 1116 HY000
ER_TOO_MANY_FIELDS 1117 HY000
ER_TOO_BIG_ROWSIZE 1118 42000
ER_STACK_OVERRUN 1119 HY000
ER_WRONG_OUTER_JOIN 1120 42000
ER_NULL_COLUMN_IN_INDEX 1121 42000
ER_CANT_FIND_UDF 1122 HY000
ER_CANT_INITIALIZE_UDF 1123 HY000
ER_UDF_NO_PATHS 1124 HY000
ER_UDF_EXISTS 1125 HY000
ER_CANT_OPEN_LIBRARY 1126 HY000
ER_CANT_FIND_DL_ENTRY 1127 HY000
ER_FUNCTION_NOT_DEFINED 1128 HY000
ER_HOST_IS_BLOCKED 1129 HY000
ER_HOST_NOT_PRIVILEGED 1130 HY000
ER_PASSWORD_ANONYMOUS_USER 1131 42000
ER_PASSWORD_NOT_ALLOWED 1132 42000
ER_PASSWORD_NO_MATCH 1133 42000
ER_UPDATE_INFO 1134 HY000
ER_CANT_CREATE_THREAD 1135 HY000
ER_WRONG_VALUE_COUNT_ON_ROW 1136 21S01
ER_CANT_REOPEN_TABLE 1137 HY000
ER_INVALID_USE_OF_NULL 1138 42000
ER_REGEXP_ERROR 1139 42000
ER_MIX_OF_GROUP_FUNC_AND_FIELDS 1140 42000
ER_NONEXISTING_GRANT 1141 42000
ER_TABLEACCESS_DENIED_ERROR 1142 42000
ER_COLUMNACCESS_DENIED_ERROR 1143 42000
ER_ILLEGAL_GRANT_FOR_TABLE 1144 42000
ER_GRANT_WRONG_HOST_OR_USER 1145 42000
ER_NO_SUCH_TABLE 1146 42S02
ER_NONEXISTING_TABLE_GRANT 1147 42000
ER_NOT_ALLOWED_COMMAND 1148 42000
ER_SYNTAX_ERROR 1149 42000
ER_DELAYED_CANT_CHANGE_LOCK 1150 HY000
ER_TOO_MANY_DELAYED_THREADS 1151 HY000
ER_ABORTING_CONNECTION 1152 08S01
ER_NET_PACKET_TOO_LARGE 1153 08S01
ER_NET_READ_ERROR_FROM_PIPE 1154 08S01
ER_NET_FCNTL_ERROR 1155 08S01
ER_NET_PACKETS_OUT_OF_ORDER 1156 08S01
ER_NET_UNCOMPRESS_ERROR 1157 08S01
ER_NET_READ_ERROR 1158 08S01
ER_NET_READ_INTERRUPTED 1159 08S01
ER_NET_ERROR_ON_WRITE 1160 08S01
ER_NET_WRITE_INTERRUPTED 1161 08S01
ER_TOO_LONG_STRING 1162 42000
ER_TABLE_CANT_HANDLE_BLOB 1163 42000
ER_TABLE_CANT_HANDLE_AUTO_INCREMENT 1164 42000
ER_DELAYED_INSERT_TABLE_LOCKED 1165 HY000
ER_WRONG_COLUMN_NAME 1166 42000
ER_WRONG_KEY_COLUMN 1167 42000
ER_WRONG_MRG_TABLE 1168 HY000
ER_DUP_UNIQUE 1169 23000
ER_BLOB_KEY_WITHOUT_LENGTH 1170 42000
ER_PRIMARY_CANT_HAVE_NULL 1171 42000
ER_TOO_MANY_ROWS 1172 42000
ER_REQUIRES_PRIMARY_KEY 1173 42000
ER_NO_RAID_COMPILED 1174 HY000
ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE 1175 HY000
ER_KEY_DOES_NOT_EXITS 1176 HY000
ER_CHECK_NO_SUCH_TABLE 1177 42000
ER_CHECK_NOT_IMPLEMENTED 1178 42000
ER_CANT_DO_THIS_DURING_AN_TRANSACTION 1179 25000
ER_ERROR_DURING_COMMIT 1180 HY000
ER_ERROR_DURING_ROLLBACK 1181 HY000
ER_ERROR_DURING_FLUSH_LOGS 1182 HY000
ER_ERROR_DURING_CHECKPOINT 1183 HY000
ER_NEW_ABORTING_CONNECTION 1184 08S01
ER_DUMP_NOT_IMPLEMENTED 1185 HY000
ER_FLUSH_MASTER_BINLOG_CLOSED 1186 HY000
ER_INDEX_REBUILD 1187 HY000
ER_MASTER 1188 HY000
ER_MASTER_NET_READ 1189 08S01
ER_MASTER_NET_WRITE 1190 08S01
ER_FT_MATCHING_KEY_NOT_FOUND 1191 HY000
ER_LOCK_OR_ACTIVE_TRANSACTION 1192 HY000
ER_UNKNOWN_SYSTEM_VARIABLE 1193 HY000
ER_CRASHED_ON_USAGE 1194 HY000
ER_CRASHED_ON_REPAIR 1195 HY000
ER_WARNING_NOT_COMPLETE_ROLLBACK 1196 HY000
ER_TRANS_CACHE_FULL 1197 HY000
ER_SLAVE_MUST_STOP 1198 HY000
ER_SLAVE_NOT_RUNNING 1199 HY000
ER_BAD_SLAVE 1200 HY000
ER_MASTER_INFO 1201 HY000
ER_SLAVE_THREAD 1202 HY000
ER_TOO_MANY_USER_CONNECTIONS 1203 42000
ER_SET_CONSTANTS_ONLY 1204 HY000
ER_LOCK_WAIT_TIMEOUT 1205 HY000
ER_LOCK_TABLE_FULL 1206 HY000
ER_READ_ONLY_TRANSACTION 1207 25000
ER_DROP_DB_WITH_READ_LOCK 1208 HY000
ER_CREATE_DB_WITH_READ_LOCK 1209 HY000
ER_WRONG_ARGUMENTS 1210 HY000
ER_NO_PERMISSION_TO_CREATE_USER 1211 42000
ER_UNION_TABLES_IN_DIFFERENT_DIR 1212 HY000
ER_LOCK_DEADLOCK 1213 40001
ER_TABLE_CANT_HANDLE_FULLTEXT 1214 HY000
ER_CANNOT_ADD_FOREIGN 1215 HY000
ER_NO_REFERENCED_ROW 1216 23000
ER_ROW_IS_REFERENCED 1217 23000
ER_CONNECT_TO_MASTER 1218 08S01
ER_QUERY_ON_MASTER 1219 HY000
ER_ERROR_WHEN_EXECUTING_COMMAND 1220 HY000
ER_WRONG_USAGE 1221 HY000
ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT 1222 21000
ER_CANT_UPDATE_WITH_READLOCK 1223 HY000
ER_MIXING_NOT_ALLOWED 1224 HY000
ER_DUP_ARGUMENT 1225 HY000
ER_USER_LIMIT_REACHED 1226 42000
ER_SPECIFIC_ACCESS_DENIED_ERROR 1227 HY000
ER_LOCAL_VARIABLE 1228 HY000
ER_GLOBAL_VARIABLE 1229 HY000
ER_NO_DEFAULT 1230 42000
ER_WRONG_VALUE_FOR_VAR 1231 42000
ER_WRONG_TYPE_FOR_VAR 1232 42000
ER_VAR_CANT_BE_READ 1233 HY000
ER_CANT_USE_OPTION_HERE 1234 42000
ER_NOT_SUPPORTED_YET 1235 42000
ER_MASTER_FATAL_ERROR_READING_BINLOG 1236 HY000
ER_WRONG_FK_DEF 1237 42000
ER_KEY_REF_DO_NOT_MATCH_TABLE_REF 1238 HY000
ER_CARDINALITY_COL 1239 21000
ER_SUBSELECT_NO_1_ROW 1240 21000
ER_UNKNOWN_STMT_HANDLER 1241 HY000
ER_CORRUPT_HELP_DB 1242 HY000
ER_CYCLIC_REFERENCE 1243 HY000
ER_AUTO_CONVERT 1244 HY000
ER_ILLEGAL_REFERENCE 1245 42S22
ER_DERIVED_MUST_HAVE_ALIAS 1246 42000
ER_SELECT_REDUCED 1247 01000
ER_TABLENAME_NOT_ALLOWED_HERE 1248 42000
ER_NOT_SUPPORTED_AUTH_MODE 1249 08004
ER_SPATIAL_CANT_HAVE_NULL 1250 42000
ER_COLLATION_CHARSET_MISMATCH 1251 42000
ER_SLAVE_WAS_RUNNING 1252 HY000
ER_SLAVE_WAS_NOT_RUNNING 1253 HY000
ER_TOO_BIG_FOR_UNCOMPRESS 1254 HY000
ER_ZLIB_Z_MEM_ERROR 1255 HY000
ER_ZLIB_Z_BUF_ERROR 1256 HY000
ER_ZLIB_Z_DATA_ERROR 1257 HY000
ER_CUT_VALUE_GROUP_CONCAT 1258 HY000
ER_WARN_TOO_FEW_RECORDS 1259 01000
ER_WARN_TOO_MANY_RECORDS 1260 01000
ER_WARN_NULL_TO_NOTNULL 1261 01000
ER_WARN_DATA_OUT_OF_RANGE 1262 01000
ER_WARN_DATA_TRUNCATED 1263 01000
ER_WARN_USING_OTHER_HANDLER 1264 01000
ER_CANT_AGGREGATE_COLLATIONS 1265 42000
ER_DROP_USER 1266 42000
ER_REVOKE_GRANTS 1267 42000



Throw love 2 weeks ago

MySQL backup and recovery
<script language="javascript" type="text" src="misc/cr.js" /> </ script>

As / Translator: Ye Jinrong (Email:), Source: http://imysql.cn, reproduced, please indicate make / translator and the source, and can not be used for commercial purposes, rights reserved.

Date: 2006/10/01

This article discusses the MySQL backup and recovery mechanisms, and how to maintain the data sheet, including the most important of the two table types: MyISAM and Innodb, the text in the design of MySQL version 5.0.22.

MySQL support is currently free backup tools: mysqldump, mysqlhotcopy, you can also backup with SQL syntax: BACKUP TABLE or SELECT INTO OUTFILE, or if the backup binary logs (binlog), you can directly copy the data files and associated configuration files. MyISAM tables are stored as files so that they are relatively easy to back up, the above-mentioned several methods can be used. Innodb tables are all stored in a data file ibdata1 in the same (and possibly more than one file, or a separate table space files), relatively good backup, free programs can copy data files, backup binlog, or use mysqldump.
1, mysqldump
1.1 Backup

mysqldump is the use of SQL-level backup mechanism, which will lead into a data table SQL script file between different versions of MySQL upgrade is relatively fit, which is the most common backup methods.
Mysqldump now speaking about some of the key parameters:

* - Compatible = name

It tells mysqldump, export the data and what kind of database or an older version of MySQL server compatible. Value can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of Basic syntax mysql database

  • Choose Hibernate or iBATIS has its reasons

    Hibernate features: Hibernate powerful database has nothing to do with good, O / R mapping ability, and if you are very proficient in Hibernate, but also for Hibernate to conduct an appropriate package, then your project will be the entire persistence lay

  • java read file

    java read documents in two ways: java.io and java.lang.ClassLoader When using the java.io, when java.lang.ClassLoader use it? (Note: If prior to read xml file java read file clearly aware of these two methods have been like! Can take much less to understa

  • Hibernate Mapping Types

    Hibernate mapping types divided into two categories: built-in mapping types and mapping types of customers. Built-in mapping types is responsible for some common Java types are mapped to the corresponding SQL type; In addition, Hibernate also allows users

  • Hibernate configuration parameters hibernate.hbm2ddl.auto

    Hibernate in the configuration file: <properties> <property name="hibernate.hbm2ddl.auto" value="create" /> </ properties> Parameter Description: validate load hibernate, the authentication to create a database t ...

  • JAVA EE JSP_JNDI

    dsfdsa http://lindows.javaeye.com/admin/blogs/213348 Tomcat 6 with the connection pool data source configuration http://www.blogjava.net/ec2008/archive/2008/07/19/216063.html project: test Driver path: D: \ workspace \ test \ WebRoot \ WEB-INF \ lib ...

  • Hibernate.cfg.xml configuration file (including the primary key generation strategy Introduction)

    Hibernate.cfg.xml configuration file: <? xml version = "1.0" encoding = "utf-8"?> <! DOCTYPE hibernate-configuration PUBLIC "- / / Hibernate / Hibernate Configuration DTD / / EN" "hibernate-configuration-2.0.dtd

  • The EJB3 Persistence

    EJB3 persistence with Hibernate is very similar to the mechanism: Environment: Server: JBOSS5.0 Database: MySQL5.0 1. Set up a data source First of all, in jboss-5.0.0.GA \ server \ default \ deploy, the establishment of a database used to connect the dat

  • can not be represented as java.sql.Timestamp

    Development of procedures for the use of hibernate when, some time there is no need to fill in the fields, but after the hibernate query time reported "Java.sql.SQLException: Value'0000-00-00 'can not be represented as java.sql.Timestamp ...

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries