day03

一数据导入二数据导出三管理表记录 *3.1 插入表记录3.2 查询表记录 （单表 多表 嵌套 连接）3.3 条件匹配3.4 更新表记录字段的值3.5 删除表记录++++++++++++++++++++++++++++++++一数据导入 : 把系统文件的内容存储到数据库的表里。把系统已有的用户信息存储到studb.user表/etc/passwd用户名 密码站位符 uid gid 描述信息 家目录 shell

create database studb;

create table studb.user(name char(50),password char(1),uid int(2),gid int(2),comment varchar(100),homedir char(150),shell char(50),index(name));查看导入数据时，搜索系统的目录show variables like "secure_file_priv";secure_file_priv | /var/lib/mysql-files/

mysql> load data infile "目录名/文件名" INTO TABLE 库.表

FIELDS TERMINATED BY "列间隔符号"LINES TERMINATED BY "行间隔符号";

mysql> system cp /etc/passwd /var/lib/mysql-files/

mysql> load data

INFILE "/var/lib/mysql-files/passwd" INTO TABLE studb.userFIELDS TERMINATED BY ":"LINES TERMINATED BY "\n";mysql>alter table studb.user add id int(2) primary key auto_increment first;

select * from studb.user;

++++++++++++++++++++++++++++++++++

修改导入数据时，搜索文件的目录 ？#vim /etc/my.cnf[mysqld]secure_file_priv="/mydatadir"......:wq#mkdir /mydatadir#chown mysql /mydatadir#setenforce 0#systemctl start mysqld#mysql -uroot -p123456mysql> show variables like "secure_file_priv";+------------------+-------------+| Variable_name | Value |+------------------+-------------+| secure_file_priv | /mydatadir/ |+------------------+-------------+

++++++++++++++++++++++++++++++++++

二数据导出: 把表记录存储到系统文件里。mysql> show variables like "secure_file_priv";MySQL> sql查询 INTO OUTFILE "目录名/文件名"；MySQL> sql查询 INTO OUTFILE "目录名/文件名" FIELDS TERMINATED BY "列间隔符号" LINES TERMINATED BY "行间隔符号"；

select * from studb.user into outfile "/mydatadir/user1.txt";

system cat /mydatadir/user1.txt

select name,uid from studb.user into outfile "/mydatadir/user2.txt";

select name,uid from studb.user limit 3 ;

select name,uid from studb.user limit 3 into outfile "/mydatadir/user1.txt" fields terminated by "#";

++++++++++++++++++++++++++++++++++

三管理表记录 * (增 删 改 查)3.1 增 插入表记录一次插入1条记录给所有字段赋值insert into 库.表 values（值列表）；

insert into user values (51,"jim","x",2001,2001,"my student","/home/jim","/bin/bash");

一次插入多条记录给所有字段赋值

insert into 库.表 values（值列表），（值列表）；

insert into user 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（值列表）；insert into user(name,uid,gid) values("bob",3001,3001);

一次插入多条记录给指定字段赋值

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

insert into user(name,uid,gid) values("bob",3001,3001),("bob",3002,3001);

insert into 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 * from studb.user;

select id,name,homedir from studb.user;

select id,name,homedir from studb.user where id<=10;

select id,name,homedir from studb.user where name="root";

3.3 匹配条件 的 表示方式？

数值比较 = != > >= < <=where 字段名 符号 数值

select name from user where uid=500;

select name from user where id<=10;

字符比较 = !=

where 字段名 符号 "字符串"select name from user where shell="/bin/bash";

select name from user where name="daemon";

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

范围内匹配

where 字段名 between 值1 and 值2; 在...之间where 字段名 in (值列表); 在....里where 字段名 not in (值列表); 不在....里

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

select name from user where name in ("root","lucy","damon");

select name,uid from user where uid in (100,500,1000,2001);

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

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

逻辑匹配 （多个查询条件时 使用）

逻辑与 and 条件都成立才可以逻辑或 or 某一个条件成立就可以逻辑非 ！ 取反

select name from user where uid=10 and shell="/bin/bash" ;

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

select name from user where name="lucy" or name="bob" or uid=1;

select name,uid from user where name!="lucy";

匹配空 is null

匹配非空 is not null

select id ,name from user where name is null;

select id ,name from user where shell is null;select id ,name,shell from user where shell is null;select name ,shell from user where shell is not null;

insert into user(id,name)values(61,""),(62,null),(63,"null");

select id,name from user where name="";

select id,name from user where name is null;select id,name from user where name="null";

DISTINCT 查询时，字段的重复值不显示，只显示第1次出现的值。

select shell from user;

select distinct shell from user;

select shell from user where uid <=1000;

select distinct shell from user where uid <=1000;

查询时做运算操作 + - / %

alter table user add s_year year default 2000 after name;

select from user;

select name , 2018 - s_year as age from user where name="root";

select name,uid,gid from user where name="bin";

select name,uid,gid, uid+gid as sum, (uid+gid)/2 as avg from user where name="bin";

select name,uid,gid, uid+gid as sum, (uid+gid)/2 as avg from user;

模糊查询 like

where 字段名 like '表达式';% 表示零个或多个字符_ 表示 一个字符

select id, name from user where name like '%';

select name from user where name like '%a%';select name from user where name like '_'；

select name from user where name like '%_';

select name from user where name like '%__';

正则匹配

. ^ $ [ ] * where 字段名 regexp '正则表达式'；

insert into user(name) values("yaya9"),("6yaya"),("ya5ya"),("y7aya");

select name from user where name regexp '[0-9]';

select name from user where name regexp '^[0-9]';select name,uid from user where uid regexp '....';select name,uid from user where uid regexp '^....$'select name,uid from user where name regexp 'a.b';

select name,uid from user where name regexp '^r.t$';

统计函数（对字段的值做统计）

sum(字段)求和avg(字段)求平均值max(字段)求最大值min(字段) 求最小值count(字段) 统计个数

select avg(uid) from user where sex="girl";

select avg(uid) ,sum(uid) from user;select min(uid),max(uid) from user;select count(id) from user;select count(name) from user;select count(name),count(id) from user;select count(name) from user where uid >1000;

查询结果分组

sql查询 group by 字段名；select shell from user where uid<=100 ;

select shell from user where uid<=100 group by shell;

select distinct shell from user where uid<=100;

查询结果排序

sql查询 order by 字段名 asc/desc;

select uid,name from user where uid >=10 and uid<=100;

select uid,name from user where uid >=10 and uid<=100 order by uid;

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

查询结果限制显示记录行数

sql查询 limit 数字；显示查询结果的前几行；select id,name,uid,shell from user where id<=10 limit 1;select id,name,uid,shell from user where id<=10 limit 3;

sql查询 limit 数字1，数字2；显示指定范围内的行

数字1 表示起始行 第1行的编号是 0数字2 表示总行数

select id,name,uid,shell from user where id<=10；

select id,name,uid,shell from user where id<=10 limit 2,3;select id,name,uid,shell from user where id<=10 limit 4,5;select id,name,uid,shell from user where id<=10 limit 1,1;select id,name,uid,shell from user where id<=10 limit 2;

查询表记录的语法格式：

select 字段名列表 from 库.表 where 匹配条件；

条件匹配：

数值比较 = != > >= < <= 字符比较 = != 逻辑比较 and or !范围内匹配 between...and \ in \ not in 空 is null非空 is not null模糊查询 like '表达式' % _ 正则匹配 regexp '正则表达式' . ^ $ []

数学计算 + - / % ( )统计函数 sum() avg() max() min() count() 不显示字段的重复值 distinct 字段名查询分组 group by 字段名查询排序 order by 字段名 asc/desc限制显示查询记录的行数 limit 数字；limit 数字1，数字2； limit 0,1limit 2,2++++++++++++++++++++++++++++++++++++++++++++3.4 更新表记录字段的值 （条件和查询时的一样）批量修改：update 库.表 set 字段名=值,字段名="值"；

只修改复合条件记录字段的值：

update 库.表 set 字段名=值,字段名="值" where 条件；

update studb.user set s_year=1995 ;

update studb.user set s_year=1990 where name="root";update studb.user set uid=uid+5 where uid<10;update studb.user set uid=null where name="bin";update studb.user set name="" where uid=5;++++++++++++++++++++++++++++++3.5 删除表记录 (条件和查询时的一样）删除全部表记录 delete from 库.表；

删除指定的记录

delete from 库.表 where 条件；delete from studb.user where uid is null;+++++++++++++++++++++++++++++++++++++where嵌套:把内层查询结果作为外查询的查询条件select 字段名列表 from 库.表 where 条件 (select 字段名列表 from 库.表 where 条件);

alter table user add liunxsys float(5,2) default 60 after name;

update user set liunxsys=90 where uid=11;update user set liunxsys=13 where uid=20;update user set liunxsys=29 where uid=32;

select avg(liunxsys) from user where uid>=10 and uid<=50;

select name ,liunxsys from user where uid>=10 and uid<=50 and liunxsys < avg(liunxsys) ;

select name ,liunxsys from user where uid>=10 and uid<=50 and liunxsys < (select avg(liunxsys) from user where uid>=10 and uid<=50);

select name ,liunxsys from user where liunxsys < (select avg(liunxsys) from user);

select name from studb.user where name in (select user from mysql.user where host="localhost" and user="root");

stuinfo: name iphone class

addrinfo: name addr select name from stuinfo where name in (select name from addrinfo where addr="beijing");select name from stuinfo where name not in (select name from addrinfo where name="zhsan" and addr="beijing");++++++++++++++++++++++++++++++++++++++++++++++++复制表 ： 命令格式 create table 库.表 sql查询；功能 1 快速创建新表 2 备份表

create table studb.user2 select * from studb.user;

create table studb.user3 select * from studb.user order by uid desc limit 5;

create database db4;

create table db4.user5 select * from studb.user where 1 = 2;key值没有 要自己添加++++++++++++++++++++++++++++++++++++++++++++++对象 ： 学生 安娜唯一标识学号缴费表 110班级表 110就业表 110

多表 查询 -----SQL命令格式

select 字段名列表 from 表名列表; （迪卡尔集）

select 字段名列表 from 表名列表 where 条件；只显示与条件匹配的记录。

create database db4;

create table db4.t1 select name,uid,shell from studb.user limit 3;

create table db4.t2 select name,uid,homedir from studb.user limit 5;

select from db4.t1; select from db4.t2;

use db4;

select * from t1,t2; 3x5=15select t1.name,t1.uid,t2.homedir from t1,t2; 3x5=15

select t1.name,t1.uid,t2.homedir from t1,t2 where t1.name = t2.name;

select t1.*,t2.homedir from t1,t2 where t1.name = t2.name and t1.uid = t2.uid;

连接查询

左连接查询 select 字段名列表 from 表A left join 表B on 条件；

右连接查询

select 字段名列表 from 表A right join 表B on 条件；

create table db4.t3 select name,uid,shell from studb.user limit 3;

create table db4.t4 select name,uid,shell from studb.user limit 5;

select from db4.t3; select from db4.t4;

select * from t3 left join t4 on t3.uid = t4.uid;

select * from t3 right join t4 on t3.uid = t4.uid;

mysql> select t3.shell from t3 left join t4 on t3.uid = t4.uid

-> group by shell;+++++++++++++++++++++++++++++++++++++++++++视图 存储过程 触发器