美文网首页
MySQL入门指令速查

MySQL入门指令速查

作者: 云一程 | 来源:发表于2017-09-26 10:17 被阅读56次
  • 最近在自学后台,写到MySQL感觉初学容易记混,在这里列出来方便日后查看。
  • 转发时请注明出处,尊重他人也是尊重自己。
  • 有错误随时指正,我会及时修正方便后人查阅。

显示所有数据库

SHOW databases;

显示数据库中所有表

SHOW tables;

显示表结构

DESCRIBE classics;

登录

mysql -u root(name) -p

使用数据库

USE publications;

创建数据库

CREATE DATABASE publications;

创建用户

GRANT ALL ON publications.* TO 'jim'@'localhost' IDENTIFIED BY 'password';


创建表

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 type VARCHAR(16),
 year CHAR(4)) ENGINE MyISAM;
USE publications;
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4)) ENGINE MyISAM;

添加自增数据,添加某一列

ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;
CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 type VARCHAR(16),
 year CHAR(4),
 id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE MyISAM;

向表中添加数据

INSERT INTO classics(author, title, type, year) VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');
INSERT INTO classics(author, title, type, year) VALUES('Jane Austen','Pride and Prejudice','Fiction','1811');
INSERT INTO classics(author, title, type, year) VALUES('Charles Darwin','The Origin of Species','Non-Fiction','1856');
INSERT INTO classics(author, title, type, year)  VALUES('Charles Dickens','The Old Curiosity Shop','Fiction','1841');
INSERT INTO classics(author, title, type, year) VALUES('William Shakespeare','Romeo and Juliet','Play','1594');

添加新列

ALTER TABLE classics ADD pages SMALLINT UNSIGNED;


删除某一列

ALTER TABLE classics DROP id;

删除列

ALTER TABLE classics DROP pages;

删除行

DELETE FROM classics WHERE title='Little Dorrit';

删除表

CREATE TABLE disposable(trash INT);
DESCRIBE disposable;
DROP TABLE disposable;
SHOW tables;


重命名表

ALTER TABLE classics RENAME pre1900;
ALTER TABLE pre1900 RENAME classics;

重命名列(type -> category)

ALTER TABLE classics CHANGE type category VARCHAR(16);

改变列中的数据类型

ALTER TABLE classics MODIFY year SMALLINT;

UPDATE...SET 更新字段

UPDATE classics SET author='Mark Twain (Samuel Langhorne Clemens)' WHERE author='Mark Twain';
UPDATE classics SET category='Classic Fiction' WHERE category='Fiction';


SELECT数据库查询

SELECT something FROM tablename;
SELECT * FROM classics;
SELECT author,title FROM classics;
SELECT title,isbn FROM classics;

SELECT COUNT 计数

SELECT COUNT(*) FROM classics;

SELECT DISTINCT 去重

SELECT author FROM classics;
SELECT DISTINCT author FROM classics;

WHERE SQL的核心与灵魂

  • 限定搜索
SELECT author,title FROM classics WHERE author="Mark Twain";
SELECT author,title FROM classics WHERE isbn="9781598184891 ";
  • 模糊搜索
SELECT author,title FROM classics WHERE author LIKE "Charles%";
SELECT author,title FROM classics WHERE title LIKE "%Species";
SELECT author,title FROM classics WHERE title LIKE "%and%";
  • LIMIT 限制返回数量搜索
SELECT author,title FROM classics LIMIT 3;
SELECT author,title FROM classics LIMIT 1,2;
SELECT author,title FROM classics LIMIT 3,1;
  • MATCH...AGAINST
SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('and');
SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('curiosity shop');
SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('tom sawyer');
  • MATCH...ATAINST...布尔模式
SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('+charles -species' IN BOOLEAN MODE);
SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('"origin of"' IN BOOLEAN MODE);

索引


创建索引

ALTER TABLE classics ADD INDEX(author(20));
ALTER TABLE classics ADD INDEX(title(20));
ALTER TABLE classics ADD INDEX(category(4));
ALTER TABLE classics ADD INDEX(year);
DESCRIBE classics;
两种创建方式
ALTER TABLE classics ADD INDEX(author(20));
CREATE INDEX author ON classics (author(20));

创建表时添加索引

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 category VARCHAR(16),
 year SMALLINT,
 INDEX(author(20)),
 INDEX(title(20)),
 INDEX(category(4)),
 INDEX(year)) ENGINE MyISAM;

主键

ALTER TABLE classics ADD isbn CHAR(13) PRIMARY KEY;

用数据填充列,并设置主键

ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE year='1876';
UPDATE classics SET isbn='9780582506206' WHERE year='1811';
UPDATE classics SET isbn='9780517123201' WHERE year='1856';
UPDATE classics SET isbn='9780099533474' WHERE year='1841';
UPDATE classics SET isbn='9780192814968' WHERE year='1594';
ALTER TABLE classics ADD PRIMARY KEY(isbn);
DESCRIBE classics;

创建带索引和主键的表

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 category VARCHAR(16),
 year SMALLINT,
 isbn CHAR(13),
 INDEX(author(20)),
 INDEX(title(20)),
 INDEX(category(4)),
 INDEX(year),
 PRIMARY KEY (isbn)) ENGINE MyISAM;

创建FULLTEXT索引

ALTER TABLE classics ADD FULLTEXT(author,title);

ORDER BY 通过一个或多个列按升序或降序排序返回结果

升序
SELECT author,title FROM classics ORDER BY author;
降序
SELECT author,title FROM classics ORDER BY title DESC;
  • 按author排序后,再按year降序排列
SELECT author,title,year FROM classics ORDER BY author,year DESC;
等价于
SELECT author,title,year FROM classics ORDER BY author ASC, year DESC;

GROUP BY 分组检索信息

SELECT category,COUNT(author) FROM classics GROUP BY category;

连接表

CREATE TABLE customers (
 name VARCHAR(128),
 isbn VARCHAR(13),
 PRIMARY KEY (isbn)
 ) ENGINE MyISAM;
INSERT INTO customers(name,isbn) VALUES('Joe Bloggs','9780099533474');
INSERT INTO customers(name,isbn) VALUES('Mary Smith','9780582506206');
INSERT INTO customers(name,isbn) VALUES('Jack Wilson','9780517123201');
SELECT * FROM customers;
  • 两个表接成一个单独的SELECT
SELECT name,author,title from customers,classics WHERE customers.isbn=classics.isbn;

NATURAL JOIN 使用NATURAL JOIN自动合并

SELECT name,author,title FROM customers NATURAL JOIN classics;

JOIN...ON 指定一列将两表连在一起

SELECT name,author,title FROM customers JOIN classics ON customers.isbn=classics.isbn;

使用AS 通过AS创建别名,节省输入和提高查询可读性

SELECT name,author,title FROM customers AS cust, classsics AS class WHERE cust.isbn=class.isbn;

使用基本运算符

SELECT author,title FROM classics WHERE author LIKE "Charles%" AND author LIKE "%Darwin";
SELECT author,title FROM classics WHERE author LIKE "%Mark Twain%" OR author LIKE "%Samuel Langhorne Clemens%";
SELECT author,title FROM classics WHERE author LIKE "Charles%" AND author NOT LIKE "%Darwin";

相关文章

  • MySQL入门指令速查

    最近在自学后台,写到MySQL感觉初学容易记混,在这里列出来方便日后查看。转发时请注明出处,尊重他人也是尊重自己。...

  • Mysql基本入门

    mysql入门 mysql与sql指令 参考文献:http://www.cnblogs.com/mr-wid/ar...

  • MySQL入门操作指令

    MySQL的相关概念介绍 MySQL 为关系型数据库(Relational Database Management...

  • curl指令速查

    -A 指定客户端用户代理标头,User-Agentcurl -A 'Mozilla/5.0 (Windows N...

  • Git指令速查

  • Linux常见命令

    1、快速查询历史输入指令 ctrl+r,再输入搜索字符查找便可。 2、find 指令和grep指令 用法:...

  • 常用Git命令

    Git常用指令速查图 Git 常用命令速查 git branch 查看本地所有分支git status 查看当前状...

  • 常用的LLDB指令

    LLDB指令的基本格式 对应着 例如给函数test设置断点: help指令 help指令可以帮助我们快速查找LLD...

  • Docker - 02 创建镜像 Dockerfile 指令速查

    docker build 命令 Dockerfile 指令速查表 .dockerignore 文件 例子 - ng...

  • InnoDB索引底层

    索引:Mysql建立的用来快速查询的已经排好序的数据结构。索引的目的就是为了快速查找数据。Mysql索引使用B+树...

网友评论

      本文标题:MySQL入门指令速查

      本文链接:https://www.haomeiwen.com/subject/tctsextx.html