DBA-1-3【管理表记录（增查改删）】

++++++++++++++++++【管理表记录（增查改删）】++++++++++++++++++
【【增加表记录】】

insert into 库.表(字段名列表) values (字段值列表)；

insert into db1.user values(43，"bob","x",2000,2000,“student user","/home/bob","/bin/bash");    //给所有字段赋值 可以不写字段名

insert into db1.user(name,uid) values("lucy","3001");    //给指定 字段 赋值
insert into db1.user(name,uid) values("lucy","3001")，("lu","3009")，("ly","3003");  //给指定 字段 赋值  可以写多条

案例：；：
mysql> insert into db1.user
    -> values
    -> (43,"bob","x",2000,2000,"studentuser","/home/bob","/bin/bash");
Query OK, 1 row affected (0.04 sec)

mysql> insert into db1.user values (44,"tom","x",2000,2000,"studentuser","/home/bob","/bin/bash");
Query OK, 1 row affected (0.03 sec)

mysql> insert into db1.user values (45,"lilli","x",2000,2000,"studentuser","/home/bob","/bin/bash");
Query OK, 1 row affected (0.04 sec)

mysql> select * from user;

mysql> insert into db1.user values (80,"lilli","x",2000,2000,"studentuser","/home/bob","/bin/bash");

mysql> insert into db1.user values (56,"lilli","x",2000,2000,"studentuser","/home/bob","/bin/bash");

mysql> delete from db1.user where id >= 45;

mysql> select * from user;

mysql> insert into db1.user values (null,"lilli","x",2000,2000,"studentuser","/home/bob","/bin/bash");   //如果不给写值或者给空值，默认就是当前最大数值 +1

mysql> select * from user;            //会发现有一个id号是81  因为之前实验前面有过给id=80 的 数据，虽然后来删除了 默认还是 以80为当前最大值然后+1. 故81.
.....  ...........     ......... 
.....  ...........     ......... 

| 42 | bob                 | x        |  2000 |  2000 | studentuser                                                     | /home/bob                 | /bin/bash      |
| 43 | bob                 | x        |  2000 |  2000 | studentuser                                                     | /home/bob                 | /bin/bash      |
| 44 | tom                 | x        |  3000 |  3000 | studentuser                                                     | /home/bob                 | /bin/bash      |
| 81 | lilli               | x        |  2000 |  2000 | studentuser                                                     | /home/bob                 | /bin/bash      |
+----+---------------------+----------+-------+-------+-----------------------------------------------------------------+---------------------------+----------------+
45 rows in set (0.01 sec)

【【//给指定 字段 赋值  可以写多条】】
mysql> insert into db1.user(name,uid)
    -> values
    -> ("lucccy","30003")
    -> ;
mysql> insert into db1.user(name,uid) values ("lucddccy","30663"),("lddy","30663"),("lucccy","40000");

=====================================================================================
【【查询表记录】】

select 字段名列表 from 库.表 where 条件;

select * from user;

举例子：
mysql> select id,name,shell from user;

mysql> select id,name,shell from user where id=3;

-------------------------------------------------------------------------------------------------
【【 更新表记录 】】

update 库.表 set 字段名=值 ，字段名=“值” where 条件；

update  user set password="A";
select id,password from user;

update user set password="x"  where id=1;

select * from user where id=1;

---------------------------------------
【【删除表记录】】
delete from 库.表  [where 条件]；  

delete from db1.user where  name="bob";
delete from db1.dogperson;

delete from db1.user where id >= 43;
----------------------------------------------
===================================================
###【【 匹配条件 】】##   

数值比较      =  > >=  <  <=  !=        字段类型必须数据数值类型
 where  字段名   符号    值
 where  字段名   符号   字段名

select * from user where id = 3;

mysql> select name,uid,gid from user where uid=gid;

mysql> select name from user where uid<10;

mysql> select name from user where uid < 10;

mysql> update user set password="F",homedir="/student" where uid< 10;
----------------------------------------------------------------------------------------------------------------------

【【 字符比较   =   !=  】】

 where  字段名  符号  “值”         ‘ 字段名的类型要是 字符的类型’

mysql> select name from user where shell="/bin/bash";

mysql> select name,shell from user where shell !="/bin/bash";

mysql> select * from user where name="mysql";
---------------------------------------------------------------------
匹配空   is   null
匹配非空   is  not  null

mysql> insert into user(name) values("yaya");

mysql> select name from user where uid is null;

mysql> update user set uid=4008 where uid is null;

mysql> select name,uid from user where name="yaya";

mysql> select name,uid from user where uid is not null;

字段也可以改称空  null      前提是 该字段 得 允许可以改位空null
mysql> select * from user where id =3;

mysql> update user set gid=null where id=3;

mysql> select * from user where id =3;

--------------------------------------------------------------------------------------------
逻辑比较
• 多个判断条件时使用
用 途
OR 逻辑或
AND 逻辑与
! 逻辑非
( ) 提高优先级

逻辑比较 （多个比较条件）
逻辑与   and  多个条件必须同时成立
逻辑或   or    多个条件某一个条件成立即可
逻辑非   !  not  取反

mysql> select name,uid from user where name="root" and uid=0 and shell="/bin/bash";

mysql> select name,uid,shell from user where name="root" or uid=7 or shell="/sbin/nologin";

mysql> select name,uid from user where name="root" and uid=1 or uid=3;
如果不加括号  先执行的是and 然后 再与or比较  再判断

( ) 先执行括号里面的 
mysql> select name,uid from user where name="root" and (uid=1 or uid=3);

mysql> select name,uid from user where name="root" and (uid=0 or uid=3);
--------------------------------------------------------------------------------
范围内匹配 / 去重显示】】】】】
• 匹配范围内的任意一个值即可
类 型                        用 途
In                     ( 值列表 ) 在...里...
Not in                    ( 值列表 ) 不在...里...
Between 数字 1 and 数字2             在...之间...
DISTINCT 字段名             去重显示      不显示字段的重复值

mysql> select name from user where name in ("apache","mysql","admin","bin");

mysql> select name,shell from user where shell not in ("/bin/bash","/sbin/nologin");

mysql> select name,uid from user where uid between 10 and 20;

mysql> select distinct shell from user;
mysql> select distinct shell from user where uid<= 10;

---------------------------------------------------------------------------------
==================================
==================================

高级查询条件 @ 高级查询条件 # 高级查询条件

模糊匹配
• 基本用法

– WHERE 字段名 LIKE ' 通配字串 '
– 通配符 _ 匹配单个字符
– % 匹配 0~N 个字符

mysql> select name,uid from user where name like '_';
mysql> select name,uid from user where name like '___';

mysql> select name ,uid from user where name like '___' and uid > 10;
mysql> select name,uid from user where name like 'r__';
mysql> select name,uid from user where name like 'a%t';
mysql> select name ,uid from user where name like '__%__';

------------------------------------
mysql> insert into user(id,name)values(60,null);
Query OK, 1 row affected (0.05 sec)

mysql> select id,name,uid from user where name like '%';    //结果不显示60 那一行

mysql> insert into user(id,name)values(61,"");

mysql> mysql> select id,name,uid from user where name like '%';    //结果显示61那以行
---------------------------------------------------------------------------------------------------------
正则匹配
• 基本用法
– WHERE 字段名 REGEXP ' 正则表达式‘
– ^ $ . [ ] *

mysql> select name,uid from user where uid regexp '....';  满足任意四个连续的字符

mysql> select name,uid from user where uid regexp '^....$'; 任意字符开头和结尾的 必须是四个字符

mysql> select name from user where name regexp 't$';      以t结尾的

mysql> insert into user(name)values("yaya9"),("6yaya"),("yay5a"),("ya2ya");
mysql> select name from user where name regexp '[0-9]'; 集合，匹配集合中的任意单个字符   0-9任意一个数字
mysql> select name from user where name regexp '^[0-9]';     0-9任意数字开头的
---------------------------------------------------------------------------------------------
四则运算
• 运算操作
– 字段必须是数值类型

类 型        用 途
+         加法
-         减法
*         乘法
/         除法
%         取余数(求模)

mysql> alter table user 
    -> add
    -> age tinyint(2) unsigned default 18
    -> after name;

mysql> select name,age, 2018-age as syear from user where name="root";    //as 后面起个名字， as也可以省掉。  2018 - age  2018 减去 age
mysql> select name,age, 2018-age  syear from user where name="root";

mysql> update user set age=age+1;
mysql> select name,age from user;

mysql> select name,uid,gid from user where uid>=10 and uid<=30;
mysql> select name,uid,gid,(uid+gid)/2 pjz from user where uid>=10 and uid<=30;
mysql> select name,uid,gid,(uid+gid)/2 as pjz from user where uid>=10 and uid<=30;    //可以带as as后面跟起的名字
mysql> select name,uid,gid,(uid+gid)/2 from user where uid>=10 and uid<=30;        // 如果不起名字  就会以(uid+gid)/2显示
------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------

聚集函数
• MySQL 内置数据统计函数
字段类型 位  数值型的
– avg( 字段名 ) : 求平均值
– sum( 字段名 ) :求和
– min( 字段名 ) : 统计最小值
– max( 字段名 ) :统计最大值
– count( 字段名 ) :统计个数

mysql> select avg(uid) from user;
mysql> select avg(uid) from user where uid>=20;     //可以跟条件。
mysql> select sum(uid) from user;

mysql> select count(id) from user;

mysql> select count(name) from user;

mysql> select name from user where shell="/bin/bash";
mysql> select count(name) from user where shell="/bin/bash";
---------------------------------------------------------------------=======================

操作查询结果】】】】】

查询结果排序
• 基本用法
– SQL 查询
ORDER BY 字段名    通常是数值类型字段
[ asc | desc ]

mysql> select name,uid from user where uid>=10 and uid<=1000 order by uid desc;

---------------------------------------------------------
查询结果分组
• 基本用法
– SQL 查询
group by 字段名   通常是字符类型字段
mysql> select shell from user where uid>=10 and uid<=1000;

mysql> select shell from user where uid>=10 and uid<=1000 group by shell;
mysql> select shell from user group by shell;

--------------------------------------------------------
查询结果过滤
• 基本用法

– SQL 查询 HAVING 条件表达式;
– SQL 查询 where 条件 HAVING 条件表达式;
– SQL 查询 group by 字段名
HAVING 条件表达式;    

mysql> select name from user having name="jerry";

mysql> select id,name from user where name like '%' having name is null;

mysql> select shell from user where uid>=10 and uid<=1000 group by shell having shell="sync";

----------------------------------------------------------

限制查询结果显示行数
• 基本用法

– SQL 查询 LIMIT N; 显示查询结果前 N 条记录
– SQL 查询 LIMIT N,M ;显示指定范围内的查询记录
– SQL 查询 where 条件查询 LIMIT N ;显示查询
结果前 N 条记录
– SQL 查询 where 条件查询 LIMIT N , M ;显
示指定范围内的查询记录

sql 查询 limit  起始行，行数；
sql 查询 limit  5，5；    //从第六行显示查询结果， 默认第一行是0
sql 查询 limit  0，1；
sql 查询 limit 1;

mysql> select shell from user where uid>=10 and uid<=1000 limit 3;

mysql> select * from user;
mysql> select * from user limit 0,1;
mysql> select * from user limit 9,5;
mysql> select * from user limit 3,3;
mysql> select * from user where uid <=10 order by uid desc limit 3;

--------------------------------------------------------------------