mysql学习笔记

create database test;                                          -- 创建数据库
show databases;                                                 -- 显示所有数据库
use test;                                                               -- 定位数据库信息
create table student(
name varchar(10)
};                                                                         -- 创建学生表
create table teacher like student                        -- 创建与student相同的teacher表
show tables;                                                       -- 显示所有表
show tables like 'stude%';                                  -- 查看匹配数据表
show tables like 't_acher';                                  -- 查看匹配数据表
describe student;                                                -- 显示表结构 (field 名， type数据类型，NULL值是否允许为空，key索引，default默认值，extra额外的属性)
desc teacher;                                                     -- 显示表结构
show columns from student;                             -- 显示表结构
show create table student;                                -- 显示表的创建语句
alter table student charset gbk;                         -- 修改表属性
alter table student charset utf8;                         -- 修改表属性
rename table teacher to te_teacher;                 -- 修改表名
alter table student add id int first;                       -- 给学生表增加id字段，放到第一位
alter table student add age int after id;               -- 给学生表增加age字段，放到id后面
alter table student change age nianling int;        -- 把字段名age改为nianling的int类型
alter table student modify name varchar(20);    -- 修改字段类型，将名字长度改为20
alter table student drop nianling;                         -- 删除字段
insert into student (name,id) values('a',1);         -- 向表中的字段插入数据
insert into student (id) values(2);                        -- 向表中的部分字段插入数据
select * from student;                                         -- 查询表中所有信息
select name from student where id = 1;             -- 查询学生表中id为1的name信息
delete from student where id = 2;                       -- 删除id为2的信息
update student set id = 3 where name = 'c';      -- 更新name为c的人id改为3
set names gbk;                                                   -- 修改字符集的问题，cmd不能添加中文信息
create table my_int(
id_1 int,
id_2 int unsigned,
id_3 tinyint,
id_4 int zerofill
);                                                                         -- 创建包含整数的表
create table my_float(
f1 float,
f2 float(10,2)
);                                                                        -- 创建含float的表，指定有效数字和小数长度(精度低的原因：一部分字节存指数，另一部分字节存数字)(float的精度大约为               7 位，double的精度大约15位)        这些是浮点数
create table my_decimal(
d1 decimal,
d2 decimal(10,2)
);                                                                        -- 定点数，系统自动根据存储的数据来分配存储空间，每大概9个数就会分配4个字节来进行存储，同时小数和整数部分是分开        的，decimal(M,D),M表示总长度，最大不超过65，D代表小数部分长度，最大不超过30，如果涉及到钱的时候会用到定点数
create table my_date(
d1 date,                                                             -- yyyy-mm-dd
d2 time,                                                             -- -838:59:59-838:59:59
d3 datetime,                                                      -- yyyy-mm-dd hh:ii:ss
d4 timestamp,                                                   -- yyyy-mm-dd hh:ii:ss  格林威治时间
d5 year                                                              -- 1900-2155年    
);                                                                        -- php中有强大的时间管理函数，不太需要sql里面的时间管理
create table my_char(
c1 char(10),
d2 varchar(10)
);                                                                        -- char为定长字符，char(L),L的长度为0-255，varchar(L)为变长字符，指定长度之后，系统会根据具体情况分配合适的长度，L的长度理论为0-65535，因为varchar要记录数据长度(系统根据数据长度自动分配空间)，所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销用来保存数据所占用的空间长度，如果数据指定长度，那么使用char类型，如果不确定数据到底有多少，那么使用varchar类型，如果超过255个字符，用text，不用char和varchar
create table my_enum(
sex enum('男','女','保密')
);  
create table my_set(
hobby enum('篮球','足球','网球'，'乒乓球','高尔夫球','冰球','橄榄球','羽毛球');
);                                                                       -- 系统为set提供了多个字节进行保存，但是系统会自动计算来选择具体的存储单元，1个字节：set有8个选项，8个字节：set有64个选项，数据选项所在的数据与数据插入的顺序无关，最终都会变成对应的顺序。
create table my_shuxing(
name varchar(10) not null comment '用户名，非空',        -- 用户名非空，注释为：用户名，非空
age int default 18,                                           -- 年龄默认值为18
);
create table my_primary_key(
id int,
age int,
name varchar(10),
primary key(id,age)  
);                                                                        -- 复合主键，可以在定义后面加primary key,也可以在后面加
create table my_auto_increment(
id int primary key auto_increment,                    -- 触发自动增长，不给定具体值
name varchar(10)
);                                                                        -- 通常自动增长用于逻辑主键

alter table my_auto_increment auto_increment 10;        

                                                                          -- 修改自动增长的初始值

alter table my_auto_increment modify id int;                

                                                                          -- 删除自增长

alter table my_auto_increment modify id int auto_increment;
                                                                         -- 增加自增长
create table my_unique(
id int unique,
age int,
name varchar(10),
unique key(age)
);                                                                       -- 添加唯一键
alter table my_unique add unique key(name); -- 添加唯一键
alter table my_unique drop index age;             -- 删除唯一键


create table simple(
name varchar(1) not null
);
insert into simple values('a'),('b'),('c'),('d');        -- 快速添加表中的值
insert into simple select name from simple;     -- 蠕虫复制，无太大业务意义，但可以在短期内增加表的数据量

update simple set name = 'e' where name = 'a' limit 4;  

                                                                          -- 把前四个a更新为e

delete from my_auto_increment [where语句]; -- 删除所有表中的数据(但是不更新自增值)
truncate my_auto_increment;                          -- 删除所有表中数据并更新自增值
                                                                         -- select [select选项] 字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制
select distinct * from simple;                           -- select选项：all默认查询全部,distinct去重

select distinct name as name1,name name2 from simple;  

                                                                         -- 字段列表：  取别名:把name取名为name1和name2

select * from (select * from simple) as my_simple;     

                                                                         -- 动态查询

select class,group_concat(name),count(*),max(age),min(high),avg(age) from student group by class;
                                                                         -- group by分组统计：按班级进行分组统计：班级，所有名字，人数，最大年龄，最小身高，平均年龄
select class,count(*) as number,group_concat(name) from student  group by class having count(*) >= 2;
select class,count(*) as number,group_concat(name) from student  group by class having number >= 2;
                                                                         -- 查询班级人数大于等于2的班级，并显示姓名。having可以使用聚合函数和字段别名，where不可以。
                                                                         -- having是在group by之后，group by是在where之后，where的时候表示数据从磁盘拿到内存，where之后的所有操作都是内存操作
select * from student order by id desc;           -- 对学生表按id进行降序排列 ，默认的asc是升序，desc是降序。
select * from student limit 2,2;                         -- 对数据进行分页，此语句为查询第三条数据开始之后的两条数据。
                                                                         -- 运算符：in代替等号，in()，括号内可以放结果集，is专门用来判断null，is null，或is not null;like一般用来进行字符串的模糊查找
select * from student
union all
select * from student;                                      -- 联合查询：联合查找两个表中的值，默认不加all为distinct(去重)，联合查询的两个表单的单元数量必须一样
select * from student cross join simple;         -- 交叉连接，一般不会用到，因为产生的数据量太大（笛卡尔积）
select * from student inner join class on student.class = class.id;
                                                                        -- 内连接：按条件匹配，从一个表查到另一个表进行匹配，内连接匹配的时候必须保证匹配到才会保存，内连接一般在对数据有精确要求的地方使用，必须保证两种表中都能有数据匹配
select * from student left join class on student.class = class.id;
                                                                        -- 外连接：包括左外连接（左连接）和右外连接（右连接）,left和right，是非常常用的一种连接查询方式，左连接表示左表为主表，右表为从表，右连接类似，通常用于我们既想看到主表的所有信息，又想看到他和其他表的关系，内连接的查询结果可能比原来少，但外链接不会。主表就算匹配不到信息也会保存，而内连接不保存

select * from student left join class using(id); -- using关键字，将两个表中的同名字段进行合并，和on，where等的使用情况差不多

select class.class from class where id = (select student.id from student where name = 'a');             

                                                                        -- 查找a这个人所在的班级名称，属于标量子查询
select class.class from class where id in (select student.id from student);                           

                                                                        -- 查找所有存在人的班级名称，属于列子查询，用到in关键字
select * from student where (age,high) = (select max(student.age),max(student.high)  from student);     

                                                                        -- 查找所有学生中年龄最大，身高最高的学生信息，属于行查询  
select * ,max(high) from (select * from student order by high desc) as temp group by class;         

                                                                        -- 查找所有班级中最高的学生。 属于表子查询。
select * from class where exists(select id from student where student.class = class.id);  

                                                                        -- 求有学生在的所有班级，exists子查询，查询返回结果只有0或1，1代表成立，0代表不成立
mysqldump -hlocalhost -P3306 --uroot -proot test > C:\Users\fallrain\Desktop\test.sql    

                                                                        -- 整库备份
mysqldump -uroot -proot test student simple > C:\Users\fallrain\Desktop\test_test.sql     

                                                                        -- 多表备份
mysql -uroot -proot test1 < C:\Users\fallrain\Desktop\test.sql                                    

                                                                        -- 整库还原
source C:\Users\fallrain\Desktop\test_test.sql;                                                     

                                                                        -- 多表还原，在mysql命令行中
create user user1 identified by '123456;        -- 创建用户，密码为123456
create user user2;                                          -- 创建用户，无密码
drop user user2;                                             -- 删除用户
set password for user1 = password('654321');                                                          

                                                                       -- 修改密码                                       
grant select on test.student to user1;            -- 赋予user1读取某表的权限
revoke all privileges on test.student from user1;                                                 

                                                                       -- 收回user1对某表的所有权限
flush privileges;                                              -- 刷新权限