MYSQLコマンドライン大全
34271 ワード
この文書はhttps://blog.csdn.net/poloyzhang/article/details/41142533
MYSQL
1.
mysqldump -u -p –default-character-set=latin1 > ( latin1)
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.
mysqldump -u -p >
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3.
mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
-d –add-drop-table create drop table
4.
A: source
mysql ,
mysql -u root -p
mysql>use
source , ( .sql)
mysql>source wcnc_db.sql
B: mysqldump
mysqldump -u username -p dbname < filename.sql
C: mysql
mysql -u username -p -D dbname < filename.sql
、
1、 MySQL: MySQL Command Line Client(MySQL DOS ), 。 :mysql>
2、 MySQL:quit exit
、
1、、
:create database < >
: xhkdb
mysql> create database xhkdb;
2、
:show databases ( : s)
mysql> show databases;
3、
:drop database < >
: xhkdb
mysql> drop database xhkdb;
4、
:use < >
: xhkdb , :
mysql> use xhkdb;
:Database changed
5、
mysql> select database();
6、 :
mysql> show tables; ( : s)
、 ,
1、
:create table < > ( < > < > [,..< n> < n>]);
mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default ’′,
> degree double(16,2));
2、
:desc , show columns from
mysql>DESCRIBE MyClass
mysql> desc MyClass;
mysql> show columns from MyClass;
3、
:drop table < >
: MyClass
mysql> drop table MyClass;
4、
:insert into < > [( < >[,..< n > ])] values ( )[, ( n )]
, MyClass , : Tom .45, Joan .99, Wang .5.
mysql> insert into MyClass values(1,’Tom’,96.45),(2,’Joan’,82.99), (2,’Wang’, 96.59);
5、
1)、
:select < , ,...> from < > where < >
: MyClass
mysql> select * from MyClass;
2)、
: MyClass
mysql> select * from MyClass order by id limit 0,2;
:
mysql> select * from MyClass limit 0,2;
6、
:delete from where
: MyClass
mysql> delete from MyClass where id=1;
7、 :update set = ,…where
mysql> update MyClass set name=’Mary’where id=1;
7、 :
:alter table add ;
: MyClass passtest, int(4),
mysql> alter table MyClass add passtest int(4) default ’′
8、 :
:rename table to ;
: MyClass YouClass
mysql> rename table MyClass to YouClass;
update set =
update set = replace( ,’ ’,' ’)
update article set content=concat(‘ ’,content);
1.INT[(M)] :
2.DOUBLE[(M,D)] [ZEROFILL] : ( )
3.DATE : -01-01 -12-31。MySQL YYYY-MM-DD DATE , DATE
4.CHAR(M) : , ,
5.BLOB TEXT , (2^16-1) 。
6.VARCHAR :
5.
() .sql
() auction.c:mysqlbin>mysqladmin -u root -p creat auction, , 。
() auction.sql
c:mysqlbin>mysql -u root -p auction < auction.sql。
, auction auction。
6.
() mysql :
alter table dbname add column userid int(11) not null primary key auto_increment;
, dbname userid, int(11)。
7.mysql
mysql>grant select,insert,delete,create,drop
on *.* ( test.*/user.*/..)
to @localhost
identified by ‘ ’;
: , :
mysql> grant usage
-> ON test.*
-> TO testuser@localhost;
Query OK, 0 rows affected (0.15 sec)
:testuser, localhost test 。 , testuser :
mysql> GRANT select, insert, delete,update
-> ON test.*
-> TO testuser@localhost;
Query OK, 0 rows affected (0.00 sec)
testuser test SELECT,INSERT DELETE UPDATE 。 MySQL :
mysql> exit
Bye9!
1: SHOW :
mysql> SHOW DATABASES;
2:2、 MYSQLDATA
mysql> Create DATABASE MYSQLDATA;
3:
mysql> USE MYSQLDATA; ( Database changed !)
4:
mysql> SHOW TABLES;
5:
mysql> Create TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));
6: :
mysql> DESCRIBE MYTABLE;
7:
mysql> insert into MYTABLE values (“hyq”,”M”);
8: ( D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt”INTO TABLE MYTABLE;
9: .sql ( D:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
10:
mysql>drop TABLE MYTABLE;
11:
mysql>delete from MYTABLE;
12:
mysql>update MYTABLE set sex=”f”where name=’hyq’;
MySql ,
windows MySql , , net start mysql 。 Linux “/etc/rc.d/init.d/mysqld start” , 。
MySql root , , , 、root , :
use mysql;
delete from User where User=”";
update User set Password=PASSWORD(‘newpassword’) where User=’root’;
, User Host , , :
mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;
, 。 mydb 。
, root , root , , 。 。 。MySql User , , User , ; GRANT 。 GRANT :
grant all on mydb.* to NewUserName@HostName identified by “password”;
grant usage on *.* to NewUserName@HostName identified by “password”;
grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;
, GRANT WITH GRANT OPTION 。 User ,Password PASSWORD , 。 , , User , REVOKE 。
(www.cn-java.com) :
:
FILE: MySQL 。
PROCESS: 。
RELOAD: , 。
SHUTDOWN: MySQL 。
/ / :
Alter: ( / ) 。
Create: 。
Delete: 。
Drop: 。
INDEX: 。
Insert: 。
Select: / 。
Update: 。
:
ALL: ( root )。
USAGE: – 。
———————
MYSQL
mysql 。 MYSQL、 、 MYSQL 。
mysql 。 MYSQL、 、 MYSQL 。
、 MYSQL
:mysql -h -u -p
、 : MYSQL
DOS , mysqlbin, mysql -uroot -p, , MYSQL, root , MYSQL ,MYSQL :mysql>
、 : MYSQL
IP :.110.110.110, root, abcd123。 :
mysql -h110.110.110.110 -uroot -pabcd123
( :u root , )
、 MYSQL :exit ( )
、
:mysqladmin -u -p password
、 : root ab12。 DOS mysqlbin,
mysqladmin -uroot -password ab12
: root , -p 。
、 : root djg345
mysqladmin -uroot -pab12 password djg345
MYSQL ( )
、
、 , , , 。 , OK。
、 。 MYSQL 。 mysql-3.23.27-beta-win。
、
、 。
show databases;
:mysql test。mysql MYSQL , , 。
、 :
use mysql;// , FOXBASE
show tables;
、 :
describe ;
、 :
create database ;
、 :
use ;
create table ( );
、 :
drop database ;
drop table ;
、 :
delete from ;
、 :
select * from ;
、
drop database if exists school; // SCHOOL
create database school; // SCHOOL
use school; // SCHOOL
create table teacher // TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default ‘ ’,
year date
); //
//
insert into teacher values(”,’glchengang’,' ’,’-10-10′);
insert into teacher values(”,’jack’,' ’,’-12-23′);
: () ID :int(3) :auto_increment :not null primary key
() NAME
() ADDRESS , 。varchar char , 。
() YEAR 。
mysql , 。 school.sql, c:\ , DOS \mysql\bin, :
mysql -uroot -p < c:\school.sql
, ; , 。( , // )。
、
、 : tab ,null \n .
:
rose 1976-10-10
mike 1975-12-23
、 load data local infile " " into table ;
: \mysql\bin , use 。
、 :( DOS \mysql\bin )
mysqldump --opt school>school.bbb
: school school.bbb ,school.bbb , , 。
.SELECT :
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[WITH OWNERACCESS OPTION]
:
([]) , ({}) 。
1 FROM
FROM SELECT 。FROM ( ), 、 INNER JOIN、LEFT JOIN RIGHT JOIN 。 , IN 。
: SQL :
SELECT OrderID,Customer.customerID
FROM Orders Customers
WHERE Orders.CustomerID=Customers.CustomeersID
2 ALL、DISTINCT、DISTINCTROW、TOP
(1) ALL SQL 。 , ALL。
:SELECT ALL FirstName,LastName
FROM Employees
(2) DISTINCT , 。
(3) DISTINCTROW ,
(4) TOP 。 , TOP N PERCENT ( N )
: %
SELECT TOP 5 PERCENT*
FROM [ Order Details]
ORDER BY UnitPrice*Quantity*(1-Discount) DESC
3 AS
, , , , , AS 。
: FirstName NickName
SELECT FirstName AS NickName ,LastName ,City
FROM Employees
:
SELECT ProductName ,UnitPrice ,UnitsInStock ,UnitPrice*UnitsInStock AS valueInStock
FROM Products
.WHERE
1
=
>
<
>=
<=
<>
!>
!<
:
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate>#1/1/96# AND OrderDate<#1/30/96#
:
Mcirosoft JET SQL , ‘#’ 。 Datevalue() 。 , ’’, 。
:
WHERE OrderDate>#96-1-1#
:
WHERE OrderDate>Datevalue(‘/1/96’)
NOT 。
:
WHERE Not OrderDate<=#1/1/96#
2 (BETWEEN NOT BETWEEN)
BETWEEN …AND… 。
: 。
WHERE OrderDate Between #1/1/96# And #2/1/96#
3 (IN ,NOT IN)
IN 。IN OR 。
: London、Paris Berlin
SELECT CustomerID, CompanyName, ContactName, City
FROM Customers
WHERE City In(‘London’,’Paris’,’Berlin’)
4 (LIKE)
LIKE 。
LIKE
?
*
# 0~9
[ ]
[! ]
- ,
: ()-0000 ()-9999
SELECT CustomerID ,CompanyName,City,Phone
FROM Customers
WHERE Phone Like ‘(171)555-####’
LIKE
LIKE ‘A*’A Bc,c255
LIKE’[*]’5*5 555
LIKE’?5’5 55,5wer5
LIKE’##5’5235,5kd5,5346
LIKE’[a-z]’a-z 5,%
LIKE’[!0-9]’ -9 0,1
LIKE’[[]’1,*
. ORDER BY
ORDER ( ) , (ASC) (DESC), 。ORDER SQL 。
ORDER , 。
:
SELECT ProductName,UnitPrice, UnitInStock
FROM Products
ORDER BY UnitInStock DESC , UnitPrice DESC, ProductName
ORDER BY , 。
: 。
SELECT ProductName,UnitPrice, UnitInStock
FROM Products
ORDER BY 1 DESC , 2 DESC,3
.
:
SELECT Customers.CompanyName, Suppliers.ComPany.Name
FROM Customers, Suppliers
WHERE Customers.City=Suppliers.City
:
SELECT ProductName,OrderID, UnitInStock, Quantity
FROM Products, [Order Deails]
WHERE Product.productID=[Order Details].ProductID
AND UnitsInStock>Quantity
Microsof JET SQL JNNER JOIN
:
FROM table1 INNER JOIN table2
ON table1.field1 comparision table2.field2
comparision WHERE 。
SELECT FirstName,lastName,OrderID,CustomerID,OrderDate
FROM Employees
INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID
:
INNER JOIN Memo OLE Object Single Double 。
JOIN ON
:
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
, , 。
FROM table [LEFT|RIGHT]JOIN table2
ON table1.field1comparision table.field2
,
: ,
SELECT ProductName ,OrderID
FROM Products
LEFT JOIN Orders ON Products.PrductsID=Orders.ProductID
: , 。
: , , , , 。
, 。
SELECT *
FROM talbe1
LEFT JOIN table2 ON table1.a=table2.c
1 Iif
Iif :Iif(IsNull(Amount,0,Amout)
: ¥, 。
Iif([Amount]>50,?Big order?,?Small order?)
.
SQL ,GROUP BY HAVING 。GROUP BY , , HAVING 。
GROUP BY
SELECT fidldlist
FROM table
WHERE criteria
[GROUP BY groupfieldlist [HAVING groupcriteria]]
:Microsoft Jet Jet OLE 。
GROUP BY Null 。
SQL Null 。
GROUP BY , 。
: ‘WA’ , 。
SELECT Title ,Count(Title) as Total
FROM Employees
WHERE Region = ‘WA’
GROUP BY Title
HAVING Count(Title)>1
JET SQL
SUM ( )
AVG ( )
COUNT ( )
COUNT (* )
MAX
MIN
VAR
STDEV
FIRST
LAST
. Parameters
Parameters :
PARAMETERS name datatype[,name datatype[, …]]
name , .
Datatype .
PARAMETERS .
:
PARAMETERS[Low price] Currency,[Beginning date]datatime
SELECT OrderID ,OrderAmount
FROM Orders
WHERE OrderAMount>[low price]
AND OrderDate>=[Beginning date]
.
, , . , , . , , , .
1
UPDATE . .
:
UPDATE
SET
WHERE
: %, %
UPDATE OEDERS
SET OrderAmount = OrderAmount *1.1
Freight = Freight*1.03
WHERE ShipCountry = ‘UK’
2
DELETE .
: .
DELETE :
DELETE [ .*]
FROM
WHERE
:
DELETE *
FROM Orders
WHERE OrderData<#94-1-1#
3
INSERT .
INTO
valueS .
INSERT :
INSETR INTO ( , ,…)
valueS( , ,…)
:
INSERT INTO Employees(FirstName,LastName,title)
valueS(‘Harry’,’Washington’,’Trainee’)
4
. .
SELECT INTO :
SELECT , ,…
INTO [IN ]
FROM
WHERE
:
SELECT *
INTO OrdersArchive
FROM Orders
.
UNION .
UNION :
[ ] UNION [ALL] UNION …
:
SELECT CompanyName,City
FROM Suppliers
WHERE Country = ‘Brazil’
UNION
SELECT CompanyName,City
FROM Customers
WHERE Country = ‘Brazil’
:
,UNION . , ALL
UNION . , .
GROUP BY HAVING . , OREER BY .
.
, , , : , .
Microsoft Jet SQL TRANSFROM :
TRANSFORM aggfunction
SELECT
GROUP BY
PIVOT pivotfield[IN(value1 [,value2[,…]]) ]
Aggfounction SQL ,
SELECT ,
GROUP BY
:
Pivotfield , IN .
value .
: :
TRANSFORM Count(OrderID)
SELECT FirstName&’’&LastName AS FullName
FROM Employees INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
WHERE DatePart(“yyyy”,OrderDate)= ‘’
GROUP BY FirstName&’’&LastName
ORDER BY FirstName&’’&LastName
POVOT DatePart(“q”,OrderDate)&’ ’
.
. SELECT .
1
:
comparision [ANY|ALL|SOME]( )
:
ANY SOME , (=,>,<>,<=,>=) . True False.ANY , , True ,ANY True ( WHERE ), .ALL True , True .
: %
SELECT * FROM Products
WHERE UnitPrice>ANY
(SELECT UnitPrice FROM[Order Details] WHERE Discount>0.25)
2
:
[NOT]IN( )
: .
SELECT ProductName FROM Products
WHERE ProductID IN
(SELECT PrdoctID FROM [Order DEtails]
WHERE UnitPrice*Quantity>= 1000)
3
:
[NOT]EXISTS ( )
: EXISTS
SELECT ComPanyName,ContactName
FROM Orders
WHERE EXISTS
(SELECT *
FROM Customers
WHERE Country = ‘UK’AND
Customers.CustomerID= Orders.CustomerID)