mysql数据库基础操作

1.操作数据库

#创建库
1)创建数据库，判断是否存在，并设置字符集
    create database if not exists db1 character set utf8mb4;


#查询库
1)查看所有库
    show databases;
2)查看库的字符集
    show create database db1;


#修改库
1）修改数据库的字符集
    alter database db1 character set utf8mb4;


#删除库
1)删除数据库
    drop database db1;
2)判断数据库存在就删除；
    drop database if exists db1;


#使用数据库
1）查询当前正在使用数据库的名称
    select database();
2）进入指定数据库
    use db1;

2.对表结构操作

#创建表
1）判断表存在就不执行，不存在就创建      
    create table if not exists tb1(
    id int unsigned not null AUTO_INCREMENT PRIMARY KEY,
    username varchar(30) not null,
    password char(32) not null,
    email varchar(100) not null,
    pic varchar(50) default './public/img/pic.jpg'
    )engine=innodb default charset=utf8;
    实例解析：
如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

2）复制表
    craete table tb2 like tb1


#查询表
1）查看库里所有的表
    show tables;
2）查看表结构
    desc tb1;
    show columns from dycx_details;
3) 查询注释
    select COLUMN_NAME, COLUMN_COMMENT from INFORMATION_SCHEMA.Columns where table_name = 'table';
#修改表
1）修改表名
    alter table tb1 rename to tb2;
2）修改表的字符集
    alter table tb1 character set utf8mb4;
3）添加一列
    alter table tb1 add gender varchar(10);
4）修改列名和类型
    alter table tb1 change gender sex varchar(20);
    alter table tb1 modify sex char(10);  #只修改类型
5）删除列
    alter table tb1 drop sex;

#删除
    1）删除表
        drop table tb1;
        drop table if exists tb1;  #判断是否存在然后删除；

3.对表里的数据操作

#查询表
    1）查询所有数据
        select * from tb1;
    2）对某一列进行去重查询
        select distinct name from tb1;
    3)多字段查询，数值相加生成新字段，起别名,判断字段为null用0替换
        select line1,line2,ifnull(line3, 0),line2+line3 as 总分 from tb1;
    
    4）条件查询
        select * from tb1 where id (=，!=, >, <, <>) 20; #注意<>也是不等于
        #查找年龄20到30之间的
        select * from tb1 where age between 20 and 30; 
        #简写，查找年龄20到30之间的等同于上面
        select * from tb1 where age >=20 and age <=30;  
        #查找年龄为10，20，30的，or也可写为||
        select * from tb1 where age=10 or age=20 or age=30; 
        #简写，查找年龄为10，20，30的等同于上面
        select * from tb1 where age in (10,20,30);
        #查询年龄为null的
        select * from tb1 where age is null;
        #查询年纪不为null
        select * from tb1 where age is not null;
   5）模糊查询
        #查询姓"马"的
        select * from tb1 where name like '马%'
        #查询名字包含"马"的
        select * from stu where name like "%马%";
        #查询名字是三个字符的
        select * from tb1 where name like "___" 
    6)排序查询
        #升序asc
        select * from student3 order by math asc;
                select * from student3 order by math;  #默认升序
                #降序desc
         select * from student3 order by math desc;
    7) 聚合函数  （注意聚合函数会排除为null的数据，解决方法计算前先用ifnull()替换）
            1. count()   计算个数
                select count(name) from tb1;  #查询一共有多少个人
            2.max() 取出最大值
                select max(age) from tb1;  #查询年龄最大的
            3.min() 取出最小值  
                select min(age) from tb1;   #查询年龄最小的
            4.sum()  求和
                select sum(age) from tb1;   #求年龄总和
            5.avg()  计算平均值
                select avg(age) from tb1；#求平均年龄
    8）分组查询  group by
            #按性别分组每组各有多少人
            select sex, count(id) from tb1 group by sex; 
            #按性别分组，每组多少人，并年龄小于20的不参与分组
            select sex,count(id) from tb1 where age>20 group by sex;
            #having ,按性别分组，每组多少人，并年龄小于20的不参与分组，并不要分组人数少于2的
            select sex,count(id) as rsu from tb1 where age >20 group by sex having rsu>2;
            
    9）分页
            #从0开始显示10条数据
            select * from student3 limit 0,10;


#增，删，改
    1)添加数据
        insert into tb1(列名1，列名2，...) values(值1，值2，...);
        insert into tb1 values(值1，值2，...)
    2)删除数据
        delete from tb1 where id=1;
        truncate table tb1;  #先删除表，然后创建一张一样的表。（如果删除所有的数据推荐使用）
    3）修改数据
        update tb1 set name="张三" , age=17 where id=1;


#按时间查询
Select * From user Where DATE_FORMAT(字段,'%m-%d') >= '06-03'

4.约束

1）非空约束 not null
2) 唯一约束 unique  #注意唯一约束可以有多个null值
    #删除除唯一约束
    alter table tb1 drop index name;  注意删除唯一约束只能用这个方法，不能用修改类型来改变唯一约束。

3）主键约束：primary key
        注意：
            1.含义：非空且唯一；
            2.一张表只有一个主键；
            3.主键就是表中记录的唯一标识；
    #删除除主键
    alter table tb1 drop primary key; #只能用这种方法删除主键


4）自增：auto_increment
5) 外键约束：foreign key
        #创建表时添加外键
        create table tb1(constraint 外键名称 foreign key (外键列名称) references 主表列名称)；
        #删除外键
        alter table tb1 drop foreign key 外键名；
        #创建表以后添加外键
        alter table tb1 add constraint 外键名称 foreign key (外键列名称) references 主表列名称
6）级联操作
    1.级联更新：on update cascade
    2.级联删除：on delete cascade

5.多表联查

1）内连接：inner join
    select * from tb1 t1,tb2 t2 where t1.id=t2.id2;
    select * from tb1 t1 join tb2 t2 where t1.id=t2.id2;
    select * from tb1 t1 inner join tb2 t2 where t1.id=t2.id2;


2）左连接：left join
    select * from tb1 t1 left join tb2 t2 on t1.id=t2.id;  #注意这里的判断条件用on
3）右联查：right
     select * from tb1 t1 right join tb2 t2 on t1.id=t2.id2;

6.事物

开启事物 start transaction; 
提交事务 commit;
 回滚事务 rollback;