//建数据库,名字叫java202303
CREATE DATABASE java202303
-- 列出所有的数据库,表
SHOW DATABASES;
SHOW TABLES;
//删除这个数据库
DROP DATABASE java202303
//删除一个表
DROP TABLE `student`;
//创建一个表,该表至少要有一个列,表的名字记得加符号 `名字`, 然后是小括号,然后列的名字记得加符号`列的名字`,然后声明变量的数据类型,中间的列记得加逗号,最后一个列不用加分号,但是小括号外要加分号; 另外,没有String类型,只有char(数字)这种类。
CREATE TABLE student(
`id` INT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(10),
`age` INT,
`gender` CHAR(1)
);
INT AUTO_INCREMENT 自动增长,需跟同primary key 和 unique key一起使用
PRIMARY KEY ,主键,不能重复,不能为空,唯一确定一条记录,
UNIQUE KEY 也是不能重复,但可以为空
VAR CHAR(10) , 使用varchar(10)最大支持是10个字符,但是实际长度就是输入字符长度。
CHAR(10) , char(10)不管输入的是多少都会占10个字符。
-- 修改列
//增加列
ALTER TABLE student
ADD COLUMN age INT //在表中增加一个列age,不能说接着创建有好几列的大表
//删除列
ALTER TABLE student
DROP COLUMN address
//修改列的数据类型
ALTER TABLE student
CHANGE address CHAR(20)
//修改列的名字,改名字的同时一定要加上列的数据类型
ALTER TABLE student
CHANGE address dizhi VARCHAR(20)
-- 清空数据
TRUNCATE TABLE student;
-- 删除表中所有数据
DELETE FROM student;
DELETE FROM student WHERE id = 1;
DELETE FROM student WHERE `name` = '周';
-- 查询所有数据
SELECT * FROM student;
-- 一.插入数据 ,对属性的擦作符号是:value
-- 1.插入一条元素的部分属性
(1) . INSERT INTO student (`id`,`name`,`age`,`gender`)VALUES(1,'周',45,'男');
这一条已经插入完成了,即使里面有一个数据是空,没有,也不能继续插入了,只能是更新数据
-- 2.插入一条完整的数据
(1) INSERT INTO student
(id,`name`,age,gender,address) VALUES(6,'李',45,'男','济南');
(2). INSERT INTO
student VALUES(7,'戴',45,'男','临沂');
-- 3.插入好几条完整的数据
(1). INSERT INTO student
VALUES(8,'刘涛',32,'男','山西'),(9,'贾阳',42,'男','济宁');
-- 二.更新修改数据, 对属性的操作符号是:set 和 =
所有人年龄+1: UPDATE student SET age = age + 1;
对某个人的地址更改为北京:UPDATE student SET address = '北京' WHERE `name` = '周';
UPDATE student SET address = '杭州' WHERE `name` = '张三';
-- 三.查询数据
通过as起别名,总数count(age),
-- 1.查询指定列:
SELECT id FROM student;
SELECT id FROM student WHERE `name`='周华浩';
SELECT age AS '年龄' FROM student;
-- 2.查询时候去掉重复的数据DISTINCT
SELECT DISTINCT gender FROM student;
-- 3.查询数据的时候添加常量列,
SELECT id,`name`,age AS '年龄' FROM student
-- 4.条件查询
SELECT * FROM student WHERE age = 25;
SELECT * FROM student WHERE address = '青岛';
-- 5.逻辑条件
SELECT * FROM student WHERE `name` = '张三' OR address = '青岛';
SELECT * FROM student WHERE gender = '女' OR address = '济南';
-- 6.比较运算: < > <= >=
SELECT * FROM student WHERE address != '北京';
SELECT * FROM student WHERE address != '青岛';
-- 7.查询某项为空的数据
控制NULL: IS NULL , IS NOT NULL
SELECT * FROM student WHERE address IS NULL;
-- 8.聚合查询
SELECT SUM(age)AS '总年龄' FROM student;
SELECT AVG(age) AS '年龄的平均值' FROM student;
SELECT MAX(age) AS '年龄的最大值' FROM student;
SELECT MIN(age) AS '年龄的最小值' FROM student;
-- 9. 对数据排序 asc升序(默认) desc 降序,排序在最后面,年龄 age ASC,名字 id DESC
SELECT * FROM student ORDER BY age;
SELECT * FROM student ORDER BY age DESC;
-- 10.多个条件排序
SELECT * FROM student ORDER BY age,id DESC;
SELECT * FROM student ORDER BY age ASC;
-- 11.分组查询,例如将男女分开,察看各有多少人,select后面的属性必然和最后一个根据分组的属性相同
SELECT gender,COUNT(*) FROM student GROUP BY gender;
SELECT gender AS '性别',COUNT(*) AS '人数'FROM student GROUP BY gender;
-- 12.这里条件筛选用的HAVING
SELECT address , COUNT(*) FROM student GROUP BY address HAVING COUNT(*)>0;
SELECT age , COUNT(*) FROM student GROUP BY address HAVING COUNT(*)>0;
SELECT age , COUNT(*) FROM student GROUP BY age HAVING COUNT(*)>0;
-- 等值连接
SELECT * FROM student,banji WHERE student.banji_id = banji.id;
SELECT * FROM student,banji WHERE student.banji_id = banji.id;
-- 内连接
SELECT * FROM student AS s INNER JOIN banji AS b ON s.banji_id = b.id; -- on是条件,等价于where
SELECT * FROM student JOIN banji ON student.banji_id = banji.id;
SELECT * FROM student ,banji WHERE student.banji_id = banji.id;
SELECT * FROM student s, banji b, banji_course bc,course c
WHERE s.banji_id = b.id AND b.id = bc.banji_id AND bc.course_id = c.id;
-- 模糊查询
SELECT * FROM student WHERE `name` LIKE '张%';-- 以张开头
SELECT * FROM student WHERE `name` LIKE '张_';-- 以张开头,而且名字是两个字
SELECT * FROM student WHERE `name` LIKE '%张%'; -- 名字里面只要有张就可以
-- 如果要查找的字符中包含“%”或“_”,“ ’”,则只要对他们进行转义就可以:
-- %ab\%cd%’ //这里要找的是: 包含 ab%cd 字符的字符
-- 取出价格最高的前三名商品,用降序
SELECT * FROM goods ORDER BY shop_price DESC LIMIT 0,3;
-- limit从0开始,偏移3个,在这里也可以直接写3
-- 取出点击量第三名到第五名的商品,用降序
SELECT * FROM goods ORDER BY click_count DESC LIMIT 2,3;
"=":要求子查询只有一个结果。 "in":子查询可以有多个结果
SELECT * FROM student WHERE id = 5;
SELECT * FROM student WHERE age IN(1,2);
笛卡尔积:查询所得的结果行数是两张表行数的乘积
SELECT * FROM student,banji;
等值连接:两边相等。
SELECT * FROM student,banji
WHERE student.banji_id = banji.id ;