MySQL快速入门和使用提示

尼耳多
121 阅读5分钟

虽然我最喜欢的SQL数据库是PostgreSQL，因为它有丰富的功能和惊人的性能，但MySQL仍然有它的好处，特别是在复制方面。事实上，谷歌云SQL现在只与MySQL兼容。如果他们提供PostgreSQL，我就不会写这篇文章了。但我现在不谈意见了。让我们来做个快速入门，之后再编个小抄。另外，看看我学到的一些技巧，包括一些最佳实践。

快速入门

service mysql start     # start mysql if not started
mysql -u root -p        # login with password prompt

设置用户和数据库

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; -- change root pass

SHOW DATABASES;
CREATE DATABASE cats;

USE cats;
SHOW TABLES;

CREATE USER 'jump'@'localhost' IDENTIFIED BY 'secret'; -- create a new user
GRANT ALL ON cats.* TO 'jump'@'localhost';             -- grant privileges
FLUSH PRIVILEGES;                                      -- refresh privileges
SHOW GRANTS;                                           -- show users and grants

创建表

CREATE TABLE cats
(
  id              INT unsigned NOT NULL AUTO_INCREMENT, -- Unique ID
  name            VARCHAR(150) NOT NULL,                -- Name of the cat
  owner           VARCHAR(150) NOT NULL,                -- Owner of the cat
  birth           DATE NOT NULL,                        -- Birthday of the cat
  PRIMARY KEY     (id)                                  -- make id primary key
);

ALTER TABLE cats ADD gender CHAR(1) AFTER name;
ALTER TABLE cats DROP gender;

DESCRIBE cats;

CRUD

INSERT INTO cats ( name, owner, birth) VALUES
  ( 'Sandy', 'Lennon', '2015-01-03' ),
  ( 'Cookie', 'Casey', '2013-11-13' ),
  ( 'Charlie', 'River', '2016-05-21' );

SELECT * FROM cats;
SELECT name FROM cats WHERE owner = 'Casey';

DELETE FROM cats WHERE name='Cookie';

小贴士

常见的Shell命令

# run the mysql client
mysql -h [host] -u [user] -p 
mysql -h [host] -u [user] -p [database]

# export data
mysqldump -u [user] -p [database] > data_backup.sql

常见的MySQL命令

\s;                 -- show status
SHOW VARIABLES;     -- show configuration params
SHOW PROCESSLIST;   -- show running queries

SELECT @@version;   -- mysql version
SELECT @@datadir;   -- location of db files
SELECT @@hostname;  -- current hostname
SELECT USER();      -- current user
SELECT DATABASE();  -- current database

SHOW DATABASES;
USE db_name;
SHOW TABLES;
DESCRIBE table_name;

常见的管理命令

-- list all mysql users in database
SELECT host, user FROM mysql.user;  

-- show grant for all users
SHOW GRANTS;
SHOW GRANTS FOR 'user';

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

GRANT ALL ON db_name.* TO 'username'@'hostname' IDENTIFIED BY 'password';

-- refresh privileges
FLUSH PRIVILEGES;

DROP USER 'username'@'hostname';

常见的数据类型

目的例子注意事项
整数int(5)
浮点数float(12,3)将你的数值四舍五入
货币decimal(10,2)最多 10 位数。2分
日期date
日期时间timestamp(8)(8)yyymmdd, (12)yyymmddhhmmss
字符串varchar(20)
大文本blob
枚举enum('blue','red','gray')不建议使用

常用函数

函数注释
NOW()日期时间输入
strcomp(str1,str2)比较字符串
lower(str)
upper(str)
ltrim(str)
substring(str,idx1,idx2)
password(str)加密密码
curdate()获取日期
curtime()获取时间

普通CRUD

-- INSERT
INSERT INTO people VALUES ('MyName', '2002­08­31');             
INSERT INTO people (name, company_id, created_at) -- copy rows from same tbl
    SELECT name, 50, NOW() FROM people WHERE company_id = 49; 

-- SELECT
SELECT * FROM tbl;                              -- All values 
SELECT * FROM tbl WHERE rec_name = "value";     -- Some values
SELECT * FROM tbl WHERE rec1 = "value1"         -- Multiple critera
    AND rec2 = "val2"; 

SELECT column_name FROM table;                    -- Selecting specific columns
SELECT DISTINCT column_name FROM table;           -- Retrieving unique outputs
SELECT col1, col2 FROM table ORDER BY col2;       -- Sorting
SELECT col1, col2 FROM table ORDER BY col2 DESC;  -- Sorting Backward
SELECT COUNT(*) FROM table;                       -- Counting Rows
SELECT owner, COUNT(*) FROM table GROUP BY owner; -- Grouping with Counting
SELECT MAX(col_name) AS label FROM table;         -- Maximum value

SELECT pet.name, comment FROM pet, event -- Selecting from multiple tables 
WHERE pet.name = event.name;             

-- SEARCH

SELECT * FROM table WHERE rec LIKE "blah%"; -- % is wildcard ­
SELECT * FROM table WHERE rec LIKE "_____"; -- Find 5­char values: _ is 1 char
SELECT * FROM table WHERE rec RLIKE "^b$";  -- regex

-- JOINS
SELECT * FROM table_1 INNER JOIN table_2 ON conditions;
SELECT * FROM table1 LEFT JOIN table2 ON conditions;

-- UPDATE
UPDATE table SET column_name = "new_value" 
WHERE record_name = "value";

-- DELETE 
DELETE FROM table WHERE condition;

DELETE table_1, table2 FROM table_1
INNER JOIN table_2 ON table_1.column_1 = table_2.column_2
WHERE condition;

连接

INNER JOIN当两个表中至少有一个基于给定条件的匹配记录时，返回记录。

SELECT t1.*, t2.* FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID

-- adding alias to header on columns
SELECT  t1.ID AS t1_id, t1.Value AS t1_v, 
        t2.ID t2_id, t2.Value AS t2_v 
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID

LEFT OUTER JOIN返回左表的所有记录和右表的匹配记录。如果右边没有匹配的列，则返回NULL。

RIGHT OUTER JOIN返回右表的所有记录和左表的匹配记录。如果左表中没有匹配的列，它将返回NULL。

常见的表操作

CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));  

DROP TABLE table_name

ALTER TABLE authors 
    ADD     name VARCHAR(255), 
    CHANGE  author_work_id wokr_id INT,
    DROP    nickname,
    CHANGE `count(*)` cnt bigint(21),  ### renaming
    ALTER   is_rich SET DEFAULT FALSE;

-- Adding a column to an already­created table
ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

-- Removing a column
ALTER TABLE tbl DROP COLUMN col;

-- Adding index to an existing table;
ALTER TABLE table_name ADD INDEX index_name (col_name);

提示

  • 始终使用正确的数据类型。例如，不要使用VARCHAR(20) ，而不是DATETIME ，因为这将导致错误并可能存储无效的数据。
  • 在存储单个字符时，使用CHAR(1) 而不是VARCHAR(1) ，以节省空间。
  • 如果它是一个固定的长度，使用CHAR 数据类型。如果不是，请使用VARCHAR 。
  • 当使用DATETIME 或DATE 数据类型时，总是使用适合你的SQL引擎的YYYY-MM-DD 日期格式或ISO日期格式。避免使用区域格式。
  • 确保你对连接子句中使用的列进行索引，以便查询快速返回结果。
  • 不要在有索引的列上使用函数，因为这违背了索引的目的。例如，不使用left(code,2)='CA' ，而是改写为code LIKE 'CA%
  • 只在需要时使用SELECT * 。要明确，不要盲目地使用。
  • 只有在需要时才使用ORDER BY ，因为它是一个缓慢的过程。
  • 选择合适的数据库引擎。如果你的应用程序读取比写入更频繁，选择MyISAM存储引擎。如果你开发的应用程序写数据多于读数据（例如，银行交易），选择INNODB存储引擎。选择错误的引擎会影响性能。
  • 如果你想检查数据的存在，使用IF EXISTS(SELECT*...)
  • 代码时要考虑到缓存。而不是WHERE date >= CURDATE() ，在你的应用代码中把日期值存储在一个变量中。运行动态函数会使缓存失效。
  • 如果你只是在寻找1个唯一的项目，在你的查询中使用LIMIT 1 。这允许数据库在找到那个特定项目后停止扫描。 SELECT 1 FROM user WHERE state = 'CA' LIMIT 1
  • 索引不只是用于主键或唯一键。如果有任何你要搜索的列，你几乎都应该为它们建立索引。
  • 当使用JOINS ，确保你连接的列在两边都有索引。
  • 另外，确保你加入的列有相同的数据类型。
  • 如果只使用几个值，即：活动、非活动、待定、过期，则使用ENUM ，而不是VARCHAR 。
  • 除非你有非常特殊的原因要使用NULL ，否则你应该总是把你的列设置为NOT NULL 。如果没有理由使用0与NULL，你就不需要它--它们需要额外的空间并增加复杂性。
avatar
文章
阅读
粉丝