Shulou(Shulou.com)06/01 Report--

Let's learn about the basics of mysql. I believe you will benefit a lot after reading it. The text is not much in essence. I hope this short article on basic knowledge of mysql is what you want.

Start the Mysql cd to mysql\ bin directory under windows and start / close the mysql service under the dos window

/ / start mysql service mysqld-- console// disable mysql service mysqladmin-uroot shutdown

SQL classification

The main SQL statements can be divided into the following three categories

DDL: data definition language, these statements define different data segments, databases, tables, columns, indexes, and other database objects. Common sentence keywords mainly include create,drop,alter and so on.

DML: data manipulation statements for adding, deleting, updating, and querying database records, and checking data integrity. Commonly used sentence keywords include insert,delete,update, select and so on.

DCL data control statements that control the direct permissions and access levels of different data segments. These statements define databases, tables, fields, user access rights, and security levels. The main sentences include keywords grant, revoke, etc.

DDL statement

It is an operation language for creating, deleting and modifying objects within the database. The biggest difference between DML and DML statements is that DML only operates on the internal data of the table, and does not involve the definition and structure modification of the table, let alone other objects. DDL is more commonly used by database administrators (DBA).

Connect to mysql CVM mysql-uroot-p to create database test1create database test1; shows which databases show databases;//mysql automatically creates information_schema: mainly stores some database information in the system, such as user table information, column information, permission information, character set information, partition information, etc. Cluster: stores the cluster information of the system mysql: stores the user permission information of the system. Test: a test database created automatically by the system, and any user can access the selected database use test1 display all tables created in the test1 database show tables delete database drop database test1; create table create table emp (ename varchar (10), hiredata date,sal decimal (10line 2), deptno int (2)); view table definition desc emp; view creation table definition show create table emp; delete table drop table emp; modify table alter table emp modify ename varchar (20) Add the table field alter table emp add column age int (3); delete the table field alter table emp drop column age; field and rename it alter table emp change age age1 int (4); both change and modify can modify the definition of the table. The difference is that the column name needs to be written twice after change, which is inconvenient, but the advantage of change is that you can modify the column name, then modify cannot modify the field sort alter table emp add birth date after ename;alter table emp modify age int (3) first; to change the table name alter table emp rename emp1

DML statement

Refers to the operation of table records in the database, including insert (insert), update (update), delete (delete) and query (select) of table records.

Insert the record insert into emp (ename,hiredate,sal,deptno) values ('zzx1','2000-01-01) inset into emp (' zzx1','2000-01-01); you may not need to specify the field name, but the order followed by the field should be the same as the order of the fields ('zzx1','2000-01-01). Fields that contain nullable fields, fields that are not empty but contain default values, self-increment fields, and fields that do not need to appear in the list of fields after insert. Only the value of the corresponding field name is written after values, and the fields that are not written can be automatically set to null, default value, and the next batch increment of self-increment is separated by comma insert into dept values (5-zero xxx'), (8-zero xxx'); update record update emp set sal=4000 where ename='xxx' Delete records delete from emp where ename='doney'; query records select * from emp;* represents all records, you can also use fields separated by commas to select records select distinct deptno from emp for which the query does not repeat Conditional query is implemented with the where keyword, and multiple conditions such as! = can be used to sort and restrict that desc and asc are sort keywords, desc is descending order, asc is ascending order ORDER BY sort, default is ascending order select * from emp order by sal If the value of the sort field is the same, the fields with the same value are sorted according to the second sort field, and if there is only one sort field, the same field will be sorted out of order select * from emp order by deptno,sal desc; limit select * from emp order by sal limit 3 / the former is the starting offset, and the latter is the display number of rows select * from emp order by sal limit 1 Limit and order by are used together for paging aggregation users to do some summary operations sum (summation), count (*) (number of records), max (maximum), min (minimum) with rollup is an optional syntax, indicating whether to re-summarize the classified aggregate results having indicates that the classified results are being conditionally filtered. Select deptno,count (1) from emp group by deptno having count (1) > = 1

Table join

It can be divided into outer connection and inner connection.

External links are divided into left links and right links.

Left join: contains all the records in the left table or even no matching records in the right table.

Right connection: same as above

Select ename,detname from emp left join dept on emp.deptno=dept.deptno; left connection and right connection can be converted to each other

Subquery

Select * from emp where deptno in (select deptno from dept); if the subquery record is unique, use = instead of inselect * from emp where deptno = (select deptno from dept limit 1)

Record query

After the data of the two tables are queried, the results are displayed together.

Union all is to gather the results together, while union is to distinct the results after union all to remove repetition.

Select deptno from emp union all select deptno from dept;select demtno from emp union select deptno from dept

? Xxx to check

If you want to view categories? Data types, exactly? Int check syntax such as? Create table

Data type

For shaping data, MySql also supports setting the width in parentheses after the type name. The default setting is int (11). With zerofill, alter table T1 modify id1 int zerofill is filled with the character'0' when the number of digits is not enough.

For decimals, MySql is divided into two types, floating point and fixed point. Floating-point numbers include float and double, while fixed-point numbers only have decimal. Fixed-point numbers are stored as strings inside Mysql, which is more accurate than floating-point numbers and is suitable for data with high precision such as currency.

Floating-point and fixed-point numbers can be added by the type name (MMagneD) M is a few digits, and D is a few digits after the decimal point.

Date Typ

DATE to represent the year, month and day

DATETIME to represent the year, month, day, hour and second

TIME to represent time, minute and second.

Current system time, usually expressed in TIMESTAMP

TIMESTAMP

Create a field of type TIMESTAMP, and the system automatically creates a default value of CURRENT_TIMESTAMP (system date). At the same time, MySql stipulates that a column of TIMESTAMP type fields can only have one default value, current_timestamp. An error will be reported if it is modified.

Another important feature of TIMESTAMP is related to the time zone. When the time is inserted, it is first converted to the local time zone and then stored, and when it is taken out from the database, the date is also converted to the local time zone and displayed, so that users in the two time zones may see the same time zone differently.

View current time zone show variables like 'time_zone'; modify time zone set time_zone='+9.00'

Format of DATETIME insertion

The string of YYYY-MM-DD HH:MM:SS or YY-MM-DD HH:MM:SS allows any punctuation to be used as a spacer for the time part, such as 92 "12" 31 11 ^ 30 ^ 45YYYMMDDHMMSS or a string with no spacer in the format of YYMMDDHHMMSS.

String type

CHAR and VARCHAR types

The main difference between the two is the storage method: the length of the CHAR column is fixed to the length declared when the table is created, and the length can be 0-255; the value in the second VARCHAR column is variable length. At the same time, when searching, the CHAR column removes the trailing space, while VARCHAR retains the space. Because CHAR is a fixed length, its processing speed is much faster than that of VARCHAR, but its disadvantage is a waste of memory, and VARCHAR is more used in use.

Create table vc (v varchar (4), c char (4)) insert into vc values ('ab','ab'); selelct length (v), length (c) from vc//4,2

Enumerate

Create table vc (v varchar (4), c char (4)) insert into vc values ('ab','ab'); selelct length (v), length (c) from vc//4,2

Set Typ

Set type can select more than one member at a time

Create table T2 (col set); INSERT into T2 VALUE ('arecedence b'), (' arecedd'), ('arecedd'), (' arecincec'), ('a'); for this collection containing repeating members, the result is' aford' only once

Operator

MOD==%== division to get the remainder

= and difference

Cannot be used for null comparison, the latter can

Between uses the format a between min and max to be equivalent to a > = min and a bit shift to the right

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.