MySQL常用语句

1.Windows下配置MySQL的log

<记录MySQL执行过的SQL语句>

  找到my.ini文件，在[mysqld]下面添加：log=”MySQL.log” 『log文件保存在安装目录下』或者log=存放日志的路径/my.log『log文件保存在设置的目录下』

  修改后要重启数据库才能生效

  Linux下配置方法：修改/etc/my.cnf，找到[mysqld]，在下面添加：log=存放日志的路径/my.log

2.修改数据库的编码格式

  MySql默认编码是Latin1，不支持中文，想支持中文就要修改默认编码，需要修改my.ini文件

 [client]增加default-character-set=utf8

[mysql]增加default-character-set=utf8

[mysqld]增加：

character_set_server=utf8

init_connect='SET NAMES utf8'

 

 如果不设置数据库的编码格式，在使用java或者jmeter测试时，如果sql语句中包含中文，程序不会报错，但是查询结果为空。<该问题可通过1方式查看>

Note：表的字段用 ``(ESC键) {有中文时}包含起来，表的字段的值用 ’’ 包含起来{值为字符、中文时}

3命令行链接数据库

  CMD切换到mysql安装目录（一般是bin目录），输入命令：mysql -u root –p回车键，弹出密码输入界面，输入密码即可进入数据库

4.SQL语句执行顺序：

5.常用sql语句

查看版本：

SELECT VERSION(); 或者

SHOW VARIABLES LIKE "%version%";或者

SELECT @@version;

查询服务器当前数据库：

SHOW DATABASES;

查看数据库中的表

USE <数据库名>;

SHOW TABLES;

例如：USE test;

SHOW TABLES;

查看数据库的编码：

SHOW CREATE DATABASE <数据库名>;

例如：SHOW CREATE DATABASE test;

查看数据库表的编码：

SHOW CREATE TABLE <表名>;

例如：SHOW CREATE TABLE information;

查看表的结构

  DESCRIBE<表名>；

  例如：DESCRIBE information;

创建数据库时指定数据库的字符集：

CREATE DATABASE <数据库名>CHARACTER SET utf8;

例如：CREATE DATABASE learn CHARACTER SET utf8;

删除数据库：

DROP DATABASE <数据库名>;

例如：DROP DATABASE test;

创建数据表时指定数据表的编码格式：

例如：CREATE TABLE learn(

pname VARCHAR(45) NOT NULL,

pricae DOUBLE NOT NULL,

bookCount INT NOT NULL,

author VARCHAR(45) NOT NULL

)DEFAULT charset = utf8;

删除数据库的表：

DROP TABLE <表名>;

  例如：DROP TABLE information;

修改数据库的编码格式：

ALTER DATABASE <数据库名> CHARACTERSET utf8;

例如：ALTER DATABASE test CHARACTER SET utf8;

修改数据表格编码格式：

ALTER TABLE <表名> CHARACTERSET utf8;

例如：ALTER TABLE information CHARACTER SET utf8;

修改表字段的类型：

  ALTERTABLE <表名> MODIFY COLUMN <字段名> <要修改的类型>;

  例如：ALTER TABLE learn MODIFY COLUMN pname varchar(20);

修改字段名：(修改列名)

  ALTERTABLE <表名> CHANGE <旧字段名> <新字段名> 类型;

  例如：ALTER TABLE test CHANGE userid ID varchar(20);

表插入数据：

  INSERTINTO <表名> VALUES ('value1','value2',' value3',……);

  例如：INSERT INTO information VALUES ('胡歌','男','1985-08-25','大二','5班','四川省成都市武侯区武侯大道1703号','510811198508255563');

用文本方式向表插入数据：

  LOADDATA LOCAL INFILE "路径" INTOTABLE <表名>;

  例如：LOAD DATA LOCAL INFILE "E:/OtherFiles/sql.txt" INTO TABLEinformation;

清空表：

DELETE FROM <表名>;

例如：DELETE FROM information;

删除一条数据：

     DELETE FROM <表名> WHERE <条件>;

     例如：DELETE FROM test WHERE account = 'jojo';

复制表：

  CREATETABLE <新表名> SELECT * FROM <被复制表名>;

  例如：CREATE TABLE test1 SELECT * FROM test;

表重命名：

RENAME TABLE <新表名> TO <旧表名>;

例如：RENAME TABLE test001 TO test;

或者：ALTER TABLE <旧表名> RENAME <新表名>;

例如：ALTER TABLE test RENAME test001;

表添加字段：(添加列)

ALTER TABLE <表名> ADDCOLUMN <字段名> <字段类型> ;

例如：ALTER TABLE test ADD COLUMN class VARCHAR(10) NOT NULL;

删除字段：(删除列)

  ALTERTABLE <表名> DROP COLUMN <字段名>;

例如：ALTER TABLE test DROP COLUMN class;

更新表数据：

  UPDATE<表名> SET <字段名>=value WHERE <条件>;

  例如：UPDATE test SET `姓名`='罗伊人' WHERE userid=002;

插入语句：

  INSERT INTO <表名> VALUES(value1,value2,value3……);

  例如：INSERT INTO test VALUES('006','林拜','男','1974-5-27','大一','五班','510811199012189513');

6.查询语句

  SQL语句中关键字都是大写的（但是这不是必需的），并且用空格将他们划分出来

  例如：SELECT * FROM student01WHEREcno>4;

       或者select * from student01where cno>4;

1.数值类查询：

         SELECT * FROM studentWHERE mark>=60;

         查询谓词：

         {>、=、<、<>、!=、!>、!<、=>、=< }

         常用聚合函数：

函数名

函数注解

AVG(col)

返回指定列的平均值

COUNT(col)

返回指定列中非NULL值的个数

MIN(col)

返回指定列的最小值

MAX(col)

返回指定列的最大值

SUM(col)

返回指定列的所有值之和

GROUP_CONCAT(col)

返回由属于一组的列值连接组合而成的结果

2.字符串查询：

         SELECT * FROM student01WHERE dname='计算机';

SELECT * FROMstudent01 WHERE dname LIKE'%工程';

         SELECT* FROM student01 WHERE dname LIKE '%算%';

         SELECT* FROM student01 WHERE dname LIKE '电%';

3.查询日期型数据

         SELECT * FROM courseWHERE ctest='2006-7-10'

4.查询前n条记录：

         SELECT* FROM mystudent LIMIT 0,100;    //查询前100条数据

5.查询后n条记录：

         SELECT* FROM course ORDER BY cnoDESC LIMIT 2  //先按照cno降序排列，在去前两条数据，即为倒数2条记录

6. 查询从指定位置开始的n条记录

         SELECT* FROM course LIMIT 1,3;  //查询从第2条到第4条的数据

         注：数据的id是从0开始的

7.查询指定时间段的数据：

         SELECT* FROM course WHERE ctest BETWEEN '2006-7-01' AND'2006-7-30';

8.查询结果去重：

         SELECT DISTINCT<字段名> FROM <表名> WHERE <查询条件>;

         SELECTDISTINCT snameFROM student01;

9.查询结果排序：

         SELECT* FROM student01 ORDER BY cno DESC;   //按字段cno降序DESC排列

         SELECT* FROM student01 ORDER BY cno ASC;   //按字段cno升序ASC排列

         注：默认排序为ASC升序排列

10.分组查询：

         举例说明：在teacher表中，查询所有男教师、女教师的平均工资。

         分析：在一次查询中要同时得到二者的查询结果，就需要以性别为基准，将表中的所有数据分组，即男教师和女教师，同时分别对两组数据进行分析，即计算工资（SAL）列的平均值。

         SELECTtsex AS teacher,AVG(sal) as AVGSal FROM teacher GROUPBY tsex;

        

         注：AS是取别名的意思

11.自然链接：

         是一种特殊的等价连接。它将表中具有相同名称的列自动进行匹配，自然连接不必指定任何同等连接条件（多表查询）

         举例说明：在STUDENT表和TEACHER表中查询学生姓名（SNAME）、所在系（DNAME）、所修的本系教师所开设的课程的课程号（CNO）以及开课教师姓名（TNAME）。

         SELECTsname,student.dname,student.cno,tname FROM student,teacher WHERE

student.cno=teacher.cno;

12.自链接：

         自连接是指表与自身进行连接(需要用到表的别名)

         举例说明：查询student表中成绩中存在不及格课程的学生的姓名、所在系、所有的课程及成绩信息。

         SELECTs1.sname,s1.dname,s1.cno,s1.mark FROM student AS s1,student01 AS s2 WHERE

s1.cno=s2.cno AND s2.mark<60 ORDER BY sname;

        

13.内连接：

         内连接也称为等同连接。返回的结果是两个表中所有相匹配的数据，舍弃不匹配的数据；源表行必须满足ON字句中的搜索条件。

         举例说明：使用【11】的例子

         SELECTsname,student.dname,student.cno,tname FROM student INNERJOIN teacher

ON student.cno=teacher.cno;

14.左链接：

         左外连接生成的结果中除了包括匹配行外，还包括JOIN关键字（FROM字句中）左边表的不匹配行。左外连接实际上可以表示为：左外连接 =内连接 + 左边表中失配的元组。

         举例说明：左连接STUDENT表和COURSE表，查询所有同学的学号（SNO）、姓名（SNAME）、课程号（CNO）、课程名称（CNAME）、考试时间（CTEST）和成绩（MARK）。

         SELECTsno,sname,student.cno,cname,ctest,mark FROM student LEFTOUTER JOIN

course ONstudent.cno=course.cno;

         注：OUTER可以省略

15.右链接：

         右外连接生成的结果中除了包括匹配行外，还包括JOIN关键字（FROM字句中）右边表的不匹配行。右外连接实际上可以表示为：右外连接 =内连接 + 右边表中失配的元组。

         举例说明: 右连接STUDENT表和COURSE表，查询所有同学的学号（SNO）、姓名（SNAME）、课程号（CNO）、课程名称（CNAME）、考试时间（CTEST）和成绩（MARK）。

         SELECTsno,sname,student01.cno,cname,ctest,mark FROM student01 RIGHT OUTER JOIN

course ONstudent01.cno=course.cno;

         注：OUTER可以省略

16.交叉链接

         交叉连接返回的是被连接的两个表所有数据行的笛卡尔积，返回值的数据行数=第一个表中符合查询条件的数据行数*第二个表中符合查询条件的数据行数

  举例说明：交叉连接STUDENT表和COURSE表，查询所有同学的学号（SNO）、姓名（SNAME）、课程号（CNO）、考试时间（CTEST）和成绩（MARK）信息。

         SELECTsno,sname,student.cno,cname,ctest,mark FROM studentCROSSJOIN course

WHERE mark>60;

         注：使用关键字CROSS JOIN连接表时，得到的查询结果是两个表的笛卡尔积，所以不能使

用ON关键字