MySQL基本操作语句
1、操作数据库
1.1 创建数据库
CREATE DATABASE [IF NOT EXISTS] XXX(数据库名称)
create database XXX(数据库名称) character set utf8 collate utf8_general_ci;
1.2 删除数据库
DROP DATABASE IF EXISTS XXX(数据库名称)
1.3 使用数据库
如果数据库的表名或者字段名是一个特殊字符，需要带上``
use `user`
1.4 查询数据库
show databases; //查询所有的数据库；
show tables; //查询数据库中的表；
describe templet; //查询表的内容
2、操作数据表
2.1 创建数据库表
格式：
create table `表名`(
`字段名` 列类型 [属性][索引][注释],
·········
`字段名` 列类型 [][][]
)engine = innodb default charset=utf=8
show create database xxxx ---查看创建数据库的语句
show create table XXX ---查看创建表的语句
desc XXX ---查看表的结构
2.2 数据表的类型：
---- 数据库引擎：
INNODB 默认使用
MYISAM 早期使用
2.3 设置数据的字符集编码：
charset=utf=8 ---设置完可以显示中文
默认不支持中文，可以在my.ini中配置默认编码：character-set-server=utf8
2.4 修改删除表：
修改：alter table
-- 修改表名： alter table 旧表名 rename as 新表名
alter table student rename as teacher
-- 增加表的字段： alter table 表名 add 字段名 列属性
alter table teacher add age int(10)
-- 修改表的字段:(重命名，修改约束)
--alter table 表名 modify 字段名 列属性[]
alter table teacher modify age varchar(10) --修改约束，不修改名
--alter table 表名 change 旧名字 新名字 列属性[]
alter table teacher change age age1 int(1) --字段重命名，修改约束
--删除表的字段
-- alter table 表名 drop 字段名
alter table teacher drop age1
删除：drop table
-- 删除表(如果存在就删除表)
drop table if exists teacher
3、MySQL数据管理
3.1 外键：
--创建外键：建表时顺便建外键
create table xxx (
``````,
``````,
``````,
primary key(xxx),
key FK_xxx(主表的外键),
constraint FK_xxx foreign key (主表的外键) references 子表名 (子表中的主键)
)
--创建外键：建表后再键外键
create table xxx (
`````,
`````.
)
alter table xxx
add constraint FK_xxx foreign key (外键名) references 子表名 (子表主键);
--删除外键：
--注意：删除具有外键关系的表，需要先删除子表，再删除主表
--删除外键；alter table xxx drop foreign key FK_xxx;
--删除索引：alter table xxx drop index FK_xxx;
3.2 DML语言(数据库操作语言)：
3.2.1 insert 添加数据
insert into 表名(表数据.....) values(值....)
--插入的值要与表里面的值一一对应
--插入多个字段：
insert into 表名(表数据.....) values(值1...),(值2...),(值3...)
3.2.2 update 更新数据
update 表名 set column_name=value[column_name1,......][where condition]
--column_name:为要更改的数据列；value为修改后的数据；condition为筛选条件
3.2.3 delete &&truncate 删除数据
--删除表数据，但数据里面的结构不会删除，例如：表自增不会重置为1；
delete from 表名 [where dondition]
--truncate:用于完全清空数据，但表结构，索引，约束不变；表自增会重载为1；
--建议使用：
truncate [table] table_name;
3.3 DQL语言(数据库查询语言)：
练习数据：
create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
drop table if exists `student`;
create table `student`(
`studentno` int(4) not null comment 学号,
`loginpwd` varchar(20) default null,
`studentname` varchar(20) default null comment 学生姓名,
`sex` tinyint(1) default null comment 性别，0或1,
`gradeid` int(11) default null comment 年级编号,
`phone` varchar(50) not null comment 联系电话，允许为空,
`address` varchar(255) not null comment 地址，允许为空,
`borndate` datetime default null comment 出生时间,
`email` varchar (50) not null comment 邮箱账号允许为空,
`identitycard` varchar(18) default null comment 身份证号,
primary key (`studentno`),
unique key `identitycard`(`identitycard`),
key `email` (`email`)
)engine=myisam default charset=utf8;
-- 创建年级表
drop table if exists `grade`;
create table `grade`(
`gradeid` int(11) not null auto_increment comment 年级编号,
`gradename` varchar(50) not null comment 年级名称,
primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;
-- 创建科目表
drop table if exists `subject`;
create table `subject`(
`subjectno`int(11) not null auto_increment comment 课程编号,
`subjectname` varchar(50) default null comment 课程名称,
`classhour` int(4) default null comment 学时,
`gradeid` int(4) default null comment 年级编号,
primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;
-- 创建成绩表
drop table if exists `result`;
create table `result`(
`studentno` int(4) not null comment 学号,
`subjectno` int(4) not null comment 课程编号,
`examdate` datetime not null comment 考试日期,
`studentresult` int (4) not null comment 考试成绩,
key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;
-- 插入科目数据
insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,"高等数学-1",110,1),
(2,"高等数学-2",110,2),
(3,"高等数学-3",100,3),
(4,"高等数学-4",130,4),
(5,"C语言-1",110,1),
(6,"C语言-2",110,2),
(7,"C语言-3",100,3),
(8,"C语言-4",130,4),
(9,"Java程序设计-1",110,1),
(10,"Java程序设计-2",110,2),
(11,"Java程序设计-3",100,3),
(12,"Java程序设计-4",130,4),
(13,"数据库结构-1",110,1),
(14,"数据库结构-2",110,2),
(15,"数据库结构-3",100,3),
(16,"数据库结构-4",130,4),
(17,"C#基础",130,1);
-- 插入学生数据 其余自行添加 这里只添加了2行
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,"123456","张伟",0,2,"13800001234","北京朝阳","1980-1-1","text123@qq.com","123456198001011234"),
(1001,"123456","赵强",1,3,"13800002222","广东深圳","1990-1-1","text111@qq.com","123456199001011233");
-- 插入成绩数据 这里仅插入了一组，其余自行添加
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,"2013-11-11 16:00:00",85),
(1000,2,"2013-11-12 16:00:00",70),
(1000,3,"2013-11-11 09:00:00",68),
(1000,4,"2013-11-13 16:00:00",98),
(1000,5,"2013-11-14 16:00:00",58);
-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) values(1,"大一"),(2,"大二"),(3,"大三"),(4,"大四"),(5,"预科班");
--自连接数据库：
CREATE TABLE `category`(
`categoryid` INT(3) NOT NULL COMMENT "id",
`pid` INT(3) NOT NULL COMMENT "父id 没有父则为1",
`categoryname` VARCHAR(10) NOT NULL COMMENT "种类名字",
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) VALUES (2, 1, "信息技术"),(3, 1, "软件开发"),(5, 1, "美术设计"),(4, 3, "数据库"),(8, 2, "办公信息"),(6, 3, "web开发"),(7, 5, "ps技术");
3.3.1 SELECT语法
--简单的select查询：
select 想查询的表数据 from 表名;
--查询student表里面的所有数据
select * from student；
--查询student里面指定的数据：
select studentno from student;
select....where...:指定结果需满足where后面的条件；
select....group by...:指定结果按照哪个字段来分组；
select....group by...having:固定搭配，分组后的数据必须满足having后面的条件；
select....order by...:指定查询数据记录按一个或多个条件进行排序；
select....limit....:指定查询的记录从哪条至哪条；
as 子句作为别名与concat()函数拼接
--查询数据时，可以给查询到的数据as个别名，当然as也可以省略：
select studentno as 学号 from student;
select studentno 学号 from student;
--给表as别名：
select studentno 学号 from student as s;
--使用concat()函数拼接字符串：
select concat("姓名",studentname) as 新名字 from student;
distinct去除重复项
select distinct studentno from result; --去除里面studentno的重复项
使用表达式查询
--查询自增步长：
select @@auto_increment_increment;
--查询版本：
select version();
--也可以直接使用表达式：
select 100*10;
模糊查询
| 操作符名称 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 操作符为null，结果为真 |
| is not null | a is not null | 操作符不为null，结果为真 |
| between | a between b and c | a范围在b与c之间 |
| like | a like b (%, _) | a匹配b |
| in | a in (a1,a2,a3...) | a等于a1,a2,a3其中一个 |
--查询95-100之间的数据：
select studentresult from result where studentresult between 95 and 100;
--查询除了1000号同学外的其他信息：
select * from student where not studentno=1000;
--查询姓刘的人：
select * from student where studentname like "刘%";
--查询姓刘且名字只有一个字的人：
select * from student where studentname like "刘_";
--查询学号为1000，1001，1002的人：
select * from student where studentno in (1000,1001,1002);
--查询成绩为空的人：
select * from reuslt where studentresult is null;
--查询所有人的有效成绩：
select * from result where studentresult is not null;
--注意：""与null是不同的。
3.3.2 连接查询
join连接查询
| 操作符描名称 | 描述 |
|---|---|
| inner join | 如果表中有至少一个匹配，则返回行 |
| left join | 即使右表中没有匹配，也会从左表中返回所有的行 |
| right join | 即使左表中没有匹配，也会从右表中返回所有的行 |
--内连接：查询两个表中的交集
select s.studentno,studentname,subjectno,studentresult from student s inner join result r
on r.studentno=s.studentno;
--左连接：以左表为基准，右表来一一匹配，匹配不上的，返回左表的记录，右表以null填充
select s.studentno,studentname,subjectno,studentresult from student s
left join result r
on r.studentno=s.studentno;
--右连接：以右表为基准，左表来一一匹配，匹配不上的，返回右表的记录，左表以null填充
select s.studentno,studentname,subjectno,studentresult from student s
right join result r
on r.studentno=s.studentno;
--等值连接：
select s.studentno,studentname,subjectno,studentresult from student s,result r
where r.studentno=s.studentno;
--自连接：数据表与自身进行连接
--把一张表看成两张一模一样的表，进行查询：
3.3.3 分页和排序
limit分页
--limit 起始页，页面的大小
--显示第N页的公式：(n-1)*pageSize,pageSize
--n：当前页； (n-1)*pageSize：起始页； pageSize：页面大小 总页数：数据总数 + 1/页面大小
--显示从数据的第一个开始，显示五个数据为一页：
select ...... from ...order by 排序的数据 desc/asc limit 0,5;
order by 排序
升序：asc； 降序：desc；
--通过成绩进行排序：
select ...... from ...order by 排序的数据(成绩) desc/asc;
3.3.4 子查询
--select ... from ... where 相关的值 = (select嵌套);
--查询数据库里面所有C语言-1 的学生信息：
select studentno,subjectno,studentresult from result where subjectno=(select subjectno from subject where subjectname="C语言-1") order by studentresult desc;
--select ... from ... where 相关的值 in (select .... and 相关的值 = (select....));
select studentno,studentname from student where studentno in (
select studentno from result where studentresult >80 and subjectno = (
select subjectno from subject where subjectname="C语言-1"
)
);
3.3.5 常用函数
日期和时间函数
date('yyyy-mm-dd hh:ii:ss'); --获取日期部分
time('yyyy-mm-dd hh:ii:ss'); --获取时间部分
date_format('yyyy-mm-dd hh:ii:ss','%d %y %a %d %m %b %j'); --格式化时间
unix_timestamp(); --获取unix时间戳
from_unixtime(); --从时间戳获得时间
md5(); --md5加密
select current_date(); --获取当前日期
select curdate(); --获取当前日期
select now(); --获取当前日期和时间
select localtime(); --获取本地日期和时间
select sysdate(); --获取当前系统日期和时间
--获取年月日，时分秒：
select year(now());
select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());
--系统信息函数：
select version(); --获取版本信息
select user(); --获取用户信息
聚合函数
| 函数名称 | 描述 |
|---|---|
| count() | 返回语句的记录总数 |
| sum() | 返回一列的总和 |
| avg() | 返回一列的平均值 |
| max() | 返回最大值 |
| min() | 返回最小值 |
--count()
select count(*) from student;
select count(1) from student;
select count(studentno) from student;
/*
count(字段):会忽略字段为null的值，不统计null的值。
count(*):会统计包含null的值。
count(1):会统计包含null的值。
*/
select subjectname,avg(studentresult),max(studentresult),min(studentresult) from result
join subject on result.subjectno=subject.subjectno group by subjectname having avg(studentresult)>70;
4、事务
4.1 事务定义
将一组sql语句放在同一批次内去执行，如果有一个sql语句出错，该批次内所有sql语句都会被取消执行，事务处理只支持innoDB和BDB数据表类型。
事务的原则(ACID)
原子性(Atomic)
- 整个事务中的所有操作,要么全部完成,要不全部不完成;事务在执行过程中发生错误,会回滚到事务开始前的状态,就像这个事务从来没有执行过一样。
一致性(Consist)
- 一个事务可以封装状态改变(除非它是一个只读的);事务必须始终保持系统处于一致的状态,不管任何给定的时间并发事务有多少;也就是说:如果事务时并发多个,系统也必须如同串行事务一样操作,其主要特征是保护性和不变性。事务前后的数据要保持一致。
隔离性(IsoIated)
- 隔离状态执行事务,使他们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事物在系统中认为只有该事务在使用系统。保证同一时间仅有一个请求用于同一数据。
持久性(Durable)
- 在事物完成后，该事务数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
隔离所导致的问题
脏读:
指一个事务读取了另一个事务未提交的数据。
不可重复性：
在一个事务内读取表中的某一行数据，多次读取结果不同。
幻读：
指在一个事务内读取到了别的事物插入的数据，导致前后读取不一致。
4.2 事物的执行
--mysql 是默认开启事务自动提交的
set autocommit = 0 --关闭
set autocommit = 1 --开启(默认)
--手动处理事务：
set autocommit = 0 --关闭事务提交
--事务开启：
start transaction --标记一个事务的开始，从这个后sql都在同一事务内
--提交：(成功)
commit
--回滚：(失败)
rollback
--事务结束：
set autocommit = 1 --开启自动提交
模拟转账
create database testone character set utf8 collate utf8_general_ci;
use testone;
create table test01(
id int(4) not null auto_increment,
name varchar(20) not null,
money decimal not null,
primary key(id)
)engine=innodb default charset=utf8;
insert into test01("name","money") values("A","1000"),("B","500");
--关闭事务提交：
set autocommit = 0;
--事务开始：
start transaction
update test01 set money = money - 200 where name="A";
update test01 set money = money + 200 where name="B";
--提交(持久性，无法恢复)/回滚:
commit;
rollback;
--事务结束：
set autocommit = 1;
5、索引
5.1 索引的简介
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
索引的作用：
- 提高查询速度；
- 确保数据的唯一性；
- 可以加速表和表之间的连接，实现表与表之间的参照完整性；
- 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间；
- 全文检索字段进行搜索优化；
5.2 索引分类
- 主键索引(Primary key)
- 唯一索引(Unique)
- 常规索引(Index)
- 全文索引(FullText)
主键索引(Primary key)
主键：某一个属性能唯一标识一条记录；
特点：1）确保数据记录的唯一性；
2）确保特定数据记录在数据库中的位置；
唯一索引(Unique)
作用：避免一个表中某数据列中的值重复与主键索引的区别；
- 主键索引只能有一个
- 唯一索引可能有多个
常规索引(Index)
作用：快速定位特定的数据；
index和key关键字都可以设置常规索引；- 应加在查询条件的字段；
全文索引(FullText)
作用：快速定位特定的数据；
- 只能用于char，varchar，text数据列类型；
- 适合大型数据集；
5.3 测试索引
--创建索引方法一：创建表时
create table 表名(
....
....
声明索引
[unique][fulltext][index/key] [索引名](字段名)
)
--创建索引方法二：在已创建的表上创建索引
create [unique][fulltext] 索引名 on 表名(表名)
--创建索引方法三：在已存在的表上创建索引
alter table 表名 add [unique][fulltext] 索引名 (字段名)
--显示索引信息：
show index from 表名;
--删除索引：
drop index 索引名 on 表名字;
--删除主键索引：
alter table 表名 drop primary key;
--explain:分析sql语句的执行性能；
explain select * from student;
- 索引不是越多越好；
- 不要对经常变动的数据添加索引；
- 小数据量的数据不要加索引；
- 索引一般加在查找条件的字段；
索引的数据结构
索引的数据类型分为两类：
hash类型：查询单条快s，范围查询慢；
btree类型：B+树，层数越多，数据量指数级增长；
6、权限管理和备份
6.1 用户管理
--创建用户： create user 用户名 identified by 密码
create user VX identified by "123456"
--修改密码（当前用户密码）：
set password = password("123456")
--修改密码（指定用户密码）：set password for 用户名 = password('密码')
set password for VX = password("123456")
--重命名：rename user 旧名字 to 新名字
rename user VX to VXXL
--用户授权：grant 权限列表 on 表名 to 用户名
-- all privileges：表示所有权限
-- *.*:表示所有库的所有表
-- 库名.表名 ： 表示某库下的某表
grant all privileges on student to VXXL
--查询权限：show grants for 用户名
show grants for VXXL
--查看当前用户的权限：
show grants;
--撤销权限：revoke 权限列表 on 表名 form 用户名
revoke all privileges on student from VXXL
6.2 MySQL备份
--导出：
mysqldump -u用户名 -p密码 库名 表名 > 文件名(d:/a.sql)
--导出多张表：
mysqldump -u用户名 -p密码 库名 表名1 表名2 表名3 > 文件名
--导出所有表：
mysqldump -u用户名 -p密码 库名 > 文件名
--导出一个库：
mysqldump -u用户名 -p密码 -B 库名 > 文件名
--导入：
--1、在登陆mysql的情况下：source 备份文件名
source D:/a.sql
--2、在不登陆情况下：
mysql -u用户名 -p密码 库名 < 备份文件
浙公网安备 33010602011771号