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'@'%';