数据库基本操作

来源：http://c.biancheng.net/mysql/10/

1.进入数据库
mysql -h 127.0.0.1 -u root -p
mysql   -u root -p

使用 service 启动：service mysqld start

使用 service 停止：service mysqld stop

2.MySQL 的配置文件是 my.cnf，一般会放在 /etc/my.cnf 或 /etc/mysql/my.cnf 目录下

3.查看数据库
SHOW DATABASES [LIKE '数据库名'];

4.创建数据库
CREATE DATABASE [IF NOT EXISTS] <数据库名>
 CREATE DATABASE test_db;

5.修改数据库
ALTER DATABASE [数据库名] 

6.删除数据库
DROP DATABASE [ IF EXISTS ] <数据库名>

7.选择数据库
USE <数据库名>

8.MySQL注释
单行注释可以使用#注释符，多行注释使用/* */注释符
行注释可以使用--注释符，--注释符后需要加一个空格，注释才能生效

1. MySQL创建数据表（CREATE TABLE语句）
创建一个新表 
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];  
CREATE TABLE tb_emp1
      (
      id INT(11),
      name VARCHAR(25),
     deptId INT(11),
     salary FLOAT
    );

 查看表结构：
desc tb_emp1;

查看表
SHOW TABLES;

2. MySQL ALTER TABLE：修改数据表
ALTER TABLE <表名> [修改选项]
修改选项的语法格式如下：
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

ALTER TABLE student RENAME TO tb_students_info;

3. MySQL修改/删除字段
修改字段名称
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；
ALTER TABLE tb_emp1 CHANGE col1 col3 CHAR(30);

修改字段数据类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
ALTER TABLE tb_emp1  MODIFY name VARCHAR(30);

删除字段
ALTER TABLE tb_emp1 DROP col2;

4. MySQL删除数据表（DORP TABLE语句）
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
 
5. MySQL删除被其它表关联的主表
ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_emp4_emp5;

6. MySQL查看表结构命令
DESCRIBE：以表格的形式展示表结构
DESCRIBE <表名>;      
DESC tb_emp1;

SHOW CREATE TABLE：以SQL语句的形式展示表结构
SHOW CREATE TABLE <表名>;
SHOW CREATE TABLE tb_emp1 \g;

7. MySQL数据表添加字段（三种方式）
在末尾添加字段
ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
ALTER TABLE student ADD age INT(4);

在开头添加字段
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;
ALTER TABLE student ADD stuId INT(4) FIRST;

在中间位置添加字段
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;
ALTER TABLE student ADD stuno INT(11) AFTER name;

MYSQL查询数据表
1.SELECT数据库查询语句
SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]
其中，各条子句的含义如下：
{*|<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称。
<表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。
WHERE <表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。
GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
[ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。
[LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。

 SELECT * FROM tb_students_info;
SELECT id,name FROM tb_students_info;

2.MySQL使用DISTINCT过滤重复数据
SELECT DISTINCT <字段名> FROM <表名>;
SELECT DISTINCT * FROM student;
SELECT DISTINCT age FROM student;
SELECT COUNT(DISTINCT name,age) FROM student;

3.MySQL AS：设置别名
SELECT stu.name,stu.height FROM tb_students_info AS stu;
SELECT name AS student_name, age AS student_age FROM tb_students_info;

4.MySQL LIMIT：限制查询结果的条数
LIMIT 初始位置，记录数
在 tb_students_info 表中，使用 LIMIT 子句返回从第 4 条记录开始的行数为 5 的记录
 SELECT * FROM tb_students_info LIMIT 3,5;

LIMIT 记录数
显示 tb_students_info 表查询结果的前 4 行
SELECT * FROM tb_students_info LIMIT 4;

LIMIT 记录数 OFFSET 初始位置
在 tb_students_info 表中，使用 LIMIT OFFSET 返回从第 4 条记录开始的行数为 5 的记录
SELECT * FROM tb_students_info LIMIT 5 OFFSET 3;

5.MySQL ORDER BY：对查询结果排序
ORDER BY <字段名> [ASC|DESC]
下面查询 tb_students_info 表的所有记录，并对 height 字段进行排序
SELECT * FROM tb_students_info ORDER BY height;

 SELECT name,height FROM tb_students_info ORDER BY height,name;
 SELECT name,height FROM tb_student_info ORDER BY height DESC,name ASC;

6.MySQL WHERE：条件查询数据
WHERE 查询条件

查询条件可以是：
带比较运算符和逻辑运算符的查询条件
带 BETWEEN AND 关键字的查询条件
带 IS NULL 关键字的查询条件
带 IN 关键字的查询条件
带 LIKE 关键字的查询条件

SELECT name,height FROM tb_students_info  WHERE height=170;
SELECT name,age FROM tb_students_info  WHERE age<22;
SELECT name,age,height FROM tb_students_info  WHERE age>21 AND height>=175;

7.MySQL LIKE：模糊查询
[NOT] LIKE  '字符串'
在 tb_students_info 表中，查找所有以字母“T”开头的学生姓名。
SELECT name FROM tb_students_info WHERE name LIKE 'T%';

“_”只能代表单个字符，字符的长度不能为 0。
在 tb_students_info 表中，查找所有以字母“y”结尾，且“y”前面只有 4 个字母的学生姓名
SELECT name FROM tb_students_info WHERE name LIKE '____y';

默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字。
SELECT name FROM tb_students_info WHERE name LIKE BINARY 't%';

8.MySQL BETWEEN AND：范围查询
[NOT] BETWEEN 取值1 AND 取值2
在表 tb_students_info 中查询年龄在 20 到 23 之间的学生姓名和年龄
SELECT name,age FROM tb_students_info  WHERE age BETWEEN 20 AND 23;
SELECT name,login_date FROM tb_students_info  WHERE login_date BETWEEN '2015-10-01' AND '2016-05-01';

9.MySQL IS NULL：空值查询
IS [NOT] NULL
查询 tb_students_info 表中 login_date 字段是 NULL 的记录
SELECT `name`,`login_date` FROM tb_students_info  WHERE login_date IS NULL;
SELECT `name`,login_date FROM tb_students_info  WHERE login_date IS NOT NULL;

10.MySQL使用GROUP BY分组查询
GROUP BY  <字段名>
SELECT `name`,`sex` FROM tb_students_info  GROUP BY sex;
SELECT age,sex,GROUP_CONCAT(name) FROM tb_students_info  GROUP BY age,sex;

聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，COUNT() 用来统计记录的条数；SUM() 用来计算字段值的总和；AVG() 用来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值。
SELECT sex,COUNT(sex) FROM tb_students_info   GROUP BY sex;

WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。
 SELECT sex,GROUP_CONCAT(name) FROM tb_students_info 
    ->GROUP BY sex WITH ROLLUP;

11.MySQL HAVING：过滤分组
HAVING <查询条件>
但是 WHERE 和 HAVING 关键字也存在以下几点差异：
一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

SELECT name,sex,height FROM tb_students_info  HAVING height>150;
SELECT GROUP_CONCAT(name),sex,height FROM tb_students_info  GROUP BY height HAVING AVG(height)>170;

12.MySQL CROSS JOIN：交叉连接
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]
SELECT * FROM tb_course CROSS JOIN tb_students_info  WHERE tb_students_info.course_id = tb_course.id;

12.MySQL INNER JOIN：内连接
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
SELECT s.name,c.course_name FROM tb_students_info s INNER JOIN tb_course c  ON s.course_id = c.id;

13.MySQL LEFT/RIGHT JOIN：外连接
左连接
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
SELECT s.name,c.course_name FROM tb_students_info s LEFT OUTER JOIN tb_course c 
    -> ON s.`course_id`=c.`id`;
右连接
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
 SELECT s.name,c.course_name FROM tb_students_info s RIGHT OUTER JOIN tb_course c 
    -> ON s.`course_id`=c.`id`;

14.MySQL子查询
WHERE <表达式> <操作符> (子查询)
使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名
SELECT name FROM tb_students_info 
    -> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');

15.MySQL INSERT：插入数据（添加数据）
INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

INSERT INTO tb_courses
    -> (course_id,course_name,course_grade,course_info)
    -> VALUES(1,'Network',3,'Computer Network');

使用 INSERT 插入数据时，允许列名称列表 column_list 为空，此时值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
INSERT INTO tb_courses
    -> VLAUES(3,'Java',4,'Java EE');

INSERT INTO <表名>
SET <列名1> = <值1>,
        <列名2> = <值2>,
        …

INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中。
INSERT INTO tb_courses_new
    -> (course_id,course_name,course_grade,course_info)
    -> SELECT course_id,course_name,course_grade,course_info
    -> FROM tb_courses;

16.MySQL UPDATE：修改数据（更新数据）
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

修改表中的字段所有数据
UPDATE tb_courses_new SET course_grade=4;
根据条件修改表中的数据
UPDATE tb_courses_new SET course_name='DB',course_grade=3.5 WHERE course_id=2;

17.MySQL DELETE：删除数据
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

删除表中的全部数据
DELETE FROM tb_courses_new;

在 tb_courses_new 表中，删除 course_id 为 4 的记录
DELETE FROM tb_courses  WHERE course_id=4;

18.MySQL TRUNCATE：清空表记录
TRUNCATE [TABLE] 表名
TRUNCATE TABLE tb_student_course;

TRUNCATE 和 DELETE 的区别
从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。
DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。
总结
当不需要该表时，用 DROP；当仍要保留该表，但要删除所有记录时，用 TRUNCATE；当要删除部分记录时，用 DELETE。

MySQL存储过程
1.MySQL创建存储过程（CREATE PROCEDURE）
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

创建名称为 ShowStuScore 的存储过程，存储过程的作用是从学生成绩信息表中查询学生的成绩信息
mysql> DELIMITER //
mysql> CREATE PROCEDURE ShowStuScore()
    -> BEGIN
    -> SELECT * FROM tb_students_score;
    -> END //
 
创建名称为 GetScoreByStu 的存储过程，输入参数是学生姓名。存储过程的作用是通过输入的学生姓名从学生成绩信息表中查询指定学生的成绩信息
mysql> DELIMITER //
mysql> CREATE PROCEDURE GetScoreByStu
    -> (IN name VARCHAR(30))
    -> BEGIN
    -> SELECT student_score FROM tb_students_score
    -> WHERE student_name=name;
    -> END //

2.查看存储过程的状态
SHOW PROCEDURE STATUS LIKE 存储过程名;
 SHOW PROCEDURE STATUS LIKE 'showstuscore' \G

查看存储过程的定义
SHOW CREATE PROCEDURE 存储过程名;
SHOW CREATE PROCEDURE showstuscore \G

3.MySQL修改存储过程（ALTER PROCEDURE）
ALTER PROCEDURE 存储过程名 [ 特征 ... ]

特征指定了存储过程的特性，可能的取值有：
CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
NO SQL 表示子程序中不包含 SQL 语句。
READS SQL DATA 表示子程序中包含读数据的语句。
MODIFIES SQL DATA 表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
DEFINER 表示只有定义者自己才能够执行。
INVOKER 表示调用者可以执行。
COMMENT 'string' 表示注释信息。

ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;

4.MySQL删除存储过程（DROP PROCEDURE）
DROP PROCEDURE [ IF EXISTS ] <过程名>
DROP PROCEDURE ShowStuScore;

5.MySQL存储函数详解
CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body

mysql> DELIMITER //
mysql> CREATE FUNCTION func_student(id INT(11))
    -> RETURNS VARCHAR(20)
    -> COMMENT '查询某个学生的姓名'
    -> BEGIN
    -> RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
    -> END //

6.MySQL调用存储过程和函数
CALL sp_name([parameter[...]]);
 CALL ShowStuScore();
 CALL GetScoreByStu('Green');

调用存储函数
SELECT func_student(3);

7.MySQL触发器
在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器，其它 SQL 语句则不会激活触发器。