MySQL的基本操作

1、常用命令行

mysql -urroot -p123456 --连接数据库

-- 修改用户密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; 

-- 所有语句以分号结尾 ;
show databases; -- 查看所有的数据库

mysql> use shool; -- 切换数据库 use + 数据库名

show tables -- 查看数据库中所有的表

describe shool; -- 显示数据库中所有表的信息

create database westos; -- 创建新的数据库 create database + 新数据库名

exit; -- 退出连接

-- sql单行注释
/*
	sql多行注释
*/

2、操作数据库

2.1、数据库的基本操作

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;

2、删除数据库

DROP DATABASE [IF EXISTS] westos;

3、使用数据库

-- 如果表明或者字段是特殊字符，需要	` `
USE `shool`;

4、查看数据库

SHOW DATABASES; -- 查看所有数据库

2.2、数据库的列类型

数值

字符串类型

日期和时间型数值类型

NULL值

理解为 "没有值" 或 "未知值"
不要用NULL进行算术运算 , 结果仍为NULL

2.3、数据库的字段属性（重点）

Unsigned：

无符号整数
声明该数据列不允许负数

ZEROFILL

0填充的
不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
通常用于设置主键 , 且为整数类型
可定义起始值和步长
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
- SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

默认为NULL , 即没有插入该列的数值
如果设置为NOT NULL , 则该列必须有值

DEFAULT

用于设置默认值
例如,性别字段,默认为"男" , 否则为 "女" ; 若无指定该列的值 , 则默认值为"男"的值

2.4、创建数据库（重点）

例子

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '未命名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`Address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`Email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTS] `表名` (
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ......
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]

查看表详细信息

DESC 表名;

重命名表

RENAME TABLE 旧表名 TO 新表名;

删除表（危）

DROP TABLE [IF EXISTS] 表名;
DROP TABLE [IF EXISTS] 表名1, 表名2, ....;

3、操作数据表

3.1、修改表结构（ALTER TABLE）

增加字段

ALTER TABLE 表名 ADD 新字段名 新数据类型 [FIRST | AFTER 旧字段名];
-- FIRST : 将新字段添加到表的最左边
-- AFTRE 旧字段名 : 将新字段添加到表中原有的某个字段之后

删除字段

ATTER TABLE 表名 DROP 字段名;

修改字段数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型;

修改字段名

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;

3.2、外键（了解）

创建表时添加约束

CREATE TABLE IF NOT EXISTS `grade` (
	`grade_id` INT(10) AUTO_INCREMENT COMMENT '年级id',
	`grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的 grade_id 字段要去引用 年级表中的 grade_id 字段
-- 定义外键key
-- 给这个外键添加约束(constraint)（执行引用）references 引用
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '未命名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`Address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`Email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`grade_id` INT(10) NOT NULL COMMENT '学生的年级',
	PRIMARY KEY(`id`),
	KEY `FK_grade_id` (`grade_id`),
	CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

注意:删除具有主外键关系的表时 , 要先删子表 , 后删主表,否则无法删除

修改表结构添加约束

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '未命名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`Address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`Email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`grade_id` INT(10) NOT NULL COMMENT '学生的年级id',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE IF NOT EXISTS `grade`(
	`grade_id` INT(10) AUTO_INCREMENT COMMENT '年级id',
	`grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建子表完毕后,修改子表添加外键
ALTER TABLE student 
ADD CONSTRAINT `FK_grade_id` FOREIGN KEY(`grade_id`) REFERENCES `grade`(`grade_id`);

-- ALTER TABLE 表名 ADD CONSTRAINT `约束名` FOREIGN KEY(`作为外键的列`) REFERENCES 哪个表(哪个字段)

3.3、数据更新（DML 熟练掌握！）

插入数据（INSERT）

INSERT INTO 表名 [(字段1, 字段2, 字段3, ......)] VALUES ('值1', '值2', '值3', ......);
/*
	字段或值之间用英文逗号隔开
	' 字段1,字段2...' 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致
	可同时插入多条数据 , values 后用英文逗号隔开 
*/

WHERE 条件子句

运算符	含义	范围	结果
=	等于	5=6	false
<> 或 !=	不等于	5!=6	true
>	大于	5>6	false
<	小于	5<6	true
>=	大于等于	5>=6	false
<=	小于等于	5<=6	true
BETWEEN	在某个范围内	BETWEEN 5 AND 10
AND	并且	5 > 1 AND 1 > 2	false
OR	或	5 > 1 OR 1 > 2	true

更新数据（UPDATE)

UPDATE 表名 SET 字段名1 = 值1 [, 字段名2 = 值2, 字段名3 = 值3, ......] [WHERE 条件表达式];

删除数据（DELETE）

DELETE FROM 表名 [where条件表达式]

TRUNCATE 命令

-- 完全清空一个数据库表，结构索引约束不会变
TRUNCATE TABLE `student`;

DELETE 与 TRUNCATE的区别

相同点：都能删除数据，不会改变表的结构
不同点：
- truncate 重新设置自增列，计数器会归零
- truncate 不会影响事务

-- 测试DELETE 与 TRUNCATE的区别

CREATE TABLE IF NOT EXISTS `test` (
	`id` INT(4) AUTO_INCREMENT NOT NULL,
	`coll` VARCHAR(10) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

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

DELETE FROM test; -- 不改变自动增量

TRUNCATE TABLE `test`; -- 将自动增量清零重置

4、使用DQL查询数据

SELECT语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

4.1、查询指定字段

-- 创建产品表
CREATE TABLE IF NOT EXISTS `Prduct`(
	`prduct_id` CHAR(4) NOT NULL COMMENT '产品编号',
	`prduct_name` VARCHAR(100) NOT NULL COMMENT '名称',
	`prduct_type` VARCHAR(32) NOT NULL COMMENT '产品类型',
	`sale_price` INTEGER COMMENT '销售单价',
	`purchase_price` INTEGER COMMENT '进货单价',
	`regist_date` DATE  COMMENT '登记日期',
 	PRIMARY KEY(`prduct_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;


-- 插入数据
INSERT INTO `prduct` VALUES 
('0001','T恤','衣服','1000','500','2009-09-20'),
('0002','打孔器','办公用品','500','320','2009-09-11'),
('0003','运动T恤','衣服','4000','2800',NULL),
('0004','菜刀','厨房用具','3000','2800','2009-09-20'),
('0005','高压锅','厨房用具','6800','5000','2009-01-15'),
('0006','叉子','厨房用具','500',NULL,'2009-09-20'),
('0007','擦菜板','厨房用具','880','790','2008-04-28'),
('0008','圆珠笔','办公用品','100',NULL,'2009-11-11');

-- 查询表中全部数据
SELECT * FROM `prduct`;
-- 查询表中指定列数据
SELECT `prduct_name`, `sale_price` FROM `prduct`;

AS作为别名

作用：

可给数据列取一个新别名
可给表取一个新别名
可把经计算或总结的结果用另一个新名称来代替

-- AS取别名(一般情况下，可用空格代替)
SELECT `prduct_name` AS 产品, `sale_price` AS 售价 FROM `prduct`;
SELECT `prduct_name` 名称, `sale_price` 价格 FROM `prduct` p;
-- CONCAT()函数拼接字符串
SELECT CONCAT('产品编号：', `prduct_id`, '商品名称：',`prduct_name`) AS 产品信息 FROM `prduct`;

DISTINCT关键字

-- distinct关键字作用 : 去掉结果中重复的记录 , 只返回一条
SELECT DISTINCT `prduct_type` FROM `prduct`;

使用表达式的列

数据库中的表达式 : 一般由文本值 , 列值 , NULL , 函数和操作符等组成

应用场景 :

SELECT语句返回结果列中使用
SELECT语句中的ORDER BY , HAVING等子句中使用
DML语句中的 where 条件语句中使用表达式

-- 查看版本号
SELECT VERSION();
-- 查看自增步长
SELECT @@Auto_increment_increment;
-- 表达式
SELECT 10*10-1 AS 计算结果;

-- 出售单价统一增加100后查看
SELECT `sale_price`+100 AS 全部升值后： FROM `prduct`;
-- 指定个别变化
SELECT `sale_price`+100 AS 商品一升值后： FROM `prduct` WHERE `prduct_id`=0001;

4.2、where条件语句

-- where条件语句
-- 查询指定价格区间内
SELECT `prduct_name`,`sale_price` 
FROM `prduct` 
WHERE `sale_price` > 600 && `sale_price` < 3200;

SELECT `prduct_name`,`sale_price` 
FROM `prduct` 
WHERE `sale_price` > 600 AND `sale_price` < 4200;

SELECT `prduct_name`AS 名称,`sale_price` AS 价格
FROM `prduct` 
WHERE `sale_price`  BETWEEN 600 AND 9200;

-- 查询除过厨房用具的产品和价格及类型
SELECT `prduct_name`, `sale_price`, `prduct_type`
FROM `prduct`
WHERE `prduct_type` != '厨房用具';

SELECT `prduct_name`, `sale_price`, `prduct_type`
FROM `prduct`
WHERE NOT `prduct_type` = '厨房用具';

模糊查询：比较运算符

-- 模糊查询 between and \ like \ in \ null
-- 查询名字中含有‘菜’的产品及编号、价钱
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
SELECT `prduct_name` AS 产品, `prduct_id` AS 编号, `sale_price` AS 价钱
FROM `prduct`
WHERE `prduct_name` LIKE '%菜%'; 

-- 查询’菜‘前面只含有一个字的
SELECT `prduct_name` AS 产品, `prduct_id` AS 编号, `sale_price` AS 价钱
FROM `prduct`
WHERE `prduct_name` LIKE '_菜%'; 
-- T恤前面两个字的
SELECT `prduct_name` AS 产品, `prduct_id` AS 编号, `sale_price` AS 价钱
FROM `prduct``student``sex`
SELECT `prduct_id`,`prduct_name`,`sale_price`
FROM `prduct`
WHERE `prduct_id` IN('0001','0003','0007');

-- 查询价格为 500、1000的所有产品
SELECT `prduct_id`,`prduct_name`,`sale_price`
FROM `prduct`
WHERE `sale_price` IN (500,1000);

-- NOLL
-- 查询批发价为空的产品（不为空则用is not null 或 where后加 NOT）
SELECT `prduct_id`,`prduct_name`,`sale_price`,`purchase_price`
FROM `prduct`
WHERE `purchase_price` IS NULL;

4.3、连接查询（掌握）

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT s.`studentno`, `studentname`, `subjectno`, `studentresult`
FROM `student` s
INNER JOIN `result` r -- 表中存在至少一个匹配时，INNER JOIN 关键字返回行。
ON s.`studentno` = r.`studentno`;

-- 不考试的李四也会出来！
SELECT s.`studentno`, `studentname`, `subjectno`, `studentresult`
FROM `student` s
LEFT JOIN `result` r -- LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
ON r.`studentno` = s.`studentno`;

SELECT s.`studentno`, `studentname`, `subjectno`, `studentresult`
FROM `student` s
RIGHT JOIN `result` r -- RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
ON r.`studentno` = s.`studentno`;

-- 等值连接
SELECT s.`studentno`, `studentname`, `subjectno`, `studentresult`
FROM `student` s , `result` r
WHERE s.`studentno` = r.`studentno`;

-- 查看缺考的(左连接应用场景)
SELECT s.`studentno`, `studentname`, `subjectno`, `studentresult`
FROM `student` s 
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NULL;

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.`studentno`, `studentname`, `subjectname` AS 科目, `studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
LEFT JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`

-- 查询课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
WHERE `studentresult` > 80
ORDER BY `studentresult` DESC -- 排序：ASC 降序  DESC 升序
LIMIT 0,10; -- 分页 limit 起始数据位置（n-1）* 每一页的数据总数

自连接

/*
自连接
   数据表与自身进行连接

需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
    查询父栏目名称和其他子栏目名称
*/

USE DATABASE school;
-- 创建一个表
CREATE TABLE `category` (
    `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
    `pid` INT(10) NOT NULL COMMENT '父id',
    `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
    PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

SELECT a.`categoryName` AS 父栏目, b.`categoryName` AS 子栏目
FROM `category` a, `category` b
WHERE a.`categoryid` = b.`pid`;

注：文章来源狂神说MySQL

posted @ 2021-03-09 19:41 关忆北阅读(111) 评论(0) 收藏举报

刷新页面返回顶部

关忆北

使人成熟的不是岁月，是经历。