Mysql study note

Mysql learning notes A Mysql and SQL syntax are almost the same, divided into the following categories. 1. Data Definition Language (DDL): create, alter, drop, etc. 2. Data Operation Language (DML): such as insert, modify, and delete 3. Data Control Language (DCL ): grant and revoke perform database operations through mysql on the console. The mysql statement Terminator is ';' after we enter the mysql console, we can view several database operations: show all databases: show databases; delete database: drop databases dbname; create database: create database [if not exists] dbname; Switch database: use dbname; Table operation: display All the table objects of the currently used database: show tables; display table structure: desc tablename; (desc --> describe) create a table: create table student (uid int, Uname varchar (20), uscore float); table creation method for subqueries: note that the column name of the new table must correspond to the column name of the subquery, And the type must be the same. Create table somestudents (uid int, uname varchat (20) as select uid, uname from student; copy (Backup) table: create table student2 as select * from student; Delete table: drop table tablename; empty table data: truncate table tablename; similar to drop from tablename. Rename a table: alter table student rename to students; Field Operation: add a table field: alter table student add addr varchar (20) default 'abcdefg '; (-- in mysql, It is case-insensitive or double quotation marks or single quotes --) add multiple table fields: alter table student add (phone1 varchar (8) default '000000' phone2 varchat (11) default '000000'); Modify the table field: extend the length of phone1 to 9 bits, set the default value, and set it to the first column of the table. Alter table student modify phone1varchar (9) default '000000' first; Set phone2 after the phone1 field alter table student modify phone2varchar (11) after phone1; modify multiple fields: in mysql, alter modify does not allow you to modify multiple columns at a time (just like insert, One insert can insert multiple rows), But oracle can, however, mysql can modify multiple columns through multiple modify statements. Alter table student modify phone2 varchar (11) default '20160301', modify phone1 varchar (9) default '20160301'; Delete the specified field: alter table student drop phone1; rename the field: if you need to change the column name, we recommend that you use change. If you only need to change the data type and display position, use modify. Alter table student change uname name varchar (20); constraint: not null: the value of a column cannot be empty. unique: unique constraint. It cannot be repeated. It can be empty. Primary key: Master constraint. It cannot be repeated. It cannot be blank. foreign key: foreign key. check: Specifies an expression to check specified data. (Mysql does not support but can be written .) Add constraint to a field in the table: non-empty constraint: add non-empty constraint: alter table student modify uage int not null; cancel non-empty constraint: alter table student modify uage int null; add default constraint: alter table student modify uage int default '20' not null; unique constraint: add unique constraint: alter table student add unique (name, psw ); the user name and password cannot be the same. Alter table student modify name varchar (23) unique; Delete the unique constraint: alter table student drop index name; remove the unique constraint of the name column. Primary key constraint: Add a primary key constraint: alter table student modify uid int primary key; delete a primary key constraint: alter table student drop primary key; remove all primary key constraints in the table. Add multiple primary key constraints: alter table student add primary key (uid, uname); Set primary key to increment: alter table student modify uid int auto_increment; foreign key constraints: add foreign key constraints: alter table student add foreign key (phone, addr) references class (phone, addr); Delete foreign key constraints: alter table student drop foreign key phone; alter table student drop foreign key addr; Note: When the record of the master table is dependent on the slave table, the record of the master table cannot be deleted unless the dependency is removed from the table. Check constraint: create table temp (id int primary key, age int, check (age> 20); the constraint age> 20 actually does not work. Index: Method of creating an index: automatic: When a table defines primary key constraints, unique and foreign key constraints, the table will be automatically indexed by the system. Manual: manually add indexes to related tables or columns to increase the query speed. Index deletion method: automatic: when the table object is deleted, the index on the table is automatically deleted manually: manually deleting the index on the relevant columns of the specified table object is similar to the book directory, you can quickly locate the relevant data. A table can have multiple indexes. Create index indexname on student (uid, uname); Delete index: drop index indexname on student; view: view is the query result of one or more tables, it is a virtual table because it cannot store data. The role and advantages of a view: Restrict access to data to make complex queries simple. Provide data independence. You can create a view for different displays of the same data: create view viewname as select uid, uname from student; normally we do not modify the view. We can add with check option create view viewname as select uid, uname from student with check option; Delete view: drop view viewname; data operation: insert statement: insert into insert: insert into student (uid, uname) vaules (1, 'dxd'); insert into student values (2, 'dd', xx, xxx, xxxxx); insert in into full Columns To student values (3, 'gg ', xxx, xxx, xxxxx), (4, 'dg', xxx, xxx, xxxxx), (5, 'tg ', xxx, xxx, xxxxx); insert multiple records. Set insert: insert into student set uid = 6, uname = 'hh '; update data: update student set uname = jdk where uid = 4; update student set sscore = sscore + 10; delete data: delete from student; delete all data, same as truncate. Delete from student where uid = 3; select query: Query all information in the table: select * from student; query data of the specified column and condition: select uid, uname from student where uid> 3; as rename the column select uid ad id from student; constant query: select 9 + 1; remove duplicate query: distinct where query: comparison, and, or, in, like, between and, order by, is null, is not null, not, not in,