做返利網(wǎng)站能賺錢的免費(fèi)入駐的電商平臺
1、創(chuàng)建和管理數(shù)據(jù)庫
-
創(chuàng)建一個(gè)名為
school的數(shù)據(jù)庫。 -
列出所有的數(shù)據(jù)庫，并確認(rèn)
school數(shù)據(jù)庫已經(jīng)創(chuàng)建。 -
如果
school數(shù)據(jù)庫已經(jīng)存在，刪除它并重新創(chuàng)建。 -
mysql> create database school; Query OK, 1 row affected (0.01 sec)mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | | zabbix | +--------------------+ 6 rows in set (0.01 sec)2、創(chuàng)建數(shù)據(jù)表
-
在
school數(shù)據(jù)庫中，創(chuàng)建一個(gè)名為students的表，包含以下字段：id（整數(shù)，主鍵，自增）、name（字符串，最大長度50）、age（整數(shù)）和grade（字符串，最大長度10）。 -
創(chuàng)建一個(gè)名為
courses的表，包含course_id（整數(shù)，主鍵，自增）、course_name（字符串，最大長度100）和teacher（字符串，最大長度50）。 -
mysql> create table students(id int auto_increment primary key,name varchar(50),age int,grade varchar(10)); Query OK, 0 rows affected (0.06 sec) mysql> create table courses(course_id int auto_increment primary key,course_name varchar(100),teacher varchar(50)); Query OK, 0 rows affected (0.01 sec)3、插入數(shù)據(jù)
-
向
students表中插入一些示例數(shù)據(jù)。 -
向
courses表中插入一些示例數(shù)據(jù) -
mysql> insert into students(name,age,grade)values('zhsan',20,1); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO students (name, age, grade) VALUES ('李四', 19, '二年級'); Query OK, 1 row affected (0.00 sec) mysql> select * from courses; Empty set (0.00 sec)mysql> select * from students; +----+--------+------+-----------+ | id | name | age | grade | +----+--------+------+-----------+ | 1 | zhsan | 20 | 1 | | 2 | 李四 | 19 | 二年級 | +----+--------+------+-----------+ 2 rows in set (0.00 sec) mysql> update students set name='張三',grade='一年級' where id=1; Query OK, 1 row affected (0.00 sec) mysql> select * from students; +----+--------+------+-----------+ | id | name | age | grade | +----+--------+------+-----------+ | 1 | 張三 | 20 | 一年級 | | 2 | 李四 | 19 | 二年級 | +----+--------+------+-----------+ 2 rows in set (0.00 sec) mysql> insert into courses(course_name,teacher)values('數(shù)學(xué)','張老師'); Query OK, 1 row affected (0.00 sec)mysql> insert into courses(course_name,teacher)values('語文','王老師'; Query OK, 1 row affected (0.00 sec)mysql> select * from courses; +-----------+-------------+-----------+ | course_id | course_name | teacher | +-----------+-------------+-----------+ | 1 | 數(shù)學(xué) | 張老師 | | 2 | 語文 | 王老師 | +-----------+-------------+-----------+ 2 rows in set (0.00 sec)mysql>4、基本查詢
-
查詢
students表中的所有數(shù)據(jù)。 -
查詢年齡大于或等于20歲的學(xué)生信息。
-
查詢姓名為"張三"的學(xué)生的所有信息。
-
mysql> select * from students; +----+--------+------+-----------+ | id | name | age | grade | +----+--------+------+-----------+ | 1 | 張三 | 20 | 一年級 | | 2 | 李四 | 19 | 二年級 | +----+--------+------+-----------+ 2 rows in set (0.00 sec) mysql> select * from students where age>=20; +----+--------+------+-----------+ | id | name | age | grade | +----+--------+------+-----------+ | 1 | 張三 | 20 | 一年級 | +----+--------+------+-----------+ 1 row in set (0.00 sec) mysql> select * from students where name='張三'; +----+--------+------+-----------+ | id | name | age | grade | +----+--------+------+-----------+ | 1 | 張三 | 20 | 一年級 | +----+--------+------+-----------+ 1 row in set (0.00 sec)5、更新和刪除數(shù)據(jù)
-
將名為"張三"的學(xué)生的年齡更新為21歲。
-
刪除年齡小于18歲的學(xué)生信息。
-
mysql> update students set age=21 where name='張三'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from students ; +----+--------+------+-----------+ | id | name | age | grade | +----+--------+------+-----------+ | 1 | 張三 | 21 | 一年級 | | 2 | 李四 | 19 | 二年級 | +----+--------+------+-----------+ 2 rows in set (0.00 sec) mysql> insert into students(name,age,grade)values('王五',17,'一年級'); Query OK, 1 row affected (0.01 sec) mysql> select * from students; +----+--------+------+-----------+ | id | name | age | grade | +----+--------+------+-----------+ | 1 | 張三 | 21 | 一年級 | | 2 | 李四 | 19 | 二年級 | | 4 | 王五 | 17 | 一年級 | +----+--------+------+-----------+ 3 rows in set (0.00 sec)mysql> delete from students where age<18; Query OK, 1 row affected (0.00 sec)mysql> select * from students; +----+--------+------+-----------+ | id | name | age | grade | +----+--------+------+-----------+ | 1 | 張三 | 21 | 一年級 | | 2 | 李四 | 19 | 二年級 | +----+--------+------+-----------+ 2 rows in set (0.00 sec)mysql>6、多表查詢
-
（假設(shè)存在一個(gè)關(guān)聯(lián)表
student_courses，表示學(xué)生和課程的關(guān)聯(lián)關(guān)系） -
查詢所有選修了"數(shù)學(xué)"課程的學(xué)生信息。
-
查詢每個(gè)學(xué)生的選課數(shù)量。
-
mysql> create table student_courses(student_id int,course_id int,foreign key (student_id) references student(id),foreign key(course_id)references courses(course_id)); Query OK, 0 rows affected (0.01 sec) mysql> insert into student_courses(student_id,course_id)values(1,2); Query OK, 1 row affected (0.01 sec)mysql> insert into student_courses(student_id,course_id)values(2,2); Query OK, 1 row affected (0.01 sec)mysql> insert into student_courses(student_id,course_id)values(2,1); Query OK, 1 row affected (0.00 sec)mysql> SELECT s.name, s.age, s.grade -> FROM student s -> JOIN student_courses sc ON s.id = sc.student_id -> JOIN courses c ON sc.course_id = c.course_id -> WHERE c.course_name = '數(shù)學(xué)'; +--------+------+-----------+ | name | age | grade | +--------+------+-----------+ | 張三 | 21 | 一年級 | | 李四 | 19 | 二年級 | +--------+------+-----------+ 2 rows in set (0.00 sec)7、事務(wù)處理
-
編寫一個(gè)事務(wù)，將學(xué)生"李四"選修的"數(shù)學(xué)"課程更改為"物理"，并確保如果更改失敗，則回滾到之前的狀態(tài)。
-
BEGIN TRANSACTION 或 START TRANSACTION：開始一個(gè)新的事務(wù)。
-
COMMIT：提交事務(wù)，將事務(wù)中的所有更改保存到數(shù)據(jù)庫。
-
ROLLBACK：撤銷事務(wù)，回滾到事務(wù)開始之前的狀態(tài)，撤銷所有在事務(wù)中所做的更改
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update student_courses set course_id=(select course_id from courses where course_name='物理') where student_id=(select id from student where name="李四") and course_id=(select course_id from courses where course_name='數(shù)學(xué)') and course_id=(select course_id from courses where course_name='數(shù)學(xué)'); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec)8、索引和約束
在
students表的name字段上創(chuàng)建一個(gè)唯一索引，確保每個(gè)學(xué)生都有一個(gè)唯一的姓名。在
courses表的course_name字段上創(chuàng)建一個(gè)普通索引，以優(yōu)化查詢性能。 -
mysql> create unique index idx_name on student(name); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> create index idx_course_name on courses(course_name); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 09、聚合函數(shù)和分組
-
查詢每個(gè)年級的學(xué)生數(shù)量。
-
查詢年齡最大的學(xué)生信息。
-
mysql> select grade,count(*) as student_count from student group by grade; +-----------+---------------+ | grade | student_count | +-----------+---------------+ | 一年級 | 1 | | 二年級 | 1 | +-----------+---------------+ 2 rows in set (0.01 sec)mysql> mysql> select * from student where age=(select max(age) from student); +----+--------+------+-----------+ | id | name | age | grade | +----+--------+------+-----------+ | 1 | 張三 | 21 | 一年級 | +----+--------+------+-----------+ 1 row in set (0.00 sec)mysql>
10、視圖和存儲過程
-
創(chuàng)建一個(gè)視圖，顯示年齡大于20歲的學(xué)生及其選課信息。
-
編寫一個(gè)存儲過程，用于添加新的學(xué)生和課程關(guān)聯(lián)記錄。
-
mysql> create view view_age20 as-> select s.id,s.name,s.age,sc.course_id,c.course_name-> from student s-> join student_courses sc on s.id=sc.student_id-> join courses c on sc.course_id=c.course_id-> where s.age >20; Query OK, 0 rows affected (0.01 sec)mysql> delimiter // mysql> create procedure addsc(in student_id int,in course_id int)-> begin insert into student_courses(student_id,course_id)values (student_id,course_id);-> -> end // Query OK, 0 rows affected (0.02 sec) mysql> delimiter ;