1、插入数据 insert into users(name,age) values ('张三','23'); 添加一条张三,23的数据
2、删除一条数据delete from users where id=1; 删除id为1的一条数据
3、删除整个表的数据delete from users;
4、查询表所有数据select * from users; 展示占整个表所有列的数据
5、查询包含条件的数据,且展示该条数据的所有列select * from users where age = 11; 展示年龄为11的所有列数据
6、更改包含条件的对应数据的值 update users set age=10 where name = '张三'; 把张三年龄改为10
7、更改表里对应列的所有值update users set age=10; 把表里的年龄都改为10
INSERT INTO student(name,age) VALUES('张三','20') ;#插入一条数据
DELETE FROM student WHERE name='张三' ; #删除数据
DELETE FROM student ; #删除整个表数据
SELECT * FROM student ; #查询整个表所有列的数据
SELECT * FROM student WHERE name='张三' ; #查询姓名为张三的所有数据
UPDATE student SET age=30 WHERE name='张三' ; #把张三的年龄改为30
UPDATE student SET age=20 ; #把表中所有人的年龄都改为20
查询
/* 创建数据库 */
CREATE DATABASE `data1` DEFAULT CHARSET = utf8;
/* 展示所有数据库 */
SHOW DATABASES;
/* 展示所有表 */
SHOW TABLES;
/* 删除数据库 */
DROP DATABASE `data1`;
/* 创建表 */
CREATE TABLE `student` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID' ,
`name` varchar(100) DEFAULT NULL COMMENT '姓名' ,
`age` int(11) DEFAULT NULL COMMENT '年龄' ,
`create_time` datetime NOT NULL COMMENT '创建时间' ,
`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ,
PRIMARY KEY (`id`) ,
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT '学生表' ;
/* 添加一列 */
ALTER TABLE student
ADD COLUMN `remark` varchar(100) DEFAULT NULL COMMENT '备注' ;
/* 修改列名和列类型 */
ALTER TABLE student
CHANGE COLUMN `remark` `newremark` int(11) NOT NULL COMMENT '新备注' ;
/* 修改列类型 */
ALTER TABLE student
MODIFY COLUMN `newremark` varchar(100) ;
/* 删除列 */
ALTER TABLE student
DROP COLUMN `newremark` ;
/* 修改表名 */
ALTER TABLE student1
RENAME TO `student2` ;
DROP TABLE `student2` ; #删除表
INSERT INTO student(name,age) VALUES('张三','20') ;#插入一条数据
DELETE FROM student WHERE name='张三' ; #删除数据
DELETE FROM student ; #删除整个表数据
SELECT * FROM student ; #查询整个表所有列的数据
SELECT * FROM student WHERE name='张三' ; #查询姓名为张三的所有数据
UPDATE student SET age=30 WHERE name='张三' ; #把张三的年龄改为30
UPDATE student SET age=20 ; #把表中所有人的年龄都改为20
SELECT name FROM student ; #查询学生表中姓名一列
SELECT * FROM student ORDER BY age DESC ; #查询出学生表的所有列并以年龄降序排列
SELECT * FROM student ORDER BY age ASC ; #查询出学生表的所有列并以年龄升序排列
SELECT * FROM student ORDER BY age ASC , name DESC ; #查询出学生表的所有列并以年龄升序和姓名降序排列
SELECT name,age FROM student ORDER BY age ASC ; #查询出学生表的姓名和年龄列并以年龄升序排列
SELECT * FROM student LIMIT 2,4 ; #查询出表中从第2条数据后的4条数据
SELECT * FROM student LIMIT 10,15 ; #查询出表中从第10条数据后的15条数据
SELECT name,age FROM student ORDER BY age DESC LIMIT 3; #查询出年龄最大的3人的姓名和年龄列
SELECT DISTINCT gender from student; #去重,查询列表中性别一列的去重结果,gender性别
SELECT name,age FROM student WHERE age = 20 limit 3; #查询出年龄为20的3人
/* 等于= 大于> 小于< 不等于<> */
SELECT name,age FROM student WHERE name LIKE '张%'; #查询张开头的姓名和年龄列
SELECT name,age FROM student WHERE name LIKE '%张'; #查询张结尾的姓名和年龄列
SELECT name,age FROM student WHERE name LIKE '%张%'; #查询包含张的姓名和年龄列
SELECT name,age FROM student WHERE age BETWEEN 20 AND 30; #查询出年龄在20-30的姓名和年龄列
SELECT name,age FROM student WHERE age IN (20,30); #查询出年龄是20,30的姓名和年龄列
SELECT name,age,gender FROM student WHERE age = 30 and gender = '男'; #查询出年龄是30和性别为男的姓名、年龄和性别列
SELECT name,age,gender FROM student WHERE age = 30 or gender = '男'; #查询出年龄是30或者性别为男的姓名、年龄和性别列
SELECT name,age FROM student WHERE not age = 30; #查询出年龄不是30的姓名、年龄列
SELECT name,age,gender FROM student WHERE not (age = 30 and gender = '男'); #查询的结果过滤掉性别男且年龄等于30的姓名、年龄和性别列
SELECT name,age,gender FROM student WHERE not (age = 30 or gender = '男'); #查询的结果过滤掉性别男和年龄等于30的姓名、年龄和性别列
SELECT count(*) FROM student; #统计表中数据条数
SELECT count(age),age FROM student GROUP BY age; #统计各年龄的个数
SELECT count(age),age FROM student GROUP BY age HAVING count(age) > 20; #统计各年龄的个数且个数大于20的
SELECT avg(age) FROM student; #平均年龄
SELECT sum(age) FROM student; #年龄求和
SELECT min(age) FROM student; #最小年龄
SELECT max(age) FROM student; #最大年龄
SELECT avg(a.age) FROM (SELECT age FROM student ORDER BY age DESC LIMIT 10) as a; #求年龄最大的前10个的平均值
/* 左连接查询,显示表a所有,表b只显示符合条件的(不符合的部分显示null) */
SELECT table_a.id table_a.name table_b.name FROM table_a LEFT JOIN table_b ON table_a.id=table_b.id;
/* 右连接查询,显示表b所有,表a只显示符合条件的(不符合的部分显示null) */
SELECT table_a.id table_a.name table_b.name FROM table_a RIGHT JOIN table_b ON table_a.id=table_b.id;
/* 内连接查询,显示表a,表b符合条件的(交集) */
SELECT table_a.id table_a.name table_b.name FROM table_a INNER JOIN table_b ON table_a.id=table_b.id;
左连接,查询结果
右连接,查询结果
内连接,查询结果