mysql 基础

CREATE TABLE tutorials_tbl(
tutorial_id INT NOT NULL AUTO_INCREMENT,
tutorial_title VARCHAR(100) NOT NULL,
tutorial_author VARCHAR(40) NOT NULL,
submission_date DATE,
issue_sn INT not null default 999,
PRIMARY KEY ( tutorial_id )
);

CREATE TABLE person_tbl(
person_id int(11),
firstName varchar(100) NOT NULL default '',
lastName varchar(40) NOT NULL default '',
submission_date date default NULL
);

INSERT INTO tutorials_tbl
(tutorial_title, tutorial_author, submission_date)
VALUES
("Learn PHP", "John Poul", NOW());

SELECT tutorial_id, tutorial_title, submission_date
FROM tutorials_tbl;

SELECT * from tutorials_tbl
WHERE tutorial_author='Sanjay';

UPDATE tutorials_tbl
SET tutorial_title='Learning JAVA'
WHERE tutorial_id=3;

SELECT * from tutorials_tbl
WHERE tutorial_author LIKE '%jay';

SELECT * from tutorials_tbl
where tutorial_id > 7
ORDER BY tutorial_author ASC;

SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a, tcount_tbl b
WHERE a.tutorial_author = b.tutorial_author;

SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a LEFT JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author;

SELECT * from tutorials_tbl
WHERE tutorial_author regexp '^Sa';

SHOW COLUMNS FROM tutorials_tbl;
desc tutorials_tbl;

ALTER TABLE tutorials_tbl DROP tutorial_author;
ALTER TABLE tutorials_tbl add tutorial_author char(10);
ALTER TABLE tutorials_tbl add tutorial_id INT NOT NULL AUTO_INCREMENT;
ALTER TABLE tutorials_tbl CHANGE tutorial_author j BIGINT;

ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
ALTER TABLE testalter_tbl RENAME TO alter_tbl;

ALTER TABLE person_tbl ADD PRIMARY KEY (person_id);
ALTER TABLE person_tbl DROP PRIMARY KEY;

ALTER TABLE tutorials_tbl add ks2 INT NOT NULL AUTO_INCREMENT;
ALTER TABLE tutorials_tbl ADD PRIMARY KEY (ks);
ALTER TABLE person_tbl DROP PRIMARY KEY;

ALTER TABLE tutorials_tbl MODIFY tutorial_title char(20);

DELIMITER $$
DELIMITER ;

A database index is a data structure that improves the speed of operations in a table.

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

there can be only one auto column and it must be defined as a key.

CREATE TABLE testb_01(
person_id int NOT NULL AUTO_INCREMENT,
PRIMARY KEY ( person_id ),
per_num int,
per_no int NOT NULL default 0,
firstName varchar(100) NOT NULL default '',
lastName varchar(40) NOT NULL default '',
submission_date date default NULL
);

CREATE INDEX index_01 on testb_01(per_no);
CREATE UNIQUE INDEX index_02 on testb_01(per_num);
CREATE UNIQUE INDEX index_03 on testb_01(firstName,lastName);

desc testb_01;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| person_id | int(11) | NO | PRI | NULL | auto_increment |
| per_num | int(11) | YES | UNI | NULL | |
| per_no | int(11) | NO | MUL | 0 | |
| firstName | varchar(100) | NO | MUL | | |
| lastName | varchar(40) | NO | | | |
| submission_date | date | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+

show index from testb_01;
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Ca
rdinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+---------------+
| testb_01 | 0 | PRIMARY | 1 | person_id | A |
2 | NULL | NULL | | BTREE | | |
| testb_01 | 0 | index_03 | 1 | firstName | A |
2 | NULL | NULL | | BTREE | | |
| testb_01 | 0 | index_03 | 2 | lastName | A |
2 | NULL | NULL | | BTREE | | |
| testb_01 | 0 | index_02 | 1 | per_num | A |
2 | NULL | NULL | YES | BTREE | | |
| testb_01 | 1 | index_01 | 1 | per_no | A |
2 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+---------------+

CREATE UNIQUE INDEX index_04 on testb_01(submission_date);

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

ALTER TABLE testb_01 ADD i INT AFTER per_no;
ALTER TABLE testb_01 ADD j INT AFTER i;

ALTER TABLE testb_01 ADD INDEX index_05 (i);
ALTER TABLE testb_01 DROP INDEX index_05;

ALTER TABLE testb_01 ADD INDEX (j); // OK !
ALTER TABLE testb_01 DROP INDEX (j); // FAIL ?

"CREATE INDEX" 和 "ALTER TABLE ADD INDEX" 啥区别？
如何在 “create table” 中定义索引？

Temporary Tables:
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
);

DROP TABLE SalesSummary;

临时表不会在show tables的列表中显示。
temporary tables will only last as long as the session is alive.

Clone Tables：
step1: Get complete structure about table. (SHOW CREATE TABLE tbl_name)
step2: Rename this table and create another same structure table. (CREATE TABLE `clone_tbl`)
step3: using INSERT INTO... SELECT statement.

Handling Duplicates:

use a PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records.
"INSERT IGNORE INTO" and "REPLACE INTO"

CREATE TABLE ttTbl
(
numb INT NOT NULL,
name INT NOT NULL,
sex CHAR(10)
);

SELECT COUNT(*) as repetitions, numb, name from ttTbl
group by numb
HAVING repetitions > 1;

SELECT COUNT(*) as repetitions, numb, name from ttTbl
group by name;

SELECT name,DISTINCT numb
FROM ttTbl;

Removing Duplicates 1：
CREATE TABLE tmp
SELECT numb, name, sex FROM ttTbl
GROUP BY (numb);

DROP TABLE ttTbl;

ALTER TABLE tmp RENAME TO ttTbl;

Removing Duplicates 2：
ALTER IGNORE TABLE person_tbl
ADD PRIMARY KEY (numb);

create table + select 可以作为表格备份的方法吗？

转载于:https://www.cnblogs.com/askme/p/6340074.html