mysql三
一数据导入二数据导出
三管理表记录 *
3.1 插入表记录
3.2 查询表记录 (单表 多表 嵌套 连接)
3.3 条件匹配
3.4 更新表记录字段的值
3.5 删除表记录
++++++++++++++++++++++++++++++++
一数据导入 : 把系统文件的内容存储到数据库的表里。
把系统已有的用户信息存储到studb.user表
/etc/passwd
用户名密码站位符uid gid 描述信息家目录shell
create databasestudb;
create tablestudb.user(
name char(50),
password char(1),
uid int(2),
gid int(2),
comment varchar(100),
homedirchar(150),
shellchar(50),
index(name)
);
查看导入数据时,搜索系统的目录
show variables like "secure_file_priv";
secure_file_priv | /var/lib/mysql-files/
mysql> loaddata infile"目录名/文件名"INTO TABLE 库.表
FIELDS TERMINATED BY"列间隔符号"
LINES TERMINATED BY"行间隔符号";
mysql> systemcp /etc/passwd /var/lib/mysql-files/
mysql> loaddata
INFILE"/var/lib/mysql-files/passwd"INTO TABLE studb.user
FIELDS TERMINATED BY":"
LINES TERMINATED BY"\n";
mysql>altertablestudb.user add> select* from studb.user;
++++++++++++++++++++++++++++++++++
修改导入数据时,搜索文件的目录 ?
#vim /etc/my.cnf
secure_file_priv="/mydatadir"
......
:wq
#mkdir /mydatadir
#chownmysql /mydatadir
#setenforce 0
#systemctlstart mysqld
#mysql -uroot -p123456
mysql> show variables like "secure_file_priv";
+------------------+-------------+
| Variable_name | Value |
+------------------+-------------+
| secure_file_priv| /mydatadir/ |
+------------------+-------------+
++++++++++++++++++++++++++++++++++
二数据导出: 把表记录存储到系统文件里。
mysql> show variables like "secure_file_priv";
MySQL> sql查询 INTOOUTFILE"目录名/文件名";
MySQL> sql查询 INTOOUTFILE"目录名/文件名" FIELDS TERMINATED BY"列间隔符号"LINES TERMINATED BY"行间隔符号";
select* fromstudb.userinto outfile "/mydatadir/user1.txt";
systemcat/mydatadir/user1.txt
selectname,uidfromstudb.userinto outfile "/mydatadir/user2.txt";
selectname,uidfromstudb.userlimit3;
selectname,uidfromstudb.userlimit3 into outfile "/mydatadir/user1.txt" fieldsterminated by "#";
++++++++++++++++++++++++++++++++++
三管理表记录 *(增 删 改 查)
3.1 增 插入表记录
一次插入1条记录给所有字段赋值
insert into库.表 values(值列表);
insert intouser values (51,"jim","x",2001,2001,"my student","/home/jim","/bin/bash");
一次插入多条记录给所有字段赋值
insert into库.表 values(值列表),(值列表);
insert intouser values (52,"jim","x",2001,2001,"my student","/home/jim","/bin/bash"),(53,"jim","x",2001,2001,"my student","/home/jim","/bin/bash");
一次插入1条记录给指定字段赋值
insert into库.表(字段名列表) values(值列表);
insertinto user(name,uid,gid)values("bob",3001,3001);
一次插入多条记录给指定字段赋值
insert into库.表(字段名列表) values(值列表),(值列表);
insertinto user(name,uid,gid)values("bob",3001,3001),("bob",3002,3001);
insertinto user(name,password,uid,gid,comment,homedir,shell) values("plj","x",10000,10000,"teacher","/home/plj","/bin/bash");
+++++++++++++++++++++++++++++++++++++++++++++++
3.2 查询表记录 (单表 多表 嵌套 连接)
单表查询
select字段名列表from 库.表;
select字段名列表from 库.表 where 匹配条件;
select * fromstudb.user;
select>
select>
select> 3.3 匹配条件 的 表示方式?
数值比较=!= > >= < 字符比较 = !=
where 字段名 符号 "字符串"
selectnamefrom userwhereshell="/bin/bash";
selectnamefrom userwherename="daemon";
selectname,shellfrom userwhereshell != "/bin/bash";
范围内匹配
where 字段名between值1and值2; 在...之间
where 字段名in(值列表); 在....里
where 字段名not in(值列表); 不在....里
select name,uidfrom userwhereuid between10 and 20;
select name from userwherenamein("root","lucy","damon");
select name,uid from userwhereuidin(100,500,1000,2001);
select name from userwhere shellnotin("/bin/bash","/sbin/nologin");
select name,shellfrom userwhere shellnotin("/bin/bash","/sbin/nologin");
逻辑匹配 (多个查询条件时 使用)
逻辑与and 条件都成立才可以
逻辑或or 某一个条件成立就可以
逻辑非! 取反
selectnamefrom userwhere uid=10andshell="/bin/bash";
selectname,uid,shellfrom userwhere uid=0andshell="/bin/bash";
selectnamefrom userwherename="lucy"orname="bob"or uid=1;
selectname,uidfrom userwherename!="lucy";
匹配空 isnull
匹配非空 isnotnull
select >
select >
select > selectname ,shellfrom user where shell is notnull;
insert into user(id,name)values(61,""),(62,null),(63,"null");
select >
select >
select > DISTINCT 查询时,字段的重复值不显示,只显示第1次出现的值。
selectshellfrom user;
select distinctshellfrom user;
selectshellfrom userwhereuid 1000;
查询结果分组
sql查询 groupby字段名;
selectshell from userwhere uid
select>
select>
select>
select> 查询表记录的语法格式:
select字段名列表from 库.表 where匹配条件;
条件匹配:
数值比较=!= > >= < select t3.shellfrom t3left joint4ont3.uid=t4.uid
-> group by shell;
+++++++++++++++++++++++++++++++++++++++++++
视图 存储过程 触发器
页:
[1]