MYSQL常用命令

进入

1、初始化操作
mysqld --initialize --console

2、将mysql添加到系统服务中
mysqld --install
3、启动服务
net start mysql
4、登录数据库
mysql -uroot -p
输入步骤1中生成的口令，可以采用拷贝粘贴的方式
5、修改口令，因为步骤1生成的口令太难记忆了，如果是云平台上的数据库，则建议不要修改
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
使用quit退出
停止服务：net stop mysql
卸载服务：mysqld --remove
6、如果忘记口令，则删除data文件夹，重新执行步骤1-5即可

常用mysql服务命令

启动数据库服务器 net start mysql
启动客户端登录mysql数据库：mysql -u username -p
例如 mysql -uroot -p123456 有安全隐患，所以一般建议mysql -uroot -p回车后再输入口令
退出mysql客户端：quit
查看mysql版本：select version(); 在mysql的命令行客户端中执行
关闭数据库服务：net stop mysql
数据库服务器开机自启：mysqld --install

创建数据库: create database 数据库名称 default character set utf8;
create database test default character set utf8;
创建成功后，在对应的安装路径下出现一个data/test文件夹

查看所有的数据库 show databases;

选定默认数据库：use dbname;
例如 use test;

查看当前的数据库：select database();

显示当前数据库中所有表：show tables;

创建新表： create table 表名称(列名称 数据类型 约束规则,…) engine=数据库引擎 default charset utf8;
mysql> create table t_student(
-> id bigint primary key,
-> name varchar(20) not null,
-> sex boolean default 1
-> ) engine=innodb default charset utf8;
创建表结束后则会发现文件夹data/test/下出现一个文件t_student.ibd
如果设置engine=myisam则会创建两个文件
myd数据信息文件，是表的数据文件
myi索引信息文件，是表数据文件中任何索引的数据树

查看表中的列定义
简单方法： desc t_student;
查看具体的列定义
show create table t_student;

放弃正在输入的命令：\c
显示命令清单：\h
查看MySQL服务器状态信息：\s
Mysql已存数据库
information_schema – 系统数据库，这个数据库损坏就会导致DBMS无法正常启动，所以这个数据库不要用户使用
schemata – 可以通过此表获取已经有数据库
tables – 可通过此表获知某数据库的所有表.
mysql-系统数据库，其中包括时区、权限等相关配置。也是属于系统数据库，一般不直接修改，仅供DBMS使用
sys系统数据库，其中保存和锁、统计等相关信息，也是属于系统数据库，不直接修改，仅供DBMS使用
performance_schema系统数据库，不直接修改，仅供DBMS使用。用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况

test – 非系统数据库,测试使用
SELECT * FROM TABLES WHERE TABLE_SCHEMA=‘mysql’ 是查询mysql数据库中的所有表

常见的SQL命令

SQL语言都基于SQL92标准，但各数据库之间又存在差异。目前最新的标准为SQL99，它同样兼容SQL92。
SQL-Structured QueryLanguage结构化查询语言。
Oralce划分为DDL、DML、DCL、TCL
MySQL划分为DDL、DCL、DQL、DML

DDL – 数据定义语言Data Definition Language
建库、建表、设置约束等：create\drop\alter
truncate表的截断
DCL – 数据控制语言　Data Controll Language
数据授权，收回授权。grant\revoke
DQL - 数据查询语言
select
DML－数据操纵语言Data Manipulation Language
主要指数据的增删改。delete\update\insert\call

SQL 对大小写不敏感；例如Insert InSerT没有区别
默认字符串比较也不区分大小写
默认分号是在数据库系统中分隔每条SQL语句的标准方法，可以使用delimiter进行修改

数据库涉及字符规范（不是语法硬性规则）

命名采用26个英文字母和0-9这十个自然数，加上下划线’_'组成,共63个字符.不能出现其他字符(注释除外)
以上命名都不得超过30个字符的系统限制.变量名的长度限制为29(不包括标识字符@)
数据对象、变量的命名都采用英文字符,禁止使用中文命名.绝对不要在对象名的字符之间留空格
如果使用特殊符号，请使用反引号括起来。create table t 1(id int); 注意这里不是单引号，是反引号
小心保留词,要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突

保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性.假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型

创建数据表的语法规则

CREATE TABLE <表名>
–（<列名> <数据类型> [ <列级完整性约束条件> ]
–[，<列名><数据类型>[ <列级完整性约束条件>] ] …
–[，<表级完整性约束条件> ] ）；
其中:
<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

DBMS为了保证数据的有效性，给开发者提供了3种完整性约束，用于定义DBMS在数据入库时进行检查，如果合法则入库，否则报错拒绝
实体完整性—主键约束 primary key ----非空唯一
表定义中不是必须有主键 create table t1(id int)
参照完整性—外键约束 foreign key—表示某个列的取值范围在另外一个列的范围内
用户自定义完整性—4种用于自定义约束 not null/null default check unique
额外：域完整性-----取值范围

删除表: drop table 表名称; 如果存在则删除表，否则报错
drop table if exists t1;

需求：
建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号不能为空，值是唯一的，并且姓名取值也唯一

create table student(
sno bigint primary key auto_increment,
sname varchar(20) not null unique,
ssex char(1) default ‘男’,
sdept varchar(32)
)engine=innodb default charset utf8;

MySQL的数据类型

数值列类型
mysql提供了五种整型： tinyint、smallint、mediumint、int和bigint。int为integer的缩写。这些类型在可表示的取值范围上是不同的。
整数列可定义为unsigned从而禁用负值；这使列的取值范围为0以上。各种类型的存储量需求也是不同的。取值范围较大的类型所需的存储量较大。
mysql 提供三种浮点类型： float、double和decimal。

create table t1() 在当前数据库中表名称不允许重复，如果重复则报错
create table if not exists t1 如果t1表不存在才执行创建操作，如果已存在则不执行任何操作
无符号的写法：
create table t13(id tinyint unsigned);
定点的浮点数
create table t14(id numeric(8,2)); 表示总位宽为8位，小数为2位，其中的正负号和小数点不进行计算
小数处理采用的是四舍五入

完整性约束
primary key 用于声明主键，非空唯一。一个表只能定义一个主键，但是主键允许使用多个列构成(复合主键)
create table t17(id int primary key);
create table t18(
id int, name varchar(20),
primary key(id,name)); 表示id和name的组合不允许为空【任何一个都不允许为null】，不允许重复，不是其中某个列不允许为空，不允许重复
4种用户定义约束
null允许为空，默认/not null不允许为空 id int not null表示id列不允许为空，如果插入null值则报错

default设置默认值，如果试图插入null，则加入null，默认值无效；如果不针对当前列进行操作，则默认值生效
create table t1(id int default 1,name varchar(20));
Insert into t1 values(null,‘yan’); 此时id插入null值。如果insert into t1(name) values(‘yan’)则默认值生效

check检查型约束，可以定义，语法create table t1(id int check(id>5)); 要求插入的数据必须满足条件id>5，但是在mysql中无效

unique唯一性约束，表示列中的数据不允许重复，否则报错，拒绝操作
create table t1(id int unique); 允许为null，null表示不确定的值
数值列属性
AUTO_INCREMENT以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。MySQL支持多种数据表，每种数据表的自增属性都有差异
create table t16(id int primary key auto_increment); 只有不插入数据或者插入null值时才会自动生成自增的值

字符串列类型
在mysql中使用单引号表示字符串类型，事实上不区分单双引号，但是一般建议使用单引号，允许使用\转义字符
类型名 参数 说明
char(n) 0-255 定长字符串
varchar(n) 0-65535 可变长字符串
create table t1(id char(2)); 表示id为字符串类型，允许最大存储2个字符，不区分中英文
char(2)表示存储的字符个数为2，如果实际长度大于2则报错；如果实际存放的字符数不足，则自动末尾补充空格。
varchar(2)表示存储的字符个数最大为2，存放字符数不足时按照实际长度存放
如果存放字符串末尾是空格时，会自动裁剪到满足长度限制为止，注意不能裁剪开头的和中间的空格
等值判定时空格不参与

char与varchar的区别

1、char是定长数据，如果char(10)但里面只保存了1个字符，则它的实际长度仍然是10。Mysql对查询的结果进行了处理 ，所以使用length无法判断它所占用的长度；varchar(10)时，如果里面只保存了1个字符，则它的长度为1，所以varchar被称了可变长字符
2、char的取值范围为0-255；varchar的取值范围为0-65535。如果需要存储的字符串的长度跟所有值的平均长度相差不大，适合用char，如MD5。 对于经常改变的值，char优于varchar，原因是固定长度的行不容易产生碎片。
3、对于很短的列，char优于varchar，原因是varchar需要额外一个或两个字节存储字符串的长度
4、char和varchar后面如果有空格，char会自动去掉空格后存储，varchar虽然不会去掉空格，但在进行字符串比较时，会去掉空格进行比较

大对象类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求

TINYTEXT可变长度最多 255 个字符；TEXT可变长度最多 65535 个字符；MEDIUMTEXT可变长度最多 16M个字符；LONGTEXT可变长度，最多4G个字符

set和枚举

enum枚举类型实际上是当作字符串进行处理，设置当前列中所允许的取值，取值只能是枚举值中的任意一个值
create table t1(id enum(‘a’,’b’,’c’));
set集合类型实际上是当作字符串进行处理，设置当前列中所允许的取值，取值可以是集合中的任意多个值
create table t1(id set(‘a’,’b’,’c’));

日期和时间列类型

date类型只能存放日期，datetime可以存放日期和时间

datetime一般供用户使用，而timestamp可以存放1970-2038年的日期和时间，一般供系统使用

bit 表示1个二进制的位 bit(8) 表示8个二进制的位，其中n的取值范围为1-64

注意boolean类型

总结

定义数据类型就是定义列，数据类型决定数据的特性
数据类型主要分为字符串类型，浮点书类型和定点数类型，日期类型以及二进制类型
数据类型在不同的存储引擎上表现不同
根据所采用的数据类型，需求和数据特性选择数据类型
删除操作drop
删除数据库 DROP DATABASE <数据库名>;
drop database test;
删除数据表 DROP TABLE <表名>;
drop table t_users;
基本表删除数据、表上的索引都删除
表上的视图往往仍然保留，但无法引用
删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述（标准中没有，认为表建立后就永久存在）
show create table t1;

修改基本表alter

修改表 ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；
说明：
<表名>：要修改的基本表
ADD子句：增加新列和新的完整性约束条件
DROP子句：删除指定的完整性约束条件
MODIFY子句：用于修改列名和数据类型

基本练习

1、创建一个数据库test，如果数据库已经存在则删除
drop database if exists test;
create database test default character set utf8;
2、切换当前数据库
select database(); 查看当前数据库
use test;
3、创建一个用于存储学生信息的学生表 student
编号id，姓名name，成绩score
show tables; 查看当前库中的所有表
drop table if exists student; 如果已经存在student表则执行删除操作
create table if not exists student(
id bigint primary key auto_increment,
name varchar(10) not null,
score numeric(4,1) default 0
)engine=innodb default charset utf8;
4、向Student表增加“入学时间”列，其数据类型为日期型
alter table student add scome date;
不论基本表中原来是否已有数据，新增加的列一律为空值。所以如果表中已经有数据，则注意not null约束的问题
5、将年龄的数据类型改为半字长整数
alter table student add age int; 向表中添加一个int类型的列age
desc student; 查看当前的表结构
alter table student modify age smallint;
6、删除学生姓名必须取唯一值的约束
alter table student modify name varchar(10) unique;

alter table student drop name; 删除指定列name
alter table student add name varchar(10); 重新添加列name
对表的基本操作 DML+DQL
增加数据:
Insert into 表名称 values(值的列表)
Insert into tb_users values(1,’yan’,’123’) 注意和表的列定义一一对应，当值不确定时也必须写null
Insert into 表名称(列名称,…) values(对应的值,…) 注意一一对应，注意数据类型。如果没有出现的列则自动插入null

插入数据总结

字符类型和日期类型的数据前后要添加’’单引号
没有指定列时，要给出完整数据，且要对应顺序：
Insert into student values(1,’Tom’,’1’);
优化写入，指定列：
Insert into stud(id,name)values(1,’jack’);

修改数据

update 表名称 set 列名称=值, … 将指定表中指定列的值全部修改为指定值
update stud set name=‘Jack’; 将stud表中的所有行的name列值修改为jack
update 表名称set 列名称=值, … where 条件 只修改满足条件的指定行上列值
update stud set name=‘Jack’where id=1; 将stud表中的id值为1的行的name列值修改为jack

同时修改两个值：update stud setname=‘Rose’,id=22 where id=1;

在原值上面进行累加操作：update product set price=price1.1 －所有商品的价格上涨10%
update product set price=price1.1 where cname=‘食品’－只有列名为cname值为食品的商品，价格上涨10%

删除数据

delete from 表名称; 删除表中的所有数据,表结构仍旧存在,类似truncate table 表名称
delete from 表名称 where 条件; 根据条件删除数据，例如delete from tb_users where id>3则会删除所有id>3的行
delete from stud 全部删除
delete from stud where id=1; 删除指定的行
特殊写法： delete from stud where 1=1 delete from stud where 1=2

也可以使用 TRUNCATE TABLE stud; 它与delete的区别是不记录操作日志，即无法恢复数据
从执行效果上看 truncate table stud和delete from stud一致，都是删除了stud表中的所有数据，但是执行是有区别的。
truncate table不是DML语句，没有事务的概念，所以直接执行删除。不能撤销操作
delete from属于DML语句，有事务，所以在删除前需要记录日志，以供事务执行回滚撤销操作。
一般针对大量数据的删除操作，使用truncate执行效率高

查询数据

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] … FROM <表名或视图名>[，<表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；

select * from 表名称 查询指定表中的所有行的所有列数据，用于指代所有列。事实上一般建议写列名称，而不是号
select * from tb_users;
Select 列名称,… from 表名称 查询指定表中的所有行的指定列的数据，没有指定的列不查询
select username,password from tb_users; 只获取username和password两个列的值，而id列的值不获取

select *或者指定列 from 表名称 where 条件; 只查询满足条件的行
条件：
比较 >大于 >=大于等于 <小于 <=小于等于 !=不等于 =等于比较，不是赋值
select * from stud where age>=18; 查询年龄大于等于18岁的学生
select * from stud where age=18; 注意=不是赋值，是等值判断，查询年龄为18岁的学生
特殊比较 =null空值判断
在数据库中null表示不确定的值，所以判断不能使用=号，只能通过is null判空或者is not null非空进行判断
select * from student where name is null;
select * from student where name is not null;

注意：’’空字符串和null不是一样的 is null可以获取id=6，但是不能获取id=7

from语句在mysql中不是必须的【数据库不同有所区别】

字符串的比较
允许进行大小的比较，但是一般使用相等和不等于的判定较多
select * from tb_users where username=’zhangsan’ and password=’123456’

模糊查询 like _和%，例如查询所有姓张的学生
使用通配符_或者%，必须使用like或者not like运算符，不能使用=号
表示一个任意字符
查询所有姓张的同学 select * from stud where name like ‘张%’ 其中%表示任意个数的任意字符
查询所有姓张的同学，并且名字为2个字符 select * from stud where name like '张’ 其中_表示1个任意字符
查询所有名字中有书字的同学 select * from stud where name like ‘%书%’
查询所有名字以书字结尾的同学 select * from stud where name like ‘%书’