MySQL基础

database

数据库导入导出

1. mysql批量执行：

在一个文件中写入sql语句，例在test文件中写入show databases;
shell>   mysql -h 172.16.37.9 -u lx-p < test
Enter password: ********

2. 数据库导入导出：

导入：
1)mysql> USE 数据库名;            [数据库要事先存在]
mysql> SOURCE d:/test.sql; 
2) [数据库要事先存在]
mysqldump --default-character-set=utf8 -u用户名 -p 数据库名 < 目标文件(如d:\test.sql)

导出：
mysqldump --default-character-set=utf8 -u用户名 -p 数据库名 > 目标文件(如d:\test.sql)
例mysqldump -u root -p tpweb > d:\test.sql

MySQL支持的数据类型：

1.字符型

2.数值型

3.日期时间型

date
time
datetime
year(2)
year(4)

4.类型的修饰符

例：

/*创建dept表*/
create table dept(
deptno int primary key,
dname varchar(30),
local varchar(30))
--创建emp表
create table emp(
empno int primary key,
ename varchar(30),
deptno int foreign key references dept(deptno) --创建外键关联dept表的主键
)

MariaDB的交互命令接口：

mysql>
    可运行的命令有两类：
    客户端命令：在mysql客户端本地直接运行的命令
        获取完整列表：
            mysql> help
    服务器端命令：由客户端将命令通过协议封闭成报文发往mysqld，由mysqld运行，并取回运行结果至客户端；
            即SQL语句，这些语句必须有语句结束符，默认为; 

            客户端命令：
                quit, \q:退出
                help, \h:帮助
                clear, \c: 中止正在编写的语句；类似bash中的Ctrl+c的功能；
                go, \g: 语句结束符
                ego, \G: 语句结束符，但竖排显示每行数据；
                delimter, \d: 定义语句结束符；
                use, \u: 设定要操作默认数据库；

            SQL语句可以通过help COMMAND获得帮助
                例:help create user;

数据库管理操作

创建数据库
    CREATE DATABASE 数据库名
    create database mydata
查看已有的数据库列表：
    SHOW DATABASES; 
查看某库创建时使用的语句：
    SHOW CREATE DATABASE db_name; 
查看MySQL支持的所有字符集：
    SHOW CHARACTER SET;
查看MySQL支持的所有Collate：
    SHOW COLLATION; 
删除数据库
    DROP DATABASE 数据库名

表管理操作：
    查看表结构
        desc 数据库名.表名;
        例：desc mysql.user;

建表要求

• 字段不可分割；
• 应该拥有主键；
• 表间不能拥相同的非主键字段；

约束

• 主键约束：不能同已有数据，非空；(Not Null)；一张表只能有一个惟一键；primary key
• 惟一键约束：不能同已有数据；一张表上可以存在多个惟一键；unique key
• 外键约束：表之间对应字段间的约束关系；foreign key
• 检查性约束：自定义的表达式约束；
• 索引：将表中一个或多个字段抽取出来，按特定数据结构重新组织和存放的数据；

CRUD操作

• 创建表：CREATE TABLE
  CREATE TABLE [IF NOT EXISTS] tbl_name (col1 type1 dec1, col2 type2 dec2, ...)
  例如：学号、姓名、年龄、性别
  学号：StuID, INT
  姓名：Name, 定长字符，30
  年龄：Age, TINYINT
  性别：Gender, 枚举，ENUM('M','F')
  示例：CREATE TABLE IN NOT EXISTS students (StuID INT UNSGINED NOT NULL PRIMARY KEY, Name CHAR(30) NOT NULL, Age TINYINT UNSIGNED, Gender ENUM('M','F'));
• 查看表结构：
  DESC tbl_name

• 查看数据库的表：
  SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
  LIKE用于作模糊匹配，支持通配符：
  _: 匹配任意单个字符；
  %: 匹配任意长度的任意字符；
  例如： show create table test_table_name;

• 删除表：DROP TABLE
  DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

• 修改表
  修改字段属性
  alter table 表名 modify|add 列名 新的数据类型
  例如： alter table customer_data add status tinyint(2) not null default 2
  修改字段名字
  alter table 表名 change 旧字段 new字段 字段类型 字段位置
  重新定义自增id
  alter table tablename auto_increment=1
  查看索引 show index from 数据库表名
  alter table 数据库add index 索引名称(数据库字段名称)
  PRIMARY KEY（主键索引）
  ALTER TABLE table_name ADD PRIMARY KEY ( column )
  UNIQUE(唯一索引)
  ALTER TABLE table_name ADD UNIQUE (column)
  INDEX(普通索引)
  mysql>ALTER TABLE table_name ADD key( column )

ALTER TABLE cms_ban ADD key(expire)

• 表选项：
  ENGINE = engine_name 存储引擎
  注意：MySQL支持插件存储引擎
  查看支持的所有存储引擎
  SHOW ENGINES;
  存储引擎：表类型 创建每个表时，都可以单独指明其表类型；但，建议同一个库内的所有表，应该使用同一种类型；
  例如engine=InnoDB

• 查看表上的索引：
  SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
  例如：SHOW INDEXES FROM mydb.teachers;

• 索引管理：
  创建索引：
  CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name,...)

  删除索引：
  DROP INDEX index_name ON tbl_name

• 插入数据
  INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
• 删除数据
  DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
• 查询数据
  SELECT单表查询：
  SELECT [DISTINCT] [SQL_CACHE | SQL_NO_CACHE] col_name AS Alias, col_name AS Alias, ... [FROM table_references [WHERE where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...]

WHERE后的条件表达式
    >, <, >=, <=, ==, !=
    BETWEEN ... AND ...
    LIKE 'pattern'
        _ 表示任意单个字符。匹配单个任意字符
        % 表示任意个或多个字符。可匹配任意类型和长度的字符。
    如果我就真的要查%或者_，怎么办呢？使用escape，转义字符后面的%或_就不作为通配符了，注意前面没有转义字符的%和_仍然起通配符作用
    RLIKE 'pattern' 使用正则表达式做匹配；
组合条件：
    AND
    OR
    NOT
<div class="md-section-divider"></div>

• 更新数据UPDATE
  UPDATE table_reference SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
  updata 表名 set 字段名1='新值',字段2='新值'... where 字段='值';
  例如：update student set name = 'test' where id = 1;

索引

mysql> explain select Newcode from caiji_dong_info_bj where dongname like '%玉泉西里二区%' ；

解释：
虽然一般情况下like用索引的情况是玉泉西里二区%但是当你所有所需数据都在一个索引(id无所谓)中还是会用到索引的。
原因：
一个包含查询所需的字段的索引称为 covering index 覆盖索引。MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提供效率。