MySQL学习总结

1 MySQL数据类型

• 数值类型：tinyint,smallint,mediumint,int,bigint,float,double,decimal;
• 日期类型：date,time,datetime,year,timestamp(不用赋值，操作时系统自动更新）；
• 字符串类型：char,varchar,tinyblob,blob,mediumblob,longblob,tinytext,text,mediumtext,longtext;  

2 SQL语句

//创建数据库(指定字符集)
create database [数据库名] character set utf8;

//删除数据库
drop database [数据库名];

//显示数据库
show databases;

//切换数据库
use [数据库名];

//创建表，以创建一张学生表为例;
drop table if exists student;
create table if not exists student(
id int primary key auto_increment,
name varchar(20) not null unique, //unique约束可以插入多条值为null的记录
gender varchar(20) default 'male',
birth date,
entry_school_date timestamp,
description text
);

//显示所有表
show tables;

//显示表结构
desc [表名];

//显示创建表的sql语句
show create table [表名];

//删除表
drop table if exists [表名];

//增加列
alter table [表名] add [列名] [数据类型];

//删除列
alter table [表名] drop [列名];

//修改数据类型
alter table [表名] modify [列名] [新的数据类型];

//修改列
alter table [表名] change column [列名] [新的列名] [数据类型];

//重命名表
rename table [表名] to [新表名];


//select的使用
select name from student where age > 15 and id [not] in (10,14,18);
select name from student where age between 10 and 15;
select name from student where name like '张%';//%代表0个或多个，_代表1个
select distinct name from student;//去重
select name as '姓名' from student;//起别名

//update的使用
update student set id = 11, age = 20 where name = 'jack';

//delete的使用
delete from student where id=11;

//insert的使用
insert into student(id,name,age) values(1,'jack',10);//插入时如果一列不给出值，默认为null，如果此列为主键，则会报错。

//外键约束
create table department(
id int primary key,  //主键等于not null + unique;
name varchar(20)
);
create table employee(
id int,
name varchar(20),
did int,
constraint did_FK foreign key(did) references department(id)
);//外键必须引用另一个表的主键;

//排序
select name from student where id >5 order by id asc;//升序 降序（desc）

//分组
select name from student group by gender having id>5;

//count()/sum()/avg()
select count(id) from student;//count忽略值为null的记录
select sum(age) from student;
select avg(age) from student;

//datediff
select datediff('2020-10-19',now());//返回到具体时间的天数

//if
select if(age>49,'高龄','低龄') from userdata;
select if(age>49,age+10,age-10) from userdata;

//引用
select e.id,d.id from employee as e, department as d;

还有一些函数，具体实战才用得到，就不多说了。

第一次写博客，很菜吧。