基本创建和查询练习
数据准备
- 创建数据库:
create database python_test_1 charset=utf8;
- 使用数据库:
use python_test_1;
- 创建students表
-- students表
CREATE TABLE students (
id int UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
name VARCHAR(20) DEFAULT '',
age TINYINT UNSIGNED DEFAULT 0,
height DECIMAL(5,2),
gender ENUM('男','女','中性','保密') DEFAULT '保密',
cls_id INT UNSIGNED DEFAULT 0,
is_delete BIT DEFAULT 0
);
-- classes表
CREATE TABLE calsses(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
name VARCHAR(30) NOT NULL
);
-- 向sutdents表中插入数据
INSERT INTO students VALUES
(0,'小明',18,180.00,2,1,0),
(0,'小悦悦',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1);
INSERT INTO classes VALUES
(0, 'python_01期'),
(0, 'python_02期'),
(0, 'python_03期');
查询练习
-- 使用 as 给字段取别名
SELECT name FROM students;
SELECT name as "姓名" FROM students;
-- select 表名.字段名 .... from 表名;
SELECT students.name from students;
-- 可以通过as给表起别名
-- select 别名.字段名 ..... from 表名 as 别名;
SELECT s.name FROM students as s;
-- 消除重复行(性别)
-- DISTINCT 字段
SELECT gender FROM students;
SELECT DISTINCT gender FROM students;
知识点:
as
可以为字段起别名as
可以为表起别名(用来区分有相同字段的不同表)select distinct
可以去重查询