- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE database_name; - 使用mysqladmin創(chuàng)建數(shù)據(jù)庫
mysqladmin -u root -p create database_name; - Note: 使用普通用戶需要特定的權限來創(chuàng)建數(shù)據(jù)庫或著刪除數(shù)據(jù)庫
- 刪除數(shù)據(jù)庫
drop database database_name; - 使用mysqladmin刪除數(shù)據(jù)庫
mysqladmin -u root -p drop database_name;
- 選中數(shù)據(jù)庫
use database_name;
- 創(chuàng)建數(shù)據(jù)庫表
CREATE TABLE table_name (column_name column_type);
CREATE TABLE IF NOT EXISTS `user` (
`id` INT UNSIGNED PRIMARY_KEY AUTO_INCREMENT,
`desc` VARCHAR(255) NOT NULL,
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 刪除數(shù)據(jù)庫表
drop table table_name;
drop table user;
- 插入數(shù)據(jù)
INSERT INTO table_name (field1, field2, field3...) VALUES(value1, value2, value3...);
INSERT INTO user (username password)
VALUES('TONY', '123'),
('TOM', '1234');
- 查詢數(shù)據(jù)
SELECT columns_name ... FROM table_name...
SELECT username FROM user;
- where字句
SELECT field1, ... FROM table_name, ... WHERE condition1 [AND [OR]] condition2....;
SELECT username FROM user where id = 1;
- UPDATE字句
UPDATE table_name SET field=new-value, filed1=new-value;
UPDATE user SET username='runserver';
- DELETE語句
DELETE FROM table_name [WHERE Clause];
DELETE FROM user WHERE username='runserver';
- LIKE字句
SELECT field1,... FROM table_name WHERE field LIKE condition [AND [OR]] field2 = 'do';
SELECT * FROM user WHERE username LIKE '%ver';
- UNION操作符
SELECT field....
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT field....
FROM tables
[WHERE condition];
SELECT country FROM websites
UNION
SELECT country FROM apps
ORDER BY country;
SELECT country FROM websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
SELECT country, name FROM websites
WHERE country='cn'
UNION ALL
SELECT country, app_name FROM apps
WHERE country = 'cn'
ORDER BY country;
- 排序
SELECT field... FROM talbe1, table2...
ORDER BY field1, [field2...] [ASC [DESC]]
SELECT * FROM user ORDER BY id asc;
- GROUP BY語句
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
SELECT name, COUNT(*) FROM user GROUP BY id;
SELECT name SUM(score) as score_number
FROM user GROUP BY name WITH ROLLUP;
SELECT coalesce(name, 'total'), SUM(socre) as sum FROM user
GROUP BY name WITH ROLLUP;
Note：WITH ROLLUP可以實現(xiàn)在分組統(tǒng)計數(shù)據(jù)基礎上在進行相同的統(tǒng)計
coalesce()可以設置一個取代null的名稱
今天先弄到這
