欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Mysql学习笔记_1

程序员文章站 2022-06-30 19:17:28
Linux作为操作系统,Apache 或Nginx作为 Web 服务器,MySQL 作为数据库,PHP/Perl/Python作为服务器端脚本解释器。这四个软件都是免费或开源软件软件,因此使用这种方式除开人工成本就可以建立起一个稳定、免费的网站系统,称为“LAMP“或“LNMP”组合。 一、以cmd ......

Linux作为操作系统,Apache 或Nginx作为 Web 服务器,MySQL 作为数据库,PHP/Perl/Python作为服务器端脚本解释器。这四个软件都是免费或开源软件软件,因此使用这种方式除开人工成本就可以建立起一个稳定、免费的网站系统,称为“LAMP“或“LNMP”组合。

一、以cmd命令使用mysql的基础步骤 DOS系统下清屏的命令:cls)

基础步骤:

1)mysql -u root -p回车    //进入数据库

(2)password ........           //输入密码

(3)show databases;       //查看数据库

(4)\T   路径........\文件名.sql;  //连接文本记录,使用该文件存储操作信息

(5)use 库名 ;  //选数据库

(6)create tables //建表

 

二、mysql操作命令

1.连接

  本地连接: mysql -u root -p

  明文连接: mysql -u root -p密码

  远程连接: mysql -h  ip地址 -u 账号 -p 

2.库的操作命令

  (1)查看所有数据库:                show  databases;

  (2)创建数据库:                   create database 库名;

  (3)删除数据库:                     drop database 库名;

  (4)查看当前使用的数据库:                select  database();

    (5)查看建库语句          show create database 数据库名; 

  (6)查看当前数据库存在的数据表        show tables; 

注意:MySQL数据库中命令不区分大小写。在Windows下,数据库名称也是不区分大小写的,但在Linux下,数据库名称严格区分大小写

 

3.表的操作命令  

  1.创建数据表
        
      create table [if not exists] 表名(
              字段名1  字段类型,
              字段名2  字段类型,
              字段名3  字段类型
              )engine=innodb default charset=utf8;

  (1)[if not exists]如果该表不存在则创建

  (2)engine  =  innodb 设置表引擎

  (3)default  charset=utf8 设置表的字符集

  (4)每个建表语句的字段必须用逗号分隔,最后一句逗号不能有

 

  2.查看建表语句      show create table '表名';

  3.查看表结构     desc 表名   或   desc 表名/G(竖表);

  4.删除表      drop  table  表名;

 

附:常用约束:

1)主键约束:primary key --非空且唯一,一张表就定义一个主键

2)主键自增长:auto_increment

3)非空:not  null --不允许插入null值,可定义多个非空约束

4)唯一性:unique  --不允许重复,但可以为空,可定义多个唯一约束(例如:账户名)

5)默认值:default  默认值  --指定缺省值,在没有添加值得情况下使用default后指定的默认值

6)零填充:zerofill 并不是限制插入的长度,仅是显示时的位数使用0

7)无符号: unsiged

8)默认值:default (例:default('2'))如果没有插入数据,则取默认值

9)注释内容: comment 建表语句中的注释

10)外键:foreign key(列名)     references 外键表名(外键列名); 

注意:有外键时,创建表,先创建父表,再创建子表;插入数据,先插入父表,再插入子表;删除数据,先删除子表中的数据,再删除父表中的数据。

 

 

4.增删改查(数据操作)

(1)增(添加数据)

            ① insert into 表名 (字段1,字段2……) values (值1,值2……);   //建议使用 

            ② insert into 表名 values (值1,值2……),(值1,值2……);      //一次性插入多条数据

            ③ insert into 表名 (字段1,字段2……) values (值1,值2……),(值1,值2……);      //一次性插入多条数据

            ④ insert into 表名 values (值1,值2...);

            ⑤ insert into 表名 set 字段1=值1,字段2=值2...;

              注意:
                1.值和字段名要一一对应,否则会报错
                2.写入的值一定要和数据类型相匹配

(2)删 (删除数据

             delete from 表名 where  条件;

             truncate 表名;

            
        注意:删除数据的时候,一定要加上where条件,否则会删除所有的数据

 

1.删除表中的某一列。

 

mysql>alter table 表名 drop <column> 列名; //column可省略

附:drop、truncate、delete的区别?

  a) drop和truncate是DDL,而delete是DML。

  b) truncate和delete只删除数据,不会删除表的结构,而drop会把数据和表结构都删除。

  c) delete可以带where有条件的删除,可以回滚(rollback),但删除速度较truncate较慢。而truncate则不可以删除特定的数据,也不可以回滚(rollback),但删除速度比delete快。

 

建议:小心使用drop和truncate,尤其在数据没有备份的情况下。

 

(3)改(修改数据)

             update 表名 set 要修改的字段=新的字段...... where  条件

        注意:修改数据的时候,一定要加上where条件,否则会修改所有的数据

 

1、修改表中的某一列。

 

a)修改表中某一列的列名(同时可修改列的数据类型)

mysql>alter table 表名 change 原列名 新列名 列数据类型;

 

b)修改表中某一列的数据类型

mysql>alter table 表名 modify 列名 列新的数据类型;

 

 2.向表中添加新的列

mysql>alter table 表名 add <column> 列名 列的数据类型 [<列的完整性约束>];//column可省略

 

3、删除表中的某一列。

mysql>alter table 表名 drop <column> 列名;  //column可省略

 

4.修改表的编码字符集。  alter table 表名 character set 字符集;

5.修改表的名称      alter table 原表名 rename to 新表名; 或   rename table 原表名 to 新表名;

 

             

 

(4)查(查找数据)

            select * from 表名 where 字段名=字段值;  

              select 字段1,字段2... from 表名 where 字段名=字段值; 

             select *(所有字段) from 表;

             select 字段1,字段2.. from 表;

 

1. 过滤表中重复的数据(关键字distinct)

select distinct 列名 from 表名;   //返回该字段下的所有字段值,若有相同字段值,则只返回一个。    

2、排序(关键字order by)

mysql>select * from 表名 order by 列名 asc;       //升序(默认),asc可省略不写。
mysql>select * from 表名 order by 列名 desc;     //降序

例:按照学生表(student)中学生成绩(grade)的由高到低顺序输出学生的学号(sno)、姓名(sname)和成绩(grade).

mysql>select sno,sname,grade from student order by grade desc;

3、分页查询(关键字limit)。

mysql>select * from 表名 limit (pageNo-1)*pagesize,pagesize; //    pageNo-->要查询的页数,pageSize-->每页显示的记录数

例:查询dept表中第一页(每页2条数据)的记录。

mysql>select * from 表名 limit 0,2; 

 

4、模糊查询(关键字like)

mysql>select * from 表名 where 列名 like ' % ';  

 “%”代表任意字符;
    “_”代表单个字符;
例:select * frome t_student where stuName like ‘张三”;
       select * frome t_student where stuName like ‘张三%”;
       select * frome t_student where stuName like ‘%张三%”;//含有张三的任意字符
       select * frome t_student where stuName like ‘张三_”

例:查询学生表(student)中姓刘的学生的信息。

mysql>select * from student where sname like '刘%';

例:查询学生表中姓名第二个字为阳的学生信息。

mysql>select * from student where sname like '_阳%';  //_指代一个字符

 

5、范围查询(关键字between .. and .. , in())

mysql>select * from 表名 where 列名  between 字段值(小) and 字段值(大);//包含两端的字段值

mysql>select * from 表名 where 列名 in (字段值1,字段值2,......);

带and的多条件查询:
select 字段1,字段2…frome 表名 where 条件表达式1 and 条件表达式2 [and 条件表达式n]
例:select * frome t_student where gradeName=’一年级’ and age=23;
 
带or的多条件查询
select 字段1,字段2…frome 表名 where 条件表达式1 or 条件表达式2 [or 条件表达式n]
例:select * frome t_student where gradeName=’一年级’ or age=23;//或者,条件只要满足一个
 
distinct去重复查询:select distinct 字段名 from 表名;
 
对查询结果排序order by:select 字段1,字段2…from 表名 order by 属性名 [asc|desc]
例:select * frome t_student order by age desc;//降序,从大到小
       select * frome t_student order by age asc;//升序,asc默认可以不写
 
 

 

例如:查询学生表(student)中语文成绩chinese在80~90分之间的所有学生信息(包含80和90)
mysql>select * from student where chinese between 80 and 90;

例如:查询学生表(student)中数学成绩math为60分,70分,80分和90分的所有学生信息。
mysql>select * from student where math in (60,70,80,90);

 

6、使用别名。

mysql>select 字段名 <as> 别名,字段名 别名,... from 表名;    //as可省略

例如:查询学生成绩表(sc)中学生姓名(sname)及总成绩,sname字段用姓名表示,所有科目得分总和用总分表示。
mysql>select sname as 姓名,chinese+math+english 总分 from sc;

 

7、分组查询(关键字group by)。

mysql>select 列名,count(列名) from 表名 group by 列名;

分组查询group by
group by 属性名 [having 条件表达式][with rollup]   单独使用(毫无意义,不能单独使用);
与group_concat()函数一起使用;
例:select gradeName,group_concat(stuName) from t_student group by gradeName;
 
与聚合函数一起使用;
例:select gradeName,count(stuName) from t_student group by gradeName;
 
与having一起使用(显示输出的结果);
例:select gradeName,count(stuName) from t_student group by gradeName having count(stuName)
 
与with rollup 一起使用(最后加入一个总和行);
例:select gradeName,group_concat(stuName) from t_student group by gradeName with rollup;
 

例如:查询每个部门的员工人数,根据部门编号(deptno)对员工进行分组,返回各部门员工人数。
mysql>select deptno,count(empno) from emp group by deptno;  //empno--员工编号,emp--员工表

having:对分组后的结果进行条件限制
例如:查询每个部门员工人数大于2的所有记录,返回人数大于2人的部门编号及其部门人数。
mysql>select deptno,count(empno) from emp group by deptno having count(empno)>2;

 

8、表连接查询(同时涉及多个表的查询称为连接查询,用来连接两个表的条件称为连接条件)。

A)内连接:join,inner join

B)外连接:left join,left outer join,right join,right outer join,union

C)交叉连接:cross join

内连接:  表1  join 表2  on  表1.字段名=表2.字段名;              //内连接查询(两张或以上的表连接起来查询需要的数据)

例:查询员工表(emp)中的员工号(empno)、员工姓名(empname)、部门号(deptno)和部门名称(deptname),部门名称字段在部门表(dept)中.

mysql> select empno,empname,emp.deptno,deptname from emp join dept on emp.deptno=dept.deptno;

外连接查询(两张或以上的表连接起来查询某张表的信息) 
1.左外连接:  表1  left join  表2    on    表1.列名 = 表2.列名; 
表1作为主表,主表中的所有记录都会输出,和从表匹配不上的字段用null进行补齐。

例:以员工表(emp)为主表实现上述查询。

mysql> select empno,empname,emp.deptno,deptname from emp left join dept on emp.deptno=dept.deptno;

2.右外连接:  表1  right join  表2   on   表1.列名=表2.列名;
表2为主表,主表中的所有记录都会输出,和从表匹配不上的字段用null进行补齐。

例:以部门表(dept)为主表实现上述查询。

mysql> select empno,empname,emp.deptno,deptname from emp right join dept on emp.deptno=dept.deptno;

 3.多条件连接查询

select * from t_book,t_bookType where t_book.bookTypeId=t_bookType.id and t_book.price>70;
子查询
1.带in关键字的子查询(一个查询语句的条件可能落在另一个select语句的查询结果中)
select * from t_book where bookType in(select id from t_bookType);
select * from t_book where bookType not in(select id from t_bookType);
 
2.带比较运算符的子查询(子查询可以使用比较运算符)
select * from t_book where price>=(select price from t_priceLevel where priceLevel=1);
 
3.带exists关键字的子查询(加入子查询查询到记录,则进行外层查询,否则,不执行外层查询)
select * from t_book where exists(select * from t_booktype);
select * from t_book where not exists(select * from t_booktype);
 
4.带any关键字的子查询(any关键字表示满足其中任一条件)
select * from t_book where price>= any(select price from t_priceLevel);
 
5.带all关键字的子查询(all关键字表示满足所有条件)
select * from t_book where price>= all(select price from t_priceLevel);
 
 
4.合并查询
 
1.union
使用union关键字是,数据库系统会将所有的查询结果合并到一起,然后去掉相同的记录;
select id from t_book union select id from t_bookType;
 
2.union all
使用union all,不会去除掉重复的记录;
select id from t_book union all select id from t_bookType;
 

9、表的复制。

mysql>create table 新表表名 select * from 被复制表表名; //新表的表结构和数据与原表相同。

mysql>create table 新表表名 select 列名1,列名2,... from 被复制表表名;  //可以通过在select查询中指定字段来限制出现在新表中的字段
mysql>create table 新表表名 select * from 被复制表表名 where 0=1; //通过使用select语句创建已存在表的空副本(即创建相同表结构,但不复制原表数据过来),并且返回一个空结果集。

10.  空值查询:select 字段1,字段2…frome 表名 where 字段  is[not] null; 

11、聚合函数。

a)返回指定列非空值的个数

mysql>select count(列名) from 表名;

例:查询学生表(student)中学生的总人数。

mysql>select count(sno) from student;  //根据学生学号计算学生个数,学号不为空且唯一


b)返回指定列的最值
mysql>select max(列名) from 表名;  //返回指定列的最大值

例:查询学生表(student)中年龄(sage)最大的学生姓名(sname)和年龄。

mysql>select sname,max(sage) from student; 
mysql>select min(列名) from 表名;   //返回指定列的最小值

例:查询学生成绩表(sc)中语文成绩(chinese) 最低的学生的学号(sno)和语文成绩。

mysql>select sno,min(chinese) from sc;


c)返回指定列的平均值
mysql>select avg(列名) from 表名;


d)返回指定列的所有值之和
mysql>select sum(列名) from 表名;

5.退出客户端

  exit;  quit;    \q      Ctrl+c      (任意一个即可)

6.mysql的其他操作

1.数据库的导入导出。

导出数据库:mysqldump -u 用户名 -p  数据库名>数据库名.sql

导入数据库:source 数据库名.sql

2、显示帮助命令清单。

mysql> \h; //(加分号和不加分号结束都不影响命令执行)

3、清除当前输入的语句(命令)。

mysql> \c

4、查询当前安装的MySQL服务器的版本号。

mysql> select version();

5、查看MySQL服务器状态信息(包含版本号,下面两个命令执行效果相同)。

mysql> status;  //(加分号和不加分号结束都不影响命令执行)

mysql> \s

 

===========================================================================================================

MySQL、DB2、Oracle、SQL Server、MariaDB等等,由于MySQL已是Oracle旗下产品,可能会被闭源,不再免费,但MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,MariaDB是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。在存储引擎方面,MariaDB基于事务的Maria存储引擎,替换了MySQL的MyISAM;使用XtraDB来代替MySQL的InnoDB。