MySQL常用命令

1、索引分类
   1、普通索引
   2、唯一索引
   3、主键索引
   4、外键索引
2、普通索引(index)
   1、使用规则
      1、一个表中可以有多个index字段
      2、字段的值可以有重复,也可以为NULL值
      3、经常把做查询条件的字段设置为index字段
      4、index字段的key标志为: MUL
   2、创建
      1、创建表时创建index
         create table t1(
         ... ...,
         ... ...,
         index(id),
         index(name));
      2、在已有表中添加索引字段
         1、语法格式
            create index 索引名 on 表名(字段名);
            # 索引名一般和字段名一样
   3、查看
      1、desc 表名;  ->查看KEY标志为 MUL
      2、show index from 表名G;
   4、删除
      drop index 索引名 on 表名;
      注意:
         删除普通索引只能一个一个删除
3、唯一索引(unique)
   1、使用规则
      1、一个表中可以有多个 unique 字段
      2、unique字段的值不允许重复,可以为空值NULL
      3、unique的KEY标志是 UNI
   2、创建(基本等同index创建)
      1、创建表时创建
         unique(字段名),
         unique(字段名)
      2、已有表中创建
         create unique index 索引名 on 表名(字段名);
      3、查看、删除唯一索引
         desc 表名;
         show index from 表名;
         drop index 索引名 on 表名;
4、主键索引(primary key) && 自增长属性(auto_increment)
   1、使用规则
      1、一个表中只能有一个主键字段
      2、对应字段的值不允许重复 且 不能为空值NULL
      3、主键字段的KEY标志为 PRI
      4、把表中能够唯一标识一条记录的字段设置为主键,通常把表中记录编号的字段设置为主键
   2、创建主键(PRI)
      1、创建表时创建
         1、字段名 数据类型 primary key auto_increment,
         2、
            id int auto_increment,
            ... ...,            # 设置自增长起始值
            primary key(id))auto_increment=10000;
      2、删除主键
         1、先删除自增长属性(modify)
            alter table 表名 modify id int;
         2、删除主键
            alter table 表名 drop primary key;
      3、在已有表中添加主键
         alter table 表名 add primary key(字段名);
5、外键
   1、定义 
      让当前表字段的值在另一个表的范围内选择
   2、语法格式
      foreign key(参考字段名)
      references 被参考表名(被参考字段名)
      on delete 级联动作
      on update 级联动作
   3、案例
      表1、缴费信息表(财务)
         学号  姓名    班级   缴费金额
            1   唐伯虎  AID01   28000
            2   点秋香  AID01   20000

      表2、学生信息表(班主任)
         学号  姓名   缴费金额
           1   唐伯虎  28000
            2   点秋香  20000

      1、创建缴费信息表
         create table jftab(
         id int primary key,
         name char(15),
         class char(5),
         money int
         )default charset=utf8;

         insert into jftab values
         (1,"唐伯虎","AID01",28000),
         (2,"点秋香","AID01",20000),
         (3,"祝枝山","AID01",22000);
      2、创建学生信息表(从表)
         create table bjtab(
         stu_id int,
         name char(15),
         money int,
         foreign key(stu_id) references jftab(id)
         on delete cascade
         on update cascade
         );
   4、级联动作
      1、cascade :数据级联更新
         当主表删除记录 或者 更新被参考字段的值时,从表会级联更新
      2、restrict 默认
         1、当删除主表记录时,如果从表中有相关联记录则不允许主表删除
         2、更新同理
      3、set null
         1、当主表删除记录时,从表中相关联记录的参考字段值自动设置为NULL
         2、更新同理
      4、no action
         on delete no action on update no action
         同 restrict,都是立即检查外键限制        
   5、删除外键
      alter table 表名 drop foreign key 外键名;
      1、外键名的查看方式
         show create table 表名;
   6、已有表中添加外键
      ## 会受到表中原有数据的限制
      alter table 表名 add foreign key(参考字段名)
      references 被参考表名(被参考字段名)
      on delete 级联动作
      on update 级联动作;
   7、外键使用规则
      1、两张表被参考字段和参考字段数据类型要一致
      2、被参考字段必须是 key 的一种,通常是 primary key
6、数据导入
   1、作用:把文件系统的内容导入到数据库中
   2、语法
      load data infile "文件名"
      into table 表名
      fields terminated by "分隔符"
      lines terminated by "n"
   3、示例
      把 /etc/passwd 文件中的内容导入到db2库下的userinfo表
      tarena :  x  :  1000 : 1000 :
      用户名  密码    UID号  GID号
      tarena,,, : /home/tarena : /bin/bash
      用户描述    用户主目录     登录权限
                                 /bin/false
                                              /usr/sbin/nologin
   4、操作步骤
      1、在数据库中创建对应的表
         create table userinfo(
         username char(20),
         password char(1),
         uid int,
         gid int,
         comment varchar(50),
         homedir varchar(50),
         shell varchar(50)
         );
      2、将要导入的文件拷贝到数据库的默认搜索路径中
         1、查看数据库的默认搜索路径
            show variables like "secure_file_priv";
            /var/lib/mysql-files
         2、Linux命令行输入:
            sudo cp /etc/passwd  /var/lib/mysql-files/
      3、执行数据导入语句
         load data infile "/var/lib/mysql-files/passwd"
         into table userinfo
         fields terminated by ":"
         lines terminated by "n";
   5、练习:将AID1709.csv文件导入到数据库中
      # csv文件单元格之间以 , 分隔

      /var/lib/mysql-files/AID1709.csv
      ls -l AID1709.csv
      rw-------
      chmod 666 AID1709.csv

      1、在数据库中创建对应的表
         id  姓名  成绩  手机号  班级
         create table scoretab(
         id int,
         name varchar(20),
         score float(5,2),
         phone char(11),
         class char(7)
         )default charset=utf8;
      2、把导入的文件复制到数据库的默认搜索路径中
         cp   源文件   目标路径
         cp  /home/tarena/AID1709.csv  /var/lib/mysql-flies/
         ######## 用 TAB 键 补齐路径 #######
      3、执行数据导入语句
         load data infile "/var/lib/mysql-files/AID1709.csv"
         into table scoretab
         fields terminated by ","
         lines terminated by "n";

         # 修改文件权限 chmod 666 AID1709.csv
7、数据导出
   1、作用
      将数据库表中的记录保存到系统文件里
   2、语法格式
      select ... from 表名
      into outfile "文件名"
      fields terminated by "分隔符"
      lines terminated by "n";
   3、把userinfo表中的username、password和uid导出到文件user.txt
      select username,password,uid from userinfo
      into outfile "/var/lib/mysql-files/user.txt"
      fields terminated by ","
      lines terminated by "n";

      1、sudo -i
      2、cd /var/lib/mysql-files/
      3、cat user.txt
   4、注意
      1、导出的内容由SQL查询语句决定
      2、执行导出命令时路径必须指定对应的数据库搜索路径
8、表的复制
   1、语法格式
      create table 表名 select 查询命令;
   2、示例
      1、复制userinfo表中的全部记录,userinfo2
         create table userinfo2 select * from userinfo;
      2、复制userinfo表中username,password,uid三个字段的第2-10条记录,userinfo3
         create table userinfo3 select username,password,uid from userinfo limit 1,9;
   3、复制表结构
      create table 表名 select 查询命令 where false;
   4、注意
      复制表的时候不会把原有表的 key 属性复制过来
9、嵌套查询(子查询)
   1、定义
      把内层的查询结果作为外层的查询条件
   2、示例
      1、把uid的值小于 uid 平均值的用户名和uid号显示出来
         select username,uid from userinfo
         where uid < (select avg(uid) from userinfo);
10、连接查询
   1、内连接
      1、定义
         从表中删除与其他被连接的表中没有匹配到的行
      2、语法格式
         select 字段名列表 from 表1 
         inner join 表2 on 条件 inner join 表3 on 条件;
      3、示例
         1、显示省市的详细信息
            select sheng.s_name,city.c_name from sheng
            inner join city on sheng.s_id=city.cfather_id;
         2、显示省市县详细信息
            select sheng.s_name,city.c_name,xian.x_name from sheng
            inner join city on sheng.s_id=city.cfather_id
            inner join xian on city.c_id=xian.xfather_id;     
   2、外连接
      1、左连接
         1、定义
            以左表为主显示查询结果
         2、语法格式
            select 字段名列表 from 表1
            left join 表2 on 条件;
         3、示例
            1、以省表为主显示省市详细信息
               select sheng.s_name,city.c_name from sheng
               left join city on sheng.s_id=city.cfather_id;
            2、显示省市区详细信息,要求县全部显示
               select sheng.s_name,city.c_name,xian.x_name from sheng left join city 
               on sheng.s_id=city.cfather_id
               right join xian on city.c_id=xian.xfather_id;
            3、显示省市区详细信息,要求 市 全部显示
               select sheng.s_name,city.c_name,xian.x_name from sheng
               right join city on sheng.s_id=city.cfather_id
               left join xian on city.c_id=xian.xfather_id;
            #### 结果集 ####
      2、右连接
         用法同左连接,以右表为主显示查询结果
11、多表查询
   1、select 字段名列表 from 表名列表;  # 笛卡尔积
   2、select 字段名列表 from 表名列表 where 条件;
      等同于 内连接 inner join

  4.修改表结构

 

  2.HAVING

  HAVING支持所有WHERE操作符。它与WHERE最重要的区别是,HAVING对GROUP BY分组后的数据进行过滤,而where在GROUP BY分组前组织新表时进行过滤。

  用例:

-- GROUP BY 与 HAVINNG
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
-- GROUP BY 与 ORDER BY
SELECT order_num, COUNT(*) AS items FROM orderitems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

图片 1

  2.日期和时间处理函数

  注:日期和时间函数根据

 函数  说明
 NOW()、SYSDATE()、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP 获取当前日期和时间
 CURDATE(), CURRENT_DATE 获取当前日期
 CURTIME(), CURRENT_TIME 获取当前时间
 DATE、YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND、MICROSECOND 获取指定日期和时间的日期、年、季度、月、周、日、小时、分钟、秒、毫秒数
 WEEKOFYEAR、DAYOFYEAR、DAYOFMONTH、DAYOFWEEK、LAST_DAY 获取指定日期和时间的年周索引、年天索引、月天索引、周天索引,最后一天的日期
MONTHNAME、 DAYNAME 获取指定日期和时间的英文月名、英文天名
DATE_ADD、DATE_SUB 指定日期按指定参数进行加减运算
PERIOD_ADD、PERIOD_DIFF 指定日期加、减多少个月
TIMEDIFF 指定日期和时间相差多少个时间
TIMESTAMPDIFF 指定日期/时间或日期时间的差值
TO_DAYS、FROM_DAYS 日期和月数的相互转换函数
TIME_TO_SEC、SEC_TO_TIME 时间和秒数的相互转换函数
STR_TO_DATE、DATE_FORMAT 字符串/日期时间格式转换成新的格式
TIME_FORMAT 时间格式转换你成新的格式
MAKEDATE、MAKETIME 拼凑日期/时间
UNIX_TIMESTAMP、FROM_UNIXTIME 日期时间和unix时间戳的相互转化

   用例:

-- 获取当前日期和时间,日期指的是年月日,时间指的是时分秒
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP;
-- 分别获取当前日期和时间
SELECT CURDATE(), CURRENT_DATE, CURTIME(), CURRENT_TIME;
-- 分别获取日期时间、年、季度、月、周、日、时、分、秒
SELECT DATE(SYSDATE()), YEAR(SYSDATE()), QUARTER(SYSDATE()), MONTH(SYSDATE()), WEEK(SYSDATE()), DAY(SYSDATE()), HOUR(SYSDATE()), MINUTE(SYSDATE()), SECOND(SYSDATE()), MICROSECOND(SYSDATE());
-- 获取指定索引
SELECT WEEKOFYEAR(SYSDATE()), DAYOFYEAR(SYSDATE()), DAYOFMONTH(SYSDATE()), DAYOFWEEK(SYSDATE()), LAST_DAY(SYSDATE());
-- 获取月和周的英文名称
SELECT MONTHNAME(SYSDATE()), DAYNAME(SYSDATE());

-- DATE加,第一个参数是指定的日期和时间,第二个参数是间隔和单位
SELECT DATE_ADD(now(), INTERVAL 1 YEAR), DATE_ADD(now(), INTERVAL 2 MONTH), DATE_ADD(now(), INTERVAL 1000 SECOND);
-- DATE减,与DATE加参数相同
SELECT DATE_SUB(now(), INTERVAL 1 YEAR), DATE_SUB(now(), INTERVAL 2 MONTH), DATE_SUB(now(), INTERVAL 1000 SECOND);
-- 日期的加减运算
SELECT PERIOD_ADD(201808, 2), PERIOD_ADD(1808, 2),PERIOD_ADD(DATE_FORMAT(SYSDATE(), '%Y%m'), 2), PERIOD_DIFF(201808, 201004), PERIOD_DIFF(1808, 1004); 
-- 时间差计算
SELECT TIMEDIFF('2018-08-06', '2018-08-5');-- 不支持日期
SELECT TIMEDIFF('19:00:00', '17:00:00'), TIMEDIFF('2018-08-6 9:30:30', '2018-08-5 17:00:00');
-- 更便捷的日期/时间差值计算,第一个参数是要计算的字段,其值为第三个日期时间减去第二个日期时间
SELECT TIMESTAMPDIFF(DAY, '2018-08-5 17:00:00', '2018-08-8 9:30:30'), TIMESTAMPDIFF(DAY, '2018-08-5', '2018-08-8');
SELECT TIMESTAMPDIFF(SECOND, '17:00:00', '19:30:30');-- 不支持单独时间计算
-- 日期和天数的相互转换
SELECT TO_DAYS(SYSDATE()), TO_DAYS('2018-8-8'), FROM_DAYS(737279);
-- 时间和秒数的相互转换
SELECT TIME_TO_SEC(SYSDATE()), TIME_TO_SEC('12:00:00'), SEC_TO_TIME(43200);
-- 字符串格式化;字符串格式化成日期只能要按照字符串的写法改写成标准日期时间字符串
SELECT STR_TO_DATE('2018.08.6 9:30:30', '%Y.%m.%d %H:%i:%s');
-- 日期时间字符串可以随便更改或获取字段
SELECT DATE_FORMAT('2018-08-06 09:30:30', '%Y%m');-- 获取年月的组合字符串
SELECT DATE_FORMAT('2018-08-06 09:30:30', '%H%i%s');-- 获取时分秒的组合字符串
SELECT DATE_FORMAT(SYSDATE(), '%Y年%m月%d日 %H时哈哈%i分嘿嘿%d秒呵呵');-- 重新格式化
-- 时间格式化只能格式化时间
SELECT TIME_FORMAT('2018-08-06 09:30:30', '%Y年%m月%d日 %H时%i分%d秒');
-- 只对'09:30:30'进行格式化,日期全部为00
SELECT TIME_FORMAT('09:30:30', '%H时%i分%d秒');
-- MAKEDATE根据数字组合成日期(以天数换算),MAKETIME根据数字组合成时间
SELECT MAKEDATE(2018, 9);-- 结果是'2018-01-09'而不是'2018-09-01'
SELECT MAKEDATE(2018, 220);-- 结果是'2018-08-08'
SELECT MAKETIME(19,30,30);-- 与日期相反,支持三个参数拼接而不支持两个参数换算
-- 日期时间和unix时间的相互转换
SELECT UNIX_TIMESTAMP(), FROM_UNIXTIME(UNIX_TIMESTAMP());

3.INSERT INTO ... SELECT 语句

  要插入的语句是从其他表中查询出来的。

  图片 2

  图片 3

       注意:数据类型得相同或者可以隐式转换!不然会报错!

 

  1.GROUP BY

  - GROUP BY子句可以包含任意数目的列。

  - GROUP BY会在最后规定的分组上进行汇总。

  - GROUP BY子句列出的每个列都必须是检索列或有效的表达式(但不能是聚合函数)。

  - 除聚合函数外,SELECT语句中的每个列都必须在GROUP BY子句中给出。

  - 如果分组列中具有NULL值,则NULL将作为一个分组返回;如果列中有多行NULL值,它们将分为一组。

  - GROUP BY子句必须出现在WHERE子句字后,ORDER BY子句之前。

  一般在使用GROUP BY子句时,应该也给出ORDER BY子句,以保证数据正确排序。

1.基本INSERT语句,单行插入

  如果没有列出列,则使一一对应。

图片 4

 

  2.约束条件

  约束是为了保证数据的完整性和一致性,约束类型包括:

 键名  类型
PRIMARY KEY 主键约束
UNIQUE KEY 唯一约束
NOT NULL 非空约束
UNSIGNED 无符号约束
DEFAULT 默认约束
FOREIGN KEY 外键约束

 

2.多行插入

图片 5

  1.查询关键字 SELECT FROM

-- 查询单列
SELECT prod_name FROM products;
-- 查询多列
SELECT prod_id, prod_name, prod_price FROM products;
-- 查询所有列
SELECT * FROM products;

  1、查询数据

  常见数据库

  商业数据库:甲骨文的Oracle、IBM的DB2、微软的Access和SQL Server。开源数据库:PostgreSQL、MySQL。

四、MySQL查询操作

   3.创建表及约束条件

  1.插入数据 INSERT INTO

-- 插入一条新的数据
/* INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('10006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL); */
-- 按表列字段的顺序插入数据时,列字段可省略
INSERT INTO customers VALUES('10006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);
-- 将一张表插入到原来的表
/* INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)  SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email FROM cust_new WHERE cust_id NOT IN (SELECT cust_id FROM customers); */
-- 复制表
CREATE TABLE custcopy AS SELECT * FROM customers;

  注意:

  - 任何SELECT选项和子句都可以使用,包括WHERE和GROUP BY。

  - 可以联结多个表执行插入数据操作。

  - 不管从多少个表中检索数据,数据都只能插入到单个表中。

  1.创建表

-- 创建表语法:CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,...);
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), salary FLOAT(8,2) UNSIGNED);
-- 查看表结构
DESC users;
SHOW COLUMNS FROM users;
SHOW CREATE TABLE users;

本文由ca88手机版登录发布于亚洲城ca88手机版官网,转载请注明出处:MySQL常用命令

TAG标签: ca88手机版登录
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。