Android基础知识-数据库基础

mac2024-01-24 40

转载

https://code.tutsplus.com/articles/sql-for-beginners–net-8200 https://code.tutsplus.com/articles/sql-for-beginners-part-2–net-8274 https://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships–net-8561

常用Sql

功能SQL语句说明创建数据库CREATE DATABASE my_first_db;CREATE DATABASE my_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;设置字符集 utf8 后缀"_cs"、"_ci"表示区分大小写、不区分大小写( Case Sensitive & Case Insensitve)utf8_general_ci 表示不区分大小写列出所有数据库SHOW DATABASES;删除数据库DROP DATABASE my_db;选择一个数据库USE my_db;创建/删除表CREATE TABLE users (username VARCHAR(20), create_date DATE);DROP TABLE users; //删除表列出数据库所有表SHOW TABLES;展示表结构EXPLAIN users;修改表-增加字段ALTER TABLE users ADD email VARCHAR(100) AFTER username;修改表-修改字段ALTER TABLE users CHANGE username user_name VARCHAR(30)插入数据 VALUES 方式INSERT INTO users VALUES(NULL, ‘johndoe’, ‘john@doe.com’, ‘2009-12-14’);插入数据 SET 方式INSERT INTO users SET user_name = ‘nettuts’, email = ‘nettuts@gmail.com’;NOW() 关键字INSERT INTO users SET create_date = NOW(), user_name = ‘batman’;LAST_INSERT_ID()查询最后一次插入数据的IDSELECT LAST_INSERT_ID();查询当前时间SELECT NOW();WHERE 条件语句SELECT * FROM users WHERE create_date != ‘2009-12-14’; SELECT * FROM users WHERE user_id <= 2;AND、OR 关键字SELECT * FROM users WHERE user_id = 1 OR user_name = ‘nettuts’;IN() 语句SELECT * FROM users WHERE create_date IN (‘2009-12-15’, ‘2019-12-15’);LIKE 关键字 %作为通配符SELECT * FROM users WHERE email LIKE ‘%TUTS%’;DESC降序排列默认 ASC升序排列SELECT * FROM users ORDER BY user_name DESC;LIMIT … OFFSET …LIMIT 1，表示获取 1 条结果OFFSET 2，表示从头两条数据之后开始算起SELECT * FROM users LIMIT 1 OFFSET 2;更新某条数据UPDATE users SET create_date = ‘2009-12-01’ WHERE create_date = ‘2009-12-14’ LIMIT 1;删除某条数据DELETE FROM users WHERE user_name = ‘batman’;TRUNCATE：重置表自增长的字段数据也会被重置TRUNCATE TABLE users;//DELETE：删除表数据不会重置自增长的字段数据DELETE FROM users;反斜杠 \ 转义字符INSERT INTO users SET user_name = 'O\‘Reilly’;表中 user_name 的值为 O’Reilly把 ’ 当作字符串写入数据库。关键字（select、delete、insert等）不能作为表的字段，否则会报错主键 PRIMARY KEYCREATE TABLE states (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20));CREATE TABLE states (id INT AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id));唯一不可重复 UNIQUECREATE TABLE states (id INT AUTO_INCREMENT, name VARCHAR(20), UNIQUE (name));设置 name 字段唯一并且索引名为 state_nameCREATE TABLE states (id INT AUTO_INCREMENT,name VARCHAR(20), PRIMARY KEY (id),UNIQUE state_name (name));创建索引CREATE TABLE states (id INT AUTO_INCREMENT, join_year INT,PRIMARY KEY (id), INDEX (join_year));创建索引用 KEY 关键字代替 INDEX 关键字CREATE TABLE states (id INT AUTO_INCREMENT, join_year INT, PRIMARY KEY (id), KEY (join_year));GROUP BY 分组BY 后面跟字段名表示根据某个字段分组SELECT * FROM states GROUP BY join_year;COUNT(*) 计数函数与 GROUP BY配合使用SELECT COUNT(*), join_year FROM states GROUP BY join_year;MIN()最小值函数MAX()最大值函数AVG()平均数函数SELECT MIN(population), MAX(population), AVG(population) FROM states;GROUP_CONCAT()将group by产生的同一个分组中的值连接起来返回一个字符串结果SELECT GROUP_CONCAT(name SEPARATOR ', '), join_year FROM states GROUP BY join_year;根据 join_year 分组把每个分组所有元素的name用逗号 ‘,’ 分隔拼接成字符串返回，还有返回join_yearSUM() 求和SELECT SUM(population) AS usa_population FROM states;查询 states表中所有元素的 population 之和以 usa_population为别名返回IF() 语句SELECT IF(true, ‘foo’, ‘bar’);//返回 fooSELECT IF(false, ‘foo’, ‘bar’);//返回 bar该语句有三个参数参数一：条件参数二：条件为 true，的返回值参数三：条件为 false，的返回值CASESELECTCOUNT(*),CASEWHEN population > 5000000 THEN 'big’WHEN population > 1000000 THEN ‘medium’ ELSE ‘small’ ENDAS state_sizeFROM states GROUP BY state_size;计数，返回 state_size字段和数量，population > 5000000 时 state_size 的值为 bigpopulation > 1000000 时 state_size 的值为 mediumpopulation <= 1000000时 state_size 的值为 smallHAVING与GROUP BY 配合使用的条件句SELECT COUNT(*), join_year FROM states GROUP BY join_year HAVING COUNT(*) > 1;多重查询SELECT * FROM states WHERE join_year = (SELECT MAX(join_year) FROM states);从一个查询语句的结果中查询IN()在IN(…)条件之内的数据SELECT * FROM states WHERE join_year IN ( SELECT join_year FROM states GROUP BY join_year HAVING COUNT(*) > 1) ORDER BY join_year;UNION 合并数据(SELECT * FROM states WHERE name LIKE ‘n%’) UNION(SELECT * FROM states WHERE population > 10000000);REPLACE INFOREPLACE INFO products SET name = ‘breadmaker’, stock = 5;类似INSERT 语句的作用插入 UNIQUE 字段重复的数据时，不会报错会先删除旧数据，然后插入本条数据id会自增INSERT IGNOREINSERT IGNORE INTO products SET name = ‘breadmaker’;插入UNIQUE 字段重复的数据时，不再报错，并不能插入成功INSERT … ON DUPLICATE KEY UPDATE假设 name 是 UNIQUE 字段 INSERT INTO products SET name = ‘breadmaker’, stock = 1 ON DUPLICATE KEY UPDATE name= ‘breadmaker2’;插入name字段重复的数据时把插入数据的name字段改为 breadmaker2INSERT INTO插入 UNIQUE字段重复的数据时，会报错。可以使用 INSERT … ON DUPLICATE KEY UPDATE 避免错误

表关系

两张表：

CREATE TABLE customers( customers_id INT AUTO_INCREMENT PRIMARY KEY, customers_name VARCHAR(100) );

//明确声明 customer_id 字段为外键，并关联 customers表的 customer_id 字段 CREATE TABLE orders( orders_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

关系sql语句说明FOREIGN KEY 外键CREATE TABLE orders(…FOREIGN KEY (customer_id) REFERENCES customers(customer_id));使用 JOIN 查询检索数据时即使数据库引擎不知道该关系，仍然可以将此列视为外键Cross Join全外连接SELECT * FROM customers CROSS JOIN orders;结果是“笛卡尔积”第一张表每一行都与第二张表中的每一行匹配。假设每个表有 4行，最终会得到 16行的结果Natural JoinSELECT * FROM customers NATURAL JOIN orders;使用这种JOIN查询，表要具有名字相同的列名Demo中，两个表都具有 _id 列，仅当此列 _id 的值在两条记录上匹配时，才会加入记录（根据相同列名来匹配）Inner Join等值连接 / 内连接SELECT * FROM customers JOIN orders WHERE customers.customers _id = orders.customers _id;只返回两个表中连接字段相等的行ON 语句SELECT * FROM customers JOIN orders ON (customers.customers_id = orders.customers_id) WHERE orders.amount > 15;USING 语句SELECT * FROM customers JOIN ordersUSING (customers_id) WHERE orders.amount > 15;类似 ON 语句两个表，有相同名字的字段时，可用 USING 来指定Left (Outer) JoinSELECT * FROM customers LEFT OUTER JOIN ordersUSING (customers_id);左表所有记录 + 右表连接字段相等的记录Right (Outer) JoinSELECT * FROM customers RIGHT OUTER JOIN ordersUSING (customers_id);右表所有记录 + 左表中连接字段相等记录

数据类型

数据类型字节数最小值最大值TINYINT1 字节-128127SMALLINT2 字节-3276832767MEDIUMINT3 字节INT4 字节BIGINT8 字节FLOAT4 字节DOUBLE8 字节DECIMAL(M,N)最多包含M个整数、N个小数位CHAR(N)VARCHAR(N)TEXTDATE‘1001-01-01’‘9999-12-31’DATETIME‘1001-01-01 00:00:00’‘9999-12-31 23:59:59’TIMESTAMP‘1970-01-01 00:00:01’ UTC‘2038-01-19 03:14:07’ UTCTIME只能保存 ‘时分秒’YEAR只能保存 ‘年’，比如 ‘2019’

1、常用 SQL

1)创建/删除/使用数据库

CREATE DATABASE my_first_db; /* EFAULT CHARACTER SET utf8：设置字符集 utf8 * COLLATE utf8_general_ci（后缀"_cs"或者"_ci"意思是区分大小写和不区分大小写（Case Sensitive & Case Insensitve）） * 这里utf8_general_ci表示不区分大小写 */ CREATE DATABASE my_first_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; //列出所有数据库 SHOW DATABASES; //删除数据库 DROP DATABASE my_first_db; //选择一个数据库 USE my_first_db;

2)创建/删除表

//创建users表，字段 username 字符类型，字段 create_date 日期类型 CREATE TABLE users (username VARCHAR(20), create_date DATE); //user_id INT AUTO_INCREMENT PRIMARY KEY：主键 user_id INT类型自增长 CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20), create_date DATE); //列出所选数据库的所有表 SHOW TABLES; //展示表结构 EXPLAIN users; //删除表 DROP TABLE users; //修改表-增加字段：在 username字段后面增加 email 字段 ALTER TABLE users ADD email VARCHAR(100) AFTER username; //修改表-删除字段 ALTER TABLE users DROP email; //修改表-修改字段：把username 字段改为 user_name 字符长度改为30 ALTER TABLE users CHANGE username user_name VARCHAR(30)

3)插入表数据

//插入数据 VALUES 方式 INSERT INTO users VALUES(NULL, 'johndoe', 'john@doe.com', '2009-12-14'); //插入数据 SET 方式 INSERT INTO users SET user_name = 'nettuts', email = 'nettuts@gmail.com',create_date = '2019-12-14'; //插入数据 INSERT INTO users (email, user_name, create_date) VALUES ('foo@bar.com', 'foobar','2019-12-16'); //NOW() 关键字 INSERT INTO users SET create_date = NOW(), user_name = 'batman', email = 'bat@man.com';

4)查询表数据

//LAST_INSERT_ID()：查询最后一次插入数据的ID SELECT LAST_INSERT_ID(); //查询当前时间 SELECT NOW(); //查询表中所有字段 SELECT * FROM users; //查询表中指定字段 SELECT user_name, email FROM users; //WHERE 条件语句 SELECT email FROM users WHERE user_name='nettuts'; SELECT * FROM users WHERE user_id <= 2; SELECT * FROM users WHERE create_date != '2009-12-14'; //AND、OR 关键字 SELECT * FROM users WHERE user_id = 1 OR user_name = 'nettuts'; //IN() 语句 SELECT * FROM users WHERE create_date IN ('2009-12-15', '2019-12-15'); //LIKE 关键字 %作为通配符 SELECT * FROM users WHERE email LIKE '%TUTS%'; //ORDER BY 语句 SELECT * FROM users ORDER BY create_date; //DESC降序排列；默认是 ASC升序排列 SELECT * FROM users ORDER BY user_name DESC; /* LIMIT ... OFFSET ... * LIMIT 1，表示获取 1 条结果 * OFFSET 2，表示从头两条数据之后开始算起 */ SELECT * FROM users LIMIT 1 OFFSET 2;

5)更新表数据

//修改某条数据 UPDATE users SET email = 'changed@gmail.com', user_name = 'barfoo' WHERE user_name = 'foobar'; UPDATE users SET create_date = '2009-12-01' WHERE create_date = '2009-12-14' LIMIT 1;

6)删除表数据

//DELETE DELETE FROM users WHERE user_name = 'batman'; //TRUNCATE：重置表，自增长的字段数据也会被重置 TRUNCATE TABLE users; //DELETE：删除表数据，不会重置自增长的字段数据 DELETE FROM users;

7)特殊字符

i. 反斜杠 \ 转义字符 INSERT INTO users SET user_name = 'O\'Reilly'; 插入后，表中 user_name 的值为 O'Reilly，把 ' 当作字符串写入数据库。 ii. 关键字（select、delete、insert等）不能作为表的字段，否则会报错

8)主键 PRIMARY KEY

//创建主键的方式 CREATE TABLE states ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) ); CREATE TABLE states ( id INT AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id) );

9)唯一不可重复 UNIQUE

//设置 name 字段唯一，索引名默认为字段名 name CREATE TABLE states ( id INT AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id), UNIQUE (name) ); //设置 name 字段唯一，并且索引名为 state_name CREATE TABLE states ( id INT AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id), UNIQUE state_name (name) );

10)INDEX 索引

//创建索引 CREATE TABLE states ( id INT AUTO_INCREMENT, name VARCHAR(20), join_year INT, PRIMARY KEY (id), UNIQUE (name), INDEX (join_year) ); //可以用 KEY 关键字代替 INDEX 关键字 CREATE TABLE states ( id INT AUTO_INCREMENT, name VARCHAR(20), join_year INT, PRIMARY KEY (id), UNIQUE (name), KEY (join_year) );

11)GROUP BY 分组：BY 后面跟字段名，表示根据某个字段分组

//根据 join_year 字段分组，join_year值相同的数据会分为一组 SELECT * FROM states GROUP BY join_year;

12)COUNT(*) 计数函数，常与 GROUP BY配合使用

//根据join_year进行分组，并计算每组数量，返回每组数量和 join_year SELECT COUNT(*), join_year FROM states GROUP BY join_year; //返回 states表数据条数 SELECT COUNT(*) FROM states; //查询 join_year = 1787 的数据条数 SELECT COUNT(*) FROM states WHERE join_year = 1787;

13)MIN()最小值函数, MAX()最大值函数, AVG()平均数函数

//查询states表中 population的最小值、最大值、平均值 SELECT MIN(population), MAX(population), AVG(population) FROM states;

14)GROUP_CONCAT() 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

/* 查询states表，根据join_year进行分组，把每个分组所有元素的name用逗号','分隔拼接成字符串返回， * 还有返回join_year */ SELECT GROUP_CONCAT(name SEPARATOR ', '), join_year FROM states GROUP BY join_year;

15)SUM() 求和

//查询 states表中所有元素的 population 之和，以 usa_population为别名返回 SELECT SUM(population) AS usa_population FROM states;

16)IF() 语句

该语句有三个参数，参数一：条件参数二：条件为true时的返回值参数三：条件为false时的返回值 //返回 foo SELECT IF(true, 'foo', 'bar'); //返回 bar SELECT IF(false, 'foo', 'bar'); /* 每次查到 population > 5000000 的数据 big_states 的结果+1， * population <=5000000的数据 big_states的结果+0 * * 每次查到 population <= 5000000 的数据 small_states 的结果+1， * population >5000000的数据 small_states 的结果+0 * * 最后返回 big_states、small_states的值 */ SELECT SUM( IF(population > 5000000, 1, 0) ) AS big_states, SUM( IF(population <= 5000000, 1, 0) ) AS small_states FROM states;

17)CASE

/* 计数，返回 state_size字段和数量， * population > 5000000 时 state_size 的值为 big * population > 1000000 时 state_size 的值为 medium * population <= 1000000时 state_size 的值为 small */ SELECT COUNT(*), CASE WHEN population > 5000000 THEN 'big' WHEN population > 1000000 THEN 'medium' ELSE 'small' END AS state_size FROM states GROUP BY state_size;

18)HAVING

//与GROUP BY 配合使用的条件句 SELECT COUNT(*), join_year FROM states GROUP BY join_year HAVING COUNT(*) > 1;

19)多重查询

//从一个查询语句的结果中查询 SELECT * FROM states WHERE join_year = ( SELECT MAX(join_year) FROM states );

20)IN() 在IN(…)条件之内的数据

SELECT * FROM states WHERE join_year IN ( SELECT join_year FROM states GROUP BY join_year HAVING COUNT(*) > 1 ) ORDER BY join_year;

21)UNION 合并数据

(SELECT * FROM states WHERE name LIKE 'n%') UNION (SELECT * FROM states WHERE population > 10000000);

22)INSERT … ON DUPLICATE KEY UPDATE

前提条件设置：创建表，name字段唯一 CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), stock INT, UNIQUE (name) ); //插入数据 name = 'breadmaker', stock = 10 INSERT INTO products SET name = 'breadmaker', stock = 10; 此时如果再插入 name = 'breadmaker' 的数据会报错，Dup里擦忒entry 'breadmaker' for key 'name' 需要执行以下SQL语句： INSERT INTO products SET name = 'breadmaker', stock = 1 ON DUPLICATE KEY UPDATE stock = stock + 1;

23)REPLACE INFO

该语句类似INSERT 语句的作用，但是对于UNIQUE字段的数据，INSERT 插入该字段重复的数据时会报错。 REPLACE INFO 插入UNIQUE字段重复的数据时，不会报错，会先删除旧数据，然后插入本条数据，id会自增。 REPLACE INFO products SET name = 'breadmaker', stock = 5;

24)INSERT IGNORE

该语句可以避免插入UNIQUE字段重复的数据时报错，但是只是不再报错，并不能插入成功 INSERT IGNORE INTO products SET name = 'breadmaker', stock = 1;

2、字段的数据类型

1)整数数据类型

类型字节数最小值最大值 TINYINT 1 -128 127 SMALLINT 2 -32768 32767 MEDIUMINT 3 INT 4 BIGINT 8

2)非整数的数字数据类型

FLOAT 4 字节 DOUBLE 8 字节 DECIMAL(M,N) 最多包含M个整数和N个小数位

3)字符串数据类型

CHAR(N) VARCHAR(N) TEXT

4)日期类型

DATE 范围 '1001-01-01' 到 '9999-12-31' DATETIME 范围 '1001-01-01 00:00:00' 到 '9999-12-31 23:59:59' TIMESTAMP 范围 '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC TIME 只能保存'时分秒' YEAR 只能保存 '年' 比如 '2019'

3、表关系

1)FOREIGN KEY 外键

CREATE TABLE customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(100) ); //明确声明customer_id字段为外键，并关联 customers表的customer_id字段 CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, amount DOUBLE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); //不需要明确声明customer_id为外键，而是声明为索引 CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, amount DOUBLE, INDEX (customer_id) ); //使用JOIN查询检索数据时，即使数据库引擎不知道该关系，您仍然可以将此列视为外键。 SELECT * FROM orders JOIN customers USING(customer_id)

2)Cross Join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。 /* 下面是有外键关系时的写法 * 结果是“笛卡尔积”，第一张表中的每一行都与第二张表中的每一行匹配。 * 由于每个表有4行，因此最终得到16行的结果 */ SELECT * FROM customers JOIN orders; 等同于： SELECT * FROM customers, orders;

3)Natural Join

使用这种JOIN查询，表需要具有匹配的列名。在我们的例子中，两个表都具有 customer_id 列。因此，仅当此列 customer_id 的值在两条记录上匹配时，才会加入记录 SELECT * FROM customers NATURAL JOIN orders;

4)Inner Join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。指定连接条件后，将执行内部连接。在这种情况下，最好使两个表上的customer_id字段都匹配。结果应类似于自然联接。

SELECT * FROM customers JOIN orders WHERE customers.customer_id = orders.customer_id;

5)ON 语句

SELECT * FROM customers JOIN orders ON (customers.customer_id = orders.customer_id) WHERE orders.amount > 15;

6)USING 语句类似 ON 语句，当两个表有名字相同的字段时，可以通过USING 来指定

SELECT * FROM customers JOIN orders USING (customer_id) WHERE orders.amount > 15;

7)Left (Outer) Join 左联接是外联接的一种。返回包括左表中的所有记录和右表中连接字段相等的记录。

SELECT * FROM customers LEFT OUTER JOIN orders USING (customer_id); SELECT * FROM customers LEFT OUTER JOIN orders USING (customer_id) WHERE orders.order_id IS NULL; SELECT * FROM customers LEFT OUTER JOIN orders USING (customer_id) WHERE orders.amount > 15; SELECT * FROM customers LEFT OUTER JOIN orders USING (customer_id) WHERE orders.amount > 15 OR orders.order_id IS NULL; SELECT * FROM customers LEFT OUTER JOIN orders ON (customers.customer_id = orders.customer_id AND orders.amount > 15);

8)Right (Outer) Join 右联接是外联接的一种。返回包括右表中的所有记录和左表中连接字段相等的记录。

SELECT * FROM customers RIGHT OUTER JOIN orders USING (customer_id); SELECT * FROM orders RIGHT OUTER JOIN customers USING (customer_id);

推荐阅读：数据库基础1 数据库基础2 数据库基础3

最新回复(0)