MySql的简单使用，所有的代码基于MAC

基础

安装mysql

brew install mysql

 查看mysql安装路径

ps -ef|grep mysql

 查看mysql的配置文件

mysql --verbose --help | grep my.cnf

 启动

mysql.server start

 登录mysql,可以在my.cnf的配置文件中加入skip-grant-table

mysql -u root -p 

 当前时间

select now();

 显示版本

select version();

 查询数据库

show databases;

 创建数据库

create database test_db;
create database test_db charset=utf8;//设置数据库的编码格式
create database test_db character set utf8;

 显示创建的语句

show create database test_db;

 删除数据库

drop database test_db;

切换数据库

use test_db;

当前选择的数据库

select database();

查看当前数据库的表

show tables;

创建一个数据表 primary key主键 not null不能为空 auto_increment自动增长,primarykey主键。 unique唯一    not null非空  foreign key外健   AUTO_INCREMENT自增

Create table students(id int unsigned not null auto_increment primary key,name varchar(20),age tinyint unsigned,hight decimal(5,2),gender enum('男','女','未知') default '未知');

删除表

drop table test;

给表添加字段

alter table students add birthday datetime;

修改表的字段类型

alter table students modify birthday date;

修改表的字段名字和类型

alter table students change birthday birth date default "1990-01-01";

删除一个字段

alter table students drop birth;

查看数据表结构

desc students;

修改表的名字

alter table students rename to stu

插入数据

insert into students values (0,'刘',18,1.72,'男');
insert into students values (0,'刘',18,1.72,'男'),(0,'刘',18,1.72,'男');
insert into students (name,hight) values ("测试",1.77);
insert into students (name,hight) values ("测试",1.77),("测试",1.77);

查询数据

select * from students;
select name,age from students;
select name as 姓名,age as 年龄 from students;
select s.name,s.age from students as s;

 修改数据

update students set age = 30,name = "老王" where id = 1;

update students set hight = hight +1 where id = 30;

删除数据

delete from students where id = 6

修改自增长数据的起始点,默认插入第一条是从1开始的

alter table students auto_increment=2000;

 删除主键

alter table students drop primary key;

 修改主键

alter table students add primary key(id);

删除表所有的数据,但是表结构还在

truncate  students 

给表添加外健,但是好多公司一般不用

alter table 添加约束的表名 add constraint 约束的名字 foreign key(字段名)references 关联的表名(关联的字段名);
给employee表的dep_id添加关联department表的id
alter table employee add constraint emp_depid_fk foreign key(dep_id)references department(id);

 删除外健

alter table 表明 drop foreign key 约束的别名
alter table employee drop foreign key emp_depid_fk

进阶
查询去重

select distinct gender from students;

 where使用

select * from students where gender <> "男";
select * from students where id > 3;
select * from students where id = 3;
select * from students where age>18 and age<40;
select * from students where age>30 or age < 20;
select * from students where not (age > 30);
select * from students where (not age > 30) and name="刘";
select * from students where name like "测%";//查询以测开头的
select * from students where name like "__";//查询名字有两个字符的
select * from students where name rlike "^测.*";//正则
select * from students where age in (30,18);//年龄30，或者18的
select * from students where age not in (30,18);//
select * from students where age between 18 and 30;//年龄18<=age<=30
select * from students where age not between 18 and 30;//年龄18<=age<=30
select * from students where age is NULL;
select * from students where age is not NULL;

 排序

select * from students order by age;
select * from students where age is not NULL order by age;//asc 升序
select * from students where age is not NULL order by age desc;//降序

分组、统计

select count(*) from students where age = 18;//统计18岁的人数
select count(*) as 18岁人数 from students where age = 18
select max(age) from students ;//最大年龄
select min(age) from students ;//最小年龄
select sum(age) from students;//所有年龄总和
select avg(age) from students;//平均年龄
select sum(age)/count(*) from students;//平均年龄
select round( sum(age)/count(*),2) from students;//2代表保留两位小数
select gender from students group by gender;//分组
select gender, count(*) from students group by gender;//分组统计
select gender,group_concat(name) from students group by gender;//根据性别统计姓名
select gender,group_concat(name,age) from students where gender="女";
select gender,group_concat(name,"_",age) from students where gender="女";
select gender,group_concat(name) from students group by gender having count(*)>3;//分组的数大于3条记录
select gender,group_concat(name) from students group by gender having avg(age)>20;//分组的平均年龄大于20

分页

select * from students limit 2;//限制查询出来的个数，最多两条
select * from students limit 0,5;//从数据里面第0条开始查5条
select * from students limit 5,5;//从第五条开始查5条
select * from students limit 5,5;//从第五条开始查5条
select name from students where gender="未知" limit 2;

联合查询

if not exists的使用

Create table cls(id int unsigned not null auto_increment primary key,name varchar(20));
alter table students add cls_id int default 0;

Create table if not exists  cls(id int unsigned not null auto_increment primary key,name varchar(20));

内连接，取交集

select * from students inner join cls ;//结果是count(students)*count(cls)
select * from students inner join cls on  students.cls_id = cls.id;//查出学生所在的班级对应的班级
select students.id,students.name,cls.name from students inner join cls on  students.cls_id = cls.id;//显示指定的列
select s.*,c.name from students as s inner join cls as c on  s.cls_id = c.id;//显示学生所有字段，给表设置别名
select s.*,c.name from students as s inner join cls as c on  s.cls_id = c.id order by c.name,s.id;//排序
select * from students as s left join cls as c on s.cls_id = c.id ;//用students为基础，查cls，等价下面
select * from cls as c right join students as s on s.cls_id = c.id ;
select * from students as s left join cls as c on s.cls_id = c.id having c.name is null;//查询没有班级的学生
select * from students as s left join cls as c on s.cls_id = c.id where c.name is null;//这个不建议
select s.id,s.name,c.name from students as s,cls as c where s.cls_id = c.id;//不建议

自关联，需要的数据

 create table areas(aid int primary key,atitle varchar(20),pid int);

source aaa.sql;//如果aaa.sql是一个sql文件，可以使用这个,前提是ls必须路径里面必须有aaa.sql

Select areas.atitle from areas inner join areas as city on city.pid = areas.aid having areas.atitle="北京市区";

 子查询

select * from students where hight = (select max(hight) from students where gender='男');//查询最高的男生信息
select * from students inner join (select max(hight) as hight from students group by cls_id) as stu on students.hight = stu.hight;//查询每个班最高的学生信息
select * from (select cls_id,  max(hight) as hight from students group by cls_id) as stu left join students as s on stu.cls_id = s.cls_id and stu.hight = s.hight;//同上
select * from students inner join (select max(hight) as hight from students group by cls_id) as stu on students.hight = stu.hight having students.gender="女";//查询每个班最高的女学生信息
select * from students left join (select max(hight) as hight from students group by cls_id) as stu on students.hight = stu.hight having students.gender="女";//同上

//视图，主要是为了方便查询

select s.id,s.name,s.age,s.hight,s.gender,c.name from students as s inner join cls as c on  s.cls_id = c.id;

 删除

drop view students_cls_view;

事务：

start transaction;//或者begin
update students set name="好人3" where id = 28;
commit;//提交 rollback；//可以回滚

索引

set profiling=1;//开启时间运行监测
select * from students;
show profiles;//查看查询的时间
set profiling=1;
create index id_index on students(id);//如果是字符串，需要把id改成name(10)  删除 drop index  id_index

show profiles;

管理用户

 使用数据库

use mysql;

 查询用户

SELECT * FROM USER;

添加用户,主机名如果写%说明任意主机都可以用

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

CREATE USER 'lili'@'%' IDENTIFIED BY '123';

修改密码

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lili';

SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

 如果mysql的root密码忘记了

这里是window的,苹果的我试了,不知道为什么一直不行
第一步停止mysql的服务器
cmd -- > net stop mysql
第二步无密码登录
mysqld --skip-grant-tables
第三步,打开新窗口,输入
use mysql;
第四步:
update user set password = password('你的新密码') where user = 'root';
第五步:关闭所有的窗口,下次就可以使用新密码登录了

权限管理

SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lili'@'%';

 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给用户lili授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'lili'@'localhost';

 撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lili'@'%';