-- 创建数据库
CREATE DATABASE book;
-- 删除数据库
DROP DATABASE book;
-- 创建表
CREATE TABLE t_student(
id INT PRIMARY KEY AUTO_INCREMENT,-- 编号AUTO_INCREMENT自动编号
studentName VARCHAR(20), -- 学生姓名
sex VARCHAR(20),-- 性别
birthday DATE,-- 生日
tel VARCHAR(20) -- 最后一个数据不用,
);
-- 删除表
DROP TABLE t_student;
-- 添加数据记录
INSERT INTO t_student(studentName,sex,birthday,tel)
VALUES('张三','男','1989-10-10','18108026384');
INSERT INTO t_student(studentName,sex,birthday,tel)
VALUES('李四','男','1988-10-10','18108026584');
INSERT INTO t_student(studentName,sex,birthday,tel)
VALUES('王五','男','1991-10-10','18108026389');
INSERT INTO t_student(studentName,sex,birthday,tel)
VALUES('赵六','男','1989-10-10','18108926384');
-- 添加多条记录
INSERT INTO t_student(studentName,sex,birthday,tel)
('张三','男','1989-10-10','18108026384'),
('李四','男','1988-10-10','18108026584'),
('王五','男','1991-10-10','18108026389'),
('赵六','男','1989-10-10','18108926384'),
-- 删除
DELETE FROM t_student WHERE studentName = '张三';
DELETE FROM t_student WHERE studentName LIKE '张%';
DELETE FROM t_student WHERE studentName LIKE '%三%';
DELETE FROM t_student WHERE sex LIKE '%男%';
DELETE FROM t_student WHERE id = 3;
-- 修改属性
UPDATE t_student SET tel = '18108026384' WHERE studentName = '张三';
-- 修改多个属性
UPDATE t_student SET tel = '110',birthday = '1989-01-11' WHERE studentName = '张三';
UPDATE t_student SET tel = '18108026384',sex = '男' WHERE studentName = '张三';
-- 查询所有的特定属性
SELECT *FROM t_student WHERE sex = '男';
-- 姓张的用户
SELECT *FROM t_student WHERE studentName LIKE '%张%';
-- 两个字的名字
SELECT * FROM t_student WHERE studentName LIKE '王_';
-- 电信用户
SELECT *FROM t_student WHERE tel LIKE '18%';
-- 姓王的男性
SELECT *FROM t_student WHERE studentName LIKE '王%' AND sex = '男';
-- 90后
SELECT *FROM t_student WHERE birthday >= '1990-01-01' AND birthday <= '1999-12-31';
-- 所有移动用户
SELECT *FROM t_student WHERE tel LIKE '13%' OR tel LIKE '15%';
-- 查询第三条到第五条记录,
-- 2代表从第几条开始,第一条是0
-- 3表示显示几条
SELECT *FROM t_student LIMIT 2,3;
-- 按年龄排序asc 升序,desc降序,默认升序
SELECT *FROM t_student ORDER BY birthday DESC;
-- 最小年龄
SELECT * FROM t_student ORDER BY birthday DESC LIMIT 0,1 ;
-- 添加没有电话的学生
INSERT INTO t_student(studentName,sex,birthday)
VALUES ('呵呵','男','1898-4-4');
-- 查询没有电话的学生
SELECT *FROM t_student WHERE tel IS NULL;
-- 查询id我3.5.8的学生
SELECT *FROM t_student WHERE id IN (3,5,8);
-- 查询所有性别,DISTINCT剔除重复记录
SELECT DISTINCT sex FROM t_student;
-- 查询表中所有的数据
SELECT * FROM t_student; -- *表示查询所有的列
数据库基础
最新推荐文章于 2016-07-31 14:42:42 发布