MYSQ Development notes-MYSQL basic enhancement (Mysql basic statements), mysq-mysql

MYSQ Development notes-MYSQL basic enhancement (Mysql basic statements), mysq-mysql
Basic MYSQL enhancement library operations

1 create a database using the UTF-8 character set: 2 create database mydb character set UTF8; 3 4 create a database with a checkpoint set 5 create database mydb character set UTF8 collate utf8_general_ci; 6 7 view all databases: 8 show databases; 9 show databases like 'pattern'; 10 11 show database creation Information 12 show create database mydb; 13 14 Delete A database: 15 drop database mydb; 16 17 modify the database (the database name cannot be modified. You can modify the database details .) 18 alter database mydb character set gb2312; 19 20 backup database (windows Command) 21 exit mysql client, quit, run: 22 mysqldump-uroot-p mydb> c: \ test. mysql command line recovery: 26 use mydb; 27 source d: \ book. SQL; 28 windows Command recovery: 29 mysql-uroot-p mydb <c: test. SQL

Detailed Table operations

1. Modify the table details: add Column 2 alter table books add classify varchar (40); 3 4. view the table creation details: 5 show create table employee; 6. view the table structure: 7 desc books; 8 9 modify the length of the bookname column: modify the column details 10 alter table books modify bookname varchar (60 ); 11 12 Modify column details 13 alter table users change column id int; 14 15 15 Delete author column: 16 alter table books drop author; 17 18 Modify table Name: 19 rename table books to book; 20 21 modify the character set of the table: 22 alter table users character set gb2312; 23 24 modify the column Name: change the password column of the user table to the userpass column 25 alter table user change column password userpass varchar (20); 26 Delete the table: 27 drop table users;

Table operations

1 standard table creation: 2 primary key constraint: primary key 3 primary key automatic growth: auto_increment 4 unique constraint: unique 5 non-null constraint: not null 6 foreign key constraint: 7 constraint ordersid_FK foreign key (ordersid) references orders (id) 8 constraint: defines a constraint 9 ordersid_FK: constraint name 10 foreign key constraint 11 key (ordersid ): constraints who 12 references orders (id) refer to WHO 13 Examples 114 create table test4 (15 id int primary key auto_increment, 16 name varchar (20) unique not null17); 18 examples 2. foreign key constraint 19 create table card (20 id Int primary key, 21 name varchar (20) 22); 23 create table users (24 id int primary key, 25 name varchar (20), 26 card_id int, 27 constraint card_id_FK foreign key (card_id) references card (id) 28); 29 insert into card (id, name) values (1, 'aaa '); 30 insert into users (id, name, card_id) values (1, 'bbbbbb', 1); 31 insert into users (id, name, card_id) values (2, 'bbbbbb', 2); // The error 32 will prompt a foreign key-related error 33 34 insert: 35 insert into users (I D, name) values (1, 'bbbbbb'); 36 insert into users values (1, "aaa"), (2, "bbb"), (3, "ccc"); // insert multiple 37 insert into users set id = 5, name = 'Tom 'in batches; // use set to insert data 38 39 update: 40 update users set name = 'Tom '; // change the user name to tom. Exercise caution when using 41 update users set name = 'Tom' where id = 1; 42 update users set name = 'Tom ', sex = 'male' where id = 2; 43 update employee set sala = sala + 1000 where username = 'Tom '; increase the salary of tom employee by 100044 45 delete: 46. Be sure to use it with the where statement. Otherwise, all records may be deleted 47 delete from table users where id = 1; 48 delete from users; delete All Data 49 truncate table employee; // delete all records in the table, the difference from the previous one is that if the table has a lot of data, it will be faster. It will destroy the table and then recreate it. Auto_increment starts from 1. 50 51 select: 52 select * from users; 53 select id, name from users; 54 select distinct name from users; // remove the repeated column 55 select name, (english + chinese + math + history) from student; // count the total score of each student 56 select * from student where name like '% Li % '; // query a student whose name contains Li's score 57 select * from student where name like 'Lee _ '; // query a student whose name is Li's score is 58 select name, math from student order by math desc; // sort by mathematical score, 59 select count (*) from student by default; // count 60 select count (*) from student where math> = 90; // count the number of mathematical scores greater than 90 61 ...... other functions

Related urls:
Mysql5.x manual http://doc.mysql.cn/
Mysql official website http://dev.mysql.com/
Mysql5.7 reference manual http://dev.mysql.com/doc/refman/5.7/en/
Other reference manual
CSDN-Mysql Knowledge Base

Author: liuning