mysql5.7中文联机文档,SQL语言学习文档MySQL/5.7

MySQL/5.7

如何下载? Ubuntu下: sudo apt-get install mysql-server

如何连接? mysql -u root -p 输入密码

清除数据库/慎重

DROP DATABASE test;

创建数据库

CREATE DATABASE test;

显示已有数据库

SHOW DATABASES;

选定操作数据库

USE test;

MySQL数据类型

TINYINT1字节小整数值

INT4字节大整数值

BIGINT8字节极大整数值

FLOAT4字节单精度

DOUBLE8字节双精度

DATEYYYY-MM-DD日期值

TIMEHH:MM:SS时间值

YEARYYYY年份值

DATETIMEYYYY-MM-DD HH:MM:SS混合日期和时间值insert into tweets values('2017-03-02 15:22:22');

TIMESTAMPYYYYMMDDHHMMSS时间戳

CHAR0~255定长CHAR(20)/20为长度

VARCHAR0-65535变长VARCHAR(20)/20为限制长度

TINYBLOB0~255小二进制

TINYTEXT0~255小文本

BLOB0~65535二进制

TEXT0~65535文本

MEDIUMBLOB0-16777215中二进制

MEDIUMTEXT0-16777215中文本

LONGBLOB0-4294967295长二进制

LONGTEXT0-4294967295长文本

删除表/慎重

DROP TABLE students;

表的重命名(不影响数据)/慎重

RENAME TABLE students TO new_name;

创建表/学生信息/考试成绩/杂货店

CREATE TABLE students (

id INT AUTO_INCREMENT PRIMARY KEY,

first_name CHAR(20),

last_name CHAR(20),

email TEXT,

phone VARCHAR(40),

birthdate DATE,

friend_id INT);

CREATE TABLE student_grades (

student_id INT,

test TEXT,

grade INT);

CREATE TABLE groceries (

id INT AUTO_INCREMENT PRIMARY KEY,

name TEXT,

quantity INT);

查看数据库所有表

SHOW TABLES;

查看表结构

DESCRIBE students;

SHOW COLUMNS FROM students;

列的增加(不影响数据)

ALTER TABLE students ADD body TEXT;

列的重新定义(不影响数据)

ALTER TABLE students CHANGE COLUMN body health CHAR(20);

设置或删除列的默认值

ALTER TABLE students ALTER COLUMN health SET DEFAULT 'OK';

ALTER TABLE students ALTER COLUMN health DROP DEFAULT;

指定删除某行内容/慎重

DELETE FROM students WHERE id = 3;

加入信息行/没有标明的列必须要DEFAULT或者自增

INSERT INTO students (first_name,last_name,email,phone,birthdate,friend_id)

VALUES ('Peter','Rabbit','peter@rabbit.com','555-6666','2002-06-24',2);

INSERT INTO students (first_name,last_name,email,phone,birthdate,friend_id)

VALUES ('Alice','Wonderland','alice@wonderland.com','555-4444','2002-07-04',1);

INSERT INTO students (first_name,last_name,email,phone,birthdate,friend_id)

VALUES ('Aladdin','Lampland','aladdin@lampland.com','555-4443','2001-05-10',4);

INSERT INTO students (first_name,last_name,email,phone,birthdate,friend_id)

VALUES ('Simba','Kingston','simba@kingston.com','555-1111','2001-12-24',3);

INSERT INTO student_grades (student_id,test,grade)

VALUES (1,'Nutrition',95);

INSERT INTO student_grades (student_id,test,grade)

VALUES (2,'Nutrition',92);

INSERT INTO student_grades (student_id,test,grade)

VALUES (1,'Chemistry',85);

INSERT INTO student_grades (student_id,test,grade)

VALUES (2,'Chemistry',95);

INSERT INTO groceries

VALUES (1,"Banana",4);

INSERT INTO groceries

VALUES (2,"Apple",1);

INSERT INTO groceries

VALUES (3,"Peach",2);

查看表中所有信息/查看组合表信息

SELECT * FROM students;

SELECT * FROM students,student_grades;

查看表中指定列的信息

SELECT first_name,last_name,phone FROM students;

按照某个列排序查看表中信息/ASC升序DESC降序

SELECT * FROM groceries ORDER BY quantity ASC;

按照某列排序查看表中信息，附带限定信息/AND/OR

SELECT * FROM groceries WHERE quantity > 1 AND quantity < 5 ORDER BY quantity DESC;

选定指定字符串值的表中信息/IN也可以NOT IN

SELECT * FROM groceries WHERE name = "Banana" OR name="Apple" OR name="xxx";

SELECT * FROM groceries WHERE name IN ("Banana","Apple","xxx");

选定某列的总和/也可以MIN,MAX/按照分组求和

SELECT SUM(quantity) FROM groceries;

SELECT name,SUM(quantity) FROM groceries GROUP BY name;

嵌套选定

SELECT * FROM students WHERE id IN (SELECT student_id FROM student_grades);

按字符串正则表达式选定

SELECT * FROM students WHERE phone LIKE "%444%";

选定时列自定义标题AS,分组统计和

SELECT student_id,SUM(grade) AS total_grades FROM student_grades GROUP BY student_id;

按照id分组统计和，并从结果中选定满足某个限定结果，GROUP后面才用HAVING

SELECT student_id,SUM(grade) AS total_grades FROM student_grades GROUP BY student_id

HAVING total_grades > 180;

使用GROUP分组后会自动记录下该分组的数据行个数，用COUNT(*)即可获得

SELECT student_id FROM student_grades GROUP BY student_id HAVING COUNT(*) >= 2;

使用CASE来为选定结果添加注释

SELECT student_id,test,

CASE

WHEN grade >= 95 THEN "very good"

WHEN grade >= 90 THEN "good"

ELSE "ok"

END AS "remark"

FROM student_grades;

不同表中按照某值匹配查看/后者效率更高并且可以再加个WHERE

SELECT * FROM students,student_grades

WHERE student_grades.student_id = students.id;

SELECT * FROM students

JOIN student_grades

ON student_grades.student_id = students.id

WHERE grade > 90;

不同表中按照某值匹配查看，若无匹配也显示

SELECT * FROM students

LEFT OUTER JOIN student_grades

ON student_grades.student_id = students.id;

更新某行中的某列信息

UPDATE students SET email='Simba@163.com' WHERE id = 4;

在同一张表中交叉查找/自我JOIN

SELECT students.first_name,students.last_name,friend.email AS friend_email

FROM students

JOIN students friend

ON students.friend_id = friend.id;

在多个不同表中交叉查找/要把SELECT id,quantity FROM groceries;中的两列数字全用对应id学生的姓氏代替

SELECT a.first_name,b.first_name FROM groceries

JOIN students a

ON groceries.id = a.id

JOIN students b

ON groceries.quantity = b.id;

//**xyt@2018**//