苏泽湛 发表于 2018-10-3 13:34:03

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    字段名    符号   &quot;字符串&quot;
  selectnamefrom userwhereshell=&quot;/bin/bash&quot;;
  selectnamefrom userwherename=&quot;daemon&quot;;
  selectname,shellfrom userwhereshell != &quot;/bin/bash&quot;;
  范围内匹配
  where   字段名between值1and值2;            在...之间
  where   字段名in(值列表);                               在....里
  where   字段名not   in(值列表);                     不在....里
  select name,uidfrom userwhereuid   between10 and 20;
  select name from userwherenamein(&quot;root&quot;,&quot;lucy&quot;,&quot;damon&quot;);
  select name,uid from userwhereuidin(100,500,1000,2001);
  select name from userwhere shellnotin(&quot;/bin/bash&quot;,&quot;/sbin/nologin&quot;);
  select name,shellfrom userwhere shellnotin(&quot;/bin/bash&quot;,&quot;/sbin/nologin&quot;);
  逻辑匹配 (多个查询条件时 使用)
  逻辑与and         条件都成立才可以
  逻辑或or            某一个条件成立就可以
  逻辑非!            取反
  selectnamefrom userwhere   uid=10andshell=&quot;/bin/bash&quot;;
  selectname,uid,shellfrom userwhere   uid=0andshell=&quot;/bin/bash&quot;;
  selectnamefrom userwherename=&quot;lucy&quot;orname=&quot;bob&quot;or    uid=1;
  selectname,uidfrom userwherename!=&quot;lucy&quot;;
  匹配空    isnull
  匹配非空   isnotnull

  select >
  select >
  select >  selectname ,shellfrom user where shell is notnull;
  insert into   user(id,name)values(61,&quot;&quot;),(62,null),(63,&quot;null&quot;);

  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]
查看完整版本: mysql三