备注[1]
关于MySQL的安装 [2]
1. 管理MySQL
- 启动数据库
mysqld --console
- 关闭数据库
mysladmin -uroot shutdown
- 连接mysql服务器 (于mysql安装目录下的bin目录运行)
mysql -u root -p
Enter password: ******
- 新建用户
2. 数据库操作
- 创建数据库
create DATABASE database_name
- 删除数据库
drop database database_name
- 选择数据库
use database_name
3. 数据表操作
- 创建MySQL数据表
CREATE TABLE table_name (column_name cloumn_type)
例：CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Field Type Null KEY Default Extra runoob_id int(11) NO PRI NULL auto_increment runoob_title varchar(100) NO NULL runoob_author varchar(40) NO NULL submission_data date YES NULL
- 删除数据表
DROP TABLE table_name
- 插入数据
INSERT INTO table_name(field1, field2,...fieldN) VALUES (value1, value2,...valueN)
- 查询数据
SELECT column_name, column_name FROM table_name[WHERE Clause][LIMIT N][ OFFSET M]
查询数据表所有记录： select *from table_name
- 更新数据
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
例:UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3
- 删除数据
DELETE FROM table_name [WHERE Clause]
例：DELETE FROM runoob_tbl WHERE runoob_id=3
临时表
- 创建临时表
CREATE TEMRORARY TABLE table_name
- 删除临时表
DROP TABLE table_name
复制表
- 获取表的完整结构
SHOW CREATE TABLE table_name \G
- 拷贝数据表
INSERT INTO clone_table (field1, field2,...fieldN) SELECT field1, field2,...fieldN FROM table_name
4. MySQL语法
- WHERE子句
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]]] condition2......
例：SELECT *from runoob_tabl WHERE runoob_author='XXXX'
- LIKE子句
SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] field2 = 'somevalue'
例:SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM'
- UNION操作符
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]
- SELECT排序
SELECT field1, field2,...fieldN FROM table_name1, table_name2...ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
例:
SELECT * from runoob_tbl ORDER BY submission_date ASC;
- GROUP BY语句
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
- 正则表达式
SELECT field_name FROM table_name WHERE field_name PEGEXP ['正则表达式']
5. 多数据表协作
- 连接的使用
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录>
SELECT x.field1, x.field2,...x.fieldN, y.field1, y.filed2,...y.fieldN FROM table_name1 x INNER JOIN table_name2 y ON [coditions]
例:SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
- LEFT JOIN（左连接）： 获取左表所有记录，即使右表没有对应匹配的记录
SELECT x.field1, x.field2,...x.fieldN, y.field1, y.filed2,...y.fieldN FROM table_name1 x LEFT JOIN table_name2 y ON [coditions]
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录
SELECT x.field1, x.field2,...x.fieldN, y.field1, y.filed2,...y.fieldN FROM table_name1 x RIGHT JOIN table_name2 y ON [coditions]
- 事务(开始事务->提交事务/回滚)
begin
...
...
commit / rollback
- ALTER命令
- 删除表字段
ALTER TABLE table_name DROP field_name
- 添加表字段
ALTER TABLE table_name ADD field_name type_name
ALTER TABLE table_name ADD field1 type_name AFTER field0
- 修改表字段
ALTER TABLE table_name MODIFY field_name type_name
ALTER TABLE table_name CHANGE field_old_name, field_new_name type_name
- 修改字段默认值
ALTER TABLE table_name ALTER field_name SET EDFAULT value
- 修改表名
ALTER TABLE table_old_name RENAME TO table_new_name
- MySQL索引
普通索引
- 创建索引
CREATE INDEX indexName ON table_name (column_name)
- 修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
- 删除索引
DROP INDEX [indexName] ON mytable
唯一索引
- 创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
- 修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
显示索引信息
SHOW INDEX FROM table_name\G
Comments NOTHING