Mysql常用命令练习（一）

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

Mysql常用命令练习一


注语句中所有大写的都是固定格式

一、数据库的三层结构

请添加图片描述

二、数据库

2.1、创建数据库

注意在创建数据库、表的时候为了规避关键字可以使用``反引号解决

CRETATE DATABASE db_name;

CRETATE DATABASE lcl_db01;		#创建数据库lcl_db01,默认字符集为utf8默认校验规则utf8_general_ci

CRETATE DATABASE lcl_db02 CHARACTER SET utf8;	#创建数据库lcl_db02,指定字符集utf8

CRETATE DATABASE lcl_db03 CHARACTER SET utf8 COLLATE utf8_bin;	#创建数据库lcl_db02,指定字符集utf8指定校验规则utf8_bin

1. CHARACTER SET:指定数据库采用的字符集如果不指定字符集,默认utf8
2. COLLATE:指定数据库宇符集的校对规则(常用的utf8_bin[区分大小写]、utf8_general_ci[不区分大小写]注意默认是utf8_general_ci)[举例说明database.sql文件]

2.2、查看、删除数据库

显示数据库语句

SHOW DATABASES;

显示数据库创建的语句

SHOW CRETAE DATABASE db_name;

删除数据库语句

DROP DATABASES db_name

2.3、备份和恢复数据库

备份数据库

mysqldump -h主机地址 -u用户名 -p密码 -B db_name1 db_name2 > 文件名.sql		#在linux命令行执行		-B备份多个数据库
mysqldump -h主机地址 -u用户名 -p密码 db_name 表1 表2 > 文件名.sql				#备份表

mysqldump -h192.168.153.123 -uroot -p123456 -B lcl_01 > /home/ydwl/lcl_01.sql	#将lcl_01库备份到/home/ydwl目录下命名为lcl_01.sql
mysqldump -h192.168.153.123 -uroot -p123456 -B lcl_01 > d:\\lcl_01.sql			#将lcl_01库备份到d盘下命名为lcl_01.sql

恢复数据库

source 文件名.sql				#mysql命令行执行
mysqldump -u用户名 -p密码 < 文件名.sql			#linux命令行执行

source /home/ydwl/lcl_01.sql	
soucre d:\\lcl_01.sql

三、表

3.1、创建表

mysql常用的数据类型(列类型)

请添加图片描述

创建表

CRETAE TABLE 表名 (列名1 数据类型,列名2 数据类型);		#创建表
CRETAE TABLE 表名 (列名1 数据类型,列名2 数据类型) character set 字符集 collate 校对规则 engine 引擎;		#创建表指定字符集和校对规则

field:指定列名 datatype:指定列类型(字段类型)
character set 如不指定则为所在数据库字符集
collate:如不指定则为所在数据库校对规则
engine:引擎

CRETAE TABLE `user` (id INT,`name` VARCHAR(255),`password` VARCHAR(255));	#创建user表指定字段和字段类型

查看表

查看当前数据库下的表

show tables;

查看其他数据库下的表

show tables from db_name;

查看表结构

查看数据表基本结构

show columns from tb_name;
describe tb_name;
desc tb_name;

查看数据表详细结构

show create table tb_name;

练习

创建emp表

字段属性
id整形
name字符型
sex字符型
brithday日期型(date)
entry_date日期型(date)
job字符型
salary小数型
resume文本型
CREATE TABLE `emp` (
	id int,
	name varchar(32),
	sex char(1),
	brithday date,
	entry_date DATETIME,
	job varchar(32),
	salary double,
	resume text
	) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;			#指定字符集 校验规则 引擎

3.2、修改表

使用ALTER TABLE 语句追加修改或删除列的语法

修改表名

ALTER TABLE 旧表名 rename to 新表名;

rename table 旧表名 to 新表名;

修改表的字符集

ALTER TABLE 表名 character set 字符集;

添加列

ALTER TABLE tb_name ADD 新列名 数据类型;							#默认最后一列

ALTER TABLE tb_name ADD 新列名 数据类型 after 已存在列名;			#在已存在的列名后面追加一列

ALTER TABLE tb_name ADD 新列名 数据类型 first;					#新增的列名放在第一位

修改列

修改表列名

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

修改列的数据类型

ALTER TABLE tb_name MODIFY 列名 数据类型;

修改列的排列位置

ALTER TABLE tb_name MODIFY 列名1 数据类型 alter 列名2;			#将列名1修改到列2后面

ALTER TABLE tb_name MODIFY 列名 数据类型 first;				#将列名1修改到第一位置

删除列

ALTER TABLE tb_name DROP 列名;

练习

根据前面创建的emp表进行修改

1、员工表emp的上增加一 个image列varchar类型 (要求在resume后面)

ALTER TABLE emp ADD image varchar(32);

2、修改job列使其长度为60

ALTER TABLE emp MODIFY VARCHAR(60);

3、删除sex列

ALTER TABLE emp drop sex;

4、表名改为employee

ALTER TABLE emp rename employee;

5、修改表的字符集为utf8

ALTER TABLE employee character set utf8;

6、列名name修改为user_name

ALTER TABLE employee change name user_name varchar(32);

3.3、删除表

DROP TABLE tb_name;

DROP TABLE if exists tb_name1,tb_name2;			#可以一次删除一个或多个没有被其他表关联的数据表

四、增删改查

4.1、Insert添加数据

INSERT INTO tb_name (列名1,列名2,列名3) 
		values(数据1,数据2,数据3);				#添加单行记录
	
INSERT INTO tb_name (列名1,列名2,列名3) 
		values(数据1,数据2,数据3),
		(数据1,数据2,数据3);						#添加多行记录

Insert注意事项

  1. 插入的数据应与字段的数据类型相同。

    例如把’abc’添加到int类型会错误

  2. 数据的长度应在列的规定范围内。

    例如不能将一个长度为80的字符串加到长度为40的列中。

  3. 在values中列出的数据位置必须与被加入的列的排列位置相对应。

  4. 字符和日期型数据应包含在单引号中。

  5. 列可以插入空值 [前提是该字段允许为空]insert into table value(null)

  6. insert into tab_name (列名…) values (),(),()形式添加多条记录

  7. 如果是给表中的所有字段添加数据可以不写前面的字段名称

  8. 默认值的使用当不给某个字段值时如果有默认值就会添加否则报错

练习

创建一张商品表goods (id int, goods_name varchar(10),price double)

添加两条记录

CREATE TABLE `goods` (id int,goods_name varchar(10),price double);

INSERT INTO `goods` (id,goods_name,price)
		VALUES(1,'华为手机','4000'),
		(2,'OPPO手机','3500');

使用 INSERT 语句向表 employee 中插入两个员工信息

INSERT INTO employee (id ,user_name ,brithday ,entry_date ,job ,Salary ,resume ,image)
    VALUES(2 ,'ly' ,'2000-6-10' ,'2022-4-26' ,'pc' ,'700' ,'无' ,' ');

INSERT INTO employee VALUES(3 ,'ds' ,'2005-4-6' ,'2022-8-20' ,'pf' ,'700' ,'无' ,' ');

4.2、Delete删除数据

DELECT FROM tb_name;		#可以加WHERE语句进行筛选不加删除整张表内容

Delete注意事项

  1. 如果不使用where子句将删除表中所有数据。慎用
  2. Delete语句不能删除某一列的值 (可使用update 设为null或者’')
  3. 使用delete语句仅删除记录不删除表本身。如要删除表使用drop table语句。drop table tb_name;

练习

使用employee进行测试

1、删除表中名称为ds的记录

DELECT FROM employee WHERE user_name = 'ds';

2、删除表中所有记录

DELECT FROM employee;

4.3、Update更新数据

UPDATE tb_name SET 列名=值;				#可以加WHERE语句进行筛选不加修改整个列内容

Update注意事项

  1. UPDATE语法可以用新值更新原有表行中的各列。
  2. SET子句指示要修改哪些列和要给予哪些值。
  3. WHERE子句指定应更新哪些行。如没有WHERE子句则更新所有的行。慎用
  4. 如果需要修改多个字段可以通过set字段1=值1,字段2=值2…

练习

在上面创建的employee表中修改表中的纪录

1、将所有员工薪水修改为5000元。

UPDATE employee SET Salary = 5000;

2、将姓名为ly的员工薪水修改为3000元。

UPDATE employee SET Salary = 3000 WHERE user_name = 'ly';

3、将ds的薪水在原有基础上增加1000元。

UPDATE employee SET Salart = Salart + 1000 WHERE user_name = 'ds';

4.4、Select查找数据

SELECT [DISTINCT] * | 列名1,列名2,列名3 FROM tb_name;		#可以加WHERE语句进行筛选不加查找整个列表内容

Select注意事项

  1. Select指定查询哪些列的数据。
  2. column指定列名。
  3. *号代表查询所有列。
  4. FROM指定查询哪张表。
  5. DISTINCT可选指显示结果时去掉重复数据

单表

运算

SELECT *,(列1 运算符 列2 运算符 列3) FROM tb_name;

AS(别名

SELECT 字段名 AS 别名 FROM tb_name

练习查询

创建学生表

create TABLE student(
id int not null DEFAULT 1,
name VARCHAR(20) not null DEFAULT '',
chinese FLOAT not null DEFAULT 0.0,
english FLOAT not null DEFAULT 0.0,
math FLOAT not null DEFAULT 0.0
);

INSERT INTO student (id,name,chinese,english,math) VALUES (1,'李小',89,78,90);
INSERT INTO student (id,name,chinese,english,math) VALUES (2,'张飞',67,98,56);
INSERT INTO student (id,name,chinese,english,math) VALUES (3,'宋江',87,78,77);
INSERT INTO student (id,name,chinese,english,math) VALUES (4,'关羽',88,98,90);
INSERT INTO student (id,name,chinese,english,math) VALUES (5,'赵云',82,84,67);
INSERT INTO student (id,name,chinese,english,math) VALUES (6,'欧阳锋',55,85,45);
INSERT INTO student (id,name,chinese,english,math) VALUES (7,'黄蓉',75,65,30);
INSERT INTO student (id,name,chinese,english,math) VALUES (8,'韩信',45,65,99);
INSERT INTO student (id,name,chinese,english,math) VALUES (9,'李信',65,87,90);

1、查询表中所有学生的信息。

SELECT * FROM student;

2、查询表中所有学生的姓名和对应的英语成绩。

SELECT name,english FROM student;

3、过滤表中英语重复数据distinct

SELECT DISTINCT english FROM student;

注要查询的记录每个字段都相同才会去重

4、统计每个学生的总分

SELECT name, (chinese + english + math) FROM student;

5、在所有学生总分上加10分

SELECT name, (chinese + english + math) + 10 FROM student;

或

SELECT name, (chinese + english + math + 10) FROM student;

6、使用别名表示学生分数

SELECT name, (chinese + english + math) + 10 AS total FROM student;

或

SELECT name, (chinese + english + math + 10) AS total FROM student;

WHERE(过滤)

在where语句中经常使用的运算符

请添加图片描述

练习

1、查询姓名为赵云的学生成绩

SELECT * FROM student WHERE name = '赵云';

2、查询英语成绩大于90分的同学

SELECT * FROM student WHERE english > 90;

3、查询总分大于200的同学

SELECT name,(chinese + english + math) AS total FROM student 
	WHERE (chinese + english + math) > 200;

4、查询math大于60且id大于4的学生成绩

SELECT * FROM student 
	WHERE math > 60 AND id > 4;

5、查询英语成绩大于语文成绩的同学

SELECT name,chinese,english FROM student 
	WHERE english > chinese;

6、查询总分大于200且语文成绩小于数学成绩姓李的学生

SELECT name,(chinese + english + math) AS total FROM student 
	WHERE (chinese + english + math) > 200 AND chinese < math AND name LIKE '李%';		#李%表示名字以韩开头的

7、查询语文分数在70-80之间的同学 #BETWEEN是闭区间包含AND两边的内容

SELECT name,chinese FROM student WHERE chinese BETWEEN 71 AND 79;

8、查询总分数为257,241,233的同学

SELECT name,(chinese + english + math) AS total FROM student 
	WHERE (chinese + english + math) in(257,241,233);

9、查询所有姓李 或者 姓宋的学生成绩

SELECT * FROM student 
	WHERE name LIKE '李%' OR name LIKE '宋%';

10、查询数学成绩别语文成绩多30分的同学

SELECT * FROM student 
	WHERE math - chinese > 30;			#运算符加减乘除都可以用

order by(排序)

SELECT 列名1,列名2,列名2 FROM student ORDER BY 列名 asc|desc		#默认asc

order by注意事项

  1. order by 指定排序的列排序的列既可以是表中的列名也可以是select语句指定的列名
  2. asc 升序[默认]、Desc 降序
  3. order by 字句应该位于select语句的结尾

练习

1、对数学成绩排序后输出【升序】

SELECT name,math FROM student ORDER BY math asc;

2、对总分按从高到底的顺序输出

SELECT name,(chinese + english + math) AS total FROM student
	ORDER BY total desc;

3、对姓李的学生总成绩排序输出【升序】

SELECT name,(chinese + english + math) AS total FROM student
	WHERE name like '李%' ORDER BY total asc;

limit(分页查询)

SELECT * FROM tb_name limit start,rows;


公式
SELECT * FROM tb_name LIMIT 每页显示记录数*(第几页-1),每页显示记录数

练习

按数学成绩降序取出每页显示3条记录。请分别显示第二页和第三页

第二页select * from student order by math desc limit 3,3;

第三页select * from student order by math desc limit 6,3;
阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
标签: mysql
返回列表

上一篇：TCP协议的长连接和短连接详解

下一篇：Java内存模型和线程安全

“Mysql常用命令练习（一）” 的相关文章

Docker下安装mysql超详细步骤_docker mysql1年前 (2023-02-02)
eclipse连接MySql数据库(菜鸟入门之详细步骤)1年前 (2023-02-02)
MySQL中深入浅出索引1年前 (2023-02-02)
SQL---mysql删除重复数据1年前 (2023-02-02)
MySQL子查询篇（精选20道子查询练习题）1年前 (2023-02-02)
MySQL的锁机制：MyISAM 表锁、InnoDB行锁1年前 (2023-02-02)
Docker--安装mysql8--方法/步骤1年前 (2023-02-02)
【大数据系列之MySQL】（四）：MySQL修改my.ini配置文件1年前 (2023-02-02)
【大数据系列之MySQL】（二十七）：子查询的经典使用案例1年前 (2023-02-02)
MySQL详细学习教程（建议收藏）_mysql1年前 (2023-02-02)

阿里云国际版