mysql的程序:
1.client:客户端程序,连接到server的程序
2.SQLyog 这个工具好用,多命令行直接执行。
3.发邮件:默认端口net25 数据库的默认端口3306,这个端口根据服务器的端口来做统一
4.server端:linux上。
show databases; 显示mysql中所有数据库的名称。
show tables; 显示当前数据库中所有表的名称。
什么是事务:
事务是数据库操作的最小工作单元,是作为单个逻辑工作单元执行的一系列操作;这些操作作为一个整体一起向系统提交,要么都执行,要么都不执行,要么同时成功,要么同时失败。
增删该查:查(测试用的多)
索引的类型
索引的优化
慢sql的优化
不同存储引擎区别
锁
写-h就是服务器
-P(大写P) 是指定端口
命令:mysql -uroot -h127.0.0.1 -p
mysql -uroot -h127.0.0.1 -P3306 -proot
第一个所有的库 第2个 所有的表
%表示限定IP identified 限定密码 ******
密码忘了:重置密码
flush privileges 一定要执行这句
重置密码:mysqld --initialize --user=root --console
授权命令
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'gloryroad' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'testman'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
show databases;
use test; #使用某一个数据库
show tables; #可以看一下它有哪些表
create database huanghuang; #创建表
drop database huanghuang; # 删除
select database(); #查看当前使用的那个库
mysql> create table huanghuang(id int,name varchar(20),age int,birthday datetime
);
Query OK, 0 rows affected (0.25 sec) #创建一个表
show tables;
# 看我建立的表在不在
#插入几条语句:
mysql> insert into huanghuang values(1,"huang",28,"1980-10-10 10:10:10");
Query OK, 1 row affected (0.02 sec)
mysql> insert into huanghuang values(2,"li",18,"1981-11-10 10:10:10");
Query OK, 1 row affected (0.01 sec)
mysql> insert into huanghuang values(3,"zhang",18,"1981-11-10 10:10:10");
Query OK, 1 row affected (0.01 sec)
mysql> insert into huanghuang values(4,"wang",18,"1981-11-10 10:10:10");
Query OK, 1 row affected (0.02 sec)
mysql> insert into huanghuang values(4,"zhao",10,"1981-11-10 10:10:10");
Query OK, 1 row affected (0.02 sec)
#查看多少条数据
select * from huanghuang;
#看表里有啥
show create table huanghuang;
#查看表结构
desc huanghuang;
#用values 可以插入多行
mysql> insert into huanghuang values(5,"zhao",10,"1981-11-10 10:10:10"),(6,"qian
",8,"2010-1-1 1:1:1");
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
#指定字段名:各自对应
mysql> insert into huanghuang(id,name,age,birthday) values(6,"zhao",10,"1981-11-
10 10:10:10"),(7,"qian",8,"2010-1-1 1:1:1");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
#查询指定列
select id,name from huanghuang;
#查询 改成中文,这叫别名
select id 序号,name 名字 from huanghuang;
#简单查询
select * from huanghuang where id=1;
select * from huanghuang where id!=1;
select * from huanghuang where id>=1;
select * from huanghuang where id<=1;
select * from huanghuang where id between 2 and 5;
select * from huanghuang where id in(1,2,3);
select * from huanghuang where id in (select id from huanghuang where id>=5);
与id 表示取交集
select id from huanghuang where id>=5 and id <=6;
与name
select id from huanghuang where id>=5 and name="qian";
#或
select id from huanghuang where id>=5 or name="qian";
select id from huanghuang where name="zhang" or name="qian";
#排序:
select * from huanghuang order by id desc;
不写是默认:
select * from huanghuang order by id;
select * from huanghuang order by id asc;
select * from huanghuang order by id,age asc;
#这个要特别说明:只能是先降序,比如id降序,然后的检索结果出来以后,相同的id再做降序(拿id来先检索结果,再拿这个id再做2次排序)
select * from huanghuang order by id desc, age desc;
select * from huanghuang order by id desc, age desc limit 1;
#第几个结果开始取,后面1是取几条,从0开始往后数5条开始
select * from huanghuang order by id desc, age desc limit 5,1;
#获取不同的名字 distinct(又区别的,不一样的)
select distinct name 名字 from huanghuang;
#统计名字有多少个
select count(name) 名字 from huanghuang;
小提示:
‘’’ 数据库就是用来存东西的,其他别让它干,乱’’’’
分组:和多表连接 在面试中考的最多。
分组一定要有聚合函数 如:count(*)
select count(*) from huanghuang group by age having age =1
select count(*),age from huanghuang group by age;
#平均年龄大于10
mysql> select avg(age) 平均年龄,id from huanghuang group by id having avg(age) >
10;
里面 avg sum 这叫聚合函数
数据库新建的数据
create table student(
id int not null auto_increment,
name varchar(20) not null,
sex char(1),
submission_data date,
primary key(id)
)engine = innodb character set utf8;
create table grade(
id int not null auto_increment,
stuid int not null,
class varchar(20) not null,
grade int(3),
primary key(id)
)engine = innodb character set utf8;
insert into student (name,sex,submission_data) values("张三","男","2010-10-10");
insert into student (name,sex,submission_data) values("李四","男","2010-10-10");
insert into student (name,sex,submission_data) values("王五","男","2010-10-10");
insert into student (name,sex,submission_data) values("赵六","男","2010-10-10");
insert into grade (name,sex,submission_data) values("丁一","男","2010-10-10");
insert into grade (stuid,class,grade) values(1,"计算机","100");
insert into grade (stuid,class,grade) values(1,"guitar","90");
insert into grade (stuid,class,grade) values(1,"美术","80");
insert into grade (stuid,class,grade) values(2,"计算机","100");
insert into grade (stuid,class,grade) values(2,"guitar","90");
insert into grade (stuid,class,grade) values(2,"美术","80");
insert into grade (stuid,class,grade) values(3,"guitar","90");
#多表查询,都是通过id来互相关联的
#等于 叫内连接
select a.name,sum(grade) from student a inner join grade b on a.id=b.stuid group by b.stuid;
select a.name,sum(grade) from student a left join grade b on a.id=b.stuid group by b.stuid;
右表没有的数据 就会显示NULL 左连接以左表为主;
on 内链接 a表b表都得有数据
select a.name,sum(grade) from student a left join grade b on a.id=b.stuid group by a.id;
右连接 右面的数据都得有 左边的数据没有就显示NULL
select a.name,sum(grade) from student a right join grade b on a.id = b.stuid group by b.stuid;
理论基础:
内连接: (#效率忒低,考个select太low)
a表的id在b表的stuid出现,则数据会显示在结果中
左连接:
a表的id在b表的stuid出现的话显示相关b表数据,若不出现在b表,则相关行显示NULL.
右连接:
b表的stuid在a表的id出现的话显示相关a表数据,若不出现在a表,则相关a表的数据列显示NULL
小结理解:
右表没有的数据 就会显示NULL 左连接以左表为主;
on 内链接 a表b表都得有数据
右连接 右面的数据都得有 左边的数据没有就显示NULL
union all 不排重 union 排重 联合
select id from student union all select stuid from grade;
select id from student union select stuid from grade;
外键
表B的某一个列 关联 表A的某一个列,且B的这列数据的所有值,必须在表a的关联列中出现
创建的数据:
create table a (id int ,
primary key(id)
);
create table b (id int,
primary key(id),
CONSTRAINT FK_ID FOREIGN KEY(id) REFERENCES a(id)
);
A表的id是B表id的外键
mysql> delete from b where id =2;
Query OK, 1 row affected (0.02 sec)
mysql> insert into b values(2);
Query OK, 1 row affected (0.02 sec)
#explain 怎么查看你这个sql有没有问题 看row row 表示执行这个sql扫描了多少行 如(执行这条sql扫描了10万行)
truncate table b;
清楚所有的表数据,索引什么的都干掉 表还在 数据没有了
跟drop的去表
drop table b; 直接全干掉
delete from b;
b的所有数据都没有了,他的索引在。
truncate 和 drop 都不会记录日志,没有回滚。
模糊查询“
mysql> select * from student where name like '张%';
+----+------+------+-----------------+
| id | name | sex | submission_date |
+----+------+------+-----------------+
| 1 | 张三 | 男 | 2010-10-10 |
+----+------+------+-----------------+
1 row in set (0.00 sec)
mysql> select * from student where name like '%五';
+----+------+------+-----------------+
| id | name | sex | submission_date |
+----+------+------+-----------------+
| 3 | 王五 | 男 | 2010-10-10 |
+----+------+------+-----------------+
1 row in set (0.00 sec)
mysql> select * from student where name like '%王五%';
+----+------+------+-----------------+
| id | name | sex | submission_date |
+----+------+------+-----------------+
| 3 | 王五 | 男 | 2010-10-10 |
+----+------+------+-----------------+
1 row in set (0.00 sec)