MySQL常用語句及注意事項

gary-liu發表於2017-04-30
MySql

1.SQL 使用單引號來環繞文字值（大部分資料庫系統也接受雙引號）。如果是數值，請不要使用引號。

2.可以在不刪除表的情況下刪除所有的行。（不要加*）這意味著表的結構、屬性和索引都是完整的

DELETE  FROM table_name

3.修改表名稱

alter table test2 rename to test3;
rename table test3to test4;

4.Between A and B (邊界A是包括的B是不包括的，不同的資料庫對邊界的處理不一樣)，我實際操作中兩邊都是包括的

5.表的複製：
1、既複製表結構也複製表內容的SQL語句：

CREATE TABLE tab_new AS SELECT * FROM tab_old;  （as可以不寫）

2、只複製表結構不復製表內容的SQL語句：

CREATE TABLE tab_new AS SELECT * FROM tab_old WHERE 1=2; （建立一個結構一樣的表，as可以不寫）

3、將一個表的部分內容複製到另一個表中：

insert into B(id,name,rate) select id,name,rate from A ;

查詢內容插入另一張表：

insert into tablename(id,poNum,poCd,poType) select * from test limit 2600,100;

limit：第一個數是從某個數的下一個（2601）開始，不寫就是前多少條，第二個數是限制取多少條.MySQL不支援top關鍵字而是用limit來實現top的功能

6.UNION 操作符用於合併兩個或多個SELECT 語句的結果集。UNION 內部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的資料型別。同時，每條 SELECT 語句中的列的順序必須相同。UNION命令只會選取不同的值。如果允許重複的值，請使用UNION ALL。

SELECT column_name(s) FROM table_name1
UNION ALL
SELECTcolumn_name(s) FROM table_name2

7.SELECT INTO 語句從一個表中選取資料，然後把資料插入另一個表中。IN 子句可用於向另一個資料庫中拷貝表：

SELECT *
INTO Persons_backup IN 'Backup.mdb'
FROM Persons

8.CREATE DATABASE my_db ，SQL中char容納固定長度的字串,varchar容納可變長度的字串.

CREATE TABLE persion
(
id bigint(20)  NOT NULL AUTO_INCREMENT,
last_name varchar(255),
first_name varchar(255),
address varchar(255),
city varchar(255),
PRIMARY KEY (`id`)
)

9.Constraints約束，約束用於限制加入表的資料的型別。可以在建立表時規定約束（通過 CREATE TABLE 語句），或者在表建立之後也可以（通過 ALTER TABLE 語句）。主要的約束有NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT

10.每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束。可以給約束起名字，建立表後新增約束,

ALTER TABLE Persons  ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

不同資料庫的操作語句不太一樣

11.新增和刪除主鍵 

  alter table person addprimary key  (id);  alter table person drop primary key;

12.表已經存在設定預設值

ALTER TABLE Persons  ALTERCity SET DEFAULT 'SANDNES'；

刪除預設值

ALTER TABLE Persons ALTER City DROP DEFAULT（MySQL為例）
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT（其他資料庫）

13.建立索引：

CREATE INDEX poNo ON Person (LastName DESC)；//索引在DDL中是用key表示

刪除索引：

alter table Person drop index poNo;

刪除表和資料庫

drop table name；
drop database name；

14.新增、修改、刪除列、改變列屬性

Alter table Person add birthday date(50)
Alter table person change birthday birth date(50)；
Alter table person drop column birthday；
Alter table Person modify column birthday varchar(50);

15.COUNT(DISTINCTcolumn_name) 函式返回指定列的不同值的數目

SELECT COUNT(DISTINCT column_name) FROM table_name

16.在 SQL 中增加 HAVING 子句原因是 WHERE 關鍵字無法與合計函式一起使用。
檢視view的作用（為了安全性，可以隱藏不必要顯示的資料）

17.分組查詢

SELECT  
  sum(CASE when score>=0 and score<60 then 1 else 0 end)   AS '不及格',  
  sum(CASE when score>=60 and score<70 then 1 else 0 end)   AS '及格',  
  sum(CASE when score>=70 and score<80 then 1 else 0 end)   AS '一般',  
  sum(CASE when score>=80 and score<90  then 1 else 0 end)   AS '良好',
  sum(CASE when score>=90 and score<=100 then 1 else 0 end)   AS '優秀'   
from student_score;

18.一天前（24小時前）資料

select * from person where create_time < NOW() - interval 1 day;
select * from person where create_time < NOW() - interval 24 hour;

19.顯示錶結構，顯示建表語句

desc tablename;
SHOW CREATE TABLE table_name;

20.顯示索引

show index from person;

21.查詢區分大小寫

select  * from  table_name where  binary  name like  'a%';

22.判斷欄位中是否含有中文，查出帶漢字的資料

SELECT * FROM person WHERE length(`name`) <> CHAR_LENGTH(`filename`)

length ()函式計算包含位元組的長度，一個漢字是3個位元組，一個數字或字母是一個位元組；char_length()是計算包含字元長度， 漢字，數字和字母都是一個字元。
所以查詢條件中，長度相等，則欄位中無漢字；長度不相等，則有漢字。

23.檢視執行計劃

explain select * from person where ....

24.超多分頁查詢優化

select a.* from tabelname a,(select id from tablename where ... limit 10000,20) b where a.id = b.id;

相關文章