1.数据库入门-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_36659409/article/details/79626312

本文详细介绍MySQL数据库的基本操作，包括连接数据库、创建与删除数据库、执行SQL命令等，并讲解了数据完整性约束、SQL语法组成等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#1. mysql数据库基本操作

##1.1 mysql、mysqladmin和mysqldump

###1.1.1. mysql命令

mysql指令连接数据库

连接数据库

mysql -h host_name -uuser_name –ppassword

h：当连接MySQL服务器不在同台主机时，填写主机名或IP地址

u：登录MySQL的用户名

p：登录MySQL的密码

注意:密码如果写在命令行的时候一定不能有空格。如果使用的系统为linux并且登陆用户名字与MySQL的用户名相同即可不用输入用户名密码，linux默认是以root登陆，windows默认用户是ODBC

示例mysql连接本地数据库

>mysql -uroot -proot

连老师的mysql

>mysql -h 192.168.4.145 -uroot -proot

mysql执行文件

>mysql -uroot -proot mysql < show.sql

###1.1.2. mysqladmin命令

完成许多系统管理任务，如创建或删除一个数据库、修改密码等

创建数据库

>mysqladmin -uroot -proot create newdb

删除数据库

>mysqladmin -uroot -proot drop newdb

修改密码

>mysqladmin -u root -proot password "root"

##mysqldump命令

完成数据库备份

>mysqldump -uroot -proot mysql > mysql.sql

##1.2 mysql小技巧

###1.2.1 如果不能远程访问：

mysql -uroot -proot

mysql>use mysql;

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

mysql>flush privileges;

mysql>exit;

###1.2.2 解决乱码：

安装mysql之后默认的字符编码为latin1，当出现乱码我们需要修改字符字符集的时候，可以执行如下操作。

登录mysql之后可以通过如下指令查看，显示如下

show variables like '%char%';

+---------------------－+-----－－--------------

| Variable_name | Value

+-----------------------+-－－------------------

| character_set_client | latin1

| character_set_connection | latin1

| character_set_database | latin1

| character_set_filesystem | binary

| character_set_results | latin1

| character_set_server | latin1

| character_set_system | utf8

| character_sets_dir | /usr/share/mysql/charsets/

+---------------------+－－－-------------------

可以通过命令修正，也可以通过配置文件修正

使用mysql的命令

mysql> SET character_set_client = utf8 ;

mysql> SET character_set_connection = utf8 ;

mysql> SET character_set_database = utf8 ;

mysql> SET character_set_results = utf8 ;

mysql> SET character_set_server = utf8 ;

mysql> SET collation_connection = utf8 ;

mysql> SET collation_database = utf8 ;

mysql> SET collation_server = utf8 ;

##1.3 数据库基础知识

###1.3.1 数据完整性之约束

五种完整性约束：

* NOT NULL ：非空约束，指定某列不能为空；

* UNIQUE : 唯一约束，指定某列或者几列组合不能重复

* PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录

* FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

* CHECK ：检查，指定一个布尔表达式，用于指定对应的值必须满足该表达式（mysql不支持check约束）

### 1.3.2. sql概述

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

mysql数据类型

* 数值列类型

* int

* bigint

* float

* double

* decimal

* 字符串列类型

* char

* varchar

* blob（存二进制数据）

* text

* 日期和时间列类型

* date 年月日

* time

* datetime 年月日时分秒

* timestamp 时间戳

* year

### 1.3.3. sql语法组成

DML（ Data Manipulation Language数据操作语言）

——查询、插入、删除和修改数据库中的数据；

——SELECT、INSERT、 UPDATE 、DELETE等；

DCL（ Data Control Language数据控制语言）

——用来控制存取许可、存取权限等；

——GRANT、REVOKE 等；

DDL（ Data Definition Language数据定义语言）

——用来建立数据库、数据库对象和定义其列

——CREATE TABLE 、DROP TABLE、ALTER TABLE 等

功能函数（内置函数）

——日期函数、数学函数、字符函数、系统函数等

##1.4. DDL

###1.4.1. 创建表

create table 表名（

列名1 列类型 [<列的完整性约束>],

列名2 列类型 [<列的完整性约束>],

.. ... );

例如：创建学生表

create table student(

name varchar(32),

age int(11)

);

###1.4.2. 查看表结构

mysql> desc student;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

###1.4.3. 删除表

mysql>drop table student;

###1.3.4. 创建表（附带约束条件）

create table student(

id int(11) not null auto_increment primary key,

name varchar(32) not null default '',

age int(11) not null default 0

);

选择主键的原则：

最少性:

尽量选择单个键作为主键

稳定性:

尽量选择数值更新少的列作为主键

逻辑主键：

尽量选择无实际业务意义的字段作为主键

###1.4.5 创建成绩表（附带外键）

create table score(

id int not null auto_increment primary key,

score int,

stuent_id int,

foreign key(stuent_id) references student(id)

);

注意：

一旦有有外键指向某个表的主键，那么删除表或数据是，就要先删除主键所在的表或数据

###1.4.6 创建表（exists）

格式：CREATE TABLE IF NOT EXISTS 表名;

create table if not exists student(

id int(11) not null auto_increment primary key,

name varchar(32) not null default '',

age int(11) not null default 0

);

###1.4.7 修改表

#给学生表增加地址列

alter table student add address varchar(256);

#给学生表地址修改类型

alter table student modify address varchar(512);

#删除学生表地址列

alter table student drop address;

##1.5 运算符

###1.5.1 算术运算符

select (1+1)*(2+2)

###1.5.2 比较运算符（重点）

![](compare.png)

请掌握常用的比较符号和in like between

###1.5.3 通配符

% 代表任意长度（长度可以为0）的字符串

select 'cdefg' like '%c%'

##1.6 DML(数据库操作语言)

insert delete update select

###1.6.1 insert

#数据库的存储每列数据字段，我们叫column

#插入数据的格式

INSERT [INTO] <表名> [列名] VALUES <值列表>

#插入前先建表

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(32) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

)

#完整插入数据

insert into user(id, name,age) values (1,'张三',20);

#不添加姓名

insert into user(id, age) values (1,20);

#注意，不写列明，那么就表示插入整张表，values就需要补充完整所有字段的数据

insert into user values (2,'zhangsan',20);

#主键冲突

insert into user values (2,'zhangsan',20);

[Err] 1062 - Duplicate entry '2' for key 'PRIMARY'

#id是自增长时，可以不指定id

insert into user(name,age) values ('zhangsan',20);

#default，就说明该值使用创建表时的默认值

insert into user(name,age) values (default,20);

#多行插入

insert into user(name,age) values (default,20);

#多行插入

#把user表的name和age列全查出来，然后插入到user1表中

#先写insert，再写select

insert into user1(name,age)

select name,age from user

###1.6.2. update

UPDATE <表名> SET <列名 = 更新值> [WHERE <更新条件>]

where 子句是判断语句，用来设定条件，限制只更新匹配的行,如果不带where子句，则更新所有行数据。

#把user表里面name是空字符串的修改为段誉

update user set name='段誉' where name=''

#把user表里面所有name字段后面追加花花公子

update user set name = concat(name,'花花公子')

#修改多个字段

update user set name= '慕容复',age=30

###1.6.3. delete

DELETE FROM <表名> [WHERE <删除条件>]

#删除user表里面id大于300的数据

delete from user where id>300

#全部删除

delete from user

###1.6.4. select

#查询所有学生信息1

select * from student

#查询所有学生信息2

select no,name,gender,age,major from student

#查询所有美术系的学生

select * from student where major = '美术系'

#查询所有美术系学生的姓名和性别

select name,gender from student where major = '美术系'

#查询所有美术系的男学生（使用了and）

select * from student where major = '美术系' and gender = '男'

#指定别名（as）

select name as '姓名',gender as '性别' from student

#去重（distinct）

#使用distentct关键字去重

select distinct major from student

#多个字段去重

select distinct name, major from student

#排序（order by）

#单字段排序

select * from student order by age

#降序（desc）

select * from student order by age desc

#多字段排序

#先以第一个排序，然后在按第二个拍，以此类推

select * from student order by no,age

#限制条数，也叫分页（limit）

#limit(offset，count)

#查询前5条

select * from student limit 5

#查询前5条

select * from student limit 0,5

#查询2，4条记录

select * from student limit 1,3

#模糊查询（like）

select * from student where name like '%欧阳%'

#in

select * from student where age in (15,16,17,18,19)

#between

select * from student where age BETWEEN 15 and 18

select * from student where birthday BETWEEN '2015-01-01' and '2017-01-01'

#分组统计（group by）

#根据学生的序号来统计总成绩

select stu_no, sum(score) from score group by stu_no

#根据专业统计各专业有多少学生

select major,count(*) from student group by major

#统计出总成绩大于110的数据

select stu_no, sum(score) from score group by stu_no HAVING sum(score)>110

#综合查询

select stu_no, sum(score) as total from score where course_id in (1,2,3,4,5) group by stu_no HAVING total>110 order by total

##1.7 多表联合查询

同时涉及多个表的查询称为连接查询

用来连接两个表的条件称为连接条件

Navicat MySQL Data Transfer

Source Server : 127.0.0.1

Source Server Version : 50545

Source Host : 127.0.0.1:3306

Source Database : user

Target Server Type : MYSQL

Target Server Version : 50545

File Encoding : 65001

Date: 2017-08-17 17:17:44

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for order

-- ----------------------------

DROP TABLE IF EXISTS `order`;

CREATE TABLE `order` (

`order_id` int(11) NOT NULL AUTO_INCREMENT,

`product_id` int(11) DEFAULT NULL,

`user_id` int(11) DEFAULT NULL,

`numbers` int(11) DEFAULT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of order

-- ----------------------------

INSERT INTO `order` VALUES ('1', '1', '1', '2');

INSERT INTO `order` VALUES ('2', '2', '1', '1');

INSERT INTO `order` VALUES ('3', '3', '2', '1');

INSERT INTO `order` VALUES ('4', '3', '3', '2');

INSERT INTO `order` VALUES ('5', '1', '3', '5');

INSERT INTO `order` VALUES ('6', '2', '1', '1');

-- ----------------------------

-- Table structure for product

-- ----------------------------

DROP TABLE IF EXISTS `product`;

CREATE TABLE `product` (

`product_id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

`price` decimal(7,2) DEFAULT NULL,

PRIMARY KEY (`product_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of product

-- ----------------------------

INSERT INTO `product` VALUES ('1', 'iPhone7', '6000.00');

INSERT INTO `product` VALUES ('2', '魅族7', '4000.00');

-- ----------------------------

-- Table structure for user

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(32) DEFAULT '',

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES ('1', '班长', '20');

INSERT INTO `user` VALUES ('2', '付班长', '21');

INSERT INTO `user` VALUES ('3', '李同学', '22');

###1.7.1. 使用内连接

内连接的特点：只能查处相等的数据

#其实我们用的是=来做的内链接

select u.`name`,p.`name`,o.numbers from user u, `order` o, product p where u.id = o.user_id and o.product_id = p.product_id

#真正的内链接(INNER JOIN)

select u.`name`,p.`name`,o.numbers from user u INNER JOIN `order` o on u.id= o.user_id INNER JOIN product p on o.product_id = p.product_id

#以上两种写法等同

###1.7.1. 使用左连接

左连接特点：以左边的表为主，右边没有则补null

#这里我们查出的是所有学生买的东西，商品不存在的就补null

select u.`name`,p.`name`,o.numbers from user u left join `order` o on u.id = o.user_id LEFT JOIN product p on o.product_id = p.product_id

###1.7.2. 使用右连接

左连接特点：以右边的表为主，左边没有则补null

#这里我们查出的是所有商品被那些人买了，买的人不存在就补null

select u.`name`,p.`name`,o.numbers from user u right join `order` o on u.id = o.user_id right JOIN product p on o.product_id = p.product_id

##1.8 子查询

将一个查询块嵌套在另一个查询块里面，就叫子查询

###1.8.1. 子查询在where语句里

#查询没有买过东西的人

select * from user u where u.id not in (select user_id from `order`)

###1.8.2. 子查询在from语句

#查询每个人消费的总金额

select a.username,sum(a.price) total from (

select u.`name` as username,p.`name` as productname,o.numbers,p.price from user u left join `order` o on u.id = o.user_id LEFT JOIN product p on o.product_id = p.product_id

) a GROUP BY a.username

###1.8.3. 子查询在select语句（了解）

#查询每个人消费的总金额

select u.name, (select total from (select o.user_id, sum(p.price*o.numbers) total from `order` o, product p where p.product_id = o.product_id GROUP BY o.user_id) a where a.user_id = u.id) as total

from user u

##1.9复制表

###1.8.1 仅仅复制表结构

create table user1 like user;

###1.8.2 复制表结构和数据

create table user2 select * from user;

###1.8.3 根据统计的结构，来创建表

create table report

select a.username,sum(a.price) total from (

select u.`name` as username,p.`name` as productname,o.numbers,p.price from user u left join `order` o on u.id = o.user_id LEFT JOIN product p on o.product_id = p.product_id

) a GROUP BY a.username

##1.9 exists

#存在

select * from user u where EXISTS (select * from `order` o where o.user_id = u.id)

#不存在

select * from user u where NOT EXISTS (select * from `order` o where o.user_id = u.id)

#通常面试的时候经常温exists和in的区别

#exists的性能更高，建议使用

##1.10 union

#union能把查询结果一样的数据联合在一起,但是会把重复的去掉

select * from user where age = 20

UNION

select * from user where age = 21

UNION

select * from user where age = 22

#union all 则会把会有的数据联合在一起，包括重复的数据

select * from user where age = 22

UNION all

select * from user where age = 23

UNION all

select * from user where age = 19

UNION all

select * from user where age = 19

UNION all

select * from user where age = 19

##1.12 试图（view）

#创建试图

create view user_product_view as

select u.`name` as user_name,p.`name` as product_name,o.numbers from user u, `order` o, product p where u.id = o.user_id and o.product_id = p.product_id

#什么时候使用试图

#在经常统计的sql上，我们一般会把这个sql创建成试图，例如企业的公司信息，部门信息等等，基本上都会做个试图

##1.13 函数（function）

###创建函数

CREATE FUNCTION `add`(`a` int,`b` int) RETURNS int(11)

BEGIN

RETURN a+b;

END

###使用函数

select add(id,age) from user

##1.14 存储过程（producer）

参数 IN 输入参数

OUT 输出参数

INOUT 输入输出参数（就是输入的单数，可以作为输出的的参数）

###1.14.1 创建简单的存储过程

#创建存储工程

DELIMITER ;;

CREATE PROCEDURE `addProcedure`(IN a int, IN b int, OUT c int)

BEGIN

#Routine body goes here...

set c = a + b;

END

;;

DELIMITER ;

#测试存储过程

call addProcedure(1,2,@out);

select @out as c

###1.14.2 创建根据表名来获得总记录数的存储过程

DELIMITER ;;

DROP PROCEDURE IF EXISTS `getTableCount` ;;

CREATE PROCEDURE `getTableCount`(IN tableName VARCHAR(128),OUT result INT)

BEGIN

SET @SQLStr=CONCAT('SELECT count(*) into @result FROM ',tableName);

PREPARE stmt from @SQLStr;

EXECUTE stmt;

set result = @result;

END ;;

DELIMITER ;

#测试

call getTableCount('user',@s);

select @s;

##统计学生成绩练习

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`id` int(11) NOT NULL,

`name` varchar(32) NOT NULL,

`description` varchar(32) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('1', '太极拳', '太极拳');

INSERT INTO `course` VALUES ('2', '九阳神功', '九阳神功');

-- ----------------------------

-- Table structure for score

-- ----------------------------

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (

`id` int(11) NOT NULL,

`student_id` int(11) NOT NULL,

`course_id` int(11) NOT NULL,

`score` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of score

-- ----------------------------

INSERT INTO `score` VALUES ('1', '1', '1', '100');

INSERT INTO `score` VALUES ('2', '1', '2', '75');

INSERT INTO `score` VALUES ('3', '2', '1', '80');

INSERT INTO `score` VALUES ('4', '2', '2', '70');

INSERT INTO `score` VALUES ('5', '3', '1', '95');

INSERT INTO `score` VALUES ('6', '3', '2', '100');

-- ----------------------------

-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`id` int(11) NOT NULL,

`name` varchar(32) NOT NULL,

`class` varchar(32) NOT NULL,

`birthday` date DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('1', '张三丰', '武当', '1907-08-19');

INSERT INTO `student` VALUES ('2', '张翠山', '武当', '1927-08-19');

INSERT INTO `student` VALUES ('3', '张无忌', '武当', '1947-08-19');

#查询成绩明细，需要关联三张表

#select stu.`name` as '学生姓名',c.`name` as '课程',s.score as '成绩' from student stu, course c, score s where stu.id = s.student_id and c.id = s.course_id

#查询出平均成绩大于80的

#1、第一步需要求出每个人的平均成绩（平均成绩就要用到平均函数avg,然后是根据姓名来求平均值的，那么就要用姓名来分组统计group by）

#2、需要使用having过滤平均成绩

select stu.`name` , avg(s.score) as avg_score from student stu, course c, score s where stu.id = s.student_id and c.id = s.course_id group by stu.`name` HAVING avg_score > 80