基础知识_数据库

T1        T2
修改var
          修改var
回滚

T1        T2
修改var    
          修改var
读取var

T1        T2
修改var    
          读取var
回滚

T1        T2
读取var    
          修改var
读取var

T1            T2
范围读取数据    
              插入一条数据
范围读取数据

SELECT ... LOCK IN SHARE MODE;  #添加共享锁
SELECT ... FOR UPDATE;	#添加排它锁

ps -ef | grep mysqld

cd /usr/bin
./mysqld_safe &

cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

mysql -u root -p

mysql>exit

#sql语句创建
CREATE DATABASE 库名;
#命令行直接创建
mysqladmin -u root -p create 库名

DROP DATABASE 库名;
#命令行删除
mysqladmin -u root -p drop 库名;

SHOW DATABASES;

USE 库名;

SHOW TABLE STATUS LIKE '表名';
ALTER TABLE 表名 ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `test_tbl`(
   `test_id` INT UNSIGNED AUTO_INCREMENT,
   `test_title` VARCHAR(100) NOT NULL,
   `test_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `test_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
============================================================
如果你不想字段为 **NULL** 可以设置字段的属性为 **NOT NULL**， 在操作数据库时如果输入该字段的数据为**NULL** ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

SHOW COLUMNS FROM test;

DROP TABLE 表名;

INSERT INTO test_tbl
(test_title,test_author,submission_date)
VALUES
("study sql","YIFEI",NOW());

#SELECT语法
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
============================================
查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

SELECT * FROM 表名;
SELECT column1,column2 FROM 表名;
SELECT DISTINCT column1,coulumn2 FROM 表名;

SELECT test_author,test_id FROM test_tbl
WHERE BINARY test_title='study php' AND 
	BINARY test_author='YIFEI'
=======================================
多个条件用AND、OR连接。
=检测是否相等，<>、!=检测不等，其他还有<=、>=。
BINARY表示区分大小写。
WHERE也可以用于DELETE 或者 UPDATE中。

UPDATE test_tbl 
SET test_title='study cpp',test_author='tws'
WHERE test_id=2;

DELETE FROM test_tbl WHERE test_id=3;

SELECT * FROM test_2 WHERE submission_date LIKE "%08-26";

SELECT submission_date FROM test_2
UNION all
SELECT submission_date FROM test_tbl
ORDER BY submission_date；
====================================
all是显示所有数据，默认为distinct去重。
两个select查询的列数应当相同。
ORDER BY submission_date是按照该列排序。

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

SELECT * FROM test_tbl ORDER BY name DESC,date ASC;
===========================================================
ASC升序排列，DESC为降序排列。
如果排序列为汉字，想要按照拼音排序:ORDER BY CONVERT(title using gbk);

SELECT name,COUNT(*) FROM employee_tbl GROUP BY name;
======================================================
按照名字分组。
count(*)统计每类的数量。

SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)
==================================================
coalesce(a,b,c)如果a!=null就使用a作为该列名称，如上如果name为空，就使用"总数"作列名。
ifnull(a,b)跟coalesce一样的效果。
WITH ROLLUP之后可以对分组结果再统计SUM(singin)。
SUM之外还有AVG、COUNT等。

SELECT a.runoob_id,a.runoob_title,b.runoob_author 
FROM runoob_2 AS a INNER JOIN runoob_tbl AS b 
ON BINARY a.runoob_author=b.runoob_author;

SELECT a.runoob_id,a.runoob_title,b.runoob_author 
FROM runoob_2 AS a,runoob_tbl AS b 
WHERE BINARY a.runoob_author=b.runoob_author;

SELECT a.runoob_id,a.runoob_title,b.runoob_author 
FROM runoob_2 AS a LEFT JOIN runoob_tbl AS b 
ON BINARY a.runoob_author=b.runoob_author;

SELECT a.runoob_id,a.runoob_title,b.runoob_author 
FROM runoob_2 AS a RIGHT JOIN runoob_tbl AS b 
ON BINARY a.runoob_author=b.runoob_author;

SELECT * FROM test WHERE name IS NULL;
=============================================
**IS NULL:** 当列的值是 NULL,此运算符返回 true。
**IS NOT NULL:** 当列的值不为 NULL, 运算符返回 true。
**<=>:** 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

SELECT * FROM runoob_2 WHERE runoob_author REGEXP '^y';

查看当前会话隔离级别:
select @@tx_isolation;
查看系统的隔离级别:
select @@global.tx_isolation;
设置会话的隔离级别，隔离级别由低到高设置依次为:
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
设置当前系统的隔离级别，隔离级别由低到高设置依次为:
set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
set global transaction isolation level serializable;

1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
3、保存点
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；

SHOW COLUMNS FROM testalter_tbl;

ALTER TABLE runoob_1 
ADD i INT AFTER runoob_id;

ALTER TABLE testalter_tbl 
DROP i;

ALTER TABLE testalter_tbl 
MODIFY c CHAR(10);
#修改字段位置
ALTER TABLE test
MODIFY name1 type1 AFTER name2;

ALTER TABLE testalter_tbl 
CHANGE i j BIGINT;

ALTER TABLE testalter_tbl 
ALTER i SET DEFAULT 1000;

ALTER TABLE testalter_tbl 
RENAME TO alter_tbl;

SHOW TABLE STATUS LIKE 'testalter_tbl'

ALTER TABLE testalter_tbl 
ENGINE = MYISAM;

ALTER TABLE talbename
DROP FOREIGN KEY keyname;

SHOW INDEX FROM table_name;

#添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
#添加唯一索引，创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
#添加全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

ALTER TABLE tbl_name DROP INDEX index_name;

#添加主键，首先确保主键列不为空。
ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
ALTER TABLE testalter_tbl DROP PRIMARY KEY;

CREATE INDEX indexName ON table_name (column_name);

DROP INDEX [indexName] ON table_name;

CREATE TABLE mytable(
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
);

CREATE TEMPORARY TABLE SalesSummary (
    product_name VARCHAR(50) NOT NULL,
    total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
    total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO SalesSummary
(product_name, total_sales, avg_unit_price, total_units_sold)
VALUES
('cucumber', 100.25, 90, 2);
SELECT * FROM SalesSummary;
DROP TABLE SalesSummary;
SELECT * FROM SalesSummary;

CREATE TEMPORARY TABLE aaa AS(
	SELECT * FROM runoob_1
    LIMIT 1,10000 
);

#输出该表的创建语句
SHOW CREATE TABLE runoob_tbl \G;
#修改表名，然后执行sql语句
CREATE TABLE `clone_tbl` (
    `runoob_id` int(11) NOT NULL auto_increment,
    `runoob_title` varchar(100) NOT NULL default '',
    `runoob_author` varchar(40) NOT NULL default '',
    `submission_date` date default NULL,
    PRIMARY KEY  (`runoob_id`),
    UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB;
#将原表数据插入到新表
INSERT INTO clone_tbl (runoob_id,runoob_title,runoob_author,submission_date)
SELECT runoob_id,runoob_title,runoob_author,submission_date FROM runoob_tbl;

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

CREATE TABLE new_name SELECT * FROM old_name;

CREATE TABLE newadmin(
    SELECT id, username AS uname, password AS pass FROM admin
)

CREATE TABLE tmp
SELECT last_name, first_name, sex FROM person_tbl  
GROUP BY (last_name, first_name, sex);

DROP TABLE person_tbl;

ALTER TABLE tmp RENAME TO person_tbl;

show variables like 'datadir';

SELECT * FROM table_name
INTO OUTFILE 'table.txt';

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

mysqldump -uroot -proot 数据库名 数据表名 > tmp.txt

#导出数据库
mysqldump -uroot -proot 数据库名 > tmp.txt
#导入数据库(库名要先手动创建)
mysql -u root -p database_name < tmp.txt

mysqldump -u root -p --all-databases > database_dump.txt

mysqldump -h other-host.com -P port -u root -p database_name > dump.txt

#导入数据库(库名要先手动创建)
mysql -u root -p database_name < tmp.txt

CREATE DATABASE abc;
USE abc;
SET NAMES utf8;
SOURCE /tmp/tmp.txt

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

# Write your MySQL query statement below
select id, 
max(case when month='Jan' then revenue end) as Jan_Revenue,
max(case when month='Feb' then revenue end) as Feb_Revenue,
max(case when month='Mar' then revenue end) as Mar_Revenue,
max(case when month='Apr' then revenue end) as Apr_Revenue,
max(case when month='May' then revenue end) as May_Revenue,
max(case when month='Jun' then revenue end) as Jun_Revenue,
max(case when month='Jul' then revenue end) as Jul_Revenue,
max(case when month='Aug' then revenue end) as Aug_Revenue,
max(case when month='Sep' then revenue end) as Sep_Revenue,
max(case when month='Oct' then revenue end) as Oct_Revenue,
max(case when month='Nov' then revenue end) as Nov_Revenue,
max(case when month='Dec' then revenue end) as Dec_Revenue
from department
group by id