用得上的MySQL操作

2017-08-16 本文已影响43人有苦向瓜诉说

预备知识：

database->table->data
语句大写小写皆可，推荐关键字大写。末尾加分号，（特殊的可以不加也可以加）
mysql有数据类型，包含int，float，double，char，date等（char类型用单引号括起来就行）
约束

PRIMARY KEY 主键
CONSTTAINT <自定义主键名> PRIMARY KEY(键名）
CONSTRAINT <自定义主键名 >PRIMARY KEY (proj_name1,proj_name2）复合主键
default  '10' ,默认值约束，
UNIQUE（proj_name1) 唯一约束
NOT NULL非空约束
外键约束:FOREIGN REFERENCES

必要的操作

登陆数据库：
```
  mysql -u root -p
```
退出数据库
```
  quit/exit；
```

数据库创建删除

创建数据库：
```
CREATE DATABASE databaseName;
```
删除数据库：
```
DROP DATABASE databaseName;
```
查看数据库：
```
show  databases;
```

连接某个数据库

 use databaseName;（这里可以不加分号）

加载sql文件，可用于备份的恢复
```
  source filepath；
```

表的创建删除

创建表： CREATE TABLE 表的名字 ( 列名a 数据类型(数据长度), 列名b 数据类型(数据长度)，列名c 数据类型(数据长度) );
```
CREATE TABLE employee(id int(10),name char(20),phone int(12));
```
删除表
```
DROP TABLE　employee;
```
查看表：
```
show tables；
```

增删查改

插入数据

INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);

查找数据（常用）

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

查看所有：
```
 SELECT * FROM employee;
```
查看部分：
```
SELECT name,age FROM employee;
```

增加where:

SELECT name,age FROM employee WHERE age>25;

使用AND和OR

 SELECT name,age FROM employee WHERE age<25 OR age>30;
 SELECT name,age FROM employee WHERE age>25 AND age<30;   //where age BETWEEN 25 AND 30 等同于where age>=25 and age<=30;

使用IN和NOT IN

 SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
 SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');

使用通配符：LIKE与通配符一起，_表示一个未指定字符，%表示不定个未指定字符。

 SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
 SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

对结果排序：ORDER BY　列名　ASC/DESC;（默认为升序）

 SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

内置函数：COUNT(可用于任何类型）,SUM（只用于数字类型）,AVG,MAX,MIN（数值，字符串，日期类型）
```
 SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
```
AS：重命名

子查询：

SELECT of_dpt,COUNT(proj_name) AS count_project FROM projectWHERE of_dpt IN(SELECT in_dpt FROM employee WHERE name='Tom');

连接查询：把多个表当作一个新的表来使用。

SELECT id,name,people_num FROM employee,department WHERE employee.in_dpt = department.dpt_name ORDER BY id;
或者使用   
SELECT id,name,people_num FROM employee JOIN department ON employee.in_dpt = department.dpt_name ORDER BY id;

修改表结构，表内容：

重命名table， RENAME TABLE 原名 TO 新名字; ALTER TABLE 原名 RENAME 新名; ALTER TABLE 原名 RENAME TO 新名;
```
   rename table oldname ＴＯ newName;
```
删除table： DROP TABLE 表名字;
增加一列： ALTER TABLE 表名字 ADD COLUMN 列名字数据类型约束; 或： ALTER TABLE 表名字 ADD 列名字数据类型约束;
新增加的列，被默认放置在这张表的最右边。如果要把增加的列插入在指定位置，则需要在语句的最后使用AFTER关键词(“AFTER 列1” 表示新增的列被放置在 “列1” 的后面)。如果想放在第一列的位置，则使用 FIRST 关键词，
```
ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;
```
删除一列： ALTER TABLE 表名字 DROP COLUMN 列名字; 或： ALTER TABLE 表名字 DROP 列名字;
```
alter table employee drop test;
```
更改一列：ALTER TABLE 表名字 CHANGE 原列名新列名数据类型约束;

注意：这条重命名语句后面的 “数据类型” 不能省略，否则重命名失败。
当原列名和新列名相同的时候，指定新的数据类型或约束，就可以用于修改数据类型或约束。需要注意的是，修改数据类型可能会导致数据丢失，所以要慎重使用。

    alter table employee change height shengao int(3) default 170;

改变数据类型： ALTER TABLE 表名字 MODIFY 列名字新数据类型;（同上）
修改表中的某个值： UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;（如果没有where，则全部更改）
```
update employee set age=32,salary=300 where name=‘TOM';
```
删除一行记录： DELETE FROM 表名字 WHERE 条件;（不加where会全部删除）
```
delete from employee where name='Ton';
```

索引

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
CREATE INDEX 索引名 ON 表名字 (列名);

我们用这两种语句分别建立索引：

    ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名为idx_id的索引
    CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名为idx_name的索引

查看新建的索引：

SHOW INDEX FROM 表名字;

视图

视图即一个虚拟的表

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

备份恢复

备份：使用mysqldump程序(不在mysql下使用，在命令行）备份数据库，产生一个SQL脚本

mysqldump -u root 数据库名>备份文件名; #备份整个数据库
mysqldump-u root 数据库名表名字>备份文件名;#备份整个表
注意：若有密码还需要mysqldump aa -u root -p > d:\aaa.sql.bak //aa为数据库
或者mysqldump --user=root -p aa > d:\aaa.sql

恢复

：source filepath直接载入

：先创建一个空数据库，然后执行 mysql -u root test < bak.sq

导入导出

导入： LOAD DATA INFILE '文件路径' INTO TABLE 表名字;
导出： SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;