MySQL 常用SQL語句

■  在DOS命令行啟動MYSQL服務：

net start mysql

■在DOS命令行停止MYSQL服務：

net stop mysql

■查看被監聽的端口:

netstat –na | findstr 3306

findstr用于查找后面的端口是否存在。

■創建數據庫用戶：只有根用戶（root）才有創建新用戶的權限

CREATE USER user_name1 IDENTIFIED BY ‘password’,

 user_name2 IDENTIFIED  BY ‘password’;

一次可以創建多個數據庫用戶

■刪除數據庫用戶:

DROP USER user_name;

■選擇用戶:

select user();

■用戶的權限控制：

GRANT庫，表級的權限控制：將某個庫中的某個表的控制權限賦予某個用戶

GRANT ALL    ON db_name.table_name TO  uer_name [indentified by ‘password’];

■查看所有的字符編碼:

SHOW CHARACTER SET;  

=======================================================================

■登錄MySQL數據庫: 在DOS命令行登錄MYSQL控制臺

mysql -u user_name -p(Enter,回車鍵入密碼，若直接輸入則為可顯)

Enter password：*********

Mysql –h hostname –u user_name –p

Enter password:*********

例：mysql –h 192.168.5.105 –uroot –p

Enter password:*******

■查看運行環境信息:

進入MYSQL命令行工具后 , 使用status;或\s 查看運行環境信息

■創建數據庫：

create database db_name；

[default]CHARACTER SET charset_name        //設置數據庫的編碼發方式

[default]COLLATE collation_name ;          //設置按collation_name字段排序

//不能寫成utf-8，utf8的默認校對為utf8_general_ci（通過show character set查看）

CREATE DATABASE db_name CHARACTER    SET utf8 COLLATE  utf8_general_ci； 

CHARACTER SET:指定數據庫采用的字符集

COLLATE:指定數據庫字符集的比較方式

■使用數據庫：

use db_name;           

■顯示數據庫：

SHOW DATABASES；

■顯示數據庫創建語句：

SHOW CREATE DATABASE db_name;

■刪除數據庫：

DROP DATABASE  db_name;

刪除時可先判斷是否存在，寫成：DROP DATABASE IF EXISTS db_name;

■查看創建數據庫的指令并查看數據庫使用的編碼：

show create databasedb_name;

■查看數據庫編碼：

showvariables like ‘char%’;

■查看數據庫當前引擎：

SHOW CREATE TABLE table_name;

■修改數據庫當前引擎：

ALTER TABLE table_name ENGINE=MYISAM| INNODB;( ‘|’表示‘或者’，選其一)

你能用的數據庫引擎取決于mysql在安裝的時候是如何被編譯的。要添加一個新的引擎，就必須重新編譯MYSQL。在缺省情況下，MYSQL支持三個引擎：ISAM、MYISAM和HEAP。另外兩種類型INNODB和BERKLEY（BDB），也常常可以使用。

■設置數據庫編碼：

setcharacter_set_client=gbk;//可以存中文

setcharacter_set_results=gbk;//可以看中文

■備份數據庫：

MYSQLDUMP –u用戶名(根用戶) –p密碼 db_name >  存放路徑級/文件名(文件格式：.sql)

（不是在mysql控制臺執行，而要退出控制臺在DOS下執行）

例子：

MYSQLDUMP –u root –p******* mydb > D:/mydb.sql;

■恢復數據庫：

前提：要創建一個空數據庫

SOURCE 存放路徑/文件名.sql (在Mysql控制臺執行)

■如何將大量數據存入數據庫中的表中:

首先，將數據按表的結構（字段的順序要對應）存入文本文檔中；

然后，某字段若沒有值則填入NULL，注意，每個字段值之間用Tab鍵隔開（/r/n）。

最后，使用命令：LOAD DATA LOCAL INFILE ‘E:/Test/pet.txt’ INTO TABLE pet  LINES

               TERMINATED BY ‘\r\n’;

Terminate 結束，終止；

■創建表：

1.CREATE TABLE pet (

id int PRIMARY KEY AUTO_INCREMENT ,

name VARCHAR(20) NOT NULL UNIQUE,

owner VARCHAR(20) NOT NULL,

species VARCHAR(20),

sex CHAR(1),

birth DATE ,

);

2.CREATE TABLE table_name(

   field1  datatype,

   field2  datatype,

   ……

)CHARACTER SET 字符集 COLLATE 校對規則;

 field:指定列名    datatype:指定列類型

■顯示表:

show tables; 

■顯示某個表創建時的全部信息：

SHOW CREATE TABLE table_name;

■顯示表的結構信息：

DESCRIBE table_name;    縮寫形式 : desc table_name;

■查找數據:

SELECT * FROM table_name;

■顯示表的各字段:

DESCRIBE table_name;

■清空表中的數據:

1.TRUNCATE table_name;

此方法會使表中的取號器（ID）從1開始

2.DELETE FROM table_name;
不帶where參數的delete語句可以刪除mysql表中所有內容，使用truncate table也可以清空mysql表中所有內容。效率上truncate比delete快，但truncate刪除后不記錄mysql日志，不可以恢復數據。
delete的效果有點像將mysql表中所有記錄一條一條刪除到刪完，而truncate相當于保留mysql表的結構，重新創建了這個表，所有的狀態都相當于新表。 

注意：在MySQL中事務的特殊說明：

1.  mysql控制臺是默認自動提交事務（dml）.

2.  若在控制臺使用事務，應該做以下設置：

*SETAUTOCOMMIT=FALSE;    //解除自動提交功能

*SAVEPOINT point;         //設置保存點point

//一系列操作…

*ROLLBACK TOpoint;      //回滾到保存點

使用TRUNCATE table_name刪除的表不能夠回滾，但刪除速度較快，

使用DELETE FROM table_name刪除的表可以回滾，刪除速度相對較慢。

■創建表:

CREATE    TABLE table_name;

■刪除表:

DROP TABLE    table_name;     

■插入數據:

1.INSERT INTO table_name [(字段1，字段2，字段3，…)] VALUES (值1，值2，…);

如果向表中的每個字段都插入一個值，那么前面[]括號內字段名可寫也可不寫。

2.INSERT INTO pet  VALUES('Puffball','Diane','hamster','f','1999-03-30',NULL);

3.從 源表 中篩選符合條件的記錄，批量插入到 (指定的)目標表 中:

insert into 目標表(字段1, 字段2,...字段n) select 字段1, 字段2,...字段n from 源表 where 條件

4.向表中插入條數據：

insert into articles (id, content,userid)

values (2,’hahaha’,11),(null,’xixixi’,10),(13,’aiaiai’,1)，…;

■刪除數據：

DELETE    FROM table_name WHERE id=’10’;

delete語句不能刪除某一列的值，可使用update更新列值。

使用delete語句僅刪除記錄，不刪除表本身，刪除表使用drop table語句。

■更新數據:

UPDATE pet SET birth = '1989-08-31'WHERE name = 'Bowser' ORDER    BY birth DESC;

ASC(升序，默認方式)；DESC（降序）

WHERE和 ORDER 語句也可用于查詢select 與 刪除delete

■查詢數據:

1.SELECT * FROM pet WHERE name = 'Bowser';

SELECT * FROM pet WHERE birth > '1998-1-1';

SELECT * FROM pet WHERE species = 'dog' AND sex ='f';

SELECT * FROM pet WHERE species = 'snake' OR species= 'bird';

SELECT * FROM pet WHERE (species = 'cat' AND sex ='m')

          OR(species = 'dog' AND sex = 'f');

SELECT name, birth FROM pet;

SELECT chinese+english+math  FROM Students;    //列之間可以進行運算

2.增加關鍵字DISTINCT檢索出每個唯一的輸出記錄，把相同的記錄值取其中一個即可。

SELECT DISTINCT owner FROM pet; //distinct[d?'st??kt] 截然不同的

3.使用一個WHERE子句結合行選擇與列選擇：

SELECT name, species, birth FROM pet

        WHEREspecies = 'dog' OR species = 'cat';

4.為了排序結果，使用ORDER BY子句:

SELECT name, birth FROM pet WHERE species = 'dog' ORDERBY birth;

默認排序是升序，最小的值在第一。要想以降序排序，在你正在排序的列名上增加DESC（降

序）關鍵字：

SELECT name, birth FROM pet ORDER BY birth DESC;

5.可以對多個列進行排序，并且可以按不同的方向對不同的列進行排序：

SELECT name, species, birth FROM pet

ORDER BY species, birth DESC;

注意DESC關鍵字僅適用于在它前面的列名（birth）；不影響species列的排列順序。

6.MySQL提供了幾個函數，可以用來計算日期，例如，計算年齡或提取日期部分：

SELECT name, birth, CURDATE(),((YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)) AS age FROM pet;

1.RENAME TABLE table_name TO new_name;

2.ALTER TABLE table_name RENAME TO new_table_name;