databasesql

Iptables firewall protection Oracle Database

Use the following script to run oracle database can protect Linux host

Test environment: server RHEL AS3U3 + Oracle 10g client Windows 2000 sp4 + Oracle 9i
# # # # # # # # # # # # # # # # Oraclefirewall.sh # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#! / Bin / bash

LC_ADDR = 192.168.1.99
LO_ADDR = 127.0.0.1

iptables-F
# Clear the existing regulatory

iptables-P INPUT DROP
# Iptables-P OUTPUT DROP
# Set the default rule for INPUT and OUTPUT chain to DROP, not only does not allow any packets arriving at the local, but also allow local outsourcing

iptables-A INPUT-p ALL-s $ LC_ADDR-d $ LC_ADDR-j ACCEPT
iptables-A OUTPUT-p ALL-s $ LC_ADDR-d $ LC_ADDR-j ACCEPT
iptables-A INPUT-p ALL-s $ LO_ADDR-d $ LO_ADDR-j ACCEPT
iptables-A OUTPUT-p ALL-s $ LO_ADDR-d $ LO_ADDR-j ACCEPT
# Allow the unit and the contact must be open, or the listener does not start properly

iptables-A INPUT-p tcp – dport 22-j ACCEPT
iptables-A OUTPUT-p tcp – sport 22-j ACCEPT
# Add rules to allow inbound ssh connection

iptables-A INPUT-p tcp – dport 1521-j ACCEPT
iptables-A OUTPUT-p tcp – sport 1521-j ACCEPT
# Add a rule that allows inbound connection port 1521, which is to allow other hosts to access the machine through the listener database.
# If the listener is not listening port 1521, be revised accordingly

iptables-A INPUT-p tcp – dport 5560-j ACCEPT
iptables-A OUTPUT-p tcp – sport 5560-j ACCEPT
# Add rules to allow inbound port 5560 connection, that is, to allow other hosts to access the machine isqlplus

iptables-A INPUT-p tcp – dport 5500-j ACCEPT
iptables-A OUTPUT-p tcp – sport 5500-j ACCEPT
# Add rules to allow inbound port 55600 connected, that is, to allow other hosts to access the machine em
# # # # # # # # # # # # # # # # End of here # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

Posted by databasesql in Recommended

mysql character encoding

One, Windows
1 suspended MySQL service
2, under the MySQL installation directory to find my.ini If you do not put my-medium.ini replication for a my.ini can
Open the my.ini in the [client] and [mysqld] below plus default-character-set = utf8, save and close
4, start the MySQL service

Second, Linux
1, stop the MySQL service (bin / mysqladmin-u root shutdown)
2, my.cnf, if not the support-files directory under the MySQL installation directory put the my-medium.cnf under copied to / etc / under and renamed my.cnf can be found in / etc / under
Open the my.cnf [client] and [mysqld] below plus default-character-set = utf8, save and close
4, start the MySQL service (bin / mysqld_safe &)

If you are unfortunate enough to export the whole there are a lot of different encoded data table database, ok. The original variety of encoding replaced by the purpose of encoding you want to use the replace tool. I have here the original utf8 and latin1, so just need to Latin1 Replace All to utf8.

After the new database you want to import data:

view plaincopy to clipboardprint?
mysql> create database `TARGET_DB` default character set utf8 collate utf8_general_ci
mysql> create database `TARGET_DB` default character set utf8 collate utf8_general_ci

OK, now you can import the data sheet:

view plaincopy to clipboardprint?
mysql-uroot-p TARGET_DB – default-character-set = utf8 </ home / test / sends.sql
mysql-uroot-p TARGET_DB – default-character-set = utf8 </ home / test / sends.sql

Finally, with mysql the operation character set will be used when commands are collected from the Internet:

view plaincopy to clipboardprint?
Modify the MySQL database default character set (mysql database default character set)

alter database testdb default character set = gb2312;
Modify MySQL table default character set (mysql table default character set)

alter table ip_to_country default character set = gb2312;
Note: Modifying MySQL’s default character set, character data already stored at the database level, or data table level, no change. Just new table or column, start using the new character set.

Convert MySQL table character set (mysql table character set)

MySQL has a data table ip_to_country the previous character column of the table data are stored in utf8 encoding format, now want to convert GB2312 encoding format storage. Need to use the MySQL character set conversion command: convert to character set

alter table ip_to_country convert to character set gb2312;
Tip: convert to the data table ip_to_country the default character set will change from utf8 to gb2312.

Conversion MySQL table a column character set (mysql column character set)

alter table ip_to_country modify country_name varchar (50) character set gb2312;
This statement the a single out country_name the character set conversion for gb2312. This usage is relatively rare in practice, because in most cases, are changing the entire character set of the table.

From:
Modify the MySQL database default character set (mysql database default character set)

alter database testdb default character set = gb2312;
Modify MySQL table default character set (mysql table default character set)

alter table ip_to_country default character set = gb2312;
Note: Modifying MySQL’s default character set, character data already stored at the database level, or data table level, no change. Just new table or column, start using the new character set.

Convert MySQL table character set (mysql table character set)

MySQL has a data table ip_to_country the previous character column of the table data are stored in utf8 encoding format, now want to convert GB2312 encoding format storage. Need to use the MySQL character set conversion command: convert to character set

alter table ip_to_country convert to character set gb2312;
Tip: convert to the data table ip_to_country the default character set will change from utf8 to gb2312.

Conversion MySQL table a column character set (mysql column character set)

alter table ip_to_country modify country_name varchar (50) character set gb2312;
This statement the a single out country_name the character set conversion for gb2312. This usage is relatively rare in practice, because in most cases, are changing the entire character set of the table.

From:

view plaincopy to clipboardprint?
Create a database named test, and set the character set is UTF8:
create database `test` default character set utf8 collate utf8_general_ci

View ta similar test data table in the database:
show table status from ta like ‘% test%’;

Check the MySQL database server and database character set.
show variables like ‘% char%’;
+ ————————– + ———————- ————— + ——
| Variable_name | Value | ……
+ ————————– + ———————- ————— + ——
| Character_set_client | utf8 | …… – client character set
| Character_set_connection | utf8 | ……
| Character_set_database | utf8 | …… – database character set
| Character_set_filesystem | binary | ……
In | character_set_results | utf8 | ……
| The, character_set_server | utf8 | …… – server character set
At | character_set_system | utf8 | ……
| Character_sets_dir | D :/ MySQL Server 5.0/share/charsets / | ……

View the the MySQL data sheet (table) character set.

show table status from sqlstudy_db like ‘% countries%’;
+ ———– + ——– + ——— + ———— + —– – + —————– + ——
| Name | Engine | Version | ROW_FORMAT | Rows | Collation | ……
+ ———– + ——– + ——— + ———— + —– – + —————– + ——
| Countries | InnoDB | 10 | Compact | 11 | utf8_general_ci | ……

View the the MySQL data columns (column) character set.

show full columns from countries;
+ ———————- + ————- + ———— —– + ——–
| Field | Type | Collation | …….
+ ———————- + ————- + ———— —– + ——–
| Countries_id | int (11) | NULL | …….
| Countries_name | varchar (64) | utf8_general_ci | …….
The | countries_iso_code_2 | char (2) | utf8_general_ci | …….
The | countries_iso_code_3 | char (3) | utf8_general_ci | …….
| Address_format_id | int (11) | NULL | …….

View the current character set supported by the installation of MySQL.

Show charset; or
show char set;
+ ———- + —————————– + ——– ————- + ——– +
| Charset | Description | Default collation | Maxlen |
+ ———- + —————————– + ——– ————- + ——– +
| Big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| Dec8 | DEC West European | dec8_swedish_ci | 1 |
| Cp850 | DOS West European | cp850_general_ci | 1 |
| Hp8 | HP West European | hp8_english_ci | 1 |
| Koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| Latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| Latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| Swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| Ascii | U.S. ASCII | ascii_general_ci | 1 |
| Ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| Sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| Hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| Tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| Euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| Koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| Gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| Greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| Cp1250 | Windows Central European | cp1250_general_ci | 1 |
| Gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| Latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| Armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| Utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| Ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| Cp866 | DOS Russian | cp866_general_ci | 1 |
| Keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| Macce | Mac Central European | macce_general_ci | 1 |
| Macroman | Mac West European | macroman_general_ci | 1 |
| Cp852 | DOS Central European | cp852_general_ci | 1 |
| Latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| Cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| Cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| Cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| Binary | Binary pseudo charset | binary | 1 |
| Geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| Cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| Eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |

Modify the character set of tables and fields
/ / Modify the table
alter table table name convert to character set gbk;
/ / Modify the field
alter table table name modify Column ‘field name’ varchar (30) character set GBK NOT NULL;

Data references:

Posted by databasesql in Recommended

Three principal uses standard SQL update statement

Three principal uses standard SQL update statement
First, the environment:
MySQL-5.0.41-win32
Windows XP Professional
Second, create a test environment:
DROP TABLE IF EXISTS t_test;
The CREATE TABLE t_test (
bs bigint (20) NOT NULL auto_increment,
username varchar (20) NOT NULL,
Password varchar (20) default NULL,
remark varchar (200) default NULL,
PRIMARY KEY (bs)
) ENGINE = MyISAM AUTO_INCREMENT = 4 DEFAULT CHARSET = gbk;
INSERT INTO t_test VALUES (1, ‘lavasoft’, ‘123456 ‘, NULL);
INSERT INTO t_test VALUES (2, ‘hello’, NULL, NULL);
INSERT INTO t_test VALUES (3, ‘haha’, zz, tt);
Third, the test
1, set a field
The second record (bs) in the table t_test set password ‘***’.
update t_test t
set t.password = ‘***’
where t.bs = 2;
2, set multiple fields
The first record (bs) in the table t_test set password for ‘*’ remark as ‘*’.
update t_test t
set t.password = ‘*’, t.remark = ‘*’
where t.bs = 1;
3, set a null value
The third record (bs) in the table t_test set password is null, remark to null.
update t_test t
set t.password = null, t.remark = null
where t.bs = 3;
This is in accordance with the standard syntax to write in a different database system, update wording, but the standard wording are supported. The above three examples to explain the situation, updated every time a row. In practice, by the where clause constraints to control the number of updated rows.
Posted by databasesql in Recommended

Oracle data import and export imp / exp command

Oracle data import and export imp / exp on the equivalent of Oracle data restore and backup. exp command to export data from a remote database server to the local dmp file, imp command to import the dmp file from the local to the remote database server. With this feature you can build two identical databases, one for testing, one for official use.

Execution environment: in SQLPLUS.EXE or DOS (command line) to perform
DOS can be performed due to the to installation directory ora81BIN oracle 8i is set to the global path,
The directory the EXP.EXE IMP.EXE file is used to perform import and export.
oracle using java prepared the two files SQLPLUS.EXE EXP.EXE IMP.EXE may be packaged class files.
SQLPLUS.EXE call EXP.EXE the wrapped class IMP.EXE, to complete the import and export functions.

The following describes the instance of import and export.
Data Export:
1 full export of the database TEST, user name, password manager system exported to D: daochu.dmp the
exp file = d: daochu.dmp full = y
The database system user and sys users export a table
exp file = d: Owner of daochu.dmp = (system, sys)
3 tables in the database inner_notify, notify_staff_relat export
The the exp file = d: tables of datanewsmgnt.dmp = (inner_notify notify_staff_relat)

4 tables in the database table1 the field filed1 to “00” starts with the data export
the exp file = d: tables of daochu.dmp = (table1) query = “of where filed1 like ‘00% ‘”

The above is commonly used export compression winzip dmp file compression.
Can also be in the above command is followed by plus compress = y to.

Data import
1 D: daochu.dmp the data import TEST database.
the imp file = d: daochu.dmp
the imp full = y file = file = d: datanewsmgnt.dmp ignore = y
Above may be a little problem, because some table already exists, then it is an error, the table will not be imported.
In the back plus ignore = y.
2 d: daochu.dmp the table table1 import
the imp file = d: tables of daochu.dmp = (table1)

Basically at the import and export enough. Many first completely remove the table, and then import.

Note:
Operator to have sufficient permissions, the permission is not enough, it will prompt.
Can connect to the database. Can be used to the database test whether connected tnsping TEST.

Appendix 1:
Operations increased import the data to the user permissions
First, start SQL * puls
Second, log in as system / manager
Third, create user username IDENTIFIED BY password (If you have created a user, this step can be omitted)
Fourth, GRANT CREATE USER, DROP USER, ALTER USER, CREATE ANY VIEW,
DROP ANY VIEW, EXP_FULL_DATABASE, IMP_FULL_DATABASE,
DBA, CONNECT, RESOURCE, CREATE SESSION TO user name
Fifth, the run-cmd-enter the directory where the dmp file
imp userid = system / manager full = y file = *. dmp
Imp userid = system / manager full = y file = filename.dmp for

Execution Example:
F: WorkOracle_Databackup> imp userid = test / test full = y file = inner_notify.dmp

Screen Display
Import: Release 8.1.7.0.0 – Production on Thursday, February 16, 2006 16:50:05
(C) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 – Production
With the Partitioning option
JServer Release 8.1.7.0.0 – Production

Export file created by EXPORT: V08.01.07 via conventional path has been completed ZHS16GBK character set ZHS16GBK NCHAR character set import and export server uses UTF8 NCHAR character set (may ncharset conversion)
Being AICHANNEL objects into the AICHANNEL
.. Being imported table INNER_NOTIFY “4 rows imported to enable constraints …
Import terminated successfully with warnings.

Appendix II:
Oracle does not allow directly change the owner of a table, use the Export / Import can achieve this goal.
First create import9.par,
Then, use the command as follows: the imp parfile = / filepath/import9.par
The cases import9.par reads as follows:
FROMUSER = TGPMS
The users can TOUSER = TGPMS2 (Note: The owner of a table by FROMUSER to TOUSER, FROMUSER and TOUSER)
ROWS = Y
INDEXES = Y
GRANTS = Y
CONSTRAINTS = Y
BUFFER = 409600
file == / backup/ctgpc_20030623.dmp
log == / backup/import_20030623.log

Posted by databasesql in Recommended

MySQL frequently used commands

MySQL frequently used commands show databases; database create database name; create a database use databasename; to select database drop database name directly delete the database, do not remind the show TABLES; display table describe tablename; specific table structure select distinct plus remove duplicate field mysqladmin drop databaseName delete the database before tips.
Display the current MySQL version and the current date select version (), CURRENT_DATE;
Modify the mysql root password:
shell> mysql-h localhost-u root-p / / login mysql> update user set password = the password (xueok654123) WHERE User = ‘root’;
mysql> FLUSH privileges / / refresh the database mysql> use dbname; open the database:
mysql> show databases; database mysql> show tables; Show all tables in the database mysql: first where USE mysql; then MySQL> describe user; displayed in the user table in the mysql database table column information);
grant
The create the user firstdb (password to firstdb) and databases, and give permission in firstdb database mysql> create database firstdb;
mysql> grant all on firstdb. * to firstdb identified by ‘firstdb’
Will automatically create a user firstdb
mysql default is the local host is localhost, the corresponding IP address is 127.0.0.1, so you use your IP address to log in to be wrong, if you want to use your IP address to log must first be authorized with the grant command.
mysql> grant all on *. * to identified by “123456”;
Description: between grant and on a variety of privileges, such as: insert, select, update, etc. on the database and table names after the first * means that all databases, the second * table root can be changed to your user name, @ with the domain name or IP address, identified by the back of the login password can be omitted, the default password is empty password.
drop database to firstdb that;
Create a connect to the server from anywhere a full super-user, but you must use a password to do something this mysql> grant all privileges on *. * To identified by ‘something’ with
Increase new user format: Grant select on database * to username @ log into the host identified by “password”
GRANT ALL PRIVILEGES ON *. * TO IDENTIFIED BY ‘something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *. * TO “IDENTIFIED BY ‘Something’ WITH GRANT OPTION;
Remove authorized:
mysql> revoke all privileges on *. * from “;
mysql> delete from user where user = “root” and host = “%”;
mysql> flush privileges;
Create a user custom in particular client it363.com login, you can access a particular database fangchandb
mysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom@it363.com identified by ‘passwd’
Rename table:
mysql> alter table t1 rename t2;
mysqldump
Backup database shell> mysqldump-h host-u root-p dbname> dbname_backup.sql
Restore the database shell> mysqladmin-h myhost-u root-p create dbname
shell> mysqldump-h host-u root-p dbname <dbname_backup.sql
If you want to unloading create table instruction, the command is as follows:
shell> mysqladmin-u root-p-d databasename> a.sql
If you just want to unload the SQL command to insert data, without the need to create the table command, the command is as follows:
shell> mysqladmin-u root-p-t databasename> a.sql
So if I only want the data, but you do not want what SQL commands, how does it work?
mysqldump-T. / phptest driver
Of these, only specified the-T parameter before unloading a plain text file, which means that the unloading data directory, / represents the current directory, and mysqldump the same directory. If you do not specify a driver table will be discharged to the data of the entire database. Each table will generate two files, one for sql file contains a built tables. Another txt file contains only data, and not the sql command.
Queries can be stored in a file and tell mysql to read from the file query instead of waiting for keyboard input. Available shell type redirect utility to get the job done. For example, if stored in the file my_file.sql queries, these queries can be executed as follows:
For example, if you want to write-ahead will be built table statement in sql.txt
mysql> mysql-h myhost-u root-p
Supported character set Mysql5.0 MySQL character set control to do a fine job, can be divided into the database, table, and field-level (and ORACLE). The last time I changed the character set of the database level, the table sysuser no impact, so change the character set but the same can not be inserted in Chinese.
Drop TABLE IF EXISTS `firstdb`. `Users`;
Create TABLE `firstdb`. `Users` (
`Id` int (11) NOT NULL auto_increment,
`Username` varchar (40) default NULL,
`Birthday` date default NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = gb2312;
When compiling MySQL, specify a default character set, the character set is latin1;
Install MySQL, a default character set specified in the configuration file (my.ini), if not specified, this value is inherited from the compile-time specified;
Start mysqld command line parameters to specify a default character set, if not specified, this value is inherited from the configuration file;
The this time character_set_server is set to the default character set;
When you create a new database, unless explicitly specified, the database character set is the default setting, character_set_server;
When a database is selected, character_set_database is set as the default character set of the database;
When you create a table in the database, the table default character set is set to character_set_database is the default character set of the database;
When a column in the table set, unless otherwise expressly provided in this column default character set is the default character set;
This character set is the character set of the data is actually stored in the database using mysqldump out is the character set under too bad; Query Browser1.1 to support Chinese input, you can use the notebook was written, and then copy over the implementation of the update firstdb.users set username = ” where id = 3;
MYSQL commonly used commands to export the entire database mysqldump-u username-p – default-character-set = latin1 database export file name (database default encoding is latin1)
mysqldump-u wcnc-p smgp_apps_wcnc> wcnc.sql
Export a table mysqldump-u username-p database watches name> Export file name mysqldump-u wcnc-p smgp_apps_wcnc users> wcnc_users.sql
3. Export a database structure mysqldump-u wcnc-p-d-add-drop-table smgp_apps_wcnc> d: wcnc_db.sql
-D data-add-drop-table add a drop table before each create statement
4 into the database A: common source command console, enter the mysql database
Such as mysql-u root-p
mysql> use database and then use the source command behind parameters of script files (such as used here. sql)
mysql> source wcnc_db.sql
B: Using mysqldump command mysqldump-u username-p dbname <filename.sql
C: Using the mysql command mysql-u username-p-D dbname <filename.sql
Start and exit, to enter MySQL: start the MySQL Command Line Client (DOS interface for MySQL), directly enter the password when the installation. The prompt is: mysql>
2, exit MySQL: quit or exit
Second, the library operation, create a database command: CREATE database <database name>
The named xhkdb For example: Create a database mysql> create database xhkdb;
2, all database commands: show databases (Note: the last s)
mysql> show databases;
3, delete the database command: drop database <database name>
For example: delete named xhkdb database mysql> drop database xhkdb;
4, connect to the database command: USE <database name>
For example: If the database xhkdb exists, try to access it:
mysql> use xhkdb;
Screen Tip: Database changed
5, the currently used database mysql> select database ();
6, this database contains a table:
mysql> show tables; (Note: The last s)
Table operation, the operation should be connected a database to create the table command: the create table <table name> (<field name> <type> .. <field name> <type>]);
mysql> create table MyClass (
> Id int (4) not null primary key auto_increment,
> Name char (20) not null,
> Sex int (4) not null default ‘0 ‘,
>-Degree double (16,2));
2, Get a Table Structure command: desc table name, or show columns from table name mysql> DESCRIBE MyClass
mysql> desc MyClass;
mysql> show columns from MyClass;
3, delete table command: drop table <table name>
For example: Delete table named MyClass table mysql> drop table MyClass;
4, insert data command: insert into <table name> [(<field name 1> .. <field name n>])] values ??(value 1)
(N)]
For example, to insert two records in Table MyClass two records: the No. 1 named Tom score of 96.45, No. 2 named Joan score of 82.99, number 3 named Wang’s results was 96.5 .
mysql> insert into MyClass values ??(1, ‘Tom’, 96.45), (2, ‘Joan’, 82.99),
(2, ‘Wang’, 96.59);
5, the data in the query table 1), the query all rows command: select <field 1, field 2, …> from <table name> where <expression>
For example: View all the data in the table MyClass mysql> select * from MyClass;
2), the query first few lines of data such as: Table MyClass first 2 rows of data mysql> select * from MyClass order by id limit 0,2;
Or:
mysql> select * from MyClass limit 0,2;
6, delete the data in the table command: delete from table name where expressions such as: Delete table MyClass No. 1 mysql> delete from MyClass where id = 1;
7, modify the data in the table: update table name set field = new value … where conditions mysql> update MyClass set name = ‘Mary’ where id = 1;
7, to increase the field in the table:
Command: alter table table name add field type;
In example: Table MyClass Add field passtest, type int (4) default value is 0
mysql> alter table MyClass add passtest int (4) default ‘0 ‘
8, change the name of the table:
Command: Rename the table to the original table name to the new name of the table;
For example: in the table MyClass name changed to YouClass
mysql> rename table MyClass to YouClass;
Update the field content update table set field name = new content update table set field name = replace (field name, ‘old’ and ‘new’);
The article added in front of the four spaces Update article set Content = concat (”, content);
The field the type 1.INT [(M)] type: normal size integer type 2.Double [(M, D)] type [ZEROFILL]: normal size (double-precision) floating-point the digital type 3.DATE Date Type: support 1000-01-01 to 9999-12-31. MySQL to YYYY-MM-DD
Format displays DATE values, but allows you to use string or numeric value is assigned to the type of the DATE column 4.CHAR (M): fixed-length string type, when storage is always filled with spaces on the right to the specified length 5.BLOB TEXT type, maximum length of 65535 (2 ^ 16-1) characters.
The 6.VARCHAR type: variable-length string type 5 into the database table (1) create a sql file (2) first generated a library auction.c: mysqlbin> mysqladmin-u root-p creat
auction will be prompted to enter a password, and then successfully created.
(2) the import auction.sql file c: mysqlbin> mysql-u root-p Auction <auction.sql,.
Through the above operation, you can create a database of auction and one of the tables auction
.
Modify the database (1) an increase in the mysql table field:
alter table dbname add column userid int (11) not null primary key
auto_increment;
The, Table dbname a field userid of type int (11).
7.mysql database authorized mysql> grant select, insert, delete, create, drop
on *. * (or test * / user * / ..)
to username @ localhost
IDENTIFIED BY ‘password’;
Such as: create a new user account so that you can access the database, you need to proceed as follows:
mysql> grant usage
-> ON test.
-> TO ;
Query OK, 0 rows affected (0.15 sec)
Since then create a new user called: testuser, the user can only connect from localhost to the database and can be connected to the test database. Next, we must specify what you can do testuser this user:
mysql> GRANT select, insert, delete, update
-> ON test.
-> TO ;
Query OK, 0 rows affected (0.00 sec)
This makes testuser can be performed on a table in a test database Select, Insert, and Delete, and Update query operation. Now we finish and exit the MySQL client program:
mysql> exit
Bye9!
1: Use the SHOW statement to find out what database exists on the server:
mysql> SHOW DATABASES;
2:2, create a database MYSQLDATA
mysql> Create DATABASE MYSQLDATA;
3: Select you create a database mysql> USE MySQLdata; (Database changed, press the Enter key instructions!)
4: View the database table mysql> SHOW TABLES;
5: Create a database table mysql> CREATE TABLE mytable (name varchar (20), sex CHAR (1));
6: shows the structure of the table:
mysql> DESCRIBE MYTABLE;
7: records added to the table mysql> insert into mytable values ??(“hyq”, “M”);
8: text data into a database table (for example the D :/ mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D :/ mysql.txt” INTO TABLE MYTABLE;
9: Importing sql file commands (for example the D :/ mysql.sql)
mysql> use database;
mysql> source d :/ mysql.sql;
10: Delete table mysql> drop TABLE mytable;
11: Empty the table mysql> delete from MYTABLE;
12: Update table data mysql> update mytable set sex = “f” where name = ‘hyq’;
The following is inadvertently see the use of MySQL in the network management experience,
Taken from:
html
Exist prior to use to ensure that this service has been launched, not started available net Start mysql command to start a service in Windows in MySQL. Available in the Linux start “/ etc / rc.d / init.d / mysqld
start “command, note that the start should have administrator privileges.
Just installed MySql contains a root account with a blank password and an anonymous account, which is a big security risk for some important applications security should be to maximize, where the anonymous account should delete the root account Set the password be ordered as follows:
use mysql;
delete from User where User = “”;
update User set Password = PASSWORD (‘newpassword’) where User = ‘root’;
If you want to restrict user login terminal, the user can update the User table in the Host field,
After the above changes should restart the database service available, log on at this time similar to the following command:
mysql-uroot-p;
mysql-uroot-pnewpassword;
mysql mydb-uroot-p;
mysql mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, detailed reference documentation. Where mydb is the name of the database you want to log.
During the development and practical application, the user should not only use the root user to connect to the database, use the root user test is very convenient, but the system will bring significant security risks, is not conducive to management techniques improve. We give the user an application used to give the most appropriate database permissions. Such as a data insertion, the user should not be given to delete data. MySQL user management through the User table, there are two ways to add new users, and set the appropriate permissions in the User table into the corresponding data line; GRANT command to create a permission users. GRANT common usage is as follows:
Grant all on mydb * to identified by “password”;
Grant usage on *. * to identified by “password”;
Grant select, insert, update on the mydb * to identified
by “password”;
of the Grant update, delete on mydb.TestTable to identified
by “password”;
To give the user gives his authority on the corresponding object management capabilities can be added behind the GRANT WITH
GRANT OPTION option. Inserted in the User table to add user Password field application PASSWORD
The function update encryption, see the password to prevent the evil designs stolen. For those who have not should be given clear, user permissions overspill should be timely recovery of permissions recovery permissions can update the User table fields, you can use the REVOKE operation.
I obtained from other data ( ) interpretation of the common permission is given below :
Global administrative privileges:
FILE: read and write files in the MySQL server.
PROCESS: display or kill service threads that belong to other users.
RELOAD: Reload access control table, refresh the log, and so on.
Shutdown: Close MySQL service.
Database / data tables / columns of data permissions:
Alter: To modify an existing data table (for example, add / remove columns) and indexes.
Create: create a new database or data table.
Delete: delete table records.
Drop: delete the data table or database.
INDEX: create or delete indexes.
Insert: increase records in the table.
Select: / search records in the table.
Update: modify existing records in the table.
Special privileges:
ALL: allow to do anything (and root).
USAGE: only allowed to log – Other not allowed to do.
———————
MYSQL commonly used commands have many friends installed mysql but do not know how to use it. In this article we to learn from the connection MYSQL, change passwords, increase user some MYSQL common command.
There are a lot of friends installed mysql but do not know how to use it. In this article we to learn from the connection MYSQL, change passwords, increase user some MYSQL common command.
First, the connection MYSQL
Format: mysql-H host address-u username-p user password 1, Example 1: Connecting to MySQL on the machine
First, open the DOS window, then enter directory mysqlbin, and then type the command mysql-uroot-p
Enter prompt you lose the password, If you have just installed MYSQL, super-user root without a password, so you can directly enter into the MYSQL, MYSQL prompt is: mysql>
Example 2: Connecting to MySQL on remote host
Assuming that the remote host’s IP: 110.110.110.110, user called root password to abcd123. Type the following command:
mysql-h110.110.110.110-uroot-pabcd123
(Note: u and the root can not have spaces, other)
3, exit MYSQL command: exit (Enter)
Second, modify the password format: mysqladmin-u username-p password old password new password 1, Example 1: to add a root password ab12. First into the directory mysqlbin under DOS, and then type the following command mysqladmin-uroot-password ab12
Note: Since the beginning of root has no password,-p old password one can be omitted.
2, Example 2: then the root password to djg345
mysqladmin-uroot-pab12 password djg345
MySQL frequently used commands (below)
Operating skills, if you hit command, a carriage return after the discovery forget the semicolon, you do not need to retype the command, you can make a semicolon Enter. This means that you can put a complete command into several lines to fight, finished with a semicolon marks the end OK.
2, you can use the cursor up and down keys to recall the previous command. But before I used an older version of MYSQL is not supported. I am using the mysql-3.23.27-beta-win.
Display command to display the list of databases.
show databases;
Beginning when the two databases: mysql, and test. the mysql library very important it MYSQL system information, change your password and add users, in fact, use this library to operate.
2, the display of the data in the table in the library:
USE mysql; / / open the library, learned FOXBASE certainly not familiar with it show tables;
3, shows the data structure of the table:
describe table;
4, building a database:
create database library name;
5, the construction of the table:
use the library name;
create table table name (field set list);
6, delete the database and delete the table:
drop database library name;
drop table table name;
7 records in the table empty:
delete from table name;
8 records in the table:
select * from table name;
Third, a database and build tables and insert data into an instance of drop database if exists school; / / If there SCHOOL deleted the create database School; / / create a library SCHOOL
Use school; / / open the library SCHOOL
create table teacher / / create table teacher
(
id int (3) auto_increment not null primary key,
name char (10) not null,
Address varchar (50) default ‘Shenzhen’,
year date
); / / End of the construction of the table / / Insert Field insert INTO Teacher values ??(”, ‘glchengang’, ‘Shenzhen’, ‘1976 -10-10 ‘);
INSERT INTO Teacher values ??(”, ‘jack’, ‘Shenzhen’, ‘1975 -12-23 ‘);
Note: (1) in the construction of the table the ID is set to numeric field with a length of 3 automatically incremented by one: int (3) and allow it to each record: auto_increment and can not be null: not null and let him become the primary key of the primary field
(2) NAME character field with a length of 10 (3) ADDRESS character field of length 50, and the default value Shenzhen. varchar and char
What difference does it say, only wait for a future article.
(4) YEAR set the date field.
If you’re in the mysql prompt, type the command above can be, but is not convenient debugging. You can be written as the above command is assumed to be school.sql a text file, and then copied to the C: \ in DOS mode to enter the directory \ mysql \ bin, and then type the following command:
mysql-uroot-p password <c: \ school.sql
If successful, vacated his no display; any errors will be prompt. (The above command has been commissioning, as long as you / / uncomment to use).
Fourth, the text data to the database, text data should be consistent with the format: field data separated by a tab key, null value \ n instead.
Example:
3 Rose Shenzhen Second 1976-10-10
Mike Shenzhen 1975-12-23
2, data incoming command load data local infile “file name” into table table name;
Note: you’d better copy the files to the \ mysql \ bin directory, and first hit the table where the library with the use command.
Backup database: (command in DOS \ mysql \ bin directory)
mysqldump – opt school> school.bbb
Note: backup the database School to school.bbb file school.bbb is a text file,
The file name of any check, open look you have found.
A Select statement of the complete syntax:
Select [ALL | DISTINCT | DISTINCTROW | TOP]
{* | Talbe. * | [Table.] Field1 [AS alias1] [, [table.] Field2 [AS alias2] [, …]]}
FROM tableexpression [, …] [IN externaldatabase]
[Where …]
[GROUP BY …]
[HAVING …]
[ORDER BY …]
[The WITH OWNERACCESS OPTION]
Description:
Part of said enclosed in brackets ([]) are optional, curly braces ({}) enclose part is said to have to choose one of them.
1 FROM clause The FROM clause specifies the source of the field in the Select statement. FROM clause is followed by one or more expressions (separated by commas), in which the expression can be a single table name, saved queries or INNER
JOIN, LEFT JOIN or RIGHT JOIN composite results. If the table or stored in an external database, after the IN clause to specify the full path.
Example: The following SQL statement returns all customer orders:
Select orderID, Customer.customerID
FROM orders Customers
Where orders.CustomerID = Customers.CustomeersID
2 ALL, DISTINCT, DISTINCTROW, TOP predicate (1) ALL to return all the records that meet the conditions of the SQL statement. If the predicate is not specified, the default is ALL.
Example: Select ALL FirstName, LastName
FROM Employees
If there are multiple records (2) DISTINCT select the field data, returns only one.
(3) DISTINCTROW If there are duplicate records, returns only one (4) TOP query the head and tail of certain records. Also return the percentage of records, which is to use TOP N
PERCENT clause (where N denotes the percentage)
Example: Back to the maximum order of 5% of the order amount Select TOP 5 PERCENT *
From [order Details]
orDER BY UnitPrice * Quantity * (1-Discount) DESC
AS clause for a field alias if you want to take a new title for the returned column, or after a calculation or summary of the field, resulting in a new value, put it in a new column AS reserved.
Example: return to the FirstName field alias NickName
Select FirstName AS NickName, LastName, City
FROM Employees
Example: Return a new inventory value Select ProductName, UnitPrice, UnitsInStock, UnitPrice * UnitsInStock AS
valueInStock
FROM Products
Comparison Operators Comparison operators meaning of the WHERE clause specifies query = equal to> greater than <less than> = greater than or equal to <= less than or equal to <> not equal to> greater than <less than Example: Back to January 96 order Select orderID, CustomerID, orderDate
FROM orders
Where orderDate> # 1/1/96 # AND orderDate <# 1/30/96 #
Note:
Mcirosoft JET SQL, date delimitation of the ‘#’. Date can also use the DateValue () function instead.
In comparison to the character data to be enclosed in single quotes”, trailing spaces in the comparison is ignored.
Example:
Where orderDate> # 96-1-1 #
Can also be expressed as:
WHERE OrderDate> DateValue (‘1 / 1/96 ‘)
NOT expression negates.
Example: See the order after January 1, 1996 Where Not orderDate <= # 1/1/96 #
2 range (BETWEEN and NOT BETWEEN)
BETWEEN … AND … operator to specify you want to search a closed interval.
Example: Back orders from 96 January to 96 February.
Where orderDate Between # 1/1/96 # And # 2/1/96 #
3 list (IN, NOT IN)
IN operator is used to match any of the values ??in the list. IN clause instead of the OR clause connected to a series of conditions.
Example: To find all of our customers live in London, Paris or Berlin Select CustomerID, CompanyName, ContactName, City
FROM Customers
Where City In (‘London’, ‘Paris’, ‘Berlin’)
4 pattern matching (LIKE)
LIKE operator test a string data field value matches a specified pattern.
LIKE operator wildcard wildcard meaning? Any single character * any length character # single digits between 0 and 9 [list of characters in the character list any of the values ??[! List of characters is not in the list of characters any value – the specified range of characters on both sides of the value of their respective upper and lower cases: Back zip code (171) 555-0000 (171) 555-9999 between customers Select CustomerID, CompanyName, City, Phone
FROM Customers
Where Phone Like ‘(171) 555 – # # # #’
LIKE operator does not meet the meaning of style and meaning of style LIKE ‘A *’ A followed by any character length BC, C255
LIKE’5 [*] ‘5 * 5 555
LIKE’5? 5 ‘5 and 5 between any of the characters 55,5 wer5
LIKE’5 # # 5 ‘5235,5005 5kd5, 5346
LIKE ‘[az]’ the AZ between any one character 5%
LIKE ‘[0-9]’ non 0-9 between any one character 0,1
LIKE ‘[[]’ 1, *
Field sort query results by one or more (up to 16) with the ORDER BY clause to sort results order clause, is ascending (ASC) or descending (DESC), the default is ascending. The ORDER clause usually placed the last of the SQL statement.
The ORDER clause defines multiple fields, sorted in accordance with the order of the field.
Example:
Select ProductName, UnitPrice, UnitInStock
FROM Products
orDER BY UnitInStock DESC, UnitPrice DESC, ProductName
ORDER BY clause in the choice of field position number instead of the field names in the list, can be mixed field name and location number.
Example: The following statement produces the same effect as the above.
Select ProductName, UnitPrice, UnitInStock
FROM Products
orDER BY 1 DESC, 2 DESC, 3
Use the connection relationship to achieve multi-table query example: to find the name Select suppliers and customers in the same city Customers.CompanyName Suppliers.ComPany.Name
FROM Customers, Suppliers
Where Customers.City = Suppliers.City
Example: to identify the number of product inventory is greater than the same product orders and order Select ProductName, OrderID, UnitInStock, Quantity
FROM Products, [Order Deails]
Where Product.productID = [Order Details]. ProductID
AND UnitsInStock> Quantity
Another method is unique with Microsof JET SQL JNNER JOIN
Syntax:
FROM table1 INNER JOIN table2
ON table1.field1 comparision table2.field2
Which comparision front WHERE clause is used in the comparison operator.
Select FirstName, lastName, OrderID, CustomerID, OrderDate
FROM Employees
INNER JOIN orders ON Employees.EmployeeID = Orders.EmployeeID
Note:
INNER JOIN can not connect Memo OLE Object Single Double data type field.
In a JOIN statement in connection multiple ON clauses syntax:
Select fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2 or
ON table1.field3 compopr table2.field3
Select fields
FROM table1 INNER JOIN
(Table2 INNER JOIN [(] table3
[INNER JOER] [(] tablex [INNER JOIN]
ON table1.field1 compopr table2.field1
ON table1.field2 compopr table2.field2
ON table1.field3 compopr table2.field3
External connection to return more records retained in the results do not match the record, no matter it exists or not meet the conditions of record must return all the records of the other side.
FROM table [LEFT | RIGHT] JOIN table2
ON table1.field1comparision table.field2
Left connected to create an external connection, the expression table on the left will display all cases: whether or not to set the volume to return all goods Select the ProductName, OrderID
FROM Products
LEFT JOIN orders ON Products.PrductsID = Orders.ProductID
Right left difference is that there is no matching record: Regardless of the left list, it returns all records from the table on the left.
Example: If you want to understand the customer’s information and statistical distribution of customers in various regions, can be connected with a right, even if a region has no customers, but also returned to the client.
Null values ??do not match each field of a table can be connected through the outer join to test for null values.
Select *
FROM talbe1
LEFT JOIN table2 ON table1.a = table2.c
Join queries using Iif function to achieve a value of 0 empty value IIF expression: IIf (IsNull (Amount, 0, AMOUT)
Example: Regardless of orders greater than or less than ¥ 50, and must return a flag.
Iif ([Amount]> 50,? Big order?,? Small order?)
Grouping and summary of results in the syntax of SQL, GROUP BY and HAVING clause is used to summarize the data. GROUP BY clause specified in accordance with which several fields to group records into groups with the HAVING clause to filter records.
The the syntax Select fidldlist of the GROUP BY clause
FROM table
Where criteria
[GROUP BY groupfieldlist [HAVING groupcriteria]]
Note: The Microsoft Jet database Jet can not be grouped Memo or OLE Object field.
Null value in the GROUP BY field for grouping, however, can not be omitted.
Null values ??are not calculated in any SQL aggregate functions.
GROUP BY clause can be up with ten fields, the sort priority arranged left to right.
Example: ‘WA’ employees table grouped by title, to identify the number of employees with the same title more than 1 title.
Select Title, COUNT (Title) as Total
FROM Employees
Where Region = ‘WA’
GROUP BY Title
HAVING Count (title)> 1
Jet SQL in the accumulation function gathered function significance SUM () the summation AVG () average COUNT () the number of records in the expression COUNT (*) calculate the number of records MAX MAX MIN minimum VAR variance the STDEV standard error FIRST values ??LAST last value 6. Parameters statement to create a parameter query parameters declaration syntax:
PARAMETERS name datatype [, name datatype [, …]]
Where name is the identifier of the parameter can be referenced by identifier parameter.
Datatype description of the data type of the parameter.
Use the PARAMETERS statement should be placed before any other statements.
Example:
PARAMETERS [Low price] Currency, [Beginning date] datatime
Select orderID, OrderAmount
FROM orders
Where orderAMount> [low price]
AND OrderDate> = [Beginning date]
Function queries the so-called functional query, in fact, is an action query, it can be fast and efficient operation of the database select query for the purpose, the selection of a qualified data, batch data function query includes update query, delete query, add a query, and make-table query.
An update query the Update clause can change the data in one or more tables It is also possible to change the value of multiple fields at the same time.
Update query syntax:
UPDATE table name SET new value Where criteria Example: UK customers given a 5% increase in volume, cargo volume increased by 3%
Update OEDERS
SET orderAmount = orderAmount * 1.1
Freight = Freight * 1.03
Where ShipCountry = ‘UK’
Delete query the Delete clause allows the user to delete a large number of obsolete or redundant data.
Note: The the delete query object is the entire record.
Delete clauses syntax:
Delete [table name. *]
The FROM source table Where criteria Example: To delete all of 94 years ago, the order Delete *
FROM orders
Where orderData <# 94-1-1 #
3 append query Insert clause one or a group of records can be appended to the end of the one or more tables.
INTO clause specifies to accept a new record in the table VALUES keyword to specify the data values ??included in the new record.
Insert clauses syntax:
INSETR INTO destination table or query (Field 1, Field 2, …)
values ??(value 1, value 2, …)
For example: add a client INSERT INTO Employees (FirstName, LastName, title)
valueS (‘Harry’, ‘Washington’, ‘Trainee’)
4-table query once all the records meet the conditions copied to a new table. Usually made a backup or copy of the record or as the basis of the report.
SELECT INTO clause is used to create a make-table query syntax:
Select Field 1, Field 2, …
INTO table [IN external database]
The FROM source database Where criteria Example: order to create an archive backup Select *
INTO ordersArchive with
FROM orders
Eight union query the UNION operator the results of multiple queries can be combined into a result set.
The UNION operator of general syntax:
[Table] query UNION [ALL] query UNION …
Example: return the name and city of the Brazilian suppliers and customers Select CompanyName, City
FROM Suppliers
Where Country = ‘Brazil’
UNION
Select CompanyName, City
FROM Customers
Where Country = ‘Brazil’
Note:
Case of default, the UNION clause does not return duplicate records. If you want to display all the records, you can add the the ALL option UNION operator requires query with the same number of field, but the field data type does not have to be the same.
Each query parameters can be grouped using the GROUP BY clause or HAVING clause in the order specified in order to display the returned data, you can use the end of the last query the OREER BY clause.
IX. Cross Sourcing cross-check sum of the data can be, on average, count or other sum calculation method, these data are grouped by two types of information: a display in the left portion of the table, and the other displayed in the top of the table.
Microsoft Jet SQL syntax to create a crosstab query with the TRANSFROM statement:
TRANSFORM aggfunction in
Select statement GROUP BY clause PIVOT in pivotfield are [IN (value1 [, value2 [, …]])]
Aggfounction refers to the SQL accumulation function,
Select statement as the title of the field,
GROUP BY grouping Description:
Pivotfield field or expression used in the query result set to create column headings, with the optional IN clause to limit its value.
The representative of value to create a fixed value of the column headings.
Example: Show the number of orders accepted by every employee in each quarter of 1996:
Transform Count (OrderID)
Select FirstName &” & LastName AS FullName
FROM Employees INNER JOIN orders
ON Employees.EmployeeID = orders.EmployeeID
Where DatePart (“yyyy”, OrderDate) = ‘1996 ‘
GROUP BY FirstName &” & LastName
orDER BY FirstName &” & LastName
POVOT DatePart (“q”, OrderDate) & ‘quarter’
X. A subquery can be understood as a set of query sub-query is a Select statement.
The value of the expression subquery returns a single value compared syntax:
Expression comparision [ANY | ALL | SOME (subquery)
Description:
ANY and SOME predicate is synonymous with comparison operators (=, <>, <>, <=,> =) used in conjunction with returns a Boolean value True or False.ANY of the mean expression returned by the subquery value of a series of by-side comparison, as long as a True produce results, the ANY test the return value of True (both the WHERE clause of the results), corresponding to the current record of the expression will enter the main query results All test the expression sub-query returns a series of value comparison produces a True result, came back to return the value True.
Example: main query returns unit price than any other discount greater than or equal to 25% of the unit price of all products Select * FROM Products
Where UnitPrice> ANY
(Select UnitPrice FROM [Order Details] Where Discount> 0.25)
2 Check the value of the expression matches a value in a set of values ??returned by the subquery syntax:
[NOT] IN (subquery)
Example: inventory value greater than or equal to 1000.
Select ProductName FROM Products
Where ProductID IN
(The Select PrdoctID FROM [Order Details]
Where UnitPrice * Quantity> = 1000)
3 detection sub-query returns any record syntax:
[NOT] EXISTS (subquery)
Example: EXISTS to retrieve customers Select ComPanyName, ContactName
FROM orders
Where EXISTS
(Select *
FROM Customers
Where Country = ‘UK’ AND
Customers.CustomerID = orders.CustomerID)
1: Use the SHOW statement to find out what database exists on the server:
mysql> SHOW DATABASES;
2:2, create a database MYSQLDATA
mysql> Create DATABASE MYSQLDATA;
3: Select you create a database mysql> USE MySQLdata; (Database changed, press the Enter key instructions!)
4: View the database table mysql> SHOW TABLES;
5: Create a database table mysql> CREATE TABLE mytable (name varchar (20), sex CHAR (1));
6: shows the structure of the table:
mysql> DESCRIBE MYTABLE;
7: records added to the table mysql> insert into mytable values ??(“hyq”, “M”);
8: text data into a database table (for example the D :/ mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D :/ mysql.txt” INTO TABLE MYTABLE;
9: Importing sql file commands (for example the D :/ mysql.sql)
mysql> use database;
mysql> source d :/ mysql.sql;
10: Delete table mysql> drop TABLE mytable;
11: Empty the table mysql> delete from MYTABLE;
12: Update table data mysql> update mytable set sex = “f” where name = ‘hyq’;
The following is inadvertently see the use of MySQL in the network management experience,
Taken from:
Exist prior to use to ensure that this service has been launched, not started available net Start mysql command to start a service in Windows in MySQL. Available in the Linux start “/ etc / rc.d / init.d / mysqld start” command, note that the start should have administrator privileges.
Just installed MySql contains a root account with a blank password and an anonymous account, which is a big security risk for some important applications security should be to maximize, where the anonymous account should delete the root account Set the password be ordered as follows:
use mysql;
delete from User where User = “”;
update User set Password = PASSWORD (‘newpassword’) where User = ‘root’;
If you want to restrict user login terminal, you can update the user in the User table Host field, during the above changes should restart the database service available, log on at this time similar to the following command:
mysql-uroot-p;
mysql-uroot-pnewpassword;
mysql mydb-uroot-p;
mysql mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, detailed reference documentation. Where mydb is the name of the database you want to log.
During the development and practical application, the user should not only use the root user to connect to the database, use the root user test is very convenient, but the system will bring significant security risks, is not conducive to management techniques improve. We give the user an application used to give the most appropriate database permissions. Such as a data insertion, the user should not be given to delete data. MySQL user management through the User table, there are two ways to add new users, and set the appropriate permissions in the User table into the corresponding data line; GRANT command to create a permission users. GRANT common usage is as follows:
Grant all on mydb * to identified by “password”;
Grant usage on *. * to identified by “password”;
Grant select, insert, update on mydb * to identified by “password”;
Grant update, the delete on mydb.TestTable to identified by “password”;
To give the user gives his authority on the corresponding object management capabilities can be added behind the GRANT WITH GRANT OPTION option. Inserted in the User table to add user Password field application PASSWORD function to update encryption to prevent misconduct, theft see the password. For those who have not should be given clear, user permissions overspill should be timely recovery of permissions recovery permissions can update the User table fields, you can use the REVOKE operation.
I obtained from other data ( ) interpretation of the common permission is given below :
Global administrative privileges:
FILE: read and write files in the MySQL server.
PROCESS: display or kill service threads that belong to other users.
RELOAD: Reload access control table, refresh the log, and so on.
Shutdown: Close MySQL service.
Database / data tables / columns of data permissions:
Alter: To modify an existing data table (for example, add / remove columns) and indexes.
Create: create a new database or data table.
Delete: delete table records.
Drop: delete the data table or database.
INDEX: create or delete indexes.
Insert: increase records in the table.
Select: / search records in the table.
Update: modify existing records in the table.
Special privileges:
ALL: allow to do anything (and root).
USAGE: only allowed to log – Other not allowed to do.
MySQL frequently used commands, create database name; create a database use databasename; select the database drop database name directly delete the database, do not remind the show TABLES; display table describe tablename; coupled with a detailed description of the table select distinct remove duplicate field mysqladmin drop the databasename to delete database before, there are tips.
Display the current MySQL version and the current date select version (), CURRENT_DATE;
2, modify the mysql root password:
shell> mysql-u root-p
mysql> update user set password = password (“xueok654123”) where user = ‘root’;
mysql> FLUSH privileges / / refresh the database mysql> use dbname; open the database:
mysql> show databases; database mysql> show tables; Show all tables in the database mysql: first where USE mysql; then MySQL> describe user; displayed in the user table in the mysql database table column information);
3, Grant
Create a connect to the server from anywhere a full super-user, but you must use a password to do something this mysql> grant all privileges on *. * To identified by ‘something’ with
Increase new user format: Grant select on database * to username @ log into the host identified by “password”
GRANT ALL PRIVILEGES ON *. * TO IDENTIFIED BY ‘something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *. * TO “IDENTIFIED BY ‘Something’ WITH GRANT OPTION;
Remove authorized:
mysql> revoke all privileges on *. * from “;
mysql> delete from user where user = “root” and host = “%”;
mysql> flush privileges;
Create a user custom in particular client it363.com login, you can access a particular database fangchandb
mysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom@it363.com identified by ‘passwd’
Rename table:
mysql> alter table t1 rename t2;
4, mysqldump
Backup database shell> mysqldump-h host-u root-p dbname> dbname_backup.sql
Restore the database shell> mysqladmin-h myhost-u root-p create dbname
shell> mysqldump-h host-u root-p dbname <dbname_backup.sql
If you want to unloading create table instruction, the command is as follows:
shell> mysqladmin-u root-p-d databasename> a.sql
If you just want to unload the SQL command to insert data, without the need to create the table command, the command is as follows:
shell> mysqladmin-u root-p-t databasename> a.sql
So if I only want the data, but you do not want what SQL commands, how does it work?
mysqldump-T. / phptest driver
Of these, only specified the-T parameter before unloading a plain text file, which means that the unloading data directory, / represents the current directory, and mysqldump the same directory. If you do not specify a driver table will be discharged to the data of the entire database. Each table will generate two files, one for sql file contains a built tables. Another txt file contains only data, and not the sql command.
5, the query is stored in a file and tell mysql to read from the file query instead of waiting for keyboard input. Available shell type redirect utility to get the job done. For example, if stored in the file my_file.sql queries, these queries can be executed as follows:
For example if you want Create-table statement is advance written in sql.txt:
mysql> mysql-h myhost-u root-p database <sql.txt
/ / Start the service mysqld – console
/ / Stop the service mysqladmin-u root shutdown
/ / Log in to use the database mysql
mysql-u root-p mysql
mysql-u root-p-h 11.11.11.11 database
/ / Create database create database db_name [default character set = gbk]
/ / Set the database default character set alter databse db_name default character set gbk
/ / Replace the database use database test after log on
use test
/ / Create the image field with a table create a table mypic to store picture
create table mypic (picid int, picname varchar (20), content blob);
/ / Display the structure of the table describe table mypic
desc mypic
/ / Display the current table tabulation statement show create table table_name
/ / Change the table type alter table table_name engine innodb | myisam | memory
/ / Insert a record insert a record
insert into mypic VALUES (1, ‘second chapter’, 0x2134545);
/ / Current user show current user
select user ();
/ / Show current password of the current user password
select password (‘root’);
/ / Display the current date show Current date
select now ();
/ / Change the user password change user password
update user set password = password (‘xxx’) where user = ‘root’;
/ / Assign user permissions grant
grant all privileges on * the *
The of grant select, insert, delete, update, alter, create, drop on LyBBS. for * identified by LyBBS;
grant select, insert, delete, update, alter, create, drop on LyBBS * by LyBBS;
/ / Refresh user rights in the case of not restart flush privileges
flush privileges
/ / Add a primary key add primary key to the table
alter table mypic add primary key (picid)
/ / Modify table structure add a new field to add a new column userid after picid
alter table mypic add column userid int after picid
/ / Change the column type, when the stored image is too large, the default blob over, but 100k
alter table userpic change image image longblob;
alter table userpic modify image longblob;
/ / Set the default character set gb2312
mysqld – default-character-set = gb2312
/ / Display the details, including the character set encoding show full columns from userpic;
/ / Change the encoding of the table ALTER TABLE userpic CHARACTER SET gb2312;
/ / Mysql JDBC connection url Chinese jdbc: mysql :/ / localhost / test? UseUnicode = true & characterEncoding = gb2312
/ / Execute an external script source
MySQL is the most popular open source SQL database management system, developed by MySQL AB, publish, and support. MySQL AB is a commercial company based on the MySQL developers, is a successful business model to combine open source values ??and square **** second-generation open source company. MySQL is a registered trademark of MySQL AB.
MySQL is a fast, multi-threaded, multi-user and robust SQL database server. MySQL Server supports mission-critical, heavy-load production systems use, it can also be embedded in a large configuration (mass-deployed) software.

Posted by databasesql in Recommended

My sql run command line client program flash

My sql run command line client program flash
2012-05-11 2:18
Installation is complete, run the command line client program fleeting
MySQL Server 5.x installation directory to copy my the-small.ini configuration file, save it as my.ini
Run the command line client program can connect.
– Reproduced please indicate the sheep feather
Posted by databasesql in Recommended

SQL in a table two foreign keys from the same table to create the view

/ *****************************************
At — Geovin Du Tu Tsui Man association Friends of Computer Information Technology Co., Ltd.
— Group of Companies relatives of employees Referrals name. A table of two foreign keys from the same table
***************************************** /

– Employee table
IF EXISTS (select * from sysobjects where id = object_id (N ‘[dbo]. [Employeetest]’) and OBJECTPROPERTY (id, N’IsUserTable ‘) = 1)
The DROP TABLE [Employeetest,]
GO
create table Employeetest is
(
Eid INT IDENTITY (1,1) PRIMARY KEY,
Ename nvarchar (30) not null,

)
– Insert data
insert into Employeetest (Ename) values ??(‘geovindu’)
insert into Employeetest (Ename) values ??(‘geovindu1’)
insert into Employeetest (Ename) values ??(‘geovindu2’)
insert into Employeetest (Ename) values ??(‘geovindu3’)
insert into Employeetest (Ename) values ??(‘geovindu4’)
insert into Employeetest (Ename) values ??(‘geovindu5’)

— Internal Referrals table
IF EXISTS (select * from sysobjects where id = object_id (N ‘[dbo]. [Retationtest]’) and OBJECTPROPERTY (id, N’IsUserTable ‘) = 1)
The DROP TABLE [Retationtest,]
GO
create table Retationtest is
(
rid INT IDENTITY (1,1) PRIMARY KEY,
REkey int not null, – employees (Referrals) foreign key
REpkey int not null, – employees (employees) foreign key
ReationsName nvarchar (30) not null

)
– Insert
insert into Retationtest (REkey, REpkey, ReationsName) values ??(1,3, ‘sz’)

– Function
if exists (select * from dbo.sysobjects where id = object_id (N ‘[dbo]. [GetName]’) and xtype in (N’FN ‘, N’IF’, N’TF ‘))
drop function [dbo]. [GetName]
CREATE FUNCTION GetName
(
@ REpkey int – parameter
)
returns varchar (500)
as
begin
DECLARE @ ename nvarchar (50)
select _AT_ ename = Ename from Employeetest where

return @ ename
end
GO

– Test
declare @ ename nvarchar (50), @ REpkey int
set _AT_ REpkey = 2
select _AT_ ename = Ename from Employeetest where
select _AT_ ename

– View
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ‘View_Employeetest’)
DROP VIEW View_Employeetest is
GO
create view View_Employeetest is
as
Select RID AS ‘ID’, the Ename AS ‘Referrals name’, dbo.GetName (REpkey) AS ’employees name’ ReationsName AS ‘relations’ from Employeetest Retationtest
where Employeetest.Eid = Retationtest.REkey
– And Employeetest.Eid = Retationtest.REpkey
GO

– Show
select * from View_Employeetest

Posted by databasesql in Recommended

Do a good job for your database data dictionary

Database design, maintenance is as long as the problem, often to query data and view the original field defined what was meant. Use sp_help to look at it? Slow to have to recall the original intention of the half-day design. Start, as long as the design work to spare, you can very easy access to the original design of the database. First, we create a data table, give the table and each field with Chinese comments. As follows:

  1. CREATE TABLE Employee (
  2. EmpIDintNOT NULL, – employee number
  3. EmpName varchar (32), – the name of the employee
  4. EmpPhone varchar (32) – Employee phone
  5. EmpStateint– Employee Status [1: on-the-job 2: left]
  6. )
  7. ;
  8. ALTER TABLE Employee ADD CONSTRAINT PK_Employee
  9. Primary KEY clustered (empid)
  10. ;
  11. To EXEC sp_addextendedproperty‘MS_Description’, ‘staff table’, ‘User’, dbo, ‘table’, Employee
  12. ;
  13. EXEC sp_addextendedproperty‘MS_Description’, ‘Employee Number’, ‘User’, dbo, ‘table’ Employee, ‘column’, empid
  14. ;
  15. EXEC sp_addextendedproperty‘MS_Description’, ‘the staff Name’, ‘user’, dbo, ‘table’ Employee, ‘column’, EmpName
  16. ;
  17. The EXEC sp_addextendedproperty‘MS_Description’, ’employee telephone’, ‘User’, dbo, ‘table’ Employee, ‘column’, EmpPhone
  18. ;
  19. EXEC sp_addextendedproperty‘MS_Description’, ’employee status [1: on-the-job, 2: Separation]’, ‘User’, dbo, ‘table’ Employee, ‘column’, EmpState
  20. ;

Then, create a stored procedure, as follows:

  1. Create procedure DataDictionary_GetbyTableName
  2. @ TableName varchar (32)
  3. as
  4. select b. [name] Tablename, Isnull (c. [name], ‘Table’) ColName, convert (varchar (256), a. [value]) Description
  5. from sys.extended_properties a inner join sysobjects b on b.id = a.major_id
  6. left join syscolumns c on a.minor_id = c.colid and a.major_id = c.id
  7. where b. [name] = @ TableName

Finally, the need to view a table, execute the stored procedure + table name. On it. As follows:

  1. DataDictionary_GetbyTableName employee

Get is:

Did not feel convenient.

Posted by databasesql in Recommended

00003.Oracle view the process data

SQL> selectname, value from v $ parameter where name = ‘processes’;

NAME VALUE

————————————-

processes 400

Posted by databasesql in Recommended

the android data manipulation SQLite

SQLite Android embedded database, is a typical relational database to support our common data type supports standard SQL statement.

Often use SQLite embedded database. In sqlite3.dll4, is a single file, no installation, can be used directly.

Android system, we need extends an abstract class the SQLiteOpenHelper it default.

  • public void onCreate (SQLiteDatabase db) {} applied to the first use to create a database and table structure
  • public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {} is called when the software version of the database update method, note that the data should not be lost.
Android project SQLiter database Step 1: Write an entity class to inherit SQLiteOpenHelper
Step 2: Add a constructor to this entity class (required)
The third step: Rewrite the onCreate (SQLiteDatabase db); created the first time you use the database table structure.
Rewrite onUpgrade (SQLiteDatabase db, in oldVersion, int newVersion); recreate the database when the database upgrade.
Step four: common code.
SQLiteDatabase db = this.getReadableDatabase (); obtain a readable database.
SQLiteDatabase db = this.getWriteableDatabase (); Get a writable database.
Cursor cursor = db.query (tableName, …);
ContentValues ??cv = new ContentValues ??(); / / the contents of a set of values
Specific classes (CRUD) to achieve the following:
package com.hkrt.server;

import java.util.ArrayList;
import java.util.List;

import com.hkrt.domain.Person;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class PersonHelper extends SQLiteOpenHelper {
    private static final String DBNAME="hkrt.db";
    private static final String TABLENAME="person";
    private static final String ID="id";
    private static final String NAME="name";
    private static final Integer VERSION=1;
	public PersonHelper(Context context) {
		super(context, DBNAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql="create table "+TABLENAME+"  ("+ID+" INTEGER primary key autoincrement,"+NAME+" varchar(20))";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}
	// 	
   public long insert(String text){
	   SQLiteDatabase db =this.getWritableDatabase();
	   ContentValues values = new ContentValues();
	   values.put(NAME, text);
	   long row = db.insert(TABLENAME, null, values);
	   return row;
   }
   //
   public long update(int id){
	   SQLiteDatabase db =this.getWritableDatabase();
	   ContentValues values = new ContentValues();
	   values.put(NAME, "");
	   String where = ID + " = ?";
	   String[] whereValue = { Integer.toString(id) };
	   long row=  db.update(TABLENAME, values, where, whereValue);//
	  return row;
	   
   }
   //   
   public long delete(int id){
	   SQLiteDatabase db = this.getWritableDatabase();
	   String whereClause=ID+"=?";
	   String[] whereArgs = { Integer.toString(id) };
	   long row =  db.delete(TABLENAME, whereClause, whereArgs);
	  return row;
   }
   //
   public List<Person> find(){
	   List<Person> list = new ArrayList<Person>();
	   Person person;
	   SQLiteDatabase db = this.getWritableDatabase();
	   Cursor cursor = db.query(TABLENAME, null, null, null, null, null, null);
	   while(cursor.moveToNext()){
		   person= new Person();
		   int id = cursor.getInt(0);
		   String name = cursor.getString(1);
		   person.setId(id);
		   person.setName(name);
		   if(person!=null){
			   list.add(person);			   
		   }
	   }
	   cursor.close();
	   db.close();
	   return list;
   }

}
Test code:
package com.hkrt.db;

import java.util.List;

import android.test.AndroidTestCase;
import android.util.Log;

import com.hkrt.domain.Person;
import com.hkrt.server.PersonHelper;

public class PersonTest  extends AndroidTestCase {
	String TAG="PersonTest";
	public void testCreateDB(){
		PersonHelper helper = new PersonHelper(this.getContext());
//		helper.getWritableDatabase();// 
		
		System.out.println(helper.insert(""));
		Log.i(TAG,String.valueOf(helper.insert("google")));//
		
//		System.err.println(helper.update(1));//
//		System.err.println(helper.delete(1));//
		List<Person> persons = helper.find();//
		for(Person p:persons){
			System.err.println(p.toString());
		}
		//	mysql ?limit
	}
	
}
Usually we do CUD, to do the processing of the transaction:
// 	
   public long insert(String text){
	   SQLiteDatabase db =this.getWritableDatabase();
	   ContentValues values = new ContentValues();
	   values.put(NAME, text);
	   
	   db.beginTransaction();//
	   long row;
	   try{
		   row = db.insert(TABLENAME, null, values);
		   db.setTransactionSuccessful();
	   }finally{
		   db.endTransaction();
	   }
	 
	   return row;
   }

Note: To add the test permissions.

Generated database \ data \ data \ <package.name> \ databases \.
Can export the generated database to view the structure and data, here recommend a sqlite sqlite develoption
Add a query result data chart:
Dos command into the the linux control background. Query sqlite3 Data.
Step 1: We need the android-sdk-windows directory. Re-entry tools or Platform-tools query adb.exe
Note: Due to different version of adb.exe in a different directory.
Specific shown below:
If the database in Chinese, there will be problems is the issue of data encoding.
android sqlite3 Tools class can directly modify and use.
package com.hkrt.sql;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

import com.hkrt.domain.User;
import com.hkrt.domain.UserEnum;

/**
 * :</br>
 * dbHelper = new DBHelper(this);</br>
 * dbHelper.open();</br>
 * </br>
 * dbHelper.close()</br>
 * @data   2010-11-12
 */

public class DBHelper
{
	//sqliteNULL Integer real text blob,
	private Context context;
	private SQLiteDatabase dbInstance;
	private DBCreator dbCreator;
	private static final String DB_NAME = "db_user";
	private static final int DB_VERSION = 1;
	
	public static final String TABLE_NAME="user";
	public static final String COLUMN_ID = "user_id";
	public static final String COLUMN_NAME="user_name";
	public static final String COLUMN_AGE="user_age";
	public static final String COLUMN_SEX="user_sex";
	public static final String COLUMN_TYPE = "user_type";
	
	private static final String CREATE_TABLE= new StringBuffer().append("Create table ").append(TABLE_NAME)
								.append(" (")
								.append(COLUMN_ID).append(" integer primary key,")
								.append(COLUMN_NAME).append(" varchar(20) not null,")
								.append(COLUMN_AGE).append(" text not null,")
								.append(COLUMN_SEX).append(" text not null,")
								.append(COLUMN_TYPE).append(" integer not null)")
								.toString();
	public DBHelper(Context context)
	{
		this.context = context;
	}
	public void open()
	{
		//?android?onCreate?
		dbCreator = new DBCreator(context,DB_NAME,null,DB_VERSION,CREATE_TABLE,TABLE_NAME);
		dbInstance = dbCreator.getWritableDatabase();
	}
	public void close()
	{
		dbCreator.close();
	}
	public void insert(ContentValues values)
	{
		dbInstance.insert(TABLE_NAME, null, values);
	}
	public void update(ContentValues values,String whereClause,String[] whereArgs)
	{
		dbInstance.update(TABLE_NAME, values, whereClause, whereArgs);
	}
	public void delete(String whereClause, String[] whereArgs)
	{
		dbInstance.delete(TABLE_NAME, whereClause, whereArgs);
	}
	/**
	 * 
	 * @param sql SQL??
	 * @param selectionArgs ?
	 * @return Cursor??JDBCResultSet???close()
	 */
	public Cursor query(String sql, String[] selectionArgs)
	{
		return dbInstance.rawQuery(sql, selectionArgs);
	}
	public ArrayList<User> getQueryAccountList(String sql,String[] args)
	{
		ArrayList<User> accoutList = new ArrayList<User>();
		open();
		Cursor cursor = query(sql, args);
		while (cursor.moveToNext()) {
			User user = new User();
			user.setId(cursor.getInt(0));
			user.setName(cursor.getString(1));
			user.setAge(cursor.getString(2));
			user.setSex(cursor.getString(3));
			user.setType(UserEnum.getUserEnum(cursor.getInt(4)));
			accoutList.add(user);
		}
		//?
		if(!cursor.isClosed())
		{
			cursor.close();
		}
		close();
		return accoutList;
	}
	/**Iduser*/
	public User getUserById(int id){
		User user =null;
		String sql="select * from "+TABLE_NAME +" where " + COLUMN_ID +" = ?";
		ArrayList<User> users = getQueryAccountList(sql,new String[]{String.valueOf(id)});
		if(users!=null){
			user= new User(); 
			user.setId(users.get(0).getId());
			user.setName(users.get(0).getName());
			user.setAge(users.get(0).getAge());
			user.setSex(users.get(0).getSex());
			user.setType(users.get(0).getType());
		}
		return user;
	}
	/**
	 * 
	 */
	public ArrayList<User> getQueryAccountListLike(String str){
		ArrayList<User> accoutList = new ArrayList<User>();
		open();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from ").append(TABLE_NAME).append(" where ").append(COLUMN_NAME).append(" like ").append("'"+str +"%'");
		Cursor cursor =	dbInstance.rawQuery(sb.toString(), new String[]{});
		while (cursor.moveToNext()) {
			User user = new User();
			user.setId(cursor.getInt(0));
			user.setName(cursor.getString(1));
			user.setAge(cursor.getString(2));
			user.setSex(cursor.getString(3));
			user.setType(UserEnum.getUserEnum(cursor.getInt(4)));
			accoutList.add(user);
		}
		//?
		if(!cursor.isClosed())
		{
			cursor.close();
		}
		close();
		return accoutList;	
	}
	

	private class DBCreator extends SQLiteOpenHelper
	{
		private Context context;
		private String createTableSql;
		private String tableName;
		

		public DBCreator(Context context, String dbname, CursorFactory factory,
				int version,String createTableSql,String tableName)
		{
			super(context, dbname, factory, version);
			this.context = context;
			this.createTableSql = createTableSql;
			this.tableName = tableName;
		}

		@Override
		public void onCreate(SQLiteDatabase db)
		{
			db.execSQL(createTableSql);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			db.execSQL("drop table if exists "+tableName);
			onCreate(db);
		}
	}
}
Posted by databasesql in Recommended