MySQL笔记

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第 9 天，点击查看活动详情

1. mysql 安装

• 想在终端直接使用mysql指令，还需配置路径，打开.bash_profile 文件，添加PATH=$PATH:/usr/local/mysql/bin，顺便在.bash_profile 里通过 alias 设置别名，设置别名的目的是执行时可以直接执行mysql或者mysqladmin。

2. mysql 相关服务

• sudo mysql.server start|stop|restart|reload|force-reload|status；
• 启动、停止、重启、重载、强制重载、状态；

3. mysql 连接服务

• 登录mysql：mysql -h 主机名 -u 用户名 -p；
  • -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
  • -u : 登录的用户名;
  • -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

4. 执行 sql 脚本

• 命令行执行
  • mysql –u 用户名 –p 密码 –D 数据库 < sql脚本文件路径全名；
  • 示例：mysql –u root –p 123456 -D test < /home/zj/create_table.sql；
  • 注意：如果在sql脚本文件中使用了use 数据库，则-D数据库选项可以忽略；
• mysql 控制台，使用source命令执行
  • source 【sql脚本文件的路径全名】 或 Mysql>. 【sql脚本文件的路径全名】；
  • 示例：source /home/zj/create_table.sql；

5. mysql 自动补全

• 修改my.cnf：vi mysql/etc/my.cnf
  • 修改成如下代码：

    #disable-auto-rehash
    auto-rehash
    

• 在mysql启动时加参数auto-rehash
  • 示例：mysql –uroot -pmysql --auto-rehash

mysql 基本操作

• show databases：列出数据库列表；

• use db1：选择要操作的数据库；

• create database db1：创建数据库；

• alter database：修改数据库；

• drop database db1：删除数据库；

• show tables：显示指定数据库的所有表；

• show table status [from db1] [like 'pattern'] \G：该命令将输出Mysql数据库管理系统的性能及统计信息；

• create table：创建新表；

• alter table：变更数据库表；

• drop table：删除表；

• truncate table db1：清空表；

• show index from tb1：显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

• create index - 创建索引（搜索键）

• drop index - 删除索引

• show columns from tb1：显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

• select distinct col：返回唯一不同的值；

• where：指定过滤条件

  • between···and···：在某个范围内；
  • like：搜索某种模式；
  • in：指定针对某个列的多个可能值；

• order by col1,col2 asc|desc：按照一个或多个列排序；

• insert into 向表格中插入值

  • 只提供被插入的值即可：

    INSERT INTO table_name
    VALUES (value1,value2,value3,...);
    

  • 指定列名及被插入的值：

    INSERT INTO table_name (column1,column2,column3,...)
    VALUES (value1,value2,value3,...);
    

• update 更新表格内容

  UPDATE table_name
  SET column1=value1,column2=value2,...
  WHERE some_column=some_value;
  #如果省略了 WHERE 子句，将更新对应列所有内容
  

• delete 删除表格内容

  DELETE FROM table_name
  WHERE some_column=some_value;
  #如果省略了 WHERE 子句，将删除对应列所有内容
  

• like 搜索列的指定模式

  SELECT column_name(s)
  FROM table_name
  WHERE column_name [ NOT ] LIKE pattern;
  

• 通配符

  • %：替代 0 个或多个字符；
  • _：替代一个字符；
  • [charlist]：字符列中的任何单一字符；
  • [^charlist]或[!charlist]：不在字符列中的任何单一字符；

• in 在 WHERE 子句中规定多个值。

  SELECT column_name(s)
  FROM table_name
  WHERE column_name IN (value1,value2,...);
  

• between 选取介于两个值之间的数据范围内的值，这些值可以是数值、文本或者日期。

  SELECT column_name(s)
  FROM table_name
  WHERE column_name [ NOT ] BETWEEN value1 AND value2;
  

• as 为列或表指定别名

  SELECT column_name AS alias_name
  FROM table_name;
  
  SELECT column_name(s)
  FROM table_name AS alias_name;
  

• union 操作符用于合并两个或多个 SELECT 语句的结果集；

  • 每个 SELECT 语句必须拥有相同数量的列；
  • 列必须拥有相似的数据类型；
  • 每个 SELECT 语句中的列的顺序必须相同；
  • all 允许有重复值；
  • 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名；

  SELECT column_name(s) FROM table1
  UNION [ALL]
  SELECT column_name(s) FROM table2;
  

• 复制表

  • select * into ···
  • insert into tb1 select ···

• 创建表

  CREATE TABLE table_name
  (
      column_name1 data_type(size) [constraint_name],
      column_name2 data_type(size) [constraint_name],
      column_name3 data_type(size) [constraint_name],
      # size 为列的最大长度
      ....
  );
  

• 约束constraint

  • NOT NULL：指示某列不能存储 NULL 值；
  • UNIQUE：保证某列的每行必须有唯一的值；
  • PRIMARY KEY：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录；
  • FOREIGN KEY ：保证一个表中的数据匹配另一个表中的值的参照完整性；
  • CHECK：保证列中的值符合指定的条件；
  • DEFAULT：规定没有给列赋值时的默认值；

• not null

   #创建
  CREATE TABLE Persons
  (
      Age int NOT NULL,
  );
  #修改
  ALTER TABLE Persons
  MODIFY Age int NOT NULL;
  #删除
  ALTER TABLE Persons
  MODIFY Age int NULL;
  

• unique

  #创建
  CREATE TABLE Persons
  (
      P_Id int NOT NULL,
      LastName varchar(255) NOT NULL,
      UNIQUE (P_Id)
      #CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
  );
  
  #添加
  ALTER TABLE Persons
  ADD UNIQUE (P_Id);
  #ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);
  
  #删除
  ALTER TABLE Persons
  DROP INDEX uc_PersonID;
  

• primary key

  • 主键唯一且不空

  #创建主键
  CREATE TABLE Persons
  (
      P_Id int NOT NULL,
      LastName varchar(255) NOT NULL,
      PRIMARY KEY (P_Id)
  );
  #添加主键
  ALTER TABLE Persons
  ADD PRIMARY KEY (P_Id);
  #删除主键
  ALTER TABLE Persons
  DROP PRIMARY KEY;
  

• forgein key

  #创建
  CREATE TABLE Orders
  (
      O_Id int NOT NULL,
      P_Id int,
      PRIMARY KEY (O_Id),
      FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
  );
  #添加
  ALTER TABLE Orders
  ADD [CONSTRAINT fk_PerOrders]
  FOREIGN KEY (P_Id)
  REFERENCES Persons(P_Id);
  #删除
  ALTER TABLE Orders
  DROP FOREIGN KEY fk_PerOrders;
  

• check

  CREATE TABLE Persons
  #创建
  (
      P_Id int NOT NULL,
      City varchar(255),
      CHECK (P_Id>0)
      #CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes');
  );
  
  #修改
  ALTER TABLE Persons
  ADD CHECK (P_Id>0)
  #ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes');
  
  #删除
  ALTER TABLE Persons
  DROP CHECK chk_Person;
  

• default

  #创建
  CREATE TABLE Persons
  (
      P_Id int NOT NULL,
      City varchar(255) DEFAULT 'Sandnes'
      OrderDate date DEFAULT GETDATE()
  );
  #添加
  ALTER TABLE Persons
  ALTER City SET DEFAULT 'SANDNES'
  #删除
  ALTER TABLE Persons
  ALTER City DROP DEFAULT
  

• 索引

  # 创建
  CREATE INDEX index_name
  #CREATE UNIQUE INDEX index_name #唯一索引
  ON table_name (column_name)
  
  # 删除
  ALTER TABLE table_name DROP INDEX index_name
  

• 视图

  CREATE VIEW name AS
  ...
  SHOW CREATE VIEW name
  DROP VIEW name
  CREATE OR REPLACE VIEW
  

• 函数

  -- 所有MySQL变量都必须以@开始
  -- 参数有类型：IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）
  CREATE PROCEDURE f1(IN p1)
  BEGIN
    SELECT *
    FROM ...
  END;
  -- DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符
  
  CALL f1(@p)
  DROP PROCEDURE f1 IF EXISTS 
  

• 用户

  CREATE USER kaka IDENTIFIED BY [PASSWORD] 'pass'
  RENAME USER kaka TO pka;
  DROP USER pka;
  SHOW GRANTS FOR pka;
  GRANT SELECT ON pka db.* TO pka@10.182.%; -- 授权命令、库、表、ip给指定用户
  REVOKE SELECT ON pka db.* FROM pka@10.182.%; -- 撤销已授权的命令
  SET PASSWORD FOR pka = Password('passaa'); -- 更改密码
  

• 显示信息

  show engines: 显示引擎信息；
  show create tables tablename: 显示表的详细信息
  SHOW STATUS，用于显示广泛的服务器状态信息；
  SHOW CREATE DATABASE，用来显示创建特定数据库；
  SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安全权限；
  SHOW ERRORS，显示错误；
  SHOW WARNINGS，显示警告；
  SHOW CHARACTER SET，显示所有可用的字符集以及每个字符集的描述和默认校对；
  SHOW COLLATION，此语句显示所有可用的校对，以及它们适用的字符集；
  SHOW VARIABLES LIKE ' %'，显示变量；
  SHOW PROCESSLIST，显示所有活动进程；
  HELP SHOW;显示允许的SHOW语句
  

%：匹配任意次数任意字符； _：匹配单个字符 尽量不要放在靠前位置，会使搜索变慢；

• 内置函数

  concat()：拼接；
  trim(),rtrim(), ltrim()：去除空格；
  
  Left() 返回串左边的字符
  Length() 返回串的长度
  Locate() 找出串的一个子串
  Lower() 将串转换为小写
  Soundex() 返回串的SOUNDEX值
  SubString() 返回子串的字符
  Upper() 将串转换为大写
  
  AddDate() 增加一个日期（天、周等）
  AddTime() 增加一个时间（时、分等）
  CurDate() 返回当前日期
  CurTime() 返回当前时间
  Date() 返回日期时间的日期部分
  DateDiff() 计算两个日期之差
  Date_Add() 高度灵活的日期运算函数
  Date_Format() 返回一个格式化的日期或时间串
  Day() 返回一个日期的天数部分
  DayOfWeek() 对于一个日期，返回对应的星期几
  Hour() 返回一个时间的小时部分
  Minute() 返回一个时间的分钟部分
  Month() 返回一个日期的月份部分
  Now() 返回当前日期和时间
  Second() 返回一个时间的秒部分
  Time() 返回一个日期时间的时间部分
  Year() 返回一个日期的年份部分`
  

• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。

• 事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是 WHERE过滤行，而HAVING过滤分组。

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔

• UNION中的每个查询必须包含相同的列、表达式或聚集函数

• 两个最常使用的引擎为MyISAM和InnoDB， 前者支持全文本搜索，而后者不支持。

• 如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL 降低INSERT语句的优先级，

• 单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。

• 如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不 是逐行删除表中的数据）

• 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引

• 利用视图，可一 次性编写基础的SQL，然后根据需要多次使用

• 更新一个视图将更新其基表，视图本身没有数据，如果你对视图增加或删除行，实际上是对其基表增加或删除行

• MySQL游标只能用于存储过程（和函数）

• 只有表才支持触发器，视图不支持（临时表也不支持）。

• ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后），START TRANSACTION ... COMMIT;

flush tables with read lock：添加全局锁； unlock tables：释放全局锁； lock tables t_student read：表级别的共享锁，也就是读锁； lock tables t_student write：表级别的独占锁，也就是写锁；