首页 > 代码库 > Mysql基础
Mysql基础
Mysql基础
- 连接和登录Mysql
自带mysql客户端连接：
mysql –h 127.0.0.1 –P 3306 –u root –p123456
图形化界面管理工具连接：Navicat/SQLyag/phpMyAdmin/MySQLworkbench
- Mysql的数据类型
整数：int/tinyint/smallint/mediumint/integer/bigint
小数：flout/double/decimal
日期和时间：date（日期：YYYY-MM-DD）/time（时间：HH:ii:ss）/year（年份）/datetime（日期和时间混合：YYYY-MM-DD HH:ii:ss）/timestamp（时间戳，通常是一个字符序列，唯一的标识某一刻的时间，范围：1970—2037）
字符串：char（定长）/varchar（变长）/text（变长：tinytext/text/mediumtext/longtext）
二进制：binary/varbinary/bi/blob（tineyblob/blob/medumblob/longblob）
- Mysql程序常用命令
显示所有数据库：show databases;
选定默认数据库：use dbname;
显示默认数据库中的所有表：show tables;
放弃正在输入的命令：\c
显示命令清单：\h
退出mysql：\q
查看mysql服务器状态信息：\s
- 数据库操作
创建数据库：create database dbname charset=utf8;（同时设置字符集，可选项）
显示数据库结构：show create database dbname;
删除数据库：drop database dbname;
数据库存储引擎：
指表的类型，决定了表在计算机中的存储方式（InnoDB/MyISM/）
查看mysql服务实例支持的存储引擎： show engines;
- 表操作
1) 创建表：
create table school(
school_id int(10) not null auto_increment primary key,
school_name varchar(20) default null,
phone int(10) unique
);
常见约束：
primary key/unique/not null/auto_increment/unsigned/default default_value（默认值约束）/default cur_timestamp（创建新纪录时默认保存当前时间：仅适用timestamp数据列）/on update cur_timestamp（修改记录时默认保存当前时间：仅适用于timestamp数据列）character set name （指定字符集：仅适用字符串）
查看表结构：desc 表名；show create table 表名；
2) 修改表：
修改表名：alter table school rename schoo2;
修改字段数据类型（modify修改时不需要指定新的字段名，直接跟需要修改的属性即可，change则需要指定旧字段名和新字段名及新属性）：
alter table school modify school_name char(20);
alter table school change school_name name char(20);
增加字段：alter table school add addr varchar(50) not null first;
增加外键：alter table students add constrain FK_1 foreignkey(stu_id) references school(school_id);
删除表的外键约束：alter table students drop foreign key FK_1;
修改表的排练顺序：alter table school modify addr varchar(50) after school_name;
删除字段：alter table school drop addr;
更改表的存储引擎：alter table school engine=MyISM;
3) 清空表：
truncate school; （表中现有数据全部清空，自增长列的id从建表时设置的起始id开始，不可回滚）
4) 删除表：
drop table school; （自增长的id不会从起始id开始，可回滚）
- 数据操作：
1) 插入insert ：
insert into teacher values(‘001’,’安大叔’，‘11000000000’)；（不指定具体字段名，插入全部字段值）
insert into classes(class_no,class_name,department_name) valuses(null,‘乔巴’,’性能’)；（指定具体字段名）
insert into student values
(‘besttest001’,’张三’,’15000000’,1),
(‘besttest002’,’李四’,’16000000’,1);（同时插入多条记录）
insert into new_school select * from student;（将一个表中查询出来的数据插入到另一个表中）
2) replace插入：
同insert语句基本相同，不同之处在于使用replace插入新纪录时，如果新纪录主键值或者唯一性约束与已有记录相同，则已有记录先被删除再插入新纪录（优点：将delete操作与insert操作合二为一）。
3) update更新：
update classes set department_name=’机电工程学院’ where class_no<=3;
4) delete删除：
delete from classes where class_name=’乔巴’;
- 数据查询
1) 单表查询：
select * from school;
select num,name,sex,addr from students;
select * from students where stu_id=1;
2) 多表查询（join 连接）：
select a.name,b.score from students a left join score b on a.id=b.student_id;（左连接：左表数据全部显示）
select a.name,b.score from students a right join score b on a.id=b.student_id; （右连接：右表数据全部显示）
select a.name,b.score from students a inner join score b on a.id=b.student_id;（内连接：只显示两表匹配数据）
select a.name,b.score from students a ,score b on a.id=b.student_id; （=连接：同内连接）
3) where条件查询：
in 关键字：select * from students where stu_id in (1,2);
- or 关键字：select * from students where stu_id=1 or stu_id=2;
and 关键字：select * from students where stu_id=1 and stu_id=2;
between and 关键字：select * from students where score between 60 and 100;
like关键字匹配查询：select * from students where name like ‘张_’;（%：包含零个或多个字符组成的任意字符串；_：任意一个字符）
is (not)null关键字：select * from students where addr is null;
4) distinct结果去重：select distint phone from classes;
5) limit限制查询条数：select * from school limit 5;
6) order by 查询结果排序（默认升序，desc指定降序）：
select * from students where sex=’女’ order by score desc;
select * from students where sex=’女’ order by score;
7) 聚合函数：count()/sum()/avg()/max()/min()
8) group by分组：
单独使用group by 关键字分组
select * from students group by sex;
group by 关键字与聚合函数一起使用
select count(*),sex from students group by sex;
group by 关键字与having子句一起使用（having条件表达式用户设置分组或聚合函数的过滤筛选条件）
select a.sex,count(a.id),b.class_name from students a,class b where a.id=b.student_id group by a.sex having b.class_name =’索隆’；
按多个字段进行分组
select a.sex,count(a.id),b.class_name from students a,class b where a.id=b.student_id group by a.sex,b.class_name;
9) union合并结果集，将多个select语句的查询结果组合成一个结果集（使用union会筛掉结果集中重复记录，union all直接合并结果集，效率高于union ）：
select name,sex,phone from students union select teacher_name,sex,mobile from teacher;
10) 子查询：select语句能够返回单个值或者一列值，且嵌套在另一个SQL语句中（一般用在主查询的where子句或having子句中，与比较运算符或者逻辑运算符一起构成筛选条件）：
select a.realname from bf_test_user a where id not in (select b.created_by from bf_bug_info b) and a.id>1;
- 授权语句
1) 为数据库授权：
增加一个超级用户，拥有所有的权限，只允许本地登录
grant all on *.* to ‘andashu‘@‘localhost‘ indentified by ‘123456‘with grant option;
增加一个普通用户，只有对bugfree数据库查询的修改的权限，允许
grant select, insert,update on bugfree.* to ‘tester‘@‘%‘ indentified by ‘123456‘;
增加一个超级用户，限制登录ip为192.168.1.101
grant all privileges on *.* to dba@‘192.168.1.101‘ indentified by ‘123456‘;
增加一个只有查询bugfree的bug表中的bugtitle的用户
grant select(title) on bugfree.bf_bug_info to ‘bugfree‘@‘%‘ indentified by ‘123456‘;
2) revoke取消数据库用户权限：
取消andashu用户的超级权限
revoke all on *.* from andashu@localhost;
取消dba用户查询权限
revoke select on *.* from dba@localhost;
3) 为数据库设置密码：
使用set password
set password for tester= password("123456")
使用update语句
update user set password=password(‘123456‘) where user=‘tester‘;
4) 删除用户：
delet from user where user=tester;
Mysql基础