菜鸟学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
