Mysql学习笔记_1

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。