MySQL 基础 简单操作

一、数据库基础

什么是数据库

数据库：保存有组织的数据的容器（通常是一个文件或一组文件）。

表：是一种结构化的文件，可以用来存储数据（类似Excel表）。数据库就是由成千上万个表组成。

什么事SQL

　　sql是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。

SQL的语句规范：

<1> 在数据库系统中，SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。建议命令大写，表名库名小写；

<2> SQL语句可单行或多行书写，以“;”结尾。关键词不能跨多行或简写。

<3> 用空格和缩进来提高语句的可读性。子句通常位于独立行，便于编辑，提高可读性。

什么是Mysql

Mysql：是一种数据库软件，用来操作数据库。

MySQL的优点：

1. MySQL开放源代码，一般都可以免费使用，成本低。
2. 执行效率高。
3. 操作简单，易学。

二、连接数据库

连接数据库需要以下信息：

主机名：本地一般为（localhost）

端口号：MySQL默认端口为：3306

用户名和密码：

三、数据类型

常见的数值类型

作用：id，年龄，工资等需要做运算的变量

BIT类型

BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位。
注意：对于位字段需要使用函数读取
　　　bin()显示为二进制
　　　hex()显示为十六进制

实例：

mysql> create table b(id bit);
Query OK, 0 rows affected (0.06 sec)

mysql> desc b;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert b values(1);  #插入数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from b; #查找数据，看不到
+------+
| id   |
+------+
|     |
+------+
1 row in set (0.00 sec)

mysql> select bin(id) from b; #加上bin得出数据
+---------+
| bin(id) |
+---------+
| 1       |
+---------+
1 row in set (0.00 sec)

字符串类型：

存储字符串：

CHAR系列 ：CHAR VARCHAR
TEXT系列 ： TINYTEXT TEXT MEDIUMTEXT LONGTEXT

存储二进制数据：

BINARY系列： BINARY VARBINARY
BLOB 系列 ：  TINYBLOB BLOB MEDIUMBLOB LONGBLOB

char (m)

      CHAR列的长度固定为创建表时声明的长度： 0 ~ 255。其中m代表字符串的长度。
      PS: 即使数据小于m长度，也会占用m长度
varchar(m)
      VARCHAR列中的值为可变长字符串，长度： 0 ~ 65535。其中m代表该数据类型所允许保存的字符串
      的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

      注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度
      更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡

text
      text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

mediumtext
      文本的最大长度为 16,777,215 (2**24 − 1)字节

ongtext
      文本的最大长度为 4,294,967,295 or 4GB (2**32 − 1)字节
 

在查询的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。
mysql> create table t1(x char(5),y varchar(5));
mysql> insert into t1 values('你瞅啥  ','瞅你妹                 ');
mysql> select x,length(x),y,length(y) from t1;
+--------+-----------+----------+-----------+
| x      | length(x) | y        | length(y) |
+--------+-----------+----------+-----------+
| 你好啊 |         9 | 你好啊    |        11 |
+--------+-----------+----------+-----------+

时间类型

作用：存储时间、日期等数据

例：

mysql> CREATE TABLE dty(d DATE,t TIME,dt DATETIME,y YEAR,ts TIMESTAMP);
Query OK, 0 rows affected (0.14 sec)

mysql> desc dty;

+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d     | date      | YES  |     | NULL              |                             |
| t     | time      | YES  |     | NULL              |                             |
| dt    | datetime  | YES  |     | NULL              |                             |
| y     | year(4)   | YES  |     | NULL              |                             |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
mysql> INSERT INTO dty VALUES (now(),now(),now(),now(),now());

Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> SELECT * FROM dty;
+------------+----------+---------------------+------+---------------------+
| d          | t        | dt                  | y    | ts                  |
+------------+----------+---------------------+------+---------------------+
| 2018-06-23 | 22:17:36 | 2018-06-23 22:17:36 | 2018 | 2018-06-23 22:17:36 |
+------------+----------+---------------------+------+---------------------+
1 row in set (0.01 sec)

枚举类型和集合类型：

字段的值只能在给定范围中选择，如单选框，多选框
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

例：

mysql> CREATE TABLE shorts(name VARCHAR(40), sex ENUM("man", "woman"));
Query OK, 0 rows affected (0.08 sec)

mysql> DESC shorts;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(40)         | YES  |     | NULL    |       |
| sex   | enum('man','woman') | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> INSERT INTO shorts VALUES("wallace","man");
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM shorts;
+---------+------+
| name    | sex  |
+---------+------+
| wallace | man  |
+---------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO shorts(name, sex) VALUES  ("wallace","aaa");  #插入不存在的值会报错
ERROR 1265 (01000): Data truncated for column 'sex' at row 1    

数据库的操作

-- 1.创建数据库（在磁盘上创建一个对应的文件夹）
    create database [if not exists] db_name [character set xxx] 

-- 2.查看数据库
    show databases;查看所有数据库
    show create database db_name; 查看数据库的创建方式

-- 3.修改数据库
    alter database db_name [character set xxx] 

-- 4.删除数据库
    drop database [if exists] db_name;

-- 5.使用数据库
    切换数据库 use db_name; -- 注意：进入到某个数据库后没办法再退回之前状态，但可以通过use进行切换
    查看当前使用的数据库 select database();

数据表操作：

创建表：

CREATE TABLE tablename(字段1 数据类型,
　　　　　　　　　　　　　字段2 数据类型 ...)

mysql> CREATE TABLE dty(d DATE,            dty:表名
　　　　　　　　　　　　　　t TIME,　　　　　　　d,t,dt,ts,t:表的字段名，类似execl的表头
　　　　　　　　　　　　　　dt DATETIME,　　　　DATE,...：表示的是每一字段的数据类型
　　　　　　　　　　　　　　y YEAR,
　　　　　　　　　　　　　　ts TIMESTAMP);

查看表信息：

desc tab_name    　　　　　　　 　　查看表结构
show columns from tab_name     查看表结构
show tables 　　　　　　　　　　　查看当前数据库中的所有的表
show create table tab_name     查看当前数据库表建表语句 

例：

查看表信息

mysql> DESC dty;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d     | date      | YES  |     | NULL              |                             |
| t     | time      | YES  |     | NULL              |                             |
| dt    | datetime  | YES  |     | NULL              |                             |
| y     | year(4)   | YES  |     | NULL              |                             |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM dty;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d     | date      | YES  |     | NULL              |                             |
| t     | time      | YES  |     | NULL              |                             |
| dt    | datetime  | YES  |     | NULL              |                             |
| y     | year(4)   | YES  |     | NULL              |                             |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql> SHOW tables;
+---------------------+
| Tables_in_tornadodb |
+---------------------+
| article             |
| b                   |
| dty                 |
| grade               |
| shorts              |
| student             |
| t                   |
| text                |
| user                |
| user_1              |
| user_article        |
| user_details        |
+---------------------+
12 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE dty;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dty   | CREATE TABLE `dty` (
  `d` date DEFAULT NULL,
  `t` time DEFAULT NULL,
  `dt` datetime DEFAULT NULL,
  `y` year(4) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表结构：

　　-- (1)增加列(字段)
      alter table tab_name add [column] 列名 类型［完整性约束条件］［first｜after 字段名］;

      #添加多个字段
      alter table users2
            add addr varchar(20),
            add age  int first,
            add birth varchar(20) after name;

   -- (2)修改一列类型
      alter table tab_name modify 列名 类型 [完整性约束条件]［first｜after 字段名］;
   -- (3)修改列
      alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］;
   -- (4)删除一列
      alter table tab_name drop [column] 列名;-- (5)修改表名
      rename table 表名 to 新表名;
　　　 drop table 表名;    删除表
   -- (6)修该表所用的字符集
      alter table student character set utf8;

　　

例：

表操作实例

添加列
mysql> ALTER TABLE b ADD (name VARCHAR(40));
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC b;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bit(1)      | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改表类型
mysql> ALTER TABLE b MODIFY name CHAR(20);
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC b;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | bit(1)   | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.04 sec)

修改列
mysql> ALTER TABLE b CHANGE name sex char(10);
Query OK, 1 row affected (0.16 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC b;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | bit(1)   | YES  |     | NULL    |       |
| sex   | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除列
mysql> ALTER TABLE b DROP sex;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC b;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

修改表名：
mysql> RENAME TABLE b TO bb;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW TABLES;
+---------------------+
| Tables_in_tornadodb |
+---------------------+
| article             |
| bb                  |

删除表：
mysql> DROP TABLE dty,t,shorts;
Query OK, 0 rows affected (0.17 sec)

向表插入数据：

<1>插入一条记录：

　　insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......);
　　insert tab_name values (value1,value2,....)  #这种方式插入的值必须一一对应
　　　　

<2>插入多条记录：
　　insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......)，
　　　　　　　　　　　　　　　　　　　　　　　　　　　　            (value1,value2,.......),
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　...                               ;

<3>set插入:
　 insert ［into］ tab_name set 字段名=值 

INSERT employee (name,gender,birthday,salary,department) VALUES
                　　　　　　("alex",1,"1985-12-12",8000,"保洁部"),
                　　　　　　("egon",1,"1987-08-08",5000,"保安部"),
                　　　　　　("yuan",1,"1990-06-06",20000,"教学部");

INSERT employee VALUES (8,"女神",0,"1992-02-12","教学部",7000,"");

INSERT employee SET name="wusir",birthday="1990-11-11";

修改表记录：

update tab_name set field1=value1,field2=value2,......[where 语句]

删除表记录：

方式1:
delete from tab_name [where ....]

方式2:
truncate table emp_new;

            /*
            如果不跟where语句则删除整张表中的数据
            delete只能用来删除一行记录
            delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop
            TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在
            事务中恢复。
            */

四、查询

-- 查询语法：

   SELECT *|field1,filed2 ...   FROM tab_name
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

-- Mysql在执行sql语句时的执行顺序：from  where  select  group by  having order by

查询全部数据：

CREATE TABLE emp(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(20),
    gender   ENUM("male","female","other"),
    age      TINYINT,
    dep      VARCHAR(20),
    city     VARCHAR(20),
   salary    DOUBLE(7,2)
);

INSERT INTO emp (name,gender,age,dep,city,salary) VALUES
                ("yuan","male",24,"教学部","河北省",8000),
                ("egon","male",34,"保安部","山东省",8000),
                ("alex","male",28,"保洁部","山东省",10000),
                ("景丽阳","female",22,"教学部","北京",9000),
                ("张三", "male",24,"教学部","河北省",6000),
                ("李四", "male",32,"保安部","北京",12000),
                ("王五", "male",38,"教学部","河北省",7000),
                ("赵六", "male",19,"保安部","河北省",9000),
                ("猪七", "female",24,"保洁部","北京",9000);

mysql> SELECT  * FROM emp;
+----+-----------+--------+------+-----------+-----------+----------+
| id | name      | gender | age  | dep       | city      | salary   |
+----+-----------+--------+------+-----------+-----------+----------+
|  1 | yuan      | male   |   24 | 教学部    | 河北省    |  8000.00 |
|  2 | egon      | male   |   34 | 保安部    | 山东省    |  8000.00 |
|  3 | alex      | male   |   28 | 保洁部    | 山东省    | 10000.00 |
|  4 | 景丽阳    | female |   22 | 教学部    | 北京      |  9000.00 |
|  5 | 张三      | male   |   24 | 教学部    | 河北省    |  6000.00 |
|  6 | 李四      | male   |   32 | 保安部    | 北京      | 12000.00 |
|  7 | 王五      | male   |   38 | 教学部    | 河北省    |  7000.00 |
|  8 | 赵六      | male   |   19 | 保安部    | 河北省    |  9000.00 |
|  9 | 猪七      | female |   24 | 保洁部    | 北京      |  9000.00 |
+----+-----------+--------+------+-----------+-----------+----------+
9 rows in set (0.00 sec)

过滤查询：

-- where字句中可以使用，where后面跟过滤的条件：

         -- 比较运算符：
                        > < >= <= <> !=
                        between 80 and 100 值在10到20之间
                        in(80,90,100) 值是10或20或30
                        like 'yuan%'  是一种模糊查询的方法
                        /*
                        pattern可以是%或者_，
                        如果是%则表示任意多字符，此例如唐僧,唐国强
                        如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__
                        */

         -- 逻辑运算符
                        在多个条件直接可以使用逻辑运算符 and or not

例：

-- 查询年纪大于24的员工
SELECT * FROM emp WHERE age>24;

-- 查询教学部的男老师信息
SELECT * FROM emp WHERE dep="教学部" AND gender="male";

order排序：按指定的列进行，排序的列即可是表中的列名，也可以是select语句后指定的别名。

-- 语法：

select *|field1,field2... from tab_name order by field [Asc|Desc]

         -- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。

例：

-- 按年龄从高到低进行排序
SELECT * FROM emp ORDER BY age DESC ;

-- 按工资从低到高进行排序
SELECT * FROM emp ORDER BY salary;

分组查询：GROUP BY 语句根据某个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG等函数进行相关查询。

-- 语法:
  SELECT column_name, function(column_name)
  　　　　　　FROM table_name
  　　　　　　WHERE 判断条件
  　　　　　　GROUP BY column_name;

例：

-- 查询男女员工各有多少人

SELECT gender 性别,count(*) 人数 FROM emp5 GROUP BY gender;

-- 查询各个部门的人数

SELECT dep 部门,count(*) 人数 FROM emp5 GROUP BY dep;

-- 查询每个部门最大的年龄

SELECT dep 部门,max(age) 最大年纪 FROM emp5 GROUP BY dep;

-- 查询每个部门年龄最大的员工姓名

SELECT * FROM emp5 WHERE age in (SELECT max(age) FROM emp5 GROUP BY dep);

-- 查询每个部门的平均工资

SELECT dep 部门,avg(salary) 最大年纪 FROM emp GROUP BY dep;

--  查询教学部的员工最高工资:

SELECT dep,max(salary) FROM emp11 GROUP BY dep HAVING dep="教学部";

-- 查询平均薪水超过8000的部门

SELECT dep,AVG(salary) FROM  emp GROUP BY dep HAVING avg(salary)>8000;

--  查询每个组的员工姓名
SELECT dep,group_concat(name) FROM emp GROUP BY dep;

-- 查询公司一共有多少员工(可以将所有记录看成一个组)

SELECT COUNT(*) 员工总人数 FROM emp;

　　　　　　　　　　　　      -- KEY: 查询条件中的每个后的词就是分组的字段

limit记录条数限制：

SELECT * from ExamResult limit 1;
SELECT * from ExamResult limit 2,5;        --  跳过前两条显示接下来的五条纪录
SELECT * from ExamResult limit 2,2;

正则表达式：

SELECT * FROM employee WHERE emp_name REGEXP '^yu';

SELECT * FROM employee WHERE emp_name REGEXP 'yun$';

SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';

五、多表查询

例：创建两个表

CREATE TABLE emp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    salary DOUBLE(7,2),
    dep_id INT
);

INSERT INTO emp (name,salary,dep_id) VALUES ("张三",8000,2),
                                            ("李四",12000,1),
                                            ("王五",5000,2),
                                            ("赵六",8000,3),
                                            ("猪七",9000,1),
                                            ("周八",7000,4),
                                            ("蔡九",7000,2);

CREATE TABLE dep(
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(20)
);

INSERT INTO dep (name) VALUES ("教学部"),
                              ("销售部"),
                              ("人事部");

mysql> select * from emp;
+----+--------+----------+--------+
| id | name   | salary   | dep_id |
+----+--------+----------+--------+
|  1 | 张三   |  8000.00 |      2 |
|  2 | 李四   | 12000.00 |      1 |
|  3 | 王五   |  5000.00 |      2 |
|  4 | 赵六   |  8000.00 |      3 |
|  5 | 猪七   |  9000.00 |      1 |
|  6 | 周八   |  7000.00 |      4 |
|  7 | 蔡九   |  7000.00 |      2 |
+----+--------+----------+--------+
7 rows in set (0.00 sec)

mysql> select * from dep;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 教学部    |
|  2 | 销售部    |
|  3 | 人事部    |
+----+-----------+
3 rows in set (0.00 sec)

联结查询：将两张表的可能性全部列出，即笛卡尔积

mysql> select * from emp,dep;
+----+--------+----------+--------+----+-----------+
| id | name   | salary   | dep_id | id | name      |
+----+--------+----------+--------+----+-----------+
|  1 | 张三   |  8000.00 |      2 |  1 | 教学部    |
|  1 | 张三   |  8000.00 |      2 |  2 | 销售部    |
|  1 | 张三   |  8000.00 |      2 |  3 | 人事部    |
|  2 | 李四   | 12000.00 |      1 |  1 | 教学部    |
|  2 | 李四   | 12000.00 |      1 |  2 | 销售部    |
|  2 | 李四   | 12000.00 |      1 |  3 | 人事部    |
|  3 | 王五   |  5000.00 |      2 |  1 | 教学部    |
|  3 | 王五   |  5000.00 |      2 |  2 | 销售部    |
|  3 | 王五   |  5000.00 |      2 |  3 | 人事部    |
|  4 | 赵六   |  8000.00 |      3 |  1 | 教学部    |
|  4 | 赵六   |  8000.00 |      3 |  2 | 销售部    |
|  4 | 赵六   |  8000.00 |      3 |  3 | 人事部    |
|  5 | 猪七   |  9000.00 |      1 |  1 | 教学部    |
|  5 | 猪七   |  9000.00 |      1 |  2 | 销售部    |
|  5 | 猪七   |  9000.00 |      1 |  3 | 人事部    |
|  6 | 周八   |  7000.00 |      4 |  1 | 教学部    |
|  6 | 周八   |  7000.00 |      4 |  2 | 销售部    |
|  6 | 周八   |  7000.00 |      4 |  3 | 人事部    |
|  7 | 蔡九   |  7000.00 |      2 |  1 | 教学部    |
|  7 | 蔡九   |  7000.00 |      2 |  2 | 销售部    |
|  7 | 蔡九   |  7000.00 |      2 |  3 | 人事部    |
+----+--------+----------+--------+----+-----------+
21 rows in set (0.00 sec)

内联结：查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。

SELECT * FROM emp,dep WHERE emp.dep_id=dep.id;

+----+--------+----------+--------+----+-----------+
| id | name   | salary   | dep_id | id | name      |
+----+--------+----------+--------+----+-----------+
|  1 | 张三   |  8000.00 |      2 |  2 | 销售部     |
|  2 | 李四   | 12000.00 |      1 |  1 | 教学部     |
|  3 | 王五   |  5000.00 |      2 |  2 | 销售部     |
|  4 | 赵六   |  8000.00 |      3 |  3 | 人事部     |
|  5 | 猪七   |  9000.00 |      1 |  1 | 教学部     |
|  7 | 蔡九   |  7000.00 |      2 |  2 | 销售部     |
+----+--------+----------+--------+----+-----------+
6 rows in set (0.00 sec)

方法二：

SELECT * FROM emp INNER JOIN dep ON emp.dep_id=dep.id;

+----+--------+----------+--------+----+-----------+
| id | name   | salary   | dep_id | id | name      |
+----+--------+----------+--------+----+-----------+
|  1 | 张三   |  8000.00 |      2 |  2 | 销售部     |
|  2 | 李四   | 12000.00 |      1 |  1 | 教学部     |
|  3 | 王五   |  5000.00 |      2 |  2 | 销售部     |
|  4 | 赵六   |  8000.00 |      3 |  3 | 人事部     |
|  5 | 猪七   |  9000.00 |      1 |  1 | 教学部     |
|  7 | 蔡九   |  7000.00 |      2 |  2 | 销售部     |
+----+--------+----------+--------+----+-----------+
6 rows in set (0.00 sec)

得到其他字段：

-- 查询李四所在的部门名称

SELECT emp.name,dep.name FROM  emp INNER JOIN dep ON emp.dep_id=dep.id WHERE emp.name="李四";

-- 查询销售部所有员工姓名以及部门名称

-- SELECT name FROM  emp WHERE dep_id in (SELECT id FROM dep WHERE name="销售部");

SELECT emp.name,dep.name FROM  emp INNER JOIN dep ON emp.dep_id=dep.id WHERE dep.name="销售部";

外连接：

--（1）左外连接：指的是左边的表一定是全部信息都有，在内连接的基础上增加左边有右边没有的结果

 SELECT * FROM emp LEFT JOIN dep　　 ON dep.id=emp.dep_id;

+----+--------+----------+--------+------+-----------+
| id | name   | salary   | dep_id | id   | name      |
+----+--------+----------+--------+------+-----------+
|  2 | 李四   | 12000.00 |      1 |    1 | 教学部    |
|  5 | 猪七   |  9000.00 |      1 |    1 | 教学部    |
|  1 | 张三   |  8000.00 |      2 |    2 | 销售部    |
|  3 | 王五   |  5000.00 |      2 |    2 | 销售部    |
|  7 | 蔡九   |  7000.00 |      2 |    2 | 销售部    |
|  4 | 赵六   |  8000.00 |      3 |    3 | 人事部    |
|  6 | 周八   |  7000.00 |      4 | NULL | NULL      |
+----+--------+----------+--------+------+-----------+
7 rows in set (0.00 sec)

--（1）外右连接：右边的表全部的信息都有，如果右边的有一些在左边的不匹配，就会为 null 在内连接的基础上增加右边有左边没有的结果

 SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id;

mysql> SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id;
+------+--------+----------+--------+----+-----------+
| id   | name   | salary   | dep_id | id | name      |
+------+--------+----------+--------+----+-----------+
|    1 | 张三   |  8000.00 |      2 |  2 | 销售部    |
|    2 | 李四   | 12000.00 |      1 |  1 | 教学部    |
|    3 | 王五   |  5000.00 |      2 |  2 | 销售部    |
|    4 | 赵六   |  8000.00 |      3 |  3 | 人事部    |
|    5 | 猪七   |  9000.00 |      1 |  1 | 教学部    |
|    7 | 蔡九   |  7000.00 |      2 |  2 | 销售部    |
+------+--------+----------+--------+----+-----------+
6 rows in set (0.00 sec)

六、约束

完整性约束

完整性约束是对字段进行限制，从而符合该字段达到我们期望的效果比如字段含有默认值，不能是NULL等 。直观点说：如果插入的数据不满足限制要求，数据库管理系统就拒绝执行操作

唯一约束

唯一约束可以有多个但索引列的值必须唯一，索引列的值允许有空值。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE。

#创建表时加入约束

CREATE TABLE t5(
  id   INT AUTO_INCREMENT,
  name VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY UK_t5_name (name)
);
-- 建表后添加约束:

alter table t5 add constraint UK_t5_name unique (name);

-- 如果不需要唯一约束，则可以这样删除

ALTER TABLE t5 DROP INDEX UK_t5_name;

自增约束

MySQL 每张表只能有1个自动增长字段，这个自动增长字段通常作为主键，也可以用作非主键使用，但是请注意将自动增长字段当做非主键使用时必须必须为其添加唯一索引，否则系统将会报错。

mysql> CREATE TABLE t4(
    ->   id INT NOT NULL,
    ->   name VARCHAR(20),
    ->   age INT AUTO_INCREMENT
    -> );

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

应该改为：

mysql> CREATE TABLE t4(
    ->   id INT NOT NULL,
    ->   name VARCHAR(20),
    ->   age INT UNIQUE AUTO_INCREMENT
    -> );
Query OK, 0 rows affected (0.13 sec)

主键约束

主键是用于唯一标识一条记录的约束，如同身份证。　　主键有两个约束：非空且唯一！

例；

-- 方式1

CREATE TABLE t1(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

-- 方式2

CREATE TABLE t2(
  id INT NOT NULL,
  name VARCHAR(20)
);

1、一张表中最多只能有一个主键

2、表中如果没有设置主键，默认设置NOT NULL的字段为主键；此外，表中如果有多个NOT NULL的字段，则按顺序将第一个设置NOT NULL的字段设为主键。

3、主键类型不一定必须是整型　　

结论：主键一定是非空且唯一，但非空且唯一的字段不一定是主键。

添加和删除主键

-- 添加主键 alter table tab_name add primary key(字段名称,...)
-- 删除主键 alter table users drop primary key;

注意，如果主键是AUTO_INCREMENT，需要先取消AUTO_INCREMENT，因为AUTO_INCREMENT只能加在KEY上。

CREATE TABLE test(num INT PRIMARY KEY AUTO_INCREMENT);<br>
 -- 思考，如何删除主键？
    ALTER TABLE test modify id int;   -- auto_increment没了，但这样写主键依然存在，所以还要加上下面这句
    ALTER TABLE test drop primary key;-- 仅仅用这句也无法直接删除主键

复合主键

所谓的复合主键 就是指你表的主键含有一个以上的字段。

如果一列不能唯一区分一个表里的记录时，可以考虑多个列组合起来达到区分表记录的唯一性，形式

创建时添加联合主键
create table sc (
    studentid int,
    courseid int,
    score int,
primary key (studentno,courseid)
);      

#修改的方式添加联合主键
alter table tb_name add primary key (字段1,字段2,字段3);

外键约束

外键的定义语法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
                    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法 可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。

例：

-- 子表
CREATE TABLE emp(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  dep_id INT
  CONSTRAINT emp_fk_emp FOREIGN KEY (dep_id) REFERENCES dep(id) -- 注意外键字段的数据类型必须与关联字段一致
);

-- 主表
CREATE TABLE dep(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

INSERT emp (name,dep_id) VALUES ("alex",1),
                                ("egon",2),
                                ("alvin",2),
                                ("莎莎",1),
                                ("wusir",2),
                                ("女神",2),
                                ("冰冰",3),
                                ("姗姗",3);

INSERT dep (name) VALUES    ("市场部"),
                            ("教学部"),
                            ("销售部");