mysql - 常用sql

mysql的常用数据类型和数据类型修饰
后面的带数字表示此类型的字段长度

 

数值型：

TINYINT 1 ，SMALLINT 2，MEDIUMINT 3 ，INT 4，BIGINT 8，DECIMAL，FLOAT 4，DOUBLE 8，BIT

 

字符串型

CHAR，VARCHAR，BINARY，VBINARY,TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOG，TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT，EMUM，SET

日期时间型

date,time,datetime,timestamp

数据限定修饰：

NOT NULL，NULL，DEFAULT，AUTO_INCREMENT，UNSIGNED，PRIMARY KEY，UNIQUE KEY，FOREIGN KEY

CHARACTER SET #ps:SHOW CHARACTER SET 显示当前数据库所支持的所有字符集

COLLATION #ps:SHOW COLLATION 显示所支持的所有排序规则

以下是SQL的每种DDL,DCL,DML语言

DDL
----Data Definition Language 数据库定义语言

如 create procedure之类

创建数据库

CREATE DATABASE [IF NOT EXISTS] DBNAME [CHARACTER SET 'CHAR_NAME'] [COLLATE 'COLL_NAME']

修改:ALTER 删除:DROP

创建一张新表

 

CRTATE TABLE [IF NOT EXISTS] TBNAME(col_name col_definition,...)

EXAMPLE:

 

1
 

mysql>CREATE TABLE students(Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT UNSIGNED,Name CHAR(20) UNIQUE KEY NOT NULL,Age TINYINT UNSIGNED INDEX,Gender CHAR(1) NOT NULL) [ENGINE={MyISAM | InnoDB }];
 

 

也可以这样写(区别在于单独定义主键，唯一键和索引)：

 

 

1
 

mysql>CREATE TABLE students(Id INT NOT NULL AUTO_INCREMENT UNSIGNED,Name CHAR(20) NOT NULL,Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL,PRIMARY KEY(id),UNIQUE KEY(name),INDEX(age))
 

 

查询出一张表的数据后创建新表(字段定义会丢失,数据会保留)

 

CREATE TABLE TBNAME SELECT...

EXAMPLE:

 

 

1
 

mysql>CREATE TABLE test SELECT * FROM students WHERE Id>5;
 

 

以一张表的格式定义，创建一张新的空表

 

CREATE TABLE TBNAME1 LIKE TBNAME2

修改表:

ALTER TABLE tb_name

MODIFY #修改字段定义

CHANGE #可以修改字段名和字段定义

ADD

DROP
 

EXAMPLE:

给表添加字段

 

 

1
 

mysql>ALTER TABLE students ADD (course VARCHAR(100),teacher CHAR(20));
 

添加惟一键

 

 

1
 

mysql>ALTER TABLE students ADD UNIQUE KEY Name;
 

修改字段：

修改course字段为Course字段，并放在Name字段之后（修改字段需要带上新的字段的定义）ps：MODIFY只能修改字段定义

 

 

1
 

mysql>ALTER TABLE students CHANGE course Course VARCHAR(100) [AFTER Name];
 

重命名表名

 

 

1

2
 

mysql>ALTER TABLE students RENAME TO stu;

mysql>RENAME TABLE stu TO students;
 

添加一个外键约束

 

 

1
 

ALTER TABLE students ADD FOREIGN KEY foreign _cid (CID) REFERENCES course (CID);
 

创建索引

CREATE INDEX index_name ON TABLE (col_name[(length)] [ASC|DESC]) [USING {BTREE|HASH}];

删除索引

 

DROP INDEX index_name ON TBNAME;

查看表状态:SHOW STATUS LIKE 'TBNAME';

查看表的索引:SHOW INDEXES FROM TBNAME;

 

DML
----Data Manipulation Language 数据操纵语言

如insert,delete,update,select(插入、删除、修改、检索)

插入修改数据

#如果每个字段都有值，不需要写字段名称,每组值用,隔开

mysql>INSERT INTO tb_name (col1,col2) VALUES ('STRING',NUM),('STRING',NUM);

mysql>INSERT INTO tb_name SET col1='string',col2='string';

mysql>INSERT INTO tb_name (col1,col2,col3) SELECT...;
 

EXAMPLE:

 

 

 

 

1

2
 

mysql>INSERT INTO students (Name,Gender,teacher) VALUE ('lujunyi','M','mage'),('wusong','M','zhuima');

mysql>INSERT INTO students SET Name='lujunyi',Gender='M',tearcher='zhuima';
 

更新数据

 

 

 

1

2
 

mysql>UPDATE tb_name SET column=value WHERE column=value;

mysql>UPDATE students SET Course='mysql' WHERE Name='lujunyi';
 

 

替换数据：

和UPDATE使用方式一样,只要将UPDATE换成REPLACE即可

删除数据

mysql>DELETE FROM tb_name WHERE conditions;

 

 

1
 

mysql>DELETE FROM students WHERE Course='mysql';
 

清空表：将会重置计数器

mysql>TRUNCATE tb_name

查询数据

单表查询：

mysql>SELECT [DISTINCT] column FROM tb_name WHERE CONDITION;

EXAMPLE:

#基本投影查询

 

 

 

1
 

mysql>SELECT Name,teacher FROM students WHERE Name='wusong';
 

#重复的结果只显示一次

 

 

 

1
 

mysql>SELECT DISTINCT Gender FROM students;
 

#组合条件,可以使用AND,OR,NOT,XOR组合多个条件

 

 

 

1
 

mysql>SELECT * FROM students WHERE Age>20 AND Gender='M';
 

#使用BETWEEN...AND...筛选出年龄介于20-25之间的数据

 

 

 

1
 

mysql>SELECT * FROM students WHERE Age BETWEEN 20 AND 25;
 

#查询Name以Y开头的的数据，%表示任意长度的任意字符，_表示任意单个字符

 

 

 

1
 

mysql>SELECT * FROM student WHERE Name LIKE 'Y%';
 

#使用正则表达式匹配查询,关键词为RLINK或者REGEXP

 

 

 

1
 

mysql> SELECT * FROM students WHERE Name RLINK '^[MNY].*$';
 

#使用IN关键词，将条件限定在一个列表中。用IS关键词，表示条件是否为空（IS NULL 或者 IS NOT NULL）

 

 

 

1
 

mysql>SELECT * FROM students WHERE Age IN (20,22,24);
 

#将查询的结果进行排序

 

 

 

1
 

mysql>SELECT * FROM students ORDER BY Name {ASC|DESC};
 

#查询结果别名显示

 

 

 

1
 

mysql>SELECT Name AS Stu_Name FROM students;
 

#LIMIT限定查询结果的条数,LIMIT 2,3表示偏移2条数据后，取3条数据

 

 

1
 

mysql>SELECT * FROM students LIMIT 2;
 

#求平均数:AVG()，最大值:MAX() 最小值MIN() 数量:COUNT() 求和:SUM()

 

 

 

1
 

mysql>SELECT AVG(age) FROM students;
 

#分组GROUP BY

 

 

1
 

mysql>SELECT Age, Gender FROM students GROUP BY Gender;
 

#别名:AS

 

 

 

1
 

mysql>SELECT COUNT(Age) AS Num,Age FROM students GROUP BY Age;
 

#过滤:HAVING

 

 

1
 

mysql>SELECT COUNT(Age) AS Num,Age FROM students GROUP BY Age HAVING Num>2;
 

 

多表查询:

 

#指定已哪个字段连接2张表

 

 

 

1
 

mysql>SELECT students.Name,courses.Cname FROM students,courses WHERE students.CID1 = courses.CID;
 

#连接时指定别名

 

 

 

1
 

mysql>SELECT students.Name,courses.Cname FROM students,courses WHERE students.CID1 = courses.CID;
 

#左外连接...LEFT JION...ON...

 

 

1
 

mysql>SELECT s.Name,c.Cname FROM students AS s LEFT JION courses AS c ON s.CID1=c.CID;
 

#右外连接...RIGHT JION...ON...

 

 

1
 

mysql>SELECT s.Name,c.Cname FROM students AS s RIGHT JION courses AS c ON s.CID1=c.CID;
 

 

子查询

 

#查询年龄大于平均年龄的数据

 

 

 

1
 

mysql>SELECT * FROM students WHERE Age > (SELECT AVG(Age) FROM students);
 

#在FROM中使用子查询

 

 

 

1
 

mysql>SELECT Name,Age FROM (SELECT * FROM students WHERE CID IN (2,3)) AS t WHERE Age>20;
 

#联合查询

 

 

1
 

mysql>(SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);
 

 

 

创建视图

CREATE VIEW VIEW_NAME AS SELECT....

 

DCL
----Data Control Language 数据库控制语言
如grant,deny,revoke等，只有管理员才有这样的权限。

创建用户

mysql>CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD'

删除用户

mysql>DROP USER 'USERNAME'@'HOSHOST支持通配符

_:任意单个字符

%:任意多个字符
 

授权

mysql>GRANT pri1,pri2...ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST' [IDENTIFIED BY 'PASSWORD']

取消授权

mysql>REVOKE pri1,pri2...ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST';

查看授权

mysql>SHOW GRANTS FOR 'USERNAME'@'HOST';

EXAMPLE:

    mysql>CREATE USER 'lujunyi'@'%' IDENTIFIED BY '123456';

    mysql>SHOW GRANTS FOR 'lujunyi'@'%';

    mysql>GRANT ALL PRIVILEGES ON testdb.* TO 'lujunyi'@'%';

 

本篇文章来源于 Linux公社网站(www.linuxidc.com)  原文链接：http://www.linuxidc.com/Linux/2013-06/86431.htm