数据库基本操作
第一节:数据库基本操作
(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)