MySQL常用命令、基础知识(持续更新~~)

1.连接数据

mysql -h localhost -u root -p

-h：指定要登录的MySQL主机名，登录本机可以省略
-u：用户名
-p：登录密码

使用exit 命令退出

2.MySQL常见的数据类型

varchar：可变长度的字符串，最长255，根据实际的数据长度动态分配空间。
char：定长字符串，最长255，分配固定长度的空间
int：整型，最长11
bigint：长整型
float：单精度浮点型数据
double：双精度浮点型数据
date：短日期类型，包括年月日信息
datetime：长日期类型，包括年月日时分秒信息
clob：字符大对象，最多可以存储4G的字符串。超过255个字符的都要采用CLOB字符大对象来存储。
blob：二进制大对象，专门用来存储图片、声音、视频等流媒体数据。

3.约束

在创建表的时候，可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性。

not null：非空约束，约束的字段不能为NULL
unique：唯一性约束，约束字段不能重复，但可以为NULL
primary key：主键约束
foreign key：外键约束
check：检查约束，mysql不支持，oracle支持

3.1非空约束

create table student(
	id int,
	name varchar(255) not null
);

3.2唯一约束

需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

unique(name,email)

3.3主键约束

任何一张表都应该有主键，主键值是每一行记录的唯一标识，主键字段中的每一个值都叫做：主键值。
主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）
一张表，主键约束只能添加1个
主键值一般都是数字，一般都是定长的

create table student(
    id int primary key,  //列级约束,单一主键
    name varchar(255)
);
create table student(
    id int,
    name varchar(255),
    email varchar(255),
    primary key(id,name) //表级约束,复合主键<在实际开发中建议使用单一主键！
);

3.4外键约束

外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。
对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）
外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。
主键不能包含空值，但允许在外键中出现空值

4.SQL语言

数据定义语言(data definition language，DDL)
数据操作语言(data manipulation language，DML)
数据控制语言(Data Control Language，DCL)
事务控制语言（Transaction Control Language，TCL）

4.1展示数据库

show databases;

4.2创建数据库

create database test;

4.3删除数据库

drop database test;

4.4创建数据表

首先选择一个数据库,然后再创建一个名为Student的表

use test;
create table student(
    id int not null auto_increment primary key COMMENT 'ID',
    name varchar(10) not null COMMENT '姓名',
    sex char(10) not null COMMENT '性别',
    age tinyint(100) not null COMMENT '年龄',
    dept char(4) not null COMMENT '专业') ENGINE=InnoDB 
    DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
    comment = '学生'; 

稍后解释各个专业名词的含义

4.5删除数据表

drop table student;
/**这两个的区别在哪里呢？**/
truncate table student;

4.6表结构的修改

ADD COLUMN <列名> <类型> [after 插入位置]/*添加字段，或者在某列后插入列*/
CHANGE COLUMN <旧列名> <新列名> <新列类型> /*改变字段名*/
MODIFY COLUMN <列名> <类型> /*修改字段类型*/
DROP COLUMN <列名> /*删除字段*/
RENAME TO <新表名> /*修改表名*/

修改字段的长度

alter table 表名 modify column 字段名 char(10);

稍后添加具体的例子

4.7增删改查

插入数据

insert into 表名(字段1,字段2) values(字段值1,字段值2)

 insert into Student (name, sex, age, dept) values ('张三', '男', 18, '软件');

删除数据

delete from 表名 where 字段名1= 字段值1
delete from 表名 /*删除表里所有数据*/

delete from student where name = '张三';
delete from student;

修改数据

update 表名 set 字段名1=字段值1 where 字段名2=字段值2
update 表名 set 字段名1=字段值1 /*修改表里所有数据*/

update student set age =20 where name = '张三';
update student set age =20;

查询数据

from --> join --> where --> group by -->having --> select --> distinct -->order by -->limit

（1）简单查询

select * from 表名

select * from student;

（2）where条件查询

select 列名称 from 表名称 where 条件;

条件：
比较：=、>、<、>=、<>、!=

is null、in、like、is not null

支持not查询， or 和 and 组合查询

范围限定：where age between 10 and 20;

子集限定：where course in (‘高数’,‘英语’);

嵌套查询

select * from student where age>18 and sex='女';

select * from student where dept like '软%';

select * from student where dept like '_件';

select * from student where age between 19 and 23;

select * from student where age in (20,21);

select student_id, name, course, grade from score where student_id in (select id from student where name='李四') and course='高数';

（3）分组（group by）

聚合函数

having必须和group by联合使用。常用函数：count、max、min、sum、avg

select count(*) from student where age>18;

select sex,avg(age) from student group by sex;

（4）去重(distinct)

select distinct name from student;

（5）排序(order by)

/**desc 降序  asc 升序**/
select * from student order by age desc;/**降序**/
select * from student order by age;/** 默认升序**/

（6）分页(limit)

/**从0开始查询5个**/
select * from score order by grade desc limit 5 offset 0;

连接查询

（1）内连接(inner join)

select * from a_table a inner join b_table b on a.a_id=b.b_id;

SELECT student.id, student.name, score.course, score.grade FROM student INNER JOIN score ON student.id = score.sid;

（2）左连接（left join）

left join是 left outer join的简写,它的全称是左外连接，是外连接中的一种
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id

（3）右连接(right join)

right join 是 right outer join的简写，全称右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL

SELECT * FROM student a right outer join b on a.a_id = b.b_id;

SELECT * FROM student a right outer join score b on a.name = b.name;

SELECT student.id, student.name, score.course, score.grade FROM student RIGHT JOIN score ON student.id = score.sid;

补充：

删除语句：DROP、TRUNCATE、 DELETE 的区别
DELETE属于DML命令，用于删除指定数据 ，执行后要进行事务提交，可以进行回滚操作。

DROP和TRUNCATE都是DDL命令，执行后会自动提交，不能回滚

DROP删除表记录，并释放空间；TRUNCATE删除表，并释放空间，但不删除表的定义，表的结构还在；DELETE删除整个表时，仅删除表的内容，保留表的定义，不释放空间。

执行速度：drop > truncate > delete

参考资料

• 图解sql顺序

• MySQL数据库基础入门

如有错误，请批评指正。