Rumah > Artikel > pangkalan data > Analisis contoh operasi asas pangkalan data MySQL
Analisis contoh operasi asas pangkalan data MySQL
- WBOYke hadapan
- 2023-05-26 23:27:541673semak imbas
1. Pengenalan kepada MySQL
1 Klasifikasi perisian pengurusan pangkalan data
Terbahagi terutamanya kepada hubungan dan bukan hubungan.
Ia boleh difahami dengan mudah bahawa pangkalan data hubungan perlu mempunyai struktur jadual, manakala pangkalan data bukan hubungan menyimpan nilai kunci dan tidak mempunyai struktur jadual.
Jenis perhubungan: seperti sqllite, db2, oracle, akses, pelayan sql, MySQL Nota: pernyataan sql adalah universal.
Bukan hubungan: mongodb, redis, memcache
2 MySQL
MySQL ialah sistem pengurusan pangkalan data hubungan yang dibangunkan oleh syarikat MySQL AB Sweden dan kini dimiliki oleh produk Oracle. .
MySQL ialah salah satu sistem pengurusan pangkalan data hubungan yang paling popular Dari segi aplikasi WEB, MySQL ialah perisian aplikasi RDBMS (sistem pengurusan pangkalan data perhubungan).
Bahasa SQL ialah bahasa piawai yang paling biasa digunakan dan digunakan untuk mengakses pangkalan data MySQL. Perisian MySQL mengamalkan dasar pelesenan dwi dan dibahagikan kepada versi komuniti dan versi komersial Disebabkan saiznya yang kecil, kelajuan yang pantas, jumlah kos pemilikan yang rendah, dan terutamanya ciri-ciri sumber terbuka, MySQL secara amnya dipilih sebagai pangkalan data laman web. pembangunan laman web bersaiz kecil dan sederhana.
MySQL membekalkan kami pakej pemasangan sumber terbuka untuk pelbagai sistem pengendalian, termasuk mac, linux dan tingkap.
2. Enjin storan (juga dipanggil jenis jadual)
Data dalam MySQL disimpan dalam fail (atau memori) menggunakan pelbagai teknologi. Teknologi yang berbeza menggunakan mekanisme penyimpanan unik, teknik pengindeksan dan tahap penguncian mereka sendiri, sekali gus menyediakan pelbagai fungsi dan keupayaan. MySQL merujuk kepada teknologi yang berbeza ini dan fungsi yang berkaitan sebagai enjin storan, yang juga boleh dipanggil jenis jadual.
MySQL dikonfigurasikan dengan banyak enjin storan berbeza secara lalai, yang boleh dipratetap atau didayakan dalam pelayan MySQL.
1. Enjin storan biasa dan senario yang berkenaan
InnoDB: Digunakan untuk aplikasi pemprosesan transaksi, menyokong kunci asing dan kunci peringkat baris. Jika aplikasi mempunyai keperluan yang agak tinggi untuk integriti sesuatu dan memerlukan ketekalan data di bawah keadaan serentak, dan operasi data termasuk banyak operasi kemas kini dan padam sebagai tambahan kepada sisipan dan pertanyaan, maka enjin storan InnoDB adalah lebih sesuai.
Selain mengurangkan kunci yang disebabkan oleh pemadaman dan kemas kini secara berkesan, InnoDB juga boleh memastikan penyerahan dan pemulangan transaksi yang lengkap Ia adalah pilihan yang sesuai untuk sistem seperti sistem pengebilan atau sistem kewangan yang mempunyai keperluan tinggi untuk ketepatan data.MyISAM: Jika aplikasi terutamanya berdasarkan operasi baca dan operasi sisip, terdapat hanya beberapa operasi kemas kini dan pemadaman, dan integriti dan keselarasan transaksi adalah Jika keperluan tidak tinggi, anda boleh memilih enjin storan ini.
Memori: Menyimpan semua data dalam memori, menyediakan akses yang sangat pantas dalam persekitaran di mana rekod dan data lain yang serupa perlu dikesan dengan cepat.
Kecacatan Memori ialah terdapat had pada saiz jadual Walaupun data boleh dipulihkan secara normal jika pangkalan data ditamatkan secara tidak normal, setelah pangkalan data ditutup, data yang disimpan dalam memori akan hilang.
Enjin storan yang disokong oleh mysql termasuk InnoDB, MyISAM, MEMORY, CSV, BLACKHOLE, NDB, FEDERATED, MRG_MYISAM, ARCHIVE, PERFORMANCE_SCHEMA.
Antaranya, NDB dan InnoDB menyediakan jadual selamat transaksi, dan enjin storan lain ialah jadual tidak selamat urus niaga.
2. Penggunaan enjin storan dalam mysql
# 查看当前的默认存储引擎: mysql> show variables like "default_storage_engine"; # 查询当前数据库支持的存储引擎 mysql> show engines \G;
1 Nyatakan enjin storan semasa membuat jadual
mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB; # 也可以使用alter table语句，修改一个已经存在的表的存储引擎。 mysql> alter table ai engine = innodb;
2 🎜> # my.ini文件
[mysqld]
default-storage-engine=INNODB
3. Operasi jadual MySQL1 Lihat struktur jadualTerdapat dua cara untuk melihat struktur jadual:
- desc [nama jadual] dan huraikan [nama jadual]: Kedua-dua kaedah ini mempunyai kesan yang sama dan boleh melihat struktur jadual semasa.
- tunjukkan cipta jadual [nama jadual]: Selain melihat definisi jadual, anda juga boleh melihat maklumat seperti enjin (enjin simpanan) dan set aksara (set aksara). Gunakan pilihan G untuk menyusun rekod secara menegak, menjadikan rekod yang lebih panjang lebih mudah untuk dipaparkan. )
mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql> show create table staff_info\G;
*************************** 1. row ***************************
Table: staff_info
Create Table: CREATE TABLE `staff_info` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
`phone` bigint(11) DEFAULT NULL,
`job` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.01 sec)
ERROR:
No query specified2 Lajur pertumbuhan automatikMedan yang dikekang dikembangkan secara automatik dan medan yang dikekang mesti dikekang oleh kunci. kunci utama pada masa yang sama--不指定id，则自动增长
create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male');
mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL |
auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | male | |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values ('nick'),('tank') ;
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | nick | male |
| 2 | tank | male |
+----+------+------+
--也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | nick | male |
| 2 | tank | male |
| 4 | asb | female |
| 7 | wsb | female |
+----+------+--------+
--对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 8 | ysb | male |
+----+------+------+
--应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student(name) values('nick');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | nick | male |
+----+------+------+
row in set (0.00 sec)
--在创建完表后，修改自增字段的起始值
mysql> create table student(id int primary key auto_increment, name varchar(20),sex enum('male','female') default 'male');
mysql> alter table student auto_increment=3 ;
mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
--也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
mysql> create table student(id int primary key auto_increment, name varchar(20),sex enum('male','female') default 'male'
)auto_increment=3 ; 4. Jenis data yang disokong oleh MySQL 1 ENUM dan jenis SET
- Nama Cina ENUM dipanggil enumeration. jenis, dan julat nilainya memerlukan Dipaparkan melalui penghitungan semasa mencipta jadual.
ENUM hanya membenarkan anda memilih satu nilai daripada koleksi nilai, bukan berbilang nilai sekali gus. Tujuan: Pemilihan radio: Pilih jantina
Penghitungan untuk 1-255 ahli memerlukan storan 1 bait
Untuk 255-65535 ahli, Memerlukan 2 bait storan;
membenarkan sehingga 65535 ahli.
- SET sangat serupa dengan ENUM Ia juga merupakan objek rentetan yang boleh mengandungi 0-64 ahli. Storan berbeza bergantung pada ahli.
Jenis set membolehkan anda memilih 1 atau lebih elemen secara sewenang-wenang daripada set nilai untuk gabungan. Suntikan tidak akan dibenarkan untuk kandungan yang berada di luar julat, dan nilai pendua akan dinyahduplikasi secara automatik. Penggunaan: Pelbagai pilihan: Minat, hobi, jantina
SET：
1-8个成员的集合，占1个字节
9-16个成员的集合，占2个字节
17-24个成员的集合，占3个字节
25-32个成员的集合，占4个字节
33-64个成员的集合，占8个字节
2、set/enum示例
mysql> create table t10 (name char(20),gender enum('female','male') ); Query OK, 0 rows affected (0.01 sec) -- 选择enum('female','male')中的一项作为gender的值，可以正常插入 mysql> insert into t10 values ('nick','male'); Query OK, 1 row affected (0.00 sec) -- 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值 mysql> insert into t10 values ('nick','male,female'); ERROR 1265 (01000): Data truncated for column 'gender' at row 1 mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车') ); Query OK, 0 rows affected (0.01 sec) -- 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能 mysql> insert into t11 values ('tank','烫头,喝酒,烫头'); Query OK, 1 row affected (0.01 sec) mysql> select * from t11; +------+---------------+ | name | hobby | +------+---------------+ | tank | 喝酒,烫头 | +------+---------------+ row in set (0.00 sec) -- 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项， mysql> insert into t11 values ('jason','烫头,翻车,看妹子'); ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
五、MySQL表查询
1、限制查询的记录数（limit）
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; --默认初始位置为0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0 , 5 ; --从第0开始，即先出第一条，然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5 , 5 ; --从第5开始，即先出第6条，然后包含这一条在内往后查5条2、使用正则表达式查询
小结：对字符串匹配的方式
WHERE emp_name = 'nick';
WHERE emp_name LIKE 'sea%';
WHERE emp_name REGEXP 'on$';
SELECT * FROM employee WHERE emp_name REGEXP '^jas';
SELECT * FROM employee WHERE emp_name REGEXP 'on$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';六、数据备份(命令行)
1、 数据库的逻辑备份
--语法： mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql --示例： --单库备份 mysqldump -uroot –p123 mysql > c:\db1.sql mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql --多库备份 mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql --备份所有库 mysqldump -uroot -p123 --all-databases > all.sql
2、 数据恢复
--方法一： [root@nick backup]-- mysql -uroot -p123 < /backup/all.sql --方法二： mysql> use db1; mysql> SET SQL_LOG_BIN=0; --关闭二进制日志，只对当前session生效 mysql> source /root/db1.sql
七、事务和锁（SQL）
begin; -- 开启事务
select * from emp where id = 1 for update; -- 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; -- 完成更新
commit; -- 提交事务八、执行计划Explain
执行计划：让mysql预估执行操作(一般正确)
Explain语法：
explain select … from … [where …]
Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。
具体用法和字段含义可以参考官网explain-output ，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（rows：显示MySQL认为它执行查询时必须检查的行数。）。所以优化语句基本上都是在优化rows。
例如：
explain select * from news;
输出：
+--+-----------+-----+----+-------------+---+-------+---+----+-----+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +--+-----------+-----+----+-------------+---+-------+---+----+----—+
Atas ialah kandungan terperinci Analisis contoh operasi asas pangkalan data MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!