1
2
3

sudo apt‐get install mysql‐server
sudo apt‐get install mysql‐client
sudo apt‐get install libmysqlclient‐dev

1
2

kirito@host:~$ mysqladmin ‐u用户名 ‐p旧密码 password 新密码
kirito@host:~$ mysqladmin ‐uroot ‐p111111 password 123456

1
2

mysql> update mysql.user set authentication_string=PASSWORD('newPwd'), plugin='mysql_native_password' where user='root';
mysql> flush privileges;

1
2

kirito@host:~$ mysql ‐h数据库服务器安装的主机 ‐P数据库端口 ‐u账户 ‐p密码
kirito@host:~$ mysql ‐h127.0.0.1 ‐P3306 ‐uroot ‐p123456

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 查看数据库服务器存在哪些数据库
SHOW  DATABASES;
# 使用指定的数据库
USE database_name;
# 查看指定的数据库中有哪些数据表
SHOW TABLES;

# 创建指定名称的数据库
CREATE DATABASE database_name;
# 删除数据库
DROP DATABASE database_name;

1
2
3
4
5
6
7

CREATE TABLE 表名(
   列名1    列的类型   [属性约束],
   列名2    列的类型   [属性约束],
   ....
   列名N    列的类型   [属性约束]   
);
# 如果名字和命令名冲突，此时使用反引号(`)括起来

1

DESC name;

1

SHOW CREATE TABLE name;

1

DROP TABLE name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 为所有表单项赋值，插入
CREATE TABLE test(id INT,_date DATE);
INSERT INTO test  VALUES(1,'2008‐12‐2');

# 选择赋值
CREATE TABLE  test4(id INT,_datetime DATETIME);
INSERT INTO test4(id, _datetime) VALUES(1, '1990‐02‐10');

/*
DATE可以通过CURDATE()来赋值当前的日期，
TIME可以通过CURTIME()来赋值当前的时间，
DATETIME与TIMESTAMP都可以通过函数NOW()来赋值当前的时间日期。
 */

1
2

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8; # or gbk
# 若想仅修改某一列的字符集 使用列操作添加约束属性 CHARACTER SET name

1

ALTER TABLE 表名 RENAME TO 新表名;

1
2
3

ALTER TABLE 表名 ADD 列名 列的类型 [属性约束];
ALTER TABLE 表名 ADD 列名 列的类型 [属性约束] FIRST; /*放在第一位*/
ALTER TABLE 表名 ADD 列名 列的类型 AFTER 列名; /*放在某字段后面*/

1

ALTER TABLE 表名 MODIFY 列名 列的类型 [属性约束];

1

ALTER TABLE 表名 DROP 列名;

1

ALTER TABLE 表名 CHANGE 列名 新_列名 列的类型 [属性约束]; # 列名不一定要新的，

1
2
3

# 设置/删除 DEFAULT
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
ALTER TABLE 表名 ALTER 列名 DROP  DEFAULT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 已有，设置
ALTER TABLE 表名 MODIFY 列名 INT NOT NULL DEFAULT 0;
# 创建表
mysql> CREATE TABLE stu2 (
  id INT DEFAULT NULL,
  sex CHAR(2) NOT NULL
)

# 效果 不能默认初始化
mysql> INSERT INTO stu2   VALUES();
ERROR 1364 (HY000): Field 'sex' doesn't have a default value

1
2
3
4
5
6
7
8
9

mysql> CREATE  TABLE stu3(id INT UNIQUE, sex CHAR(2));
mysql> CREATE TABLE stu3(id INT, sex CHAR(2), UNIQUE(id)); # 第二种写法

# 效果 插入重复值报错
mysql> INSERT INTO  stu3 VALUES(1,'f');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  stu3 VALUES(1,'f');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#已有表，修改，删除
ALTER TABLE table_name ADD primary key(column_name); /*必须是不存在重复值的列*/
ALTER TABLE table_name DROP primary key;
# 创建时设置
mysql> CREATE TABLE stu4(id INT PRIMARY KEY, sex CHAR(2));
mysql> CREATE TABLE stu4(id INT, sex CHAR(2), PRIMARY KEY(id)); # 第二种写法

# 效果 只能有一个列为主键，且没有重复
mysql> INSERT INTO stu4(id,sex) VALUES(1,'f');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO stu4(id,sex) VALUES(1,'f');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

ALTER TABLE 表名 CHANGE id id INT AUTO_INCREMENT; #必须先是索引
ALTER TABLE 表名 AUTO_INCREMENT=number; # 设置初始值
# 创建时
mysql> CREATE TABLE stu5(id INT AUTO_INCREMENT,sex CHAR(2)); # 错误，必须是索引
 ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
 
 mysql> CREATE TABLE stu5(id INT PRIMARY KEY AUTO_INCREMENT ,sex CHAR(2)); # 正确方式
 mysql> CREATE TABLE stu6(id INT UNIQUE KEY AUTO_INCREMENT ,sex CHAR(2)); # 正确方式
 
 # 效果  不提供初始值，自动递增分配， 提供重复的则报错

1
2

INSERT INTO 表名( 字段1, 字段2,... 字段N ) VALUES( value1, value2,... valueN );
# 字段（field）和值（value）都可以省略

1
2
3
4
5
6
7

DELETE FROM <表名> [WHERE <删除条件>]

# 栗子
delete from class where _id=9;
delete from class;
# 不跟条件表示删除整个表内容（不是整个表，框架还在），等价于
truncate table <表名>

1
2
3
4

UPDATE <表名> SET <列名=更新值> [WHERE <更新条件>]

# 栗子
update class SET score=100 WHERE name="郑同学";

1
2
3
4
5
6
7
8
9

SELECT column_name0,column_name1...
FROM table_name0,table_name1...
[WHERE clause]
[LIMIT N] [OFFSET M ]

# 查询表phonelist所有字段信息
SELECT * FROM phonelist;
# 只查询名字（name）为小张的所有字段信息
SELECT * FROM phonelist WHERE name="小张";

1
2
3
4
5
6
7

SELECT DISTINCT column_name0,column_name1...
FROM table_name0,table_name1...
[WHERE clause]
[LIMIT N] [OFFSET M ]

# 不出现重复打印班级里的同学名字
SELECT DISTINCT name FROM class;

1
2

# 吧所有同学学号乘以10再打印
SELECT id*10 FROM class;

1
2
3
4

# 查询班级所有name字段不为空的所有信息
SELECT * FROM class WHERE name IS NOT NULL;
# 计算 学分 = 成绩/10，成绩为NULL的按0分算
SELECT IFNULL(score,0)/10 FROM class;

1
2

# 查询所有信息，按id降序排序
SELECT * FROM phonelist ORDER BY id DESC;

1

SHOW INDEX FROM 表名;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE INDEX index_name ON 表名(列名); 			# index_name 自定义
CREATE UNIQUE INDEX index_name ON 表名(列名);   # unique 表示唯一索引
ALTER TABLE 表名 ADD INDEX index_name(列名);    # 列名有多个就是组合索引
CREATE TABLE `news`(                   	   
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL,
    `content` varchar(255) NULL,
    `time` varchar(20) NULL DEFAULT NULL,
    PRIMARY KEY (`id`), 
    INDEX index_name (title(255))
)# 创建表同时建立索引，PRIMARY KEY 其实也是索引

1

ALTER TABLE 表名 DROP INDEX index_name;

1

EXPLAIN select * from table_name where clause;

1
2
3
4
5

# 从第n行开始，打印m条数据，行数从0开始，可以不加offset，默认从0开始
SELECT * FROM 表名 LIMIT m OFFSET n;

# 另一种写法，从第n行开始打印m条
select * from 表名 limit n,m;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT LOWER("ABC"); # abc
SELECT UPPER("abc"); # ABC
SELECT CONCAT("china","is",UPPER("greate"));# china is GREATE

SELECT CHAR_LENGTH("abc"); # 3
SELECT LENGTH("china"); # 5
SELECT RPAD("abc",6,"xyz"); # abcxyz 
SELECT LPAD("abc",6,"X"); # XXXabc

SELECT  TRIM("  abc   ");

SELECT REPLACE("abc","b","BB"); # aBBc

SELECT SUBSTRING("abcchina",3); # cchina
SELECT SUBSTRING("abcchina",3,4); # cchi
SELECT SUBSTRING("abcchina",‐3); # ina

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT ABS(‐100); # 100
SELECT MOD(100,3); # 1

#一下都是对浮点型数字进行操作
SELECT FLOOR(1.23); # 1
SELECT CEIL(1.23); # 2

SELECT ROUND(3.145); # 3
SELECT ROUND(3.145,2);  # 3.15 保留俩位小数
SELECT TRUNCATE(3.14,1); # 3.1 截取小数点后1位

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT NOW(); # 2019-02-13 12:51:39
SELECT YEAR(NOW()); # 2019
SELECT MONTH(NOW()); # 2
SELECT DAY(NOW()); # 13
# HOUT MINUTE SECOND 用法同上

SELECT CURRENT_DATE(); # 2019-02-13  简写CURDATE()
SELECT CURRENT_TIME(); # 12:56:20  简写CURTIME()

SELECT LAST_DAY(NOW()); # 2019-02-28
SELECT LAST_DAY(str_to_date('2019-12-01 00:00:00','%Y-%m-%d %H:%i:%s')); # 2019-12-31

SELECT DATE_ADD(NOW(),INTERVAL 2 DAY); # 2019-02-15 13:02:01 
SELECT DATE_SUB(NOW(),INTERVAL 3 HOUR);

SELECT DATEDIFF(LAST_DAY(NOW()),NOW()); # 15 

1
2
3
4
5
6

SELECT FORMAT(235235.346326,2); # 小数点保留2位 235,235.35

SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p'); # Feb 13 2019 01:16 PM
SELECT DATE_FORMAT(NOW(),'%m-%d-%Y'); # 02-13-2019  

SELECT STR_TO_DATE('2019-12-01 00:00:00','%Y-%m-%d %H:%i:%s'); # 2019-12-01 00:00:00

1
2

# 计算班级 50分以上同学的分数最大差
SELECT MAX(score)‐MIN(score) FROM class WHERE;

1
2

# 统计60分以上，各个分数的人数
SELECT COUNT(*),score FROM class WHERE score>60 GROUP BY score;

1
2

## 统计60分以上，各个分数的人数, 并且只筛选相同分数的人数小于10人的分组
SELECT COUNT(*),score FROM class WHERE score>60 GROUP BY score HAVING count(*)<10;

1
2

# 这里给表 phonelist 起了别名 p，多表操作别名很方便
SELECT p.name  FROM phonelist p where p.id=2;

1

SELECT a.t1 b.msg FROM a,b WHERE a.id=b.id; #这里id就是连接条件，id相同才会被关联

1

ALTER TABLE `A` ADD FOREIGN KEY(`myid`) REFERENCES `B`(`id`);

1
2

# 查询分数高于班级平均分的同学的姓名
SELECT name FROM class WHERE score>(SELECT AVG(score) FROM class);

1
2
3
4

# 查询工资等于部门经理的员工信息.
SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE JOB='manager');
SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE JOB='manager');
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE JOB='manager');

1
2

# tmp是临时表别名
SELECT tmp.* FROM (SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) tmp WHERE tmp.avg_sal > 2000;

1
2
3

kirito@host:~$ mysqldump ‐u账户 ‐p密码 数据库名称>文件存储地址
# 栗子
kirito@host:~$ mysqldump ‐uroot ‐p123456 testDB> /home/kirito/testDB_bak.sql

1
2
3

kirito@host:~$ mysql ‐u账户 ‐p密码 数据库名称< 文件存储地址 （数据库要已存在）
# 栗子
kirito@host:~$ mysql ‐uroot ‐p123456 testDB< /home/kirito/_bak.sql

1
2
3
4

# 也可以进入数据库导入
CREATE DATABASE testdb;
USE testdb;
SOURCE /home/kirito/_bak.sql;

1
2
3
4
5
6

SELECT [DISTINCT] [聚合函数]
FROM
WHERE
GROUP BY
HAVING
ORDER BY

1
2
3
4
5
6
7
8

FROM
WHERE
GROUP BY   #从这里开始可以使用别名
聚合函数
HAVING
SELECT
DISTINCT
ORDER BY

1
2
3
4
5
6
7
8
9

BEGIN 	 # 开启一个事务
COMMIT 	 # 提交事务
ROLLBACK # 回滚事务

# 栗子
BEGIN;
SELECT * FROM class WHERE id=1;
UPDATE class SET score=100 WHERE id=1;
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

for update # 加锁  锁释放发生在回滚和提交。
# lock in share mode # 共享锁
/*
for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for
update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。
*/

# 栗子
BEGIN;
SELECT * FROM class WHERE id=1 FOR UPDATE; # 加锁
UPDATE class SET score=100 WHERE id=1;
COMMIT; # 释放

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

//a.cpp 简单示例
#include <stdio.h>
#include <mysql/mysql.h>

MYSQL mysql,*sock;  //连接要用到

int main(){
	const char * host = "127.0.0.1"; 	//主机名
	const char * user = "root";
	const char * passwd = "123456";
	const char * db = "zsh";     		//数据库名字
	unsigned int port = 3306; 			//不改动默认就是这个
	const char * unix_socket = NULL;
	unsigned long client_flag = 0; 		//一般0

	const char * i_query = "select * from phonelist"; //查询语句

	int ret;
	MYSQL_RES * result;
	MYSQL_ROW row;

	mysql_init(&mysql); 	//必须

	if((sock = mysql_real_connect(&mysql,host,user,passwd,db,port,unix_socket,client_flag)) == NULL){
		ret = mysql_errno(&mysql);
		printf("connect error,errno %d\n",ret);
		//fprintf(stderr,"%s\n",mysql_error(&mysql));
		exit(1);
	}else{
		fprintf(stderr,"connect success\n");
	}

	mysql_query(&mysql,"set names utf8");//在连接后使用，中文显示正常
    /*
      这一句等于
      SET character_set_client = utf8;
      SET character_set_results = utf8;
      SET character_set_connection = utf8; 
     */

	if(mysql_query(&mysql,i_query) != 0){
		fprintf(stderr,"query error\n");
		exit(1);
	}else{
		if((result = mysql_store_result(&mysql)) == NULL){
			fprintf(stderr,"save result error\n");
			exit(1);
		}else{

			int field_num = mysql_field_count(&mysql); //获得列数
			MYSQL_FIELD * fields = mysql_fetch_fields(result); //列名

			for(int i=0; i<field_num; i++){
				printf("%s \t",fields[i].name);
			}putchar('\n');

			while((row = mysql_fetch_row(result)) != NULL){
				for(int i=0; i<field_num; i++){
					printf("%s \t",row[i]);
				}putchar('\n');
			}

		}
	}

	mysql_free_result(result);
	mysql_close(sock);
	exit(EXIT_SUCCESS);
}