MySQL学习笔记—基本操作汇总

时间:2022-09-16 19:42:45

目录: 

一, mysql 登录参数

二,修改MySQL提示符:2 种方式

三,MySQL常用命令

四,数据库操作 

五,数据表操作

六,约束

七,约束扩展

八,外键约束的参照操作

九,表级约束与列级约束

十,小结 

十一,修改数据表(一)

十二,修改数据表(二)

十三,insert插入记录

十四,update方法

十五,select方法

十六,group by方法

十七,order by方法

十八,limit方法


一,mysql 登录参数

1, -D,--database=name 打开指定数据库
2,- -delimiter = name 指定分隔符
3,-h,- -host = name 服务器名称
4,-p,- -password[ = name] 密码
5,-P,- -port=# 端口号
6,- -prompt =name 设置提示符
7,-u,- -user =name 用户名
8,-V,- -version 输出版本信息并且退出
9,- d   --database 当前数据库
例:mysql -u root -p -P 127.0.0.1
127.0.0.1 : 网络术语当中成为本地回环地址



二,修改MySQL提示符:2 种方式
1,连接客户端时通过参数指定
shell> mysql -uroot -proot - -prompt  提示符
2,连接上客户端后,通过prompt命令修改
mysql>prompt  提示符


三,MySQL常用命令
1,显示当前服务器版本
SELECT VERSION();
2,显示当前日期时间
SELECT NOW();
3,显示当前用户
SELECT USER();


四,数据库操作 

(花括号必选,中括号可选),语句后面要分号。

增:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
删:DROP DATABASE db_name;
改:ALTER DATABASE db_name CHARACTER SET charset_name;
查:显示所有的数据库 :SHOW DATABASES;


五,数据表操作
1,数据库最重要的组成部分之一,是其他对象的基础。
(1)打开数据库
USE 数据库名称;
(2)创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
...
)
2,查看数据表:(查看选择的数据库的表)
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];
例:
SHOW TABLES;//查看当前选择的数据库的所有表
SHOW TABLES FROM TEST;//查看TEST数据库中的所有表,当前选择数据库位置不变。
SELECT DATABASE();//查看当前选择的数据库
SHOW COLUMNS FROM tbl_name;//查看数据表结构


3,插入记录(数据表中的 行)

INSERT [INTO] tb1_name [(col_name,...)] VALUES(val,...);
INSERT 关键字
INTO 可省略
tb1_name 数据表的名称
col_name 数据表中字段(列)的名称 可以省略,当省略时,后面插入记录数要和所有字段数相符,不省略时,要输入字段名称,后面插入记录也要相符。
VALUES(val,..) 关键字(记录) 记录数要和前面字段相符


4,记录查找
SELECT expr,...FROM tb1_name
SELECT 查找关键字 expr 字段 *表示全部
FROM tb1_name哪个数据表


六,约束

1. 约束保证数据的完整性和一致性。
2. 约束分为表级约约束和列级约束。
3. 约束类型包括:
NOT NULL(非空约束)
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)


FOREIGN KEY(外键约束)
作用:
保持数据一致性,完整性
实现一对一或者一对多关系
要求:
1. 父表和子表必须使用相同的存储引擎,而且禁止使用临时表。
子表: 具有外键列的表。
父表: 子表参照的表。
2. 数据表的存储引擎只能为InnoDB。
3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
* 数字长度或是否有符号位必须相同,否则将提示错误并无法创建外键约束: 
外键列: 加过外键列的列。
参照列: 外键列所参照的列。
4. 外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引。


编辑数据表的默认存储引擎
MySQL配置文件: default-storage-engine=INNODB
查看创建表的存储引擎: SHOW CREATE TABLE table_name;


例子:
父表:
mysql> CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, //参照列: id
pnane VARCHAR(20) NOT NULL
);
子表:
mysql> CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
pid SMALLINT UNSIGNED, 
FOREIGN KEY (pid) REFERENCES provinces (id) //外键列: pid 参照列:provinces.id
);


七,约束扩展

1.自动编号 - AUTO_INCREMENT

自动编号,且必须与主键配合使用
自动编号AUTO_INCREMENT
1、自动编号:保证记录的唯一性
2、类型必须为整型(可以是FLOAT(5,0)等,小数点后必须为0),必须和主键PRIMARY KEY组合使用
3、默认情况下,起始值为1,每次的增量为1
默认情况下,起始值为1, 每次增量为1.
CREATE TABLE tb3(
id SMALLINT UNSIGNED AUTO_INCREMENT,
username VARCHAR(30) NOT NULL); //有错,自动增量字段必须设置成主键


主键可以写为PRIMARY KEY,也可以写成KEY
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL
auto_increment必须和主键primary key一起使用,但是主键primary key不一定要和auto_increment一块使用




八,外键约束的参照操作:
1. CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。
2. SET NULL: 从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL。
3. RESTRICT: 拒绝对父表的删除或者更新操作。
4. NO ACTION: 标准SQL的关键字,在MySQL中与RESTRICT相同。


例子: 
添加父表:
mysql> CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, //参照列: id
pnane VARCHAR(20) NOT NULL
);
添加子表带CASCADE操作:
mysql> CREATE TABLE user1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
);
为父表添加数据:
INSERT INTO provinces(pname) VALUES('A')
INSERT INTO provinces(pname) VALUES('B')
INSERT INTO provinces(pname) VALUES('C')
删除父表中的数据,并影响子表数据:
DELETE FROM provinces where id = 3;
==> 父表id为3的数据被删, 其子表中pid为3的数据也被删!
*外键约束 很少使用物理外键约束(innodb支持),在实际开发用的比较多是逻辑外键(不使用freign key)



九,表级约束与列级约束
1.表级约束与列级约束
(1)对一个数据列建立的约束,称为列级约束
(2)对多个数据列建立的约束,称为表级约束
(3)列级约束既可以在列定义时声明,也可以在列定以后声明
(4)表级约束只能在列定义后声明
2.主键、外键、非空、唯一、check,default都是约束条件。主键、外键、既可以作为表级约束,也可作为列级约束
而像非空、唯一等( not null, default )只有列级约束


十,小结 

数据类型 整型 (tinyint smallint mediumint int bigint)

浮点型 (FLOAT{[M,D]},DOUBLE{[M,D]})M:总位数,D:小数点后位数
日期 (YEAR DATE TIME TIMESTEMP)
字符型 (CHAR VARCHAR TEXT)
DOS 界面连接数据库 mysql -u用户名 -p密码 -hHOST
----总结------
创建数据表:CREATE TABLE table_name;
查看数据表:SHOW COLUMNS FROM table_name;
向表中插入:INSERT table_name
查找记录 :SELECT * FROM table_name


创建数据表
CREATE TABLE tb5(
->id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
->username VARCHAR(20) NOT NULL UNIQUE KEY,
->sex ENUM('男','女','保密') DEFAULT '保密'
->);


十一,修改数据表(一)
1,添加
(1)添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
例如:
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
在 id 后面添加 username属性:
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
(2)添加多列
ALTER TABLE tbl_name ADD [COLUMN](col_name column_definition,...)该方法添加的列不可以指定位置
2,删除
(1)删除单列
ALTER TABLE tbl_name DROP [COLUMN] col_name
例如:ALTER TABLE users1 DROP truename;
(2)一次删除多列:
ALTER TABLE uses1 DROP password,DROP age;
也能删除一列的同时,新增一列。其间用逗号隔开。


3、添加主键约束:
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY[index_type] (col_name);
4、添加唯一约束
ALTER TABLE tbl_name ADD UNIQUE(col_name);
5、添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_type](外键列) REFERENCES 父类(参考列);
6、添加/删除默认DEFAULT约束
ALTER TABLE tbl_name ALTER col_name SET DEFAULT literal / DROP DEFAULT;


7、删除主键约束:ALTER TABLE 表名 DROP PRIMARY KEY;
8、删除唯一约束:ALTER TABLE 表名 DROP {INDEX|KEY} 约束所在字段;
9、删除外键约束:ALTER TABLE 表名 DROP FOREIGN KEY 外键索引名字;
10、查看外键约束名称:SHOW CREATE TABLE 表名;
11、查看索引:SHOW INDEXES FROM 表名;


十二,修改数据表(二)
修改列定义:
ALTER TABLE 表名 MODIFY 字段名 数据类型(FIRST或者AFTER 字段名);


修改列名称:
ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 (FIRST 或者 AFTER 字段名);

修改数据表名:
1、ALTER TABLE 表名 RENAME (TO|AS) 新表名;

2、RENAME TABLE 表名 TO 新表名(表名2 TO 新表名2);
//不建议随意修改,可能造成数据丢失||数据列之间的关联等


十三,insert插入记录
1,第一种方法
INSERT [INTO] table_name [(column_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...;
/**
column_name 表示列名
expr 表示表达式
DEFAULT 可直接书写,表示默认值
当字段有自动增加的属性后,在插入表的所有字段的数据时,可以填写 NULL 或 DEFAULT 这两个关键字,表示按照原先默认自动增长的属性
插入多行数据时,可以用逗号隔开
*/
Eg:
INSERT INTO table_name VALUE(DEFAULT,'BeforeDayBreak',md5('123'),DEFAULT,0),(NULL,'Superman',md5('123'),DEFAULT,0);


自增的属性可以为其赋值为null或者default来实现自增


2,insert的第二种方法:
INSERT [INTO] tb1_name SET col_name={expr|DEFAULT},……
说明:与第一种方式的区别在于,此方法可以使用子查询(SubQuery);且只能一次性插入一条记录
例:INSERT users SET username='Ben',password='456';


十四,update方法
更新记录UPDATE,有两种方式,单表和多表
1.单表更新:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={exp1|DEFAULT}[,col_name2=...][WHERE where_condition] (一般来说要用WHERE指定位置,不然所有数据都会被更新)

例:

不指定位置:UPDATE users set age = age + 5,sex = 0; 使表里所有记录的age加5
指定位置: UPDATE users set age = age+ + 10 WHERE id % 2=0; 取id为偶数的位置
update更新数据
update <表名>
set <列名>=<表达式>,[,<列名>=<表达式>]...
[where <条件>];


十五,select方法
select version();
select database();
select now();
select 表达式;(只是计算表达式返回结果不会依赖某个表)
select columns from 表:(显示表的结构)
select 字段,字段,.. from 表;(selecth后面指定的字段名影响返回结果的先后顺序)
select 表1.字段,表2.字段.. from 表;
select id AS userid, 字段 as 字段别名.... from 表。 (as对字段别名返回结果,不加AS后面的会默认是别名)


十六,group by方法
查询结果分组:
group by{col_name|position}
select * from users group by sex;
select * from users group by 1;(这里的一表示查询的第一个字段,这里查询所有,第一个字段就是id,也就是会按照字段进行分组)
ASC 升序默认 desc 降序


SELECT sex from users GROUP BY sex 按照sex进行分组。


十七,ord er by方法
对查询结果进行排序,默认是升序
order by{col_name}
select * from users order by id desc; //对一个字段排序
select * from users order by age,id desc; //两个字段同时排序
desc是降序
1、对查询结果进行排序:[ORDER BY [col_name | expr | position } [ASC|DESC],...]elect * from user order by id desc;
2、可以同时按多条字段进行排序,规则是先按前面的字段排,在基础上再按后面字段排。
3、如:SELECT * FROM users ORDER BY age,id DESC; 先按照age排序,如果age有重复的,重复的字段里按id排序


十八,limit方法
select * from users limit 2 ;从第一条开始返回,返回前两个;
select * from users limit 3,2 ;忽略前三条,从第四条开始,取前两条;//mysql从0开始计数和id无关
将查询出的数据插入到指定表的指定字段中,支持多条数据:
insert table_name(column_name) select column_name from table_name1;
例:INSERT test(username) SELECT username FROM users WHERE age >=30;
mysql分页要用到:
[LIMIT{[offset,]row_count|row_count OFFSET offset}]