菜鸟学Linux 第074篇笔记 Mysql语句

包含总览

1.表创建、修改、删除

2.索引的创建和删除、

3.SELECT语句的简单使用

简单查询

多表查询

子查询

联合查询

SQL语句

数据库

表

索引

视图

DML

数据库

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [CHARACTER SET=] [COLLATE=];

CREATE SCHEMA myskydb CHARACTER SET='gbk' COLLATE='gbk_chinese_ci';

SHOW COLLATION;

SHOW CHARACTER SET;

ALTER

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;

(数据库一般不推荐重新命名)

表

创建

1.直接定义一张空表

CREATE TABLE [IF NOT EXISTS] tb_name (col_name col_defination);

2.从其它表中查询出数据，并以之创建新表

CREATE TABLE coursetest SELECT * FROM courses WHERE CID <= 2;

(此种创建表，虽然可以复制某表中的数据但是字段的定义属性是不会复制的)

3.以其它表为模板创建一个空表

CREATE TABLE testcourses2 LIKE courses;  (表格式定义相同)

CREATE TABLE

tab1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(20) NOT NULL, Age TINYINT NOT NULL)

tab2 (id INT NOT NULL AUTO_INCREMENT,Name CHAR(20) NOT NULL, Age TINYINT NOT NULL,PRIMARY KEY(id),UNIQUE KEY(Name),INDEX(age))

键也称作约束，可用作索引，属于特殊索引(有特殊限定): B+Tree

CREATE INDEX

单字段

PRIMARY KEY

UNIQUE KEY

多字段

PRIMARY KEY(col,...)

UNIQUE KEY(col,...)

INDEX(col,...)

SHOW INDEXES tab_name;

修改表

添加、删除、修改字段

ALTER TABLE testcourses2 ADD UNIQUE KEY (Course);

ALTER TABLE coursetest CHANGE course Course CHAR(60) NOT NULL FIRST;

ALTER TABLE coursetest ADD startdate DATE DEFAULT '2017.1.10';

改表名

ALTER TABLE coursetest RENAME TO testcourse;

RENAME TABLE testcourse TO testcourses;

修改表属性

ALTER TABLE course ENGINE=innodb;

ALTER TABLE student ADD FOREIGN KEY (CID) REFERENCES courses (CID);

ALTER TABLE student ADD FOREIGN KEY foreign_cid (CID) REFERENCES courses (CID);

(引用外键)

外键只可在innodb使用

表数据

INSERT INTO student (Name,CID) VALUE ('Chen Jialuo',5);

DELETE FROM student WHERE CID=5;

索引

CREATE INDEX index_name ON tb_name (col_name,...) USING {BTREE|HASH};

col_name [(length)] [ASC | DESC] 索引使用长度

CREATE INDEX name_on_student ON student (Name) USING BTREE;

SHOW INDEXES FROM student\G;

DROP INDEX name_on_student ON student;

(索引只可删除和创建不可修改)

DML：

SELECT

INSERT INTO

DELETE

UPDATE

SELECT select-list FROM tb_name WHERE qualification

查询语句类型：

简单(单表)查询

SELECT [DISTINCT] * FROM tb_name;  [DISTINCT]去除重复

SELECT field1,field2 FROM tb_name; 投影

SELECT field1,field2 FROM tb_name WHERE qualification; 选择

多表查询

子查询

FROM子句：要查询的关系  后可接 表、多个表、其它SELECT语句

WHERE子句： 布尔关系表达式

=, >, >=, <=, <  (数值无需加引号，字符需加上)

逻辑关系 AND, OR, NOT, BETWEEN, LIKE(%,_ REGEXT,RLIKE) IN

SELECT Name FROM students WHERE Age>20 and Gender='M';

SELECT Name,Age,Gender FROM students WHERE Age>20 AND NOT Gender='M';

SELECT Name,Age,Gender FROM students WHERE NOT (Age>20 OR Gender='M');

SELECT Name,Age FROM students WHERE Age>=20 AND Age<=25;

SELECT Name,Age FROM studnets WHERE Age BETWEEN 20 AND 25;

SELECT Name FROM students WHERE Name LIKE 'Y%';

SELECT Name FROM students WHERE Name LIKE 'Y____';

SELECT Name FROM students WHERE Name LIKE '%ing%';

SELECT Name FROM students WHERE Name RLIKE '^[XY].*$';

SELECT Name FROM students WHERE Age IN (18,20,25);

SELECT Name FROM students WHERE CID1 {IS NULL | IS NOT NULL};

ORDER BY field_name {ASC|DESC} 查询出排序

SELECT Name FROM students WHERE CID1 IS NOT NULL ORDER BY Name DESC;

AS 显示查询字段为所指定的名字（别名）

SELECT Name AS Students_name FROM students;

LIMIT [offset] count 限制显示

SELECT Name FROM students LIMIT 2,3;

AVG MAX MIN SUM COUNT 聚合

SELECT AVG(Age) FROM students;

GROUP BY 分组 HAVING=WHERE 后加条件 HAVING只可跟GROUP BY后引用，用来再次过虑结果

SELECT AVG(Age) FROM students GROUP BY Gender; 

SELECT COUNT(CID1) AS Person,CID1 FROM students \换行

  GROUP BY CID1 HAVING Person>=2; 

多表查询

连接；

交叉连接：笛卡尔乘积

自然连接

SELECT * FROM students,courses WHERE students.CID1=courses.CID;

SELECT s.Name,c.Cname FROM students AS s,courses AS c WHERE s.CID1=c.CID;

外连接

左外连接 ... LEFT JOIN  ... ON ...

左外连接，以左表为主与右表进行连接当左表所比较的字段中有值，而右表无值对应，则右表字段中的值会显示为空(NULL)

SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ONs.CID1=c.CID;

右外连接 ... RIGHT JOIN ... ON ...

右外连接，以右表为主和左表进行连接当右表所比较的字段中有值，而左表无值对应，则左表字段中的值会显示为空(NULL)；

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

自连接

同一张表里进行连接，与外连接类似，只不过此连接在同一张表内查询比对；

子查询

即在一条查询语句中嵌套另一个查询语句

比较操作符中使用子查询，子查询所返回的值必须是单值

 SELECT Name FROM students WHERE Age > (SELECT AVG(Age) FROM students);

  (注意所嵌套的查询语句不用写;号结束符)

IN中使用子查询 (子查询可以返回多值)

SELECT Name FROM students WHERE Age IN (SELECT Age From tutors);

FROM 使用子查询

SELECT Name,Age FROM (SELECT Name,Age FROM students) AS t WHERE t.Age>=20;

联合查询

可以将两条查询语句的结果合到一个表上

以第一个表查询的字段为表头，第二个查询的结果追加到表后

UNION

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

小作业：

在students表中找到CID2中没有学生选修的课程有哪些

SELECT N.Cname FROM (SELECT s.Name,c.Cname FROM students AS s RIGHT 

JOIN courses AS c ON s.CID1=c.CID) AS N WHERE Name IS NULL;

(此为一行查询语句)

本文转自Winthcloud博客51CTO博客，原文链接http://blog.51cto.com/winthcloud/1890786如需转载请自行联系原作者

Winthcloud