该文是mysql实战知识点
适合有一定基础，代码实战教学为主，基础知识比较少，可以帮助大家有效巩固mysql基础 *** 作。
该文是基础篇
基础篇包括对数据库，表的结构 *** 作，查询及增删改。
后续会更新进阶篇，包括视图，触发器，函数以及存储过程。
mysql -h主机地址 -u用户名 -p用户密码
连接本地mysql
mysql -u root -p 123456
连接远程mysql (ip地址是10.120.71.89)
mysql -h 10.120.71.89 -u root -p 123456
-h 代表输入远程mysql服务器的ip
-u 代表输入用户名
-p 代表输入密码
quit exit 退出mysql服务
1.1.2新增用户格式
GRANT SELECT on 数据库.* to 用户名@登录主机 identified by "密码"
新增一个用户，用户名shop 密码123的用户新加对库的查询 插入 修改 删除 权限
grant select ，insert， update，delete on *.* to shop@"%"identfied by"123456"
其中%代表Internrt上任何一台计算机都可以连接你的数据库
改为localhost 或者改为指定ip地址才能访问 eg
grant select ，insert， update，delete on *.* to shop@localhost identfied by"123456"
grant select ，insert， update，delete on *.* to shop@10.120.71.89
identfied by"123456"
在修改密码时，用户需要获得reload权限
GRANT reload ON *.* to 'shop'@'%'
实战-修改用户密码
mysql -u root -p 123456
grand reload on *.* to 'shopdb'@'%'
update mysql.user set authentication_string=PASSWORD("123456"),password_expired+'N'WHERE user='shopdb'AND Host='localhost';
flush pribileges;
flush privilrges 刷新mysql的系统权限相关表（设置新用户或改密码后需要）
1.2数据库 *** 作 1.2.1查看数据库SHOW DATABASES；
实战–查看数据库
mysql -u root -p 123456;
show databases;
CREATE DATABASE databaseName
实战–创建数据库
mysql -u root -p 123456;
create database shop;
USE databaseName
实战–使用数据库
mysql -u root -p 123456;
use shop;
DROP DATABASE databaseName
实战 --删除数据库
mysql -u root -p 123456;
show databases;
--mysql
--shop
drop database shop;
show databases;
--mysql
create table user（ name 类型， ）
实战 --创建表
mysql -u root -p 123456;
create database shop;
use shop;
create table user(
id int(10) unsigned not null auto_increment,
name varchar(25),
sex varchar(25),
age int(10),
password varchar(25),
primary key(id)) charset=utf-8;
)
show tables;
--user
DESC tableName
实战–查看表结构
mysql -u root -p 123456;
use shop;
Desc user;
--field type Null Key Default Extra
--id int(10) NO PRI NULL auto_increment
--name varchar(5) YES NUll
复制表结构，主键，索引
CREATE TABLE new_table like table;
插入数据
INSERT TABLE new_table SELECT * FROM table;
实战——复制表结构，数据，主键，索引
向user(之前创建过)表中插入一条记录
mysql -u root -p 123456;
use shop;
insert into user values(2,"tom","男","30","123456",0);
select * from user;
将user表结构索引主键复制到newuser中
create table newuser like user;
select * from newuser;
把旧的数据复制到新表中
insert newuser select * from user;
复制表结构，数据，可执行如下
CREATE TABLE newtable select * from table;
复制表结构 数据不复制
CREATE TABLE newtable SELECT * from old_table where 0;
实战–复制表结构，数据
mysql -u root -p 123456;
CREATE TABLE newtable2 select * from table;
CREATE TABLE newtable3 SELECT * from old_table where 0;
表二复制数据和结构 ，表三复制结构
1.4数据 *** 作 1.4.1插入 INSERT 查询 SELECT
将数据添加到数据库中
INSERT INTO 表名(字段名，字段名) values(值,值);
INSERT INTO 表名 values(值,值);
查询数据
select * from 表名;
select id from 表名;
select * from 表名 where id=10;
select * from 表名 where id=10 and name="liubei";
实战-数据插入和查询
mysql -u root -p 123456;
use shop;
DESC user;
insert into user(id,name,sex)values(3,"lihua","男");
insert into user values(4,'lili','女','25','2222','0');
select * from user;
select name from user;
select * from user where sex='男';
slelect * from user where sex='男' and name ='lihua';
修改数据
update 表名 set 字段=值 where 条件;
update user set name='lihua' where id=4;
实战–修改记录
mysql -u root -p 123456;
use shop;
select * from user;
将user表中名字为dabid的性别改为女生
update user set sex='女' where name='dabid';
删除记录
DELETE FROM 表名 WHERE 条件;
DELETE FROM user WHERE id=4;
实战–删除记录
删除id为4和性别为男的数据
mysql -u root -p 123456;
use shop;
查询id=4用户
delete from user where id=4;
查询性别等于男的用户
delete from user where sex='男';
升序 ASC为升序可以省略
SELECT * FROM USER ORDER BY id ASC;
SELECT * FROM USER ORDER BY id;
降序 DESC 对某字段降序
SELECT * FROM USER ORDER BY id DESC;
多条记录
SELECT * FROM USER ORDER BY sex,id DESC;
实战–查询结果排序
对查询结果通过id进行升序和降序。
mysql -u root -p 123456;
use shop;
用id进行升序
select * from user order by id asc;
通过id进行降序排序
select * from user order by id desc;
通过性别升序，年龄降序排序。
select * from user order by sex asc,age asc;
分组–字段相同的值为一组
group by 单独使用时，只显示每一组的第一条记录。
group_concat(字段名)可以作为一个输出字段，来放置每一组的某字段的值的集合。
select * from user group by sex;
select sex,group_concat(name) from user group by sex;
实战——查询结果分组
mysql -u root -p 123456;
use shop;
select * from user ;
select * from user by sex;(只取出对应的第一条记录)
根据用户性别id进行分组
select * from user group by sex,id;
按性别进行分组，输出用户的姓名
select sex.group_concat(name)from user group by sex;
俺性别进行分组，输出用户id
select sex.group_concat(id)from user group by sex;
设置分组条件的表达式，having只能用于group by；
select sex ,count(sex) from user where age>15 group by sex having count(sex)>2;
以性别为组，筛选出性别组数量大于2的当组的性别和数量。
实战–设置分组条件
mysql -u root -p 123456;
use shop;
select * from user ;
按性别分组，并查询性别分组后大于2的性别，输出性别及数量。
select sex ,count(sex) from user group by sex having count(sex)>2;
按性别分组，并且查询在性别分组后大于4的性别，输出性别及数量。
select sex ,count(sex) from user group by sex having count(sex)>4;
先用age>20的条件筛选，然后按性别分组，并且查询在性别分组后大于4的性别，输出性别及数量。
select sex ,count(sex) from user where age >20 group by sex having count(sex)>4;
limit用于限制查询的数量，常用于分页语句，可以接受一个或者两个参数。
一个参数
检索前六行记录
select * from user limit 6;
两个参数
从第二条数据开始，检索出5条数据；
select * from user limit 2,5;
实战- 数据检索
mysql -u root -p 123456;
use shop;
select * from user ;
检索出前六条记录
select * from user limit 6;
检索从第三条开始的四条记录
select * from user limit3,4;
检索出女生前三条数据
select * from user where sex='女' limit 0,3;
检索出女生按年龄排序后的前四条数据
select * from user where sex='女' order by age desc limit 0,4;
创建唯一标识–使用PRIMARY KEY添加主键
create table tablename{,,,primary key(**)};
实战–设置主键
mysql -u root -p 123456;
use shop;
设置id为主键
create table user(
id int(10) ,
name varchar(25),
sex varchar(25),
age int(10),
password varchar(25),
primary key(id)) charset=utf-8;
删除表主键
alter table user drop primary key;
设置name为主键
alter table user add primary key(name);
复合主键是多字段组成的主键，以多字段来确定唯一标识。
实战–复合主键设置
mysql -u root -p 123456;
use shop;
设置id和name为复合主键
create table user(
id int(10) ,
name varchar(25),
sex varchar(25),
age int(10),
password varchar(25),
primary key(id,name)) charset=utf-8;
alter table user add phone varchar(255) not null;
实战–添加字段
mysql -u root -p 123456;
use shop;
create table user(
id int(10) ,
name varchar(25),
sex varchar(25),
age int(10),
password varchar(25),
primary key(id)) charset=utf-8;
添加phone字段
alter table user add phone varchar(255) not null;
alter table user modify phone int(25) not null;
实战–改变字段
mysql -u root -p 123456;
use shop;
create table user(
id int(10) ,
name varchar(25),
sex varchar(25),
age int(10),
password varchar(25),
primary key(id)) charset=utf-8;
修改password字段为int(25);
alter table user modify password int(25) not null;
desc user;(查看表结构)
alter table 表名 change 字段名 字段新名称 字段类型
实战–字段重命名
mysql -u root -p 123456;
use shop;
create table user(
id int(10) ,
name varchar(25),
sex varchar(25),
age int(10),
password varchar(25),
primary key(id)) charset=utf-8;
将age 重命名为 myage
alter table user change age myage int(10);
设置默认值
alter table user alter name set default kk;
删除默认值
alter table user alter int drop default;
实战—字段设置默认值
mysql -u root -p 123456;
use shop;
create table user(
id int(10) ,
name varchar(25),
sex varchar(25),
age int(10),
password varchar(25),
primary key(id)) charset=utf-8;
设置密码默认为123456，之后插入数据password可以不插入，默认有值123456
alter table user alter password set default 123456;
insert into user(id,name,sex,age) values(1,'li','男',12);
删除默认值
alter table user alter password droup default;
insert into user(id,name,sex,age) values(1,'li','男',12);报错
自增属性：AUTO_INCREMENT
1.把null值插入到自增列中，mysql会自动生成选一个序列编号，从1开始递增。
2.插入值是没明确指出就是插入null。
3.myisam引擎中如果值重复就报错，大于已有值从已有值开始递增。innodb引擎中updata auto——increment自动会报错。
4.delete删除字段后，前面空余id不会复用。
实战–设置自增字段
mysql -u root -p 123456;
use shop;
创建user表设置id为自增字段，但是没有设置成主键会报错，所以设置成主键
create table user(id int auto_increment,name varchar(255),primary key(id));
插入数据
insert into user (name) values('lihua'); ----1,lihua
insert into user (name) values('xiaoming'); ----2,xiaoming
insert into user values(100,'xiaohua'); ----100,xiaohua
insert into user values('xiao'); ----101,xiao
delete from user where id=101;
insert into user values('xiaoli'); ----102,xiaoli
基本 查询语法格式
SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT
SELECT 查询内容 *表示所有字段 eg select *
FROM 表名 从哪张表查
WHERE 表达式 条件查询 <>不等于 ！=不等于 <=小于等于
GROUP BY 字段名 分组查询 一般搭配聚合函数使用
HAVING 表达式 与group by一起使用-对分组后的数据进行过滤 可以跟聚合函数 (where不行)
ORDER BY 字段名 进行排序
LIMIT 记录数 限制查询结果，可以分页查询
实战–查询数据
mysql -u root -p 123456;
use shop;
查询user表中name字段的数据
select name from user;
查询性别等于男的数据
select * from user where ser='男';
查询年龄大于20的数据
select * from user where age>20;
查询共有多少数据
select count(*) from user;
查询年龄总和
select sum(age) from user;
查询年龄最大的数据
select max(age) from user;
按性别进行分组查询
select * from user group by sex;
按年龄降序排列查询
select * from user order by age desc;
按年龄升序查询
select * from user order by age asc;
查询前五条数据
select * from user limit0,5;
查询所有字段中的一个或多个字段
实战–基本查询过滤
mysql -u root -p 123456;
use shop;
查询所有数据
select * from user;
查询一个或者多个数据
select name ,sex from user;
条件过滤关键词为AND, OR ,IN ,NOT IN , IS NULL, IS NOT NULL, BETWEEN AND
1.and 与 必须都满足
select * from user where age=20 and name='lihua';
2.or 或 满足一个
select * from user where age=20 or name = 'lihua';
3.in 在指定范围内
select * from user where age in(14,15,16);
4.not in 不在指定范围内
select * from user where age not in(14,15,16);
5.is 为空 is null 表示没值 （对应区域没数据） is not null 表示非空 不能用 ！=null
select * from user where name is null;
6.between and 在区间内 包括裂变
select * from user where age between 10 and 20;
实战–条件查询过滤
mysql -u root -p 123456;
use shop;
使用and 查询性别为女年龄大于27的数据
select * from user where sex='女' and age>27;
使用or 查询叫小红或小明的数据
select * from user where name='小红'or name ='小明';
使用in 查询id在（3,5,7）范围内数据
select * from user where id in a(3,5,7);
使用not in 查询id不在（3,5,7）范围内数据
select * from user where id not in a(3,5,7) ;
使用is null 查询手机号码为空的数据
select * from user where phone is null;
使用is not null 查询手机号不为空的数据
select * from user where phone is not null;
查询年龄大于等于20小于等于30的数据
select * from user where age between 20 and 30;
select * from user where age>= 20 and age<= 30;
查询性别为男的数据
select * from user where sex='男';
查询性别不为男的数据
select * from user where sex!='男';
select * from user where sex<>'男';
使用关键词like
1.like ‘李%’ 查询以李开头的数据
select * from user where name like ' 李%';
2.like ‘%李’ 查询以李结尾的数据
select * from user where name like '%李';
3.like ‘%李%’ 查询含有李的数据
select * from user where name like '%李%';
实战–模糊查询过滤
mysql -u root -p 123456;
use shop;
select * from user;
查询以李开头的数据
select * from user where name like ' 李%';
查询以李结尾的数据
select * from user where name like '%李';
查询含有李的数据
select * from user where name like '%李%';
DISTINCT 去除重复的值
select distinct age from user;
AS 给列起别名 可以省略as
select name as 名字 from user;
select name 名字 from user;
实战–字段控制查询过滤
mysql -u root -p 123456;
use shop;
select * from user;
去除重复值查询年龄
select distinct age from user;
查询id和age的和,起别名为total
select id+age as total from user;
select id+age total from user;
使用regexp关键字来指定匹配规则，检索数据
| 匹配模式 | 含义 | 例子说明 | 示例 |
|---|---|---|---|
| ^ | 文本开始 | ^b | bag,bad |
| $ | 文本结束 | st$ | test,bst |
| . | 单个字符 | b.t | bat,but |
| * | 前面0或多个字符(平替) | f*n | fn,faan |
| + | 前面大于一个字符(平替) | ba+ | ba,baaa,bay |
| <字符串> | 指定字符串,包含该字符串 | fa | fan,afa |
| [字符集合] | 集合中任意字符 | [abc] | nad,apple |
| [^字符集合] | 不在集合中字符 | [^abc] | low,high |
| 字符串{n,} | 前面至少出现n次 | b{2} | bb,bbbbbb |
| 字符串{n,m} | 前面字符出现n到m次 | b{2,3} | bbb |
实战–使用正则表达式
mysql -u root -p 123456;
use shop;
select * from user;
查询remark字段里以b开头的数据
select * from user where remark regexp '^b';
查询loginname字段里以ang结尾的数据
select * from user where loginname regexp 'ang$';
用 . 来代替字符串中任意一字符
select * from user where loginname regexp 'x.a';
用 * 匹配前面的字符任意次，包括0
select * from user where remark regexp '^ba*';
用 + 匹配前面的字符至少一次
select * from user where remark regexp '^ba+';
匹配指定字符串
select * from user where loginname regexp 'xiao';
匹配多个字符串，中间用 | 分开
select * from user where loginname regexp 'xiao|mei';
方括号[]是一个字符集合 [0-9]表示数字
select * from user where liginname regexp '[da]';
查询不在这个集合范围内的数据
select * from user where id regexp '[^1-4]';
字符串（n，）前面的字符至少出现n次
select * from user where remark regexp 'a{2,}';
字符串（n，m）前面的字符至少出现n次，至多m次
select * from user where remark regexp 'ba{1,3}';
父表达式调用另一个表达式结果的查询 *** 作，
子查询可以是select包含的任意字句如distinct，group by,order by,limit,join,union
但是对应的外部查询必须是：select insert update delete set do
按返回结果分类 可分为 表子查询 行子查询 列子查询 标量子查询
按返回结果的调用方法，可分为 where 子查询 from 子查询 exists 子查询
2.3.2按结果分类的子查询 1.表子查询常用于查询from语句中
select * from (select name,sex,age from user where age>20)as user;
实战–表子查询
mysql -u root -p 123456;
use shop;
select * from user;
查询用户年龄大于20的性别姓名和年龄；
select * from (select name,sex,age from user where age>20)as user;
返回结果为一行数据，常用于from或者where字句中
select * from user where sex=(select sex from user where name ='lihua');
实战–行子查询
mysql -u root -p 123456;
use shop;
select * from user;
查询和lihua性别一样的用户id
select * from user where sex=(select sex from user where name ='lihua');
返回结果为多行一列 可以用 in any all *** 作符
in 在指定选项中
select id,name from user where id in(select id feom user where sex='女');
any 必须接一个比较 *** 作符
select id ,name,age from user where age>any(select age from user where name='lihua');
all 与所有值比较
select * from user where age<=all(select age from user);
实战–列子查询
mysql -u root -p 123456;
use shop;
select * from user;
查询性别为女的id name
select id,name from user where id in(select id feom user where sex='女');
查询年龄大于david的用户的id name age
select id ,name,age from user where age>any(select age from user where name='lihua');
查询年龄最小的用户
select * from user where age<=all(select age from user;);
返回的结果是一个标量，一行一列
select name from user where age=(select age from user where id=4) and id <>4;
实战–标量子查询
mysql -u root -p 123456;
use shop;
select * from user;
查询和id等于4一样年龄的用户
select name from user where age=(select age from user where id=4) and id <>4;
内层查询结果作为外层查询条件
select * from user where age>(select age from user where name='lihua');
实战–where型子查询
mysql -u root -p 123456;
use shop;
select * from user;
查询年龄大于lihua的用户
select * from user where age>(select age from user where name='lihua');
返回结果集作为一个临时表，设置别名，然后在临时表中进行查询；
select * from (select name ,age from user where sex='男')as temp where temp.age>12;
实战–from型子查询
mysql -u root -p 123456;
use shop;
select * from user;
查询性别为男的用户
select * from (select name ,age from user where sex='男')as temp;
查询性别为男且年龄大于12的用户
select * from (select name ,age from user where sex='男')as temp where temp.age>12;
内层返回一个真假值
子查询至少返回一个值 则exists语句值就是true
当返回值为true时，外层子查询进行查询，否则不查询 not exists相反
相对于if，条件成立就查询
select * from suer where exists(select * feom user where password='123456');
实战-exists子查询
mysql -u root -p 123456;
use shop;
select * from user;
查询用户密码存在123456
select * from suer where exists(select * feom user where password='123456');
查询用户密码存在123456，然后输出用户密码123456
select * from suer where exists(select * feom user where password='123456') and password='123456';
查询有何面目不存在12345
select * from suer where not exists(select * feom user where password='123456');
用于技术平均值
select avg(age) from user;
实战–avg函数
mysql -u root -p 123456;
use shop;
select * from user;
查询年龄这一列的平均年龄
select avg(age) from user;
查询表中记录的个数
select count(*) from user where agr>20;
实战–count函数
mysql -u root -p 123456;
use shop;
select * from user;
计算年龄大于20的个数
select count(*) from user where age>20;
查询最大或最小值
select max(age) from user;
select min(age) from user;
实战–max min函数
mysql -u root -p 123456;
use shop;
select * from user;
查询数据中的最大年龄
select max(age) from user;
查询男生中最大的年龄
select max(age) from user where age='男';
查询所有数据中最小的年龄
select min(age) from user;
查询女生中最小的年龄
select min(age) from user where age='女';
计算某一列的和
select sum(age) from user;
实战–sum函数
mysql -u root -p 123456;
use shop;
select * from user;
年龄求和
select sum(age) from user;
年龄求和设置别名
select sum(age) total feom user;
男生求和
select sum(age) mantotal feom user where age='男';
关键词：inner join on ，inner可以省略
从左表取出每一天记录与右表匹配，匹配成功后才会保留
select * from 左表 [inner] join 右边 on 左表.字段=2右表.字段;
on 表示链接条件，如果没有on 会保留所有匹配结果；
where可以代替on，但是where没on效率高
不同表有同名字段需要用别名加以区分
mysql -u root -p 123456;
use shop;
使用等值内连接查询6
select * from user u inner join score s on u.id=s.id;
省略inner关键字
select * from user u join score s on u.id=s.id;
省略on关键字
select * from user u join score s;
分为左外连接和右外连接
使用关键字outer join on
select * from 左表 left/right join 右表 on 左表.字段=右表.字段；
on 不可以省略
left join 也可以写成 left outer join ，返回左表中所有的值和右表中匹配的值，如果右表无匹配值就显示null
right join同理
mysql -u root -p 123456;
use shop;
左外连接 省略outer
select * from user u left join score s on u.id=s.id;
右外连接 不省略outer
select * from user u right outer join score s on u.id=s.id;
两个表中列名称相等才能使用
关键字：natural join
可以分为自然内连接和自然外连接
select * from 左表 left/right inner join 右边 on using(字段名);
自然内连接
select * from user natural join score;
自然左外连接
select * from user natural left join score;
自然右外连接
select * from user natural left join score;
外连接模拟自然左外连接
select * from user left join score using(id);
实战–自然连接查询
mysql -u root -p 123456;
use shop;
自然内连接
select * from user natural join score;
自然左外连接
select * from user natural left join score;
自然右外连接
select * from user natural left join score;
外连接模拟自然左外连接
select * from user left join score using(id);
从一张表里循环取出每一条记录然后与另一张表的每一条表进行匹配；
结果为笛卡尔积 所有字段都会保留
关键字 cross join
select * 左表 cross join 右表 或者 from 左表，右表;
实战–交叉连接查询
mysql -u root -p 123456;
use shop;
交叉连接查询 使用关键字 cross join
select * from user cross join score;
交叉连接查询 使用from
select * from user,score;
将多次查询结果过合并起来进行拼接，要求两次查询列数必须一致，类型可以不一样
关键字 UNION 会去掉重复的行
关键字UNION all 不会去掉重复的行
order by不能直接使用，需要使用括号括住查询语句才行 要order by生效必须搭配limit
select column_name from table1
UNION(all)
select column_name from table2
实战–联合查询
mysql -u root -p 123456;
use shop;
查询user表的id 和score表的id集合
select id from user
UNION
select id from score;
查询user的id name 和成绩表score表的id score表集合
select id,name from user
UNION
select id,score from score;
使用order by limit 要使用括号
(select id,name from user order by id desc limit 0,2)
UNION
(select id,score from score order by id desc limit 0,2);
使用order by 推荐放到所有字句后，对最终合并的结果来排序或筛选
(select id ,name from user where age>20)
UNION
(select id,score from score where score>80)order by id limit 0,2;
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）