1、创建一个表,存在几个字段:id、name、start_time、end_time、age、status
CREATE TABLE test_abc(
id INT,
NAME VARCHAR(300),
start_time DATETIME,
end_time DATETIME,
age INT,
STATUS INT
)
2、创建主键id
ALTER TABLE test_abc ADD CONSTRAINT id PRIMARY KEY(id);
主键唯一且不能为空
或者在创建表的时候一同创建:
CREATE TABLE test_abc(
id INT primary key,
NAME VARCHAR(300),
start_time DATETIME,
end_time DATETIME,
age INT,
STATUS INT
)
如何创建另一个表test_ppp的外键user_id
CREATE TABLE test_ppp (
FOREIGN KEY(id) REFERENCES test_abc(id),
NAME VARCHAR(300),
creat_time DATETIME,
platfrom VARCHAR(300),
id INT PRIMARY KEY
)
两个表有主外键相关,才可以进行关联查询
3、插入一些数据:
INSERT INTO test_abc VALUES(12,'wer','20191206063413','20191208233509',38,1);
INSERT INTO test_abc VALUES(10,'asd','2019-01-23 12:34:20','2019-02-01 06:07:36',7,3);
INSERT INTO test_abc VALUES(11,'ppp','20191206063413','20191208233509',NULL,7);
批量插入多行:
INSERT INTO test_ppp VALUES (3,'yyu','2018-09-23 08:08:08','pc') , (2,'rtyu','2019-03-19 20:45:23','pc')
4、插入一列:
ALTER TABLE test_abc ADD COLUMN address VARCHAR(300);
5、删除某一列ip:
ALTER TABLE test_abc DROP ip;
6、修改某一条数据:
将name =wer,id = 12的一组数据中age修改为28:
update test_abc set age = 28 where name = 'wer' and id = 12;
7、删除数据:
将age = 67的一条数据删除:
delete from test_abc where age = 67;
8、SELECT * FROM test_abc WHERE age IS NULL;
null需要用is
9、ceil()向上取整:
SELECT CEIL(ta.mark),ta.name FROM test_abc ta WHERE ta.id = 11;
10、abs(): 取绝对值
floor(): 向下取整
sign(): 判断正负-------返回值为0、-1(负数)、1(正数)
trim (): 移除字符串两边空白
11、按时间升序排序,只要查询出前三条数据:
SELECT ta.* FROM test_abc ta ORDER BY start_time ASC LIMIT 0,3;
12、去重:
SELECT DISTINCT(ta.name),ta.id FROM test_abc ta ORDER BY start_time;
13、模糊查询:
一个“_”代表一个字符,且默认存在。
“%”不区分
SELECT ta.* FROM test_abc ta WHERE ta.name LIKE '舞__' ORDER BY start_time;
SELECT ta.* FROM test_abc ta WHERE ta.name LIKE '舞%' ORDER BY start_time;
SELECT ta.* FROM test_abc ta WHERE ta.name LIKE '%舞%' ORDER BY start_time;
查询结果均为:
SELECT ta.* FROM test_abc ta WHERE ta.name LIKE '舞_' ORDER BY start_time;
SELECT ta.* FROM test_abc ta WHERE ta.name LIKE '_舞_' ORDER BY start_time;
查询结果均为:空
14、创建视图:
CREATE VIEW test_view AS (SELECT ta.* FROM test_abc ta ORDER BY start_time ASC );
删除视图:
DROP VIEW IF EXISTS test_view;
DROP VIEW test_view;
15、两张表关联查询:
CREATE TABLE test_abc(
id INT PRIMARY KEY,
NAME VARCHAR(300),
start_time DATETIME,
end_time DATETIME,
age INT,
STATUS INT
)
CREATE TABLE test_ppp (
FOREIGN KEY(id) REFERENCES test_abc(id),
NAME VARCHAR(300),
creat_time DATETIME,
platfrom VARCHAR(300),
id INT PRIMARY KEY
)
查询出两张表中id都为1的人的名字:
SELECT tp.name,ta.name FROM test_ppp tp,test_abc ta WHERE ta.id = tp.id AND tp.id = 1 AND ta.id = 1;
16、内连接:
SELECT * FROM test_abc ta INNER JOIN test_ppp tp ON ta.id = tp.id;
查询出的是两张表id重合的部分
17、左连接:
SELECT * FROM test_abc ta LEFT JOIN test_ppp tp ON ta.start_time = tp.creat_time;
查询出的是:LEFT左边表test_abc的全部数据,以及LEFT右边表test_ppp中与左边表的重合数据,test_ppp表的其余字段全部为null
18、右连接:
SELECT * FROM test_abc ta RIGHT JOIN test_ppp tp ON ta.start_time = tp.creat_time;
查询出的是RIGHT 右边表test_ppp中的全部数据,以及RIGHT左边表test_abc中与右边表重合的数据,test_abc其余字段全部为null
19、关于数据导入:
这里以navicat为例,导入ww.txt文件:
(1)在ww.txt文件中插入数据,注意不是insert into,而是:
中间以TAB分隔。
(2)navicat选中表名,选择“导入”---> 选择txt格式
---->导入ww.txt文件
---->输入“源栏位”,选择主键(根据目标表test_abc来走,若存在主键就勾选)
---->开始
导入成功。