Basic mysql operations

Basic mysql operations

I. Concept: data database: DB database management system: DBMS Database System: DBS MySQL: Database mysql: client commands (used to connect to services or send SQL commands) SQL: structured Query Language. MySQL supports this language. The SQL language is divided into four parts: DDL, DML, DQL, DCL 2. Connect to the database: mysql-h host name-u user name-p Password Database Name C: \> mysql -- use an anonymous account and password to log on to the local service C: \> mysql-h localhost-u root-proot -- use the root account and root password to log on to the local service C: \> mysql-u root-p -- recommended logon mode: Enter password: *** C: \> mysql-u root-p lamp61 -- log on to the lamp61 database directly. 3. Authorization: Format: grant allows the operation on Database Name. table name to account @ source identified by 'Password'; -- instance: Create a zhangsan account and password 123. authorize all tables in the lamp61 database to add, delete, modify, and query data, source is not limited to mysql> grant select, insert, up Date, delete on lamp61. * to zhangsan @ '%' identified by '000000'; Query OK, 0 rows affected (123 sec) 4. Basic SQL operations mysql> show databases; -- view all databases under the current user mysql> create database [if not exists] database name; -- create database mysql> use test; -- select mysql> drop database name to enter the test database; -- delete a database mysql> show tables; -- view all tables in the current database mysql> select database (); -- view the current database mysql> desc tb1; -- view the table structure of tb1. Mysql> create table demo (-- create demo table-> name varchar (16) not null,-> age int,-> sex enum ('w', 'M ') not null default 'M'); Query OK, 0 rows affected (0.05 sec) mysql> desc demo; -- View table structure + ------- + --------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ------------- + ------ + ----- + --------- + ------- + | name | varchar (16) | NO | NULL | age | int (1 1) | YES | NULL | sex | enum ('w', 'M ') | NO | m | + ------- + --------------- + ------ + ----- + --------- + ------- + 3 rows in set (0.00 sec) mysql> drop table if exists mytab; -- try to delete the mytab table -- add a data mysql> insert into demo (name, age, sex) values ('hangsan', 20, 'w'); Query OK, 1 row affected (0.00 sec) mysql> insert into demo values ('lisi', 22, 'M'); -- do not specify the field name to add data Query OK, 1 row affected (0.00 sec) mys Ql> insert into demo (name, age) values ('wangwu', 23); -- specify some field names to add data Query OK, 1 row affected (0.00 sec) -- batch add data mysql> insert into demo (name, age, sex) values ('aaa', 21, 'w'), ("bbb", 22, 'M'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from demo; -- Query data mysql> update demo set age = 24 where name = 'aaa'; -- modify Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> delete from demo where name = 'bbb '; -- delete Query OK, 1 row affected (0.00 sec) mysql> \ h -- quick help mysql> \ c -- Cancel command input mysql> \ s -- view the current database status mysql> \ q -- exit mysql command line 5. MySQL database data type: mySQL data types are classified into four categories: numeric, String, date, and NULL. 5.1 numeric type: * tinyint (1 byte) smallint (2 byte) mediumint (3 byte) * int (4 byte) bigint (8 byte) * float (4 byte) float (6, 2) * double (8 bytes) decimal (custom) string form value 5.2 string type common string * char fixed length string char (8) * varchar variable string varchar (8) binary tinyblob blob mediumblob longblob text type tinytext * text is commonly used in <textarea> </textarea> mediumtext longtext * enum enumeration set 5.3 time and date type: date year month day time minute second datatime year month day hour minute second timestamp time stamp year 5.4 NULL value NULL meaning "no value" or "unknown "Value" can be used to test whether a value is NULL. You cannot perform arithmetic calculations on the NULL value to perform arithmetic operations on the NULL value. The result is false if it is NULL 0 or NULL, all other values mean the true MySQL OPERATOR: Arithmetic Operator: +-*/% comparison operator: <>=<=! = Database-specific comparison: in, not in, is null, is not null, like, between and logical operator: and or not 6. Table field constraints: unsigned (positive) zerofill leading zero-filling auto_increment auto-incrementing default value not null non-empty primary key (non-null not repeated) unique uniqueness (can be null but not repeated) index general index 7: table creation statement format: create table Name (field name type [field constraint], field name type [field constraint], field name type [field constraints],...); mysql> create table stu (-> id int unsigned not null auto_increment primary key,-> name varchar (8) not null unique,-> age tinyint unsigned,-> sex enum ('M', 'w') not null default 'M',-> classid char (6) ->); Query OK, 0 rows affected (0.05 sec) mysql> desc stu; + --------- + upper + ------ + ----- + --------- + -------------- + | Field | Type | Null | Key | Default | Extra | + --------- + ------------------- + ------ + ----- + --------- + ---------------- + | id | int (10) unsigned | NO | PRI | NULL | auto_increment | name | varchar (8) | NO | UNI | NULL | age | tinyint (3) unsigned | YES | NULL | sex | enum ('M', 'w') | NO | m | classid | char (6) | YES | NULL | + --------- + ------------------- + ------ + ----- + --------- + ------------------ + 5 rows in set (0.00 sec) mysql> show create table stu \ G -- view the table creation statement ************************** * 1. row *************************** Table: Stu Create Table: create table 'std' ('id' int (10) unsigned not null auto_increment, 'name' varchar (8) not null, 'age' tinyint (3) unsigned default NULL, 'sex' enum ('M', 'w') not null default 'M', 'classid 'char (6) default NULL, primary key ('id'), unique key 'name' ('name') ENGINE = MyISAM default charset = utf8 1 row in set (0.00 sec) mysql> insert into stu (id, name, age, sex, classid) values (1, 'Hangsan', 20, 'M', 'lamp 61 '); Query OK, 1 row affected (0.00 sec) mysql> insert into stu (name, age, sex, classid) values ('lisi', 22, 'w', 'lamp61 '); Query OK, 1 row affected (0.00 sec) mysql> insert into stu (name, age, classid) values ('wangw', 21, 'lamp61 '); Query OK, 1 row affected (0.00 sec) mysql> insert into stu values (null, 'qq', 24, 'w', 'lamp62'); Query OK, 1 row affected (0.00 sec) mysql> insert into s Tu values (null, 'A', 20, 'M', 'lamp62'), (null, 'bb ', 25, 'M', 'lamp 63 '); query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from stu; + ---- + ---------- + ------ + ----- + --------- + | id | name | age | sex | classid | + ---- + ---------- + ------ + ----- + --------- + | 1 | zhangsan | 20 | m | lamp61 | 2 | lisi | 22 | w | lamp61 | 3 | wangwu | 21 | m | lamp61 | 4 | qq | 24 | W | lamp62 | 5 | aa | 20 | m | lamp62 | 6 | bb | 25 | m | lamp63 | + ---- + ---------- + ------ + ----- + --------- + 6 rows in set (0.00 sec) 8. Modify the table structure // Requirement Analysis-> function (module) -> Database Design-> entity search, attribute search, link search 9, data backup and recovery (mysqldump...> and mysql... <) -- ================================================ -- 1. the lamp103. SQL file D: \> mysqldump-u root-p lamp103> d:/lamp103.sqlEnter password: -- 2. export the uu table in the lamp103 library. D: \> mysqldump-u root-p lamp103 uu> d:/lamp103_uu.sqlEnter password: -- 3. import (Restore) The lamp103 database D: \> mysql-u root-p lamp103 <. /lamp103.sqlEnter password: