MySQL Fundamental

  Foreign key Method 1 - while creating table create table if not exists `student` ( key `fk_gradeId` ( `gradeId` ) , -- define key constraint `fk_gradeId` foreign key ( `gradeId` ) references `grade` ( `gradeId` ) -- add constranit + reference   ) Method 2 - after create table alter table `students` add constraint `fk_gradeId` foreign key ( `gradeId` ) references `grade` ( `gradeId` ) The above two methods are physical foreign keys, database-level foreign keys, which are not recommended Best practice Database is only used to store data Using foreign keys by programming

  Table engine MYISAM VS. INNODB (default) MYISAM INNODB Transaction support NO YES Data row locking NO YES Foreign key constraints NO YES Full text index YES NO Table size smaller bigger(2 times) Advantage MYISAM: space saving, faster INNODB: higher security, support multi-table multi-user operation

  CREATE TABLE BY SQL Create a table by sql syntax CREATE DATABASE IF NOT EXISTS TESTDB01 ; CREATE TABLE IF NOT EXISTS `students` ( `id` int ( 4 ) not null auto_increment , `name` varchar ( 30 ) not null default 'anonymous' , `sex` varchar ( 10 ) not null default 'female' , `pwd` varchar ( 20 ) not null default '123456' , `birthday` datetime default null , `address` varchar ( 100 ) default null , `email` varchar ( 50 ) default null , primary key ( `id` ) ) engine = innodb default charset = utf8 ; Some of important commands - sometimes you may use below code to get how wordbench generate table show create database school ; -- common commands -- 'school', 'CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION=\'N\' */' show create table students ; -- 'students', 'CREATE TABLE `students` (\n `id` int NOT NULL AUTO_INCREMENT,\n `name` va...

  Syntax of MySQL in MAC commands Start MySQL sudo /usr/local/mysql/support-files/mysql.server start sudo /usr/local/mysql/support-files/mysql.server stop sudo /usr/local/mysql/support-files/mysql.server restart Open MySQL mysql -u root -p Reset password ./mysql FLUSH PRIVILEGES; SET PASSWORD FOR root@localhost = '123456' ; Check database show databases; Change database use + "database_name" ; Describe show tables; ​ describe + "database_name" ; Create database create database + "name" ; Comment -- single line /* multiline comment */