MySQL基础命令（自练）

创建数据库
CREAT DATABASE TP;

DROP命令删除数据库
DROP DATABASE TP;

选择数据库
mysql -u root - p
mysql> use TP;

创建数据表
CREATE TABLE IF NOT EXISTS ‘tp_001’(
‘tp_id’ INT UNSIGNED AUTO_INCREMENT,
‘tp_name’ VARCHAR(200) NOT NULL,
‘tp_author’ VARCHAR(50) NOT NULL,
‘submission_date’ DATE,
PRIMARITY KEY(‘tp_id’)
)ENGING=InnoDB DEFAULT CHARSET=utf8;

删除数据表
DROP TABLE tp_001;

插入数据
INSERT INTO tp_001(tp_id,tp_name)
VALUES
(‘001’,‘kate’);

查询数据
SELECT * FROM tp_001 WHERE LIMIT 1,2;

where子句
SELECT * FROM tp_001 WHERE tp_id=1;

UPDATE更新
UPDATE tp_001 SET tp_name=‘手机’ WHERE tp_id=2;

DELETE语句
DELETE FROM tp_001 WHERE tp_id=3;

LIKE语句(模糊搜索)
SELECT * FROM tp_001 WHERE tp_name LIKE ‘*菲’;

UNION操作符
SELECT tp_name FROM tp_001 UNION SELECT tp_name FROM tp_002 ORDER BY tp_name;

排序(ORDER BY)
SELECT * FROM tp_001 ORDER BY submission_date des;

多表连接
内连接：SELECT s.tp_id,m.ap_num FROM tp_001 s INNER JOIN mp_001 m ON s.tp_id=m.ap_num;
左连接：SELECT s.tp_id,m.ap_num FROM tp_001 s LEFT JOIN mp_001 m ON s.tp_id=m.ap_num;
右连接：SELECT s.tp_id,m.ap_num FROM tp_001 s RIGHT JOIN mp_001 m ON s.tp_id=m.ap_nim;

NULL值处理
SELECT * FROM tp_001 WHERE tp_name=NULL;
SELECT * FROM tp_001 WHERE tp_id IS NOT NULL;

正则表达式
查找name字段中以’st’为开头的所有数据：
SELECT name FROM tp_001 WHERE name REGEXP ‘^st’;

查找name字段中以’ok’为结尾的所有数据：
SELECT name FROM tp_001 WHERE name REGEXP ‘$ok’;

查找name字段中包含’mar’字符串的所有数据：
SELECT name FROM tp_001 WHERE name REGEXP ‘mar’;

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：
SELECT name FROM tp_001 WHERE name REGEXP ‘