数据库基本操作

第一节：数据库基本操作

（1）查看数据库、查看数据库版本：

#查看数据库：
MariaDB [(none)]> show databases;
#查看数据库版本：
MariaDB [(none)]> select version();

（2）查看当前用户、查看所有用户：

#查看当前用户：
MariaDB [(none)]> select user();
#查看当前所有用户：
MariaDB [(none)]> select user,host from mysql.user;

（3）创建数据库：

MariaDB [(none)]> create database test1;

（4）使用数据库：

MariaDB [(none)]> use test1;

（5）查看数据库信息，查看当前连接的数据库：

MariaDB [mysql]> select database();

（6）删除数据库：

MariaDB [(none)]> drop database test1;

第二节：数据库表的操作

查看当前数据库下拥有的所有表：

MariaDB [mysql]> show tables;

查看数据库表属性定义：

MariaDB [mysql]> desc user;

2.1 创建表

创建学校数据库——班级表

班级表：class

id	name	sex	age
1	tom	male	33
2	jack	male	21
3	alice	female	19

语法：
create table 表名(
　　字段名1 类型[(宽度) 约束条件],
　　字段名2 类型[(宽度) 约束条件],
　　字段名3 类型[(宽度) 约束条件]
　　)[存储引擎 字符集];
　　==在同一张表中，字段名是不能相同
　　==宽度和约束条件可选
　　==字段名和类型是必须的

mysql> create database school;　　# 创建school数据库
Query OK, 1 row affected (0.01 sec)

mysql> use school;　　# 使用school数据库
Database changed
mysql> create table class(
    -> id int,
    -> name varchar(50),
    -> sex enum('m','f'),
    -> age int
    -> );　　# 创建class表，包含字段id,name,sex,age，分别指定类型、宽度、约束条件
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;    # 查看school数据库中的现有表
+------------------+
| Tables_in_school |
+------------------+
| class            |
+------------------+
1 row in set (0.00 sec)

mysql> select * from class;    # 查看school数据库中class表的所有字段的值
Empty set (0.00 sec)　　 # 还未向表中插入内容，暂无

2.2 向表中插入内容

语法：

insert into 表名(字段1,字段2...) values(字段值列表...);

查看表结构，语法：

desc [table_name];

mysql> desc class;　　# 查看表结构
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into class values
    -> (1,'tom','m',33),
    -> (2,'jack','m',21),
    -> (3,'alice','f',19);　　# 顺序插入
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from class;　　# 查看school数据库中class表的所有字段的值
+------+-------+------+------+
| id   | name  | sex  | age  |
+------+-------+------+------+
|    1 | tom   | m    |   33 |
|    2 | jack  | m    |   21 |
|    3 | alice | f    |   19 |
+------+-------+------+------+
3 rows in set (0.00 sec)

mysql> insert into class(name,age) values
    -> ('zhang',22),
    -> ('wang',45);　　# 只想表中指定字段插入值
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class;　　# 查看school数据库中class表的所有字段的值
+------+-------+------+------+
| id   | name  | sex  | age  |
+------+-------+------+------+
|    1 | tom   | m    |   33 |
|    2 | jack  | m    |   21 |
|    3 | alice | f    |   19 |
| NULL | zhang | NULL |   22 |
| NULL | wang  | NULL |   45 |
+------+-------+------+------+
5 rows in set (0.00 sec)

2.3 DESCRIBE查看表结构

DESCRIBE [table_name];
DESC [table_name];

SHOW CREATE TABLE查看表详细结构
SHOW CREATE TABLE [table_name];

2.4 复制表结构

复制一个表结构的实现方法有两种：

　　方法一：在 create table 语句的末尾添加 like子句，可以将源表的表结构复制到新表中，语法格式如下：

create table 新表名 like 源表;

#例：
MariaDB [school]> desc class;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [school]> create table class1 like class;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> desc class1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

　　方法二：在 create table 语句的末尾添加一个 select 语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中：

create table 新表名 select * from 源表;

2.5 数据库中的表

（1）用户表：被用户创建和维护的一些表，包括了用户自己的信息

（2）数据字典表(视图)：被数据库系统创建和维护的一些表，包括了数据库的信息

　　数据库字典，由 information_schema 数据库负责维护：

　　　　tables－存放数据库里所有的数据表、以及每个表所在数据库

　　　　schemata－存放数据库里所有的数据库信息

　　　　views－存放数据库里所有的视图信息

　　　　columns－存放数据库里所有的列信息

　　　　triggers－存放数据库里所有的触发器

　　　　routines－存放数据库里所有存储过程和函数

　　　　key_column_usage－存放数据库所有的主外键

　　　　table_constraints－存放数据库全部约束

　　　　statistics－存放了数据表的索引

2.6 表的完整性约束

　　作用：用于保证数据的完整性和一致性

　　说明：

　　1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值

name varchar(50) not null

 　　2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

sex enum('male','female') not null default 'male'    # enum枚举类型
age int unsigned NOT NULL default 20       # 必须为正值（无符号）不允许为空 默认是20

 　　3. 是否是key

　　　　主键 primary key

　　　　外键 forengn key

　　　　索引 (index,unique...)

age int unsigned default 'm' not null    # unsigned无符号数，整数
hobby set('music','disc','dance','book') default 'book,dance'    # set集合类型可以有零个或多个值 
name varcher(30) unique    # 设置唯一约束unique

　　设置主键约束 PRIMARY KEY

　　primary key字段的值是不允许重复，且不允许不NULL（UNIQUE + NOT NULL）

　　单列做主键

　　多列做主键（复合主键）

　　单列做主键：

　　方法一：

id int primary key not null auto_increment    # auto_increment标识该字段的值自动增长（整数类型，而且为主键）

 　　方法二：

id int auto_increment not null
......
primary key(id)

　　复合主键：
　　service表：

host_ip	存储主机IP
service_name	服务名
port	服务对应的端口
allow(allow,deny)	服务是否允许访问

　　主键： host_ip + port = primary key

mysql> create table service(
    -> host_ip varchar(15) not null,
    -> service_name varchar(10) not null,
    -> port varchar(5) not null,
    -> allow enum('Y','N') default 'N',
    -> primary key(host_ip,port)    # 设置复合主键：host_ip + port
    -> );

2.7 修改表

（1）修改表名

ALTER TABLE 表名
RENAME 新表名;

MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class            |
| class1           |
+------------------+
2 rows in set (0.00 sec)

MariaDB [school]> alter table class1
    -> rename class_bak;
Query OK, 0 rows affected (0.01 sec)

MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| class            |
| class_bak        |
+------------------+
2 rows in set (0.00 sec)

（2）增加字段

　　ALTER TABLE 表名
　　ADD 字段名 数据类型 [完整性约束条件…],
　　ADD 字段名 数据类型 [完整性约束条件…];

　　ALTER TABLE 表名
　　ADD 字段名 数据类型 [完整性约束条件…] FIRST;

　　ALTER TABLE 表名
　　ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;

mysql> alter table student
add name varchar(20) not null,
add age int not null default 22;

（3）删除字段

　　ALTER TABLE 表名
　　DROP 字段名;

mysql> alter table student
drop sex;

（4）修改字段

　　ALTER TABLE 表名
　　MODIFY 字段名 数据类型 [完整性约束条件…];

　　ALTER TABLE 表名
　　CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

mysql> update employees set name='tomaaa' where name='tom';
mysql> delete from employees where name='alice';

　　ALTER TABLE 表名
　　CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

2.8 复制表

（1）复制表结构＋记录 （key不会复制: 主键、外键和索引）

mysql> create table new_student select * from student;

（2）只复制表结构

mysql> create table new1_student select * from student where 1=2;   #条件为假，查不到任何记录

（3）复制表结构，包括Key

mysql> create table new2_student like student;

2.9 删除表

　　在MySQL中有两种方法可以删除数据，一种是DELETE语句，另一种是TRUNCATE TABLE语句。

　　DELETE语句可以通过WHERE对要删除的记录进行选择。而使用TRUNCATE TABLE将删除表中的所有记录。

DELETE FROM table1;
TRUNCATE TABLE table1;

　　其中第二条记录中的TABLE是可选的。

　　如果要删除表中的部分记录，只能使用DELETE语句。

DELETE FROM table1 WHERE ...;

2.10 表的查询

　　简单查询

mysql> select * from class;
mysql> select name,id from class;

 　　避免重复DISTINCT

mysql> select distinct sex from class;    # 即去重

 　　通过四则运算查询

mysql> select age*2 from class;    # age*2
mysql> select age*2 as new_age from class;    # 将age*2作为new_age的值

 　　CONCAT() 函数用于连接字符串

mysql> select concat(id,' new_age=',age*2) from class;    # ' new_age='作为连接字符串

 　　单条件查询

mysql> select name,age from class where age='21';

 　　关键字BETWEEN AND

mysql> select name,age from class where age between 33 and 45;

 　　关键字IS NULL

mysql> select id,name from class where id is null;

 　　关键字IN集合查询

mysql> select name,age from class where id=1 or id=2;
mysql> select name,age from class where age in(33,19);
mysql> select name,age from class where age not in(33,19);

 　　关键字LIKE模糊查询，通配符’%’或‘_’

mysql> select name,sex from class where name like 'zh%';    # 查询出了name=zhang的信息

 　　查询排序，按单列排序

mysql> select name,age from class order by age asc;
mysql> select name,age from class order by age desc;
mysql> select name,age from class order by id desc,age asc;

 　　限制查询的记录数LIMIT N

mysql> select id,name from class order by id desc limit 2;    # 支持n,m：查询n到m的

 　　使用集合查询

mysql> select max(id) from class;    # min、avg、sum

　　分组查询GROUP BY关键字和GROUP_CONCAT()函数一起使用

mysql> select id,group_concat(name) from class group by id;

　　使用正则表达式查询REGEXP ''

mysql> select * from class where name regexp '^z';

　　多表的连接查询（先建立如下两个测试表）

mysql> create table employee(
    -> em_id int auto_increment primary key not null,
    -> em_name varchar(50),
    -> age int,
    -> dept_id int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into employee(em_name,age,dept_id) values
    -> ('tianyun',19,200),
    -> ('tom',26,201),
    -> ('jack',30,201),
    -> ('alice',24,202),
    -> ('robin',40,200),
    -> ('natasha',28,204);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create table department(
    -> dept_id int,
    -> dept_name varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into department values
    -> (200,'hr'),
    -> (201,'it'),
    -> (202,'sale'),
    -> (203,'fd');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from employee;
+-------+---------+------+---------+
| em_id | em_name | age  | dept_id |
+-------+---------+------+---------+
|     1 | tianyun |   19 |     200 |
|     2 | tom     |   26 |     201 |
|     3 | jack    |   30 |     201 |
|     4 | alice   |   24 |     202 |
|     5 | robin   |   40 |     200 |
|     6 | natasha |   28 |     204 |
+-------+---------+------+---------+
6 rows in set (0.00 sec)

mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | hr        |
|     201 | it        |
|     202 | sale      |
|     203 | fd        |
+---------+-----------+
4 rows in set (0.00 sec)

　　交叉连接： 生成笛卡尔积，它不使用任何匹配条件

mysql> select employee.em_name,employee.age,employee.dept_id,department.dept_name from employee,department;

　　内连接： 只连接匹配的行

mysql> select employee.em_id,employee.em_name,employee.age,department.dept_name from employee,department where employee.dept_id = department.dept_id;

　　外连接语法：
　　　　SELECT 字段列表
　　　　FROM 表1 LEFT|RIGHT JOIN 表2
　　　　ON 表1.字段 = 表2.字段;

　　外连接之左连接： 会显示左边表内所有的值，不论在右边表内匹不匹配

　　找出所有员工及所属的部门，包括没有部门的员工

mysql> select em_id,em_name,dept_name from employee left join department on employee.dept_id = department.dept_id;
+-------+---------+-----------+
| em_id | em_name | dept_name |
+-------+---------+-----------+
|     1 | tianyun | hr        |
|     2 | tom     | it        |
|     3 | jack    | it        |
|     4 | alice   | sale      |
|     5 | robin   | hr        |
|     6 | natasha | NULL      |
+-------+---------+-----------+
6 rows in set (0.00 sec)

　　外连接之右连接： 会显示右边表内所有的值，不论在左边表内匹不匹配

　　找出所有部门包含的员工，包括空部门

mysql> select em_id,em_name,dept_name from employee right join department on employee.dept_id = department.dept_id;
+-------+---------+-----------+
| em_id | em_name | dept_name |
+-------+---------+-----------+
|     1 | tianyun | hr        |
|     5 | robin   | hr        |
|     2 | tom     | it        |
|     3 | jack    | it        |
|     4 | alice   | sale      |
|  NULL | NULL    | fd        |
+-------+---------+-----------+
6 rows in set (0.00 sec)

　　全外连接： 包含左、右两个表的全部行

mysql> select * from employee full join department;
+-------+---------+------+---------+---------+-----------+
| em_id | em_name | age  | dept_id | dept_id | dept_name |
+-------+---------+------+---------+---------+-----------+
|     1 | tianyun |   19 |     200 |     200 | hr        |
|     1 | tianyun |   19 |     200 |     201 | it        |
|     1 | tianyun |   19 |     200 |     202 | sale      |
|     1 | tianyun |   19 |     200 |     203 | fd        |
|     2 | tom     |   26 |     201 |     200 | hr        |
|     2 | tom     |   26 |     201 |     201 | it        |
|     2 | tom     |   26 |     201 |     202 | sale      |
|     2 | tom     |   26 |     201 |     203 | fd        |
|     3 | jack    |   30 |     201 |     200 | hr        |
|     3 | jack    |   30 |     201 |     201 | it        |
|     3 | jack    |   30 |     201 |     202 | sale      |
|     3 | jack    |   30 |     201 |     203 | fd        |
|     4 | alice   |   24 |     202 |     200 | hr        |
|     4 | alice   |   24 |     202 |     201 | it        |
|     4 | alice   |   24 |     202 |     202 | sale      |
|     4 | alice   |   24 |     202 |     203 | fd        |
|     5 | robin   |   40 |     200 |     200 | hr        |
|     5 | robin   |   40 |     200 |     201 | it        |
|     5 | robin   |   40 |     200 |     202 | sale      |
|     5 | robin   |   40 |     200 |     203 | fd        |
|     6 | natasha |   28 |     204 |     200 | hr        |
|     6 | natasha |   28 |     204 |     201 | it        |
|     6 | natasha |   28 |     204 |     202 | sale      |
|     6 | natasha |   28 |     204 |     203 | fd        |
+-------+---------+------+---------+---------+-----------+
24 rows in set (0.00 sec)

　　复合条件连接查询
　　示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25

mysql> select em_id, em_name, age, dept_name
    -> from employee,department
    -> where employee.dept_id = department.dept_id
    -> and age>25;
+-------+---------+------+-----------+
| em_id | em_name | age  | dept_name |
+-------+---------+------+-----------+
|     2 | tom     |   26 | it        |
|     3 | jack    |   30 | it        |
|     5 | robin   |   40 | hr        |
+-------+---------+------+-----------+
3 rows in set (0.00 sec)

 　　示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示

mysql> select em_id, em_name, age, dept_name
    -> from employee,department
    -> where employee.dept_id = department.dept_id
    -> order by age asc;
+-------+---------+------+-----------+
| em_id | em_name | age  | dept_name |
+-------+---------+------+-----------+
|     1 | tianyun |   19 | hr        |
|     4 | alice   |   24 | sale      |
|     2 | tom     |   26 | it        |
|     3 | jack    |   30 | it        |
|     5 | robin   |   40 | hr        |
+-------+---------+------+-----------+
5 rows in set (0.00 sec)

 　　子查询
　　　　子查询是将一个查询语句嵌套在另一个查询语句中；
　　　　内层查询语句的查询结果，可以为外层查询语句提供查询条件；
　　　　子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字；
　　　　还可以包含比较运算符：= 、 !=、> 、<等

mysql> select dept_id,dept_name from department
    -> where dept_id IN
    -> (select DISTINCT dept_id from employee where age>=25);    //查询员工年龄大于等于25岁的部门
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | hr        |
|     201 | it        |
+---------+-----------+
2 rows in set (0.01 sec)