管理表結構:
1.插入
mysql> insert into db1.user values(22,"bob","x","3000,3000","test user","/home/bob","/bin/bash");
添加一條記錄,給所有字段賦值
mysql> insert into db1.user(name,uid,gid) values("tom","4003,4003");
添加一條記錄,指定字段賦值
ps:
字段值要與字段類型相匹配 字符類型的字段,要用""號括起來
依次給所有字段賦值時,字段名可以省略
只給部分字段賦值時,必須明確寫出對應的字段名稱
沒有賦值的字段使用默認值或自增長賦值
2.查詢
mysql> select name,uid,gid from user ;
mysql> select * from user;
mysql> select name,uid from user where id<=3;
查詢表記錄,*表示所有字段 查看當前庫表記錄時庫名可以省略 字段列表決定顯示列個數(shù)
條件決定顯示行的個數(shù)恕稠。
3.更新表記錄 update
mysql> update 庫名.表名 set 字段名=值琅绅,字段名=值;
mysql> update user set password="a",comment="student";
mysql> update user set password="x",comment="root" where id=1;
字段值要與字段類型相匹配 對于字符類型的字段,值要用雙引號括起來
若不使用where限定條件,會更新所有記錄字段值
限定條件時,只更新匹配條件的記錄的字段值
4.刪除表記錄 delete
mysql> delete from db1.user;
mysql> delete from db1.user where id>=22;
不添加條件刪除表中的所有行
匹配條件:
單獨說一下mysql的匹配條件
1.基本匹配條件 適用于select鹅巍、update千扶、delete
數(shù)值對比,字段必須是數(shù)值:
= 等于
大于
= 大于等于
<= 小于等于
骆捧!= 不等于
mysql> select * from user where uid=2;
mysql> select name,uid,gid from user where uid=gid;
mysql> select name,uid,gid from user where uid!=gid;
2.字段對比澎羞,必須是字段的類型
= 等于
!= 不等于
is null 空
is not null 非空
mysql> select name from user where name="mysql";
mysql> select name,shell from user where shell!="/bin/bash";
mysql> select id,name from user where name is null;
mysql敛苇。 select id,name from user where name is not null;
3.邏輯匹配
多個判斷條件時使用and 邏輯與多個判斷條件必須同時成立
or 邏輯或多個判斷某個條件成立即可
妆绞!或not邏輯非取反
mysql> select name,uid,gid,shell from user where name="root" or uid=100 or gid=100;
4.在范圍內匹配|去重查詢
in 在 里
not in 不在里
between and 在 之間
distinct 字段名
mysql> select name,uid from user where uid in (1,10,34);
mysql> select name from usere where name in ("mysql","bin","root");
mysql> select name,shell from user where shell not in ("/bin/bash","/sbin/nologin");
mysql> select * from user where id between 10 and 30;
mysql> select distinct gid from user;