增刪改查使用示范 · Linux學習手冊

[TOC] # Mysql基本操作 ## 增 ### 創建數據庫 1. 外部命令行 ``` mysqladmin -uroot -p123456 create dbname ``` mysqladmin創建的數據庫不能指定字符集 2. 內部命令行 ``` CREATE DATABASE newdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci; ``` 3. 腳本應用 ``` CREATE DATABASE IF NOT EXISTS newdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci; ``` ### 創建數據表 1. 創建數據表 ``` create table student ( ids int auto_increment primary key, name varchar(20), chinese float, english float, math float ); ``` ### 插入數據 ``` insert into student values(1,'李明',89,78,90);? insert into student values(2,'乘風',67,89,56);? insert into student values(3,'南宮流云',87,78,77);? insert into student values(4,'南宮皓月',88,98,90);? insert into student values(5,'南宮紫月',82,84,67);? insert into student values(6,'蕭炎',55,85,45);? insert into student values(7,'林動',75,65,30); ``` ### 插入新的一列為student表新增一列physical類型為float ``` ALTER TABLE student ADD physical FLOAT; ``` ## 刪 ### 刪除數據庫 1. 外部命令行刪除時有提示 ``` mysqladmin -uroot -p drop RUNOOB ``` 刪除時無提示，強制刪除 ``` mysqladmin -uroot -p drop -f RUNOOB ``` 2. 內部命令行 ``` drop database RUNOOB; ``` ### 刪除數據表 ### 刪除表內數據 ## 改 ### 修改表名將student表名修改為Grade ``` ALTER TABLE student RENAME TO Grade; ``` ### 修改（更新）表內數據更新林動的數學成績為99 ``` UPDATE student SET math = '99' WHERE name = '林動'; ``` ## 查插入測試數據 ``` create table student ( ids int auto_increment primary key, name varchar(20), chinese float, english float, math float ); insert into student values(1,'李明',89,78,90);? insert into student values(2,'乘風',67,89,56);? insert into student values(3,'南宮流云',87,78,77);? insert into student values(4,'南宮皓月',88,98,90);? insert into student values(5,'南宮紫月',82,84,67);? insert into student values(6,'蕭炎',55,85,45);? insert into student values(7,'林動',75,65,30); ``` ### 查詢表內所有數據 ``` mariadb> SELECT * FROM student; +-----+----------+---------+---------+------+ | ids | name | chinese | english | math | +-----+----------+---------+---------+------+ | 1 | 李明 | 89 | 78 | 90 | | 2 | 乘風 | 67 | 89 | 56 | | 3 | 南宮流云 | 87 | 78 | 77 | | 4 | 南宮皓月 | 88 | 98 | 90 | | 5 | 南宮紫月 | 82 | 84 | 67 | | 6 | 蕭炎 | 55 | 85 | 45 | | 7 | 林動 | 75 | 65 | 30 | +-----+----------+---------+---------+------+ 7 rows in set ``` ### 查詢指定列 ``` mariadb> SELECT name,math FROM student; +----------+------+ | name | math | +----------+------+ | 李明 | 90 | | 乘風 | 56 | | 南宮流云 | 77 | | 南宮皓月 | 90 | | 南宮紫月 | 67 | | 蕭炎 | 45 | | 林動 | 30 | +----------+------+ 7 rows in set ``` ### 去重查詢 ``` mariadb> SELECT DISTINCT math FROM student; +------+ | math | +------+ | 90 | | 56 | | 77 | | 67 | | 45 | | 30 | +------+ 6 rows in set ``` ### 查詢并計算所有人的總分 ``` mariadb> SELECT ids,name,(chinese+math+english) as 總分 FROM student; +-----+----------+------+ | ids | name | 總分 | +-----+----------+------+ | 1 | 李明 | 257 | | 2 | 乘風 | 212 | | 3 | 南宮流云 | 242 | | 4 | 南宮皓月 | 276 | | 5 | 南宮紫月 | 233 | | 6 | 蕭炎 | 185 | | 7 | 林動 | 170 | +-----+----------+------+ 7 rows in set ``` ### 查詢并計算有所南宮同學的總分 ``` mariadb> SELECT ids,name,(chinese+math+english) as 總分 FROM student WHERE name like "南宮%"; +-----+----------+------+ | ids | name | 總分 | +-----+----------+------+ | 3 | 南宮流云 | 242 | | 4 | 南宮皓月 | 276 | | 5 | 南宮紫月 | 233 | +-----+----------+------+ 3 rows in set ``` where 子句中的運算符 ![image](https://img-blog.csdn.net/20180526155434349?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzM3OTY0MDcx/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70) **需要注意的是where 后面不能用別名，因為數據庫中先執行where子句，再執行select子句。** ``` mariadb> SELECT ids,name,(chinese+math+english) as 總分 FROM student WHERE 總分 > 200; 1054 - Unknown column '總分' in 'where clause' mariadb> SELECT ids,name,(chinese+math+english) as 總分 FROM student WHERE (chinese+math+english) > 250; +-----+----------+------+ | ids | name | 總分 | +-----+----------+------+ | 1 | 李明 | 257 | | 4 | 南宮皓月 | 276 | +-----+----------+------+ 2 rows in set ``` ### order by排序語句 - asc升序(默認)，desc降序 - order by 子句應該位于select語句的結尾 - order by column1 column2表示當column1相同時才會按照column2排序 1. 對數學成績進行升序排序 ``` mariadb> SELECT ids,name,math FROM student ORDER BY math; +-----+----------+------+ | ids | name | math | +-----+----------+------+ | 7 | 林動 | 30 | | 6 | 蕭炎 | 45 | | 2 | 乘風 | 56 | | 5 | 南宮紫月 | 67 | | 3 | 南宮流云 | 77 | | 1 | 李明 | 90 | | 4 | 南宮皓月 | 90 | +-----+----------+------+ 7 rows in set ``` 2. 對數學成績進行降序排序 ``` mariadb> SELECT ids,name,math FROM student ORDER BY math DESC; +-----+----------+------+ | ids | name | math | +-----+----------+------+ | 1 | 李明 | 90 | | 4 | 南宮皓月 | 90 | | 3 | 南宮流云 | 77 | | 5 | 南宮紫月 | 67 | | 2 | 乘風 | 56 | | 6 | 蕭炎 | 45 | | 7 | 林動 | 30 | +-----+----------+------+ 7 rows in set ``` 3. 對所有人總分進行降序排序 ``` mariadb> SELECT ids,name,(chinese+math+english) as 總分 FROM student ORDER BY (chinese+math+english) DESC; +-----+----------+------+ | ids | name | 總分 | +-----+----------+------+ | 4 | 南宮皓月 | 276 | | 1 | 李明 | 257 | | 3 | 南宮流云 | 242 | | 5 | 南宮紫月 | 233 | | 2 | 乘風 | 212 | | 6 | 蕭炎 | 185 | | 7 | 林動 | 170 | +-----+----------+------+ 7 rows in set ``` 4. 先選出數學成績大于70的然后按照數學，以及中文成績降序排列 ``` mariadb> SELECT ids,name,math,chinese FROM student WHERE math > 70 ORDER BY math DESC,chinese DESC; +-----+----------+------+---------+ | ids | name | math | chinese | +-----+----------+------+---------+ | 1 | 李明 | 90 | 89 | | 4 | 南宮皓月 | 90 | 88 | | 3 | 南宮流云 | 77 | 87 | +-----+----------+------+---------+ 3 rows in set ``` ### 常用函數函數 | 說明 --- | --- avg() | 返回列的平均值 count() | 返回列的行數 max() | 返回列中的最大值 min() | 返回列中的最小值 sum() | 返回列的總和 1. count() count(*)統計包含null值的行，count(列名)統計中已剔除null值的行統計當前student表中一共有多少學生 ``` mariadb> SELECT COUNT(*) AS 人數 FROM student; +------+ | 人數 | +------+ | 7 | +------+ 1 row in set ``` 2. avg() 統計所有人的數學平均分 ``` mariadb> SELECT AVG(math) AS 數學平均成績 FROM student; +--------------+ | 數學平均成績 | +--------------+ | 65 | +--------------+ 1 row in set ```